データベース言語 SQL リレーショナルデータモデルにおけるデータ操作言語 : リレーショナル代数 少なくともリレーショナル代数と同等のデータ検索能力をもつときリレーショナル完備という. リレーショナル代数はユーザフレンドリではない. 自然な英文による質問の表現が必要になる. リレーショナルデータベース言語 SQL 英文による簡単な構文 リレーショナル代数でできない, 合計, 平均, 最大などの計算機能の組み込み. 親言語への組み込み. データベース言語 データ操作と定義を行うデータベースインタフェース SQL は国際規格, 日本工業規格 (JIS X3005) に制定されている. 標準 SQL リレーショナルデータベース言語の標準化 1970 年代 : 個別言語 IBM サンホゼ研究所で System R 用の SEQUEL (Structured English Query Language) が開発される. カリフォルニア大学バークレー校で INGRES 用の QUEL (Query Language) が開発される. IBM ワトソン研究所で QBE (Query-by-Example) が開発される. 1980 年代 : 標準言語 ANSI (American National Standards Institute) による標準化 ISO (International Organization for Standardization) による標準化 情報処理学会による標準化 1987 年 6 月 15 日 :SQL が国際規格 ISO9075 として制定. 1987 年 11 月 1 日 :SQL が日本工業規格 JIS X3005 として制定. 1992 年 SQL-92 SQL3(SQL:1999) オブジェクト指向の拡張 SQL の構成 SQL による質問 基本概念 列 (column) 属性 行 (row) タプル 表 (table) リレーション 実表 (base table): 実際にデータが入った表 ビュー表 (view table): 実表から導き出される表 SQL では行の重複を許す.
表の定義 表名 CREATE TABLE 表名 データ型 列名データ型 主キー PRIMARY KEY 列名 外部キー FOREIGN KEY 列名 REFERENCES 表名 表の定義 CREATE TABLE 顧客表 ( 顧客番号 CHAR(4) NOT NULL, 顧客名 NCHAR(10) NOT NULL, 顧客住所 NCHAR(20) NOT NULL, PRIMARY KEY ( 顧客番号 )) CREATE TABLE 受注表 ( 顧客番号 CHAR(4) NOT NULL, 伝票番号 INT NOT NULL, 受注年月日 DATE NOT NULL, PRIMARY KEY ( 顧客番号, 伝票番号 ), FOREIGN KEY ( 顧客番号 ) REFERENCES 顧客表 ) SQL による質問 質問指定 (query specification) の基本構文 : SELECT 文 SELECT < 値式 1>,< 値式 2>,,< 値式 n> FROM < 表参照 1>,< 表参照 2>,,< 表参照 m> WHERE < 探索条件 > < 値式 1>,< 値式 2>,,< 値式 n>: 値式リスト < 表参照 1 >,< 表参照 2 >,,< 表参照 m >: 表参照リスト < 探索条件 >(search condition) は一般に述語 ( 命題関数 ) である. SELECT 文の処理により得られる結果リレーションを導出表 (derived table) という. SQL の特徴 データ型の導入 : ドメインに相当 文字列型, 概数型 (FLOAT, REAL), 真数型 (DECIMAL, INTEGER) 集約関数のサポート COUNT, SUM, AVG, MAX, MIN DISTINCT 句, GROUP-BY 句, HAVING 句, ORDER-BY 句 BETWEEN 述語, IN 述語, LIKE 述語, NULL 述語 EXISTS 述語 親言語 (COBOL, FORTRAN,Pascal, PL/I) への埋め込み
(simple query) とは FROM 句にただ一つの < 表参照 > が指定され,WHERE 句の < 探索条件 > に SELECT 文が入らない質問. 全商品の全属性値を求めよ. (WHERE true) 納品した商品の商品番号を求めよ. : マルチ集合 SELECT 商品番号 納品した商品の商品番号を, 重複を除去して求めよ. SELECT DISTINCT 商品番号 商品番号とその定価とその 2 割引の値段を求めよ. : 計算式 SELECT 商品番号, 定価, 定価 *0.8 定価が 10 万以上の商品の全属性値を求めよ. : 比較演算子 WHERE 定価 100000 定価が 5 万以上 10 万以下の商品の全属性値を求めよ. :BETWEEN 述語 WHERE 定価 BETWEEN 50000 AND 100000 IN 述語 : 教科書 p.103 参照 LIKE 述語 : 文字列比較 WHERE 顧客名 LIKE % 商店 NULL 値 ( 述語 ): 空値 WHERE 顧客名 = NULL EXISTS 述語 : 入れ子型質問における解の存在確認
定価が 5 万以上 10 万以下のテレビの全属性値を求めよ. : 述語の組み合わせ WHERE 定価 BETWEEN 50000 AND 100000 AND 商品名 = ' テレビ ' 商品を定価の安いものから高いものに順に並べよ. :ORDER BY 句, 昇順 (ASC), 降順 (DESC) ORDER BY 定価 ASC 納品した商品の総数を商品番号毎に求めよ. : 集約関数,GROUP BY 句 SELECT 商品番号,SUM( 納品数量 ) GROUP BY 商品番号 納品した商品の総数が 10 以上のものを商品番号毎に求めよ. : HAVING 句 SELECT 商品番号,SUM( 納品数量 ) GROUP BY 商品番号 HAVING SUM( 納品数量 ) 10 集約関数 集約関数には以下のものがある. SUM( 列名 ): 数値列に対して, 合計を求める. AVG( 列名 ): 数値列に対して, 平均を求める. MIN( 列名 ): 数値列に対して, 最小値を求める. MAX( 列名 ): 数値列に対して, 最大値を求める. COUNT(*): 条件にあう行数をカウントする. 例 受注明細表から商品番号ごとの受注数量の合計を求めて表示する SELECT 商品番号,SUM( 数量 ) FROM 受注明細表 GROUP BY 商品番号 結合質問 参照表リストには二つ以上の表名が現れる. 商品の商品番号と納品した商品の商品番号が等しい商品情報と納品情報のすべての組を求めよ. : 等結合 SELECT 商品.*, 納品.*, 納品 WHERE 商品. 商品番号 = 納品. 商品番号
結合質問 商品情報とそれを納品した顧客番号と納品数量をすべてを求めよ. : 自然結合 SELECT 商品.*, 顧客番号, 納品数量, 納品 WHERE 商品. 商品番号 = 納品. 商品番号 結合質問 納品状況を示す全ての商品名と顧客名と納品数量の組を求めよ. : 三つの表 SELECT 商品名, 顧客名, 納品数量, 納品, 顧客 WHERE 商品. 商品番号 = 納品. 商品番号 AND 納品. 顧客番号 = 顧客. 顧客番号 上司よりも高給をとっている社員の社員番号とその上司の社員番号を求めよ. : 自己結合, タプル変数 SELECT X. 社員番号,Y. 社員番号 FROM 社員 X, 社員 Y WHERE X. 部長 = Y. 社員番号 AND X. 給与 > Y. 給与 入れ子型質問 WHERE 句に再び SELECT 文 ( 入れ子型質問, 部分質問 ) が入る質問. 商品番号 G1 の商品を納入した顧客の顧客番号と顧客名を求めよ. :IN 演算子 SELECT 顧客番号, 顧客名 WHERE 顧客番号 IN (SELECT 顧客番号 WHERE 商品番号 = 'G1') 以下と等価 SELECT 顧客番号, 顧客名, 顧客 WHERE 納品. 顧客番号 = 顧客. 顧客番号 AND 商品番号 = 'G1' 入れ子型質問 商品番号 G1 の商品を納品した顧客の顧客番号と顧客名を求めよ. : 相互関係を有する部分質問 SELECT 顧客番号, 顧客名 WHERE 'G1' IN (SELECT 商品番号 WHERE 顧客番号 = 顧客. 顧客番号 ) 平均定価より高い商品の商品番号を求めよ. : 結合質問では書けない質問 SELECT 商品番号 WHERE 定価 > (SELECT AVG( 定価 ) )
リレーショナル完備 和 R S UNION SLELCT * FROM S 差 R-S WHERE NOT EXISTS ( FROM S) 直積 R S SELECT R.*, S.*,S 射影 R[A i1, A i2,, A ik ] SELECT A i1, A i2,, A ik 選択 R[A i θa j ] WHERE A i θa j データの変更処理 データの挿入 INSERT INTO 挿入する表名 ( 挿入列名 ) VALUES 挿入値 INSERT INTO 顧客表 ( 顧客番号, 顧客名, 顧客住所 ) VALUES ('A001',N' 横浜商会 ',N' 横浜市金沢区 ') データの変更処理 データの更新 UPDATE 表名 SET 列名 = 式 WHERE 探索条件 UPDATE 商品表 SET 単価 = 単価 *1.1 WHERE 商品名 LIKE N' プリンタ %' データの変更処理 データの削除 DELETE FROM 表名 WHERE 探索条件 DELETE 表 WHERE 顧客名 =N' 中国商店 '
データの制御 処理権限 読み取りのための SELECT 権限 挿入のための INSERT 権限 削除のための DELETE 権限 更新のための UPDATE 権限 表の再定義のための REFERENCE 権限 処理権限の付与 GRANT 権限 ON 表名 TO 認可識別子 処理権限の取り消し REVOKE 権限 ON 表名 FROM 認可識別子