MySQL資料庫操作（7）聯表查詢、事務

標籤：填充   狀態   系統故障   max   port   style   cross   not   篩選   

聯表查詢

內串連`[INNER| CROSS] JOIN`

無條件內串連：
無條件內串連，又名交叉串連/笛卡爾串連
第一張表種的每一項會和另一張表的每一項依次組合

有條件內串連：
在無條件的內串連基礎上，加上一個ON子句
當串連的時候，篩選出那些有實際意義的記錄行來進行拼接

在寫條件時注意兩張表的列名是否一樣，
如果時一樣的則要在前面加上表名，tb_name.colname這種形式存在

例：
無條件內串連

SELECT * FROM `student` INNER JOIN `department`;

+------+--------+---------+----+----------+| s_id | name   | dept_id | id | name     |+------+--------+---------+----+----------+|    1 | David  |       1 |  1 | English  ||    1 | David  |       1 |  2 | Art      ||    1 | David  |       1 |  3 | Computer ||    1 | David  |       1 |  4 | Alchemy  ||    2 | Lucy   |       3 |  1 | English  ||    2 | Lucy   |       3 |  2 | Art      ||    2 | Lucy   |       3 |  3 | Computer ||    2 | Lucy   |       3 |  4 | Alchemy  ||    3 | Lily   |       3 |  1 | English  ||    3 | Lily   |       3 |  2 | Art      ||    3 | Lily   |       3 |  3 | Computer ||    3 | Lily   |       3 |  4 | Alchemy  ||    4 | John   |       4 |  1 | English  ||    4 | John   |       4 |  2 | Art      ||    4 | John   |       4 |  3 | Computer ||    4 | John   |       4 |  4 | Alchemy  ||    5 | Jack   |       2 |  1 | English  ||    5 | Jack   |       2 |  2 | Art      ||    5 | Jack   |       2 |  3 | Computer ||    5 | Jack   |       2 |  4 | Alchemy  ||    6 | Alfred |       3 |  1 | English  ||    6 | Alfred |       3 |  2 | Art      ||    6 | Alfred |       3 |  3 | Computer ||    6 | Alfred |       3 |  4 | Alchemy  |+------+--------+---------+----+----------+

 

有條件內串連：

SELECT * FROM `student` INNER JOIN `department`ON dept_id=id;

+------+--------+---------+----+----------+| s_id | name   | dept_id | id | name     |+------+--------+---------+----+----------+|    1 | David  |       1 |  1 | English  ||    2 | Lucy   |       3 |  3 | Computer ||    3 | Lily   |       3 |  3 | Computer ||    4 | John   |       4 |  4 | Alchemy  ||    5 | Jack   |       2 |  2 | Art      ||    6 | Alfred |       3 |  3 | Computer |+------+--------+---------+----+----------+

　　

查詢出 學生姓名，和對應學院名：

SELECT s.name `student`, d.name `department` FROM `student` sINNER JOIN `department` dON dept_id=id;

+---------+------------+| student | department |+---------+------------+| David   | English    || Lucy    | Computer   || Lily    | Computer   || John    | Alchemy    || Jack    | Art        || Alfred  | Computer   |+---------+------------+

外串連`{ LEFT| RIGHT } [OUTER] JOIN`

左外串連： （以左表為基準）
兩張表做串連的時候，在串連條件不匹配的時候
留下左表中的資料，而右表中的資料以NULL填充

右外串連： （以右表為基準）
對兩張表做串連的時候，在串連條件不匹配的時候
留下右表中的資料，而左表中的資料以NULL填充

例：
先往學生表中添加資料，只添加名字，造成學生表中有dept_id為NULL的資料

INSERT INTO student(name)VALUES(‘xixi‘);

往學院表中添加資料，造成一個沒有學生的學院

INSERT INTO department(name) VALUES(‘Sport‘);

查看所有學生表資料

SELECT * FROM student;

