SQL J2EE I 第3回 / 2005-04-28.

Presentation on theme: "SQL J2EE I 第3回 / 2005-04-28."— Presentation transcript:

1 SQL J2EE I 第3回 /

2 きょうの内容 SQLの概要を知る。

3 リレーショナルデータベースの 仕組み 「データベース」は、「テーブル」から構成される 「テーブル」は、「行」から構成される
「行」は、「項目」から構成される no title author publisher 42377 伽藍とバザール 山形浩生訳 光芒社 42872 Java言語で学ぶデザインパターン入門 結城浩　著 ソフトバンク

4 SQL リレーショナルデータベースを操作するための言語。
ほかには SQL89, SQL92, SQL99

5 テーブルの作成 (1) create table テーブル名 ( 項目名 データ型, ..... );

6 テーブルの作成 (2) create table books ( ndc varchar(10),
tyosya_hyouji varchar(2), id int, title varchar(50), author varchar(50), publisher varchar(30), constraint pk_books primary key(id) );

7 データ型 データにはSQL標準の型がある。 他にも、DBMSごとに独自のデータ型がある。 char(n) 固定長の文字列 (長さ n)
varchar(n) 可変長の文字列 (長さ n) int 符号付き整数 float(p) 浮動小数点 (p は精度) date 日付 time 時刻 他にも、DBMSごとに独自のデータ型がある。

8 制約 (1) 制約とは何か テーブルの項目に入る値の条件 制約には２通りある。 列制約  ひとつの列に制約を加える
テーブル制約  複数の列に同時に制約を加える

9 制約 (2) 主キー制約  主キーを指定する 列制約 テーブル制約 create table books (
制約 (2)　主キー制約  主キーを指定する 列制約 create table books ( ndc varchar(10), tyosya_hyouji varchar(2), id int primary key, title varchar(50), author varchar(50), publisher varchar(30) ); テーブル制約 create table books ( ndc varchar(10), tyosya_hyouji varchar(2), id int, title varchar(50), author varchar(50), publisher varchar(30), primary key (id) );

10 制約 (3) not null 制約  列に null を許さない
create table books ( ..... title varchar(50) not null, );

11 制約 (4) unique 制約  データの重複を許さない
create table students ( ..... varchar(50) unique, );

12 制約 (5) check 制約  指定した論理式をチェック
create table books ( ..... id int check (0 <= id and id < ), );

13 制約 (6) 制約名 (列制約) constraint 制約名 制約 制約にひっかかると制約名でエラーが表示
制約 (6) 制約名 (列制約) create table books ( ndc varchar(10), tyosya_hyouji varchar(2), id int constraint pk_books primary key, title varchar(50), author varchar(50), publisher varchar(30) ); constraint 制約名 制約 制約にひっかかると制約名でエラーが表示

14 制約 (7) 制約名 (テーブル制約) create table books ( ndc varchar(10),
制約 (7) 制約名 (テーブル制約) create table books ( ndc varchar(10), tyosya_hyouji varchar(2), id int, title varchar(50), author varchar(50), publisher varchar(30), constraint pk_books primary key (id), constraint check_id check (0 <= id and id<100000) );

15 制約 (8) 外部キーの設定 (1) no タイトル 著者 出版社 42872 Java言語で学ぶデザインパターン入門 結城浩 著
結城浩　著 ソフトバンク no keyword 42872 Java デザインパターン それぞれのテーブルには「no」という項目がある。この項目を通じて２つのテーブルを結合できる。 　　↑ この「no」が外部キー。元のテーブルを参照できる。

16 制約 (9) 外部キーの設定 (2) create table テーブル名 ( 項目名 データ型 .....
項目名 データ型 ..... constraint 制約名 foreign key(項目名) references 参照テーブル名(項目名) );

17 制約 (10) 外部キーの設定 (3) create table books (
id int constraint pk_books primary key, ..... ); create table keywords ( id int, keyword varchar(30), constraint fk_id foreign key(id) references books(id)

18 テーブルの削除 drop table テーブル名 例) drop table books;

19 データの検索 (1) 基本パターン select 項目のリスト from テーブルのリスト where 検索条件

20 データの検索 (2) 検索条件が無い場合 select title from books;
テーブル books 中のすべてのデータから、title という項目を取り出す select id, title from books; テーブル books 中のすべてのデータから、id と title という２つの項目を取り出す select * from books; テーブル books 中のすべてのデータから、すべての項目を取り出す

21 データの検索 (3) 単純な検索条件 select id, title from books where id < 30000;
select title, publisher from books where publisher = ‘八千代出版’; 出版社が「八千代出版」のものを検索 「八千代出版会」はヒットしない。同一でなければダメ

22 データの検索 (4) 論理演算 (and) select id, title from books
where <= id and id < 43000; Id が 以上かつ 未満のものを検索 where between and 43000; こう書くこともできる

23 データの検索 (5) 論理演算 (or) select title, author, publisher from books
where publisher = ‘群像社’ or publisher = '八千代出版'; 出版社が「群像社」か「八千代出版」のものを検索

24 データの検索 (6) like とワイルドカード
select title from books where title like 'Java%'; 「Java」からはじまるタイトルの本を検索 where title like '%Java%'; タイトルに「Java」を含む本を検索

25 データの検索 (7) like とワイルドカード
select title from books where title like '%Java%' or title like '%Perl%'; タイトルに「Java」か「Perl」を含む本を検索

26 データの検索 (8) テーブルの結合 (1) あるタイトルの本のキーワードを知りたい。 テーブルは正規化によって分割されている。 no
著者 出版社 42872 Java言語で学ぶデザインパターン入門 結城浩　著 ソフトバンク あるタイトルの本のキーワードを知りたい。 テーブルは正規化によって分割されている。 no Keyword 42872 Java デザインパターン

27 データの検索 (9) テーブルの結合 (2) 複数のテーブルを使う場合、項目名の指定は次の形になる。
テーブル名.項目名 検索条件として、それぞれのテーブルの id が一致している必要がある。 select books.title, keywords.keyword from books, keywords where books.id = keywords.id;

28 データの検索 (10) テーブルの結合 (3) SQL92 以降では次のように書ける
select books.title, keywords.keyword from books join keywords on books.id = keywords.id;

29 データの検索 (11) テーブルの結合 (4) books.id = keywords.id のように、id という項目名が共通の場合、次のようにも書ける。(HSQLDBでは動かない) select books.title, keywords.keyword from books join keywords using(id);

30 データの登録 insert into テーブル名 values (値1, 値2, ...) 例)
insert into books values ( '361', 'M', '41439', '情報メディア論', '丸山不二夫　編著', '八千代出版' ); create table での項目の順番通りに指定する

31 データの更新 update テーブル名 set 項目名 = 式 where 更新条件 例) update books
set author = '丸山不二夫　編著 / 植田龍男　ほか著' where id = '41439'; where句がないとすべての行が更新されてしまう！

32 データの削除 delete from テーブル名 where 削除条件 例)
delete from books where id = '41439'; where句がないとすべての行が削除されてしまう！

33 参考文献・URL UNIXデータベース入門 はじめてでもわかるSQLとデータ設計
丸山学長によるデータベース入門。SQL89をベースにしていますが、よくまとまっている有益なテキストです。 はじめてでもわかるSQLとデータ設計 西村めぐみ著 ディー・アート ISBN きわめてわかりやすく解説されています。