MySQL

MySQLでDBとテーブルを作成する

  /
DB

SQLとはデータベースを操作するために使用する言語の事を言います。
他の呼び方として「クエリ」と呼ばれる事もあります。

SQLの記法なのですが以下になっています。

  • セミコロンで文を区切る
  • 大文字小文字は区別しない
  • コメントについては
    • /*区間コメント*/
    • — 文末までのコメント
    • # 文末までのコメント

データベースを作るSQL

create database test_db;

データベースを削除するSQL

drop database test_db;

テーブルを作るSQL

create table test_db.test_table (
	id int(6) unsigned default 0 comment 'ID',
	var varchar(20) default 'hello' comment '値'
);

あくまでも、例ですので各種値を変更して作成してください。

現在の値ですが以下のようになっています。

  • テーブル名：test_dbのtest_table
  • idカラム：６桁の符号なし整数型で、何も格納されなかった場合自動的に０を格納します。日本語表記はIDです。
  • varカラム：20桁の可変長文字列型で、何も格納されなかった場合自動的にhelloを格納します。日本語表記は値です。

テーブルを削除するSQL

drop table test_db.test_table;

テーブルの詳細を確認するSQL

show full columns from test_db.test_table;

test_db.test_tableのように、DB名とテーブル名を一緒に指定してあげないとエラーになる場合がありますので、上記のような記述にして確実に詳細を見れるようにしましょう。

使用するデータベースを指定するSQL

use test_db;

上記のSQLを流すと、明示的に使用するDBを指定する事ができます。
DBを指定することによって、create文やselect文を書く時にわざわざ毎回DB名を指定せずに書くことができます。

not null制約・unique制約

create table test_table (
	id int not null default 0 comment 'ID',
	val varchar(20) unique comment '値'
);

idカラムにはnot null制約をつけています。not null制約とは「値が未設定の状態（Null）であることは許されず、追加、更新の際に必ず何らかの値を与えてあげないといけない」という制約です。要するに、なんか値を入れないと追加も更新もさせてあげないよということです。

次に、valカラムのunique制約についてです。unique制約とは「他の行にすでに入っている値と重複するものは挿入できませんよ」という制約です

主キーと複合主キーの簡単な説明

主キーについて

主キー

上の表の主キーに出席番号カラムを設定しています。これによって「出席番号が１の人〜」と呼びかけると「まさお」「男」「２８」が手を挙げることができます。

このように出席番号によって誰か一人を特定できるように振られた番号（文字）を主キーと言います。

では次の表の場合はどうでしょうか？

複合主キー

上の表では「出席番号１番の人〜」と呼びかけても「まさお」か「まりこ」かどちらかを特定することはできません。

では、「出席番号１番で男性の人〜」と呼びかけると「まさお」を特定することができます。

このように２つ以上の要素で一人が特定できる事を複合主キーと言います。

主キーを設定したテーブルを作成するSQL

create table test_db.test_table (
	id int primary key
	val varchar(20)
);

上のSQLは、idカラムを主キーに設定したテーブルを作成するSQLです。

出来上がりのイメージは以下です。

主キー完成イメージ

複合主キーを設定したテーブルを作成するSQL

create table test_db.test_table (
	id int,
	year int,
  name varchar(10),
	 
);

上のSQLは、idカラムとyearカラムを主キーに設定したテーブルを作成するSQLです。

出来上がりのイメージは以下です。

複合主キー完成イメージ

複合キーにした理由は、id１番も４番も「きょうへい」でどちらの「きょうへい」かわからなくて一人を特定できないからです。そこで入社年度であるyear要素も主キーにすることで、１番で2018年入社のきょうへいさんと４番で2019年入社のきょうへいさんといった具合に一人を特定することができます。

テーブルにカラムを追加するSQL

カラムを追加する場合などに使用するSQLとなっています。

下記SQLを流すことで、key2、key3カラムがテーブルに追加されます。

alter table test_db.test_table 
add column key2 varchar(20) not null,
add column key3 varchar(20) not null;

追加する位置を指定してテーブルにカラムを追加するSQL

alter table test_db.test_table 
add column key4 varchar(20) not null after key2;

上記のSQLを流すことによって、既存のkey2カラムの後にkey4カラムを追加することになります。このように追加する位置を指定できます。

一番最初のカラムにkey5カラムを指定する場合を以下に示します。

alter table test_db.test_table 
add column key5 varchar(20) not null first;

上記の記述ではfirstというキーワードを使って、一番最初のカラムにkey5カラムを指定させています。

既存のカラムの情報を変更するSQL

alter table test_db.test_table 
modify column key5 int;

上記のSQLを流すと、元はvarchar(20)だったkey5カラムをintに変更できます。

下のSQLのようにすれば、not null制約なども同時に追加できます。

alter table test_db.test_table 
modify column key5 int not null;

既存カラムを削除するSQL

alter table test_db.test_table 
drop column key5;

上記のSQLを流してあげればkey5カラムが削除されます。

外部キーについて

外部キー（FOREING KEY）とは、関連したテーブル間を結ぶために設定する列のことです。

例えば、従業員テーブルに「従業員番号、部署番号、名字、名前」の項目があります。

他にも部署テーブルというテーブルがあって両者は図のように情報を共有している状態です。

このように、データベースの「この項目（部署番号）には好き勝手な値を入れないでください！こちら（部署テーブルの部署番号）から選んで入れてください」的な制約がついている項目のことを外部キーと呼んでいます。

外部キー

外部キーの役割

繰り返しのようになりますが、具体的には
子テーブルに値を追加する時、親テーブルに無い値を追加しようとするとエラーになってしまします。

また、親テーブルの値を、更新したり、削除しようとした時、もしすでに子テーブルでその値が使われていた場合困ったことになると思うのですが、その場合の選択肢を選ぶことができます。

選択肢は、エラーにする、同時に削除する、同時に更新する、といった選択肢です。

このように親テーブルと子テーブルとの間で値の整合性（矛盾がない様）を取ることができるようにするのがFOREIGN KEY 制約(外部キー制約)です。

具体的な書き方

書式は以下のようになっています。

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

実際のテーブルを使って書いてみたのが以下になります。

alter table test_db.shops 
add constraint fk_pref_id
foreign key(pref_id)
references prefs(id)
on update cascade
on delete restrict;

イメージは以下になります。

外部キーを貼る

RELATED POST