MySQL函数讲解（字符串函数、转化函数、流程函数、比较谓词等）

函数讲解（字符串函数、转化函数、流程函数、比较谓词等）"/>

MySQL函数讲解（字符串函数、转化函数、流程函数、比较谓词等）

目录

MySQL常见函数

字符串函数 进行字符串操作的函数

算术函数 进行数值计算的函数

日期函数 进行日期操作的函数

转换函数 进行数据类型和值转换的函数

流程函数  进行条件删选

聚合函数 进行数据聚合的函数

谓词 一般用于条件判断

什么是函数

函数指一段可以直接被另一段程序调用的程序或代码

MySQL常见函数

字符串函数 进行字符串操作的函数

MySQL常用的内置 字符串函数如下

CONCAT(S1,S2……Sn)          字符串拼接函数，将S1,S2……Sn拼接为一个字符串

LENGTH(str)                        字符串长度，显示出字符串中包含多少个字符

LOWER(str)                         将字符串str全部转为小写

UPPER(str)                          将字符串str全部转为大写

REPLACE(str1,str2,str3)        字符串替换，将str1中的str2部分替换为str3

LPAD(str,n,pad)                   左填充，用字符串pad对str的左边进行填充，使得str达到n个字符串长度

RPAD(str,n,pad)                  右填充，用字符串pad对str的右边进行填充，使得str达到n个字符串长度

TRIM(str)                             去掉字符串的头部和尾部的空格

SUBSTRING(str,start,len)      返回字符串str从start位置起的len个长度的字符串（字符串的索引是从1开始的，而不是0）

update staff1 set number =  lpad(number,8,'0'); #修改number字段值，使用字符串”0”对其左填充，使得number达到8个字符串长度(注意：number的数据类型为varchar)

算术函数 进行数值计算的函数

MySQL常用的内置 算术函数如下

+（加法）、-（减法）、*（乘法）、/（除法）

ABS(x)                   对x取绝对值

MOD(x,y)              返回x/y的模（余数）

RAND()                 返回0~1内的随机数

ROUND(x,y)          求参数x的四舍五入的值，保留y位小数

CEIL(x)                  对x向上取整

FLOOR(x)              对x向下取整

select round(rand()*100000,0);   #随机生成5位数的验证码     如果产生的小数0.012345，生成的验证码就只有4位数1234，因此需要补0
select lpad(round(rand()*100000,0),5,'0');  #补0

日期函数 进行日期操作的函数

MySQL常用的内置 日期函数如下

CURDATE()           返回当前日期（年月日）

CURTIME()            返回当前时间

NOW()                  返回当前日期和时间

YEAR(date)           获取指定date（年月日）的年份

MONTH(date)       获取指定date（年月日）的月份

DAY(date)             获取指定date（年月日）的天数

DATE_ADD(date , INTERVAL expr type)     在指定date（年月日）上增加expr（expr可以是年/月/日，具体是什么类型由type决定）

DATEDIFF(date1 , date2)     返回起始时间date1和结束时间date2之间的天数（date1-date2）

select date_add(now(),interval 20 day );    #当前日期的20天之后的日期（month为月，year为年）
select name,datediff(curdate(),entrydate) as 'date' from staff1 order by date desc;    #根据入职时间（entrydate）推算出入职天数，并按照倒序排序

转换函数 进行数据类型和值转换的函数

CAST(数据1 AS 数据类型1)      将数据1转为数据类型1

COALESCE(数据1 , 数据2 ,…… )

主要由两个用途：1、将NULL替换为其他的值

2、返回此数据列表中第一个非null表达式的值

select cast('001' as signed integer); #将字符串“001”转换为有符号的整数，显示结果为1
select coalesce(number,0) from staff1;   #查询staff1表的number字段，并将此字段的NULL值替换为0
select coalesce(number,age,0) from staff1; #查询staff1表的number字段；当number为null，age不为null时，number返回age的值；当number和age都为null时，返回0；当number为非null时，返回真实值

