DB設計で学んだこと

データベースとは何か

大量の情報を保存し、、コンピュータから効率よくアクセスできるように加工したデータの集まりのこと

DBMSとは何か

データベースを管理するコンピュータシステムのこと

RDBMSとは何か

DBMSの中でもリレーショナルデータベースを扱うシステムのこと代表的なものとして。MySQL,PostgreSQL,DB2がある。

SQLとは何か

データベースの定義や操作を行うデータベース言語一つで最も普及している。

テーブルとは何か

データを管理している二次元の表のこと

カラムとは何か

列のこと。テーブルに保管するデータ項目を指す。

レコードとは何か

行のこと。一件のデータのこと。

クエリとは何か

データベースに対する命令文のこと

実行計画とは何か

SQLの内部動作を調べる手段、DBMSがSQL文を実行する際にどのようなアクセス経路でデータを取得し、どのような計算を行うことが最も効率的か判断するために作る計画書。

N+1問題とは何か

ループ処理の中でSQLを発行するとSQLクエリが大量増殖してしまう問題のこと。

N+1問題の主要な解決方法

①JOINする：
SQLのjoinを使って一本釣りをする
②Eager Loadする：
あらかじめガバッとSELECTしておく

テーブルの作成：create <テーブル名>

ユーザーの登録、変更、削除

ユーザー作成：CREATE USER '[username]'@'localhost' IDENTIFIED BY '[password]';
ユーザー情報一覧の表示：SELECT user, host FROM user;
アクセス権の付与：GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION;
権限のリロード：FLUSH PRIVILEGES;

CHARとVARCHARの違い

CHAR(固定長)あらかじめ格納するデータの領域が確保される。（ディスク使用量が多くなる、パフォーマンスは良い）
＊末尾のスペースは削除される
VARCHAR(可変長)データに応じて確保されるディスク領域を調整する。（ディスク使用量が少なくなる、パフォーマンスが悪い）
＊末尾のスペースは保存される

データベースの基本機能

①データの検索と更新
②同時実行制御
同時実行制御とは、データベース管理システム（DBMS）が複数のトランザクション処理を同時に実行しつつデータの整合性を保つ制御のこと。

ロック：同時実行制御を実現する主たる手段,DBのテーブルや行を固定して、別のトランザクションからテーブルや行の参照・更新ができないようにすること

・共有ロック：他のトランザクションから参照はできるが更新はできなくなるロック
・占有ロック：他のトランザクションから参照も更新もできなくなるロック

＊複数のトランザクションが共有ロック、排他ロックをかけようとした場合の処理として、ハイタロックは一つのトランザクションからしかかけられない。

＊トランザクションとロック
トランザクションを開始して、テーブルの更新・削除・ロックに関連する処理を実行するとトランザクションが終了するまでテーブル、レコードがロックされる。

＊ロックの種類：テーブルにロックをかけるテーブルロックと行にロックをかける行ロックがある。

トランザクション処理は複数のデータ処理を一体不可分の単位として扱う実行方式で、実行の前後でデータに矛盾が無いことが要請される。実際のコンピュター上では複数のトランザクションを矛盾なく実行する必要があ理、他のトランザクション処理によって、処理に矛盾が起こらないように管理しなければならない。原理としてはそれぞれ順番に実行すればいいのだが、性能が極端に下がり、実行するのは難しい。そこでアクセスしようとする資源（テーブルやレコードなど）を一時的に封鎖（ロック）して占有し、処理中は他のトランザクションのアクセスを禁じる「排他制御」が行われる。

③耐障害性
④セキュリティ

ソフトウェアとデータベースの関係性

・ソフトウェアは階層の上から以下の3つに大別される
①OS(オペレーティング・システム)
②ミドルウェア（ここにデータベースが含まれる）
③アプリケーション

アーキテクチャの歴史

①スタンドアロン：
②クライアント/サーバ：インターネットからデータベースに直接アクセスできる
③Web3：クライアントとサーバー層の間に、「Webサーバー層」と「アプリケーション層」が追加された。

＊レプリケーション（複製）：サーバー部分が冗長化されても、ストレージ部分は冗長化されず、データが冗長化されない問題がある中で、データベースサーバとストレージのセットを複数用意する。
Active内のデータは常に更新しているため、stand byの方も更新を反映しないと生合成が取れなくなる。

データベースの階層

