嵌套子查询

子查询中还有子查询（多重）

一般超过三层考虑效率问题

select 要查询的列 from 主查询的表 where 条件=（

​ select 要查询的子查询1的列 from 子查询1的表 where 条件=（

​ select 要查询的子查询2的列 from 子查询2的表 where 条件

​ )

);

例：

mysql> -- 查询在Asia地区工作的员工信息
mysql>
mysql> SELECT id , first_name , title , salary
    -> FROM s_emp e
    -> WHERE EXISTS (
    ->                SELECT 'x' FROM s_dept d
    ->                WHERE d.id = e.dept_id AND d.region_id = ( SELECT id FROM s_region WHERE name = 'Asia')
    ->              );
+----+------------+----------------------+---------+
| id | first_name | title                | salary  |
+----+------------+----------------------+---------+
| 14 | Mai        | Sales Representative | 1525.00 |
| 23 | Radha      | Stock Clerk          |  795.00 |
|  9 | Antoinette | Warehouse Manager    | 1300.00 |
| 22 | Eddie      | Stock Clerk          |  800.00 |
+----+------------+----------------------+---------+
4 rows in set (0.01 sec)

事务

MySQL查询事务提交方式

select @@autocommit;

例：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

查询结果为1为自动提交为0为非自动提交

设置关闭自动提交

set autocommit=0;（临时关闭，下次连接会自动打开）

例：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

回滚事务

用于撤销删除的数据

rollback;

例：

mysql> delete from dept where deptno IN (50,60,70) ;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOCATION |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOCATION |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 开发部     | 未央宫   |
|     60 | 测试部     | 长乐宫   |
|     70 | 市场部     | 阿房宫   |
+--------+------------+----------+
7 rows in set (0.00 sec)

CASE语句

case when 条件 then 代替条件的结果 else 不满足条件（默认） end

例：

mysql> SELECT s.name 姓名,c.name 课程名,sc.score 成绩,sc.exam_time 考试时间,CASE
    -> WHEN sc.score BETWEEN 90 AND 100 THEN 'A'
    -> WHEN sc.score BETWEEN 80 AND 89 THEN 'B'
    -> WHEN sc.score BETWEEN 70 AND 79 THEN 'C'
    -> WHEN sc.score BETWEEN 60 AND 69 THEN 'D'
    -> ELSE 'E'
    -> END 等级
    -> FROM t_students s JOIN t_scores sc ON s.id=sc.sid JOIN t_courses c ON sc.cid=c.id;
+--------+----------+------+------------+------+
| 姓名   | 课程名   | 成绩 | 考试时间   | 等级 |
+--------+----------+------+------------+------+
| 张无忌 | C++      |   99 | 2021-07-11 | A    |
| 张无忌 | C++      |   92 | 2021-07-15 | A    |
| 周芷若 | C++      |   97 | 2021-07-11 | A    |
| 张三丰 | C++      |   90 | 2021-08-10 | A    |
| 刘书妹 | C++      |   52 | 2021-07-11 | E    |
| 刘书妹 | C++      |   44 | 2021-08-11 | E    |
| 张无忌 | Java     |   92 | 2021-07-15 | A    |
| 小昭   | Java     |   90 | 2021-07-15 | A    |
| 吴莫愁 | Java     |   62 | 2021-07-25 | D    |
| 赵敏   | Oracle   |   88 | 2021-07-10 | B    |
| 张三丰 | Oracle   |   53 | 2021-07-27 | E    |
| 周芷若 | HTML/CSS |  100 | 2021-07-12 | A    |
| 吴莫愁 | HTML/CSS |   72 | 2021-08-15 | C    |
| 刘书妹 | HTML/CSS |   58 | 2021-08-12 | E    |
+--------+----------+------+------------+------+
14 rows in set (0.00 sec)

round()函数补充

round()函数可取小数点后位数的四舍五入

round(数字,小数点后位数);

例：

mysql> select round(3.1415926,3);
+--------------------+
| round(3.1415926,3) |
+--------------------+
|              3.142 |
+--------------------+
1 row in set (0.00 sec)