粤嵌打卡第30天 （Mysql数据库总结------子查询、视图、事务控）

今天我们来聊聊Mysql数据库的一些基础知识点吧！！！

1、子查询

子查询：嵌套查询

子查询的意义：将多条SQL合并为一条sql执行，降低数据库的连接次数

子查询可以使用为位置：

• 1- 可以在 where 子句后，充当条件表达式构成

• 2- 可以出现在 select 子句后， 子查询结果充当列

• 3- 可以在From子句后，充当数据源

子查询的分类：

1-单行单列子查询（标量子查询）

• 查询和Fred同一个领导的领导信息

SELECT * FROM employees 
WHERE 
  manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Fred') 
  AND first_name != 'Fred';

2- 单行多列子查询

• 和SCOTT同一个部门，并且同样工作的人员信息

SELECT * FROM emp 
WHERE (deptno,job) = (SELECT deptno,job FROM emp WHERE ename = 'SCOTT');

3- 多行单列子查询

• 输入和20部门同样工作的其他部门人员信息

SELECT * FROM emp 
WHERE deptno != 20
 AND job IN (SELECT DISTINCT job FROM emp WHERE deptno=20);

3- 可以在From子句后，充当数据源

• 输出每个部门工资最高的人员信息

SELECT * 
FROM emp e, (SELECT MAX(sal) max_sal,deptno FROM emp GROUP BY deptno) t
WHERE e.sal = t.max_sal AND e.deptno = t.deptno

SELECT * 
FROM emp e
WHERE (e.sal,e.deptno) 
	IN 
	(SELECT MAX(sal) max_sal,deptno FROM emp GROUP BY deptno);

匹配多行结果的关键字：

 IN ：匹配多行结果的某一个即可。 （等值判断）
 ANY：符合多行结果的某一个即可。 （不等值判断）
 ALL：符合多行结果的任意一个。   （不等值判断）

• 工资高于10部门某个人的其他部门人员信息（工资高于10部门最低的人员工资即可）

SELECT * FROM emp
WHERE deptno != 10
	AND sal > ANY (SELECT sal FROM emp WHERE deptno = 10);

• 工资高于30部门任意一个的其他部门人员信息（工资高于30部门最高的人员工资）

SELECT * FROM emp
WHERE deptno != 30
	AND sal > ALL (SELECT sal FROM emp WHERE deptno = 30);

4- 多行多列子查询

• 和20部门同样工作，且上司一致的其他部门人员信息

SELECT *
FROM emp
WHERE (job,mgr) IN (SELECT DISTINCT job,mgr FROM emp WHERE deptno = 20)
 AND deptno != 20;

示例题目：

• 输出每个员工，工资占所有员工总工资的百分比

SELECT e.*,
	CONCAT(ROUND(sal / (SELECT SUM(sal) FROM emp) * 100, 2),'%') percent_sal
FROM emp e

• 输出每个员工，工资占本部门员工总工资的百分比

SELECT e.*,
	CONCAT(ROUND(sal / (SELECT SUM(sal) FROM emp WHERE deptno=e.DEPTNO) * 100, 2),'%') percent_sal
FROM emp e

敲重点：

---

查询原理3：

表的别名有效范围：本条SQL，且一旦定义了表的别名，就不能用表原名。

列的别名有效范围：本条SQL的结果集生成之后，在结果集生成之前不能用列别名

注意：结果集合生成的时机： 在order by 子句执行之前

---

示例代码：

• 查询在10部门员工编号在10-20的员工信息

SELECT e.*
FROM (SELECT * FROM emp t WHERE t.deptno IN (10,20)) e
WHERE e.deptno = 10;

• 查询员工编号为10的员工信息

SELECT * FROM(
SELECT empno e_empno,ename e_name,deptno d_no
FROM emp e) e
WHERE e.d_no = 10;

• 按照员工编号降序排列输出所有员工信息

SELECT empno e_empno,ename e_name,deptno d_no
FROM emp 
ORDER BY d_no DESC

连接查询的性能高于多行匹配查询（IN ANY ALL）

• 子查询可以放在SQL任意位置，只要不违反不同子句的要求

SELECT SUM(sal) FROM emp e
GROUP BY (SELECT MOD(sal,1000) FROM emp WHERE e.empno = empno)

子查询的另一种分类：

