3、MYSQL函数（了解）

MySQL支持的字符串函数

函数功能
concat(s1,s2,......sn) 连接s1,s2,.....,sn为一个字符串
insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，若 x 超过字符串长度，则返回值为原始字符串。假如 y的长度大于其他字符串的长度，则从位置 x 开始替换。若任何一个参数为 NULL，则返回值为 NULL。
lower(str) 将字符串str中字符变为小写
upper(str) 将字符串str中字符变为大写
left(str,x) 返回字符串str最左边的x个字符
right(str,x) 返回字符串str最右边的x个字符
lpad(str,n,pad) 用字符串pad对str最左边进行填充，直到长度为n个字符长度
rpad(str,n,pad) 用字符串pad对str最右边进行填充，直到长度为n个字符长度
ltrim(str) 去掉字符串str左侧的空格
rtrim(str) 去掉字符串str右侧的空格
repeat(str,x) 返回str重复x次的结果
replace(str,a,b) 用字符串b替换字符串str中所有出现的字符串a
strcmp(s1,s2) 比较字符串s1和s2
trim(str) 去掉字符串行尾和行头的空格
substring(str,x,y) 返回从字符串str x位置起y个字符长度的字串
复制代码
select left('abcd',2); -- ab
select concat('ab','cd','wz'); -- abcdwz
select insert('abcde',2,3,null); -- null
select INSERT('abcde',2,2,'yyyyy'); -- ayyyyyde
select lpad('ab',4,'mkb'); -- mkab
select replace('abc','a','m'); -- mbc
select strcmp('ab','ac'); -- -1
select strcmp('ba','ac'); -- 1
select substring('abc',1,2); -- ab
select LOWER('ABCd'); -- abcd 
select trim('   dad    '); -- dad
复制代码

MySQL支持的数值函数

函数功能
abs(x) 返回x的绝对值
ceil(x) 返回大于x的最小整数值
floor(x) 返回小于x的最大整数值
mod(x,y) 返回x/y的模
rand() 返回0~1内的随机值
round(x,y) 返回参数x的四舍五入的有y位小数的值
truncate(x,y) 返回数字x截断为y位小数的结果 
select abs(-1); -- 1
select ceil(2.1); -- 3
select floor(2.9); -- 2
select mod(7,5); -- 2
select rand(); -- 随机的 0.3777260141239744
select round(5.555,2);-- 5.56
select TRUNCATE(15.26,1); -- 15.2

MySQL支持的日期和时间函数

函数功能
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前的日期和时间
unix_timestamp(date) 返回日期date的unix时间戳
from_unixtime 返回unix时间戳的日期值
week(date) 返回日期date为一年中的第几周
year(date) 返回日期date的年份
hour(time) 返回time的小时值
minute(time) 返回time的分钟值
monthname(date) 返回日期date的月份名
date_format(date,fmt) 返回按字符串fmt格式化日期date值
date_add(date,interval expr type) 返回一个日期或时间值加上一个时间间隔的时间值
datediff(expr,expr2) 返回起始时间expr和结束时间expr2之间的天数

MySQL支持的日期和时间格式

格式符格式说明
%S和%s 两位数字形式的秒（00，01，...，59）
%i 两位数字形式的分（00，01，...，59）
%H 两位数字形式的小时，24小时（00，01，...，23）
%h和%I 两位数字形式的小时，12小时（00，01，...，11）
%k 数字形式的小时，24小时（0，1，...，23）
%l 数字形式的小时，12小时（0，1，...，12）
%T 24小时的时间形式（hh:mm:ss）
%r 12小时的时间形式（hh:mm:ssAM或hh:mm:ssPM）
%p AM或PM
%W 一周中每一天的名称（Sunday，Monday，...，Saturday）
%a 一周中每一天名称的缩写（Sun，Mon，...，Sat）
%d 两位数字表示月中的天数（00，01，...，31）
%e 数字形式表示月中的天数（00，01，...，31）
%D 英文后缀表示月中的天数（1st，2nd，3rd，...）
%w 以数字形式表示周中的天数（0=Sunday，1=Monday，...，6=Saturday）
%j 以3位数字表示年中的天数（001，002，...，366）
%U 周（0，1，52），其中Sunday为周中的第一天
%u 周（0，1，52），其中Monday为周中的第一天
%M 月名（January，February，...，December）
%b 缩写的月名
%m 两位数字表示的月份（01，02，...，12）
%c 数字表示的月份（1，2，...，12）
%Y 4位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”

