#はじめに
sqlを勉強して新しく学んだことなどを忘備録として残します。
#where id in(1,2,3)
select * from products where id = 1 or id = 2 or id = 3
みたいに書くのではなく
select * from products where id in (1,2,3)
とかくとスマート
#is not null
select * from users where age = null
これだとうまく動かないので
select * from users where age is null
とする
#between
select * from foods where price >= 1000 and price <= 2000
これは
select * from foods where between 1000 and 2000
と書ける
#%
曖昧検索するときに
select * from users where name like '%田%';
みたいにできる
%田 で最後に田がつくもの
田% で最初に田がつくもの
#_
曖昧検索のときに文字の数を指定したいときに_が使える
select * from users where name like '__子'
これで名前が三文字で最後に子がつくものを取ってこれる。
#interval
intervalで時間を足し引きできる
select current_date() + interval 3 day;
select current_date() - interval 3 day;
select current_timestamp + interval 3 hour;
select current_timestamp - interval 3 hour;
#extract
年、月を数字列で表示してくれる
select * from posts where extract(year_month from created_at) = 201701;
これで2017年1月のpostsがとってこれる。
year_month以外にも
year month day hour minute second が使える
#date_format
extractと似ている
select * from posts where date_format(created_at, '%Y%m') = 201701;
みたいにつかう
#サブクエリ
()でくくってsql文を実行してその結果を使える。
#サブクエリ内の値を使う
select
round((sum(a.num) / count(a.order_id)),0) as average, a.users_locate
from (
select
users.prefecture_id as order_pre,
prefectures.name as users_locate,
user_id,
ord
er_id,
sum(product_price * product_qty) as num,
extract(year_month from orders.order_time) as order_month
from
order_details
inner join
orders
on
orders.id = order_details.order_id
inner join
users
on
users.id = orders.user_id
inner join
prefectures
on
users.prefecture_id = prefectures.id
group by
order_id) as a
group by
a.users_locate
order by
a.order_pre;
サブクエリに対して as で名前を付けたら
名前.valueでサブクエリ内の値を取得できる。
この場合 a.num, a.order_id, a.users_locate
#inner join
select users.id, users.name, posts.body from users
inner join posts on posts.user_id = users.id;
みたいに使える。
outer joinとは違いpostsにusers_idがないものなどposts.user_id = users.idが成立しないものは取ってこない。
#outer join
主になるテーブルを指定してそのテーブルのすべてをとってくる。
select users.id, users.name, posts.body from users
left outer join posts on posts.user_id = users.id;
これでpostsを持たないusersも表示してくれる。
#union
複数のテーブルを結合
結合する場合カラム数、カラム名は同じ出なければいけない。
select users.name, users.email from users
union
select admin_users.name, admin_users.email from admin_users;
これでusersとadmin_usersを一緒にして出力してくれる。
#create view
create view users_name_and_email(name,email) as
select name,email from users;
とすると
select * from users_name_and_email;
これでusersのname,emailが取得できる。
sql文を保存する感じ
#coalesce(null,0)
nullをほかの何かに変える
select price(null,0) from products;
これでpriceがnullの場合0に変換して出力
#create table
create table books (id int not null auto_increment primary key, name varchar(255);
auto_incrementは自動的に値を増やしていってくれる。
#insert into
insert intoの時にauto_incrementとなっているものは値を入れなくても勝手に数字を入れてくれる。
例えばidとか
insert into users (name,age) values ('taro',18);
このsql文でusersにidがauto_incrementであれば勝手に次のidを入れてくれる。