MySQL 基础知识（六）之数据查询（二）

6 数值型函数

7 字符串函数

8 流程控制函数

9 聚合函数

10 分组查询 (group by)

11 分组过滤 (having)

12 限定查询 (limit)

13 多表查询

13.1 连接条件关键词 (on、using)

13.2 连接算法

13.3 交叉连接 (cross join)

13.4 内连接 (inner join)

13.5 外连接 (left join、right join)

14 子查询

14.1 select 子查询（只需了解）

14.2 from 子查询

14.3 where 子查询

15 查询顺序总结

6 数值型函数

数值型函数	描述
abs()	求绝对值
sqrt()	求二次方根
mod(x,y)	求 x 除以 y 的余数
pi()	返回圆周率
ceil() 或 ceiling()	两个函数功能相同，都是向上取整
floor()	向下取整，返回值为 bigint 类型
rand()	生成一个 0 ~ 1 的随机数，传入相同的参数，得到的随机数也相同
round(x,y)	对 x 进行四舍五入，y 表示保留几位小数
truncate(x,y)	返回 x 截断后有 y 位小数后的结果
pow(x,y) 或power(x,y)	求 x 的 y 次方

# 求 -3 的绝对值、4 的二次方根、11 / 3 的余数、圆周率
select abs(-3), sqrt(4), mod(11,3), pi();

# 对 3.49 向上取整、3.49 向下取整、生成一个随机数
select ceil(3.49), floor(3.49), rand();

# 对 3.415 保留两位小数、返回 3.415 截断后有两位小数的结果、求 2 的三次方
select round(3.415,2), truncate(3.415,2), pow(2,3);

7 字符串函数

字符串函数	描述
length(s)	返回字符串 s 的长度
concat(s1,s2,...,sn)	拼接字符串
insert(s,idx,len,replacestr)	替换字符串，将字符串 s 从第 idx 位置开始，len 个字符长的子串替换为字符串 replacestr，字符串下标从 1 开始的
lower(s) 或 lcase(s)	将字符串 s 中的字母都转换为小写
upper(s) 或 ucase(s)	将字符串 s 中的字母都转换为大写
left(s,n)	从字符串 s 左侧开始截取，截取 n 位字符
right(s,n)	从字符串 s 右侧开始截取，截取 n 位字符
lpad(s,len,pad)	从字符串 s 左侧开始填充字符串 pad，直到字符串 s 的长度为 len
rpad(s,len,pad)	从字符串 s 右侧开始填充字符串 pad，直到字符串 s 的长度为 len
trim(s)	去掉字符串 s 左右两侧的空格
ltrim(s)	去掉字符串 s 左侧的空格
rtrim(s)	去掉字符串 s 右侧的空格
repeat(s,n)	返回字符串 s 重复 n 次后的结果
space(n)	返回 n 个空格
strcmp(s1,s2)	比较字符串 s1 和 s2 的 ASCII 值大小
replace(s,a,b)	用字符串 b 替换字符串 s 中的所有子串 a
substr(s,idx,len) 或 substring(s,idx,len)	截取字符串，从字符串的索引 idx 开始，截取 len 位字符
reverse(s)	字符串反转
NULLIF(s1,s2)	比较字符串 s1 和 s2，如果两个字符串相等，则返回 NULL，否则返回 s1
field(s,s1,s2,...,sn)	返回字符串 s 在字符串列表中第一次出现的位置，下标从 1 开始
find_in_set(s1,s2)	返回字符串 s1 在字符串 s2 中第一次出现的位置。其中，字符串 s2是一个以逗号分隔的字符串

# 求字符串 'MySQL' 的长度，拼接 'My' 、'SQL'和'数据库'，替换 'Oracle数据库' 为 'MySQL数据库'
select length('MySQL'), concat('My','SQL','数据库'), insert('Oracle数据库',1,5,'MySQL');

# 将字符串 'MySQL' 转为小写、大写，从 'MySQL数据库' 左侧截取 5 位字符、右侧截取 3 位字符
select lower('MySQL'), ucase('MySQL'), left('MySQL数据库',5), right('MySQL数据库',3);

# 从字符串 '情人节' 左侧开始填充字符串 '财神节'，直到字符串 '情人节' 的长度为 11;
# 从字符串 '情人节' 右侧开始填充字符串 '财神节'，直到字符串 '情人节' 的长度为 11;
select lpad('情人节',11,'财神节'), rpad('情人节',11,'财神节');

# 去掉 '   MySQL数据库   ' 两侧、左侧、右侧的空格
select trim('   MySQL数据库   '), ltrim('   MySQL数据库   '), rtrim('   MySQL数据库   ');

# 返回 '财神节' 重复 8 次的结果，返回 3 个空格，比较  '情人节' 和 '财神节' ASCII 值大小
select repeat('财神节',8), space(3), strcmp('情人节','财神节');

# 用 '财神节' 替换 '情人节情人节情人节' 中的子串 '情人节'
# 从 'MySQL数据库' 截取出 'MySQL'
select replace('情人节情人节情人节','情人节','财神节'), substr('MySQL数据库',1,5);

