MySQL练习（三）---数据库的高级查询

数据库的高级查询

聚合函数

#聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等。
#求公司员工的平均月收入是多少?  月收入=薪资+佣金，如果佣金为NULL则设佣金为0
SELECT AVG(sal+IFNULL(comm,0))FROM t_emp;
#SUM函数用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加。
SELECT SUM(ename)FROM t_emp;
SELECT SUM(sal)FROM t_emp WHERE deptno IN(10,20);
#SUM函数求和会排除NULL值
SELECT SUM(comm) FROM t_emp ;
#MAX函数用于获得非空值的最大值。
SELECT MAX(comm) FROM t_emp;
#查询10和20部门中，月收入最高的员工?
SELECT MAX(sal+IFNULL(comm, 0)) FROM t_emp WHERE deptno IN(10,20);
#查询员工名字最长的是几个字符?
SELECT MAX(LENGTH(ename)) FROM t_emp;
#MIN函数用于获得非空值的最小值。
SELECT MIN(empno)FROM t_emp;
SELECT MIN(hiredate)FROM t_emp;
#AVG函数用于获得非空值的平均值，非数字数据统计结果为0
SELECT AVG (sal+IFNULL(comm,0)) FROM t_emp;
SELECT AVG (ename)FROM t_emp;
#COUNT(*)用于获得包含空值的记录数，COUNT(列名)用于获得包含非空值的记录数。
SELECT COUNT(*)FROM t_emp;
SELECT COUNT(comm) FROM t_emp ;
#查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数?
SELECT COUNT(*) FROM t_emp WHERE deptno IN(10,20) AND sal>=2000 AND DATEDIFF(NOW(),hiredate)/365>=15;
#注意：聚合函数是不能出现在WHERE子句里面。所以SELECT COUNT(*)FROM t_emp WHERE hiredate>="1985-01-01" AND sal>AVG(sal);这句是执行不了的

分组查询

## 分组查询
#GROUP BY子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理
SELECT deptno,AVG(sal) FROM t_emp GROUP BY deptno;
#数据库支持多列分组条件，执行的时候逐级分组。
#查询每个部门里，每种职位的人员数量和平均底薪
SELECT deptno,job,COUNT(*),AVG(sal)FROM t_emp GROUP BY deptno,job ORDER BY deptno;
#查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定:
#SELECT子句中可以包括聚合函数，或者GROUpBY子句的分组列，其余内容均不可以出现在SELECT子句中
SELECT deptno,COUNT(*),AVG(sal) FROM t_emp GROUP BY deptno;
#这句将执行出错，因不符合规定 SELECT deptno,COUNT(*),AVG(sal),sal FROM t_emp GROUP BY deptno;
#对分组结果集再次做汇总计算   #WITH ROLLUP：在group分组字段的基础上再进行统计数据。
SELECT deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal)FROM t_emp GROUP BY deptno WITH ROLLUP;
#GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串
#查询每个部门内底薪超过2000元的人数和员工姓名
SELECT deptno,GROUP_CONCAT(ename),COUNT(*)FROM t_emp WHERE sal>=2000 GROUP BY deptno;
#各种子句的执行顺序 FROM -> WHERE->GROUP BY -> SELECT ->ORDER BY->LIMIT

HAVING子句

## HAVING子句
#查询部门平均底薪超过2000元的部门编号 SELECT deptno FROM t_emp WHERE AVG(sal) >=2000 GROUP BY deptno;
#因为WHERE子句先于GROUP BY执行，一旦WHERE子句中出现了汇总函数，数据库根本不知道按照什么范围计算汇总值;所以上面这条语句是错的
SELECT deptno FROM t_emp GROUP BY deptno HAVING AVG(sal) >=2000 ;#查询部门平均底薪超过2000元的部门编号
#查询每个部门中，1982年以后入职的员工超过2个人的部门编号
SELECT deptno 
FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno 
HAVING COUNT(*)>=2;ORDERBY deptno ASC;
#HAVING子句不能独立存在，必须紧跟GROUP BY子句
#HAVING子句的特殊用法
#按照数字1分组，MySQL会依据SELECT子句中的列进行分组,HAVING子句也可以正常使用
SELECT deptno,COUNT(*)FROM t_emp GROUP BY 1;
SELECT deptno,COUNT(*)FROM t_emp GROUP BY 1 HAVING deptno IN(10,20);

表连接查询

## 表连接查询
#从多张表中提取数据，必须指定关联的条件。如果不定义关联条件就会出现无条件连接，两张表的数据会交叉连接，产生笛卡尔积。
#    SELECT empno, ename , dname FROM t_emp JOIN t_dept;
#规定了连接条件的表连接语句，就不会出现笛卡尔积
SELECT e.empno,e.ename,d.dname FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;
#表连接分为两种:内连接和外连接
#内连接是结果集中只保留符合连接条件的记录
#外连接是不管符不符合连接条件，记录都要保留在结果集中
#内连接的多种语法形式
#  SELECT...... FROM 表1 JOIN 表2 ON 连接条件;
#  SELECT ......FROM 表1 JOIN 表2 WHERE 连接条件;
#  SELECT ......FROM 表1 ，表2 WHERE 连接条件;
#查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级?
SELECT e.empno,e.ename,d.deptno,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 e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno 
WHERE e1.ename="SCOTT" AND e2.ename !="SCOTT"; #相同的数据表也可以做表连接

