情報科学III

平成21年度 情報科学III (理系コア科目・2年生)
SQL データベースアクセスのた
めの文法
担当 岡村耕二
月曜日 2限
http://okaweb.ec.kyushu-u.ac.jp/lectures/jk3/
本資料の一部は、堀良彰准教授、天野浩文准教授等による
1
以前の講義資料をもとにしています。
キーに関する補足
 ひとつのテーブルには,複数のキーが存在してもよい.
(キーが複数のカラムからなる,という意味ではなく,キーとなりう
るカラム集合が複数ある,ということ)
– その場合,キーのうちのひとつを主キー(primary key)と呼
ぶ.
 一部のカラムの値を決めただけではレコードが一意に決まらな
い場合でも,そのテーブルのすべてのカラムをまとめたものは,
必ずキーとなる.
 実世界では,「氏名」や「会社名」などは,必ずしもキーとはなら
ない.
– 同姓同名の人,同名の会社などはたくさん存在する.
– そのような場合,個人番号,会社番号といった,必ず一意に
なるカラムを別に定めることが多い.
2
リレーショナルデータベース言語SQLの基礎
(つづき)
3
SQLの機能の便宜的な分類(再)
 問い合わせ機能
SELECT 文という
– SELECT ~ FROM ~ WHERE
 データ操作機能
– INSERT 文
– UPDATE 文
– DELETE 文
今日やること:
 データ定義機能
文法の座学+MySQL での実習
– CREATE 文
– DROP 文
– ALTER 文
 その他
– ユーザ管理
– セッション管理
4
INSERT 文
 機能
– テーブルにレコードを挿入(追加)する.
 3種類の構文
– カラムの値を指定して1行(レコード)だけ挿入
INSERT [INTO] テーブル名
SET カラム=値[,カラム=値, ... ]
– 複数行も一度に挿入
INSERT [INTO] テーブル名
VALUES(値[, 値, ... ])[, (値[, 値, ... ]), ...]
– SELECT 文の結果を一度に挿入
5
UPDATE 文
 機能
– テーブル内のレコードの値を変更する.
 構文・意味に SELECT 文と類似性がある.
– WHERE 句がない場合は,テーブルのレコードをすべて変更
してしまう.
UPDATE テーブル名
SET カラム=値[,カラム=値, ... ]
[WHERE 条件式 ]
 UPDATE 文を実行する前に,同じ WHERE 句を持つ SELECT
文を実行して,どのようなレコードが影響を受けることになるの
か,確認すべき.
– UPDATE 文の実行時に「よろしいですか?」なんて聞いてく
れない.
6
DELETE 文
 機能
– テーブル内のレコードを削除する.
 構文・意味に SELECT 文・ UPDATE 文と類似性がある.
– WHERE 句がない場合は,テーブルのレコードをすべて削除
してしまう.
DELETE [FROM] テーブル名
[WHERE 条件式 ]
 DELETE 文を実行する前に,同じ WHERE 句を持つ SELECT
文を実行して,どのようなレコードが影響を受けることになるの
か,確認すべき.
– DELETE 文の実行時にも,「よろしいですか?」なんて聞い
てくれない.
7
CREATE 文
 機能
– データベースを新規作成する: CREATE DATABASE
• 「データベース」とは,テーブルの集まり(容れ物).
• データベースは複数あってもよいが,同時に使えるのは
一つだけ.
– テーブルを新規作成する: CREATE TABLE
 データベースの作成時
CREATE DATABASE データベース名
 テーブルの作成時
CREATE TABLE テーブル名
( カラム名 データ型 [NULL|NOT NULL] [カラムオプション],
...
)
このまとまりを「カラム定義」と呼ぶことにしよう.
8
CREATE TABLE の詳細(1)
 [NULL|NOT NULL]
– NULL
• NULL 値が入ってもよいことを表す.
• 省略したときはこちらが設定されているとみなされる.
– NOT NULL
• NULL 値が入ってはならないことを表す.
– NULL が指定されていないカラムは,キーとなりうる.
 主キーとしたいカラムが単一の場合には,カラムオプションのと
ころで PRIMARY KEY と指定する.
– NOT NULL を省略しても,それが指定されたのと同じことに
なる.
 主キーが複数のカラムからなる場合は,カラム定義の終わった
あとに,PRIMARY KEY 句を別途設ける.
9
CREATE TABLE の詳細(2)
 データ型の例(1)
