3 SQL言語前編（解説）

サイト:	LinuC Open Network
コース:	井上先生用コース
ブック:	3 SQL言語前編（解説）

印刷者:	ゲストユーザ
日付:	2024年 09月 17日(火曜日) 05:07

説明

この章では、SQLの以下の事項について説明します。

3.1 データ定義
3.2 データ操作
3.3 問い合わせ

3.1 データ定義

表(テーブル)は、列(カラム)と行(タプル)から構成されています。この節では、表と列の定義に関する事項ついて説明します。

デフォルト値と制約条件

表を定義する際に、格納されるデータに対してデフォルト値や制約条件を設定しておくと、誤ったデータが格納されたり、必要なデータが欠落したりというような問題が発生する可能性を減らすことができます。デフォルト値と制約条件は、以下のいずれかの方法で設定します。

列制約 --- 1つの列に対して設定する
表制約 --- 1つ以上の列に対して設定する

設定できる項目は以下の通りです。列制約でしか設定できないものと、どちらでも設定できるものがあります。ただし、2つ以上の列に関係するものは表制約でしか設定できません。

列制約で設定

項目	指定方法
デフォルト値	DEFAULT デフォルト値
非NULL制約(空値不可)	NOT NULL
一意性制約(重複不可)	UNIQUE
検査制約	CHECK (論理式)
主キー制約	PRIMARY KEY
外部キー制約	REFERENCES 参照表名(参照列名 [, ...])

表制約で設定

項目	指定方法
一意性制約(重複不可)	UNIQUE (列名 [, ...])
検査制約	CHECK (論理式)
主キー制約	PRIMARY KEY (列名 [, ...])
外部キー制約	FOREIGN KEY (列名 [, ...]) REFERENCES 参照表名(参照列名 [, ...])

デフォルト値は、列に格納する値が指定されていない場合に格納する値を指定します。デフォルト値の設定がない列には空値(NULL値)が入ります。
非NULL制約は、列がNULL値を取らないことを保証します。
一意性制約は、列のデータあるいは複数列のデータを組み合わせたものが、表に存在するすべての行で一意であることを保証します。
検査制約は、特定の列の値が論理式を満たすことを保証します。
主キー制約は、列または複数列の組み合わせが、表内の行を一意に識別するものとして利用できることを保証します。これは非NULLかつUNIQUEの制約と等価です。
外部キー制約は、列または複数列の組み合わせが、他の表(参照表)の列または複数列(参照列)の値と一致することを保証します。これによって関連する2つの表間の参照整合性が維持されます。なお、同時に非NULL制約が設定されてなければ、NULL値の格納は可能です。

CREATE TABLE文

CREATE TABLE文は、データベースに新しい表を作成します。形式は以下の通りです。

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名 データ型 [列制約] [...]
    [, ...]
    [, [CONSTRAINT 制約名] 表制約] [...])

列名とデータ型は1組以上、オプションの列制約は列ごとに複数、表制約は表に複数指定できます。

IF NOT EXISTS を指定すると、既存の表が存在してもエラーになりません。
表名は、作成する新しい表の名前です。
列名は、新しい表を構成する列の名前です。
データ型は、列のデータ型です。データ型については4.1節で説明します。
列制約は、列に格納する値の制約条件です。
表制約は、表に格納する行(実際には列の値)の制約条件です。
制約名はシステムで自動的に付与されますが、ユーザによる指定も可能です。

具体例として、prod表(商品表)を作成するCREATE TABLE文を以下に示します。

CREATE TABLE prod ( --商品表
    prod_id INTEGER NOT NULL, --商品番号
    prod_name TEXT NOT NULL, --商品名
    price INTEGER DEFAULT 0 CHECK(price >= 0), --価格
    PRIMARY KEY (prod_id));

prod表は3つの列で構成され、prod_id列には非NULL制約を含む主キー制約、prod_name列には非NULL制約、price列にはデフォルト値 0 と検査制約(値は負でない)が設定されています。

次の例は、customer表(顧客表)を作成するCREATE TABLE文です。

CREATE TABLE customer ( --顧客表
    customer_id INTEGER PRIMARY KEY, --顧客番号
    customer_name TEXT NOT NULL); --顧客名

