1.4. SQL 言語

1.4.1.1. 簡単な検索

SELECT文を使った簡単な例は以下の通りです。

SELECT * FROM PART
    WHERE PRICE > 10;
	

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25
	

SELECT PNAME, PRICE 
    FROM PART
    WHERE PRICE > 10;
	

                      PNAME  |  PRICE
                     --------+--------
                      Bolt   |   15
                      Cam    |   25
	

SELECT PNAME, PRICE 
    FROM PART
    WHERE PNAME = 'Bolt' AND
         (PRICE = 0 OR PRICE <= 15);
	

 PNAME  |  PRICE
--------+--------
 Bolt   |   15
	

SELECT PNAME, PRICE * 2 AS DOUBLE
    FROM PART
    WHERE PRICE * 2 < 50;
	

 PNAME  |  DOUBLE
--------+---------
 Screw  |    20
 Nut    |    16
 Bolt   |    30
	

1.4.1.2. 結合

以下の例は、結合 がSQL でどのように表現されるかについてです。

SUPPLIER,PART,SELLS という共通の属性をもつ3つのテーブルを結合す るために、以下の文を考えてみます。

SELECT S.SNAME, P.PNAME
    FROM SUPPLIER S, PART P, SELLS SE
    WHERE S.SNO = SE.SNO AND
          P.PNO = SE.PNO;
      

 SNAME | PNAME
-------+-------
 Smith | Screw
 Smith | Nut
 Jones | Cam
 Adams | Screw
 Adams | Bolt
 Blake | Nut
 Blake | Bolt
 Blake | Cam
      

共通の名前がつけられた属性(SNOとPNO)がリレーション中にあるので、 FROM句ですべてのリレーションに対して別名をつけています。 ここで属性名の前に別名と続くドットをつけることにより、同じ名前を もつ属性を区別することができます。結合は内部結合に出てくるのと同じ 方法で計算されています。まず、直積は SUPPLIER × PART × SELLS を導き出します。次にこれらのタプルから WHERE句で指定される条件を満たす ものだけが選択されます。(すなわち、共通の名前がつけられた属性名 は等しくなければいけない)最後に、 S.NAME と P.PNAME 以外のすべて の列を射影します。

結合を行うもう一つの方法は次のように SQL の JOIN 文を使用することです。

select sname, pname from supplier
	JOIN sells USING (sno)
	JOIN part USING (pno);
	

 sname | pname
-------+-------
 Smith | Screw
 Adams | Screw
 Smith | Nut
 Blake | Nut
 Adams | Bolt
 Blake | Bolt
 Jones | Cam
 Blake | Cam
(8 rows) 
	 

JOIN 文を使用してできた結合テーブルは、WHERE句も ,GROUP BY や HAVING 句よりも前におかれた FROM 句内の発生するテーブルリファ レンスならび項目です。 FROM 句内で、 WHERE 句・GROUP BY 句、HAVING 句が始まる前には、 テーブルリファレンス並びを置くことができますが、JOIN 文によっ てできる結合テーブルはこのリファレンスならびの中の一項目とし て使うことができます。コンマで区切られるならば、他のテーブル 参照やテーブル名や他の JOIN を FROM 句ni 含んでもかまいません。 結合されたテーブルは論理上 FROM 句でリストされる他のどのテー ブルと同じです。

SQL の JOIN には2つの主要な型があります。交差結合(修飾なし結 合) と修飾結合です。修飾結合は、 ON,USING,NATURAL といった結合条件とそれが適用 される方法(内部結合または外部結合)によって更に分類されます。

結合はどんな型のものでも連結したりネストする(すなわちT1かT2の一方もしくは両方が、結合でできたテー ブルになっている) ことができます。結合は通常左から右へ処理されますが、 JOIN 句を括弧でくくって結合順を指定することもできます。

1.4.1.3. 集約演算子

