はじめに
SQLの勉強をした際の個人的なメモです！
取得操作
-- 全てを取得
select * from mydb.products;
-- idが1の行を取得
select * from mydb.products where id = 1;
-- nameが'商品0003'の行を取得
select * from mydb.products where name = '商品0003';
-- priceが1000より大きいの行を取得
select * from mydb.products where price > 1000;
-- priceが1000未満の行を取得
select * from mydb.products where price < 1000;
-- priceが100でない行を取得
select * from mydb.products where price <> 100;
-- priceが10でない行を取得
select * from mydb.products where price != 100;
-- idが1か2か3の行を取得
select * from mydb.products where id in(1,2,3);
-- idが1か2か3でない行を取得
select * from mydb.products where id not in(1,2,3);
-- priceがnullではない行を取得
select * from mydb.products where price is not null;
-- priceがnullの行を取得
select * from mydb.products where price is null;
-- priceが1000から1900の行を取得
select * from mydb.products where price between 1000 and 1900;
-- priceが1000から1900の行を取得 andを利用する場合
select * from mydb.products where price >= 1000 and price <= 1900;
-- priceが1000又は2000の行を取得 論理和を使う
select * from mydb.products where price = 1000 or price = 2000;
/*
パターンマッチングによる絞り込み
・ワイルドカード文字で文字列のパターンを指定します。
1. '%' ・・・0文字以上の任意の文字列
2. '_' ・・・任意の1文字
*/
-- '中'から始まる苗字の人を抽出して欲しい
select * from mydb.users where last_name like '中%';
-- '中'を含む苗字の人を抽出して欲しい
select * from mydb.users where last_name like '%中%';
-- '子'を含む名前の人を抽出して欲しい
select * from mydb.users where first_name like '%子%';
-- '子'で終わる3文字の名前の人を抽出して欲しい
select * from mydb.users where first_name like '__子';
/*
取得件数を制限するlimit
基本的に1000件ほどに制限をかけておいた方が良い。
10万件あたりから、負荷がすごいかかる
本番環境で分析するのではなく、分析用のデータベースで分析を行い、負荷の少ない夜間に分析用のDBと本番用のDBを同期するような構成をとる！
*/
-- 商品一覧を10件だけ取得する
select * from mydb.products limit 10;
-- 商品一覧を0から10件だけ取得する
select * from mydb.products limit 0, 10;
-- 商品一覧を10から10件だけ取得する
select * from mydb.products limit 10, 10;
-- 男性の情報を10件取得する
select id, last_name, gender from mydb.users where gender=1 limit 10;
情報セキュリティについて
部内で方針がまとまっていな場合は、下記のサイトを参考に制定する
独立行政法人 情報処理推進機構 Webサイト
主な集約関数
- sum・・・合計値
- avg・・・平均値
- min・・・最小値
- max・・・最大値
- count・・・行数
/*
合計値を求めるSUM関数
*/
select * from mydb.orders;
-- 2017年1月分の行を取得
select
*
from
mydb.orders
where
order_time >= '2017-01-01 00:00:00'
and order_time < '2017-02-01 00:00:00';
-- 2017年1月分の売り上げ合計を取得
select
sum(amount)
from
mydb.orders
where
order_time >= '2017-01-01 00:00:00'
and order_time < '2017-02-01 00:00:00';
/*
平均値を求めるavg関数
*/
-- 全商品の価格の平均を出す。
select avg(price) from mydb.products;
/*
最小値を求めるmin関数
*/
-- 商品価格の最小値を求める
select min(price) from mydb.products;
/*
最大値を求めるmax関数
*/
-- 商品価格の最大値を求める
select max(price) from mydb.products;
/*
集約関数におけるnullの扱い
集約関数では基本的にnullは無視される！
可能であればnullが含まれないようにデータベースを設計する方がいい！
nullを許容する場合は、0とnullの違いやnullと空文字の違いを意識する必要がある。
*/
/*
対象行の行数を数えるcount関数
*/
-- ユーザーの人数を取得する
select count(*) from mydb.users;
-- 女性ユーザーの人数を取得する
select count(*) from mydb.users where gender = 2;
/*
月間のユニークユーザー数を求める
2017年1月にアクセスしたユーザー数を求める
count(distinct expr)の形で、count関数は、重複を排除した上で個数を取得する事ができる。
*/
-- アクセスログの内容を確認する
select * from mydb.access_logs;
-- 2017年1月の情報に絞る（request_monthに月の情報が入っている）
select * from mydb.access_logs where request_month = '2017-01-01';
-- 重複を削除してユーザの人数をカウント
select count(distinct user_id) from mydb.access_logs where request_month = '2017-01-01';
/*
データのグループ化 group by
指定する列名で集約される
*/
-- ユーザーの一覧を確認する
select * from mydb.users;
-- 都道府県ID(prefecture_id)を元に集計する
select
prefecture_id,
count(*)
from
mydb.users
group by
prefecture_id;
/*
期間ごとに集計する
group by で集計
*/
-- アクセスログの全データを表示
select * from mydb.access_logs;
-- 2017年の情報を取得する
select
*
from
mydb.access_logs
where
request_month >= '2017-01-01'
and request_month < '2018-01-01';
-- request_monthごとに集約する
select
request_month,
count(distinct user_id)
from
mydb.access_logs
where
request_month >= '2017-01-01'
and request_month < '2018-01-01'
group by
request_month;
/*
集約結果をさらに絞り込む
having句 → 集約したデータに対して条件を指定する事ができる。
havingはgroup byの後に記述する必要がある。
*/
-- 2017年のアクセスログから、月間ユニークユーザー数が630人以上の月を一覧にする
select
request_month,
count(distinct user_id)
from
mydb.access_logs
where
request_month >= '2017-01-01'
and request_month < '2018-01-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 * from mydb.access_logs;
/*
データの集約②
出力は2017年1月〜2017年6月までにする
*/
select
*
from
mydb.access_logs
where
request_month >= '2017-01-01'
and request_month < '2017-07-01';
/*
データの集約③
出力は2017年1月〜2017年6月までにする
月ごとのリクエスト数を取得
*/
select
request_month,
count(*)
from
mydb.access_logs
where
request_month >= '2017-01-01'
and request_month < '2017-07-01'
group by
request_month;
/*
データの集約④
出力は2017年1月〜2017年6月までにする
月ごとのリクエスト数を取得
アクセス数が1000以上月だけ抜き出す
*/
select
request_month,
count(*)
from
mydb.access_logs
where
request_month >= '2017-01-01'
and request_month < '2017-07-01'
group by
request_month
having
count(*) >= 1000;
並び替え
/*
データの並び替え
order by
並び順の指定方法
- asc ・・・ 昇順（デフォルト）
- desc ・・・ 降順
※ 並び順を指定しなかった場合、どんな並び順になるかは定義されていないので、並び順が重要な場合は、order byを必ず指定すること
*/
-- 商品一覧を価格の高い順に並び替える
select * from mydb.products order by price desc;
-- 商品一覧を価格の低い順に並び替える
select * from mydb.products order by price asc;
-- 商品一覧を価格の低い順に並び替える（省略Ver）
select * from mydb.products order by price;
/*
複数の並び替え条件を指定する
1. 商品一覧を価格の高い順に並べて作成
2. 価格が同じ場合は、登録順で並び替える
※ 日本語の商品名並び替えについて
・商品名で並び替えたいとき、アルファベットの商品名であれば、アルファベット順に並び変わる。
・日本語（全角文字）については、文字コード順になるので、期待通りに並び替えられないため工夫が必要
・代替案は読み仮名を入れる列を新規に作って、その列を利用して、並び替える事が必要
*/
-- 商品一覧を価格の高い順に並び替える
select * from mydb.products order by price desc, id asc;
-- 商品一覧を価格の高い順に並び替える（省略Ver）
select * from mydb.products order by price desc, id;
/*
データの並び替え
ユーザーの一覧を出力し、生年月日が古い順に並べてください。
また、生年月日が同一の場合は、都道府県ID順（昇順）に並べる）
*/
select * from mydb.users order by birthday asc, prefecture_id asc;
算術演算
/*
いろいろな算術演算子
・足し算：+
・引き算：-
・掛け算：*
・割り算：/
・あまり：%
*/
-- 足し算
select 10 + 3;
-- 引き算
select 10 - 3;
-- 掛け算
select 10 * 3;
-- 割り算
select 10 / 3;
-- あまり
select 10 % 3;
/*
nullを含む演算の注意点
nullを含んだ演算の結果は全てnullになるので注意が必要です。
*/
-- 足し算
select 10 + null;
-- 引き算
select 10 - null;
-- 掛け算
select 10 * null;
-- 割り算
select 10 / null;
-- あまり
select 10 % null;
/*
絶対値の取得 abs
数値の符号を考えずに、ゼロからの距離の大きさを表す数値
*/
-- 10の絶対値
select abs(10);
-- -10の絶対値
select abs(-10);
-- 0の絶対値
select abs(0);
/*
四捨五入 round関数
商品価格一覧を作成する時に、税込価格を出力してほしい
ただし、小数第一位で四捨五入して出力
round関数の利用方法
round(対象の数値, マルメの桁数)
*/
select
name as 商品名,
price * 1.08 as '税込価格(四捨五入無し)',
round(price * 1.08, 1) as 税込価格
from
mydb.products;
/*
文字列の演算
文字列連結
a || b ・・・ 文字列aと文字列bを連結
→ただし、MySQL, SQL Server 2012以降ではconcat関数を利用する
concat(文字列1, 文字列2, 文字列3)
*/
-- ユーザ名をさん付けで出力してほしい
select concat(last_name, ' ', first_name, 'さん') from mydb.users;
/*
文字列の演習
メルマガ送信用のリスト作成
・出力項目
- 宛名「名字 + さん」
- メールアドレス
・女性だけに送信したい
*/
-- 名字をさん付けで出力（女性に限定）
select concat(last_name, 'さん'), email from mydb.users where gender = 2;
日付の演算
/*
日付と時刻の演算
主な日付や時刻の演算子
- 現在の日付・・・current_date
- 現在の時刻・・・current_timestamp
- n日後の日付・・・d + interval n day
- n日前の日付・・・d - interval n day
- x時間後の時刻・・・t + interval 'x hour'
- x時間前の時刻・・・t - interval 'x hour'
- extract・・・日付や時刻の特定の部分（年や月）までを取り出す
*/
-- 現在の日付
select current_date();
-- 現在の時刻
select current_timestamp();
-- n日後の日付
select current_date() + interval 3 day;
-- n日前の日付
select current_date() - interval 3 day;
-- x時間後の時間
select current_timestamp() + interval 6 hour;
-- x時間前の時間
select current_timestamp() - interval 6 hour;
/* extractの利用方法 */
-- ordersテーブルから注文日時が2017年01月のレコードを取得する
select * from mydb.orders where extract(year_month from order_time) = 201701;
-- ordersテーブルから注文日時が2017年のレコードを取得する
select * from mydb.orders where extract(year from order_time) = 2017;
-- ordersテーブルから注文日時が1月のレコードを取得する
select * from mydb.orders where extract(month from order_time) = 01;
テーブルの結合
内部結合
/*
テーブルの結合
・テーブルの正規化のメリット
1. データの管理が安易になる
2. データ容量の削減
→特別な意図がない限りは、テーブルは正規化する
特別な意図→システムのパフォーマンス向上
・テーブルの結合とは
テーブル同士をある条件で結合することにより、正規化なしの状態を作り出すこと。
*/
/*
◾主キー(Primary Key, PK)
1つの行を特定できる列のこと
◾外部キー(Foreign Key, FK)
他のテーブルとの関連付けに使う列のこと
外部キーは関連づけされた先のテーブルでは主キーになる
*/
/*
リレーションシップの種類
→ テーブル同士の結びつき・関係性の種類
【1対多】→ 1の方は主キーになっている
【多対多】→ 中間テーブルが必要
【1対1】→ あまり使う事がない（テーブルごとにアクセス件を設定できるので、詳細に権限管理をしたい場合に利用する！）
*/
/*
内部結合でテーブルを結合する
inner joinについて
顧客一覧を取得して、都道府県名も表示する
・出力列
ユーザーID・名字・名前・都道府県名
*/
-- ユーザーの一覧を取得する
select * from mydb.users;
-- 都道府県一覧を確認する
select * from mydb.prefectures;
-- 結合してみる
select
users.id,
users.last_name,
users.first_name,
prefectures.name
from
mydb.users
inner join
mydb.prefectures
on mydb.users.prefecture_id = mydb.prefectures.id;
-- テーブル名を設定する
select
u.id,
u.last_name,
u.first_name,
p.name
from
mydb.users as u
inner join
mydb.prefectures as p
on u.prefecture_id = p.id;
-- inner joinを省略した書き方（join）
select
u.id,
u.last_name,
u.first_name,
p.name
from
mydb.users as u
join
mydb.prefectures as p
on u.prefecture_id = p.id;
/*
内部結合＋絞り込み
上記のデータの中から女性のユーザーのみを抽出したい
*/
-- 女性のみに絞り込む
select
u.id,
u.last_name,
u.first_name,
p.name
from
mydb.users as u
inner join
mydb.prefectures as p
on u.prefecture_id = p.id
where
u.gender = 2;
/*
select文の記述順序と実行順序
・記述順序
1. select・・・取得行（カラム）の指定
2. from・・・対象テーブルの指定
3. 結合処理
4. where・・・絞り込み条件の指定
5. group by・・・グループ化の条件を指定
6. having・・・グループ化した後の絞り込み条件を指定
7. order by・・・並び替え条件を指定
8. limit・・・取得する制限を指定
・実行順序
1. from・・・対象テーブルの指定
2. 結合処理
3. where・・・絞り込み条件の指定
4. group by・・・グループ化の条件を指定
5. having・・・グループ化した後の絞り込み条件を指定
6. select・・・取得行（カラム）の指定
7. order by・・・並び替え条件を指定
8. limit・・・取得する制限を指定
*/
/*
内部結合についての演習
2017年1月の東京都に住むユーザーの注文情報一覧を出力する
・取得する列（カラム）
- 注文id(orders.id)
- 注文日時(orders.order_time)
- ユーザーid(users.id)
- 名字(users.last_name)
- 名前(users.first_name)
*/
-- mydbデータベースを利用する事を明示
use mydb;
-- usersテーブルの内容を確認
select * from users;
-- ordersテーブルの内容を確認
select * from orders;
-- 結合する
select
o.id as order_id,
o.order_time as order_time,
o.amount order_amount,
u.id as user_id,
u.last_name as user_last_name,
u.first_name as user_first_name
from
orders as o
inner join
users as u
on o.user_id = u.id
where
u.prefecture_id = 13
and extract(year_month from o.order_time) = 201701;
-- 指示はなかったが、orderID順に並べた方が親切なので並び替える。
select
o.id as order_id,
o.order_time as order_time,
o.amount order_amount,
u.id as user_id,
u.last_name as user_last_name,
u.first_name as user_first_name
from
orders as o
inner join
users as u
on o.user_id = u.id
where
u.prefecture_id = 13
and extract(year_month from o.order_time) = 201701
order by
order_id ;
外部結合
use mydb;
/*
外部結合
outer join
片方のテーブルの情報が全て出力される、テーブルの結合
外部結合は欠落のあるデータを取り扱う結合
・left outer join・・・左側（from句で最初に書いたテーブル）をマスタとする。
・right outer join・・・右側（from句で後に書いたテーブル）をマスタとする。
*/
-- usersテーブルとordersテーブルを結合（内部結合）
select
u.last_name as last_name,
u.id as user_id,
o.user_id as order_user_id,
o.id as order_id
from
users as u
inner join
orders as o
on u.id = o.user_id
order by
u.id;
-- usersテーブルとordersテーブルを結合（外部結合 左）
-- user id 34(注文を行なっていないユーザー）も表示される
select
u.last_name as last_name,
u.id as user_id,
o.user_id as order_user_id,
o.id as order_id
from
users as u
left outer join
orders as o
on u.id = o.user_id
order by
u.id;
-- left outer joinの省略系
select
u.last_name as last_name,
u.id as user_id,
o.user_id as order_user_id,
o.id as order_id
from
users as u
left join
orders as o
on u.id = o.user_id
order by
u.id;
/*
応用
全ての商品について販売個数一覧を出力
*/
-- 商品情報を全部出力して個数を算出
select
p.id as '商品id',
p.name as '商品名',
sum(d.product_qty) as '個数'
from
products as p
left outer join
order_details as d
on p.id = d.product_id
group by
p.id;
-- inner joinにした場合（販売データがないものは表示されない）
select
p.id as '商品id',
p.name as '商品名',
sum(d.product_qty) as '個数'
from
products as p
inner join
order_details as d
on p.id = d.product_id
group by
p.id;
複数テーブルの結合
/*
3つ以上のテーブルを利用した結合
注文一覧を出してほしい
注文詳細情報と商品情報も一覧の中に入れてほしい
*/
-- ordersテーブルの内容を確認する
select
*
from
orders;
-- order_detailsを内部結合
select
*
from
orders as o
inner join
order_details as od
on o.id = od.order_id;
-- productsを内部結合
select
*
from
orders as o
inner join
order_details as od
on o.id = od.order_id
inner join
products as p
on od.product_id = p.id;
-- 必要な列のみ表示
select
o.id as order_id,
o.user_id as user_id,
o.amount as amount,
o.order_time as order_time,
p.name product_name,
od.product_qty as qty,
p.price as product_price
from
orders as o
inner join
order_details as od
on o.id = od.order_id
inner join
products as p
on od.product_id = p.id;
/*
ユーザーIDだとだれかわからないので、苗字と名前を追加してみる。
*/
-- usersテーブルを結合
select
o.id as order_id,
o.user_id as user_id,
u.last_name as last_name,
u.first_name as first_name,
o.amount as amount,
o.order_time as order_time,
p.name product_name,
od.product_qty as qty,
p.price as product_price
from
orders as o
inner join
order_details as od
on o.id = od.order_id
inner join
products as p
on od.product_id = p.id
inner join
users as u
on o.user_id = u.id;
/*
多対多を含むテーブルの結合
商品ID = 3に紐づく商品カテゴリを全て確認したい
・必要な情報
- 商品ID
- 商品名
- カテゴリ名
*/
-- IDが3の商品を取得
select
p.id as product_id,
p.name as product_name,
c.name as category_name
from
products as p
inner join
products_categories pc
on p.id = pc.product_id
inner join
categories c
on pc.category_id = c.id
where
p.id = 3;
/*
テーブルの足し算
集合演算子 union, union all
ユーザーとアドミンユーザーを足し合わせた一覧が欲しい
・出力する列
- email
- 姓
- 名
- 性別（性別を表す数字でOK）
◾注意点
テーブル1とテーブル2の列数を合わせる必要がある
同じ位置にあるカラムのデータは一致している必要がある。
*/
-- ユーザーテーブルから情報を取得
select
email,
last_name,
first_name,
gender
from
users;
-- アドミンユーザーテーブルから情報を取得
select
email,
last_name,
first_name,
gender
from
admin_users;
-- 2つの結果を結合（重複行を削除する場合）
select
email,
last_name,
first_name,
gender
from
users
union
select
email,
last_name,
first_name,
gender
from
admin_users;
-- 2つの結果を結合（重複行を削除しない場合）
select
email,
last_name,
first_name,
gender
from
users
union all
select
email,
last_name,
first_name,
gender
from
admin_users;
/*
union利用時の句について
・where, group by, havingといった句をつける事ができる
・ただし、order by だけは全体として最後に1つしか記述できない
*/
-- usersテーブルから男性・admin_userテーブルから女性だけを抜き出し結合
-- 2つの結果を結合（重複行を削除しない場合）
select
email,
last_name,
first_name,
gender
from
users
where
gender = 1
union all
select
email,
last_name,
first_name,
gender
from
admin_users
where
gender = 2
order by
gender;
unionの動作
ビューについて
/*
ビューについて
・データそのものを保存するのではなく、データを取り出すSELECT文だけを保存する
・データベースユーザーの利便性を高める道具
・SQLの観点からみると、テーブルと同じもの
・ビューを使うと、必要なデータが複数のテーブルに跨がる場合などの複雑な集約を行いやすくなる
・よく使うselect文はビューにして使い回す事ができる。
*/
/*
ビューとテーブルについて
・テーブル
実際のデータを保存
・ビュー
ビューの中にはselect文が保存されている
ビュー自体はデータを持たない
◾ ビューの制限
order by句が使えない
ビューに対する更新は不可能ではないが制限がある
◾ ビューのメリット
データを保存しないので、記憶装置の容量を節約できる。
よく利用するselect文をビューにしておく事で、使い回しができる。
◾ ビューのデメリット
パフォーマンスの低下を招く場合がある。
*/
/*
ビューの作成
◾ 流れ
1. select文を作成してみる
2. select文からviewを作成
◾ ビューを作成する構文
create view ビュー名 (<ビューの列名1>, <ビューの列名2>, ・・・) as <select文>
都道府県別のユーザー数を取得する。
*/
-- select文を作成してみる
select
p.name as name,
count(*) as count
from
users as u
inner join
prefectures as p
on u.prefecture_id = p.id
group by
u.prefecture_id;
-- Viewにしてみる！（一度実行すると、Viewの部分に1つ追加されているはず！）
create view prefecture_user_counts(name, count)
as
select
p.name as name,
count(*) as count
from
users as u
inner join
prefectures as p
on u.prefecture_id = p.id
group by
u.prefecture_id;
-- Viewにしたものはテーブルのように利用できる
select
name,
count
from
prefecture_user_counts;
/*
ビューの制限事項
・order byが利用できない
・更新系（insert, delete, update）に制約があります。
◾下記の条件を満たす場合のみ更新が可能
1. select句にdistinctが含まれていない
2. from句に含まれるテーブルが1つだけ
3. group by句を使用していない
4. having句を使用していない
※テーブルとビューの更新は、連動して行われるため、集約したビューは更新不可能
*/
/*
ビューの削除
drop view ビュー名;
*/
-- ビューを削除する
drop view prefecture_user_counts;
サブクエリ
/*
サブクエリについて
・ある問い合わせの結果に基づいて、異なる問い合わせを行う仕組み
・複雑な問い合わせができる
・where句の中で使う事が多い
・where句以外でも、select句、from句、having句など様々な場所で利用できる。
サブクエリを使うと・・・
日々の業務改善のデータ分析に役立つデータが、データベースから直接SQLで取り出せる。
例）
・全商品の平均単価より、高い商品を取得
・商品別の平均販売量よりも、多く売れている日を取得
・商品カテゴリごとに、平均単価を取得
・2017年12月に、商品を購入していない、ユーザーを取得
*/
/*
サブクエリ（where句）
サブクエリとnot in 演算子を利用する
2017年12月に商品を購入していない、ユーザを取得したい
・必要なデータ
- ユーザーid
- 名字
- email
*/
-- usersの一覧を取得
select
u.id,
u.last_name,
u.email
from
users as u;
-- 2017年12月に商品を購入しているユーザーを取得
select
user_id
from
orders
where
extract(year_month from order_time) = '201712';
-- not in 演算子を利用して、ユーザーを絞り込む
select
u.id,
u.last_name,
u.email
from
users as u
where
id not in (
select
user_id
from
orders
where
extract(year_month from order_time) = '201712'
);
/*
演習
サブクエリ（where句）
2017年12月に商品を購入した、ユーザー一覧を出す。
必要な情報はuser_id、名字、email
*/
-- in 演算子を利用して、ユーザーを絞り込む
select
u.id,
u.last_name,
u.email
from
users as u
where
id in (
select
user_id
from
orders
where
extract(year_month from order_time) = '201712'
);
/*
応用
全商品の平均単価よりも単価が高い商品の一覧を教えてください。
スカラサブクエリとは、
必ず一行一列だけの戻り値を返す、サブクエリのこと
*/
-- 商品の一覧を出力する
select
*
from
products;
-- 商品の平均単価を求める
select
avg(price)
from
products;
-- 商品を絞り込む
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;
条件分岐
/*
条件分岐 case式
case式を利用すれば、SQLで場合分けを記述する事ができる
ユーザーのアクティビティ度合いによって施策を変えたい。
ユーザーを累計注文回数でランク分けする。
- A：5回以上
- B：2回以上(2or3or4)
- C：1回
※ 注文回数0回のユーザーは出力不要
◾ 必要情報
- ユーザーID
- 累計注文回数
- ユーザーランク(A or B or C)
*/
-- user一覧を取得
select
*
from
users as u;
-- ユーザーごとの注文回数を取得（inner joinを利用しているので、注文回数が0のユーザーは表示されません）
select
u.id,
count(*)
from
users as u
inner join
orders as o
on u.id = o.user_id
group by
u.id;
-- case文でランク分けをする
select
u.id as 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 as 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を置き換える方法
nullだった場合0に置き換える
*/
-- 商品の累計注文個数
select
p.id,
p.name,
sum(od.product_qty) as num
from
products as p
left outer join
order_details as od
on p.id = od.product_id
group by
p.id;
-- caseを使ってnullを0に置き換える
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 as p
left outer join
order_details as od
on p.id = od.product_id
group by
p.id;
/*
MySQL 8.0.2以降の予約後RANKについて
予約語でRANKが利用されているため、RANKという文字列を利用することができません。
*/
/*
演習
商品を販売個数でランク分けする
・全商品を累計販売個数でランク分けして欲しい。
- A：20個以上
- B：10個以上
- C：10個未満
・ランクが高い順に並び替える
・必要な列
- 商品ID
- 商品名
- 販売個数
- ランク
*/
-- 商品の販売個数を取得する
select
p.id,
p.name,
case
when sum(od.product_qty) is null then 0
else sum(od.product_qty)
end as num,
case
when sum(od.product_qty) >= 20 then 'A'
when sum(od.product_qty) >= 10 then 'B'
else 'C'
end as product_rank
from
products as p
left outer join
order_details as od
on p.id = od.product_id
group by
p.id
order by
product_rank asc;
応用問題
use mydb;
/*
応用の演習問題
全期間での平均客単価を求めよ
単価は小終点第一位で四捨五入
*/
-- オーダーの一覧を取得する
select
*
from
orders;
-- 平均を算出する
select
round(avg(amount), 0)
from
orders;
/*
月別の平均客単価を求める
小数第一位で四捨五入
*/
-- 月別でグルーピング
select
*
from
orders;
-- 月別の平均を算出する
select
extract(year_month from order_time) as time,
round(avg(amount), 0)
from
orders
group by
extract(year_month from order_time);
-- 月別の平均を算出する（改版バージョン）
select
date_format(order_time, '%Y年%m月') as '年月',
round(avg(amount), 0) as '平均客単価'
from
orders
group by
date_format(order_time, '%Y年%m月');
/*
全期間での都道府県別平均客単価を求める。
小数点第一位で四捨五入する
◾必要な列
- 都道府県ID
- 都道府県名
- 平均客単価
◾並び順
- 都道府県ID昇順
*/
-- オーダーにユーザーテーブルと都道府県テーブルを結合する
select
*
from
orders as o
inner join
users as u
on o.user_id = u.id
inner join
prefectures as p
on u.prefecture_id = p.id;
-- 都道府県別にデータを絞り込む
SELECT
p.id AS '都道府県ID',
p.name AS '都道府県名',
ROUND(AVG(o.amount), 0) AS '平均客単価'
FROM
orders AS o
INNER JOIN
users AS u ON o.user_id = u.id
INNER JOIN
prefectures AS p ON u.prefecture_id = p.id
GROUP BY p.id
ORDER BY p.id;
/*
都道府県別・月別平均客単価
◾必要な列
- 都道府県ID
- 都道府県名
- 年月
- 平均客単価
◾並び順
- 都道府県ID昇順
*/
-- 月別に集計する
select
p.id as '都道府県ID',
p.name as '都道府県名',
date_format(o.order_time, '%Y年%m月') as '年月',
round(avg(o.amount), 0) as '平均客単価'
from
orders as o
inner join
users as u
on o.user_id = u.id
inner join
prefectures as p
on u.prefecture_id = p.id
group by
p.id,
date_format(o.order_time, '%Y年%m月')
order by
p.id;
データの更新
use mydb;
/*
データの追加
商品データに新規データを1件追加する
※ 注意点
列リストと、values句の値リストは、列数が一致している必要がある。
*/
-- 商品に1つ追加する
insert into products(name, price) values ('新商品A', 1000);
-- データを確認してみる。
select * from products order by id desc;
/*
列リストを省略してデータを追加する
values句の列の定義順にカンマ区切りでデータを設定。
テーブルの全列に対して、値を設定する必要がある。
*/
-- 省略して、データを追加する。
insert products values(1002, '新商品B', 2000);
-- データを確認してみる。
select * from products order by id desc;
/*
行を複数追加する
新商品
三件をデータベースに追加する。
◾情報
- 新商品C 3000円
- 新商品D 4000円
- 新商品E 5000円
*/
-- 商品を複数追加する。
insert into products (name, price)
values
('新商品C', 3000),
('新商品D', 4000),
('新商品E', 5000);
-- データを確認してみる。
select * from products order by id desc;
/*
レコードの更新 update
全商品を10%引きに設定する
*/
-- 現在の価格を確認する
select * from products;
-- SQLアップデートのセーフモードを無効化する
set sql_safe_updates=0;
-- 10%引きの価格を設定する
update products set price = price * 0.9;
-- 現在の価格を確認する
select * from products;
/*
特定の条件に合致する行のデータを更新
商品idが3の商品を「SQL入門」に変更する
*/
-- idが3の商品を確認する
select * from products where id = 3;
-- updateとwhere句を利用する
update products set name = 'SQL入門' where id = 3;
-- idが3の商品を確認する
select * from products where id = 3;
-- 複数の列を更新してみる
update products set name = 'SQL入門2', price = 1000 where id = 3;
-- idが3の商品を確認する
select * from products where id = 3;
/*
更新条件にサブクエリを利用する
累計販売数が10を超えている商品については、価格を5％アップしたい
*/
-- 商品idごとの販売個数を取得する
select
product_id,
sum(product_qty)
from
order_details
group by
product_id;
-- 累計販売個数が10を超えている商品を取得
select
product_id,
sum(product_qty)
from
order_details
group by
product_id
having
sum(product_qty) >= 10;
-- 販売個数が10個を超えている商品の情報を取得
select
*
from
products as p
where
id in(
select
product_id
from
order_details
group by
product_id
having
sum(product_qty) >= 10
);
-- 更新用のSQLを作成
update
products
set
price = price * 1.05
where
id in(
select
product_id
from
order_details
group by
product_id
having
sum(product_qty) >= 10
);
-- 商品の情報を確認
select * from products;
/*
行の削除
商品のカテゴリの紐付きを削除したい
*/
-- product_categoriesを削除する
delete from products_categories;
-- product_categoriesを確認する
select * from products_categories;
/*
条件を指定して、行を削除する
商品IDの1001を削除する
*/
-- 商品ID 1001を確認する
select * from products where id = 1001;
-- 商品ID 1001を削除する
delete from products where id = 1001;
-- 商品ID 1001を確認する
select * from products order by id desc;
/*
削除条件にサブクエリを利用する
1個も売れていない商品を削除する
*/
-- 売れた事がある商品を抽出する
select
product_id
from
order_details
group by
product_id;
-- 売れた事が無い商品を取得する
select
*
from
products
where
id not in
(
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
);
-- 商品を確認する
select * from products;
データ構造の操作
/*
データ構造の操作
データベースのメンテナンスを行える知識
*/
/*
データベースの追加
create database文を利用する
*/
-- 現在のデータベースの一覧を表示する
show databases;
-- 新しいデータベースを作成する
create database book_store;
-- 現在のデータベースの一覧を表示する
show databases;
/*
命名ルール
データベースオブジェクト（データベース名、テーブル名、列名など）の命名ルールについて学習
ルールに従わないと、エラーとなる
データベースオブジェクトに利用可能な文字
・半角のアルファベット
・半角の数字
・アンダースコア
・ただし、名前の最初は半角のアルファベットとする
*/
/*
テーブルの追加
*/
-- 利用するデータベースを指定する
use book_store;
-- テーブルの一覧を表示する
show tables;
-- booksテーブルを作成する
create table books(id int not null auto_increment primary key, title varchar(255) not null);
-- テーブルの一覧を表示する
show tables;
-- テーブルの構造を確認する
show columns from books;
/*
テーブル構造の変更
*/
/*
列の追加
*/
-- 現在のテーブル構造を確認
show columns from books;
-- id列の後ろにprice列を追加する
alter table books add price int after id;
-- 現在のテーブル構造を確認
show columns from books;
/*
列名の変更
*/
-- 現在のテーブル構造を確認
show columns from books;
-- 列名を変更する
alter table books change price unit_price int;
-- 現在のテーブル構造を確認
show columns from books;
/*
列の削除
*/
-- 現在のテーブル構造を確認
show columns from books;
-- 列を削除
alter table books drop unit_price;
-- 現在のテーブル構造を確認
show columns from books;
/*
テーブルの削除
*/
-- テーブルの一覧を表示
show tables;
-- booksテーブルを削除
drop table books;
-- テーブルの一覧を表示
show tables;
/*
データベースの削除
*/
-- データベースの一覧を表示
show databases;
-- データベースを削除する
drop database book_store;
-- データベースの一覧を表示
show databases;
/*
データ構造の操作の注意点
・操作は基本的には取り消せない。
特に、実務において本番環境を操作するときは、サービスをメンテナンスモードにして、バックアップをとってから、alter table等を実行するのが安全。
・想定外に時間がかかり、システムトラブルになる場合もあります。
テスト環境で、alter tableのテストをして、問題点を洗い出してから、本番環境で実行するのが良い
*/