DATA 전문가로 가는 길

[Admin][MariaDB] DB 생성 과 테이블 조작(DDL, DML) 본문

Data Architecture/Admin

[Admin][MariaDB] DB 생성 과 테이블 조작(DDL, DML)

EstenPark 2016. 8. 4. 08:59
1. 새로운 DB 생성 및 테이블 생성
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- ESTDB 데이터베이스 생성
MariaDB [(none)]> CREATE DATABASE ESTDB default character set utf8mb4;
Query OK, 1 row affected (0.00 sec)
 
-- ESTDB 데이터베이스로 변경
MariaDB [(none)]> USE ESTDB;
Database changed
 
-- 사용자 테이블 생성
--#1 IF NOT EXISTS를 활용해서 현재 데이터베이스에 해당 테이블이 존재하는 지 체크 후 생성
MariaDB [ESTDB]> CREATE TABLE IF NOT EXISTS ESTDB.USER_INFO (
    -> USER_NO  BIGINT      NOT NULL, -- 사용자번호
    -> USER_ID  VARCHAR(30) NOT NULL, -- 사용자ID
    -> USER_TEL VARCHAR(15) NULL,     -- 사용자전화번호
    -> REG_DT   DATETIME    NOT NULL  -- 등록일시
    -> )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)
 
--#2 데이터베이스에 존재하는 TABLE 정보
MariaDB [ESTDB]> SHOW TABLES;
+-----------------+
| Tables_in_ESTDB |
+-----------------+
| USER_INFO       |
+-----------------+
1 row in set (0.01 sec)
 
--#3 IF NOT EXISTS를 사용했기 때문에 이미 존재하면 WARNING으로 처리 함.
MariaDB [ESTDB]> CREATE TABLE IF NOT EXISTS ESTDB.USER_INFO (
    -> USER_NO  BIGINT      NOT NULL, -- 사용자번호
    -> USER_ID  VARCHAR(30) NOT NULL, -- 사용자ID
    -> USER_TEL VARCHAR(15) NULL,     -- 사용자전화번호
    -> REG_DT   DATETIME    NOT NULL  -- 등록일시
    -> )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
--#4 WARNING을 확인 해보면 아래와 같은 문구 확인
MariaDB [ESTDB]> SHOW WARNINGS;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Note  | 1050 | Table 'USER_INFO' already exists |
+-------+------+----------------------------------+
 
-- 사용자 테이블 기본키 생성
MariaDB [ESTDB]> ALTER TABLE USER_INFO ADD CONSTRAINT USER_INFO_PK PRIMARY KEY (USER_NO);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 
-- 사용자 테이블 자동 증가(Auto Increment) 생성
MariaDB [ESTDB]> ALTER TABLE USER_INFO MODIFY COLUMN USER_NO BIGINT NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.03 sec)              
Records: 0  Duplicates: 0  Warnings: 0
 
-- 사용자 테이블 자동 증가(Auto Increment)에 대한 시작 값
MariaDB [ESTDB]> ALTER TABLE ESTDB.USER_INFO AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
<p></p>

2. 테이블 변경 작업
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- 테이블에 IP_ADDR 컬럼 추가
MariaDB [ESTDB]> ALTER TABLE ESTDB.USER_INFO ADD IP_ADDR VARCHAR(15) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
-- 테이블 USER_INFO_IX01 인덱스 추가
MariaDB [ESTDB]> ALTER TABLE ESTDB.USER_INFO ADD INDEX USER_INFO_IX01(USER_NO, IP_ADDR);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
--#1 테이블 USER_INFO의 USER_INFO_IX01 인덱스를 삭제
MariaDB [ESTDB]> ALTER TABLE USER_INFO DROP INDEX USER_INFO_IX01;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
--#2 테이블 USER_INFO IP_ADDR 컬럼 삭제
MariaDB [ESTDB]> ALTER TABLE USER_INFO DROP COLUMN IP_ADDR;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
--#3 USER_INFO 테이블에 대한 정보 확인
MariaDB [ESTDB]> DESC USER_INFO;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| USER_NO  | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| USER_ID  | varchar(30)  | NO   |     | NULL    |                |
| USER_TEL | varchar(15) | YES  |     | NULL    |                |
| REG_DT   | datetime    | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
 
