4.データの参照

データが入力されたテーブルから必要なデータを参照するには、SQL 文を用いてデータベースに問い合わせなければなりません。このようにデータベースに問い合わせて指定した情報を生成させる命令をクエリ (QUERY) といいます。具体的には SELECT 文を用いてデータベースに欲しいデータを生成するように指示します。 SELECT 文の説明のために次の受注表を用意します。既にテーブルの定義とデータの入力は終っています。
受注表

以後、説明文での受注表とは上表を指します。

4.1. SELECT によるデータの参照

テーブルからデータを参照するには、SELECT 文を用います。SELECT に続けて参照したい列名、続けて FROM と参照するべき列の存在するテーブル名を記述します。基本構文は次の通りです。

1
SELECT 「column1,column2,column2,...」 FROM 「tablename」
  • 全列を参照する場合は、列名を書く代わりに "*" と書くことも可能です。下記のSQL文の機能と同じように見られます。
1
SELECT * FROM 「tablename」

受注表から、受注番号、顧客コード及び商品コードを参照する時は、次のような SQL を記述します。
sample-04-1:

1
2
/* データの参照 */
SELECT 受注番号 , 顧客コード , 商品コード FROM 受注表 ;

実行結果

sample-04-2:

1
2
/* データの参照 */
SELECT * FROM 受注表 ;

実行結果

DISTINCT という引数を利用すれば、出力から重複した値を削除することができます。次の例では、受注表から納品日を出力しますが、重複するものを削除して表示します。 sample-04-3:

1
2
/* データの参照 */
SELECT DISTINCT 納品日 FROM 受注表 ;

実行結果

4.2. 条件付き参照

SELECT 文では WHERE 句を用いることで条件を付けてデータを参照することができます。WHERE 句には述語を定義することができます。述語とは、テーブルの行を TRUE, FALSE または UNKNOWN と評価する式のことです。SELECT 文ではテーブルから述語が TRUE である行のみを抽出します。

例えば、受注表から顧客コードが '003' の商品コードを参照したい場合は次のように SQL を記述します。尚、分かりやすいように顧客コードも表示するようにしてあります。
sample-04-4:

1
2
3
/* データの参照 */
SELECT 顧客コード , 商品コード FROM 受注表
WHERE 顧客コード = '003' ;

実行結果

4.2.1. 不等式を使った述語

述語に等式を利用したもの以外にも,不等式などの関係演算子を使用することができます。SQL は次の関係演算子を認識します。

符号 说明
= 等しい
> より大きい
< より小さい
>= 以上
<= 以下
<> 等しくない

等式の場合にも言えることなのですが、比較に NULL が使用された場合は、これらの演算子も全て UNKNOWN となります。

次のサンプルは、受注表から受注個数が 20 より大きい顧客コードと商品コードを調べるものです。尚、分かりやすくするために受注個数も参照しています。

sample-04-5:

1
2
3
/* データの参照 */
SELECT 顧客コード , 商品コード , 受注個数 FROM 受注表
WHERE 受注個数 > 20 ;

実行結果

4.2.2. ブール演算子を使った述語

一般に、ブール式とは TRUE か FALSE というブール値を返すもので、ブール演算子は 1つ以上のブール値を関連づけて 1つのブール値を導き出すものです。SQL では NULL が存在することから、ブール値として TRUE, FALSE 及び UNKNOWN という 3つのブール値を使用する点で通常とは異なります。SQL が認識するブール演算子は AND, OR 及び NOT です。 |NOT|NOT A という形でブール式を受け取って、その値を FALSE から TRUE に変更するか、TRUE から FALSE に変更します。次の表では NOT TRUE の場合、真理値として FALSE となることを示しています。| |AND|A AND B という形でブール式を 2つ受け取り、2つの式が両方とも TRUE の場合は TRUE、それ以外の場合は FALSE と評価します。次の表では、FALSE AND TRUE のとき真理値は FALSE, UNKNOWN AND TRUE のとき真理値は UNKNOWN となることを示しています。| |OR|A OR B という形でブール式を 2つ受け取り、どちらかの式が TRUE の場合は TRUE、それ以外の場合は FALSE と評価します。|

NOT 演算子を使用したデータの参照例を sample-12-3 に示します。受注表から顧客コードが 001 でない全ての列を参照します。

sample-04-6:

1
2
/* データの参照 */
SELECT * FROM 受注表 WHERE NOT 商品コード = '001' ;

実行結果