MySQL支持的日期间隔类型

表达式类型描述格式
hour 小时 hh
minute 分 mm
second 秒 ss
year 年 YY
month 月 MM
day 日 DD
year_month 年和月 YY-MM
day_hour 日和小时 DD hh
day_minute 日和分钟 DD hh:mm
day_second 日和秒 DD hh:mm:ss
hour_minute 小时和分 hh:mm
hour_second 小时和秒 hh:ss
minute_second 分钟和秒 mm:ss

MySQL支持的流程函数

函数功能
if(value,t,f) 如果value是真，返回t；否则返回f
ifnull(value1,value2) 如果value1不为空，返回value1,否则value2
case when[value1] then [result]...else [default] end 如果value1是真，返回result，否则返回default
case [expr] when [value1] then [result1] ... else [default] end 如果expr等于value1，返回result1，否则返回default
复制代码
select 
CASE 
    WHEN 2>3 THEN 'a'
    ELSE    'b'
end;
-- b
select 
case 'a'
    when 'a'    then '1'
    ELSE '2'
END;
-- 1
复制代码

MySQL的JSON函数

1、创建JSON函数

函数功能
json_array() 创建json数组
json_object() 创建json对象
json_quote()/json_unquote() 加上/去掉json文档两边的双引号

2、查询JSON函数

函数功能
json_contains() 查询文档中是否包含指定的元素
json_contains_path() 查询文档中是否包含指定的路径
json_extract() 根据条件提取文档中的数据
json_keys() 提取所有key的集合
json_search() 返回所有符合条件的路径集合

3、修改JSON函数

函数功能
json_merge()(deprecated 5.7.22) json_merge_preserve 将两个文档合并
json_array_append() 数组尾部追加元素
json_array_insert() 在数组的指定位置插入元素
json_remove() 删除文档中指定位置的元素
json_replace() 替换文档中指定位置的元素
json_set() 给文档中指定位置的元素设置新值，如果元素不存在，则进行插入

4、查询JSON元数据的函数

函数功能
json_depth() JSON文档的深度（元素最大嵌套层数）
json_length() JSON文档的长度（元素个数）
json_type() JSON文档类型（数组、对象、标量类型）
json_valid() JSON格式是否合法

5、JSON工具函数

函数功能
json_pretty() 美化JSON格式
json_storage_size() JSON文档占用的存储空间
json_storage_free() JSON文档更新操作后剩余的空间，MySQL8.0新增
json_table() 将JSON文档转换为表格，MySQL8.0新增
json_arrayagg() 将聚合后参数中的多个值转换为JSON数组
json_objectagg() 将两个列或者是表达式解释为一个key和一个value，返回一个JSON对象

MySQL窗口函数

1、ROW_NUMBER() 排序返回序号（连续的，指定排序字段值相等也会出现不同序号）

row_number()后面的over是关键字，用来指定函数执行的窗口范围，如果后面什么都不写，则意味着窗口包含所有行，窗口：函数在所有行上进行计算；如果不为空，则支持以下4种语法：

window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名则更清晰易读；

partition by子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；

order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，既可以和partition子句配合使用，也可以单独使用；

frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

基于行

基于范围

例如:  select 要查询的值,row_number() over(order by 表中某一个列名称) as 起一个列名 from 表名称

2、RANK()/DENSE_RANK()

RANK()/DENSE_RANK()：

rank()/dense_rank()这两个函数与row_number()非常类似，只是在出现重复值时处理逻辑有所不同。

rank():不一定连续的，指定排序字段值相等，序号相同，但是下一个序号就会跳跃

