SQL データベースアクセスのための文法 担当 岡村耕二 月曜日 ２限 平成21年度 情報科学III （理系コア科目・２年生）

Presentation on theme: "SQL データベースアクセスのための文法 担当 岡村耕二 月曜日 ２限 平成21年度 情報科学III （理系コア科目・２年生）"— Presentation transcript:

1 SQL データベースアクセスのための文法 担当 岡村耕二 月曜日 ２限 平成21年度 情報科学III （理系コア科目・２年生）
2007/5/30 平成21年度 情報科学III （理系コア科目・２年生） SQL データベースアクセスのための文法 担当 岡村耕二 月曜日 ２限 本資料の一部は、堀良彰准教授、天野浩文准教授等による 以前の講義資料をもとにしています。 情報科学III（８）

2 キーに関する補足 ひとつのテーブルには，複数のキーが存在してもよい． （キーが複数のカラムからなる，という意味ではなく，キーとなりうるカラム集合が複数ある，ということ） その場合，キーのうちのひとつを主キー（primary key）と呼ぶ． 一部のカラムの値を決めただけではレコードが一意に決まらない場合でも，そのテーブルのすべてのカラムをまとめたものは，必ずキーとなる． 実世界では，「氏名」や「会社名」などは，必ずしもキーとはならない． 同姓同名の人，同名の会社などはたくさん存在する． そのような場合，個人番号，会社番号といった，必ず一意になるカラムを別に定めることが多い．

3 リレーショナルデータベース言語SQLの基礎 （つづき）

4 SQLの機能の便宜的な分類（再） 問い合わせ機能 SELECT ～ FROM ～ WHERE データ操作機能 SELECT 文という
INSERT 文 UPDATE 文 DELETE 文 データ定義機能 CREATE 文 DROP 文 ALTER 文 その他 ユーザ管理 セッション管理 SELECT 文という 今日やること： 文法の座学＋MySQL での実習

5 INSERT 文 機能 テーブルにレコードを挿入（追加）する． ３種類の構文 カラムの値を指定して１行（レコード）だけ挿入
複数行も一度に挿入 SELECT 文の結果を一度に挿入 INSERT [INTO] テーブル名 SET カラム=値[,カラム=値, ... ] INSERT [INTO] テーブル名 VALUES(値[, 値, ... ])[,　(値[, 値, ... ]), ...]

6 UPDATE 文 機能 テーブル内のレコードの値を変更する． 構文・意味に SELECT 文と類似性がある．
WHERE 句がない場合は，テーブルのレコードをすべて変更してしまう． UPDATE 文を実行する前に，同じ WHERE 句を持つ SELECT 文を実行して，どのようなレコードが影響を受けることになるのか，確認すべき． UPDATE 文の実行時に「よろしいですか？」なんて聞いてくれない． UPDATE テーブル名 SET カラム=値[,カラム=値, ... ] [WHERE 条件式 ]

7 DELETE 文 機能 テーブル内のレコードを削除する． 構文・意味に SELECT 文・ UPDATE 文と類似性がある．
WHERE 句がない場合は，テーブルのレコードをすべて削除してしまう． DELETE 文を実行する前に，同じ WHERE 句を持つ SELECT 文を実行して，どのようなレコードが影響を受けることになるのか，確認すべき． DELETE 文の実行時にも，「よろしいですか？」なんて聞いてくれない． DELETE [FROM] テーブル名 [WHERE 条件式 ]

8 CREATE 文 機能 データベースを新規作成する： CREATE DATABASE 「データベース」とは，テーブルの集まり（容れ物）．
データベースは複数あってもよいが，同時に使えるのは一つだけ． テーブルを新規作成する： CREATE TABLE データベースの作成時 テーブルの作成時 CREATE DATABASE データベース名 CREATE TABLE テーブル名 ( カラム名 データ型 [NULL|NOT NULL] [カラムオプション], ... ) このまとまりを「カラム定義」と呼ぶことにしよう．

9 CREATE TABLE の詳細（１） [NULL|NOT NULL] NULL NULL 値が入ってもよいことを表す．
省略したときはこちらが設定されているとみなされる． NOT NULL NULL 値が入ってはならないことを表す． NULL が指定されていないカラムは，キーとなりうる． 主キーとしたいカラムが単一の場合には，カラムオプションのところで PRIMARY KEY と指定する． NOT NULL を省略しても，それが指定されたのと同じことになる． 主キーが複数のカラムからなる場合は，カラム定義の終わったあとに，PRIMARY KEY 句を別途設ける．

