MySQL：查询（万字超详细版）

** 💎所属专栏**： MySQL

在这里插入图片描述

💎1. 单表查询

💎1.1 全列查询和指定列查询

全列查询：

select * from exam;

在实际开发中不要使用 * 来进行查询，因为数据库会很大，影响效率

指定列查询：

select id,name,math from exam;

查询的结果是一个表达式，例如，在查询命令中添加一个数值，每一列都会有这个数值

select id ,name ,10 from exam;

之后，可以通过as关键字来为查询结果中的列指定别名，as和引号可以省略，但如果别名中存在空格就不能省略引号了

select id as '编号' ,name as '姓名' from exam;

列与列之间还可以进行运算：

select chinese + math + english as '总分' from exam;

还可以直接加上指定的数字

select math + 10 from exam;

💎1.2 去重查询

去重查询是通过关键字 distinct 来实现的，在之前的math中，是存在一个重复的98的，我们来试验一下去重查询操作：

select distinct math from exam;

有一点需要注意的是，在查询结果中，每一列都相同才认为是重复数据，刚刚只查询的是math这一列，这次加上id试试：

可以看到，这一次重复的98并没有被去掉，因为id不同

💎1.3 排序查询

对于多字段，按照字段的前后顺序，如果第一个字段相同，按照第二个字段进行排序

select math from exam order by math desc ;

如果数据为 null 就认为是最小的，升序排最前面，降序排最后面

还可以加上之前的表达式和别名进行排序

select id ,name, math + chinese + english as '总分' from exam order by math + chinese + english desc;

前面已经定义了别名，所以后面可以直接用

select id ,name, math + chinese + english as '总分' from exam order by 总分 desc;

关于null的特殊情况：

1.不论和任何值相加，结果都是null

2.始终被看作false

3.null 并不等同于 0

所以说，如果想上面的按照总分排序，如果其中一门为Null,那么他的总分就是null

最后还可以通过多个字段进行排序，排序的优先级就按照书写的顺序进行排列

例如：先按数学降序排列，再按语文升序排列，再按英语顺序排列

select id, name, math, chinese,english
from exam
order by math desc, chinese asc, english asc;

💎1.4 条件查询

💎1.4.1 比较运算符

先来看比较运算符

前面的大于等于这些符号和java中一样，就不多说了，而java中相等是用 "==" 表示的，mysql中是 "=" ，判断不等于的这两个写法都可以

这里有一些小细节需要注意：

-- 查询英语不及格的
select name ,english from exam where english < 60;

如果表中有null，不参与筛选，所以最终的结果不会包括null

-- 查询英语比语文好的
select * from exam where english > chinese;

在上面的比较方式中，同一行的数据是可以比较的，但是不能跨行比较

select name, english + math + chinese as total
from exam
where english + math + chinese < 250
order by total desc ;

如果where 后面使用了表达式的话要写完整的表达式，不能写别名

原因：和SQL的执行顺序有关

1.如果要在数据库中查找某些数据，首先要确定表，先执行****from

2.在查询过程中，要根据指定的查询条件把符合条件的数据过滤出来，这时执行的是where字句

3.执行select后面的指定的列，这些列最终要加到结果的展示集里

4.根据**order by 子句的列名和排序规则进行最后的排序**

根据以上顺序可以推断出，在执行where语句时，还没到select里的total，所以用不了
<=>表示等于，是专门针对null判断的，例如null <=> null 的结果为1
null的判断是不能用 "=" 判断的

接下来的 between...and...和 in (...) 都是字面意思，分别用来判断是否在一个区间和是否在括号中的列表中

select 1 in (1,2,3);
select 2 between 1 and 4;

而like表示模糊匹配的意思是，按照单个字符和任意个字符进行匹配，来看一个例子：

** 当用 '%' 表示的是找出姓张的人，任意字符也就是张后面可以跟任意个字符，'_' 表示单个字符，也就是张后面只能跟一个字符**

最后还有一个判断是否为null的命令

💎1.4.2 逻辑运算符

接下来看逻辑运算符
运算符说明对应Java中的逻辑运算符AND多个条件必须都为 TRUE(1)，结果才是 TRUE(1)&&OR任意一个条件为 TRUE(1), 结果为 TRUE(1)||NOT条件为 TRUE(1)，结果为 FALSE(0)!

