TCL

Transaction Control Language
트랙잭션 : 데이터베이스의 논리적 연산단위
트랜잭션의 대상 : DML(UPDATE, INSERT, DELETE)

트랜잭션의 특성

• 원자성(atomicity) : 정의된 연산드을 모두 실행되던지 아니면 전혀 실행되지 않은 상태로 남아있어야 함.(all or nothing)
• 지속성(durability) : 트랜잭션이 성공적으로 수행되면 갱신한 내용은 영구적으로 저장
• 일관성(cpmsostemcu) : 실행 전 DB내용에 문제가 없다면, 실행 이후에도 문제가 없어야 함
• 고립성(isolation) : 트랜잭션 실행중 다른 트랜잭션에 영향을 받아서는 안됨

autocommit 해제
리눅스 mariadb에서는 autocommit 기본적으로 설정되어 있음. autocommit을 한다는 것은 어떠한 실수도 용납하지 않겠다는 뜻..
mariadb에서 set autocommit=0로 설정할 수 있지만, 영구적으로 반영되는 것은 아니라 리눅스 시스템 상에서 설정

[root@server ~]# vi /etc/my.cnf
[mysqld]
...
autocommit=0 # 추가

COMMIT

입력한 자료나 수정한 자료에 대해 또는 삭제한 자료에 대해 문제가 없다고 판단할 경우 COMMIT으로 트랜잭션 완료

MariaDB [test]> INSERT INTO users (name,uid,locate,email,age) VALUES ('KIM',0,'SEOUL','K@N',25);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.00 sec)

COMMIT 이후 데이터 상태

• 변경사항 반영
• 이전 데이터 영구 삭제
• 모든 사용자 결과 확인 가능
• 관련된 행에 대한 LOCKINIG이 풀리고, 다른 사용자들이 행 조작 가능

ROLLBACK

COMMIT 이전에 변경 사항을 취소

MariaDB [test]> SELECT * FROM users;
+------+-----+--------+--------+------+
| name | uid | locate | email  | age  |
+------+-----+--------+--------+------+
| KIM  |   0 | SEOUL  | K@N    |   25 |
| KIM  |   1 | BUSAN  | KIM@G  | NULL |
| lee  |   3 | seoul  | 222@22 |   20 |
+------+-----+--------+--------+------+
3 rows in set (0.00 sec)

MariaDB [test]> DELETE FROM users WHERE age=20;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SELECT * FROM users;
+------+-----+--------+-------+------+
| name | uid | locate | email | age  |
+------+-----+--------+-------+------+
| KIM  |   0 | SEOUL  | K@N   |   25 |
| KIM  |   1 | BUSAN  | KIM@G | NULL |
+------+-----+--------+-------+------+
2 rows in set (0.00 sec)

MariaDB [test]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM users;
+------+-----+--------+--------+------+
| name | uid | locate | email  | age  |
+------+-----+--------+--------+------+
| KIM  |   0 | SEOUL  | K@N    |   25 |
| KIM  |   1 | BUSAN  | KIM@G  | NULL |
| lee  |   3 | seoul  | 222@22 |   20 |
+------+-----+--------+--------+------+
3 rows in set (0.00 sec)

SAVEPOINT

SAVEPOINT 정의 시 ROLLBACK할 때 현 시점에서 SAVEPOINT까지 일부 롤백 가능

MariaDB [test]> SAVEPOINT SVPT1;
Query OK, 0 rows affected (0.00 sec)
# 또는 

MariaDB [test]> DELETE FROM users WHERE age=20;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> ROLLBACK TO SVPT1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM users;
+------+-----+--------+--------+------+
| name | uid | locate | email  | age  |
+------+-----+--------+--------+------+
| KIM  |   0 | SEOUL  | K@N    |   25 |
| KIM  |   1 | BUSAN  | KIM@G  | NULL |
| lee  |   3 | seoul  | 222@22 |   20 |
+------+-----+--------+--------+------+
3 rows in set (0.00 sec)

DDL

Data Definition Language

CREATE TABLE

MariaDB [test]> CREATE TALBE 테이블명 (
    -> 컬럼명1 DATATYPE [DEFAULT 형식],
    -> 컬럼명2 DATATYPE [DEFAULT 형식]
);

DECRIBE 테이블명

생성된 테이블 구조 확인

MariaDB [test]> DESCRIBE users;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| uid    | int(10)     | NO   | PRI | NULL    |       |
| locate | varchar(10) | YES  |     | NULL    |       |
| email  | varchar(20) | YES  |     | NULL    |       |
| age    | int(5)      | YES  |     | NULL    |       |
| KKK    | int(11)     | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

제약조건

• PRIMARY KEY : 행 데이터를 고유하게 식별하기 위한 기본키
  PRIMARY KEY = + UNIQUE + NOT NULL
• UNIQUE KEY : 행 데이터를 고유하게 식별하기 위한 고유키를 정의. NOT NULL의 대상은 아님
• NOT NULL : NULL 값의 입력 금지
• CHECK : 입력할 수 있는 값의 범위 제한
• FOREIGN KEY : 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 생성

ALTER TABLE

ADD COLUMN

MariaDB [test]> ALTER TABLE 테이블명
	-> ADD (추가할 칼럼명 데이터유형);
);

DROP COLUMN

MariaDB [test]> ALTER TABLE 테이블명
	-> DROP COLUMN 삭제할 칼럼명;

MODIFY COLUMN

컬럼 수정

MariaDB [test]>	ALTER TABLE 테이블명
	-> MODIFY (칼럼명1 데이터유형 [DEFAULT 식] [NOT NULL],
    -> ...
    -> );

RENAME COLUMN

MariaDB [test]>	ALTER TABLE 테이블명
	-> RENAME COLUMN 변경해야할 테이블명 TO 새로운 컬럼명;

DROP TABLE

테이블 삭제

MariaDB [test]> DROP TABLE 테이블명 [CASCADE CONSTRAINT];

CASCADE CONSTRAINT : 해당 테이블과 관련잇던 참조된 제약조건에 대해서도 삭제

DCL

Data Control Language
유저를 생성하고 권한을 제어할 수 있는 명령어

CREATE USER

데이터베이스 사용자 생성

MariaDB [test]> CREATE USER 사용자이름@호스트네임
	-> IDENTIFIED BY 패스워드;

DROP USER

사용자 제거

MariaDB [test]> DROP USER 사용자이름@호스트네임;

GRANT

사용자에게 명령어에 대한 권한 부여

MariaDB [test]> GRANT 부여할명령어 ON 테이블 TO 사용자명;

REVOKE

부여한 권한 취소

MariaDB [test]> REVOKE 명령어 FROM 사용자명;

데이터베이스 백업

물리적 백업

• 저장된 데이터와 설정파일 등을 직접 백업하는 방식
• 속도가 빠름
• 이식성이 떨어짐

논리적 백업

• 데이터를 논리적인 파일로 변환하고 백업하는 방식
• 속도가 느리며 설정까지는 백업이 안됨
• 이식성이 높음

백업

[root@server ~]# mysqldump –u root –p 데이터베이스명 (테이블명) > 백업파일명

불러오기

[root@server ~]# mysql –u root –p < 백업파일명