MySQL04-数据操作之单表查询&别名

1. 别名

别名就是一个字符串标识，使用别名可以让数据展示更加直观。

1.1 表的别名

在查询操作，特别是多表查询操作时，假若表名很长使用起来就不太方便，此时可为表取一个別名，用该别名来代替表的名称。其语法格式如下：

表名 [as] 表的别名 
# 其中，as 可以省略，如：
表名 表的别名

# 例如，将 student 改为 stu 查询整表
select * from student as stu;

1.2 字段的别名

为字段取别名的语法格式如下：

字段名 [as] 字段的别名
# 或
字段名 字段的别名

# 例如，将 student 中的 name 取别名为“姓名”，age 取别名为 “年龄”，查询整表
select name as '姓名',age as '年龄'
from student;

注意：

1. 别名字符串一般可以不加引号，但如果别名内有空格，则必须用单引号或双引号引起来；
2. 字段名与别名之间也可以不用 as ，而直接用空格连接，如：select name 姓名,age 年龄 from student; ；
3. 在 select 语句执行中，where 子句执行顺序在前，列别名生效在后，因此在 where 子句中不能用别名来判断，必须用列的原名判断，否则运行会报错；

2. 简单查询-DQL

select 语句用于从表中选取数据，并将结果存储在结果表中，这个结果表也常常称为结果集。

2.1 单字段/多字段查询

select 字段1,字段2,字段3...
from 表名;

# 示例，从 student 表中查询 name、age 字段
select name,age
from student;

# 示例，用通配符 * 从 student 表中查询所有字段
select * from 表名;

说明：

1. 如果查询的是表中的所有字段，可以使用通配符 * 代替字段名，查询语句可以写成：select * from 表名;，表示查询该表的所有字段内容，即按照创建表时的顺序排列展示所有字段，在实际应用中不建议使用 select * 语句，这会降低数据查询效率；

2.2 去除重复记录查询

去除重复记录查询即将查询结果中的重复值合并，通常用于只需知道整体分布情况，不需精确到个人的情况下，比如查询学生家庭住址分布情况、学生年龄分布情况等。

select distinct 字段1,字段2,字段3...
from 表名;

注意：

1. 在 select 中除了书写列名，还可以书写字符串，这样书写可以用于标记，如查询日期标记字段： select stuid, stuname,'2023-03-20' from student;，可以在查询结果中 stuid 和 stuname 字段后加一个 2023-03-20 字段，用于标记日期。

3. 条件查询-DQL

在简单查询的基础上加上 where 子句，即可实现条件查询，其基本语法如下：

select 字段名1,字段名2... 
from 表名 
[where 条件表达式];

根据 where 子句的条件表达式不同，条件查询又可分为以下几种不同的类型

3.1 关系运算符查询

使用关系运算符构成 where 子句的条件表达式，可完成一类条件查询，MySQL中常用的关系运算符有：

关系运算符	说明
= 或 <=>	等于
<> 或 !=	不等于
<	小于
!<	不小于
<=	小于等于
>	大于
!>	不大于
>=	大于等于

例如，查询 student 表中年龄等于或大于17的学生的信息：

select *
from student
where age >= 17;

注意：

1. = 和 <=> 几乎完全相同，区别在于 <=> 可以用来对 NULL 进行判断，被比较的两者都为 NULL 时，返回值为 1，即逻辑真；
2. <> 和 != 是完全等价的，都是进行不等于判断；

3.2 between and 关键字查询

between and 关键字用于判断某个字段的值是否在指定的范围之内，是SQL中专门关于闭区间判断的运算符，其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 between 值1 and 值2;

# 例如，查询 student 表中字段 score 在 60 - 85 之间的学生的信息
select *
from student
where score between 60 and 85;

# 同时，between and 关键字 也可用关系运算符表示，即上述示例可表示为
select *
from student
where score >= 60 and score <= 85;

注意：

1. between 之后跟的是范围的最小值，and 之后跟的是范围的最大值，两者顺序不能颠倒；
2. not between and 是 between and 运算的反运算，其格式为在 between and 运算前直接加 not，如：

# 查询 student 表中字段 score 在 60 - 85 以外的学生的信息
select *
from student
where score not between 60 and 85;

3.3 like 关键字和通配符查询

like 关键字用于判断两个字符串是否相匹配，可直接匹配字符串，也可配合通配符 % 或 _ 进行模糊查询，其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 like 通配符表示的值;

# like 直接匹配字符串查询，查询 student 表中字段 name 为 陈 的学生的信息
select *
from student
where name like '陈';

