Transcript SQL 文法とデータベースアクセス

平成21年度 情報科学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 のユーザ名
• 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.
（新規データベースの場合のみ）データベース作成 これらの手順を忘
れると，CREATE
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 からログアウト
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
注意
 注意：bossp の上の MySQL では：
– ユーザは，以下の形式の名前をしたデータベースしか
作成・使用できないように設定されている．
• 自分のMySQLユーザ名そのもの
例： te999999
– テーブル名にはそのような制約はない．
30
練習問題
 下記をテキスト(スライド）のサンプルを見ながら行って
ください。
– 商品名、値段をデータに取るテーブルを作成しなさい
。
– データベース（そのデーブル）に値を入力してくださ
い。
– 入力した値を確認して下さい。
31
演習問題
 地域、商品をデータに取るテーブルを作成しなさい。
 データを入力してください。その際、同じ地域名が入るよ
うにしてください。
 地域名ごとに商品を出力させてみてください。
32