SQLエディタによる データベースプログラミング

Presentation on theme: "SQLエディタによる データベースプログラミング"— Presentation transcript:

1 SQLエディタによる データベースプログラミング
01

2 複数のシステムで共有できるようにしたもの
前回のおさらい データベースとはデータを１つにまとめて 複数のシステムで共有できるようにしたもの 蔵書管理システム 貸出管理システム 生徒ファイル 生徒番号　学年　クラス　 番号　名前　性別　住所 貸出ファイル 貸出番号　図書番号 貸出月　貸出日 蔵書ファイル 図書番号　書名　 著者名　分類 データベース データベース管理システム（DBMS） 情報システム

3 関係データベースではデータを行（レコード）と 列（フィールド）による2次元の表（テーブル）で あらわし複数の表を組み合わせてデータを管理する
前回のおさらい 前回のおさらい 関係データベースではデータを行（レコード）と 列（フィールド）による2次元の表（テーブル）で あらわし複数の表を組み合わせてデータを管理する 列（フィールド）：データ項目 生徒番号 学年 クラス 番号 名前 性別 住所 12001 1 奥沢一輝 男 狛田村 12002 2 岡田一男 佐津町 12003 3 吉村一郎 坂本村 行（レコード）：1件ずつのデータ

4 関係データベースの表の操作 射影 指定した列（フィールド）を取り出す 選択 指定した行（レコード）を取り出す 結合
前回のおさらい 関係データベースの表の操作 射影 指定した列（フィールド）を取り出す 選択 指定した行（レコード）を取り出す 結合 2つ以上の表（テーブル）を結びつける サクセスでこれらの操作を体験

5 データベースに登録されているデータを プログラムで検索しよう
今回の学習内容 データベースに登録されているデータを プログラムで検索しよう ・コンピュータにはプログラムで指示をする ・プログラムはコンピュータに伝わる言語 （プログラミング言語）で書く ・SQLはデータベースを操作するための言語 ・今日はSQLでデータベースからデータを検索してみよう 前回は図書館だったが、今回は一般の商品を考えてみよう!!!

6 世の中のシステムでは、SQLというデータベースを 操作するための言語が使われている
一般の商品をサクセスで考えると… サクセス：「表示　商品データ」 サクセス：「射影　商品コード、商品名」 商品コード 商品名 メーカー 価格 C4009 チキンヌードル＜カレー味＞ みずうみ製麺 150 C6390 坦々ヌードル C7320 トマトヌードル C8522 シーフードヌードル ： 商品コード 商品名 C4009 チキンヌードル＜カレー味＞ C6390 坦々ヌードル C7320 トマトヌードル C8522 シーフードヌードル ： サクセス：「選択　商品名　トマトヌードル」 商品 コード 商品名 メーカー 価格 C7320 トマトヌードル みずうみ製麺 150 世の中のシステムでは、SQLというデータベースを 操作するための言語が使われている

7 pid pname pmaker pprice
今回は「商品表（テーブル）」を扱う ・表（テーブル）名：product ・4個の列（フィールド）がある。 ・pidは商品コード。 　この番号で商品を特定できる。（同じ番号の商品はない） ・pname, pmaker, ppriceに商品名、メーカー、価格が 　入っている。 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

8 SQLエディタを起動 ● SQLエディタ http://saccess.eplang.jp/try/sql/

9 クラスIDとユーザIDの入力 クラスID・ユーザID入力エリアに それぞれ指示のあったIDを入力してください。
クラスID　: 　指定した6桁の数字 ユーザID　:　 自分の番号など（例: A07）

10 SQLエディタの使い方(1) ・画面のSQL実行エリアにプログラムを入れて実行する。 SQL実行エリア

11 SQLエディタの使い方(2) 実行したプログラムは上の履歴表示エリアに追加される
履歴表示エリアのプログラムはマウスでクリックで 再度使える。 実行した結果は下に表示される。 実行されたプログラムの履歴 SQLを実行すると 実行結果が表示される

12 SQLエディタの使い方(3) SQL文が間違っていたら、エラーメッセージが表示される。

13 まずはデータベースに接続しよう 今回は「shop」データベースを使用します。 SQL実行エリアに「use shop;」と入力しましょう。

14 まずはデータベースに接続しよう 履歴表示エリアに入力したプログラムが表示されます。 SQL実行エリアの下に接続成功のコメントが表示されます。
入力したプログラムが履歴として表示 接続成功のメッセージ

15 SQLの書き方 ・英語の単語で命令を書く。英語の文章と並びが似ている。 select * from product;
（英文として読むと「商品表（テーブル）から * を選べ」という意味になる） ・「select」は「選択した結果を表示する」命令。 ・「*」は「すべての項目」の意味。 ・「product」は「表（テーブル）の名前」。 ・英文と同じく単語を空白で区切る。末尾は「.」ではなく「 ;」（セミコロン）を書く。

