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

課題Aのポイント 1)の情報を受け渡す 1)品物、単価情報 アップロード 2)品物、単価表の作成 品物個数入力 3)品物、単価表 入力された品物個数 から、総額計算 while(strlen($lines[$ngoods])>0){ sscanf($lines[$ngoods],"%s %d",$g,$v); $goods[$ngoods]= array("name"=>$g, "value"=>$v); 　$ngoods++; } 連想配列を使用

課題Bのポイント 1)の情報を受け渡す 1)2つの情報を アップロード 2)2つの表を作成 ユーザ入力 3)2つの表、入力された情報 　アップロード 2)2つの表を作成 　ユーザ入力 3)2つの表、入力された情報 　から結果を出力

おさらい（１） データベースとは 「複数の応用目的での共有を意図して組織的かつ永続的に格納されたデータ群」（北川博之著『データベース』，昭晃堂，1996年） なぜ複数のプログラムがファイルでデータを共有してはだめなのか？ いずれのプログラムも，ファイルの中のデータ形式を強く意識して記述しなければならない． 複数のプログラムが同じファイルに同時にアクセスすると，データを破壊してしまう可能性も高い． 同じデータがバラバラのファイルに重複して記録される可能性も高い．

おさらい（２） データベース管理システムの利用 基本的に，すべて「お願い」 プログラム１ プログラム２ プログラム n 例えば： データベース管理システムは，競合するアクセスの間の「交通整理」を行える可能性が出てくる． 例えば： 新たな項目が追加されたくらいなら，プログラムの修正は不要． データの格納される順序が変わっても，プログラムの修正は不要． データ ベース

おさらい（３） リレーショナルデータベース（RDB） データベースは，テーブル（表）の集まり テーブル（table） テーブルは，行に相当するレコード（record）の集まり 列には，おのおの，カラム（column）またはフィールド（field）と呼ばれる名前がついている． NULL 値 不明または非存在を表す特殊な値 テーブルのキー（key） テーブルに格納されたレコードを一意に識別できるカラム（の集まり） キーに同じ値を持つレコード，NULL を含むレコードがあってはならない．

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

前回のおさらい（４） データベース設計の「指針」 テーブル内に同じ情報が重複して記録されるようなら，そのテーブルをうまく分割できないか考える． 同時に使うことの多いカラムが別々のテーブルに分かれて記録されるようなら，それらのテーブルをうまくまとめることができないか考える．

前回のおさらい（５） データベースから情報を取り出す操作 選択（selection） テーブルから，条件を満たすレコードだけを取り出す． 射影（projection） テーブルから，必要なカラムだけを切り出す． 結合（join） 共通のカラムをてがかりに，複数のテーブルを横につなぎ合わせる． 内部結合・自然結合と，外部結合がある． その他 集約演算，グループ化，並べ替え

SELECT [ALL | DISTINCT] SELECT項目リスト FROM テーブル名１[, テーブル名２, ...] 前回のおさらい（６） SQLの問い合わせの基本形 SELECT [ALL | DISTINCT] SELECT項目リスト FROM テーブル名１[, テーブル名２, ...] [ JOIN 結合条件 ] [ WHERE 検索条件 ] [ ] 内は省略可能 | は それで区切られた要素のいずれかひとつを選ぶことを示す． 上の SELECT 節の例では，ALLまたはDISTICTのどちらか一方を書くことができるが，まるまる省略してもよい，ということを意味している．

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

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

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

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

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

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

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

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

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

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

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

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

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

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

データ定義文の例（１） 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の約束事である．このため，書籍によっては，省略されていることもある．

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

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

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

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

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

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

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

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

MySQLにおける対話型作業の典型的な手順 サーバ bossp にログイン MySQLクライアントを起動（MySQLに接続） [te999999@bossp ~] 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 以下を正しく入力しても無視されてしまう．

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

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>

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

実習・課題 スライドのサンプルを元に，自分のデータベースを作ってみよう． 注意：bossp の上の MySQL では： ユーザは，以下のどちらかの形式の名前をしたデータベースしか作成・使用できないように設定されている． 自分のMySQLユーザ名そのもの 例： te999999 それとアンダースコアで始まる名前 例： te999999_greetingcards テーブル名にはそのような制約はない． 課題 作成したデータベースについて文書で報告してください。 このあたりも，将来よそで使うときには，そこの管理者に尋ねること．