# like 配合通配符进行模糊查询，查询 student 表中字段 name 姓为 陈 的学生的信息
select *
from student
where name like '陈%';

注意：

1. not like 是 like 运算的反运算，其格式为在 like 运算前直接加 not，如：

# 查询 student 表中字段 name 姓不为 陈 的学生的信息
select *
from student
where name not like '陈%';

通配符的转义

转义即表示转义字符原来的语义，一个转义字符的目的是开始一个字符序列，使得转义字符开头的该字符序列具有不同于该字符序列单独出现时的语义。

select *
from 表名
where 条件表达式
escape '/';

# 示例，查询名字里带有_的人
select *
from student
where name like '/_%'
escape '/';

如上所述，原先 _ 在MySQL中表示通配符，表示匹配一个字符，当要查询包含 _ 的字段时，就需要用 / 对 _ 进行转义，取消它的通配符资格，也即 /_ 等同于字符 _ ；escape 关键字的主要作用就是指定任意一个字符替代 / 的作用。

3.4 and 关键字查询

and 关键字，也即逻辑与，表示并且，用来关联两个或两个以上的条件，用 and 连接的条件需同时成立，其基本语法如下：

select 字段1,字段2...
from 表名
where 条件表达式1 and 条件表达式2;

# 例如，查询 student 表中 age 大于 15 且 sex 为 男 的学生信息
select *
from student
where age > 15 and sex = '男';

注意：

1. 因为 and 关键字表示并且，在同一记录（同一行）中，不存在一个字段是 A 并且同时是 B 的情况（比如字段 sex，不能既是男又是女），所以用 and 关键字连接的条件字段名应不相同，就像上面例子中的字段 age 和 sex，一定得是两个不同的字段名，如果一个查询的两个条件是属于同一字段的，那就不应该是逻辑与，而是逻辑或，即应该使用 or 关键字查询，这也是判断使用 and 或 or 关键字的方法；
2. and 关键字也可用符号 && 表示，比如上面的例子可以写成 select * from student where age > 15 && sex = '男';，但是并不常用，日常使用写作 and 为好。

3.5 or 关键字查询

or 关键字，也即逻辑或，表示或者，用来关联两个或两个以上的条件，用 or 连接的条件只要满足其中任意一个即可，其基本语法如下：

select 字段1,字段2...
from 表名
where 条件表达式1 or 条件表达式2;

# 例如，查询 student 表中字段 addr 为新疆或西藏的学生的信息
select *
from student
where addr = 新疆 or addr = 西藏;

注意：

1. AND(与) 和 OR(或) 运算是有优先级的，AND 的优先级要高于 OR；
2. 在同时有 AND 和 OR 的运算中，要使 OR 运算优先运行，可以用小括号 () 把 OR 连接的条件括起来，小括号的优先级是最高的；
3. or 关键字也可用符号 || 表示，比如上面的例子可以写成 select * from student where addr = 新疆 || addr = 西藏;，但是并不常用，日常使用写作 or 为好。

3.6 in 关键字查询

in 关键字用于判断某个字段的值是否在指定集合中（两个或两个以上同一字段的条件时），其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 in (值1,值2...);

# 例如，查询 student 表中字段 addr 为新疆、西藏的学生的信息
select *
from student
where addr in ('新疆','西藏');

注意：

1. in 关键字和 and 关键字大体上是一样的，不同在于，当多个条件所在的字段不同时，只能用 and 关键字，当多个条件所在的字段相同时，使用 in 关键字写法更简洁；
2. not in 为不包含运算，是 in 运算的反运算，其格式为在 in 运算前直接加 not，如：

# 查询 student 表中字段 addr 为新疆、西藏之外的学生的信息
select *
from student
where addr not in ('新疆','西藏');

3.7 null 值查询

在MySQL中，使用 IS NULL 关键字来判断字段的值是否为空值 NULL，其基本语法如下：

select 字段1,字段2...
from 表名
where 字段名 is null;

# 例如，查询 student 表中字段 score 为 null 的学生的信息
select *
from student
where score is null;

注意：

1. NULL 值是数据库中一个非常特殊的值，叫空值，它不是数值中的 0，也不是字符中的空格或空字符串，NULL 是 a missing unknownvalue，即不存在、不确定的意思；
2. 要判断 NULL 值，必须有专门的比较运算符或函数，IS NULL 就是用于判断空值 NULL 是函数；
3. 因为 is null 是一种函数，所以 字段名 is null 等同于 is null (字段名) ，两者效果一样；
4. IS NULL 专门用来判断空值，不能用 = null 表示，而是等同于 <=> null ，仅能用 <=> 判断NULL，这也是 = 和 <=> 的区别；
5. is not null 是 is null 运算的反运算，其格式为在 is null 运算间直接加 not，如：

