MySQLに必要な共通知識のまとめ

MySQLに必要な共通知識のまとめ

この記事では、MySQL に必要な一般的な知識のポイントをまとめています。ご参考までに、詳細は以下の通りです。

最近、SQL を整理していたときに、素晴らしいメモを見つけました。それは、元の著者が SQL を学習したときに作成したメモでした。この要約を皆さんと共有したいと思います。これは、SQL の包括的なリーク検出とトラブルシューティングを行うのに役立ちます。元の著者 hjzCy の努力に感謝します。元のリンクは記事の下部にあります。間違いがあれば、皆さんで一緒に指摘していただければ幸いです。

MySQLサーバへのログインとログアウト

# MySQLにログイン
$ mysql -u ルート -p12345612

# MySQL データベース サーバーを終了します exit;

基本的な構文

--すべてのデータベースを表示 show databases;

-- データベースを作成します。CREATE DATABASE test;

-- スイッチデータベース使用テスト。

-- データベース内のすべてのテーブルを表示します。show tables;

-- データテーブルを作成する CREATE TABLE pet (
  名前 VARCHAR(20)、
  所有者 VARCHAR(20)、
  種 VARCHAR(20)、
  性別 CHAR(1)
  生年月日、
  死亡日
);

-- データ テーブル構造を表示します -- ペットを説明します。
desc ペット;

-- テーブルをクエリ SELECT * from pet;

-- データを挿入します INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- データを変更します。UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- データを削除します。DELETE FROM pet where name = 'squirrel';

-- テーブルを削除します。DROP TABLE myorder;

テーブル作成の制約

主キー制約

-- 主キー制約 -- フィールドを重複なしかつ空にせず、テーブル内のすべてのデータの一意性を保証します。
テーブルユーザーの作成 (
  id INT 主キー、
  名前 VARCHAR(20)
);

-- 共同主キー -- 共同主キーの各フィールドは空にできず、フィールドの合計は設定された共同主キーと同じにすることはできません。
テーブルユーザーの作成 (
  id INT、
  名前 VARCHAR(20)、
  パスワード VARCHAR(20)、
  主キー(ID, 名前)
);

-- 自動インクリメント制約 -- 自動インクリメント制約の主キーはシステムによって自動的に割り当てられます。
テーブルユーザーの作成 (
  id INT 主キー AUTO_INCREMENT、
  名前 VARCHAR(20)
);

-- 主キー制約を追加する -- 主キーの設定を忘れた場合は、SQL ステートメントを使用して設定することもできます (2 つの方法)。
ALTER TABLE user に PRIMARY KEY(id) を追加します。
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 主キーを削除します。ALTER TABLE user drop PRIMARY KEY;

一意の主キー

-- テーブルを作成するときに一意の主キーを作成します CREATE TABLE user (
  id INT、
  名前 VARCHAR(20)、
  UNIQUE(名前)
);

-- 一意の主キーを追加する -- テーブルの作成時に一意のキーを設定しなかった場合は、SQL ステートメントを使用して設定することもできます (2 つの方法)。
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 一意の主キーを削除します ALTER TABLE user DROP INDEX name;

非 Null 制約

-- テーブルを作成するときに非NULL制約を追加します -- フィールドがNULLにならないように制約します CREATE TABLE user (
  id INT、
  名前 VARCHAR(20) NOT NULL
);

-- 非NULL制約を削除します。ALTER TABLE user MODIFY name VARCHAR(20);

デフォルトの制約

-- テーブルを作成するときにデフォルトの制約を追加します -- フィールドのデフォルト値を制約します CREATE TABLE user2 (
  id INT、
  名前 VARCHAR(20)、
  年齢 INT デフォルト 10
);

-- 非 NULL 制約を削除します ALTER TABLE user MODIFY age INT;

外部キー制約

-- クラス CREATE TABLE クラス (
  id INT 主キー、
  名前 VARCHAR(20)
);

-- 学生テーブル CREATE TABLE students (
  id INT 主キー、
  名前 VARCHAR(20)、
  -- ここでのclass_idは、class_id INTのidフィールドと関連付けられる必要があります。
  -- class_id の値は、クラスの id フィールド値から取得する必要があることを示します。FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. メインテーブル（親テーブル）のクラスにないデータ値は、セカンダリテーブル（子テーブル）のクラスでは使用できません。
-- 2. プライマリ テーブル内のレコードがセカンダリ テーブルによって参照されている場合、プライマリ テーブルを削除することはできません。

3つの主要なデータベース設計パラダイム

1NF

フィールド値をさらに分割できる限り、最初の正規形は満たされません。

パラダイム設計は詳細であればあるほど、特定の実務には適しているかもしれませんが、必ずしも有益であるとは限らず、プロジェクトの実際の状況に応じて設定する必要があります。

2NF

最初のパラダイムを満たすという前提の下では、他の列は主キー列に完全に依存している必要があります。不完全な依存関係がある場合、それは複合主キーの場合にのみ発生します。

-- 注文テーブル CREATE TABLE myorder (
  製品ID INT、
  顧客ID INT、
  製品名 VARCHAR(20)、
  顧客名 VARCHAR(20)、
  主キー (product_id、customer_id)
);

実際、この注文テーブルでは、 product_name product_idにのみ依存し、 customer_name customer_idにのみ依存します。つまり、 product_nameとcustomer_idは無関係であり、 customer_nameとproduct_idも無関係です。

これは第 2 正規形を満たしていません。他のすべての列は主キー列に完全に依存している必要があります。

テーブルmyorderを作成します（
  order_id INT 主キー、
  製品ID INT、
  顧客ID INT
);

テーブル製品の作成 (
  id INT 主キー、
  名前 VARCHAR(20)
);

顧客テーブルを作成（
  id INT 主キー、
  名前 VARCHAR(20)
);

分割後、 myorderテーブルのproduct_idとcustomer_id order_id主キーに完全に依存するようになり、 productとcustomerテーブルの他のフィールドも主キーに完全に依存するようになります。 2番目のパラダイムの設計を満たします。

3NF

2 番目のパラダイムを満たすという前提では、主キー列以外の列間に推移的な依存関係があってはなりません。

テーブルmyorderを作成します（
  order_id INT 主キー、
  製品ID INT、
  顧客ID INT、
  顧客電話番号 VARCHAR(15)
);

テーブル内のcustomer_phone order_idとcustomer_id列に依存している可能性がありますが、これは第 3 正規形の設計を満たしていません。つまり、他の列間に推移的な依存関係は存在できません。

テーブルmyorderを作成します（
  order_id INT 主キー、
  製品ID INT、
  顧客ID INT
);

顧客テーブルを作成（
  id INT 主キー、
  名前 VARCHAR(20)、
  電話番号 VARCHAR(15)
);

変更後、他の列の間に推移的な依存関係はなくなります。他の列は主キー列にのみ依存し、第 3 正規形の設計を満たします。

クエリ演習

データを準備する

-- データベースを作成します。CREATE DATABASE select_test;
-- データベースを切り替える USE select_test;

-- 学生テーブルを作成する CREATE TABLE student (
  VARCHAR(20) PRIMARY KEYなし、
  名前 VARCHAR(20) NOT NULL,
  性別 VARCHAR(10) NOT NULL,
  誕生日 DATE, -- 誕生日クラス VARCHAR(20) -- クラス);

