search
LoginSignup
67

More than 3 years have passed since last update.

posted at

updated at

Organization

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つ。
・列の追加・削除
・テーブル制約の追加・削除
・列に対するデフォルト値の設定・削除

3.DROP TABLE

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

制約

主キー

レコードを一意的に特定するためのもの。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);

トランザクション

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

ACID特性

1.ATOMICITY(原子性)

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

2.CONSISTENCY(一貫性)

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

3.ISOLATION(隔離性)

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

4.DURABILITY(持続性)

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

インデックス

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

長所・短所

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
67