基本構文
select from where 構文
select 得たい列 from table名 where 条件式;
全ての列を得たい場合は を使用する。
whereの条件が複数ある場合はANDまたはORでつなぐ。
sql
select * from users;
select name, age from users where age > 20 AND gender='female';
※必要な列に絞ることでパフォーマンスが改善し料金が抑えられるので、なるべく、 は使用しないにしましょう
sqlの記述順序
- select
- from
- where
- group by
- having
- order by
- limit
sqlの実行順序
- from
- where
- group by
- having
- select
- order by
- 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;