SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新人エンジニアに贈る、SQL Training

【入門】SQLの基本的な文法は? サブ言語のCREATE&SELECTをやさしく解説

新人エンジニアに贈る、SQL Training 第2回

  • X ポスト
  • このエントリーをはてなブックマークに追加

 前回は、データベースやSQLの歴史的な背景の話から始めて、SQLの基本のデータ単位であるテーブルを理解することを目標にさまざまな概念を見てきました。本記事ではいよいよ、RDBMSのクエリ言語であるSQLの文法に触れていきます。

  • X ポスト
  • このエントリーをはてなブックマークに追加

以前の記事

サブ言語

DDLとDML

 SQLにはサブ言語がいくつか定義されており、この連載では、DDLとDMLを扱います。データ定義言語(DDL)は、データベースのデータ構造を定義する言語です。DDLは、Data Definieiton Languageの略です。DDLは、表の作成(CREATE)、列の更新(ALTER)などの命令を行います。データ操作言語(DML)は、Data Manipulation Languageのことで、データの検索(SELECT)、挿入(INSERT)などの命令が含まれます。

CREATE

 CREATE文は、テーブルの構造(スキーマ)を定義する時に利用します。これからステップバイステップで、学生クラブのデータベースを作成していきます。このデータベースには、学生、クラブ、クラブメンバーという3つのテーブルが含まれています。

学生データベースに含まれる3つのテーブル
学生データベースに含まれる3つのテーブル

 CREATEの構文はシンプルで、基本的にはテーブル名と、属性名と属性のデータ型のペアのリストを書くだけになります。

CREATE TABLE student (
  sid INTEGER,
  name CHAR(20),
  age INTEGER,
  major CHAR(20));

PK

 この命令でデータベースにテーブルを作ることができますが、このテーブルを有益なものにするために、主キー(Primary Key)を設定します。主キーとは、テーブル内の1行を一意に識別するための検索キーです。このキーをある属性に対して設定すると、その列(主キー列)では、値の重複が禁止されます。この制約を、主キー制約と呼びます。定義の仕方は、PRIMARY KEY(対象の属性名)をリストに加えるだけです。

CREATE TABLE student (
  sid INTEGER,
  name CHAR(20),
  age INTEGER,
  major CHAR(20),
  PRIMARY KEY (sid));

 この例だと、主キー制約によって、同じ学生番号(sid)を持つ学生を登録することができなくなります。主キーは、複数の列から作ることもできます(複合主キー)。例えば、PRIMARY KEY(name, age)とすると、氏名と年齢が同じ学生を重複して登録することができなくなります。

 他の2つのテーブルも主キー付きで作成してみます。

CREATE TABLE club (
  cid INTEGER,
  name CHAR(20),
  budget INTEGER,
  PRIMARY KEY (cid));

CREATE TABLE club_member (
  sid INTEGER,
  cid INTEGER,
  joined_at DATE,
  PRIMARY KEY (sid, cid));

 club_memberの主キーを複合主キーにした理由は、1人の学生があるクラブに複数登録されないようにするためです。

FK

 もう1つ、加えたい制約があります。外部キーによる制約です。外部キー(Foreign Key) とは、特定のテーブルの列を参照するためのキー(ポインター)です。このポインターを利用すると、2つのテーブルの間に関連(親子関連)をもたらすことができます。

 それでは、club_memberとstudentテーブルの間に親子関連を持たせてみます。

CREATE TABLE club_member (
  sid INTEGER,
  cid INTEGER,
  joined_at DATE,
  PRIMARY KEY (sid, cid),
  FOREIGN KEY (sid) REFERENCES student(sid));

 CREATE文に、FOREIGN KEY(自身のPK)REFERENCES 参照先のテーブル名(参照先PK)が追加されたのがわかると思います。これで、参照元(club_member)が子テーブル、参照先(student)が親テーブルとなります。

 外部キーは、主キーと違い1つのテーブルに対して複数設定できます。club_memberとclubの間にも親子関連を持たせます。

CREATE TABLE club_member (
  sid INTEGER,
  cid INTEGER,
  joined_at DATE,
  PRIMARY KEY (sid, cid),
  FOREIGN KEY (sid) REFERENCES student(sid),
  FOREIGN KEY (cid) REFERENCES club(cid));

 さて、外部キーを設定すると、具体的に何が嬉しいのでしょうか。主キー同様、外部キーによって課される制約を外部キー制約(または、参照整合性制約)と呼びます。この制約によって以下2つのことが禁止されます。

  1. 子テーブルの外部キーの値は親テーブルの主キーに存在する値でないといけない
  2. 親テーブルが持つ参照されているキーに対してのUPDATE/DELETEを禁止

 もう少し説明すると、1に関しては、例えば、学生番号が4の学生をclub_memberテーブルに追加することができなくなります。なぜなら、studentテーブルにsidが4の学生が存在しないからです。2に関しては、例えば学生番号1の学生を削除することができなくなります。理由は、club_memberにsidが1のレコードが存在するからです。何となく外部キー制約のイメージが掴めたでしょうか。

MySQLの参照アクション

 禁止事項の2つ目は、MySQLでは参照アクションと呼ばれています。参照アクションは、親テーブルが持つ参照されているキーに対してUPDATEやDELETEをした場合の子テーブルの外部キーに与える影響を指します。UPDATEとDELETEそれぞれに対して、4つのアクションから1つ選ぶことができます。先ほど説明した制約は、MySQLではデフォルトの挙動になっています。

 シンタックスは以下のような形になります。

FOREIGN KEY (sid)
    REFERENCES student 
        ON UPDATE アクション名
        ON DELETE アクション名

PKとFKを設定しよう

 基本的に主キーと外部キーは設定しておきましょう。主キーは、行の重複を防いだり、クエリで個別の行を参照可能にしたり、外部キー参照をサポートしたりなどのメリットがあります。外部キーを用いると、参照整合性が保たれ、CASCADEアクションを実現できます。このような手間なことはアプリ側でやらずにデータベースに任せましょう。

ここまでの要点

 RDBMSはリレーショナルモデルがベースでした。このデータベースの特徴を構造、操作、整合性の3つの観点に着目すると以下のようにまとめることができます。

  • 構造:テーブル
  • 操作:SQL
  • 整合性:主キー制約、外部キー制約

 データベースを比較する際は、この3つの軸を用いると比較が容易になると思います。さらに、SQLにはサブ言語が存在することと、その中のデータ定義言語に含まれるCREATE TABLE文の基礎を学びました。次はデータ操作言語のSELECTをみていきます。

次のページ
SELECT

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
新人エンジニアに贈る、SQL Training連載記事一覧

もっと読む

この記事の著者

三宅 悠太(ミヤケ ユウタ)

 海外の大学でコンピュータサイエンスの学士を取得後、株式会社サイバーエージェントに入社。チャットボットプラットフォームのバックエンド開発などを経て、現在は、株式会社メルコインで、暗号資産やブロックチェーンに関するサービス開発に従事。その他、TechTrainのメンタリングも務める。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/17227 2023/01/30 11:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング