okinawa

勉強メモ

DB・SQLの基本

 

参考図書

・スッキリわかるSQL入門
スッキリシリーズは本当に素晴らしい。
いつおもお世話になっております。
 

select

検索
select 列名 from table名;
 

select文全部入り

select文に全構文を入れるとこうなる。
select 列名
from table
where 条件式
group by 列名
having 条件式
order by 列名
 

insert

挿入・新しい行の作成
insert into teble名 (列名, 列名, 列名) values('値', '値', '値');
 
全列追加なら列名省略できる
insert into teble名 values('値', '値', '値');
 

update

更新・すでにある行の修正
update table名 set 列名 = '値' where 列名 = '値';
where句なしだと全行更新になる。
 

delete

削除・行の削除
delete from table名 where 列名 = '値';
where句なしだと全行更新になる。
 

演算子

比較演算子

=, <, >, <=, >=, <>

<>は左右の値が等しくない

!=と同じ
select * from 家計簿 where 出金額 <> 1000;
 

Null判定

=で判定できない。
式 is nll;
式 is not null;
select * from 家計簿 where 出金額 is null;
select * from 家計簿 where 出金額 is not null;
 

LIKE

select * from table where A LIKE '%abc%';
 

BETWEEN

ある範囲内に値がおさまっているかを判定。
式 BETWEEN 値1 AND 値2;
select * from table where 出金額 between 100 and 300;
 

IN/NOT IN

値がカッコ内の複数の値のいずれかに合致するかを判定する。
式 IN (値1, 値2, 値3)
select * from table where 出金額 in (100, 299, 300);
select * from table where 出金額 not in (100, 299, 300);
 

ANY/ALL

ANYは値リストと比較して、いずれかが真なら真
ALLは値リストと比較して、全てが真なら真
式 比較演算子 ANY  (値1, 値2, 値3)
式 比較演算子 ALL  (値1, 値2, 値3)
select * from table where 出金額 < ANY (100, 200, 300);
select * from table where 出金額 > ALL (100, 200, 300);
 

AND/OR

条件式 AND 条件式
条件式 OR 条件式
select * from table 出金額 < 100 AND 出金額 > 50;
select * from table 出金額 = 100 OR 出金額 = 50;
 

論理演算子の優先順位

NOT>AND>ORの優先順位
 
例:下記は、条件式2 AND 条件式3をまず評価
→その結果と条件式1 OR 条件式4を評価する。
SELECT * FROM 湊くんの買い物リスト
WHERE 販売店 = 'A' /* 条件式1 */
OR 販売店 = 'B' /* 条件式2 */
AND カテゴリ = 'ゲーム' /* 条件式3 */
OR カテゴリ = 'DVD' /* 条件式4 */
多分こうなってる→(販売店B AND ゲーム)OR 販売店A OR DVD
 
カッコで括ると優先順位を変えれる
SELECT * FROM 湊くんの買い物リスト
WHERE ( 販売店 = 'A' OR 販売店 = 'B')
AND ( カテゴリ = 'ゲーム' OR カテゴリ = 'DVD');
 

主キー

必ず何らかのデータが格納される。NOT NULL
他の行と重複しない。
 

複数列を主キーにする

例:氏名と生年月日を主キーにする
氏名だけだと重複するが、生年月日も含めれば重複しない。
 

検索結果を加工する

DISTINCT

重複行を除外する。
複数列指定の場合は全ての列の値が重複している場合のみ除外。
select distinct 列名 from table;
select distinct 列名1, 列名2 from table;//全ての列の値が重複している場合のみ。
 

ORDER BY

並び替え
ASC昇順
DESC降順
select column FROM table ORDER BY 出金額 DESC;
 
複数行指定での並び替え
最初に指定された列でまず並び替え、同じ値があれば次の指定で並び替え。
select column from table ORDER BY 入金額 DESC, 出金額 ASC;
※注意
並べ替えはDBに結構な負荷がかかる。
インデックスの併用を推奨。
DISTINCTとUNIONも並べ替えを行っていることがある。
 

