データベースについて学んだことの走り書き

データベース

データベース（DB）とは

• 検索や蓄積が容易にできるように整理された情報の集まり。
• すべてのシステムがデータを取り扱っていて、データを取り扱う手段としてほぼすべてのシステムが何かしらの、データベースを利用している。

DBMS　データベース管理システムとは

• データベースの定義や操作、制御などの機能を持つミドルウェア
• 多くの人が大量のデータを共同利用する用途で強みを発揮する。

リレーショナルデータベースマネジメントシステム（RDBMS）とは

• 表の形でデータを管理するデータベースのこと

どこで利用されている？

• ECサイトなら、ユーザー情報、商品情報、売上情報などのデータを保存して扱うために利用されている。
• SNS
• オンライン学習サイト
• 会計ソフト
• 銀行の預金情報管理
• 住民票管理

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

• 大量のデータから必要なデータを取り出すため
• 多人数でデータを共有して利用するため
• データの保護

テーブルとは
・下の図全体のことを指す。エクセルの表のようなもので管理する。テーブルは複数で管理する。

• 行（レコード、ロウ）とは上の図の青い部分を指す。一件分のデータを表す。
• 列（フィールド、カラム）とは上の図の赤い部分を指す。データを構成する各項目を表す。
• データベースの中に入れ物を作って、そこにデータを格納していく。

データベース設計の流れ

①論理設計…物理的な制約にとらわれずに机上でデータベース設計を考えること。細かいことを抜きにして全体像を作ろう。

1. エンティティの定義…データベースに保存する項目を決めること。システムに必要な項目を洗い出して、具体的な内容を決めていく作業。
2. 正規化…効率の悪いテーブルを分割していくこと。
3. ER 図作成

ER図

データ同士の関係性を表す図

実態と実態間の関連をという概念を使って、データの構造を図にしたもの。

実態からみて複数か否かによって矢じり部分の有り無しが決まる。

例　会社→従業員
また、1対多のほかに１対１、多対多などのバリエーションが考えられる。

社員と社員番号は1対1、商品と仕入れ先は多対多。

②物理設計…論理設計を実際のデータベース運用環境に当てはめる工程。

1. テーブル定義…まず、データを格納する部分の設計を行いデータを文字や数字などに分類し、それぞれの記録の方法や取り扱い方の規約を定める。これをデータ型という。性能要件では、データベースに求められる性能から、導入するハードウェアやネットワーク環境を明確にする。
2. インデックス設計…インデックスとは「索引」を意味し、検索目的となるテーブル内のレコード（情報）を識別する項目と、レコードの格納場所を示すポインタで構成されている。インデックスによって目的のデータが格納されている場所をすぐに特定できるため、データベースの処理スピードの高速化を実現できる。

SQLとは（ER図を基に書く）

• データベース、テーブル、行や列を扱うための言語

例　select * from users where age ≥ 20;

SQLの記述ルール

• 大文字、小文字は区別されない
• SQL文の最後にはセミコロンを付ける。

例 select * from users;

• 文字と日付はシングルクォーテーションで囲う

例　 select * from users where name ‘Inoue’;

例 select * from users where created_at ≥ ‘2010-01-01 00:00:00’;

• 単語は半角スペースもしくは改行で区切る

クエリとは　（query,問い合わせ）

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

例　ユーザー情報を取得するためにクエリを追加する。

データ型とは

• データベースでは、テーブルを作成するときに、それぞれの列（カラム・フィールド）に指定した形式のデータしか、入力できないように設定する。
• この時指定するデータの形式をデータ型という。

主なデータ型の種類

• 数値型

　　数値型の種類
　　　・int型　整数　-2147483648～2147483647
　　　　　補足…数値は符号なしとすることができる。正の数だけ扱うことができる。正の整数　
　　　　　　　 だけ扱うことで、扱える値の範囲が増える。
　　　・tinyint型　とても小さな整数　-128～127

　　補足…　真偽値true/falseを扱うことができる。

　　　　　　trueは数字の1、falseは数字の0で管理される。関連してMySQLでは0とnullが　　　　　

　　　　　　falseで、空文字を含むそれ以外の値はすべてtrueとなる。

補足２…実務上、整数の中でも真偽値を扱いたいときや、127以下の数機を扱う際は　

　　　　tinyint総数を扱うときは、dobleを使うことが多い。floatは使う理由が明確
　　　　にできる場合には使う。

・float型　小さい（単精度）浮動小数点

・double型　普通サイズ（倍精度）浮動小数点

• 文字列型
  　・char型　（キャラ）
  　　　固定長の文字列255文字まで。文字列を格納するときに指定した長さになるようにペー
  　　　スが埋め込まれる。
  例）商品コードで5桁固定”CD123”。定義方法：char（５）
  　・varchar型
  　　　可変長の文字列255文字まで。
  　　　例）email”[e-mail]”。　定義方法：varchar(255)
  　・text　
  　　長い文字列65535文字まで。実務上は、255文字まではvarchar、それ以上はtextとする
  　　　ことが多い。

• 日付

  　　　・date型
  　　　　　日付　’1000-01-01’から’9999-12-31’
  　　　・datetime型
  　　　　　日付と時刻’1000’-01-01　00：00：000000’から’9999-12-3123：59：59.999999’
  　　　・time型
  　　　　　時刻　’-838：59：59’から’838：59：59’まで

データベースからデータを取得する。

• データベースからデータを取得するselect文を利用する。
• 構文
  　select　列1、列２　…from　テーブル名;
• すべての列を取得したい場合は、

　　　select * from テーブル名;

SQLのコメント

• コメントとは
  テキストとしてSQL自体は書かれているが、SQLの実行時に書かれていないものとみなすための方法。
• 用途
  あとで見返したときに困らないようにメモ書きをしたり、検証のために一時的に特定のSQLを無効にしたいとき。
• コメントの種類
  ・ 「－－」　例　- - select * from users; 　一行コメント
  ・「/**/」　例　/*select * from users; */　複数行にわたってコメント

SQLの列を指定してデータを取得する方法

• ユーザー情報一覧が欲しい。個人情報漏洩リスクを考慮して、出力する列は、ユーザーIDと苗字だけにしてほしい。

use mydb;
select id, last_name from users;

補足…「*」を使ってすべての行を取得するよりも、必要な列に絞ってデータを取得する方が高速に実行できる。実務では、基本的には必要なカラムに絞ってデータを取得する。

試験的にSQLを書く時や、学習のとき等、パフォーマンスの心配がいらないときは、「*」を使うこともある。

SQLの列の名前を変える方法

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

　　「name」 を「名前」に、「price」を「価格」に変更

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

• 商品価格に税込み価格を追加してほしい。

ポイント

• テーブルから抽出した結果をそのまま使うのではなく、計算した結果を出力できる。

例

select
name as 名前,
price as 価格,

price * 1.08 as 税込み価格

from
products;

条件を指定してデータを取得することができる

• 価格が9800円以上の商品一覧が欲しい。
• where句

構文

select 列１,列２… from テーブル名 where 条件;

答え

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

• 比較演算子の種類

演算子説明

<　小なり

　大なり

<=　以下

=　以上

=　等しい

<>　もしくは

!=　等しくない

in　ある値が値セット内に含まれているかどうか

not in　値が値セットに含まれているかどうか

is null　値がnull

is not null　値がnullでない

like　パターンマッチング（あいまい検索）

between … and… 値が値の範囲内に含まれているか

• • 1)products テーブルから全件取得
    select * from products;
    -- 2)idが1の行を取得
    select * from products where id = 1;
    -- 3)名前が「商品003」の行を取得
    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句

’中’から始まる苗字のユーザー一覧を取得してほしい。

ポイント

like 句を使う

• 構文

select 列1、… , from テーブル名 where 列名 like ワイルドカード文字;

ワイルドカード文字とは

• 文字列のパターンを指定できる。
  1.’％’ …0文字以上の任意の文字列。
  2.’＿’…任意の1文字

例）

1.’中％’→’中’で始まる文字列

2.’中％’→’中’を含む文字列

3.’％子’→’子’で終わる文字列

4.’＿子’→何かしらの2文字から始まり、’子’で終わる文字列。

• • )中で始まる文字列を抽出
    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句

• 商品一覧を10件だけ取得してほしい

• limit句を使う

• 構文
  select 列1…　from テーブル名 limit [オフセット,]最大取得件数;

  select * from products limit 10 ;
  

または、select * from products limit 0,10;でも同じ結果が得られる。

0から始まっているのは、コンピューターが0から数えるから。