流程函数  进行条件删选

流程函数一般在SQL语句中实现条件筛选，从而提高语句的效率

MySQL常用的内置 流程函数如下

如果value（条件表达式）为true，则返回t，否则返回f

IF(vaule , t , f);                     

如果value1（可以是某个记录）不为空，则返回value1，负责返回value2

IFNULL(value1 , value2);      

如果val1为true，则返回res1；val2为true，返回res2；否则返回default

CASE

  WHEN [val1 ] THEN [res1]

  WHEN [val2] THEN [res2]

   ……

   ELSE [default] 

END;

如果espr的值等于val1，返回res1；espr的值等于val2，返回res2；否则返回default

CASE [expr]

  WHEN [val1] THEN [res1]  

  WHEN [val2] THEN[res2]

  ……

  ELSE [default]

END；

select name,(case origo when '重庆' then '新一线城市' else '非一线城市' end) from staff1; #查询staff1表的name和工作城市字段，如果工作城市为重庆，则显示为新一线城市，其余为非一线城市

聚合函数 进行数据聚合的函数

聚合函数的使用在DQL的分组查询有介绍

将一列数据作为一个整体，进行纵向计算（所有NULL值不参与计算）

count     统计数量（一列有多少个值）

max        最大值（一列的最大值）

min        最小值（一列的最小值）

avg         平均值（一列的平均值）

sum        求和（一列的所有数值求和）

聚合函数使用格式：

SELECT 聚合函数 (字段列表) AS [别名] FROM 表名 [WHERE条件列表];

select COUNT(*) from emp;   #统计这个表有多少行（由于Null不参与统计，可能每个列的统计数值不一致，其中会取最大值作为结果返回）
select max(age) from emp;   #统计age的最大值
select avg(age) from emp;   #统计age的平均值

谓词 一般用于条件判断

谓词的使用在DQL的条件查询有介绍

谓词，全名为比较谓词；是一种需要满足返回值为真值True的函数

BETWEEN … AND …     在某个范围之内（左闭右闭）

IN(…)                           …为多个信息，in(…)表示只要满足…中的某个信息就算匹配，为真值

NOT IN(…)                   …为多个信息，只要不满足…的所有信息就算匹配，为真值

LIKE 占位符               模糊匹配（_表示匹配单个字符、%表示匹配任意字符）（OR的简便用法）

IS NULL                       表示NULL值

IS NOT NULL               表示非NULL值

select * from staff1 where gender is null ;     #查询gender为null，并将其对应的所有字段的值都显示出来
select * from staff1 where gender is not null ; #查询gender为非null，并将其对应的所有字段的值都显示出来
select * from staff1 where gender in ('男','女') ;  #查询性别为男、或者性别为女的信息，并将其对应的所有字段的值都显示出来
select * from staff1 where name like '__'  ;      #查询名称为2个字符信息，并将其对应的所有字段的值都显示出来
select * from staff1 where number between 20000100 and 20000102 #查询number为20000100到20000102之间对应的信息，并将其对应的所有字段的值都显示出来

注意事项

对于IN来说，如果此运算符的两侧有一个为NULL,则返回结果只能是NULL或1；如果找不到匹配项则返回NULL；如果找到匹配项，则返回1；

对于NOT IN来说，如果此运算符的两侧有一个为NULL,则返回结果只能是NULL或0；如果找不到匹配项则返回NULL；如果找到匹配项，则返回0；

select null in (1,3) , null in (1,3,null); #结果为null 和 null
select 10 in (1,null), 10 in (null,10); #结果为null 和 1
select 10 in (1,20), 10 in (1,10);      #结果为0 和 1select null not in (1,3) , null not in (1,3,null); #结果为null 和 null
select 10 not in (1,null), 10 not in (null,10); #结果为null 和 0
select 10 not in (1,20), 10 not in (1,10);      #结果为1 和 0

MySQL基础语法（DDL、DQL、DML、DCL）_静下心来敲木鱼的博客-CSDN博客=1001.2014.3001.5502