テーブルの結合とはテーブルの正規化とは

テーブルの正規化とは

  • テーブルを分けて情報の重複をなくしていく作業

テーブルの正規化するメリット

  • 1 . データ管理が容易になる

  • 2 . データ容量の削減

  • →特別な意図がなければ、テーブルは正規化する。

    • 特別な意図の例) システムのパフォーマンス向上のため。

テーブルの結合とは

  • テーブル同氏をある条件で結合することにより、正規化なしの状態を作り出すこと。

基本は正規化、しかしながら、

  • パフォーマンスの問題が解消できない(できなくなりそうな)場合はあえて非正規化することもある。

  • 実務で見かける可能性もある。

主キーと外部キー

主キー(Primary Key, PK)

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

外部キー(Foreign Key, FK)

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

リレーションシップの種類

  • テーブル同士の結び付き・関係性の種類

  • 1対多

  • 多対多
  • 一対一

1対多の事例

  • (1)ユーザー - (多)注文

  • (1)ブログの投稿者 - (多)ブログの投稿

  • (1)注文 - (多)注文明細

  • (1)部署 - (多)従業員

多対多の事例

  • (多) 商品 - (多) 商品カテゴリ

  • (多) ブログの投稿 - (多)ブログのカテゴリ

  • (多) ユーザー - (多) 操作権限

一対一の事例

  • 特殊なので、あまり使う機会はない

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

  • 構文
select
  テーブル名1.列名,
  テーブル名2.列名 ...
from
  テーブル名1
inner join
  テーブル名2
on テーブル名1.列名 = テーブル名2.列名;

内部結合 + 絞り込み

  • 構文 inner join + where
select
  テーブル名1.列名,
  テーブル名2.列名 ...
from
  テーブル名1
inner join
  テーブル名2
on テーブル名1.列名 = テーブル名2.列名
where
  絞り込み条件;

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

外部結合

  • 片方のテーブルの情報がすべて出力される、テーブルの結合

  • 外部結合は欠落のあるデータを取り扱う結合。

  • 構文 :外部結合 left outer join

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

left outer join / right outer join

  • left outer join...左側(from句で最初に書いたテーブル)をマスタとする。

  • right outer join...右側(from句で後に書いたテーブル)をマスタとする。

内部結合 inner join

  • お互いに一致している行が統合テーブルの対象となる

テーブルの足し算 集合演算子 union nuion all

  • 構文
select 列1, ... from テーブル1
union select 列2, ... FROM テーブル2
  • ベースとなるselectの結果に、unionの後に記載したselectの結果を足し算する

日付と時刻の演算

日付と時刻の関数や演算子

  • 現在の日付 ・・・ current_date

  • 現在の時刻 ・・・ current_timestamp

  • n日後の日付 ・・・ d + n

  • n日前の日付 ・・・ d - n

  • x時間後の時刻 ・・・ interval'x hour'

  • x時間前の時刻 ・・・ - interval'x hour'

  • extrfact ・・・ 日付や時刻の特定の部分(年や月) までを取り出す。

SQL 関数と演算子

四捨五入 round

  • 構文
round(対象の数値, 丸めの桁数)
  • 例)

    • 丸めの桁数に0を指定すると、小数点第1位で四捨五入 round(10,555, 0)

    • 丸めの桁数に1を指定すると、小数点第2位で四捨五入 round(10,555, 1)

      • →10.6

    • 丸めの桁数に2を指定すると、小数点第3位で四捨五入 round(10,555, 2)

      • →10.56

文字列演算

  • 文字列演算 a || b ・・・文字列aと文字列bを連結
    • ただし、MySQL,SQL Server 2012以後ではconcat関数を使用
    • concat(文字列1, 文字列2, 文字列3 ・・・)

SQL データの並び替え

select文で取得するデータの並び替え order by

  • 構文
order by 列名や式 並び順, ...

  • 並び順の指定

    • asc ・・・昇順(ascending) ※デフォルト。並び順を指定しない場合ascになる
    • desc ・・・降順(descending)

  • order byには、カンマ区切りで複数の並び替え条件を指定できる。

  • 登録順はid昇順で並び替える。

データの集約

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

  • 構文
sum(expr)

※exprの合計値を返す。

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

  • 構文
avg(expr)

※exprの平均値を返す。

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

min(expr)

※exprの最小値を返す。

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

max(expr)

※exprの最大値を返す。

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

nullとは

  • 値がないことを示す特別な表現

  • 数値の0でも、長さ0でもなく、値がない。

  • 集約関数では、基本的にはnullが無視される。

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

  • 構文
count(expr)

補足: 
 * exprの数をカウント
 * count(*)とすると、テーブルの行数を取得できる。
 * userテーブルの行数が、ユーザー数となる。