-- 语文大于80或英语大于80
select name, english, chinese
from exam
where english > 80
   or chinese > 80;

or 的话是任意一个条件为true 就符合，哪怕另一个条件为null不参与比较

-- 语文英语都大于80分
select name, english, chinese
from exam
where english > 80
  and chinese > 80;

下面来分析一下and 和 or 的优先级：

-- 比较and 和 or 的运算优先级
select name, chinese, math, english
from exam
where chinese > 80
   or math > 70 and english > 70;

select name, chinese, math, english
from exam
where (chinese > 80 or math > 70)
  and english > 70

通过对比发现，and 和 or 和 java 中的优先级是一样的，都是and > or ，不过还是建议根据需求加括号

💎1.5 分页查询

在实现已经提到过，如果直接通过select * from不加限制来查询全部的数据是不安全的，通过分页查询可以有效的控制一次查询出来的结果集中的记录条数，可以有效的减少数据库服务器的压力，也有利于用户查看，例如我们经常见到的这种就是用到了分页查询

例如从第0条开始，往后读取2条数据有一下这几种写法：

-- 从第0条开始往后读取2条数据
select *
from exam
order by id
limit 2;

select *
from exam
order by id
limit 0,2;

select * from exam
order by id
limit 2 offset 0;

可以通过下面这个公式来计算第 s 页所需要的偏移量 n:

n = (s - 1) * 每页显示的记录数

如果说指定的起始位置超出了整个表的范围就会返回一个空的结果集

💎1.6 分组查询

where 和 having的区别：

执行时机不同：where 是分组前进行过滤，不满足where条件不进行分组，having是对分组后的结果进行过滤

判断条件不同：where不能对聚合函数进行判断，而having可以

-- 根据角色进行分组，显示出角色和平均薪资,使用round四舍五入
select role, round(avg(salary), 2)
from emp
group by role;

-- 讲分组后的数据根据薪资进行升序排列
select role, round(avg(salary), 2) as 平均薪资
from emp
group by role
order by 平均薪资 ASC;

通过having 对分组后的数据进行过滤：

-- 对分组后的数据进行过滤
select role, round(avg(salary), 2) as 平均薪资
from emp
group by role
having 平均薪资 > 10000
   and 平均薪资 < 100000;

综合小练习：

查询平均薪资低于10000的角色和平均薪资

-- 显示平均薪资低于10000的角色和其平均薪资
select role ,avg(salary) as 平均薪资
from emp
group by role
having 平均薪资 < 10000;

💎2. 多表查询

联合查询就是联合多个表进行查询，为了消除表中字段的依赖关系，设计数据时把表进行拆分，这时就会导致一条SQL语句查找出来的数据不够完整，就可以通过联合查询把关系中的数据全部查出来，在一个数据行中显示详细信息

步骤：

1.首先确定哪几张表要参与查询

2.根据表于表之间的主外键关系，确定过滤条件

3.精简查询字段

例如上面的两个表，通过联合查询获取下面的表的这种信息：

💎2.1 内连接

取多张表的笛卡尔集

分别创建上面的class，student表，并添加数据：

create table class
(
    id   bigint primary key auto_increment,
    name varchar(20)
);

create table student
(
    id       bigint primary key auto_increment,
    name     varchar(20),
    gender   varchar(1),
    class_id bigint,
    foreign key (class_id) references class (id)
);
insert into class(id, name)
values (1, 'Java01'),
       (2, 'Java02');
       
insert into student(id, name, gender, class_id)
values (1, '张三', '男', 1),
       (2, '李四', '男', 2),
       (3, '王五', '女', 2),
       (4, '赵六', '男', 1);

通过下面的这个命令可以查询到两张表取笛卡尔集后的数据：

select * from student,class;

但是会发现，有四条数据是不匹配的

2.通过连接条件过滤掉无效的数据

由于这两个表是存在主外键关系的，只需要判断主外键字段是否相等

-- 通过连接条件过滤掉无效的数据
select *
from student,
     class
where student.class_id = class.id;

这样，得到的就都是正确的数据了

3.通过指定列查询精简结果集

