不正经

这个屌丝很懒，什么也没留下！

热门标签

5-高级SQL查询_sql高级查询总结

作者：不正经 | 2024-06-17 06:58:40

踩

sql高级查询总结

PS：sum函数执行逻辑——只会统计有效的数据

PS：sum函数查询 VS 表达式求和查询

PS：distinct（去重） VS group by（分组）

①创建测试数据

②distinct使用

③group by使用

④distinct 和 group by的区别

PS：group by 注意事项

①group by 之后不能加 where

②having 或 group by 可单独使用。

③（常见面试题）为什么 where 不能用别名，而 having 和 group by 能用别名？

--->①on 在内连接和外连接中的作用不一样

--->②在外连接中 on 和 where 的作用不一样

PS：插入查询结果

把一张表查询出的结果，插入到另一张表中。要求两张表的列数和列类型要匹配。

前面讲的所有select规则在此处都适用~

1.聚合查询

聚合查询：行和行之间进行运算。
带表达式查询：列和列之间进行运算。

常⻅的聚合函数（SQL里内置的函数）有：

它们都是针对表中某个列的所有行来进行运算的~

MySQL的函数，相当于Java里的方法：

聚合函数需要设置参数，在查询中可以使用多次聚合函数，可以多个聚合函数一起使用。

不是数字的数据，直接舍弃。

1.1.count函数

返回查询到的数据的条数。

用法一：

能查询出所有null和非null的数据总和。推荐使用，最标准，MySQL、SQL Server、Oracle都能使用。

用法二：

能查询出所有null和非null的数据总和。兼容性不是很好，MySQL能用，其他数据库不一定能用。

用法三：

count(列名)，查询当前字段中非null的数据总和。不推荐使用，查询结果极不稳定。

建议写成：

用法四：

count(表达式)。

count(1)1就是常数表达式。

在不同的count统计场景下，要使用不同的count查询。

若要查询表中非null的数据总和，建议使用count(*)配合where条件使用。

1.2.sum函数

返回查询到的数据的总和，不是数字没有意义。

PS：sum函数执行逻辑——只会统计有效的数据

判断数据是否为有效数据：

若是有效数据：会将其加入已经计算的结果集中。
若不是有效数据（NULL或非数值型（比如varchar型））：会将其看作0，并加入已经计算的结果集中。

PS：sum函数查询 VS 表达式求和查询

若要求和查询的数据中包含NULL：

sum函数：会把除去NULL外的其他数据进行求和运算。
表达式求和：直接得到和为NULL。

1.3.avg函数

返回查询到的数据的平均值，不是数字没有意义，会将含有不合规范数据的一行数据整体舍弃掉。

1.4.max函数

返回查询到的数据的最⼤值，不是数字没有意义。

1.5.min函数

返回查询到的数据的最⼩值，不是数字没有意义。

PS：ifnull函数

ifnull函数是 MySQL 控制流函数之⼀，它接收两个参数，如果第一个参数不是 NULL，则返回第⼀个参数，否则返回第⼆个参数。
ifnull(参数1,参数2);
使用ifnull函数的实例——解决遗留问题：总成绩为null的问题

另外的解决⽅案：使⽤⾮空约束或默认值 0 来解决。

注：

在公司里多使用分布式多份部署，一份程序部署到了多台服务器，处理能力会是原来的多倍，但没有办法把MySQL分成多份。
so~MySQL的资源很紧缺，而程序的资源相对不紧缺。在MySQL中应尽量不使用表达式和函数（它们应该是在Java程序里）。

所以这种问题最好在建表时就设置好非空约束，从而避免ifnull查询，减轻MySQL负担。

2.分组查询group by

select 中使⽤ group by ⼦句可以对指定列进⾏分组查询。

需要满⾜：使⽤ group by 进⾏分组查询时，select 指定的字段必须是“分组依据字段”，其他字段若想出现在 select 中则必须包含在聚合函数中。

select 列名1 [,sum(列名2)...] from 表名 group by 列名1 [,列名3];

PS：distinct（去重） VS group by（分组）

①创建测试数据

②distinct使用

基本语法：
select distinct 列名 [,列名...] from 表名;
a.单列去重

根据 aid（文章 ID）去重：

b.多列（两列及以上）去重

根据 aid（文章 ID）和 uid（用户 ID）联合去重：

c.聚合函数 + distinct

计算 aid 去重之后的总条数：

③group by使用