customer表は2つの列で構成され、customer_id列には主キー制約、customer_name列には非NULL制約が設定されています。なお、主キー制約は列制約、表制約のどちらで設定しても同じ効果です。

次の例は、orders表(注文表)を作成するCREATE TABLE文です。

CREATE TABLE orders ( --注文表
    order_id INTEGER UNIQUE, --注文番号
    order_date TIMESTAMP, --注文日
    customer_id INTEGER REFERENCES customer(customer_id), --商品番号
    prod_id INTEGER, --顧客番号
    qty INTEGER, --数量
    FOREGIN KEY (prod_id) REFERENCES prod(prod_id));

orders表は5つの列で構成され、order_id列には一意性制約、customer_id列には外部キー制約(列制約)、prod_id列には外部キー制約(表制約)が設定されています。このため、customer_id列に格納できる値はcustomer表に存在する値、prod_id列に格納できる値はprod表に存在する値でなければなりません。なお、外部キー制約も主キー制約と同様に列制約、表制約のどちらで設定しても同じ効果です。

ALTER TABLE文

ALTER TABLE文は、既存の表の定義を変更します。形式は以下の通りです。

ALTER TABLE [IF EXISTS] 表名
    アクション [, ...]

列および表制約の追加と削除を行うアクションは、以下のいずれかを指定します。

    ADD [COLUMN] 列名 データ型 [列制約] [...]
    DROP [COLUMN] 列名
    ADD [CONSTRAINT 制約名] 表制約
    DROP CONSTRAINT 制約名

IF EXISTS を指定すると、既存の表が存在しなくてもエラーになりません。
表名は、変更する表の名前です。
ADD [COLUMN] は新しい列を追加します。
DROP [COLUMN] は既存の列を削除します。
ADD [CONSTRAINT] は新しい表制約を追加します。
DROP CONSTRAINT は既存の表制約を削除します。

具体例として、customer表に新しい列を追加するALTER TABLE文を以下に示します。

ALTER TABLE customer
    ADD COLUMN address TEXT; --住所

追加されたaddress列には、デフォルト値(NULL値)が格納されます。

次の例は、customer表からaddress列を削除するALTER TABLE文です。

ALTER TABLE customer
    DROP COLUMN address;

ALTER TABLE文には上記以外にも多数の構文があります。マニュアルを参照してください。

DROP TABLE文

DROP TABLE文は、データベースから既存の表を削除します。形式は以下の通りです。

DROP TABLE [IF EXISTS] 表名 [, ...]

IF EXISTS を指定すると、既存の表が存在しなくてもエラーになりません。
表名は、削除する表の名前です。複数指定できます。

他の表から外部キー制約によって参照されている表は削除できません。また、表を削除するのではなく、表の行を空にするには DELETE文またはTRUNCATE文を使用してください。

具体例として、orders表を削除するDROP TABLE文を以下に示します。

DROP TABLE orders;

TRUNCATE文

TRUNCATE文は、表に格納されているすべての行を高速に削除します。形式は以下の通りです。

TRUNCATE [TABLE] 表名 [, ...]

表名は、すべての行を削除する表の名前です。複数指定できます。

TRUNCATE文は、条件指定のないDELETE文と同じようにすべての行を削除します。ただし、他の表から外部キー制約によって参照されている場合は削除できません。

具体例として、customer表を空にするTRUNCATE文を以下に示します。

TRUNCATE customer;

3.2 データ操作

この節では、表のデータの追加、変更、削除を行うSQL文を説明します。

データの追加(INSERT文)

INSERT文は、表に新しいデータを追加します。データは行の単位で追加されます。値式を使用して行(複数可)を挿入すること、次節で説明する問い合わせの結果を使って複数行を挿入することができます。INSERT文の形式は以下の通りです。

INSERT INTO 表名 [(列名 [, ...])]
    { VALUES (値式 [, ...]) [, ...] | 問い合わせ }

列名の並び(列リスト)と値式の並び(値リスト)は1対1に対応している必要があります。また、値リストは複数指定できます。