10 CREATE TABLE の詳細（２） データ型の例（１） CHAR(n)
長さ n バイトの固定長文字列（n は最大で255）．ただし，後ろに空白があってもテーブルから取り出すときには削除される． VARCHAR(n) 最大 n バイトの可変長文字（n は最大で255） ． TEXT 最大65,535バイトの可変長文字列． INT 整数 FLOAT 浮動小数点数 これ以降の説明は，DBMSによって，大きな違いがある．ここで取り上げているのは MySQL のもの．

11 CREATE TABLE の詳細（３） データ型の例（２） DATETIME
:00:00 から :59:59 までの日付と時刻 DATE，TIME，YEAR もある． ただし，YEAR の範囲は 1901～2155であるが，不正な値をセットしようとすると 0000 にされる． ENUM('値1', '値2', ...) 列挙された値のいずれか１つ，空文字列，もしくは NULL しかとらないデータ型 NOT NULL 指定した場合は，NULL を除く． SET ('値1', '値2', ...) 列挙された値の中から最大64個までの値を含む集合，空集合，または，NULL しかとらないデータ型

12 CREATE TABLE の詳細（３） その他のカラムオプション DEFAULT 値
INSERT 文で値が指定されずに挿入される場合にセットされる省略値を指定する．

13 DROP 文 機能 テーブルを削除する： DROP TABLE データベースを削除する： DROP DATABASE

14 ALTER 文（１） 機能 テーブルの設定を変更する： ALTER TABLE ALTER DATABASE は省略．
カラムの追加 カラムの変更 カラムの削除

15 ALTER 文（２） カラムの追加 カラムの変更 カラムの削除 ALTER TABLE テーブル名
スライド No.14 参照 ALTER TABLE テーブル名 ADD [COLUMN] カラム定義 [FIRST|AFTER 既存カラム] [ADD ...] カラムの挿入位置．省略時は末尾． ALTER TABLE テーブル名 CHANGE [COLUMN] カラム名 カラム定義 [FIRST|AFTER 既存カラム] [CHANGE ...] カラムのテーブル内での位置を移動させたいときに指定．省略時は元の位置． ALTER TABLE テーブル名 DROP [COLUMN] カラム名 [DROP ...] 「カラム定義」はカラム名から始まるので，名前を変更しないときは，同じ名前が２度繰り返されることになる．

16 ALTER 文に関する補足説明 まだレコードが登録されていないなら，テーブルを変更するのも，いったん削除してから登録し直すのも，そんなに変わらない． ただし，カラムの型を変更すると，他のカラムまで影響を受けることもある． 例：MySQL では，CHAR 型のカラムが複数あるとき，どれかを VARCHAR 型に変更すると他のカラムもすべてVARCHAR 型に合わされる，など． しかし，レコードが登録されてからの ALTER 文の実行には注意が必要である．

17 データ定義文の例（１） CREATE DATABASE te999999_greeting_cards;
CREATE TABLE address_book( name varchar(32) not null primary key, postal_code char(8), address1 char(40), address2 char(40), phone char(13) ); 主キーが単一のカラムからなる場合は，そのカラムの定義の中で PRIMARY KEY と宣言する． 前のスライドまで，文の後ろにセミコロン(；)はなかった．実は，すべてのSQL 文の末尾にセミコロン(；)を書く必要がある． ただし，これは，SQL の文法と言うよりも，MySQLなど，対象となるDBMSの約束事である．このため，書籍によっては，省略されていることもある．

18 データ定義文の例（２） CREATE TABLE letters( name varchar(32) not null,
l_year year not null, sent enum('○', '×'), primary key (name, l_year) ); 主キーが複数のカラムからなる場合は，個々のカラム定義の中ではなく，すべてのカラム定義が終わった後に PRIMARY KEY 句を続ける． 長い文を入力する際に，セミコロンがくる前に改行した場合にどういう現象が起こるかは，使用するDBMSによって異なるだろう． MySQL の場合にどうなるかの説明は，次の節で．

19 データ操作文の例（１） INSERT INTO address_book
SET name='安部晋三', postal_code=' ', address1='東京都千代田区永田町1-6-1', address2='首相官邸', phone='03-xxxx-yyyy'; INSERT INTO address_book VALUES('安部昭恵', ' ', '東京都千代田区永田町1-6-1', '総理大臣官邸','03-xxxx-yyyy'), ('麻生太郎', ' ', '東京都港区六本木7丁目', '衆議院議員宿舎', '03-aaaa-bbbb');

