MySQL三大类处理函数

最新推荐文章于 2022-04-13 14:39:11 发布

qu_sprrow

最新推荐文章于 2022-04-13 14:39:11 发布

阅读量248

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/qu_sprrow/article/details/104120419

版权

一,文本函数

1,拼接函数
    concat(字段名1,字段名2..), 字段之间拼接到一起
    group_concat(字段名), 结合 group by 使用,同字段内容竖着的连接一起,逗号隔开
    select id,concat(name,gender),math from student;
    select class,group_concat(id) from student group by class;
2,计算长度函数
    length(), 计算字节长度
    char_length(), 计算字符长度
    select id,length(id),name,lenth(name),char_length(name) from sthdent;
3,字母大小写转换函数
	lower(), 字母转小写 select lower("ABCabc");
	upper(), 字母转大写 SELECT UPPER("ABCabc");
4,trim函数
	ltrim(),rtrim(),trim(), 左右和两头去掉空格对应的函数
	完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
		(1) trim(leading "c" from "cbaabc"), 删除左边的字符c
		(2) trim(trailing "c" from "cbaabc"), 删除右边的字符c 
		(3) trim(both "c" from "cbaabc"), 删除两边的字符c
	简化格式：TRIM([remstr FROM] str)
		(1) 当省略不写时对应两头去空
		(2) 不省略对用两头去除对应的字符,同: trim(both remstr from str)
5,截取函数
	(1) left(str,len), 左边截取对应长度字符  select left("天下第一",2);
	(2) right(str,len), 右边截取对应长度字符	select right("天下第一",2);
	(3) mid(str,pos,len), 从指定的索引位置截取对应长度字符 
		select mid("天下第一",1,2)
	
	(4) substring截取特定长度字符串
		substring(str,pos),  既substring(被截取字符串,从第几位开始截取)
		substring(str,pos,len), 即substring(被截取字符串,截取位置,截取长度)
		select substring("www.quezhuosparrow.com",9) # 从第九个开始读取到结束
		select substring("www.quezhuosparrow.com",-6) # 倒数第六个读取到最后 
		select substring("www.quezhuosparrow.com",9,3) # 9位置开始截取长度3
		select substring("www.quezhuosparrow.com",-6,2) # -6位置往后截取长度2
	(5) substring按关键字进行读取
		substring_index(str,delim,count), (字符串,关键字,出现的次数)
		SELECT SUBSTRING_INDEX("www.quezhuosparrow.com",".",2);  
			关键字前面部分, www.quezhuosparrow
		SELECT SUBSTRING_INDEX("www.quezhuosparrow.com",".",-2); 
			关键字后面部分, quezhuosparrow.com
		SELECT SUBSTRING_INDEX("www.quezhuosparrow.com","ok",-2); 
			没有关键字则对应原字符, www.quezhuosparrow.com
6,位置函数:  返回substr在str第一次出现的位置,当没有是返回0
	(1) INSTR(str,substr)
	(2) POSITION(substr IN str)
	(3) LOCATE(substr,str)
7,替换函数
	REPLACE(str,from_str,to_str).  SELECT replace("天下第一","一","二");

二,数值处理函数

1, ABS(X) 取绝对值
2, SQRT(X) 取平方根
3, POWER(X,Y) 求指数 select power(2,2); # 4

4, round(x,[d]) 四舍五入函数,d 表示小数点后保留位数,可以为负数,省略不写表示0
	select round(5.6); # 6
	select round(-0.1); # 0 取最靠近原始数字的整数
	select round(7.456,1); # 7.5
	select round(1124.34,-2); # 1100
5, truncate(x,[d]) 舍去直接取值函数, d 表示小数点后保留位数,可以为负数,省略则表示0
	select truncate(1156.73); # 1156
	select truncate(1156.73,-1); # 1150
6, ceiling(x) 天花板函数  select ceiling(12.3); # 13
7, floor(x) 地板函数  select floor(12.7); # 12

8, rand() 随机取值函数,但是取值范围只在[0,1],任意的数值
	若是想要[x,y]的随机整数可用round(rand() * (x-y) + x )推导过来
9, greatest(值1,值2...) 返回里面数据最大的值
   least(值1,值2,...) 返回里面数据最小的值
	  SELECT GREATEST(1,2,3); # 3
	  select least(1,2,3); # 1

10, 数据的离散程度
	(1) var_pop(数据字段), 总体方差  var_samp(数据字段), 样本方差
	(2) stddev_pop(数据字段), 总体标准差  stddev_samp(数据字段), 样本标准差

三,日期时间函数