INTO句は、データの追加先を指定します。
表名は、行を追加する表の名前です。
列名は、データを入力する列の名前です。列リストに指定しなかった列にはデフォルト値が入力されます。ただし、列リスト全体を省略した場合は、表のすべての列を定義順に指定したものとみなされます。
VALUES句は、追加するデータを指定します。
値式は、入力する値、または値を計算する式です。キーワード DEFAULTを指定するとデフォルト値が入力されます。
VALUES句の代わりに問い合わせを指定できます。
問い合わせは、追加する行の集合を作成するためのSELECT文です。任意のSELECT文を使用できますが、SELECT句に指定する値式の並びは列リストに対応している必要があります。

具体例として、前節で作成したprod表にデータを追加するINSERT文を以下に示します。

  INSERT INTO prod VALUES (1, 'みかん', 50);
  INSERT INTO prod (prod_id, prod_name, price) VALUES (2, 'りんご', 70);
  INSERT INTO prod (prod_name, price, prod_id) VALUES ('メロン', 100, 3);

上記3つのINSERT文はすべてprod表を構成する3列すべてに値を明示的に与えています。

次の例は、prod表のprice列に入力する値を省略したINSERT文です。

  INSERT INTO prod (prod_id, prod_name) VALUES (4, 'バナナ');
  INSERT INTO prod (prod_id, prod_name, price) VALUES (5, 'ぶどう', DEFAULT);

入力が省略されたprice列にはデフォルト値 0 が格納されます。

データの変更(UPDATE文)

UPDATE文は、条件を満たす全ての行の指定した列の値を変更します。SET句には、変更する列のみを指定します。UPDATE文の形式は以下の通りです。

UPDATE 表名
    SET { 列名 = 値式 | (列名 [, ...]) = (値式 [, ...]) } [, ...]
    [WHERE 条件]

UPDATE句は、データの更新先を指定します。
表名は、行を変更する表の名前です。
SET句は、変更する列と値を指定します。
列名は、変更する列の名前です。
値式は、変更後の値、または値を計算する式です。値式の並び(値リスト)は、列名の並び(列リスト)と1対1に対応している必要があります。
WHERE句は、変更する行を指定します。
条件は、変更する行を選択する条件です。指定方法は、SELECT文のWHERE句を参照してください。なお、WHERE句を省略するとすべての行が変更されます。

具体例として、prod表のprice列を変更するUPDATE文を以下に示します。

  UPDATE prod SET price = 30 WHERE prod_id = 4;
  UPDATE prod SET (prod_name, price) = ('さくらんぼ', 80) WHERE prod_id = 5;

最初のUPDATE文ではprod_id列が4の行のprice列、次のUPDATE文ではprod_id列が5の行のprod_name列とprice列が変更されます。

次の例は、prod表のprice列に条件を設定したUPDATE文です。

  UPDATE prod SET price = price + 10 WHERE price >= 80;

変更前のprice列が80以上のすべてのprice列に+10を加算しています。

データの削除(DELETE文)

DELETE文は、指定した表から条件を満たす行を削除します。形式は以下の通りです。

DELETE FROM 表名
    [WHERE 条件]

FROM句は、データの削除元を指定します。
表名は、行を削除する表の名前です。
WHERE句は、削除する行を指定します。
条件は、変更する行を選択する条件です。指定方法は、SELECT文のWHERE句を参照してください。なお、WHERE句を省略するとすべての行が削除され、空の表になります。

具体例として、prod表から条件を満たす行を削除するDELETE文を以下に示します。

DELETE FROM prod WHERE price >= 80;

price列が80以上の行がすべて削除されます。

3.3 問い合わせ

この節では、問い合わせ(クエリ)を行うSELECT文を説明します。問い合わせとは、表からデータを検索する処理を指します。SELECT文は他のSQL文と比較して複雑なため、以下の順で説明します。

単純なSELECT文
グループ化を含むSELECT文
複数の表を対象とするSELECT文

単純なSELECT文

SELECT文は表からデータを検索します。形式は以下の通りです。