dense_rank()：连续的，指定排序字段值相等，序号相同，但是下一个序号不会就跳跃

select *,row_number() over(PARTITION by sex order by age asc) row_num from student;
​
select *,rank() over(PARTITION by sex order by age asc) row_num from student;
​
select *,dense_rank() over(PARTITION by sex order by age asc) row_num from student;

3、PERCENT_RANK()/CUME_DIST()

percent_rank()和cume_dist()这两个函数都是计算数据分布的函数，percent_rank()和之前的rank()函数相关，每行按照以下公式进行计算：

（rank-1)/(rows-1)，其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数。

相比percent_rank()，cume_dist()函数的应用场景更多，它的作用是分组内小于等于rank值的行数/分组内的总行数（统计大于等于当前订单金额的订单数，占总订单数的比例）。

4、NTILE(N)

ntile()函数的功能是对一个数据分区中的有序结果集进行划分，将其分为N个组，并为每个小组分配一个唯一的组编号。

此函数在数据分析中应用较多，比如由于数据最大，需要将数据分配到N个并行的进程分别计算，此时就可以用NTILE(N)对数据进行分组，由于记录数不一定被N整除，所以每组记录数不一定完全一致，然后将不同组号的数据再分配。

5、NTH_VALUE(expr,N)

NTH_VALUE(expr,N)函数可以返回窗口中第N个expr的值，expr既可以是表达式，也可以是列名。

6、LAG(expr,N)/LEAD(expr,N)

LAG(expr,N)和LEAD(expr,N)这两个函数的功能是获取当前数据行按照某种排序规则上的N行（LAG）/下N行（LEAD）数据的某个字段。

7、FIRST_VALUE(expr)/LAST_VALUE(expr)

FIRST_VALUE(expr)和LAST_VALUE(expr)这两个函数的功能分别是获得滑动窗口范围内参数字段中第一个和最后一个的值。

8、聚合函数作为窗口函数

使用各种聚合函数（sum、avg、max、min、count）作为窗口函数来使用

MySQL中的其他常用函数

函数功能
DATABASE() 返回当前数据库名
VERSION() 返回当前数据库版本
USER() 返回当前登录用户名
INET_ATON(IP) 返回IP地址的数字表示
INET_NTOA(num) 返回数字代表的IP地址
PASSWORD(str) 返回字符串str的加密版本
MD5() 返回字符串str的MD5值

加密与解密函数

  PASSWORD(str) -- 返回字符串str的加密版本，41位长的字符串。加密结果`不可逆`，MySQL8.0已弃用
  MD5(str) -- 返回字符串str的md5加密后的值若参数为NULL，则会返回NULL
  SHA(str) -- 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL
  ENCODE(value,password_seed) -- 返回使用password_seed作为加密密码加密value，MySQL8.0已弃用
  DECODE(value,password_seed) -- 返回使用password_seed作为加密密码解密value，MySQL8.0已弃用

example:

  -- PASSWORD在MySQL8.0弃用
  SELECT PASSWORD('mysql') FROM DUAL;
  -- MD5()、SHA()加密不可逆
  SELECT MD5('mysql'),SHA('mysql') FROM DUAL;
  -- ENCODE、DECODE()在MySQL8.0弃用
  SELECT ENCODE('sakura','kinomoto'),DECODE(ENCODE('sakura','kinomoto'),'kinomoto') FROM DUAL;

MySQL信息函数

  VERSION() -- 返回当前MySQL的版本号
  CONNECTION_ID() -- 返回当前MySQL服务器的连接数
  DATABASE()，SCHEMA() -- 返回MySQL命令行当前所在的数据库
  USER() -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
  CURRENT_USER() -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
  SYSTEM_USER() -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
  SESSION_USER() -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
  CHARSET(value) -- 返回字符串value自变量的字符集
  COLLATION(value) -- 返回字符串value的比较规则

example:

  SELECT VERSION() FROM DUAL;
  SELECT CONNECTION_ID() FROM DUAL;
  SELECT DATABASE(),SCHEMA() FROM DUAL;
  SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER() FROM DUAL;
  SELECT CHARSET('sakura') FROM DUAL;
  SELECT COLLATION('sakura') FROM DUAL;

