MySQL （综合查询）

高级查询

聚合函数

MySQL内置了5种聚合函数，分别是SUM、MAX、MIN、AVG、COUNT。

-- sum : 求和
​
select sum(列名) from 表名 [其他子句];
​
-- max : 求最大值
​
select max(列名) from 表名 [其他子句];
​
-- min : 求最小值
​
select min(列名) from 表名 [其他子句];
​
-- avg : 求平均值
​
select avg(列名) from 表名 [其他子句];
​
-- count : 求数量
​
select count(列名) from 表名 [其他子句];

​

group by

group by 对数据进行分组，在分组后，select后面的列不能再出现group by 后面分组条件和聚合函数以外的其他列。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2; 

having

having : 对 group by 分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;

多表查询

多表关系

• 一对多（多对一）

• 多对多

• 一对一

笛卡尔乘积现象

笛卡尔乘积就是每个表的每一行都和其他表的每一行组合，想要消除无效的笛卡尔乘积，我们可以在多表查询的时候加上连接查询的条件即可；

等值连接查询

通常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表。

n个表进行等值连接查询，最少需要n-1个等值条件来约束

-- 查询每个分类的所有商品信息
select category.name,goods.name from goods,category where goods.category_no = category.no;

注：如果给表起了别名，就不能再使用表名来指定对应的字段，此时只能用别名来指定字段。

自连接查询

多表查询不止可以在多个表之间查询，也可以在一个表之间查询

-- Biri 的领导是谁（Biri 向谁报告）。  
select e.last_name, m.first_name,m.last_name,concat(m.first_name,m.last_name) 
from s_emp e,s_emp m 
where e.manager_id = m.id and e.last_name='Biri';
​
-- 将s_emp表看分别看成员工表和领导表，在一个表内进行自连接查询；

内连接查询

内连接查询的是两张表交集部分的数据。与等值连接差不多；

内连接查询使用inner join 关键字来实现，inner可以省略，条件用on连接，多个条件使用（）将其括起来

-- 查询每个种类的所有商品
select c.name, g.name from goods g join category c on c.no =
g.category_no;

外连接

外连接分为左外连接（left outer join）和右外连接（right outer join），条件用on连接，多个条件使用（）将其括起来。

左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表信息在从表中进行匹配

-- 左外连接（此时表1为主表）
select 字段名 from 表1 left join 表2 on 条件；
​
-- 右外连接（此时表2为主表）
select 字段名 from 表1 right join 表2 on 条件；

注意事项：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。相对于子查询来说，在外部直接执行的查询语句被称作主查询。

-- 单行子查询，此时父查询的查询条件是=
-- 查询零食种类的所有商品信息
select * from goods where category_no = (select no from category
where name = '零食');
-- 查询部门名称是 ‘ ACCOUNTING’ 的所有员工信息
select * from emp where deptno = (select deptno from dept where
dname = 'ACCOUNTING');
​
-- 多行子查询，此时父查询的查询条件是in ，any，all,not in
-- 统计所有员工都发布在那些部门信息
select * from dept d where d.dept_no in (select e.dept_no from emp e);
​
​
-- 多列子查询
-- 查询公司中和员工***相同薪水和奖金的员工
select * from emp e1 where (e1.sal,e1.comm) = (select
e2.sal,e2.comm from emp e2 where e2.ename = '张青');
​
-- 关联子查询
select student_name,
(select score from scores where subject = 'Math' and
students.student_id = scores.student_id) as math_score
from students;

IN : 在指定的集合范围之内，多选一

NOT IN : 不在指定的集合范围之内

ANY : 子查询返回列表中，有任意一个满足即可

ALL : 子查询返回列表的所有值都必须满足

>any : 表示大于子查询中的任意一个值，即大于最小值

>all : 表示大于子查询中的所有值，即大于最大的值

综合查询

SQL的书写顺序：

select --> distinct -->from --> join --> on --> where --> group by --> having--> order by--> limit

SQL的执行顺序：

from --> on --> join --> where --> group by --> having --> select --> distinct--> order by--> limit

补充

IFNULL 用法

MySQL IFULL 函数是MySQL控制流函数之一，它接受两个参数，如果不是 NULL ，则返回第一个参数。 否则， IFNULL 函数返回第二个参数。

select IFNULL(num, '暂无') from cart;
-- 如果行数据num为null，查询结果中显示‘暂无’，如果不是null，显示num的值

应避免在where 子句中使用 IFNULL 函数，因为它会降低查询的性能。如果要检查值是否为 NULL ，则可以在where 子句中使用 IS NULL 或 IS NOT NULL 。

CASE语句

CASE 语句遍历条件并在满足第一个条件时返回一个值（如 IF-THEN-ELSE 语句）。因此，一旦条件为真，它将停止读取并返回结果。

如果没有条件为真，它将返回 ELSE 子句中的值。

如果没有ELSE部分且没有条件为真，则返回NULL。

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
-- condition1, condition2, ...conditionN 条件
-- result1, result2, ...resultN 条件为真时返回的值
-- END 表示结束