yusuke_mrmt’s diary

エンジニアブログ

SQLについて

データベースとSQL

データベース概要

データベースとは

・検索や蓄積が容易にできる様に整理された情報の集まり

・全てのシステムがデータを取り扱っている

・データを取り扱う手段として、ほぼ全てのシステムが何かしらのデータベースを使用

 

データベースが必要な理由

1、大量のデータから必要なデータを取り出すため

2、大人数でデータを共有して利用するため

3、データの保護

 

SQL概要

SQLとは

・データベース、テーブル、行や列を扱うための言語をSQLと呼ぶ

 

 

リレーショナルデータベース基本用語

クエリ(問い合わせ)

→データの検索や更新、削除、抽出などの要求をデータベースに送信すること。

 

データ型

・データベースでは、テーブルを作成するときに、それぞれの列(カラム・フィールド)に指定した形式のデータしか、入力できない様に設定する

このとき指定するデータの形式をデータ型という

 

数値型

・int型

 整数

・tinyint型

 とても小さな整数

・int unsigned

 数値は符号無しとすることが

 

文字型

・char型(キャラ)

 固定長の文字列255文字まで。

 文字列を格納するときに指定した長さが

 

日付・時刻型

・date型

 日付

 

MySQL

オープンソースRDBMS

・オラクル社

 

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句

データを10件取得

select * from products limit 10;

 

0から10までのデータ取得

select * from products limit 0, 10;

 

11から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ファイルをインポートする

区切り記号付きを選択

UnicodeUTF-8

 

集約関数

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

 

演習

⬛️2017年1月の東京都に住むユーザーの注文一覧を出力

・注文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;

 

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

・注文一覧を出力

 ・注文詳細情報と商品情報も一覧の中に入れる

 

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=3に紐づく商品カテゴリ名を全て表示

・商品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;

 

テーブルの足し算

・ユーザーとアドミンユーザーを足し合わせた一覧出力

・email

・姓

・名

・性別

 

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だけは全体として最後に1つしか記入できない。

 

サブクエリとは

・ある問い合わせの結果に基づいて異なる問い合わせを行う仕組み

・複雑な問い合わせができる

・where句の中で使うことが多い

・where句以外にもselect句、from句、having句など様々場所で利用できる

 

サブクエリ(where句)

⬛️2017年12月に商品を購入していないユーザーにメルマガを出す

・ユーザーID

・名字

・email

 

【構文】

select

  列名, ...

from

  テーブル名

where

  列名 演算子(select 列名 from テーブル名 ...);

 

select 

  id,

  last_name,

  email

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

 

演習 

⬛️2017年12月に商品を購入したユーザーにメルマガを出す

・ユーザーID

・名字

・email

 

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

    );

追加

・商品単価の高い順に並び替える

・商品単価が同じ時は登録順(id照準)に並び替える

 

select

  *

from

  products

where 

  price >

    (

      select

        avg(price)

      from

        products

    )

order by

  price desc, id asc;

 ※ascは省略できる。

 

条件分岐 case式

・case式を使うと、SQLで場合分けを記述することができる

・場合分けのことを条件分岐という

 

条件によって値を変更

⬛️ユーザーのアクティビティの度合いによって施策を変える

・ユーザーを累計注文回数でランク分け

 A:5回以上

 B:2回以上(2 or 3 or 4)

 C:1回

 ※注文回数0回のユーザーは出力必要

・ユーザーID

・累計注文回数

・ユーザーランク(A or B or C)

 

【構文】

case

  when 条件式1 then 値1 → 条件式1がtrue(真)ならば値1

  [when 条件式2 then 値2 ・・・] → 条件式2がtrue(真)ならば値2

  [else 値3] → どれにも当てはまらなければ値3

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

;

 

データの更新

・新商品を1件追加

・商品名:新製品A

・価格:1,000円

 

構文

insert into

  テーブル名(列1、列2、列3・・・)

values

  (値1、値2、値3・・・)

 

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

select * from products;

 

列を省略してinsert

例)insert products values(1002, '新商品A', 2000);

・values句に列の定義順、カンマ区切りで値を設定

・ただし、列を省略するには条件がある

・テーブルの全列に対して、値を指定する必要がある

 

行を複数行追加

・新商品3件をデータベースに追加

・商品名:新商品C

 価格:3,000円

・商品名:新商品D

 価格:4,000円

・商品名:新商品E

 価格:5,000円

 

構文

insert into

  テーブル名(列1、列2、列3・・・)

values

  (値1、値2、値3・・・),

  (値1、値2、値3・・・),

  (値1、値2、値3・・・);

 

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;

 

特定の条件に合致する行のデータを更新する

・商品ID3の商品名を「SQL入門」と書き換える

 

構文

update テーブル名 set 列1 = 値1, [列2 = 値2...]

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

 

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

累計販売数が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_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

 

削除条件にサブクエリを使う

・1個も売れていない商品は、売るのを止めるため削除する

 

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):最大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;