[국비] SQL 내용정리 Day04

트랜잭션(Transaction)

• 트랜잭션은 논리적인 작업의 단위이다.
• 트랜잭션은 분리되어서는 안 될 작업의 단위이다.
• 트랜잭션의 시작은 실행 가능한 첫 번째 SQL 문장이 실행 될 때 시작한다.
• 트랜잭션은 COMMIT이나 ROLLBACK문에 의해 명시적으로 종료하거나, DDL이나 DCL 문장 실행으로 자동 커밋되어 종료될 수 있다.
• 사용자의 데이터베이스 종료 또는 시스템 충돌(Crash)에 의한 데이터베이스 비정상적 종료에 의해 트랜잭션이 종료되어 변경사항이 취소될 수 있다.

-- Auto Commit 확인
SHOW AUTOCOMMIT;
-- AUTO COOMMIT on
SET AUTOCOMMIT ON;
-- AUTO COOMMIT off
SET AUTOCOMMIT OFF;

• Autocommit이 Off로 지정하고 INSERT를 통해 값을 추가한 후 만약 데이터가 올바르지 않은 경우 값을 수정하거나 되돌려야 하는데 ROLLBACK 키워드를 사용해서 데이터를 취소 또는 되돌릴 수 있다.

INSERT INTO emps
(employee_id, last_name, email, hire_date, job_id)
VALUES (300, 'kim', 'abc@naver.com', sysdate, 1800);

-- 보류 중인 모든 데이터 변경사항을 취소(폐기), 직전 커밋 단계 회귀(돌아가기) 트랜잭션 종료.
ROLLBACK;

• 트랜잭션은 먼저 SQL 문장이 제일 처음 실행될 때 시작하고, 해당 행위에 따라 종료된다.
  • COMMIT이나 ROLLBACK 문장이 완료될 때
  • CREATE 같은 DDL 문장이 완료될 때
  • DCL 문장이 완료될 때
  • 사용자가 SQL Plus 등 실행을 종료할 때
  • 머신 실패 또는 시스템 충돌에 의해 데이터베이스가 비정상적으로 종료될 때
• COMMIT과 ROLLBACK의 장점
  • 데이터의 일관성을 제공한다.
  • 데이터를 영구적으로 변경하기 전에 데이터 변경을 미리보게 한다.
  • 관련된 작업을 논리적으로 그룹화 한다.
  • 하나의 트랜잭션이 끝난 후 실행 가능한 SQL 문장은 자동적으로 트랜잭션을 시작한다.

DELETE FROM emps
WHERE last_name = 'kim';

-- SAVAPOINT 생성
-- ROLLBACK할 포인트를 직접 이름을 붙여서 지정
-- ANSI 표준 문법이 아니기 때문에 권장하지 않는다.
SAVEPOINT delete_kim;

ROLLBACK TO SAVEPOINT delete_kim;

INSERT INTO emps
(employee_id, last_name, email, hire_date, job_id)
VALUES (301, 'Park', 'park@naver.com', sysdate, 1800);

-- 보류 중인 모든 데이터 변경사항을 영구적으로 적용하면서 트랜잭션 종료.
-- commit 이후에는 어떤 방법을 사용하더라도 되돌릴 수 없다.
COMMIT;

• DDL 문장이나 DCL 문장은 자동으로 커밋되기 때문에 트랜잭션을 암시적으로 종료한다.
• COMMIT, SAVEPOINT, ROLLBACK 문장을 사용하면 트랜잭션을 제어할 수 있다.

문장	설명
COMMIT	모든 미결정 데이터를 영구적으로 변경해서 현재 트랜잭션을 종료한다.
SAVEPOINT savepoint_name	현재 트랜잭션 내에 savepoint를 표시하고, savepoint 이름은 식별자 규칙에 따라야 하고 32자만 사용된다.
ROLLBACK [[TO [SAVEPOINT]] savepoint_name]	ROLLBACK은 모든 미결정 데이터 변경을 버림으로써 현재의 트랜잭션을 종료한다.

테이블 생성(CREATE TABLE)

오라클 데이터베이스는 이름을 지정할 때 표준 규칙에 따라서 지정해야 한다.

• 테이블 이름과 열 일므은 문자로 시작해야 하고, 1~30 문자 길이를 가질 수 있다.
• 이름은 오직 A-Z, a-z, 0-9, _(underscore), $, #(#은 유효한 문자이지만 권장되지 않는다.)의 문자만 포함해야 한다.
• 이름은 오라클 서버 사용자에 의해 소유된 다른 객체의 이름과 중복되어서는 안 된다.
• 이름은 오라클 예약어를 사용하면 안 된다.
• 이름은 대소문자를 구분하지 않는다.

