0


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;

标签: 数据库

本文转载自: https://blog.csdn.net/m0_58652786/article/details/124123443
版权归原作者 笨笨在努力 所有, 如有侵权,请联系我们删除。

“MySQL学习(八)SQL进阶版”的评论:

还没有评论