データベース研修

データベース研修リレーショナルデータベース(RDB) パーソルキャリア株式会社テクノロジー本部エンジニアリング統括部サービス開発部 ※本資料は2023年3月時点の情報であり、2023年新卒の研修教材です。

目次・データベースとは・RDBMS ・MySQL ・SQL ・テーブル設計・参考リンク

データベース

データベースとは・データベース（DB; DataBase）　・データの集合そのもの　　・データ（data）：ディジタル化された情報　　・ベース（base）：⼀台，基盤，基地・構造化されたデータの集合です。

webサービスとデータベース・webサービスではたいてい何らかのデータを扱うことになります。・例えば名前やメールアドレス、テキストコメントなどwebフォームから入力して送信したり、そのデータを別の画面で表示したり・そういったデータを管理するためにデータベースを使用します

webサービスとデータの操作・一般的にデータに対する主な操作として「Create（生成）」「Read（読み取り）」「Update （更新）」「Delete（削除）」があり、それらを総称してCRUD(クラッド)と呼ばれます・RESTfulなWEBサービスは、特定のリソースにHTTPメソッドでアクセスすることでデータの受信や操作を行います・POST,GET,PUT,DELETEのメソッドでCRUDの性質を満たします cf. RESTful APIとは何か？ https://qiita.com/NagaokaKenichi/items/0647c30
ef596cedf4bf2

データベースの種類種類説明階層型データベースデータを階層構造（木構造）で表すリレーショナルデータベース（Relational Database：RDB） Excelのような2次元表の形式でデータを管理最も広く利用されている
オブジェクト指向データベースデータとそれを操作する処理をまとめたオブジェクトを管理するデータベース XMLデータベース XML形式のデータを扱うキー・バリュー型データストア検索に使うキー（Key）と値（Value）の組み合わせで管理プログラムでいう「連想配列」や「ハッシュ」的なイメージ他にも色々...

RDBとは・Relational Database：RDB ・表形式でデータを表す id 名前年齢性別 1 田中太郎
24 男 2 花沢花子 26 女 3 鈴木次郎 31 男

DBMSとは・データベース管理システム（DBMS; DataBase Management System）　・データベースを操作し管理するために必要なソフトウェア（システム）・データベースを管理し、外部のソフトウェアからの要求に応えてデータベースの操作を行う専門のソフトウェア

RDBMSとは・データを表の形式で永続化し、かつデータ間の関連もまた表の形式で永続化できる DBMS 関係データベース管理システム - Wikipedia

RDBMSのデータベースとテーブルデータベーステーブルA テーブルB データベースという器の中にテーブルと呼ばれるデータを格納する表がある

テーブルの構造 id 名前年齢性別 1 田中太郎 24 男 2
花沢花子 26 女 3 鈴木次郎 31 男行(レコード) フィールド列(カラム) テーブルは列(カラム)と行(レコード)でできており、レコードの中の入力項目はフィールドと呼ばれる

代表的なRDBMS ・Oracle Database：Oracle社のRDBMS ・SQL Server：Microsoft社のRDBMS ・DB2：IBM社のRDBMS ・PostgreSQL：オープンソースのRDBMS ・MySQL：オープンソースのRDBMS（2010年からOracle社が開発元）サビ開では主にMySQLが使用されている

MySQLとは公式サイト https://www.mysql.com/jp/ ・オープンソース SQL データベース管理システム・データベースはリレーショナルデータベース・Facebook、Twitter、YouTubeなど、多くの人気Webサイトでも使用されている

MySQLの利用状況・世界的にはMySQLの方がライバルであるPostgreSQLより多く使用されている・ウェブサイトの構築に用いるソフトウェア環境として、LAMP (Linux, Apache, MySQL, Perl・PHP・Python) の略称が知られている・Yahoo!、Facebook、Twitterなどの巨大なウェブサイトでの適用例も多く、WebアプリケーションのXOOPS、WordPress、SugarCRMなどCMSも、バックエンドのデータベー
スとしてMySQLを利用している

MySQLのインストール以下のサイトを参考にhomebrewでインストールしてください $ brew update $ brew install mysql https://qiita.com/kobayashi-m42/items/dae22e49ab060adf920f
実践

MySQLのサーバ起動とログイン・サーバ起動・ログイン $ mysql.server start $ mysql -u root
・mysqlのプロンプト(mysql>)が表示されるサーバーを終了させるときは、 $ mysql.server stop 実践

