2021-04-08 RDBMS DDL, DCL
Summary
-
DDL (Data Definition Language) (CREATE TABLE, ALTER TABLE, DROP, TRUNCATE)
-
Group By, Having
-
DCL (Data Control Language) (CREATE USER, GRANT PRIVILEGES, REVOKE)
-
launchpad.net 의 sample database를 이용한 실습.
-
w3schools.com 의 'Learn SQL'을 통한 실습.
DDL (Data Definition Language)
CREATE DATABASE <db명> #database 생성.
DROP DATABASE <db명> #database 삭제.
CREATE TABLE <table명> #table 생성.
DROP TABLE <table명> #table 삭제.
TRUNCATE TABLE <table명> #table 은 남겨두고 records 전부 삭제.
ALTER TABLE <table 명>
MODIFY COLUMN <column명> <datatype> #column의 datatype 수정.- CREATE, DROP DATABASE
- CREATE TABLE & INSERT INTO (table 생성, record 삽입)
- SHOW CREATE TABLE (table 생성 정보 출력)
- TRUNCATE TABLE (table record 삭제)
- DROP TABLE (table 삭제)
CONSTRAINT
-
NULL(default) / NOT NULL
-
UNIQUE
-
PK(Primary Key)
-
FK(Foriegn Key)
-
CHECK(Mysql does not support)
-
DEFUALT
-
NOT NULL 로 지정한 column에 null값의 record를 넣으려고 시도할 경우 error.
- UNIQUE 로 지정한 column (ID) 에 대해 이미 존재하는 ID값을 넣을 경우 error.
- UNIQUE와 NOT NULL 을 동시에 만족하는 column에 한해서, Primary Key 로 지정할 수 있음.
- 다른 table의 PK로 지정되어있는 column에 한해서, create 하는 table의 FK로 지정가능.
- default를 지정하면 record 삽입 시, value를 할당하지 않아도 기본값(default) 가 들어감.
- Auto Increment 로 지정시 column값을 자동으로 1씩 증가하며 reocrd 삽입 시 할당.
- ALTER TABLE 로 column 수정, 삭제, 추가 가능.
DCL (Data Control Language)
- 사용자 생성
CREATE USER 'username'@'localhost' IDENTIFIED BY 'mypasswd';- 사용자 삭제
DROP USER 'username'@'localhost';- 사용자 조회
use mysql;
SELECT user, host FROM user;
- 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
# 모든 db의 모든 table 접근허용 (사실상 SuperUser가 됨.)
GRANT ALL PRIVILEGES ON sample.* TO 'username'@'localhost';
# sample db의 모든 table 에 대해 권한 부여.
GRANT SELECT, INSERT, UPDATE ON sample.* TO 'username'@'localhost';
# sample db의 모든 table 에 대해 'C,R,U' 권한을 부여.- 권한 반영 (권한 설정 후 반드시 해줘야함.)
FLUSH PRIVILEGES;- 권한 확인
SHOW GRANTS FOR 'username'@'localhost';- 권한 삭제
REVOKE ALL ON *.* FROM 'username'@'localhost';
DML (Data Manipulation Language)
GROUP BY
- 집계함수로, 개별 query가 아닌 group별 통계(count, avg, min, max, ..)등을 확인할 때 사용.**
HAVING
- GROUP BY 결과에 filter 추가.
Transaction
Transaction의 ACID특성.
Atomicity(원자성), Consistency(일관성), Isolation(격리성), Durability(내구성) 을 만족하는가.
예로 은행거래 (ATM 계좌이체) 를 생각해보자. 이체도중 DB 시스템이 다운되어 내 계좌에서는 돈이 빠져나갔는데 상대방 계좌로 돈이 들어가지 않았다면? -- 금융 사고. 이러한 일이 있어서는 안됨.
--> All or Nothing (Commit or Rollback 으로 구현)
아예 실행 되던지, 아니면 아예 안되던지. 애매한 상황은 없어야 한다는게 Transaction이다.
여러 줄의 SQL Query를 실행할 때 장애가 발상했을 경우, Query 전체를 취소하거나 확정.
MYSQL에서는 이러한 Transaction의 지원 가능 여부가 storage engiine에 따라 다름. Index 지원, Lock, Join 등의 지원여부도 달라짐.
Storage engine
- MYISAM - 가장 오래된 engine으로 Transaction을 지원하지 않음. Legacy 및 여러 호환성 때문에 남아있으나, 이제는 거의 사용하지 않는 추세.
- InnoDB (Default) - Transaction을 지원하고 외래키를 지원한다. Record Lock, MVCC를 지원하고 MYISAM에서 지원했던 풀텍스트 인덱스와 R-Tree 인덱스를 지원한다. 대부분 요새는 InnoDB사용.
- Memory - DB전체 내용을 RAM에 올려서 빠르게 동작하는 Engine. 그러나 아직 안정적이지 않고 임시 Table위주로 사용한다. 읽기는 우수하나 쓰기성능 떨어짐.
Storage Engine 확인
mysql database 의 user table은 MyISAM engine으로 만들어졌다.
employees db의 employees table 은 InnoDB로 만들어졌다.
따라서 두 Table간 Transaction 지원 여부가 다르므로 (InnoDB만 transaction을 지원.) Transaction 실습은 employees DB의 employees table에서 진행한다.
이렇게 set autocommit=0; 으로 autocommit 기능을 OFF 해 줘야 Transaction 실습이 가능.(실습 진행 후에는 다시 set autocommit=1; 로 기능을 켜주자.)
rollback & commit
이렇게 rollback 을 이용하면 commit 없이 생성된 것들은 전부 취소된다. (Update, delete 의 경우도 마찬가지.)
이런식으로 어떠한 state를 저장하는 savepoint를 이용하더라도 그 지점으로 rollback이 가능하다.
(savepoint 는 commit 이 진행되면 사라짐)
DDL문은 Transaction 지원이 안됨. rollback 불가. --> Table CRUD 에는 신중해야 함.
! 생각해볼 내용.
-
장애 원인?
- DBMS 연결이 끊어짐
- DBMS 터짐.
- server down(전원 나감)
- 해킹
- network 장애
-
rollback 처리 - HW가 살아있고, DBMS도 살아있어야 함.(SW상의 장애만 롤백 가능.)
-
Transaction 의 지원이 되려면 DBMS 외부에서 장애 monitoring을 통해 rollback 을 처리해야 한다. --> WAS(Web Application Server), middle ware 를 이용해야 함.
Lock
여러 개의 Transaction/Thread 가 동시에 공유자원에 접근할 때. (동시에 읽기 쓰기를 진행하는 경우!)
그것을 Lock 으로 통제한다.
- Global Lock
- 특정 명령이 수행되면 다른 모든 명령어의 수행이 정지됨.
- Table Lock
- 특정 transaction이 해당 table을 수정하고 있으면 다른 transaction은 해당 table을 접근하지 못함.
- 다른 table은 접근 허용.
- Record Lock(row-level locking)
- 특정 transaction이 해당 record를 수정하고 있으면 다른 transcation은 해당 record를 접근하지 못함.
- 동일 table의 다른 record는 접근 허용.