• 1- 关联子查询： 关心子查询具体内容(IN、 ANY、ALL)

• 2- 相关子查询： 不关心子查询具体内容，只关心有没有结果(EXISTS、 NOT EXISTS)

• 输出所有领导

SELECT * FROM emp e
WHERE EXISTS (SELECT 0 FROM emp e2 WHERE e.`EMPNO` = e2.`MGR`);

• 输出所有基层员工

SELECT * FROM emp e
WHERE NOT EXISTS (SELECT 0 FROM emp e2 WHERE e.`EMPNO` = e2.`MGR`);

2、视图

1、视图： 没有自己的数据，只有预定义的查询SQL

• 定义视图

CREATE OR REPLACE VIEW 视图名字 AS 子查询

• 定义视图名称的规范： V_视图名称

创建并查看视图：

CREATE OR REPLACE VIEW v_emp AS SELECT * FROM emp WHERE deptno IN (10,30);

SELECT * FROM v_emp;

2、视图的使用意义：

• 1、开发过程中隐藏某些行、列信息

• 2、将一个经常使用的复杂查询简单化

视图本身就在于查询，不在于修改。

CREATE OR REPLACE VIEW v_emp 
	AS 
	SELECT empno,ename,job,mgr,hiredate,deptno
	 FROM emp WHERE deptno !=20;

CREATE OR REPLACE VIEW v_emp_info
	AS 
	 SELECT e.*,d.dname,d.loc,g.grade
	 FROM emp e,dept d,salgrade g
	 WHERE e.deptno = d.deptno AND e.sal BETWEEN g.losal AND g.hisal;

SELECT * FROM v_emp_info WHERE empno = 7788;

3、视图的分类：

• 1- 简单视图：数据源来自一张表，并且没有数据变形（能对应到物理数据行）

SELECT * FROM v_emp;

• 2- 复杂视图：数据源来自多张表的连接查询，或者有数据变形。

CREATE OR REPLACE VIEW v_emp_dept_count
	AS 
SELECT COUNT(1) emp_count,deptno FROM emp GROUP BY deptno;

SELECT * FROM v_emp_dept_count;

• 可以通过修改简单视图，去修改表的物理数据，但是不能通过复杂视图修改物理数据。

UPDATE v_emp SET ename=LOWER(ename) WHERE empno=7499;
SELECT * FROM emp;

错误案例

UPDATE v_emp_dept_count SET emp_count = 6 WHERE deptno = 10;

3、事务

数据库的事务 ：

一次DML操作（一个或者多个DML操作，最终由commit命令存储到库，或者由rollback会滚所有操作，不存储到库，存储到数据库缓存中

• 设置不自动提交事务

set autocommit=0;
try{
	#- 扣除转出账户金额
	#- 增加转入账户金额
	COMMIT;
}catch(  e){
	ROLLBACK;
}

• commit命令一次性保存；或者rollback取消上述两个操作。

ACID概述：

• 原子性 （Atomicity）

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。

• 隔离性 (Isolation)

隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持久性 (Durability)

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

• 一致性 (Consistency)

一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。 这个状态是满足预定的约束就叫做合法的状态，再通俗一点，这状态是由你自己来定义的。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！
子查询习题：

1、基于products表，查询其价格是同类产品中价格最高或者是最低的产品的编号、产品名称。

SELECT p.`product_id` AS "MAX_id",p.`name` AS "MAX_name"
FROM products p
WHERE price IN (SELECT MAX(price) FROM products GROUP BY product_type_id);

2、基于employees表，查询手下没有下级的员工编号、姓名。

SELECT e1.`employee_id` AS "员工编号",CONCAT(e1.`first_name`,e1.`last_name`) AS "姓名"
FROM employees e1
WHERE NOT EXISTS(SELECT 1 FROM employees e2 WHERE e1.`employee_id` = e2.`manager_id`);

3、基于employees表，查询手下员工超过两名的管理人员的编号和姓名。

SELECT e1.`employee_id`,CONCAT(e1.`first_name`,e1.`last_name`)AS "姓名"
FROM employees e1
WHERE EXISTS (SELECT COUNT(1) FROM employees e2 WHERE e2.`manager_id` = e1.`employee_id` GROUP BY e2.`manager_id` HAVING COUNT(1)>=2);

查询ACID原理请点击 -> https://www.cnblogs.com/CuiHongYu/p/10845354.html