情報科学III

平成２３年度前期情報科学III
SQL データベースアクセスのた
めの文法
担当松永裕介
月曜日２限
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 文
 機能
– テーブルにレコードを挿入（追加）する．
 ３種類の構文
– カラムの値を指定して１行（レコード）だけ挿入
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 の詳細（１）
 [NULL|NOT NULL]
– NULL
• NULL 値が入ってもよいことを表す．
• 省略したときはこちらが設定されているとみなされる．
– NOT NULL
• NULL 値が入ってはならないことを表す．
– NULL が指定されていないカラムは，キーとなりうる．
 主キーとしたいカラムが単一の場合には，カラムオプションのと
ころで PRIMARY KEY と指定する．
– NOT NULL を省略しても，それが指定されたのと同じことに
なる．
 主キーが複数のカラムからなる場合は，カラム定義の終わった
あとに，PRIMARY KEY 句を別途設ける．
9
CREATE TABLE の詳細（２）
 データ型の例（１）
– CHAR(n)
• 長さ n バイトの固定長文字列（n は最大で255）．ただし，
後ろに空白があってもテーブルから取り出すときには削
除される．
– VARCHAR(n)
• 最大 n バイトの可変長文字（n は最大で255）．
– TEXT
• 最大65,535バイトの可変長文字列．
– INT
• 整数
– FLOAT
これ以降の説明は，DBMSによって，
• 浮動小数点数
大きな違いがある．ここで取り上げて
いるのは MySQL のもの．
10
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 しかとらないデータ型
– NOT NULL 指定した場合は，NULL を除く．
11
CREATE TABLE の詳細（３）
 その他のカラムオプション
– DEFAULT 値
• INSERT 文で値が指定されずに挿入される場合にセット
される省略値を指定する．
12
DROP 文
 機能
– テーブルを削除する： DROP TABLE
– データベースを削除する： DROP DATABASE
DROP TABLE テーブル名
DROP DATABASE データベース名
13
ALTER 文（１）
 機能
– テーブルの設定を変更する： ALTER TABLE
• ALTER DATABASE は省略．
 ALTER TABLE 文で可能な主な変更の種類
– カラムの追加
– カラムの変更
– カラムの削除
14
ALTER 文（２）
 カラムの追加
スライド No.14 参照
ALTER TABLE テーブル名
ADD [COLUMN] カラム定義 [FIRST|AFTER 既存カラム]
[ADD ...]
 カラムの変更
カラムの挿入位置．省略時は末尾．
ALTER TABLE テーブル名
CHANGE [COLUMN] カラム名カラム定義 [FIRST|AFTER 既存カラム]
[CHANGE ...]
 カラムの削除
ALTER TABLE テーブル名
DROP [COLUMN] カラム名
[DROP ...]
カラムのテーブル内での位置を移動させ
たいときに指定．省略時は元の位置．
「カラム定義」はカラム名から始まるので，
名前を変更しないときは，同じ名前が２度
繰り返されることになる．
15
ALTER 文に関する補足説明
 まだレコードが登録されていないなら，テーブルを変更するのも，
いったん削除してから登録し直すのも，そんなに変わらない．
– ただし，カラムの型を変更すると，他のカラムまで影響を受
けることもある．
• 例：MySQL では，CHAR 型のカラムが複数あるとき，どれ
かを VARCHAR 型に変更すると他のカラムもすべて
VARCHAR 型に合わされる，など．
 しかし，レコードが登録されてからの ALTER 文の実行には注
意が必要である．
16
データ定義文の例（１）
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
データ定義文の例（２）
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
データ操作文の例（１）
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
データ操作文の例（２）
前ページの上の 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 のユーザ名
• rupus のログイン名と同じ
• パスワードは，講義中に指示する．
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.
サーバ rupus にログイン
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.
サーバ rupus からログアウト
これらの手順を忘
れると，CREATE
TABLE 以下を正し
く入力しても無視さ
れてしまう．
26
その他の MySQL コマンド
 show コマンド
– データベースに関するさまざまな情報を表示させる．
• show databases;
• show tables;
use コマンドを実
行した後でないと
• show columns from テーブル名;
使えない．
 use コマンド
– これから利用するデータベースの名前を宣言する．
• use データベース名;
– これ以降，このデータベースの中に新しいテーブルを作っ
たり，既存のテーブルのデータを参照したりできるようになる．
27
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>
28
MySQL クライアントとの対話（２）
 ただし，シングルクォート（' と '）で囲まれた途中で改行すると
…
– 文法的には間違っていないが，変なところに改行文字
（ASCIIコードの LF だが \n と表す）が入ってしまう．
mysql>
->
->
->
->
'>
->
->
mysql>
create table letters
(
name varchar(32) not null,
l_year year not null,
sent enum('○
',
'×')
);
ここでうっかり改行
してしまった．
すると，
'○\n' と入力した
ことになる．
29
注意
 注意：rupus の上の MySQL では：
– ユーザは，以下の形式の名前をしたデータベースしか作
成・使用できないように設定されている．
• 自分のMySQLユーザ名そのもの
例： te999999
本当は改行しない
– テーブル名にはそのような制約はない．
– パスワードを変更するには以下のようにする。
• SET PASSWORD FOR ユーザ名
@localhost=PASSWORD(‘変更後のパスワード’);
30
練習問題
 下記の操作を下のサンプルを見ながら行ってください。
– 商品名、値段をデータに取るテーブルを作成しなさい。
– データベース（そのテーブル）に値を入力してください。
– 入力した値を確認して下さい。
 結果は作成したテーブル名と入力した値を確認する SQL 文を
記述した WEB ページの URL の形で報告すること。
商品名
値段
りんご
１８０
みかん
１５０
31
演習問題




地域、商品をデータに取るテーブルを作成しなさい。
下のサンプルデータを入力してください。
地域名ごとに商品を出力させてみてください。
結果は作成したテーブル名と、各地域ごとの商品を出力させる
SQL文を記述したＷＥＢページのＵＲＬの形で報告すること。
地域
商品
福岡
明太子
福岡
ラーメン
北海道
ラーメン
北海道
ジャガイモ
沖縄
ゴーヤ
32

Download Report