-- 指定列查询精简结果集
select student.id, student.name, class.name
from student,
     class
where student.class_id = class.id;

通过指定列查询可以使结果更加精简，这也就是内连接的第一种写法

还可以加入别名的方式进行简化SQL语句

-- 别名方式简化
select s.id, s.name, c.name
from student s,
     class c
where s.class_id = c.id;

第二种写法：

通过关键字 inner join 来实现的，from 表1 inner join 表2 意思是从表1到表2建立内连接，关注点是表1，从表1里面找表2的连接

-- 写法2
select s.id, s.name, c.name
from student s
         inner join class c on class_id = c.id;

inner 也可以省略

-- inner可以省略
select s.id, s.name, c.name
from student s
         join class c on class_id = c.id;

💎2.1.1小练习

创建的表的部分内容如下

找出许仙的成绩：

首先确定要联合的表，这里联合student和score表，然后对目标表去笛卡尔集，再通过连接条件进行过滤，再精确字段

select *
from student,
     score
where student.student_id = score.student_id
  and name = '许仙';

此时发现显示的字段有点多，再根据要求查询指定字段

-- 指定字段
select name, score
from student,
     score
where student.student_id = score.student_id
  and name = '许仙';

查询每位同学的总成绩和学生信息：

和前面步骤一样

select student.student_id, sum(score.score)
from student,
     score
where student.student_id = score.student_id;

之后，由于求的是每个同学的总分，所以还需要按照student_id进行分组，再精简字段

select student.student_id,student.name, sum(score.score)
from student,
     score
where student.student_id = score.student_id
group by student.student_id;

查询每位同学每一门的成绩：

还是按照之前的步骤进行，只不过这次需要用到三张表

select student.student_id,student.name,course.name,score.score
from student,
     score,
     course
where student.student_id = score.student_id
  and score.course_id = course.course_id;

使用 [inner] join on 的形式：

select st.student_id, st.name, c.name, sc.score
from student st
     inner
         join score sc
              on st.student_id = sc.student_id
         join course c
              on c.course_id = sc.course_id;

💎2.2 外连接

外连接又分为左外连接和右外连接

内连接和外连接的区别：

内连接只会查询到两个表的交集部分，外连接可以查询左边或右边整个表

💎2.2.1右外连接

右链接就是以 join 右边的表为基准，显示这个表的全部数据，左边的表如果没有匹配的记录的话会以null作为补充

-- 使用右外连接
select student.student_id, student.name, class.class_id, class.name
from student
         right join class on student.class_id = class.class_id;

这里从 student 表到 class 表建立右外连接，没有学生的班级id也会显示出来，用null代替

**右边class表中的数据都显示出来了，左边没有与之对应的行用null补充 **

💎2.2.2 左外连接

需求：查询哪位同学没有参加考试

也就是在student表里有记录，在score表里没有对应的记录

这时就可以使用左连接，把student表作为基准表

-- 左外连接
select student.student_id, student.name, score
from student
         left join score on student.student_id = score.student_id;

💎2.3 自连接

自连接可以实现行与行之间的比较功能

需求：找出计算机原理课程成绩大于Java的

还是和之前的步骤一样，只不过这里由于是自连接，取笛卡尔集的时候需要确定别名

-- 找出计算机原理的成绩大于Java的
-- 取笛卡尔集
use test3;
select *
from score s1,
     score s2
where s1.student_id = s2.student_id;
-- 确定过滤条件
select *
from score s1,
     score s2
where s1.student_id = s2.student_id
and s1.course_id = 3 and s2.course_id = 1
and s1.score > s2.score;

自连接查询可以是内连接，也可以是外连接，在下面这张员工表中
idnamejobmanager_id1张三总裁
null
2李四项目经理
1
3王五工程师2
来看内连接的例子：

查询员工及其所属领导的名字

select a.name, b.name
from emp a,
     emp b
where a.manager_id = b.id;

查询所有员工及其领导的名字（如果员工没有领导也要表示出来）

select a.name, b.name
from emp a
         left join emp b on a.manager_id = b.id;

💎2.4 子查询

SQL语句中嵌套select语句称为嵌套查询，又叫子查询