SQLは引数として式をとる集約関数(たとえば AVG, COUNT, SUM, MIN, MAX)を提供しています。式は WHERE 句で合致したそ れぞれの行に対して評価されます。そして、集約演算子は入力値の集合 に対して集約演算が計算されます。通常集約は SELECT 文に対して単一 行の結果を返します。しかし、問い合わせにおいてグループわけを行っ た場合には、それぞれのグループの行に対して別々に演算を行います。 そして、集約の結果はグループ毎に返されます。(次節を参照してくだ さい)

SELECT AVG(PRICE) AS AVG_PRICE
    FROM PART;
	

 AVG_PRICE
-----------
   14.5
	

SELECT COUNT(PNO)
    FROM PART;
	

 COUNT
-------
   4
	

1.4.1.4. グループ毎の集計

SQLではテーブルのタプルをグループに分割させる ことができます。それから、上で記述された集約の演算子は、各グルー プに適用させることができます。すなわちその列のすべての値に対して ではなく、各グループ毎に集約演算しが別々に適用されます。

グループへのタプルの分割は GROUP BYというキー ワードの後にグループを定義する属性のリストを続けることで行います。 GROUP BY A₁, ⃛, A_kとすれば、ある2つの行が同じグ ループに振り分けられるのは、属性A₁, ⃛, A_k すべてが等しくなっている時かつ その時に限ることになります。

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME;
	

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  2  | Jones |   1
  3  | Adams |   2
  4  | Blake |   3
	

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
   1   |  Smith  |   2
   2   |  Jones  |   4
   3   |  Adams  |   1
   3   |  Adams  |   3
   4   |  Blake  |   2
   4   |  Blake  |   3
   4   |  Blake  |   4
	

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
   1   |  Smith  |   1
                 |   2
--------------------------
   2   |  Jones  |   4
--------------------------
   3   |  Adams  |   1
                 |   3
--------------------------
   4   |  Blake  |   2
                 |   3
                 |   4
	

GROUP BY と集約演算子を使っている問い合わせが意味をなすためには、 ターゲットリストの中に直接記述できる属性は GROUP BY で指定した属 性のみであることに注意してください。その他の属性は、集約関数の引 数の内部から間接的に使用できるだけです。そうでないと他の属性と関 係づけられる一意の値が存在しないことになります。

また、例えば AVG(MAX(sno))のように、集約の集約は意味をなさないこ とにも注意しましょう。SELECT はグループ化と集約については1パスの 処理しかしないからです。この種のことがやりたければ、FROM 句で副 問い合わせや一時テーブルを使って、内側の(最初の)集約の処理を1段 階行えばよいでしょう。

1.4.1.5. Having

HAVING 句は WHERE 句のような働きをします。つまり HAVING 句で指定 された条件を満たすグループのみが抽出されて SELECT の対象になりま す。本質的に、グループ化と集約演算が行われる前に、WHERE句は不必 要な入力行を省くのに対して、HAVINGは GOURP 化された後のグループ のそれぞれの行に対して不必要なグループを省きます。よって、WEHRE は集約関数の結果を使うことができません。一方で、HAVING には集約 関数を含むことができないなどの条件はありません!条件に集約を含ま ないのであれば WHERE を使って書いた方がいいでしょう。その方が、 結局は捨ててしまうグループに対する集約計算をしなくてすみます。

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
    FROM SUPPLIER S, SELLS SE
    WHERE S.SNO = SE.SNO
    GROUP BY S.SNO, S.SNAME
    HAVING COUNT(SE.PNO) > 1;
	

 SNO | SNAME | COUNT
-----+-------+-------
  1  | Smith |   2
  3  | Adams |   2
  4  | Blake |   3
	

1.4.1.6. 副問い合わせ

WHERE と HAVING 句で値が使えるような場所ならどこでも副問い合わせ (subqyery,subselect)が使用できます。この場合、先に副問い合わせが 評価され、得られた結果がその場所での値として使われます。副問い合 わせの使用はSQLに表現力を拡張します。