本番環境では、何件取得できるかわからないときは、limitで制限をかけるべき。なぜなら、本番のデータベースに負荷の高いクエリを送ってしまうと、サービスが停止してしまうから。

理想は、本番のデーターベースに直接アクセスするのではなく、データ解析専用のデータベースを用意すること。

10万件を超えたあたりから、パフォーマンスが悪くなる確率が高くなる傾向があるため、1000件あたりを目途に制限をかける。

• 男性ユーザーの一覧を取得してほしい。取得件数は10件で

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

select文によって取得されたデータの利用方法

• コンピュータープログラムから利用する。

Ruby（プログラミング言語）→　コネクタ/ドライバ→　データベース
データベース→コネクタ/ドライバ→Ruby（プログラミング言語）

プログラミング言語を使ってSQLをデータベースに投げて、例えばユーザー情報を画面に出力したり、商品情報を画面に出力したりする。開発者が各プログラムからデータベースに接続するにはコネクタやドライバというソフトウェアを利用するのが一般的。

• CSVファイルに書き出し、表計算ソフトに取り込む。（エクセル、googleスプレッドシート等）

CSVとは区切り文字である「,」で区切ったテキストデータ。

例）

id name price

1,商品001,5300

2,商品001,5300

3,商品001,5300

TSVとは

• 区切り文字である「タブ」で区切ったテキストデータ。

情報セキュリティ

• 扱うデータの情報漏洩に注意
• 組織で運用しているデータベースには、個人情報や機密情報が含まれる。
• 所属している組織のルールに従ってデータを取り扱う。
• 独立行政法人情報処理推進機構Webサイトで無料コンテンツが見られる。

データの集約

• 集約関数とはSQLでテーブルの値を集計するために使う。
• 関数は様々な計算をパッケージ化したもの。

主な集約関数

• sum(expr)…合計値
  2017年一月の合計売上を出したい。

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

• avg(expr)…平均値
  自社サイトで取り扱っている全商品の平均価格を教えて
  select avg(price) from products;
• min(expr)…最小値
  自社サイトで取り扱っている全商品の最小値を教えて
  select min(price) from products ;
• max(expr)…最大値
  自社サイトで取り扱っている全商品の最大値を教えて
  select max(price) from products ;
• count(expr)…行数
  ユーザーが何人いるか教えて
  select count() from users;
  このユーザーのうち、女性ユーザーが何人いるのか教えて
  select count() from users where gender = 2;

expr…expression(式)引数と呼ばれ、呼び出すごとに異なる値を関数に渡すために使用される。

データ集計の例

• 月別売上
• 商品価格の最小値、最大値、平均値
• テーブル行数の取得
• 月別アクセス数

2017年１月にアクセスしたユニークユーザー数（ECサイト登録ユーザーのみ）教えて
・ここでのユニークユーザーとは、決まった期間内にアクセスしたユーザーの数を表す数値。
・あるユーザーAが決まった期間内に10アクセスしたとしても、1と数える
・where句とcount(distinct expr)を組み合わせて使う。
・count(distinct expr)を組み合わせて使う。この形でcount関数は重複を排除して個数を取得

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

• 月別ユニークユーザー数
  ・group by 句を使用
  ・access_logsテーブルのデータを、request_monthによってグループ分けすることによって、　　
  　月別のユニークユーザー数を求める。

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

• 月間ユニークユーザーが630人以上の月を抽出
  ・having句を使う
  ・where句と同様に、条件に合致する行だけに絞り込める。
  ・having句はテーブルのデータを集約した結果に対して、条件式を適用する場合に利用する。
  ・構文　select 列1,… from テーブル名 where 条件式 group by 列1,… 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;

• 都道府県別のユーザー数
  ・group by 句
  ・構文
  　select 列名 from テーブル名 group by 列名;
  ・countなどを使ってデータを集計するときに、グループ単位で集計が行われる
  ・group by で指定する列名によって、グループ化される。

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

処理対象テーブル（users）のprefecture_idをgroup byでグループ化する。（東京なら10のように）そして、10（東京）のグループや1（北海道）のグループの数をcount(*)で集約して、都道府県のユーザー数を取得できる。

集約関数におけるnullの扱い

• nullとは値がないことを示す特別な表現
• 数値の0でも、長さ0でもなく、値がない。
• 集約関数は基本的にnullは無視される。