16 select フィールド名 from テーブル名;
射影の書き方 select フィールド名 from テーブル名; ・「 * 」の部分に列（フィールド）名を書くと 　そのフィールドだけが表示される。 ・2個以上の列（フィールド）名は「 , 」（カンマ）で区切る。 (例）商品名と価格のフィールドを表示する select pname,pprice from product;

17 select フィールド名 from テーブル名;
例題 1.1 「商品名」だけを表示しよう。 ● 射影の書き方 ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

18 select pname from product;
例題 1.1 「商品名」だけを表示しよう。 　　　　select pname from product; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

19 select pname from product;
例題 1.1 「商品名」だけを表示しよう。 　　　　select pname from product; 解答

20 select フィールド名, フィールド名 from テーブル名;
例題 1.2 「商品名」と「メーカー」を表示しよう ● 射影の書き方 ※2個以上のフィールド名は「，」（カンマ）で区切る ● 「product」テーブルのフィールド名 select フィールド名, フィールド名 from テーブル名; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

21 select pname,pmaker from product;
例題 1.2 「商品名」と「メーカー」を表示しよう select pname,pmaker from product; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

22 select pname,pmaker from product;
例題 1.2 「商品名」と「メーカー」を表示しよう select pname,pmaker from product; 解答

23 select フィールド名 from テーブル名 where 条件式;
選択条件の書き方 select フィールド名 from テーブル名 where 条件式; ・条件で選択するときは「where」に続いて条件を書く。 ・等しい 「=」　　　　・大きい小さい「>」「<」 ・以上以下「>=」「<=」 ・等しくない　　「<>」 (例）140円の商品を表示する select * from product where pprice=140;

24 select フィールド名 from テーブル名 where 条件式;
例題 2.1 「150円の商品」を表示しよう ● 選択条件の書き方 ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名 where 条件式; ・等しい 「=」　　　　・大きい小さい「>」「<」 ・以上以下「>=」「<=」 ・等しくない　　「<>」 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

25 select * from product where pprice=150;
例題 2.1 「150円の商品」を表示しよう select * from product where pprice=150; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

26 select * from product where pprice=150;
例題 2.1 「150円の商品」を表示しよう select * from product where pprice=150; 解答

27 select フィールド名 from テーブル名 where 条件式;
例題 2.2 「150円以上の商品」を表示しよう ● 選択条件の書き方 ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名 where 条件式; ・等しい 「=」　　　　・大きい小さい「>」「<」 ・以上以下「>=」「<=」 ・等しくない　　「<>」 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

28 select * from product where pprice>=150;
例題 2.2 「150円以上の商品」を表示しよう select * from product where pprice>=150; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

29 select * from product where pprice>=150;
例題 2.2 「150円以上の商品」を表示しよう select * from product where pprice>=150; 解答

30 select フィールド名 from テーブル名 where 条件式;
例題 2.3 「150円以外の商品」を表示しよう ● 選択条件の書き方 ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名 where 条件式; ・等しい 「=」　　　　・大きい小さい「>」「<」 ・以上以下「>=」「<=」 ・等しくない　　「<>」 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

31 select * from product where pprice<>150;
例題 2.3 「150円以外の商品」を表示しよう select * from product where pprice<>150; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

32 select * from product where pprice<>150;
例題 2.3 「150円以外の商品」を表示しよう select * from product where pprice<>150; 解答

33 select フィールド名 from テーブル名 where フィールド名＝’文字列’;
選択条件の書き方（文字列） select フィールド名 from テーブル名 where フィールド名＝’文字列’; ・文字で選択するときは、条件の文字列を「’」 　　　　　　　　　 （クォーテーション）で囲む。 (例）メーカが「ラビット乳業」の商品を表示する select * from product where pmaker='ラビット乳業’;

34 select フィールド名 from テーブル名 where フィールド名＝’文字列’;
例題 3.1 「メーカーがみずうみ製麺の商品」 　 　を表示しよう ● 選択条件の書き方（文字列） ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名 where フィールド名＝’文字列’; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

35 「メーカーがみずうみ製麺の商品」 を表示しよう pid pname pmaker pprice
例題 3.1 「メーカーがみずうみ製麺の商品」 　 　を表示しよう select * from product where pmaker=’みずうみ製麺’; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

36 「メーカーがみずうみ製麺の商品」 を表示しよう select * from product where pmaker=’みずうみ製麺’;
例題 3.1 「メーカーがみずうみ製麺の商品」 　 　を表示しよう select * from product where pmaker=’みずうみ製麺’; 解答

37 select フィールド名 from テーブル名 where フィールド名＝’文字列’;
例題 3.2 「商品名が麦茶の商品」を表示しよう ● 選択条件の書き方（文字列） ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名 where フィールド名＝’文字列’; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

38 「商品名が麦茶の商品」を表示しよう select * from product where pname=’麦茶’; pid pname
例題 3.2 「商品名が麦茶の商品」を表示しよう select * from product where pname=’麦茶’; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

39 例題 3.2 「商品名が麦茶の商品」を表示しよう select * from product where pname=’麦茶’; 解答

40 select フィールド名 from テーブル名 where 条件式 AND 条件式;
選択条件の書き方（複数の条件） select フィールド名 from テーブル名 where 条件式 AND 条件式; ・2個以上の条件は「AND」でつなぐ。 　　　　　　　　（どちらかが成り立てばよい場合は「OR」） (例）「ラビット乳業の140円」の商品を表示する select * from product where pprice=140 AND pmaker=’ラビット乳業’;

41 select フィールド名 from テーブル名 where 条件式 AND 条件式;
例題 4.1 「みずうみ製麺の150円の商品」 を表示しよう ● 選択条件の書き方（複数の条件） ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名 where 条件式 AND 条件式; ※2個以上の条件は「AND」、どちらかが成り立てばよい場合は「OR」 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

42 「みずうみ製麺の150円の商品」 を表示しよう pid pname pmaker pprice 例題 4.1 解答 意味 商品コード 商品名
select * from product where pmaker=’みずうみ製麺’ AND pprice=150; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

43 「みずうみ製麺の150円の商品」 を表示しよう 例題 4.1 解答
select * from product where pmaker=’みずうみ製麺’ AND pprice=150; 解答

44 「150円以上か140円未満の商品」 を表示しよう ● 選択条件の書き方（複数の条件） ● 「product」テーブルのフィールド名 pid
例題 4.2 「150円以上か140円未満の商品」 を表示しよう ● 選択条件の書き方（複数の条件） ● 「product」テーブルのフィールド名 select フィールド名 from テーブル名 where 条件式 AND 条件式; select フィールド名 from テーブル名 where 条件式 AND 条件式; ※2個以上の条件は「AND」、どちらかが成り立てばよい場合は「OR」 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

45 「150円以上か140円未満の商品」 を表示しよう pid pname pmaker pprice 例題 4.2
select * from product where pprice>=150 OR pprice<140; 解答 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

46 「150円以上か140円未満の商品」 を表示しよう 例題 4.2 解答
select * from product where pprice>=150 OR pprice<140; 解答

47 まとめ ・SQLを使うと、1行で選択や射影を 行えて便利 ・来週はSQLを使った検索の続きを行う
宿題 最後の授業でオリジナルデータベースを使った演習を行うので 登録したい商品情報を考え、配布のExcelシートに記入する。

48 SQLエディタによる データベースプログラミング
02

49 SQLの振り返り ・表示の基本形 ・射影 ・選択条件 select * from product;
※フィールド名の代わりに「＊（アスタリスク）」を指定すると 　全てのフィールドの値を返す。 select フィールド名 from テーブル名; select フィールド名 from テーブル名 where 条件式;

50 前回と同様に「商品表（テーブル）」を扱う
・表（テーブル）名：product ・4個の列（フィールド）がある。 ・pidは商品コード。 　この番号で商品を特定できる。（同じ番号の商品はない） ・pname, pmaker, ppriceに商品名、メーカー、価格が 　入っている。 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

51 SQLエディタを起動 ● SQLエディタ http://oecu-edu.sakura.ne.jp/sql/

52 クラスIDとユーザIDの入力 クラスID・ユーザID入力エリアに それぞれ指示のあったIDを入力してください。
クラスID　: 　指定した6桁の数字 ユーザID　:　 自分の番号など（例: A07）

53 データベースに接続する 演習では「shop」データベースを使用します。 SQL実行エリアに「use shop;」と入力しましょう。

54 結果の並べ方 ・「order by asc」で結果を指定したフィールドで 昇順（小さい順）に並べられる。※ascは省略可
select フィールド名 from テーブル名 order by フィールド名 asc; ・「order by asc」で結果を指定したフィールドで 　昇順（小さい順）に並べられる。※ascは省略可 ・「desc」を付けると降順（大きい順）になる。 (例）価格の安い順 select * from product order by pprice; (例）価格の高い順 select * from product order by pprice desc;

55 価格の安い順に並べた際に 最初にある商品名とメーカーは何か？
問 1 価格の安い順に並べた際に 最初にある商品名とメーカーは何か？ ● 昇順に並べる書き方　 ・価格が安い順 select フィールド名 from テーブル名 order by フィールド名 asc; select * from product order by pprice; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

56 価格の安い順に並べた際に 最初にある商品名とメーカーは何か？
問 1 価格の安い順に並べた際に 最初にある商品名とメーカーは何か？ 解答

57 価格の高い順に並べた際に 最初にある商品名とメーカーは何か？
問 2 価格の高い順に並べた際に 最初にある商品名とメーカーは何か？ ● 昇順に並べる書き方　 ・価格が高い順 select フィールド名 from テーブル名 order by フィールド名 desc; select * from product order by pprice desc; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

58 価格の高い順に並べた際に 最初にある商品名とメーカーは何か？
問 2 価格の高い順に並べた際に 最初にある商品名とメーカーは何か？ 解答

59 文字を含む検索の書き方 select フィールド名 from テーブル名 where フィールド名 like '検索条件';
・「%」を検索条件（文字列）の前後において指定する。 ※「%」は「この位置にどんな文字列があってもいい（なくてもいい）」という意味 ※「%」は必ず半角で書くこと！ （例）「茶」という文字を含む商品名を選択する select * from product where pname like ’%茶%’;

60 「商品名に麦を含む商品」を表示しよう。 ● 文字を含む検索の書き方 pid pname pmaker pprice
問 3 「商品名に麦を含む商品」を表示しよう。 ● 文字を含む検索の書き方　 ・「%」を検索条件（文字列）の前後において指定する。 (例）like ‘%茶%’;　　 生姜番茶、煎茶百年　　　 select フィールド名 from テーブル名 where フィールド名 like '検索条件'; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

61 「商品名に麦を含む商品」を表示しよう。 pid pname pmaker pprice
問 3 「商品名に麦を含む商品」を表示しよう。 解答 select * from product where pname like '%麦%'; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

62 「商品名に麦を含む商品」を表示しよう。 select * from product where pname like '%麦%'; 問 3
解答 select * from product where pname like '%麦%';

63 「商品名が茶で終わる商品」を表示しよう。
問 4 「商品名が茶で終わる商品」を表示しよう。 ● 文字を含む検索の書き方　 ・「%」を検索条件（文字列）の前後において指定する。　　 select フィールド名 from テーブル名 where フィールド名 like '検索条件'; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

64 「商品名が茶で終わる商品」を表示しよう。
問 4 「商品名が茶で終わる商品」を表示しよう。 解答 select * from product where pname like '%茶'; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

65 「商品名が茶で終わる商品」を表示しよう。
問 4 「商品名が茶で終わる商品」を表示しよう。 解答 select * from product where pname like '%茶';

66 結果の件数や合計を表示する select count(フィールド名) from テーブル名;
・結果の件数 ・合計 ・平均 select count(フィールド名) from テーブル名; select sum(フィールド名) from テーブル名; select avg(フィールド名) from テーブル名; （例）全てのレコード数を表示する select count(*) from product; ※ 「*(アスタリスク)」を指定した場合はすべてのレコード数を取得

67 pid pname pmaker pprice
問 5 価格が150円の商品のレコード数は？ ● 結果の件数の書き方　 ● 選択条件の書き方 select count(*) from テーブル名; select フィールド名 from テーブル名 where 条件式; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

68 pid pname pmaker pprice
問 5 価格が150円の商品のレコード数は？ 解答 select count(*) from product where pprice=150; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

69 価格が150円の商品のレコード数は？ select count(*) from product where pprice=150; 問 5
解答 select count(*) from product where pprice=150;

70 pid pname pmaker pprice
問 6 「商品の価格の合計」を表示しよう。 ● 合計の書き方　 select sum(フィールド名) from テーブル名; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

71 pid pname pmaker pprice
問 6 「商品の価格の合計」を表示しよう。 解答 select sum(pprice) from product; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

72 問 6 「商品の価格の合計」を表示しよう。 解答 select sum(pprice) from product;

73 select avg(フィールド名) from テーブル名;
問 7 「商品の平均価格」を表示しよう。 ● 結果の件数の書き方　 select avg(フィールド名) from テーブル名; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

74 select avg(pprice) from product;
問 7 「商品の平均価格」を表示しよう。 解答 select avg(pprice) from product; 列(フィールド)名 pid pname pmaker pprice 意味 商品コード 商品名 メーカー 価格

75 select avg(pprice) from product;
問 7 「商品の平均価格」を表示しよう。 解答 select avg(pprice) from product;

76 まとめ 「order by」で検索結果を小さい順などに 並べ替えられる。 「%」で文字を含む検索を行える。
「count」や「sum」で件数や合計を 計算できる。 来週は登録したデータを検索するインターネットの プログラムを体験する。 宿題 HTML、CSS、PHPがどのようなものか調べておく