# 反转字符串 'MySQL数据库'，判断 c 和 'MySQL数据库' 是否相等
select reverse('MySQL数据库'), nullif('MySQL数据库','Oracle数据库');

# 返回字符串 'ab' 在字符串列表 'abc','a','b','ab' 中第一次出现的位置
# 返回字符串 'ab' 在字符串 'abc,a,b,ab' 中第一次出现的位置
select field('ab','abc','a','b','ab'), find_in_set('ab','abc,a,b,ab');

8 流程控制函数

流程控制函数	描述
if(condition,value1,value2)	如果 condition 条件为true，返回 value1，否则返回 value2
IFNULL(value1,value2)	如果 value1 不为 NULL，返回 value1，否则返回 value2

select if(1>2,'真','假'),if(1,'真','假'),if('1','真','假'),if(1<2,'真','假');
select ifnull(null,'为NULL'),ifnull('不为NULL',NULL);

9 聚合函数

聚合函数会自动忽略 NULL，若该行有一个列值不为 NULL，则这行数据有效
在 where 子句中不能使用聚合函数

聚合函数	说明
count()	求总行数
sum()	求单列中所有行的总和
avg()	求单列中所有行的平均值
max()	求单列中所有行的最大值
min()	求单列中所有行的最小值

#计算表 goods 总行数，weight 总和、平均 netprice，最大 saleprice，最小 saleprice
select count(*), sum(weight), avg(netprice), max(saleprice), min(saleprice) from goods;

10 分组查询 (group by)

在一条 select 语句中，如果有 group by 语句，select 后面只能是：参加分组的字段、聚合函数

# 按照商品名 name 进行分组，计算每种商品的重量 weight 
select name, sum(weight) from goods group by name;

11 分组过滤 (having)

使用 having 可以对分组后的数据进行过滤
having 不能单独使用，必须和 group by 联合使用
having 不能代替 where，优先使用 where，where 实在完成不了，再选择 having

# 按照商品名 name 进行分组，计算每种商品的重量 weight，选择其中总重量 > 40 的
select name, sum(weight) from goods group by name having sum(weight) > 40;

12 限定查询 (limit)

limit offset_start,row_count，限定查询的起始行 (offset_start) 和总行数 (row_count)，起始下标从 0 开始
offset_start 默认是 0，可以省略，表示从第一行开始查询

关键字顺序

select

...

from

...

where

...

group by

...

having

...

order by

...

limit

...

以上语句执行顺序:

from
where
group by
having
select
order by
limit

# 查询表 goods 第 3 行到第 5 行的数据
select * from goods limit 2,3;

# 查询表 goods 前 3 行的数据
select * from goods limit 3;

13 多表查询

sales 表

drop table if exists sales;
create table sales (
id int primary key auto_increment,
name varchar(20),
saleprice float(7,2)
)charset=utf8;

insert into sales(id, name, saleprice) values
(1, '香蕉', 3.8),
(3, '苹果', 7.5),
(4, '橘子', 4.5),
(7, '葡萄', 4.7);

交叉连接：cross join (,)
内连接：(inner) join
外连接：left (outer) join、right (outer) join、union

MySQL join 语法官方文档：

MySQL 5.7 Reference Manual / ... / JOIN Clausehttps://dev.mysql.com/doc/refman/5.7/en/join.html

13.1 连接条件关键词 (on、using)

执行顺序：

通过 on | using 连接多张表，再通过 where 进行筛选

select * from A join B on A.id=B.id;
# 等价于
select * from A join B using(id);

# 执行顺序
from -> on|using -> where -> group by -> having -> select -> order by -> limit

13.2 连接算法

参考文档：

MySQL 连接查询超全详解https://learnku.com/articles/46944

Simple Nested Loop Join (SNLJ)

两张表做笛卡尔积进行扫描，比较费时，MySQL 不会选用该连接算法，时间复杂度 O(n * n)

Block Nested Loop Join (BNLJ)

没有索引，MySQL使用该算法。对外层循环的结果集进行分块，减少内层循环的次数，时间复杂度 O(n / m * n) ，其中 m 为分片数。

Index Nested Loop Join (INLJ)

有索引，MySQL使用该算法。用小结果集驱动大结果集，将筛选结果小的表首先连接，再去连接结果集比较大的表，即用小的表的取连接大的表

补充 left join | right join 连接算法，瞎编的伪代码，具体可以看官方文档：

MySQL 5.7 Reference Manual / ... / Nested Join Optimizationhttps://dev.mysql.com/doc/refman/5.7/en/nested-join-optimization.html

注：MySQL 8.0 版本提供了 hash join 连接算法，其他版本仍是 nested loop join 连接算法，尽量少用 join，不同时连接三张表

13.3 交叉连接 (cross join)

A cross join B 把表 A 和表 B 的数据进行一个 N * M 的组合，即笛卡尔积。
逗号 (,) 相当于 cross join，但逗号 (,) 的优先级低于 xxx join，不建议使用逗号进行多表连接（可读性差，易出错）

MySQL 中逗号 (,) 的优先级低于 xxx joinhttps://blog.csdn.net/zjs246813/article/details/135706189

