1. 트랜잭션 (transaction)
1) 개념
여러개의 DML 작업들을 하나의 작업으로 묶어서 처리하는 개념으로
묶음을 의미한다. 묶음의 개수 (1개 이상)
2) 명령어
-
commit
트랜잭션이 모두 실행했을때 성공해서 실제로 DB에 반영하는 명령어.
즉 모든 DML은 실행되었을 때 실제 DB에 반영 안 됨 ★ -
rollback
3) 트랜잭션 용도
예> 계좌이체 ( update + insert )
A B
1000
성공시 - commit 으로 실제 DB에 반영
A B
500 500
실패시 - rollback 으로 모두 취소.
A B
10004) 트랜잭션 시작 ~ 종료
시작 : DML 사용시 자동으로 시작
종료 : 명시적으로 종료해야됨 (commit / rollback)
commit - 실제 DB에 반영하는 방식으로 종료
rollback - 취소하면서 종료
cf) DBMS 종류에 따라 auto commit이 되기도 한다
: 예> MySQL. 하지만 비활성 가능
: Oracle은 명시적으로 commit 해야됨
2. DML (Data Manipulation Language 데이터 조작어)
1) 종류
- insert
테이블의 레코드 생성
upsert(update+insert) : 값이 존재하면 update되고 없으면 insert - delete
테이블의 레코드 삭제 - update
테이블의 컬럼 수정
2) 특징
- 트랜잭션 관련됨
2-1. INSERT
1) 용도
-새로운 레코드 생성.
-단일행 생성 또는 멀티행 생성 모두 가능
2) 문법
(1) 새로운 테이블 생성
CTAS 방법 : 제약 조건은 복사가 안 됨
-- 구조만 복사해서 새로운 테이블 생성
Create Table 테이블명
As
Select 문;
CREATE TABLE copy_emp
AS
SELECT empno, ename, sal FROM emp
WHERE 1=2; -- 빈 테이블 생성(2) 단일행 생성하는 방법
- 컬럼명 지정한 경우 (권장)
==> 선택적으로 값을 저장이 가능
나머지는 null로 저장됨
insert into 테이블명 (컬럼명, 컬럼명1 )
values ( 값, 값1 );
commit;
문자와 날짜는 반드시 '' 로 지정해야 된다- 컬럼명 생략한 경우
==> 선택적으로 값 저장이 불가
반드시 테이블의 모든 컬럼에 값을 저장해야 된다
==> 지정된 값 순서대로 실제 테이블의 컬럼에 맵핑된다
insert into 테이블명
values ( 값, 값1, ..., 값6 );
commit;(3) 다중행 생성하는 방법
- 서브쿼리 이용
insert into 테이블명 ( 컬럼명, 컬럼명1 )
select 컬럼명, 컬럼명2
from 테이블명;- value_list 이용 ★
insert into 테이블명 (컬럼명, 컬럼명1 )
values ( 값, 값1 ),( 값, 값1 ),( 값, 값1 );2-2. UPDATE
1) 용도
- 컬럼 수정
2) 문법
(1) 조건 없는 update
: 모든 레코드 대상
update 테이블
set 컬럼명1=값 , 컬럼명2=값 ;
commit;(2) 조건 있는 update ★
: 조건에 일치하는 레코드 대상
update 테이블
set 컬럼명1=값 , 컬럼명2=값
where 조건식;
commit;(3) 서브쿼리 이용한 update
UPDATE dept
SET dname =(SELECT dname
FROM dept
WHERE deptno = 20 ) as X,
loc = ( SELECT loc
FROM dept
WHERE deptno = 30 ) as X2
WHERE deptno = 90;- MySQL
UPDATE dept , (SELECT dname
FROM dept
WHERE deptno = 20 ) as X,
(SELECT loc
FROM dept
WHERE deptno = 30 ) as X2
SET dept.dname = X.dname,
dept.loc = X2.loc
WHERE deptno = 90;2-3. DELETE
1) 용도
- 레코드 삭제
2) 문법
(1) 조건식 없는 형태
: 모든 레코드 대상이므로 모든 레코드가 삭제됨
delete from 테이블명;(2) 조건식 있는 형태
: 조건에 일치하는 레코드만 삭제됨
delete from 테이블명
where 조건식;(3) limit 지원
delete from 테이블명
where 조건식
order by 표현식
limit n;3. DDL (Data Definition Language : 데이터 정의어)
1) 용도
- 객체 (테이블, 인덱스, 프로시저, 사용자,...) 생성, 수정, 삭제할 때 사용하는 SQL문
- 무조건 auto commit !! (commit; rollback; 적용 X)
2) 종류
- CREATE : 객체를 생성하는 명령어
- ALTER : 객체의 구조를 변경하는 명령어. 객체에 따라 세부적인 명령어나 문법이 상이
- DROP : 객체를 제거할 때 사용하는 명령어
- TRUNCATE : 테이블의 데이터를 제거하는 데 사용하는 명령어. DELETE와 다르게 DDL이기 때문에 ROLLBACK이 되지 않으므로 사용에 주의
3) 데이터 타입
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/data-types.html
(1) 수치형
- 정수 :
INTEGER(INT, 4byte), SMALLINT(2byte), MEDIUMINT(3byte), BIGINT(8byte) - 실수 :
부동소수점 - FLOAT, DOUBLE
고정소수점 - DECIMAL, NUMERIC
(2) 문자형
- 고정 길이 :
char(n) : n byte, 0~255 - 가변 길이 :
varchar(n) : n byte, 0~65535
(Oracle에서는 varchar2(n) 제공됨)
TEXT : 길이 제한이 없음 - enum('값', '값2') : 지정된 값만 허용
- BLOB (Binary Large Object) : 이미지, 소리 등 바이너리 데이터 저장용
(3) 날짜형
- DATE : 0000-00-00, curdate() 이용해서 insert 하기
- TIME : 00:00:00, curtime() 이용해서 insert 하기
- DATETIME : 0000-00-00 00:00:00, now() 이용해서 insert 하기
- YEAR : 0000
3-1. CREATE
1) 테이블 생성
create table [if not exists] 테이블명
(컬럼명1 데이터타입,
컬럼명2 데이터타입,
...
);if not exists : Oracle에서는 지원 X
insert 시 컬럼명을 지정하지 않으면 null값이 저장된다
create table [if not exists] 테이블명
(컬럼명1 데이터타입,
컬럼명2 데이터타입 defalut 기본값,
...
);insert 시 컬럼명을 지정하지 않으면 기본값이 저장된다
default 키워드 이용하여 null 값 저장 방지
2) 테이블 생성 + 제약 조건 설정
(1) 컬럼 레벨
: NOT NULL은 꼭 컬럼 레벨에서만 지정 가능
create table IF NOT EXISTS 테이블명
( 컬럼명1 데이터타입 제약조건설정1,
컬럼명2 데이터타입 제약조건설정2,
컬럼명3 데이터타입 DEFAULT 기본값,
..
); CREATE TABLE student
(no INT PRIMARY KEY,
name VARCHAR(10) UNIQUE,
address VARCHAR(10) NOT NULL,
age INT CHECK (age IN (10, 20, 30)));(2) 테이블 레벨
: NOT NULL 지정 불가능
create table IF NOT EXISTS 테이블명
( 컬럼명1 데이터타입,
컬럼명2 데이터타입 DEFAULT 기본값,
컬럼명n 데이터타입,
제약조건설정1,
제약조건설정2
);CREATE TABLE student2
(no INT,
name VARCHAR(10),
address VARCHAR(10) NOT NULL,
age INT ,
CONSTRAINT PRIMARY KEY(NO),
CONSTRAINT UNIQUE(NAME),
CONSTRAINT CHECK (age IN(10, 20, 30)));- foreign key 설정
create table slave1
(n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num)); 3) 테이블 생성 후 -> 제약 조건 설정 추가
(1) 테이블 생성
create table IF NOT EXISTS 테이블명
( 컬럼명1 데이터타입,
컬럼명2 데이터타입 DEFAULT 기본값,
..
);(2) 제약 조건 설정
Alter table add 제약조건설정;-> 설정 문법은 3-2. alter 참고하기
cf)
- 설정된 제약 조건 보기
select *
from information_schema.table_constraints
where table_name = 'slave1';- 테이블 컬럼 정보 보기
DESC slave1;4) foreign key 설정
(1) foreign key 설정
create table slave1
(n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num)); (2) on delete cascade
create table slave1
(n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) ON DELETE CASCADE); : 참조하고 있는 master 테이블의 값을 삭제했을 때 slave 테이블의 값도 같이 삭제된다
(3) on delete set null
drop table slave1;
create table slave1
(n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) ON DELETE SET NULL); : 참조하고 있는 master 테이블의 값을 삭제했을 때 slave 테이블의 외래키 값을 null로 설정한다
3-2. ALTER
1) 용도
객체(테이블)의 구조를 변경하는 명령어. 객체에 따라 세부적인 명령어나 문법이 상이
2) 문법
(1) add
ALTER TABLE scott_t
ADD (address VARCHAR(30));
ALTER TABLE scott_t
ADD (email VARCHAR(30), phone varchar(10));(2) modify
ALTER TABLE scott_t
MODIFY address INT;
ALTER TABLE scott_t
MODIFY address VARCHAR(5);(3) drop
ALTER TABLE scott_t
DROP email ;(4) rename
ALTER TABLE scott_t
RENAME COLUMN address TO addr;
ALTER TABLE scott_t
RENAME COLUMN Name TO name;3) 제약 조건 변경 / 추가
(1) add
ALTER TABLE scott_t2
ADD CONSTRAINT PRIMARY KEY(num);
ALTER TABLE scott_t2
ADD CONSTRAINT UNIQUE(NAME);
ALTER TABLE scott_t2
ADD CONSTRAINT CHECK(age>20);
ALTER TABLE scott_t2_child
ADD CONSTRAINT FOREIGN KEY(num)
REFERENCES scott_t2(num);(2) modify
ALTER TABLE scott_t2
MODIFY address VARCHAR(20) NOT NULL;3-3. DROP
1) 용도
객체(테이블)를 삭제한다
2) 문법
drop table IF EXISTS 테이블명, 테이블명;-> 테이블 여러 개 한 번에 삭제 가능
3) 주의할 점
foreign key가 참조하는 master 테이블은 삭제 불가능
예> drop table master1; // 에러
해결 :
mysql: CASCADE 지원 안 됨
따라서 slave 먼저 삭제 후 master 삭제
oracle: drop table master1 CASCADE;
==> slave에 있는 fk 제약조건이 삭제됨
3-4. TRUNCATE
1) 용도
테이블의 데이터를 제거한다. DELETE와 다르게 DDL이기 때문에 ROLLBACK이 되지 않으므로 사용에 주의
2) 문법
truncate table 테이블명;
truncate table copy_emp;3) 동작
모든 레코드가 삭제됨
delete from 테이블명;과 유사함
cf) delete vs truncate
- delete:
DML
rollback 가능 (복구 가능)
백업본 만들고 저장영역 유지 - truncate:
DDL
복구 안됨 ( DDL은 auto commit )
백업본 필요없음. 최소한의 저장영역만 유지
delete 보다 삭제성능이 우수함
