MySQL数据库操作（7）联表查询、事务

联表查询

内连接`[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` s
INNER JOIN `department` d
ON 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 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 |
+------+--------+---------+----+----------+

使用左连接把学生的数据全取出来，该学生没有学院信息的用NULL填充，没有Sport学院

SELECT * FROM student LEFT JOIN department
ON 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 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 |
| 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_id
LEFT JOIN course c ON se.c_id = c.id
LEFT 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_id
LEFT JOIN `course` c ON se.c_id = c.id
LEFT JOIN `department` d ON s.dept_id = d.id
GROUP 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 s
LEFT JOIN `stu_details` stu ON s.s_id = stu.s_id
LEFT 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 |
+----+------+---------+