LIMIT/OFFSET

先頭から数行だけ取得する。
LIMIT 取得行数 (OFFSET 先頭から除外する行数)
select * from table LIMIT 10;
select * from table OFFSET 20;
select * from table LIMIT 10 OFFSET 20; // 先頭20行を除き、21行目から30行目までを取得。
 

集合演算子

集合演算子が使える条件:
列数とデータ型が一致している事。
上記が一致していれば違うテーブルや列でもOK。

UNION

和集合

2つの検索結果を足し合わせたもの。
select 文1
UNION (ALL)
select 文2
デフォルトだとUNIONは重複行をまとめる。
UNION ALLにすると重複行も全てそのまま返す。
 

EXCEPT/MINUS

参考:SQL EXCEPTのサンプル(差分を抽出する)↓
 
差集合。ある集合と別の集合の差。

あるセレクト文の検索結果に存在する行から、別のセレクト文の検索結果に存在する行を差し引いた集合。
select 文1
EXCEPT(ALL)
SELECT 文2
 
下記はテーブルAで取得した行とテーブルBで取得した行を全ての列の値で比較し、列の値がすべて一致する行は抽出しません。
→テーブルAのみにある行を抽出します。
select * from テーブルA
EXCEPT
select * from テーブルB
 
順番にも注意。
下記はテーブルBのみにある行を抽出します。
select * from テーブルB
EXCEPT
select * from テーブルA
 
※注意
OracleではEXCEPT使えず、MINUSを使う。
MySQLはどっちも使えない。
 

INTERSECT

積集合を求める。

2つのセレクト文に共通する行を集めた集合です。
select 文1
INTERSECT
select 文2
 

式と関数

式の種類

足し算
数値+数値・日付+数値
引き算
数値ー数値・日付ー日付
掛け算
数値*数値
/
割り算
数値/数値
|| or +
文字列の連結
文字列||文字列
 

select文で存在しない列を表示する

出金額, 出金額+200, 固定値という3つの列が表示される。
select 出金額, 出金額+200, '固定値'
from 家計簿;
 
出金額、200円増しの2つの列が表示。中身は200円増しの値。
select 出金額, 出金額+200 as 200円増し
from 家計簿;
 

CASE演算子

if文と同じ。
CASE 列や式 WHEN 値 THEN 値
WHEN 値 THEN 値 //elseif
ELSE 値 // else
END AS 列名
 
単純case式
=判定しかできない。
CASE gender
WHEN '男' THEN 1
WHEN '女' THEN 2
ELSE 99
END
 
検索case式
CASEの後にすぐWHENを書く。
=以外の比較演算子も使える。
CASE WHEN 入金額 < 5000 THEN 'お小遣い'
WHEN 入金額 < 100000 THEN '一時収入'
WHEN 入金額 < 300000 THEN '給料出たー!'
ELSE '想定外の収入です!'
END AS 収入の分類
 

関数

例:length関数
文字列の長さを表す。
LENGTH(引数)
SELECT メモ, LENGTH(メモ) AS メモの長さ
FROM 家計簿
 

ストアドプロシージャ

データベース世界における戻り値のない関数。

ストアドファンクション

データベース世界における戻り値のある関数。
 
参考:わかりそうでわからないストアドプロシージャ
 

よくある関数

length/len 文字列の長さ
trim/Ltrim/Rtrim 前後/左/右の空白除去
replace 指定文字置換
substring/substr 文字列の一部抽出
round 指定ケタで四捨五入
power べき乗
current_date/current_time 現在の日付/時刻
cast データ型変換
coalesce 最初に登場するNULLでない値を返す
 

関数の小技

「select 関数」で関数の動作を確認。
select length('あああ');// 表示結果は3
 

集計関数とグループ化