--#4 테이블에 IP_ADDR 컬럼 추가와 USER_INFO_IX01 인덱스 추가를 한번에 작업
-- DATA가 많을 경우 한번에 작업하기 때문에 속도를 개선 할 수 있습니다.(스키마에 대한 변경을 두번에 하지 않고, 한번에 하기 때문에 효율 좋습니다.)
MariaDB [ESTDB]> ALTER TABLE ESTDB.USER_INFO  ADD IP_ADDR VARCHAR(15) NOT NULL, ADD INDEX USER_INFO_IX01(USER_NO, IP_ADDR);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
--#5 USER_INFO 테이블에 대한 정보 확인
MariaDB [ESTDB]> DESC USER_INFO;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| USER_NO  | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| USER_ID  | varchar(30)  | NO   |     | NULL    |                |
| USER_TEL | varchar(15) | YES  |     | NULL    |                |
| REG_DT   | datetime    | NO   |     | NULL    |                |
| IP_ADDR  | varchar(15) | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
--#6 USER_INFO 테이블의 IP_ADDR 컬럼 삭제
하나의 커넥션에서 다른 커넥션의 DML이 대기 이벤트로 빠지지 않고, 바로 작업이 가능하다.(InnoDB, TokuDB 엔진만 가능)
MariaDB [ESTDB]> ALTER TABLE USER_INFO CHANGE COLUMN USER_TEL USER_TEL VARCHAR(30) NOT NULL;
Query OK, 0 rows affected (0.02 sec)              
Records: 0  Duplicates: 0  Warnings: 0
 
 
--#7 USER_INFO 테이블의 USER_TYPE 컬럼 추가
MariaDB [ESTDB]> ALTER TABLE ESTDB.USER_INFO ADD USER_TYPE VARCHAR(1), LOCK=NONE, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
--#8 USER_INFO 테이블의 IP_ADDR 컬럼 삭제
MariaDB [ESTDB]> ALTER TABLE USER_INFO DROP COLUMN IP_ADDR;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
--#9 USER_INFO 테이블 정보 확인
MariaDB [ESTDB]> DESC USER_INFO;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| USER_NO   | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| USER_ID   | varchar(30) | YES  |     | NULL    |                |
| USER_TEL  | varchar(30) | NO   |     | NULL    |                |
| REG_DT    | datetime    | NO   |     | NULL    |                |
| USER_TYPE | varchar(1)  | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