AND 演算子を使用したデータの参照例を sample-04-7 に示します。受注表から、商品コードが 101 で受注個数が 15 以上の受注番号と顧客コードと納品日を参照します。尚、分かりやすいように商品コードと受注番号も参照するように記述してあります。

sample-04-7:

1
2
3
/* データの参照 */
SELECT 受注番号, 顧客コード, 商品コード, 受注個数 FROM 受注表
WHERE 商品コード = '101' AND 受注個数 >= 15 ;

実行結果

OR 演算子を使用したデータの参照例を sample-04-8 に示します。受注表から顧客コードが 003 または商品コードが 102 の受注番号、顧客コード、商品コードを参照します。

sample-04-8:

1
2
3
/* データの参照 */
SELECT 受注番号, 顧客コード, 商品コード FROM 受注表
WHERE 顧客コード = '003' OR 商品コード = '102' ;

実行結果

4.3. 特殊演算子

SQL には、前節で説明したブール演算子の他に、IN, BETWEEN, LIKE, IS NULL といった特殊な演算子が用意されています。これらの演算子を利用することでさらに複雑な条件を与えてテーブルからデータを参照することができるようになります。

4.3.1. IN 演算子

IN 演算子を利用することで、含まれる値と含まれない値の集合を明示的に定義することができます。受注表から顧客コードが 001 と 002 であるものを参照するとき、IN 演算子を利用せずに次のように書けます。

sample-04-9:

1
2
SELECT * FROM 受注表
WHERE 顧客コード = '001' OR 顧客コード = '002' ;

実行結果

これと同じ事が IN 演算子を利用すると、より簡単な文で書くことができます。

sample-04-10:

1
2
3
/* データの参照 */
SELECT * FROM 受注表
WHERE 顧客コード IN ('001','002') ;

実行結果

4.3.2. BETWEEN 演算子

BETWEEN 演算子は、述語が TRUE と評価される値の範囲を定義するものです。例えば、受注表から受注個数が 5 から 15 の受注番号を抽出するには次のように記述します。

sample-04-11:

1
2
3
/* データの参照 */
SELECT 受注番号 , 受注個数 FROM 受注表
WHERE 受注個数 BETWEEN 5 AND 15 ;

実行結果

上の実行結果からも分かるように、BETWEEN 演算子は境界値も含むことも注意しなければなりません。SQL は境界値を含まない BETWEEN を直接的にサポートしていません。境界値を含まないようにするには、例えば次のような工夫が必要です。

sample-04-12:

1
2
3
4
/* データの参照 */
SELECT 受注番号,受注個数 FROM 受注表
WHERE (受注個数 BETWEEN 5 AND 15)
AND NOT 受注個数 IN(5,15);

実行結果

4.3.3. LIKE 演算子

LIKE 演算子は、テキスト文字列データ型からサブ文字列を検索するものです。テキスト列を検索して、ある文字列と一致する部分があるかどうか確認します。LIKE はそのためにワイルドカードを使用します。ワイルドカードとは、何かと一致する特殊な文字です。LIKE に使用するワイルドカードには次の 2つがあります。

アンダスコア記号(_) 1 文字に相当する。たとえば、'b_t' は 'bat' や 'bit' とは一致するが、'brat' とは一致しない。
パーセント記号(%)
任意数 (0 文字を含む) の連続した文字に相当する。'%p%t' は 'put'、'posit'、'opt' とは一致するが、'spite' とは一致しない。

ここで、LIKE 演算子を用いたデータの参照のサンプルのために、新たにテーブルを定義し、データを入力したものを用意します。

顧客表

今後、顧客表といえば上表を指すものとします。この顧客表から顧客名が O で始まるものを抽出するには次のように記述します。

sample-04-13:

1
2
/* データの参照 */
SELECT * FROM 顧客表 WHERE 顧客名 LIKE 'O%' ;

実行結果

もし、ワイルドカードである '' と '%' を含む文字列のデータで、'' と '%' を検索したい場合にはエスケープ文字を使用します。エスケープ文字は '' と '%' の直前に配置すると、'' と '%' はワイルドカードとして解釈されなくなります。エスケープ文字は LIKE 句の後ろに ESCAPE 句を記述して指定します。次の例ではエスケープ文字を'$'とした場合に、''で始まる顧客名を検索する文です。顧客表には''で始まるデータがないので、何も出力されません。

sample-04-14:

