SQLについて
データベースとSQL
データベース概要
データベースとは
・検索や蓄積が容易にできる様に整理された情報の集まり
・全てのシステムがデータを取り扱っている
・データを取り扱う手段として、ほぼ全てのシステムが何かしらのデータベースを使用
データベースが必要な理由
１、大量のデータから必要なデータを取り出すため
２、大人数でデータを共有して利用するため
３、データの保護
SQL概要
SQLとは
・データベース、テーブル、行や列を扱うための言語をSQLと呼ぶ
リレーショナルデータベース基本用語
クエリ（問い合わせ）
→データの検索や更新、削除、抽出などの要求をデータベースに送信すること。
データ型
・データベースでは、テーブルを作成するときに、それぞれの列（カラム・フィールド）に指定した形式のデータしか、入力できない様に設定する
このとき指定するデータの形式をデータ型という
数値型
・int型
整数
・tinyint型
とても小さな整数
・int unsigned
数値は符号無しとすることが
文字型
・char型（キャラ）
固定長の文字列２５５文字まで。
文字列を格納するときに指定した長さが
日付・時刻型
・date型
日付
・オラクル社
CREATE
SELECT id, name from products;
データベースからデータを取得する
・ユーザー一覧を出力
select * from users;
select
name as 名前,
price as 価格,
from
products;
select name, price from products where price >= 9800;
比較演算子
select * from products;
select * from products where id = 1;
select * from products where name = '商品0003'
select * from products where price > 1000;
select * from products where price < 1000;
select * from products where price != 100;
select * from products where id in(1,2,3);
select * from products where id not in(1,2,3);
select * from products where price is not null;
select * from products where price is null;
select * from products where price between 1000 and 1900;
like句
'中'から始まるデータの取得
select * from users where last_name like '中%';
'中'を含むデータの取得
select * from users where last_name like '%中%';
'子'で終わるデータの取得
select * from users where last_name like '子%';
limit句
データを１０件取得
select * from products limit 10;
０から１０までのデータ取得
select * from products limit 0, 10;
１１から１０件のデータ取得
select * from products limit 10, 10;
select id, last_name from users where gender = 1 limit 10;
取得したデータの利用方法
・CSV書き出し
select * from products;
Exportボタンをクリック
ファイルを保存（CSV）
データをエクセルに取り込む方法
CSVファイルをインポートする
区切り記号付きを選択
集約関数
・sum
select
sum(amount)
from
orders
where
order_time >= '2019-01-01 00:00:00'
and order_time < '2019-02-01 00:00:00';
・avg
select avg(price) from products;
・min
select min(price) from products;
・max
select max(price) from products;
・count
select count(*) from users;
select count(*) from users where gender = 2;
ユニークユーザー数を求める
select
count(distinct user_id)
from
access_logs
where
request_month = '2019-01-01';
・group by句
select
prefecture_id,
count(*)
from
users
group by
prefecture_id;
・期間ごとに集計する
select
*
from
access_logs
where
request_month >= '2019-01-01'
and request_month < '2019-02-01';
group by
request_month;
・having句
記述順序
select
↓
from
↓
where
↓
group by
↓
having
select
*
from
access_logs
where
request_month >= '2019-01-01'
and request_month < '2019-02-01';
group by
request_month
having
count(distinct user_id) >= 630;
select分の記述順序と実行順序
記述順序
1、 select 取得行（カラム）の指定
2、 from 対象テーブルの指定
3、 where 絞り込み条件の指定
4、 group by グループ化の条件を指定
5、 having グループ化した後の絞り込み条件を指定
6、 order by 並び替え条件を指定
7、 limit 取得する行数の制限
実行順序
1、 from 対象テーブルの指定
2、 where 絞り込み条件の指定
3、 group by グループ化の条件を指定
4、 having グループ化した後の絞り込み条件を指定
5、select 取得行（カラム）の指定
6、 order by 並び替え条件を指定
7、 limit 取得する行数の制限
select
request_month,
count(*)
from
access_logs
where
access_logs where request_month >= '20170-01-01'
and request_month < '2017-02-01'
group by request_month
having
count(*) >= 1000;
データの並び替え
降順
select * from products order by price desc;
昇順
select * from products order by price asc;
※order byで並び順を指定しないとバラバラになる。
order by句
select * from products order by price desc, id asc;
生年月日が古い順に並べる。
select * from users order by birthday asc, prefecture_id asc;
関数と演算子
・足し算
select 10 + 3;
・引き算
select 10 - 3;
・掛け算
select 10 * 3;
・割り算
select 10 / 3;
・余り
select 10 % 3;
select 10 + null;
select 10 - null;
select 10 * null;
select 10 / null;
select 10 % null;
※結果は全てnullになる。
・絶対値の取得
ゼロからの距離の大きさを表す数値
・絶対値の取得
select abs(10);
10
select abs(-10);
10
select abs(0);
0
・四捨五入
round関数
round（対象の数値、丸めの桁数）
select id, name, price * 1.08 from products;
select id, name, round(price * 1.08, 0) from products;
select round(10.555, 0);
select round(10.555, 1);
select round(10.555, 2);
・文字列の演算
select concat(last_name, '', first_name, 'さん') from users;
・メルマガ送信用のリスト作成
select concat(last_name, 'さん'), email from users where gender = 2;
・日付と時刻の演算
現在の日付
select current_date();
現在の時刻
select current_timestamp();
n日後の日付
select current_date() + 3;
n日前の日付
select current_date() - 3;
x時間後の時刻
select current_time() + interval 6 hour;
x時間前の時刻
select current_time() - interval 6 hour;
select * from orders where extract(year_month from order_time) = 201701;
テーブルの結合
内部結合
・顧客一覧を取得
・都道府県名の表示
・必要な列はユーザーID、名字、名前、都道府県名
select
u.id,
u.last_name,
u.first_name,
p.name
from
users as u
inner join
prefectures as p
on u.prefecture_id = p.id;
内部結合＋絞り込み
・顧客一覧を取得
・都道府県名の表示
・必要な列はユーザーID、名字、名前、都道府県名
追加
・女性のみのデータ
select
u.id,
u.last_name,
u.first_name,
p.name
from
users u
inner join
prefectures p
on u.prefecture_id = p.id
where u.gender = 2;
演習
⬛️２０１７年１月の東京都に住むユーザーの注文一覧を出力
・注文ID
・注文日時
・注文金額合計
・ユーザーID
・名字
・名前
select
o.id order_id,
o.order_time order_time,
o.amount amount,
u.id user_id,
u.last_name last_name,
u.first_name first_name
from
orders o
inner join
users u
on o.user_id = u.id
where
u.prefecture_id = 13
and o.order_time >= '2017-01-01 00:00:00'
and o.order_time < '2017-02-01 00:00:00'
order by order_id;
外部結合
select
u.last_name last_name,
u.id user_id,
o.user_id order_user_id,
o.id order_id
from
users u
left outer join
orders o
on u.id = o.user_id
order by u.id;
応用
⬛️全ての商品について、販売個数一覧を出力
select
p.id,
p.name,
sum(od.product_qty) num
from
products
left outer join
order_details od
on p.id = od.product_id
group by p.id;
３つ以上のテーブルを使った結合
・注文一覧を出力
・注文詳細情報と商品情報も一覧の中に入れる
select
o.id order_di,
o.user_id user_id,
o.amount amount,
o.order_time order_time,
p.name product_name,
od.product_qty qty,
p.price product_price
from
orders o
inner join
order_details od
on o.id = od.order_id
inner join
products p
on od.product_id = p.id;
追加
・user_idだとわからないため名字と名前で表示
・上記を一覧に追加
select
o.id order_di,
o.user_id user_id,
u.last_name last_name,
u.first_name first_name,
o.amount amount,
o.order_time order_time,
p.name product_name,
od.product_qty qty,
p.price product_price
from
orders o
inner join
order_details od
on o.id = od.order_id
inner join
products p
on od.product_id = p.id
inner join
users u
on o.user_id = u.id;
多対多の関係を含む結合
・商品ID＝３に紐づく商品カテゴリ名を全て表示
・商品ID
・商品名
・カテゴリ名
select
p.id product_id,
p.name product_name,
c.name category_name
from
products p
inner join
products_categories ps
on p.id = pc.product_id
inner join
categories c
on pc.category_id = c.id
where
p.id = 3;
テーブルの足し算
・ユーザーとアドミンユーザーを足し合わせた一覧出力
・姓
・名
・性別
select
email,
last_name,
first_name,
gender
from
users
union
select
email,
last_name,
first_name,
gender
from
admin_users;
※union allで重複行が削除されない。
例題
・usersテーブルから男性のみ出力
・admin_usersテーブルからは女性のみ
・unionした結果を性別順に並び替える
select
email,
last_name,
first_name,
gender
from
users
where
gender = 1
union
select
email,
last_name,
first_name,
gender
from
admin_users
where
gender = 2
order by gender;
※where、group by、havingといった句をつけることができる。
※order byだけは全体として最後に１つしか記入できない。
サブクエリとは
・ある問い合わせの結果に基づいて異なる問い合わせを行う仕組み
・複雑な問い合わせができる
・where句の中で使うことが多い
・where句以外にもselect句、from句、having句など様々場所で利用できる
サブクエリ（where句）
⬛️２０１７年１２月に商品を購入していないユーザーにメルマガを出す
・ユーザーID
・名字
【構文】
select
列名, ...
from
テーブル名
where
列名 演算子（select 列名 from テーブル名 ...）;
select
id,
last_name,
from
users
where id not in(
select
user_id
from
orders
where
order_time >= '2017-012-01 00:00:00'
and order_time < '2018-01-01 00:00:00');
演習
⬛️２０１７年１２月に商品を購入したユーザーにメルマガを出す
・ユーザーID
・名字
select
id,
last_name,
from
users
where id in (
select
user_id
from
orders
where
order_time >= '2017-12-01 00:00:00'
and order_time < '2018-01-01 00:00:00'
);
応用
・全商品の平均単価よりも単価が高い商品の一覧を出力
select
*
from
products
where
price >
(
select
avg(price)
from
products
);
追加
・商品単価の高い順に並び替える
・商品単価が同じ時は登録順（id照準）に並び替える
select
*
from
products
where
price >
(
select
avg(price)
from
products
)
order by
price desc, id asc;
※ascは省略できる。
条件分岐 case式
・case式を使うと、SQLで場合分けを記述することができる
・場合分けのことを条件分岐という
条件によって値を変更
⬛️ユーザーのアクティビティの度合いによって施策を変える
・ユーザーを累計注文回数でランク分け
A：５回以上
B：２回以上（２ or ３ or ４）
C：１回
※注文回数０回のユーザーは出力必要
・ユーザーID
・累計注文回数
・ユーザーランク（A or B or C）
【構文】
case
when 条件式１ then 値１ → 条件式１がtrue（真）ならば値１
[when 条件式２ then 値２ ・・・] → 条件式２がtrue（真）ならば値２
[else 値３] → どれにも当てはまらなければ値３
end
※[ ]の部分は省略可能。最後のendは省略不可。必須。
select
u.id user_id,
count(*) as num,
case
when count(*) >= 5 then 'A'
when count(*) >= 2 then 'B'
else 'C'
end as user_rank
from
users as u
inner join
orders as o
on u.id = o.user_id
group by u.id;
追加
・ユーザーのランクが高い順に並び替える
select
u.id user_id,
count(*) as num,
case
when count(*) >= 5 then 'A'
when count(*) >= 2 then 'B'
else 'C'
end as user_rank
from
users as u
inner join
orders as o
on u.id = o.user_id
group by u.id
order by user_rank asc;
取得値nullを置き換える
select
p.id,
p.name,
case
when sum(od.product_qty)is null then 0
else sum(od.product_qty)
end as num
from
products
left outer join
order_details ob
on p.id = od.product_di
group by p.id;
演習
・全商品を累計販売個数でランク分けする
・ランクが高い順に並び替え￥いてる
・商品ID
・商品名
・販売個数
・ランク（A or B or C）
select
p.id,
p.name(product_qty),
case
when sum(product_qty) >= 20 then 'A'
when sum(product_qty) >= 10 then 'B'
when
end
sum(product_qty)
from
products
left outer join
order_details od
on p.id = od.product.id
group by p.id
order by rank;
応用問題
◾️全期間での平均客単価
・単価は小数第一位で四捨五入
select
round(avg(amount), 0)
from
orders;
◾️月別での平均客単価
・単価は小数第一位で四捨五入
select
date_format(order_time, '%Y%m') as order_year_month,
round(avg(amount), 0) as average_customer_spend
from
orders
group by
date_format(order_time, '%Y%m')
order by order_year_month
;
◾️都道府県別平均客単価
・単価は小数第一位で四捨五入
select
pref.id as prefecture_id,
pref.name as prefecture_name,
round(avg(o.amount), 0) as average customer spend
from
orders o
inner join users u
on o.user_id = u.id
inner join prefectures pref
on u.prefecture_id = pref.id
group by
pref.id
order by
pref.id
;
◾️都道府県別・月別平均客単価
・単価は小数第一位で四捨五入
select
pref.id as prefecture_id,
pref.name as prefecture_name,
date_format(o.order_time, '%Y%m') as order_year_month
round(avg(o.amount, 0) as average_customer_spend
from
orders o
inner join users u
on o.user_id = u.id
inner join prefectures pref
on u.prefecture_id = pref.id
group by
prefecture_id, order_year_month
order by
prefecture_id, order_year_month
;
データの更新
・新商品を１件追加
・商品名：新製品A
・価格：1,000円
構文
insert into
テーブル名（列１、列２、列３・・・）
values
（値１、値２、値３・・・）
insert into products(name, price) values('新商品A', 1000);
select * from products;
列を省略してinsert
例）insert products values(1002, '新商品A', 2000);
・values句に列の定義順、カンマ区切りで値を設定
・ただし、列を省略するには条件がある
・テーブルの全列に対して、値を指定する必要がある
行を複数行追加
・新商品３件をデータベースに追加
・商品名：新商品C
価格：３,０００円
・商品名：新商品D
価格：４,０００円
・商品名：新商品E
価格：５,０００円
構文
insert into
テーブル名（列１、列２、列３・・・）
values
（値１、値２、値３・・・）,
（値１、値２、値３・・・）,
（値１、値２、値３・・・）;
insert into products(name, price)
values
('新商品C', 3000),
('新商品D', 4000),
('新商品E', 5000);
select * from products;
データの更新UPDATE
・全商品を１０％引き
select * from products;
set sql_safe_updates = 0;
update products set price = price * 0.9;
select * from products;
特定の条件に合致する行のデータを更新する
・商品ID３の商品名を「SQL入門」と書き換える
構文
update テーブル名 set 列１ = 値１, [列２ = 値２...]
[where 条件式];
select * from products where id = 3
update products set name = 'SQL入門' where id = 3
select * from products where id = 3;
update products set name = 'SQL入門1', price = 1000 where id = 3;
select * from products where id = 3;
更新条件にサブクエリを使う
累計販売数が１０を超えている商品については、価値を５％UPする。
select
product_id,
sum(product_qty)
from
order_details
group by
product_id
having
sum(product_qty) >= 10
;
select * from products
update
products
set
price = price * 1.05
where
id in
(
select
product_id
from
order_details
group by
product_id
having
sum(product_qly) >= 10
);
行の削除
・商品に割り当てられている商品カテゴリを使うのを止めるため、商品とカテゴリの紐付きを削除する
select * from products_categories;
delete from products_categories;
※deleteで削除したデータは基本的には元に戻せない
大量のデータをdeleteするときに予想外に時間がかかる場合がある
条件を指定して行の削除
select * from products where id = 1001;
delete from products where id = 1001;
select * from products where id = 1001
削除条件にサブクエリを使う
・１個も売れていない商品は、売るのを止めるため削除する
select
product_id
from
order_details
group by
product_id;
delete
from
products
where
id not in(
select
product_id
from
order_details
group by
product_id
) ;
データベース構造の操作
データベースの追加
構文
create detabase データベース名;
show databases;
create database book_store;
show databases;
テーブルの追加
// データベースの選択
use book_store;
show tables;
create table books(id int not null auto_increment primary key, title varchar(255) not null);
・列 ID
int：整数型
not null：nullを許可しない
auto_increment：idを自動的にふる
primary key；主キーの設定
・列名 title
varchar(255)：最大２５５文字の可変長文字列型
not null：nullを許可しない
テーブル構造の変更
・列の追加
use book_store;
show columns from books;
alter table books add price int affter id;
show columns from books;
・列名の変更
構文：alter table テーブル名 change 旧列名 新列名 データ型;
alter table books change price unit_price int;
・列名の削除
構文：alter table テーブル名 drop 列名;
alter table books drop unit_price;
テーブルの削除
構文：drop table テーブル名;
show tables;
drop table books;
show tables;
データベースの削除
構文：drop database データベース名;
show databases;
drop database book_store;
show databases;
