はじめに
まずはじめに、あなたは、SQL得意ですか？？
いいえと答えたあなたは、ぜひこのまま読み進めてください。確実に力になれるはずです。
自分も入社後につまづいたことの一つにSQLがあります。生のSQLを触らなくても、Railsの開発中にSQLの知識が乏しいためにつまづいた。。なんてこともあります。
エンジニアにおいて、SQLの基礎知識は必須のものだと思うので、皆さんの力になれると幸いです！
※paiza.IOというサイトですぐにSQLを書けるので、試しながら記事を閲覧するのもいいかもしれないです！
SQLとは
RDBMS(リレーショナルデータベースマネジメントシステム)をコントロールするための言語のことです。
その中でも、どのRDBMSにも使える標準SQLと、RDBMSごと特有のSQLがあります。
特徴
SQLでは、主に以下のことが可能です。
- データ操作
- 表のデータの登録・修正・削除・検索、複数の表の結合など
- データ定義
- データベースのデータ構造の定義など
- データ制御
- データベースへのアクセス制御など
SELECT文
基本形
テーブルからデータを抽出、取得するために用いる命令文のこと。
# SELECT カラム名 FROM テーブル名;
SELECT name FROM users;
#=> usersテーブルから、nameカラムを取得
WHERE
特定の条件を満たすレコード(行)のみを取得する命令文のこと。
# SELECT カラム名 FROM テーブル名 WHERE 条件式;
SELECT name FROM users WHERE age >= 20;
#=> usersテーブルから、年齢が20歳以上のnameカラムを取得
SELECT name FROM users WHERE age >= 20 AND id <= 10;
#=> usersテーブルから、年齢が20歳以上かつidが10以下のnameカラムを取得
SELECT name FROM users WHERE age >= 20 OR id <= 10;
#=> usersテーブルから、年齢が20歳以上、またはidが10以下のnameカラムを取得
COUNT
レコードの数を数えて表示する命令文のこと。
SELECT COUNT(*) FROM テーブル名;
SELECT COUNT(*) FROM users WHERE age >= 50;
#=> usersテーブルの50歳以上の人数を取得
SELECT COUNT(*) AS '高齢者' FROM users WHERE age >= 65;
#=> usersテーブルの65歳以上の人数を「高齢者」という名前で取得
ORDER BY
並べ替えて表示させたい場合に用いる命令文のこと。
※デフォルトでは昇順(小さいものから大きいものの順)で取得される
# SELECT カラム名 FROM テーブル名 ORDER BY 並べ替えの基準となるカラム名;
SELECT name, age FROM users ORDER BY age DESC;
#=> usersテーブルの名前と年齢を年齢の高い順に取得
GROUP BY
テーブルをいくつかのグループに切り分けて扱う命令文のこと。
※集約キー...テーブルをどのように切り分けるかを指定するカラム
# SELECT カラム名 FROM テーブル名 GROUP BY 集約キー;
SELECT category_id, COUNT(*) FROM products GROUP BY category_id;
#=> カテゴリ(category_id)ごとの商品数をカウント
SELECT category_id, COUNT(*) FROM products WHERE price <= 1000 GROUP BY category_id;
#=> 値段が1000円以下の商品の中でカテゴリごとにグループ分けして商品数をカウント
HAVING
グループ化したテーブルに対して条件を指定する命令文のこと。
# SELECT カラム名 FROM テーブル名 GROUP BY 集約キー HAVING グループの値に対する条件式;
SELECT category_id, COUNT(*) FROM products GROUP BY category_id HAVING COUNT(*) = 2;
#=> カテゴリごとの商品数をカウントして、2個だったカテゴリだけを取得
JOIN
複数テーブルの結合を行う命令文のこと。
JOINにはいくつかの種類があります。
INNER JOIN
指定した関連性のある要素を軸にして、組み合わせてくれる命令文のこと。
# SELECT カラム名 FROM テーブル名1 INNER JOIN テーブル名2 ON 結合の条件;
SELECT * FROM products INNER JOIN categories ON categories.id = products.category_id;
#=> productsテーブルのcategory_idとcategoriesテーブルのidを紐づけて結合
OUTER JOIN
内部結合 + αの情報を表示する命令文のこと。
「LEFT OUTER JOIN」「RIGHT OUTER JOIN」「FULL OUTER JOIN」という3種類があります。
LEFT OUTER JOIN
LEFTとは左側、つまりFROMで指定した側のテーブルを指します。このテーブル側を軸に情報を表示する命令文のこと。
# SELECT カラム名 FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON 結合条件;
SELECT * FROM team LEFT OUTER JOIN users ON team.team_id = users.team_id;
#=> teamテーブルのteam_idとusersテーブルのteam_idを紐づけて結合
RIGHT OUTER JOIN
右側の表の内容をすべて抽出し，左側の表の内容は左側の表の内容と一致するもののみ抽出する命令文のこと。
# SELECT カラム名 FROM テーブル名1 RIGHT OUTER JOIN テーブル名2 ON 結合条件;
SELECT * FROM team RIGHT OUTER JOIN users ON team.team_id = users.team_id;
#=> teamテーブルのteam_idとusersテーブルのteam_idを紐づけて結合
FULL OUTER JOIN
2つのテーブルそれぞれに一致しないレコードも抽出結果に含めて抽出する命令文のこと。
# SELECT カラム名 FROM テーブル名1 FULL OUTER JOIN テーブル名2 ON 結合条件;
SELECT * FROM team FULL OUTER JOIN users ON team.team_id = users.team_id;
#=> teamテーブルのteam_idとusersテーブルのteam_idを紐づけて結合
LIKE
文字列の部分一致検索をする命令文のこと。
SELECT name, price FROM products WHERE name LIKE '%シャツ';
#=> 「シャツ」を含む名前の値段を取得する
BETWEEN
範囲検索(上限と下限の値を設定した検索)を行う命令文のこと。
SELECT name, price FROM products WHERE price BETWEEN 500 AND 2000;
#=> 値段が500円から2000円までの商品名を取得
IN
ORで複数の条件式をつなぐ必要がある場合にすっきりと書くことが可能。
SELECT name, price FROM products WHERE price IN(1000, 2000, 3000);
#=> 値段が100円、2300円、9800円の商品の名前と値段を取得
SELECT name, price FROM products WHERE price NOT IN(1000, 2000, 3000);
#=> 値段が100円、2300円、9800円の商品以外の名前と値段を取得
ビュー
よく使うSELECT文を保存して、使い回すことのできる機能のこと。
ビューは一度書いたら削除するまで保存されます。
データ容量を節約できたり、データベースの変更に強いことが特徴です。
※ビューの定義にはORDER BYを使うことはできないので注意が必要。
# CREATE VIEW ビュー名 (カラム名1, カラム名2, ...) AS SELECT文;
# ビューの定義部分
CREATE VIEW ProductCount (category, product_count) AS SELECT category, COUNT(*) FROM products GROUP BY category;
# ビューを参照
SELECT category, product_count FROM ProductCount;
ビューを削除する際は以下のようにする。
# DROP VIEW ビュー名;
DROP VIEW ProductCount;
サブクエリ
ビュー定義のSELECT文を、FROM句に直接書き込んだもの。
SELECT category, product_count FROM (SELECT category, COUNT(*) FROM products GROUP BY category) AS ProductCount;
スカラサブクエリ
取得する値が単一の値になるサブクエリのこと。スカラ値を記入できる箇所ならどこでもスカラサブクエリを書くことが出来ます。
SELECT name, price FROM products WHERE price >= (SELECT AVG(price) FROM products);
#=> 値段の平均値よりも高い商品名と値段を表示
CASE
条件分岐を記述する命令文のこと。CASE式には単純CASE式と検索CASE式がある。
# CASE WHEN 条件式 THEN 値(式)
# WHEN 条件式 THEN 値(式)
# ELSE 値(式)
# END
SELECT name,
CASE WHEN price >= 3000 THEN price * 0.7
WHEN price >= 1000 THEN price * 0.6
ELSE price
END AS 'セール価格"'
FROM products;
#=> セール価格を表示(1000円以上は4割引き、3000円以上は3割引き)
INSERT文
基本形
テーブルにレコードを追加するのに用いる命令文のこと。
※指定しなかったカラムにはNULLが入ります。
# INSERT INTO テーブル名(カラム名1, カラム名2, ...) VALUES(値1, 値2, ...);
INSERT INTO users(id, name, age) VALUES(11, "吉田", 23);
#=> usersテーブルにid=11、name='吉田'、age=23のレコードを追加
UPDATE文
テーブル中にあるデータの値を更新する命令文のこと。
# UPDATE テーブル名 SET カラムと値の指定;
UPDATE users SET age = 25 WHERE id= 1;
#=> idが1のユーザーの年齢を25に更新
DELETE文
テーブル中にあるレコードを削除する命令文のこと。
# DELETE FROM テーブル名;
DELETE FROM users WHERE id= 10;
#=> idが10のユーザーを削除
まとめ(注意点)
- 命令文の終わりには ; (セミコロン)を忘れずにつけること
- 単語の間は半角スペースか改行で区切ること
- 命令文は慣例的に大文字で記述すること
- 文字列や日付を書く際にはシングル(ダブル)クォーテーションで囲うこと
- WHEREの後に条件式を複数個書く場合はANDやORでつなげること
- SELECT句には、「定数」「集約キー」「集約関数」のいずれかしか書くことが出来ない
- HAVING句には、「定数」「集約キー」「集約関数」のいずれかしか書くことが出来ない
- BETWEENを使うと上限と下限の値が含まれることに気を付ける
- よく使うSELECT文はビューとして定義しておく
- サブクエリの後ろには、必ず「AS サブクエリ名」と別名をつけること
- サブクエリは内側から順番に実行されていく
- SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BYの順でSQL文を書く
終わりに
ここまでいろいろなSQLの知識を紹介してきましたが、あくまで基礎知識です。
ここからさらに発展的な使い方などもあるので、基礎を押さえた人は挑戦してみましょう！
参考知識
比較演算子
# aがbより大きい
a > b
# bがaより大きい
a < b
# aはb以上
a >= b
# bはa以上
a <= b
# aとbは等しい
a = b
# aとbは等しくない
a <> b
集約関数
# SUM(合計を求めるカラム名)
SELECT SUM(age) FROM users WHERE age >= 50;
#=> usersテーブルの年齢が50以上の数の合計を取得
# AVG(平均値を求めるカラム名)
SELECT AGE(age) FROM users WHERE age >= 50;
#=> usersテーブルの年齢が50以上の平均を取得
# MAX(最大値を求めるカラム名)
SELECT MAX(age) FROM users WHERE age >= 50;
#=> usersテーブルの年齢が50以上の最大値を取得
# MIN(最小値を求めるカラム名)
SELECT MIN(age) FROM users WHERE age >= 50;
#=> usersテーブルの年齢が50以上の最小値を取得
部分一致
# 前方一致...文字列の最初に指定した文字が含まれている
<検索する文字>%
# 中間一致...文字列のどこかに指定した文字が含まれている
%<検索する文字>%
# 後方一致...文字列の末尾に指定した文字が含まれている
%<検索する文字>