根据查询的结果又可以分为：标量子查询（查询结果为单个值），列子查询（查询结果为一列），行子查询（查询结果为一行），表子查询（查询结果为多行多列）

💎2.4.1 单行子查询

需求：查询与许仙一个班级的同学

如果不适用子查询的话，就需要用多条SQL语句来查询

-- 查找和许仙一个班级的同学
select class_id from student where name = '许仙';
-- 得出许仙的 class_id 是 1
select name from student where class_id = 1;

下面来看子查询的形式

select *
from student
where class_id = (select class_id from student where name = '许仙')
  -- 也可以对子查询的整体加上再加上过滤条件
  and name != '许仙';

要注意的是外层条件的列，与同层查询条件的列必须要匹配

💎2.4.2 多行子查询

需求：获取语文和英文的成绩信息

如果不用子查询，还是需要两步进行，获取课程的 id 之后，再根据 id 去查找成绩信息

-- 获取课程 id
select course_id
from course
where name = '语文'
   or name = '英文';

select *
from score
where course_id = 4
   or course_id = 6;

由于这里查询到的是多行的信息，所以在进行子查询的时候需要使用 IN（）来判断

select *
from score
where course_id in (select course_id
                    from course
                    where name = '语文'
                       or name = '英文');

💎2.4.3 多列子查询

需求：查询重复的分数

按照同一个学生，同一门课程，同样的成绩这三个列同时去分组，然后分组之后在 having字句中用count(*)判断分组中的记录数

select student_id, course_id, score
from score
group by student_id, course_id, score;

-- 加上having过滤条件
select student_id, course_id, score,count(*)
from score
group by student_id, course_id, score having count(*) > 1;

多列分组查询

select *
from score
where (student_id, course_id, score) in (select student_id, course_id, score
                                         from score
                                         group by student_id, course_id, score
                                         having count(*) > 1);

外层查询中的条件字段和内层查询中的结果对比，完全符合条件才可以

💎2.5 EXISTS关键字

语法：select * from 表名 where exists (select * from 表名)；

exists 后面括号中的查询语句，如果有结果返回就执行外层查询，如果返回的是一个空结果集，就不执行外层的查询

select *
from course
where exists(select * from course where course_id = 100);

select *
from course
where exists(select null);

对于上面的SQL语句，虽然exists中返回的是 null ，但不是空结果集，所以还会执行外层查询

💎2.6 临时表查询

在 from 子句中使用子查询，就是把一个子查询当做一个临时表来使用，下面还是通过一个例子来介绍

需求：查询所有比"中文系2019级3班"平均分高的成绩信息

首先获取 '' 中文系2019级3班"的平均分，将其看做临时表

-- 获取平均分作为临时表
select avg(sc.score)
from score sc,
     student st,
     class c
where c.class_id = st.class_id
and st.student_id = sc.student_id
and c.name = '中文系2019级3班';

接下来用表中的真实成绩和临时表比较

-- 用表中的真实成绩和临时表比较
select *from score sc,
   (select avg(sc.score) score -- 这里要起别名
from score sc,
     student st,
     class c
where c.class_id = st.class_id
and st.student_id = sc.student_id
and c.name = '中文系2019级3班') tmp
where sc.score > tmp.score;

💎3. 合并查询

合并查询就是合并多个查询结果到一个结果集中

需求：查找score_id < 3 或者 score > 90的同学

-- 查找score_id < 3 或者 score > 90的同学
use test3;
select *
from score
where score_id < 3;
select *
from score
where score > 90;

-- 两次查询结果联合起来
select *
from score
where score_id < 3
union
select *
from score
where score > 90;

在单表中用 or 更加简洁，在多表中，就没有办法用 or ，如果最终的查询结果是从多个表中获取到的，必须要用 union 来进行合并

下面来看两张表的

-- 复制表结构
create table student2 like student;
insert into student2(student_id, sn, name, mail, class_id)
values (1, 2022, '张三', null, 2),
       (2, 2023, '李四', null, 1);
select * from student;
select * from student2;

-- 两张表数据在一个数据表中显示
select * from student union
select * from student2;

需要注意的是，合并显示的两张表的列名要匹配一致

union 和 union all 的区别：

union 会自动去除合并结果中的重复行

union all 则会保留所有结果集中的所有行，包括重复的行