SQL・Databesを基本からまとめてみた【SELECT文】

SELECT文

テーブルからデータを抽出、取得するために用いる命令文

Basic.sql

SELECT [カラム（列）名]　FROM [テーブル名];

※ SELECT文のSELECTやFROMを句と呼ぶ

例：membersテーブルからheightカラムとweightカラムを取得する

Sample.sql

SELECT height, weight　FROM members;

SELECT句に書くカラム名には定数や式も書くことができる
例：身長の単位をメートル（m）で表示し、身長の測定日と測定者を表示

Sample.sql

SELECT height/100, ’2002-04-01’, ’Suzuki’　FROM members;

※ SQLでは基本的に半角英数字で記入する

※ 文字列や日付を書く際は、シングルコーテーションで囲う必要がある

WHERE句

• 一部の行だけ取得
  特定の条件を満たすレコード（行）のみを取得するにはWHEREを用いる

Basic.sql

SELECT [カラム（列）名]　FROM [テーブル名] WHERE 条[件式];

例：身長（height）が180cm以上の人の名前を取り出す

Sample.sql

SELECT name FROM member WHERE height >= 180;

複数の条件式を書くことができ、その場合、条件をANDやORで繋げて書く

条件式のどちらも満たしているレコードを取得するならばAND
条件式のどちらかを満たしているレコードを取得するならばORでつなぐ
例：身長（height）が170cm以上かつ体重が70kg以下の人を取得

Sample.sql

SELECT name FROM members WHERE height >= 170 AND weight <= 70;

例：身長（height）が180cm以上または体重が160cm以下の人を取得

Sample.sql

SELECT name FROM members WHERE height >= 180 OR weight <= 160;

COUNT関数

• レコード（行）の数を数える
  レコードの数を数えて表示するには　COUNT関数を用いる

Basic.sql

SELECT 	COUNT（*）FROM [テーブル名];

例：50歳以上の人数を取得する

Sample.sql

SELECT 	COUNT(*) FROM members WHERE age >= 50;

COUNT関数のような集計に用いる関数を『集約関数』と呼ぶ
COUNT関数以外にも以下の種類がある

SUM関数　[書き方：SUM（合計を求めるカラム名）]
AVG関数　[書き方：AVG（平均値を求めるカラム名）]
MAX関数　[書き方：MAX（最大値を求めるカラム名）]
MIN関数　[書き方：MIN（最小値を求めるカラム名）]

• カラム名に別名をつけるASキーワード
  COUNT（*）というカラム名ではどんな情報が入っているのかわかりづらい
  => カラム名に別名をつけるにはASを使う
  別名を付けたいカラムの後にAS 別名とすることで実現できる

Basic.sql

SELECT 	COUNT（*）AS [カラム名(別名)] FROM [テーブル名] ;

例：50歳以上の人数を取得してカラム名を『50歳以上の人数』にする

Sample.sql

SELECT 	COUNT（*）AS "50歳以上の人数" FROM members WHERE age >= 50 ;

別名に日本語を使う時は、ダブルクオテーションで囲う必要がある

例：身長の単位をメートルで表示し、身長の測定日と測定者を表示

Sample.sql

SELECT height / 100 AS height_m, "2018-04-01" AS "測定日","吉田"
AS "測定者"
FROM members;

ORDER BY句

• 昇順・降順に並べ替えて表示する
  ORDER BY : 並び替え
  DESC : 降順

Basic.sql

SELECT [カラム名] FROM [テーブル名] ORDER BY [並び替えの基準となるカラム名];

例：membersテーブルの名前と年齢を年齢の若い順に取得

Sample.sql

SELECT name, age FROM members ORDER BY age;

デフォルトでは昇順で取得されるが、降順で取得する事もできる

例：membersテーブルの名前と年齢を年齢の高い順に取得

Sample.sql

SELECT name, age FROM members ORDER BY age　DESC;

GROUP BY句

• テーブルをいくつかのグループに切り分けて扱う場合は、GROUP BY句を用いる
• GROUP BY句は、集計を行う際に用いる

Basic.aql

SELECT [カラム名]　FROM[テーブル名] GROUP BY [集約キー];

※ 集約キーとは ➡︎ どのようにテーブルを切り分けるかを指定する列
集約キーとして指定したカラムが共通のもの同士でテーブルから切り分ける

例：職種(job_id)ごとに人数をカウントする

Sample.sql

SELECT job_id, COUNT（*）FROM members GROUP BY age　job_id;

GROUP BYや集約関数を用いる際の間違えやすいポイント

①SELECT句に書くことのできるカラム制限
GROUP BY句や集約関数を用いる時には、SELECT句には以下のいずれしか書くことができない

• 定数
• GROUP BY句で指定したカラム名(集約キー)
• 集約関数
  ②GROUP BY句やASで命名した別名は指定できない
  SQLでは、SELECT句がGROUP BY句よりも後に実行されるため

