MySQL学习（八）SQL进阶版

1.聚合查询

1.1聚合函数

常见的聚合函数如下：

用法很简单，当正常函数使用即可，如查询数学的平均值：

select avg(math) from exam_result;

1.2聚合字段

聚合字段是指按哪个字段进行聚合，聚合类似分组的意思，通常看到计算同类产品，相同产品等统计工作时我们就需要按照某字段属性进行聚合查询

关键字：group by(某字段)

如求各公司总数，就要按公司字段进行聚合：

 select company, count(*) from emp2 group by company;

1.3多聚合

如果要按照多个字段逐级去聚合，就叫多聚合

多聚合的各个字段用逗号分隔即可，如：

select company, depart, role, count(*)
from emp2 
group by company, depart, role;

返回结果：依次按公司、部门、角色进行聚合

1.4having关键字

【注意】聚合之后，也是可以继续使用其它诸如排序、分页等操作

如继续使用排序：

select company, depart, count(*) 
from emp2 
group by company, depart 
order by count(*);

但是！！！如果聚合后要继续进行过滤条件的话，不能继续使用where了，要使用having

select company, depart, role, count(*) 
from emp2 
group by company, depart, role
having count(*) = 1;

ps:having & where

where筛选的是数据库中本来就有的字段，是先where筛选再select，而having是先select再having筛选，是在已经筛选过的新库表里再having

2.联表查询

所谓联表查询，就是查询结果需要在多张表上进行联合查询；

多表查询的结果是对多张表取笛卡尔积

2.1 普通联表查询

【ps】普通联表查询默认为内连接

（1）直接from后跟联表名称，用逗号隔开

以联合查询users表和articles表为例，原表数据如下:

联表查询：

-- 在 from 后边直接跟 2 张表（2 张以上也可以）
-- 视为同时从 2 张表中查询数据
select * from users, articles;    -- 一共 6 条数据 = 2 * 3

结果：

（2）使用join关键字

select * from users join articles;

结果一样：

2.2 +过滤条件

（1）直接逗号连接的，可直接加where语句

-- 添加 联表 条件后，得到的结果才是有意义的
select * 
from users, articles 
where users.uid = articles.author_id;

不止一个过滤条件的，接着用and

select * 
from users, articles 
where uid = author_id and users.name = '小红';

（2）用join连接的，可以用关键词on进行条件过滤（用where也不会报错，但是（1）中没有join而用on却会报错）

select * 
from users join articles 
on uid = author_id;

（3）

select * 
from users, articles 
where uid = author_id and users.name = '小红';

select * 
from users 
join articles 
on uid = author_id 
where users.name = '小红';

select * 
from users 
join articles 
on uid = author_id and users.name = '小红';

以上三种写法是相同的意思，均正确

2.3 内连接

以上连接均为内连接，实则是省略了inner这个单词

select * 
from users 
inner join articles   -- inner 可以省略
on uid = author_id;        

2.4 左外连 and 右外连

左外连和右外连共同构成了外连接

• 左外连：left outer join，左侧的表完全显示
• 右外连：right outer join，右侧的表完全显示

其中，outer这个单词可以省略不写

为了便于理解，我们稍微修改一下原表数据

可以看到，当我们要找uid==author_id时，左表中的序号2在右表中无对应行，而右表中的序号3在左表中无对应行

🤨🤨🤨对于内连接：只会显示所有匹配的上的

select * 
from users 
inner join articles     -- inner 可以省略
on uid = author_id;        

🤨🤨🤨来看左外连：

-- 左外联
select * 
from users 
left outer join articles 
on uid = author_id; 

🤨🤨🤨再来看右外连：

-- 右外联
select * 
from users 
right outer join articles 
on uid = author_id;

2.5 自连接

自连接就是同一张表连接自己进行查询

典型例子：查询计算机原理成绩比java成绩高的同学（因为是同一个同学的不同字段比较查询，所以需要自连接查询，自连接查询至少要给其中一张自己起个别名，便于区分）

先看原表结构：

成绩score表

学生信息student2表

课程信息course表

• 先自连接查询：

