组函数:
- 单行函数:每一行会得到一个结果
- 组函数(多行函数):一组得到一个结果,默认情况下所有的数据是一组
常用组函数(统计):
- SUM():求和
- AVG():求平均
- MAX():求最大
- MIN():求最小
- COUNT():统计个数
eg1:求所有人的工资总和
SELECT SUM(salary)
FROM s_emp;
eg2:求41号部门员工的薪资总和,平均薪资,最高薪资,最低薪资,人数
SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(id)
SELECT SUM(salary)
FROM s_emp
WHERE dept_id=41;
注:
- 组函数统计不包含空值
- SUM(),AVG()仅针对于数值型统计
- MAX(),MIN(),COUNT()任何类型都可以
- COUNT(),统计个数,可以利用他统计行数,要注意,不要针对可能是空的做统计
- COUNT():主键列,或者*(所有列联合不为空就可以)
- 组函数不能直接出现在WHERE条件中(WHERE筛选出结果后才能进行统计)
- SELECT中如果出现组函数,不能再出现普通的列(Oracle中有)
--不能像下面这样 SELECT dept_id,SUM(salary) FROM s_emp;
GROUP BY子句:分组
- 语法:
SELECT FROM WHERE GROUP BY 列1,列2 ORDER BY;
- 注:
- 执行流程
- FROM->WHERE,行的筛选->GROUP BY,把行分组->SELECT,针对组的统计->ORDER BY,排序
- WHERE:分组前的筛选,GROUP BY在WHERE之后
- 组函数:一个组可以得到一个结果
- 查询时,SELECT子句出现组函数或者有GROUP BY子句【“”组“”关系】,查询中不能出现普通的列,但可以出现分组的列
- 可以按照多列分组,多个列值都相等的在一组
- 执行流程
eg1:列出每个部门员工的数量,最低薪水,平均薪水,最高薪水
--dept_id时分组的列,可以列出
SELECT dept_id,COUNT(id),MIN(salary),AVG(salary),MAX(salary)
FROM s_emp
GROUP BY dept_id;
订单表和订单明细表
- 订单表:订单编号,总额,用户信息等
- 订单明细表:订单明细编号,订单编号,商品编号,数量,价格等
eg2:查询每个订单对应的订单明细数量
SELECT ord_id,COUNT(item_id)
FROM s_item
GROUP BY ord_id;
eg3:查询每个销售对应的客户数量,销售编号
SELECT sales_rep_id, COUNT(id)
FROM s_customer
GROUP BY sales_rep_id;
eg4:查询每个销售对应的客户数量,销售编号,销售名字
SELECT c.sales_rep_id, COUNT(c.id),e.first_name
FROM s_customer c, s_emp e
WHERE c.sales_rep_id=e.id
GROUP BY c.sales_rep_id,e.first_name;
--加与不加e.first_name分组结果一样,只是为了显示名字
eg5:查询部门编号大于40的每个部门id,部门人数,部门名
SELECT d.id,d.name,COUNT(e.id)
FROM s_emp e,s_dept d
WHERE e.dept_id=d.id
AND d.id>40
GROUP BY d.id,d.name;
HAVING子句:
- WHERE子句:行的筛选
- HAVING:组的筛选,一定出现在分组后
- 语法:
SELECT FROM WHERE 条件,针对行 GROUP BY HAVING 条件,针对组 ORDER BY;
- 注:
- 执行流程:FROM->WHERE,行的筛选->GROUP BY,分组->HAVING,组的筛选->SELECT->ORDER BY
- 有GROUP BY,才能有HAVING
- HAVING中只能使用组函数或者分组的列
eg1:查询部门人数高于2人的部门编号,部门人数
SELECT dept_id, COUNT(id)
FROM s_emp
GROUP BY dept_id
HAVING COUNT(id)>2;
eg2:查询平均工资高于1100的部门编号,部门平均薪资
SELECT dept_id, AVG(salary)
FROM s_emp
GROUP BY dept_id
HAVING AVG(salary)>1100;
eg3:查询部门所有人薪资都高于1000的部门编号
SELECT dept_id
FROM s_emp
GROUP BY dept_id
HAVING MIN(salary)>1000;
eg4:查询职位包含sal(不区分大小写),并且对应职位人数高于2人的职位名,人数
SELECT title,COUNT(id)
FROM s_emp
WHERE LOWER(title) LIKE '%sal%'
GROUP BY title
HAVING COUNT(id)>2;
相较于下面这种写法,上面这种写法效率更高
SELECT title,COUNT(id)
FROM s_emp
GROUP BY title
HAVING LOWER(title) LIKE '%sal%' AND COUNT(id)>2;
嵌套查询:
- 查询里面的查询,叫子查询
SELECT
FROM
WHERE (
SELECT
FROM
WHERE
);
注:
- 查询里面嵌套的查询需要用()括起来
- 子查询可能出现的位置:WHERE子句,HAVING子句,FROM子句
WHERE子句中:
eg1:查询薪资高于Mark的薪资的员工id,薪资
SELECT id, salary
FROM s_emp
WHERE salary>(
SELECT salary
FROM s_emp
WHERE first_name='Mark'
);
eg2:查询高于平均薪资的员工id,名字和薪资
SELECT id, first_name, salary
FROM s_emp
WHERE salary>(
SELECT AVG(salary)
FROM s_emp
);
eg3:查询薪资高于3号和4号员工的员工编号,薪资
如果子查询返回多个结果,不能直接用>,=,<比较,多行子查询
--子查询产生多个结果,错误
SELECT id, salary
FROM s_emp
WHERE salary>(
SELECT salary
FROM s_emp
WHERE id IN(9,10)
);
--正确写法之一(多行子查询)
SELECT id, salary
FROM s_emp
WHERE salary>(
SELECT salary
FROM s_emp
WHERE id=9
)
AND salary>(
SELECT salary
FROM s_emp
WHERE id=10
);
/*
使用ALL,表示salary大于(小于)所有的结果 类似于AND
ANY:salary大于(小于)任意一个结果 类似于OR
IN:等于集合中的某一个
等于一般不使用ALL和ANY,而使用IN
*/
SELECT id, salary
FROM s_emp
WHERE salary>ALL(
SELECT salary
FROM s_emp
WHERE id IN(9,10)
);
--先求出最大值的方法也行(MAX)
HAVING子句中
eg1:查询部门平均薪资高于Bela薪资的所有部门编号,平均薪资
SELECT dept_id, AVG(salary)
FROM s_emp
GROUP BY dept_id
HAVING AVG(salary)>(
SELECT salary
FROM s_emp
WHERE first_name='Bela'
);
FROM子句中:
eg1:查询用户 表第6~10条记录(经典例子):
--rownum是关键字,所以外面的rownum与内部的rownum不同,查询结果不正确
SELECT *
FROM (
SELECT rownum, id,first_name, salary
FROM s_emp
WHERE rownum<11
)
WHERE rownum>5;
--取别名,得到正确结果
SELECT *
FROM (
SELECT rownum rn, id,first_name, salary
FROM s_emp
WHERE rownum<11
)
WHERE rn>5;
eg2:查询薪资最高的5个员工编号,薪资(经典例子):
--错误写法1,会先取前5个,在排序
SELECT id, salary
FROM s_emp
WHERE rownum<6
ORDER BY salary DESC;
--错误写法2,排序之前rownum已经确定,取到的还是前5个
SELECT id, salary
FROM(
SELECT id, salary,rownum r
FROM s_emp
ORDER BY salary DESC
)
WHERE r<6;
--正确写法
SELECT *
FROM(
SELECT id, salary
FROM s_emp
ORDER BY salary DESC
)
WHERE rownum<6;
给结果集起别名,如下,取别名为temp,如有需要可用该方式取别名
SELECT *
FROM(
SELECT id, salary
FROM s_emp
ORDER BY salary DESC
) temp
WHERE rownum<6;
eg3:查询薪资升序排序后第6~10的员工信息
SELECT *
FROM(
SELECT t.*, rownum rn
FROM (
SELECT id,first_name,salary,dept_id
FROM s_emp
ORDER BY salary ASC
) t
WHERE rownum<11
)
WHERE rn>5;
至此查询完结