MySQLメモ004：テーブルの作成

テーブルの作成

CREATE TABLE ＜テーブル名＞
  (＜カラムの定義＞
   [,＜インデックスの定義＞])
  [＜オプション＞]

カラムの定義

＜カラム名＞ ＜データ型＞[(桁数)] [NOT NULL] [DEFAULT ＜デフォルト値＞] [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT '＜コメント＞']
[,...]

インデックスの定義

PRIMARY KEY [＜インデックス名＞] [＜インデックスの型＞] (＜カラム名＞[,...])
| INDEX [＜インデックス名＞] [＜インデックスの型＞] (＜カラム名＞[,...])
| UNIQUE [＜インデックス名＞] [＜インデックスの型＞] (＜カラム名＞[,...])
| FULL TEXT [＜インデックス名＞] [＜インデックスの型＞] (＜カラム名＞[,...])
| FOREIGN KEY [＜インデックス名＞] [＜インデックスの型＞] (＜カラム名＞[,...]) [＜参照の定義＞]
[,...]

・"FULL TEXT"は全文検索用の特殊なインデックス。日本語非対応？

参照の定義

REFERENCES ＜テーブル名＞ [(＜カラム名＞[,...])
  [MATCH FULL| MATCH PARTIAL]
  [ON DELETE [RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]]
  [ON UPDATE [RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT]]

・FOREIGN KEYの後に付ける。
・RESTRICT は親テーブルで参照されている行の削除または更新を許可しない。
・CASCADE は親テーブルで参照されている行が削除または更新されるときに、子テーブルの関連する行も削除または更新される。

オプション

ENGINE = ＜ストレージエンジン名＞
COMMENT = '＜コメント＞'
MAX_ROWS = ＜最大行＞

テーブルのデータ付きコピー
・テーブル構造とデータをコピーする。

CREATE TABLE ＜新規テーブル＞
 [AS] SELECT *
    FROM ＜コピー元テーブル＞
    [＜WHERE句＞]

・WHERE句を指定すればデータを抜粋してコピーできる。

テーブルの構造のみコピー
・テーブルの構造だけをコピーする。データは引き継がない。

CREATE TABLE ＜新規テーブル＞ LIKE ＜コピー元テーブル＞

CREATE TABLE文の表示
・テーブルを指定してそのテーブルを作成するCREATE TABLE文を表示することができる。

SHOW CREATE TABLE ＜テーブル名＞

インデックスの情報の表示

SHOW INDEX FROM ＜テーブル名＞

実行例

mysql> CREATE TABLE products
    ->  (product_id INT,
    ->   name VARCHAR(10),
    ->   price INT,
    ->   manufacturer_id INT,
    ->   category_id INT,
    ->   comment VARCHAR(30),
    ->   PRIMARY KEY (product_id),
    ->   INDEX products_index_1 (manufacturer_id),
    ->   INDEX products_index_2 (category_id));
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW CREATE TABLE products;
+----------+------------------------------------+
| Table    | Create Table                       |
+----------+------------------------------------+
| products | CREATE TABLE `products` (
  `product_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `manufacturer_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `comment` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  KEY `products_index_1` (`manufacturer_id`),
  KEY `products_index_2` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM PRODUCTS;
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products |          0 | PRIMARY          |            1 | product_id      | A        |           0 |     NULL | NULL   |      | BTREE      |         |        |
| products |          1 | products_index_1 |            1 | manufacturer_id | A        |           0 |     NULL | NULL   | YES  | BTREE      |         |        |
| products |          1 | products_index_2 |            1 | category_id     | A        |           0 |     NULL | NULL   | YES  | BTREE      |         |        |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql>