mysql常用函数

'会飞的猪

已于 2023-02-25 08:42:41 修改

阅读量312

点赞数

分类专栏： mysql 文章标签： mysql常用函数大全

于 2019-04-30 10:51:48 首次发布

本文链接：https://blog.csdn.net/weixin_43832604/article/details/89681715

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL中的常用函数

IFNULL(a,b)

SELECT IFNULL(1,2)      #返回 1
SELECT IFNULL(null,2)    #返回 2
SELECT IFNULL(‘a’,2)    #返回 a

IF(a,b,c)

SELECT IF(1>2,'a','b')    #返回  b
SELECT IF(1<2,'a','b')   # 返回  a

CASE a WHEN b THEN c ELSE d end

SELECT CASE 'abc' WHEN 'abc' then 1 ELSE 2 END     #返回  1
SELECT CASE 'abc' WHEN 'abcd' then 1 ELSE 2 END     #返回  2

RAND() 随机数，返回0~1 之间的随机数

SELECT RAND()     #每执行一次，返回一个随机不同的随机数
SELECT * from t_table ORDER BY RAND() LIMIT 8    #获取随机的8条数据
SELECT RAND(1)  #每次返回 结果 相同的随机数

ABS(X)

SELECT ABS(-2)  #返回绝对值,2

SIGN(X) 返回值取决于x ,是正数返回1,负数返回-1,零返回0

SELECT SIGN(88)  #返回1
SELECT SIGN(-1)  #返回-1
SELECT SIGN(0)  #返回0

MOD(a,b) 返回 a除以b的余数

select MOD(7,5) #余2
select MOD(5,5) #余0
select MOD(1,2) #余1

FLOOR(X) 返回一个不大于x的最大整数

select FLOOR(-100.99)  #返回 -101
select FLOOR(101.99)  #返回 101
select FLOOR(0)  #返回 0

CEILING(N) 返回不小于N的最小整数值

select CEILING(-101.99)  #返回-101
select CEILING(101.99)  #返回102

ROUND(N,D) 返回N的四舍五入值,保留D位小数(D的默认值为0)

select ROUND(0.1897,2)  #返回0.19
select ROUND(-0.1834,2)  #返回0.18
select ROUND(0.1897,0)  #返回0

count() 计数，统计

SELECT count(1) from t_table  #统计当前表中有多少数据

sum() 求和

SELECT sum(5+6)  # 返回11
SELECT sum(id)  from t_table  #返回当前表字段的和

like concat(‘%’,‘模糊匹配值’,‘%’)

SELECT * from t_table where name like concat('%','模糊匹配值','%') 
等同于
SELECT * from t_table where description like '%模糊匹配值%'

STR_TO_DATE(‘时间字符’,‘转化的格式’) 字符串转date

SELECT STR_TO_DATE('2019-01-08','%Y-%m-%d %H:%M:%S')   # 返回 2019-01-08 00:00:00

DATE_FORMAT(date,‘格式’) date转格式

SELECT DATE_FORMAT('2019-01-08 00:00:00','%Y-%m-%d') #返回2019-01-08

DATE_ADD(date,INTERVAL expr unit) 时间添加函数：date 时间,expr 需要加的数,unit(YEAR 年,MONTH 月,DAY 日,HOUR 时,MINUTE 分,SECOND 秒)

SELECT DATE_ADD('2019-01-30 00:00:00',INTERVAL 2 YEAR)  # 返回  2021-01-30 00:00:00
SELECT DATE_ADD('2019-01-30 00:00:00',INTERVAL 2 MONTH)  # 返回  2019-03-30 00:00:00
SELECT DATE_ADD('2019-01-30 00:00:00',INTERVAL 2 DAY)  # 返回  2019-02-01 00:00:00
SELECT DATE_ADD('2019-01-30 00:00:00',INTERVAL 2 HOUR)  # 返回  2019-01-30 02:00:00
SELECT DATE_ADD('2019-01-30 00:00:00',INTERVAL 2 MINUTE)  # 返回  2019-01-30 00:02:00
SELECT DATE_ADD('2019-01-30 00:00:00',INTERVAL 2 SECOND)  # 返回  2019-01-30 00:00:02

SELECT DATE_SUB(date,INTERVAL expr unit) 时间减函数

SELECT DATE_SUB('2019-01-30 00:00:00',INTERVAL 2 YEAR)  # 返回  2017-01-30 00:00:00
SELECT DATE_SUB('2019-01-30 00:00:00',INTERVAL 2 MONTH)  # 返回  2018-11-30 00:00:00
SELECT DATE_SUB('2019-01-30 00:00:00',INTERVAL 2 DAY)  # 返回  2019-01-28 00:00:00
SELECT DATE_SUB('2019-01-30 00:00:00',INTERVAL 2 HOUR)  # 返回  2019-01-29 22:00:00
SELECT DATE_SUB('2019-01-30 00:00:00',INTERVAL 2 MINUTE)  # 返回  2019-01-29 23:58:00
SELECT DATE_SUB('2019-01-30 00:00:00',INTERVAL 2 SECOND)  # 返回  2019-01-29 23:59:58

CONCAT(str1,str2,…) #将多个字符,拼接字符串

SELECT CONCAT('a','b','c')  #返回 abc

CHARACTER_LENGTH(‘字符串’) #查看字符串字符数

select CHARACTER_LENGTH('abc')  #返回 3

CONCAT_WS(‘每个字符间要添加的内容’,‘字符’,‘字符’,…) #拼接字符，以添加的内容隔开

select CONCAT_WS('@','a','b','c')  #返回 a@b@c

