sqlの新しい学び

#はじめに
sqlを勉強して新しく学んだことなどを忘備録として残します。

#where id in(1,2,3)

```sql 
select * from products where id = 1 or id = 2 or id = 3
```

みたいに書くのではなく

```sql
select * from products where id in (1,2,3)
```

とかくとスマート

#is not null

```sql 
select * from users where age = null
```

これだとうまく動かないので

```sql
select * from users where age is null
```

とする

#between

```sql 
select * from foods where price >= 1000 and price <= 2000
```

これは

```sql 
select * from foods where between 1000 and 2000
```

と書ける

#%

曖昧検索するときに

```sql 
select * from users where name like '%田%';
```

みたいにできる

%田　で最後に田がつくもの
田% で最初に田がつくもの

#_

曖昧検索のときに文字の数を指定したいときに_が使える

```sql 
select * from users where name like '__子'
```

これで名前が三文字で最後に子がつくものを取ってこれる。

#interval

intervalで時間を足し引きできる

```sql 
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

年、月を数字列で表示してくれる

```sql 
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と似ている

```sql 
select * from posts where date_format(created_at, '%Y%m') = 201701;
```

みたいにつかう

#サブクエリ

()でくくってsql文を実行してその結果を使える。

#サブクエリ内の値を使う

```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

```sql 
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 

主になるテーブルを指定してそのテーブルのすべてをとってくる。

```sql 
select users.id, users.name, posts.body from users
left outer join posts on posts.user_id = users.id;
```

これでpostsを持たないusersも表示してくれる。

#union

複数のテーブルを結合

結合する場合カラム数、カラム名は同じ出なければいけない。

```sql 
select users.name, users.email from users 
union
select admin_users.name, admin_users.email from admin_users;
```

これでusersとadmin_usersを一緒にして出力してくれる。


#create view

```sql 
create view users_name_and_email(name,email) as 
select name,email from users;
```

とすると

```sql
select * from users_name_and_email;
```

これでusersのname,emailが取得できる。

sql文を保存する感じ


#coalesce(null,0)

nullをほかの何かに変える

```sql 
select price(null,0) from products;
```

これでpriceがnullの場合0に変換して出力





#create table

```sql 
create table books (id int not null auto_increment primary key, name varchar(255);
```

auto_incrementは自動的に値を増やしていってくれる。

#insert into 

insert intoの時にauto_incrementとなっているものは値を入れなくても勝手に数字を入れてくれる。

例えばidとか

```sql 
insert into users (name,age) values ('taro',18);
```

このsql文でusersにidがauto_incrementであれば勝手に次のidを入れてくれる。