RDBMSのシステム構成クライアント (データを利用するプログラム) RDBMSサーバ (データベースを読み書きするプログラム) データベース (ハードディスク等に
保存されたデータ群 ) 命令 (SQL) 命令結果 (読み出しデータ等) cf. https://codezine.jp/article/detail/12216

コマンドの実行 > SELECT 'hello world!' AS info; ・hello world!が表示される実践

ファイルを読み込んで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

SQLとは・データベースを操作するために開発された言語・SQLにはISO（国際標準化機構）で定められた標準規格がある　・ただし、実際にはRDBMSごとに違い(方言)がある部分もある・「Structured Query Language」の略　・ただし標準規格の定義としては略語ではなくSQLという名前

SQLの種類・DDL(Data Deﬁnition Language) ・DML(Data Manipulation Language) ・DCL(Data Control Language)
・TCL(Transaction Control Language)

DDL(Data Deﬁnition Language) データベースオブジェクトの生成や削除変更を行うコマンド CREATE データベース、テーブル、ユーザ等のオブジェクトを新規作成する DROP データベースからオブジェクトを削除する ALTER 既にあるオブジェクトの定義を変更する

DML(Data Manipulation Language) テーブルに対するデータの取得・追加・更新・削除を行うコマンド SELECT データベースからデータを検索する INSERT テーブルにデータを追加する UPDATE テーブル内のデータを更新する
DELETE テーブル内のレコードを削除する

DCL(Data Control Language) パーミッションや権限に関連するコマンド GRANT データベースへのアクセス権を定義する REVOKE GRANTで与えたアクセス権を取り下げる

TCL(Transaction Control Language) データベース内におけるトランザクションを扱うコマンド START TRANSACTION または BEGIN 新しいトランザクションを開始 COMMIT
現在のトランザクションをコミットして、その変更を永続的なものにします ROLLBACK 現在のトランザクションをロールバックして、その変更を取り消します

予約語と識別子・MySQLには、SELECT、FROM、 INSERT、INTO、JOINなど、特定の語句が「予約語」として指定されている・データベース名やテーブル名、カラム名などを指定する語句のことを「識別子」と呼ぶ予約語一覧 https://dev.mysql.com/doc/refman/8.0/en/keywords.html > SELECT
id, name FROM users;

