初めてのPHP & MySQL 第2版
connect
mysql -h host -u user -p
mysql command
- command
- paramter:意味
- quit
- none:コマンドラインユーティリティを終了
- use
- database name:使用するDBを指定する
- show
- table name / database name:利用可能なテーブルやデータベースなどのリストを出力する
- describe
- table name:テーブルの列の説明を出力する
- status
- none:データベースのバージョンとステータスを表示する
- source
- file name:指定ファイルからコマンドを実行する
利用可能なデータベースを表示する
SHOW DATABASES;
使用するデータベースを指定する
USE mysql;
DB管理
ユーザ作成
GRANT ALL PRIVILEGES ON *.* TO 'user'@'hostname' IDENTIFIED BY 'password'
DB作成
CREATE DATABASE store; USE store;
テーブルの作成
CREATE TABLE books ( title_id INT NOT NULL AUTO_INCREMENT, title VARCHAR(150), pages INT, PRIMARY KEY (title_id) );
CREATE TABLE authors ( author_id INT NOT NULL AUTO_INCREMENT, title_id INT NOT NULL, author VARCHAR(125), PRIMARY KEY (author_id) );
- NOT NULL
- 「空」の状態を許可しない
- AUTO_INCREMENT
- 主キーが自動生成される
- PRIMARY KEY
- 主キーフィールドになる列を指定
テーブルにデータを追加
INSERT INTO table COLOMNS(列名) VALUES(値);
- 数値はクォートしない
- 文字列は必ずクォートする
- 日時と時刻は必ずクォートする
- 関数はクォートしない
- NULLはクォートしない
INSERT INTO books VALUES(1, "Linux in a Nutshell", 112); INSERT INTO authors VALUES(NULL, 1, "Ellen Siever"); INSERT INTO authors VALUES(NULL, 1, "Aaron Weber");
テーブル定義の操作
テーブル名の変更
ALTER TABLE table_name RENAME new_table_name;
列のデータ型の変更
ALTER TABLE authors MODIFY author VARCHAR(150);
author_id 列の後に author 列を配置。 FIRST を指定すると、その列はテーブルの最初の列になる
ALTER TABLE authors MODIFY author varchar(125) AFTER author_id;
列の追加
ALTER TABLE table_name ADD column_name DATA_TYPE;
AFTER column_name か FIRST で列を追加する場所を指定できる
列名の変更
AFTER TABLE table_name CHANGE new_column_name old_column_name varchar(125);
列の削除
ALTER TABLE table_name DROP COLUMN column_name;
テーブル全体の削除
DROP TABLE table_name;
SELECT
SELECT column_name FROM table_name [WHERE] [ORDER];
- WHERE クエリから返される結果を限定できる
- ORDER BY データをソートできる
WHERE
SELECT * FROM books WHERE title = "Classic Shell Scripting";
SELECT books.pages FROM books WHERE title = "Classic Shell Scripting";
- AND/OR を使うことで複数の条件を指定できる
- 「table_name.colomn_name」の形式が好ましい
ORDER BY
SELECT * FROM authors ORDER BY author;
SELECT * FROM authors ORDER BY author DESC;
テーブルの結合
user_id と title_id の組み合わせに purchase_id を割り当てる purchases テーブルを作成してデータを追加する SQL
CREATE TABLE purchases ( purchase_id int NOT NULL AUTO_INCREMENT, user_id varchar(10) NOT NULL, title_id int(11) NOT NULL, purchased timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (purchase_id) ); INSERT INTO `purchases` VALUES(1, 'mdavis', 2, '2005-11-26 17:04:29'); INSERT INTO `purchases` VALUES(2, 'mdavis', 1, '2005-11-26 17:05:58');
著者名、ページ数、著者を一覧表示するクエリを作成する SELECT 文
SELECT books.*, author FROM books, authors WHERE books.title_id = authors.title_id;
- books.*, author の部分は、 books テーブルからは全フィールドを選択するが、authors テーブルからは author だけを選択することを示している
- WHERE books.title_id = authors.title_id の部分は、title_id で二つのテーブルを連結している
JOIN ON
SELECT 列 FROM テーブル JOIN 結合対象テーブル ON (条件);
SELECT * FROM books JOIN authors ON (books.title_id = authors.title_id);
- 自然結合と同じように使うことができる
- 名前で自動的にマッチングする代わりに、結合する列を指定できる
エイリアス
テーブル名の後に AS と記述し、その後ろにエイリアスを指定する
SELECT * from books AS b, authors AS a WHERE b.title_id = a.author_id;
UPDATE
UPDATE books SET pages = 476 WHERE title = "Linux in a Nutshell";
DELETE
DELETE FROM authors WHERE author_id = 1;
LIKE / % / _
- % はワイルドカード
- _ は1文字とマッチする
SELECT * FROM authors WHERE author LIKE "%b%";
SELECT * FROM authors WHERE author LIKE "Aaron Webe_";
論理演算子
WHERE 節のなかでは、AND / OR / NOT を使用できる
- 著者が"Ellen Siever"ではないレコードを全て返す
SELECT * FROM authors WHERE NOT (author = "Ellen Siever");
- 書籍と著者情報を返す
SELECT * FROM books, authors WHERE title = "Linux in a Nutshell" AND author = "Aaron Weber" AND books.title_id = authors.title_id;
- 著者名が"Aaron Weber" または ""Ellen Siever" というレコードを全て返す
SELECT *
FROM books, authors
WHERE (author = "Aaron Weber"
OR author = "Ellen Siever")
AND books.title_id=authors.title_id;
このクエリの括弧は重要。 AND 条件で authors テーブルと books テーブルを結合する前に、著者名の OR 条件を明確にする必要がある
データベース設計
リレーショナルデータベース
- 複数のテーブルを格納する機能がある
- 別々に格納されたテーブルに関係がある場合、共通のキーによって両方のテーブルを結合することができる
- 新たなデータベースを設計する際、どの項目を｢主キー｣にするかは重要な作業
- 主キーは必ず一意でなければならない
リレーションシップのタイプ
- 1対1のリレーションシップ
[users] [ship]
User ------ User
First Address
Last City
State
Zip
- 1対多のリレーションシップ
(ry
- 多対多のリレーションシップ
(ry
正規化
- データを構成する列の関連性を割り出して効率的にまとめる作業
- 列の論理的な関係性に基づいてデータを分割しデータの重複を最小限にする
正規化されていないテーブル
- 住所変更等に対応するのは大変
| User ID | First name | Last name | Address | Phone | Title | Author1 | Author2 | Pages | When |
|---|---|---|---|---|---|---|---|---|---|
| Mdavis | Michele | Davis | Linksway, Fx Pnt | 414-352-4818 | Linux in a Nutshell | Ellen Siever | Aaron Weber | 112 | 2007/9/3 |
| Mdavis | Michele | Davis | Linksway, Fx Pnt | 414-352-4818 | Classic Shell Scripting | Arnold Robbins | Nelson Beebe | 576 | 2007/9/3 |
正規化の形式
第1正規形
条件
- 同じような列を繰り返し定義しない(e.g. Author1, Author2)
- 全ての列は値は1つだけ含んでいること
- 一意に行を識別する主キーがあること
正規化後
| User ID | Fitst name | Last name | Address | Phone | Title | Pages | When |
|---|---|---|---|---|---|---|---|
| Mdavis | Michele | Davis | Linksway, Fx Pnt | 414-352-4818 | Linux in a Nutshell | 112 | 2007/9/3 |
| Mdavis | Michele | Davis | Linksway, Fx Pnt | 414-352-4818 | Classic Shell Scripting | 576 | 2007/9/3 |
| Title | Author name |
|---|---|
| Linux in a Nutshell | Ellen Siever |
| Linux in a Nutshell | Aaron Weber |
| Classic Shell Scripting | Arnold Robbins |
| Classic Shell Scripting | Nelson Beebe |
| User ID | First name | Last name | Address | City | Stat | Zip code | Phone | Title | Pages | Date |
|---|---|---|---|---|---|---|---|---|---|---|
| Mdavis | Michele | Davis | 4505 N, Linksway | FxPnt | MN | 55114 | 414-352-4818 | Linux in a Nutshell | 112 | 2007/9/3 |
| Mdavis | Michele | Davis | 4505 N, Linksway | FxPnt | MN | 55114 | 414-352-4818 | Classic Shell Scripting | 576 | 2007/9/3 |
第2正規形
- 第1正規形が水平方向の行に対するデータの冗長性に対処するが、第2正規形は垂直方向の列に対するデータの冗長性に対処する
- 第2正規形のデータを作成するには、既にそのテーブルが第1正規形でなければならない
- 複数の行を横断して値が繰り返される列を全て特定する必要がある(そのような列は別のテーブルに切り離し、元のテーブルの主キーで参照する)
- テーブルのなかに、主キーに依存しない属性がないか検討する方法がある
正規化後(Users Books Authors Purchases に分割された)
- books テーブル
| Title_ID | Title | Pages |
|---|---|---|
| 1 | Linux in a Nutshell | 112 |
| 2 | Classic Shell Scripting | 576 |
- authors テーブル
| Author_ID | Author name |
|---|---|
| 1 | Ellen Siever |
| 2 | Aaron Weber |
| 3 | Arnold Robbins |
| 4 | Nelson Beebe |
- authors テーブルと books テーブルをつなぐ book_author テーブル
| Title_ID | Author_ID |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
- Users テーブル
| User_ID | First name | Last name | Address | City | State | Zip code | Phone |
|---|---|---|---|---|---|---|---|
| Mdavis | Michele | Davis | 7505 N. Linksway | FxPnt | MN | 55114 | 414-352-4818 |
- Purchase テーブル
| Purchase_ID | User_ID | Title_ID | When |
|---|---|---|---|
| 1 | Mdavis | 1 | 2007/9/3 |
| 2 | Mdavis | 2 | 2007/9/3 |
第3正規形
- 第1正規形と第2正規形のルールに正しく従ってきた場合、そのまま第3正規形に適合している場合がある
- 第3正規形ではテーブルのなかから、主キーに完全に依存せずテーブル内のほかの列に依存するデータを探す
- Purchase テーブルでは、住所の構成要素とユーザーは直接関係無いと考えることができる
- つまり、住所は郵便番号に、郵便番号は都市に、都市は州に依存している
- Purchase テーブルでは、住所の構成要素とユーザーは直接関係無いと考えることができる
- 第3正規形では、多くのテーブルが作成されることとなるが、正規化をどこで止めるかは自分次第
- すくなくとも第2正規形には適合させた方がよい
データ型
[]内は省略可
| フィールドタイプ | 説明 | 例 |
|---|---|---|
| INT[(M)] | 整数(Mは最大表示幅) | 997 |
| FLOAT[(M,D)] | 少数(最大M桁、小数点以下D桁) | 3.14142 |
| CHAR(M) | 文字列(M文字:最大255) | 「テスト」 |
| VARCHAR(M) | 文字列(M文字:最大255文字、MySQL 5 では 約 65000文字) | "testing 1.2.3" |
| TEXT or BLOB | 65535文字までの文字列 | "All work and no play makes Jack a dull boy." |
| DATE | YYYY-MM-DD | 2003-12-25 |
| TIME | HH:MM:SS | 11:36:02 |
- books テーブルの列のデータ型
| フィールド名 | データ型 |
|---|---|
| Title_ID | INT |
| Title | VARCHAR(150) |
| Pages | INT |
- authors テーブルの列のデータ型
| フィールド名 | データ型 |
|---|---|
| Author_ID | INT |
| Title_ID | INT |
| Author | VARCHAR(100) |
データのバックアップとリストア
データベースファイルのコピー
- Linux : /var/lib/mysql
- MySQLサービスを停止してからファイルコピーをする
- バックアップしたディレクトリにあるファイル全てを置き換える必要がある
mysqldump
- SQL 文がテキストファイルに出力される
- 出力されたファイルには、データベースオブジェクトの再構築や、データの復元に必要なコマンドが含まれている
- 単一テーブル、単一データベース、または全データベースのどれをバックアップするかについてパラメータを指定できる
- バックアップしたいオブジェクト名に続けて > と記述してファイル名を指定すると、出力結果がファイルに出力される
store というデータベースをバックアップ & リストア:
$ mysqldump -u user -p store > my_backup_of_store.sql $ mysql -u root -p -D store < my_backup_of_store.sql
authors テーブルだけをバックアップする:
$ mysqldump -u root -p store authors > authors.sql
全データベースをバックアップ & リストア:
$ mysqldump -u root -p --all-databases > my_backup.sql $ mysql -u root -p < my_backup.sql
テスト用などに、データベースの状態を空の状態(構造のみ)で出力する:
$ mysqldump -u root -p --no-data store > structure.sql
データだけをバックアップ:
$ mysqldump -u root -p --no-create-info store > data.sql
mysqlimport
- CSV形式のデータを取り込みたい場合に便利
インデックス
- インデックスを使用するには事前に定義しておく必要がある
- インデックスに含まれたデータはソートされている
- なぜすべてインデックス化しないのか
- ディスク容量の限界
- 膨大で包括的なインデックスを作成・メンテナンスするのは非効率
- インデックスに含まれるデータが大きすぎると読み取りに時間がかかる
インデックスが使用されるタイミング
- WHERE 節
- SELECT * FROM authors WHERE author = 'Ellen Siever'; というクエリは、利用可能な場合に author 列のインデックスを使う
- ORDER BY 節
- SELECT * FROM contacts ORDER BY author; というクエリは、利用可能な場合に author 列のインデックスを使う
- MIN MAX 関数
- MIN 関数や MAX 関数で指定される列にインデックスがある場合、そのクエリではインデックスが使われる
インデックスの作成
- 簡単なインデックスの作成
- UNIQUE という指定によって author という列にインデックスが作成される(ただし、全てのインデックスが一意とは限らない)
CREATE UNIQUE INDEX authind ON authors (author);
複数列のインデックス
- 2つ以上の列でもインデックスを作成できる
- 複数列を使うユニークインデックスは、列の値を組み合わせることで一意性を確保する
- インデックス列としてふさわしいのは、WHERE 節で使用されそうな列
- 確実に使用される特定の列の組み合わせであればよい
- 複合インデックスの列は、頻繁に使われる列が先に来るように並べる
- インデックスの先頭の値だけを使う場合でも、複合インデックスによるクエリの実行速度をあげる
- プライマリインデックスは1つのテーブルに1つだけ作成できる
- ユニークインデックスは好きなだけ作成できる
EXPLAIN
EXPLAIN の出力には、どのようにクエリが処理されたかについて細かい情報が含まれる
EXPLAIN SELECT * FROM authors WHERE author = 'Arnold Robbins';