#はじめに
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を入れてくれる。