基本语法：
select 列名 [,列名...] from 表名 group by 列名
a.单列去重

根据 aid（文章 ID）去重：

与 distinct 相比 group by 可以显示更多的列，而 distinct 只能展示去重的列。

b.多列去重

根据 aid（文章 ID）和 uid（用户 ID）联合去重：

c.聚合函数 + group by

统计每个 aid 的总数量：

从上述结果可以看出，使用 group by 和 distinct 加 count 的查询语义是完全不同的，distinct + count 统计的是去重之后的总数量，而 group by + count 统计的是分组之后的每组数据的总数。

④distinct 和 group by的区别

官方文档在描述 distinct 时提到：在大多数情况下 distinct 是特殊的 group by：

但二者还是有一些细微的不同的，比如以下几个：

区别1：查询结果集不同

1）当使用 distinct 去重时，查询结果集中只有去重列信息。当试图添加非去重字段查询时，SQL 会报错：

2）而使用 group by 排序可以查询一个或多个字段：

区别2：使用业务场景不同

1）统计去重之后的总数量需要使用 distinct。使用 distinct 统计某列去重之后的总数量：

2）而统计分组明细，或在分组明细的基础上添加查询条件时，就得使用 group by 了。使用 group by 统计分组之后数量大于 2 的文章：

区别3：性能不同

1）如果去重的字段有索引，那么 group by 和 distinct 都可以使用索引，此情况它们的性能是相同的。

2）而当去重的字段没有索引时，distinct 的性能就会高于 group by，因为在 MySQL 8.0 之前，group by 有一个隐藏的功能会进行默认的排序，这样就会触发 filesort 从而导致查询性能降低。

PS：group by 注意事项

以下内容基于MySQL5.7 InnoDB 数据库引擎。

①group by 之后不能加 where

要求语法顺序：

select
from
where
group by
having
order by
limit

select 执行顺序：

from 阶段
where 阶段
group by 阶段
having 阶段
select 阶段
order by 阶段
limit 阶段

②having 或 group by 可单独使用。

③（常见面试题）为什么 where 不能用别名，而 having 和 group by 能用别名？

having 和 group by 可使用别名。 where 不能使用别名。order by 可使用别名。

where不能用别名，这和MySQL语句执行顺序有关。在执行where查询时，select还没执行完，因此where中想要用还未执行完的select中的别名是不行的。
order by 能使用别名是因为 select 是在 order by 之前。
若按上述逻辑，在 group by 和 having 中也不能使用别名~but：

在 group by 和 having 中能使用别名是因为MySQL为了使用方便，在5.7.5之后做了扩展，允许在 group by 和 having 中使用别名，官方文档中有对应说明。

PS：having VS where

having能用别名，where不能用别名。
where能用select中未出现的列进行条件筛选，而having不行。


-- 准备测试表及数据：
create table emp(
    id int primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary numeric(11,2) -- numeric是decimal更细化的一个类型，用法和decimal类似
);
 
insert into emp(name, role, salary) values
('⻢云','服务员', 1000.20),
('⻢化腾','游戏陪玩', 2000.99),
('孙悟空','游戏⻆⾊', 999.11),
('猪⽆能','游戏⻆⾊', 333.5),
('沙和尚','游戏⻆⾊', 700.33),
('隔壁⽼王','董事⻓', 12000.66);

2.1.分组条件查询having

group by⼦句进⾏分组以后，需要对分组结果再进⾏条件过滤时，不能使⽤where语句，⽽需要用having。

group by是在where之后，它的执行顺序也是在where之后。
having是在group by之后，它的执行顺序也是在group by之后。

2.2.SQL查询关键字执行顺序

SQL 查询执⾏先后顺序： group by > having > order by > limit。

3.联表查询（多表查询）

3.1.前置知识——笛卡尔积

笛卡尔积⼜称直积，表示为 X*Y，⽐如 A 表中的数据为 m ⾏，B 表中的数据有 n ⾏，那么 A 和 B 做笛卡尔积，结果为 m*n ⾏。

要进行联表查询，首先得到笛卡尔积，再去除掉无效数据，得到有效数据。

⽐如以下表，⼀个⼈只能在⼀个班级，它们的笛卡尔积就有 9 个：

排除掉脏数据，有效的数据⾏信息有：

用户表-编号	用户表-名称	用户表-班级id	班级表-编号	班级表-班级名	班级表-状态
s1	张三	c1	c1	Java31	正常
s2	李四	c2	c2	Java33	正常
s3	王五	c3	c3	Java30	已毕业

