1. DML ( Data Manipulation Lanuage : 데이터 조작어 )
1) 종류
- insert 문
테이블의 레코드 생성
upsert ( update + insert )
==> 값이 존재하면 update 되고 없으면 insert.
- delete 문
테이블의 레코드 삭제
- update 문
테이블의 컬럼 수정
2) 특징
- 트랜잭션 ( transaction ) 관련됨.
2. 트랜잭션 ( transaction )
1) 개념
여러개의 DML 작업들을 하나의 작업으로 묶어서 처리하는 개념으로 묶음을 의미한다.
묶음의 갯수 ( 1개 이상 )
2) 명령어
- commit
트랜잭션이 모두 실행했을때 성공해서 실제로 DB 에 반영하는 명령어.
즉, 모든 DML 은 실행되었을때 실제 DB 에 반영안됨. ( * )
- rollback
3) 트랜잭션 용도
ex) 계좌이체 ( update + insert )
A B
1000
A -> B : 500
# 성공시 - commit 으로 실제 DB에 반영
A B
500 500
# 실패시 - rollback 으로 모두 취소
A B
1000
4) 트랜잭션 시작 ~ 트랜잭션 종료
트랜잭션시작 : DML 사용시 자동으로 시작됨.
ex)
<--- 트랜잭션 시작
INSERT
COMMIT; <--- 트랜잭션 종료( 실제 DB 반영 ) | ROLLBACK; <--- 취소
트랜잭션종료 : 명시적으로 종료해야 된다.
COMMIT / ROLLBACK 명령어 이용
COMMIT 은 실제 DB 에 반영하는 방식으로 종료
ROLLBACK 은 취소하는 방식으로 종료
5) DBMS 종류에 따라서 AUTO COMMIT 될 수 있다.
==> DML 하면 자동으로 COMMIT 되는 것을 의미한다.
==> Oracle : 명시적으로 COMMIT 해야 됨.
3. INSERT 문
1) 용도
- 새로운 레코드 생성
- 단일행 생성( * ) 또는 멀티행 생성 모두 가능
2) 문법
가. 컬럼명 지정한 경우 ( 권장 )
INSERT INTO 테이블명 ( 컬럼명, 컬럼명1 )
VALUES ( 값, 값1 ); # 문자와 날짜는 반드시 ' ' 로 지정해야 된다.
=> 선택적으로 값을 저장이 가능.
나머지는 null 로 저장됨.
=> 가독성이 좋다.
나. 컬럼명 생략한 경우 ( 권장안함 )
INSERT INTO 테이블명
VALUES ( 값, 값1, ... , 값6 );
==> 선택적으로 값 저장이 불가.
반드시 테이블의 모든 컬럼에 값을 저장해야 된다.
==> 지정된 값 순서대로 실제 테이블의 컬럼에 맵핑된다.
==> 가독성이 떨어진다.
3) 주의할점
- pk 컬럼에는 중복값이 저장안되고 null 값 저장 불가
4) 문법 2 - 다중행 생성하는 방법
# 실습전에 새로운 테이블 생성하자.
- CTAS 방법 : 제약조건까지는 복사가 되지않아서 중복으로 실행해도 오류가 발생하지 않는다.
CREATE TABLE 테이블명
AS
SELECT 문;
# 구조만 복사해서 새로운 테이블 생성
CREATE TABLE copy_emp
AS
SELECT empno, ename, sal FROM emp
WHERE 1=2;
가. subquery 이용
문법:
INSERT INTO 테이블명 ( 컬럼명, 컬럼명1 )
SELECT 컬럼명, 컬럼명2
FROM 테이블명;
나. value_list 이용 ( * )
문법:
INSERT INTO 테이블명 ( 컬럼명, 컬럼명1 )
VALUES ( 값, 값1 ) , ( 값, 값1 ) , ( 값, 값1 )
4. update 문
1) 용도
- 컬럼 수정.
2) 문법
가. 조건없는 update
==> 모든 레코드 대상.
update 테이블
set 컬럼명1=값 , 컬럼명2=값 ;
commit;
나. 조건있는 update (********)
==> 조건에 일치하는 레코드 대상.
update 테이블
set 컬럼명1=값 , 컬럼명2=값
where 조건식;
commit;
3) subquery 이용한 update
-- MySQL 에러 ( Oracle 가능 )
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/update.html 참조
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;
5. delete 문
1) 용도
- 레코드 삭제
2) 문법
가. 조건식이 없는 형태
==> 모든 레코드 대상임. 따라서 모든 레코드가 삭제됨.
DELETE FROM 테이블명;
나. 조건식 있는 형태
==> 조건에 일치하는 레코드만 삭제.
DELETE FROM 테이블명
WHERE 조건식;
3) subquery는 지원 안됨.
==> Oracle 은 지원됨.
4) limit 지원.
DELETE FROM 테이블명
WHERE 조건식
ORDER BY 표현식
LIMIT n;
####################################################################################
6. DDL ( Data Definition Language : 데이터 정의어 )
1) 용도
- 객체(테이블, 인덱스, 프로시저, 사용자, ... ) 생성, 수정, 삭제할 때 사용하는 SQL 문
2) 테이블 생성1
문법1:
CREATE TABLE 테이블명
( 컬럼명1 데이터타입,
컬럼명2 데이터타입,
....
);
==> 컬럼에 중복데이터 저장이 가능해짐.
==> 잘못된 데이터가 저장될 수 있다.
==> IF NOT EXISTS ( oracle 지원 안됨 )
==> INSERT 할때 컬럼명을 지정하지 않으면 null 값이 저장된다.
3) 데이터타입
가. 수치형
- 정수: INTEGER( INT: 4byte ), SMALLINT( 2byte ), MEDIUMINT( 3byte ), BIGINT( 8byte )
- 실수:
-> 부동소수점: FLOAT, DOUBLE
-> 고정소수점: DECIMAL, NUMERIC
나. 문자형
- 고정길이 : char(n) : n byte ( n byte 크기의 영문자 및 한글 저장가능 ), 0 ~255
ex) 성별, 학년
char(2): 영문자 2글자, 한글 2글자 저장 가능
- 가변길이 : varchar(n) : n byte, 0 ~ 65535
( Oracle 은 varchar2(n) 제공됨 )
TEXT : 길이 제한 없음.
- enum ( '값', '값2' ) : 지정된 값만 허용.
- BLOB ( Binary Large Object ) : 이미지, 소리 등 바이너리 데이터 저장용.
다. 날짜형
- DATE : 0000-00-00, CURDATE( ) 이용해서 INSERT
- TIME : 00:00:00, CURTIME( ) 이용해서 INSERT
- DATETIME : 0000-00-00 00:00:00, NOW( ) 이용해서 INSERT
- YEAR : 0000
2) 테이블 생성 2
문법1:
CREATE TABLE 테이블명
( 컬럼명1 데이터타입,
컬럼명2 데이터타입 DEFAULT 기본값,
....
);
==> 컬럼에 중복데이터 저장이 가능해짐.
==> 잘못된 데이터가 저장될 수 있다.
==> IF NOT EXISTS ( oracle 지원 안됨 )
==> INSERT 할때 컬럼명을 지정하지 않으면 null 값이 아닌 기본값으로 저장된다.
ex)
INSERT INTO 테이블명 ( 컬럼명1 )
VALUES ( 값1 );
컬럼명2 에는 null 값이 아닌 기본값으로 저장된다.
용도:
- 정해져있는 값 ( 고정값 ) (ex) 성별값, 게시판의 조회수
- 날짜
#####################################################################
7. 제약조건 설정하기
1) 테이블을 생성하면서 제약조건을 설정
가. 컬럼 레벨
CREATE TABLE IF NOT EXISTS 테이블명
( 컬럼명1 데이터타입 제약조건설정,
컬럼명2 데이터타입 제약조건설정,
컬럼명3 데이터타입 DEFAULT 기본값,
....
);
==> 5개의 제약조건 모두 사용이 가능하다.
나. 테이블 레벨
CREATE TABLE IF NOT EXISTS 테이블명
( 컬럼명1 데이터타입,
컬럼명2 데이터타입 DEFAULT 기본값,
컬럼명n 데이터타입,
제약조건설정1,
제약조건설정2
);
==> 4개의 제약조건 모두 사용이 가능하다.
not null 제외( not null 은 컬럼레벨만 지원 )
2) 테이블을 먼저 생성후에 이후에 제약조건을 설정 ( * )
가. 다음과 깉이 컬럼명과 데이터타입
CREATE TABLE IF NOT EXISTS 테이블명
( 컬럼명1 데이터타입,
컬럼명2 데이터타입 DEFAULT 기본값,
....
);
나. 제약조건을 나중에 설정한다.
ALTER TABLE ADD 제약조건설정문법 ( 테이블 레벨 과 문법이 똑같다. )
8. primary, unique, not null, check 4가지 제약조건 설정방법
1) 컬럼 레벨
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) 테이블 레벨
CREATE TABLE student2
( no INT,
name VARCHAR(10),
address VARCHAR(10) NOT NULL, -- not null 반드시 컬럼레벨만 지원
age INT ,
CONSTRAINT PRIMARY KEY(NO),
CONSTRAINT UNIQUE(NAME),
CONSTRAINT CHECK( age IN ( 10,20,30 )));
9. foreign key
-- MASTER TABLE
CREATE TABLE master1
(num INT PRIMARY KEY,
name VARCHAR(10) NOT NULL);
INSERT INTO master1 ( num, name ) VALUES ( 1, 'aa');
INSERT INTO master1 ( num, name ) VALUES ( 2, 'bb');
INSERT INTO master1 ( num, name ) VALUES ( 3, 'cc');
INSERT INTO master1 ( num, name ) VALUES ( 4, 'dd');
COMMIT;
1) 컬럼 레벨 ( 일단 KIP )
CREATE TABLE slave1
(n INT PRIMARY KEY,
num INT REFERENCES master1(num) );
==> FK 제약조건이 생성이 안됨.
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/create-table.html
2) 테이블 레벨
CREATE TABLE slave1
(n INT PRIMARY KEY,
num INT,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) );
INSERT INTO slave1 (n, num) VALUES ( 10, 1 );
INSERT INTO slave1 (n, num) VALUES ( 20, 2 );
INSERT INTO slave1 (n, num) VALUES ( 30, 5 ); -- master 에 5가 없어서 ERROR
INSERT INTO slave1 (n, num) VALUES ( 40, null );
select *
from information_schema.table_constraints
where table_name = 'slave1';
==> MySQL 제약조건 추가시 not null 은 컬럼레벨로 하고
나머지는 테이블 레벨로 설정하자.
10. master 테이블의 레코드 삭제
# 삭제안됨. fk 가 참조하기 때문이다.
DELETE FROM master1
WHERE num = 1;
# 해결방법 2가지
1) master 의 slave 레코드를 같이 삭제
- ON DELETE CASCADE
# 삭제방법1
drop table slave1;
create table slave1
( n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) ON DELETE CASCADE );
insert into slave1 (n, num) values ( 10, 1 );
insert into slave1 (n, num) values ( 20, 2 );
-- insert into slave1 (n, num) values ( 30, 5 ); -- 에러발생
insert into slave1 (n, num) values ( 40, null );
COMMIT;
-- MASTER 의 레코드 삭제
DELETE FROM master1
WHERE num = 1; -- 연쇄적으로 삭제 : ON DELETE CASCADE
2) slave 의 fk 값을 null 로 설정
- ON DELETE SET NULL
# 삭제방법2
drop table slave1;
create table slave1
( n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) ON DELETE SET NULL );
-- insert into slave1 (n, num) values ( 10, 1 );
insert into slave1 (n, num) values ( 20, 2 );
-- insert into slave1 (n, num) values ( 30, 5 ); -- 에러발생
insert into slave1 (n, num) values ( 40, null );
COMMIT;
-- MASTER 의 레코드 삭제 -> NULL 로 설정
DELETE FROM master1
WHERE num = 2;
11. 테이블 삭제
1) 문법
DROP TABLE IF NOT EXISTS 테이블명, 테이블명;
2) fk 가 참조하는 master 는 삭제 안됨.
ex)
DROP TABLE master1; // 에러
해결:
mysql: CASCADE 지원안됨.
1) slave 먼저 삭제
2) master 삭제
oracle: DROP TABLE master1 CASCADE;
==> slave 에 있는 fk 제약조건이 삭제됨.
12. 테이블 절삭
문법:
TRUNCATE TABLE 테이블명;
동작:
모든 레코드가 삭제됨.
DELETE FROM 테이블명; 유사함.
* DELETE vs TRUNCATE
DELETE: - DML
- ROLLBACK 가능 ( 복구 가능 )
- 백업본 만들고 저장영역 유지
TRUNCATE: - DDL
- 복구안됨
- 백업본 필요없음. 최소한의 저장영역만 유지
- DELETE 보다 삭제성능이 우수하다.
13. 테이블 수정
1) 컬럼 추가, 삭제
2) 컬럼 데이터 크기 변경, 타입 변경
3) 컬럼이름 변경
4) 제약조건 설정 ( * )
use testdb;
-- DML
-- AUTO COMMIT 확인
SHOW VARIABLES LIKE 'autocommit%';
-- SHOW VARIABLES LIKE 'autocommit'; 도 나오는데 'autocommit%' 를 사용하는 이유?
-- autocommit OR autocommit% 이라는 의미이다.
-- AUTO COMMIT 비활성화
SET autocommit = FALSE;
SHOW VARIABLES LIKE 'autocommit%';
# INSERT
-- - 테이블에 데이터를 입력하기 위한 데이터 조작어 이다.
-- - 한번에 하나의 행을 테이블에 입력하는 방법과 value_list 및 서브쿼리를 이용하여
-- 한번에 여러 행을 동시에 입력하는 방법이 있다.
-- - INTO 절에 명시한 컬럼에 VALUES 절에서 지정한 컬럼값을 입력한다.
-- ( 일대일 대응)
-- - INTO 절에 컬럼명을 지정하지 않으면 테이블 생성시 정의한 컬럼순서와 동일한
-- 순서로 입력된다.
-- - 입력되는 데이터의 타입은 컬럼의 데이터 타입과 같아야 되며 입력되는 데이터의
-- 크기는 컬럼의 크기보다 작아야 된다.
DESC dept;
--
-- 컬럼명 지정한 경우
INSERT INTO dept (deptno, dname, loc )
VALUES (90,'인사과','서울');
COMMIT; -- DB 에 반영하는 식으로 종료
SELECT * FROM dept;
--
--
INSERT INTO dept (deptno, dname ) # loc 컬럼에 null 저장
VALUES (91,'인사과');
COMMIT;
SELECT * FROM dept;
--
--
INSERT INTO dept (loc, dname, deptno )
VALUES ('서울','인사과', 80);
COMMIT;
SELECT * FROM dept;
--
--
-- 컬럼명 지정하지 않은 경우
INSERT INTO dept
VALUES (70,'인사과','서울');
COMMIT;
SELECT * FROM dept;
-- INSERT INTO dept
-- VALUES (70,'인사과');
-- 에러발생 컬럼명을 지정하지 않은 상태에서 컬럼에 해당하는 값을
-- 전부다 넣지않으면 에러발생
--
-- 다중행 생성
CREATE TABLE copy_emp
AS
SELECT empno, ename, sal FROM emp
WHERE 1=2; -- 껍데기만 생성
CREATE TABLE copy_emp2
AS
SELECT empno, ename, sal FROM emp;
-- WHERE 절을 포함하지 않고 만들면 레코드도 포함해서 생성
# 서브쿼리 이용한 다중 데이터 저장 1
-- 제약조건까지는 복사가 되지않아서 중복으로 실행해도 오류가 발생하지 않는다.
INSERT INTO copy_emp (empno, ename, sal)
SELECT empno, ename, sal
FROM emp;
ROLLBACK; -- copy_emp ROLLBACK으로 데이터삽입 취소.
# value_list 이용한 다중 데이터 저장 2 ( * )
INSERT INTO copy_emp (empno, ename, sal)
VALUES (71, '홍길동',800), (72, '이순신',900), (73, '유관순',950);
COMMIT;
# UPDATE
-- UPDATE 용도
-- - 테이블에 저장된 행들을 변경하는 문장이다.
-- - 한 번에 여러 개의 행들을 변경할 수 있다.
--
UPDATE dept
SET dname ='경리과', loc='부산'
WHERE deptno = 90;
SELECT * FROM dept;
--
-- ▪ 서브쿼리를 이용한 복수 컬럼 변경
-- 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;
-- https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/update.html 참고
-- 오류 해결 ↓
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) update 문
UPDATE dept
SET dname='경리과', loc='부산'
WHERE deptno=90;
commit;
UPDATE dept
SET dname='경리과', loc='부산';
rollback;
select *
from dept;
-- subquery 이용한 update
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;
# DELETE 용도
-- - 테이블에 저장된 행들을 삭제한다.
-- - 한 번에 여러 개의 행들을 삭제할 수 있다.
-- - MySQL에서는 같은 table을 사용하는 서브쿼리는 지원이 안된다.
-- - limit 이용한 삭제 가능.
DELETE FROM dept
WHERE deptno = 90;
COMMIT;
SELECT * FROM dept;
--
DELETE FROM dept; -- 에러발생. 이유는 emp 에서 fk 로 참조하고 있기 때문에
--
SELECT * FROM emp;
DELETE FROM emp; -- 조건이 없으면 다 삭제된다.
DELETE FROM emp
WHERE deptno = 90; -- 조건이 있다면 해당되는 조건만 삭제
ROLLBACK;
--
-- limit 실습 -----------
SELECT *
FROM copy_emp2
ORDER BY sal DESC;
--
DELETE FROM copy_emp2
WHERE sal = 3000
ORDER BY empno DESC
LIMIT 1;
SELECT *
FROM copy_emp2
ORDER BY sal DESC;
-- ------------------------
# MERGE 기능
-- - 대상 테이블에 해당 행이 이미 존재하면 UPDATE 가 실행되고 존재하지
-- 않으면 INSERT가 실행된다.
-- UPSERT 문 -----
SELECT * FROM dept;
--
INSERT INTO dept (deptno, dname, loc )
VALUES (90,'인사과','서울');
INSERT INTO dept (deptno, dname, loc )
VALUES (92,'인사과','서울') ON DUPLICATE KEY UPDATE loc='제주';
SELECT * FROM dept;
--
# Transaction 정의
-- -트랜잭션은 데이터베이스의 논리적인 단위이다.
-- -트랜잭션은 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을
-- 가리킨다.
-- -하나의 트랜잭션에는 하나 이상의 SQL문장이 포함되며 분할 할 수 없는 최소의
-- 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소된다.
-- 즉, 트랜잭션은 ‘All or Nothing’이다.
-- -트랜잭션의 대상이 되는 SQL문은 DML문이다.
# COMMIT
-- - 모든 데이터 변경사항을 데이터베이스에 영구히 반영시키는 명령어.
-- - 변경전의 데이터는 모든 잃게 된다.
-- - 모든 사용자들이 트랜잭션 종료 후의 결과를 확인 할 수 있다.
-- - 트랜잭션이 진행 중이었던 행들에 대한 잠금이 모두 해소되며, 다른 사용자에
-- 의해서 변경이 가능해진다.
-- - MySQL은 기본적으로 autocommit 으로 설정되어 있다.
-- set autocommit = true|false 지정 가능
# autocommit 설정값 확인
show variables like 'autocommit%';
#autocommit 설정 또는 해제
SET AUTOCOMMIT = TRUE; -- 설정
SET AUTOCOMMIT = FALSE; -- 해제
-- ROLLBACK
-- - 모든 데이터 변경사항을 취소하는 명령어.
-- - 변경전의 데이터로 복원된다.
-- - 모든 사용자들이 트랜잭션 종료 후의 결과를 확인 할 수 있다.
-- - 트랜잭션이 진행 중이었던 행들에 대한 잠금이 모두 해소되며 다른 사용자에
-- 의해서 변경이 가능해진다.
# 읽기 일관성 ( Read Consistency )
-- - 사용자들에게 가장 최근에 commit 된 데이터를 보여주는 것.
-- - 데이터를 검색하는 사용자들과 변경하는 사용자들 사이에 일관적인 관점을
-- 제공한다.
-- 즉, 다른 사용자들이 변경중인 데이터를 볼 수 없게 한다.
-- - 일관적인 데이터베이스 변경방법을 제공함으로써 동일한 데이터를 동시에
-- 변경할 때 발생할 수 있는 혼란을 방지한다.
####################################################
-- DDL
-- 1) 컬럼명과 데이터타입만 지정한 경우
CREATE TABLE IF NOT EXISTS dept_2
( deptno INT,dname VARCHAR(10),loc VARCHAR(10) );
INSERT INTO dept_2 ( deptno, dname )
VALUES ( 1, '관리' ); -- loc 에 null 값 저장
SELECT * FROM dept_2;
-- ▪ DEFAULT 옵션
-- - 해당 테이블에 행을 입력할 때 해당 컬럼에 값을 지정하지 않은 경우 자동으로
-- 기본값이 입력되어 null 값이 저장되는 것을 방지할 수 있다.
-- 2) DEFAULT 추가
CREATE TABLE IF NOT EXISTS dept_3
( deptno INT,dname VARCHAR(10),loc VARCHAR(10) DEFAULT '서울' );
INSERT INTO dept_3 ( deptno, dname )
VALUES ( 1, '관리' ); -- loc 에 기본값인 서울 저장
SELECT * FROM dept_3;
--
CREATE TABLE IF NOT EXISTS board
( num INT PRIMARY KEY AUTO_INCREMENT,
author VARCHAR(10) NOT NULL,
title VARCHAR(20) NOT NULL,
content VARCHAR(100) NOT NULL,
writeday DATE DEFAULT (CURRENT_DATE),
readcnt INT DEFAULT 0 );
-- DATE DEFAULT (CURRENT_DATE)
INSERT INTO board ( author, title, content )
VALUES ('aa','bb','cc');
SELECT * FROM board;
CREATE TABLE IF NOT EXISTS board2
( num INT PRIMARY KEY AUTO_INCREMENT,
author VARCHAR(10) NOT NULL,
title VARCHAR(20) NOT NULL,
content VARCHAR(100) NOT NULL,
writeday DATETIME DEFAULT NOW(),
readcnt INT DEFAULT 0 );
-- DATETIME DEFAULT NOW()
# 제약조건 ( constraint )
# ▪ 제약조건 종류
# ▪ 제약조건 정의 ( Constraints )
# -부적절한 자료가 입력되는 것을 방지하기 위하여 constraint을 사용한다.
# -제약 조건은 종속성이 존재할 경우 테이블 삭제를 방지 한다.
# -테이블에서 행이 삽입, 갱신, 삭제될 때마다 테이블에서 규칙을 적용한다.
# -제약 조건은 테이블 레벨 및 컬럼 레벨 방법으로 규칙을 적용한다.
# 제약조건 타입 설명
# PRIMARY KEY 테이블의 행(레코드) 식별용. NOT NULL + UNIQUE
# UNIQUE 유일한 값 저장. null 허용.
# NOT NULL 반드시 값을 저장. null 허용 안함.
# CHECK 특정 조건 지정. 예> gender IN (‘M’,’F’)
# FOREIGN KEY 컬럼과 참조된 테이블의 컬럼 사이의 외래키 관계설정
# DESC information_schema.table_constraints; -- 제약조건확인
# 테이블을 생성하면서 제약조건을 설정
-- 컬럼 레벨
CREATE TABLE student
( no INT PRIMARY KEY,
name VARCHAR(10) UNIQUE ,
address VARCHAR(10) NOT NULL, # 컬럼레벨만 가능
age INT CHECK( age IN ( 10,20,30 )));
INSERT INTO student ( no, name, address, age )
VALUES (1, 'aa', 'bb', 10 );
INSERT INTO student ( no, name, address, age )
VALUES (2, 'aa', 'bb', 10 ); -- name 은 unique 라서 똑같으면 error 발생
INSERT INTO student ( no, name, address, age )
VALUES (3, 'aa2', 'bb', 40 ); -- 10,20,30 만 가능한데 40이라서 error 발생
-- 테이블 레벨
CREATE TABLE student2
( no INT,
name VARCHAR(10),
address VARCHAR(10) NOT NULL, -- not null 반드시 컬럼레벨만 지원
age INT ,
CONSTRAINT PRIMARY KEY(NO),
CONSTRAINT UNIQUE(NAME),
CONSTRAINT CHECK( age IN ( 10,20,30 )));
# FOREIGN KEY
# - FOREIGN KEY는 child 테이블에서 정의한다.
# - master 테이블의 PRIMARY KEY, UNIQUE KEY로 정의된 열을 참조 할 수 있으며 참조
# 된 열의 값 및 NULL값만 저장할 수 있다.
# - FOREIGN KEY는 열 또는 열의 집합을 지정할 수 있으며 동일 테이블 또는 다른 테이블
# 간의 관계를 지정할 수 있다.
-- MASTER TABLE
CREATE TABLE master1
(num INT PRIMARY KEY,
name VARCHAR(10) NOT NULL);
INSERT INTO master1 ( num, name ) VALUES ( 1, 'aa');
INSERT INTO master1 ( num, name ) VALUES ( 2, 'bb');
INSERT INTO master1 ( num, name ) VALUES ( 3, 'cc');
INSERT INTO master1 ( num, name ) VALUES ( 4, 'dd');
COMMIT;
SELECT * FROM information_schema.table_constraints WHERE table_name = 'slave1';
-- SLAVE 테이블 작성: 컬럼레벨 ( FK 설정이 안됨. 확인 필요 )
-- CREATE TABLE slave1
-- (n INT PRIMARY KEY,
-- num INT REFERENCES master1(num) );
-- master1의 num 을 참조하겠다.
-- master1의 num 이 PK 이기 때문에 참조가능.
-- master1의 PK 또는 UK 컬럼만 참조 가능하다.
-- INSERT INTO slave1 (n, num)
-- VALUES ( 10, 1 );
-- INSERT INTO slave1 (n, num)
-- VALUES ( 20, 2 );
-- INSERT INTO slave1 (n, num)
-- VALUES ( 30, 5 );
-- INSERT INTO slave1 (n, num)
-- VALUES ( 40, null );
-- slave 테이블 작성: 컬럼 레벨 ( fk 설정이 안됨. 확인 필요 )
drop table slave2;
create table slave2
( n int primary key,
num int REFERENCES master1(num) ); -- master의 pk 또는 uk 컬럼만 참조 가능하다.
-- slave 테이블 작성: 테이블 레벨
drop table slave1;
create table slave1
( n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) );
insert into slave1 (n, num) values ( 10, 1 );
insert into slave1 (n, num) values ( 20, 2 );
insert into slave1 (n, num) values ( 30, 5 ); -- 에러발생
insert into slave1 (n, num) values ( 40, null );
COMMIT;
select *
from information_schema.table_constraints
where table_name = 'slave1';
-- MASTER 의 레코드 삭제
DELETE FROM master1
WHERE num = 1; -- 삭제안됨. fk 가 참조하기 때문이다.
# 삭제방법1
drop table slave1;
create table slave1
( n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) ON DELETE CASCADE );
insert into slave1 (n, num) values ( 10, 1 );
insert into slave1 (n, num) values ( 20, 2 );
-- insert into slave1 (n, num) values ( 30, 5 ); -- 에러발생
insert into slave1 (n, num) values ( 40, null );
COMMIT;
-- MASTER 의 레코드 삭제
DELETE FROM master1
WHERE num = 1; -- 연쇄적으로 삭제 : ON DELETE CASCADE
# 삭제방법2
drop table slave1;
create table slave1
( n int primary key,
num int,
CONSTRAINT FOREIGN KEY(num) REFERENCES master1(num) ON DELETE SET NULL );
-- insert into slave1 (n, num) values ( 10, 1 );
insert into slave1 (n, num) values ( 20, 2 );
-- insert into slave1 (n, num) values ( 30, 5 ); -- 에러발생
insert into slave1 (n, num) values ( 40, null );
COMMIT;
-- MASTER 의 레코드 삭제 -> NULL
DELETE FROM master1
WHERE num = 2;
# 테이블 삭제
# - 데이터베이스에서 해당 테이블을 제거하는 것이다.
# - 테이블에 저장된 모든 데이터와 관련 INDEX및 제약조건이 삭제된다.
# - 만약 foreign key 제약조건이 있으면 테이블 삭제 안됨. CASCADE 지원 안됨.
-- 테이블 삭제
DROP TABLE IF EXISTS copy_emp, copy_emp2;
DROP TABLE master1; -- slave1 에 제약조건이 있기때문에 에러발생
-- 해결방법
-- mysql: CASCADE 지원안됨.
-- 1) slave 먼저 삭제
-- 2) master 삭제
-- oracle: DROP TABLE master1 CASCADE;
-- ==> slave 에 있는 fk 제약조건이 삭제됨.
# TRUNCATE
# 테이블 잘라내기
# -테이블의 모든 행들을 삭제 할 수 있다.
# -TRUNCATE은 저장 공간을 해제하고 DELETE 명령은 해제하지 않는다.
# -ROLLBACK 정보를 발생시키지 않아서 DELETE 보다 수행속도가 빠르다.
# 단, DELETE와 달리 ROLLBACk 은 불가능하다.
CREATE TABLE copy_emp
AS
SELECT * FROM emp;
# 테이블 변경 ( ALTER TABLE 문 )
-- 테이블 수정
-- 1) 컬럼 추가, 삭제
CREATE TABLE scott_t
( num INT,
NAME VARCHAR(10) );
DESC scott_t;
--
-- 추가
ALTER TABLE scott_t
ADD ( address VARCHAR(30));
ALTER TABLE scott_t
ADD ( email VARCHAR(30), phone VARCHAR(10)); -- ADD 는 여러개 가능
DESC scott_t;
--
-- 삭제
ALTER TABLE scott_t
DROP address;
-- ALTER TABLE scott_t
-- DROP (email, phone); -- DROP 은 하나씩만 가능
--
# 기존 컬럼 변경
# - 숫자 및 문자 컬럼의 전체 길이를 증가 또는 축소 시킬 수 있다.
# 단, 모든 행의 컬럼이 NULL 또는 행이 없는 경우
# - 모든 행의 해당 컬럼 값이 NULL인 경우에만 데이터 타입을 변경 할 수 있다.
# - 디폴트 값을 변경하면 변경 이후부터 입력되는 행에 대해서만 적용된다.
-- 2) 컬럼 데이터 크기 변경, 타입 변경
-- 데이터가 없다면 크기,타입 변경가능(하나씩만 가능)
ALTER TABLE scott_t
MODIFY email VARCHAR(35);
ALTER TABLE scott_t
MODIFY email INT;
-- 3) 컬럼이름 변경
ALTER TABLE scott_t
RENAME COLUMN name TO username;
DESC scott_t;
-- 4) 제약조건 설정 ( * )
# 제약조건 추가
# -NOT NULL 제약조건은 ALTER TABLE ~ MODIFY 명령을 사용한다.
# -나머지 제약조건은 ALTER TABLE ~ ADD 명령을 사용한다.
CREATE TABLE scott_t2
( num INT,
NAME VARCHAR(10),
age INT,
address VARCHAR(20) );
-- num 컬럼에 PRIMARY KEY 제약조건 추가
ALTER TABLE scott_t2
ADD CONSTRAINT PRIMARY KEY(num);
-- name 컬럼에 UNIQUE 제약조건 추가
ALTER TABLE scott_t2
ADD CONSTRAINT UNIQUE(NAME);
-- age 컬럼에 CHECK 제약조건 추가
ALTER TABLE scott_t2
ADD CONSTRAINT CHECK(age>20);
-- address 컬럼에 NULL 허용상태를 허용하지 않는 NOT NULL 상태로 변경
ALTER TABLE scott_t2
MODIFY address VARCHAR(20) NOT NULL;
-- slave 테이블 작성
CREATE TABLE IF NOT EXISTS scott_t2_child
( no INT PRIMARY KEY,
num INT );
ALTER TABLE scott_t2_child
ADD CONSTRAINT FOREIGN KEY(num)
REFERENCES scott_t2(num);
'Programming > MySQL' 카테고리의 다른 글
| MySQL 3일차정리 (0) | 2024.04.02 |
|---|---|
| MySQL 3일차 (0) | 2024.04.01 |
| MySQL 2일차 (0) | 2024.03.29 |
| MySQL 1일차 수정 (0) | 2024.03.29 |
| 1일차 수업 (0) | 2024.03.28 |