[mysql] DML, DDL, DCL

DML : 데이터 조작

- select, update, insert, delete 등

- rollback 가능

DDL : 데이터 정의

- create, drop, alter 등

- ddl은 트랜잭션을 발생시키지 않는다. 따라서 rollback, commit 불가. 즉시 적용

DCL : 데이터 제어

- grant, revoke, deny 등

insert

mysql> create table testtbl1(id int, username char(20),age int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into testtbl1 values(1, '홍길동',25);
Query OK, 1 row affected (0.00 sec)

mysql> insert into testtbl1(id, username) values(2, '설현');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtbl1;
+------+-----------+------+
| id   | username  | age  |
+------+-----------+------+
|    1 | 홍길동    |   25 |
|    2 | 설현      | NULL |
+------+-----------+------+
2 rows in set (0.00 sec)

id가 자동입력되는 테이블 생성

mysql>  CREATE TABLE testtbl2
    ->   (id  int AUTO_INCREMENT PRIMARY KEY,
    ->    userName char(20),
    ->    age int );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO testtbl2 VALUES (NULL, '지민', 25);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testtbl2 VALUES (NULL, '유나', 22);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testtbl2 VALUES (NULL, '유경', 21);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM testtbl2;
+----+----------+------+
| id | userName | age  |
+----+----------+------+
|  1 | 지민     |   25 |
|  2 | 유나     |   22 |
|  3 | 유경     |   21 |
+----+----------+------+
3 rows in set (0.00 sec)

auto_increment의 현재 번호 확인

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

auto_increment를 100에서부터 데이터 insert

mysql> alter table testtbl2 auto_increment=100;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into testtbl2 values(null,'찬미',23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtbl2;
+-----+----------+------+
| id  | username | age  |
+-----+----------+------+
|   1 | 지민     |   25 |
|   2 | 유나     |   22 |
|   3 | 유경     |   21 |
| 100 | 찬미     |   23 |
+-----+----------+------+
4 rows in set (0.00 sec)

auto_increment가 1000부터 3씩 증가도록 테이블 구성

mysql> CREATE TABLE testtbl3
    ->   (id  int AUTO_INCREMENT PRIMARY KEY,
    ->    userName char(20),
    ->    age int );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table testtbl3 auto_increment=1000;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set @@auto_increment_increment=3;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO testtbl3 VALUES (NULL, '나연', 20);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testtbl3 VALUES (NULL, '정연', 18);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testtbl3 VALUES (NULL, '모모', 19);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM testtbl3;
+------+----------+------+
| id   | userName | age  |
+------+----------+------+
| 1000 | 나연     |   20 |
| 1003 | 정연     |   18 |
| 1006 | 모모     |   19 |
+------+----------+------+
3 rows in set (0.00 sec)

testtbl4를 생성하고 employees DB의 employees 테이블의 emp_no, first_name, last_name을 쿼리해서

testtbl4로 insert

mysql> CREATE TABLE testtbl4 (id int, Fname varchar(50), Lname varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO testtbl4
    ->   SELECT emp_no, first_name, last_name
    ->     FROM employees.employees ;
Query OK, 300024 rows affected (36.74 sec)
Records: 300024  Duplicates: 0  Warnings: 0

또는

mysql> CREATE TABLE testtbl4
    ->    (SELECT emp_no, first_name, last_name  FROM employees.employees) ;
Query OK, 300024 rows affected (19.53 sec)
Records: 300024  Duplicates: 0  Warnings: 0

update

update table set column=값

where ... 

mysql> update testtbl4 set Lname='없음' where Fname='Kyoichi';
Query OK, 251 rows affected (0.18 sec)
Rows matched: 251  Changed: 251  Warnings: 0

cf. update table testtbl4 이 아님, update testtbl4

delete

mysql> DELETE FROM testtbl4 WHERE Fname = 'Aamer';
Query OK, 228 rows affected (0.17 sec)

mysql> select * from testtbl4 where Fname = 'Aamer';
Empty set (0.13 sec)

데이터 대량 삭제 : delete, drop, truncate

> 30만건의 데이터가 있는 bigtbl1,2,3을 생성

mysql> create table bigtbl1 (SELECT * FROM employees.employees);
Query OK, 300024 rows affected (22.59 sec)
Records: 300024  Duplicates: 0  Warnings: 0

mysql> create table bigtbl2 (SELECT * FROM employees.employees);
Query OK, 300024 rows affected (22.72 sec)
Records: 300024  Duplicates: 0  Warnings: 0

mysql> create table bigtbl3 (SELECT * FROM employees.employees);
Query OK, 300024 rows affected (25.65 sec)
Records: 300024  Duplicates: 0  Warnings: 0

> delete로 삭제 : 8초 이상 소요

mysql> delete from bigtbl1;
Query OK, 300024 rows affected (8.27 sec)

> drop으로 삭제

mysql> drop table bigtbl2;
Query OK, 0 rows affected (0.60 sec)

> truncate로 삭제

mysql> truncate table bigtbl3;
Query OK, 0 rows affected (0.59 sec)

delete를 통한 데이터 삭제가 가장 오래 소요됨.

drop으로 삭제하면 테이블 구조도 함께 삭제됨.

delete 및 truncate로 삭제하면 테이블은 보존됨.

mysql> show tables;
+-----------------+
| Tables_in_sqlDB |
+-----------------+
| bigtbl1         |
| bigtbl3         |
| buytbl          |
| buytbl2         |
| testtbl1        |
| testtbl2        |
| testtbl3        |
| testtbl4        |
| testtbl5        |
| usertbl         |
+-----------------+
10 rows in set (0.01 sec)

조건부 데이터 삽입

insert ignore into

mysql> select * from membertbl;
+--------+-----------+--------+
| userid | name      | addr   |
+--------+-----------+--------+
| BBK    | 바비킴    | 서울   |
| EJW    | 은지원    | 경북   |
| JKW    | 조관우    | 경기   |
+--------+-----------+--------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE membertbl
    -> ADD CONSTRAINT pk_membertbl PRIMARY KEY (userid);
Query OK, 0 rows affected (0.14 sec)

> userid는 primary key로 되어 중복값은 들어갈 수 없는 상태임

mysql> INSERT INTO membertbl VALUES('BBK' , '비비코', '미국');
mysql> INSERT INTO membertbl VALUES('SJH' , '서장훈', '서울');

mysql> INSERT INTO membertbl VALUES('HJY' , '현주엽', '경기');

> BBK 는 중복되어 에러가 발생할 것임

> 에러는 무시하고 다음열의 insert문은 정상 실행되도록 하기 위해서 ignore 구문이 들어감

( UI 프로그램 상의 말이고, CLI에서 긁어 붙이면 아래 두줄-서장훈과 현주협-은 성공하겠지? )

mysql> INSERT ignore INTO membertbl VALUES('BBK' , '비비코', '미국');

mysql> INSERT ignore INTO membertbl VALUES('SJH' , '서장훈', '서울');

mysql> INSERT ignore INTO membertbl VALUES('HJY' , '현주엽', '경기');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT * FROM membertbl;
+--------+-----------+--------+
| userid | name      | addr   |
+--------+-----------+--------+
| BBK    | 바비킴    | 서울   |
| EJW    | 은지원    | 경북   |
| HJY    | 현주엽    | 경기   |
| JKW    | 조관우    | 경기   |
| SJH    | 서장훈    | 서울   |
+--------+-----------+--------+
5 rows in set (0.00 sec)

primary key가 중복될 경우, 그래도 업데이트 하려면

INSERT INTO membertbl VALUES('BBK' , '비비코', '미국')
 ON DUPLICATE KEY UPDATE name='비비코', addr='미국';

INSERT INTO membertbl VALUES('DJM' , '동짜몽', '일본')
 ON DUPLICATE KEY UPDATE name='동짜몽', addr='일본';

SELECT * FROM membertbl;

+--------+-----------+--------+
| userid | name      | addr   |
+--------+-----------+--------+
| BBK    | 비비코    | 미국   |
| DJM    | 동짜몽    | 일본   |
| EJW    | 은지원    | 경북   |
| HJY    | 현주엽    | 경기   |
| JKW    | 조관우    | 경기   |
| SJH    | 서장훈    | 서울   |
+--------+-----------+--------+
6 rows in set (0.00 sec)