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
๋ฐ์ดํฐ ๊ตฌ์กฐ๋ฅผ ์ ์ํ๋๋ฐ ์ฌ์ฉ๋๋ ๋ช
๋ น์ด
๊ตฌ์กฐ๋ฅผ ์์ฑ(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 ;
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)
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)
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)
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)
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)
DROP TABLE person;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
Empty set (0.00 sec)