SELECT [DISTINCT] {値式 [[AS] 別名] [, ...] | * }
    [FROM 表名 [[AS] 別名] [, ...]]
    [WHERE 条件]
    [GROUP BY 値式 [, ...]]
    [HAVING 条件]
    [{ UNION | INTERSECT | EXCEPT } [DISTINCT | ALL] SELECT文]
    [ORDER BY 値式 [ASC | DESC] [, ...] ]
    [LIMIT 行数]
    [OFFSET 行数]

SELECT句は、出力する行を形成する列を指定します。
値式は、出力する列名、値または値を計算する式です。表示目的で別の名前を付けることができます。
値式の代わりにアスタリスク(*)を指定すると、表のすべての列を指定したものとみなされます。
オプションのDISTINCTを指定すると、重複した行は出力から除去されます。省略時はALLで重複した行も出力されます。
FROM句は、検索対象を指定します。
表名は、検索する表の名前です。参照目的で別の名前を付けることができます。複数の表を指定する場合については、「複数の表を対象とするSELECT文」で説明します。FROM句を省略した場合は、列が何もない1行だけの仮想的な表が指定されたものとみなされます。
WHERE句は、行の検索条件を指定します。
条件は、行を選択する論理式です。この論理式が真とならない行はすべて出力から取り除かれます。論理式については別の節で説明します。WHERE句を省略した場合は、すべての行が出力の対象になります。
ORDER BY句は、出力する行のソート方法を指定します。
値式は、出力する列名または値を計算する式です。この値の順に行が並べ替えられます。オプションの ASC | DESC は昇順か降順かの指定で、省略時はASCで昇順になります。
LIMIT句は、出力する行数を指定します。
行数は、出力する最大の行数です。これを超える行は出力から除去されます。
OFFSET句は、読み飛ばす行数を指定します。
行数は、読み飛ばす最大の行数です。読み飛ばした残りの行が出力されます。

具体例として、SELECT句だけのSELECT文を以下に示します。電卓のような四則演算ができます。

SELECT 1+2*3;

次の例は、prod表の行数を出力するSELECT文です。COUNT関数については、別の節で説明します。

SELECT COUNT(*) FROM prod;

次の例は、prod表のすべてのデータを出力するSELECT文です。

SELECT * FROM prod;

次の例は、prod表からprice列が70未満の行のprod_name列とprice列を出力するSELECT文です。

SELECT prod_name, price
    FROM prod
    WHERE price < 70;

次の例は、DISTINCTを使用したSELECT文です。orders表のprod_id列の値を重複なしで出力します。

SELECT DISTINCT prod_id
    FROM orders;

次の例は、ORDER BY句、LIMIT句、OFFSET句を使用したSELECT文です。

SELECT order_id, prod_id, qty
    FROM orders
    ORDER BY qty DESC
    LIMIT 1
    OFFSET 2;

orders表をqty列の降順にソートし、最初の2行を読み飛ばすことにより、3番目に大きな値のみを出力しています。

グループ化を含むSELECT文

グループ化とは、指定された列の値が同じ行をまとめたグループに表を分割する操作です。例えば、以下のorders表について考えます。

orders表

order_id	order_date	customer_id	prod_id	qty
1	2024-04-01	1	1	10
2	2024-04-01	2	2	5
3	2024-04-01	3	3	8
4	2024-04-02	2	1	3
5	2024-04-02	3	2	4

orders表をprod_id列でグループ化すると、以下のように3グループに分割されます。

グループ化されたorders表

prod_id	order_id	order_date	customer_id	qty
1	1	2024-04-01	1	10
1	4	2024-04-02	2	3
2	2	2024-04-01	2	5
2	5	2024-04-02	3	4
3	3	2024-04-01	3	8

グループ化された表の検索は、グループ単位で行われます。例えば、行数のカウント結果は以下のようになります。

prod_id	count(*)
1	2
2	2
3	1

グループ化には、以下の句を使用します。

GROUP BY句は、グループ化するためのキーを指定します。
値式は、キーとなる列名または値を計算する式です。計算結果が同じ値になった行を1つのグループにまとめます。
HAVING句は、グループの検索条件を指定します。
条件は、グループを選択する論理式です。この論理式が真とならないグループはすべて出力から取り除かれます。論理式については別の節で説明します。HAVING句を省略した場合は、すべてのグループが出力の対象になります。

