データベース - MySQL - テーブル作成

[fr-sato]

■目的

SQLを用いてテーブル作成、操作を学びます。
課題毎に発行したコマンドをIssueに記述して下さい。

■課題1

作成したDBに対し、以下を満たすテーブルを作成して下さい。

• ストレージエンジン:InnoDB
• 文字コード:UTF-8
• テーブル構成

カラム名	型	NULL可
id	INT	NO
title	VARCHAR(32)	YES
note	TEXT	YES
created_at	DATETIME	NO

■課題2

作成したテーブルを削除し、IDをプライマリーキーにして再作成して下さい。

■課題3

テーブル削除、再作成をせずに以下のカラム追加、変更、削除をして下さい。

• titleの次に以下のカラムを追加
  • カラム名：editor 型：VARCHAR(16) NULL可：YES
• titleのNULL可をNOに変更
• noteを削除

  ■課題4

以下のテーブルを別に作成し、課題1で作成したテーブルを親として外部キー制約を追加して下さい。

カラム名	型	NULL可
id	INT	NO
detail	TEXT	YES

■課題5

課題4で作成したテーブルを削除して下さい。削除後は次の課題で使うので再度テーブルを作成して下さい。

[fr-itaya]

本課題は工数見積5時間で行います。 開始時間・中断は追ってコメントにてご連絡致します。 よろしくお願いします！

[fr-itaya]

6/3は17：30開始、課題1と2に取り組みます。

[fr-itaya]

■課題1

作成したDBに対し、仕様を満たすテーブルを作成

• コマンド：
  • テーブル作成：CREATE TABLE [TableName] ( [ColumnName1] [DataType], [ColumnName2] [DataType], ... );
  • ストレージエンジン選択：ENGINE = InnoDB;
  • 文字コード設定：CHARACTER SET(又はCHARSET) 'utf8';
  • テーブル確認：SHOW TABLES;
  • テーブル構造表示：DESCRIBE(又はDESC) [TableName]
• 出力結果：

mysql> CREATE TABLE test01 
        -> (
        -> id INT NOT NULL,
        -> title VARCHAR(32),
        -> note TEXT,
        -> created_at DATETIME NOT NULL
        -> )
        -> ENGINE = InnoDB,
        -> CHARACTER SET 'utf8';
