SQLチートシート

基本構文

select from where 構文

select 得たい列 from table名 where 条件式;

全ての列を得たい場合は * を使用する。
whereの条件が複数ある場合はANDまたはORでつなぐ。

select * from users;
select name, age from users where age > 20 AND gender='female';

※必要な列に絞ることでパフォーマンスが改善し料金が抑えられるので、なるべく、* は使用しないにしましょう

sqlの記述順序

1. select
2. from
3. where
4. group by
5. having
6. order by
7. limit

sqlの実行順序

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit

selectの順番だけ実行と記述で順序がことなることに注意。

集約関数

※集約関数では、nullは無視されるので特に配慮は不要

合計値を求めるsum

2019年に入ってからの売り上げが知りたい場合

select sum(amount) from orders where order_time >= '2018-01-01 00:00:00';

平均値avg

select avg(price) from orders where order_time >= '2018-01-01 00:00:00';

最小値min,max

select min(price) from places;

数を数えるcount

-- 女性ユーザー
select count(*) from users where  gender='male';
-- 四月のUU(応用)
select count(distinct user=id) from access_logs where between '2019-04-01' AND '2019-04-31';

グループごとに集計するgroupby

-- 都道府県ごとのホテルの数
select prefecture, count(*) from places group by prefenture;
-- 2018年の月別UU(応用)
select created_month, count(distinct user_id) form access_logs where created_month between '2019-01-01 ' AND '2019-01-01' group by created_month;

created_monthのカラムがない場合、asを用いてcreated_atから作り直す必要があるが、後述

having

group byされたものから、絞り込み

select request_month, count(*) from access_logs where created_at between '2018-01-01 00:00:00' AND  '2019-01-01 00:00:00' group by request_month having count(*) > 1000;

データの並び替え

order by

-- 価格の安い順にホテルを出力
--価格が同じときは登録日時が早い順
select * from places order by prices asc, id asc;

ascは低い順、descは高い順
カンマ区切りで複数指定できる

関数と演算子

絶対値の取得

select abs(-10);

四捨五入round

-- 税込み価格を表示
select id, name, round(price * 1.08, 0) from places;

文字列結合concat

select concat(family_name, 'さん') from users;

日付と時刻の演算

主な演算子

• 現在の日付・・current_date
• 現在の時刻・・current_timestamp
• n日後の日付 d + interval n day
• 時刻の日や月のみを取り出して出力・・extract
• 値を特定の型としてキャスト・・cast

-- 2018年1月に作られたplacesの名前を取り出す
select name from places where extract(month_year from created_at)=201801;

--UU
SELECT DATE_FORMAT(created_at, ‘%Y-%m’) as reg_time,COUNT(DISTINCT user_id) 
FROM access_logs GROUP BY reg_time ;

テーブルの結合

内部結合 innner join

select reservation.id, places_id from reservation inner join
on reservation.places_id = places.id; 

サブクエリ

-- 2018年に商品を購入していないユーザーの一覧
select * from users where not in
(select id from orders where created_at between 20180101 and 20190101)

-- 全ホテルの平均単価よりも高いホテルのみを取得し、商品単価の高い順に並べる
select * from places where price > (select avg(price) from places)
order by price desc;