+------+--------+---------+| s_id | name   | dept_id |+------+--------+---------+|    1 | David  |       1 ||    2 | Lucy   |       3 ||    3 | Lily   |       3 ||    4 | John   |       4 ||    5 | Jack   |       2 ||    6 | Alfred |       3 ||    7 | xixi   |    NULL |+------+--------+---------+

使用內串連加條件只能看到有分配好學院的學生的資訊，沒有新添加的xixi和Sport；

SELECT * FROM student INNER JOIN departmentON dept_id=id;

+------+--------+---------+----+----------+| s_id | name   | dept_id | id | name     |+------+--------+---------+----+----------+|    1 | David  |       1 |  1 | English  ||    2 | Lucy   |       3 |  3 | Computer ||    3 | Lily   |       3 |  3 | Computer ||    4 | John   |       4 |  4 | Alchemy  ||    5 | Jack   |       2 |  2 | Art      ||    6 | Alfred |       3 |  3 | Computer |+------+--------+---------+----+----------+

使用左串連把學生的資料全取出來，該學生沒有學院資訊的用NULL填充，沒有Sport學院

SELECT * FROM student LEFT JOIN departmentON dept_id=id;

+------+--------+---------+------+----------+| s_id | name   | dept_id | id   | name     |+------+--------+---------+------+----------+|    1 | David  |       1 |    1 | English  ||    5 | Jack   |       2 |    2 | Art      ||    2 | Lucy   |       3 |    3 | Computer ||    3 | Lily   |       3 |    3 | Computer ||    6 | Alfred |       3 |    3 | Computer ||    4 | John   |       4 |    4 | Alchemy  ||    7 | xixi   |    NULL | NULL | NULL     |+------+--------+---------+------+----------+

使用右外串連把目前還沒有學生的學院的資料也顯示出來，有Sport學院，沒有學生xixi

SELECT * FROM student RIGHT JOIN departmentON dept_id=id;

+------+--------+---------+----+----------+| s_id | name   | dept_id | id | name     |+------+--------+---------+----+----------+|    1 | David  |       1 |  1 | English  ||    2 | Lucy   |       3 |  3 | Computer ||    3 | Lily   |       3 |  3 | Computer ||    4 | John   |       4 |  4 | Alchemy  ||    5 | Jack   |       2 |  2 | Art      ||    6 | Alfred |       3 |  3 | Computer || NULL | NULL   |    NULL |  5 | Sport    |+------+--------+---------+----+----------+

作為班主任，我想看到，學生的 ( 姓名，選的課程名，所屬學院 )

SELECT s.name `student`, c.name `course`, d.name `deparment` FROM `student` s LEFT JOIN `select` se ON se.s_id = s.s_idLEFT JOIN course c ON se.c_id = c.idLEFT JOIN department d ON s.dept_id = d.id;

+---------+--------+-----------+| student | course | deparment |+---------+--------+-----------+| Lucy    | Math   | Computer  || John    | Math   | Alchemy   || David   | Python | English   || John    | Python | Alchemy   || David   | Music  | English   || Lucy    | Music  | Computer  || John    | Music  | Alchemy   || Lily    | NULL   | Computer  || Jack    | NULL   | Art       || Alfred  | NULL   | Computer  || xixi    | NULL   | NULL      |+---------+--------+-----------+

將課程按照學生和學院分組，顯示每個學生的選課情況，進行結果最佳化

SELECT s.name student,GROUP_CONCAT(c.name SEPARATOR ‘,‘) course,d.name deparment FROM `student` s LEFT JOIN `select` se ON s.s_id = se.s_idLEFT JOIN `course` c ON se.c_id = c.idLEFT JOIN `department` d ON s.dept_id = d.idGROUP BY s.name,d.name ;

+---------+-------------------+-----------+| student | course            | deparment |+---------+-------------------+-----------+| Alfred  | NULL              | Computer  || David   | Python,Music      | English   || Jack    | NULL              | Art       || John    | Math,Music,Python | Alchemy   || Lily    | NULL              | Computer  || Lucy    | Music,Math        | Computer  || xixi    | NULL              | NULL      |+---------+-------------------+-----------+