聚合函数是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。聚合函数不能嵌套调用。

常见的聚合函数类型

  AVG() -- 平均数函数
  SUM() -- 求和函数
  MAX() -- 最大值函数
  MIN() -- 最小值函数
  COUNT() -- 求总数函数
  • AVG() 和 SUM() 只适用于数值类型的字段
  • MAX() 和 MIN() 适用于数值类型、字符串类型、时间类型
  • COUNT(*)、COUNT(1)返回表中记录总数，适用于任意数据类型
  • COUNT(expr) 返回expr不为空的记录总数
  • AVG() = SUM()/COUNT()
  • 聚合函数忽略NULL
  -- AVG()、SUM()只适用于数据类型
  SELECT AVG(salary),SUM(salary) FROM employees;
   
  -- MAX()、MIN()适用于数值类型、字符串类型、时间类型
  SELECT MAX(salary),MIN(salary) FROM employees;
  SELECT MAX(last_name),MIN(last_name) FROM employees;
  SELECT MAX(hire_date),MIN(hire_date) FROM employees;
   
  SELECT COUNT(employee_id) FROM employees;
  SELECT COUNT(commission_pct) FROM employees;
  SELECT COUNT(*) FROM employees;
  SELECT COUNT(1) FROM employees;
   
  SELECT AVG(salary),SUM(salary)/COUNT(salary) FROM employees;
  SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct)/COUNT(*) FROM employees;

使用 COUNT(*)、COUNT(1)、COUNT(具体字段) 统计表中的记录数，哪个效率更高？

  • 如果使用的是MyISAM存储引擎，则三者效率相同，都是O(1)；
  • 如果使用是InnoDB存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(具体字段)

GROUP BY的使用

  SELECT 字段名1, 分组函数(字段名2)
  FROM 表名
  GROUP BY 字段名1;
  • 可以使用GROUP BY子句将表中的数据分成若干组；
  • 在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中，反之，GROUP BY中声明的字段可以不出现在SELECT中；
  • GROUP BY 声明在 FROM 后面，WHERE 后面，ORDER BY 前面，LIMIT 前面
  • 使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
  • 当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的。
  -- SELECT 中出现的非分组函数的字段必须声明在 GROUP BY 中。
  -- GROUP BY 中声明的字段可以不出现在SELECT中
  SELECT department_id,AVG(salary),SUM(salary),MAX(salary) FROM employees GROUP BY department_id;
  SELECT department_id,job_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id,job_id;
  SELECT job_id,department_id,AVG(salary),SUM(salary) FROM employees GROUP BY job_id,department_id;
   
  SELECT department_id,AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP;

HAVING的使用

  SELECT 字段名1, 分组函数1(字段名2)
  FROM 表名
  GROUP BY 字段名1;
  HAVING 带分组函数的过滤条件;
  • HAVING 必须声明在 GROUP BY 后面。
  • 如果过滤条件中使用了聚合函数，则必须使用 HAVING 来替换 WHERE。否则，报错。
  • 当过滤条件没有聚合函数时，则此过滤条件中声明在 WHERE 中或 HAVING 中都可以，建议声明在 WHERE 中。
  • 开发中，我们使用 HAVING 的前提是 SQL 中使用了 GROUP BY。
  SELECT department_id,MAX(salary)
  FROM employees
  GROUP BY department_id
  HAVING MAX(salary) > 10000;
   
  SELECT department_id,MAX(salary)
  FROM employees
  WHERE department_id IN (10,20,30)
  GROUP BY department_id
  HAVING MAX(salary) > 10000;

 

posted @   carat9588  阅读(55)  评论(0)  编辑  收藏  举报
相关博文：
· 2、MYSQL数据类型和运算符（了解）
· 6、存储过程与存储函数
· MySQL基础2【678910多表查询-函数-子查询-管理表】尚硅谷
· MySQL基础篇
· MySQL基础篇
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示