MySQL 非エンジニア備忘録

#MySQL非エンジニア

##目的
MySQLの使用環境があったので備忘録としてまとめておく。
企業が使っているデータベースの仕組みや普段接しているアプリケーションがどのようにデータベースと繋がっているか学習したいと思ったから。基本的には個人備忘録用。
誤った箇所があればご指摘ください。

##データベースの管理

###データベースの作成

データベースを新たに作成するにはCREATE DATABASE文を使う

MySQL

CREATE DATABASE データベース名;

データベースを作成する際にはキャラクタセット(利用できる文字の種類)を
指定することも可能。
デフォルトのキャラクタセットはLatin-1という欧文セット。
日本語を扱う場合には、utf8(UTF-8/Unicode)やcp932(シフトJS)が指定できる。
次の例では文字コードをUTF-8にしてデータベースexampledbを作成

MySQL

CREATE DATABASE exampledb CHARACTER SET  utf8;

###データベースの一覧を見る

MySQL

SHOW databases;

※　データベースの一覧に表示される「Information_schema」「mysql」はMySQLサーバーの管理用データベースなのでいじらないように。

###データベースの削除

データベースを削除するにはDROP DATABASE文を使う。

MySQL

DROP DATABASE　データベース名;

###データベースの選択

MySQL

USE データベース名

「Database changed」と表示されればOK( ;は不要。)

###データベースにユーザーを登録する

MySQLを利用するにはあらかじめデータベースユーザーを作成しておく必要がある。
MySQLをインストールした直後には管理者権限を持つrootユーザーが用意されている。
実際にはrootユーザーは使用せず管理用のユーザーを作成して利用する。
rootユーザーはとても大きな権限を持っているので、もし第三者にアカウントを奪われたら大変。

新しいユーザーを追加するコマンドは以下の通り

MySQL

CREATE USER ユーザー名@ホスト名　IDENTIFIED BY 'パスワード';

ユーザーを作成するときには同時にホスト名も指定する。
ここで指定したホストからのみデータベースに接続できる。
(ホスト名にはwww.example.comといった名前や「192.168.11.1」といったIPアドレスが指定できる)
データベースサーバーと同じホストから接続する場合は「localhost」としておけば良い。
ユーザーのパスワードはIDENTIFIED BYに続けて指定。パスワードは省略可能。

EXAMPLE

MySQL

CREATE USER newuser@localhost　IDENTIFIED BY 'dbpassword';

###ユーザーに権限を付与する

データベースのユーザーにはデータベースにアクセスする権限、テーブルを作成する権限、テーブルのデータを閲覧する権限など、様々な権限を付与することができる。
新しく作られたユーザーには権限が設定されていないので必要な権限を設定する。
権限の設定にはGRANT文を使用する。

MySQL

GRANT 権限 ON データベース名.テーブル名
TO ユーザー名@ホスト名 [IDENTIFIED BY 'パスワード']

代表的な権限は以下のとおり。

権限	説明
ALL	全ての権限(ただしGRANT OPTIONを除く)
ALL PRIVILEGES	ALLと同じ
CREATE	データベースやテーブルの作成
CREATE USER	ユーザーの作成・削除
CREATE VIEW	ビューの作成
DELETE	DELETEコマンドの実行
DROP	データベースやテーブルの削除
INSERT	INSERTコマンドの実行
SELECT	SELECTコマンドの実行
UPDATE	UPDATEコマンドの実行
GRANT OPTION	権限の付与

次の例では、sampledbデータべースの全テーブルに対して、全ての権限をnewuserに与えている。「sampledb.*」は「sampledbデータベースにある全てのテーブル」を表す。

MySQL

GRANT ALL PRIVILEGES ON sampledb.* TO newuser@localhost;

データベースを特定せず、全てのデータベースに対する権限を設定する場合は
「.」を使ってデータベースとテーブルを指定する。次の例では全てのデータベースに対してSELECT文が実行できる権限をユーザーnewuserに与えている。この場合、データベースを更新したりレコードを削除したりする権限は「newuser」にはない。

MySQL

GRANT ALL PRIVILEGES ON *.* TO newuser@localhost;

指定したユーザーが存在しない場合にはGRANT文だけで新規ユーザー作成、権限付与を行うことができる。

MySQL

GRANT ALL PRIVILEGES ON *.* TO testuser@localhost IDENTIFIED BY 'パスワード'

###データベースのユーザーパスワードの変更

パスワードを変更するときにはSET PASSWORDを使う

MySQL

SET PASSWORD FOR ユーザー名@ホスト名=password('新しいパスワード');

###ユーザーの一覧表示

データベースにどんなユーザーがいるのか調べるにはMySQLが管理用に使っているmySQLデータベースのuserテーブルを参照する。

MySQL

SELECT User, Host FROM mysql.user;

###ユーザーの削除

不要になったデータベースのユーザーはDROP USERコマンドで削除できる

MySQL

DROP USER ユーザー名@ホスト名;

##テーブルの設計

###テーブル情報の確認

MySQL

DESC members;

左から列名、データ型、非NULL制約、キー、デフォルト値、その他と並んでいる。
※1列目の「Field」は列に付けられた名前(予約後は使用不可)

###データ型とは

レコードの各列にはどのようなデータを格納できるのかあらかじめ属性が決められている
主なデータ型は下記の通り

データ型	説明
TINYINT	整数(1バイト)
SMALLINT	整数(2バイト)
INT	整数(4バイト)
BIGINT	整数(8バイト)
FLOAT	浮動小数点数(4バイト)
DOUBLE	浮動小数点数(8バイト)
CHAR(N)	N(0~255)文字の固定長文字列
VARCHAR(N)	N(0~65535)文字の可変長文字列
TEXT	65535バイトまでの文字列(大文字小文字の区別なし)
BLOB	65535バイトまでの文字列(大文字小文字の区別あり)
DATE	日付(YYYY-MM-DD)
DATETIME	日時(YYYY-MM-DD HH:MM:SS)
TIME	時刻(HH:MM:SS)

###制約とは

制約とは，テーブルに格納するデータを限定する方法。
データ型によって整数であるか，文字列であるかといったデータの種類を限定することができる。しかし，それでは正数のデータのみを受け付けるとか，同じデータが重複してはいけないとかといった制限を行うことができません。
このような制約を列やテーブルに対して定義することができる。
制約に違反するデータを格納しようとするとエラーとなる。

代表的な制約

制約	説明
PRIMARY KEY	主キーとする
UNIQUE	重複した値を受け付けない
NOT NULL	NULL値を受け付けない(非NULL制約)
DEFAULT値	デフォルト値を設定する

###非NULL制約

NULL値を許容するかどうか、その列にはNULLが格納されても良いか示されている
「NO」となっていればNULLはダメ。

###主キーとは

PRIMARY KEYを表す。値に重複がなくユニークな値でなければならない。

###外部キーとは

他のテーブルの主キーが参照している列のことを外部キーという。

※主キー列と外部キー列によってテーブルが結びつけられる

###参照整合性

主キー列にはデータがあるのに外部キー列にはその値がないといった場合どうなるか？

下記一例

この例ではunitテーブルの主キーであるid列は外部キーとしてstaffテーブルのunit_idを参照している。
しかし、例えばstaffテーブルにある「unit_id = 3」に該当する値はunitテーブルには存在しない。そのため、テーブルを結合すると該当部分は「NULL」になってしまう。
主キーと外部キーとの間の整合性を**"参照整合性という"**。
参照整合性がなくなってしまうとテーブルを結合した時にレコードが取得できなくなったり適切な値が表示されなくなったりする。

###正規化

目的：データの冗長性をなくして、データベースの保守性を高める

正規化には幾つもの段階がある。
全く正規化されていない非正規化から正規化を進めて第1正規化、第2正規化、第3正規化に
至る。第4正規化以降もあるが通常は第3正規化までで十分。

##テーブルの作成

###テーブルの管理

####テーブルの作成

新しいテーブルを作るにはCREATE TABLE文を使う

MySQL

CREATE TABLE テーブル名(
列名1 データ型　[制約],
列名2 データ型　[制約],
列名3 データ型　[制約],
...
);

EXAMPLE

MySQL

CREATE TABLE goods(
id INT PRIMARY KEY,
name CHAR(10),
price INT
);

####テーブルのコピー

テーブルの構造をコピーして新しくテーブルを作成することができる

MySQL

CREATE TABLE 新規テーブル名　LIKE コピー元テーブル名;

####カラムをテーブルに追加する

テーブルの作成後にカラムを追加する場合にはALTER ~ ADD文を使う。

MySQL

ALTER　TABLE テーブル名 ADD カラム名 データ型 [FIRST];

※もし左端にカラムを追加した場合は[FIRST]を指定する

####カラムをテーブルから削除する

カラムをテーブルから削除する場合にはADDの代わりにDROPを使う。

MySQL

ALTER　TABLE テーブル名 DROP カラム名 データ型 [FIRST];

####テーブル名の変更

テーブル作成後にテーブル名を変更。

MySQL

ALTER　TABLE 現在のテーブル名 RENAME 新しいテーブル名;

####テーブルの削除

テーブルの削除を行うには DROP TABLE文を使用する。

MySQL

DROP TABLE テーブル名;

##ビューの作成

###ビューとは

実際のテーブルとは異なる仮想敵なテーブルを作ることができる。
仮想的なテーブルをビュー(View)と呼ぶ。
ビューは複数のテーブルを結合して一時的なテーブルを作ったり、実際のテーブルとは異なったアクセス権を持たせたい場合などに利用する。SELECT文を使って何度もデータを抽出したりテーブルを結合したりするよりもあらかじめビューを作っておいたほうがパフォーマンスも高まる。
ビューを作成するにはCREATE VIEW文を使う。

MySQL

CREATE VIEW ビュー名
AS SELECT 列名 FROM　テーブル名　WHERE 条件;

###ビューの削除

MySQL

DROP VIEW ビュー名;

代表的な制約

制約	説明
PRIMARY KEY	主キーとする
UNIQUE	重複した値を受け付けない
NOT NULL	NULL値を受け付けない(非NULL制約)
DEFAULT値	デフォルト値を設定する

---

##レコードの挿入/変更/削除

###INSERT命令を使ってレコード追加する

データベースにデータを追加するにはレコード単位で行う。

MySQL

INSERT INTO db_name.tbl_name (col_name1, col_name2, ...)
  VALUES (value1, value2, ...);

USE文を使ってデータベースに接続している状態で、そのデータベース内に
あるテーブルにデータを追加する場合は次のように単にテーブル名だけを
指定して作成することもできる

MySQL

INSERT INTO tbl_name (col_name1, col_name2, ...)
  VALUES (value1, value2, ...);

複数レコードの登録を1つのSQL文で実行する場合は下記のようにカンマ区切りで記述する。

EXAMPLE

MySQL

INSERT INTO user_table (id, name, memo, status) VALUES 
(1, 'test1', 'メモ', 1),
(2, 'test2', 'メモ', 1),
(3, 'test3', 'メモ', 1),
(4, 'test4', 'メモ', 1);

• テーブルにレコードを追加するにはINSERT命令を使う。
• VALUES ()内に全ての列を「,」で区切って指定する。
• 登録する値が文字列の場合は、引用符「'」で囲む。

####NULLとは何か?

NULLというのはINSERT命令で何もしていしなかったところ、「NULL」という文字が入る。
NULLは「値が存在してない」状態を示す。数字の「0」とは違うので注意。

####NULLのあるレコードを検索する

MySQL

SELECT * FROM table名 WHERE IS NULL;

###レコードの更新(変更)

特定のレコードの内容を変更するには、UPDATE命令を使用する。

MySQL

UPDATE テーブル名 SET フィールド名 WHERE 条件;

EXAMPLE

MySQL

UPDATE goods SET origin = 'THAILAND' WHERE id = 5;

---

###レコードの削除

レコードを削除するには、DELETE命令を使用する。

MySQL

DELETE FROM テーブル名 WHERE 条件;

EXAMPLE

MySQL

DELETE FROM goods WHERE id = 5;

####全てのレコードを削除

WHERE句を省略することにより全てのレコードが削除される。

##テーブルの結合
複数のテーブルを対象とした結合方法
内部結合で使用するテーブルは以下の通り

classテーブル

classname	t_id
1-1	t1
1-2	t5
1-3	t7
2-1	t4
2-2	t3
2-3	t9
3-1	t2
3-2	t8
3-3	t8

teachersテーブル

id	name	age
t1	Sato	27
t2	Suzuki	32
t3	Takahashi	31
t4	Tanaka	28
t5	Watanabe	29
t6	Ito	30

外部結合で使用するテーブルは以下の通り

staffテーブル

id	name	unit_id
1	Sato	1
2	Suzuki	2
3	Tanaka	3

unit テーブル

id	u_name
1	Tokyo
2	Osaka
4	Nagoya

###内部結合

WHERE句を使用した内部結合方法

MySQL

SELECT class.classname , teachers.name FROM class, teachers
Where class.t_id = teachers.id;

JOIN句を使用した内部結合方法

MySQL

SELECT classname, name FROM class
JOIN teachers ON class.t_id = teachers.id;

###外部結合

内部結合では紐付けたカラムのデータが片方のテーブルにしかないレコードは出力されない。
結合に使う列のデータが片方のテーブルにしか存在しない場合でも、テーブルを結合する
方法がある。それを外部結合という

####左外部結合

最初に指定したテーブルにあるレコードを全て出力する左外部結合

