MySQLに関する覚え書き

データベースの一覧を表示

SHOW DATABASES;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample1            |
+--------------------+
2 rows in set (0.00 sec)

テーブルの一覧を表示

SHOW TABLES FROM データベース名;

mysql> SHOW TABLES FROM sample;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sample1            |
+--------------------+
2 rows in set (0.00 sec)

テーブルの内容を表示

DESC テーブル名;

mysql> DESC sample1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| value1 | bigint(20) | YES  |     | NULL    |       |
| value2 | int(11)    | YES  |     | NULL    |       |
| value3 | text       | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

テーブルの項目を変更

ALTER TABLE テーブル名 CHANGE 古い項目名 新しい項目名 新しい型;

mysql> DESC sample;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| value1  | bigint(20) | YES  |     | NULL    |       |
| value2a | int(11)    | YES  |     | NULL    |       |
| value3  | text       | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE sample CHANGE value2 value2x TEXT;
Query OK, 0 rows affected (0.13sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC sample;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| value1  | bigint(20) | YES  |     | NULL    |       |
| value2a | text       | YES  |     | NULL    |       |
| value3  | text       | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

型は同じままで項目名のみを変更することもできます。

テーブルに項目を追加

ALTER TABLE テーブル名 ADD 追加したい項目名 型;

mysql> DESC sample;
+-------------------+------------+------+-----+---------+-------+
| Field             | Type       | Null | Key | Default | Extra |
+-------------------+------------+------+-----+---------+-------+
| value1            | bigint(20) | YES  |     | NULL    |       |
| value2            | int(11)    | YES  |     | NULL    |       |
| value3            | text       | YES  |     | NULL    |       |
+-------------------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE sample ADD value4 TEXT;
Query OK, 0 rows affected (0.12sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC sample;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| value1 | bigint(20) | YES  |     | NULL    |       |
| value2 | int(11)    | YES  |     | NULL    |       |
| value3 | text       | YES  |     | NULL    |       |
| value4 | text       | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

追加する位置を指定する場合はAFTERやFIRSTが使えます。

mysql> ALTER TABLE sample ADD value5 TEXT AFTER value3;
Query OK, 0 rows affected (0.08sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sample ADD value6 TEXT FIRST;
Query OK, 0 rows affected (0.08sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC sample;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| value6 | text       | YES  |     | NULL    |       |
| value1 | bigint(20) | YES  |     | NULL    |       |
| value2 | int(11)    | YES  |     | NULL    |       |
| value3 | text       | YES  |     | NULL    |       |
| value5 | text       | YES  |     | NULL    |       |
| value4 | text       | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)