【MySQL】SQLのデータ定義(DDL)の書き方まとめ(データベースやテーブルの作成、削除、更新、挿入)

データベース

SQLのDDLの書き方をまとめました。
データベースやテーブルの作成、削除、更新、挿入、主キーや外部キーの作成といった操作方法を紹介します。
データ操作に関しては、SQLのデータ操作(DML)の書き方まとめを参考にしてみてください。

目次から読む

カーソルがあたっているSQLを実行する

DBeaverなら、windowsもMacもctrl + enterでカーソルがあたっているSQLを実行できます。
SQLを書く場所の左側の矢印ボタンでも同じことが可能です。

複数行のSQLを実行する

DBeaverなら、windowsなら、alt+ X、Macならoption + Xで選択した複数行のSQLを実行可能です。

データベースを作成する

create database test_db;

セミコロンがないと、文が終わらず下のSQL文と結合されます。
文末には必ずセミコロンが必要です。

データベースを削除する

drop database test_db;

テーブルの作成方法

/*
create table ベータベース名.テーブル名 (
  カラム名 データ型 default デフォルト値 制約 comment 'コメント',
  ...,
  表制約
) ENGINE = [INNODB | MyISAM];
*/
create table test_db.test_table (
	id int(8) unsigned default 0 comment 'ID',
	val varchar(20) default 'こんにちは' comment '値'
);

上のENGINEはデータベースの種類ですが、INNODBしかほぼ使わないので記述はしなくても大丈夫です。
省略するとINNODBになります。

カラム名は、属性のことです。
commentが属性に対してのコメントです。
default デフォルト値は、カラム名に値が挿入されなかったときのデフォルトの値です。
一番最後の表制約は外部キーの設定などに使います。

SQLの実行

最後の行にカーソルをあわせて実行します。
上の場合なら、);の部分にカーソルをあわせてctrl+enterで実行します。

SQLエラー : No database selected

上のエラーが出たときは、どのデータベースかを指定していないためのエラーです。

SQLエラー : you have an error in your SQL syntax;

構文エラーです。
varcharなどの文字数を指定しているか、つづりに間違いがないか、文末にセミコロンがついているか、
最後の行にコンマをつけていないかなどチェックしてみましょう。

データ型

以下のようなデータ型が選択できます。

  • INT: 整数値
  • FLOAT: 浮動小数点
  • ※正の値に限定する場合は unsigned を使用。
  • DATETIME: 日時
  • TIMESTAMP: 日時
  • CHAR: 固定長文字列
  • VARCHAR: 可変長文字列
  • BLOB: バイナリデータ（画像や音声、動画など）

INTやFLOATのあとにunsignedをつけると正の値に制限できます。
またINTやVARCHARのように文字数が指定できるものは、varchar(20)のようにして指定します。

テーブルの削除

-- drop table データベース名.テーブル名;
drop table test_db.test_table;

データベースのときとほぼ同じでdropで削除できます。

テーブル定義の表示方法

３つの方法があります。

コメントなしのdesc, describe

-- テーブル定義表示
-- desc データベース名.テーブル名;
desc test_db.test_table;
describe test_db.test_table;

descもしくはdescribeでテーブルの内容を確認できます。
ただし、descではコメントは確認できません。

コメントありのshow full columns

-- show full columns from データベース名.テーブル名;
show full columns from test_db.test_table;

show full columns fromではコメントを含めて属性情報を表示できます。
fullをつけない場合は上のdescと同じです。

-- show create table データベース名.テーブル名;
show create table test_db.test_table;

show create tableでは、現時点でのクエリを確認することができます。
なれてくるとshow create tableの方が見やすくなるケースがあります。

アクティブのデータベースを切り替える use

useを使うと、アクティブのデータベースを設定することができます。

-- use データベース名;
use test_db;

上を実行すると、データベース名を省略したものは、useで定義されたデータベースがアクティブになります。
そのため、データベース名を省略できます。

アクティブなデータベース名を表示する

select detabase();

制約について

以下のような制約があります。

  • UNIQUE: 一意制約
  • NOT NULL: NOT NULL制約
  • CHECK: チェック制約
  • PRIMARY KEY: 主キー制約
  • FOREIGN KEY: 外部キー制約

制約をテーブルや表に設けることで、正しい状態を保つことができます。

たとえば、UNIQUEを指定すれば、必ず一意の値に制限することができます。
NOT NULLなら、必ず値を保持させることができます。
PRIMARY KEYなら主キーであることを担保できます。
CHECK制約は、MySQLのバージョン8.1以上で使える制約です。
値はなるべく限定的にしてあげることで意図した動きになる上に、ストレージの容量も減らすことができます。

CHECKはPHPなどプログラムでもできるので、PHP側でやったほうがいいでしょう。

表制約

表全体にかける制約のことを表制約といいます。
表制約は、カラムの定義が終わったあとに記述します。

複合主キーや外部キーの指定などを表制約で行います。

列制約

列に対して書ける制約を列制約といいます。
列にNOT NULLなどを指定して値を正常に保ちます。

use test_db;
create table test_table(
	id int not null default 0 comment 'ID',
	-- id int default 0 not null comment 'ID'でもいい
	var varchar(20) unique comment '値'
);
show full columns from test_table;

show full columnsで制約についても確認できます。

主キーの作成

create table test_table(
	key1 int primary key
);
show full columns from test_table;

KeyにPRIなどの文字があれば設定できています。
Primary keyは、NOT NULLでUNIQUEなもので、その２つの制約があるものと認識すればOKです。

複合主キーの作成方法