SELECT * 
    FROM PART 
    WHERE PRICE > (SELECT PRICE FROM PART
                   WHERE PNAME='Screw');
	

 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25
	

SELECT * 
    FROM SUPPLIER S
    WHERE NOT EXISTS
        (SELECT * FROM SELLS SE
         WHERE SE.SNO = S.SNO);
	

1.4.1.7. FROM句中の副問い合わせ

副問い合わせの別の使い方は、FROM句に副問い合わせを使うことです。 このような使い方では副問い合わせが複数の列と行を出力することがで きるので非常に有効です、一方式の中の副問い合わせは単一の結果のみ を返さなければいけません。またこのような副問い合わせにより、一時 テーブルを使うという手段にでることなく、複数段のグループ化と集約 計算を行うことができます。

SELECT MAX(subtable.avgprice)
    FROM (SELECT AVG(P.PRICE) AS avgprice
          FROM SUPPLIER S, PART P, SELLS SE
          WHERE S.SNO = SE.SNO AND
                P.PNO = SE.PNO
          GROUP BY S.SNO) subtable;
	

1.4.1.8. 和、積、差

これらの操作は和、積、2つの副問い合わせによって得られたタプルの 差である集合論的差を計算します。

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Jones'
UNION
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNAME = 'Adams';    
	

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna
	

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
INTERSECT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO < 3;
	

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
	

SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 1
EXCEPT
    SELECT S.SNO, S.SNAME, S.CITY
    FROM SUPPLIER S
    WHERE S.SNO > 3;
	

 SNO | SNAME |  CITY
-----+-------+--------
  2  | Jones | Paris
  3  | Adams | Vienna
	

1.4.2.1. Create Table

データ定義における最も基本的なコマンドは新しいリレーション(テー ブル)を作成するものです。 CREATE TABLE コマンドの文法は以下の通 りです。

CREATE TABLE table_name
    (name_of_attr_1 type_of_attr_1
     [, name_of_attr_2 type_of_attr_2 
     [, ...]]);
      

CREATE TABLE SUPPLIER
    (SNO   INTEGER,
     SNAME VARCHAR(20),
     CITY  VARCHAR(20));
     

CREATE TABLE PART
    (PNO   INTEGER,
     PNAME VARCHAR(20),
     PRICE DECIMAL(4 , 2));
     

CREATE TABLE SELLS
    (SNO INTEGER,
     PNO INTEGER);
	

1.4.2.2. SQLのデータ型

以下はSQLでサポートされているいくつかのデータ 型のリストです。

• INTEGER: 符号付き整数 (31 ビットの精度).

• SMALLINT: 符号付き整数 (15 ビット精度).

• DECIMAL (p[,q]): 小数点以上が p桁までで、小数 点以下が q桁まで の、符号付きのパック 10進数。 qが省略された場合には 0 とみなされます。

• FLOAT: 符号付き倍精度実数

• CHAR(n): 長さ nの固定長文字列

• VARCHAR(n): 最大 n文字の可変長文 字列

1.4.2.3. Create Index

インデックスはリレーションにアクセスする速度をあげるために使用さ れます。例えば、リレーションRの属性 Aにインデックスが張られているものとします。 この時、t(A) = aであるようなタプル全部を取り出すのに は、この条件を満たすタプルの数のほぼ比例する時間だけでできてしま い、R全体のタプル数に比例する時間はかかり ません。

SQLにおけるインデックスの作成には CREATE INDDEX コマンドが使用されます。文法は以下の通りです。

CREATE INDEX index_name 
    ON table_name ( name_of_attribute );
      

CREATE INDEX I ON SUPPLIER (SNAME);
      

1.4.2.4. Create View

ビューは仮想的なテーブルと考えられます。 すなわちデータベース中に物理的には 存在しないがユーザにとってはそれが存在するように見えるということ です。一方、基底テーブルの場合には、 テーブルのどの行に対しても物理的記憶領域がが本当にどこかに存在します。

