SQL (2) データベース論 Ⅰ 第 7 回 URL 作成者末次文雄 C

Size: px

Start display at page:

Download "SQL (2) データベース論 Ⅰ 第 7 回 URL 作成者末次文雄 C"

いおりおおふさ
5 years ago
Views:

1 SQL (2) データベース論 Ⅰ 第 7 回 URL 作成者末次文雄 C

2 課題 6 の解答例テーブル定義 CREATE DATABASE 学科 ; CREATE TABLE 学科 TBL ( 学科番号 INT(7) NOT NULL UNIQUE, 学科名称 NCHAR(10), 主任 NCHAR(10) ); CREATE TABLE 学生 TBL ( 学籍番号 CHAR(7) NOT NULL UNIQUE, 年次 INT(1), 入学年度 INT(4), 氏名 NCHAR(10) ); CREATE TABLE 所属 TBL ( 学科コード INT(7) NOT NULL, 年度 INT(4), 学生コード CHAR(7), );

3 課題 6 の解答例 SELECT 文 SELECT 学科 TBL. 学科名称, 学生 TBL. 学籍番号, 学生 TBL. 氏名 FROM 学科 TBL, 学生 TBL, 所属 TBL 結合 (JOIN) WHERE 学生 TBL. 入学年度 =1999 AND 所属 TBL. 年度 =2002 AND 学科 TBL. 学科番号 = 所属 TBL. 学科コード AND 学生 TBL. 学籍番号 = 所属 TBL. 学生コード ORDER BY 所属 TBL. 学科コード, 所属 TBL. 学生コード ; 学科名称学籍番号氏名電子工学 A Aさん電子工学 A Bさん情報工学 A Cさん ~ ~ ~

4 目次 1. データ定義 ( 詳細編 ) 2. データ操作 ( 詳細編 ) 3. データ操作 ( 副問合せ )( サブクエリ ) 4. 埋め込みSQL 5. カーソルの使用 6. レポート課題 7. 参考書ほか

5 1. データ定義 ( 詳細編 ) 1.1 テーブル制約 1.2 表の定義変更 1.3 INDEX の作成

6 1.1 テーブル制約テーブル定義時に各種のデータ制約を設定することが出来る 1 列 ( データ項目 ) のNOT NULL 値 2 列内でのデータの一意性 3テーブル内の主キー 4 外部キー制約 5 列 ( データ項目 ) のデータ値上記によってテーブル単位でのデータの一貫性を保証できる

7 1.1 ( 続き ) テーブル制約 1 列 ( データ項目 ) の NOT NULL 値この列には値が入っていなければならない ( 値が入っていないとエラーになる ) CREATE TABLE テーブル名 ( データ項目名データ型 ( 長さ ) NOT NULL); 2 列内でのデータの一意性この列では値の重複が許されない ( 値が重複すればエラーになる INDEX が作られる ) CREATE TABLE テーブル名 ( データ項目名データ型 ( 長さ ) NOT NULL UNIQUE);

8 1.1 ( 続き ) テーブル制約 3 テーブル内の主キーこの列が主キーであることを示す ( 通常他のテーブルの外部キーに対応しており外部キー側で REFERENCES 制約があればこの行は削除できない ) (INDEX が作成される ) CREATE TABLE テーブル名 ( データ項目名データ型 ( 長さ ) NOT NULL CONSTRAINT 識別名 PRIMARY KEY);

9 1.1 ( 続き ) テーブル制約 4 外部キー制約この列が外部キーであることを示す ( 他のテーブルの主キーが既に存在していなければ外部キー側で行の作成が出来ない ) CREATE TABLE テーブル名 ( データ項目名データ型 ( 長さ ) NOT NULL CONSTRAINT 識別名 FOREIGN KEY REFERENCES 主キーのあるテーブル名 ( 列名 ));

10 1.1 ( 続き ) テーブル制約 5 列 ( データ項目 ) のデータ値この列の値を制約する条件を記述する ( この条件に違反する行は存在出来ない ) CREATE TABLE テーブル名 ( データ項目名 A データ型 ( 長さ ), データ項目名 B データ型 ( 長さ ), データ項目名 C データ型 ( 長さ ), CHECK ( 条件文 )); 条件の例 CHECK ( データ項目名 B>=10000)

