SQL サブクエリ

サブクエリ

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

  • 構文

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

スカラ・サブクエリ

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

SQL ビュー

テーブルとビューの違いは?

  • テーブル

    • 実際のデータを保存

  • ビュー

    • ビューの中にはselect文が保存される
    • ビュー自体はデータを持たない。

ビューの制限

  • order b句が使えない

  • ビューに対する更新は不可能ではないが制限がある。

ビューのメリット

  • データを保存しないので、記憶装置の容量を節約できる。

  • よく使うselect文をビューにしておくことで、使い回しができる。

ビューのデメリット

  • パフォーマンス低下を招く場合がある

ビューの作成

  • 構文
create view ビュー名(<ビューの列名>,<ビューの列名2>, ,..)
as
<select文>

ビューの削除

  • 構文
 drop view ビュー名;

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

テーブルの正規化とは

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

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

  • 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の記述順序と実行順序が異なる