# 查询 student 表中字段 score 不为 null 的学生的信息
select *
from student
where score is not null;

4. 统计查询-DQL

4.1 聚合函数查询

聚合统计函数也叫组函数、聚簇函数，与数学函数、字符串函数等不同，聚合函数只作用于表中的某一字段（某一列），在默认情况下，聚合函数会把当前表中的数据当做一个组进行统计，聚合函数有以下几种类型：

count()：计数函数，统计表的行数或某个列下值的个数。
max()：最大值函数，求某列值中的最大值。
min()：最小值函数，求某列值中的最小值。
sum()：求和函数，对表的某列进行求和操作，显然这个列只能是数值类型列，否则报错。
avg()：平均值函数，对表的某列进行求平均值操作，显然这个列也只能是数值类型列，否则报错。

聚合函数的使用语法如下：

select 聚合函数(字段1,字段2,字段3...) from 表名;

# 使用 count() 函数统计 student 表中学生人数
select count(*) from student;
# 使用 max() 函数查询 student 表中最大年龄
select max(stuage) from student;
# 使用 min() 函数查询 student 表中最小年龄
select min(stuage) from student;
# 使用 sum() 函数查询 student 表中年龄和
select sum(stuage) from student;
# 使用 avg() 函数查询 student 表中年龄平均值
select avg(stuage) from student;

注意：

1. 聚合函数具有自动滤空的功能，若字段中某一个值为NULL，那么会自动将其过滤使其不参与运算；
2. 使用 count() 函数统计时，如果统计的字段没有空值，则可以写为 count(任一字段名) 或 count(*)，两种写法效果一样；

4.2 group by 分组查询

group by 子句会先将表中的数据进行分组，再进行查询等操作，可理解为将原来的表拆分成了几张小表，其基本语法如下：

select 字段1,字段2...
from 表名
group by 字段3,字段4...

# 例如，统计 student 表中男女生年龄平均值
select stusex 性别, avg(stuage) 平均年龄
from student
group by stusex;
# 即以 stusex 为分组依据，查询 student 表中 stusex 和 avg(stuage) 两个字段

注意：

1. group by 子句后可跟多个字段，即多级分组，在多级分组时，一级分组内有重复数据，二级分组才会有意义，以此类推，同时多个字段的顺序直接控制了数据源中数据的分组方式，即从前往后依次为一级、二级、三级......分组，这类似于 Excel 中的多级排序；

# 例如，统计 student 表中各地男女生人数
select addr 地址, stusex 性别, count(stuid) 人数
from student
group by addr,stusex;
# 这里先以学生住址分组，同时，在同一住址中都有男女生，再在住址分组的基础上以男女生分组

2. 在具体应用中，group by 子句常与聚合函数一起使用；
3. 在分组查询中，查询的字段（select 后跟着的字段）一般为聚合函数和分组字段，查询其他字段是没有意义的；
4. 一般在 select 语句中加入 group by 子句的分组依据列，以提高查询结果集的可读性；

4.3 having 统计限制

having 子句以来实现对分组统计的条件限制，与 where 子句的条件限制功能有些相似，但 having 子句是对 group by 分组后的结果进行过滤，只能出现在 group by 子句之后，其基本语法如下：

select 字段1,字段2...
from 表名
group by 字段3,字段4...
having 条件表达式

# 例如，统计 score 表中平均分高于 80 的学生的学号和平均分
select stuid 学号,avg(score) 平均分
from score
group by stuid
having 平均分 > 80;

注意：

1. select 语句的各子句的相对位置不能前后颠倒，group by 子句只能在 where 子句后面，having 子句只能在 group by 子句后面；
2. 在查询中，SQL的执行顺序为 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT；

where 子句和 having 子句的区别：

1. 使用位置不同，where 子句通常出现在 select 语句的最后面，having 子句通常出现在 group by 子句之后；
2. 执行时机不同，where 子句在 group by 子句之前对数据进行筛选，having 子句对 group by 子句之后得到的数据进行过滤；
3. 判断条件不同，where 子句的条件中不能包含聚合函数，having 子句中不能使用除了分组字段（即 group by 子句后跟着的字段）和聚合函数之外的其他字段；
4. 过滤对象不同，where 子句用于过滤从数据表中检索的行，它可以用于过滤行的任何条件，如等于、小于、大于等，而 having 子句用于过滤聚合函数的结果，如 SUM、AVG、COUNT 等，它通常用于过滤分组后的结果。
5. 对性能的影响不同，where 子句在过滤数据时，可以减少查询的数据量，从而提高查询的效率。而 having 子句则是在过滤分组后的结果时进行过滤，如果使用不当，可能会对查询的性能造成负面影响。从性能的角度来说，having 子句中如果使用了分组字段作为过滤条件，应该替换成 where 子句；因为 where 子句可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好；