Query OK, 0 rows affected (0.26 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| test01 |
+----------------------+
1 row in set (0.00 sec)

mysql> DESC test01;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| title | varchar(32) | YES | | NULL | |
| note | text | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

■課題2

作成したテーブルを削除し、IDをプライマリーキーにして再作成

• コマンド：
  • テーブル削除：DROP TABLE [TableName];
  • プライマリーキーの設定：CREATE TABLE文中にPRIMARY KEY (ColmnName)
• 出力結果：

mysql> DROP TABLE test01;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE test02
    -> (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> title VARCHAR(32),
    -> note TEXT,
    -> created_at DATETIME NOT NULL
    -> )
    -> ENGINE=InnoDB,
    -> CHARSET 'utf8';
Query OK, 0 rows affected (0.18 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| test02               |
+----------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE test02;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | varchar(32) | YES  |     | NULL    |                |
| note       | text        | YES  |     | NULL    |                |
| created_at | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

[fr-itaya]

18：10 中断します。 課題3-5は6/4に回します。

[fr-itaya]

補足：属性のデータ型について

型	意味	備考
INT	単精度の整数	AUTO_INCREMENT設定可能
CHAR(n)	固定長の文字列	nで指定した文字数に要統一
VARCHAR(n)	可変長の文字列	nは最大文字数
TEXT	テキストデータ	データサイズの指定はない
DATETIME	年月日時分秒	表示はYYYY-MM-DD HH:MM:SS

• AUTO_INCREMENT 属性：新しく追加するレコードを識別するための一意な値を生成できる。
  • 使用する際は当該カラムをPRIMARY KEYに設定する必要あり。

[bossato]

確認しました。descだと文字モードやストレージエンジンが確認できないので、別のコマンドで確認して見てください。

[fr-itaya]

補足2：テーブル構造表示コマンド

• DESC=SHOW COLUMNS 文字モード・ストレージエンジンが確認できない
• 文字モード、ストレージエンジンを確認するにはSHOW TABLE STATUSを使用
• 出力結果

mysql> SHOW TABLE STATUS;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| test02 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2014-06-03 09:04:05 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

FROM [DBname] LIKE 'TableName' を末尾に付けることでDB・テーブルを指定して表示。

mysql> SHOW TABLE STATUS FROM mysql_test LIKE 'test02';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| test02 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2014-06-03 09:04:05 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

DESC+文字コード情報ならばSHOW FULL COLUMNSで表示出来る模様。

mysql> SHOW FULL COLUMNS FROM test02;
+------------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int(11)     | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| title      | varchar(32) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| note       | text        | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| created_at | datetime    | NULL            | NO   |     | NULL    |                | select,insert,update,references |         |
+------------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
4 rows in set (0.00 sec)

[fr-itaya]

6/4　12：00開始でよろしくお願いします！

[fr-itaya]

■課題3

テーブル削除、再作成をせずに以下のカラム追加、変更、削除

• titleの次に以下のカラムを追加
  • カラム名：editor 型：VARCHAR(16) NULL可：YES
  • 使用コマンド：ALTER TABLE [TableName] ADD [NewColumnName] [DataType] AFTER [ColumnName];
• titleのNULL可をNOに変更
  • 使用コマンド：ALTER TABLE [TableName] MODIFY COLUMN [ColumnName] [NewDataType(+ Constraint)] ;
• noteを削除
  • 使用コマンド：ALTER TABLE [TableName] DROP COLUMN [ColumnName];
• 出力結果：

mysql> DESCRIBE test02;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | varchar(32) | YES  |     | NULL    |                |
| note       | text        | YES  |     | NULL    |                |
| created_at | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| test02 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2014-06-03 09:04:05 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test02 ADD editor VARCHAR(16) AFTER title;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test02;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | varchar(32) | YES  |     | NULL    |                |
| editor     | varchar(16) | YES  |     | NULL    |                |
| note       | text        | YES  |     | NULL    |                |
| created_at | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE test02 MODIFY COLUMN title TEXT NOT NULL;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test02;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | text        | NO   |     | NULL    |                |
| editor     | varchar(16) | YES  |     | NULL    |                |
| note       | text        | YES  |     | NULL    |                |
| created_at | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE test02 DROP COLUMN note;
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test02;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | text        | NO   |     | NULL    |                |
| editor     | varchar(16) | YES  |     | NULL    |                |
| created_at | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

■課題4

以下のテーブルを別に作成し、課題1で作成したテーブルを親として外部キー制約を追加

カラム名	型	NULL可
id	INT	NO
detail	TEXT	YES

• 使用コマンド：

CREATE TABLE [Child_TableName] ([Child_ColumnName] [DataType],
  FOREIGN KEY ([Child_ColumnName])
  REFERENCES [Parent_TableName] ([Parent_ColumnName])
) ENGINE=InnoDB;

• 外部キー制約を設定する場合、作成するテーブルはInnoDB型である必要がある。
• 出力結果：

mysql> CREATE TABLE test03
    -> (
    -> id INT NOT NULL,
    -> detail TEXT,
    -> FOREIGN KEY (id)
    -> REFERENCES test02(id)
    -> )
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> SHOW TABLE STATUS;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| test02 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2014-06-04 03:33:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| test03 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2014-06-04 03:51:24 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.01 sec)

mysql> DESCRIBE test02;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | text        | NO   |     | NULL    |                |
| editor     | varchar(16) | YES  |     | NULL    |                |
| created_at | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> DESCRIBE test03;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | MUL | NULL    |       |
| detail | text    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• MUL=重複キー。外部キー制約を設定すると自動で生成されるインデックス。
  • インデックス情報をSHOW INDEX FROM [TableName];で表示

mysql> SHOW INDEX FROM test03;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 |          1 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

■課題5

課題4で作成したテーブルを削除

• 使用コマンド：DROP TABLE [TableName];(課題2参照)
• 出力結果：

mysql> SHOW TABLES;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| test02               |
| test03               |
+----------------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE test03;
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| test02               |
+----------------------+
1 row in set (0.00 sec)

[fr-sasaki]

確認しました。OKです。 テーブル情報を表示するコマンドは、SHOW CREATE TABLE table_nameをよく使うのでこちらも覚えておきましょう！

[fr-sato]

ついでなのでSHOW CREATE TABLE table_nameも試してみましょう。

[fr-itaya]

補足3：テーブル構造表示コマンド2

• SHOW CREATE TABLE [TableName]：テーブルのCREATE文を表示
• 出力結果

mysql> SHOW CREATE TABLE test02;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                              |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test02 | CREATE TABLE `test02` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `editor` varchar(16) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[fr-sasaki]

:+1: