MySQL データベース操作の基本

RDBMSとは

各データは 2次元の表(テーブル)によって表現される。
行と列それぞれに情報が記載されており、データ自体は複数かつ多様化しているけど、それぞれのデータの共通部分を探し出し、結局はひとつの巨大なデータベースとして機能するというもの。
現在では多くの商用データベースシステムが、このリレーショナル型を採用している。

SQLとは

RDBMSを制御する「データベース言語」。
主な機能は、
1.データベースの定義
2.データベースの操作
3.トランザクション管理

よく使うデータ型

・char：固定長文字列
・varchar：可変長文字列
・text：サイズ無制限の文字列
・int：整数
・double：浮動小数点数
・datetime：年月日・時刻
・enum：文字列定数をリスト化

テーブル操作

基本3操作

1.CREATE TABLE

テーブルの作成。
ex)

CREATE TABLE BLOG
(
  ID int NOT NULL,
  タイトル char(100)
  日付 date,
  本文 text
); 

2.ALTER TABLE

テーブルの変更。
主な変更は以下の３つ。
・列の追加・削除
・テーブル制約の追加・削除
・列に対するデフォルト値の設定・削除

3.DROP TABLE

テーブルの削除。
①DELETE文で、テーブルのデータを空にする。
②DROP TABLEで、テーブルを破棄する。
この２段階を踏まないと、テーブルが正しく破棄されない。

制約

主キー

レコードを一意的に特定するためのもの。IDに使われることが多い。

CREATE TABLE USER
(
  ログインID int PRIMARY KEY,
  email varchar(40) UNIQUE
);

UNIQUE

これを設定するといわゆる「ダブり」が起こったときにはじいてくれる。emailやログインIDによく用いられる。

CREATE TABLE USER
(
  ログインID int UNIQUE,
  email varchar(40) UNIQUE
);

NOT NULL

「必須項目」を指定することができる。

CREATE TABLE INFO
(
  名前 varchar NOT NULL,
  電話番号 int NOT NULL
);

CHECK

項目に条件を指定して、それを満たしていなければはじいてくれる。
「修学旅行でお菓子を500円までしか買っちゃダメ」みたいな条件を付けて以下に例を示す。

CREATE TABLE SWEETS_BUY
(
  名前 varchar(40),
  個数 int CHECK(個数 <= 4)
  金額 int CHECK(金額 <= 500)
);

DEFAULT

値をあらかじめ設定したいときに記述する。以下は性別選択でデフォルトを「男性」に設定したいときの記述である。

CREATE TABLE INFO
(
  名前 varchar(40),
  性別 enum('man', 'woman') DEFAULT 'man'
);

データベース操作

すでに表が定義されていることを前提。

基本3操作

1.INSERT INTO

データの作成。「VALUES」の中身は複数作成可能。

INSERT INTO BLOG(タイトル,日付,本文)
        VALUES('ブログ始めました',2015-04-30 23:00, 'hogehoge');

2.UPDATE

データの変更。WHERE句で指定するケースが多い。

UPDATE BLOG SET タイトル = 'hogehoge'
WHERE  ID = 2;      

3.DELETE

データの削除

DELETE FROM BLOG WHERE ID = 2;

データ参照

SQL構文を用いてデータベースに問い合わせることを「クエリ」と呼ぶ。基本的には「SELECT」を用いて欲しいデータを生成・表示するように指示する。

SELECT 名前, 電話番号, email FROM 個人情報;

条件付きで参照したいときはWHERE句を用いる。

SELECT 名前, 電話番号, email FROM 個人情報
        WHERE ID <= 8;

特殊演算子

1.IN

含まれる値と含まれない値の集合を明示的に定義することが可能。

SELECT * FROM 個人情報
     WHERE ID IN(1, 3);

2.BETWEEN

TRUEが帰る値の範囲を定義する演算子。

SELECT * FROM 個人情報
     WHERE 年齢 BETWEEN 10 AND 30;

3.LIKE

文字列を検索するもの。

SELECT * FROM 個人情報
     WHERE 名前 LIKE miya%;

これを実行すると"miyashita"も"amamiya"も"miyamae"も「TRUE」である。「%」は何文字だろうが「miya」が含まれていればなんでもOK。要するに、部分一致なのです。

ただし次の命令は少し違う。

SELECT * FROM 個人情報
     WHERE 名前 LIKE miya___;

これを実行すると、"miyashita"は「FALSE」、"miyamae"は「TRUE」が返ってくる。「_」は文字数制限付きなのだ。

集約関数

1つの列グループに対して施すことのできる演算機能のこと。
SUM()：列の合計
AVG()：列の平均
MAX()：列の最大値
MIN()：列の最小値
COUNT()：行数を求める

ex)

SELECT AVG(年齢) FROM 個人情報

並び替え

出力に順番を適用できるようになっている。記述の仕方は以下のとおり。

SELECT * FROM 個人情報 ORDER BY 年齢 DESC;

結合

いわゆる「リレーションシップ」をもとに複数のテーブルを結合して、データを引き出す。
ex)ブログの場合
BLOGテーブルに[ID, タイトル, 日付, 本文]
USERテーブルに[ID, 名前, email, パスワード]
がそれぞれ入っているとする。
「BLOGのID」と「USERのID」をひも付けて[名前, 日付, 本文]を一覧表示させたいとき、以下のように記述する。

SELECT USER.名前, BLOG.日付, BLOG.本文
    FROM BLOG,USER
    WHERE BLOG.ID = USER.ID; 

「SELECT」で表示させたい項目を(テーブル名).(項目名)で記述し。「FROM」で結合したいテーブルを記述し、「WHERE」で細かい条件を指定する。

サブクエリ

複数のクエリを組み合わせて、1つのクエリが生成した出力で、他のクエリの出力を制御することができる。
一番の標準形は「入れ子」構造。内側のクエリで生成された値を外側のクエリが受け取り、WHERE 句で TRUE となるものについて、値を参照する。
基本的なサブクエリはWHERE句の述語によく用いられる。

SELECT ユーザーID FROM BLOG
         WHERE ID IN
         (SELECT ID FROM USER
          WHERE 年齢 >= 20);

トランザクション

複数の処理を意味的に１つの処理として扱うこと。

ACID特性

1.ATOMICITY（原子性）

「それ以上分割することのできない最小単位」がトランザクションの条件。なぜならトランザクションを構成する処理の結果は、「すべて有効」または「すべて無効」の二択。白黒はっきりしてるのです。

2.CONSISTENCY（一貫性）

トランザクションは実行前・後でデータの整合性を持つので、一貫したデータ確保が必要となる。

3.ISOLATION（隔離性）

トランザクションが複数あってデータを共有している場合、片方のトランザクションで変更中のデータをもう片方のトランザクションで処理するのは不可能。

4.DURABILITY（持続性）

トランザクションで処理されるデータの状態はトランザクションが終了するまで変化しない。

インデックス

データの検索速度を向上させるために、「行」に関する場所が書いてある索引のこと。データを見つけるのに、
×　一行一行探す
○　索引から探す
のでは、効率さが全然違ってきます。データベースの規模が大きければなおさら。

長所・短所

表の結合条件に使用される列や、値の分布が大きな列に対してインデックスを作成するとパフォーマンス向上につながる。
逆に値の分布が小さな列や、テーブルが頻繁に更新されるような場合はパフォーマンスの低下につながってしまう。さらにデータ挿入時にインデックスを貼りすぎていると、動作が重くなる。
結論：バランス良く使うフィールド限定にすべき。