#查询月薪超过公司平均月薪的员工信息?
SELECT e1.ename
FROM t_emp e1 
JOIN (SELECT AVG(sal) avg FROM t_emp) e2 ON e1.sal > e2.avg;  #结果集也可以作为一张“表”来跟其他表连接

SELECT e.ename,e.deptno,e.sal
FROM t_emp e
JOIN (SELECT AVG(sal) avg FROM t_emp) t ON e.sal > t.avg;  #结果集也可以作为一张“表”来跟其他表连接

#查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄?
SELECT COUNT(*) AS "RESEARCH部门的人数",MAX(e.sal) AS "最高底薪",MIN(e.sal) AS "最低底薪",AVG(e.sal) AS "平均底薪",
FLOOR(AVG(DATEDIFF(NOW(),e.hiredate)/365)) AS "工龄"
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH";

#FLOOR()向下取整，CEIL()向上取整  例如
SELECT FLOOR(40.256458),CEIL(30.11105);

#查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级?
SELECT 
	e.job,
	MAX(e.sal+IFNULL(e.comm,0)),
	MIN(e.sal+IFNULL(e.comm,0)),
	AVG(e.sal+IFNULL(e.comm,0)),
	MAX(s.grade),
	MIN(s.grade)
FROM t_emp e JOIN t_salgrade s
ON (e.sal+IFNULL(e.comm,0)) BETWEEN s.losal AND s.hisal 
GROUP BY e.job;

#查询每个底薪超过部门平均底薪的员工信息
SELECT e.empno,e.ename,e.sal,e.deptno,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 e.empno,e.ename,d.dname
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno=d.deptno;
#左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。


SELECT d.dname,COUNT(e.deptno) FROM t_dept d LEFT JOIN t_emp e ON d.deptno=e.deptno
GROUP BY d.deptno;
#查询每个部门的名称和部门的人数?如果没有部门的员工，部门名称用NULL代替。
#UNION关键字可以将多个查询语句的结果集进行合并:  (查询语句）UNION（查询语句）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) 
UNION
(SELECT d.dname,COUNT(*)
FROM t_dept d RIGHT JOIN t_emp e ON d.deptno=e.deptno
GROUP BY d.deptno);

#查询每名员工的编号、姓名、部门、月收入、工资等级、工龄、上司编号、上司姓名、上司部门?
SELECT 
	e1.empno AS "员工编号",e1.ename AS "姓名",d1.dname AS "部门",
	e1.sal+IFNULL(e1.comm,0) AS "月收入",
	t.grade AS "工资等级",
	FLOOR(DATEDIFF(NOW(),e1.hiredate)/365) AS "工龄",
	e1.mgr AS "上司编号",e2.ename AS "上司姓名",d2.dname AS "上司部门"
FROM t_emp e1 
LEFT JOIN t_dept d1 ON e1.deptno=d1.deptno
LEFT JOIN t_emp e2 ON e1.mgr=e2.empno 
LEFT JOIN t_salgrade t ON e1.sal BETWEEN t.losal AND t.hisal
LEFT JOIN t_dept d2 ON e2.deptno=d2.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;

#外连接的注意事项
#内连接只保留符合条件的记录，所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE子句里，不符条件的记录是会被过滤掉的，而不是保留下来。
SELECT e.ename,d.dname,d.deptno 
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno AND e.deptno = 10;

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

子查询

## 子查询
#子查询是一种查询中嵌套查询的语句
#查询底薪超过公司平均底薪的员工的信息
SELECT empno, ename, sal FROM t_emp WHERE sal>=(SELECT AVG(sal) FROM t_emp);#比较每条记录都要重新执行子查询
# WHERE子句的这种子查询最简单，最容易理解，但是却是效率很低的子查询
#子查询可以写在三个地方:WHERE子句、FROM子句、SELECT子句，但是只有FROM子句子查询是最可取的

#用表连接替代WHERE子查询
#表连接的优点是子查询只执行一次，查询效率特别高
SELECT e2.empno,e2.ename FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno AND e2.ename!="FORD" AND e1.ename="FORD";

#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;

#查询语句执行的时候要多次的依赖于子查询的结果，这类子查询被称作相关子查询
#WHERE子查询和SELECT子查询都属于相关子查询
#因为相关子查询要反复多次执行，所以应该避免使用

#单行子查询和多行子查询
#单行子查询的结果集只有一条记录，多行子查询结果集有多行记录
#多行子查询只能出现在WHERE子句和FROM子句中
#如何用子查询查找FORD和MARTIN两个人的同事?
SELECT ename FROM t_emp 
WHERE deptno IN (SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN")) AND ename NOT IN ("FORD","MARTIN");

#WHERE子句中的多行子查询
#WHERE子句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断
#查询比FORD和MARTIN底薪都高的员工信息?
SELECT ename FROM t_emp WHERE sal >ALL(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN"));

#EXISTS关键字是把原来在子查询之外的条件判断，写到了子查询的里面。
# SELECT..... FROM 表名 WHERE [NOT] EXISTS(子查询);
#查询工资等级3和4级的员工信息?
SELECT empno,ename,sal,comm FROM t_emp
WHERE EXISTS(SELECT * FROM t_salgrade WHERE grade IN (3,4) AND sal BETWEEN losal AND hisal);