- データベース（MySQL）とSQLの理解
- SQLによるMySQLの基本操作
- SQLの種類とデータ登録の基本
- データ検索
- データの更新と削除
- トランザクション処理
- データ検索の発展
- 集計関数
- GROUP BY句とHAVING句
- その他の基本的な関数
- リレーショナルデータベースの基本概念
- テーブル結合
- サブクエリ
データベース（MySQL）とSQLの理解
本章では一般的なデータベースの概念とMySQLの基本構造、SQLの概要と基本文法について解説します。
本章の目標
- Databaseの概要を理解する
- RDBMSの概要を理解する
- SQLの概要を理解する
- SQLの基本文法をおぼえる
- MySQLの基本構造を理解する
- １．Databaseとは
- ２．RDBMSとは
- ３．SQLとは
- 1. SQL概要
- 2. SQLの基本構文
- ４．MySQLの基本構造
- 1. データベースとテーブル
- 2. 初期状態のデータベース
- ５．本章のまとめ
Database（DB）とは、複数のアプリケーションやユーザーによってデータが共有できるように整理された、データの集合体のことです。 DBには「階層型DB」「ネットワーク型DB」など、いくつか種類がありますが、現在では「リレーショナルDB」が主流となっています。
「リレーショナルDB」の仕組みについては「SQL基礎」で解説します。
２．RDBMSとは
RDBMS（Relational DataBase Management System）とは、リレーショナルDBを管理するソフトウェアの総称を指します。 日本ではOracle社の「Oracle」が圧倒的なシェアを持ちますが、その他にもIBM社の「DB2」、Microsoft社の「SQL Server」「Access」なども使われています。 また近年では「MySQL」や「PostgreSQL」などをはじめとした、オープンソースのRDBMSを使用するケースが急速に増えています。 尚、本講座では「MySQL」を使用して学習を進めます。
３．SQLとは
1. SQL概要SQL（Structured Query Language）とは、リレーショナルDBを操作するための言語です。 SQLは大きく分けてデータ定義言語(DDL：Data Definition Language)、データ操作言語(DML：Data Manipulation Language)、 データ制御言語(DCL：Data Control Language)の3種類から構成されます。それぞれの意味については、後述します。
2. SQLの基本構文SQLの軸となるのが「キーワード」です。キーワードとは、SQLにあらかじめ用意された命令のための語句です。 このキーワードに、テーブルの名前や列の名前といったデータなどを組み合わせたものを単位として、それらを複数組み合わせることでSQL文が完成します。 キーワードと列名等の組み合わせを「句」と呼びます。
本項で例として用いられている「SELECT」キーワードや「FROM」キーワードの内容については後ほど詳しく解説します。
1 語句の間は半角スペースで区切ります。
半角スペースはいくつ付けても文法上問題ありません。また、半角スペースの代わりにTabを使うことも出来ます。
しかし、半角スペースを1文字入れるのが一般的です。
尚、全角スペースを入れると文法上エラーになりますので注意してください。
2 最後に「;」（セミコロン）を付けます。
SQL文の最期には必ず「;」（セミコロン）を付けることになっています。「;」が付けられるまでは、
SQL文にはまだ続きがあると見なされます。
途中は改行してもOKです。
SELECT
●●
FROM
▲▲
;
SQL文はたとえ改行しても、「;」が登場するまではまだSQL文の続きがあると見なされますので、複数行に改行して記述することが可能です。
改行して記述する場合、半角スペース（もしくはTab）は記述不要になりますが、記述してもエラーにはなりません。そのため、インデント（字下げ）を設定することができます。
3 文字列は「'」（シングルクォート）で囲む
SQLでは文字列は「’」（シングルクォート）で囲むことになっています。
SELECT
●●
FROM
▲▲
WHERE
■■=’test’
;
また、指定したい文字列に「’」が含まれている場合、「’」を重ねるという決まりがあります。例えば、「Let’s」という文字列を指定したい場合は、
Let’’s’
4 大文字と小文字は区別されません
SQLの構文では、大文字と小文字は区別されませんので、全て大文字、全て小文字もしくは大文字と小文字が混在していても、
文法上は同一と見なされます。但し、データ自体の指定は大文字と小文字が区別されますので注意してください。
※本講座では、キーワードは大文字で記載します。
４．MySQLの基本構造
ここでは、MySQLの構造について学習します。
1. データベースとテーブルMySQLは、いくつかのデータベースによって構成されています。また、各データベースは、テーブル（データの入れ物）、インデックス（データベースの索引）、ビュー（データの見せ方を示す情報）によって構成されています。
※インデックス、ビューの説明は、本講座では省きます。
2. 初期状態のデータベースMySQLインストール後の初期状態では、以下の三つのデータベースが用意されています。
- * mysqlデータベース
- MySQLが動作するための基本的な情報（ユーザー情報など）を管理するためのデータベース
- * information_schemaデータベース
- MySQLの構成情報を管理するデータベースです。
- * testデータベース
- テスト用のデータベースで、中身はありません。
| mysqlデータベースとinformation_schemaデータベースは、MySQLの動作には欠かせないデータベースですので、間違って削除などしないようにしてください。ユーザーのテーブル等もこのデータベースには作成しないようにしてください |
５．本章のまとめ
本章ではデータベース、RDBMS、MySQLの概要について説明しました。
まず大きな括りとしてデータベースがあり、その中にRDBMSがあり、様々なRDBMSの一つとしてMySQLが有ることを理解してください。
また、データベースを操作する言語であるSQLについて、その基本文法を詳述しました。
SQLの基本文法についてはこれからテキストを読み進め、実習をするにあたっての基礎となりますので、しっかりと理解し、おぼえるようにして下さい。
SQLによるMySQLの基本操作
本章ではMySQLの基本操作と実際にデータベースにデータを登録するのに必要な準備段階について解説します。
本章の目標
- クライアントからのMySQL接続方法をおぼえる
- ユーザーの作成/削除の方法をおぼえる
- データベースの作成方法をおぼえる
- テーブルの概念を理解する
- テーブルの作成方法をおぼえる
目次
- １．MySQLへの接続とユーザーの作成
- 1. ＭySQLの起動
- 2. MySQLデータベースへの接続
- 3. ユーザーの作成
- 4. ユーザーの削除
- ２．新しいデータベースの作成
- 1. データベースの作成
- 2. データベースの選択
- ３．テーブルの理解とテーブル作成
- 1. テーブルとは
- 2. テーブル作成の準備
- 3. テーブルの作成
- ４．本章のまとめ
１．MySQLへの接続とユーザーの作成
実際にMySQLを操作してみましょう。まずはMySQLの起動方法、mysqlクライアントによるMySQLデータベースへの接続・ユーザーの作成について学習します。
1. ＭySQLの起動デスクトップ上の「xampp-controlショートカット」をダブルクリックし、xamppのコントロールパネルを起動します。
xamppコントロールパネルのMySQLを開始します。
2. MySQLデータベースへの接続デスクトップ上の「MySQL（コマンドプロンプト）」をダブルクリックして、コマンドプロンプトを起動します。
次のコマンドを入力し、「Enter」キーを押します。
mysql -u root -p
パスワードの入力を促されますので、「root123」と入力します。
下の画面が出れば、MySQLへの接続は完了です。
3. ユーザーの作成ここまでは「root」ユーザーでデータベースに接続しましたが、「root」ユーザーは本来管理者が使うIDですので、
通常の作業をする場合に「root」ユーザーで行うのはあまり好ましくありません。
そこで、新しくユーザーを作成してみましょう。
MySQLでユーザーを新規作成するには、「GRANT」コマンドを使用します。
下記が「GRANT」コマンドの書式です。
GRANT ALL PRIVILEGES ON データベース名.* TO ユーザー名@ホスト名
IDENTIFIED BY 'パスワード';
たとえば、データベース名が「test」、追加するユーザー名が「testuser」、ホスト名が「localhost」、
パスワードが「kanda123」だとすると、
GRANT ALL PRIVILEGES ON test.* TO testuser@localhost IDENTIFIED BY 'kanda123';
となります。
| ホスト名とは、データベースにアクセスするコンピュータの名前を表すものです。今回の例では「localhost」を指定していますので、MySQLデータベースサーバーと同じコンピュータからアクセスすることを意味します。 |
ユーザーが正常に作成出来ているか確認してみましょう。一度ログアウトし、先程作成したユーザーで、MySQLに再接続してみます。ログアウトするには、「QUIT」コマンドを用います。
QUIT;
先程作成したユーザーでログインします。
mysql -u testuser -p
4. ユーザーの削除作成されたユーザーは、「MySQL」データベースの「user」テーブルに保存されています。
「user」テーブルを参照して見ましょう。
まずは一旦ログアウトし、「root」ユーザーでMySQLに接続しなおしてください。
その後、下記のSQLコマンドを入力してみてください。
USE mysql;
SELECT user FROM user;
ユーザーを削除するには、「user」テーブルからユーザーのデータを削除します。
下記のSQLコマンドを入力し、データを削除してください。
DELETE FROM user WHERE user='testuser' AND host='localhost';
実際に削除されているか確認してみましょう。
SELECT user FROM user;
「testuser」が存在していません。
2. 新しいデータベースの作成
初期状態ではmysqlデータベース、information_schemaデータベース、testデータベースの３つがあるというお話をしましたが、 ここでは実際にこれから自分が使うデータベースを作成してみましょう。
1. データベースの作成データベースを作成するには、「CREATE DATABASE」文を使用します。
書式は下記の通りです。
CREATE DATABASE データベース名;
ここでは、「mybookdb」という名前のデータベースを作成してみましょう。
CREATE DATABASE mybookdb;
実際にデータベースが作成されているかを確認してみます。
現在あるデータベースを確認するには「SHOW DATABASES」文を使用します。
SHOW DATABASES;
先程作成した「mybookdb」が作成されています。
「mybookdb」データベースを使用するユーザーを作成してください。
ユーザー名は「bms」、パスワードは「bms123」とします。
作成し終えたら、「bms」ユーザーでログインし直してください。
GRANT ALL PRIVILEGES ON mybookdb.* TO bms@localhost IDENTIFIED BY 'bms123'
QUIT;
データベースを削除するには、「DROP DATABASE」文を使用します。
DROP DATABASE データベース名;
例えば先程作成した「mybookdb」を削除する場合は、
DROP DATABASE mybookdb;
となります。
但し、一度データベースを削除してしまうと、そのデータベースの中に作成したテーブル・ビュー・インデックス削除され、元に戻せませんので注意が必要です。
※ここではデータベースの削除は行いません
2.データベースの選択作成したデータベースで作業するには、使用するデータベースを選択する必要があります。
データベースを選択するには、「USE」コマンドを使用します。
書式は下記の通りです。
USE データベース名;
「mybookdb」データベースを選択する場合は、
USE mybookdb;
となります。
正常に選択された場合、「Database changed」と表示されます。
3. テーブルの理解とテーブル作成
ここでは、データを扱う基本的な単位であるテーブルについて学んでいきます。
1. テーブルとはテーブルとは、実際にデータを格納する入れ物です、Excelに例えると、ワークブックがデータベースだとすると、ワークシートがテーブルに当たります。
Excelでもワークシートがないとデータを入力することができないのと同じで、実際にデータを格納するには、まずテーブルを作成しておく必要があります。
2.テーブル作成の準備テーブルを作成するには、事前に決めておかなければならない項目があります。
- 1.テーブル名
- テーブルの名前です。日本語も使えますが、アルファベットと「_」（アンダースコア）を組み合わせて命名するのが一般的です。
- 2.列名
- データを格納する各列の名前です。テーブル名同様、日本語も使えますが、アルファベットと「_」（アンダースコア）を組み合わせて命名するのが一般的です。
- 3.列のデータ型
- テーブルがExcelと違う点は、各列に格納するデータの型を、あらかじめ決めておき、一度決めたらそれ以外の型のデータは入れられないということです。 例えばExcelでは、同じ列に数値も文字も日付も入力することができますが、テーブルは数値型に設定した列には、文字のデータは入力できません。
MySQL使用できる列の型のうち、よく使われるものには次のようなものがあります。
| 型 | データの範囲 |
|---|---|
| INT | -2147483648から2147483647 (符号無しの場合0から4294967295) |
| 型 | データの範囲 |
|---|---|
| CHAR | 0から255文字 |
| VARCHAR | 0から65535バイト |
それに対してVARCHAR型は末尾に空白を付けるようなことはしません。また現行のバージョンでは末尾に空白がある文字列であっても空白が付いたまま格納されます。取得する時も空白付きで取得しますが、WHERE句で比較する時だけ末尾の空白を削除した上で比較されます。
* 日付時刻型
| 型 | データの範囲 |
|---|---|
| DATE | '1000-01-01' から '9999-12-31' |
| DATETIME | '1000-01-01 00:00:00' から '9999-12-31 23:59:59' |
MySQLでは、上記の型の他にも、次のような型が扱えます。
* 整数型
| 型 | データの範囲 |
|---|---|
| TINYINT | -128から127 (符号無しの場合0から255) |
| SMALLINT | -32768から32767 (符号無しの場合0から65535) |
| MEDIUMINT | -8388608から8388607 (符号無しの場合0から16777215) |
| BIGINT | -9223372036854775808から9223372036854775807 |
| 型 | データの範囲 |
|---|---|
| FLOAT | -3.402823466E+38 から -1.175494351E-38
0 1.175494351E-38 から 3.402823466E+38 |
| DOUBLE | -1.7976931348623157E+308 から -2.2250738585072014E-308
0 2.2250738585072014E-308 から 1.7976931348623157E+308 |
DOUBLE(桁数, 小数点以下の桁数)
桁数は全体の桁数で、小数点以下の桁数は桁数の中で小数点以下の桁数です。 例えば全体が5桁で小数点以下の桁数が3桁のものはdouble(5, 3)と記述し、34.567のように表示されます。
* 日付時刻型
| 型 | データの範囲 |
|---|---|
| TIMESTAMP | '1970-01-01 00:00:01' から '2037-12-31 23:59:59' |
| TIME | '-838:59:59' から '838:59:59' |
| YEAR | 1901 から 2155、そして 0000 |
| 型 | データの範囲 |
|---|---|
| BINARY | 0から255文字 |
| VARBINARY | 0から65535バイト |
* BLOB型
| 型 | データの範囲 |
|---|---|
| TINYBLOB | 最長255 (2の8乗 - 1) バイト |
| BLOB | 最長65,535 (2の16乗 - 1) バイト |
| MEDIUMBLOB | 最長65,535 (2の16乗 - 1) バイト |
| LONGBLOB | 最長4,294,967,295、または4GB (2の32乗 - 1) バイト |
巨大なバイナリデータを格納する時に利用されます。
* TEXT
| 型 | データの範囲 |
|---|---|
| TINYTEXT | 最長255 (2の8乗 - 1) バイト |
| TEXT | 最長65,535 (2の16乗 - 1) バイト |
| MEDIUMTEXT | 最長65,535 (2の16乗 - 1) バイト |
| LONGTEXT | 最長4,294,967,295、または4GB (2の32乗 - 1) バイト |
TEXT型は巨大なテキストデータを格納する時に利用されます。
- 4.列の属性
- 列の属性は必須ではありませんが、実際の開発現場では列に属性を付けることがほとんどです。 列の属性にはいくつかの種類がありますが、一番重要なのが主キー（プライマリキー）です。
主キーとは、そのテーブルの中で1つのデータを特定する列の属性のことです。
下の例を見てみましょう。
社員の情報を格納するテーブルを作成した場合、姓名だけではデータを特定することができない場合があります。
そこで、社員番号のような値が重複しないユニークな列があれば、社員のデータを特定できます。
この場合、社員番号の列が主キーになります。
| ポイント：主キーのルール
①重複する値を取らない ②空の状態は許されない |
テーブルには必ず主キーを設定するようにしましょう。主キーは1つの列とは限りません。2つ以上の列を組み合わせてデータを一意にする場合、複数の列を主キーとして設定できます。 このような主キーを複合主キー（連結キー）と言います。
例えば先程の社員テーブルの場合、グループ内の企業で同じ社員番号体系を持っていると、 グループ全体では社員番号だけでデータを特定できないことになります。
このような場合、表に会社コードを追加することで、データを一意に特定できます。
このとき、会社コードと社員番号の組み合わせで主キーとします。
列の属性は、主キー以外にも下記のようなものがあります。
| 属性 | 意味 |
|---|---|
| NOT NULL | 空のデータを許さない |
| UNIQUE | データの重複を許さない |
| DEFAULT | データが未指定の場合、指定した値を自動的に入力する |
| AUTO_INCREMENT | 自動的に連番を入力する |
3.テーブルの作成それでは実際にテーブルを作っていきましょう。
テーブルは、「mybookdb」に作成していきます。「bms」ユーザーでログインし、「USE」文で「mybookdb」を選択してください。
USE mybookdb;
これから下表のテーブルを作成していきます。
テーブル名：「bookinfo」
| 項目 | データのタイプ | 列名 | データ型 | 属性 |
|---|---|---|---|---|
| ISBN番号 | 文字列（最大半角20文字/全角10文字） | isbn | VARCHAR（20） | PRIMARY KEY |
| タイトル | 文字列（最大半角100文字/全角50文字） | title | VARCHAR（100） | |
| 価格 | 数値（整数） | price | INT | NOT NULL |
「CREATE TABLE」文の構文は次の通りです。
CREATE TABLE テーブル名 ( 列名 データ型 属性, 列名 データ型 属性, 列名 データ型 属性, ・・・・ );
「bookinfo」テーブルを作成する場合は次のようになります。
CREATE TABLE bookinfo ( isbn VARCHAR(20) PRIMARY KEY, title VARCHAR(100), price INTEGER NOT NULL );
属性を設定しない列は、属性の記述を省けます。
それでは実際に作成してみましょう。
テーブルが作成されているか確認してみましょう。
現在あるテーブルの確認には「SHOW TABLES」文を使用します。
SHOW TABLES;
「bookinfo」テーブルが作成されているのが確認できます。
さらに作成したテーブルの詳細を確認するには、「SHOW FIELDS」文を使用します。
構文は下記の通りです。
SHOW FIELDS FROM テーブル名;
では「bookinfo」テーブルの詳細を確認してみましょう。
SHOW FIELDS FROM bookinfo;
「Field」は列名を表します。
「Type」はデータのタイプを表します。
「Null」は NULL 値がカラムの中に格納する事ができるかどうかを表します。
「Key」は列の制約を表します。「PRI」の値は、列がそのテーブルの主キー属性が設定されていることを指します。
「Default」は、カラムに割り当てられたデフォルト値を指示します。
「Extra」は列についてその他の情報を表します。例えば「AUTO_INCREMENT」属性が指定されていた場合、ここに表示されます。テーブルを削除するには、「DROP TABLE」文を使用します。
DROP TABLE データベース名;
例えば先程作成した「mybookdb」を削除する場合は、
DROP TABLE bookinfo;
となります。
但し、一度テーブルを削除してしまうと、元に戻せませんので注意が必要です。
※ここではテーブルの削除は行いません複合主キーを設定する場合、単一の主キーを設定する場合と「CREATE TABLE文」の書式が異なります。
例えば仮に「bookinfo」の主キーをISBN番号とタイトルに設定する場合、下記のような「CREATE TABLE」文を発行するとエラーになります。
CREATE TABLE bookinfo (
isbn VARCHAR(20) PRIMARY KEY,
title VARCHAR(100) PRIMARY KEY,
price INTEGER NOT NULL
)
;
複数主キーを設定するには、下記のような構文を使います。
CREATE TABLE bookinfo (
isbn VARCHAR(20),
title VARCHAR(100),
price INTEGER NOT NULL,
PRIMARY KEY(isbn,title)
)
;
オプションとして「PRIMARY KEY(列名1,列名2,・・・)」とする訳です。
4． 本章のまとめ
本章ではデータベース、MySQLへの接続方法、ユーザーの作成、データベースの作成、テーブルの作成ついて実習を交えて説明しました。
これらは実際にデータベースを構築する際にどれも欠かせないステップになりますので、しっかりとマスターしてください。
特にテーブルの基本概念は、将来的にデータベース設計をする上での基礎知識ともなりますので、是非とも理解しておいてください。
SQLの種類とデータ登録の基本
本章ではMySQLの基本操作と実際にデータベースにデータを登録するのに必要な準備段階について解説します。
本章の目標
- SQLの種類を理解する
- テーブルへのデータ登録方法をおぼえる
目次
１．SQLの種類
ここから、本格的にSQL文を用いてデータの操作を学習していきます。まずはSQLについての理解を深めましょう。
1. SQL文の種類SQLは大きく分けてデータ定義言語(DDL：Data Definition Language)、データ操作言語(DML：Data Manipulation Language)、 データ制御言語(DCL：Data Control Language)の3種類から構成されるということを前述しましたが、ここではそれぞれの意味を説明します。
- データ定義言語（DDL）
- データベースにおけるデータ構造を定義するために用いられる言語のことです。
今まで学習した「CREATE TABLE」文や「DROP TABLE」文などがこれにあたります。 - データ操作言語（DML）
- データベースにおいてデータの検索・新規登録・更新・削除を行うための言語のことです。
これから学習する「INSERT」文や「SELECT」文などがこれにあたります。 - データ制御言語（DCL）
- データベースにおいてデータに対するアクセス制御を行うための言語のことです。
ユーザーを作成する際に用いた「GRANT」文がこれにあたります。
SQLでは、データ制御言語（DML文）がもっとも頻繁に使用されます。
２．テーブルへのデータ登録
ここでは実際にテーブルにデータを登録する方法について学習していきます。
1. データ登録の初めに本校のMySqlはUTF-8環境で構築されています。
コマンドプロンプトから日本語データの登録や確認を行う場合には、「SET NAMES CP932;」コマンドを実行し、コマンドプロンプトが正常に表示できる文字コードへ一時的に変更する必要があります。
日本語入力を行う際は、忘れずにコマンドの実行をお願い致します。
2. データ登録の基本それでは早速DML文を使用して、テーブルにデータを登録してみましょう。テーブルにデータを登録するには、「INSERT」文を使用します。
書式は下記の通りです。
INSERT INTO テーブル名(列名1,列名2・・・) VALUES(値1,値2,・・・);
列名1に値1が、列名2に値2が入力されていきます。
「bookinfo」テーブルに次のようなデータを登録していきましょう。
| isbn | title | price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
INSERT INTO bookinfo(isbn,title,price) VALUES('00001','SQL入門テキスト',1050);
isbnとtitleは文字項目ですので、値が「'」（シングルクォート）で囲まれていることに注意してください。
コマンドプロンプトで日本語を入力したい場合には、Alt+半角/全角キーを押し、ローマ字入力に切り替えてください。
データが実際に登録されているかを確認してみましょう。テーブルの中身を検索するには「SELECT」文を使います。 「SELECT」文の詳細については後述しますので、まずは下記のDML文を入力してみてください。
SELECT * FROM bookinfo;
データが登録されているのがわかります。
尚、テーブルのすべての列にデータを登録する場合は、テーブル名の後の列名を省略することができます。
例えば先程の「INSERT」文は、下記のように書くこともできます。
INSERT INTO bookinfo VALUES('00001','SQL入門テキスト',1050);
列名を省略した場合は、VALUES句の値の順序は、「CREATE TABLE」文で定義した列の順番になることに注意しましょう。「bookinfo」テーブルに下記のデータを登録してください。
| isbn | title | price |
|---|---|---|
| 00002 | SQL基礎テキスト | 2300 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 2100 |
| 00005 | Java Struts基礎テキスト | 3800 |
３．本章のまとめ
本章ではデータベース、SQLの種類と簡単なデータ登録について学習しました。
章としては短いですが、本章で説明したデータ登録の方法は、MySQLに限らず、大多数のデータベースで使用できるものですので、ここでしっかり憶えてください。
データ検索
本章ではSQLの中心とも言える、データ検索の基本について解説します。
本章の目標
- データ検索の基礎を理解する
- データの抽出条件を理解する
- 検索結果の並べ替え方法をおぼえる
目次
- １．データ検索の基礎
- 1. 最も単純なデータ検索
- 2. 列名の別名
- ２．データの抽出条件
- 1. 比較演算子を使用したデータの抽出
- 2. あいまい検索
- 3. 論理演算子
- ３．データの並び替え
- 1. 単一列での並び替え
- 2. 複数列での並べ替え ４．本章のまとめ
１．データ検索の基礎
データの検索は、DML文の中でも最も使用するSQL文です。そもそも、データベースにデータを保管しただけでは、何の使い道もありません。 保管したデータをすばやく検索し、取り出せるようになって、初めてデータベースの意味があるのです。
データの検索には「SELECT」文を使用しますが、一口に検索といってもさまざまなパターンがあり、 その全てを覚えるには大変な労力がかかりますが、ここではまず最も基本となるデータ検索の方法をマスターしましょう。
1. 最も単純なデータ検索ここでは最も単純なデータ検索の方法を説明します。とはいっても、この方法は今までの学習で既に出てきています。
まずデータ検索の基本形の書式を説明します。
SELECT 列名1,列名2,・・・ FROM テーブル名;
これは指定したテーブルの全てのデータを検索し、列名で指定した列のデータを表示する方法です。
例えば、「bookinfo」テーブルのisbn番号を全て検索したい場合は、
SELECT isbn FROM bookinfo;
になります。
また、「bookinfo」テーブルの全てのデータを表示したい場合は、
SELECT isbn,title,price FROM bookinfo;
になるわけです。
ここで、思い出していただきたいのが、データ登録をした際に、登録されたデータを確認するために発行した「SELECT」文です。
SELECT * FROM bookinfo;
列名の部分が「*」（アスタリスク）になっていますね。
全ての列を表示する場合には、列名を一括して「*」で代用することができます。
2. 列名の別名先程の検索結果表示では、各列のタイトルが、列名そのままになっていました。 しかし、テーブルの内容をよく知っている場合は問題ないですが、知らない人が見た場合にわからない可能性があります。そこで、列名に別名をつけて表示してみましょう。
書式は下記の通りです。
SELECT
列名1 AS 別名,
列名2 AS 別名,
・・・・
FROM テーブル名
;
実際に「bookinfo」テーブルで試してみましょう。
SELECT
isbn AS ISBN番号,
title AS 書籍のタイトル,
price AS 価格
FROM bookinfo
;
テーブル内容がわからない人でも、これならわかりやすいですね。
２．データの抽出条件
前項では指定したテーブルの全てのデータを検索する方法を学習しました。ここでは、テーブルの中の特定のデータを取得する方法を学習していきます。
1. 比較演算子を使用したデータの抽出比較演算子を使用した「SELECT」文の構文は、下記の通りです。
SELECT 列名 FROM テーブル名 WHERE 検索対象の列名 比較演算子 値;
例えば「bookinfo」テーブルからISBN番号が「00003」のデータを抽出したい場合は、
SELECT * FROM bookinfo WHERE isbn='00003';
となります。
比較演算子には下記のようなものがあります。
| 演算子 | 使用例 | 意味 |
|---|---|---|
| = | a = b | aとbが等しい |
| <> | a <> b | aとbが等しくない |
| < | a < b | aがbより少ない |
| > | a > b | aがbより大きい |
| <= | a <= b | aがb以下 |
| >= | a >= b | aがb以上 |
例1）ISBN番号が「00003」以外
SELECT * FROM bookinfo WHERE isbn <> '00003';
例2）価格が2,000円以上
SELECT * FROM bookinfo WHERE price >= 2000;
例3）価格が3,800円未満
SELECT * FROM bookinfo WHERE price < 3800;
2. あいまい検索データ検索をするとき、WHERE句にLIKE演算子を使用すると、あいまい検索をすることができます。
書式は、次の3パターンがあります。
パターン1）指定した値で始まるデータ
SELECT 列名 FROM テーブル名 WHERE 検索対象の列名 LIKE '値%'
パターン2）指定した値で終わるデータ
SELECT 列名 FROM テーブル名 WHERE 検索対象の列名 LIKE '%値'
パターン3）指定した値を含むデータ
SELECT 列名 FROM テーブル名 WHERE 検索対象の列名 LIKE '%値%'
「bookinfo」で確認してみましょう。
パターン1）書籍名が「SQL」で始まるデータ
SELECT * FROM bookinfo WHERE title LIKE 'SQL%';
パターン2）書籍名が「テキスト」で終わるデータ
SELECT * FROM bookinfo WHERE title LIKE '%テキスト';
パターン3）書籍名に「入門」を含むデータ
SELECT * FROM bookinfo WHERE title LIKE '%入門%';
3. 論理演算子検索条件は、一つだけとは限りません。複数の検索条件を組み合わせて検索したいケースもあります。
複数の検索条件を組み合わせるには、論理演算子を使用します。ここでは、論理演算子のもっとも基本となる「AND」と「OR」を説明します。
まず基本文法です。
SELECT 列名 FROM テーブル名 WHERE 検索条件① AND（または）OR 検索条件② AND（または）OR ・・・;
「AND」はかつ、「OR」はもしくはを表します。
例えば「bookinfo」で、書籍名が「SQL」で始まり、かつ価格が2000円以下のものを検索する場合は、
となります。
また、書籍名が「SQL」で始まるか、もしくは価格が2000円以下のものを検索する場合は、
となります。
３．データの並び替え
データベースのデータは、指定した条件に従って並べ替え（ソート）ができます。
ここでは、データを並び替える方法を学習します。
1. 単一列での並び替えデータの並べ替えには「ORDER BY」句を使用します。
基本的な構文は下記の通りです。
SELECT 列名 FROM テーブル名 ORDER BY ソート対象の列名 ASC｜DESC;
ASCは昇順、DESCは降順を示します。
昇順は値の小さい順に並べ、降順は値の大きい順に並べます。
例えば「bookinfo」で価格の安い順に並べ替えるには、
SELECT * FROM bookinfo ORDER BY price ASC;
となります。
尚、「ORDER BY」句の昇順降順のデフォルトは昇順になっているので、昇順で並べ替える場合はASCを省略できます。
SELECT * FROM bookinfo ORDER BY price;
ISBN番号で降順に並べ替えるには下記のようになります。
SELECT * FROM bookinfo ORDER BY isbn DESC;
2. 複数列での並べ替えソート対象の列は複数指定することも可能です。特にソート対象の列に同一の値が合った場合、それらのデータの並べ替えはランダムです。
例えば「bookinfo」で価格の安い順に並べ替えた場合、先の例では下記のようにisbnが昇順で並べ替えられていましたが、これは保障されているものではありません。
場合によっては下記のように並べ替えられる場合があります。
このような場合、ソート対象の列を複数指定することによって、正しく並べ替えることができます。
ソート対象の列を複数指定するには、昇順降順の指定の後に、続けて第2基準の列を指定します。
SELECT 列名 FROM テーブル名 ORDER BY ソート対象の第1列名 ASC｜DESC, ソート対象の第2列名 ASC｜DESC,・・・;
例えば、「bookinfo」で価格の安い順、isbn番号の昇順に並べ替えるには、
SELECT * FROM bookinfo ORDER BY price ASC,isbn ASC;
もしくは
SELECT * FROM bookinfo ORDER BY price,isbn;
と指定します（前述しましたが、ASCは省略できます）。
また、価格の安い順、isbn番号の降順に並べ替えるには、
SELECT * FROM bookinfo ORDER BY price ASC,isbn DESC;
もしくは
SELECT * FROM bookinfo ORDER BY price,isbn DESC;
と指定します（ASCは省略できます）。
ソートの基準列が文字型である場合は、数字→記号→アルファベット→ひらがな→カタカナ→漢字の順にソートされます。
漢字内のソート順は、その漢字を表す文字コード順に並べ替えられます。
４．本章のまとめ
本章ではデータ検索の手法を解説してきました。
前述しましたが、データ検索はSQLの中でも最も多く使われるものであり、SQLの中心とも言えるものです。
また抽出条件の指定は、他のDML文でも使用されるものなので、その考え方と文法をしっかりと押さえておいてください。
データの更新と削除
本章ではテーブルに登録されたデータの更新（変更）及び削除の方法について解説します。
本章の目標
- データの更新方法をおぼえる
- データの削除方法をおぼえる
目次
１．データの更新
登録したデータを後から変更したいケースは多々あります。ここでは、テーブルに登録したデータを変更する方法を学習します。
1. データ更新の基本データの更新には、「UPDATE」文を使用します。
構文は下記の通りです。
UPDATE テーブル名 SET 更新対象列名=更新値,更新対象列名=更新値・・・;
例えば「bookinfo」で、書籍名を「Android基礎テキスト」、価格を2000円にしたい場合、
UPDATE bookinfo SET title='Android基礎テキスト',price=2000;
となります。
※ 但し、このままDDL文を発行すると全てのデータが更新されてしまいますので、この操作は実行しないでください。
| isbn | title | Price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00002 | SQL基礎テキスト | 2300 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 2100 |
| 00005 | Java Struts基礎テキスト | 3800 |
| isbn | title | Price |
|---|---|---|
| 00001 | Android基礎テキスト | 2000 |
| 00002 | Android基礎テキスト | 2000 |
| 00003 | Android基礎テキスト | 2000 |
| 00004 | Android基礎テキスト | 2000 |
| 00005 | Android基礎テキスト | 2000 |
更新するデータを特定する場合は、「SELECT」文同様、「WHERE」句を使用します。
UPDATE テーブル名 SET 更新対象列名=更新値,更新対象列名=更新値・・・WHERE 検索対象の列名 演算子 '値' ;
「bookinfo」を例にして説明しましょう。
ISBN番号が「00005」のデータの書籍名を「Android基礎テキスト」に、価格を2000円にする場合は、
UPDATE bookinfo SET title='Android基礎テキスト',price=2000 WHERE isbn='00005';
となります。
また、価格が2000円以上のデータの価格を3000円に変更したい場合は、
UPDATE bookinfo SET price=3000 WHERE price>=2000;
となります。
「bookinfo」テーブルを元の状態に戻してください。
| isbn | title | Price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00002 | SQL基礎テキスト | 3000 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 3000 |
| 00005 | Android基礎テキスト | 3000 |
| isbn | title | Price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00002 | SQL基礎テキスト | 2300 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 2100 |
| 00005 | Java Struts基礎テキスト | 3800 |
UPDATE bookinfo SET price=2100 WHERE isbn='00004';
UPDATE bookinfo SET title='Java Struts基礎テキスト',price=3800 WHERE isbn='00005';
２．データの削除
データ操作の基本の最後は、データの削除です。データの削除とは、行（レコード）のデータをテーブルから削除してしまうことです。ここではデータ削除の基本を学習します。
1. データ削除の基本データの削除には、「DELETE」文を使用します。
構文は下記の通りです。
DELETE FROM テーブル名;
例えば「bookinfo」からデータを削除したい場合、
DELETE FROM bookinfo;
となります。
※ 但し、このままDDL文を発行すると、「UPDATE」文の時と同様全てのデータが削除されてしまいますので、この操作は実行しないでください。
| isbn | title | Price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00002 | SQL基礎テキスト | 2300 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 2100 |
| 00005 | Java Struts基礎テキスト | 3800 |
| isbn | title | Price |
|---|
DELETE FROM テーブル名 WHERE 削除対象の列名 演算子 '値' ;
例えば、「bookinfo」からISBN番号が「00005」のデータを削除する場合は、
DELETE FROM bookinfo WHERE isbn ='00005';
となります。
「bookinfo」の状態を確認します。
SELECT * FROM bookinfo;
また、価格が2000円以上のデータを削除したい場合は、
DELETE FROM bookinfo WHERE price>=2000;
となります。
「bookinfo」の状態を確認します。
SELECT * FROM bookinfo;
「bookinfo」テーブルを元の状態に戻してください。
| isbn | title | Price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00003 | Java基礎テキスト | 1050 |
| isbn | title | Price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00002 | SQL基礎テキスト | 2300 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 2100 |
| 00005 | Java Struts基礎テキスト | 3800 |
INSERT INTO bookinfo(isbn,title,price) VALUES('00004','PHP基礎テキスト',2100);
INSERT INTO bookinfo(isbn,title,price) VALUES('00005','Java Struts基礎テキスト',3800);
３．本章のまとめ
本章ではデータの更新方法と削除方法を解説しました。
ポイントは条件指定がデータの抽出条件と同じ指定ができるということです。
データの更新と削除方法自体のバリエーションは多くないので、ここで基本文法をマスターしておいてください。
トランザクション処理
複数の処理をひとつの処理単位として実行するトランザクション処理について解説します。
本章の目標
- トランザクション処理の概念を理解する
- トランザクション処理の方法をおぼえる
目次
１．トランザクション処理
ここまでは、処理をひとつひとつ実行してきましたが、アプリケーションを開発していく中では、複数の処理を意味的には1つの処理として扱いたいケースが出てきます。 ここではトランザクション処理について学習していきます。
1. トランザクション処理の概念前述した通り、トランザクション処理とは複数の処理を意味的には1つの処理として扱うことです。例えば注文処理を考えてみましょう。 ユーザーが商品Aを注文したとします。この場合、注文テーブルに注文データを登録すると同時に、在庫データから商品Aの在庫をマイナス1する必要があります。 ここでトランザクション処理を行わない場合はどうなるでしょうか？
例えば注文テーブルにデータを登録し、在庫テーブルから在庫をマイナスする前に何らかのトラブルが発生してプログラムが終了してしまったとします。 すると、この場合は注文テーブルと在庫テーブルに不整合が発生してしまうことになります。
このような場合は、処理①で行った注文テーブルへのデータ追加を仮登録の状態にしておき、トラブルが派生した場合、処理①で行った処理を取り消す必要があります。 この処理を取り消すことをデータベースの世界ではロールバック（ROLLBACK）すると言います。
一方、何もトラブルが発生しなかった場合、処理①と処理②で行ったデータ操作はまだ仮登録の段階ですので、仮登録の状態を確定させなければなりません。 このように、処理を確定することを、コミット（COMMIT）といいます。
2. トランザクション処理の準備ここでは簡便のために、「bookinfo」のみで処理の流れを見ていきます。
但し、ここで注意点があります。MySQLでは、トランザクション処理を行う場合、トランザクション処理を行うためのテーブルであることを明示して作成する必要があります。 今まで使っていた「bookinfo」はトランザクション処理を行うためのテーブルであることを明示して作成していませんので、テーブルを作成しなおす必要があります。 トランザクション処理を行うことを明示して作成するには「CREATE TABLE」文に、「ENGINE=InnoDB」オブションを付けます。
CREATE TABLE bookinfo (
isbn VARCHAR(20) PRIMARY KEY,
title VARCHAR(100),
price INTEGER NOT NULL
) ENGINE=InnoDB
;「bookinfo」テーブルを元の状態に戻してください。
| isbn | title | Price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00002 | SQL基礎テキスト | 2300 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 2100 |
| 00005 | Java Struts基礎テキスト | 3800 |
CREATE TABLE bookinfo (
isbn VARCHAR(20) PRIMARY KEY,
title VARCHAR(100),
price INTEGER NOT NULL
) ENGINE=InnoDB
;
INSERT INTO bookinfo(isbn,title,price) VALUES('00001','SQL入門テキスト',1050);
INSERT INTO bookinfo(isbn,title,price) VALUES('00002','SQL基礎テキスト',2300);
INSERT INTO bookinfo(isbn,title,price) VALUES('00003','Java基礎テキスト',1050);
INSERT INTO bookinfo(isbn,title,price) VALUES('00004','PHP基礎テキスト',2100);
INSERT INTO bookinfo(isbn,title,price) VALUES('00005','Java Struts基礎テキスト',3800);
3. トランザクション処理の実行トランザクション処理を開始するには「BEGIN」コマンド、ロールバックするには「ROLLBACK」コマンド、コミットするには「COMMIT」コマンドを実行します。
処理の流れとしては、
BEGIN;
実際の処理
ROLLBACK;（または）COMMIT;
となります。
まずは「bookinfo」に下記データを追加し、これを取り消して見ましょう。
| isbn | title | price |
|---|---|---|
| 00006 | Android基礎テキスト | 2000 |
SELECT * FROM bookinfo;
では実際に、トランザクション処理を開始します。
BEGIN;
INSERT INTO bookinfo(isbn,title,price) VALUES('00006','Android基礎テキスト',2000);
この状態で、テーブルの中身を確認すると・・・
SELECT * FROM bookinfo;
データが追加されているのが確認できます。
ではデータの登録処理を取り消して、もう一度テーブルの中身を確認してみましょう。
ROLLBACK;
SELECT * FROM bookinfo;
登録されたデータが取り消されているのが分かります。
今度は、テーブルのデータを一度全て削除し、これを取り消してみましょう。
BEGIN;
DELETE FROM bookinfo;
SELECT * FROM bookinfo;
ROLLBACK;
SELECT * FROM bookinfo;
一度削除されたデータが元に戻っています。
では今度は、処理を確定してみましょう。
先程のように、新たにデータを追加して、今度は確定してみます。
BEGIN;
INSERT INTO bookinfo(isbn,title,price) VALUES('00006','Android基礎テキスト',2000);
SELECT * FROM bookinfo;
COMMIT;
SELECT * FROM bookinfo;
今度は処理が確定されています。 この状態で「ROLLBACK」をしてみましょう。
ROLLBACK;
SELECT * FROM bookinfo;
今度は処理が確定されていますので、新しく登録されたデータが残っているのが確認できます。
２．本章のまとめ
本章ではトランザクション処理の概念と実行方法を学習しました。
トランザクション処理はデータの一貫性を保証する重要な概念ですが、考え方や実行方法自体はさほど難しいものではないので、本講座をよく読み込んで、理解を深めてください。
SQLII基礎
HOME > データベース基礎 SQL基礎2 > データ検索の発展
本章ではデータ検索の発展的な使い方について解説します。
本章の目標
- NOT LIKE演算子をおぼえる
- BETWEEN、NOT BETWEEN演算子をおぼえる
- IN、NOT IN演算子をおぼえる
- IS NULL、IS NOT NULLをおぼえる
- 算術演算子を理解する
- 算術演算子の使い方をおぼえる
目次
- １．さまざまな演算子
- 1. NOT LIKE演算子
- 2. BETWEENとNOT BETWEEN演算子
- 3. INとNOT IN演算子
- 4. IS NULLとIS NOT NULL演算子
- ２．算術演算子
- 1. 検索結果を計算する
- 2. 計算結果を元に検索する
- ３．本章のまとめ
１．さまざまな演算子
SQL入門講座では基本的なデータ検索と、比較演算子、「LIKE」演算子、論理演算子の「AND」「OR」演算子を学習しました。
今回はさらに発展的なデータ検索を学習します。
1. NOT LIKE演算子「LIKE」の発展形です。まずは「LIKE」演算子の使用方法についておさらいしてみましょう。
「bookinfo」テーブルで書籍名が「SQL」で始まるデータを検索する場合は、
SELECT * FROM bookinfo WHERE title LIKE 'SQL%';
でしたね？
ではこれを「NOT LIKE」にするとどうなるでしょう？
SELECT * FROM bookinfo WHERE title NOT LIKE 'SQL%';
書籍名が「SQL」で始まらないもの、すなわち「SQL」で始まるデータ以外のデータが抽出されます。
2. BETWEENとNOT BETWEEN演算子ある値の範囲でデータを抽出する場合を考えてみましょう。例えば「bookinfo」テーブルで価格が1500円～2000円のデータを検索する場合、今まで学習してきたことを使うと、
SELECT * FROM bookinfo WHERE price >= 1500 AND price <= 2000;
となりますね。これを「BETWEEN」演算子を使って検索することができます。
「BETWEEN」演算子の構文は次のとおりです。
SELECT 列名 FROM テーブル名 WHERE 検索対象列名 BETWEEN 値1 AND 値2;
先程の「bookinfo」テーブルの例だと、
SELECT * FROM bookinfo WHERE price BETWEEN 1500 AND 2000;
となります。こちらの方が、比較演算子と論理演算子を組み合わせた場合より見た目にもすっきり書けます。
「BETWEEN」演算子にも「NOT LIKE」演算子のように、「NOT BETWEEN」演算子があります。
SELECT 列名 FROM テーブル名 WHERE 検索対象列名 NOT BETWEEN 値1 AND 値2;
先程の「bookinfo」の例に当てはめて見ると
SELECT * FROM bookinfo WHERE price NOT BETWEEN 1500 AND 2000;
となりますが、意味は、
SELECT * FROM bookinfo WHERE price < 1500 OR price > 2000;
と同じになります。
3. INとNOT IN演算子ある列の値をいくつか指定して検索する場合、比較演算子と論理演算子を組み合わせて使うと、
SELECT 列名 FROM テーブル名 WHERE 検索対象列=値1 OR 検索対象列=値2 OR・・・;
となりますね。
これを「IN」演算子で記述するともう少しすっきりします。
SELECT 列名 FROM テーブル名 WHERE 検索対象列 IN (値1,値2,・・・）;
例えば「bookinfo」テーブルからISBN番号が「00001」「00003」「00005」のデータを検索する場合は、
SELECT * FROM bookinfo WHERE isbn='00001' OR isbn='00003' OR isbn='00005';
と書くところを、
SELECT * FROM bookinfo WHERE isbn IN ('00001','00003','00005');
と書くことができます。
「IN」演算子にも、対になる「NOT IN」演算子があります。
SELECT 列名 FROM テーブル名 WHERE 検索対象列 NOT IN (値1,値2,・・・）;
これは、指定した値以外のデータが抽出されます。
「bookinfo」テーブルを例にとった場合、ISBN番号が「00001」「00003」「00005」以外のデータを検索する場合は、
SELECT * FROM bookinfo WHERE isbn NOT IN ('00001','00003','00005');
となります。
もうお分かりかと思いますが、これは
SELECT * FROM bookinfo WHERE isbn<>'00001' AND isbn<>'00003' AND isbn<>'00005';
と同じです。
4. IS NULLとIS NOT NULL演算子値がNULLかどうかを判定する演算子です。
現在「bookinfo」テーブルにはNULLを含むデータがありませんので、まずはNULLを含むデータを作成しましょう。
「bookinfo」テーブルに下記のデータを登録してください。
| isbn | title | price |
|---|---|---|
| 00007 | NULL | 2800 |
ではまず「IS NULL」演算子の構文です。
SELECT 列名 FROM テーブル名 WHERE 検索対象列 IS NULL;
検索対象列がNULLのデータを検索します。
「bookinfo」テーブルだと、先程追加したデータが検索されます。
SELECT * FROM bookinfo WHERE title IS NULL;
言うまでもありませんが、「IS NOT NULL」演算子はこの逆になります。
SELECT 列名 FROM テーブル名 WHERE 検索対象列 IS NOT NULL;
SELECT * FROM bookinfo WHERE title IS NOT NULL;
| NULL（ヌルまたはナルと読みます）とは何でしょうか？
NULLとは、テーブルの列に値が何も設定されていない状態を指します。 これは、0でも空文字（長さが0の文字）でもスペースでもありません。 MySQLクライアントでは、表示上「NULL」となります。 試しに「bookinfo」に、書籍タイトルが0、空文字、スペースのデータを登録して、違いを見てみましょう。 ※操作はしないでください。 
データを見てみます。 SELECT * FROM bookinfo WHERE ISBN BETWEEN '00007' AND '00010'; 
0も空文字もスペースも、（表示上見えなくても）値は設定されている状態なのですが、NULLは実際には値が何もない状態ですので、注意が必要です。 |
２．算術演算子
SQLでは、検索した結果に計算を加えたり、また計算結果を元に検索することができます。
ここでは計算に用いる基本的な算術演算子の使用方法について学習します。
1. 検索結果を計算するまずは検索結果に計算を加える方法です。
基本構文は下記の通りです。
SELECT 列名または数値 算術演算子 列名または数値 FROM テーブル名 (必要によりWHERE句)
算術演算子には下記のものがあります。
| 演算子 | 使用例 | 意味 |
|---|---|---|
| + | a+b | aとbを足す |
| - | a-b | aからbを引く |
| * | a*b | aとbをかける |
| / | a/b | aをbで割る |
| % | a％b | aをbで割った余り |
SELECT isbn,title,price*1.05 FROM bookinfo;
となります。
| isbn | title | price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 * 1.05 = 1102.50 |
| 00002 | SQL基礎テキスト | 2300 * 1.05 = 2415.00 |
| 00003 | Java基礎テキスト | 1050 * 1.05 = 1102.50 |
| 00004 | PHP基礎テキスト | 2100 * 1.05 = 2205.00 |
| 00005 | Java Struts基礎テキスト | 3800 * 1.05 = 3990.00 |
| 00006 | Android基礎テキスト | 2000 * 1.05 = 2100.00 |
| 00007 | NULL | 2800 * 1.05 = 2940.00 |
2. 計算結果を元に検索するSQLでは、WHERE句の中に算術演算子を記述することができます。
先程は計算結果に消費税計算を加えていましたが、今度は税込みの価格が2200円以上のデータを検索してみましょう。
SELECT * FROM bookinfo WHERE price*1.05 >= 2200;
| 「WHERE」句で計算した結果は、「SELECT」句の検索結果とは無関係なので注意しましょう。
計算結果表示させたい場合は、「SELECT」句でも計算する必要があります。 |
３．本章のまとめ
本章では抽出条件の使用する演算子に加え、算術演算子の使用方法について学習しました。
どれも使用頻度の高いものばかりですので、この章でしっかりとマスターしておいてください
SQLII基礎
HOME > データベース基礎 SQL基礎2 > 集計関数
本章では関数の基本的な考え方と集計関数の使い方について解説します。
本章の目標
- 関数の基本を理解する
- 集計関数の種類をおぼえる
- AVG関数の使い方をおぼえる
- SUM関数の使い方をおぼえる
- COUNT関数の使い方をおぼえる
- MAX関数の使い方をおぼえる
- MIN関数の使い方をおぼえる
目次
- １．関数の基本
- 1. 関数とは
- 2. 関数の書式
- 3. 関数の種類
- ２．集計関数の使い方
- 1. AVG関数
- 2. SUM関数
- 3. COUNT関数
- 4. MAX関数
- 5. MIN関数
- ３．本章のまとめ
１．関数の基本
まずは、関数の基本的な考え方と書式を学習します。
1. 関数とはSQLでは「関数」という仕組みを利用することができます。関数とは、あるまとまった処理を行い、その結果を返す道具のようなものです。
関数の中には、処理のための材料となる値を渡す必要があるものもあります。そのような材料のことを「引数」と呼びます。引数は関数によって1つだけのときもあれば、複数のときもあります。
また、関数から返ってくる実行結果を「戻り値」と呼びます。
2. 関数の書式関数の書式は下記の通りです。
引数がない場合：関数()
引数が1つの場合：関数(引数1)
引数が2つの場合：関数(引数1,引数2)
引数が3つ以上の場合：関数(引数1,引数2, 引数3,・・・)
引数がない場合は、関数名のうしろに括弧をつけるだけです。
引数がある場合は、関数名のうち路の括弧の中に引数を記述します。
引数が複数の場合は、括弧内の引数を「,」（カンマ）で区切って並べます。
3. 関数の種類本章で扱う集計関数には下記のようなものがあります。
| 関数名 | 機能 |
|---|---|
| AVG | 平均値を求める |
| SUM | 合計を求める |
| COUNT | 行の数を求める |
| MAX | 最高値を求める |
| MIN | 最低値を求める |
また、RDBMSによっては、独自の関数を実装しているケースが多々あります。
それらについては、各RDBMSのマニュアルを参照してください。
２．集計関数の使い方
集計関数とは、複数の値の集まりに対して計算を実行し、1つの値を返す関数です。具体的な事例を見ながら理解を深めましょう。
1. AVG関数AVG関数は、引数に指定した列の全ての行の値の平均値を算出し、戻り値として返します。
書式は下記の通りです。
SELECT AVG(列名) FROM テーブル名;
例えば「bookinfo」の価格の合計値を算出したいときは、
SELECT AVG(price) FROM bookinfo;
となります。つまりこの場合は、「price」列がAVG関数の引数、平均値が戻り値となるわけです。
2. SUM関数SUM関数は、引数に指定した列の全ての行の値の合計値を算出し、戻り値として返します。
書式は下記の通りです。
SELECT SUM(列名) FROM テーブル名;
「bookinfo」の価格の合計値を算出したいときは、
SELECT SUM(price) FROM bookinfo;
となります。
3. COUNT関数COUNT関数は、引数に指定した列の行数を求め、戻り値として返します。
書式は下記の通りです。
SELECT COUNT(列名) FROM テーブル名;
「bookinfo」のisbn列の行数をもとめる場合、
SELECT COUNT(isbn) FROM bookinfo;
となります。
また、COUNT関数は「*」（アスタリスク）を引数に指定することができます。
SELECT count(*) FROM bookinfo;
4. MAX関数MAX関数は、引数に指定した列の中の最高値を戻り値として返します。
書式は下記の通りです。
SELECT MAX(列名) FROM テーブル名;
例えば「bookinfo」の価格の最高値を取得したい場合は、下記のようになります。
SELECT MAX(price) FROM bookinfo;
5. MIN関数MIN関数は、MAX関数とは逆に、引数に指定した列の中の最低値を戻り値として返します。
書式は下記の通りです。
SELECT MIN(列名) FROM テーブル名;
例えば「bookinfo」の価格の最低値を習得したい場合は、下記のようになります。
SELECT MIN(price) FROM bookinfo;
| 上記の例のように、MIN関数では同一の最低値が複数あっても戻り値は一つ（最低値）です。これは、MAX関数も同様です。 |
３．本章のまとめ
本章では関数の基本概念と集計関数について学習しました。
集計関数は、次章で解説する「GROUP BY」句と組み合わせて使われるケースが多いので、その考え方、使用方法をマスターしておいてください。
SQLII基礎
HOME > データベース基礎 SQL基礎2 > GROUP BY句とHAVING句
本章では特定列をグループ化し、集計できる「GROUP BY」句と、その抽出条件を指定する「HAVING」句について解説します。
本章の目標
- グループ化の考え方を理解する
- 「GROUP BY」句の使い方をおぼえる
- 「GROUP BY」句と集計関数を組み合わせた使い方をおぼえる
- 「HAVING」句の使い方をおぼえる
目次
- １．「GROUP BY」句の基本
- 1. 学習の準備
- 2. 「GROUP BY」句の基本
- ２．「GROUP BY」句と集計関数
- 1. 「GROUP BY」句とAVG関数
- 2. 「GROUP BY」句とCOUNT関数
- 3. 「GROUP BY」句とSUM関数
- 4. 「GROUP BY」句とMAX関数、MIN関数
- ３．「GROUP BY」句と「HAVING」句
- 1. 「HAVING」句の使い方
- ４．本章のまとめ
１．「GROUP BY」句の基本
データグループ化の基本的な考え方を学習します。
1. 学習の準備まずは、「GROUP BY」句を学習するための準備として、新しいテーブルを作成し、データを登録しましょう。
| * 新規テーブルの作成
下表のテーブル「orderinfo」テーブルを作成してください。 テーブル名：「orderinfo」
orderno INTEGER AUTO_INCREMENT PRIMARY KEY, user VARCHAR(20), isbn VARCHAR(20), quantity INTEGER, date date )ENGINE=InnoDB; * 新規テーブルへのデータ登録 次に下記のデータを登録してください。
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15'); * データの確認 データの中身を確認してみましょう。 SELECT * FROM orderinfo; 
|
2. 「GROUP BY」句の基本「GROUP BY句」はある特定の列をキーに（グループキーといいます）、データをグループ化するための句です。
書式は下記のようになります。
SELECT グループ化キー FROM テーブル名 GROUP BY グループ化キー;
先程作成した「orderinfo」で見てみましょう。
「orderinfo」のユーザー名には、複数のユーザーが存在しますが、重複しているデータもあります。
これをユーザー毎にグループ化してみましょう。
SELECT user FROM orderinfo GROUP BY user;
user列がグループ化され重複が排除されたユーザー名の一覧が取得できました。
| 「GROUP BY」句と似たような働きをする句に、「DISTINCT」句があります。
「DISTINCT」句は、検索結果の重複を排除する機能があります。 書式は下記のようになります。 SELECT DISTINCT 列名 FROM テーブル名; 先程「orderinfo」をuser列でグルーピング化したように、user列の重複を排除するには SELECT DISTINCT user FROM orderinfo; となります。 
但し「DISTINCT」句は重複を排除するためのものであり、「GROUP BY」句のように、集計関数と同時に使ってグループごとの集計を取るような使い方はできません。 |
２．「GROUP BY」句と集計関数
「GROUP BY」句を単独で使うことは少なく、集計関数と同時に用いられるのが一般的です。ここでは、「GROUP BY」句と集計関数を組み合わせた使い方を解説します。
1. 「GROUP BY」句とAVG関数AVG関数は前の章で解説したように平均値を取る関数でしたが、「GROUP BY」句と組み合わせると、グループ化キー毎の平均値を出すことができます。
書式は下記のようになります。
SELECT グループ化キー,AVG(列名) FROM テーブル名 GROUP BY グループ化キー;
例えば「orderinfo」で、ユーザー毎の平均注文数を出す場合は下記のようになります。
SELECT user,AVG(quantity) FROM orderinfo GROUP BY user;
2. 「GROUP BY」句とCOUNT関数COUNT関数もAVG関数同様、「GROUP BY」句と組み合わせることで、グループ化キーごとの行数を算出することができます。
書式は下記のようになります。
SELECT グループ化キー,COUNT(列名) FROM テーブル名 GROUP BY グループ化キー;
「orderinfo」で、ユーザー毎の行数（この場合は注文回数）を出す場合は下記のようになります。
SELECT user,COUNT(orderno) FROM orderinfo GROUP BY user;
3. 「GROUP BY」句とSUM関数次はSUM関数と「GROUP BY」句の組み合わせを見ていきます。
書式は下記のようになります。
SELECT グループ化キー,COUNT(列名) FROM テーブル名 GROUP BY グループ化キー;
今度は「orderinfo」で、ISBN番号ごとの注文数の合計を出して見ましょう。
DML文は下記のようになります。
SELECT isbn,SUM(quantity) FROM orderinfo GROUP BY isbn;
4. 「GROUP BY」句とMAX関数、MIN関数今回はMAX関数、MIN関数と「GROUP BY」句の組み合わせを同時に解説します。
書式はMAX関数の場合は、
SELECT グループ化キー,MAX(列名) FROM テーブル名 GROUP BY グループ化キー;
MIN関数の場合は、
SELECT グループ化キー,MIN(列名) FROM テーブル名 GROUP BY グループ化キー;
のようになりますが、同じグループ化キーで集計する場合、集計関数は複数指定することができます。
例えば「orderinfo」で、ユーザーごとに最初の注文日と最終注文日を調べてみましょう。
最初の注文日はdate列の最小値を、最終注文日は同じくdate列の最大値を取得することで算出できます。
DML文は下記のようになります。
SELECT user,MIN(date),MAX(date) FROM orderinfo GROUP BY user;
| グループ化キーは複数指定することもできます。
下記のようになります。 SELECT グループ化キー1, グループ化キー2・・・,集計関数(列名) FROM テーブル名 GROUP BY グループ化キー1, グループ化キー2・・・; 例えば「orderinfo」で、ユーザーごと、ISBN番号ごとの注文数合計をとる場合は、 SELECT user,isbn,SUM(quantity) FROM orderinfo GROUP BY user,isbn; となります。 
また、グループ化キーは「SELECT」句に必ず含めなければならないわけではありません。 例えば下記のように、ユーザーごとの注文数量合計を出す場合に、ユーザー名を出さないように書いても、SQLの構文上はエラーにはなりません。 SELECT SUM(quantity) FROM orderinfo GROUP BY user; 
但し結果を見てもわかるように、どのデータの集計結果かはわからないので、特殊な使用例と言えるでしょう。 |
３．「GROUP BY」句と「HAVING」句
「GROUP BY」句でグループ化したデータに対して、条件を指定してデータを絞り込む際に使用する「HAVING」句について説明します。
1. 「HAVING」句の使い方「HAVING」句の使い方は、「WHERE」句に似ていますが、SQLでは「WHERE」句の中で集計関数を使えないルールになっていますので、 そのような場合は「HAVING」句を使用します。
「HAVING」句の基本的な書式は下記のようになります。
SELECT グループ化キー,集計関数(列名) FROM テーブル名 GROUP BY グループ化キー
HAVING 集計関数(列名) 演算子 条件;
例えば「orderinfo」で、ISBN番号ごとの注文数の合計を出し、このうち注文数が10以上のデータを抽出する場合を考えてみます。
DML文は下記のようになります。
SELECT isbn,SUM(quantity) FROM orderinfo GROUP BY isbn HAVING SUM(quantity) >=10;
「HAVING」句では、下記の図のように、一旦集計されたデータを基に抽出条件が評価されます。
４．本章のまとめ
本章では「GROUP BY」句の使い方と集計関数との組み合わせ、「HAVING」句の使い方について解説しました。
特に「GROUP BY」句と集計関数を組み合わせて使用するケースは非常に多いので、本章を何度も見直して確実に習得しておきましょう。
SQLII基礎
HOME > データベース基礎 SQL基礎2 > その他の基本的な関数
第2章では関数の基本と集計関数を説明しましたが、本章ではそれ以外によく使われる関数について解説します。
本章の目標
- 文字列関数の基本をおぼえる
- 日付関数の基本をおぼえる
目次
- １．文字列関数
- 1. CHAR_LENGTH関数
- 2. CONCAT関数
- 3. SUBSTRING関数
- ２．日付関数
- 1. CURDATE関数
- 2. YEAR関数、MONTH関数、DAY関数
- 3. DAYNAME関数
- ３．本章のまとめ
１．文字列関数
ここでは、文字列を処理するための関数をいくつか取り上げます。
1. CHAR_LENGTH関数文字列の長さを取得する関数です。
書式は下記のようになります。
SELECT CHAR_LENGTH(列名) FROM テーブル名;
「bookinfo」テーブルの書籍タイトルの文字列の長さを取得したい場合、下記のようになります。
SELECT CHAR_LENGTH(title) FROM bookinfo;
| 最後の行は値がないので、表示上は「NULL」となります。 |
2. CONCAT関数文字列を連結する関数です。
書式は下記のようになります。
SELECT CONCAT(文字列A,文字列B) FROM テーブル名;
「bookinfo」テーブルの書籍タイトルに、「書籍名は、」と付け加えたい場合は下記のようになります。
SELECT CONCAT('書籍名は、',title) FROM bookinfo;
| 最後の行は値がないので、文字列連結の対象外になります。 |
「bookinfo」テーブルの書籍タイトルに、「書籍名は、」と「です。」付け加えたい場合は下記のようになります。
SELECT CONCAT('書籍名は、',title,'です。') FROM bookinfo;
またテーブルの列を連結することも出来ます。
「bookinfo」でISBN番号と書籍名を連結する場合は、
SELECT CONCAT(isbn,title) FROM bookinfo;
のようになります。
3. SUBSTRING関数部分文字列を取り出す関数です。
書式は下記のようになります。
SELECT SUBSTRING(文字列,取り出し開始位置,取り出す文字列数) FROM テーブル名;
「bookinfo」テーブルの書籍タイトルの1文字目から3文字取り出したい場合は、
SELECT SUBSTRING(title,1,3) FROM bookinfo;
第3引数を省略すると、取り出し開始位置から、最後まで取り出します。
SELECT SUBSTRING(title,4) FROM bookinfo;
上記の指定は、書籍タイトルの4文字目から最後までを取り出しています。
| 最後の行は値がないので、文字列抽出の対象外になります。 |
２．日付関数
ここでは、日付を処理するための関数をいくつか取り上げます。
1. CURDATE関数現在の日付を取得する関数です。
この関数は、引数をとりません。下記のように記述すると、現在の日付を取得できます。
SELECT CURDATE();
※今日が2010年9月13日の場合です。
| MySQLのSELECT文では、参照するテーブルがなく、単にデータを表示したいような場合は「FROM」句を省略できます。
例えば、単に「A」という文字列を表示したい場合は、 SELECT 'A'; と記述できます。 
|
2. YEAR関数、MONTH関数、DAY関数それぞれ日付から年、月、日を取得する関数です。
書式は下記のようになります。
SELECT YEAR(列名) FROM テーブル名;
SELECT MONTH(列名) FROM テーブル名;
SELECT DAY(列名) FROM テーブル名;
「orderinfo」の注文日からそれぞれ年、月、日を取得してみましょう。
まず年です。
SELECT YEAR(date) FROM orderinfo;
次は月を取得してみましょう。
SELECT MONTH(date) FROM orderinfo;
最期に日を取得してみましょう。
SELECT DAY(date) FROM orderinfo;
3. DAYNAME関数引数で与えられた日付の曜日を取得する関数です。
書式は下記のようになります。
SELECT DAYNAME(列名) FROM テーブル名;
「orderinfo」テーブルの注文日付の曜日を取得する場合は、
SELECT DAYNAME(date) FROM orderinfo;
のようになります。
その他の関数には、下記のようなものがあります。
|
３．本章のまとめ
本章で解説した関数は全体のごく一部ですが、まずは関数がどんな機能を持ち、どういった方法で使用するかを把握してください。 関数を全て記憶する必要はありませんが、マニュアルをみて使える程度には理解する必要があります。 関数を使うだけで、複雑なSQLを簡略化したり、開発工数を削減できたりしますので、積極的に使用することをお勧めします。
SQLII基礎
HOME > データベース基礎 SQL基礎2 > リレーショナルデータベースの基本概念
本章では、リレーショナルデータベースの基本的な概念について解説します。
本章の目標
- リレーショナルデータベースの概念を理解する
- 外部キー制約の考え方を理解する
目次
- １．リレーショナルデータベースとは
- 1. リレーショナルデータベースとは
- 2. テーブルの概念
- 3. リレーショナルの意味
- ２．外部キー制約
- 1. 外部キー制約の基本
- 2. 親テーブルデータの変更と削除
- ３．本章のまとめ
１．リレーショナルデータベースとは
まずリレーショナルデータベースの考え方を押さえます。
1. リレーショナルデータベースとはリレーショナルデータベースは、1970年にIBM社のEdgar F. Codd博士によって提唱されたリレーショナルデータモデルの理論に従ったデータ管理方式の一つです。
1件のデータ（レコード）を複数の項目（フィールド）の集合として表現し、データの集合をテーブルと呼ばれる表で表す方式で、 ID番号や名前などのキーとなるデータを利用して、データの結合や抽出を容易に行なうことができるのが特徴です。
2. テーブルの概念いままで見てきた通り、列（フィールド）が集まって行（レコード）を形成し、これが集まってテーブルとなります。
「bookinfo」を参考にすると、「isbn」「title」「price」がそれぞれフィールドを表し、これが集まって1件のレコードとなり、さらにレコードの集まりがテーブル「bookinfo」となります。
リレーショナルデータベースでは、テーブルがデータの入れ物の基本単位となります。
表（bookinfo）
3. リレーショナルの意味これまでは、単一のテーブルを個別に扱ってきましたが、この個々のテーブルを互いに関係（リレーション）付け、 複数のテーブルを連携して管理する方式が、リレーショナルデータベースなのです。
今まで出てきた「bookinfo」と「orderinfo」を関連付けてみましょう。
※簡略のために、データを一部だけ表示しています。
この2つの表は「isbn」で関連付けられています。
つまり、「orderinfo」の注文番号「1」で注文された書籍は「SQL入門テキスト」、注文番号「5」で注文された書籍は「SQL基礎テキスト」ということになります。
この2つのテーブルは下記のように1つにまとめることもできます。
| orderno | user | isbn | title | price | quantity | date |
|---|---|---|---|---|---|---|
| 1 | kanda | 00001 | SQL入門テキスト | 1050 | 1 | 2010-07-01 |
| 5 | kanda | 00002 | SQL基礎テキスト | 2300 | 1 | 2010-08-22 |
しかしこれを「bookinfo」と「orderinfo」に分けることによって、「SQL入門テキスト」とpriceの1050円というデータは「bookinfo」の1件で済みます。
これは、データの容量を抑えるとともに、データに変更があった場合の保守性の観点からも有用です。
「bookinfo」を例にとれば、仮に書籍名「SQL入門テキスト」が変更になった場合でも、1万件のデータを更新せずに済みます。
このように冗長な表を複数の表に分割して冗長さをなくすことを「正規化」と呼びます。
正規化の手法は本テキストの範囲を超えますので説明は省略しますが、興味のある方は専門の書籍等を参照してください。
２．外部キー制約
1. 外部キー制約の基本リレーショナルデータベースにおいては、各表はデータを参照される側と参照する側で親子関係が成り立っています。 このような場合、参照される側の表を親テーブル（またはマスターテーブル）、参照する側の表を子テーブル（またはトランザクションテーブル）と呼びます。 例えば「bookinfo」と「orderinfo」の関係で言えば、「bookinfo」が親テーブル、「orderinfo」が子テーブルとなります。
ここで問題なのが、親テーブルに無いデータを子テーブルに追加すると、親テーブルと子テーブルの整合性が崩れてしまいます。
「bookinfo」と「orderinfo」を例にとると、現在「bookinfo」のデータは下記のようになっています。
テーブル（bookinfo）
| isbn | title | price |
|---|---|---|
| 00001 | SQL入門テキスト | 1050 |
| 00002 | SQL基礎テキスト | 2300 |
| 00003 | Java基礎テキスト | 1050 |
| 00004 | PHP基礎テキスト | 2100 |
| 00005 | Java Struts基礎テキスト | 3800 |
| 00006 | Android基礎テキスト | 2000 |
| 00007 | NULL | 2800 |
このような不整合を防ぐためにリレーショナルデータベースの表には、外部キー制約（または参照整合性制約）という制約をつけることが出来ます。
外部キー制約は、表作成時に参照する側の表につけます。また、参照先の列は、そのテーブルの主キーである必要があります。 これは主キーが重複を許さず、「NOT NULL」であるというルールがあるためです。 参照先の列に重複したデータがあるとどのデータと結びつけてよいかわかりませんし、NULLのデータがあると参照できないためです。
外部キー制約を付ける場合のDDL文の書式は下記の通りです。
CREATE TABLE テーブル名 (
列名 データ型 属性,
列名 データ型 属性,
列名 データ型 属性,
・・・・,
FOREIGN KEY (参照元の列名) REFERENCES 参照先のテーブル名(参照先の列名)
);
| * 「orderinfo」テーブルの再作成
下表のテーブル「orderinfo」テーブルのisbn列に、「bookinfo」のisbn列を参照する外部キー制約をつけてテーブルを再作成してください。 DROP TABLE orderinfo; CREATE TABLE orderinfo ( orderno INTEGER AUTO_INCREMENT PRIMARY KEY, user VARCHAR(20), isbn VARCHAR(20), quantity INTEGER, date date, FOREIGN KEY (isbn) REFERENCES bookinfo(isbn) )ENGINE=InnoDB; 
* 「orderinfo」テーブルのへのデータ登録 注文データを再登録してください。 INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00001',1,'2010-07-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15'); * データの確認 データの中身を確認してみましょう。 SELECT * FROM orderinfo; 
|
「orderinfo」に、次のデータを登録するDDL文を発行してみます。
| orderno | user | isbn | quantity | date |
|---|---|---|---|---|
| 31 | kanda | 0008 | 1 | 2010-07-01 |
上の画面のように、エラーになったと思います。これは、参照先「bookinfo」の「isbn」列に「00008」というデータがないため、外部キー制約違反のエラーになったことを表しています。
また、親テーブルのデータも、勝手に変更や削除してしまうと子テーブルとの間でやはり不整合が起きます。
「bookinfo」と「orderinfo」の関係で言うと、「orderinfo」のデータにISBN番号が「00001」のデータがあるのに、「bookinfo」の「00001」のデータを削除したり、 番号を変更してしまうと、「bookinfo」と「orderinfo」が不整合な状態となってしまいます。
このような場合も、外部キー制約をつけておくと、整合性を保つように変更や削除ができなくなります。
試しに「bookinfo」のデータを変更、削除してみましょう。
まずは「bookinfo」のISBN番号「00001」を「00008」に変更する、下記のDDL文を発行してみてください。
UPDATE bookinfo SET isbn='00008' WHERE isbn='00001';
やはり外部キー制約の違反でエラーになったと思います。
次に同じデータを削除してみましょう。
DELETE FROM bookinfo WHERE isbn='00001';
同じくエラーになりましたね。
このように外部キー制約をつけておくと、親テーブルと子テーブルの整合性が保証されるのです。
2. 親テーブルデータの変更と削除外部キー制約が付いていた場合でも、親テーブルのデータを変更したり削除したりといったケースは出てきます。
このような場合、親テーブルと子テーブルの整合性を保ったまま行うには、ひとつは下記の手順で変更できます。
* データを変更する場合（「bookinfo」のISBN番号「00007」を「00008」に変更する場合）
※簡略のために、データを一部だけ表示しています。
* データを削除する場合（「bookinfo」のISBN番号「00007」を削除する場合）
上記のような手順を踏めば、データの整合性を保ったまま、親テーブルデータの変更や削除が行なえますが、非常に手間がかかります。
このような場合、外部キー制約を設定するときに「ON UPDATE CASCADE」オプションと「ON DELETE CASCADE」オプションを付けておくと便利です。 「ON UPDATE CASCADE」オプションを付けておくと、親テーブルのデータが変更されたときに、同じキーを持つ子テーブルのデータも自動的に変更されます。 また、「ON DELETE CASCADE」オプションを付けておくと、親テーブルが削除されたときに、同じキーを持つ子テーブルのデータも自動的に削除されます。
書式は下記の通りです。
CREATE TABLE テーブル名 (
列名 データ型 属性,
列名 データ型 属性,
列名 データ型 属性,
・・・・,
FOREIGN KEY (参照元の列名) REFERENCES 参照先のテーブル名(参照先の列名)
ON UPDATE CASCADE ON DELETE CASCADE
);
| * 「orderinfo」テーブルの再作成
「orderinfo」テーブルの外部キー制約に、「ON UPDATE CASCADE」オプションと「ON DELETE CASCADE」オプションをつけてテーブルを再作成してください。 DROP TABLE orderinfo; CREATE TABLE orderinfo ( orderno INTEGER AUTO_INCREMENT PRIMARY KEY, user VARCHAR(20), isbn VARCHAR(20), quantity INTEGER, date date, FOREIGN KEY (isbn) REFERENCES bookinfo(isbn) ON UPDATE CASCADE ON DELETE CASCADE )ENGINE=InnoDB; 
* 「orderinfo」テーブルのへのデータ再登録 注文データを再登録してください。 INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00001',1,'2010-07-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15'); * データの確認 データの中身を確認してみましょう。 SELECT * FROM orderinfo; 
|
まず「orderinfo」のISBN番号「00007」と「00008」のデータを検索し、現在の状態を確認しておきましょう。
SELECT * FROM orderinfo WHERE isbn IN ('00007', '00008');
この状態で、「bookinfo」のISBN番号「00007」のデータを番号「00008」に変更し、再度「orderinfo」を確認してみましょう。
UPDATE bookinfo SET isbn='00008' WHERE isbn = '00007';
SELECT * FROM orderinfo WHERE isbn IN ('00007', '00008');
「orderinfo」のデータも、変更されていることが確認できます。
次に、先程変更した「bookinfo」のISBN番号「00008」のデータを削除し、「orderinfo」のデータを確認してみます。
DELETE FROM bookinfo WHERE isbn = '00008';
SELECT * FROM orderinfo WHERE isbn IN ('00007', '00008');
データが削除されていることがわかります。
※「Empty set」は検索結果が0件であることを意味します。
３．本章のまとめ
本章ではリレーショナルデータベースの基本概念と外部キー制約について解説しました。
「SQL入門講座テキスト」にも記述しましたが、リレーショナルデータベースは、現在使用されているデータベースの中で主流となっており、 本章で解説したことはその中心的な概念となっていますので、必ず理解しておくようにしてください。
SQLII基礎
HOME > データベース基礎 SQL基礎2 > テーブル結合
本章では、実際に複数のテーブルを連携させるテーブル結合のSQLについて解説します。
本章の目標
- 内部結合の概念を理解する
- 内部結合の使い方をおぼえる
- 外部結合の概念を理解する
- 外部結合の使い方をおぼえる
目次
- １．テーブル結合の基本
- 1. 内部結合
- ２．表結合の発展
- 1. 学習の準備
- 2. 外部結合の考え方
- 3. 左外部結合
- 4. 右外部結合
- 5. 結合と「WHERE」句の組み合わせ
- 6. 結合と算術演算子の組み合わせ
- 7. 結合と「ORDER BY」句の組み合わせ
- ３．本章のまとめ
１．テーブル結合の基本
まずは基本的なテーブル結合の考え方と使用方法を説明します。
1. 内部結合前章ではリレーショナルデータベースと外部キー制約の概念を解説しましたが、テーブルを分割しただけで実際にデータとして連携して使用できなければ意味がありません。
このような場合、SQLを使ってテーブル同士を結合し、擬似的にひとつの表のようにして取り出すことができます。
これを表（テーブル）の結合（またはジョイン）といいます。
「bookinfo」と「orderinfo」を例に説明します。前章では表の正規化に触れましたが、テーブルの結合はこの逆をイメージすると分かりやすいでしょう。
現在「bookinfo」と「orderinfo」はそれぞれのisbn列で関連付けることができます。
つまり「bookinfo」のisbn列と「orderinfo」のisbn列で、同じデータを持つもの同士を結合することができるわけです。 そして結合されたテーブルを、あたかもひとつのテーブルのように扱えるのです。
テーブルの結合にはSELECT文を使います。
書式は下記のようになります。
SELECT テーブル名1もしくは2.列名,・・・ FROM テーブル名1 INNER JOIN テーブル名2
ON テーブル名1.列名1 = テーブル名2.列名2;
「FROM」句で連携させたいテーブル名1とテーブル名2の名前を「INNER JOIN」で結びます。
その後「ON」に続けて、テーブル1のどの列とテーブル2のどの列を基準に結合するかを指定します。
「SELECT」句では、表示させたいテーブルの列名を、「テーブル名.列名」という記述で表現します。
「bookinfo」と「orderinfo」を結合して表示してみましょう。
まず、両方のテーブルをisbn列を基準に結合し、全列を表示してみます。
SELECT * FROM bookinfo INNER JOIN orderinfo ON bookinfo.isbn=orderinfo.isbn;
両方のテーブルのデータが結合され、「bookinfo」テーブルのデータが左側に、「orderinfo」テーブルのデータが右側に表示されます。
このように、「SELECT」句に列を指定しない場合、左側に「INNER JOIN」で指定したはじめのテーブルの内容が表示され、右側に2番目に指定したテーブルの内容が表示されます。
では、下の表のようなイメージ（列の並び順を変更）で、データを取得してみましょう。
| orderno | user | isbn | title | price | quantity | date |
|---|---|---|---|---|---|---|
| 1 | kanda | 00001 | SQL入門テキスト | 1050 | 1 | 2010-07-01 |
| 5 | kanda | 00002 | SQL基礎テキスト | 2300 | 1 | 2010-08-22 |
SELECT orderinfo.orderno,orderinfo.user,bookinfo.isbn,bookinfo.title,
bookinfo.price,orderinfo.quantity,orderinfo.date
FROM bookinfo INNER JOIN orderinfo ON bookinfo.isbn=orderinfo.isbn;
表記のように「テーブル名.列名」のように表記します。
isbn列は「orderinfo」にも「bookinfo」にもありますが、今回の場合はどちらの列を使っても同じデータが取得
できるので、どちらを指定しても同じです。例では「bookinfo」のisbn列を使用しています。
この形が基本形ですが、テーブル名に別名をつけることで、SQL文をもう少し短くすることもできます。
SELECT B.orderno,B.user,A.isbn,A.title,A.price,B.quantity,B.date
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn;
この例では、「bookinfo」に「A」、「orderinfo」に「B」という別名を付けています。
今回は「bookinfo」のisbn列と「orderinfo」のisbn列で、データが一致するものだけを結合しました。 このように、結合対象のテーブル同士の結合のキーとなる列で、データが同じものだけを結合することを特に「内部結合（もしくはINNER JOIN）」と呼びます。
| テーブルを結合する場合の「SELECT」句では、「テーブル名.列名」という指定がルールになっていますが、片方のテーブルにしか存在しない列はテーブル名を省略することができます。
例えば「bookinfo」のtitle列とprice列、「orderinfo」のorderno列・user列・quantity列・date列は片方の列にしか存在しませんので、DDL文は下記のように書き換えることができます。 SELECT orderno,user,A.isbn,title,price,quantity,date FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn; |
２．表結合の発展
ここでは、テーブル結合の発展的な使い方として、外部結合について解説します。
1. 学習の準備まずは、外部結合を学習するための準備をしましょう。
| * 「bookinfo」テーブルへの新規データ登録
「bookinfo」テーブルに下記のデータを登録してください。
INSERT INTO bookinfo(isbn,title,price) VALUES('00008','C++入門テキスト',2100); * 新規テーブルの作成 下表のテーブル「userinfo」を作成してください。 テーブル名「userinfo」
user VARCHAR(20) PRIMARY KEY, password VARCHAR(20), email VARCHAR(100) )ENGINE=InnoDB; * 新規テーブルへのデータ登録 「userinfo」下記のデータを登録してください。
INSERT INTO userinfo(user,password,email) VALUES('ebisu','ebisupass','ebisu@kanda-it-school.com'); INSERT INTO userinfo(user,password,email) VALUES('kanda','kandapass','kanda@kanda-it-school.com'); INSERT INTO userinfo(user,password,email) VALUES('meguro','meguropass','meguro@kanda-it-school.com'); INSERT INTO userinfo(user,password,email) VALUES('osaki','osakipass','osaki@kanda-it-school.com'); INSERT INTO userinfo(user,password,email) VALUES('shibuya','shibuyapass','shibuya@kanda-it-school.com'); INSERT INTO userinfo(user,password,email) VALUES('sugamo','sugamopass','sugamo@kanda-it-school.com'); INSERT INTO userinfo(user,password,email) VALUES('tamachi','tamachipass','tamachi@kanda-it-school.com'); INSERT INTO userinfo(user,password,email) VALUES('ueno','uenopass','ueno@kanda-it-school.com'); * データの確認 データを確認しておきましょう。 SELECT * FROM userinfo; 
* 「orderinfo」テーブルの再作成 「orderinfo」テーブルに「userinfo」テーブルへの外部キー制約を設定するためにデータとテーブルを再作成します。 外部キー制約は「orderinfo」テーブルのuser列に設定し、参照先は「userinfo」テーブルのuser列とします。 外部キー制約には、「ON UPDATE CASCADE」オプションと「ON DELETE CASCADE」オプションをつけてください。 DROP TABLE orderinfo; CREATE TABLE orderinfo ( orderno INTEGER AUTO_INCREMENT PRIMARY KEY, user VARCHAR(20), isbn VARCHAR(20), quantity INTEGER, date date, FOREIGN KEY (isbn) REFERENCES bookinfo(isbn) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (user) REFERENCES userinfo(user) ON UPDATE CASCADE ON DELETE CASCADE )ENGINE=InnoDB; DROP TABLE orderinfo; * 「orderinfo」テーブルのへのデータ再登録 「orderinfo」のデータを再登録しておきましょう。 INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00001',1,'2010-07-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14'); INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15'); |
2. 外部結合の考え方前節では、内部結合について解説しました。内部結合の考え方は、結合キーとなる列に同じデータがある場合だけデータを抽出するというものでした。
これに対して外部結合は、内部結合に加えて、片方のテーブルにしかないデータも取得することができます。
「bookinfo」と「orderinfo」の関係で見ていくと、先程「bookinfo」にISBN番号「00008」のデータを登録しましたが、「orderinfo」にはISBN番号「00008」の注文データが存在しません。
これを内部結合で結合すると、「bookinfo」のISBN番号「00008」のデータは抽出されません。
しかし外部結合を使うと、「bookinfo」のISBN番号「00008」のデータも取得できます。
3. 左外部結合まずは左外部結合の書式を見てみましょう。
SELECT テーブル名1もしくは2.列名,・・・ FROM テーブル名1 LEFT JOIN テーブル名2 ON テーブル名1.列名1 = テーブル名2.列名2;
書式は内部結合と似ていますが、「INNER JOIN」が「LEFT JOIN」に変わっています。このように指定すると、はじめに指定したテーブルの全データと2番目に指定したテーブルの結合キーで結合できるデータが取得されます。
このような外部結合を「左外部結合」と呼びます。
前節の「bookinfo」と「orderinfo」の例で見ると、DDL文は下記のようになります。
SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date FROM bookinfo A LEFT JOIN orderinfo B ON A.isbn=B.isbn;
この場合は、「bookinfo」の全データと、「orderinfo」のisbn列のデータが「bookinfo」のisbn列に含まれるデータを取得できます。
※外部結合で片方のテーブルにしかデータが存在しない場合、もう片方のテーブルのデータは、データ自体がないので「NULL」と表示されます。
内部結合では注文のあるデータしか表示されませんでしたが、外部結合を使うと注文のないデータもチェックできるようになります。
4. 右外部結合右外部結合は、左外部結合の逆です。
まず右外部結合の書式を見てみます。
SELECT テーブル名1もしくは2.列名,・・・ FROM テーブル名1 lefT JOIN テーブル名2
ON テーブル名1.列名1 = テーブル名2.列名2;
2番目に指定したテーブルの全データとはじめに指定したテーブルの結合キーで結合できるデータが取得されます。
今度は「orderinfo」と「userinfo」を例にとって説明していきます。
「orderinfo」と「userinfo」はそれぞれのuser列で結合することができますが、「userinfo」のユーザー名「ebisu」と「sugamo」の注文データは「orderinfo」にはありません。
| orderno | user | isbn | quantity | date |
|---|---|---|---|---|
| 3 | akiba | 00001 | 1 | 2010-08-02 |
| 5 | kanda | 00002 | 1 | 2010-08-22 |
| 8 | meguro | 00003 | 4 | 2010-07-30 |
| 19 | osaki | 00006 | 1 | 2010-08-20 |
| 10 | shibuya | 00003 | 1 | 2010-08-21 |
| 22 | tamachi | 00006 | 6 | 2010-09-30 |
| 30 | ueno | 00002 | 1 | 2010-07-15 |
| user | password | |
|---|---|---|
| akiba | akibapass | akiba@kanda-it-school.com |
| ebisu | ebisupass | ebisu@kanda-it-school.com |
| kanda | kandapass | kanda@kanda-it-school.com |
| meguro | meguropass | meguro@kanda-it-school.com |
| osaki | osakipass | osaki@kanda-it-school.com |
| shibuya | shibuyapass | shibuya@kanda-it-school.com |
| sugamo | sugamopass | sugamo@kanda-it-school.com |
| tamachi | tamachipass | tamachi@kanda-it-school.com |
| ueno | uenopass | ueno@kanda-it-school.com |
この2つのテーブルを、下表のように「userinfo」のユーザー名「ebisu」と「sugamo」のデータを含めて取得したい場合に右外部結合を使います。
| orderno | user | isbn | quantity | date | password | |
|---|---|---|---|---|---|---|
| 3 | akiba | 00001 | 1 | 2010-08-02 | akibapass | akiba@kanda-it-school.com |
| ebisupass | ebisu@kanda-it-school.com | |||||
| 5 | kanda | 00002 | 1 | 2010-08-22 | kandapass | kanda@kanda-it-school.com |
| 8 | meguro | 00003 | 4 | 2010-07-30 | meguropass | meguro@kanda-it-school.com |
| 19 | osaki | 00006 | 1 | 2010-08-20 | osakipass | osaki@kanda-it-school.com |
| 10 | shibuya | 00003 | 1 | 2010-08-21 | shibuyapass | shibuya@kanda-it-school.com |
| sugamopass | sugamo@kanda-it-school.com | |||||
| 22 | tamachi | 00006 | 6 | 2010-09-30 | tamachipass | tamachi@kanda-it-school.com |
| 30 | ueno | 00002 | 1 | 2010-07-15 | uenopass | ueno@kanda-it-school.com |
FROM orderinfo A lefT JOIN userinfo B ON A.user=B.user;
| 左外部結合と右外部結合はどちらのテーブルを中心に取り出すかだけで本質的な違いはありません。
できるだけ統一して使うほうが、混乱が少ないでしょう。 |
5. 結合と「WHERE」句の組み合わせテーブルを結合した場合でも、WHERE句で抽出条件を付け加えることができます。
書式は下記の通りです。
SELECT テーブル名1もしくは2.列名,・・・
FROM テーブル名1 INNER JOIN または LEFT JOIN または RIGHT JOIN テーブル名2
ON テーブル名1.列名1 = テーブル名2.列名2
WHERE テーブル名.列名 演算子 値;
例えば「bookinfo」と「orderinfo」を結合し、「orderinfo」の注文数が3冊以上のデータを抽出したい場合は、下記のようなDDL文になります。
SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
WHERE B.quantity >=3;
また外部結合時に「IS NULL」演算子を使うと、片方のテーブルにしかないデータを特定できるような便利な使い方ができます。
「bookinfo」と「orderinfo」で、「bookinfo」にしかないデータを抽出したい場合、下記のようなDDL文で可能です。
SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date
FROM bookinfo A LEFT JOIN orderinfo B ON A.isbn=B.isbn
WHERE B.isbn IS NULL;
これは、外部結合時に片方のテーブルにしかデータが存在しない場合、もう片方のテーブルのデータは、データ自体がないので「NULL」になるためです。
6. 結合と算術演算子の組み合わせ「WHERE」句と同じように、テーブルを結合した場合でも算術演算子が利用できます。
「bookinfo」と「orderinfo」を結合して「bookinfo」の価格と「orderinfo」の注文数を乗算し、注文金額を取得することもできます。
SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date,A.price*B.quantity
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn;
7. 結合と「ORDER BY」句の組み合わせ今度は結合したデータの並べ替えをしてみましょう。
基本書式は下記の通りです。
SELECT テーブル名1もしくは2.列名,・・・
FROM テーブル名1 INNER JOIN または LEFT JOIN または RIGHT JOIN テーブル名2
ON テーブル名1.列名1 = テーブル名2.列名2;
ORDER BY テーブル名.列名
「bookinfo」と「orderinfo」を結合し、「orderinfo」の注文番号順に並び替えて見ましょう。
SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
ORDER BY B.orderno;
| テーブルの結合は2つだけに限定されるわけではありません。3つ以上のテーブルを結合することもできます。
「bookinfo」テーブル、「orderinfo」テーブル、「userinfo」テーブルの3テーブルを結合する場合を見てみましょう。 SELECT * FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn INNER JOIN userinfo C ON B.user=C.user ; 上記のように2番目のテーブル名に続けて「INNER JOIN」（「LEFT JOIN」「RIGHT JOIN」も可）を指定し、 さらに結合したいテーブル名と「ON」で結合条件を指定します。それ以上のテーブルを結合する場合もこれの繰り返しになります。 
|
| テーブルの結合はいままで解説した結合方法の他にも全結合、自己結合、非等価結合、クロス結合等様々な結合方法があります。
これらの結合方法は、本テキストの範囲を超えるため解説しませんが、興味のある方は専門の書籍等を参照してください。 |
３．本章のまとめ
テーブルの結合は、リレーショナルデータベースを扱う上で最も重要な知識で、 これを知っておかないとリレーショナルデータベースを使用する意味が半減すると言っても過言ではないでしょう。
何度も復習して、考え方と使用方法をしっかりと身につけておいてください。
SQLII基礎
HOME > データベース基礎 SQL基礎2 > サブクエリ
本章では、複数の検索を組み合わせて実行することができるサブクエリについて解説します。
本章の目標
- サブクエリの概念を理解する
- 「WHERE」句でのサブクエリの使い方をおぼえる
- 「FROM」句でのサブクエリの使い方をおぼえる
- 「SELECT」句でのサブクエリの使い方をおぼえる
- 「HAVING」句でのサブクエリの使い方をおぼえる
目次
- １．サブクエリの基本
- 1. サブクエリとは
- 2. サブクエリの具体例
- ２．サブクエリの発展
- 1. 「FROM」句でのサブクエリ
- 2. 「SELECT」句でのサブクエリ
- 3. 「HAVING」句でのサブクエリ
- ３．本章のまとめ
１．サブクエリの基本
まずはサブクエリの考え方と基本的な使用方法を説明します。
1. サブクエリとはデータベースに対する処理の命令要求（DML文の実行）をクエリといいます。 SQLでは、あるクエリ（サブクエリ）の実行結果を基に、別のクエリ（メインクエリ）を制御することができます。
下図を見てください。
この例では、サブクエリのSELECT文の検索結果を、メインクエリの検索条件としています。
このように、クエリを入れ子にしてサブクエリの実行結果を基にメインクエリを処理することができるのです。
サブクエリには様々な使い方がありますので、次項以降で具体的な例で説明していきます。
2. サブクエリの具体例まずサブクエリの中では最も基本となる、SELECT文の「WHERE」句で使う方法を、具体的な例を見ていきながら説明します。
基本的な構文は下記のようになります。
SELECT 列名 FROM テーブル名 WHERE 列名 演算子 サブクエリ
「bookinfo」を例にして説明します。
ISBN番号「00001」の「SQL入門テキスト」と同じ価格の書籍を検索したい場合を考えます。
まず「SQL入門テキスト」の価格を検索する必要があります。
DML文は下記のようになりますね。
SELECT price FROM bookinfo WHERE isbn = '00001';
これで「SQL入門テキスト」の価格が抽出できます。
さらに同一価格の書籍を抽出するには、
SELECT * FROM bookinfo WHERE price = 1050;
となります。
この2つのDML文
SELECT price FROM bookinfo WHERE isbn = '00001';→SQL①
SELECT * FROM bookinfo WHERE price = 1050;→SQL②
を、「SQL①」をサブクエリ、「SQL②」をメインクエリとしてひとつにまとめると、下記のようなDML文になります。
SELECT * FROM bookinfo WHERE price = (SELECT price FROM bookinfo WHERE isbn = '00001');
ではISBN番号「00002」の「Java Struts基礎テキスト」より価格の高い書籍を抽出したい場合はどうなるでしょうか？
DML文は下記のようになります。
SELECT * FROM bookinfo WHERE price > ( SELECT price FROM bookinfo WHERE isbn = '00002' );
２．サブクエリの発展
ここからは、サブクエリの発展的な使い方を説明します。
1. 「FROM」句でのサブクエリ前節では「WHERE」句の条件としてサブクエリを使用しましたが、サブクエリは「WHERE」句だけで使われるわけではありません。
まず「FROM」句での使用例から見ていきます。
「FROM」句でサブクエリを使用する場合の書式は下記のようになります。
SELECT 列名 FROM サブクエリ
例えば「bookinfo」と「orderinfo」を結合して注文金額を算出し、ユーザーごとの受注金額を集計した後にその受注金額の最大値を取得するケースを考えてみましょう。
受注金額の集計までは、これまで学習したDDL文で取得することができます。
SELECT B.user,SUM(A.price*B.quantity)
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
GROUP BY B.user;
この集計した値を元に最大の注文金額を取得するには、先に示したDDL文をサブクエリとして「FROM」句に入れ、テーブルと見做すことで実現できます。
DDL文は下記のようになります。
SELECT MAX(C.amount) FROM
( SELECT B.user,SUM(A.price*B.quantity) AS amount
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
GROUP BY B.user ) C;
2. 「SELECT」句でのサブクエリ次に「SELECT」句でのサブクエリの使用方法について見てみましょう。
書式は下記のようになります。
SELECT サブクエリ FROM テーブル名;
「orderinfo」でユーザー毎の受注数量と全体の受注数量を対比して見たい場合などは下記のようなサブクエリで取得することができます。
SELECT user,SUM(quantity),(SELECT SUM(quantity) FROM orderinfo) FROM orderinfo GROUP BY user;
3. 「HAVING」句でのサブクエリ「HAVING」句でもサブクエリが使えます。
書式は下記のようになります。
SELECT グループ化キー,集計関数(列名) FROM テーブル名 GROUP BY グループ化キー
HAVING 集計関数(列名) 演算子 サブクエリ;
「orderinfo」でユーザー毎の受注数量と全体の受注数量を対比して見たい場合などは下記のようなサブクエリで取得することができます。
「orderinfo」で、注文数が平均注文数を下回っているユーザーを取得したい場合などは、下記のようなDDL文で取得可能です。
SELECT user,SUM(quantity) FROM orderinfo GROUP BY user
HAVING SUM(quantity) < ( SELECT AVG(quantity) FROM orderinfo );
３．本章のまとめ
サブクエリはプログラムを作成する上で必須というわけではありませんし、イメージが湧きにくく、少し取っ付きにくいところがありますので、 敬遠されがちですが、理解してものにできればより複雑な検索を可能にする強力な武器になります。
ぜひともマスターして、開発工数の短縮を目指してください。