– CHAR(n)
• 長さ n バイトの固定長文字列(n は最大で255).ただし,
後ろに空白があってもテーブルから取り出すときには削
除される.
– VARCHAR(n)
• 最大 n バイトの可変長文字(n は最大で255) .
– TEXT
• 最大65,535バイトの可変長文字列.
– INT
• 整数
– FLOAT
これ以降の説明は,DBMSによって,
• 浮動小数点数
大きな違いがある.ここで取り上げて
いるのは MySQL のもの.
10
CREATE TABLE の詳細(3)
 データ型の例(2)
– DATETIME
• 1000-01-01 00:00:00 から
9999-12-31 23:59:59 までの日付と時刻
• DATE,TIME,YEAR もある. ただし,YEAR の範囲は 1901~2155
であるが,不正な値をセットしようとすると 0000 にされる.
– ENUM('値1', '値2', ...)
• 列挙された値のいずれか1つ,空文字列,もしくは NULL しかとらな
いデータ型
– NOT NULL 指定した場合は,NULL を除く.
– SET ('値1', '値2', ...)
• 列挙された値の中から最大64個までの値を含む集合,空集合,また
は,NULL しかとらないデータ型
– NOT NULL 指定した場合は,NULL を除く.
11
CREATE TABLE の詳細(3)
 その他のカラムオプション
– DEFAULT 値
• INSERT 文で値が指定されずに挿入される場合にセット
される省略値を指定する.
12
DROP 文
 機能
– テーブルを削除する: DROP TABLE
– データベースを削除する: DROP DATABASE
DROP TABLE テーブル名
DROP DATABASE データベース名
13
ALTER 文(1)
 機能
– テーブルの設定を変更する: ALTER TABLE
• ALTER DATABASE は省略.
 ALTER TABLE 文で可能な主な変更の種類
– カラムの追加
– カラムの変更
– カラムの削除
14
ALTER 文(2)
 カラムの追加
スライド No.14 参照
ALTER TABLE テーブル名
ADD [COLUMN] カラム定義 [FIRST|AFTER 既存カラム]
[ADD ...]
 カラムの変更
カラムの挿入位置.省略時は末尾.
ALTER TABLE テーブル名
CHANGE [COLUMN] カラム名 カラム定義 [FIRST|AFTER 既存カラム]
[CHANGE ...]
 カラムの削除
ALTER TABLE テーブル名
DROP [COLUMN] カラム名
[DROP ...]
カラムのテーブル内での位置を移動させ
たいときに指定.省略時は元の位置.
「カラム定義」はカラム名から始まるので,
名前を変更しないときは,同じ名前が2度
繰り返されることになる.
15
ALTER 文に関する補足説明
 まだレコードが登録されていないなら,テーブルを変更するのも,
いったん削除してから登録し直すのも,そんなに変わらない.
– ただし,カラムの型を変更すると,他のカラムまで影響を受
けることもある.
• 例:MySQL では,CHAR 型のカラムが複数あるとき,どれ
かを VARCHAR 型に変更すると他のカラムもすべて
VARCHAR 型に合わされる,など.
 しかし,レコードが登録されてからの ALTER 文の実行には注
意が必要である.
16
データ定義文の例(1)
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の約束事であ
る.このため,書籍によっては,省略されていることもある.
17
データ定義文の例(2)
CREATE TABLE letters(
name varchar(32) not null,
l_year year not null,
sent enum('○', '×'),
primary key (name, l_year)
);
主キーが複数のカラムからなる場合
は,個々のカラム定義の中ではなく,
すべてのカラム定義が終わった後
に PRIMARY KEY 句を続ける.
長い文を入力する際に,セミコロンがくる前に改行した場合にどういう現象が起こる
かは,使用するDBMSによって異なるだろう.
MySQL の場合にどうなるかの説明は,次の節で.
18
データ操作文の例(1)
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');
19
データ操作文の例(2)
前ページの上の INSERT 文が終了した後に以下の INSERT 文を実行しようとすると,
同じキー値を持ったレコードがすでにあるため,エラーになる.
INSERT INTO address_book
VALUES('安部晋三', '100-8968',
'山口県山口市...',
'安部晋三事務所','083-xxx-yyyy');
UPDATE 文の例
UPDATE address_book
SET address2='総理大臣官邸'
WHERE name='安部晋三';
20
リレーショナルデータベース管理システムMySQL
21
MySQL
 MySQL AB 社が公開しているオープンソースのDBMS
– http://www.mysql.com/
– 無償で利用可能
 最もポピュラーなDBMSの一つ