-- 教師テーブルを作成する CREATE TABLE teacher (
  VARCHAR(20) PRIMARY KEYなし、
  名前 VARCHAR(20) NOT NULL,
  性別 VARCHAR(10) NOT NULL,
  誕生日、
  職業 VARCHAR(20) NOT NULL, -- 職名 部門 VARCHAR(20) NOT NULL -- 部門);

-- コーステーブルを作成する CREATE TABLE course (
  VARCHAR(20) PRIMARY KEYなし、
  名前 VARCHAR(20) NOT NULL,
  t_no VARCHAR(20) NOT NULL、--教師番号--tnoが教師テーブルのnoフィールドの値から取得されることを示します FOREIGN KEY(t_no) REFERENCES teacher(no)
);

-- スコアテーブル CREATE TABLE score (
  s_no VARCHAR(20) NOT NULL, -- 学生番号 c_no VARCHAR(20) NOT NULL, -- コース番号 degree DECIMAL, -- 成績 -- s_no と c_no がそれぞれ学生テーブルとコーステーブルの no フィールド値から取得されることを示します。 FOREIGN KEY(s_no) REFERENCES student(no),
  外部キー(c_no) 参照コース(no)、
  -- s_no、c_noを共同主キーとして設定します PRIMARY KEY (s_no、c_no)
);

-- すべてのテーブルを表示する SHOW TABLES;