SELECT文でしか使えない
  • select文の選択列リスト部分○
  • having句の中○
  • where句の中×
集計関数の結果は必ず1行になる。
結果表がデコボコになる形はダメ(各列の行数が同じじゃないとダメ)
 

集計関数の種類

SUM

全行の値を足す。
数値。
NULLは無視。

max

各行の最大値を求める。
数値・文字列・日付。
NULLは無視。

min

最小値。
数値・文字列・日付。
NULLは無視。

avg

平均値。
数値。
NULLは無視。

count

行数をカウントする。
count(*)// NULLを含めて行数をカウント
count(列) // NULLを無視してカウント
count(distinct 列名) // 重複行を除いたカウント
 

グループ化

GROUP BY

GROUP BY 列名
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
GROUP BY 費目 --費目列でグループ化する
 

HAVING

集計後・グループ化後に絞り込む。
SELECT 費目, SUM(出金額) AS 費目別の出金額の合計
FROM 家計簿
GROUP BY 費目
HAVING SUM(出金額) > 0 --合計値が0 より大きいグループを抽出
whereとやってることは同じ。
whereは集計・グループ化前に絞り込み。
havingは集計・グループ化後に絞り込み。
 

副問い合わせ

ほかのSQL文の一部分として登場するSELECT文。
SELECT 列名 FROM table
WHERE 列名 = (SELECT MAX(金額) FROM table2)
 

副問い合わせ処理のしくみ

副問い合わせ⇒主問い合わせの順に処理される。
副問い合わせの中に副問い合わせも書ける。
 

副問い合わせのパターン

得られる結果はこの3つ。