1
2
/* データの参照 */
SELECT * FROM 顧客表 WHERE 顧客名 LIKE '$_%' ESCAPE '$' ;

実行結果

(何も出力されず)

4.3.4. IS NULL 演算子

IS NULL 演算子は NULL 値を持つデータを検索するものです。次の例では、顧客表から顧客名列に NULL 値を持つデータを全て抽出するものです。実際には、顧客名列には NULL 値は存在しないので何も出力されません。

sample-04-15:

1
2
/* データの参照 */
SELECT * FROM 顧客表 WHERE 顧客名 IS NULL ;

実行結果

(何も出力されず)

4.4. 集約関数

集約関数とは、1つの列グループに対して施すことのできる演算機能をいいます。集約関数は列グループ全体につき 1つの値を生成します。集約関数には次の 5つがあります。

  • SUM () :指定条件によって得られた列の値の合計を求める関数
  • AVG ():指定条件によって得られた列の値の平均値を求める関数
  • MAX ():指定条件によって得られた列の値の中で最大値を返す関数
  • MIN ():指定条件によって得られた列の値の中で最小値を返す関数
  • COUNT ():指定条件によって得られた表の基数、すなわち行数を求める関数

以下に集約関数を用いた例を示します。

sample-04-16:受注表から、SUM () を用いて受注個数の合計値を求めます。

1
2
/* 合計の算出 */
SELECT SUM (受注個数) FROM 受注表 ;

実行結果

sample-04-17:受注表から、AVG () を用いて受注個数の平均値を求めます。

1
2
/* 平均値の算出 */
SELECT AVG (受注個数) FROM 受注表 ;

実行結果

sample-04-18:受注表から、MAX () を用いて受注個数の最大値を求めます。

1
2
/* 最大値の算出 */
SELECT MAX (受注個数) FROM 受注表 ;

実行結果

sample-04-19:受注表から、MIN () を用いて受注個数の最小値を求めます。

1
2
/* 最小値の算出 */
SELECT MIN (受注個数) FROM 受注表 ;

実行結果

sample-04-20:COUNT () を用いて、受注表の行数を求めます。

1
2
/* 行数の算出 */
SELECT COUNT (*) FROM 受注表 ;

実行結果

sample-04-21:受注表から、COUNT () を用いて顧客コードの種類の数を求めます。COUNT (DISTINCT 列名 ) とすることで、指定列の NULL 値以外を持つ値で、しかも異なる値を持つ行数を返します。

1
2
/* 行数の算出 */
SELECT COUNT (DISTINCT 顧客コード) FROM 受注表 ;

実行結果

sample-04-22:受注表から、顧客コードが 001 の行数、その受注個数の合計、平均値、最大値及び最小値を表示させるには次のように記述します。

1
2
3
4
/* 集約関数による出力 */
SELECT COUNT(*) , SUM (受注個数) , AVG (受注個数) ,
       MAX (受注個数) , MIN (受注個数)
FROM 受注表 WHERE 顧客コード = '001' ;

実行結果

確認

1
SELECT * FROM 受注表 WHERE 顧客コード = '001' ;

確認:実行結果

4.5. グループ化

SQL におけるグループ化とは、同列内の値の中で、同じ値を持つデータごとに集合化することをいいます。グループ化には GROUP BY 句を用います。また、グループ化された情報に対して条件を設定し、その条件に合致するものだけを抽出することもできます。これには、GROUP BY 句でグループ化する列名を指定した後、HAVING 句を続けて抽出条件を記述します。

4.5.1. GROUP BY 句

GROUP BY 句を用いれば,ある列の値の部分集合を定義して,それに集約関数を適用することができます。この部分集合とは、共通の集約が適用される対象として、ある列に値を持つかのように定義されたグループのことです。例えば、受注表で顧客コードに対する受注個数の最大値を求める場合は次のように書けます。

sample-04-23:

1
2
3
4
/* グループ化 */
SELECT 顧客コード , MAX(受注個数) 
FROM 受注表
GROUP BY 顧客コード ;

実行結果

GROUP BY 句で顧客コード別に新しいテーブルを一度作成し、それぞれのテーブルの中で受注個数の最大値を集約関数で抽出し、その結果を顧客コード別に表示している、というようなイメージです。 GROUP BY 句は複数の列に使用することもできます。次のサンプルでは、各顧客コードの受注個数の最大値を商品コード別に参照したい場合のものです。

sample-04-24:

