数据库开发（五）组函数，子查询

组函数：

• 单行函数：每一行会得到一个结果
• 组函数（多行函数）：一组得到一个结果，默认情况下所有的数据是一组

常用组函数（统计）：

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

至此查询完结