ビューの場合、それ専用で他とは物理的に独立したような、ビューのも のと見分けられるようなデータが格納されることはありません。その代 わりに、システムはシステムカタログ(システムカタログ を参照)でビューの定義を格納し ています。(すなわちビューを実体化するために物理的に格納されてい る基底テーブルにアクセスするルールを格納しています。)ビューの実 装に関するそのほかの方法論についてはSIM98 で言及されています。

SQLではビューの定義にCREATE VIEW コマンドを使用します。文法は以下の通りです。

CREATE VIEW view_name
    AS select_stmt
      

以下のビュー定義を考えてみましょう。(SUPPLIER と PART データベースで使われているテー ブルを再び使用します。)

CREATE VIEW London_Suppliers
    AS SELECT S.SNAME, P.PNAME
        FROM SUPPLIER S, PART P, SELLS SE
        WHERE S.SNO = SE.SNO AND
              P.PNO = SE.PNO AND
              S.CITY = 'London';
      

これで仮想リレーションである London_Suppliersをあたかも基底テーブルの 一つであるかのように使用することができます。

SELECT * FROM London_Suppliers
    WHERE PNAME = 'Screw';
      

 SNAME | PNAME
-------+-------
 Smith | Screw                 
      

この結果を計算するために、データベースシステムは最初に基底テーブ ル SUPPLIER , SELL, PARTに隠れたアクセスを しなければいけません。それはこれらの基底テーブルに対してビュー定 義で与えられる問い合わせを実行することによって行われます。その後、 さらなる選択(ビューに対する問い合わせで指定されている)が行われて、 結果のテーブルが得られます。

1.4.2.5. Drop Table, Drop Index, Drop View

テーブルを削除する(テーブル中にあるすべてのタプルを含む)ためには DROP TABLE コマンドを使用します。

DROP TABLE table_name;
       

SUPPLIER テーブルを削除するには以下のような文を使用します。

DROP TABLE SUPPLIER;
      

DROP INDEX コマンドはインデックスの削除に使用されます。

DROP INDEX index_name;
      

最後に、ビューを削除するには DROP VIEW コマンドを使用します。

DROP VIEW view_name;
      

1.4.3.1. Insert Into

いったんテーブルが作成されると(Create Tableを参照)INSERT INTOコマンドを使用してタプルを挿入することができます。 構文は以下の通りです。

INSERT INTO table_name (name_of_attr_1 
    [, name_of_attr_2 [,...]])
    VALUES (val_attr_1 [, val_attr_2 [, ...]]);
      

リレーション SUPPLIER(SUPPLIER と PART データベースより)に最初のタプルを挿入するには以下の文 を使用します。

INSERT INTO SUPPLIER (SNO, SNAME, CITY)
    VALUES (1, 'Smith', 'London');
      

リレーション SELLES に最初のタプルを挿入するには以下を使用します。

INSERT INTO SELLS (SNO, PNO)
    VALUES (1, 1);
      

1.4.3.2. Update

あるリレーションで、いくつかのタプルの一つもしくは複数のの属性値 を変更する場合には、 UPDATE コマンドを使用します。構文は以下の通 りです。

UPDATE table_name
    SET name_of_attr_1 = value_1 
        [, ... [, name_of_attr_k = value_k]]
    WHERE condition;
      

PARTというリレーションで、'Screw' という部品の属性 PRICE の値を 変更するには以下のようにします。

UPDATE PART
    SET PRICE = 15
    WHERE PNAME = 'Screw';
      

名前が 'Screw' のタプルの属性 PRICE の新しい値は 15 です。

1.4.3.3. Delete

特定のテーブルのタプルを削除するには DELEFE FROM コマンドを使用 します。構文は以下の通りです。

DELETE FROM table_name
    WHERE condition;
      

SUPPLIER テーブルの供給者 'Smith' を削除するには以下の文を使用し ます。

DELETE FROM SUPPLIER
    WHERE SNAME = 'Smith';