3. Sample Data 생성
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Blair","010-334-8610","2016/03/01",2),("Yeo","010-901-2447","2016/11/09",1),("Shannon","010-364-0212","2015/24/11",1),("Desiree","010-196-9289","2016/02/01",1),("Mira","010-247-4479","2017/24/03",2),("Lareina","010-297-4871","2016/21/07",2),("Nicole","010-248-1759","2016/02/08",1),("Maryam","010-348-0917","2016/18/03",3),("Casey","010-600-2628","2015/02/11",2),("Melanie","010-530-5396","2017/24/02",1);
Query OK, 10 rows affected, 5 warnings (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 5
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Autumn","010-851-2342","2017/19/03",3),("Kellie","010-628-0258","2016/07/04",3),("May","010-301-9448","2016/25/06",2),("Linda","010-742-6274","2016/10/10",2),("Moana","010-205-8740","2017/01/04",2),("Teegan","010-313-3707","2015/13/12",1),("Shelly","010-523-0285","2017/22/02",2),("Rhiannon","010-365-2544","2017/14/06",2),("Keelie","010-667-2166","2017/23/05",2),("Bree","010-289-1395","2016/23/09",3);
INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Cheyenne","010-874-1395","2016/03/01",2),("Nina","010-445-0031","2016/15/02",3),("Kessie","010-230-7236","2016/01/07",2),("Kathleen","010-863-3145","2016/12/02",2),("Rosalyn","010-255-6847","2015/29/11",1),("Cameran","010-746-6348","2016/01/05",3),("Stacey","010-585-0143","2017/14/07",2),("Hope","010-217-1290","2015/13/11",1),("Camille","010-458-2036","2016/18/03",1),("Skyler","010-612-7845","2017/09/07",2);
Query OK, 10 rows affected, 7 warnings (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 7
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Cheyenne","010-874-1395","2016/03/01",2),("Nina","010-445-0031","2016/15/02",3),("Kessie","010-230-7236","2016/01/07",2),("Kathleen","010-863-3145","2016/12/02",2),("Rosalyn","010-255-6847","2015/29/11",1),("Cameran","010-746-6348","2016/01/05",3),("Stacey","010-585-0143","2017/14/07",2),("Hope","010-217-1290","2015/13/11",1),("Camille","010-458-2036","2016/18/03",1),("Skyler","010-612-7845","2017/09/07",2);
Query OK, 10 rows affected, 5 warnings (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 5
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Allegra","010-315-5172","2015/06/11",1),("Tasha","010-783-3937","2017/12/02",2),("Kellie","010-761-1219","2016/15/06",2),("Cora","010-402-5327","2017/06/05",1),("Stacey","010-237-7552","2016/02/03",1),("Dara","010-445-6791","2015/22/09",3),("Gillian","010-745-7548","2016/24/01",3),("Vanna","010-561-9486","2015/02/12",1),("Ramona","010-160-9184","2017/17/05",3),("Nita","010-806-0353","2015/19/12",2);
Query OK, 10 rows affected, 5 warnings (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 5
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Myra","010-647-1271","2016/22/09",3),("Germane","010-913-0930","2017/14/04",1),("Olympia","010-762-4425","2016/16/04",2),("Zenia","010-258-1620","2016/10/08",2),("Pamela","010-475-0455","2016/22/05",1),("Kaye","010-238-7292","2015/06/09",1),("Yen","010-719-5408","2015/18/12",2),("Simone","010-958-7024","2016/11/02",3),("Debra","010-348-0684","2017/12/05",2),("May","010-289-9025","2016/08/10",3);
INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Haviva","010-192-2749","2017/17/06",2),("Emily","010-942-6850","2015/27/10",3),("Vivian","010-679-3896","2016/04/09",2),("Liberty","010-163-1504","2016/08/08",3),("Leslie","010-180-5075","2017/25/01",3),("Tatiana","010-194-3689","2016/30/09",3),("Velma","010-590-0832","2016/12/10",2),("Uta","010-173-8394","2017/02/02",2),("Montana","010-573-7174","2017/02/01",2),("Ivory","010-460-9535","2017/20/02",2);
Query OK, 10 rows affected, 5 warnings (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 5
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Haviva","010-192-2749","2017/17/06",2),("Emily","010-942-6850","2015/27/10",3),("Vivian","010-679-3896","2016/04/09",2),("Liberty","010-163-1504","2016/08/08",3),("Leslie","010-180-5075","2017/25/01",3),("Tatiana","010-194-3689","2016/30/09",3),("Velma","010-590-0832","2016/12/10",2),("Uta","010-173-8394","2017/02/02",2),("Montana","010-573-7174","2017/02/01",2),("Ivory","010-460-9535","2017/20/02",2);
INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Amela","010-390-3949","2015/23/09",1),("Ignacia","010-969-7389","2016/05/11",2),("Wilma","010-186-7881","2015/24/09",2),("Nyssa","010-707-2099","2016/05/04",1),("India","010-175-9767","2016/02/01",2),("Vivien","010-671-7425","2016/11/02",1),("Jeanette","010-270-2690","2015/30/10",1),("Shaine","010-755-4151","2017/04/07",2),("Alisa","010-398-0513","2017/26/05",1),("Donna","010-349-0881","2016/11/02",2);
INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Suki","010-989-1453","2016/25/07",1),("Brenna","010-127-6607","2016/06/02",1),("Cheryl","010-122-2691","2016/10/11",2),("Madeson","010-259-2716","2016/23/04",1),("Vera","010-469-4339","2016/10/07",3),("Melinda","010-314-8906","2016/02/09",1),("Naida","010-145-0406","2016/19/07",1),("Gillian","010-538-0755","2016/08/03",3),("Madaline","010-187-8686","2017/28/01",2),("Zephr","010-516-3032","2016/13/11",1);
Query OK, 10 rows affected, 5 warnings (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 5
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Amela","010-390-3949","2015/23/09",1),("Ignacia","010-969-7389","2016/05/11",2),("Wilma","010-186-7881","2015/24/09",2),("Nyssa","010-707-2099","2016/05/04",1),("India","010-175-9767","2016/02/01",2),("Vivien","010-671-7425","2016/11/02",1),("Jeanette","010-270-2690","2015/30/10",1),("Shaine","010-755-4151","2017/04/07",2),("Alisa","010-398-0513","2017/26/05",1),("Donna","010-349-0881","2016/11/02",2);
INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Rachel","010-935-5494","2017/20/01",1),("Xerxes","010-147-0285","2016/01/08",3),("Mariam","010-676-8614","2017/08/04",3),("Wendy","010-341-0944","2016/16/03",3),("Dora","010-732-4777","2016/21/01",1),("Hillary","010-176-9219","2017/20/06",1),("Yoko","010-594-4756","2016/12/07",1),("Carissa","010-852-0000","2015/02/09",2),("Quyn","010-203-0675","2016/28/07",2),("Maggy","010-316-2660","2015/16/10",1);
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 4
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Suki","010-989-1453","2016/25/07",1),("Brenna","010-127-6607","2016/06/02",1),("Cheryl","010-122-2691","2016/10/11",2),("Madeson","010-259-2716","2016/23/04",1),("Vera","010-469-4339","2016/10/07",3),("Melinda","010-314-8906","2016/02/09",1),("Naida","010-145-0406","2016/19/07",1),("Gillian","010-538-0755","2016/08/03",3),("Madaline","010-187-8686","2017/28/01",2),("Zephr","010-516-3032","2016/13/11",1);
INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("May","010-579-4746","2015/03/11",1),("Cecilia","010-544-5203","2015/08/09",3),("Bertha","010-460-9979","2016/24/09",2),("Cassady","010-288-9607","2017/03/05",3),("Ria","010-833-5666","2016/10/02",3),("Eugenia","010-689-6958","2015/20/10",3),("Vivian","010-212-2491","2016/27/12",1),("Daria","010-394-3245","2016/20/09",2),("Carol","010-662-4160","2016/22/06",2),("Diana","010-579-4012","2015/07/11",1);
Query OK, 10 rows affected, 5 warnings (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 5
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("Rachel","010-935-5494","2017/20/01",1),("Xerxes","010-147-0285","2016/01/08",3),("Mariam","010-676-8614","2017/08/04",3),("Wendy","010-341-0944","2016/16/03",3),("Dora","010-732-4777","2016/21/01",1),("Hillary","010-176-9219","2017/20/06",1),("Yoko","010-594-4756","2016/12/07",1),("Carissa","010-852-0000","2015/02/09",2),("Quyn","010-203-0675","2016/28/07",2),("Maggy","010-316-2660","2015/16/10",1);
Query OK, 10 rows affected, 6 warnings (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 6
 
MariaDB [ESTDB]> INSERT INTO USER_INFO (USER_ID,USER_TEL,REG_DT,USER_TYPE) VALUES ("May","010-579-4746","2015/03/11",1),("Cecilia","010-544-5203","2015/08/09",3),("Bertha","010-460-9979","2016/24/09",2),("Cassady","010-288-9607","2017/03/05",3),("Ria","010-833-5666","2016/10/02",3),("Eugenia","010-689-6958","2015/20/10",3),("Vivian","010-212-2491","2016/27/12",1),("Daria","010-394-3245","2016/20/09",2),("Carol","010-662-4160","2016/22/06",2),("Diana","010-579-4012","2015/07/11",1);
Query OK, 10 rows affected, 5 warnings (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 5
 
--#11 샘플 데이터 건수 확인
MariaDB [ESTDB]> SELECT COUNT(*) FROM USER_INFO;
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)


저작자표시

'Data Architecture > Admin' 카테고리의 다른 글

[Admin] MariaDB/MySQL Linux Filesystem Cache(unmap) 메모리 초기화  (0) 2019.08.09
[Admin] MariaDB/MySQL InnoDB 테이블 압축(Compression)  (0) 2019.08.07
[Admin][MariaDB] JSON 테이블에 적재(Json Table Type)  (0) 2016.04.18
[Admin][Oracle] User Process 파라미터 지정  (0) 2016.02.29
[Admin][Oracle] 오라클 시작과 종료(shutdown, no mount, mount, open)  (0) 2016.02.29
'Data Architecture/Admin' Related Articles more
Comments