1
2
3
4
/* グループ化 */
SELECT 顧客コード , 商品コード , MAX(受注個数) 
FROM 受注表
GROUP BY 顧客コード , 商品コード ;

実行結果

4.5.2. HAVING 句

SQLで、HAVING 句を追加すること、原因は、WHERE キー・ワードは合計の関数と一緒に使うことができません。 WHERE文はGROUP BY文の前に。HAVING文はGROUP BY文の後。

sample-04-24 で 20 以上の最大受注個数 (MAX (受注個数)) のみを参照したい場合のように、GROUP BY 句によりグループ化された情報に対して条件を設定する場合は、HAVING 句を用います。
実際には次の sample-04-25: のように書きます。

sample-04-25:

1
2
3
4
5
/* グループ化 */
SELECT 顧客コード , 商品コード , MAX(受注個数) 
FROM 受注表
GROUP BY 顧客コード , 商品コード
HAVING MAX(受注個数) >=20;

実行結果

WHERE 句と HAVING 句は条件を設定するという観点からは非常に似たようなもののように見えます。しかし、WHERE 句と HAVING 句とは全く異なるものです。WHERE 句は SELECT 文が行を選ぶときの条件を指定するものですが、HAVING 句は WHERE 句が指定する条件で呼び出された行から、グループを選ぶための条件を与えているのです。ですから行を選ぶ WHERE 句には集約関数を置くことはできませんが、HAVING 句では集約関数を置くことができます。

4.6. 並べ替えと名前付け

4.6.1. 出力の並べ替え

テーブルは順序の無い集合なので、テーブルからデータを取り出すとき、データが順番に抽出されるとは限りません。SQL では ORDER BY 句を使って出力に順番を適用できるようにしています。列ごとに昇順 (ASC) 降順 (DESC) を指定することができます。デフォルトでは昇順となっています。

また、複数の列を並べ替えることができます。このときは、ORDER BY 句に続く列の並んでいる順に並び替えられます。すなわち、最初に指定された列が最も優先され、2 番目に指定された列が最初の重複値の順番を決定する、といった具合になります。

sample-04-26:

1
2
/* 出力の並べ替え */
SELECT * FROM 顧客表 ORDER BY 顧客コード DESC  ;

実行結果

ORDER BY と GROUP BY を利用すれば、グループを並べ替えることができます。例えば受注表で商品コードをグループ化して商品コードを降順に並べるときは次の通り SQL を記述します。

sample-04-27:

1
2
3
/* 出力の並べ替え */
SELECT 商品コード FROM 受注表
GROUP BY 商品コード ORDER BY 商品コード DESC ;

実行結果

また、列名の代わりに整数を使って列を指定することもできます。整数は最初の列が 1 で示され、3 番目の列が 3 で示されるといったように SELECT 句での列の位置を示します。

sample-04-28:

1
2
3
4
/* 出力の並べ替え */
SELECT 顧客コード , 受注個数  
FROM 受注表
ORDER BY 2 DESC ;

実行結果

4.6.2. 出力列の名前付け

出力列には、名前を付ける事ができます。MAX (列名) など集約関数を使って出力された結果などには列名が付かないので、AS 句を用いて名前を指定します。

例えば、受注表から受注個数の平均値を求め、平均という列名で表示させるには、次のように記述します。

sample-04-29:

1
2
/* 出力列の名前付け */
SELECT AVG(受注個数) AS 平均 FROM 受注表 ;

実行結果

4.7. 特殊演算子を補充する：TOP 句、LIMIT 演算子、ROWNUM 演算子

注：SQLiteで、TOP 句とROWNUM 演算子は実行することができません。しかし、LIMIT 演算子は実行することができます。 ➊MySQL と Oracleで：

1
SELECT TOP 「number|percent」「column1,column2,column2,...」 FROM 「tablename」;

➋SQLiteとMySQL と Oracleで：

1
SELECT 「column1,column2,column2,...」FROM 「tablename」LIMIT 「number」;

➌Oracleで：

1
SELECT 「column1,column2,column2,...」FROM 「tablename」WHERE ROWNUM <= 「number」

sample-04-30:SQLiteで実行することができません

1
2
3
4
/* TOP 句①*/
select TOP 5 * FROM 受注表;
/* TOP 句②*/
SELECT TOP 50 PERCENT * FROM 受注表;

sample-04-31:SQLiteで実行することができます。

1
2
/* LIMIT 演算子*/
select * FROM 受注表 LIMIT 5;

sample-04-32:SQLiteで実行することができません。