例

10、null、20→平均値avg()は15となる。（10+20）/2

• 可能であれば、値にnullが含まれないように、データベース構造を設計した方がトラブルは減る。
• nullを許可する場合、0とnullの違いや、nullと空文字の違いを意識する必要。
• nullの代わりに数値の0や文字列の’’空文字は使えないのか検討する。

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…取得する行数の制限

データの並び替え

• 並び替え　order by句を使う
• 降順（大→小）で並び替える。
• 構文

order by 列名や式　並び順, …
補足）並び順の指定
　・asc…昇順（デフォルト）
　・desc…降順

商品価格が高い順

select
*
from
products
order by
price
desc;

商品価格が低い順

select
*
from
products
order by
price
asc;

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

商品一覧を価格が高い順に並べて、価格が同じときは、登録順で並び替えてほしい。

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

• 日本語の商品名並び替え
  ・商品名ごとに並び替えたいとなったとき、アルファベットの商品名であれば、アルファベット順に並び変わる。
  ・日本語（全角文字）については、文字コード順になるので、期待通り並べ変えられないため、工夫が必要
  ・代替案は、横仮名入れる列を新規に作って、それを使って並び替える。（検証必要）

関数と算術演算子

足し算　+

引き算　-

掛け算　*

割り算　/

余り　　％

• nullを含む演算
  ・nullを含んだ計算結果はnullになる。
  例）10+null＝null , 10-null = null,10*null= null,10/null=null

• 絶対値の取得　abs
  ・数値の符号を考えない、ゼロからの距離の大きさを表す数値
  ・10の絶対値は10
  ・-10の絶対値は10
  ・0の絶対値は0

• 四捨五入　round
  ・商品価格一覧を作成するときに、税込み価格を出力してほしい。
  ・ただし、小数点第1位で四捨五入して出力
  ・round関数を使う
  ・構文
  round (対象の数値,丸めの桁数)
  例）
  　丸めの桁数に0を指定すると、小数第1位で四捨五入 round(10.555,0)→11
  　round(10.555,1)→10.6
  round(10.555,2)→10.56

select
id,name,round(price*1.08,0)

from
products;

• 文字列の演算
  ・ユーザーの一覧を、山田 太郎さんというように苗字+名前+さんのフォーマットで出力
  ・文字列連結 a || b … 文字列aとbを連結
  ・ただし、MySQL、SQL Server2012以降はconcat関数を使用。
  ・concat(文字列1,文字列2,文字列3)

select
concat(last_name,'',first_name,'さん')
from
users;

• 日付と時刻の演算
  ・現在の日付…current_date
  ・現在の時刻…current_timestamp
  ・n日後の日付…d+n
  ・n日前の日付…d-n
  ・x時間後の時刻…interval’x hour’
  ・x時間前の時刻…-interval’x hour’
  ・extract…日付や時刻の特定の部分（年や月）までを取り出す。
• 2017年０１月のレコードの取得

select
*
from
orders
where extract(year_month from order_time) = 201701;

テーブルの結合

テーブルの正規化とは
　・テーブルを分けて情報の重複をなくしていく作業
　・テーブルを正規化するメリット
　　①データの管理が容易になる。
　　②データの容量の削減

基本は正規化、しかしパフォーマンスの問題が解消できない場合は、あえて非正規化することもある。

主キー

• １つの行を特定できる列のこと。

外部キー

• 他のテーブルとの関連付けに使う列のこと。
• 外部キーは関連付けされた先のテーブルでは主キーになる。

リレーションシップの種類（テーブル同士の結びつき、関連性の種類）

一対多）

ユーザー（1）　－　　注文（多）

注文（1）　－　　注文明細（多）

多対多）

学生（多）　－　講義（多）

学生ごとに受ける講義はそれぞれであり、講義側も受ける学生がそれぞれである。一つの行には一つのデータしか入れられないので、どこかの学生の講義IDの列にはnullという情報が入ってしまう。なので中間テーブルの受講というものを作って、学籍番号と講義IDを紐づけ、学生Aは講義番号1と2、学生Cは講義番号１と２と3という複数の情報を持たせるようになる。

１対１）あまり使わない

ユーザー（1）　－　電話番号（1）