-- 学生テーブルデータを追加します INSERT INTO student VALUES('101', 'Zeng Hua', 'Male', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
学生にVALUES('103', '王丽', '女', '1976-01-23', '95033')を挿入します。
学生にVALUES('104', '李军', '男', '1976-02-20', '95033')を挿入します。
学生にVALUES('105', '王芳', '女', '1975-02-10', '95031')を挿入します。
学生にVALUES('106', '陸軍', '男性', '1974-06-03', '95031')を挿入します。
学生にVALUES('107', '王尼马', '男', '1976-02-20', '95033')を挿入します。
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
学生にVALUES('109', '赵铁柱', '男', '1974-06-03', '95031')を挿入します。

-- 教師テーブルデータを追加します INSERT INTO teacher VALUES('804', 'Li Cheng', '男性', '1958-12-02', '准教授', 'コンピューターサイエンス部門');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲座师', '電子工学部');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '教助手', 'コンピュータ系');
教師に VALUES('831', 'Liu Bing', '女性', '1977-08-14', 'ティーチングアシスタント', '電子工学科') を挿入します。

-- コースデータを追加します INSERT INTO course VALUES('3-105', 'Introduction to Computer Science', '825');
コースに VALUES('3-245', 'オペレーティング システム', '804') を挿入します。
コースに VALUES('6-166', 'デジタル回路', '856') を挿入します。
コースに VALUES('9-888', '上級数学', '831') を挿入します。

-- スコア テーブル データを追加します INSERT INTO score VALUES('103', '3-105', '92');
スコアにVALUES('103', '3-245', '86')を挿入します。
スコアにVALUES('103', '6-166', '85')を挿入します。
スコアにVALUES('105', '3-105', '88')を挿入します。
スコアにVALUES('105', '3-245', '75')を挿入します。
スコアにVALUES('105', '6-166', '79')を挿入します。
スコアにVALUES('109', '3-105', '76')を挿入します。
スコアにVALUES('109', '3-245', '68')を挿入します。
スコアにVALUES('109', '6-166', '81')を挿入します。

-- テーブル構造を表示します SELECT * FROM course;
スコアから*を選択します。
学生から*を選択します。
教師から*を選択します。

1から10

-- 学生テーブルのすべての行を照会します SELECT * FROM student;

-- 学生テーブルの名前、性別、クラスのフィールドのすべての行を照会します。SELECT name, sex, class FROM student;

-- 教師テーブル内の一意の部門列をクエリします -- 部門: 重複排除クエリ SELECT DISTINCT department FROM teacher;

-- スコアが 60 から 80 の間のスコア テーブルのすべての行をクエリします (間隔クエリと演算子クエリ)
-- BETWEEN xx AND xx: クエリ間隔、AND は「および」を意味します
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- スコア テーブル内のスコアが 85、86、または 88 の行を照会します -- IN: 仕様内の複数の値を照会します SELECT * FROM score WHERE degree IN (85, 86, 88);

-- クラスが '95031' であるか、性別が 'female' である学生テーブルのすべての行をクエリします -- or: or 関係を示します SELECT * FROM student WHERE class = '95031' or sex = 'female';

-- 学生テーブルのすべての行をクラスの降順でクエリします -- DESC: 降順、高から低へ -- ASC (デフォルト): 昇順、低から高へ SELECT * FROM student ORDER BY class DESC;
SELECT * FROM 学生 ORDER BY クラス ASC;

-- スコア テーブルのすべての行を c_no の昇順、degree の降順でクエリします。SELECT * FROM score ORDER BY c_no ASC, degree DESC;

-- クラス「95031」の生徒数を照会します -- COUNT: 統計 SELECT COUNT(*) FROM student WHERE class = '95031';

-- スコア テーブルで最高スコアを獲得した学生の学生 ID とコース ID をクエリします (サブクエリまたはソート クエリ)。
-- (SELECT MAX(degree) FROM score): 最高スコアを計算するサブクエリ SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

-- ソートクエリ -- LIMIT r, n: は、r 行目から開始して n データのクエリを実行することを意味します。SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

グループごとの平均スコアを計算する

各コースの平均成績を照会します。

-- AVG: 平均値 SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';

-- GROUP BY: グループクエリ SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

グループ化条件とあいまいクエリ

scoreテーブルで、少なくとも 2 人の学生が受講し、数字が 3 で始まるコースの平均スコアを照会します。

スコアから*を選択します。
-- c_no コース番号 +------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+

分析表によると、少なくとも 2 人の学生が3-105 、 3-245 、 6-166コースを選択しており、 3 で始まるコースは3-105と3-245です。つまり、 3-105と3-245のすべての平均degreeスコアを照会したいのです。

-- まず、グループ化して c_no、AVG(degree) をクエリします SELECT c_no、AVG(degree) FROM score GROUP BY c_no
+-------+--------------+
| c_no | AVG(度) |
+-------+--------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+--------------+

-- 少なくとも 2 人の学生が受講しているコースを照会します -- HAVING: HAVING COUNT(c_no) >= 2 であることを示します

-- コースは 3 から始まります -- LIKE はあいまいクエリを示し、「%」は「3」の後の任意の文字に一致するワイルドカードです。
c_no は '3%' のように動作します。

-- 前の SQL ステートメントを連結します。
-- 最後に COUNT(*) を追加すると、各グループの数も照会されます。
SELECT c_no、AVG(degree)、COUNT(*) FROM score GROUP BY c_no
COUNT(c_no) >= 2 かつ c_no が '3%' の場合;
+-------+-------------+-----------+
| c_no | AVG(度) | COUNT(*) |
+-------+-------------+-----------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
+-------+-------------+-----------+

複数テーブルクエリ - 1

すべての学生のname 、およびscoreテーブル内の学生に対応するc_noとdegreeを照会します。

学生から番号、名前を選択します。
+-----+-----------+
| いいえ | 名前 |
+-----+-----------+
| 101 | 曽華 |
| 102 | クアンミン |
| 103 | 王 麗|
| 104 | 李俊 |
| 105 | 王芳|
| 106 | 陸軍 |
| 107 | 王尼瑪 |
| 108 | 張全丹 |
| 109 | 趙鉄竹|
+-----+-----------+

s_no、c_no、degree を score から選択します。
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+

分析により、 scoreテーブルのs_noフィールド値を、 studentテーブルの対応するnameフィールド値に置き換えるだけでよいことがわかります。どうすればよいでしょうか?

-- FROM...: は、学生テーブルとスコア テーブルからのクエリを示します。 -- WHERE 条件は、student.no と score.s_no が等しい場合にのみ結果が表示されることを示します。
学生、スコアから名前、c_no、学位を選択します
ここで、student.no = score.s_no;
+-----------+-------+--------+
| 名前 | c_no | 学位 |
+-----------+-------+--------+
| 王 立 | 3-105 | 92 |
| 王 立 | 3-245 | 86 |
| 王 立 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 趙鉄竹 | 3-105 | 76 |
| 趙鉄竹 | 3-245 | 68 |
| 趙鉄竹 | 6-166 | 81 |
+-----------+-------+--------+

複数テーブルクエリ - 2

すべての学生のno 、コース名 ( courseテーブルのname )、成績 ( scoreテーブルではdegree ) の列を照会します。

学生scoreに関連付けられているのはnoのみなので、 scoreテーブルをクエリすると、学生に関連付けられているすべてのnoとdegreeを見つけることができます。

s_no、c_no、degree を score から選択します。
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+

次に、 courseテーブルをクエリします。

+-------+-----------------+
| いいえ | 名前 |
+-------+-----------------+
| 3-105 | コンピュータ入門 |
| 3-245 | オペレーティング システム |
| 6-166 | デジタル回路 |
| 9-888 | 上級数学 |
+-------+-----------------+

scoreテーブルのc_no 、 courseテーブルの対応するnameフィールド値に置き換えるだけです。

-- スコア テーブルとコース テーブルからそれぞれクエリを実行するためのクエリ フィールド名を追加します。
-- は、フィールドのエイリアスを取得することを意味します。
SELECT s_no、name as c_name、degree FROM score、course
ここで、 score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name | 学位 |
+------+-----------------+--------+
| 103 | コンピュータ入門 | 92 |
| 105 | コンピュータ入門 | 88 |
| 109 | コンピュータ入門 | 76 |
| 103 | オペレーティング システム | 86 |
| 105 | オペレーティング システム | 75 |
| 109 | オペレーティング システム | 68 |
| 103 | デジタル回路 | 85 |
| 105 | デジタル回路 | 79 |
| 109 | デジタル回路 | 81 |
+------+-----------------+--------+

3つのテーブル関連クエリ

すべての学生のname 、コース名 ( courseテーブル内のname )、 degree照会します。

scoreテーブルには学生 ID とクラス ID のみが関連付けられているため、 scoreテーブルに対してクエリを実行するだけで済みます。

スコアから*を選択します。
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+

s_noとc_no 、 studentテーブルとsrouseテーブルの対応するnameフィールド値に置き換えるだけです。

まず、 s_no studentテーブルのnameフィールドに置き換えます。

SELECT name、c_no、degree FROM student、score WHERE student.no = score.s_no;
+-----------+-------+--------+
| 名前 | c_no | 学位 |
+-----------+-------+--------+
| 王 立 | 3-105 | 92 |
| 王 立 | 3-245 | 86 |
| 王 立 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 趙鉄竹 | 3-105 | 76 |
| 趙鉄竹 | 3-245 | 68 |
| 趙鉄竹 | 6-166 | 81 |
+-----------+-------+--------+

次に、 c_no courseテーブルのnameフィールドに置き換えます。

-- コース テーブル SELECT no, name FROM course;
+-------+-----------------+
| いいえ | 名前 |
+-------+-----------------+
| 3-105 | コンピュータ入門 |
| 3-245 | オペレーティング システム |
| 6-166 | デジタル回路 |
| 9-888 | 上級数学 |
+-------+-----------------+

-- フィールド名が重複しているため、代わりに「テーブル名.フィールド名をエイリアスとして使用」を使用します。
SELECT 学生名をs_name、コース名をc_name、学位として
学生、スコア、コースから
ここで、student.NO = score.s_no
かつ、 score.c_no = course.no;

サブクエリとグループ化による平均スコアの算出

クラス95031の学生の各コースの平均スコアを照会します。

scoreテーブルで、 studentテーブルの生徒番号に従って生徒のクラス番号と成績をフィルター処理します。

-- IN (..): 選択した学生番号を s_no の条件として使用します SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+

現時点では、クラス95031の学生の各コースの平均スコアを取得するには、 c_noグループ化するだけで済みます。

SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+--------------+
| c_no | AVG(度) |
+-------+--------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+--------------+

サブクエリ - 1

コース3-105の学生番号109よりも高いスコアを持つ学生のすべてのレコードを照会します。

まず、クラス番号3-105を除外し、生徒番号109のスコアよりも高いスコアを持つすべての行を検索します。

スコアから*を選択
c_no = '3-105'の場合
AND 度 > (度を選択 FROM スコア WHERE s_no = '109' AND c_no = '3-105');

サブクエリ - 2

学生番号109のスコアよりも高いスコアを持つすべてのコース3-105のスコア レコードを照会します。

-- コース番号に制限はありませんが、コース3～105の学生番号109のスコアより高ければ大丈夫です。
スコアから*を選択
WHERE 度 > (SELECT 度 FROM スコア WHERE s_no = '109' AND c_no = '3-105');

YEAR 関数と IN キーワードを使用したクエリ

生徒番号101と108同じ年に生まれたすべてのno 、 name 、 birthday列を照会します。

-- YEAR(..): 日付から年を抽出します。SELECT no, name, birthday FROM student
WHERE YEAR(誕生日) IN (SELECT YEAR(誕生日) FROM student WHERE no IN (101, 108));

複数レベルのネストされたサブクエリ

教師'張旭'の生徒の成績表を照会します。

まず教師番号を見つけます:

SELECT NO FROM teacher WHERE NAME = '张旭'

sourseテーブルから教師のコース番号を見つけます。

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '张旭' );

コース番号を選択してスコア テーブルを照会します。

