「SQL・データ分析入門」 サポートサイト

このページは、世界最大級のオンライン学習プラットフォームUdemy（ユーデミー）とProglus（プログラス）にて公開している、「はじめてのSQL・データ分析入門」、及び、Proglus（プログラス）にて公開中の「はじめてのSQL ・データ分析入門」 受講生の方向けサポートサイトです。

よくあるご質問、及び、コースで実行するSQL一覧をテキストで掲載しています（一部除く）。
エラーが発生した時に見比べたり、動作するSQLをコピー＆ペースとして実行したいときなど、個人の学習用にご利用ください。

MySQL 5.7(.21)、MySQL8.0にて動作確認をしております。その他の環境では動作が異なる場合があります。

よくあるご質問

Q. MySQL Workbenchで使えるショートカットを教えてください。
A. 下記の記事を参照してください。
– MySQL Workbenchの便利なショートカット

Q. MySQL 5.7/8.0 for macOSのアンインストール方法を教えてください
A. サポート記事「macOSにHomebrewでインストールしたMySQL5.7, MySQL8.0をアンインストールする方法」を参照してください。

Q. MySQL 5.7 for Windowsのアンインストール方法を教えてください
A. サポート記事「MySQL 5.7 for Windowsのアンインストール方法」を参照してください。

サンプルコード（SQL）

SQL文の基礎

/* データベースの選択 */ 
use mydb;
/* ユーザー一覧の取得 */ 
select * from users;

演習回答：データを取り出す

select * from products;

コメント

-- select * from users;
/* select * from users;
select * from products */

よくあるSQLのエラー

select name, price from products;
-- DB選択していない
use mydb;
select * from users;

-- スペルミス
select * form users;

-- 全角文字
ｓｅｌｅｃｔ * from users;

列を指定してデータを取得する select

select id, last_name from users;

補足: SQLの書き方

select id,                    last_name from                          users;

select
    id,
    last_name
from
    users;
    
SELECT ID, LAST_NAME FROM USERS;

SELECT id, last_name FROM users;

SELect ID, LAst_NAme FroM USers;


select  id,  last_name from  users LIMIT 0, 1000;

演習回答：列を指定してデータを取得する

select name, price from products;

select name, price from products;
select name as 名前, price as 価格 from products;
select name 名前, price 価格 from products;

列に別名をつける

select name, price from products;
select name as 名前, price as 価格 from products;
select name 名前, price 価格 from products;

列の値に対して演算を行う

select
    name as 名前,
    price as 価格,
    price * 1.08 as 税込価格
from
    products;

条件を指定してデータを取得する where

select name, price from products where price >= 9800;

比較演算子の種類

-- 1) products テーブルから全件取得
select * from products;
-- 2) idが1の行を取得
select * from products where id = 1;
-- 3) 名前が「商品0003」の行を取得
select * from products where name = '商品0003';
-- 4) priceが1000より大きい行を取得
select * from products where price > 1000;
-- 5) price が1000より小さい行を取得
select * from products where price < 1000;
-- 6) priceが100でない行を取得
select * from products where price <> 100;
-- 7) 次のようにもかけます。
select * from products where price != 100;
-- 8) idが1か2か3の行を取得
select * from products where id in(1, 2, 3);
-- 9)idが1か2か3ではない行を取得
select * from products where id not in(1, 2, 3);
-- 10)priceがnullではない行を取得
select * from products where price is not null;
-- 11)priceがnullの行を取得
select * from products where price is null;
-- 12)priceが1000から1900の行を取得
select * from products where price between 1000 and 1900;
-- 13)これは次のようandを使っても書ける。andは、論理積。条件AもBも成り立つ時にtrue（真）。
select * from products where price >= 1000 and price <= 1900;
-- 14)or （論理和）も使える。条件Aか条件Bが一つ以上成り立つ場合にtrue
-- 価格が1000円又は2000円の行を取得
select * from products where price = 1000 or price = 2000;

パターンマッチングによる絞込 like

select * from users where last_name like '中%';
select * from users where last_name like '%中%';
select * from users where first_name like '%子';
select * from users where first_name like '__子';

取得件数を制限する limit

select * from products limit 10;
select * from products limit 0, 10;
select * from products limit 10, 10;

演習回答：取得件数を制限する limit句

select id, last_name, gender from users where gender = 1 limit 10;

合計値を求める sum集約関数

-- select * from orders;

select
    sum(amount)
from
    orders
where
    order_time >= '2017-01-01 00:00:00'
    and order_time < '2017-02-01 00:00:00';

平均値を求める avg集約関数

-- select * from products;

select avg(price) from products;

最小値を求める min集約関数

select min(price) from products;

最大値を求める max集約関数

select max(price) from products;

対象行の行数を数える count集約関数

-- select * from users; 