– 参考になる書籍がたくさん出版されている.
• 他に,PostgreSQL,Oracle, Microsoft SQL Server など
– もちろん,SQLの参考書もたくさんあるわけだが,方言が多
いので,今回の講義のために入手するのなら,MySQLを対
象としたものがよいだろう.
 日本語マニュアルも閲覧可能
– http://dev.mysql.com/doc/refman/4.1/ja/index.html
22
MySQL の対話的利用
 MySQL の利用法
– 対話的利用
• TTSSH などでサーバにログインしてから,そのサーバ上
で,MySQL クライアントと呼ばれるプログラムを起動する.
• 後は,このクライアントを経由して利用する.
• 今日の実習ではこちらを.
– プログラムの中からの利用
• PHP プログラム(webサーバ経由でも可)の中から,
MySQL にアクセスするための関数を呼び出す.
• こちらは次回以降に.
23
MySQL クライアントの起動(MySQL への接続)
 boosp 上での設定
– MySQL のユーザ名
• bossp のログイン名と同じ
• パスワードは,講義中に紙で配布する.
bossp の OS からのプロンプト
これは,将来よそ
で MySQL を使
うときには異なっ
ているかもしれな
い.
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 クライアントからのプロンプト
24
MySQL クライアントの終了(MySQL からの切断)
 quit コマンドを使用する.
– SQL 文を入力できるところで使えるわけだが,これはもちろ
ん SQL の文ではない.
– このようなコマンドを,MySQLコマンドと呼んでSQL文と区別
することにしよう.
MySQL クライアントからのプロンプト
mysql> ...
mysql> ...
mysql> quit;
Bye
[te999999@bossp ~]$
bossp の OS からのプロンプト
25
MySQLにおける対話型作業の典型的な手順
1.
サーバ bossp にログイン
2.
MySQLクライアントを起動(MySQLに接続)
[te999999@bossp ~] mysql -p
3.
(新規データベースの場合のみ)データベース作成
mysql-> CREATE DATABASE dbname;
4.
使用するデータベースの宣言
mysql-> use dbname;
5.
(新規テーブルの場合のみ)データ定義
mysql-> CREATE TABLE tablename
6.
データ操作
mysql-> SELECT/INSERT/UPDATE/DELETE etc.
7.
MySQL クライアントを終了(MySQLから切断)
mysql-> quit;
8.
サーバ bossp からログアウト
これらの手順を忘
れると,CREATE
TABLE 以下を正し
く入力しても無視さ
れてしまう.
26
その他の MySQL コマンド
 show コマンド
– データベースに関するさまざまな情報を表示させる.
• show databases;
• show tables;
use コマンドを実
行した後でないと
• show columns from テーブル名;
使えない.
 use コマンド
– これから利用するデータベースの名前を宣言する.
• use データベース名;
– これ以降,このデータベースの中に新しいテーブルを作っ
たり,既存のテーブルのデータを参照したりできるようになる.
27
MySQL クライアントとの対話(1)
 長い SQL 文が画面の1行に入りきれないときは
– そこでとりあえず改行してもよい.
– プロンプトの形が “->” に変わって,まだ文が終わっていな
いことを示してくれる.
• 終えるときには,セミコロン(;)または \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>
28
MySQL クライアントとの対話(2)
 ただし,シングルクォート (' と ') で囲まれた途中で改行すると
…
– 文法的には間違っていないが,変なところに改行文字
(ASCIIコードの LF だが \n と表す)が入ってしまう.
mysql>
->
->
->
->
'>
->
->
mysql>
create table letters
(
name varchar(32) not null,
l_year year not null,
sent enum('○
',
'×')
);
ここでうっかり改行
してしまった.
すると,
'○\n' と入力した
ことになる.
29
注意
 注意:bossp の上の MySQL では:
– ユーザは,以下の形式の名前をしたデータベースしか作
成・使用できないように設定されている.
• 自分のMySQLユーザ名そのもの
例: te999999
– テーブル名にはそのような制約はない.
30
練習問題
 下記をテキスト(スライド)のサンプルを見ながら行ってください。
– 商品名、値段をデータに取るテーブルを作成しなさい。
– データベース(そのデーブル)に値を入力してください。
– 入力した値を確認して下さい。
31
演習問題
 地域、商品をデータに取るテーブルを作成しなさい。
 データを入力してください。その際、同じ地域名が入るようにし
てください。
 地域名ごとに商品を出力させてみてください。
32