指定した期間のユニークユーザー数を求める方法

  • ここでの、ユニークユーザーとは、決まった集計期間内にアクセスしたユーザーの数を表す数値

  • あるユーザーAが決まった機関内に10アクセスしたとしても、1と数える。 where句と集約関数count(distinct expr)を組み合わせて使う。

  • count(distinct exor)の形で、count関数は、重複を排除した上で、個数を取得できる。

テーブルのデータをグループ化するgroup by句

  • 構文
select
  列名
from
  テーブル名
group by 列名;
  • countなどを使ってデータを集計する時に、グループ単位で集計が行われる
  • group byで指定する列名によって、グループ化される。

集約結果をさらに絞り込むhaving句

  • where句と同様に、条件に合致する行だけに絞り込める。

  • having句は、テーブルのデータを集約して結果に対して、条件式を適用する場合に利用する。

  • 構文

select 
  列1, ...
from
  テーブル名
where
  条件式
group by
  列1, ...
having
  条件式;

記述順序 having

  • select
    • ↓
  • from
    • ↓
  • where
    • ↓
  • group by
    • ↓
  • having

※ ポイントとしては、havingは、group byの後に書く

select文の記載順序と実行順序

  • 記述順序 (必ずこの順序で書く必要がある)

      1. select ・・・ 取得行(カラム)の指定
      1. from ・・・ 対象テーブルの指定
      1. where ・・・ 絞り込み条件の指定
      1. group by ・・・ グループ化の条件を指定
      1. having ・・・ グループ化した後の絞り込み条件を指定
      1. order by ・・・ 並び替え条件を指定
      1. limit ・・・ 取得する行数の制限

  • 実行順序

      1. from ・・・ 対象テーブルの指定
      1. where ・・・ 絞り込み条件の指定
      1. group by ・・・ グループ化の条件を指定
      1. having ・・・ グループ化した後の絞り込み条件を指定
      1. select ・・・ 取得行(カラム)の指定
      1. order by ・・・ 並び替え条件を指定
      1. limit ・・・ 取得する行数の制限

  • 記述順序/実行順序の違い

      1. select / from
      1. from / where
      1. where / group by
      1. group by / having
      1. having / select
      1. order by / order by
      1. limit / limit

※ ポイント : selectの記述順序と実行順序が異なる

データを表計算ソフトに取り込む

select文により取得したデータの利用方法

データをCSVに書き出す

CSVとは

 * Comma-Separated Valuesの略
 * 区切りもじであるコンマ「,」で区切ったテキストデータ

CSVの例

id ,name price
1, 商品01, 500

※CSVの代わりにTSVという形式を使うことがある。

TSVとは

  • Tab-Separated Values(TSV)の略
  • 区切りもじであるコンマ「タブ」で区切ったテキストデータ

SQL文の基礎

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

* 構文
   select 列1,列2 ... from テーブル名
* 全ての列を取得したい場合は、
   select * from テーブル名;

初心者が間違いやすいこと

  • DBを選択していない
  • スペルミス
  • 全角文字で記載

コメントの種類

  • ① 「--」

  • ② 「/ /」

  • 「*」を使って全ての行を取得するより、必要な列を絞って取得する方が高速に実行できる

  • 実務では、基本的に必要なカラムに絞ってデータを取得する。
  • 試験的にSQLを書く時や、学習の時など、パフォーマンスの心配がないときは、「*」を使うこともある。

列に別名を付ける

select 列1 as 別名にする文字を記載 from テーブル名

※asは省略可能

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

  • テーブルから抽出した結果をそのまま使うだけではなく、計算した結果を出力することができる。
select
  name as 名前,
  price as 価格,
  price * 1.1 as 税込価格
from
  products;

条件を指定して値を取得できるwhere句を使用

  • 構文
select 列1, 列2 ... from  テーブル名 where 条件;

where句で条件を設定する際に利用できる代表的な演算子の種類

  • 「=」等しい
  • 「>」より大きい
  • 「>=」以上
  • 「<」より小さい
  • 「<=」以下
  • 「<>」, 「!=」等しくない
  • 「in」ある値が値セット内に含まれているかどうか
  • 「not in」値が値セット内に含まれていないかどうか
  • 「is null」値がnull
  • 「is not null」値がnullでない
  • 「like」パターンマッチング(あいまい検索)
  • 「between ... and ...」値が値の範囲内に含まれているか

パターンマッチングによる絞込ができるlike句

  • 構文
select 列1,　...,from テーブル名 where 列名 like ワイルドカード文字;

ワイルドカード文字について

  • ワイルドカード文字で文字列のパターンを指定します。

    1. '%' (パーセント) ・・・0文字以上の任意の文字列
    2. '_' (アンダースコア) ・・・任意の1文字

  • 例)

    1. '中%' →'中'で始まる文字列
    2. '%中%' → '中'を含む文字列
    3. '%子' → '子'で終わる文字列
    4. '__子' →何かしらの文字から始まり、'子'で終わる文字列

取得件数を制限できるlimit句

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

※オフセットは省略可能