Mysql的DQL操作
- DQL(Data Query Language):数据查询语言,对数据中的数据进行查询【读】
函数
1、字符串函数
- length
- 功能:用于计算字符串的字节个数
- 语法:length(字符串)
- 示例:
select length('abcdef'); -- 6
select length('中国'); -- 6
select length('abc中国'); -- 9
- char_length
- 功能:用于计算字符串的字符个数
- 语法:char_length(字符串)
- 示例
select char_length('abcdef'); -- 6
select char_length('中国'); -- 2
select char_length('abc中国'); -- 5
- lower
- 功能:用于将大写字母转换成小写字母
- 语法:lower(字符串)
- 示例
select lower('ABCdef'); -- abcdef
- upper
- 功能:用于将小写字母转换成大写字母
- 语法:upper(字符串)
- 示例
select upper('ABCdef'); -- ABCDEF
- concat
- 功能:用于实现字符串的拼接
- 语法:concat(字符串1,字符串2,……,字符串N)
- 示例
select concat('abc','DEF','ghI'); -- abcDEFghI
select concat('abc',',','DEF',',','ghI'); -- abc,DEF,ghI
select concat('99.99','%'); -- 99.99%
- concat_ws
- 功能:用于实现字符串的拼接,可以指定字符串之间的分隔符
- 语法:concat_ws(分隔符,字符串1,字符串2,……,字符串N)
- 示例
select concat_ws(',','abc','DEF','ghI'); -- abc,DEF,ghI
select concat_ws('abc','DEF','ghI'); -- abcDEFghI
select concat_ws('-','2023','04','27'); -- 2023-04-27
- substr
- 功能:用于实现字符串的裁剪
- 语法:substr(字符串, 开始位置, 截取长度) / substring(字符串, 开始位置, 截取长度)
- 示例
select substr('abcdefg',1,3); -- abc
select substr('abcdefg',-3,3); -- efg
- replace
- 功能:用于实现字符串的替换
- 语法:replace(字符串,替换谁,替换成什么)
- 示例
select replace('abcdefg','cde','CDE'); -- abCDEfg
- reverse
- 功能:实现字符串的反转
- 语法:reverse(字符串)
- 示例
select reverse('abcdefg'); -- gfedcba
- locate
- 功能:查找子串的位置或者是否包含子串
- 语法:locate(子串,字符串) ,不存在就返回0,存在就返回子串的开始位置
- 示例
select locate('abc','abcdefg'); -- 1
select locate('bc','abcdefg'); -- 2
- trim
- 功能:用于实现对字符串去首尾空格
- 语法:trim(字符串)
- 示例
select trim(' abcd efgh '); -- abcd efgh
- strcmp
- 功能:用于比较两个字符串的大小
- 语法:strcmp(str1, str2),0表示相等,1表示str1大于str2,-1表示str1小于str2
- 示例
select strcmp('abcdez','abcdfg'); -- -1
2、数值函数
- abs
- 功能:用于返回给定数值的绝对值
- 语法:abs(数值)
- 示例
select abs(-1); -- 1
select abs(1); -- 1
- ceil
- 功能:用于实现对数值进行向上取整
- 语法:ceil(数值)
- 示例
select ceil(3.14); -- 4
select ceil(3); -- 3
- floor
- 功能:用于实现对数值进行向下取整
- 语法:floor(数值)
- 示例
select floor(3.14); -- 3
select floor(3.99); -- 3
select floor(3); -- 3
- mod
- 功能:用于实现两个数值 A除以B运算 的余数
- 语法:mod(A, B)
- 示例
select mod(3,2); -- 3%2=1
select mod(9,3); -- 9%3=0
- rand: random:随机
- 功能:用于随机生成一个0 - 1 之间的小数
- 语法:rand() 、rand(N),如果给定了N,相当于随机数只生成一次,以后每次结果都相同
- 示例
select rand(); -- 0.14648645943634814 运行一次改变一次
select rand(1); -- 0.40540353712197724 每次运行都不会发生改变
- round
- 功能:用于实现四舍五入,指定小数点后保留几位
- 语法:round(数值,小数点后位数),不给定位数则取整
- 示例
select round(3.1415926); -- 3
select round(3.1415926,2); -- 3.14
select round(3.1415926,3); -- 3.142
- truncate
- 功能:用于直接截取小数点后的位数,指定小数点后保留几位,不四舍五入
- 语法:truncate(数值,小数点后位数)
- 示例
select truncate(3.1415926,2); -- 3.14
select truncate(3.1415926,3); -- 3.141
- pi
- 功能:用于生成圆周率
- 语法:pi()
- 示例
select pi(); -- 3.141593
- power
- 功能:用于计算数值指定的次方
- 语法:power(数值,次方)
- 示例
select power(2,10); -- 1024
- sqrt
- 功能:用于返回指定数值的平方根
- 语法:sqrt(数值)
- 示例
select sqrt(1024); -- 32
select sqrt(4); -- 2
- log
- 功能:返回指定以M为底的N的对数值
- 语法:log(底数,数值)
- 示例
select log(2,1024); -- 10
3、日期函数
-
now:现在
-
功能:获取当前的日期和时间
-
语法:now()
-
示例
-
select now(); -- 2023-04-27 17:14:29
-
curdate:现在日期
-
功能:获取当前的日期,不包含时间
-
语法:curdate()
-
示例
-
select curdate(); -- 2023-04-27
-
curtime
-
功能:获取当前的时间,不包含日期
-
语法:curtime
-
示例
-
select CURTIME(); -- 17:14:59
-
date
-
功能:从日期时间中获取日期
-
语法:date(日期时间)
-
示例
-
select date('2023-04-27 17:15:09'); -- 2023-04-27
-
time
-
功能:从日期时间中获取时间
-
语法:time(日期时间)
-
示例
-
select time('2023-04-27 17:15:09'); -- 17:15:09
-
year/quarter/month/day/hour/minute/second
-
功能:从日期时间中获取指定的元素
-
语法:year/quarter/month/day/hour/minute/second(日期时间)
-
示例
-
select year('2023-04-27 17:15:09'); -- 2023
select quarter('2023-04-27 17:15:09'); -- 2
select month('2023-04-27 17:15:09'); -- 4
select day('2023-04-27 17:15:09'); -- 27
select hour('2023-04-27 17:15:09'); -- 17
select month('2023-04-27 17:15:09'); -- 4
select second('2023-04-27 17:15:09'); -- 9
-
unix_timestamp
-
功能:将指定日期时间转换我时间戳
-
语法:unix_timestamp(日期时间)
-
示例
-
select unix_timestamp('2023-04-27 17:17:23'); -- 1682587043
-
from_unixtime
-
功能:将时间戳,转换成日期时间
-
语法:from_unixtime(时间戳)
-
示例
-
select from_unixtime(1682587043); -- 2023-04-27 17:17:23
-
date_format
-
功能:用于实现两个日期格式之间的转换
-
语法:date_format(日期时间, 目标格式)
-
示例
-
select date_format('2023-04-27 17:18:23','%Y-%M-%D %H:%m:%S'); -- 2023-April-27th 17:04:23
select date_format('2023-04-27 17:18:23','%Y-%m-%d %H-%m-%s'); -- 2023-04-27 17:04:23
-
date_add/date_sub
-
功能:用于实现日期的加减
-
语法:date_add/date_sub(日期,天数)
-
示例
-
select date_add('2023-04-27 17:18:23',interval 1 year ); -- 2024-04-27 17:18:23
select date_add('2023-04-27 17:18:23',interval 1 quarter ); -- 2023-07-27 17:18:23
select date_add('2023-04-27 17:18:23',interval 1 month ); -- 2023-05-27 17:18:23
select date_add('2023-04-27 17:18:23',interval 1 day ); -- 2023-04-28 17:18:23
select date_add('2023-04-27 17:18:23',interval 1 hour ); -- 2023-04-27 18:18:23
select date_add('2023-04-27 17:18:23',interval 1 minute ); -- 2023-04-27 17:19:23
select date_add('2023-04-27 17:18:23',interval 1 second ); -- 2023-04-27 17:18:24
select date_sub('2023-04-27 17:18:23',interval 1 year ); -- 2022-04-27 17:18:23
select date_sub('2023-04-27 17:18:23',interval 1 quarter ); -- 2023-01-27 17:18:23
select date_sub('2023-04-27 17:18:23',interval 1 month ); -- 2023-03-27 17:18:23
select date_sub('2023-04-27 17:18:23',interval 1 day ); -- 2023-04-26 17:18:23
select date_sub('2023-04-27 17:18:23',interval 1 hour ); -- 2023-04-27 16:18:23
select date_sub('2023-04-27 17:18:23',interval 1 minute ); -- 2023-04-27 17:17:23
select date_sub('2023-04-27 17:18:23',interval 1 second ); -- 2023-04-27 17:18:22
-
datediff
-
功能:用于获取两个日期之间的差值
-
语法:date_diff(日期1, 日期2)
-
示例
-
select datediff('2023-04-27','2023-04-30'); -- -3
select datediff('2023-04-27','2023-04-25'); -- 2
-
timediff
-
功能:用于获取两个时间之间的差值
-
语法:timediff(时间1, 时间2)
-
示例
-
select timediff('2023-04-27 17:18:23','2023-04-26 17:18:23'); -- 24:00:00
select timediff('2023-04-27 17:18:23','2023-04-27 16:18:23'); -- 01:00:00
-
last_day
-
功能:用于获取每个月最后一天
-
语法:last_day(日期)
-
示例
-
select last_day('2023-04-27 17:18:23'); -- 2023-04-30
select last_day('2023-02-27 17:18:23'); -- 2023-02-28
-
day_of_week / day_of_month / day_of_year
-
功能:获取某个日期是那一周/那一个月/那一年的第几天
-
语法:day_of_week(日期)/day_of_month(日期)/day_of_year(日期)
-
示例
-
select dayofweek('2023-04-27 17:18:23'); -- 5
select dayofmonth('2023-04-27 17:18:23'); -- 27
select dayofyear('2023-04-27 17:18:23'); -- 117
-
week_of_year
-
功能:获取日期在今年的第几周
-
语法:week_of_year(日期)
-
示例
-
-- 等价于week(date,3)
/*
WEEK(date, mode);有两个参数
1、date是要获取周数的日期
2、mode是一个可选参数,用于确定周数计算的逻辑
模式 一周的第一天 范围 说明
0 星期日 0-53
遇到本年的第一个星期天开始,是第一周。前面的计算为第0周。
1 星期一 0-53 假如第一周能超过3天,那么计算为本年的第一周。否则为第0周
2 星期日 1-53 遇到本年的第一个星期天开始,是第一周。
3 星期一 1-53 假如第一周能超过3天,那么计算为本年的第一周。否则为上年度的第5x周。
4 星期日 0-53 假如第一周能超过3天,那么计算为本年的第一周。否则为第0周
5 星期一 0-53 遇到本年的第一个星期一开始,是第一周。
6 星期日 1-53 假如第一周能超过3天,那么计算为本年的第一周。否则为上年度的第5x周。
7 星期一 1-53 遇到本年的第一个星期一开始,是第一周。
*/
select weekofyear('2023-04-27 17:18:23'); -- 17
4、特殊函数
1)常用其他函数
- 聚合函数group_concat
- 逻辑判断函数case when
- 其他判断函数及转换函数
-
if
-
功能:用于实现条件判断,基于条件判断的结果返回不同的值
-
语法:if(判断条件,条件成立返回的结果,条件不成立返回的结果)
-
-
ifnull
-
功能:用于判断第一个参数是否为null,如果为null则返回第二个参数,如果不为null则返回第一个参数
-
语法:ifnull(参数1,参数2)
-
-
coalesce
-
功能:返回参数列表中第一个非空的值
-
语法:coalesce(参数1,参数2,参数3……参数N)
-
-
cast
- 功能:将某一数据的类型进行转换
- 语法:cast( 列 as 新的类型)
-
2)窗口函数
-
设计:为了实现分组聚合、排序、位置偏移等操作并**保留原始数据内容**,提高查询效率和代码可读性
-
功能:基于数据实现**分区,并对分区内部的数据进行基于窗口的排序、聚合等操作并保留原始的数据**行内容
1)) 窗口聚合函数
-
函数:sum、count、max、min、avg
-
功能:生成一列基于每个分区的窗口进行sum、count、max、min、avg聚合的结果
-
语法
sum/count/max/min/avg(处理的列) over (partition by col order by col [window_size])
2))窗口分析函数
-
row_number
-
功能:用于对每个分区内部进行**编号,编号从1开始,不考虑重复值问题,如果值相同,编号不相同**
-
场景:取每个分区内部的TopN
-
语法
row_number() over (partition by col order by col)
-
注意:窗口函数执行的顺序在group by之后,如果要对窗口函数的结果进行过滤,一般需要构建子查询
-
rank
-
功能:用于对每个分区内部进行编号,编号从1开始,不考虑重复值问题,如果值相同,编号相同,留下空位
-
场景:取每个分区内部的TopN,允许并列排名,会留下空位
-
语法
rank() over (partition by col order by col)
-
-
dense_rank
-
功能:用于对每个分区内部进行编号,编号从1开始,不考虑重复值问题,如果值相同,编号相同,不留空位
-
场景:取每个分区内部的TopN,允许并列排名并且不留空位
-
语法
dense_rank() over (partition by col order by col)
-
-
ntile
-
percent_rank
3))窗口偏移函数
-
first_value
-
功能:用于取分区窗口内某一列的第一个值
-
语法
first_value(col) over (partition by col order by col)
-
-
last_value
-
功能:用于取分区窗口内某一列的最后一个值
-
注意:窗口范围的问题
-
语法
last_value(col) over (partition by col order by col)
-
-
lead
-
功能:用于获取分区内某一列向后偏移N个单位的值
-
语法
lead(某一列,N个单位,取不到的默认值) over (partition by col order by col)
-
-
lag
-
功能:用于获取分区内某一列向前偏移N个单位的值
-
语法
lead(某一列,N个单位,取不到的默认值) over (partition by col order by col)
-
注意
窗口大小
-
默认窗口
- 既有分区,又有排序:默认窗口是从分区第一行到当前行
- 只有分区:默认窗口是从分区第一行到最后一行 => 整个分区
- 只有排序:从分区第一行到当前行,如果有重复排序值,直接累加
-
自己定义窗口
rows|range between 起始位置 and 结束位置
- preceding:前面的
- following:后面的
- current row:当前行
- unbounded preceding:从分区的第一行
- unbounded following:到分区的最后一行
rows between unbounded preceding and current row: 从分区的第一行到当前行 rows between 2 preceding and current row: 从前2行到当前行 rows between 3 preceding and 1 following: 从前3行到后1行, 一共5行 rows between current row and unbounded following:从当前行到最后一行
MySQL关键词执行顺序
-
书写顺序
select distinct 聚合函数, 窗口函数 from 数据表A join 数据表B on 关联条件 where 分组前过滤 group by 分组字段 having 分组后过滤 order by 排序字段 limit 分页查询
-
执行顺序
1) from 2) join 3) on 4) where 5) group by 6) 聚合函数 7) having 8) 窗口函数 9) select 10) distinct 11) order by 12) limit
-
限制:窗口函数不能放在同一条SQL语句的where、group by、having后使用,但是可以放在order by 中使用
顺序
- 数据库:MySQL优化器允许用户在having使用别名,底层优化时候自动进行表达式替换
- 连接器:负责连接,接受客户端请求
- 解析器:将SQL语句进行解析:发现语法错误,数据库、表、字段是否存在
- 编译器:将SQL语句抽象成底层语法树
- 优化器:优化你的执行过程
- 执行器:负责执行整个任务