mysql memo

初めてのPHP & MySQL 第2版 を読んだ自分用メモ。PHP の部分はスルー。途中から抜けが多いけど本にはしっかり書いてあるよ!

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 / % / _

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正規形

条件

  1. 同じような列を繰り返し定義しない(e.g. Author1, Author2)
  2. 全ての列は値は1つだけ含んでいること
  3. 一意に行を識別する主キーがあること

正規化後

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 テーブルでは、住所の構成要素とユーザーは直接関係無いと考えることができる
      • つまり、住所は郵便番号に、郵便番号は都市に、都市は州に依存している
  • 第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';