UPPER(str) #将字符串变大写

SELECT UPPER('abc')   #返回 ABC

LOWER(str) #将字符串变小写

SELECT LOWER('ABC')   #返回 abc

LEFT(s,n) 返回字符串s的前n个字符

SELECT LEFT('abcdef',2)   #返回 ab

RIGHT(s,n) 返回字符串s的后n个字符

SELECT RIGHT('abcdef',2)   #返回 ef

REVERSE(s) 将字符串s的顺序反过来

SELECT REVERSE('abc') #返回 cba

NOW() 获取当前时间

SELECT NOW() #返回 2019-02-30 08:19:4

CONVERT(s USING cs) 函数将字符串s的字符集变成cs。或将一个类型的值转化为类一个类型

SELECT CHARSET('ABC')  -- utf8
SELECT CHARSET(CONVERT('ABC' USING gbk)) -- gbk

convert(param, decimal(12,2)) 
select convert(18.6325, decimal(4,2))    18.63
也可排序 ORDER BY sort DESC,CONVERT(name USING GBK) ASC;

GROUP_CONCAT(sname) 拼接字段

select GROUP_CONCAT(sname) from students #曾华,匡明,王丽,李军,王芳,陆君,李四

CASE WHEN THEN ELSE END 判断

SELECT CASE WHEN 1>2 THEN 'true' ELSE 'false' END  # 返回 false
SELECT CASE 'c' WHEN 'a' THEN 1 WHEN 'b' THEN 2  ELSE 3 END # 返回 3
select id as 'categoryId', name as 'categoryName' 
  	  from t_category 
  	 where status = 1 
  	   and parent_id = 2329 
  	 order by case when id = 2436 then 1 else 0 end desc, sort 
  	 #排序用法

locate(‘str’,‘strabc’) 返回str 在strabc中出现的位置

SELECT locate('a','wwawbcc') # 返回 3 

<if test='categoryResultDto.parentIds.indexOf(",2329,") > 0'>
       and locate(CONCAT(',2329,'), pc.parent_ids) > 0 
     </if>

FIND_IN_SET（b,‘a,b,c’）返回 2 用于查找以，隔开所出现的位置

SELECT
	g.id,
	(
		SELECT
			GROUP_CONCAT(sd. NAME)
		FROM
			sys_dict sd
		WHERE
			FIND_IN_SET(sd.`code`, g.special_screen)
	) AS aa
FROM
	t_goods g
WHERE
	g.id IN (73739, 13324, 341465)

FIELD（字段,a,b,c,d,…）用于固定in查询后的返回顺序

SELECT g.id FROM t_goods g WHERE g.id IN (73739, 13324, 341465) //查出顺序 13324 73739  341465
SELECT g.id FROM t_goods g WHERE g.id IN (73739, 13324, 341465) ORDER BY FIELD(g.id, 73739, 13324, 341465)//查出顺序73739, 13324, 341465

instr(field, str) 可使用内部函数 instr，替代传统的 like 方式，并且速度更快（第一个参数 field 是字段，第二个参数 str 是要查询的串，返回串 str 的位置，没找到就是0）

select * from book where  INSTR( book_name , "经" ) > 0

escape转义用法

LIKE CONCAT('%', "/123", '%') ESCAPE '/' 
#意思就是/之后不做通配符

35 cast 转换类型

Cast(字段名 as 转换的类型 )，其中类型可以为：

CHAR[(N)] 字符型 
DATE  日期型
DATETIME  日期和时间型
DECIMAL  float型
SIGNED  int
TIME  时间型

select cast(date as signed) as date from  table1;
0 + cast(g.weight * gsoi.apply_num as char)as weight

SUBSTRING_INDEX(str,delim,count) str目标字符串-delim指定字符串-count正数从指定字符串左侧截取，负数指定字符串右侧截取

select SUBSTRING_INDEX("我们abc123","abc",1) #我们
select SUBSTRING_INDEX("我们abc123","abc",-1) #123

字符串排序含数字中文排序

select id,dict_name,type_code from t_dictionary  where type_code='GRADE' ORDER BY `dict_name`*1;  
select id,dict_name,type_code from t_dictionary  where type_code='GRADE' ORDER BY dict_name+0;  
select id,dict_name,type_code from t_dictionary  where type_code='GRADE' ORDER BY CAST(dict_name AS DECIMAL);

38 表外键

CREATE TABLE customerinfo (customerid INT PRIMARY KEY) ENGINE = INNODB;

CREATE TABLE salesinfo (
	salesid INT NOT NULL,
	customerid INT NOT NULL,
	PRIMARY KEY (customerid, salesid),
	FOREIGN KEY (customerid) REFERENCES customerinfo (customerid) ON DELETE CASCADE
) ENGINE = INNODB;

39 ROW_NUMBER() OVER(ORDER BY COLUMN) 排序函数

SELECT
	t.companyId,
	t.payAmount,
	Row_number () over (ORDER BY t.payAmount DESC) goodsAmountRank
FROM
	t_rebate_order t

40 @rownum 行号

SELECT 
  @rownum := @rownum +1 AS rownum,
  e.*
FROM
  (SELECT 
    @rownum := 0) r,
  employee e

更多函数参考函数大全

'会飞的猪

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
mysql常用函数

MySQL中的常用函数IFNULL(a,b)SELECT IFNULL(1,2) #返回 1SELECT IFNULL(null,2) #返回 2SELECT IFNULL(‘a’,2) #返回 aIF(a,b,c)SELECT IF(1>2,'a','b') #返回 bSELECT IF(1<2,'a','b') # 返回 ...
复制链接

扫一扫