SQL（MySQL）入門！ALTER（テーブルの作成・削除・変更・主キー・NOT NULL）編

環境
　Windows 10
　MySQL : version(5.7.28)
使用アプリ
　コマンドプロンプト(Windowsマーク押して「cmd」って打ったら出てくるやつ)

---

#テーブル操作 コマンドまとめ
###テーブル作成

CREATE TABLE データベース名.テーブル名(
   カラム名1 データ型 オプション,
   カラム名2 データ型 オプション,
   カラム名3 データ型 オプション,
   ...);

記入例 :
　商品IDに主キーと自動連番のオプションを付与した在庫テーブルを作成

CREATE TABLE 在庫(
    商品ID INT PRIMARY KEY AUTO_INCREMENT,
    商品名 TEXT,
    価格 INT,
    個数 INT
);

• すでにデータベースに接続している場合（USE文を実行）は、[データベース名]の部分を省略可能。
• データベースに接続していない状態でテーブルを作成したい場合は[データベース名].[テーブル名]の部分の記述が必要。
• カラム名には、最大64バイトまで設定可能。
• NULLを禁止する場合、オプション部分にNOT NULLを追加する。
• キー（主キー、プライマリキー）を設定する場合、オプション部分にPRIMARY KEYを追加する。
• CREATE TABLE IF NOT EXISTS [テーブル名] と書くことも可能。（テーブルの重複を防げる）

---

###テーブル一覧を表示

SHOW TABLES FROM データベース名;

• データベースに接続中の場合は、FROM以降を省略可能。その場合、接続中のデータベース内のテーブル一覧が表示されます。

---

###テーブルの構造を確認する

DESCRIBE テーブル名;
-- DESC テーブル名;
-- データベースに接続中

SHOW COLUMNS FROM テーブル名 FROM データベース名 LIKE "条件"; 
-- データベース外から確認するとき

-- すべて同じ結果が表示される

• データベースに接続中の場合は、FROM以降を省略可能。その場合、接続中のデータベース内のテーブル一覧が表示されます。
• LIKE[条件]を指定することで。条件にあったカラムのみを抽出することが可能。

---

###テーブル定義を変更

ALTER TABLE;

作成済みのテーブルに変更を加えたい場合、主に次のような変更が可能です。

• テーブル名の変更

    ALTER TABLE 変更前のテーブル名 RENAME TO 変更したいテーブル名;
  

• 主キーの追加

    ALTER TABLE テーブル名 ADD PRIMARY KEY カラム名;
  

• 複合主キーの追加

    ALTER TABLE テーブル名 ADD PRIMARY KEY(カラム名1, カラム名2);
  

注意点 : テーブルに１つの主キーを設定している状態でもう1つの主キーを違うカラムに追加しようとしても、下記のようなエラーがでる。
ERROR 1068 (42000): Multiple primary key defined
解決策としては一度、主キーを全て削除してから再度ALTER TABLEで主キーを付け直すとできました。（別の方法があればぜひ教えて下さい）

• 主キーの設定

    ALTER TABLE テーブル名 MODIFY カラム名 データ型 PRIMARY KEY;
  

• 自動連番(AUTO_INCREMENT)の設定

    ALTER TABLE テーブル名 MODIFY カラム名 データ型 AUTO_INCREMENT;
  

• 各データを特定するための唯一のフィールド(UNIQUE KEY)を追加
  ※一つのテーブルに複数のUNIQUE KEYを設定することもできる

    ALTER TABLE テーブル名 ADD カラム名 データ型 UNIQUE KEY;
  

• 主キーの削除

    ALTER TABLE テーブル名 DROP PRIMARY KEY;
  

• インデックスの追加

    ALTER TABLE テーブル名 ADD INDEX インデックス名 (カラム名);
  

• 複合インデックスを追加する場合は、カンマで区切る

    ALTER TABLE テーブル名 ADD INDEX インデックス名 (カラム名1,カラム名2,カラム名3...);
  

• インデックス(unique制約)を外す

    ALTER TABLE テーブル名 DROP INDEX インデックス名;
  

