2. SQL基礎

2.1. SQLとは

SQL(Structured Query Language)とはデータベースを操作するための言語。 DBMS上でデータの追加や削除、並べ替えなどを行うようコンピュータに命令することができる。 基本的に１行ずつ入力して確定し、直ちに実行される。複数のSQLを組み合わせて大きな一つの塊のSQLとして実行することもできるが、通常のプログラミング言語のように一連の操作をまとめてセットすることのできる「ストアドプロシージャ」という機能のあるDBMSもある。

2.1.1. ストアドプロシージャ

DBMSにSQL文を1つのプログラムにまとめ保存しておくことはストアドプロシージャと呼ばれる。 一連の処理が実行される。

また、メリットは以下の通り。

• ネットワークの負荷削減
• 処理速度の向上

2.1.2. SQLの種類

標準SQL規格では大きく以下の3つが定義されている。

• データ定義言語(DDL).....例：CREATE,DROP,ALTER等
• データ操作言語(DML).....例:INSERT,UPDATE,DELETE,SELECT
• データ制御言語(DCL).....例:GRANT,REVOKE,SET TRANSACTION,BEGIN,COMMIT,ROLLBACK,SAVEPOINT,LOCK

2.1.3. SQLの4大命令

命令	説明	文法
INSERT	データを追加する	INSERT INTO テーブル名 (カラム名1, カラム名2, ...) VALUES (値1, 値2, ...);
SELECT	データを参照する	SELECT カラム名1, カラム名2, ... FROM テーブル名 [WHERE 絞込条件];
UPDATE	データを更新する	UPDATE テーブル名 SET カラム名1=値1 [, カラム名2=値2 ...] [WHERE 絞込条件];
DELETE	データを削除する	DELETE FROM テーブル名 [WHERE 絞込条件];

2.2. SQL基本操作

操作例を用いて解説。

操作例のテーブル

membersテーブル

id	name	sex	birthday
1	tarou	male	1999-11-30
2	hanako	female	1993-01-14

2.2.1. INSERT文

INSERT はテーブルにレコードを追加するSQL文。

INSERT INTO members (name,sex,birth_day)
VALUES ('tarou', 'male', '1992-11-30');

INSERT INTO members (name,sex,birth_day)
VALUES ('hanako', 'femal','1993-01-14');

2.2.2. SELECT文

全件全列を参照する

SELECT * FROM members; -- 全件検索

特定の列だけを参照する

全ての列は要らない時はカラム名で絞込をしよう。

SELECT name FROM members; -- 全件検索(名前だけ見たい)

特定の条件に一致するデータだけを参照する

SELECT * FROM members WHERE name = 'tarou'; -- 名前が"tarou"のレコードを検索

2.2.3. UPDATE文

全件特定のカラムを更新

--membersのレコードのnameをすべて'jirou'に更新する
UPDATE members SET name = 'jirou';

実行後のテーブル

id	name	sex	birthday
1	jirou	male	1999-11-30
2	jirou	female	1993-01-14

絞込条件に該当するデータの特定のカラムを更新

--membersのレコードでidが1のレコードのname,birth_dayを更新
UPDATE members SET name = 'saburo', birth_day = '2015-03-11' WHERE id = 1;

実行後のテーブル

id	name	sex	birthday
1	aburo	male	2015-03-11
2	jirou	female	1993-01-14

2.2.4. DELETE文

特定条件に該当するデータを削除

--idが1のレコードを削除する
DELETE FROM members WHERE id = 1;

実行後のテーブル

id	name	sex	birthday
2	jirou	female	1993-01-14

全件データを削除

--membersのレコード全てを削除する
DELETE FROM members;

実行後のテーブル

id	name	sex	birthday

2.2.5. 絞り込み WHERE

データの全体母数からWHERE句を使ってデータ集合を絞り込むために利用する。 WHERE句が使えるのはSQL4大命令のうち「SELECT」、「UPDATE」、「DELETE」。

SELECTのWHEREによる絞り込み

SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" = "値";

利用できる比較演算子

記号	説明
=	等価演算子
<=>	安全等価演算子(NULL)
>	右不等演算子
>=	以上演算子
<	左不等演算子
<=	以下演算子
!=, <>	不等価演算子

なおWHERE句内の比較演算はAND,ORで絞り込みが可能。

SELECT * from users WHERE age >= 20 AND age <= 30; 

2.2.6. BETWEEN句

〇〇以上、〇〇未満の情報を取得するというケースは不等号を使っても表現することが出来るが、BETWEENを使うことでも表現することが可能。

SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" BETWEEN "最小値" AND "最大値";

BETWEEN句以降に最小値と最大値を指定する。 この時最小値と最大値の順番は重要となる。 BETWEEN句が使えるのは、整数型を代表する以上以下で表現が出来る型のみ。

2.2.7. LIKE句

部分一致で検索するときに用いる。 LIKE句で使う"%"は、ワイルドカードと読んでおり、"%ky%"としたときには、kyを含む文字列を取得することが出来る。 これを応用して、"ky%"と書いた時には、kyから始まる文字列の絞込が出来る。

SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" LIKE "%絞込をしたい文字列%";

2.2.8. INとNOT IN

INで指定するときには、カンマ区切りの配列で複数の条件を指定することができる。 文字列の場合にも("foo","bar","baz")という形で指定をすることができる。

SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" in ("指定したい情報をカンマ区切りで指定");
SELECT ”取得したい情報” from "テーブル名" WHERE "絞込をしたいcolumn名" not in ("指定したい情報をカンマ区切りで指定");

2.2.9. EXISTS

2つのテーブルが関係する場合の検索処理に用いる。

SELECT ”取得したい情報” from "テーブル名" WHERE EXISTS (SELECT "column名" FROM "判定に使うテーブル名" WHERE "テーブル名.column名" = "判定に使うテーブル名.判定に使うcolumn名");

SQLの中にSQLが書かれている形のことをサブクエリーと呼ぶ。 サブクエリーが何なのか？という説明よりも、()の中で問い合わせた結果を使って再度検索が動いているのをイメージ出来るようになるとレベルアップ出来る。

2.10. ORDER BY文

ORDER BYはソートを行ってくれる機能。

SELECT [表示要素名] FROM [テーブル名] ORDER BY [ソートする要素名] [昇順・降順の指定];

昇順・降順の指定

指定	順
ASC	昇順
DESC	降順

並び順の指定を省略した場合はASCがデフォルト。 複数のソートキーも対応している。はじめに書くほど優先でカラムが実行される。

2.2.11 表示件数の制限

LIMITはデータの表示件数を制限できる。

select * from テーブル名 LIMIT 取得件数;

2.2.12. テーブル定義の確認

DESCRIBEはテーブルの定義情報を確認することができる。

DESCRIBE テーブル名;

2.2.13. SQLの集合関数

集合関数とは、SQLに備わっている演算機能。 集合関数には主に次の5つがある。

関数名	説明
COUNT	総数を求める
SUM	総和を求める
MAX	最大値を求める
MIN	最小値を求める
AVG	平均を求める

COUNT

SELECT COUNTはデータの件数を数える。

select count(*) from テーブル名;

SUM

sumは総和です。

select sum(カラム名) from テーブル名;

MAX

maxは最大値です。

select max(age) from users where birthplace = '大分県';

MIN

MINは最小値です。

select count(*),max(age),min(age) from users where birthplace = '大分県' and gender_id = 0;

AVG

avgは平均値です。

SELECT count(*) AS 総数,
       max(age) AS 最高齢,
       min(age) AS 最年少,
       avg(age) AS 平均年齢
FROM users
WHERE birthplace = '大分県';

2.2.14. グループ

GROUP BY

group byは「〜ごと」という処理を行う。

SELECT 関数名(カラム名１),カラム名２ FROM テーブル名 GROUP BY カラム名２;

HAVING

having は集合関数の結果をもとに絞り込むことができる。

SELECT 関数名(カラム名１),カラム名２ FROM テーブル名 GROUP BY カラム名２ HAVING 関数名(カラム名１);

2.3. テーブルの結合

テーブルの結合には幾つかのパターンがある。

• 内部結合　：　指定された共通列で、紐付いているレコード”のみ” で結合テーブルが作成される
• 外部結合　：　指定された共通列で、紐付いているレコード”以外” も結合テーブルとして作成される

2.3.1. 内部結合

内部結合とは、２つのテーブルを結合しデータを取得する方法において、共通列が一致するレコード"のみ" 取得する方法が内部結合になります。

SELECT
  テーブルA.カラム1,
  テーブルB.カラム1,
  ......
FROM
  テーブルA
  INNER JOIN テーブルB ON テーブルA.カラム2 = テーブルB.カラム2

内部結合では、FROM句で指定したテーブルAと、結合するテーブルBをINNER JOIN句で指定する。 そして、テーブル同士の紐付け条件としてON句を利用し、共通列となるテーブルA.カラム2、テーブルB.カラム2を指定し紐付けを行う事でテーブルが結合される。

2.3.2. 外部結合

左外部結合

SELECT
  テーブルA.カラム1,
  テーブルB.カラム1,
  ......
FROM
  テーブルA
  LEFT OUTER(省略可) JOIN テーブルB ON テーブルA.カラム2 = テーブルB.カラム2

左外部結合では、FROM句で指定したテーブルAと、結合するテーブルBをLEFT JOIN句で指定する。 テーブル同士の紐付け条件は、内部結合と同様にON句を利用し、共通列となるテーブルA.カラム2、テーブルB.カラム2を指定し紐付けを行う。

右外部結合

SELECT
  テーブルA.カラム1,
  テーブルB.カラム1,
  ......
FROM
  テーブルA
  RIGHT OUTER(省略可) JOIN テーブルB ON テーブルA.カラム2 = テーブルB.カラム2

右外部結合では、左外部結合とは反対で、RIGHT JOIN句で指定したテーブルBを基に、FROM句で結合するテーブルAをで指定する。 テーブル同士の紐付け条件は、左外部結合と同様にON句を利用し、共通列となるテーブルA.カラム2、テーブルB.カラム2を指定し紐付けを行う。

完全外部結合

SELECT
  テーブルA.カラム1,
  テーブルB.カラム1,
  ......
FROM
  テーブルA
  FULL OUTER(省略可) JOIN テーブルB ON テーブルA.カラム2 = テーブルB.カラム2

完全外部結合は、サンプルのSQLを見て頂くと解る通り、左外部結合と右外部結合の機能を併せ持っている。 テーブル同士の紐付け条件は、左/右外部結合と同様にON句を利用し、共通列となるテーブルA.カラム2、テーブルB.カラム2を指定し紐付けを行う。