予約語と識別子・MySQLのテーブル名やカラム名などの「識別子」には、基本的に「予約語」が使えない・識別子に予約語と同じ単語を使う場合はバッククォート(`)で括る必要がある・トラブルの原因になるので、アンチパターンです > SELECT id, `order`, created_at, updated_at
FROM items ORDER BY `order`;

マニュアル・公式　・https://dev.mysql.com/doc/refman/8.0/en/ ・MySQLの使い方　・https://www.dbonline.jp/mysql/ 公式のマニュアルは情報量が多いので、 1度に全部読み込むのではなく、困ったときに都度見に行けば良いと思います。

データベース、テーブルの追加削除

データベースの作成 mydbという名前のデータベースが作成される https://www.dbonline.jp/mysql/database/index1.html > CREATE DATABASE mydb; 実践

データベースの一覧を見るデータベースの一覧が表示される https://www.dbonline.jp/mysql/database/index2.html > SHOW databases; 実践ちなみに... 画面をクリアにするときは、 >
system clear;

テーブルの作成・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) ); 実践

主キー(PRIMARY KEY) 格納されたすべてのデータの中から 1 つのデータを特定したいときに、 1 つまたは複数のカラムの値を検索してデータを特定しますが、その時に最も適したカラムに設定されるのがプライマリーキーです。・PRIMARY
KEYの値の条件・NULL禁止・値の重複なし

データ型(文字) データ型説明 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

データ型(数値) データ型説明 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

データ型(日付/時間) データ型説明 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

テーブルの一覧をみるテーブルの一覧が表示される https://www.dbonline.jp/mysql/table/index2.html > SHOW tables; 実践

テーブル構造を見る・usersのテーブルを作成するためのCREATE文が表示される https://www.dbonline.jp/mysql/table/index2.html ・DESCRIBE users; でもテーブルー構造を表示できる。 > SHOW CREATE TABLE
users; 実践

> ALTER TABLE users ADD COLUMN age INTEGER AFTER name;
テーブル構造を変更する・usersテーブルのnameの次にageというカラムを追加する・追加(ADD)、変更(CHANGE)、削除(DROP)などがある https://www.dbonline.jp/mysql/table/index18.html 実践

テーブルを削除する usersテーブルが削除される https://www.dbonline.jp/mysql/table/index4.html > DROP TABLE users; 実践 > SHOW
tables と打つと、usersテーブルが削除されていることを確認できるはず

データベースを削除する mydbのデータベースが削除される https://www.dbonline.jp/mysql/database/index5.html > DROP DATABASE mydb; 実践 > SHOW
databases と打つと、mydbが削除されていることを確認できるはず

データの追加・取得・更新・削除

下準備 > CREATE DATABASE mydb; > USE mydb; > CREATE
TABLE users ( id INT AUTO_INCREMENT, name TEXT, age INTEGER, PRIMARY KEY (id) ); 実践

データを追加する・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); 実践

データを取得する・１行目はusersテーブルの全てのデータを取得して表示する　・*は全てのカラムを指定する・２行目はusersテーブルの全てのデータのidとnameを取得して表示する https://www.dbonline.jp/mysql/select/index1.html > SELECT * FROM users;
> SELECT id, name FROM users; 実践

データを更新する・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; 実践

データを削除する・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; 実践

データの条件付き取得詳しくはSQL研修で！

条件を指定してデータを取得・ageが 22と等しいデータを全て取得して表示する・条件の種類については後述 https://www.dbonline.jp/mysql/select/index4.html > SELECT * FROM users
WHERE age = 22;

比較演算子演算子使用例意味 = 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

論理演算子演算子使用例意味 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’);

・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;

取得データの開始位置と行数の上限を設定・usersテーブルのデータを開始位置2から最大4つ取得する・OFFSETは0から始まる https://www.dbonline.jp/mysql/select/index12.html > SELECT * FROM users LIMIT
4 OFFSET 2;

グループ化・ageが同じ値のデータをグループ化し、ageごとの個数をカウントする　・COUNT関数で個数がカウントされる https://www.dbonline.jp/mysql/select/index9.html > SELECT age, COUNT(age) FROM users
GROUP BY age;

集計関数・SQLでテーブルの値を集計するために使う機能・対象データがNULLの場合は無視される名前  説明  AVG()  引数の平均値を返します  COUNT()  返された行数のカウントを返します  GROUP_CONCAT() 
連結された文字列を返します  MAX()  最大値を返します  MIN()  最小値を返します  SUM()  集計を返します  https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

テーブルの結合詳しくはSQL研修で！

テーブルを結合してデータを取得・usersテーブルのidとarticlesテーブルのuser_idが一致したデータを結合して表示する https://www.dbonline.jp/mysql/join/ > SELECT * FROM users INNER
JOIN articles ON users.id = articles.user_id;

内部結合(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

外部結合指定したそれぞれのテーブルのカラムの値が一致するデータだけではなくどちらかのテーブルにだけデータがある場合も合わせて取得 LEFT JOINとRIGHT JOINの２つがある

外部結合(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

外部結合(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 何か

別名をつける(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;

制約

整合性維持機能・レコードのカラムに登録できるデータに制約をつけることで、不整合なデータの登録を防ぐ　・null制約　・ユニーク制約　・PRIMARY KEY制約・外部キー制約

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) );

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), );

ユニーク制約・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) );

外部キー制約・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) );

インデックス・DBで作成できる索引情報のこと。・インデックスの存在する列に対して検索が行われた場合、 DBMSはインデックスの使用を試みる為、高速になることが多い。・ユニーク制約を行う場合にもインデックスが作られる・索引情報保存のため、ディスク容量を消費する・テーブルのデータ変更があるとインデックスの書き換えも必要となり、 INSERT,UPDATE,DELETE文のオーバーヘッドが増す https://www.dbonline.jp/mysql/index/index1.html

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') );

ユーザの追加と権限

機密保護機能・データベースにアクセスするユーザ毎にアクセス権限を設定することにより、ユーザ毎にデータベースやテーブルのデータへのアクセスを制限できる

ユーザ作成・test_userという名前のパスワードが passXXXXのlocalhostからアクセスできるユーザを作成する・IDENTIFIED BYの指定がない場合はパスワードなしのユーザを作成できる・hostの指定を%にした場合、任意のホストからログインできるユーザになる https://www.dbonline.jp/mysql/user/index1.html > CREATE USER
'test_user'@'localhost' IDENTIFIED BY 'passXXXX';

ユーザ権限設定・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;

権限の削除・test_user@localhostの全ての権限が削除される・ALLのところを別の権限タイプを指定することで、指定に応じた権限のみを削除できる https://www.dbonline.jp/mysql/user/index8.html > REVOKE ALL, GRANT OPTION FROM
test_user@localhost;

作成したユーザでのログイン・権限変更後は再ログインしないと権限が反映されない $ mysql -u test_user -p

トランザクションと排他制御

トランザクション・ある一連の処理が完了するまで別の処理ができないようにすることでデータの不整合を防ぐ・複数の SQL 文によるデータ更新を1つの処理としてまとめてデータベースに反映させること

例えば例) 商品購入時の購入履歴と在庫管理の処理 start 購入履歴テーブルにデータを追加在庫管理テーブルのデータを変更 end 購入履歴テーブル
在庫管理テーブル顧客商品個数商品在庫数鍋 10 鍋を1個買う start時のテーブル状態

成功時 start 購入履歴テーブルにデータを追加在庫管理テーブルのデータを変更 end 購入履歴テーブル在庫管理テーブル顧客
商品個数 Aさん鍋 1 商品在庫数鍋 9 鍋一個買う Aさん鍋一つ購入データ追加鍋の在庫を1減らす鍋の合計個数に矛盾はない鍋の合計個数に矛盾はない

失敗時 start 購入履歴テーブルにデータを追加在庫管理テーブルのデータを変更 end 購入履歴テーブル在庫管理テーブル顧客
商品個数 Aさん鍋 1 商品在庫数鍋 10 鍋一個買う Aさん鍋一つ購入データ追加鍋の在庫を1減らす予期しない処理中断鍋の合計個数に矛盾はない鍋の合計個数に矛盾が発生

トランザクション start 購入履歴テーブルにデータを追加在庫管理テーブルのデータを変更 end 購入履歴テーブル在庫管理テーブル商品
在庫数鍋 9 鍋一個買う Aさん鍋一つ購入データ追加鍋の在庫を1減らす鍋の合計個数に矛盾はない commitされると変更が反映される commit rollback エラーなしエラーあり一つの処理として扱う変更を反映する顧客商品個数 Aさん鍋 1

トランザクション start 購入履歴テーブルにデータを追加在庫管理テーブルのデータを変更 end 購入履歴テーブル在庫管理テーブル顧客
商品個数商品在庫数鍋 10 鍋一個買う Aさん鍋一つ購入データ追加鍋の在庫を1減らす鍋の合計個数に矛盾はない処理全体がなかったことになる commit rollback エラーなしエラーあり一つの処理として扱う反映を中止する

排他制御とは？あるトランザクションが実行中のときに、そのトランザクションが対象としているデータをロックし、他のトランザクションのアクセスを禁止することによって、データの整合性を確保しようとする仕組み cf. https://oss-db.jp/dojo/dojo_01 cf. https://style.potepan.com/articles/25875.html

例えば start 残高を参照する残高に20000プラスして保存する end 残高 70000 20000円仕送り送る
start 残高を参照する残高から5000マイナスして保存する end 振込処理引き落とし処理 5000円下ろす残高テーブル例) 銀行の振り込みと引き落としの同時処理

排他制御しない場合 start 残高を参照する残高(70000)に20000 プラスして保存する end 残高 70000 20000円仕送り送る
start 残高を参照する残高(70000)から 5000マイナスして保存する end 振込処理引き落とし処理 5000円下ろす残高テーブル残高 90000 残高 65000 残高結果が間違ってしまう 70000 70000

排他制御した場合 start 更新のために残高を参照する残高(70000)に20000 プラスして保存する end 残高 70000 20000円仕送
り送る start 更新のために残高を参照する残高(90000)から 5000マイナスして保存する end 振り込み処理引き落とし処理 5000円下ろす残高テーブル残高 90000 残高 85000 正しい残高になる 70000 90000 commit ロック状態ロック解除ロック開始 commit commitでロック解除される

ACID特性（トランザクションの特性） • 原子性（Atomicity） ◦ 全て実行されるか、または全て実行されないか。 • 一貫性（Consistency） ◦ 実行前後で矛盾が無い状態が保たれる。 •
独立性（Isolation） ◦ 他のトランザクションに影響を与えない。 • 永続性（Durability） ◦ トランザクションの結果は失われない。

トランザクション分離レベル分離レベルダーティーリードファジーリードファントムリード READ UNCOMMITTED 発生発生発生
READ COMMITTED 発生しない発生発生 REPEATABLE READ 発生しない発生しない発生※ SERIALIZABLE 発生しない発生しない発生しない ※InnoDBではREPEATABLE READでもファントムリードが発生しないトランザクションが複数同時に発生した際に、どの程度一貫性を担保するかのレベル。4 段階存在する。

トランザクション分離レベル • ダーティーリード（Dirty Read） ◦ 他のトランザクションのコミットされていない内容が見える • ファジーリード（Fuzzy Read） ◦
他のトランザクションのコミットした追加・削除・更新が見える • ファントムリード（Phantom Read） ◦ 他のトランザクションのコミットした追加・削除が見える

トランザクション分離レベルの確認 > SELECT @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ |
REPEATABLE-READ | +-------------------------+ 1 row in set (0.02 sec) MySQLのデフォルトは「REPEATABLE READ」 ※研修用に用意したテーブルを使用しています

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; ...

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 | 新しい科目 | +----+-----------------+

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 | 新しい科目 | +----+-----------------+

トランザクションまとめ・一連の処理の途中でエラーになったときにデータの不整合を防ぐ・同時アクセスが発生した時の不整合を防ぐ

テーブル設計

データの⼀元管理・正規化　・冗長を排除し、データの独立性（関係従属性のことではなく、不必要な依存のこと）を高める更新異常が発生しないようにする　・「1事実1箇所」（1 fact in 1 place）にすること

非正規例)料理の注文データ注文番号注文日名前電話番号料理値段個数料理
値段個数合計 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 注文表

問題点・注文の種類が多くなるたびに列を増やす必要がある・同じ情報が複数箇所にあり、更新が必要になった場合に複数箇所更新する必要がある注文番号注文日名前電話番号料理値段
個数料理値段個数合計 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つ目

第一正規化・繰り返し項目を別レコードにする・計算して算出できるものは無くす注文番号注文日名前電話番号料理値段個数
料理値段個数合計 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 注文表

第一正規化後注文番号注文日名前電話番号料理値段個数 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 注文表

よくなったこと・注文料理の受付数を列で制限されなくなった・合計金額という冗長な情報を削除できた

第二正規化関数従属：主キーが決まると、列の値が一意に定まる関係部分関数従属：複合キーの一部の項目だけで、列の値が一意に定まる関係注文番号注文日名前電話番号料理値段個数
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 複合キー複合キー注文番号の部分関数従属料理の部分関数従属部分関数従属を別テーブルに分ける

第二正規化後注文番号料理個数 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 料理表

よくなったこと・料理の情報の重複がなくなり１箇所で管理できるようになった・注文がなくても料理のデータを追加できるようになった・注文データを料理と個数という最低限の情報だけで追加できるようになった

第三正規化注文番号注文日名前電話番号 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 注文顧客表推移的関数従属：第二正規化テーブルで、主キー以外の項目で列の値が一意に定まる関係名前の推移的関数従属推移的関数従属を別テーブルに分ける

第三正規化後注文番号料理個数 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 顧客表

よくなったこと・顧客の情報の重複なくなりが１箇所で管理できるようになった・注文がなくても顧客の情報が追加できるようになった

正規化のメリット、デメリット・メリット　・データ管理(保守)が容易になる　・データの共通性(汎用性)が向上する　・データ容量を削減できる・デメリット　・検索パフォーマンスが悪くなることがある〜余談〜検索パフォーマンスの低下を防ぐために、意図的に正規化を行わないケースもあったりします。

やってみよう・Twitter風アプリのDBを設計してみよう・保存したいデータは以下の通り。　・ユーザー情報(ID, 名前) 　・投稿内容　・投稿へのコメント　・いいね！実践特に正解はないので、あれこれ考えてみよう！

参考リンク

参考リンク・データベースとは　・https://www.slideshare.net/KentaOku/db01-46816582 ・データベース入門　・https://www.slideshare.net/hayasitd/ss-60946852

参考リンク・トランザクション　・https://www.slideshare.net/KentaOku/12-49451823 ・正規化　・https://www.slideshare.net/KentaOku/db10-48389863

参考リンク・MySQL公式　・https://dev.mysql.com/doc/refman/8.0/en/ ・MySQLの使い方　・https://www.dbonline.jp/mysql/

データベース研修

データベース研修

More Decks by techtekt

Other Decks in Programming

Featured

Transcript