CREATE TABLE [schema.]table_name (
	column data_type [DEFAULT expr],
    ...
);

• schema: 소유자의 이름과 똑같다.
• table_name: 테이블의 이름
• DEFAULT expr: INSERT 문장에서 값을 생략할 경우 디폴트 값을 명시한다.
• column: 열의 이름
• data_type: 열의 데이터 타입과 길이

CREATE TABLE dept2 (
    dept_no NUMBER(2),
    dept_name VARCHAR2(14),
    loca VARCHAR2(15),
    dept_date DATE,
    dept_bonus NUMBER(10)
);

• NUMBER(2) -> 정수를 2자리까지 저장할 수 있는 숫자형 타입
• NUMBER(5, 2) -> 정수부, 실수부를 합친 총 자리수 5자리, 소수점 2자리 EX) 123.45
• NUMBER -> 괄호를 생략할 시 (38, 0)으로 자동 지정된다.
• VARCHAR2(byte) -> 괄호 안에 들어올 문자열의 최대 길이를 지정
                                    영어는 1byte, 한글은 2byte로 인식한다. EX) VARCHAR2(14) 영어는 14글자 / 한글은 7글자

데이터형	설명
VARCHAR2(size)	가변 길이 문자 데이터(최대 크기는 명시해야 하며 최소 크기는 1, 최대 크기는 4000바이트 이다.)
CHAR(size)	size Byte 길이의 고정 길이 문자 데이터(디폴트이며 최소 크기는 1, 최대 크기는 2000바이트 이다.)
NUMBER(p, s)	전체 p자리와 소수점 이하 s자리를 가지는 숫자(소수점은 자리수에서 제외) 정밀도는 십진수의 최대 개수이며, 스케일은 소수점 오른쪽의 자리수이다. (정밀도는 1~38까지의 범위이고, 스케일은 -84에서 127까지의 범위를 지정할 수 있다.)
DATE	January 1, 4712 B.C. 와 December 31, 9999 A.D. 사이의 날짜와 시간 값이다.
LONG	2GB까지의 가변 길이 문자 데이터이다.
CLOB	4GB까지의 단일 바이트 문자 데이터이다.
RAW(size)	size 길이의 원시 이진 데이터이다. 최대 크기는 2000이다. (최대 크기는 명시해야 한다.)
LONG RAW	2GB까지의 가변 길이 원시 이진 데이터이다.
BLOB	4GB까지의 이진 데이터이다.
BFILE	4GB까지의 외부 파일에 저장된 이진 데이터이다.

테이블 구조 변경(ALTER TABLE)

-- NUMBER타입에 들어가는 자리수를 확인
INSERT INTO dept3
VALUES (99, '영업직', '서울', sysdate, 2000000);

-- 컬럼 추가
ALTER TABLE dept2
ADD (dept_count NUMBER(3));

-- 열 이름 변경
ALTER TABLE dept2
RENAME COLUMN dept_count TO emp_count;

-- 열 속성 수정
ALTER TABLE dept2
MODIFY (emp_count NUMBER(4));

-- 열 속성 삭제
ALTER TABLE dept2
DROP COLUMN emp_count;

ALTER TABLE dept2
RENAME TO dept3;

• 문자를 숫자로 수정할 경우 신중하게 생각해야 한다.
  데이터 값이 문자가 있는 경우 숫자로 변환하면 에러이다.
  EX) MODIFY (emp_count VARCHAR(30));

테이블 삭제, 데이터 비우기(DROP TABLE, TRANCATE)

-- 테이블 삭제 (구조는 남겨두고 내부 데이터만 모두 삭제)
TRUNCATE TABLE dept3;

DROP TABLE dept3;

테이블 생성과 제약 조건

• 테이블 열레벨 제약조건 (PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY)
• PRIMARY KEY: 테이블의 고유 식별 컬럼 (주요 키) UNIQUE + NOT NULL
• UNIQUE: 유일한 값을 갖게 하는 컬럼 (중복값 방지)
• NOT NULL: null을 허용하지 않음.
• FOREIGN KEY: 참조하는 테이블의 PRIMARY KEY를 저장하는 컬럼
• CHECK: 정의된 형식만 저장되도록 허용.

CREATE TABLE dept2 (
    dept_no NUMBER(2) CONSTRAINT dept2_deptno_pk PRIMARY KEY, 
    dept_name VARCHAR2(14) NOT NULL CONSTRAINT dept2_deptname_uk UNIQUE,
    loca NUMBER(4) CONSTRAINT dept2_loca_locid_fk REFERENCES locations(location_id),
    dept_bonus NUMBER (10), 
    dept_gender VARCHAR2(1) CONSTRAINT dept2_gender_ck CHECK(dept_gender IN('M', 'F'))
);

