今天我们来聊聊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原理请点击 ->