データベースは階層に分かれている。フォルダに相当する「スキーマ」。テーブルは実際には「いくつかのスキーマの中に格納される」という形をとっている。スキーマの上位には「データベース」（ディレクトリ）がある。最上位には「インスタンス」（ルートディレクトリ？）という概念がある。これは物理的な概念で、DBMSが動く時の単位。

＊MySQLではスキーマとデータベースを区別していない。

トランザクションとは：データベースに対する一つ以上の更新をまとめて呼ぶ時の名称。(一連の複数のデータ処理を実行中に中断されては困るような時用いる)

トランザクション(ACID特性）

①Atomicity(原子性）：一連の操作が「全部成功」するか「全部失敗」するか
②Consistency(一貫性）：一連のデータ操作の前後で一貫性を保つ
③isolation(独立性）：一連の操作が複数のユーザーから同時に行われる際に、「それぞれの処理が矛盾なく行われることを保証」
④Durability(持続性）：一連の処理が終了し完了したのちに結果が失われない。

トランザクション分離レベル:③isolaitonに関する概念、他のトランザクションへの影響度

ダーティリード (Dirty Read)

あるトランザクションがコミットされる前に別のトランザクションからデータを読み出せてしまう現象。

ファジーリード/ノンリピータブルリード (Fuzzy Read / Non-Repeatable Read)

あるトランザクションでデータを複数回読み取っている途中で、でデータを更新してコミットした場合、一回めとは違う結果のデータを読み取ってしまう問題。(非再現リードとも呼ぶ)

ファントムリード (Phantom Read)

あるトランザクションを最初に読み込んだ時、選択できるデータが現れたり、消えたりする現象。

これらの問題をどの程度許容するかがトランザクション分離レベル。

READ UNCOMMITTED：コミットされていない変更を他のトランザクションから参照できる

ダーティリード、ファジーリード、ファントムリードが全て発生する

READ COMMITTED：コミットされた変更を他のトランザクションから参照できる

Oracle、PostgreSQL、SQL Serverのデフォルトのトランザクション分離レベル
ファジーリード、ファントムリードが発生する

REPEATABLE READ：コミットされた追加・削除を他のトランザクションから参照できる

MySQLのデフォルトのトランザクション分離レベル
ファントムリードが発生する
MySQL(InnoDB)はREPEATABLE READでもファントムリードが発生しない

SERIALIZABLE：強制的にトランザクションを順序付けて処理する(直列化)

読み取るすべての行に共有ロックをかける
ダーティリード、ファジーリード、ファントムリードが全て発生しない

「列」とは固体の「属性」である。

オブジェクト指向的に考えると、テーブル名がクラス、列がインスタンスに相当する。

基本的なコマンド

・データベースからデータを取得：　SELECT 列x・・・　FROM table名;
・列に列名をつける：select 列x as 変更名, 列y as 変更名 from table名;
・列の追加：select 列x, price * 1.08 as 税込価格 from table;
＊税込み価格の計算など追加で列を入力できる

・条件の追加：末尾にwhere 条件
（例）
①idが1か2か3: select * from products where id *in(1,2,3);
②priceがnulではない：　select * from products where price *is not null;
③priceが1000と1900の間：　select * from products where price *between 1000 and 1900;

・パターンマッチングによる絞り込み
・例えば名前に"中"が含まれる人を抽出。likeを使う
select * from users where last_name like '中%';

・取得件数を制限;末尾にlimit
・10件取得　　select * from products limit 10;

集約関数：　値を集約するために使う関数

・合計値：　sum(expr)
・平均値: avg(expr)
・最小値：min(expr)
・最大値：max(expr)
・対象行の行数を数える：count(expr)
・重複を排除した形で数える：　count(distinkt expr)

count()だとNULLも集計される！

＊集約関数においては基本的にnullは無視される
*AVGでもNULLは無視されるが、COALESCEを使えばNULLも値に入る。
・期間ごとに集約するgroup by関数

・集約結果をさらに絞り込む　having；テーブルのデータを集約した結果に対して条件式を適用する時に用いる。--group byで絞り込んだのに条件をつけて絞りこむイメージ
＊記述順序：select, from, join, where, group by, having, limit

データの並び替え：order by 列名や式　並び順(asc, desc)

*order byはカンマ区切りで複数指定できる

文字列連結

concat(文字列１、文字列２、文字列３）

テーブルの結合：テーブル同士をある条件で結合することにより*正規化なしの状態を作ること

・テーブルを正規化するメリット
①データ管理が容易になる　②データ容量の削減

テーブルのエッセンス

*①テーブルとは集合である　*②テーブルとは関数である

・主キー：一つの行を特定できる列のこと。一つのテーブルに必ず必要。また重複してはいけない。。NULLが存在してもいけない。
・外部キー：他のテーブルとの関連付けに使う列。関連づけられたテーブルでは主キーとなる。

正規形

①第一正規形：テーブルのセルに複合的な値を含んでいない。
②第二正規形：第1正規形の表から、部分関数従属している列が切り出されたもの
③第三正規形：第2正規形の表から、主キー以外の列に関数従属している列が切り出されたもの

・リレーションシップの種類①1対多②多対多③1対1

・内部結合でテーブルを結合する：　inner join お互いに一致している行が結合の対象
select テーブル名1.列名, テーブル名2.列名　from　テーブル名1 inner join テーブル名2 on table名1.列名　＝ テーブル名2.列名

・外部結合でテーブルを結合する：outer join　欠落のあるデータを取り扱う
・left outer join 左側（fromで最初に書いたテーブル）をマスタにする
・right outer join 右側（fromで最初に書いたテーブル）をマスタにする

INNER JOINは、共通の値のみを結合するのに対して、OUTER JOINは、一方のテーブルにしか存在しない値も含めて結合することができます。

＊ポイント：joinは複数回使える

ビューとは

データそのものを保存するのではなく、データを取り出すselect分だけを保存する。ビュー自体はデータを持たない。
・記憶装置の容量を節約できたり、よく使う文をselect文としてビューにしておくことで使い回しができる。
：create view ビュー名（<ビューの列名1><ビューの列名2>,..) as

viewの削除

drop view ビュー名

集合演算子

構造のよく似た複数のテーブルに対してSELECTでレコードを取得して取得結果を組み合わせるSQL

UNION, UNION ALL 和集合を求める

EXISTSとは

他のテーブルに値の存在する行のみ抽出するSQL.サブクエリないでメインクエリの表や列を利用する相関副問い合わせの一つ。
EXISTSでは、EXISTSの後のサブクエリが何らかの値を返すレコードだけを取り出す。NULLの場合は値が返されない。
NOT EXISTS句では、サブクエリで値が返されないレコードを取得する(NULL)のものも返される

*NOT IN と　NOT EXISTSの結果は異なる（特にNULLが存在する場合）
NOT INは比較の結果、偽になるレコードを取り出す。
ただ大体の場面においてEXISTSとINは同じ結果になる。

サブクエリとは　

ある問い合わせの結果に基づいて異なる問合せを行う仕組み、複雑な問い合わせができる。fromの後

スカラサブクエリ

必ず一行一列だけの戻り値を返すサブクエリのこと。where, havingの後などどこにでも入れられる。

条件分岐case式

case when 条件式1 then 値1...
end

自己結合：同一のテーブルを結合する結合方法＊同じテーブルなので別名をつける

交差結合：2つのテーブルのデータの全ての組み合わせを取得するSQL

(細かな困りごと）

年月日時間まで入っているデータから年と月だけ抽出したい

date_format(order_time, '%Y%m') *order_timeは年月日時間が入っているデータ

新規の行を追加したい

insert into テーブル名（列1,列２,..)values (列１,列２、、）、

####　レコードの更新をしたい
update テーブル名　*set 列１　＝ 値1, [列２＝値2]　「where 条件」

データベースからファーストネームに "vi" が含まれる人を検索したい

where first_name *like '%vi%'

データベースから従業員番号が 10011, 10021, 10031 のいずれかに合致する人のデータを取得したい

SELECT * FROM employees WHERE emp_no *IN (10011, 10021, 10031);

データベースから誕生日が 1959 年 1 月の人のレコードを取得したい

SELECT * FROM employees WHERE birth_date LIKE '1959-01-%';

データベースから上から3番目と4番目だけ取得したい。(limitとOFFSET)

OFFSETを使用すると、クエリの結果セットの先頭行から何行目から結果を返すかを指定できます。

SELECT処理の実行結果をテーブルに挿入する。

INSERT INTO table_name(column_list)

*別のテーブルを作成して、SELECT処理の実行結果を挿入する場合
CREATE TABLE SELECT

論理設計のグレーノウハウ

・主キーが決められないケース
①そもそも入力データに主キーにできるような一意キーが存在しない
②一位キーはあるが、サイクリックに使いまわされている
③一意キーはあるが、途中で指す対象が変化する

⇨代理キーによる解決、自然キーによる解決（タイムスタンプの導入、インターバルの導入）
＊可能な限り自然キーを使う

・アドホックな集計キー