一見二つに分ける必要がないように思えるが、データベースはテーブルごとにアクセス権を接待できる。例えば、電話番号は個人情報なので、特別な権限を持った人だけ見れるようにする必要があるときに利用する。

• 内部結合でテーブルを結合する。
  ・inner joinを使う。
  ・顧客一覧を取得してほしい。都道府県IDで出力されてもよくわからないので、都道府県名も表示してほしい。
  ・inner joinの構文
  select
  テーブル名1.列名,
  テーブル名2.列名…
  from
  テーブル名1
  inner join
  テーブル名2
  on テーブル名1.列名 = テーブル名2.列名;

SQL

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

女性だけに条件を絞る

・inner joinの構文
select
テーブル名1.列名,
テーブル名2.列名…
from
テーブル名1
inner join
テーブル名2
on テーブル名1.列名 = テーブル名2.列名

where

条件;

SQL

select
users.id,
users.last_name,
users.first_name,
prefectures.name
from
users
inner join
prefectures
on users.prefecture_id = prefectures.id
where
gender = 2;

記述順序

1.select…取得列（カラム）の指定

2.from…対象テーブルの指定

3.結合処理

4.where…絞り込み条件の指定

6.group by…グループ化条件を指定

7.having…グループ化した後の絞り込み条件を指定

8.order by…並び替え条件を指定

9.limit…取得する行数の制限

実行順序

1.from…対象テーブルの指定

2.結合処理

3.where…絞り込み条件の指定

4.group by…グループ化条件を指定

5.having…グループ化した後の絞り込み条件を指定

6.select…取得列（カラム）の指定

7.order by…並び替え条件を指定

8.limit…取得する行数の制限

例　東京都に住むユーザーの、注文情報一覧を出す。

SQL

select
orders.id,
orders.order_time,
orders.amount,
users.id,
users.last_name,
users.first_name
from
orders
inner join
users
on orders.user_id = users.id
where
users.prefecture_id = 13
and
orders.order_time >= '2017-01-01 00:00:00'
and
orders.order_time < '2017-02-01 00:00:00'
order by
orders.id asc;

• 外部結合
  ・片方のテーブルの情報がすべて出力される。
  ・外部結合は欠落のあるデータを取り扱う結合。

select
テーブル名1.列名,
テーブル名2.列名…
from
テーブル名1
left outer join
テーブル名2
on テーブル名1.列名 = テーブル名2.列名

　　・left outer join
　　　　左側（from句で最初に書いたテーブル）をマスタとする。

　　・right outer join
　　　　右側（from句で後に書いたテーブル）をマスタとする。

userテーブルとorderテーブルの結合

select
users.last_name last_name,
users.id user_id,
orders.user_id order_user_id,
orders.id order_id
from
users
inner join
orders
on users.id = orders.user_id
order by
users.id;

• すべての商品について、販売個数一覧を出して

SQL

select
products.id,
products.name,
sum(order_details.product_qty)
from
products
inner join
order_details
on products.id = order_details.product_id
group by products.id;

• ３つ以上のテーブルを使った結合
  ・注文一覧を出してほしい。
  ・注文情報一覧と商品情報も一覧の中に入れてほしい。
  ・joinは複数回使える。（oracle DBだと動作しない）

SQL

select
o.id order_id,
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_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;

• 多対多の関係を含む結合
  ・商品ID　＝３に紐づく商品カテゴリ名をすべて教えてほしい。
  ・商品ID、商品名、カテゴリ名

SQL

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

• テーブルの足し算　union , union all
  ・ユーザーと、アドミンユーザーを足し合わせた一覧が欲しい
  ・出力する列はemail,姓,名,性別
  ・集合演算子　unionを使って、レコードの足し算を行うことで実現する。

　 ・select 列１, … from テーブル1
union select 列2, … FROM　テーブル2
　　・ベースとなるselectの結果に、unionの後に記載したselectの結果を足し算する。
　　・両方のテーブルにあるカラムで指定したものが出力される。どちらのテーブルにもある重複　
　　　行は削除される。
　　・where,group by,havingといった句を付けられる。order byだけは、全体として最後に一つし

　　　か記述できない。

SQL

select
email,
last_name,
first_name,
gender
from
users
union　←　重複行を削除しない場合はunion allを使う。
select
email,
last_name,
first_name,
gender
from
admin_users;

ビュー

• データそのものを保存するのではなく、データを取り出すSELECT文だけを保存する