1
2
/* ROWNUM 演算子*/
select * FROM 受注表 WHERE ROWNUM <=5;

4.7. SQL文

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
CREATE TABLE 受注表
(
    受注番号     CHAR(5),
    顧客コード   CHAR(4),
    商品コード   CHAR(4),
    受注個数     INTEGER,
    納品日       CHAR(8)
);

insert into 受注表 values('10001','001','102',30,'20010401');
insert into 受注表 values('10002','002','101',20,'20010401');
insert into 受注表 values('10003','001','103',10,'20010402');
insert into 受注表 values('10004','001','103',15,'20010402');
insert into 受注表 values('10005','003','101',15,'20010403');
insert into 受注表 values('10006','001','101',15,'20010404');
insert into 受注表 values('10007','002','102',20,'20010404');
insert into 受注表 values('10008','002','101',20,'20010405');
insert into 受注表 values('10009','001','102',20,'20010406');
insert into 受注表 values('10010','003','101',10,'20010406');
insert into 受注表 values('10011','001','102',25,'20010407');
insert into 受注表 values('10012','003','103',20,'20010407');

SELECT 受注番号 , 顧客コード , 商品コード FROM 受注表 ;

SELECT * FROM 受注表 ;

SELECT DISTINCT 納品日 FROM 受注表 ;

SELECT 顧客コード , 商品コード FROM 受注表
WHERE 顧客コード = '003' ;  

SELECT 顧客コード , 商品コード , 受注個数 FROM 受注表
WHERE 受注個数 > 20 ;

SELECT * FROM 受注表 WHERE NOT 商品コード = '001' ;

SELECT 受注番号, 顧客コード, 商品コード, 受注個数 FROM 受注表
WHERE 商品コード = '101' AND 受注個数 >= 15 ;

SELECT 受注番号, 顧客コード, 商品コード FROM 受注表
WHERE 顧客コード = '003' OR 商品コード = '102' ;

SELECT * FROM 受注表
WHERE 顧客コード = '001' OR 顧客コード = '002' ;  

SELECT * FROM 受注表
WHERE 顧客コード IN ('001','002') ;  

SELECT 受注番号 , 受注個数 FROM 受注表
WHERE 受注個数 BETWEEN 5 AND 15 ;

SELECT 受注番号,受注個数 FROM 受注表
WHERE (受注個数 BETWEEN 5 AND 15)
AND NOT 受注個数 IN(5,15);

CREATE TABLE 顧客表
(
    顧客コード   CHAR(4),  
    顧客名       CHAR (20)
);

insert into 顧客表 values('001','KUROKIYA');
insert into 顧客表 values('002','ONSIDE');
insert into 顧客表 values('003','FIRST HOUSE');

SELECT * FROM 顧客表 WHERE 顧客名 LIKE 'O%' ;

SELECT * FROM 顧客表 WHERE 顧客名 LIKE '$_%' ESCAPE '$' ;

SELECT * FROM 顧客表 WHERE 顧客名 IS NULL ;

SELECT SUM (受注個数) FROM 受注表 ;

SELECT AVG (受注個数) FROM 受注表 ;

SELECT MAX (受注個数) FROM 受注表 ;

SELECT MIN (受注個数) FROM 受注表 ;

SELECT COUNT (*) FROM 受注表 ;  

SELECT COUNT (DISTINCT 顧客コード) FROM 受注表 ;

SELECT COUNT(*) , SUM (受注個数) , AVG (受注個数) ,
MAX (受注個数) , MIN (受注個数)
FROM 受注表 WHERE 顧客コード = '001' ;  

SELECT * FROM 受注表 WHERE 顧客コード = '001' ;

SELECT 顧客コード , MAX(受注個数) FROM 受注表
GROUP BY 顧客コード ;

SELECT 顧客コード , 商品コード , MAX(受注個数) FROM 受注表
GROUP BY 顧客コード , 商品コード ;  

SELECT 顧客コード , 商品コード , MAX(受注個数) FROM 受注表
GROUP BY 顧客コード , 商品コード
HAVING MAX(受注個数) >=20;  

SELECT * FROM 顧客表 ORDER BY 顧客コード DESC  ;

SELECT 商品コード FROM 受注表
GROUP BY 商品コード ORDER BY 商品コード DESC ;  

SELECT 顧客コード , 受注個数 FROM 受注表
ORDER BY 2 DESC ;

SELECT AVG(受注個数) AS 平均 FROM 受注表 ;