티스토리 뷰
https://www.youtube.com/watch?v=ijpxmi4DPj4
오히려 옵티마이저가 빠지고 DML,TCL,DDL,DCL을 다룬다.
DML
-데이터의 삽입(INSERT), 수정(UPDATE),삭제(DELETE), 병합(MERGE)이 필요하다.
-반드시 COMMIT, ROLLBACK 반드시 필요하다 - TCL
INSERT
-테이블에 행을 삽입
- 한 번에 한 행만 입력가능(SQL Serve, 여러 행 동시 삽입 가능
- 컬럼별 데이터타입과 사이즈에 맞게 삽입(스키마 필수)
- 작성하지 않은 컬럼은 NULL이 입력된다.
- 전체 컬럼에 대한 데이터 입력시 테이블명 뒤의 컬럼명 생략 가능
UPDATE
- 데이터 수정할 떄 사용
- 컬럼 단위 수행
- 다중 컬럼 수정 가능
UPDATE 테이블명 SET 수정할 컬럼명 = 수정값
WHERE 조건
WHERE 절로 수정 대상을 선택 가능하다.
2. 다중컬럼 수정
- 방법 1
UPDATE 테이블명 SET 수정컬럼명1=수정값 1, 수정컬럼명 2= 수정값 2
WHERE 조건
-방법 2
UPDATE 테이블명
SET (수정컬럼명1,수정컬럼명 2 ) = (SELECT 수정값 1, 수정값 2)
WHERE 조건
UPDATE 테이블명
SET (수정컬럼명1,수정컬럼명 2 ) = (SELECT MAX(SAL), MAX(COM) FROM 테이블명)
WHERE 조건
서브쿼리로 값을 하나씩 리턴해줘야 한다.
3) DELETE
-데이터 삭제할 떄 사용
- 행 단위로 실행된다.
DELETE [FROM] 테이블명
[WHERE 조건]
WHERE 절로 삭제할 행을 선택 가능하다.
4) MERGE
-데이터 병합
-참조 테이블과 동일하게 맞추는 작업(참조 테이블의 데이터 입력, 참조 테이블의 값으로 수정 등)
MERGE INTO 테이블명
USING 참조테이블
ON (연결조건)
WHEN MATCHED THEN
UPDATE
SET 수정할 내용
WHEN NOT MATCHED THEN
INSERT VALUES(삽입할 내용)
연결 조건은 생략 불가(둘 다 있는 연결조건이어야 한다)
NEW 테이블을 바탕으로 OLD를 수정한다.
테이블명은 OLD가 먼저 오고, 참조 테이블은 NEW가 된다.
연결 조건은 Alias를 쓸 수 있다.
MERGE INTO OLD m1
USING NEW m2
ON (m1.no = m2.no)
WHEN MATCHED THEN
UPDATE
SET m1.price = m2.price #매칭이 되었다면 가격만 업데이트
WHEN NOT MATCHED THEN
INSERT VALUES (m2.no, m2.name, m2.price) #다 없다면 행 전체를 업데이트
TCL
트랜잭션 제어어로 COMMIT, ROLLBACK이 포함된다.
DML에 의해 조각된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어
DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK 발생할 수 있다.
잠금(LOCK)
-트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한
-잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만이 접근 및 해제 가능하다(관리자 권한 계정 제외)
트랜잭션
- 트랜잭션은 데이터 베이스의 논리적 연산 단위(하나의 연속적인 업무 단위)
-분할 할 수 없는 최소의 단위
- ALL OR NOTHING 개념(모두 COMMIT 하거나 ROLLBACK 처리 되어야 한다.
트랜잭션의 특성
원자성 : ALL OR NOTHING
일관성 : 실행 이후에도 잘못되어있으면 안된다.
고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받으면 안됨
지속성 : 갱신한 데이터베이스 내용이 영구적으로 저장
COMMIT
-한 번 커밋을 수행하면 커밋 이전에 수행된 DML은 모두 저장되며 되돌릴 수 없다.
-ORACLE은 DDL 시 AUTI COMMIT(비활성화는 가능)
ROLLBACK
- 데이터베이스에 저장되지 않고, 최종 커밋 지점/변경 전 / 특정 SAVEPOINT 지점으로 원상복구(단, 그 중간에 커밋이 찍혔으면 불가)
-최종 커밋 시점 이전까지 롤백 가능
SAVEPOINT
- 롤백을 부분적으로 수행하기 위해 사용되는 지점을 지정
SAVEPOINT savepoint_name;
INSERT 행
INSERT 행
COMMIT
UPDATE ROLLBACK_TEST 9999
ROLLBACK
#그러면 ROLLBACK은 적용 X
UPDATE ROLLBACK_TEST 9998
COMMIT
UPDATE ROLLBACK_TEST 9999 ---(1)
SAVEPOINT A;
DELETE ROLLBACK_TEST
UPDATE ROLLBACK_TEST
ROLLBACK TO A;
#여기서 SAVE 포인트로 돌아가고, (1)번은 적용된 상태.
COMMIT # COMMIT을 하면 9999가 확정된다.
DDL
-데이터 정의어
CREATE(생성), ALTER(변경) , DROP( 삭제) TRUNCATE( 데이터 삭제)
AUTO COMMIT(명령어 수행하면 즉시 저장하고, 원상복구 불가하다)
CREATE
- 테이블 생성 시 소유자 명시 가능(생략 시 명령어 수행 계정의 소유다)
- 테이블이나 인덱스와 같은 객체를 생성하는 명령어
테이블 복제
- CREATE TABLE 테이블명 AS SELECT * FROM 복제테이블명;
- SELECT 문의 결과랑 완전히 동일하게 만들어진다.
- 테이블에 있는 제약조건, INDEX 등은 복제되지 않는다.
CHAR(n) - 고정형 문자 타입으로 사이즈 전달 필수. 사이즈 만큼 확정 데이터 입력(빈자리수는 공백)
VARCHAR2(n) - 가변형 문자 타입으로 사이즈 전달 필수. 사이즈보다 작은 문자값이 입력되더라도 입력값 그대로 유지
NUMBER(p, s) - 숫자형 타입으로 자리수 생략 가능. 소수점 자리 제한 시 s 전달(p는 총 자리수)
DATE - 날짜타입으로 사이즈 전달 불가
WHERE 절에 항상 거짓인 조건을 쓰게 되면 데이터는 없고, 구조만 카피된다.
ALTER
- 테이블 구조 변경(컬럼명, 컬럼 데이터 타입, 컬럼사이즈, defalut 값, 컬럼삭제, 컬럼추가, 제약조건)
-컬럼순서 변경 불가(재생성으로 해결해야 한다) 무조건 추가되는 컬럼은 맨 뒤에 붙는다.
1. 컬럼 추가
- 새로 추가된 컬럼위치는 맨 마지막(절대 중간 위치에 추가 불가능하다)
- 컬럼 추가 시 데이터타입 필수, default 값, 제약조건 명시할 수 있음
- 여러 컬럼 동시 추가 가능(반드시 괄호 사용한다)
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFALUT] [제약조건];
컬럼명을 여러개 할 때 괄호를 생략하면 에러가 걸린다.
ADD (컬럼명 1, 컬럼명 2) 여야 한다.
데이터가 있는 상태에서 새로 컬럼 추가시 NULL이 된다.
그래서 컬럼 추가시 NOT NULL 속성은 전달 불가하다.
하지만 데이터가 없는 상태에서는 NOT NULL로도 가능하다.
컬럼 추가시 DEFAULT를 선언하면 NOT NULL 속성을 갖는 컬럼 추가 가능하다.
2. 컬럼(속성 변경)
- 컬럼 사이즈, 데이터 타입, default 값 변경 가능
- 여러 컬럼 동시 변경 가능
ALTER TABLE 테이블명 MODIFY (컬럼명 DEFAULT 값)괄호 생략 가능하다
1) 컬럼 사이즈 변경
- 컬럼 사이즈 증가는 항상 가능하다.
- 컬럼 사이즈 축소는 데이터 존재 여부에 따라 제한(데이터가 있는 경우 데이터의 최대 사이즈 만큼 축소 가능)
- 동시 변경 가능(반드시 괄호 필요)
ALTER TABLE 테이블명 MODIFY (COL_A NUMBER(10), COL_B VARCHAR(6));
2) 데이터 타입 변경
- 빈 컬럼일 경우에만 데이터 타입 변경 가능
- 단, CHAR, VARCHAR 타입일 경우 데이터가 있어도 서로 변경 가능하다.
ALTER TABLE EMP MODIFY DEPTNO NUMBER(4) 가능 #데이터가 없는 상태에서의 변경
3) DEFAULT 값 변경 - 자주출제
- DEFALUT 값이란 특정 컬럼에 값이 생략될 경우(입력 시 언급되지 않을 경우) 자동으로 부여되는 값
- INSERT 시 DEFAULT 값이 선언된 컬럼에 NULL을 직접 입력할 떄는 DEFAULT 값이 아닌 NULL이 입력된다.
- 이미 데이터가 존재하는 테이블에 DEFAULT 값 선언 시 기존 데이터 수정 안된다(이후 입력된 데이터부터 적용된다)
- DEFAULT 값 해제 시 DEFAULT 값을 NULL로 선언한다.
ALTER TABLE T2 MODIFY (SAL DEFAULT 3000);
3) 컬럼 이름 변경
- 데이터가 있건 없건 항상 가능하고, 동시에 여러 컬럼 이름 변경 불가능하다(괄호 전달 불가)
-ALTER RENAME 명령어로 처리한다.
RENAME COLUMN 기본 컬럼명 TO 새컬럼명
4) 컬럼 삭제
- 여기도 데이터가 있건 없건 항상 가능
- RECYCLEBIN에 남지 않아 위험
- 여기도 동시 삭제 불가
DROP
- 객체(테이블, 인덱스 등) 삭제
- DROP 후에는 조회 불가
PURGE로 테이블 삭제시 RECYCLEBIN에서 조회가 불가하다.
TRUNCATE
- 구조는 남기고 데이터만 즉시 삭제, 즉시 반영(AUTO COMMIT)
- RECYCLEBIN에서 남지 않는다.
DELETE /DROP/ TRUNCATE 차이
- DELETE : 데이터 일부 또는 전체 삭제, 롤백 가능
- TRUNCATE : 데이터 전체 삭제만 가능(일부 삭제 불가), 즉시 반영(롤백 불가)
- DROP : 데이터와 구조를 동시 삭제, 즉시 반영(롤백 불가)
제약 조건
- 데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치다.
- 테이블 생성 시 정의 가능하고, 컬럼 추가 시 정의 가능하다.
- 이미 생성된 컬럼에 제약조건만 추가 가능하다.
PRIMARY KEY (기본키)
- 유일한 식별자(각 행을 구별할 수 있는 식별자 기능)
- 중복 허용 X, NULL 허용 X => UNIQUE + NOT NULL
- 하나의 테이블에 여러 기본키를 생성할 수 없다
- 하나의 기본키를 여러 컬럼을 결합하여는 생성할 수 있다.
- PRIMARY KEY 생성 시 자동으로 UNIQUE INDEX가 생성된다.
CREATE TABLE
컬럼 1 데이터 타입 [DEFAULT 기본값] [제약조건] - 제약조건 부여
이미 생성된 컬럼이면 ALTER에서 ADD [CONSTRAINT 제약조건명] 제약조건 종류
DROP CONSTRAINT 제약조건명
제약조건 = PRIMARY KEY
NO NUMBER(10)
2. UNIQUE
- 중복을 허용하지 않음
-NULL은 허용
3. NOT NULL
제약조건 중 하나지만, 제약조건을 추가하는 방식이 아니다.
NOT NULL을 선언하지 않으면 Nullable 컬럼으로 생성된다.
그래서 이미 만들어진 컬럼에 NOT NULL 선언하려면 MODIFY를 사용해야 한다.
4. FOREIGN KEY(중요)
- 참조 테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성
- 반드시 참조(부모) 테이블의 참조 컬럼(REFERENCE KEY)이 사전에 PK 혹은 UNIQUE KEY를 가져야 한다.
부모가 확정되어 있지 않으면 포린 키를 쓸 수 없다.
CREATE TABLE 테이블명(
컬럼 1 데이터 타입 [DEFAULT 값] REFERENCES 참조테이블(참조키) )
FOREIGN KEY 옵션(생성 시 정의되고 변경 불가능하다. 재생성 해야 한다)
1. ON DELETE CASCADE : 부모 데이터 삭제 시 자식 데이터 함께 삭제
2. ON DELETE SET NULL : 부모 데이터 삭제 시 자식 데이터의 참조값은 NULL로 수정
5. CHECK
- 직접적으로 데이터의 값을 제한한다(양수)
SAL > 0 같은 조건
기타 오브젝트 - 한 두 문
1) 뷰(VIEW)
2) 시퀀스(SEQUENCE)
-자동으로 연속적인 숫자를 부여해주는 객체
- Python으로 따지면 Range?
3) 시노님
- 테이블 별칭 생성
HR 계정에서 SCOTT.EMT를 EMT로 조회하는 방볍
원래 이름인데 EMT라는 별칭을 준다.
DCL
- 데이터 제어어로 객체에 대한 권을 부여 (GRANT) 하거나 회수 (REVOKE) 하는 기능
- 테이블 소유자는 타계쩡에 테이블 조회 및 수정 권한 부여 및 회수 가능하다.
1) 오브젝트 권한
- 테이블에 대한 권한 제어
- 테이블 소유자는 타계쩡에 소유 테이블에 대한 조회 및 수정 권한 부여 및 회수 가능하다
2) 시스템 권한
- 시스템 작업 등을 제어
GRANT
- 동시에 여러 유저에 대한 권한 부여 가능
- 동시 여러 권한 부여 가능
- 동시 여러 객체 권한 부여는 불가능하다.
GRANT 권한 ON 테이블명 FROM 유저
REVOKE
- 동시 여러 권한 회수 가능
- 이미 회수된 권한 재회수 불가
- 도이셍 여러 유저로부터 권한 회수 가능
REVOKE 권한 ON 테이블명 FROM 유저
롤(ROLE)
- 권한의 묶음(생성 가능한 객체)
- SYSTEM 계정에서 ROLE 생성 가능
롤은 재접속을 해야 권한 부여가 된다.
롤을 통해 부여한 권한은 직접 회수가 불가능하다.
롤을 통한 회수만 가능하다.
권한부여 옵션(중간관리자의 권한)
1. WITH GRANT OPTION
- WITH GRANT OPTION으로 받은 오브젝트 권한을 다른 사용자에게 부여할 수 있다.
- 중간관리자가 부여한 권한은 중간 관리자만 회수 가능
- 중간관리자에게 부여된 권한 회수 시 제 3자에게 부여된 권한도 함께 회수된다.
2. WITH ADMIN OPTION
- WITH ADMIN OPTION을 통해 부여 받은 시스템 권한/롤 권한을 다른 사용자에게 부여할 수 있음
- 중간관리자를 거치지 않고 직접 회수 가능
- 중간관리자 권한 회수 시 제 3자에게 부여된 권한은 함께 회수되지 않는다.