MySQLの基礎
投稿日: | |
---|---|
修正日: | |
タグ: |
MySQLの使い方に関するメモ。試した環境はDebianやWindowsである。
SQLの構成
SQL(Structured Query Language)とはRDB(Relational Data Base)において,データの操作や定義,制御を行うためのデータベース言語である。RDBは日本語で関係データベースと呼ばれるもので,1つ以上の表から構成される。表の行はデータの基本単位であり,レコードとも呼ばれる。同じように表の列は属性とも呼ばれる。SQLの利用方法は次の3つがある。
- 対話的に処理。
- プログラムに埋め込む。
- 自動生成ツールで使用。
本稿では「対話的に処理」する場合の方法を説明する。
また,SQLの文は次の3つに分類できる。
- データ定義文(DDL: Data Definition Language)
- データベースやテーブルの作成や削除など。
- データ操作文(DML: Data Manupulation language)
- テーブルの検索や変更など。
- データ制御文(DCL: Data Control Language)
- トランザクション関連の処理(コミットやロールバック)。
インストール
Debian7.10 amd64では,
user% sudo aptitude install mysql-server
または
user% sudo apt-get install mysql-server
で,WindowsではGUIのインタフェースでインストールする。また両者ともMySQLのためのルートパスワードの設定が求められる。
Windowsのコマンドプロンプトで実行する場合,更に環境変数Pathにmysql.exeがあるディレクトリの場所を追加しなければならない。環境変数の設定は,次の手順で行う。
- エクスプローラで[コンピュータ]を開く。
- [システム・プロパティ]を選択。
- [システムの詳細設定]を選択。
- [詳細]タブを選択。
- [環境変数]を選択。
- [編集]でPathを選択。
- ;で区切って追加するパス(mysql.exeが格納されたディレクトリの位置)を追加する。
インストールや初期の設定が終了後,
user% mysql -u root -p
とコマンドを打ち込み,パスワードが求められてそれを入れてMySQLのプロンプトが出ればインストール成功である。
日本語
文字列に日本語を使うには設定が必要である。私の環境では(Debian7.10 amd64),/etc/mysql/my.cnfの[〜]の各セクションに,それぞれ次の行を追加した。
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
[mysqldump]
default-character-set=utf8
[mysql]
default-character-set=utf8
その後mysqlを再起動する。
/etc/init.d/mysql restart
SQLを使う際の注意点
- 命令や名前の大文字小文字は区別しない(本稿では可読性のためSQLのキーワードは大文字で記述する)。
- 文字列はシングルクォーテーション(')で囲う。
- いくつかの変更するための処理(述語)は,変更処理だけ行い変更後の表を画面に表示しない。もしユーザが変更結果を知りたい場合,参照するための処理を指定する必要がある。
サンプルRDBの作成
本稿の説明では主に次の表を使って説明する。
社員番号(id) | 名前(name) | 年齢(age) | 性別(sex) | 血液型(blood) |
---|---|---|---|---|
1001 | 山田太郎 | 40 | 男 | B |
1002 | 佐藤一郎 | 29 | 男 | A |
1003 | 鈴木花子 | 35 | 女 | AB |
2001 | 伊藤月子 | 30 | 女 | A |
2002 | 齋藤次郎 | 36 | 男 | O |
2003 | 高橋雪 | 34 | 女 | A |
3001 | 後藤三郎 | 19 | 男 | O |
前述の表を生成する場合,以下のコードをコピーアンドペーストすれば良い。
CREATE DATABASE sampledb;
CREATE TABLE sampledb.employee
(id INTEGER PRIMARY KEY NOT NULL,
name CHAR(30) NOT NULL,
age INTEGER ,
sex CHAR(3) ,
blood CHAR(2) );
INSERT INTO sampledb.employee VALUES (1001, '山田太郎', 40, '男', 'B');
INSERT INTO sampledb.employee VALUES (1002, '佐藤一郎', 29, '男', 'A');
INSERT INTO sampledb.employee VALUES (1003, '鈴木花子', 35, '女', 'AB');
INSERT INTO sampledb.employee VALUES (2001, '伊藤月子', 30, '女', 'A');
INSERT INTO sampledb.employee VALUES (2002, '齋藤次郎', 36, '男', 'O');
INSERT INTO sampledb.employee VALUES (2003, '高橋雪', 34, '女', 'A');
INSERT INTO sampledb.employee VALUES (3001, '後藤三郎', 19, '男', 'O');
ここでこれらの文の見方については言及しない。
データ操作文(DML: Data Manupulation language)
参照と変更
DMLは表に対する処理のことであり,主に参照系と変更系の2種類に分類できる。
- 参照系(select)
-
- 社員表から社員番号と名前,性別の列を表示。
この述語は次のように構成される。SELECT id,name,sex FROM sampledb.employee;
表はいずれかのデータベースに属し,"データベース.表"のように指定するか,USEコマンドで現在使用するデータベースを変更することで,表だけ記述できる。SELECT 列 FROM 表
USE sampledb; SELECT id,name,sex FROM employee;
- 社員表の一覧(全ての列)を表示。
SELECT * FROM sampledb.employee;
- 社員表から年齢(age)が30歳以上のヒトを検索(出力)。
-
SELECT * FROM sampledb.employee WHERE age >= 30;
- 社員表から男性でかつ年齢(age)が30歳以上のヒトを検索(出力)。
SELECT * FROM sampledb.employee WHERE sex='男' && age >= 30;
- 社員表から女性または年齢(age)が30歳以上のヒトを検索(出力)。
SELECT * FROM sampledb.employee WHERE sex='女' || age >= 30;
- 社員表から男性で年齢(age)が20歳から30歳までのヒトを検索(出力)。
-
またはSELECT * FROM sampledb.employee WHERE age BETWEEN 20 AND 30;
SELECT * FROM sampledb.employee WHERE 20 <= age && age <= 30;
- 社員表から〜子で終わる名前のヒトを検索(出力)。
-
または正規表現を使ってSELECT * FROM sampledb.employee WHERE name LIKE '%子';
SELECT * FROM sampledb.employee WHERE name REGEXP '.*子';
- 社員表の一覧を年齢の昇順で表示
-
SELECT * FROM sampledb.employee ORDER BY age;
- 社員表の一覧を年齢の降順で表示
SELECT * FROM sampledb.employee ORDER BY age DESC;
- 社員表の一覧から血液型ごとに平均年齢を表示。
SELECT blood,AVG(age) FROM sampledb.employee GROUP BY blood;
- 社員表の一覧から血液型ごとに平均年齢(30歳以上)を表示。
SELECT blood,AVG(age) FROM sampledb.employee GROUP BY blood HAVING blood > 30;
- 変更系(update,insert,delete)
-
- 社員表に社員番号1004番の田中三郎,年齢18歳,血液型O型を追加。
-
列を指定。INSERT INTO sampledb.employee VALUES (1004, '田中三郎', 18, 'O');
INSERTはINSERT INTO sampledb.employee(id, name, age) VALUES (1005, '衛宮四郎', 18);
INSERT INTO 表[(列, ...)] VALUES (値, ...);
- 社員表の社員番号1001番の年齢を30歳に変更。
-
UPDATE sampledb.employee SET age=30 WHERE id=1001;
ただしUPDATEやDELETEはWHERE句を指定しなければ全てのレコードに対して処理を行うので,必ずWHERE句を付けると考えた方が無難である。UPDATE 表 SET 列=値[,列=値];
- 社員表の社員番号1001番の年齢を30歳に,血液型をB型に変更。
UPDATE sampledb.employee SET age=30, blood='B' WHERE id=1001;
- 社員表から社員番号1002のヒトの項目を削除。
DELETE FROM sampledb.employee WHERE id=1002;
DELETEは表だけ指定すれば最低限成り立つが,前述の通りそれでは全てのレコードを対象にしてしまう。
DELETE FROM 表;
集合関数
SQLではSUM(総和)やMAX(最大値),MIN(最小値),AVG(平均),COUNT(総数)を求めるような関数が用意されている。以下にそれらの関数を使用した例を示す。
- 社員表から社員の平均年齢を検索
select AVG(age) FROM sampledb.employee;
副問い合わせ
SQLでは()を使用することで,ある検索結果で得た情報を別の検索のために使用することができる。これを副問い合わせという。以下に例を示す。
- 社員表から平均年齢よりも高い社員を検索
SELECT * FROM sampledb.employee WHERE age > ( SELECT AVG(age) FROM sampledb.employee );
結合
SQLではいくつかの表を組み合わせて検索する場合があり,そのための仕組みとして結合という方法が用意されている。結合にはいくつか種類があるが,ここでは以下の3つの結合を紹介する。
- 内部結合(INNER JOIN)
- 2つの表で一致する部分を抽出。
- 外部結合(OUTER JOIN)
- 片方の表で一致する部分を抽出。
- 交差結合(CROSS JOIN)
- 2つの表の組み合わせを生成。
以下でMySQLでのそれぞれの結合の方法を紹介する。
内部結合を説明するために新たに次の表を追加する。
顧客番号(cid) | 名前(name) | 担当社員番号(eid) |
---|---|---|
109001 | 本田五郎 | 1001 |
109002 | 高倉六郎 | 1002 |
109003 | 豊田松代 | 1001 |
109004 | 松田竹子 | 1003 |
CREATE TABLE sampledb.customer
(cid INTEGER PRIMARY KEY NOT NULL,
name CHAR(30) NOT NULL,
eid INTEGER NOT NULL);
INSERT INTO sampledb.customer VALUES (109001, '本田五郎', 1001);
INSERT INTO sampledb.customer VALUES (109002, '高倉六郎', 1002);
INSERT INTO sampledb.customer VALUES (109003, '豊田松代', 1001);
INSERT INTO sampledb.customer VALUES (109004, '松田竹子', 1003);
- 内部結合
-
複数の表に分けて管理する場合,それらのデータが冗長にならないように設計した方がデータベースのサイズは小さい。例えば,前述のデータベースでは,顧客表は担当者の番号だけを記録し,担当者の名前は社員表を見れば良い。しかし顧客名と担当者名を対応させたい時,それでは不便である。そのような時に内部結合を使用する。
- 顧客名と担当する社員の社員番号,担当する社員の名前を表示。
SELECT customer.name,employee.id,employee.name FROM sampledb.employee INNER JOIN sampledb.customer on employee.id=customer.eid;
関連する2つの表がある時,それらは1側と多側に分けることができる。例えば前述の社員表と顧客表においては,社員表の社員番号と顧客表の担当社員番号で関連付けることができ,社員表の社員番号が各レコードで一意に定まるの対し,顧客表は同じ内容を指しているが一意に定まることはない。
関連付ける列は"表1.列=表2.列"のように指定する。 なお表示する列を指定する場合は,1側(employee.id)と多側の列(customer.eid)を指定しても同じような結果となる。SELECT 列 FROM 1側の表 INNER JOIN 多側の表 on 関連付ける列;
SELECT customer.name,employee.id,employee.name FROM sampledb.employee INNER JOIN sampledb.customer on employee.id=customer.eid;
SELECT customer.name,customer.eid,employee.name FROM sampledb.employee INNER JOIN sampledb.customer on employee.id=customer.eid;
また,表の名前はASを使うことで別名を付けて簡略化することができる。
SELECT c.name,e.id,e.name FROM sampledb.employee AS e INNER JOIN sampledb.customer AS c on e.id=c.eid;
ASは表の名前だけでなく列名などにも使用可能である。例えば前述の例では顧客名も担当者名もnameで分かりづらい。そういった場合,それぞれ別名を付けて表示することができる。
SELECT c.name AS 顧客名,e.id AS ID,e.name AS 担当者名 FROM sampledb.employee AS e INNER JOIN sampledb.customer AS c on e.id=c.eid;
- 外部結合
-
前述と同じような内容を知りたい時でも,「社員がそれぞれどの顧客を担当しているか」を知りたい場合がある。当然ながら社員とはいえ,さまざまな仕事内容があり,中には顧客と直接対応しない者もいる。そのような情報が欲しい場合,外部結合を使用して片方の表に乗っている場合はレコードを表示する。
外部結合の場合,LEFTやRIGHTといったキーワードで,ベースとする表を指定する。関連付けた列でベースとしている方にレコードが存在する場合,そのレコードを生成する。今回の例では,社員をベースに考えているため,社員表をベースとしている。なお左表と右表というのは"左表 {LEFT|RIGHT} OUTER JOIN 右表"というように記述する。今回の例でいえば,"employee LEFT OUTER JOIN customer"と"customer RIGHT OUTER JOIN employee"は同じ結果となる。SELECT c.name,e.id,e.name FROM sampledb.employee AS e LEFT OUTER JOIN sampledb.customer AS c on e.id=c.eid;
- 交差結合
データ定義文(DDL: Data Definition Language)
SQLサーバは複数のデータベースを,データベースは複数の表を,表は複数のレコードを持つ。DMLがレコードの参照・変更・追加・削除などであったが,DDLはデータベースや表に対してそのような処理を行う。
MySQLで表を生成するまでの流れは次の通りである。
- データベースの作成。
CREATE DATABASE sampledb
- テーブルの作成。
ID id: 整数 名前 name: char(20) 年齢 age: 整数 血液型 blood: char(2) CREATE TABLE テーブル名 (属性名 データ型 値の制約, 属性名 データ型 値の制約 ...);
テーブル名は現在使用しているデータベースのものであれば,直接テーブル名を指定できるが,それ以外の場合は「データベース名.テーブル名」とする。現在使用中のテーブルはUSE文で変更できる。
USE データベース名;
以下にテーブルの生成例を示す。
CREATE TABLE sampledb.employee (id INTEGER PRIMARY KEY NOT NULL, name CHAR(20) NOT NULL, age INTEGER , blood CHAR(2) );
CHARで指定する数字は,文字数ではなくバイト数である。英字であればその2つは同じ意味を持つが,その他の言語を利用する場合それらは文字コードによって使用するバイト数が異なる。
生成したテーブルの列情報はDESCで確認できる。
DESC テーブル名;
作成したデータベースやテーブルの一覧はSHOWで確認できる。
- データベース
SHOW databases;
- 表
前述のテーブルの一覧表示は,現在設定されているデータベースのものが表示される。SHOW tables;
制約
各レコードのデータ型を設定する場合,整数や文字列といったコンセプトとは別に,格納する際の条件(制約)が設定できる。制約には,ブランク(NULL)を禁止するものや,同じ列の他のレコードと値が同じになってはいけないものなどさまざまある。RDBを生成する場合,必ず1レコードだけを抽出するための列を用意するべきである。このような列を主キーという。主キーの制約には"PRIMARY KEY NOT NULL"の2つが必要である。
データベースや表の削除
- データベースの削除。
DROP DATABASE sampledb;
- 表の削除。
DROP TABLE sampledb.employee;
データ制御文(DCL: Data Control Language)
データベースには,複数のユーザが同時に処理を行うことで,データの整合性がとれない状況を防ぐ仕組みがある。代表的なものには,以下の2つがある。
- 排他的制御
- あるユーザが処理を行う際に他のユーザが処理できないようにロックする方法。
- トランザクション処理
- いくつかのレコード変更処理をトランザクションという単位でまとめて扱い,最終的にそのデータを反映させる場合はコミット,取り消す場合はロールバックを行うことでデータの整合性を守る。なおMySQLではデフォルトでは自動コミットとなっており,変更系の文を記述するたびにデータの変更が行われる。
DCLはこのような制御を行う。
トランザクション処理
実際にデータベースを扱う場合,一部のデータだけ変更できれば良いわけではない。例えば購入処理では,購入者の所有数を増やすと同時に,在庫表のものを減らさなければならない。このような処理はいずれかだけできれば良い訳ではなく,必ず全て必要である。SQLでは,このような処理をトランザクションという単位でまとめて処理を行う。トランザクションの主な処理は次のような手順である。
- トランザクションの開始。
START TRANSACTION;
- 変更処理をいろいろ(参照処理も可能だが参照処理は逐次行われる)。
-
- コミット。データを正式に反映する。
COMMIT;
- ロールバック。トランザクション開始時からの変更処理を取り消す。
ROLLBACK;
本稿を順番に読んだ場合,今まで見たレコードの変更系処理は,処理を記述する度にコミットされてきた。それゆえコミットのイメージは分かるものとし,ここではロールバックの例を示す。
- トランザクションの開始。
START TRANSACTION;
- 参照。初期状態を確認(参照系)。
SELECT * FROM sampledb.employee;
- 更新(変更系)。
UPDATE sampledb.employee SET age=41 WHERE id=1001;
- 参照(参照系)。
SELECT * FROM sampledb.employee;
- 新しいレコードを追加(変更系)。
INSERT INTO sampledb.employee VALUES (1005, '幸田七郎', 25, '男', 'B');
- 参照(参照系)。
SELECT * FROM sampledb.employee;
- ロールバック。前述の2つの変更系処理を取り消す。
ROLLBACK;
- 参照。初期状態と同じ状態になっている。
SELECT * FROM sampledb.employee;