-- select count(*) from users;

select count(*) from users where gender = 2;

応用：月間ユニークユーザー数を求める

-- select * from access_logs;

select 
    count(distinct user_id)
from
    access_logs
where
    request_month = '2017-01-01';

データをグループ化 group by

select
    prefecture_id,
    count(*)
from
    users
group by
    prefecture_id;

### 応用 : 期間ごとに集計する 

select
    request_month,
    count(distinct user_id)
from 
    access_logs
where
    request_month >= '2017-01-01'
    and request_month < '2018-01-01'
group by
    request_month;

集計をさらに絞り込む having

select
    request_month,
    count(distinct user_id)
from 
    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 * from access_logs; 

select
    request_month,
    count(*)
from
    access_logs
where
    request_month >= '2017-01-01'
    and request_month < '2017-07-01'
group by 
    request_month
having
    count(*) >= 1000;

並び替え order by

-- select * from products order by price desc;

-- select * from products order by price asc;

select * from products order by price;

複数の並び替え条件を指定する

select * from products order by price desc, id asc;

select * from products order by price desc, id;

演習回答：データの並び替え

-- 演習
-- ユーザー一覧を、出力。
-- 生年月日が古いに並べる。
-- 生年月日が一緒の場合は、都道府県ID順(昇順)に並べる。

-- select * from users order by birthday asc, prefecture_id asc;

select * from users order by birthday, prefecture_id;

いろいろな算術演算子

/* 足し算 */
select 10 + 3;

/* 引き算 */
select 10 - 3;

/* 掛け算 */
select 10 * 3;

/* 割り算 */
select 10 / 3;

/* 余り */
select 10 % 3;

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

/* 商品価格一覧を作成する時に、税込み価格を出力してほしい。
ただし、小数第1位で四捨五入して出力 */
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;

演習回答: 文字列の演算 メルマガ送信用のリスト作成

/* 演習 : 
メルマガ送信用のリスト作成
出力項目は、
宛名「名字 + さん」
メールアドレス 
例）中村さん, nakamura@example.com
女性だけに送信したい  */

select concat(last_name, 'さん'), email from users where gender = 2;

日付と時刻の演算

/* 現在の日付 ・・・ current_date */
select current_date();
/* 現在の時刻 ・・・ current_timestamp */
select current_timestamp();
/* n日後の日付　・・・ interval */
select current_date() +  interval 3 day;
/* n日前の日付 ・・・ interval  */
select current_date() -  interval 3 day;
/* x時間後の時刻   ・・・ interval 'x hour' */
select current_time() + interval 6 hour;
/* x時間前の時刻 ・・・ - interval 'x hour' */
select current_time() - interval 6 hour;

### 日付と時刻の演算
/* extract ・・・ 日付や時刻の特定の部分（年や月）までを取り出す。 */
/* ordersテーブルから注文日時(order_timeカラム)が、2017年01月のレコードを取得する。*/
select * from orders where extract(year_month from order_time) = 201701;
/* ordersテーブルから注文日時(order_timeカラム)が、2017年のレコードを取得する。*/
select * from orders where extract(year from order_time) = 2017;
/* ordersテーブルから注文日時(order_timeカラム)が、1 月のレコードを取得する。*/
select * from orders where extract(month from order_time) = 1;

内部結合でテーブルを結合する　inner join

/* 顧客一覧を取得して欲しい。
都道府県IDで出力されてもよくわからないので、都道府県名も表示してほしい。
必要な列は、ユーザーID, 名字, 名前, 都道府県名。*/
/*
select
    users.id,
    users.last_name,
    users.first_name,
    prefectures.name
from
    users
inner join
    prefectures
on users.prefecture_id = prefectures.id;*/

select
    u.id,
    u.last_name,
    u.first_name,
    p.name
from
    users u
inner join
-- join
    prefectures p
on u.prefecture_id = p.id;

内部結合 + 絞り込み

/* 顧客一覧を取得して欲しい。
都道府県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;

演習回答：内部結合

/* 2017年1月の、東京都に住むユーザーの、注文情報一覧を出して。
取得する列（カラム）は、
注文id (orders.id)
注文日時(orders.order_time)
注文金額合計(orders.amount)
ユーザーid (user.id)
名字(users.last_name)
名前(users.first_name) */

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;

外部結合 left outer join

/* usersテーブルとordersテーブルを結合 */
select
    u.last_name last_name,
    u.id user_id,
    o.user_id order_user_id,
    o.id order_id
from
    users u
-- inner join
-- left outer join
left 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 p
left outer join 
-- inner join
    order_details od
on p.id = od.product_id
group by p.id
;

3つ以上のテーブルを使った結合

/* 注文一覧を出して欲しい。
注文詳細情報と商品情報も一覧の中に入れて欲しい。*/
select
    o.id order_id,
    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;

