データベース研修

by techtekt

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

データベース

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

RDBMS

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

MySQL

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

SQL

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

制約

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

ユーザの追加と権限

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

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

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

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

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

テーブル設計

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

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

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

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

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

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

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

参考リンク

Slide 116

Slide 116 text

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

Slide 117

Slide 117 text

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

Slide 118

Slide 118 text

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