③GROUP BY句に並べかえの機能はない
並べ替えで取得するにはORDER BY句が必要

HAVING句

• GROUP BYと条件式
  グループ化したテーブルに対して条件を指定するにはHAVING句を用いる

Basic.sql

SELECT [カラム名]　FROM[テーブル名] GROUP BY [集約キー]
HAVING [グループの値に対する条件式];

例：job_idごとの人数を数えて２人いたjob_idだけを取得する

Sample.sql

SELECT job_id, COUNT（*）FROM members GROUP BY　job_id　COUNT（*）= 2;

このようにHAVING句は、グループ化したテーブルを前提としているため、HAVING句には以下のいずれしか書くことができない

• 定数
• GROUP BY句で指定したカラム名(集約キー)
• 集約関数

JOIN句

• 複数のテーブルを結合する
  テーブルを結合するにはJOINを用いる
  結合の種類には何種類かあるが今回はINNER JOIN（内部結合を）扱う

Basic.sql

SELECT [カラム名]　FROM[テーブル名１] INNER JOIN[テーブル名 2]
ON [結合の条件];

重複したデータが内容になるべく分割してテーブルを作成した上で、必要に応じて結合して仮想的な１つのテーブルとして扱った方が効率が良い

テーブルを結合するにはJOINを用いる　結合の種類には何種類あるが今回はINNER JOIN(内部結合)を扱う

例：membersテーブルのjob_idとjob_idテーブルのidを紐づけて結合する

Sample.aql

SELECT * FROM members 
INNER JOIN jobs ON jobs.id = members.jobs.id;

このように複数のテーブルに結合する時には、カラム名を書くときにどのテーブルのカラムかを指定してあげる必要があるので、jobs.idのようにテーブル名.カラム名というように書く

例：membersテーブルのjob_idとjob_idテーブルのidを紐づけて結合する

Sample.aql

SELECT * FROM members INNER JOIN jobs ON jobs.id = members.jobs.id;

例：体重が50kg以上かつ年収が500万円以上かつ年齢が40歳未満の人の名前を取得

Sample.sql

SELECT members.name, jobs.salary FROM members
INNER JOIN jobs ON jobs.id = members.job_id
WHERE jobs.salary >= 1000 ORDER BY jobs.salary DESC;

LIKE

文字列の『部分一致検索』をするにはLIKEを使う
部分一致　=> 指定した文字が含まれていること

Basic.sql

SELECT [表示要素] FROM [テーブル名] WHERE [要素名] LIKE [部分一致];

例：TシャツとYシャツの値段を取得

Sample.sql

SELECT name, selling_price FROM Products
WHERE name LIKE '%シャツ';

例：『〇〇用品』というカテゴリの商品の名前と販売価格を販売価格が高い順に取得

Sample.sql

SELECT name, selling_price category FROM Products
WHERE category LIKE '%用品' ORDER BY selling_price DESC;

部分一致には前方一致、中間一致、後方一致の3種がある

前方一致:文字列の最初に指定した文字が含まれている（<検索する文字>%）
中間一致:文字列のどこかに指定した文字が含まれている（%<検索する文字>%）
後方一致:文字列の末尾に指定した文字が含まれている（%<検索する文字>）

BETWEEN

『範囲検索』をするにはBETWEENを使う
範囲検索　=> 上限と下限を設定した検索

Basic.sql

SELECT [カラム名] FROM [テーブル名] WHERE [評価する値]
BETWEEN [最小値] AND [最大値] ;

例：販売価格が500円から2000円までの商品名を取得

Sample.sql

SELECT name, selling_price FROM Products WHERE selling_price
BETWEEN 500 AND 2000;

ANDを忘れないこと！
BETWEENを使うと上限と下限の値が含まれることに注意！

IN NOT IN

ORで複数の条件式をつなぐ必要がある場合はINを使う
反対に以外の条件式でつなぐ場合はNOT INを使う

Basic.sql

SELECT [カラム名] FROM [テーブル名] WHERE [カラム名] IN  ('値1', '値2', ..., 値n);

例：販売価格が100円,2300円,9800円の商品の名前と値段を取得

Sample.sql

SELECT name, selling_price FROM Products WHERE selling_price
IN（100, 2300, 9800);

例：販売価格が100円,2300円,9800円以外の商品の名前と値段を取得

Sample.sql

SELECT name, selling_price FROM Products WHERE selling_price
NOT IN（100, 2300, 9800);

ビュー・サブクエリ

より複雑な問い合わせをするためには、ビューとサブクエリを使う

ビュー

よく使うSELECTを保存して、使い回すことのできる機能
ビューが参照されると保存されたSELECT文を実行して仮想のテーブルを作る

Basic.sql

CREATE VIEW [ビュー名] (<ビューのカラム名>, <ビューのカラム名>,...) AS
<SELECT文>;

例：商品カテゴリごとの個数をカウントしたテーブルを取得するビューを定義

