データベース研修

パーソルキャリア株式会社 テクノロジー本部 エンジニアリング統括部 サービス開発部
※本資料は2023年3月時点の情報であり、当該部門における2023年新卒の研修教材です。

Transcript

1. データベース研修 リレーショナルデータベース(RDB) パーソルキャリア株式会社 テクノロジー本部 エンジニアリング統括部 サービス開発部 ※本資料は2023年3月時点の情報であり、2023年新卒の研修教材です。
2. 目次 ・データベースとは ・RDBMS ・MySQL ・SQL ・テーブル設計 ・参考リンク
3. データベース
4. データベースとは ・データベース（DB; DataBase） 　・データの集合そのもの 　　・データ（data）：ディジタル化された情報 　　・ベース（base）：⼀台，基盤，基地 ・構造化されたデータの集合です。
5. webサービスとデータベース ・webサービスではたいてい何らかのデータを扱うことになります。 ・例えば名前やメールアドレス、テキストコメントなどwebフォームから入力して送信し たり、そのデータを別の画面で表示したり ・そういったデータを管理するためにデータベースを使用します
6. webサービスとデータの操作 ・一般的にデータに対する主な操作として「Create（生成）」「Read（読み取り）」「Update （更新）」「Delete（削除）」があり、それらを総称してCRUD(クラッド)と呼ばれます ・RESTfulなWEBサービスは、特定のリソースにHTTPメソッドでアクセスすることでデー タの受信や操作を行います ・POST,GET,PUT,DELETEのメソッドでCRUDの性質を満たします cf. RESTful APIとは何か？ https://qiita.com/NagaokaKenichi/items/0647c30 ef596cedf4bf2
7. データベースの種類 種類 説明 階層型データベース データを階層構造（木構造）で表す リレーショナルデータ ベース（Relational Database：RDB） Excelのような2次元表の形式でデータを管理 最も広く利用されている オブジェクト指向データ ベース データとそれを操作する処理をまとめたオブジェクトを管理する データベース XMLデータベース XML形式のデータを扱う キー・バリュー型データ ストア 検索に使うキー（Key）と値（Value）の組み合わせで管理 プログラムでいう「連想配列」や「ハッシュ」的なイメージ 他にも色々...
8. RDBMS
9. RDBとは ・Relational Database：RDB ・表形式でデータを表す id 名前 年齢 性別 1 田中太郎 24 男 2 花沢花子 26 女 3 鈴木次郎 31 男
10. DBMSとは ・データベース管理システム （DBMS; DataBase Management System） 　・ データベースを操作し管理するために必要なソフトウェア（システム） ・データベースを管理し、外部のソフトウェアからの要求に応えてデータベースの操作を 行う専門のソフトウェア
11. RDBMSとは ・データを表の形式で永続化し、かつデータ間の関連もまた表の形式で永続化できる DBMS 関係データベース管理システム - Wikipedia
12. RDBMSのデータベースとテーブル データベース テーブルA テーブルB データベースという器の中にテーブルと呼ばれるデータを格納する表がある
13. テーブルの構造 id 名前 年齢 性別 1 田中太郎 24 男 2 花沢花子 26 女 3 鈴木次郎 31 男 行(レコード) フィールド 列(カラム) テーブルは列(カラム)と行(レコード)でできており、レコードの中の入力項目はフィールド と呼ばれる
14. 代表的なRDBMS ・Oracle Database：Oracle社のRDBMS ・SQL Server：Microsoft社のRDBMS ・DB2：IBM社のRDBMS ・PostgreSQL：オープンソースのRDBMS ・MySQL：オープンソースのRDBMS（2010年からOracle社が開発元） サビ開では主にMySQLが使用されている
15. MySQL
16. MySQLとは 公式サイト https://www.mysql.com/jp/ ・オープンソース SQL データベース管理システム ・データベースはリレーショナルデータベース ・Facebook、Twitter、YouTubeなど、多くの人気Webサイトでも使用されている
17. MySQLの利用状況 ・世界的にはMySQLの方がライバルであるPostgreSQLより多く使用されている ・ウェブサイトの構築に用いるソフトウェア環境として、LAMP (Linux, Apache, MySQL, Perl・PHP・Python) の略称が知られている ・Yahoo!、Facebook、Twitterなどの巨大なウェブサイトでの適用例も多く、Webアプリ ケーションのXOOPS、WordPress、SugarCRMなどCMSも、バックエンドのデータベー スとしてMySQLを利用している
18. MySQLのインストール 以下のサイトを参考にhomebrewでインストールしてください $ brew update $ brew install mysql https://qiita.com/kobayashi-m42/items/dae22e49ab060adf920f 実践
19. MySQLのサーバ起動とログイン ・サーバ起動 ・ログイン $ mysql.server start $ mysql -u root ・mysqlのプロンプト(mysql>)が表示される サーバーを終了させるときは、 $ mysql.server stop 実践
20. RDBMSのシステム構成 クライアント (データを利用する プログラム) RDBMSサーバ (データベースを 読み書きする プログラム) データベース (ハードディスク等に 保存されたデータ群 ) 命令 (SQL) 命令結果 (読み出し データ等) cf. https://codezine.jp/article/detail/12216
21. コマンドの実行 > SELECT 'hello world!' AS info; ・hello world!が表示される 実践
22. ファイルを読み込んでsqlを実行 > SOURCE info.sql; SELECT 'hello world!' AS info; info.sql cf. https://dev.mysql.com/doc/refman/8.0/en/mysql-batch-commands.html
23. SQL
24. SQLとは ・データベースを操作するために開発された言語 ・SQLにはISO（国際標準化機構）で定められた標準規格がある 　・ただし、実際にはRDBMSごとに違い(方言)がある部分もある ・「Structured Query Language」の略 　・ただし標準規格の定義としては略語ではなくSQLという名前
25. SQLの種類 ・DDL(Data Definition Language) ・DML(Data Manipulation Language) ・DCL(Data Control Language) ・TCL(Transaction Control Language)
26. DDL(Data Definition Language) データベースオブジェクトの生成や削除変更を行うコマンド CREATE データベース、テーブル、ユーザ等のオブジェクトを新規作成する DROP データベースからオブジェクトを削除する ALTER 既にあるオブジェクトの定義を変更する
27. DML(Data Manipulation Language) テーブルに対するデータの取得・追加・更新・削除を行うコマンド SELECT データベースからデータを検索する INSERT テーブルにデータを追加する UPDATE テーブル内のデータを更新する DELETE テーブル内のレコードを削除する
28. DCL(Data Control Language) パーミッションや権限に関連するコマンド GRANT データベースへのアクセス権を定義する REVOKE GRANTで与えたアクセス権を取り下げる
29. TCL(Transaction Control Language) データベース内におけるトランザクションを扱うコマンド START TRANSACTION または BEGIN 新しいトランザクションを開始 COMMIT 現在のトランザクションをコミットして、その変更を永続的なものにしま す ROLLBACK 現在のトランザクションをロールバックして、その変更を取り消します
30. 予約語と識別子 ・MySQLには、SELECT、FROM、 INSERT、INTO、JOINなど、特定の語句が 「予約語」として指定されている ・データベース名やテーブル名、カラム名などを指定する語句のことを 「識別子」と呼ぶ 予約語一覧 https://dev.mysql.com/doc/refman/8.0/en/keywords.html > SELECT id, name FROM users;
31. 予約語と識別子 ・MySQLのテーブル名やカラム名などの「識別子」には、基本的に「予約語」が使えない ・識別子に予約語と同じ単語を使う場合はバッククォート(`)で括る必要がある ・トラブルの原因になるので、アンチパターンです > SELECT id, `order`, created_at, updated_at FROM items ORDER BY `order`;
32. マニュアル ・公式 　・https://dev.mysql.com/doc/refman/8.0/en/ ・MySQLの使い方 　・https://www.dbonline.jp/mysql/ 公式のマニュアルは情報量が多いので、 1度に全部読み込むのではなく、困ったときに都度見に行けば良いと思います。
33. データベース、テーブルの追加削除
34. データベースの作成 mydbという名前のデータベースが作成される https://www.dbonline.jp/mysql/database/index1.html > CREATE DATABASE mydb; 実践
35. データベースの一覧を見る データベースの一覧が表示される https://www.dbonline.jp/mysql/database/index2.html > SHOW databases; 実践 ちなみに... 画面をクリアにするときは、 > system clear;
36. テーブルの作成 ・USEで使用するデータベースを指定する ・usersというテーブルが作成される ・id,nameというカラムが作成される ・AUTO_INCREMENTの設定で、データ作成時に idは自動でインクリメントされたものが設定されるようになる https://www.dbonline.jp/mysql/table/index1.html > USE mydb; > CREATE TABLE users ( id INT AUTO_INCREMENT, name TEXT, PRIMARY KEY (id) ); 実践
37. 主キー(PRIMARY KEY) 格納されたすべてのデータの中から 1 つのデータを特定したいときに、 1 つまたは複数 のカラムの値を検索してデータを特定しますが、その時に最も適したカラムに設定される のがプライマリーキーです。 ・PRIMARY KEYの値の条件 ・NULL禁止 ・値の重複なし
38. データ型(文字) データ型 説明 CHAR(M) 格納時に必ず、Mで指定された長さになるように右側がスペースで埋められる固定長文字列。Mの範囲は0から255 。 VARCHAR(M) 可変長文字列。M はカラムの最大長文字数。Mの範囲は0から65535。 TEXT 最大長が 65535文字のテキスト文字列。値にマルチバイト文字が含まれる場合、有効な最大長は少なくなります。 BLOB 最大長が 65535文字のバイナリ文字列。 https://dev.mysql.com/doc/refman/5.6/ja/string-type-overview.html
39. データ型(数値) データ型 説明 TINYINT [UNSIGNED] 非常に小さい整数。 符号付きの範囲は-128から127。符号なしの範囲は0から255。 INTEGER [UNSIGNED] 普通サイズの整数。 符号付きの範囲は-2147483648から2147483647。 符号なしの範囲は0から4294967295。 BIGINT [UNSIGNED] 大きい整数。 符号付きの範囲は-9223372036854775808から9223372036854775807。 符号なしの範囲は0から18446744073709551615。 BOOLEAN 0 or 1が格納されます。0は false, 1はtrueと見なされます。 FLOAT 浮動小数点数。 DOUBLE 倍精度の浮動小数点数。 https://dev.mysql.com/doc/refman/5.6/ja/numeric-type-overview.html
40. データ型(日付/時間) データ型 説明 DATE 日付です。サポートしている範囲は '1000-01-01' から '9999-12-31' です。 DATETIME 日付と時間の組み合わせです。 サポートしている範囲は '1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999' です。 TIMESTAMP タイムスタンプ。TIMESTAMP 値はエポック ('1970-01-01 00:00:00' UTC) から (‘2038-01-19 03:14:07’) の秒数として格 納されます。（2038年問題） TIME 時間。範囲は、'-838:59:59.000000' から '838:59:59.000000' です。 https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-type-overview.html
41. テーブルの一覧をみる テーブルの一覧が表示される https://www.dbonline.jp/mysql/table/index2.html > SHOW tables; 実践
42. テーブル構造を見る ・usersのテーブルを作成するためのCREATE文が表示される https://www.dbonline.jp/mysql/table/index2.html ・DESCRIBE users; でもテーブルー構造を表示できる。 > SHOW CREATE TABLE users; 実践
43. > ALTER TABLE users ADD COLUMN age INTEGER AFTER name; テーブル構造を変更する ・usersテーブルのnameの次にageというカラムを追加する ・追加(ADD)、変更(CHANGE)、削除(DROP)などがある https://www.dbonline.jp/mysql/table/index18.html 実践
44. テーブルを削除する usersテーブルが削除される https://www.dbonline.jp/mysql/table/index4.html > DROP TABLE users; 実践 > SHOW tables と打つと、usersテーブルが削除されていることを確認できるはず
45. データベースを削除する mydbのデータベースが削除される https://www.dbonline.jp/mysql/database/index5.html > DROP DATABASE mydb; 実践 > SHOW databases と打つと、mydbが削除されていることを確認できるはず
46. データの 追加・取得・更新・削除
47. 下準備 > CREATE DATABASE mydb; > USE mydb; > CREATE TABLE users ( id INT AUTO_INCREMENT, name TEXT, age INTEGER, PRIMARY KEY (id) ); 実践
48. データを追加する ・usersテーブルにname = ‘Suzuki’, age=20のデータとname = ‘Tanaka’, age=22の データを追加する ・テーブルのカラムを全て設定する場合、(name, age)の部分は省略できる ・カラムを指定しなかった場合は指定しなかったフィールドにはNULLが入る https://www.dbonline.jp/mysql/insert/index1.html > INSERT INTO users (name, age) VALUES ('Suzuki', 20), ('Tanaka', 22); 実践
49. データを取得する ・１行目はusersテーブルの全てのデータを取得して表示する 　・*は全てのカラムを指定する ・２行目はusersテーブルの全てのデータのidとnameを取得して表示する https://www.dbonline.jp/mysql/select/index1.html > SELECT * FROM users; > SELECT id, name FROM users; 実践
50. データを更新する ・id = 1のレコードのnameを’Sato’に更新する ・WHERE で条件を指定する。 ・条件を指定しない場合はテーブルの全てのデータが更新されてしまう ・条件の指定し忘れを防ぐために、 safe-updatesモードを使用すると安全 https://www.dbonline.jp/mysql/insert/index7.html http://download.nust.na/pub6/mysql/doc/refman/5.1-olh/ja/safe-updates.html > UPDATE users SET name = “Sato” WHERE id = 1; 実践
51. データを削除する ・id = 2のデータが削除される ・WHEREで条件を指定する ・条件を指定しない場合はテーブルの全てのデータが削除されてしまう ・条件の指定し忘れを防ぐために、 safe-updatesモードを使用すると安全 https://www.dbonline.jp/mysql/insert/index10.html http://download.nust.na/pub6/mysql/doc/refman/5.1-olh/ja/safe-updates.html > DELETE FROM users WHERE id = 2; 実践
52. データの条件付き取得 詳しくはSQL研修で！
53. 条件を指定してデータを取得 ・ageが 22と等しいデータを全て取得して表示する ・条件の種類については後述 https://www.dbonline.jp/mysql/select/index4.html > SELECT * FROM users WHERE age = 22;
54. 比較演算子 演算子 使用例 意味 = a = b a と b は等しい != a != b a と b は等しくない < a < b a は b よりも小さい <= a <= b a は b よりも小さいか等しい > a > b a は b よりも大きい >= a >= b a は b よりも大きいか等しい IS NULL a IS NULL a はNULL IS NOT NULL a IS NOT NULL a はNULLではない BETWEEN AND a BETWEEN min AND max a は minより大きいか等しくmaxより小さいか等しい LIKE a LIKE pattern SQL の単純な正規表現比較を使用したパターンマッチング https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
55. 論理演算子 演算子 使用例 意味 AND a AND b a と b が共にTRUEの時にTRUE OR a OR b a か b の少なくとも1つがTRUEの場合にTRUE XOR a XOR b a と b が共にTRUEまたはFALSEの時にFALSE a または b がNULLのときはNULL NOT NOT a a がTRUEならFALSE、a がFALSEならTRUE https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html > SELECT * FROM users WHERE (age > 20) AND (name = ‘Suzuki’);
56. ・usersのデータをageの降順で取得して表示する 　・ASC：昇順(デフォルト) 　・DESC：降順 ・カンマ区切りで複数指定することも可能 https://www.dbonline.jp/mysql/select/index11.html ※文字列をソートする場合は注意が必要 参考：https://style.potepan.com/articles/26546.html 取得するデータをソートする > SELECT * FROM users ORDER BY age DESC;
57. 取得データの開始位置と行数の上限を設定 ・usersテーブルのデータを開始位置2から最大4つ取得する ・OFFSETは0から始まる https://www.dbonline.jp/mysql/select/index12.html > SELECT * FROM users LIMIT 4 OFFSET 2;
58. グループ化 ・ageが同じ値のデータをグループ化し、ageごとの個数をカウントする 　・COUNT関数で個数がカウントされる https://www.dbonline.jp/mysql/select/index9.html > SELECT age, COUNT(age) FROM users GROUP BY age;
59. 集計関数 ・SQLでテーブルの値を集計するために使う機能 ・対象データがNULLの場合は無視される 名前
 説明
 AVG()
 引数の平均値を返します
 COUNT()
 返された行数のカウントを返します
 GROUP_CONCAT()
 連結された文字列を返します
 MAX()
 最大値を返します
 MIN()
 最小値を返します
 SUM()
 集計を返します
 https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
60. テーブルの結合 詳しくはSQL研修で！
61. テーブルを結合してデータを取得 ・usersテーブルのidとarticlesテーブルのuser_idが一致したデータを結合して表示す る https://www.dbonline.jp/mysql/join/ > SELECT * FROM users INNER JOIN articles ON users.id = articles.user_id;
62. 内部結合(INNER JOIN) 指定したそれぞれのテーブルのカラムの値が一致するデータだけを取得 id name age 1 佐藤 25 2 鈴木 18 3 田中 20 4 渡辺 18 5 斎藤 35 id user_id article 1 3 テストです 2 1 コメント 3 1 あああ 4 6 何か id name age id user_id article 1 佐藤 25 2 1 コメント 1 佐藤 25 3 1 あああ 3 田中 20 1 3 テストです users articles
63. 外部結合 指定したそれぞれのテーブルのカラムの値が一致するデータだけではなくどちらかの テーブルにだけデータがある場合も合わせて取得 LEFT JOINとRIGHT JOINの２つがある
64. 外部結合(LEFT JOIN) 左側のテーブルにしかないデータも取得する id user_id article 1 3 テストです 2 1 コメント 3 1 あああ 4 6 何か id name age id user_id article 1 佐藤 25 2 1 コメント 1 佐藤 25 3 1 あああ 2 鈴木 18 NULL NULL NULL 3 田中 20 1 3 テストです 4 渡辺 18 NULL NULL NULL 5 斎藤 35 NULL NULL NULL id name age 1 佐藤 25 2 鈴木 18 3 田中 20 4 渡辺 18 5 斎藤 35
65. 外部結合(RIGHT JOIN) 右側のテーブルにしかないデータも取得する id user_id article 1 3 テストです 2 1 コメント 3 1 あああ 4 6 何か id name age 1 佐藤 25 2 鈴木 18 3 田中 20 4 渡辺 18 5 斎藤 35 id name age id user_id article 3 田中 20 1 3 テストです 1 佐藤 25 2 1 コメント 1 佐藤 25 3 1 あああ NULL NULL NULL 4 6 何か
66. 別名をつける(AS) ・カラム名やテーブル名などにASを使って別名をつけることができる > SELECT usr.id AS user_id, atl.id AS article_id, usr.name, atl.article FROM users AS usr INNER JOIN artictles AS atl ON usr.id = atl.user_id;
67. 制約
68. 整合性維持機能 ・レコードのカラムに登録できるデータに制約をつけることで、不整合なデータの登録を 防ぐ 　・null制約 　・ユニーク制約 　・PRIMARY KEY制約 ・外部キー制約
69. NULL制約とデフォルト ・nameにNOT NULL制約を追加 　・nameには NULL を格納できなくなる 　・nameに値を指定しなかった場合、DEFAULTで指定した値が入る https://www.dbonline.jp/mysql/table/index5.html > CREATE TABLE friends ( name VARCHAR(10) NOT NULL DEFAULT '匿名', address VARCHAR(10) );
70. PRIMARY KEY制約 ・idにPRIMARY KEY制約を追加 　・既存データと同じ値の idのデータを作成できなくなる 　・idにNULLを格納できなくなる https://www.dbonline.jp/mysql/table/index8.html > CREATE TABLE books( id INTEGER PRIMARY KEY, name VARCHAR(10), );
71. ユニーク制約 ・emailにユニーク制約を追加 ・既存データと同じ値のemailのデータを作成できなくなる https://www.dbonline.jp/mysql/table/index9.html > CREATE TABLE users( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) UNIQUE, name VARCHAR(10) );
72. 外部キー制約 ・user_idにusersテーブルのidの外部キー制約を追加 ・user_idにはusersテーブルのデータに存在するidの値しか設定できなくなる ・外部キー制約対象のusers.idはPRIMARY KEY制約がかかっている必要がある ・articles.user_idに存在するusers.idのレコードは削除できなくなる https://www.dbonline.jp/mysql/table/index11.html > CREATE TABLE articles( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, article TEXT, CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id) );
73. インデックス ・DBで作成できる索引情報のこと。 ・インデックスの存在する列に対して検索が行われた場合、 DBMSはインデックスの使用を試みる為、高速になることが多 い。 ・ユニーク制約を行う場合にもインデックスが作られる ・索引情報保存のため、ディスク容量を消費する ・テーブルのデータ変更があるとインデックスの書き換えも必要となり、 INSERT,UPDATE,DELETE文のオーバーヘッドが増す https://www.dbonline.jp/mysql/index/index1.html
74. CHECK制約 ・MySQL8.0.16で追加された新機能(2019/04/25) ・テーブルに挿入されるデータを精査して、条件に合わないものは弾いてくれる ・下のコードの場合、genderに入る単語を「man」「woman」「other」の3種類に限定し ている おまけ ALTER TABLE `users` ADD CONSTRAINT `users_gender_chk_1` CHECK ( gender IN ('man', 'woman', 'other') );
75. ユーザの追加と権限
76. 機密保護機能 ・データベースにアクセスするユーザ毎にアクセス権限を設定することにより、ユーザ毎 にデータベースやテーブルのデータへのアクセスを制限できる
77. ユーザ作成 ・test_userという名前のパスワードが passXXXXのlocalhostからアクセスできるユーザを作成する ・IDENTIFIED BYの指定がない場合はパスワードなしのユーザを作成できる ・hostの指定を%にした場合、任意のホストからログインできるユーザになる https://www.dbonline.jp/mysql/user/index1.html > CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'passXXXX';
78. ユーザ権限設定 ・test_userに全てのデータベースのアクセス権限を追加し、他のユーザの権限も変更できるように設定 　・許可する権限タイプにALL PRIVILEGES(全ての権限)を指定している 　　・権限タイプはSELECT、CREATEなどコマンドレベルで指定できる 　・*.*の部分が全てのデータベースを指定 　　・権限はデータベース、テーブル、カラム単位などで設定可能 　・WITH GRANT OPTIONで他のユーザの権限設定もできるようになる https://www.dbonline.jp/mysql/user/index6.html > GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION;
79. 権限の削除 ・test_user@localhostの全ての権限が削除される ・ALLのところを別の権限タイプを指定することで、指定に応じた権限のみを削除できる https://www.dbonline.jp/mysql/user/index8.html > REVOKE ALL, GRANT OPTION FROM test_user@localhost;
80. 作成したユーザでのログイン ・権限変更後は再ログインしないと権限が反映されない $ mysql -u test_user -p
81. トランザクションと排他制御
82. トランザクション ・ある一連の処理が完了するまで別の処理ができないようにすることでデータの不整合 を防ぐ ・複数の SQL 文によるデータ更新を1つの処理としてまとめてデータベースに反映させ ること
83. 例えば 例) 商品購入時の購入履歴と在庫管理の処理 start 購入履歴テーブルに データを追加 在庫管理テーブルの データを変更 end 購入履歴テーブル 在庫管理テーブル 顧客 商品 個数 商品 在庫数 鍋 10 鍋を1個買う start時のテーブル状態
84. 成功時 start 購入履歴テーブルに データを追加 在庫管理テーブルの データを変更 end 購入履歴テーブル 在庫管理テーブル 顧客 商品 個数 Aさん 鍋 1 商品 在庫数 鍋 9 鍋一個買う Aさん鍋一つ購入 データ追加 鍋の在庫を1減ら す 鍋の合計個数に矛 盾はない 鍋の合計個数に 矛盾はない
85. 失敗時 start 購入履歴テーブルに データを追加 在庫管理テーブルの データを変更 end 購入履歴テーブル 在庫管理テーブル 顧客 商品 個数 Aさん 鍋 1 商品 在庫数 鍋 10 鍋一個買う Aさん鍋一つ購入 データ追加 鍋の在庫を1減ら す 予期しない処 理中断 鍋の合計個数に矛 盾はない 鍋の合計個数に 矛盾が発生
86. トランザクション start 購入履歴テーブルに データを追加 在庫管理テーブルの データを変更 end 購入履歴テーブル 在庫管理テーブル 商品 在庫数 鍋 9 鍋一個買う Aさん鍋一つ購入 データ追加 鍋の在庫を1減ら す 鍋の合計個数に矛 盾はない commitされると変 更が反映される commit rollback エラーなし エラーあり 一つの処理として 扱う 変更を反映する 顧客 商品 個数 Aさん 鍋 1
87. トランザクション start 購入履歴テーブルに データを追加 在庫管理テーブルの データを変更 end 購入履歴テーブル 在庫管理テーブル 顧客 商品 個数 商品 在庫数 鍋 10 鍋一個買う Aさん鍋一つ購入 データ追加 鍋の在庫を1減ら す 鍋の合計個数に矛 盾はない 処理全体がなかっ たことになる commit rollback エラーなし エラーあり 一つの処理として 扱う 反映を中止する
88. 排他制御とは？ あるトランザクションが実行中のときに、そのトランザクションが対象としているデータを ロックし、他のトランザクションのアクセスを禁止することによって、データの整合性を確 保しようとする仕組み cf. https://oss-db.jp/dojo/dojo_01 cf. https://style.potepan.com/articles/25875.html
89. 例えば start 残高を参照する 残高に20000プラスし て保存する end 残高 70000 20000円仕送 り送る start 残高を参照する 残高から5000マイナ スして保存する end 振込処理 引き落とし処理 5000円下ろす 残高テーブル 例) 銀行の振り込みと引き落としの同時処理
90. 排他制御しない場合 start 残高を参照する 残高(70000)に20000 プラスして保存する end 残高 70000 20000円仕送 り送る start 残高を参照する 残高(70000)から 5000マイナスして保 存する end 振込処理 引き落とし処理 5000円下ろす 残高テーブル 残高 90000 残高 65000 残高結果が間違っ てしまう 70000 70000
91. 排他制御した場合 start 更新のために残高を 参照する 残高(70000)に20000 プラスして保存する end 残高 70000 20000円仕送 り送る start 更新のために残高を 参照する 残高(90000)から 5000マイナスして保 存する end 振り込み処理 引き落とし処理 5000円下ろす 残高テーブル 残高 90000 残高 85000 正しい残高になる 70000 90000 commit ロック状態 ロック解除 ロック開始 commit commitでロック解 除される
92. ACID特性（トランザクションの特性） • 原子性（Atomicity） ◦ 全て実行されるか、または全て実行されないか。 • 一貫性（Consistency） ◦ 実行前後で矛盾が無い状態が保たれる。 • 独立性（Isolation） ◦ 他のトランザクションに影響を与えない。 • 永続性（Durability） ◦ トランザクションの結果は失われない。
93. トランザクション分離レベル 分離レベル ダーティーリード ファジーリード ファントムリード READ UNCOMMITTED 発生 発生 発生 READ COMMITTED 発生しない 発生 発生 REPEATABLE READ 発生しない 発生しない 発生※ SERIALIZABLE 発生しない 発生しない 発生しない ※InnoDBではREPEATABLE READでもファントムリードが発生しない トランザクションが複数同時に発生した際に、どの程度一貫性を担保するかのレベル。4 段階存在する。
94. トランザクション分離レベル • ダーティーリード（Dirty Read） ◦ 他のトランザクションのコミットされていない内容が見える • ファジーリード（Fuzzy Read） ◦ 他のトランザクションのコミットした追加・削除・更新が見える • ファントムリード（Phantom Read） ◦ 他のトランザクションのコミットした追加・削除が見える
95. トランザクション分離レベルの確認 > SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.02 sec) MySQLのデフォルトは「REPEATABLE READ」 ※研修用に用意したテーブルを使用しています
96. REPEATABLE READ A> use training; // Aでトランザクション開始 A> BEGIN; B> use training; // Bでトランザクション開始 B> BEGIN; A> SELECT * FROM subjects WHERE id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 国語 | +----+--------+ 1 row in set (0.02 sec) A> UPDATE subjects SET name = '更新' WHERE id = 1; A> SELECT * FROM subjects WHERE id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 更新 | +----+--------+ 1 row in set (0.02 sec) // Bではコミット前の変更が見えない（ ダーティーリードが起きない ） B> SELECT * FROM subjects WHERE id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 国語 | +----+--------+ 1 row in set (0.01 sec) A> commit; // Bではコミット後の変更が見えない（ ファジーリードが起きない ） B> SELECT * FROM subjects WHERE id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 国語 | +----+--------+ 1 row in set (0.01 sec) A> INSERT INTO subjects (name) VALUES ('新しい科目 '); A> commit; // Bではコミット後の追加処理が見えない（ ファントムリードが起きない ） B> SELECT * FROM subjects; ...
97. READ COMMITTED A,B> set session transaction isolation level read committed; A,B> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-COMMITTED | +-------------------------+ // トランザクションの開始 A,B> BEGIN; A> UPDATE subjects SET name = '新しい更新 ' WHERE id = 1; // Bではコミット前の変更が見えない（ ダーティーリードが起きない ） B> SELECT * FROM subjects WHERE id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 更新 | +----+--------+ A> commit; // トランザクション中の BでAがコミットした内容が見えてしまう（ ファジーリードが発生 ） B> SELECT * FROM subjects WHERE id = 1; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 新しい更新 | +----+-----------------+ A> INSERT INTO subjects (name) VALUES ('新しい科目 '); A> commit; // トランザクション中の BでAがコミットした追加が見えてしまう（ ファントムリードが発 生） B> SELECT * FROM subjects; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 新しい更新 | ... | 7 | 新しい科目 | +----+-----------------+
98. READ UNCOMMITTED A,B> use training; A,B> set session transaction isolation level read uncommitted; A,B> SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-UNCOMMITTED | +-------------------------+ A,B> BEGIN; A> UPDATE subjects SET name = '更新' WHERE id = 1; // トランザクション中の BでAがコミットしてない内容が見える //（ダーティーリードが発生 ） B> SELECT * FROM subjects WHERE id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 更新 | +----+--------+ A> commit; // トランザクション中の BでAがコミットした内容が見えてしまう（ ファジーリードが発生 ） B> SELECT * FROM subjects WHERE id = 1; +----+--------+ | id | name | +----+--------+ | 1 | 更新 | +----+--------+ A> INSERT INTO subjects (name) VALUES ('新しい科目 '); A> commit; // トランザクション中の BでAがコミットした追加が見えてしまう（ ファントムリードが発 生） B> SELECT * FROM subjects; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 新しい更新 | ... | 8 | 新しい科目 | +----+-----------------+
99. トランザクションまとめ ・一連の処理の途中でエラーになったときにデータの不整合を防ぐ ・同時アクセスが発生した時の不整合を防ぐ
100. テーブル設計
101. データの⼀元管理 ・正規化 　・冗長を排除し、データの独立性（関係従属性のことではなく、不必要な依存のこと）を 高める更新異常が発生しないようにする 　・「1事実1箇所」（1 fact in 1 place）にすること
102. 非正規 例)料理の注文データ 注文番号 注文日 名前 電話番号 料理 値段 個数 料理 値段 個数 合計 C0001 2021/04/01 佐藤太郎 090-0022-1 122 カレー 500 2 ラーメン 600 1 1600 C0002 2021/04/03 田中花子 090-0034-9 921 マグロ丼 800 1 ピザ 1000 1 1800 C0003 2021/04/04 斎藤次郎 090-0077-5 567 ピザ 1000 3 3000 C0004 2021/04/05 佐藤太郎 090-0022-1 122 マグロ丼 800 1 800 注文表
103. 問題点 ・注文の種類が多くなるたびに列を増やす必要がある ・同じ情報が複数箇所にあり、更新が必要になった場合に複数箇所更新する必要があ る 注文番号 注文日 名前 電話番号 料理 値段 個数 料理 値段 個数 合計 C0001 2021/04/01 佐藤太郎 090-0022-1 122 カレー 500 2 ラーメン 600 1 1600 C0002 2021/04/03 田中花子 090-0034-9 921 マグロ丼 800 1 ピザ 1000 1 1800 C0003 2021/04/04 斎藤次郎 090-0077-5 567 ピザ 1000 3 3000 C0004 2021/04/05 佐藤太郎 090-0022-1 122 マグロ丼 800 1 800 料理1つ目 料理2つ目
104. 第一正規化 ・繰り返し項目を別レコードにする ・計算して算出できるものは無くす 注文番号 注文日 名前 電話番号 料理 値段 個数 料理 値段 個数 合計 C0001 2021/04/01 佐藤太郎 090-0022-1 122 カレー 500 2 ラーメン 600 1 1600 C0002 2021/04/03 田中花子 090-0034-9 921 マグロ丼 800 1 ピザ 1000 1 1800 C0003 2021/04/04 斎藤次郎 090-0077-5 567 ピザ 1000 3 3000 C0004 2021/04/05 佐藤太郎 090-0022-1 122 マグロ丼 800 1 800 注文表
105. 第一正規化後 注文番号 注文日 名前 電話番号 料理 値段 個数 C0001 2021/04/01 佐藤太郎 090-0022-1122 カレー 500 2 C0001 2021/04/01 佐藤太郎 090-0022-1122 ラーメン 600 1 C0002 2021/04/03 田中花子 090-0034-9921 マグロ丼 800 1 C0002 2021/04/03 田中花子 090-0034-9921 ピザ 1000 1 C0003 2021/04/04 斎藤次郎 090-0077-5567 ピザ 1000 3 C0004 2021/04/05 佐藤太郎 090-0022-1122 マグロ丼 800 1 注文表
106. よくなったこと ・注文料理の受付数を列で制限されなくなった ・合計金額という冗長な情報を削除できた
107. 第二正規化 関数従属：主キーが決まると、列の値が一意に定まる関係 部分関数従属：複合キーの一部の項目だけで、列の値が一意に定まる関係 注文番号 注文日 名前 電話番号 料理 値段 個数 C0001 2021/04/01 佐藤太郎 090-0022-1122 カレー 500 2 C0001 2021/04/01 佐藤太郎 090-0022-1122 ラーメン 600 1 C0002 2021/04/03 田中花子 090-0034-9921 マグロ丼 800 1 C0002 2021/04/03 田中花子 090-0034-9921 ピザ 1000 1 C0003 2021/04/04 斎藤次郎 090-0077-5567 ピザ 1000 3 C0004 2021/04/05 佐藤太郎 090-0022-1122 マグロ丼 800 1 複合キー 複合キー 注文番号の部分関数従属 料理の部分関数従属 部分関数従属を別テーブルに分ける
108. 第二正規化後 注文番号 料理 個数 C0001 カレー 2 C0001 ラーメン 1 C0002 マグロ丼 1 C0002 ピザ 1 C0003 ピザ 3 C0004 マグロ丼 1 注文表 注文番号 注文日 名前 電話番号 C0001 2021/04/01 佐藤太郎 090-0022-1122 C0002 2021/04/03 田中花子 090-0034-9921 C0003 2021/04/04 斎藤次郎 090-0077-5567 C0004 2021/04/05 佐藤太郎 090-0022-1122 注文顧客表 料理 値段 カレー 500 ラーメン 600 マグロ丼 800 ピザ 1000 料理表
109. よくなったこと ・料理の情報の重複がなくなり１箇所で管理できるようになった ・注文がなくても料理のデータを追加できるようになった ・注文データを料理と個数という最低限の情報だけで追加できるようになった
110. 第三正規化 注文番号 注文日 名前 電話番号 C0001 2021/04/01 佐藤太郎 090-0022-1122 C0002 2021/04/03 田中花子 090-0034-9921 C0003 2021/04/04 斎藤次郎 090-0077-5567 C0004 2021/04/05 佐藤太郎 090-0022-1122 注文顧客表 推移的関数従属：第二正規化テーブルで、主キー以外の項目で列の値が一意に定まる 関係 名前の推移的関数従属 推移的関数従属を別テーブルに分ける
111. 第三正規化後 注文番号 料理 個数 C0001 カレー 2 C0001 ラーメン 1 C0002 マグロ丼 1 C0002 ピザ 1 C0003 ピザ 3 C0004 マグロ丼 1 注文表 注文顧客表 料理 値段 カレー 500 ラーメン 600 マグロ丼 800 ピザ 1000 料理表 注文番号 注文日 名前 C0001 2021/04/01 佐藤太郎 C0002 2021/04/03 田中花子 C0003 2021/04/04 斎藤次郎 C0004 2021/04/05 佐藤太郎 名前 電話番号 佐藤太郎 090-0022-1122 田中花子 090-0034-9921 斎藤次郎 090-0077-5567 顧客表
112. よくなったこと ・顧客の情報の重複なくなりが１箇所で管理できるようになった ・注文がなくても顧客の情報が追加できるようになった
113. 正規化のメリット、デメリット ・メリット 　・データ管理(保守)が容易になる 　・データの共通性(汎用性)が向上する 　・データ容量を削減できる ・デメリット 　・検索パフォーマンスが悪くなることがある 〜余談〜 検索パフォーマンスの低下を防ぐために、意図的に正 規化を行わないケースもあったりします。
114. やってみよう ・Twitter風アプリのDBを設計してみよう ・保存したいデータは以下の通り。 　・ユーザー情報(ID, 名前) 　・投稿内容 　・投稿へのコメント 　・いいね！ 実践 特に正解はないので、あれこれ考えてみよう！
115. 参考リンク
116. 参考リンク ・データベースとは 　・https://www.slideshare.net/KentaOku/db01-46816582 ・データベース入門 　・https://www.slideshare.net/hayasitd/ss-60946852
117. 参考リンク ・トランザクション 　・https://www.slideshare.net/KentaOku/12-49451823 ・正規化 　・https://www.slideshare.net/KentaOku/db10-48389863
118. 参考リンク ・MySQL公式 　・https://dev.mysql.com/doc/refman/8.0/en/ ・MySQLの使い方 　・https://www.dbonline.jp/mysql/