#
ドキュメント

Document

自分のための備忘録です。

目次

SQLの概要

  • SQLスキーマ文
  • SQLデータ文
  • SQLトランザクション文

SQLスキーマ文を使って作成されたすべてのデータベース要素は、データディクショナリ(data dictionary)と呼ばれる特別なテーブル集合に格納されます。この「データベースに関するデータ」はまとめてメタデータ(metadata)と呼ばれます。

初めてのSQL(p47)

NULL

select e.emp_id, concat(e.fname, ' ', e.lname) from employee as e where e.superior_emp_id is NULL; # OK
select e.emp_id, concat(e.fname, ' ', e.lname) from employee as e where e.superior_emp_id = NULL;  # NG

テーブル

永続テーブル(create table文を使って作成されたテーブル 一時テーブル(サブクエリから返された行セット) 仮想テーブル(create view文を使って作成されたテーブル

初めてのSQL(p45)

ビュー(仮想テーブル)

ビューとは、データディクショナリに格納されたクエリです。外観や振る舞いはテーブルに似ていますが、データは関連付けられていません(これが仮想テーブルと呼ばれる由縁です)。ビューに対してクエリを発行すると、クエリがビューの定義とマージされて、最終的に実行するクエリが生成されます。

CREATE VIEW employee_vw AS
SELECT emp_id, fname, lname
YEAR(start_date) start_year
FROM employee

ビューが作成されたあと、追加のデータが作成されることはありません。select文があとから使用できるように格納されるだけです。

初めてのSQL(p46)

サブクエリ

サブクエリから返されるテーブルの種類によって、サブクエリの使用法と、含む側の文がそのテーブルにアクセスするために使用できる演算子が決まります。含む側の文の実行が終了すると、サブクエリがら返されたテーブルはすべて破棄されるため、サブクエリは文スコープの一時テーブルのような働きをします(文スコープとは、SQL文の実行が終了したあと、サブクエリの結果に割り当てられたメモリがすべて開放されることを意味します)。

初めてのSQL(p157)

サブクエリは常にかっこ(())で囲まれ、通常は含む側の文よりも先に実行されます。

初めてのSQL(p157)

サブクエリの例は下記のURLを参照。
https://github.com/s-hiroshi/learningsql/blob/master/training.sql

サブクエがもたらす柔軟性は、クエリの作成において計り知れない価値があります。実際に利用できるテーブルに縛られることなく、必要に応じてほぼどのようなデータビューでも作成でき、そのテーブルをほかのテーブルやサブクエリによって生成されたテーブルに結合できるようになるからです。

初めてのSQL(p173)

非相関サブクエリと相関サブクエリ

  • 非相関サブクエリは、単独で実行でき、含む側のSQLを参照しない
  • 相関サブクエリは、含む側のクエリを参照する

非相関サブクエリ

非相関サブクエリで特に覚えておくと良い点を書きます。

テーブルを作成するサブクエリは、非相関サブクエでなければなりません。これらのサブクエリは先に実行され、サブクエリが生成するテーブルは含む側のクエリの実行が終了するまでメモリに保持されます

相関サブクエリ

相関サブクエリは、含む側の列を1つ以上参照するという点において、含む側の文に依存します。

初めてのSQL(p167)

非相関サブクエリとは異なり、相関サブクエリは含む側の文よりも先に実行されるのではなく、候補行(最終結果に含まれる可能性のある行)ごとに実行されます。

初めてのSQL(p167)

ただし、候補行に対する個別の実行は、サブクエリを含む側の文より先に実行されることに変わりはありません。

相関サブクエリの例

SELECT
  c.cust_id
  ,c.cust_type_cd
  ,c.city
FROM customer c
WHERE 2 = (SELECT COUNT(*)
          FROM account AS a
          WHERE a.cust_id = c.cust_id);

上記は、customerテーブルのレコード数だけ、サブクエリが実行されます(cust_idはユニーク)。

exists演算子

exists演算子を使用する場合は、select 1 またはselect * を指定する決まりになっています。

初めてのSQL(p170)

DELETE文と相関サブクエリ

MySQLのdelete文に相関サブクエリを使用する際には、どのような場合も、テーブルエイリアスは使用できないことに注意してください。

初めてのSQL(p171)

サブクエリと一緒に使われる演算子の例

=, in, all, any, existsなど

グループ化

group by節はwhere節が評価されたあとに実行されるため、そのためのフィルタ条件をwhere節に追加することはできません。

初めてのSQL(p143)

集約関数

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.1 GROUP BY (集約) 関数

COUNT()SUM()MAX()MIN()

集計結果に対する条件なのでHAVING句を使います。

CASE式

CASE式は列名や定数をかける場所には常に書くことができます。

達人に学ぶSQL徹底指南書(p25)

例1

PopTb1テーブル

// PopTb1
prefname | population
徳島    100
香川    200
愛媛    150
高知    200
福岡    300
佐賀    100
長崎    200
東京    400
群馬    50

SQL

SELECT 
    CASE 
        WHEN pref_name = '香川' THEN '四国'
        WHEN pref_name = '愛媛' THEN '四国'
        WHEN pref_name = '徳島' THEN '四国'
        WHEN pref_name = '高知' THEN '四国'
        WHEN pref_name = '福岡' THEN '九州'
        WHEN pref_name = '長崎' THEN '九州'
        WHEN pref_name = '佐賀' THEN '九州'
        ELSE 'その他'
    END
    AS 地区
  ,SUM(population) AS 人口
FROM PopTbl GROUP BY 地区;

結果

県名|人口
その他    450
九州    600
四国    650

例2

PopTbl2テーブル

id pref_name population sex
1    徳島    69    1
2    徳島    40    2
3    香川    100    1
4    香川    100    2
5    愛媛    50    2
6    愛媛    100    1
7    高知    100    1
8    高知    100    2
9    佐賀    400    2
10    佐賀    300    1
11    福岡    600    1
12    福岡    500    2
13    長崎    600    1
14    熊本    600    1
15    熊本    600    2
16    鹿児島    400    2
17    鹿児島    400    1
18    大分    300    1
19    大分    320    2
20    長崎    320    2
25    愛知    250    1
26    愛知    200    2

SQL

SELECT pref_name as 県名,
    SUM(CASE WHEN sex = 1 THEN population ELSE 0 END) AS 男性,
    SUM(CASE WHEN sex = 2 THEN population ELSE 0 END) AS 女性
FROM PopTbl2 GROUP BY 県名;

結果

県名| 男性 | 女性
佐賀    300    400
大分    300    320
徳島    69    40
愛媛    100    50
愛知    250    200
熊本    600    600
福岡    600    500
長崎    600    320
香川    100    100
高知    100    100
鹿児島    400    400

結合

クロス結合(=交差結合, 直積結合)

SELECT * FROM example e1 CROSS JOIN example e2;
// または
SELECT * FROM example e1, example e2;

SQL DISTINCT文で重複行を1行で表示する

外部キー

社員テーブル

社員ID 名前 所属ID
1001 A 2
1002 B 1
1003 C 3
1004 D 1
1005 E 2

主キー: 社員ID
外部キー: 所属ID

所属テーブル

所属ID 所属名
1 営業
2 開発
3 財務
4 総務

主キー: 所属ID

外部参照のため下記の順序でテーブルを作成します。

  1. 所属
  2. 社員
CREATE TABLE 所属 (
  所属ID VARCHAR(3)    NOT NULL  PRIMARY KEY,
  所属名  VARCHAR(255) NOT NULL
);

CREATE TABLE 社員 (
  社員ID VARCHAR(4)   NOT NULL PRIMARY KEY,
  名前   VARCHAR(255) NOT NULL,
  所属ID VARCHAR(3) NOT NULL,
  CONSTRAINT FOREIGN KEY(所属ID) REFERENCES 所属(所属ID)
);

外部キーを設定する意味

外部キー(=外部参照整合制約)が参照するキーの削除を制限できる。
例では所属テーブルの総務以外のレコードは削除できない。

ER図

oneがmanyの外部キー。

削除

テーブルは社員 -> 所属の順でしか削除できない。

ONとWHERE

ON 句は結合条件、WHERE 句は抽出条件

ON 句は結合条件、WHERE 句は抽出条件 - 集中力なら売り切れたよ

関係代数

関係演算は集合演算と関係演算に分けられます。

  • 集合演算
  • 関係演算

関係代数 – データベース研究室

トラブルシューティング

Lost connection to MySQL server at 'sending authentication information', system error

ruby on rails 3 - Mysql 5.6 headaches on Mac OSX - Stack Overflo