• カラム名とデータ型の両方を変更

    ALTER TABLE テーブル名 CHANGE 変更前のカラム名 変更後のカラム名 変更後のデータ型;
  

• カラムのデータ型のみの変更

    ALTER TABLE テーブル名 MODIFY カラム名 変更後のデータ型;
  

• NULLを許可しない

    ALTER TABLE テーブル名 MODIFY カラム名 データ型 NOT NULL;
  

• カラムの追加

    ALTER TABLE テーブル名 ADD カラム名 データ型 オプション;
  

• 先頭のカラムに追加

    ALTER TABLE テーブル名 ADD カラム名 データ型 オプション FIRST;
  

• 任意の場所にカラムを追加

    ALTER TABLE テーブル名 ADD カラム名 データ型 オプション AFTER 移動したいカラムの上に来るカラム名;
  

• 任意のカラムを先頭に移動

    ALTER TABLE テーブル名 MODIFY 移動したいカラム名 データ型 FIRST;
  

• 任意の場所にカラムを移動

    ALTER TABLE テーブル名 MODIFY 移動したいカラム名 データ型  AFTER 移動したいカラムの上に来るカラム名;
  

• カラムの削除

    ALTER TABLE テーブル名 DROP カラム名;
  

• テーブルのデータをすべて削除

    TRUNCATE テーブル名;
  

---

###テーブルの削除

DROP TABLE データベース名.テーブル名;

すでにデータベースに接続している場合は、データベース名. の部分は省略可能。

DROP TABLE IF EXISTS テーブル名;

もし、テーブル名テーブルが存在するときだけ指定したテーブルを削除する

• 複数のテーブルを削除

    DROP TABLE データベース名.テーブル名1, データベース名.テーブル名2...;
  

---

補足

ALTER TABLE テーブル名 ADD PRIMARY KEY(カラム名1, カラム名2);

「テーブルに１つの主キーを設定している状態でもう1つの主キーを違うカラムに追加しようとしてもエラーがでる」と、上の方で書いているのですが、
SERIALのデータ型で設定したカラムとはPRIMARY KEYの設定が残ったままでもできました。
以下、実行内容です。

実行前の状態を残していなかったのですが、カラム1はSERIALで設定しており、カラム2はPRIMARY KEYで設定しました。
テーブル構造を確認すると、カラム1がUNIQUE KEY設定になっていたので、どちらのカラムもPRIMARY KEY設定にしようとしたのが背景にあります。

まずは、テーブル内のPRIMARY KEYを削除

mysql> ALTER TABLE テーブル名 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.05 sec)

テーブル構造の確認

mysql> desc テーブル名;
+------------------------+---------------------+------+-----+--------------------------+--------------------------------+
| Field                  | Type                | Null | Key | Default                  | Extra                          |
+------------------------+---------------------+------+-----+--------------------------+--------------------------------+
| column1_id             | bigint(20) unsigned | NO   | PRI | NULL                     | auto_increment                 |
| column2_num            | int(4)              | NO   |     | 1                        |                                |

カラム2のPRIMARY KEY設定が削除されて、UNIQUE KEYが設定されていたカラム1がPRIMARY KEYに変わって、そのまま削除されずに残っているのがわかります。

ここで主キーの設定をカラム1とカラム2に追加します。

mysql> ALTER TABLE テーブル名 ADD PRIMARY KEY(column1_id, column2_num);
Query OK, 0 rows affected (0.04 sec)

mysql> desc テーブル名;
+------------------------+---------------------+------+-----+--------------------------+--------------------------------+
| Field                  | Type                | Null | Key | Default                  | Extra                          |
+------------------------+---------------------+------+-----+--------------------------+--------------------------------+
| column1_id             | bigint(20) unsigned | NO   | PRI | NULL                     | auto_increment                 |
| column2_num            | int(4)              | NO   | PRI | 1                        |                                |

なぜかできました

上では、主キーの設定が残っている状態で主キーの追加をしてもエラーが出て実行されなかったのに...。

上に書いた時の状況と違うところは、SERIALしかないのでそこが関係しているのかなと思っています。
SQLに詳しい方、教えて下さい