11 1.1 ( 続き ) テーブル制約補足 : 複数の列を連結した制約文テーブル定義の終わりにまとめて記述 CREATE TABLE テーブル名 ( 列 A データ型 ( 長さ ) NOT NULL, 列 B データ型 ( 長さ ) NOT NULL, 列 C データ型 ( 長さ ) NOT NULL, UNIQUE ( 列 A, 列 B), PRIMARY KEY ( 列 A, 列 B), CONSTRAINT 識別名 PRIMARY KEY ( 列 A, 列 B), CONSTRAINT 識別名 FOREIGN KEY REFERENCES 主キーのあるテーブル名 ( 列名 ), CONSTRAINT 識別名 FOREIGN KEY REFERENCES 主キーのあるテーブル名 ( 列名 );

12 1.2 表の定義変更作成済みのテーブル定義を変更削除するテーブルの削除 DROP TABLE テーブル名 ; テーブルの変更 (DATABASE VIEW INDEX も同様である ) ALTER TABLE テーブル名 ADD 追加列名列の属性 ; ALTER TABLE テーブル名 MODIFY 変更列名新属性 ; ALTER TABLE テーブル名 DROP COLUMN 列名 ; ALTER TABLE テーブル名 ADD CONSTRAINT 制約文 ; ALTER TABLE テーブル名 DROP CONSTRAINT 制約文 ;

13 1.3 INDEX の作成 INDEX の作成 CREATE [UNIQUE] INDEX インデックス名 ON テーブル名 ( 列名 [, 列名 ]); INDEX の削除 DROP INDEX インデックス名 UNIQUE 指定時は重複が有ればエラー

14 2. データ操作 ( 詳細編 ) 2.1 取出し条件 2.2 結合 (JOIN) 2.3 和と差 (UNION MINUS) 2.4 グループ化 2.5 一括挿入 (INSRET) 2.6 演算子と関数

15 2.1 取出し条件 1 取出す表列組 (= 行 ) の指定 SELECT 列 1, 列 2, 列 3, FROM 表 A WHERE 条件式 ; 列の指定 ( 射影演算 ) 表の指定組 (= 行 ) の取出し条件 SELECT * -- 注ホスト言語では使用しない習慣が良い FROM 表 A WHERE 列 1>1000; SELECT DISTINCT 入学年度 FROM 学生 TBL WHERE 入学年度 >=1990; 重複を排除する

16 2.1 ( 続き ) 取出し条件 1 ( 続き ) AND OR SELECT 学科コード, 学籍コード FROM 所属 TBL WHERE 学科コード = AND 年度 =1998 ; AND 条件 SELECT 学科コード, 学籍コード FROM 所属 TBL WHERE 年度 =1995 OR 年度 =1998 ; OR 条件

17 2.2 結合 (JOIN) 1 テーブル ( 表 ) の内部結合 --- 等 ( 自然 ) 結合 SELECT FROM 表 A INNER JOIN 表 B ON 表 A. 列 1 = 表 B. 列 2 ; または (SQL-92 SQL Server ACCESS ) 表は 3 つ以上でも可能 SELECT FROM 表 A, 表 B WHERE 表 A. 列 1 = 表 B. 列 2 ; (ORACLE SQL Server ) または NATURAL JOIN

18 2.2 結合 (JOIN) 2 内部結合に条件を追加 SELECT FROM 表 A INNER JOIN 表 B ON 表 A. 列 1 = 表 B. 列 2 WHERE 表 A. 列 1>=1000 ; または (SQL-92 SQL Server ACCESS ) SELECT FROM 表 A, 表 B WHERE 表 A. 列 1 = 表 B. 列 2 AND 表 A. 列 1>=1000 ; (ORACLE SQL Server )

19 2.2 結合 (JOIN) 3 外部結合 ( 条件適合以外も取出す ) 反対は RIGHT SELECT FROM 表 A LEFT OUTER JOIN 表 B ON 表 A. 列 1 = 表 B. 列 2; (SQL-92 SQL Server ACCESS ) または反対は表 A(+) SELECT FROM 表 A, 表 B(+) WHERE 表 A. 列 1 = 表 B. 列 2; (ORACLE )

20 2.3 和と差 1 和集合 (UNION) SELECT 表 A. 列 1, FROM 表 A WHERE 検索条件 UNION [ALL] SELECT 表 B. 列 2 FROM 表 B WHERE 検索条件 ; ALL 指定は重複を排除しない意味 2 差集合 (MINUS または EXCEPT) 省略 ( 書式は UNION と同様ただし ALL 指定は無い )

21 2.4 グループ化 1 列の値でグループを作り集計をする SELECT 列 1, 集計関数 ([* ALL DISTINCT] 列 2) FROM 表 A GROUP BY 列 1; SELECT 科目コード,COUNT(*),AVG( 成績 ) FROM 表 A GROUP BY 科目コード ;

22 2.4 ( 続き ) グループ化 2 グループ化したデータに条件設定 SELECT 列 1, 集計関数 ([* ALL DISTINCT] 列 2) FROM 表 A GROUP BY 列 1 HAVING 集計関数の検索条件 ; SELECT 科目コード,COUNT(*),AVG( 成績 ) FROM 表 A GROUP BY 科目コード HAVING COUNT(*) >10 ;

23 2.5 一括挿入 (INSRET) INSERT INTO 商品価格 TBL VALUES (10030, 商品名 BB, 1500, Y 商事,1200) INSERT INTO 商品価格 TBL ( 商品コート商品名価格仕入先単価 ) VALUES (10030, 商品名 BB, 1500, Y 商事,1200) 1 行追加 1 行追加商品コード商品価格 TBL 商品名価格仕入先仕入単価 INSERT INTO 商品価格 TBL SELECT 列 1, 列 2, 列 3, 列 4, 列 5 FROM 別の表 WHERE 検索条件一括挿入 SELECT * にすればバックアップが可能別の表

24 2.6 演算子と関数 1 演算子の種類算術演算子比較演算子ヒット演算子論理演算子集合演算子その他演算子 + = & AND INTERSECT + - > BETWEEN MINUS - * >= ^ IN UNION IS NULL / < ~ NOT JOIN % <= OR CASE <> ALL LIKE!= ANY EXISTS SOME

25 2.6 ( 続き ) 関数 2 関数の種類集計関数文字列関数 AVG VAR ASCII LOWER SAPACE COUNT VARIANCE CHAR LPAD STR GROUPING VARP CHARINDEX LTRIM STUFF MAX CHR NCHAR SUBSTR MIN CONCAT REPLACE SUBSTRING STDDEV INSTR REPLICATE TRIM STDEV LEFT REVERS UNICODE STDEVP LEN RIGHT UPPER SUM LENGTH RPAD LENGTHB RTRIM

26 2.6 ( 続き ) 関数日付関数 ADD_MONTHS DATEDIFF DATENAME DATEPART DAY GETDATE GETUTCDATE MONTH MONTH_BETWEEN SYSDATE YEAR 変換関数 CAST CONVERT DECODE ISNULL NVL TO_CHAR TO_DATE TO_NUMBER

27 2.6 ( 続き ) 関数算術関数 ABS ACOS ASIN ATAN CEIL CEILING COS COT DEGREES EXP FLOOOR LN LOG LOG10 MOD PI POWER RADIANS RAND ROUND SIGN SIN SQUARE SORT TAN TRUNK

28 3. データ操作 ( 副問合せ ) 問合せの中で更に問合せを行う 1 結果が一つのケース複数あればエラー SELECT AVG( 売上数 ) FROM 売上 TBL; 答えは 5になる SELECT 商品 ID FROM 売上 TBL WHERE 売上数 <5 ; SELECT 商品 ID FROM 売上 TBL WHERE 売上数 < (SELECT AVG( 売上数 ) FROM 売上 TBL) ; 売上数の平均を下回っている商品 ID は? 例 : 売上 TBL 商品 ID 売上数 A1 A3 C2 D1 D

29 3.( 続き ) 副問合せ 2 結果が複数のケース例 : 売上 TBL 商品 ID 売上数 A1 2 A3 8 C2 4 D1 5 D3 6 商品 ID A1 A2 A3 C2 D1 商品 TBL 商品名あいうえおカテゴリー飲料飲料飲料弁当菓子 D3 か菓子カテゴリーが飲料の売上を作成する商品 ID 売上数 A1 2 A3 8 SELECT 商品 ID, 売上数 FROM 売上 TBL WHERE 商品 ID IN ( SELECT 商品 ID FROM 商品 TBL WHERE カテゴリー = 飲料 ); いずれかと等しいの意味

30 3.( 続き ) 副問合せ 3 相関副問合せ ( 繰返し ) 例 : 売上 TBL(U) 商品 ID 売上数 A1 2 A3 8 C2 4 D1 5 D3 6 商品 ID A1 A2 A3 C2 D1 商品 TBL (S) 商品名あいうえおカテゴリー飲料飲料飲料弁当菓子 D3 か菓子外側の SQL 文から値を副問合せ文に渡す方法カテゴリーが飲料の売上を作成する商品 ID 売上数 A1 2 A3 8 SELECT 商品 ID, 売上数 FROM 売上 TBL U 2 順番に返す WHERE 飲料 =( SELECT カテゴリー FROM 商品 TBL S WHERE S. 商品 ID = U. 商品 ID) ; 11 行づつ渡す

31 4. 埋め込み SQL ホスト言語にSQL 文を記述する方式文法 1 実行命令 EXEC SQL SQL 文 2 使用変数先頭に : を付けるプリコンパイルが必要

32 5. カーソルの使用 5.1 カーソルとは? 5.2 カーソルの構文 5.3 実行手順 5.4 使用例

33 5.1 カーソルとは? SQLは元来非手続き型言語であり条件を満たす複数の行を一括して扱う一方従来からの言語は手続き型言語であり 1 行づつの処理を行うホスト言語側で埋め込み方式でSQLを使用する場合には SQLにデータを1 行づつ取出す仕組が必要 ( カーソル機能という CURSOR)

34 5.2 カーソルの構文 1 カーソル宣言 ( 定義 ) DECLARE カーソル名 CURSOR FOR 問合せ式 (=SELECT,FROM,WHERE ) ORDER BY 列名 1, 列名 2 2 オープン文 OPEN カーソル名 3 フェッチ文 FETCH カーソル名 INTO データ項目の入れ場所ハラメータ名 1, ハラメータ名 2,, INDICATOR 標識名 4 クローズ文 CLOSE カーソル名 5 カーソル破棄文 DEALLOCATE カーソル名 SQLCODE: 整数 SQLSTATE: 文字列 (5)

35 5.2 ( 続き ) カーソルの構文 6 更新文 DELETE FROM テーブル名 WHERE CURRENT OF カーソル名値が入っている UPDATE テーブル名 SET 列名 = パラメータ名 WHERE CURRENT OF カーソル名

36 5.3 実行手順 1 ホスト言語に SQL 文をコーディング 2 プリコンパイル 3 コンパイル 4 実行 DBMS DECLARE 構文チェックテーブルプログラム OPEN FETCH 処理 CLOSE 導出表作成カーソル操作導出表

37 5.4 使用例省略参照 ( 教科書 161 ページ )

38 6. レポート課題 1 次ページの事例についてテーブル定義の変更後に以下のデータ操作文の具体例を作成してください行の挿入行の更新行の削除問合せ文( 次ページで指示されたもの ) 1 レポートの内容レベルは A4x1 枚程度 2 次回の授業開始時に提出して下さい ( ただしそれ以前に提出する場合はメールで願いますアドレス : fwhy6454@mb.infoweb.ne.jp )

39 6. の課題用の事例 2 課題のテーブル学科 TBL 学科番号年度学科名称主任教授学生 TBL 学籍番号年次入学年度氏名科目 TBL 学科番号所属 TBL 学科コード年度学生コード科目コード年度学科番号期 HS 区分 3 データ検索の事例情報学科 2002 年度データベース論科目の評価粗点の一覧表を作成 ( 学籍番号氏名入学年度評価粗点を出す ( ただし入学年度学籍番号順 ) 上記の範囲で受講者総数平均点を出す科目名称担当教師受講者 TBL 科目コード年度受講者コード評価粗点

40 7. 参考書ほか大木幹雄テータヘース設計の基礎 ( 日本理工出版会 ) 小野哲ほかまるごと図解 SQL がわかる ( 技術評論社 ) 宮坂雅輝 SQL ハンドブック ( ソフトバンク社 ) ライアンほか SQL フロクラミンク入門 ( ソフトハンク ) 村上毅ほか MySQL 活用ガイド ( 秀和システム ) R. ストーンズほか PostgreSQL 活用テクニック ( インフレス ) 松崎為豁データベースの基礎の基礎 ( ソフトバンク ) 山田精一 Oracle のデータベース ( 翔泳社 )

Oracle データベースと PSQL v11 を比較する株式会社エージーテック 2014 年 9 月 30 日

Oracle データベースと PSQL v11 を比較する株式会社エージーテック 2014 年 9 月 30 日 Oracle データベースと PSQL v11 を比較する株式会社エージーテック 2014 年 9 月 30 日免責事項株式会社エージーテックは本書の使用を利用者またはその会社に対して現状のままでのみ許諾するものです株式会社エージーテックはいかなる場合にも本書に記載された内容に関するその他の一切の保証を明示的にも黙示的にも行いません本書の内容は予告なく変更される場合があります商標