SELECT * FROM スコア WHERE c_no = (
  SELECT no FROM course WHERE t_no = (
    SELECT no FROM teacher WHERE NAME = '张旭'
  ）
);

複数テーブルクエリ

選択科目を受講する生徒が 5 人以上いる教師の名前を検索します。

まず、 teacherテーブルで、 noフィールドを使用して、教師の同じコースを受講している生徒が少なくとも 5 人いるかどうかを判断します。

-- 教師テーブルを照会します。SELECT no, name FROM teacher;
+-----+--------+
| いいえ | 名前 |
+-----+--------+
| 804 | リー・チェン|
| 825 | 王平|
| 831 | 劉冰|
| 856 | 張旭|
+-----+--------+

SELECT name FROM teacher WHERE no IN (
  -- 対応する条件はここで確認してください);

教師番号に関連するテーブルの情報を表示します。

コースから*を選択します。
-- t_no: 教師番号+-------+-----------------+------+
| いいえ | 名前 | t_no |
+-------+-----------------+------+
| 3-105 | コンピュータ入門 | 825 |
| 3-245 | オペレーティング システム | 804 |
| 6-166 | デジタル回路 | 856 |
| 9-888 | 上級数学 | 831 |
+-------+-----------------+------+

教師番号に関連するフィールドがcourseテーブルにあることがわかりましたが、どのコースに少なくとも 5 人の学生が登録されているかはまだわかりません。そのため、 scoreテーブルに基づいてクエリを実行する必要があります。

-- その前に、クエリ条件を充実させるためにスコアにいくつかのデータを挿入します。
スコアにVALUES('101', '3-105', '90')を挿入します。
スコアにVALUES('102', '3-105', '91')を挿入します。
スコアにVALUES('104', '3-105', '89')を挿入します。

-- スコア テーブルをクエリします SELECT * FROM score;
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+

-- スコア テーブルで c_no をグループとして使用し、c_no が少なくとも 5 つのデータ項目を保持するように制限します。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| いいえ |
+-------+
| 3-105 |
+-------+

フィルタリングされたコース番号に基づいて、コースに少なくとも 5 人の学生がいる教師番号を見つけます。

SELECT t_no FROM course WHERE no IN (
  SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| いいえ |
+------+
| 825 |
+------+

teacherテーブルで、フィルタリングされた教師番号に基づいて教師の名前を見つけます。

SELECT name FROM teacher WHERE no IN (
  -- 最終条件 SELECT t_no FROM course WHERE no IN (
    SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
  ）
);

サブクエリ - 3

「コンピュータサイエンス学科」のコースの成績証明書を確認してください。

アイデアとしては、まずcourseテーブルですべての計算機系コースの番号を見つけ、次にこの番号に基づいてscoreテーブルをクエリするというものです。

-- 教師テーブルを通じてコン​​ピュータサイエンス部門のすべての教師番号を照会します。SELECT no, name, department FROM teacher WHERE department = 'コンピュータサイエンス部門'
+-----+--------+--------------+
| 番号 | 名前 | 部門 |
+-----+--------+--------------+
| 804 | Li Cheng | コンピュータサイエンス学科 |
| 825 | 王平 | コンピュータサイエンス学科 |
+-----+--------+--------------+

-- コーステーブルを通じて教師のコース番号を照会する SELECT no FROM course WHERE t_no IN (
  SELECT no FROM teacher WHERE department = 'コンピュータサイエンス学部'
);
+-------+
| いいえ |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- フィルタリングされたコース番号に基づいてスコアテーブルをクエリする SELECT * FROM score WHERE c_no IN (
  SELECT no FROM course WHERE t_no IN (
    SELECT no FROM teacher WHERE department = 'コンピュータサイエンス学部'
  ）
);
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+--------+

UNIONとNOTINの使用

計算機系と電子工程系さまざまな専門職名を持つ教師を照会します。

-- NOT: 論理否定を表します SELECT * FROM teacher WHERE department = 'Computer Science' AND career NOT IN (
  SELECT 職業 FROM 教師 WHERE 部門 = '電子工学部'
）
-- 2つのセットを結合するUNION
SELECT * FROM teacher WHERE department = '電子工学科' AND career NOT IN (
  SELECT 職業 FROM 教師 WHERE 部門 = 'コンピュータサイエンス部門'
);

ANYは少なくとも1つを意味します - DESC（降順）

スコアが3-245より <u>少なくとも</u> 高い3-105コースのscoreテーブルを照会します。

SELECT * FROM スコア WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+--------+

SELECT * FROM スコア WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
+------+-------+--------+

-- ANY: SQL ステートメント内の任意の条件を満たします。
つまり、3～105 のスコアの中に、3～245 から選択したどの行よりも大きいスコアが 1 つあれば、条件を満たします。
--最後に、結果を降順でクエリします。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(
  SELECT degree FROM score WHERE c_no = '3-245'
) 度数順で並べ替え DESC;
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+------+-------+--------+

全て

3-105コースと3-245より高い成績のscoreテーブルを照会します。

-- 前の質問に少しだけ修正を加えます。
-- ALL: SQL ステートメント内のすべての条件を満たします。
-- つまり、条件を満たすには、3 ～ 105 の各行のスコアが、3 ～ 245 から除外されたすべての行のスコアよりも大きくなければなりません。
SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(
  SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
+------+-------+--------+

テーブルデータを条件付きクエリとしてコピーする

コースの平均scoreよりも低いスコアを持つコースのスコア テーブルを照会します。

-- 平均スコアを照会します SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+-------+--------------+
| c_no | AVG(度) |
+-------+--------------+
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+--------------+

-- スコア テーブルをクエリします。SELECT degree FROM score;
+--------+
| 学位 |
+--------+
| 90 |
| 91 |
| 92 |
| 86 |
| 85 |
| 89 |
| 88 |
| 75 |
| 79 |
| 76 |
| 68 |
| 81 |
+--------+

-- テーブル b をテーブル a に適用してデータを照会します -- スコア a (b): テーブルを a (b) として宣言します。
-- このように、クエリを実行する条件として a.c_no = b.c_no を使用できます。
SELECT * FROM スコア a WHERE 度 < (
  (SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+--------+

サブクエリ - 4

コースを教えるすべての教師のnameとdepartmentを照会します（ courseテーブルにコースがあります） 。

SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| 名前 | 部門 |
+--------+-----------------+
| Li Cheng | コンピュータサイエンス学科 |
| 王平 | コンピュータサイエンス学科 |
| 劉 冰| 電子工学科|
| 張旭 | 電子工学科 |
+--------+-----------------+

条件付きグループフィルタリング

少なくとも 2 人の男子生徒がいるclassのstudentテーブルを照会します。

-- 学生テーブル情報を表示する SELECT * FROM student;
+-----+-----------+-----+-----------+-------+
| 番号 | 名前 | 性別 | 誕生日 | クラス |
+-----+-----------+-----+-----------+-------+
| 101 | Zeng Hua | 男性 | 1977-09-01 | 95033 |
| 102 | クアンミン | 男性 | 1975-10-02 | 95031 |
| 103 | 王麗 | 女性 | 1976-01-23 | 95033 |
| 104 | 李軍 | 男性 | 1976-02-20 | 95033 |
| 105 | 王芳 | 女性 | 1975-02-10 | 95031 |
| 106 | 陸軍 | 男性 | 1974-06-03 | 95031 |
| 107 | 王尼瑪 | 男性 | 1976-02-20 | 95033 |
| 108 | 張全丹 | 男性 | 1975-02-10 | 95031 |
| 109 | 趙鉄竹 | 男性 | 1974-06-03 | 95031 |
| 110 | 張飛 | 男性 | 1974-06-03 | 95038 |
+-----+-----------+-----+-----------+-------+

-- 男性の性別のみを照会し、クラスごとにグループ化し、クラス行が 1 より大きい値に制限されます。
SELECT class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| クラス |
+-------+
|95033|
|95031|
+-------+

NOTLIKE ファジークエリ否定

studentテーブルで、姓が「Wang」ではない学生のレコードを照会します。

-- NOT: 否定 -- LIKE: あいまいクエリ mysql> SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+-----------+-------+
| 番号 | 名前 | 性別 | 誕生日 | クラス |
+-----+-----------+-----+-----------+-------+
| 101 | Zeng Hua | 男性 | 1977-09-01 | 95033 |
| 102 | クアンミン | 男性 | 1975-10-02 | 95031 |
| 104 | 李軍 | 男性 | 1976-02-20 | 95033 |
| 106 | 陸軍 | 男性 | 1974-06-03 | 95031 |
| 108 | 張全丹 | 男性 | 1975-02-10 | 95031 |
| 109 | 趙鉄竹 | 男性 | 1974-06-03 | 95031 |
| 110 | 張飛 | 男性 | 1974-06-03 | 95038 |
+-----+-----------+-----+-----------+-------+

YEAR 関数と NOW 関数

studentテーブル内の各学生の名前と年齢を照会します。

-- YEAR(NOW()) 関数を使用して現在の年を計算し、生年を減算して年齢を取得します。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| 名前 | 年齢 |
+-----------+------+
| 曽華 | 42 |
| クアンミン | 44 |
| 王 麗| 43 |
| 李軍 | 43 |
| 王芳 | 44 |
| 陸軍 | 45 |
| ワン・ニマ | 43 |
| 張全丹 | 44 |
| 趙鉄竹 | 45 |
| 張飛 | 45 |
+-----------+------+

MAX関数とMIN関数

studentテーブル内のbirthday最大値と最小値を照会します。

学生からMAX(誕生日)、MIN(誕生日)を選択します。
+---------------+---------------+
| MAX(誕生日) | MIN(誕生日) |
+---------------+---------------+
| 1977-09-01 | 1974-06-03 |
+---------------+---------------+

複数セグメントのソート

classとbirthdayの降順でstudentテーブルをクエリします。

SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+-----------+-------+
| 番号 | 名前 | 性別 | 誕生日 | クラス |
+-----+-----------+-----+-----------+-------+
| 110 | 張飛 | 男性 | 1974-06-03 | 95038 |
| 103 | 王麗 | 女性 | 1976-01-23 | 95033 |
| 104 | 李軍 | 男性 | 1976-02-20 | 95033 |
| 107 | 王尼瑪 | 男性 | 1976-02-20 | 95033 |
| 101 | Zeng Hua | 男性 | 1977-09-01 | 95033 |
| 106 | 陸軍 | 男性 | 1974-06-03 | 95031 |
| 109 | 趙鉄竹 | 男性 | 1974-06-03 | 95031 |
| 105 | 王芳 | 女性 | 1975-02-10 | 95031 |
| 108 | 張全丹 | 男性 | 1975-02-10 | 95031 |
| 102 | クアンミン | 男性 | 1975-10-02 | 95031 |
+-----+-----------+-----+-----------+-------+

サブクエリ - 5

「男性」教師と彼らが教えるコースについて問い合わせます。

SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| いいえ | 名前 | t_no |
+-------+--------------+------+
| 3-245 | オペレーティング システム | 804 |
| 6-166 | デジタル回路 | 856 |
+-------+--------------+------+

サブクエリを使用した MAX 関数

最高得点を獲得した生徒のscoreテーブルを照会します。

-- 最高スコアを検索します (このクエリの結果は 1 つだけです)
SELECT MAX(degree) FROM score;

-- 上記の条件に従って、最高スコアのテーブルをすべてフィルタリングします。
-- 度数値が条件を複数回満たすと仮定すると、このクエリには複数の結果が含まれる場合があります。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no | 学位 |
+------+-------+--------+
| 103 | 3-105 | 92 |
+------+-------+--------+

サブクエリ - 6

「Li Jun」と同じ性別のクラスメート全員のname照会します。

-- まず、Li Jun の性別を条件として選択します。SELECT sex FROM student WHERE name = 'Li Jun';
+-----+
| セックス |
+-----+
| 男性 |
+-----+

-- 性別に基づいて名前と性別を照会する
名前、セックスを選択した学生からのセックス=（
  学生からセックスを選択します= '李军'
);
+-----------+-----+
|名前|
+-----------+-----+
| zeng hua |
| kuang ming |
|
|軍隊|
| wang nima |
| Zhang Quandan |
| zhao tiezhu |
| ZHANG FEI |
+-----------+-----+

サブクエリ-7

「李军」と同じ性別とクラスのクラスメートnameを照会します。

名前、性別、セックス=（
  学生からセックスを選択します= '李军'
）およびclass =（
  学生からクラスを選択します= '李军'
);
+----------+-----+------+
|名前|
+----------+-----+------+
| Zeng Hua |
|
| wang nima |
+----------+-----+------+

サブクエリ-8

「コンピューターサイエンスの紹介」というコースを受講したすべての男子学生のトランスクリプトを照会します。

必要な「コンピューターサイエンスの紹介」と性別の「男性」は、 courseとstudentテーブルにあります。

select * from score where c_no =（
  name = 'コンピューターサイエンスの紹介」を選択します。
）およびs_no in（
  セックス= '男'から学生からいいえを選択します
);
+------+-------+--------+
| s_no |
+------+-------+--------+
| 101 |
| 102 |
| 104 |
| 109 | 3-105 | 76 |
+------+-------+--------+

レベルごとに検索します

学生の成績を表してデータを挿入するgradeテーブルを作成します。

テーブルグレードを作成します（
  low int（3）、
  upp int（3）、
  グレードチャー（1）
);

グレード値に挿入（90、100、 'a'）;
グレード値に挿入（80、89、 'b'）;
グレード値に挿入（70、79、 'c'）;
グレード値に挿入（60、69、 'd'）;
グレード値に挿入（0、59、 'e'）;

select * from Grade;
+------+------+-------+
|
+------+------+-------+
| 90 |
| 89 |
| 70 |
| 69 |
| 0 |
+------+------+-------+

すべての学生のs_no 、 c_no 、およびgrade列をクエリします。

アイデアは、間隔（ BETWEEN ）クエリを使用して、学生の成績（ degree ）がgradeテーブルのlowとuppの間であるかどうかを判断することです。

S_NO、C_NO、スコア、グレードのグレードを選択します
ローとuppの間の程度。
+------+-------+-------+
| s_no |
+------+-------+-------+
| 101 |
| 102 |
| 103 |
| 103 |
| 103 |
| 104 |
| 105 |
| 105 |
| 105 |
| 109 |
| 109 |
| 109 |
+------+-------+-------+

接続クエリ

接続クエリをテストするためのデータを準備します。

データベースtestjoinを作成します。

テーブルの人を作成します（
  id INT、
  名前 VARCHAR(20)、
  Cardid Int
);

テーブルカードを作成します（
  id INT、
  名前 VARCHAR(20)
);

カード値に挿入（1、「ライスカード」）、（2、「建設銀行カード」）、（3、「農業銀行カード」）、（4、「産業および商業カード」）、（5、「郵便カード」）;
select * from card;
+------+------------+
| ID | 名前 |
+------+------------+
| 1 | ミールカード|
| 2 | CCB カード|
| 3 | 農業銀行カード|
| 4 | 名刺|
| 5 | 郵便カード |
+------+------------+

人の値（1、 '张三'、1）、（2、 '李四'、3）、（3、 '王五'、6）に挿入します。
select * from person;
+------+--------+--------+
| ID | 名前 | カード ID |
+------+--------+--------+
| 1 | 張三 | 1 |
| 2 | 李思 | 3 |
| 3 | 王武 | 6 |
+------+--------+--------+

2つのテーブルを分析した後、 person TableにはcardIdフィールドのcardテーブルに対応するid外部キーがないことがわかりました。設定6と、 cardId値がcardテーブルに存在しないため、 cardIdフィールド値がperson挿入できません。

内部結合

これら2つのテーブルの関連データを照会するには、 INNER JOINを使用してそれらを接続できます。

 - 内側結合：2つのテーブルを一緒に結合する内部結合を示します。
 - オン：特定の条件が実行されることを示します。
select * from person inner jock on person.cardid = card.id;
+------+--------+--------+-------+----------+
| ID | 名前 | カード ID | ID | 名前 |
+------+--------+--------+-------+----------+
| 1 | 張三| 1 | 1 | 食事カード|
| 2 | 李思 | 3 | 3 | 農業銀行カード |
+------+--------+--------+-------+----------+

 - 内側のキーワードを省略すると、同じ結果が生成されます。
-select * from person in card on person.cardid = card.id;
注： cardのテーブル全体が右側にリンクされています。

左外部結合

左のテーブル（ person ）が完全に表示され、条件を満たしている場合はNULLのテーブルが表示されます。

 - 左結合は、左外の結合とも呼ばれます。
select * from person left join card on person.cardid = card.id;
+------+--------+--------+-------+----------+
| ID | 名前 | カード ID | ID | 名前 |
+------+--------+--------+-------+----------+
| 1 | 張三| 1 | 1 | 食事カード|
| 2 | 李思 | 3 | 3 | 農業銀行カード |
| 3 | 王武 | 6 | NULL | NULL |
+------+--------+--------+-------+----------+

右外のリンク

右側のテーブル（ card ）が完全に表示されNULL 。

select * from person right join card on person.cardid = card.id;
+------+--------+--------+-------+----------+
| ID | 名前 | カード ID | ID | 名前 |
+------+--------+--------+-------+----------+
| 1 | 張三| 1 | 1 | 食事カード|
| 2 | 李思 | 3 | 3 | 農業銀行カード |
| NULL | NULL | NULL | 2 | CCB カード |
| NULL | NULL | NULL | 4 | 名刺 |
| NULL | NULL | NULL | 5 | 郵便カード |
+------+--------+--------+-------+----------+

完全な外部リンク

2つのテーブルのすべてのデータを完全に表示します。

-MySQLは、この構文で完全な外側結合をサポートしません-select * from person full join card on person.cardid = card.id;
 - エラーが発生しました：
 - エラー1054（42S22）：不明な列 'person.cardid' in 'on句'

-MySQLフル接続構文、Unionを使用して2つのテーブルをマージします。
select * from person left join card on person.cardid = card.id
連合
select * from person right join card on person.cardid = card.id;
+------+--------+--------+-------+----------+
| ID | 名前 | カード ID | ID | 名前 |
+------+--------+--------+-------+----------+
| 1 | 張三| 1 | 1 | 食事カード|
| 2 | 李思 | 3 | 3 | 農業銀行カード |
| 3 | 王武 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | CCB カード |
| NULL | NULL | NULL | 4 | 名刺 |
| NULL | NULL | NULL | 5 | 郵便カード |
+------+--------+--------+-------+----------+

取引

MySQLでは、トランザクションは実際には分離不可能な作業の最小単位です。トランザクションは、ビジネスの完全性を確保することができます。

たとえば、銀行の譲渡：

-a-> -100
更新ユーザーセットマネー=マネー-100ここでname = 'a';

-B-> +100
ユーザーセットマネーを更新=お金 + 100ここでname = 'b';

実際のプロジェクトでは、1つのSQLステートメントのみが正常に実行され、もう1つの失敗が失敗すると仮定すると、データは一貫していません。

したがって、複数の関連するSQLステートメントを実行する場合、トランザクションは、これらのSQLステートメントを同時に正常に実行するか、すべてが失敗することを要求する場合があります。

トランザクションを制御する方法 - コミット /ロールバック

MySQLでは、トランザクションの自動コミットステータスがデフォルトで有効になっています。

 - トランザクションの自動コミットステータスをクエリ@@ autocommitを選択します。
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+

自動提出の役割：SQLステートメントを実行すると、それが生成する効果がすぐに反映され、ロールバックできません。

ロールバックとは何ですか？例えば：

データベースバンクを作成します。

銀行を使用します。

テーブルユーザーの作成 (
  id INT 主キー、
  名前 VARCHAR(20)、
  Money Int
);

ユーザー値に挿入（1、 'a'、1000）;

[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
+----+------+-------+

MySQLのトランザクションが自動的にデータベースに送信するため、挿入ステートメントが実行された直後にデータが有効になることがわかります。その後、いわゆるロールバックは、実行されたすべてのSQLステートメントを元に戻し、データが最後に送信されたときに状態にロールバックすることを意味します。

mysqlでROLLBACKを使用してロールバックを実行します。

 - 最後のコミットロールバックへのロールバック。

[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
+----+------+-------+

実行されたすべてのSQLステートメントが提出されているため、データは展開されていません。では、どのようにしてデータをロールバックできますか？

-- 自動コミットをオフにする SET AUTOCOMMIT = 0;

 - 自動送信ステータスを照会@@ autocommitを選択します。
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+

自動提出が閉じられた後、テストデータがロールバックされます。

ユーザー値に挿入（2、 'B'、1000）;

 - オートコミットを閉じた後、データの変更は仮想一時的なデータテーブルに表示されます。
 - 変更されたデータは、実際にはデータテーブルに挿入されていません。
[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
| 2 | b | 1000 |
+----+------+-------+

 - データテーブルの実際のデータは実際には次のとおりです。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
+----+------+-------+

 - データはまだ実際に送信されていないため、ロールバックロールバックを使用できます。

-select * from from areを削除します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
+----+------+-------+

では、仮想データをデータベースに実際にどのように送信しますか？ COMMITの使用：

ユーザー値に挿入（2、 'B'、1000）;
 - 手動でデータを送信（永続性）、
 - データは実際にはデータベースに送信され、送信されたデータは実行後に巻き戻すことはできません。
専念;

 - 提出後にロールバックをテストします。

 - 再びクエリ（ロールバックが無効です）
[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
| 2 | b | 1000 |
+----+------+-------+

要約する

  1. 自動提出

    • 自動提出ステータスを確認してください： SELECT @@AUTOCOMMIT 。
    • 自動提出ステータスを設定します： SET AUTOCOMMIT = 0 。
  2. 手動提出

    @@AUTOCOMMIT = 0の場合、 COMMITコマンドを使用してトランザクションを送信します。

  3. トランザクションロールバック

    @@AUTOCOMMIT = 0の場合、 ROLLBACKコマンドを使用してトランザクションをロールバックします。

取引の実際の適用については、銀行振込プロジェクトに戻りましょう。

 - 転送更新ユーザーセットマネー=マネー-100ここでname = 'a';

 - ユーザーセットマネー=マネー + 100ここで、name = 'b';

[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
|
|
+----+------+-------+

この時点で、移転中に事故が発生した場合、 ROLLBACKを使用して最後に提出された状態にロールバックできます。

 - 移転中に事故が発生し、ロールバックする必要があると仮定します。
ロールバック;

[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
| 2 | b | 1000 |
+----+------+-------+

この時点で、私たちは事故の前に州に戻りました。つまり、この取引は私たちの言葉に戻る機会を提供します。データに事故がないと仮定すると、データをデータテーブルに手動で送信できます： COMMIT 。

手動でトランザクションを開始 - トランザクションを開始 /開始します

トランザクションのデフォルトのコミットが有効になった後（ @@AUTOCOMMIT = 1 ）、現時点ではトランザクションロールバックを使用できません。ただし、トランザクションイベントを手動で有効にすることができ、ロールバックが可能になります。

 - トランザクションを手動で開始するために、トランザクションを手動で開始するか、トランザクションを開始するためにトランザクションを手動で開始します。
始める;
更新ユーザーセットマネー=マネー-100ここでname = 'a';
ユーザーセットマネーを更新=お金 + 100ここでname = 'b';

 - 手動で開いたトランザクションが自動的にコミットしないため、
 - 現時点で変更されるデータは、まだ一時的なテーブルに保存されます。
[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
|
|
+----+------+-------+

 - ロールバックをテストします。

[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
| 1 | 1000 |
| 2 | b | 1000 |
+----+------+-------+

データはまだCOMMITを使用して送信され、このトランザクションのロールバックは提出後に再び発生することはできません。

始める;
更新ユーザーセットマネー=マネー-100ここでname = 'a';
ユーザーセットマネーを更新=お金 + 100ここでname = 'b';

[fromユーザー]を選択します。
+----+------+-------+
| ID | 名前 | お金 |
+----+------+-------+
|
|
+----+------+-------+

 - データコミットを送信します。

 - テストロールバック（テーブルのデータが送信されたため無効）
ロールバック;

トランザクションの酸性特性と使用

取引の 4 つの主な特徴:

  • 原子性：トランザクションは最小のユニットであり、再び分割することはできません。
  • C一貫性：同じトランザクションのSQLステートメントは、同時に成功または失敗するために保証する必要があります。
  • I分離：トランザクション1とトランザクション2の間に分離があります。
  • D持続性：トランザクションが完了すると（ COMMIT ）、返されません（ ROLLBACK ）。

トランザクションの分離

トランザクションの分離は、4つのタイプ（低いものから高へのパフォーマンス）に分けることができます。

  1. コミットを読む（提出されていないことを読む）

    複数のトランザクションがある場合、トランザクションは他のトランザクションのコミットされていないデータを見ることができます。

  2. コミットメントを読む（コミットされた読み物）

    他のトランザクションによって提出されたデータのみを読み取ることができます。

  3. 繰り返し読む（繰り返し読むことができる）

    複数の接続がトランザクションを有効にしている場合、データレコードをトランザクション間で共有できません。それ以外の場合は、コミットされたレコードのみを共有できます。

  4. シリアル化可能（シリアル化）

    すべてのトランザクションは固定順序で実行され、次のトランザクションの書き込み操作は1つのトランザクションが実行された後も実行されます。

現在のデータベースのデフォルトの分離レベルを確認します。

-MySQL 8.x、Globalはシステムレベルを表し、セッションレベルを追加しません。
@@ global.transaction_isolationを選択します。
@@ transaction_isolationを選択します。
+--------------------------------+
|
+--------------------------------+
|。
+--------------------------------+

-MySQL 5.x
@@global.tx_isolationを選択します。
@@ tx_isolationを選択します。

分離レベルを変更します。

 - システムの分離レベルを設定すると、レベルの後に分離レベルが設定されます（コミットされていない読み取り）。
グローバルトランザクション分離レベルを設定してください。

 - システムの分離レベルを照会し、変更されていることがわかります。
@@ global.transaction_isolationを選択します。
+--------------------------------+
|
+--------------------------------+
| 読み取り未コミット |
+--------------------------------+

ダーティリード

read committedの分離をテストします（送信されていない読み取り） ：

ユーザー値に挿入（3、 'Xiao Ming'、1000）;
ユーザー値に挿入（4、 'taobao store'、1000）;

[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
+---+--------------+

 - トランザクション操作データをオンにします -  Xiao MingがTaobaoストアで800元の靴を購入したとします。
トランザクションを開始します。
更新ユーザーセットマネー=マネー-800ここでname = 'xiao ming';
更新ユーザーセットマネー=マネー + 800ここで、name = 'taobao store';

 - その後、Taobaoストアは相手の結果をチェックし、お金が届いたことを発見しました。
[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
+---+--------------+

Xiao Mingの転送は新たに開かれたトランザクションで運用されており、この操作の結果は他のトランザクション（他者のTaobaoストア）で見ることができるため、Taobaoストアのクエリ結果は正しく、Taobaoストアは領収書を確認します。しかし、現時点では、Xiao MingがそれがあったトランザクションでROLLBACKコマンドを実行した場合はどうなりますか？

-Xiao Mingのトランザクションロールバック。

 - 相手が誰であろうと、結果をもう一度検索すると、次のことがわかります。
[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
+---+--------------+

これはDirty Readingと呼ばれ、あるトランザクションは別のトランザクションによってまだ提出されていないデータを読み取ります。これは、実際の開発で発生することは許可されていません。

コミットされた読み取り

隔離レベルを設定してコミットして読む：

グローバルトランザクション分離レベルの読み取りを設定します。
@@ global.transaction_isolationを選択します。
+--------------------------------+
|
+--------------------------------+
| コミット読み取り |
+--------------------------------+

このようにして、新しいトランザクションが入ったとき、提出されたトランザクションデータのみを照会することができます。しかし、現在のトランザクションでは、彼らはまだコミットされていないデータ、たとえば次のようです。

 - 操作データトランザクション（現在のトランザクション）
トランザクションを開始します。
更新ユーザーセットマネー=マネー-800ここでname = 'xiao ming';
更新ユーザーセットマネー=マネー + 800ここで、name = 'taobao store';

 - 分離レベルは、現在のトランザクションでコミットされた読み取りに設定されていますが、
 - それが見ているのは、実際に送信されたデータではなく、データテーブルのデータを一時的に変更することです。
[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
+---+--------------+


 - この時点で新しいトランザクションがリモートで開かれ、データベースに接続されていると仮定します。
$ mysql -u root -p12345612

 - 現時点では、リモート接続によってクエリされたデータは、送信されたユーザーからのselect *のみになります。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
+---+--------------+

しかし、これにはまだ問題があります。つまり、トランザクションがデータで動作する場合、他のトランザクションがトランザクションのデータに干渉します。例えば：

 -  Xiao Zhangは、データを照会するときに発見されました。
[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
+---+--------------+

 -  Xiao Zhangがテーブルのお金の平均値を見つける前に、Xiao Wangは操作を行いました。
トランザクションを開始します。
ユーザー値に挿入（5、 'c'、100）;
専念;

 - テーブルの実際のデータは次のとおりです。
[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
| 5 |
+---+--------------+

 -  Xiao Zhangが再び平均値を見つけたとき、計算は一致しません。
ユーザーからavg（money）を選択します。
+------------+
| AVG（お金）|
+------------+
|
+------------+

Commitedの読み取りにより、他のトランザクションによって提出されたデータのみを読むことができますが、まだ問題があります。つまり、同じテーブルからデータを読むとき、矛盾が発生する可能性があります。これは、繰り返しのない読書の現象と呼ばれます（読み取りコミット） 。

ファントムリード

分離レベルを再現可能な読み取りに設定します（繰り返し読むことができます） ：

グローバルトランザクション分離レベルの再現可能な読み取りを設定します。
@@ global.transaction_isolationを選択します。
+--------------------------------+
|
+--------------------------------+
| 繰り返し読み取り |
+--------------------------------+

繰り返し可能な読み取りをテストし、2つの異なる接続でSTART TRANSACTIONが個別に実行されると仮定して、

-Xiao Zhangdu-chengdu Start Transaction;
ユーザー値に挿入（6、 'd'、1000）;

-Xiao Wang- Beijing Start Transaction;

-Xiao Zhang -Chengdu Commit;

現在のトランザクションが開始された後、コミットする前に照会することはできず、コミット後に照会することができます。ただし、コミットする前に他のトランザクションが有効になっている場合、このトランザクションラインでは操作トランザクションとの接続は見つかりません。別のスレッドを開くのと同等です。

Xiao ZhangがCOMMITを実行したかどうかに関係なく、Xiao WangはXiao Zhangのトランザクションレコードを照会しませんが、彼自身のトランザクションの記録のみを照会します。

[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
| 5 |
+---+--------------+

これは、 Xiao Wangがこれより前に新しいトランザクション（ START TRANSACTION ）を開始したため、彼の新しいトランザクションラインでは、他のトランザクションとの接続がないためです。つまり、他のトランザクションが現時点でデータを操作している場合、それはわかりません。

ただし、実際のデータテーブルでは、Xiao Zhangがデータを挿入したということです。しかし、Xiao Wangは現時点では同じデータを挿入しませんでした。

ユーザー値に挿入（6、 'd'、1000）;
 - エラー1062（23000）：キー「プライマリ」のエントリ '6'を重複させる

エラーが報告され、操作は6の主キーを持つフィールドがあると言われました。この現象は、他のトランザクションによって送信されるデータを読み取りすることもできます。

シリアル化

名前が示すように、すべてのトランザクション書き込み操作はシリアル化されています。どういう意味ですか？分離レベルをシリアル化可能に変更します。

グローバルトランザクション分離レベルのシリアル化可能な設定。
@@ global.transaction_isolationを選択します。
+--------------------------------+
|
+--------------------------------+
| シリアル化可能 |
+--------------------------------+

Xiao ZhangとXiao Wangを例として取りましょう。

-Xiao Zhangdu-chengdu Start Transaction;

-Xiao Wang- Beijing Start Transaction;

 - トランザクションを開始する前に、テーブルを照会し、操作データを準備します。
[fromユーザー]を選択します。
+---+--------------+
| ID | 名前 | お金 |
+---+--------------+
|
|
| 3 |
| 4 |
| 5 |
| 6 |
+---+--------------+

 - 私は7番の王Xiaohuaがないことがわかったので、データを挿入しました。
ユーザー値に挿入（7、 'Wang Xiaohua'、1000）;

現時点ではどうなりますか？現在の分離レベルはシリアル化可能（シリアル化）であるため、シリアル化は次のとおりです。すべてのトランザクションがシリアルキューに配置されると仮定すると、すべてのトランザクションが固定順序で実行され、1つのトランザクションが実行された後、次のトランザクションの書き込み操作が引き続き実行されます（ 1つのトランザクションのみがキューで実行できることを意味します）。

この説明によると、Xiao Wangがデータを挿入すると、Xiao Zhangが存在するトランザクションを終了するためにCOMMITするか、待機時間が発生するまで、彼は待機状態を持ちます。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL クエリ スキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキル」、および「MySQL データベース ロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません:
  • PHPのMySQLIとMySQLの違いに関するいくつかの知識ポイント分析
  • MySQLの基礎知識のまとめ
  • MySQL アーキテクチャのナレッジポイントの概要
  • mysql接続の原理の知識ポイントクエリに参加する
  • コンピューター試験の第2レベルのMySQL知識ポイントに一般的に使用されるmysqlコマンド
  • MySQLの要約は、コマンドの知識ポイントを変更します
  • MySQLデータベースの基本的な知識ポイントと操作の概要
  • MySQL 最適化における B ツリー インデックスの知識ポイントのまとめ
  • MySql の知識ポイント: トランザクション、インデックス、ロックの原則、使用状況の分析

<<:  Nginx で CDN サーバーを構築する方法の詳細な説明 (画像とテキスト)

>>:  vue3 のさまざまなファイルタイプのプレビュー機能の例

推薦する

MySQLストレージエンジンについて学びましょう

目次序文1. MySQL メインストレージエンジン: 2. さまざまなストレージエンジンがテーブルを...

Postman 自動インターフェーステストの実践

目次背景説明GETリクエストの作成事前リクエストスクリプトで署名を作成するスクリプトは環境変数に書き...

面接の質問: 3 行 3 列のレイアウト、表は結合され、ネストされた表は許可されません

面接の質問で、3 行 3 列のレイアウトが求められます。1 行目の 2 番目の列と 2 行目の 2 ...

MySQL pt-slave-restart ツールの使い方の紹介

目次MySQL マスター スレーブ レプリケーション環境を設定する場合、マスター データベースとスレ...

HTML テーブル境界制御実装コード

一般的に、テーブルを使用する場合は、常に <table border="1"...

CSSインジェクションの知識の要約

最近のブラウザでは、CSS 内で JavaScript を実行することはできなくなりました。以前は、...

メタを使用してトラフィックキャッシュをキャンセルし、ページにアクセスするたびにページを更新して簡単にデバッグできるようにします。

コードをコピーコードは次のとおりです。 <!-- ブラウザがローカル キャッシュからページにア...

Dockerは同じIPネットワークセグメントとの接続を実現する

最近、Docker とホストが同じネットワーク セグメント上で通信する問題を解決し、そのプロセス全体...

マウスを動かしたときに画像のズーム効果とゆっくりとした遷移​​効果を実現するCSSのサンプルコード

transform:scale()比例したズームインまたはズームアウトを実現できます。 transi...

この構成ファイルの排他ロックに失敗したという VMware 仮想マシンのプロンプトの解決方法

VMware が異常シャットダウンした後、再起動すると「この構成ファイルを排他的にロックできませんで...

HTML テキストエスケープのヒント

今日、CSDN で HTML テキスト エスケープのちょっとしたトリックを見ましたが、とても簡単です...

MySQLとSpringの自動コミットの詳細な説明

1 MySQLの自動コミット設定MySQL はデフォルトで自動的にコミットします。つまり、各 DML...

Linuxがすべてのコマンドをサポートしていない問題の解決策

Linux がすべてのコマンドをサポートしていない場合はどうすればいいですか?すべてのLinuxコマ...

Dockerカスタムネットワークコンテナ相互接続

目次序文-リンクカスタムネットワーク質問する序文前回は、 -Linkパラメータを使用してコンテナ間の...

Element-ui アップロードファイルのアップロード制限の解決策

質問アップロードするファイルのタイプを accept に追加することは、「表面的な」役割しか果たしま...