1, 提取时间
	(1) date(expr) 返回日期
	(2) time(expr) 返回时间
	(3) year(date) 返回对应的年份
	(4) month(date) 返回对应的月份, MONTHNAME(date):返回日期月份对应的英语
	(5) day(date) 返回对应的天号, DAYNAME(date):返回日期天号对应周几状态
	(6) minute(time),second(time) 返回对应的分钟和秒数
	(7) last_day(date) 返回日期对应月份的最后一天
	
2, 获取系统日期时间
	(1) curdate() = current_date() 获取系统当前日期 2020-02-01
	(2) curtime() = current_time() 获取系统当前时间 22:01:16
	(3) current_timestamp() 获取系统当前时间戳 2020-02-01 22:01:16

3, 日期间隔计算
  (1) DATEDIFF(expr1,expr2) 返回两个日期之间的天数
	 select * from employees where datediff("20180101",date(ruzhi_date)>=0;
	 # 2018-01-01 之前入职的所有人员的信息 (结果也是可以为负值的)
  (2) TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 指定间隔单位
     select timestampdiff(year,"2017/01/01","2019/01/02"); # month,day..都可以
	
4, 时间日期的加减变动
 (1) DATE_ADD(date,INTERVAL expr unit) = ADDDATE(date,INTERVAL expr unit) 
 
   (1 对某个日期加上n天的操作
      select date_add('2018-06-26',INTERVAL '5' day); #  2018-07-01   
      select date_add('2018-06-26',INTERVAL '-5' day); # 2018-06-21
   备注:expr是字符串,如果加正值,直接'5'即可,减少可以用date_sub函数或expr为负值'-5'.
   
   (2 对某个日期加上n小时,n分钟,n秒的操作(hour,minute,second 都是同样使用)
      select data_add("2018-06-26 23:59:59",interval 1 hour); 
      
   (3 对于某个日期加上n分钟n秒
      select date_add('2018-06-26 23:59:59',INTERVAL '1:1' MINUTE_SECOND);

   (4 对于某个日期加上n小时n分钟n秒
      select date_add('2018-06-26 23:59:59',INTERVAL '1:1:1' HOUR_SECOND);
    备注:单位HOUR_SECOND就是从小时到秒.expr:HOURS:MINUTES:SECONDS
    
   (5 对某个日期加上n天n小时n分钟n秒
      select date_add('2018-06-26 23:59:59',INTERVAL '2 2:1:1' DAY_SECOND);
   

 (2) DATE_SUB(date,INTERVAL expr unit) = SUBDATE(date,INTERVAL expr unit)
   备注:使用方法和上面相同

5, 时间戳相互转换
距离1970-01-01 00:00:00的秒数, 实验是从8点开始计算的,很多不同版本,了解即可
  (1) unix_timestamp函数有两种调用形式
    (1 无参数 select unix_timestamp(); 返回当前时间的时间戳
	(2 有参数 select unix_timestamp(date); 返回与指定时间的秒数差	

  (2) from_unixtime(unix_timestamp([date]),format); 
  # 根据format返回对应日期时间, unix_timestamp用数字都是一样的效果
  # format省略不写会默认使用会使用%Y-%m-%d %H:%i:%s的格式
    select FROM_UNIXTIME(unix_timestamp());
    
6, 字符串转换为日期 STR_TO_DATE(str,format)
	SELECT STR_TO_DATE("01,02,2019","%m,%d,%Y");  
	# 注意format要和前面的str对应上,datetime返回格式系统默认

常用的日期时间参数形式: (其他的有需要可以网上查找)
(1) 年: %Y 表示4位数的年份
(2) 月: %m 表示两位的月份(01,02...12)
(3) 日: %d 表示两位的天数(01,02,...30.)
(4) 时: %H 表示两位的时辰(00,01...23)
(5) 分: %i 表示两位的分钟(00,01...59)
(6) 秒: %s 表示两位的秒数(00,01...59)

附加函数:

if函数

1, 语法: IF(expr1,expr2,expr3) 如果expr1成立返回expr2,否则返回expr3
   SELECT if(2>1,"对","错");  # 对

case函数

1, 用法1
  CASE expression
  WHEN value1 THEN returnvalue1
  WHEN value2 THEN returnvalue2
  WHEN value3 THEN returnvalue3
  ……
  ELSE defaultreturnvalue
  END
demo:  
  select t.name,
  (case t.sex when 0 then 'woman' when 1 then 'man' else 'unknown' end) sex 
  from test t; 
2, 用法2
  CASE
  WHEN condition1 THEN returnvalue1
  WHEN condition 2 THEN returnvalue2
  WHEN condition 3 THEN returnvalue3
  ……
  ELSE defaultreturnvalue
  END
demo:
  select t.name,
  (case when t.sex=0 then 'woman' when t.sex=1 then 'man' else 'unknown' end) sex 
  from test t;