04数据库的高级查询

高级查询

数据统计分析

聚合函数
统计员工平均月收入？

SELECT AVG(sal+IFNULL(comm,0)) AS avg FROM t_emp;

SELECT SUM(sal) FROM t_emp WHERE deptno IN(10,20);

SELECT MAX(comm) FROM t_emp;

SELECT SUM(sal),MAX(sal+IFNULL(comm,0)) FROM t_emp
WHERE deptno IN(10,20);

SELECT MAX(LENGTH(ename)) FROM t_emp;

SELECT min(hiredate) FROM t_emp;

SELECT COUNT(*),COUNT(comm) FROM t_emp;

SELECT count(*) FROM t_emp
WHERE deptno IN(10,20) AND sal>=2000
AND DATEDIFF(NOW(),hiredate)/365 >=15;

下图中第二个命令会出错，因为命令行不知道计算那些AVG(sal)

SELECT count(*) FROM t_emp
WHERE deptno IN(10,20) AND sal>AVG(sal);

SELECT count(*) FROM t_emp
WHERE deptno IN(10,20) AND sal>AVG(sal)
> 1111 - Invalid use of group function
> Time: 0s

因此得使用：分组查询

SELECT deptno,round(AVG(sal)) FROM t_emp GROUP BY deptno;

SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp 
GROUP BY deptno,job
ORDER BY deptno;

with rollup汇总

SELECT deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal)
FROM t_emp GROUP BY deptno WITH ROLLUP

SELECT deptno,COUNT(*),GROUP_CONCAT(ename)
FROM t_emp
WHERE sal>=2000
GROUP BY deptno;

分组查询遇到的困难。
WHERE字句优先级优于GROUP BY执行，如果WHERE语句中有聚合函数则错误，因为不知道聚合范围
此时要引入HAVING字句…

SELECT 
FROM t_emp
WHERE AVG(sal)>=2000
GROUP BY deptno;

SELECT 
FROM t_emp
WHERE AVG(sal)>=2000
GROUP BY deptno
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM t_emp
  WHERE AVG(sal)>=2000
  GROUP BY deptno' at line 2
> Time: 0s

使用having语句

SELECT deptno
FROM t_emp
GROUP BY deptno HAVING AVG(sal)>=2000;

SELECT deptno
FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2

典型错误语句

SELECT deptno
FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=AVG(sal)

表的内外连接

表的内连接，要有ON…（连接条件否则会出现笛卡尔积14*4 = 56个结果）

#查询每名员工的部门信息
SELECT e.empno,e.ename,d.dname
FROM t_emp e join t_dept d ON e.deptno=d.deptno;

#表连接分为内连接和外连接
#衍生语法
SELECT e.empno,e.ename,d.dname
FROM t_emp e join t_dept d WHERE e.deptno=d.deptno;

SELECT e.empno,e.ename,d.dname
FROM t_emp e,t_dept d where e.deptno=d.deptno;

#查询每个员工的工号，姓名，部门名称，底薪，职位，工资等级
SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal

#查询SCOTT相同部门员工都有谁
SELECT deptno
FROM t_emp
WHERE ename="SCOTT"

SELECT ename 
FROM t_emp 
WHERE deptno = (SELECT deptno FROM t_emp WHERE ename="SCOTT")

#子查询非常慢
SELECT ename 
FROM t_emp 
WHERE deptno = (SELECT deptno FROM t_emp WHERE ename="SCOTT")
AND ename != "SCOTT"

#子查询非常慢
SELECT ename 
FROM t_emp 
WHERE deptno = (SELECT deptno FROM t_emp WHERE ename="SCOTT")
AND ename != "SCOTT"

表的外连接

左连接

SELECT e.empno,e.ename,d.dname
FROM t_dept d
RIGHT JOIN t_emp e ON e.deptno = d.deptno

SELECT e.empno,e.ename,d.dname
FROM t_dept d
RIGHT JOIN t_emp e ON e.deptno = d.deptno

查询每个部门的名称和人数

#外接练习1
#查询每个部门的名称和人数
SELECT d.dname, count(*)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno

SELECT d.dname, count(d.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno

通过union合并，这条命令没有统计到 陈浩

#查询每个部门的名称和人数，如果没有部门的员工，部门名称用Null替代
#通过union合并之后
(SELECT d.dname, count(d.deptno)
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno) UNION
(SELECT d.dname, count(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno)

需要把陈浩统计上去

#这样就把陈浩（无部门）统计上了
(SELECT d.dname, count(*)
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno) UNION
(SELECT d.dname, count(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno = e.deptno
GROUP BY d.deptno )

查询每个员工的编号，姓名，部门，月薪，工资等级，工龄，上司编号，上司姓名，上司部门

SELECT e.empno,e.ename,d.dname,e.sal + IFNULL(e.comm, 0),
			 s.grade,FLOOR(DATEDIFF(NOW(),e.hiredate) / 365),
			 t.empno AS mgrno, t.ename AS mname,t.dname AS mdname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(SELECT e1.empno,e1.ename,d1.dname
FROM t_emp e1 JOIN t_dept d1
ON e1.deptno = d1.deptno) t ON e.mgr = t.empno

注意and和where的区别

SELECT
e.empno,e.ename,d.dname
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno = d.deptno
AND e.deptno = 10;

where

SELECT
e.empno,e.ename,d.dname
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno = d.deptno
WHERE e.deptno = 10;

子查询语法规则

where子查询

#查询底薪超过公司平均底薪的员工信息
#where子查询是相关子查询费时间
SELECT empno,ename,sal
FROM t_emp
WHERE sal >=(SELECT AVG(sal) FROM t_emp)

from子查询

#from 子查询只会执行一次，查询效率高
SELECT e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) as avg FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno AND e.sal>=t.avg

#SELECT子查询
SELECT e.empno,e.ename,
(SELECT dname FROM t_dept 	WHERE deptno = e.deptno)
FROM t_emp e

多行子查询，比较时可以用IN
查询和f m在同一个部门的人

#子查询2
#多行子查询
#比较时可以用 in
SELECT ename
FROM t_emp
WHERE 
deptno IN
(SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN"))
AND ename NOT IN("FORD","MARTIN")

查询比f和m底薪高的员工信息

#查询比f and m 底薪高的员工信息
SELECT ename 
FROM t_emp
WHERE sal>=ALL
(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN"))
AND ename NOT IN("FORD","MARTIN")

关键字exist
查询工资等级3和4的员工信息

#关键字exist
SELECT empno,ename,sal,comm
FROM t_emp
WHERE EXISTS(
SELECT * FROM t_salgrade
WHERE sal BETWEEN losal AND hisal
AND grade IN(3,4)
)