MySQLのテーブル操作

2020.01.24 2021.07.06

データベース操作って弱い分野なんですよね。なので、コマンドの覚え書き。本記事はテーブルの操作コマンド一覧です。

テーブル作成

CREATE TABLE テーブル名;

--sample
CREATE TABLE samplemeta (
    post_id INT NOT NULL,
    meta_key VARCHAR(255),
    meta_value VARCHAR(255),
    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

テーブル削除

DROP TABLE テーブル名;

テーブル名変更

ALTER TABLE 旧テーブル名 RENAME TO 新テーブル名;

キー関連

自然キー

入力された値を主キーとして使います。
NOT NULLとPRIMARY KEYを指定します。

CREATE TABLE samplemeta (
    post_id INT NOT NULL PRIMARY KEY,
    meta_key VARCHAR(255),
    meta_value VARCHAR(255),
    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

サロゲートキー

代用キー。システム側で自動採番された値を主キーとして使います。
AUTO_INCREMENTを指定します。

CREATE TABLE samplemeta (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    meta_key VARCHAR(255),
    meta_value VARCHAR(255),
    created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

複合キー

複数のカラムの組み合わせでユニークなキーにします。

CREATE TABLE monster_heart (
    name VARCHAR(64) NOT NULL,
    grade VARCHAR(2) NOT NULL,
    color INT NOT NULL,
    cost INT NOT NULL,
    modified DATETIME DEFAULT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(name, grade)
);

外部キー

別テーブルのカラムをキーとします。
下記サンプルは複数指定の場合。単一の場合はカラムを１つ指定します。
複数のテーブルから指定も可能です。

CREATE TABLE monster_heart_append (
    name VARCHAR(64) NOT NULL,
    grade VARCHAR(2) NOT NULL,
    append VARCHAR(255) NOT NULL,
    modified DATETIME DEFAULT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (name, grade) REFERENCES monster_heart(name, grade)
);

PRIMARY KEY 指定の削除

テーブルからPRIMARY KEYを全て削除します。カラムは削除されません。

ALTER TABLE テーブル名 DROP PRIMARY KEY;

-- sample
ALTER TABLE samplemeta DROP PRIMARY KEY;

PRIMARY KEY 指定の追加

指定のカラムにPRIMARY KEYを追加します。

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

-- sample
ALTER TABLE samplemeta ADD PRIMARY KEY(post_id, meta_key);

カラム操作

カラムの変更

カラム名を変更したり、定義を変更するコマンドです。

ALTER TABLE テーブル名 CHANGE 旧カラム名 新カラム定義;

-- sample
ALTER TABLE samplemeta CHANGE meta_key meta_key VARCHAR(255);

カラム名を変更しない場合はMODIFYも利用できます。

ALTER TABLE テーブル名 MODIFY カラム名 新カラム定義;

-- sample
ALTER TABLE samplemeta MODIFY meta_key INT DEFAULT 1;

NOT NULL制約は新カラム定義に従う
現在PRIMARY KEYのカラムを変更するとき、新カラム定義にPRIMARY KEYを記述するとエラーになる
PRIMARY KEYとして追加する場合は新カラム定義に記述するか上記、PRIMARY KEY 指定の追加を使う

カラムの追加

ALTER TABLE テーブル名 ADD カラム名 カラム定義;

-- sample
ALTER TABLE samplemeta ADD meta_num INT NOT NULL;

カラムの削除

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

デフォルト値の変更

ALTER TABLE テーブル名 ALTER カラム名 SET DEFAULT デフォルト値;

デフォルト値の削除

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

コメントコメントをキャンセル

XAMPPのMySQLで文字コードを変更する

MySQLのデータ操作