create table test_table(
	key1 int,
	key2 int,
	primary key (key1, key2)
);
show full columns from test_table;

複合主キーを作成するときは、列を定義したあと、表制約の部分で定義します。
上の場合なら、primary keyとしてkey1とkey2が指定されています。
show full columnsでは、KEYにPRIが両方入っていることが確認できます。

この複合主キーの場合は、key1とkey2にNOT NULL制約がついているような状態と考えられます。

auto_increment 自動IDの付与

主キーの値にこだわりがないときは、auto incrementで自動IDを付与することができます。
auto_incrementは、3つの注意点があります。

auto_incrementはインデックスされている必要がある

create table test_table(
	key1 int auto_increment primary key
	-- key1 int auto_increment unique
	-- index (key1)	
);
show full columns from test_table;

auto_incrementを使用するときは、indexを付与する必要があります。
indexとは、検索するための仕組みです。
primary keyとするか、uniqueとするか、indexで指定するかでindexを付与することができます。

Extraにauto_incrementがついていればOKです。
Extraに関してはdescでも確認できます。

auto_incrementの初期化

-- auto_incrementの初期化
alter table test_db.mst_shops auto_increment = 1;

1など開始たい数値を代入することで初期化できます。

indexの確認方法

show index from test_table;

indexは、show indexで確認することができます。
primary keyでindexを付与したなら、key_nameの属性でprimaryと確認することができます。

auto_incrementは一つのテーブルにつき一つの属性のみ

create table test_table(
	key1 int auto_increment primary key
	key2 int auto_increment unique
);
show full columns from test_table;

上のように２つ以上の列に対してauto_incrementはできません。

デフォルト値は設定できない

create table test_table(
	key1 int auto_increment default 0 primary key
);
show full columns from test_table;

自動で採番されるため、上のようにデフォルト値は設定できません。
エラーになります。

テーブル定義の変更

create tableで作成したテーブルを変更したいとき、alter tableを使います。

既存テーブルに追加したいとき

use test_db;
alter table test_table
add column key2 varchar(30) not null,
add column key3 int(8) not null;

add columnで追加できます。
複数追加したい場合はaddをカンマでつなげて記述します。

特定の場所に追加したいとき after

alter table test_table 
add column key10 int(4) after key1;

afterを使うことで、特定のcolumnのあとに追加することができます。
上の場合ならkey1の後にkey10が追加されます。

最初に追加したいとき first

alter table test_table 
add column key12 int(4) first;

上の場合、firstによって、key12が最初のcolumnとして追加されます。

現在あるカラムの情報を変更したいとき modify

alter table test_table 
modify column key12 varchar(20);

上のように既存のカラムを変更したいときは、modifyを使います。
制約なども付与できます。

カラムを削除する drop

alter table test_table 
drop column key12;

dropで既存のカラムを削除することができます。

primary keyを削除する

alter table test_table 
drop primary key;

上のようにすると、primary keyを削除できます。

incorrect table definition; there can be only one auto column and it must be defined as a key

上のエラーが出た場合、auto_incrementを先に削除する必要があります。

alter table test_table 
modify column key1 int(10) not null;

その場合、上のようにauto_increamentがついていたkeyをmodifyで変更します。
その後、primary keyを削除します。

alter table test_table 
drop primary key;

外部キーを設定する

外部キーは他のテーブルと結合するときに使うキーのことです。

外部キーを設定するときの注意点

以下の注意点があります。

  • 型情報は合わせる
  • インデックスの自動付与

同じ情報なので型情報は合わせる必要があります。

外部キーを作成すると、自動的にインデックスが付与されます。
インデックスとは、検索や並び替えを高速で処理するために必要なデータベースオブジェクトです。
ただし、すでに有効なキーが存在する場合には作成されません。例えば、複合主キーの一番最初はインデックスが作成されません。

外部キーのアクション

外部キーには、レコードが変更・削除されたときにアクションを指定することができます。
外部キーのアクションは以下の通りです。

  • ON DELETE: レコードが削除された際のアクション
  • ON UPDATE: レコードが更新された際のアクション

上のアクションのときの動作は主に以下の選択肢があります。

  • CASCADE
  • RESTRICT

CASCADE(カスケード)は、親テーブルの行を削除または更新し、子テーブル内の一致する行を自動的に削除または更新します。
RESTRICTは、親テーブルに対する削除または更新操作を拒否します。
ON DELETE または ON UPDATE 句を省略した場合、RESTRICTになります。

外部キー作成の構文

alter table 子テーブル名
add constraint 制約名（※削除する際に使用）
foreign key (対象のキー名)
      references 親テーブル名(テーブルキー名)
      on update cascade 
      on delete restrict; -- 省略可

親テーブルは、外部キーをはられる方です。

alter table mst_shops 
add constraint fk_pref_id
foreign key (pref_id)
references mst_prefs(id)
on update cascade
on delete restrict;

上のように、まずは外部キーを貼りたいテーブル（子テーブル）を変更します。
制約名はfkなんとかなどわかりやすい名前にします。
addが終わったら表制約でforeign keyの対象キーを指定します。
参照先として、referencesを使って親テーブルを指定します。

アクションの指定があれば指定します。restrictの場合はデフォルトなので書かなくても大丈夫です。

外部キーの確認方法

データベースのER図をクリックして、テーブルをつなぐ線が出ているので、
その線をクリックすると外部キーを確認することができます。

外部キー制約ありのテーブルを削除するときの注意点

外部キー制約ありのテーブルを削除するとき、子テーブルから削除する必要があります。
そうしないとエラーになり、テーブルを削除できません。

データベース

Posted by devsakaso