| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 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 |
Tags
- MySQL
- solaris
- Oracle RAC
- dba
- perl + 정규표현식
- RHEL4
- cygwin
- Linux
- php5
- PERL
- sqlplus
- solaris network
- 연산자
- Network
- fdisk
- memory
- patch
- command & perl
- perl one-liner
- Unix
- oracle
- perl string
- mariaDB
- rac
- prompt
- grid
- bash
- SQL
- oracle install
- 오라클
Archives
- Today
- Total
DATA 전문가로 가는 길
[Admin][MariaDB] DB 생성 과 테이블 조작(DDL, DML) 본문
1. 새로운 DB 생성 및 테이블 생성
2. 테이블 변경 작업
3. Sample Data 생성
-- 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
-- 테이블에 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)
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