はじめに

ここではMySQLのテーブルに関するコマンドをまとめておく。

`create database`

基本的には、MySQLの中に「データベース」という単位があって、そこでデータを区分けし、さらに各データベースの中でテーブルという形でデータを保持することになる。まず、create databaseでhatenaというデータベースを作る。そして、その中にid, val, nameというカラムを持つtbl01というテーブルを作る。

mysql> create database hatena;

# use hatena;でデータベースを設定すれば`hatena.`の指定は不要
mysql> CREATE TABLE hatena.tbl01 (
            id INT,
            val INT,
            name CHAR(30)
);

ここ以降のコマンドの説明はは、use <database name>を使用したコマンドである。

mysql > use hatena;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

create tableを実行する際には、データベースのENGINEやCHARSET、INDEXなどなど様々なオプションを設定できる。もちろん後からでも設定できる。

`show create table`

show create tableはテーブルの定義情報を確認できる。

mysql> show create table tbl01;

+-------+-----------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                   |
+-------+-----------------------------------------------------------------+
| tbl01 | CREATE TABLE `tbl01` (
  `id` int(11) DEFAULT NULL,
  `val` int(11) DEFAULT NULL,
  `name` char(30) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

とか、こんな感じになる。

mysql> 
CREATE TABLE `check` (
  `No` int(11) NOT NULL AUTO_INCREMENT,
  `INT` int(11) NOT NULL,
  `INT_UNSIGNED` int(10) unsigned NOT NULL,
  `TINY_INT` tinyint(1) NOT NULL,
  `VARCHAR` varchar(255) NOT NULL,
  `TEXT` longtext NOT NULL,
  `DATE` date NOT NULL,
  `DATETIME` datetime NOT NULL,
  `TIMESTAMP` timestamp NOT NULL,
  `checkcol` varchar(45) NOT NULL,
  PRIMARY KEY (`No`),
  KEY `No_ind` (`No`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

`show table status`

show table statusはデータベース上の全てのテーブルの情報を確認できる。Auto_incrementやCreate_time、Rowsなど様々な情報が確認できる。

mysql> show table status like 'tbl01' \G;
*************************** 1. row ***************************
           Name: tbl01
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-11-01 11:47:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

`desc`

テーブルのカラム情報を見たい場合は、desc <table name>を使用する。

mysql> desc tbl01;

+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| val   | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

`alter tabel`

テーブルの構造を修正する場合はalter tabelコマンドで行う。下記のコマンドを組み合わせることで、様々な変更が行える。

変更内容	コマンド	Sample SQL
カラムの追加	add column	alter table tbl01 add column name2 char(10);
カラムの変更	change	alter table tbl01 change name NAME char(100);
カラムの修正	modify	alter table tbl01 modify NAME char(255);
カラムの削除	drop	alter table tbl01 drop name2;
インデックスの追加	add index	alter table tbl01 add index idx_name (id, val);
プライマリーキーの追加	add primary key	alter table tbl01 add primary key (id);

`add column`

例えば、新しいカラムを追加する場合は、こんな感じ。

mysql> alter table tbl01 add column name2 char(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tbl01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| val   | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
| name2 | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

`change`

nameカラムをchar(100)のNAMEカラムに変更するコマンド。

mysql> alter table tbl01 change name NAME char(100);

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tbl01;

+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| val   | int(11)   | YES  |     | NULL    |       |
| NAME  | char(100) | YES  |     | NULL    |       |
| name2 | char(10)  | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

`modify`

NAMEのデータ型をchar(100)からchar(255)に変更する。

mysql> alter table tbl01 modify NAME char(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tbl01;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| val   | int(11)   | YES  |     | NULL    |       |
| NAME  | char(255) | YES  |     | NULL    |       |
| name2 | char(10)  | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

`drop`

name2というカラムを削除するコマンド。

mysql> alter table tbl01 drop name2;

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tbl01;

+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | YES  |     | NULL    |       |
| val   | int(11)   | YES  |     | NULL    |       |
| NAME  | char(100) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

`add primary key`

プライマリーキーを追加する場合と削除する場合。

# 追加
mysql> alter table tbl01 add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tbl01;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | NO   | PRI | NULL    |       |
| val   | int(11)   | YES  |     | NULL    |       |
| NAME  | char(255) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

# 削除
mysql> alter table tbl01 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tbl01;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | NO   |     | NULL    |       |
| val   | int(11)   | YES  |     | NULL    |       |
| NAME  | char(255) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

`add index`

インデックスを付与する場合と削除する場合。← #Here#の部分。

# 追加
mysql> alter table tbl01 add index idx_name (id, val);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tbl01\G;
*************************** 1. row ***************************
       Table: tbl01
Create Table: CREATE TABLE `tbl01` (
  `id` int(11) NOT NULL,
  `val` int(11) DEFAULT NULL,
  `NAME` char(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `idx_name` (`id`,`val`) ← #Here#
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

# 削除
mysql> alter table tbl01 drop id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tbl01\G;
*************************** 1. row ***************************
       Table: tbl01
Create Table: CREATE TABLE `tbl01` (
  `val` int(11) DEFAULT NULL,
  `NAME` char(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  KEY `idx_name` (`val`) ← #Here#
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

`truncate`

テーブルは削除せず、格納されている値のみを削除する場合は、truncateコマンドを使う。

mysql> truncate tbl01;
Query OK, 0 rows affected (0.01 sec)

`drop`

テーブルを削除する時はdropコマンド。

mysql> drop table tbl01;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

参考文献

MySQL徹底入門第3版

MySQLのこと。

MySQLのことについてまとめているブログ。他人に見せる用でもなく、自分の勉強備忘録。検索インデックスも外してるので、辿りついた方・・・ようこそ。そんな大した情報ないですよ?!たまにアルゴリズムの練習も

MySQLのcreate , alter table に関するまとめ

はじめに

`create database`

`show create table`

`show table status`

`desc`

`alter tabel`

`add column`

`change`

`modify`

`drop`

`add primary key`

`add index`

`truncate`

`drop`

参考文献