MySQL 실습(2)

RDBMS DDL, DCL

Posted by : Dohyun Kim at Apr 9, 2021

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는 접근 허용.

<-- Incomment incase you want to use Disqus

-->