GROUP BY句を指定したSELECT文では、以下の点に注意する必要があります。

SELECT句、HAVING句、ORDER BY句で指定できる値式は、ORDER BY句で指定したキーか集約関数に限定されます。理由は、グループ内で単一の値をとる必要があるためです。
WHERE句はGROUP BY句よりも先に実行されるため、グループ化はWHERE句で選択された行に対して行われます。
ORDER BY句、LIMIT句で指定する行数は、行ではなくグループの数になります。

具体例として、orders表をprod_id列でグループ化して、prod_id列と各グループの行数を出力するSELECT文を以下に示します。

SELECT prod_id, COUNT(*)
    FROM orders
    GROUP BY prod_id;

以下は、上記にHAVING句を追加して行数が2のグループのみを出力するSELECT文です。

SELECT prod_id, COUNT(*)
    FROM orders
    GROUP BY prod_id
    HAVING COUNT(*) = 2;

以下は、WHERE句とGROUP BY句を使ったSELECT文です。

SELECT prod_id, COUNT(*)
    FROM orders
    WHERE customer_id > 1
    GROUP BY prod_id;

orders表からcustomer_id列が 1 よりも大きな行を選択後に、prod_id列でグループ化しています。

複数の表を対象とするSELECT文

2つの表を対象とする検索には、集合演算と結合演算があります。

集合演算は、表を行を要素とする集合と考え、2つの集合間の和、差、共通集合を求めます。例えば、以下のprod表とprod2表について考えます。

prod表

prod_id	prod_name	price
1	みかん	50
2	りんご	70
3	メロン	100
4	バナナ	30

prod2表

prod_id	prod_name	price
1	みかん	50
3	メロン	100
5	ぶどう	50

prod表とprod2表の和集合、差集合、共通集合は以下のようになります。

和集合

prod_id	prod_name	price
1	みかん	50
2	りんご	70
3	メロン	100
4	バナナ	30
5	ぶどう	50

prod表とprod2表のどちらかにある行はすべて出力されます。

差集合(prod - prod2)

prod_id	prod_name	price
2	りんご	70
4	バナナ	30

prod表にあってprod2表にない行のみが出力されます。

共通集合

prod_id	prod_name	price
1	みかん	50
3	メロン	100

prod表とprod2表の両方にある行のみが出力されます。

集合演算を行う際には、以下の句を使用します。

UNION句は、和集合を求めます。
EXCEPT句は、差集合を求めます。
INTERSECT句は、共通集合を求めます。

各句で指定が必要なSELECT文は、集合演算を行う相手の表を生成する問い合わせです。いずれの場合も、2つの表間で列数とデータ型が一致している必要がありますが、列名は異なっていても構いません。オプションの DISTINCT | ALL は重複した行を除去するかしないかの指定で、省略時はDISTINCTで重複した行が除去されます。

集合演算の具体例として、prod表とprod2表の和集合を出力するSELECT文を以下に示します。

SELECT prod_id, prod_name, price FROM prod
    UNION
    SELECT prod_id, prod_name, price FROM prod_2;

結合演算は、2つの表を横方向に結合します。結合することによって、別の表から関連するデータを取得することができます。結合方法には、内部結合、外部結合、クロス結合の3種類があります。内部結合は、多くの場合に単に結合と呼ばれます。

内部結合は、2つの表の共通の列(結合キー)に着目して、列の値が等しい行どうしを結合した表を出力します。以下の例は、orders表とprod2表をprod_id列で内部結合した結果です。内部結合では相手の表に結合できる行が存在しない行は出力されません。

orders					prod2
order_id	order_date	customer_id	prod_id	qty	prod_id	prod_name	price
1	2024-04-01	1	1	10	1	みかん	50
3	2024-04-01	3	3	8	3	メロン	100
4	2024-04-02	2	1	3	1	みかん	50

外部結合は、内部結合の結果に加えて、相手の表に結合できる行が存在しない行も出力します。外部結合には、左側の表の行をすべて出力する左外部結合、右側の表の行をすべて出力する右外部結合、両方の表の行をすべて出力するフル外部結合の3種類があります。以下の例は、orders表とprod2表をprod_id列でフル外部結合した結果です。

