실습환경구축

---

CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4;
# DEFAULT CHARACTER SET utf8mb4;
# UTF8MB4라는 인코딩쓰겠다는것

Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerobase           |
+--------------------+
5 rows in set (0.00 sec)

use zerobase;
Database changed

Table

---

DDL (데이터 정의어)

---

데이터 구조를 정의하는데 사용되는 명령어
구조를 생성(CREATE)하거나 삭제(DROP), 이름을 변경(ALTER)한다.

🧷 테이블 생성

CREATE TABLE table_name (
    column1 데이터유형,
    column2 데이터유형);

🧷 테이블 확인

  SHOW TABLES;

🧷 테이블 정보확인

  DESC table_name;

# mytable 생성
 create table mytable
    -> ( id int, name varchar(16) );
    #ID(정수형)와 NAME(가변문자형) 칼럼을 가진 TABLE을 생성
Query OK, 0 rows affected (0.06 sec)

# 테이블확인
mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| mytable            |
+--------------------+
1 row in set (0.02 sec)

# mytable 정보확인
mysql> desc mytable;
ERROR 1146 (42S02): Table 'zerobase.mytables' doesn't exist
mysql>  desc mytable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

🧷 테이블 삭제

  DROP table_name;

🧷 테이블명 변경

  ALTER TABLE table_name
  RENAME new_table_name

🧷 테이블 컬럼 추가

  ALTER TABLE table_name
  ADD COLUMN column type ;

🧷 테이블 컬럼 정보 수정

  ALTER TABLE table_name
  MODIFY COLUMN column new_type ;

🧷 테이블 컬럼 이름 수정

  ALTER TABLE table_name
  CHANGE COLUMN old_column new_column new_type ;

🧷 테이블 컬럼 삭제

  ALTER TABLE table_name
  DROP COLUMN column ;

• 테이블명 mytable -> person 으로 변경

ALTER TABLE mytable RENAME person;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| person             |
+--------------------+
1 row in set (0.00 sec)

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 테이블 agee 컬럼 추가

ALTER TABLE person ADD COLUMN agee double;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| agee  | double      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 테이블 정보 (타입 double -> int) 수정

ALTER TABLE person MODIFY COLUMN agee int;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| agee  | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 테이블 컬럼명 agee -> age로 변경

ALTER TABLE person CHANGE COLUMN agee age int;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 테이블 age 컬럼 삭제

ALTER TABLE person DROP COLUMN age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• person 테이블 삭제

 DROP TABLE person;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
Empty set (0.00 sec)

---

Daily Study Note