多対多の関係を含む結合

select 
    p.id product_id,
    p.name product_name,
    c.name  category_name
from products p
inner join products_categories pc
    on p.id = pc.product_id
inner join categories c
    on c.id = pc.category_id
where p.id = 3;

テーブルの足し算 union, union all

/* ユーザーと、アドミンユーザーを足し合わせた一覧が欲しい
出力して欲しい列は、
email
姓
名
性別(性別を表す数字でOK) */
/*
select
    email,
    last_name,
    first_name,
    gender
from 
    users
union all
select 
    email,
    last_name,
    first_name,
    gender
from
    admin_users;*/
    
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;

ビューの作成

/* 都道府県別のユーザー数を教えて。*/
/*create view prefecture_user_counts(name, count)
as
select
    p.name name,
    count(*) count
from 
    users u
inner join
    prefectures p
    on u.prefecture_id = p.id
group by
    u.prefecture_id;*/
    
select
    name,
    count
from
    prefecture_user_counts;

ビューの削除

drop view prefecture_user_counts;

select name, count
from prefecture_user_counts;

サブクエリ(where句)

select
    id,
    last_name,
    email
from
    users
where id not 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
    id,
    last_name,
    email
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
        )
order by
    price desc, id;

条件によって値を変更する

 /*ユーザーのアクティビティの度合いによって、施策を変えたい、
ユーザーを累計注文回数で、ランク分けして。
A :  5回以上  
B :  2回以上 （2 or 3 or 4）
C :  1回
※注文回数0回のユーザーは出力不要。

必要情報:
ユーザーID
累計注文回数
ユーザーランク(A or B or C) */
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 
;

取得値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 p
left outer join
    order_details od
on p.id = od.product_id
group by p.id;

演習回答：商品を販売個数でランク分け

/*全商品を累計販売個数で、ランク分けして欲しい。
A : 20個以上  
B : 10個以上
C : 10個未満
ランクが高い順に、並び替えて。
必要な列は、商品ID、商品名、販売個数、ランク（A or B or C）*/

select
    p.id,
    p.name,
    sum(product_qty),
    case
    when sum(product_qty) >= 20 then 'A'
        when sum(product_qty) >= 10 then 'B'
        else 'C'
    end as product_rank
from
    products p
left outer join
    order_details od
    on p.id = od.product_id
group by p.id
order by product_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
;

都道府県別平均客単価

/*都道府県別平均客単価を教えてほしい。

必要な列
都道府県ID
都道府県名
平均客単価（小数第１位で四捨五入）

並び順
都道府県ID昇順

全期間での平均客単価を教えて*/
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
;

都道府県別・月別平均客単価

/* 都道府県別・月別平均客単価を教えて

必要な列
都道府県ID
都道府県名
年月
平均客単価（小数第１位で四捨五入）

並び順
都道府県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
;
    

データの更新

レコードを1件追加する insert

insert into products (name, price) values ('新商品A', 1000);
select * from products;

列リストを省略して1件行を追加する

select * from products;
insert products values(1002, '新商品B', 2000);
select * from products;

行を複数追加する

/*新商品を3件データベースに追加してほしい。 */
商品名：新商品C
価格：3,000

商品名：新商品D
価格：4,000

商品名：新商品E
価格：5,000*/
insert into 
    products (name, price)
values
    ('新商品C', 3000),
    ('新商品D', 4000),
    ('新商品E', 5000);

select * from products;

行の更新 update

/* 全商品を10%引きに設定して。*/

select * from products;
set sql_safe_updates = 0;
update products set price = price * 0.9;

select * from products;

特定の条件に合致する行を更新 update

/* 商品idが3の商品名を、「SQL入門」に変えておいて。*/
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;

更新条件にサブクエリを使う

/*累計販売数が10個以上の商品については、
価格を5%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_qty) >= 10
    );
    
select * from products;

行の削除

/*今、商品に割り振られている商品カテゴリを使うのをやめるので、
商品とカテゴリのひも付きを削除してほしい*/

select * from products_categories;

delete from products_categories;

select * from products_categories;

条件を指定して、行の削除

/* 商品ID 1001は、
間違いで登録してしまったものだから、削除してほしい。*/
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
    );
    
select * from products;

データベースの作成

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);

show tables;

show columns from books;

テーブル構造の変更

show columns from books;

alter table books add price int after id;

show columns from books;

alter table books change price unit_price int;

show columns from books;

alter table books drop unit_price;

show columns from books;

テーブルの削除

show tables;

drop table books;

show tables;

データベースの削除

show databases;

drop database book_store;

show databases;

注意事項

受講生の皆さんの個人的な学習にのみご利用いただけます。
営利・非営利問わず二次利用する行為を禁止します。