単一の値(スカラー

単一の値として扱う。
where 列 = (副問い合わせ)

n行1列(ベクター)

複数の値として扱う。
where 列 any < (副問い合わせ)
where 列 all < (副問い合わせ)
where 列 in(副問い合わせ)

n行n列(マトリックス)

テーブルとして扱う。
select * from (副問い合わせ)
insert into (副問い合わせ) values ~
update (副問い合わせ) set ~
 
特殊な例
insert文の特殊構文。
INSERT INTO 家計簿集計(費目, 合計, 平均, 回数)
-----ここから先がVALUES以降の記述に相当する。------
FROM 家計簿
SELECT 費目, SUM(出金額), AVG(出金額), 0
WHERE 出金額 > 0
GROUP BY 費目
----------------------------------------------
 

副問い合わせからNULLを除外する

SELECT * FROM 家計簿アーカイブ
WHERE 費目 IN (SELECT 費目 FROM 家計簿
WHERE 費目 IS NOT NULL)
 
SELECT * FROM 家計簿アーカイブ
WHERE 費目 IN (SELECT COALESCE(費目, 'ぬるぽ') FROM 家計簿) // NULLをぬるぽにする
 

テーブルの結合

いくつでもテーブル結合可能。
select 列
from tableA
join tabaleB
on 結合条件
join tableC
on 結合条件
join ~
 
SELECT 日付, 費目名
FROM 家計簿
JOIN 費目 -- 結合する他の表を指定
ON 家計簿.費目ID = 費目.ID -- 結合条件を指定
 

結合の処理イメージ

tableAの1行目に結合すべきtableBの行はどれかな?
というのを1行ずつ順番に処理するイメージ。
select *
from tableA //テーブルAに対して
join tableB //テーブルBを結合
on tableA.ID = tableB.ID //テーブルA.ID=テーブルB.IDになるように1行ずつ。
 

結合相手が複数の場合

行数が増える。
ID
名前
1
山田
2
佐藤
⇕結合
会員ID
ランク
1
ゴールド
2
シルバー
1
ブロンズ
上記2テーブルを結合
select ID, 名前, ランク
from tableA
join tableB
on tableA.ID = tableB.会員ID
山田さんが二人に増殖してしまう。
ID
名前
ランク
1
山田
ゴールド
1
山田
ブロンズ
2
佐藤
シルバー
 

結合相手の行がない場合

行数が減る。
ID
名前
1
山田
2
佐藤
⇕結合
会員ID
ランク
1
ゴールド
上記2テーブルを結合
select ID, 名前, ランク
from tableA
join tableB
on tableA.ID = tableB.会員ID
佐藤氏の消失。
ID
名前
ランク
1
山田
ゴールド
 

行数が減ると困る時は?

左部外部結合(left outer join)

左表の行数が減らない。
結合相手がない時はNULLが入る。
select 列
from 左テーブル
left outer join 右テーブル
on 結合条件
 
ID
名前
1
山田
2
佐藤
⇕左部外部結合
会員ID
ランク
1
ゴールド
佐藤氏無事。
ID
名前
ランク
1
山田
ゴールド
2
佐藤
NULL
 

右部外部結合(RIGHT OUTER JOIN)

右表の行数が減らない。

完全外部結合(FULL OUTER JOIN)

左右表の全行出力。

外部結合と内部結合の違い

結合すべき行が見つからない時に
外部結合は行が消滅しない、内部結合は行が消滅する。

複数条件で結合

select 列
from tableA
join tabaleB
on 結合条件 and 結合条件2

3つ以上のテーブル結合の処理順

select 列
from tableA
join tabaleB //まずAにBを結合
on 結合条件
join tableC //その後にCを結合
on 結合条件

副問い合わせテーブルと結合

n行m列の副問い合わせと結合できる。
slect *
form tableA
join (select * form tableB)
on A = B;
 

外部キーと主キー

基本的にはtableAの外部キーとtableBの主キーを指定して結合することが多い。
主キーは
  • 主キーはタプル(行)の一意識別能力を持つ(重複しない).
  • 主キーは空値(NULL)を含まない
外部キーとは
  • 外部キーの値は,参照先のリレーションの主キーの値から選ばれる.
  • 外部キーは空値(NULL)を含むことが許される.
 
外部キーと主キーの例↓
tableA
ID(主キー)
会員ID(外部キー)
名前
1
1
山田
2
2
佐藤
⇕結合(ON tableA.会員ID = tableB.会員ID)
tableB
会員ID(主キー)
ランク
1
ゴールド
2
シルバー
ID
名前
ランク
1
山田
ゴールド
2
佐藤
シルバー

トランザクション

トランザクションとは一塊の処理。
トランザクションは仮の書き換えで、コミットするまで確定しない。
 
もし停電してDBが止まっても、電源入れ直すとロールバック処理が走るんだって。すげー。
 

テーブルの作成(CREATE TABLE)

create table テーブル名 (列名, 列名の型名)
create table ABC (ID INTEGER, 日付 DATE)
 

テーブルの削除(DROP TABLE)

drop table テーブル名
 

テーブル定義の変更(ALTER TABLE)

列の追加

alter table テーブル名 add 列名 型 制約

列の削除

alter table テーブル名 drop 列名 型 制約
 

代表的な制約

NOT NULL

NULLは許されない!

UNIQUE

一意。
重複は許されない!

CHECK

ある列の値が妥当かをチェックする。
check(ID >= 0) // IDが0以上かをチェック

主キー(primary key)

  • 主キーの値を指定すれば、どの1行かを完全に特定できる。(重複不可)
  • NOT NULL。

外部キー(foreign key)

  • 外部キーの値は,参照先のリレーションの主キーの値から選ばれる.
  • 外部キーは空値(NULL)を含むことが許される.
書き方1
create table テーブル名(
列名 型 REFERENCES 参照先テーブル名(参照先列名))
書き方2
create table テーブル名(
foreign key (参照元列名) references 参照先テーブル名(参照先列名)
 

権限の付与・はく奪(GRANT/REVOKE)

grant 権限名 to ユーザー名// 権限の付与
reboke 権限名 from ユーザー名 //権限のはく奪
 

データベースの知識を深めよう

インデックス(CREATE INDEX)

インデックスとはDBの索引情報。 インデックスをつけると検索が高速になる。  
 
create index インデックス名 ON テーブル名(列名)
drop index インデックス名
drop index インデックス名 on テーブル名 (mysqlの場合)
 

インデックスのデメリット

インデックス作成のため、ディスク容量を消費する。
データ変更するときにインデックスも同時に書き換える必要が出る。
そのためINSERT・UPDATE・DELETEの処理が増える。  
 

ビュー

SQL分を短縮するのが目的。
何度も呼び出すテーブルをビューとして登録する。  
create view ビュー名 as select文
drop view ビュー名
 

採番

追加する行に独自の番号を振る。

AUTO_INCREMENT

いつも使ってるやつ。
1から順に自動連番。

SEQUENCE

シーケンスではデフォルト値や、1回の採番で増やす値、最大値などのオプションが使用可能。
create sequence シーケンス名;
drop sequence シーケンス名;
 

バックアップ

ACID特性

原子性:処理が中断しても中途半端な状態にならない。
一貫性:データの内容が矛盾しない。
分離性:複数の処理をどう実行しても副作用がない。
永続性:記録した情報は消滅せず保持され続ける。  
 

2つのファイルをバックアップ

データベースの内容そのもの:処理が重い。低頻度。
ログファイルの内容:処理軽い。高頻度。 DBのログファイルには実行した全てのSQL文が載っている。  
 

ロールフォワード

ログファイルを使って、障害発生の直前までデータ更新を進めること。
更新をキャンセルするロールバックの逆。  
 

DB設計

DB設計の流れ

概念設計

管理すべき情報がどのようなものかを整理。
RDBを意識せずに洗い出し。
情報同士の関連も整理。

論理設計

概念設計で明らかになった各情報について、RDBを使う前提で構造を整理し具体化。
どんなテーブルでどんな列を作るかまでを明らかにする。
ER図を作る。
型や成約などはまだ。

物理設計

特定のDBMS製品を使う前提で具体化する。
すべての列について型、インデックス、制約、デフォルト値など全ての要素を確定させる。
どんなSQL文を書くかまで落とし込む。
正規化もここでやる。
 

正規化

正規化の手順

非正規形→第1正規形→第2→第3

非正規形から第1正規形

・第1正規形の達成条件
テーブルのすべての行のすべての列に1つずつ値が入っている。
よって、繰り返しの列やセルの結合が現れてはならない。
 
下記の社員テーブルを第1正規形にしよう。
 
社員テーブル
部署番号
部署名
社員番号
社員名
役職コード
役職名
年齢
D1
開発1部
107
L
主任
31
121
R
一般
22
122
浅香あゆみ
R
一般
24
D2
開発2部
107
L
主任
31
112
C
副主任
29
 
1,繰り返し列を切り出す(行結合してない列のこと)
社員テーブルから繰り返し列を切り出し、社員番号テーブルを作る。
 
社員番号テーブル
社員番号
社員名
役職コード
役職名
年齢
107
L
主任
31
121
R
一般
22
122
浅香あゆみ
R
一般
24
107
L
主任
31
112
C
副主任
29
 
2,切り出し列の先から仮の主キーを選ぶ
社員番号列を主キーとする。
 
社員番号テーブル
社員番号
社員名
役職コード
役職名
年齢
107
L
主任
31
121
R
一般
22
122
浅香あゆみ
R
一般
24
107
L
主任
31
112
C
副主任
29
 
3,切り出し元の主キーをコピーし、切り出し先の複合主キーとする。
社員テーブルの部署番号をもってくる。
部署番号と社員番号の複合主キーとなる。
 
社員番号テーブル
社員番号
部署番号
社員名
役職コード
役職名
年齢
107
D1
L
主任
31
121
D1
R
一般
22
122
D1
浅香あゆみ
R
一般
24
107
D2
L
主任
31
112
D2
C
副主任
29
 
部署テーブル
社員テーブルから切り残された部分。
部署番号
部署名
D1
開発1部
D2
開発2部
これが非正規系から第1正規形への変形。
 
 

第1から第2正規形

・第2正規形の達成条件
複合主キーを保つテーブルの場合、非主キー列は複合主キーの全体に関数従属する。
よって、複合主キーの一部の列に対してのみ関数従属する列が含まれてはならない。
 

関数従属性

ある列Aの値が決まれば、自ずと列Bの値も決まるという関係。
このとき列Bは列Aに関数従属しているという。
 
例:金額が複合主キー(入出金ID+費目ID)に関数従属。
入出金ID+費目IDが確定すると金額が確定する。
入出金ID
費目ID
費目名
金額
41001
H01
住居費
65000
41001
H07
手数料
525
 

部分関数従属

穢らわしい関数従属のことw
複合主キーの一部の列にしか関数従属しないこと。
 
例:費目名が費目IDだけに関数従属。
費目IDだけが決まれば費目名が確定する。
入出金ID
費目ID
費目名
金額
41001
H01
住居費
65000
41001
H17
手数料
525
 
社員番号テーブルを第2正規形に変形しよう。
 
社員番号テーブル
社員番号
部署番号
社員名
役職コード
役職名
年齢
107
D1
L
主任
31
121
D1
R
一般
22
122
D1
浅香あゆみ
R
一般
24
107
D2
L
主任
31
112
D2
C
副主任
29
 
1,部分関数従属している列を切り出す。
 
社員テーブル
社員名
役職コード
役職名
年齢
L
主任
31
R
一般
22
浅香あゆみ
R
一般
24
L
主任
31
C
副主任
29
 
2,部分関数従属先の列をコピーし、主キーとする。
 
社員テーブル
社員番号
社員名
役職コード
役職名
年齢
107
L
主任
31
121
R
一般
22
122
浅香あゆみ
R
一般
24
107
L
主任
31
112
C
副主任
29
 
社員番号テーブル(切り残された部分)
社員番号
部署番号
107
D1
121
D1
122
D1
107
D2
112
D2
 
部署テーブル(ここはそのまま)
部署番号
部署名
D1
開発1部
D2
開発2部
これで第2正規形になった。
 

第2から第3正規形へ

第3正規形の達成条件
テーブルの非キー列は、主キーに直接関数従属すべきである。
よって、主キーに関数従属する列にさらに関数従属する列は存在してはならない。
 

推移関数従属

穢らわしい関数従属2w
主キーに対する間接的な関数従属。
 
例:きれいな関数従属
利用者IDが主キー入出金IDに関数従属。
入出金IDが決まれば利用者IDが決まる。
入出金ID
費目ID
利用者ID
利用者名
41001
2021/4/16
1
41001
2021/4/10
2
橘工事
 
例:推移関数従属
利用者名が利用者IDに関数従属。
そして利用者IDは入出金IDに関数従属。
つまり利用者名が入出金IDに間接的な関数従属。
入出金ID
費目ID
利用者ID
利用者名
41001
2021/4/16
1
41001
2021/4/10
2
橘工事
 
1,間接的に主キーに関数従属する列を切り出す。
社員テーブルから役職名列を切り出す。
 
役職テーブル
役職名
主任
一般
一般
主任
副主任
 
2,直接関数従属先だった列をコピーする。
 
役職テーブル
役職コード
役職名
L
主任
R
一般
R
一般
L
主任
C
副主任
 
社員テーブル
社員番号
社員名
役職コード(FK)
年齢
107
L
31
121
R
22
122
浅香あゆみ
R
24
107
L
31
112
C
29
 
部署テーブル
部署番号
部署名
D1
開発1部
D2
開発2部
 
部員テーブル
部署番号(FK)
社員番号(FK)
D1
107
D1
121
D1
122
D2
107
D2
112
 

最終的なER図