一、查询
1.基础查询
1.1.select cols 比 select * 效率高
# 方法一,运行效率高
select cols from table_name;
# 方法二,运行效率低
select * from table_name;
因为需要将*转化为每一个列名
1.2.去重
# 方法一,distinct
select distinct cols from table_name;
# 方法二,group by
select cols from table_name group by cols;
# 方法三,union
select cols from table_name;
union
select cols from table_name;
# 统计序列去重后的总数
count(distinct col)
1.2.1.distinct 和 group by 相同与不同
- 相同
都是对后面所有的字段均起作用,即去重是查询的所有字段完全重复的数据,而不是只对 distinct / group by 后面连接的单个字段重复的数据。 - 不同
- distinct
- distinct 只能放在查询字段的最前面,不能放在查询字段的中间或者后面
- 要查询多个字段,但只针对一个字段去重,是无法使用 distinct 去重实现的
- group by
- 一般与聚类函数使用(如count()/sum()等),也可单独使用
- 查询的字段与 group by 后面分组的字段没有限制。(在oracle中使用group by时,查询的字段必须是group by 分组的字段和聚类函数。如select name,sex from tb_students group by name这个sql)
- distinct
1.2.2.union 和 union all 的区别1
- union
- 取交集,去重,相当于distinct
- 结果排序
- union all
- 取并集
- 结果不排序
- 在没有去重的前提下,使用 union all 的执行效率要比 union 高
PS:
- union 内部的 select 语句(并非表)必须拥有相同数量和相同顺序的列,且列也必须拥有相似的数据类型2。
- union 结果集中的列名总是等于 union 中第一个 select 语句中的列名。
1.3.limit
# 选取 n 行
select col from table_name limit 0,n; # 运行效率高
select col from table_name limit n offset 0; # 运行效率中,offset表示位置偏移量(跳过的数量)
select col from table_name limit n; # 运行效率低
select col from table_name where id <= n;
# 查询第 n 到 m 行
select col from table_name limit m-n offset n;
select col from table_name offset n rows fetch next m-n rows only;
PS: limit 常与 order by 并用
select col from table_name order by id desc limit 0,n; # 默认升序 asc
limit语句的查询时间与起始记录(offset)的位置成正比。
limit优化
1.4.列重命名 as
select col1 as col1_alias ,col2 as col2_alias from table_name;
2.条件查询
sql内部的执行流程:
from join > where > group by > avg/sum 等聚合函数 > having > select > order by >limit
2.1.常用关键字
查询条件 | 谓词 |
---|---|
比较运算符 | =,>,<,>=,<=,<=>,<> 或 !=,not + 比较运算符 |
逻辑谓词 | and,or |
字符匹配 | like,not like |
空值 | is null,is not null |
确定集合 | in,not in |
确定范围 | between … and … ,not between … and … |
谓词 | exists,all,some(any),unique |
- <=>(不同于 = 运算符),当比较的的两个值相等或者都为 null 时返回 true。null 值处理请详见 null 值处理 。
- 相较于运算符,between(闭区间)可靠性和效率高一些。
- like 常与 _ 、%、[ ]、^ 并用:
- _:匹配任意一个字符
- %:匹配0个或多个字符
- [ ]:匹配[ ]中的任意一个字符(若要比较的字符是连续的,则可以用连字符“-”表 达 )
- [ ^ ]:不匹配[ ]中的任意一个字符
- 模糊匹配 也可用 regexp 正则匹配
# 找出除姓张、唐之外的学生信息
select cols from student where name like '[^张唐]%'
# 查询姓名中第二个字为大或小的学生信息
seletc cols from student where name like '_[小大]%'
# 匹配日期 date 列是 2024-08 月内的数据[^3]
# 方法一,比较运算符
where date >= '2024-08-01' and date <= '2024-08-31'
# 方法二,between ... and ...
where date between '2024-08-01' and '2024-08-31'
# 方法三,like
where date like "2024-08%"
# 方法四,year、month函数
where year(date)='2024' and month(date) = '08'
# 方法五,date_format函数
where date_format(date, '%Y-%m') = '2024-08'
where date_format(date, '%y%m') = '2408'
# 方法六,substring
where substring(date,1,7) = '2024-08'
# 方法七,substring_index
where substring_index(date,'-',2) = '2024-08'
# 方法八,mid
where mid(date,1,7) = '2024-08'
# 方法九,left
where left(date,7) = '2024-08'
2.2.order by
select col1, col2, col3 from table_name order by col2 [asc | desc], col3 [asc | desc] # 默认升序 asc
PS:
order by 排序条件可以是单个字段、多个字段、表达式、函数、别名、以及以上的组合等。3
排序字段中包含null值时,null值不参与排序,而会集中显示在结果的开头或末尾,如下图:3
排序查询时查询条件要和排序条件的序列最好一一对应,增加可读性。
2.group by … having …
select col1, col2, col3 from table_name
where 分组前筛选
group by 分组条件 having 分组后筛选
order by col2 [asc | desc], col3 [asc | desc];
where和group by后面不能跟聚合函数,having和order by后可以跟聚合函数。2
where、group by、having后不能使用别名,但order by后可以使用别名。2
2.null 处理函数
- null 是大于所有数值型还是小于所有数值型是由 DBMS 决定的,在 MySQL 中,null 值与任何其它值的比较(即使是 null)永远返回 null,即 null = null返回 null。严谨起见,有空值时需加上 is not null 的条件。
- = null 或 != null [×]
- is null 或 is not null [√ ]
select cols from tabel_name where num_col is not null and num_col > n;
函数名 | 参数 |
---|---|
ifnull (a,b,c) | null 值处理。如果 a is not null,则返回 a;a is null,则返回 b;如果 b is null,则返回 c;如果 a b c 都为 null ,则返回为 null。 |
coalesce(a,b,c) | 同 ifnull (a,b,c) |
select ifnull(col,'列名') from tabel_name;
select coalesce(col1,0)+coalesce(col2,0) from tabel_name;
3.高级查询
4.多表查询
附录:常用函数
函数 | 用法 |
---|---|
ifnull (a,b,c) | null 值处理。如果 a is not null,则返回 a;a is null,则返回 b;如果 b is null,则返回 c;如果 a b c 都为 null ,则返回为 null。 |
coalesce(a,b,c) | 同 ifnull (a,b,c) |
参考链接:MySQL UNION 操作符 ↩︎ ↩︎ ↩︎