MySQL

SELECT *, FROM staff
LEFT JOIN unit
ON staff.unit_id = unit.id;

出力結果

id	name	unit_id	id	u_name
1	Sato	1	1	Tokyo
2	Suzuki	2	2	Osaka
3	Tanaka	3	NULL	NULL

最初=左側に指定したテーブルにあるレコードは全て出力されている。
しかし、staffテーブルにある「unit_id = 3」に対応するデータが右側のテーブル(unitテーブル)にないので、その部分は「NULL」となっている

####右外部結合

MySQL

SELECT *, FROM staff
RIGHT JOIN unit
ON staff.unit_id = unit.id;

出力結果

id	name	unit_id	id	u_name
1	Sato	1	1	Tokyo
2	Suzuki	2	2	Osaka
NULL	NULL	NULL	4	Nagoya

左外部結合とは逆。２番目=右側に指定したunitテーブルにあるレコードは全て出力されている。
しかし、unitテーブルにある「id = 4」に対応するデータが左側のテーブル(staffテーブル)にないため、その部分は「NULL」になっている。

##サブクエリ

###2つのSELECT文を1つにまとめる

利用するテーブルは以下の通り

members テーブル

id	name	address	age
1	Sato	Tokyo	27
2	Suzuki	Tokyo	32
3	Takahashi	Kanagawa	32
4	Tanaka	Tokyo	28
5	Watanabe	Kanagawa	29
6	Ito	Tokyo	30
7	Yamamoto	Kanagawa	35

平均年齢より高い人だけ抽出したいときは下記のようにサブクエリを使う

MySQL

SELECT * FROM members age >= (SELECT AVG(age) FROM members); 

上記の通り　>= 以降で具体的な数値を指定するのではなく、その値を得るための
SQL文そのものを()で括って指定するのがサブクエリ。サブクエリを使うと複数のSQL文
を一つにまとめて見やすくすることができる。

###複雑なサブクエリ

membersテーブルと以下のテーブルを使用して複雑なサブクエリで検索結果を表示してみる

class テーブル

classname	t_id
1-1	t1
1-2	t5
1-3	t7
2-1	t4
2-2	t3
2-3	t9
3-1	t2
3-2	t8
3-3	t8

年齢が30歳以上の先生が担任をやっているクラスを検索

MySQL

SELECT classname FROM class 
WHERE t_id IN
(SELECT id FROM teachers WHERE age >= 30);

出力結果

id	name	address	age
2	Suzuki	Tokyo	32
3	Takahashi	Kanagawa	32
7	Yamamoto	Kanagawa	35

「WHERE~比較演算子(サブクエリ)」ではなく「WHERE ~ IN (サブクエリ)」となっている。
「年齢(age)が30歳以上の先生」(SELECT id FROM teachers WHERE age >= 30)
の結果は複数。比較演算子の右側に複数の値があると、どれを比較したら良いのかわからない。
そこで、サブクエリの結果が複数の値になる場合は比較演算子ではなくINを使用する。

##データ整合性を維持する仕組み

###トランザクションとは

トランザクションとは分割することのできない人まとまりの処理のこと。

1. 「口座Aからの引き落としの処理」
2. 「口座Bへの入金処理」

片方だけ実施されると処理に矛盾が生じる。
そのようなことにならないようにひとまとまりの処理のいずれかが失敗したら全体処理の開始時の状態に戻すことによってデータベースに矛盾が生じないようにする。

コミット:トランザクション処理を開始し、全部終わった時点で確定すること
ロールバック：トランザクション処理中に何か問題が発生し、その処理を開始時点に戻すこと

###トランザクション実施例

1. MySQLのストレージエンジンをトランザクションに対応したもの変更する。

　　　※ストレージエンジン：データを格納したり検索したりといった処理を担当する部分

MySQLは複数のストレージエンジンを持っている。デフォルトのMyISAMというストレージエンジンはトランザクションに対応してないのでInnoDBというトランザクションに対応したものに変更が必要。

テーブルのストレージエンジンの調べ方

MySQL

SHOW TABLE STATUS;

ストレージエンジンをInnDBに変更する

MySQL

ALTER TABLE テーブル名 ENGINE = InnoDB;

1. トランザクションを開始する

MySQL

START TRANSACTION;

1. INSERT INTO命令でテーブルにレコードを追加する

コードは省略

1. 3の処理を取り消してトランザクション開始時の状態に戻す

MySQL

ROLLBACK;

1. 3の処理がなかった事になり、INSERT INTO命令がされる前の状態になる

2. 4の時点で COMMIT;を行うとコミットが完了する