• データベースユーザーの利便性を高める道具。

• SQLの観点からみると、テーブルと同じもの。

• ビューを使うと、必要なデータが複数のテーブルにまたがる場合など複雑な集約を行いやすくなる

• よく使うSELECT文はビューにして使いまわすことができる。

• テーブルとビューの違い
  ・テーブルは実際のデータを保存
  ・ビューの中にはSELECT文が保存される
  ・ビュー自体はデータを持たない

• ビューの制限
  ・order by句が使えない
  ・ビューに対する更新は不可能ではないが制限がある

• ビューの制限
  ・order by 句が使えない
  ・ビューに対する更新は不可能ではないが制限がある

• ビューのメリット
  ・データを保存しないので、記憶装置の容量を節約できる
  ・よく使うSELECT文をビューにしておくと使いまわせる

• デメリット
  ・パフォーマンス低下を招く場合がある

• ビューの構文
  ・create view文を使う
  ・構文
  create view ビュー名（<ビューの列名1>,<ビューの列名2>, …）
  as
  
  ・都道府県別のユーザー数を教えて
  SQL

/**create view prefecture_user_counts(name,count)←viewを作る
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文を使う
  ・構文
  drop view ビュー名;

SQL

drop view prefecture_user_counts;

サブクエリ

• ある問い合わせの結果に基づいて、異なる問い合わせを行う仕組み
• 複雑な問い合わせができる
• where句の中で使うことが多い
• where句以外にも、select句、from句、having句など様々な場所で利用できる。
• 日々の業務改善のデータ分析に役立つデータが、データベースから直接SQLで取り出せる。

例）
　・全商品の平均単価より、高い商品を取得
　・商品別の商品販売量よりも、多く売れている日を取得
　・商品カテゴリごとに、平均単価を取得
　・２０１７年１２月に商品を購入していない、ユーザーを取得

• where句で使うサブクエリの構文
  ・2017年12月に、商品を購入していないユーザーにメルマガを出したいから該当ユーザー一覧を出してほしい。必要な情報は、ユーザーID、苗字、メールアドレス

select
列名, …
from
テーブル名
where
列名 演算子　(select 列名 from テーブル名2 …);←このselect文に基づいて、上のselect文を実行する

SQL

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

• 応用　全商品の平均単価より高い商品を取得

スカラ・サブクエリ
・必ず１行1列だけの戻り値を返す、サブクエリのこと
・スカラとは単一のという意味
・絶対にサブクエリが複数行返さないようにする

SQL

select
*
from
products
where
price > (
select
avg(price)
from
products
);

条件分岐　case式

• case式を使うと、SQLで場合分けを記述することができる。
• 場合分けのことを条件分岐という。
• 成績表なら
  点数90点以上なら、Aと出力

　 点数80点以上なら、Bと出力　

　 点数70点以上なら、Cと出力

• ユーザーのアクティビティの度合いによって、施策を変えたい。
• ユーザーを累計注文回数でランク分けして、
  A：５回以上
  B：２回以上
  C：１回

構文

case
when 条件式1 then 値1 条件式1がtrueなら値1
[when 条件式2 then 値2…]　条件式Bがtrueならば値2
[else 値3]　どれにも当てはまらなければ値3
end

• 最初に条件に合致したブロックだけが実行

SQL

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 u
inner join
orders o
on u.id = o.user_id
group by u.id
order by user_rank asc;

• 取得値nullを0に置き換える

select
p.id,
p.name,
case
when sum(od.product_qty) is null then 0　←ここでcase文の条件分岐で指定
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;

• 商品の販売個数でランク分け

SQL

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 eva
from
products p
left outer join
order_details od
on p.id = od.product_id
group by
p.id
order by eva;

応用

• 月別平均客単価

select
date_format(order_time,'%Y%m') as order_year_month,
round((amount),0) as average_customer_spend
from
orders
group by
date_format(order_time,'%Y%m');

• 都道府県別平均客単価

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;

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

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
pref.id,
date_format(order_time,'%Y%m')
order by
prefecture_id asc,
order_year_month asc;

データの更新

• 新規行（レコード）を１件追加する方法

• 新商品を一件追加してほしい。
  ・商品名：新製品A
  ・価格　：1000円

• 構文
  insert into
  テーブル名（列1、列2、列3、…）
  values
  (値1,値2,値3, … );
  #列リストとvalues句の値リストは、列数が一致している必要がある。

• 列リストを省略してデータを１件追加する
  ・構文
  insert products values (1002,’新製品B’,2000);
  テーブル名
  #values句に列の定義順、カンマ区切りで値を設定
  #テーブルの全列に対して値を指定する必要あり

• 複数行追加する
  ・新商品３件をデータベースに追加
  ・商品名C,D,E価格はC=3000、D=4000、E＝5000
  ・構文

insert into
テーブル名（列1,列2,列3…）
values
(値1,値2,値3, …),
(値1,値2,値3, …),
(値1,値2,値3, …);

#oracle DBでは利用できない

SQL

insert into products (name,price)
values
('新商品C',3000),
('新商品D',4000),
('新商品E',5000);

• データの更新
  ・全商品を10％引きにしたい
  ・update文を使う
  ・構文
  update テーブル名 set 列1 = 値1, [列2 = 値2…]
  [where 条件式];

SQL

select * from products;
set sql_safe_updates = 0; ←セーフモードを一時的に解除
update products set price = price*0.9;

• 特定の条件に合致するデータを更新
  ・商品名idが3の商品名を、「SQL入門」に変える
  ・update文とwhere句を使う
  SQL
  update products set name = 'SQL入門' where id = 3;

• 更新条件にサブクエリを使う
  ・累計販売数が10を超えている商品に関しては、価格を5％Upして

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

• 行の削除
  ・商品に割り振られている商品カテゴリを使うのをやめるので商品とカテゴリのひも付きを削除する
  ・行を削除するdeleat文を使用
  ・商品とカテゴリを紐づけている、中間テーブルのproduct_categoriesのデータをすべて削除
  ・構文
  delete from テーブル名 [where 削除条件];

SQL

delete from products_categories;
#deleteで削除したデータは、基本的に戻せない。
#大量のデータをdeleteするときに予想外に時間がかかる場合がある。

• 条件を指定して、行を削除する方法
  ・商品ID1001は間違いで登録してしまったものだから、削除してほしい。
  ・delete文とwhere句を利用する
  SQL
  delete from products where id = 1001;
  #うっかりwhere句を指定し忘れると、productテーブル全体が削除される！！

• 削除条件にサブクエリを使う
  ・１個も売れない商品は、売るのをやめて削除して。

SQL

delete
from
products
where
id not in(
select
product_id
from
order_details
group by
product_id
);

データベース構造の操作

• データベースのメンテナンスを行えるスキル

• データベースの追加
  ・”book_store”という名前のデータベースを作成する
  ・create database文を使用
  ・構文
  create database データベース名;

SQL

create database book_store;

• 命名ルール
  ・データベースオブジェクトに使っていい文字
  　半角アルファベット
  　半角数字
  　アンダースコア
  　#ただし名前の最初は半角アルファベットとする。

• テーブルの追加
  ・booksという名前のテーブルを作成する。
  ・列はid,title

SQL

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文字の可変長文字列型
noy null :nullを許可しない

show columns from books;　　テーブルの構造確認

• テーブル構造の変更
  ・書籍を管理する”books”テーブルに価格を管理する列を追加。

SQL

alter table books add price int after id;

• 列名の変更
  ・列名”price”を”unit_price”に変更
  ・構文　alter table テーブル名 change 旧列名 新列名 データ型;
  SQL
  alter table books change price unit_price int;

• 列名の削除
  ・”books”テーブルから列”unit_price”を削除。
  ・構文　alter table テーブル名 drop 列名;

SQL

alter table books drop unit_price;

• テーブルの削除
  ・”books”テーブルを削除する。
  ・構文　drop table テーブル名

SQL
drop table books;
#削除したテーブルは簡単に復旧できないので注意

• データーベースの削除
  ・データーベース”book_store”を削除する。
  ・構文　drop database データベース名;

SQL

drop database book_store;

#削除したデータベースは簡単に復旧できないので注意

• 注意事項
  ・操作は基本的に取り消せないので本番環境を操作するときは、サービスをメンテナンスモードにしてバックアップを取ってから、alter tableなどを実行するのがいい。
  ・想定外に時間がかかりシステムトラブルになる場合もある。テスト環境でalter tableのテストをして問題点を洗い出してから、本番環境で実行するのがおすすめ。