-- 테이블레벨 제약조건 (모든 열 선언 후 제약조건을 추가하는 방식)
CREATE TABLE dept2 (
    dept_no NUMBER(2),
    dept_name VARCHAR2(14) NOT NULL,
    loca NUMBER(4),
    dept_date DATE,
    date_bonus NUMBER(10),
    dept_gender VARCHAR2(1),
    
    CONSTRAINT dept2_deptno_pk PRIMARY KEY (dept_no),
    CONSTRAINT dept2_deptname_uk UNIQUE (dept_name),
    CONSTRAINT dept2_loca_locid_fk FOREIGN KEY (loca) REFERENCES locations(location_id),
    CONSTRAINT dept2_deptdate_uk UNIQUE (dept_date),
    CONSTRAINT dept2_gender_ck CHECK(dept_gender IN('M', 'F'))  
);

• 외래키(foreign key)가 부모테이블에 없다면 INSERT가 불가능하다.

INSERT INTO dept2
VALUES (10, 'gg', 4000, sysdate, 100000, 'M');

• 외래키가 부모테이블에 있다면 INSERT 가능하다.

INSERT INTO dept2
VALUES (10, 'job1', 2000, sysdate, 100000, 'M');

INSERT INTO dept2
VALUES (20, 'job2', 1800, sysdate, 100000, 'M');

INSERT INTO dept2
VALUES (30, 'job3', 1800, sysdate, 100000, 'F');

• 제약 조건 변경
  • 제약 조건은 추가, 삭제가 가능하다. 변경은 안 된다.
  • 변경하려면 삭제하고 새로운 내용으로 추가해야 한다.

CREATE TABLE dept2 (
    dept_no NUMBER(2),
    dept_name VARCHAR2(14),
    loca NUMBER(4),
    dept_date DATE,
    date_bonus NUMBER(10),
    dept_gender VARCHAR2(1)
);

-- pk 추가
ALTER TABLE dept2 ADD CONSTRAINT dept_no_pk PRIMARY KEY(dept_no);
-- fk 추가
ALTER TABLE dept2 ADD CONSTRAINT dept_loca_fk FOREIGN KEY (loca)
REFERENCES locations(location_id);
-- check 추가
ALTER TABLE dept2 ADD CONSTRAINT dept2_gender_ck CHECK(dept_gender IN('M', 'F'));
-- UNIQUE 추가
ALTER TABLE dept2 ADD CONSTRAINT dept2_deptname_uk UNIQUE (dept_name);
-- NOT NULL은 열 수정형태로 변경.
ALTER TABLE dept2 MODIFY dept_name VARCHAR2(14) NOT NULL;

-- 제약조건 삭제 (제약조건 이름으로)
ALTER TABLE dept2 DROP CONSTRAINT dept_no_pk;

SQL Quiz

문제1.
- 조건) M_NAME 는 가변문자형 널값을 허용하지 않는다.
- 조건) M_NUM 은 숫자형 , 이름 mem_memnum_pk primary key
- 조건) REG_DATE 는 날짜형 , 널값을 허용하지 않음 , 이름 mem_regdate_uk ) UNIQUE 키
- 조건) GENDER 가변문자형
- 조건) LOCA 숫자형 , 이름 mem_loca_loc_locid_fk ) foreign key 참조 locations 테이블 location_id

CREATE TABLE members (
    m_name VARCHAR2(20) NOT NULL,
    m_num NUMBER(3) CONSTRAINT mem_memnum_pk PRIMARY KEY,
    reg_date DATE NOT NULL CONSTRAINT mem_regdate_uk UNIQUE,
    gender VARCHAR2(1) CHECK(gender IN('M', 'F')),
    loca NUMBER(4) CONSTRAINT mem_loca_loc_locid_fk REFERENCES locations(location_id)
);

문제2.
- MEMBERS테이블과 LOCATIONS 테이블을 INNER JOIN 하고 m_name , m_mum , street_address , location_id 컬럼만 조회
- m_num 기준으로 오름차순 조회

INSERT INTO members
VALUES ('AAA', 1, '18/07/01', 'M', 1800);
INSERT INTO members
VALUES ('BBB', 2, '18/07/02', 'F', 1900);
INSERT INTO members
VALUES ('CCC', 3, '18/07/03', 'M', 2000);
INSERT INTO members
VALUES ('DDD', 4, SYSDATE, 'M', 2000);

COMMIT;

SELECT 
    m.m_name,
    m.m_num,
    loc.street_address,
    loc.location_id
FROM members m
JOIN locations loc
ON m.loca = loc.location_id
ORDER BY m.m_num ASC;​