🧷 실습환경 만들기

🖇️ Table 생성

• zerobase라는 이름의 데이터베이스 생성

CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4;

⇊

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

ㄴDEFAULT CHARACTER SET:기본자료형 , utf8mb4:다국어,이모지 지원

• table 생성 문법

CREATE TABLE tablename(
    columnname datatype,
    columnname datatype,
    ...
)

• id(int)와 name(varchar(16))칼럼을 가지는 mytable 이라는 이름의 테이블 작성

mysql> use testdb
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id int,
    ->     name varchar(16)
    -> );
Query OK, 0 rows affected (0.02 sec)

SHOW TABLES;
⇊

+------------------+
| Tables_in_testdb |
+------------------+
| mytable          |
+------------------+

• table 정보 확인 문법

DESC tablename;

mysql> DESC mytable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

🖇️ Table 변경/삭제

• table 이름변경 문법
  ALTER TABLE tablename
RENAME new_tablename

ALTER TABLE mytable RENAME person;
⇊

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| person           |
+------------------+

• table column 추가 문법
  ALTER TABLE table name
ADD COLUMN columnname datatype;

  • person 테이블에 agee(double) 컬럼 추가

    ALTER TABLE person ADD COLUMN agee double;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | YES  |     | NULL    |       |
    | name  | varchar(16) | YES  |     | NULL    |       |
    | agee  | double      | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+

• table column 변경 문법-datatype

  ALTER TABLE tablename
MODIFY COLUMN columnname datatype;

  • agee의 datatype 변경

  mysql> ALTER TABLE person MODIFY COLUMN agee 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    |       |
  | agee  | int         | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+

• table column 변경 문법-name

  ALTER TABLE tablename
CHANGE COLUMN old_columnname new_dolumnname new_datatype;

  • agee 이름을 age(int)로 변경

  mysql> ALTER TABLE person CHANGE COLUMN agee age int;
  Query OK, 0 rows affected (0.01 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    |       |
  +-------+-------------+------+-----+---------+-------+

• Table column 삭제 문법
  ALTER TABLE tablename
DROP COLUMN columnname;

  • person에서 age 컬럼 삭제

  mysql> ALTER TABLE person DROP COLUMN age;
  Query OK, 0 rows affected (0.01 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    |       |
  +-------+-------------+------+-----+---------+-------+

• table 삭제 문법
  DROP TABLE tablename

  • person 테이블 삭제

    mysql> DROP TABLE person;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show tables;
    Empty set (0.00 sec)

---

from.제로베이스 데이터 취업스쿨 강의