Sample.sql

//ビューの定義
CREATE VIEW ProductSum (category, count_product) 
AS
SELECT category, COUNT(*) FROM Products GROUP BY category;

//FROM句にビューを指定
SELECT category, count_products FROM ProductSum;

上ではビューの定義
下では保存したビューの利用を行なっている

• ビューの定義部分
  ASの前ではCREATE VIEW文でビューの名前とビューのカラム名を決定している
  ASの後には保存するSELECT文を書いている
  ASの後に書いたSELECT文が、ASの前に書かれたビュー名とカラム名で保存される
  保存されるSELECT文のカラムとビューのカラムは並び順で一致する
  ビューは一度書いたら削除されるまで保存される

• ビューの利用部分
  FORM句の後にテーブルではなくビューを指定することでビューを利用できる
  FORM句にビューが指定されると指定されたビューが保存しているSELECT文が実行されて
  一時的に仮想的なテーブルを作成する

ビューとテーブルとの違いは『実データを保存しているかどうか』Productsテーブルは各レコードに実データを保存しているが、『ビューはSELECT文を保存』している

実データを保存していないので、以下のメリットがある

• データ容量を節約できる
  どんなに大きなテーブルでも数行のSELECT文として保存ができるため
• データベースの変更に強い
  呼び出された時SELECT文を実行して仮想てきなテーブルを作成するので、
  テーブルの中身が変更されても、変更されたデータをその都度取得できるため

例：以下の条件を満たすビューをProductsテーブルから定義し、定義したビューの全てのカラムを取得する
①販売価格が1000円以上
②登録日が2018年7月1日以降
③カラムは、name, selling_price, registration_date

Sample.sql

//SELECT文
CREATE VIEW Product (name, selling_price, registration_date) 
AS
SELECT name, selling_price, registration_date FROM Products
WHERE selling_price >= 1000 AND registration_date >= '2018-07-01';

//FROM句にビューを指定
SELECT name, selling_price, registration_date FROM Products;

サブクエリ

ビュー定義のSELECT文をFORM句に書き込んだもの

ビューはSELECT文を名前をつけて保存するものだったが、サブクエリはSELECT文を保存せずに直接FORM句に書き込んで使う

Sample.sql

SELECT category, count_products
FROM (SELECT category, COUNT(*) AS count_product 
      FROM Products GROUP BY category) 
AS ProductSum;

ビューとして定義せず、FORM句に直接SELECT文で書いている
サブクエリの後ろにAS サブクエリ名を書くことで、
サブクエリの名前（今回はProductSum）を定義している

サブクエリの使いどころとして、集約関数を組み合わせた分析を可能にする

スカラサブクエリ

取得する値が単一の値（スカラ）になるサブクエリのこと
サブクエリは、複数の値（テーブル）を取得していたが、単一の値を取得するサブクエリをスカラサブクエリと呼ぶ

例：販売価格の平均値より販売価格が高い商品名と販売価格を表示

Sample.sql

SELECT name, selling_price FROM Products 
WHERE selling_price > (SELECT AVG(selling_price) FROM Products);

SELECT AVG(selling_price) FROM ProductsはProductsテーブルの販売価格の平均値である2678.7500という単一の値を取得するので、スカラサブクエリである

CASE式

場合分け（条件分岐）を記述するにはCASE式を使う
CASE式には単純CASE式と検索CASE式がある
単純CASE式は検索CASE式によって表現できるため、検索CASE式のみ使う

Basic.sql

//検索CASE
CASE WHEN <条件式> THEN <値・式>　
　　　WHEN <条件式> THEN <値・式>
　　　ELSE<値・式>
END

例：セール時の価格を表示する(1000円以上は１割引、5000円以上は２割引)

Sample.sql

SELECT name,
       CASE WHEN selling_price >= 5000 THEN selling_price * 0.8
       CASE WHEN selling_price >= 5000 THEN selling_price * 0.9
　　　  ELSE selling_price
END

CASE式はこれ自体が一つの『式』であるため、単一の値を結果として返す
ELSEはその条件式も満たされない時に返される値・式を記述する
ENDは省略不可なので注意！ELSEは省略可能だが原則として書く！

例：商品カテゴリごとの販売価格の合計を１行で取得する
得たい結果
category SUM(selling_price)
キッチン用品　7100
事務用品　10530
衣類　3800

Sample.sql

SELECT
SUM(CASE WHEN category = 'キッチン用品' THEN selling_price
　　ELSE 0 END) AS 'キッチン用品',
SUM(CASE WHEN category = '事務用品' THEN selling_price
　　ELSE 0 END) AS '事務用品',
SUM(CASE WHEN category = '衣類' THEN selling_price
　　ELSE 0 END) AS '衣類',
FROM Products;
END

参考サイト

3時間で学ぶ SQL ・データベース 超入門【丁寧な解説+演習問題で SQL データ抽出の基本が身につく】標準 SQL