orders					prod2
order_id	order_date	customer_id	prod_id	qty	prod_id	prod_name	price
1	2024-04-01	1	1	10	1	みかん	50
2	2024-04-01	2	2	5
3	2024-04-01	3	3	8	3	メロン	100
4	2024-04-02	2	1	3	1	みかん	50
5	2024-04-02	3	2	4
					5	ぶどう	50

クロス結合は、結合キーは使用せず、2つの表の行の組み合わせすべてを出力します。以下の例は、prod表とprod2表をクロス結合した結果です。

prod			prod2
prod_id	prod_name	price	prod_id	prod_name	price
1	みかん	50	1	みかん	50
1	みかん	50	3	メロン	100
1	みかん	50	5	ぶどう	50
2	りんご	70	1	みかん	50
2	りんご	70	3	メロン	100
2	りんご	70	5	ぶどう	50
以下省略

クロス結合は一見無意味な操作を行っているように見えるかもしれませんが、WHERE句と組み合わせることにより意味のある検索を行うことができます。

結合演算を行う場合は、FROM句を以下のように記述します。

    FROM 表名1 [[AS] 別名]
        結合方法 表名2 [[AS] 別名] [ON 結合条件] [結合方法 ...]

表名1は、結合する最初の表の名前です。
参照目的で別の名前を付けることができます。
結合方法は、以下のいずれかを指定します。
[INNER] JOIN --- 内部結合、結合条件を満たす行のみを出力
LEFT [OUTER] JOIN --- 左外部結合、結合条件を満たさない左側の表の行も出力
RIGHT [OUTER] JOIN --- 右外部結合、結合条件を満たさない右側の表の行も出力
FULL [OUTER] JOIN --- フル外部結合、結合条件を満たさない左右両側の表の行も出力
CROSS JOIN --- クロス結合、左右両側の表の行のすべての組み合わせを出力
表名2は、結合する2番目以降の表の名前です。
参照目的で別の名前を付けることができます。
結合条件は、結合する行の条件を指定します。
クロス結合以外は必ず指定する必要があり、クロス結合では指定できません。結合条件は論理式で、この論理式が真となるすべての行が出力されます。論理式については別の節で説明します。

FROM句で、結合方法を指定せずに表名を単純にカンマで区切って複数指定した場合にはクロス結合が行われます。WHERE句は、結合後の行を選択する条件となります。

結合演算の具体例として、orders表とprod表をprod_id列で内部結合するSELECT文を以下に示します。prod_id列はorders表とprod表の両方にあるため、どちらを指すのか明確にするため表名.列名の形式で記述します。

SELECT order_id, order_date, customer_id, prod.prod_id, prod_name, qty
    FROM orders
        JOIN prod ON orders.prod_id = prod.prod_id;

上記のSELECT文は、クロス結合とWHERE句を使って以下のように書き直すことができます。

SELECT order_id, order_date, customer_id, prod.prod_id, prod_name, qty
    FROM orders, prod
    WHERE orders.prod_id = prod.prod_id;

次の例は、orders表とprod表をprod_id列で右外部結合するSELECT文です。prod表のすべての行が結果に現れます。

SELECT order_id, order_date, customer_id, p.prod_id, prod_name, qty
    FROM orders AS o
        RIGHT JOIN prod AS p ON o.prod_id = p.prod_id;

次の例は、orders表とprod表をprod_id列で結合し、さらにcustomer表をcustomer_id列で結合するSELECT文です。

SELECT order_id, order_date, customer_name, prod_name, qty
    FROM orders AS o JOIN prod p ON o.prod_id = p.prod_id
        JOIN customer AS c ON o.customer_id = c.customer_id;

次の例は、結合条件に不等号を使ったSELECT文です。prod表のpriceがprod2表のprice列よりも大きい行を出力します。このSELECT文は、「みかん」よりも価格が高い商品の名前を出力します。

SELECT p1.prod_name
    FROM prod AS p1
        JOIN prod2 AS p2 ON p1.price > p2.price
    WHERE p2.prod_name = 'みかん';