select s1.student_id
from score s1
join score s2
-- 只保留自连接结果中相同学号的行
on s1.student_id = s2.student_id 
-- 计算机原理的课程编码为3
where s1.course_id = 3 
-- Java的课程编码为1
and s2.course_id = 1
and s1.score > s2.score;

查询结果：

这样的查询结果没啥用，看不到是谁

• 所有我们优化一下，三张表都连接一下，返回详细信息

    -- 联合查询
    select student2.id,student2.name,s1.score 计算机原理,s2.score Java
    from score s1
    join score s2
    -- 同一名学生的
    on s1.student_id = s2.student_id
    -- 去course表中找课程id
    and s1.course_id in (select id from course where course.name = '计算机原理')
    and s2.course_id in  (select course.id from course where course.name = 'Java')
    and s1.score > s2.score
    -- 连接student2表，获取学生信息
     join student2
    on s1.student_id = student2.id;

3.子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

3.1 单行子查询

直接小括号嵌套即可：

select * 
from student 
where classes_id = (select classes_id from student where name='不想毕业');

3.2 多行——in / not in

如上面刚写过的查找课程编号操作：

in:存在

not in :不存在

3.3 多行——exists / not exists

-- 使用 EXISTS
select * 
from score sco 
where exists (
    select sco.id from course cou where (name='语文' or name='英文') and cou.id = sco.course_id
) order by id;

3.4 in & exists 区别

可以理解为：

in：先执行内部查询，就是先去找满足in内部的条件查询，然后在找到的符合条件里再执行外部查询继续筛选

exists：先通过外部的 sql 得到结果，将每行的结果，代入到内部的exists里的 sql中，，如果能得到结果（行数 > 0），说明满足了 exists 的条件，将满足的返回结果

4.合并查询

（1）union 合并重复行

（2）union all 不合并重复行

【】注意，用union合并的查询，最后有一个分号即可

如：

组合 Products 表中的产品名称和 Customers 表中的顾客名称_牛客题霸_牛客网 (nowcoder.com)

select prod_name from Products
union
select cust_name prod_name from Customers
order by prod_name;

5.真题题解

1.返回 2020 年 1 月的所有订单的订单号和订单日期_牛客题霸_牛客网 (nowcoder.com)

select order_num,order_date
from Orders
where year(order_date) = 2020
and month(order_date) = 1
order by order_date;

2.顾客登录名_牛客题霸_牛客网 (nowcoder.com)

select cust_id,cust_name,
upper(concat(substr(cust_contact,1,2),substr(cust_city,1,3))) user_login
from Customers;

由这两道题，除了上文中写到的聚合函数，我们再来扩展一些常用到的函数：

（1）字符串函数

• upper()——转大写
• lower()——转大写
• substr(string,start,length)——截取从start开始长度为length个范围内的字符（start从1开始）
• concat(str1,str2)——拼接两个字符串

（2）日期和时间函数

• month()——返回月
• year()——返回年

3.计算总和_牛客题霸_牛客网 (nowcoder.com)

select order_num,sum(item_price * quantity) total_price
from OrderItems
group by order_num
having total_price >= 1000;

4.确定最佳顾客的另一种方式（二）_牛客题霸_牛客网 (nowcoder.com)

select cust_name,total_price
from Customers c
join Orders o
on o.cust_id = c.cust_id
join(
    select oi.order_num,sum(item_price * quantity) total_price
    from OrderItems oi
    group by oi.order_num
    having  total_price >= 1000

) j
on j.order_num = o.order_num
order by total_price;

5.返回产品名称和每一项产品的总订单数_牛客题霸_牛客网 (nowcoder.com)

select prod_name,ifnull(orders,0) orders
from Products p
left outer join
(select prod_id,count(order_num) orders
from OrderItems
group by prod_id) j
on p.prod_id = j.prod_id
order by prod_name;

6.返回每个顾客不同订单的总金额_牛客题霸_牛客网 (nowcoder.com)

select o.cust_id,total_ordered 
from Orders o
join
(select order_num,sum(item_price * quantity) total_ordered 
from OrderItems
group by order_num) t
on o.order_num = t.order_num
order by total_ordered desc;