5. 排序查询-DQL

通过 select 语句查询的默认查询结果集中，数据是按照数据记录的物理顺序来显示的，当默认的查询结果顺序无法满足需求时，可通过添加 order by 子句来控制查询结果的顺序。

5.1 order by 排序查询

order by 子句用来对查询结果集进行排序，排序依据即其后指定的字段，排序方式由 asc 或 desc 控制，其基本语法如下：

select 字段1,字段2...
from 表名
order by 字段3,字段4...

# 例如，查询 score 表中的学号及分数，并按分数高低降序排列
select stuid,score
from score
order by score desc;

注意：

1. order by 子句中，asc 为升序（数值从小到大），desc 为降序（数值从大到小），排序方式在缺省状态下默认为升序 asc；
2. 若 order by 子句后跟着多个字段，即指定了多个排序依据，产生多级排序，从前往后依次为一级、二级、三级......排序，同时，各个排序依据还能指定不同的排序方式，如 order by score desc,stuid asc，但要注意，在多级排序时，一级排序内有重复数据，二级排序才会有意义，以此类推；

# 例如，查询 score 表中的学号及分数，并按分数高低降序排列，如果分数相同，则按学号升序排序
select stuid, score
from score
order by score desc, stuid asc;

3. order by 子句后指定的排序依据除了能是字段名，还能是查询后的字段顺序值（即 select 语句后跟着的字段顺序，值为1,2,3...），如上述例子还可写为 select stuid,score from score order by 2 desc; ；
4. 若排序字段为空值，则该值为最小值，即在降序排序中空值显示在最后，在升序排序中显示在最前；
5. 当 order by 子句后的字段值为数字或字母时，即按照数字或字母的顺序进行排序，当字段值为中文汉字时，会根据对应字符的ASCII码排序，而不是汉字的首字母顺序，因此排序结果往往都是错误的，这时需要借助相应的函数才能实现想要的效果；

5.2 limit 分页查询

limit 子句常常应用于分页系统，控制在某一页中显示的数据记录数量，通常与 order by 子句一起使用，其基本语法如下：

select 字段1,字段2...
from 表名
limit 起始索引, 查询记录数;

# 例如，查询 score 表中的学号及分数，每页显示十名，查询第二页的数据
select stuid,score
from score
limit 1,10;

注意：

1. 起始索引即查询的起始数据的索引，起始索引从 0 开始，查询记录数即查询结果显示的行数，如 limit 10,5 ，即表示从数据表的第 11 行开始，一共查询显示 5 行，利用这个特性可以实现分页查询效果，分页的起始索引 = ( 查询页码 - 1 ) * 每页显示记录数，如，一页显示 10 行，要查询第 3 页的数据，起始索引应为 (3-1)*10=20，SQL语句写为 limit 20,10；
2. 当查询第一页数据，即起始行 a 为 0 时，起始索引可以省略，如查询第 1 页，显示 6 行，可以简写为 limit 6；
3. 当 limit a 中查询结果记录总数不足 a 行，则按实际结果集记录数量显示；
4. 分页查询的语句不同数据库各有不同，limit 仅是 MySQL 中的分页查询语句；
5. 当 limit 子句 和 offset 子句一起出现时，limit 子句决定显示行数，offset 子句决定显示起始点，如 limit 3 offset 6 表示从第 7 行开始，显示 3 行；

6. 基础查询的编写和运行顺序

基础查询的编写顺序和运行顺序是不同的，具体内容如下。

6.1 基础查询的编写顺序

基础查询的编写顺序为 select -> from -> where -> group by -> having -> order by -> limit

# 例如，查询score表中分数大于60，平均分大于80的学生的学号和平均分，以学号分组，按平均分倒序排列，从第四个开始显示，只显示五行
select stuid 学号,avg(score) 平均分
from score
where score > 60
group by stuid
having 平均分 > 80
order by 平均分 desc
limit 3,5;

6.2 基础查询的运行顺序

基础查询的编写顺序如下

from 数据表列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
select 查询字段列表
order by 排序字段列表
limit 分页参数