作為宿管， 看到學生的 ( 姓名， 年齡，性別，所屬學院)

SELECT s.name, stu.age,stu.sex,d.name deparment FROM student sLEFT JOIN `stu_details` stu ON s.s_id = stu.s_idLEFT JOIN `department` d ON s.dept_id = d.id;

+--------+------+------+-----------+| name   | age  | sex  | deparment |+--------+------+------+-----------+| David  |   18 | M    | English   || Lucy   |   19 | F    | Computer  || Lily   |   16 | F    | Computer  || John   |   20 | M    | Alchemy   || Jack   | NULL | NULL | Art       || Alfred | NULL | NULL | Computer  || xixi   | NULL | NULL | NULL      |+--------+------+------+-----------+

事務

事務： 是資料庫運行中的一個邏輯工作單位。
#原子性
事務必須是原子工作單元；對於其資料修改，要麼全都執行，要麼全都不執行。

#一致性
事務在完成時，必須使所有的資料都保持一致狀態。

#隔離性
由並發事務所作的修改必須與任何其它並發事務所作的修改隔離。
```

為了保證資料庫記錄的更新從一個一致性狀態變更為另一個一致性狀態
使用事務來處理是非常必要。

例：
建立一張銀行賬戶的表

CREATE TABLE `account`(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) NOT NULL,`balance` INT);

添加兩個使用者及使用者的存款的資訊

INSERT INTO `account`(`name`,`balance`)VALUES(‘Max‘,10000),(‘Van‘,2000);

查看狀態：

SELECT * FROM account;

+----+------+---------+| id | name | balance |+----+------+---------+|  1 | Max  |   10000 ||  2 | Van  |    2000 |+----+------+---------+

假設現在使用者Van在商店買了500元東西，現在要轉賬給商店，那麼就需要從小明的賬戶上減去500，然後在商店的使用者Max上加上500，但是如果在減500的過程中出現了系統故障，再重新啟動後發現Van的錢扣了，但商店Max卻沒有收到，這時候就會出現資料變動不一致。對於這種資料的修改我們需要的就是要麼同時修改成功，要麼同時修改失敗，所以這就需要用事務來進行出來。

START TRANSACTION：開始一個新的事務
COMMIT：提交當前事務，做出永久改變
ROLLBACK：復原當前事務，放棄修改

開始事務

START TRANSACTION;

Van扣錢

UPDATE `account` SET `balance`= `balance`-500 WHERE `name` =‘Van‘;

查看狀態，錢已經扣掉：

SELECT * FROM account;

+----+------+---------+| id | name | balance |+----+------+---------+|  1 | Max  |   10000 ||  2 | Van  |    1500 |+----+------+---------+

使用ROLLBACK;使資料的修改不生效，回到事務前的狀態：

ROLLBACK;

查看狀態，錢已經恢複：

SELECT * FROM account;

+----+------+---------+| id | name | balance |+----+------+---------+|  1 | Max  |   10000 ||  2 | Van  |    2000 |+----+------+---------+

做一次正確的操作：

START TRANSACTION;UPDATE `account` SET `balance`= `balance`-500 WHERE `name` =‘Van‘;UPDATE `account` SET `balance`= `balance`+500 WHERE `name` =‘Max‘;COMMIT;

查看狀態，交易完成：

SELECT * FROM account;

+----+------+---------+| id | name | balance |+----+------+---------+|  1 | Max  |   10500 ||  2 | Van  |    1500 |+----+------+---------+

當COMMIT後，資料修改成功，ROLLBACK也沒法回到之前了。

ROLLBACK;SELECT * FROM account;

+----+------+---------+| id | name | balance |+----+------+---------+|  1 | Max  |   10500 ||  2 | Van  |    1500 |+----+------+---------+

　　

MySQL資料庫操作（7）聯表查詢、事務