정미나, "유선배 SQL 개발자(SQLD) 과외노트", 시대고시기획
해당 책을 바탕으로 작성하였습니다.
그냥 개인적으로 다시 보고 공부하려고 만들었습니다.😀
DML
Data Manipulation Language
데이터를 입력하고 입력된 데이터를 수정, 삭제, 조회하는 명령어.
INSERT
데이터 삽입.
- INSERT INTO 테이블명 (컬럼명1, 컬럼명2...) VALUES (데이터1, 데이터2...);
이 경우 명시되지 않은 컬럼에는 NULL값이 입력되는데 PK나 NOT NULL 제약조건이 걸린 컬럼에는 NULL 값이 입력될 수 없다.
- INSERT INTO 테이블명 VALUES(데이터1, 데이터2...);
이 경우 전체 컬럼에 대한 데이터가 테이블의 컬럼 순서대로 빠짐없이 나열되어야 하는데 순서가 바뀌어 데이터 유형이 맞지 않거나 누락된 데이터가 있어 전체 컬럼 갯수와 맞지 않을 경우 에러.
UPDATE
이미 저장된 데이터를 수정.
- UPDATE 테이블명 SET 컬럼명 = 새로운 데이터 (WHERE 조건);
where 조건 절이 없으면 모든 데이터가 수정됨.
DELETE
이미 저장된 데이터를 삭제.
- DELETE FROM 테이블명 (WHERE 조건);
만약 where 절 없이 정말 테이블 전체 데이터를 삭제하고자 하는 경우 DELETE보다 TRUNCATE가 시스템 부하 측면에서 유리. TRUNCATE는 별도의 로그를 쌓지 않아 ROLLBACK이 불가능하며 DELETE는 COMMIT 전에 ROLLBACK이 가능.
INSERT, UPDATE, DELETE
명령어를 날리고 별도의 COMMIT 명령어를 실행해야 데이터가 반영되며 ROLLBACK도 가능.MSSQL의 경우 AUTO COMMIT 됨.
MERGE
테이블에 새로운 데이터를 입력하거나 이미 저장되어 있는 데이터에 대한 변경 작업을 가능하게 함.
- MERGE INTO 타겟테이블명 USING 비교테이블명 ON 조건
WHERE MATCHED THEN
UPDATE SET 컬럼명 = 새로운 데이터 [, 컬럼명 = 새로운 데이터..]
WHERE NOT MATCHED THEN
INSERT [(컬럼명1, 컬럼명2...)]
VALUES (데이터1, 데이터2...);
예시)
MERGE
INTO user_backup ub
USING user u
ON (ub.id = u.id)
WHEN MATCHED THEN
UPDATE
SET ub.name = u.name
ub.email = u.email
...
WHEN NOT MATCHED THEN
INSERT(ub.id, ub.name, ub.email...)
VALUES(u.id, u.name, u.email...);TCL
Transaction Control Language
트랜잭션을 제어하는 명령어.
트랜잭션의 특징
- 원자성 : 트랜잭션으로 묶인 일련의 동작들은 모두 성공하거나 모두 실패해야 함.
- 일관성 : 트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야 함.
- 고립성 : 하나의 트랜잭션은 고립되어 수행돼야 함.
- 지속성 : 트랜잭션이 성공적으로 수행됐을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장돼야 함.
COMMIT
INSERT, DELETE, UPDATE 후 변경된 내용을 확정, 반영하는 명령어.
COMMIT을 실행하지 않으면 메모리까지만 반영이 되는데 메모리는 휘발성이기 때문에 언제든 사라질 수 있고 다른 사용자는 변경된 값을 조회할 수 없다.
COMMIT을 해야 최종적으로 데이터 파일에 기록이 되고 비로소 트랜잭션이 완료되는 것.
UPDATE 한 뒤 오랜 시간 동안 COMMIT이나 ROLLBACK을 하지 않았을 경우 Lock에 걸려 다른 사용자가 변경할 수 없는 상황이 발생할 수 있으니 주의.
ROLLBACK
INSERT, DELETE, UPDATE 후 변경된 내용을 취소하는 명령어. ROLLBACK을 하면 변경하기 이전 값으로 복구. 위와 마찬가지로 Lock이 걸릴 수 있으니 주의.
SAVEPIONT
ROLLBACK을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능을 가진 명령어.
ROLLBACK 뒤에 특정 SAVEPOINT를 지정해주면 그 지점까지만 데이터가 복구.
DDL
Data Definition Language
데이터 정의어.
| 유형 | 데이터타입 |
|---|---|
| 문자 | CHAR, VARCHAR, CLOB |
| 숫자 | NUMBER |
| 날짜 | DATE |
CHAR, VARCHAR 차이
CAHR은 고정길이, VARCHAR은 가변길이
CHAR에서 'Jennie' = 'Jennie '
VARCHAR에서 'Jenni' != 'Jenni '
CREATE
테이블을 생성하기 위한 명령어.
- CREATE TABLE 테이블명 (
컬럼명1 데이터 타입 (옵션)
...
);
예시
create table teacher(
teacher_no number not null,
teacher_name varchar2(20) not null,
subject_id varchar(5) not null,
mobile_no varchar2(15),
address varchar2(100),
constraint teacher_pk primary key (teacher_no),
constraint teacher_fk foreign key (subject_id) references subject(subject_id)
);테이블 생성 시 규칙
지키지 않으면 에러 발생
- 테이블명은 고유해야 함.
- 한 테이블 내에서 컬럼명은 고유해야 함.
- 컬럼명 뒤에 데이터 유형과 데이터 크기가 명시돼야 함.
- 컬럼에 대한 정의는 괄호() 안에 기술.
- 각 컬럼들은 ','로 구분.
- 테이블명과 컬럼명은 숫자로 시작 불가.
- 마지막은 ';'로 끝남.
안 지켜도 되지만 지키는 것을 권장
- 테이블은 각각의 정체성을 나타내는 이름을 가져야 함. 정체 모를 이름의 테이블명은 지양.
- 컬럼명을 정의할 때는 다른 테이블과 통일성이 있어야 함. 같은 데이터를 저장하는 컬럼이 A 테이블에서는 USER_ID이고 B 테이블에서는 MEMBER_ID면 안 됨.
제약조건(CONSTRAINT)
테이블에 저장될 데이터의 무결성, 즉 데이터의 정확성과 일관성을 유지하고 데이터에 결손과 부정합이 없음을 보증하기 위해 작성.
초기에 정의하는 것이 바람직.
- PRIMARY KEY(기본키) : 테이블에 저장된 각각의 ROW에 대한 고유성을 보장. 한 테이블에 하나씩만 정의할 수 있으며 PK로 지정된 컬럼에는 NULL 불가. 자동으로 UNIQUE 인덱스로 생성.
- UNIQUE KEY(고유키) : PK와 유사하게 테이블에 저장된 각각의 ROW에 대한 고유성을 보장. but NULL 가능.
- NOT NULL : 해당 컬럼은 NULL 불가.
- CHECK : 컬럼에 저장될 수 있는 값의 범위를 제한.
예) CONSTRAINT COL1 CHECK(COL1 IN('Y', 'N')) - FOREIGN KEY(외래키) : 하나의 테이블이 다른 테이블을 참조하고자 할 때 FK를 정의.
참조 무결성 규정 관련 옵션
- CASCADE : Parent 값 삭제 시 Child 값 같이 삭제
- SET NULL : Parent 값 삭제 시 Child의 해당 컬럼 NULL 처리
- SET DEFAULT : Parent 값 삭제 시 Child의 해당 컬럼 DEFAULT 값으로 변경
- RESTRICT : Child 테이블에 해당 데이터가 PK로 존재하지 않는 경우에만 Parent 값 삭제 및 수정 가능
- NO ACTION : 참조 무결성 제약이 걸려있는 경우 삭제 및 수정 불가
기존에 존재하던 테이블을 복사해서 생성하고 싶은 경우
- create table user_backup as select * from user;
컬럼별로 데이터 유형을 다시 명시하지 않아도 되지만 제약 조건이 모두 복사되는 것이 아니라 NOT NULL 조건만 복사되며 나머지 PK, UK, CHECK 등의 조건은 초기화되므로 필요 시 별도로 ALTER 명령어를 써서 정의해야 함.
ALTER
보통 한 번 생성된 테이블의 구조는 변경하지 않는 것이 일반적. 하지만 불가피하게 변경할 경우 사용.
ADD COLUMN
새로운 컬럼 추가.
추가된 컬럼의 위치는 맨 끝. 별도로 위치 지정 불가.
- ALTER TABLE 테이블명 ADD 컬럼명 데이터유형;
DROP COLUMN
기존에 있던 컬럼 삭제.
복구 불가.
- ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
MODIFY COLUMN
기존에 있던 컬럼명 변경.
데이터 유형, DEFAULT 값, NOT NULL 조건 변경 가능.
기존 컬럼에 저장된 데이터가 바꾸려는 데이터의 크기보다 크다면 변경 불가.
DEFAULT 값 변경 시에는 변경 이후 저장되는 데이터에만 적용.
현재 NULL 값이 저장되어 있지 않은 컬럼에만 NOT NULL 조건 추가 가능.
- ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형 [조건], 컬럼명2..);
RENAME COLUMN
기존에 있던 컬럼명 변경.
- ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 변경할컬럼명;
ADD CONSTRAINT
제약조건 추가.
- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);
DROP TABLE
테이블 삭제. 해당 테이블을 참조하고 있는 다른 테이블이 존재하는 경우 CASCADE 옵션을 명시하지 않으면 삭제되지 않는다.
- DROP TABLE 테이블명 [CASCADE CONSTRAINT]
CASCADE CONSTRAINT는 참조 제약조건도 함께 삭제한다는 뜻.
TRUNCATE TABLE
테이블에 저장되어 있는 데이터를 모두 제거.
저장 공간이 재사용되도록 초기화 됨.
ROLLBACK이 불가해 DDL로 분류.
- TRUNCATE TABLE 테이블명;
DCL
Data Control Language
USER를 생성, USER에게 데이터를 컨트롤할 수 잇는 권한을 부여하거나 회수.
USER 관련 명령어
하나의 DB에는 여러 USER를 가질 수 있다.
CREATE USER
사용자를 생성. CREATE USER 권한이 있어야 수행 가능.
- CREATE USER 사용자명 IDENTIFIED BY 패스워드;
ALTER USER
사용자를 변경.
- ALTER USER 사용자명 IDENTIFIED BY 패스워드;
DROP USER
사용자를 삭제.
- DROP USER 사용자명;
권한 관련 명령어
GRANT
사용자에게 권한을 부여.
- GRANT 권한 TO 사용자명;
REVOKE
사용자에게 권한을 회수.
- REVOKE 권한 FROM 사용자명;
ROLE 관련 명령어
특정 권한들을 하나의 세트처럼 묶는 것.
ROLE을 이용한 권한 부여
- ROLE 생성
- CREATE ROLE 롤명;
- ROLE에 권한 부여
- GRANT 권한 TO 롤명;
- ROLE을 사용자에게 부여
- GRANT 롤명 TO 사용자명;
주의
create table smp1 (col1 number);
insert into smp1 values(1);
create table smp2 (col1 number);
insert into smp2 values(1);
rollback;
insert into smp2 values(2);
select count(*) from smp1;
=> 1- Oracle의 경우 DDL 수행 시 묵지석으로 COMMIT이 수행됨.
- alter table smp modify col1 number default 0;
- alter table smp modify (col1 number default 0);
- alter table smp add col1 date;
- alter table smp add (col1 date);
=> 모두 문법상 가능.
트랜잭션 동작 중 일부만 ROLLBACK 될 수 있다?
X
트랜잭션의 특성 중 하나인 원자성. 이는 일부만 ROLLBACK 될 수 없음을 의미. 만약 트랜잭션 중간에 SAVEPOINT를 지정한다면 하위 트랜잭션이 생성되는 것. 그리고 그 트랜잭션 단위로 ROLLBACK이 되는 것임.
create table smp (col1 number, col2 varchar(1));
- insert into smp values(1, '1');
- insert into smp values('1', '1');
- insert into smp values(1, 1);
모두 가능하다 왜? 암시적인 형변환 즉 데이터베이스가 내부적으로 알아서 데이터 유형을 변환하기 때문에.
- insert into smp values('a', 1);
이건 안 됨 왜? A는 숫자가 아니므로 암시적 형변환 불가.