20 データ操作文の例（２） INSERT INTO address_book VALUES('安部晋三', '100-8968',
前ページの上の INSERT 文が終了した後に以下の INSERT 文を実行しようとすると，同じキー値を持ったレコードがすでにあるため，エラーになる． INSERT INTO address_book VALUES('安部晋三', ' ', '山口県山口市...', '安部晋三事務所','083-xxx-yyyy'); UPDATE 文の例 UPDATE address_book SET address2='総理大臣官邸' WHERE name='安部晋三';

21 リレーショナルデータベース管理システムMySQL

22 MySQL MySQL AB 社が公開しているオープンソースのDBMS http://www.mysql.com/ 無償で利用可能
参考になる書籍がたくさん出版されている． 他に，PostgreSQL，Oracle, Microsoft SQL Server など もちろん，SQLの参考書もたくさんあるわけだが，方言が多いので，今回の講義のために入手するのなら，MySQLを対象としたものがよいだろう． 日本語マニュアルも閲覧可能

23 MySQL の対話的利用 MySQL の利用法 対話的利用
TTSSH などでサーバにログインしてから，そのサーバ上で，MySQL クライアントと呼ばれるプログラムを起動する． 後は，このクライアントを経由して利用する． 今日の実習ではこちらを． プログラムの中からの利用 PHP プログラム（webサーバ経由でも可）の中から，MySQL にアクセスするための関数を呼び出す． こちらは次回以降に．

24 MySQL クライアントの起動（MySQL への接続）
boosp 上での設定 MySQL のユーザ名 bossp のログイン名と同じ パスワードは，講義中に紙で配布する． これは，将来よそで MySQL を使うときには異なっているかもしれない． bossp の OS からのプロンプト Linux ユーザ名と MySQL ユーザ名が異なるときは mysql –u username -p ~]$ mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 150 to server version: Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> MySQL クライアントからのプロンプト

25 MySQL クライアントの終了（MySQL からの切断）
quit コマンドを使用する． SQL 文を入力できるところで使えるわけだが，これはもちろん SQL の文ではない． このようなコマンドを，MySQLコマンドと呼んでSQL文と区別することにしよう． MySQL クライアントからのプロンプト mysql> ... mysql> quit; Bye ~]$ bossp の OS からのプロンプト

26 MySQLにおける対話型作業の典型的な手順
サーバ bossp にログイン MySQLクライアントを起動（MySQLに接続） ~] mysql -p （新規データベースの場合のみ）データベース作成 mysql-> CREATE DATABASE dbname; 使用するデータベースの宣言 mysql-> use dbname; （新規テーブルの場合のみ）データ定義 mysql-> CREATE TABLE tablename データ操作 mysql-> SELECT/INSERT/UPDATE/DELETE etc. MySQL クライアントを終了（MySQLから切断） mysql-> quit; サーバ bossp からログアウト これらの手順を忘れると，CREATE TABLE 以下を正しく入力しても無視されてしまう．

27 その他の MySQL コマンド show コマンド データベースに関するさまざまな情報を表示させる． show databases;
show tables; show columns from テーブル名; use コマンド これから利用するデータベースの名前を宣言する． use データベース名; これ以降，このデータベースの中に新しいテーブルを作ったり，既存のテーブルのデータを参照したりできるようになる． use コマンドを実行した後でないと使えない．

28 MySQL クライアントとの対話（１） 長い SQL 文が画面の１行に入りきれないときは そこでとりあえず改行してもよい．
プロンプトの形が “->” に変わって，まだ文が終わっていないことを示してくれる． 終えるときには，セミコロン（；）または \g mysql> CREATE TABLE address_book( -> name varchar(32) not null primary key, -> postal_code char(8), -> address1 char(40), -> address2 char(40), -> phone char(13) -> ); Query OK, 0 rows affected (0.00 sec) mysql>

29 MySQL クライアントとの対話（２） ただし，シングルクォート （' と '） で囲まれた途中で改行すると…
文法的には間違っていないが，変なところに改行文字（ASCIIコードの LF だが \n と表す）が入ってしまう． mysql> create table letters -> ( -> name varchar(32) not null, -> l_year year not null, -> sent enum('○ '> ', -> '×') -> ); mysql> ここでうっかり改行してしまった． すると， '○\n' と入力したことになる．

30 注意 注意：bossp の上の MySQL では： ユーザは，以下の形式の名前をしたデータベースしか作成・使用できないように設定されている．
自分のMySQLユーザ名そのもの 例： te999999 テーブル名にはそのような制約はない．

31 練習問題 下記をテキスト(スライド）のサンプルを見ながら行ってください。 商品名、値段をデータに取るテーブルを作成しなさい。
データベース（そのデーブル）に値を入力してください。 入力した値を確認して下さい。

32 演習問題 地域、商品をデータに取るテーブルを作成しなさい。 データを入力してください。その際、同じ地域名が入るようにしてください。
地域名ごとに商品を出力させてみてください。