MySql笔记

一、查询

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）

1.2.2.union 和 union all 的区别¹

• union
  • 取交集，去重，相当于distinct
  • 结果排序
• union all
  • 取并集
  • 结果不排序
  • 在没有去重的前提下，使用 union all 的执行效率要比 union 高

PS：

• union 内部的 select 语句（并非表）必须拥有相同数量和相同顺序的列，且列也必须拥有相似的数据类型²。

• 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 排序条件可以是单个字段、多个字段、表达式、函数、别名、以及以上的组合等。³
排序字段中包含null值时，null值不参与排序，而会集中显示在结果的开头或末尾，如下图：³

排序查询时查询条件要和排序条件的序列最好一一对应，增加可读性。

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后可以跟聚合函数。²
where、group by、having后不能使用别名，但order by后可以使用别名。²

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)

---

1. 参考链接：SQL UNION 和 UNION ALL 区别 ↩︎

2. 参考链接：MySQL UNION 操作符 ↩︎ ↩︎ ↩︎

3. 参考链接：SQL查询从0到1，怎么用Order by排序数据？ ↩︎ ↩︎