スッキリ分かるSQL入門の要約を行いました
(章タイトルや部分的に抜けている記述は自分が必要な知識のみを記載しており
省略しております)
序章
・SQL4大構文
SELECT
UPDATE
DELETE
INSERT
・主キーが備えるべき3つの特性
非NULL性 : 必ず何かしらの値を持っている
一意性 : 他と重複しない
不変性 : 一度決定されたら値が変化する事がない
・charとvarcharの違い
charは固定長
varcharは可変長
例:char(10)←10byteの領域を確保,varchar(10)も同様だが
4バイト占領したとき、char(10)は10バイトメモリを消費するが、
varchar(10)は残り6byteは破棄され4byteのして格納される
・NULLについて
どのような値を格納されてない状態をNULLという
NULLには2パターンの使い方がある
1.格納するべきデータが不明の場合
2.データを格納すること自他が無意味
例:
1.入金が0の場合
2.入金操作をせず、出金操作をした(入金は関係ない)
またNULLはSQL構文で記述不可
NULL判定したい場合は IS NULL演算子、IS NOT NULLをしよう
SELECT * FROM user WHERE name IS NULL;
第五章
・CASE 演算子
列の値や条件式を評価して、その結果に応じて値を変換する
CASE 評価する列や式
例:
SELECT 費目,
CASE 費目 WHEN '居住費' THEN '固定費'
WHEN '水道光熱費' THEN '固定費'
ELSE '変動費'
END AS 出費の分類
FROM 家計簿 ;
(補足: SELECT後のカラム名は要素が一つでもCASEの前には「,(コンマ)」をつけないとエラーになる)
・ユーザー定義関数
SQLは関数を利用できるが、あらかじめ用意された関数のみならず自分で記述手作成できる関数もあるこれをユーザ関数という
またDBMS内に保存し、データベースの外部から呼び出すものを「ストアドプロシージャ」という
・文字列結合には
“||”演算子や”+”演算子を使う
*ただしMYSQLでは両方とも対応しておらず、”CONCAT”を使用する
例:SELECT *,
CONCAT(name, "さん") AS "名前にさん付けします"
FROM users;
SQLにも関数がある
・日付を得る関数
CURRENT_TIMESTAMP … 現在の日時(年/月/日/時/分/秒)
CURRENT_DATE … 現在の日時(年/月/日)
CURRENT_TIME … 現在の時刻(時/分/秒)
第六章&第七章
・DBMS製品には集計関数が5つある
SUM … 各行の値合計を求める
MAX … 各行の値最大値を求める
MIN … 各行の値最小値を求める
AVG … 各行の値の平均値を求める
COUNT … 行数をカウントする
・グループ化
SQLの集計をグループごとに行い、グループごとの集計結果を結果表の形で得られる事
上記の集計関数は戻り値を一行で返してくれるが、
「GROUP BY カラム名」を指定する事でカラム名の種類の分だけグループ分してくれる
例:カラム名:Fruit
りんご
りんご
ばなな
りんご
…の時SELECT COUNT(*) AS 果物 FROM x GROUP BY Fruit;
果物
3
1
となる
・副問い合わせ
SQL文内の一部として登場するSELECT文,丸括弧で記述し文全体が評価され値として扱われる
例:
SELECT 日付,メモ,出金額,
(SELECT 合計 FROM 家計簿集計 WHERE 費目 ='食費')
AS 過去の合計額
FROM 家計簿アーカイブ
WHERE 費目= '食費';
また問い合わせ結果が
1行1列になるもの...単一行副問い合わせ
n行1列になるものを複数行副問い合わせ
複数行副問い合わせは「IN」「ANY」「ALL」演算子などと併せて用いる
・NULLではない値を返すCOALESCE関数
COALESCE(列や式1,列や式2,列や式3)
引数の内、最初に現れたNULLではない引数
これを利用する事でNULLを明示的に表示ができる
第八章
・結合
SELECT 選択列リスト
FROM テーブルA
JOIN テーブルB
ON 両テーブルの結合条件
結合には
左外部結合(LEFT JOIN)
右外部結合(RIGTH JOIN)
完全外部結合(FULL JOIN)
がある
結合時、結合相手がいない行はやNULLの場合、結合結果から消滅する
左外部結合は左表の結合相手がいなくても必ず結合できる
左、右、完全外部結合は本来結果表から消滅してしまう行も強制的に出力できる、これを総称して「外部結合」という
また結合相手が見つからないときに行が消滅してしまう事を「内部結合」という
・結合に関する事
テーブル名の指定は「テーブル名.カラム名」で指定する
第九章
・トランザクションについて
トランザクションに含まれる複数のSQL分がDBMSによって不可分なものとして扱われる声質のことをトランザクションの「原子性」という
またトランザクション処理が正常に進行した場合、仮の書き換えを全て確定する
これを「コミット」という
さらに異常が起き、仮の書き換えをキャンセルしなかった事にすることを「ロールバック」という
トランザクション処理の指示
BEGIN … 開始の指示、この指示以降をSQL分の1つのトランザクションとする
COMMIT … 終了の指示、この指示までを一つのトランザクションとして変更を確定する
ROLLBACK … 終了の指示、この指示までを１つのトランザクションとし、変更の取り消しをする
・3つの副作用
DNMSを同時利用し、なんらかの形でロールバックが発生した時に起こる代表的な副作用
ダーティーリード … 変更確定前の他の人が読めてしまう事
反復不能不可 … SELECT文でテーブルを検索した後、他の人がUPDATE文を実行する事で次に
SELECTをした際に検索結果が異なってしまう事
ファントムリード … 反復不能読み取りと似ており、他の人がINSERT文で行の追加を行うと最初と次のSELECTで取得するけっかのぎょうすうがかわってしまう事
・トランザクションの分離について
上記副作用は、トランザクションによって解決できる
DBMSはここのトランザクションによって「分離性」を維持する為「ロック」と呼ばれるしくみをつかう
これはトランザクションが現在読み書きしている行に鍵をかけ、他の人から読み書きできないようにする事
(ただしトランザクションが完了するまで相手は待たされることになり、ロックを多用するとDBの動作が遅くなる)
またDBMSはどの程度トランザクションを分離するかを「トランザクション分離レベル」として指定できる
↓指定方法
SET TRANSACTION ISOLATION LEVEL 分離レベル名
or
SET CURRENT ISOLATTION 分離レベル名
大抵の場合READ COMMITTEDを選べばいい(らしい)
・明示的なロック
SQL文を使って指定した対象を明示的にロックする事ができる
主なロックの種類
行ロック : ある特定の行だけをロックする
例: SELECT ~ FOR UPDATE (NOWAIT)
表ロック : ある特定のテーブル全体をロックする
例:LOCK TABLE テーブル名 IN モード名 MODE (NOWAIT)
データベースロック : データベース全体をロックする
ロックをかける際の制限の強さを指定できる
排他的ロック … 他からのロックを一切許可しない、主にデータの更新時に利用
共有ロック … 他からの共有ロックを許す特性があり、データの読み取り時に利用
(ただしロックは最小限に、かつ排他的ロックの代わりに共有ロックを使用できないか検討する)
第十章
・SQL4種類の命令
DML( データ操作言語)...
四大命令(SELECT,INSERT,UPDATE,DELETE)を含むデータの格納や取り出し、
更新削除などの命令
TCL(トランザクション制御言語)...
COMMIT,ROLLBACK等トランザクションの開始や終了の命令
DDL(データ定義言語)...
CREATE,ALTER,DROP等テーブルの作成や削除、各種設定の命令
DCL(データ制御言語)...
DMLやDDLの利用に関する許可や禁止の命令を出す
また「３」と「４」の命令に関してはデータベース管理者だけが使う命令となる
・DCLとは
権限を付与する -> GRANT文
例:GRANT ‘権限名’ TO ‘ユーザー名’
権限を剥奪する -> REVOKE文
例: REVOKE ‘権限名’ FROM ‘ユーザー名’
・テーブル作成時にデフォルト値を入れるテーブル作成巣をする際は
作成時にDEFAULTを使用
例:
CREATE TABLE 家計簿(
日付 DATE ,
メモ VARCHAR(100) DEFAULT ‘不明’,
入学金 INTEGERR DEFAULT 0,
出金額 INTEGERR DEFAULT 0,
)
・テーブル定義更新は「ALTER TABLE」 文を使用する
*列の追加
ALTER TABLE テーブル名 ADD 列名 型
*列の削除
ALTER TABLE テーブル名 DROP 列名
・存在しない時のみテーブルを作成する
CREATE TABLE IF NOT EXISTS 家計簿(...);
・存在する時のみテーブルを削除
DROP TABLE IF EXISTS 家計簿;
・DBMSの3つの制約
NOT NULL制約
NULLの格納を許可しない、大抵DEFAULT指定と組み合わせて利用される
例: VARCHAR(10) DEFAULT ‘不明’ NOT NULL
CHECK制約
ある列に格納される値が妥当であるか細かく判定したいときに使用される
CHECKの後ろの括弧内に記述した条件式が真となるようにする
例: INTEGER DEFAULT 0 CHECH( 入金額 >= 0 )
UNIQUE制約
列の内容の重複を防ぐ
例: VARCHAR(40) UNIQUE
主キー制約(PRIMARY KEY)は「NULLも重複も許されない列」ではなく
データを一意に識別する、主キーとしての役割が期待されている
参照整合性の崩壊を防ぐ目的に「外部キー制約(FOREIGIN KYE 制約)」がある
外部キー制約の指定は2パターンあり
①
CREATE TABLE テーブル名(
列名 型 REFERENCES 参照先テーブル名(参照先列名)
…)
②
CREATE TABLE テーブル名(
…
FOREIGIN KEY (参照元列名)
REFERENCES 参照先テーブル名(参照先列名)
)
テーブルの全行を削除する場合、「TRUNCATE TABLE」文が利用される
第十一章
・Indexについて
インデックスは指定した列に対して検索が行われ、存在する列に対して検索が行われるとDBMSは自動的にインデックスの使用を試みるため、高速になる場合が多い
インデックスの作成
CREATE INDEX インデックス名 ON テーブル名(列名);
高速化を測る方法として
「EXPLAIN PLAN」文や「EXPLAIN」文を使うと、指定したSQL文を十個するうプランを調べる事が出来る
・ビューについて
擬似的なテーブルを作成する事が出来る
作成: CREATE VIEW ビュー名 AS SELECT文
削除: DROP VIEW ビュー名
メリット
シンプルで分かりやすいSQL文を書く事が出来る
権限と組み合わせてデータ参照の許可する範囲を柔軟に定めてくれる
デメリット
ビューは実態は単なる名前を付けたSELECT文
そのため相乗以上に高い負荷をDBに対して送っている可能性も考慮しなければならない
・採番テーブルについて
採番テーブルは主キーの値を追加する際、重複が許されないため、最後に使った番号をテーブルに記録しておく事
・ACID特性
原子生 処理が中断しても中途半端な状態にならない事
一貫性 データの内容が矛盾した状態にならない事
分離性 複数の処理を同時実行しても副作用がない事
永続性 記録した情報は消滅せず保持され続ける事
・ログファイルについて
DBのログファイルの事を「REDOログ」や「アーカイブログ」「トランザクションログ」という
このログファイルはこれまでデータベースを更新した全てのSQL文が記録されている
通常のバックアップは1日毎に行い、ログファイルだけを高頻度でバックアップを行うのが一般的(10分や1時間周期)
・ロールバックとロールフォワードの違い
ロールバック
データベースの利用中に実行失敗やデッドロックなどを要因として発生する
ロールフォワード
障害復旧時に行われる処理であるため、滅多に発生しない
第十二章
・データベース構築のおおまかな流れ
①お客さんの要件をヒヤリング
②データベース設計作業
概念設計
論理設計
物理設計
③DDL
概念設計
顧客から要件をヒヤリングし、エンティティへと導き出す作業をする
概念設計では要件を実現するために中層的な概念としてどのように情報の塊を管理しなければならない
情報の塊を「エンティティ」という
エンティティ :「テーブル」のようなもの
属性 : 「テーブル」の「列」のようなもの
関連 : 「リレーションシップ」のようなもの
概念設計の成果として「ER図」と呼ばれる図にする事が一般的
以下記載ルール
入出金明細(エンティティ名)
| 入出金明細(主キー) | ←(属性)
|入出金行為ID(FK) ←(外部キ |←(属性)
|費目ID |←(属性)
|金額 |←(属性)
論理設計
概念上のエンティティをリレーショナルデータモデルで取り扱いやすい形のテーブルに変形させる
論理設計での大まかな作業の流れ
①多対多の分解
多対多になっているテーブルを分化して中間テーブルを追加する事
(連関エンティティともいう)
②キーの整理
全てのテンティティのキーについて整理、確認する事
主キーがつけられないエンティティには人工主キーをつける対応をする
③正規化
矛盾したデータを格納できないよう、テーブルを複数に分割していく作業
(個人的に混乱してきたので一旦DBの1対多や多対多についてまとめ)
「1対多」の関係は、あるテーブルの1つのレコードが、別のテーブルの複数のレコードに関連していること
「多対多」の関係は、あるテーブルの複数のレコードが別テーブルの複数のレコードに関連している事
(例:User情報を格納するUserテーブルと、そのユーザが受講する科目を選ぶCourseテーブルがあるとして、Userテーブルには複数人がいて複数人のコースを選ぶ
この状態が多対多)
[参考URL:https://qiita.com/ramuneru/items/db43589551dd0c00fef9]
・正規化の手順
正規化によってテーブルが分割された事が「正規形」」という
・正規化の流れ
非正規形→第一正規型→第二正規型→第三正規化
・非正規形とは
一つのセルに対して複数行が付属している事
セル結合している事
繰り返し同じカラムが登場している
第一正規形
テーブルすべての行と列には1つずつの値が入ってるべきで
繰り返し列やセル結合を無くすことが第一正規化
(レコードの横の長さを統一する事も必要となってくる)
第二正規化
1.複合主キーを持つテーブルが、非キー列は複合主キー全体に関数従属するべきである
そのため「複合主キーの一部の列に対してのみ関数従属する列」が含まれてはなならない
2.また複合主キー主キーの一部に関数従属(部分関数従属)する列がある場合は切り出す
例:[入金行為ID]と[費目ID]の二つの複合主キー対して[費目名]が[費目ID]として部分関数従属している場合、[費目名]を別テーブルに切り出し、[費目ID]を主キーとして結合する
関数中属性とは...
ある列Aの値が決まれば列Bの値も決まるという関係のこと
列Bは列Aに関係従属しているという
テーブルにおける理想的な関数従属は全ての非キー列は、主キーに綺麗に関係従属していることである
第三正規化
非キー列は主キーに直接関数従属するべきなので、「主キーに関数従属する列にさらに関数従属する列」の存在をなくす
つまり間接的に主キーに関数従属する列を切り出す事
例:テーブルA
[入出金行為ID] [日付] [利用者ID] [利用者名]
[ 41000 ] [2024/01/03] [1] [TANAKA ]
がある場合、利用者名は利用者IDに関係従属し、利用者IDは主キーである入出金行為IDに関係従属している = 利用者名は間接的に主キーを関数従属しているので
[利用者名]を別テーブルに切り出し、[利用者ID]をそのテーブルの主キーとする
↓第三正規化後
テーブルA:
[入出金行為ID] [日付] [利用者ID]
[ 41000 ] [2024/01/03] [1]
テーブルB:
[利用者ID] [利用者名 ]
[1] [TANAKA]
物理設計
論理設計後、どのDBMS製品を利用するか確定した後上でDBMS製品がサポートする型や制約、インデックス、利用するハードウェアなどの制約を考慮し設計する
・物理設計の内容
最終的なテーブル名、列名の決定
列の型の決定
制約、デフォルト値を決定
インデックスの決定
その他(意図的な正規化崩し、ビュー作成等)
テーブル名は論理設計まで日本語で行われる事が多いが、最終的にはアルファベットを用いた名前をつける事が多い
またDB内に作られるテーブル名や列名を「物理名」という
反対に論理設計まで利用してきた名前は「論理名」