若有3张表，表中数据行分别为a行、b行、c行，那么三张表做笛卡尔积，结果为a*b*c行......

创建数据库和表：


-- 创建数据库
drop database if exists java33;
create database java33 default character set 'utf8mb4';
 
-- 切换数据库
use java33;
 
-- 创建班级表
drop table if exists class;
create table class(
    id int primary key auto_increment comment '班级编号',
    classname varchar(250) not null comment '班级名称'
);
 
-- 创建学⽣表
drop table if exists student;
create table student(
    id int primary key auto_increment comment '学生编号',
    sn varchar(50) comment '学号',
    username varchar(250) not null comment '学生名称',
    `mail` varchar(250) comment '邮箱',
    class_id int,
    foreign key (class_id) references class(id)
);
 
-- 创建课程表
drop table if exists course;
create table course(
    id int primary key auto_increment comment '课程编号',
    name varchar(250) not null
);
 
-- 成绩表
drop table if exists score_table;
create table score_table(
    id int primary key auto_increment comment '成绩编号',
    score decimal(4,1),
    student_id int not null,
    course_id int not null,
    foreign key (student_id) references student(id),
    foreign key (course_id) references course(id)
);

添加测试数据：


-- 班级表添加数据
insert into class(id,classname) values(1,'Java班级'),(2,'C++班级');
 
-- 课程表添加数据
insert into course(id,name) values(1,'计算机'),(2,'英语');
 
-- 学⽣表添加数据
insert into student(id,sn,username,mail,class_id) 
values(1,'CN001','张三','zhangsan@qq.com',1),
(2,'CN002','李四','lisi@qq.com',2),
(3,'CN003','王五','wangwu@qq.com',1);
 
-- 成绩表添加数据
insert into score_table(id,score,student_id,course_id) values(1,90,1,1),(2,59,1,2),(3,65,2,1),(4,NULL,2,2);

3.2.内连接

内连接侧重于两个表之间的共性，它的作⽤是使⽤联接，⽐较两个（或多个）表之间的共有数据，然后进⾏返回。

⽐如要查询学⽣的成绩，涉及到两张表：学⽣表和成绩表，使⽤内连接查询的数据是下图的红⾊部分：

3.2.1.内连接语法


select * from 表名1 [inner|cross] join 表名2 [on 过滤条件] [where 过滤条件];
-- inner 内连接
-- cross 交叉
-- join 加入（内连接关键字，必须要有）
-- on 从语法上可以省略，如果省略 on 得到的是多表的笛卡尔积，会含有无效数据
-- where 根据业务场景来决定是否要加

内连接的写法有以下 4 种：

select * from 表名1 join 表名2 [on 过滤条件] [where 过滤条件]; (两张表、多张表建议使用)（主要掌握）
select * from 表名1 inner join 表名2 [on 过滤条件] [where 过滤条件];
select * from 表名1 cross join 表名2 [on 过滤条件] [where 过滤条件];
select * from 表名1, 表名2 [where 过滤条件]; （不支持on过滤条件）(两张表建议使用)（主要掌握）

3.2.2.实战

①查询张三的成绩

a.进行内连接查询（笛卡尔积）

b.去掉无效的数据（on过滤条件）

c.查询张三的成绩

写法一：

写法二：

写法三：

写法四：

改进：不要使用*，只查询需要的列：

若多张表中有相同字段名，得区分开不同的表名中的字段名，使用别名会简单些。

不强制要求给表起别名，但建议都加上别名。

表的别名通常是使用表前缀的缩写。

②查询每个⼈的成绩和个⼈信息

③查询每个⼈的总成绩和个人信息（姓名）

a.查询学生表和成绩表（笛卡尔积）

b.去掉无效数据(关联字段)

c.使用聚合函数 sum 统计总成绩

如果成绩是多列存储，使用表达式。
如果成绩是多行存储，使用聚合函数sum()。

d.根据学生（id）分组查询每个人的

④查询每个⼈成绩+科⽬名+个⼈信息


-- 1.联表查询（3张表）
select * from 表名1 join 表名2 join 表名3;
 
-- 2.过滤掉笛卡尔积中的无效数据
select * from 表名1 join 表名2 [on 过滤条件] join 表名3 [on 过滤条件];

⑤查询学生的成绩和科目及个人信息，要求根据相同科目显示在一起，并按照成绩降序排列，并将null替换成0

