什么是分组函数
分组函数运算每一组记录,每一组返回一个结果
AVG ([DISTINCT|ALL]n) 求平均数; AVG(comm) : comm中的null值个数不算在内，值是0时算）
SUM ([DISTINCT|ALL]n) 求和
COUNT ({ * |[DISTINCT|ALL]expr}) 计数 count(*) : 空值的个数也算； count(comm ):不会计算空值的个数
MAX ([DISTINCT|ALL]expr) 求最大值
MIN ([DISTINCT|ALL]expr) 最小值
#SELECT AVG(sal), MAX(sal),
MIN(sal), SUM(sal)
FROM emp
WHERE job LIKE 'SALES%';
#MIN 和 MAX函数适用于任何数据类型
SELECT MIN(时间), MAX(时间)
FROM emp
#使用 COUNT 函数
COUNT(*) 返回检索行的数目， 不论其是否包含 NULL值
SELECT COUNT(*)
FROM emp
WHERE deptno = 30；
count(column_name)是对列中不为空的行进行计数
SELECT COUNT(comm)
FROM emp
WHERE deptno = 30；
IFNULL函数强制分组函数包括空值
SELECT AVG(IFNULL(comm,0))
FROM emp；
GROUP BY :所有用来分组的列在SELECT列表中不能使用分组函数
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno (将相同的deptno,整合为一个组；此时 deptno 就不能再用任何分组函数)
注意和 DISTINCT（去重）的区别
使用GROUP BY子句将表分成小组
组函数忽略空值, 可以使用NVL,NVL2,COALESCE 等函数处理空值
在多列上使用 GROUP BY子句
按部门分组统计EMP表中各职位的工资总和
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY deptno,job
不能使用WHERE子句限定组，可使用HAVING子句限定组
HAVING子句
Having子句的作用是对行分组进行过滤
记录被分组
使用组函数
匹配HAVING子句的组被显示
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
#SELECT deptno, max(sal)
FROM emp
GROUP BY deptno
HAVING max(sal)>2900
#SELECT job, SUM(sal) PAYROLL
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY SUM(sal);
小结 语法
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
子查询
找出工资高于Jones的雇员
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
SQL> SELECT ename
FROM emp
WHERE sal >
(SELECT sal
FROM emp
WHERE empno=7566
1、子查询在主查询前执行一次
2、主查询使用子查询的结果
使用子查询的规则
1、子查询要用括号括起来
2、将子查询放在比较运算符的右边
3、子查询中不要加ORDER BY子句
4、对单行子查询使用单行运算符
5、对多行子查询使用多行运算符
子查询的种类
1、单行子查询
2、多行子查询
3、多列子查询
单行子查询
#返回一行记录
#使用单行记录比较运算符
SELECT ename, job 2 FROM emp
WHERE job =
(SELECT job FROM emp WHERE empno = 7369)
AND sal >
(SELECT sal
FROM emp
WHERE empno = 7876);
在子查询中使用分组函数
SELECT ename, job, sal
FROM emp
WHERE sal =
(SELECT MIN(sal)
FROM emp);
在子查询中使用HAVING子句
1、先执行子查询
2、ELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal)
FROM emp
WHERE deptno = 20);
多行子查询
1、返回多行
2、使用多行比较运算
#select ename from emp where deptno in
(select deptno from emp where ename = 'SMITH' or ename = 'MILLER');
#SELECT empno, ename, job ,sal
FROM emp
WHERE sal > ALL
(SELECT avg(sal)
FROM emp
GROUP BY deptno)
#SELECT empno, ename, job
FROM emp
WHERE sal > ANY
(SELECT sal
FROM emp
WHERE job = 'CLERK')
AND job <> 'CLERK';
使用标准SQL嵌套子查询和连接查询两种方法查询选修课程名称为'gs’的学生学号和姓名
#1st
SELECT stuid, stuname
FROM students
WHERE stuid IN
(SELECT stuid FROM grade
WHERE couid=(SELECT couid FROM course WHERE couname='gs' )
)
#2nd
SELECT s.stuid,s.stuname
FROM students s,course c,grade g
WHERE s.stuid=g.stuid AND c.couid=g.couid AND c.couname='gs'
#3rd
SELECT s.stuid,stuname FROM students s, (SELECT stuid, c.couid FROM course c , grade g WHERE c.couid=g.couid AND c.couname='gs' ) b WHERE s.stuid=b.stuid
#2 使用标准SQL嵌套语句查询选修课程编号为‘2’的学生姓名和所属班级
#SELECT stuname, class FROM students s WHERE s.stuid=(SELECT stuid FROM grade WHERE couid=2)
SELECT stuname,class FROM students WHERE stuid IN (SELECT stuid FROM grade WHERE couid = 2);
#3.使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属班级
SELECT stuname , class FROM students WHERE stuid IN (SELECT stuid FROM grade GROUP BY stuid HAVING COUNT(*) = (SELECT COUNT(*) FROM course));
#4.查询选修了课程的学生人数
SELECT COUNT(DISTINCT stuid) FROM grade;
#SELECT COUNT(*) FROM (SELECT DISTINCT stuid FROM grade) AS a;
#5.查询选修课程超过3门的学员学号和所属班级
#1st
SELECT stuid , class FROM students WHERE stuid IN (SELECT stuid FROM grade GROUP BY stuid HAVING COUNT(*) > 3);
#2nd
SELECT s.stuid,s.class FROM students s,
(SELECT stuid, COUNT(couid) FROM grade GROUP BY stuid HAVING COUNT(couid)>3 ) b
WHERE s.stuid=b.stuid
#查询在部门销售部工作的员工姓名、工资、职位
SELECT ename,sal,job
FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES')
#使用子查询，找出哪个部门下没有员工
SELECT dname
FROM dept
GROUP BY deptno
HAVING deptno NOT IN(SELECT deptno FROM emp)
#使用子查询，找出那些工资低于平均工资的员工姓名、工资、职位、部门号
SELECT ename,job,sal,deptno
FROM emp
WHERE sal<(SELECT AVG(sal)FROM emp)
#使用子查询，找出那些工资低于其中任意一个部门的平均工资的员工
SELECT ename
FROM emp
WHERE sal < ANY(SELECT AVG(sal) FROM emp GROUP BY deptno)
#查询每个部门入职最晚的员工的部门编号、员工姓名及其工资的信息
SELECT deptno,ename,sal
FROM emp
GROUP BY deptno
HAVING MAX(hiredate)
#查询每个部门的部门号、部门名称、部门人数(没有员工的部门人数显示为0)
SELECT d.dname,d.deptno,COUNT(e.deptno)
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY e.deptno
#查询高于自己部门平均工资的员工名字,部门号,工资,平均工资
SELECT emp.ename,emp.deptno,emp.sal, AVG(emp.sal)
FROM emp,
(SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ) b
WHERE emp.deptno=b.deptno AND sal>b.avgsal
SELECT emp.ename,emp.deptno,emp.sal, AVG(emp.sal)
FROM emp,(SELECT deptno,AVG(sal) asd FROM emp GROUP BY deptno) b
WHERE emp.deptno=b.deptno AND emp.sal>b.asd
#查询工资排名前三的员工信息
SELECT * FROM emp
ORDER BY sal DESC
LIMIT 3
