MySQL初级--DQL操作（函数）

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语句抽象成底层语法树
• 优化器：优化你的执行过程
• 执行器：负责执行整个任务