MYSQL子查询和多表联合查询

MYSQL子查询和多表连接查询

子查询：sql里面嵌套sql

SELECT id,name FROM student 

WHERE id=(SELECT stu_id FROM score WHERE c_name='计算机' AND grade=70);

IN 后面可以赋多个值，用括号

中文系的学生信息

SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name='中文') 

NOT IN

SELECT * FROM student WHERE id NOT IN (SELECT stu_id FROM score WHERE c_name='中文') 

EXISTS(存在)

EXISTS 如果子查询返回结果为真 则执行前面sql ，如果为假 则前半句sql执行结果为空

SELECT * FROM student WHERE EXISTS (SELECT stu_id FROM score WHERE c_name='中文111') 

ANY子句

ANY后面的子查询有多个值时，只要有一个值满足就可以，

grade < ANY(SELECT grade FROM score WHERE stu_id=901) 子查询取最大值

grade > ANY(SELECT grade FROM score WHERE stu_id=901) 子查询取最小值

grade = ANY(SELECT grade FROM score WHERE stu_id=901) 等号和IN的作用一致

SELECT * FROM score WHERE grade < ANY(SELECT grade FROM score WHERE stu_id=901)

SELECT * FROM score WHERE grade = ANY(SELECT grade FROM score WHERE stu_id=901)

ALL子句

SELECT * FROM score WHERE grade< ALL(SELECT grade FROM score WHERE stu_id=901) 
取最小值比较
SELECT * FROM score WHERE grade > ALL(SELECT grade FROM score WHERE stu_id=902)
取最大值比较

UNION,把两个表的字段合并，去重（重复的部分只显示一个）

SELECT name FROM s1 UNION SELECT name FROM s2

多表连接查询

SELECT student.name,score.grade FROM student,score WHERE student.id = score.stu_id;

多表关联之后，查询的结果是一张虚拟的表，我们可以在这张虚拟表的基础上，在进行筛选（筛选条件之间在连接字段后面接着加就行）
指定字段显示的时候，写成 表名.字段名student.name,score.grade
SELECT student.name AS 姓名,score.grade AS 成绩 FROM student,score WHERE student.id = score.stu_id ;

内连接:

内连接是查询出来两张表中相同的信息

inner join...on
SELECT * FROM a_table a INNER JOIN b_table b ON a.a_id = b.b_id

左连接:

展示出左边表中所有数据，右边的表只显示和左边的表相同的数据，其余空行数据用null代替

left join ...on
SELECT * FROM a_table a LEFT JOIN b_table b ON a.a_id=b.b_id

右连接:

显示出右边表中所有数据，左表只显示和右表相同的部分，左边其余null代替

right join ...on
SELECT * FROM a_table a RIGHT JOIN b_table b ON a.a_id = b.b_id

MySQL事务

事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID。

原子性

事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。

一致性

当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。

以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。

隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。

持久性

事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。

事务的 ACID 原则保证了一个事务或者成功提交，或者失败回滚，二者必居其一。因此，它对事务的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。

开始事务

BEGIN;

提交事务

MySQL 使用下面的语句来提交事务：

COMMIT;

COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。

提交事务，意味着将事务开始以来所执行的所有数据都修改成为数据库的永久部分，因此也标志着一个事务的结束。一旦执行了该命令，将不能回滚事务。只有在所有修改都准备好提交给数据库时，才执行这一操作。

回滚（撤销）事务

MySQL 使用以下语句回滚事务：

ROLLBACK;

ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。这里的操作指对数据库的更新操作。

当事务执行过程中遇到错误时，使用 ROLLBACK 语句使事务回滚到起点或指定的保持点处。同时，系统将清除自事务起点或到某个保存点所做的所有的数据修改，并且释放由事务控制的资源。因此，这条语句也标志着事务的结束。

总结

BEGIN 或 START TRANSACTION 语句后面的 SQL 语句对数据库数据的更新操作都将记录在事务日志中，直至遇到 ROLLBACK 语句或 COMMIT 语句。如果事务中某一操作失败且执行了 ROLLBACK 语句，那么在开启事务语句之后所有更新的数据都能回滚到事务开始前的状态。如果事务中的所有操作都全部正确完成，并且使用了 COMMIT 语句向数据库提交更新数据，则此时的数据又处在新的一致状态。

**eg:**将张三的账户余额减少 1000 元，并让事务回滚，SQL 语句和运行结果如下所示：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
 
mysql> UPDATE bank SET currentMoney = currentMoney-1000 WHERE customerName='张三';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ROLLBACK;
Query OK, 0 rows affected (0.07 sec)
 
mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三         |       500.00 |
| 李四         |       501.00 |
+--------------+--------------+
2 rows in set (0.00 sec)

从结果可以看出，执行事务回滚后，账户数据恢复到初始状态，即该事务执行之前的状态。