参考図書
スッキリシリーズは本当に素晴らしい。
いつおもお世話になっております。
select
検索
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は値リストと比較して、全てが真なら真
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 */
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にすると重複行も全てそのまま返す。
ちなみに列の数が同じテーブル同士じゃないとUNIONできない。
UNIONとJOINの違い
UNIONは行を足す。テーブルが下に伸びる。
JOINは列を足す。下にも横にも伸びる。
列は、tableA + tableB
行は、tableA × tableB
になる。
詳しくは下記を参考に。
テーブル結合ってなんなの?
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
※注意
INTERSECT
積集合を求める。
2つのセレクト文に共通する行を集めた集合です。
select 文1
INTERSECT
select 文2
式と関数
式の種類
+
|
足し算
|
数値+数値・日付+数値
|
ー
|
引き算
|
数値ー数値・日付ー日付
|
*
|
掛け算
|
数値*数値
|
/
|
割り算
|
数値/数値
|
|| or +
|
文字列の連結
|
文字列||文字列
|
select文で存在しない列を表示する
出金額, 出金額+200, 固定値という3つの列が表示される。
select 出金額, 出金額+200, '固定値'
from 家計簿;
出金額、200円増しの2つの列が表示。中身は200円増しの値。
select 出金額, 出金額+200 as 200円増し
from 家計簿;
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は無視。
平均値。
数値。
NULLは無視。
count
行数をカウントする。
count(*)// NULLを含めて行数をカウント
count(列) // NULLを無視してカウント
count(distinct 列名) // 重複行を除いたカウント
グループ化
GROUP BY
GROUP BY 列名
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
GROUP BY 費目 --費目列でグループ化する
GROUP BYを増やすと分割されていく
SELECT 費目, 性別, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
GROUP BY 費目, 性別 --費目列でグループ化する
SUM(出金額)
↓
↓費目列を追加
↓
SUM(出金額)→ 費目1のSUM(出金額)
→ 費目2のSUM(出金額)
↓
↓ 性別列を追加
↓
SUM(出金額)→ 費目1のSUM(出金額)→費目1の性別:女のSUM(出金額)
→費目1の性別:男のSUM(出金額)
→ 費目2のSUM(出金額)→費目1の性別:女のSUM(出金額)
→費目1の性別:男のSUM(出金額)
こんな感じでどんどん枝分かれしていくイメージ。
1本の枝が2本、4本、8本と2の2乗で枝分かれしていく。
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 列 any < (副問い合わせ)
where 列 all < (副問い合わせ)
where 列 in(副問い合わせ)
テーブルとして扱う。
select * from (副問い合わせ)
insert into (副問い合わせ) values ~
update (副問い合わせ) set ~
特殊な例
insert文の特殊構文。
INSERT INTO 家計簿集計テーブル(費目, 合計, 平均, 回数)
-----ここから先がVALUES以降の記述に相当する。------
SELECT
FROM 家計簿テーブル
SELECT 費目, SUM(出金額),
AVG(出金額), 0
WHERE 出金額 > 0
GROUP BY 費目
----------------------------------------------
副問い合わせからNULLを除外する
WHERE 費目 IN (SELECT 費目 FROM 家計簿
WHERE 費目 IS NOT NULL)
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
|
シルバー
|
1
|
ブロンズ
|
上記2テーブルを結合
select ID, 名前, ランク
from tableA
join tableB
on tableA.ID = tableB.会員ID
↓
山田さんが二人に増殖してしまう。
ID
|
名前
|
ランク
|
1
|
山田
|
ゴールド
|
1
|
山田
|
ブロンズ
|
2
|
佐藤
|
シルバー
|
結合相手の行がない場合
行数が減る。
⇕結合
上記2テーブルを結合
select ID, 名前, ランク
from tableA
join tableB
on tableA.ID = tableB.会員ID
↓
佐藤氏の消失。
行数が減ると困る時は?
左部外部結合(left outer join)
左表の行数が減らない。
結合相手がない時はNULLが入る。
select 列
from 左テーブル
left outer join 右テーブル
on 結合条件
⇕左部外部結合
↓
佐藤氏無事。
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の主キーを指定して結合することが多い。
主キーは
外部キーとは
外部キーと主キーの例↓
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)
テーブル定義の変更(ALTER TABLE)
列の追加
alter table テーブル名 add 列名 型 制約
列の削除
alter table テーブル名
drop 列名 型 制約
代表的な制約
NOT NULL
NULLは許されない!
UNIQUE
一意。
重複は許されない!
CHECK
ある列の値が妥当かをチェックする。
check(ID >= 0) // IDが0以上かをチェック
主キー(primary key)
外部キー(foreign key)
書き方1
create table テーブル名(
列名 型 REFERENCES 参照先テーブル名(参照先列名))
書き方2
create table テーブル名(
foreign key (
参照元列名) references 参照先テーブル名(参照先列名)
権限の付与・はく奪(GRANT/REVOKE)
grant 権限名 to ユーザー名// 権限の付与
reboke 権限名 from ユーザー名 //権限のはく奪
データベースの知識を深めよう
インデックス(CREATE INDEX)
インデックスとはDBの索引情報。 インデックスをつけると検索が高速になる。
create index インデックス名 ON テーブル名(列名)
インデックスのデメリット
インデックス作成のため、ディスク容量を消費する。
データ変更するときにインデックスも同時に書き換える必要が出る。
そのためINSERT・UPDATE・DELETEの処理が増える。
ビュー
何度も呼び出すテーブルをビューとして登録する。
create view ビュー名 as select文
採番
追加する行に独自の番号を振る。
AUTO_INCREMENT
いつも使ってるやつ。
1から順に自動連番。
SEQUENCE
シーケンスではデフォルト値や、1回の採番で増やす値、最大値などのオプションが使用可能。
バックアップ
ACID特性
原子性:処理が中断しても中途半端な状態にならない。
一貫性:データの内容が矛盾しない。
分離性:複数の処理をどう実行しても副作用がない。
永続性:記録した情報は消滅せず保持され続ける。
2つのファイルをバックアップ
データベースの内容そのもの:処理が重い。低頻度。
ログファイルの内容:処理軽い。高頻度。 DBのログファイルには実行した全てのSQL文が載っている。
ログファイルを使って、障害発生の直前までデータ更新を進めること。
DB設計
DB設計の流れ
概念設計
管理すべき情報がどのようなものかを整理。
情報同士の関連も整理。
論理設計
概念設計で明らかになった各情報について、RDBを使う前提で構造を整理し具体化。
どんなテーブルでどんな列を作るかまでを明らかにする。
ER図を作る。
型や成約などはまだ。
物理設計
すべての列について型、インデックス、制約、デフォルト値など全ての要素を確定させる。
正規化もここでやる。
正規化
正規化の手順
非正規形→第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
|
部署テーブル
社員テーブルから切り残された部分。
これが非正規系から第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
|
部署テーブル(ここはそのまま)
これで第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
|
部署テーブル
部員テーブル
部署番号(FK)
|
社員番号(FK)
|
D1
|
107
|
D1
|
121
|
D1
|
122
|
D2
|
107
|
D2
|
112
|
最終的なER図