a.实现3张表的联查join（笛卡尔积）
b.得到笛卡尔积中的有效数据 join...on...join...on
c.将科目显示到一块（对行顺序有要求）使用排序：order by c.name
d.上一个排序的规则上，加联合排序：order by c.name, st.score desc
e.select ifnull(st.score, 0)

3.2.3.内连接查询的"问题"

我们发现学⽣表有 3 个⽤户，然⽽使⽤内连接查询的时候，王五同学的数据⼀直没被查询到，王五同学可能是考完试转班过来的，所以只有学⽣表有数据，其他表没有数据。但即使这样，我们也不能漏⼀个⼈，如果其他表为空，成绩可以是 NULL 或者 0，但不能遗漏，这个时候就需要使⽤外连接了。

3.3.外连接

外连接包括内连接和其他⾄少⼀张表的所有满⾜条件的信息，外连接包括：

左（外）连接
右（外）连接

其中左连接查询的内容如下图红⾊部分：

右连接如下图红⾊部分：

3.3.1.左连接

左连接表示以左边的表（表1）为主查询数据。

表1中的所有数据都会被查询出来，表2只会查询出和表1相关联的数据。


select * from 表名1 left join 表名2 on 连接条件;
-- 必须加 on

查询所有人的成绩：

查询所有人的个人信息+课程名+分数：（3张表要把中间关联表放在中间位置）

3.3.2.右连接

右连接表示以右边的表（表2）为主查询数据。

表2中所有的数据都会被查询出来，表1只会查询出和表2相关联的数据。

select * from 表名1 right join 表名2 on 连接条件;

3.3.3.左连接 VS 右连接

左连接：查询出左表中的所有数据，和右表中关联的数据。
右连接：查询出右表中的所有数据，和左表中关联的数据。

所有的左连接都能使用右连接来实现，所有的右连接都能使用左连接来实现。

如果查询的是两张表，只需要调换两个表的位置，就可以轻松实现左连接和右连接的互换，因此掌握一种外连接来查询即可。

3.3.4.on 和 where 的区别

在内连接中 on 可以省略；在外连接（左/右连接）中 on 不能省略【若在外连接中就想不要 on 只查询出笛卡尔积，则可以写上 on 后加一个永远为 true 的无意义的表达式，如 on 1=1】。
on 在内连接中的执行效果和在外连接中的执行效果不一样：left join on 查询不能过滤掉左表中的数据，能过滤掉右表中的数据；而内连接 on 查询能过滤掉全局数据。
在外连接中 on 和 where 的作用也不一样：过滤笛卡尔积的条件放在on后，其他多个查询条件放在where后。

select * from student where 1=1 and username='xxx' and score='xxx' and password='xxx' and ...;

若查询条件是可选项，可以在where后加上永远为true的条件表达式，这样就可以不用管username、score、password...谁是第一个，谁是第二个...因为第一个前有and，就不用管where后第一个条件表达式的问题了。

--->①on 在内连接和外连接中的作用不一样

内连接 on 查询能过滤掉全局数据。
外连接 on 查询不会对主表中的数据过滤，只会对次表中的数据过滤。

left join on 查询不能过滤掉左表（主表）中的数据，能过滤掉右表（次表）中的数据。
right join on 查询不能过滤掉右表（主表）中的数据，能过滤掉左表（次表）中的数据。

on 在内连接中的作用：

on 只过滤笛卡尔积中的无意义数据：

on 过滤全局列表中的数据：

on 在外连接中的作用：

--->②在外连接中 on 和 where 的作用不一样

内连接2张表查询时，多个查询条件可以一股脑都放在on后。
外连接2张表查询时，过滤笛卡尔积的条件放在on后，其他多个查询条件放在where后。

3.4.自连接

⾃连接是指在同⼀张表连接⾃身进⾏查询。⾃连接就是查询同⼀张表的内查询。

select *|列名 [,列名...] from 表名 [as] 别名1, 表名 [as] 别名2 where 别名1.字段名=别名2.字段名 [,...];

查询英语成绩<计算机成绩的数据：

都是查询成绩，自查询（2张成绩表进行查询）【笛卡尔积】。
添加where条件：保证2张成绩表都是一个人的成绩【where st1.student_id=st2.student_id】表1和表2里都是既有英语成绩又有计算机成绩。
添加where条件：指定表1为英语表，表2为计算机表。
添加where条件：英语成绩<计算机成绩的数据。