01.mysql常用函数

1、通配符

% 匹配多个字符，_ 匹配一个字符

使用通配符%，尽量不要把它们用在搜索模式的开始处。如('%fdf')

2、REGEXP

正则表达式匹配。默认不区分大小写。

REGEXP与LIKE的区别：

• like匹配整个列值，如果别匹配的整个文本在列值中出现，将不会找到它。

• regexp在列值内进行匹配。如果像like一样，可以使用^和$定位符。

• 如果需要区分大小写可以使用REGEXP BINARY

  mysql转义使用\\两个反斜杠（mysql自己解释一个，正则表达式库解释另外一个）

3、mysql常用函数

3.1 文本处理函数

• concat 列拼接
• rtrim 去掉右边的所有空格
• ltrim 去掉左边的所有空格
• trim 去掉左右两边的空格
• upper 文本转为大写
• lower 文本转小写
• left(str,length) 返回具有指定长度的字符串的左边部分
• right(str,length) 返回具有指定长度的字符串的右边部分
• length(str)返回字符串长度
• locate(substr,str)substr在str中出现的位置。包含>0,不包含=0
• soundex(str)类似的发音字符和音节，能对文本串进行发音比较而不是字母比较
• substring从字符串中提取子字符串,pos从1开始提取
  • substring(str,pos)
  • substring(str from pos)
  • substring(str,pos,len)
  • substring(str from pos for len)

3.2日期和时间处理函数

select cust_id,order_num from orders where order_date = '2005-09-01'
order_date的数据类型为datetime。这种类型存储日期和时间值。
当order_date为2005-09-01 11:30:05是无法检索出来的。
只能检索出2005-09-01 00:00:00。
可以使用date()函数只比较日期即可。

• now() 返回数据库所在时间
• curdate()返回当前日期
• curtime返回当前时间
• date(date)返回日期时间的日期部分如:2020-04-04
• time(date)返回日期时间的时间部分如:23:53:26
• year(date)返回日期时间的年份部分如:2020
• month(date)返回日期时间的月份部分如:4
• day(date)返回日期时间的天数部分如:4
• hour(date)返回日期时间的小时部分如:23
• minute(date)返回日期时间的分钟部分如:51
• second(date)返回日期时间的秒部分如:33
• date_add(date,INTERVAL expr type)与addDate(date,INTERVAL expr type)相同。
  • erpr为具体的数值。type可为year,month,day,hour,minute,second。
  • type为组合类型详见https://www.runoob.com/sql/func-date-add.html
  • 当erpr为负值，相当于date_sub
• date_sub(date,INTERVAL expr type)与subdate(date,INTERVAL expr type)相同。
  • 当erpr为负值，相当于date_add
• addtime(expr1,expr2)添加到expr2 到 expr1 并返回结果
  • 如：select addtime(now(),'10:30:30') from dual;
  • 当expr2为-如：select addtime(now(),'-10:30:30') from dual;相当于subtime
• subtime(expr1,expr2)expr1减去expr2并返回结果
  • select subtime(now(),'10:30:30') from dual;
  • 当expr2为-如：select subtime(now(),'-10:30:30') from dual;;相当于addtime
• datediff
• date_format(date,formate)用于以不同的格式显示日期/时间数据
  • %Y年，4位
  • %y年，2位
  • %M ，月名（April）
  • %m ，月，数值(00-12)
  • %D ，带有英文前缀的月中的天（5th）
  • %d ，月的天，数值(00-31)
  • %e ，月的天，数值(0-31)
  • %H ，小时 (00-23)
  • %h ，小时 (01-12)
  • %i ，分钟数值(00-59)
  • %S ，秒(00-59)
  • %s ，秒(00-59)
  • %T ，时间, 24-小时 (hh:mm:ss)
• str_to_date('2020-03-20 16:01:45', '%Y-%m-%d %H:%i:%s')字符串转时间

3.3数值处理函数

• rand()返回0-1的随机数
  • rand(1)与rand(20)每次分别返回两个固定数值，在0-1之间
• round四舍五入函数
  • round(数值)同round(数值,0)只保留整数，四舍五入
  • round（数值，参数）
    • 如果参数的值为正数，表示保留几位小数。round(45.926,2) 为45.93，round(45.926,1)为45.9
    • 如果参数的值为负数，表示对小数点前第几位进行四舍五入。round(51.926,-1)为50，round(51.926,-2)为100
• ceil（数值）：向上取整
  • ceil(99.1)为100，ceil(-99.1)为-99
• floor（数值）：向下取整
  • floor(99.1)为99，floor(-99.1)为-100
• abs(x)：返回 x 的绝对值
• truncate(X,D)：将数字X截断到指定的小数位数D（不四舍五入）
  • truncate(89.985,2)为89.98
  • truncate(89.985,4)为89.9850当D大于实际小数位是会补0

3.4字符串转数字(空字符为0)

• SELECT '123'+0
• CAST(value as type);CONVERT(value, type);
  • type为signed,unsigned,decimal
  • decimal默认不保留精度。
  • 保留精度可以使用demical(M,D)**函数采取四舍五入的方式，保留小数位数.

3.5数字转字符串

• concat(99,780,'')
• cast(44.7686 as char)

3.5函数判断空值

• ISNULL()函数仅仅是用于判断空值的，接受一个参数并返回一个布尔值。存在返回0，不存在返回1
• IFNULL(v1, v2)如果第一个参数不是NULL，则返回第一个参数；否则，将返回第二个参数。
• NULLIF(expr1,expr2)。如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同

3.6条件判断

• IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值。否则返回expr3

  • select IF(age<4,'小朋友','大朋友') from tb_user;
    

• IF ELSE用于存储过程与函数。

  IF age<2 THEN '小朋友'
  ELSEIF age<4 THEN '中朋友'
  ELSE '大朋友'
  END IF
  

• CASE WHEN

  • SELECT *,
    (CASE 
    WHEN age<2 THEN '小朋友'
    WHEN age<4 THEN'中朋友'
    ELSE '大朋友'
    END) as descp
    FROM tb_user;
    

  • SELECT *,
    (CASE sex
    WHEN 0 THEN '男'
    WHEN 1 THEN'女'
    ELSE '其他'
    END) as '性别'
    FROM tb_user;