MySQL-高级查询

查询处理

排序（默认不是按主键排序的）

order by 字段1[，字段2] [asc|desc]

• 默认是升序排序
• 也可以指定 select 列表中列的序号进行排序
• 如果是多个字段，那么在上一个字段排序完的基础上排序下一个

限制数量

limit 行数（从第一行开始）
limit 开始行（从0开始），行数

• 可以使用 limit 解决分页问题 

去重

select distinct 字段1[,字段2] from 表名;

• 如果是多个字段，则表示查询这些字段不同时一样的数据 

组合查询

select 字段1[,字段2] from 表1
union
select 字段1[,字段2] from 表2；

• 每个查询必须包含相同数量的列
• 列必须拥有相似的数据类型
• union 自动去除了重复行，如果不想去除重复行，可以使用 union all

练习

函数

数值函数

abs(x)：返回 x 的绝对值

ceil(x)：向上取整，返回大于等于 x 的最小整数值

floor(x)：向下取整，返回小于等于 x 的最大整数值

round(x，y=0)：四舍五入，将 x 四舍五入 y 位小数，y 不传返回整数，y 为负数时，保留 x 值到小数点左边 y 位

truncate(x，y)：截断函数，返回被舍去至小数点后 y 位的数字 x，y 为负数时截断小数点左边 y 位

mod(x，y)：返回 x 除以 y 的余数

rand()：生成 0-1 的随机数

字符函数

concat(s1，s2, …. )：字符串连接，如果任何一个参数为 null，则返回值为 null

concat_ws(x，s1，s2,…)：指定分隔符的字符连接函数，x 是连接分隔符，如果分隔符为 null，则结果为 null

lower(str)：大写转小写

upper(str)：小写转大写

length(str)：字符串长度

Itrim(str)：删除字符串左侧空格

rtrim(str)：删除字符串右侧空格

trim(str)：删除字符串两侧空格

substr(str，n，len)：截取子字符串，字符串 str 从 n 的位置截取长度为 len 的字符串，如果 n 为负数，则子字符串的位置起始于字符串结尾的 n 个字符

left(str，n)：返回字符串 str 的最左边 n 个字符

right(str，n)：返回字符串 str 的最右边 n 个字符

replace(str，from_str，to_str)：替换函数，字符串 str 中所有的字符串 from_str 均被 to_str 替换，然后返回这个字符串

format (x，n)：将数字 x 格式化，并以四舍五入的方式保留小数点后 n 位，结果以字符串的形式返回。若 n 为 0，则返回结果不含小数部分

日期时间函数

curdate()/current_date()：获取当前日期，YYYY-MM-DD 格式

curtime()/current_time()：获取当前时间，HH:MM:SS 格式

week(date)：返回 date 为一年中的第几周

now()/sysdate()：获取当前日期和时间，YYYY-MM-DD HH:MM:SS 格式

date_add(date，interval expr type)：执行日期的加运算，date 是一个 datetime 或 date 值，指定起始时间。expr 是时间间隔。type 为关键词，如 YEAR, MONTH, DAY, WEEK, HOUR 等。

datediff(date1，date2)：计算两个日期之间的间隔天数

unix_timestamp(date)：返回 date 的 UNIX 时间戳

from_unixtime(unix)：返回 unix 时间戳的日期值

date_format(date，format)：日期格式化，按 format 格式化 date 值

str_to_date(date，format)：将字符串转换成 date 类型

聚合函数(分组函数)

avg(expression)：返回某列的平均值

sum(expression)：返回某列值的和

count(expression)：返回某列的行数

max(expression)：返回某列的最大值

min(expression)：返回某列的最小值

• 聚合函数会自动的忽略空值，不需要手动增加条件排除 NULL
• 聚合函数不能作为 where 子句后的限制条件

流程函数

if(value，t，f)：如果 value 为真返回 t，否则返回 f

ifnull(column，value)：如果 column 为空返回 value，否则返回 column

在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL，为了防止计算结果出现 NULL，建议先使用 ifnull 空值处理函数预先处理

练习

分组查询

创建分组

select 字段1[,字段2] from 表名 group by 字段1;

• 根据一个或多个字段对结果集进行分组，在分组的字段上可以使用 count、sum、avg 等函数
• 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行NULLL值，它们将分为一组
• group by 子句必须出现在 where 子句之后，order by 子句之前

过滤分组

select 字段1[,字段2] from 表名 group by 字段1 having 限制条件;

• having 必须和 group by 一起使用
• having 非常类似于 where。唯一的差别是 where 过滤行，而 having 过滤分组。having 和 where 的区别也可以理解为，where 是分组前过滤，having 是分组后过滤

练习

正则表达式

regexp 操作符后面跟的就是正则表达式，正则表达式的作用是匹配文本，将一个模式(正则表达式)与一个文本串进行比较。

select * from emp where ename like "%s%";select * from emp where ename regexp "s";

like 和 regexp 的区别：

• like：匹配整个列，如果被匹配的文本仅在列值中出现(没有配合其他通配符)，like将不会找到它
• regexp：在列值内进行匹配，如果被匹配的文本在列值中出现，regexp 将会找到它，相应的行将被返回

匹配单个实例

|：表示匹配其中之一，使用 | 从功能上类似 or

[ ]：匹配字符之一，[ ]是另一种形式的 or 语句。[123] 为 [1|2|3] 的缩写

[-]：匹配范围，使用 - 来定义一个范围。例如：[1-3]、[a-z]

ll：转义字符，多数正则表达式使用单个反斜杠作为转义字符，但MySQL 要求两个反斜杠(MySQL 自己解释一个，正则表达式库解释另一个)

匹配字符类：存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类

匹配多个实例

常用元字符

重复元字符（修饰前一个字符）

练习