sqlの新しい学び

はじめに

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を入れてくれる。