# 查询 表 goods 和 sales 通过 cross join 连接后的结果
select * from goods cross join sales;

# 查询 表 goods 和 sales 通过 逗号 (,) 连接后的结果
select * from goods, sales;

上述代码中，表 goods 有 7 行数据，sales 有 4 行数据，通过 cross join 连接的后的表有 7 * 4 行数据

13.4 内连接 (inner join)

A (inner) join B on A.id = B.id，内连接产生的结果集是表 A 和 B 的交集，即符合连接条件的结果集，其中 inner 可省略，在 MySQL 中 join 默认是 inner join

如果不加连接条件，在 MySQL 中，join、inner join 等同于 cross join，根据本文 11.2 连接算法，没有连接条件则结果集是笛卡尔积

# 通过 goos.name 和 sales.name 内连接两张表，可以对表名使用别名
select * from goods g join sales s on g.name = s.name;

13.5 外连接 (left join、right join)

A left (outer) join B：以左表 A 为主表，outer 可以省略，大致连接步骤看本文 11.2 连接算法中的 left join | right join 连接算法

A right (outer) join B：以右表 B 为主表，outer 可以省略

union：拼接两张表的查询结果，两张表的查询结果的列数需要相同并且每列的数据类型也要相同，列名可以不同。union 默认去除重复的行，union all 不去除重复行。

# 左连接，例子不是很好
select * from goods g left join sales s on g.name = s.name;

# 右连接
select * from goods g right join sales s on g.name = s.name;

# 如果不去除重复行，将 union 改为 union all
select id, name,netprice from goods
union
select id,name,saleprice from sales;

14 子查询

select 语句中嵌套 select 语句，嵌套的 select 语句被称为子查询，不建议使用子查询（需要创建临时表，用完后还要删除临时表）

子查询可以用在哪里：

select

... (select)

from

... (select)

where

... (select)

14.1 select 子查询（只需了解）

# 查询表 goods 和 sales 相同商品名的数据，并用 goods 中的 id、name 和 sales 中的 name 显示
select g.id, g.name goodname, (select g.name from sales s where g.name = s.name)  as  salename
from goods g;

14.2 from 子查询

把内层的查询结果当成临时表，供外层 SQL 再次查询

# 查询 表 goods 和 sales 相同商品 id 的数据
# 并用 goods 中的 id、name 和 临时表中 saleprice 显示（实际 SQL 不是这样写的）
select g.id, g.name, s.saleprice
from goods g, (select id, saleprice from sales) s
where g.id = s.id;

14.3 where 子查询

where 子查询把 select 查询结果当作一个列表项，结合 in、exists、any、all 等关键字使用。

# 查询 goods表的 id 出现在 sales 表中的数据
select * from goods where id in (select id from sales);

all 表示所有，需要结合 =、>、<、>=、<=、!=、>< 来使用

条件	描述
c > all(集合)	筛选 c 列中大于集合中的最大值的值
c >= all(集合)	筛选 c 列中大于或等于集合中的最大值的值
c < all(集合)	筛选 c 列中小于集合中的最小值的值
c <= all(集合)	筛选 c 列中小于或等于集合中的最小值的值
c <> all(集合)	筛选 c 列中不和集合中的任何值相等的值，可用于字符串
c != all(集合)	筛选 c 列中不和集合中的任何值相等的值，可用于字符串
c = all(集合)	筛选 c 列中和集合所有值都相等的值，可用于字符串

select * from goods where name != all(select name from goods where name = '苹果');

any 表示任意一个，需要结合 =、>、<、>=、<=、!=、>< 来使用

条件	描述
c > any(集合)	筛选 c 列中大于集合中的最小值的值
c >= any(集合)	筛选 c 列中大于或等于集合中的最小值的值
c < any(集合)	筛选 c 列中小于集合中的最大值的值
c <= any(集合)	筛选 c 列中小于或等于集合中的最大值的值
c <> any(集合)	筛选 c 列中不等于集合中的所有值的值，可用于字符串 c = any(集合) 表示 c 列中的值等于集合中的一个或多个值就满足筛选条件，即 c = a1 \| c = a2 c <> any(集合) 是 c = any(集合) 的逆否命题，即 c != a1 & c != a2
c != any(集合)	筛选 c 列中不等于集合中的所有值的值，可用于字符串
c = any(集合)	筛选 c 列中只要和集合中一个值相等的值，可用于字符串

select * from goods where name != any(select name from goods where name = '苹果');

exists(子查询) 用来判断子查询是否为空，不为空，则返回 true；为空，则返回 false

select id, name, netprice from goods where exists(select 1);

15 查询顺序总结

关键字顺序：

select

...

distinct

...

from

...

join

...

on

...

where

...

group by

...

having

...

order by

...

limit

...

以上语句执行顺序:

from
join
on
where
group by
having
select
distinct
order by
limit

注：在MySQL中，group by、order by、having 中可以使用别名，Oracle 中不可以。

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses

来自官方文档：

MySQL 8.0 Reference Manual / ... / Problems with Column Aliaseshttps://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html