MySQL常用函数

MySQL常用函数

一、与数值相关的函数

(一) abs函数

1、  格式：select  abs(参数)；

2、  作用：求参数的绝对值

(二) ceil函数/ceiling函数

1、  格式：select  ceil（参数）；

2、  作用：向上取整，返回最小的整数

(三) floor函数

1、  格式：select  floor（参数）；

2、  作用：向下取整，返回最大的整数

(四) mod函数

1、  格式：select  mod（值1，值2）；

2、  作用：求值1除以值2后的余数

3、  注意：结果的符合和被除数相同，另外如果被除数小于除数，结果为被除数

(五) pow函数/power函数

1、  格式：select  pow（m，n）；

2、  作用：求m的n次方

(六) rand函数

1、  格式：select  rand()

2、  作用：生成0-1间的随机数，包含0，但是不包含1

3、  注意：如果要生成n-m，包含n、m的随机整数，可以使用如下格式select  floor(rand()*(最大值+1-最小值)+最小值)；

(七) round函数

1、  格式1：round(x)，返回参数X的四舍五入的一个整数

2、  格式2：round(x,d)，返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。如果d的中为负数，那么表示从小数点向前进行四舍五入，如select round(163.656, -2);结果为200

(八) sqrt函数

1、  格式；select  sqrt(x);

2、  作用：求x的算术平方根

(九) truncate函数

1、  格式：select  truncate（x,d）；

2、  作用：对x的小数位进行截取

3、  示例：

l  select truncate(123456.789,1);结果为123456.7

l  select truncate(123456.789,0);结果为123456

l  select truncate(123456.789,-2);结果为123400

二、与字符串相关的函数

(一) length函数

1、  作用：返回字符串的长度，以字节为单位，汉字占三个字节

2、  格式：select  length（’字符串’）；

(二) char_length函数

1、  作用：返回字符串的长度，以字符个数为单位

2、  格式：select  char_length(‘字符串’);

(三) concat函数

1、  作用：连接字符串，返回连接后的字符串

2、  格式：select  concat(字符串1，字符串2，字符串3….);

3、  注意：参数可以是字符串、数值、布尔值，如果是布尔值，true为1，false为0，如果参数由null，那么结果为null

(四) concat_ws函数

1、  作用：连接字符串，并且可以指定分隔符

2、  格式：select  concat_ws(‘分隔符’，字符串1，字符串2，字符串3…);

(五) left函数

1、  格式：left(str,x)；

2、  作用：返回str左边x个字符，x为null则返回null

(六) right函数

1、  格式：right(str,x)；

2、  作用：返回str右边x个字符，x为null则返回null

(七) substr函数/substring函数

1、  格式：substr(str, x，y)；

2、  作用：在字符串str中从x开始进行截取y个字符，注意substr函数中如果省略第三个参数，那么表示从x位置一直截取到最后

3、  注意该函数还可以写成如下格式：substr（str  from  x  for  y），作用同上

(八) lower函数/lcase函数

1、  格式：lower(str)；

2、  作用：将字符串转换为小写

(九) upper函数/ucase函数

1、  格式：upper(str)；

2、  作用：将字符串转换为大写

(十) instr函数

1、  格式：select  instr(str, s)；

2、  作用：返回s在字符串str中首次出现的位置，如果没有s返回0

(十一) insert函数

1、  格式：select  insert(str,x,y,insert)；

2、  作用：从str的x位置开始，替换y长度的字符串为insert

3、  示例：select insert('abcdef', 2, 3, '*');结果为a*ef

(十二) replace函数

1、  格式：replace(str,a,b)

2、  作用：在字符串str中用字符串b替换所有的字符串a，参数a可以是一个字符也可以是多个字符

(十三) trim函数

1、  格式：trim（字符串）

2、  作用：去掉字符串两端的空格

3、  示例：select concat('*', trim('   a b c   '), '*');

4、  注意：trim还可以有如下作用

l  trim(子串  from str)：表示将字符串str两侧的指定子串删除select trim('ab' from 'abcdefab');结果为cdef

l  trim（both  子串 from str）：作用同上

l  trim（leading  子串  from str）：删除左侧子串

l  trim（trailing  左侧  from  str）：删除右侧子串

(十四) ltrim函数

1、  格式：ltrim（字符串）

2、  作用：去掉字符串左侧空格

(十五) rtrim函数

1、  格式：rtrim（字符串）；

2、  作用：去掉字符串右侧空格

(十六) strcmp函数

1、  格式：strcmp(s1,s2)；

2、  作用：如果S1比S2小，返回-1；如果S1比S2大则返回1；如果相等则返回0（比较的是ASCII码）

3、  注意：字符串比较时也可以使用比较运算符符直接进行比较，如’a’>’b’

(十七) lpad函数

1、  格式：lpad(str,n,pad)；

2、  作用：用pad对字符串str从最左边进行填充，直到总长度达到n

3、  示例：select lpad('abc', 10, '*');结果为*******abc

4、  注意：如果长度n的值小于字符串str现有长度，那么会对字符串进行截取（从左往右截）

(十八) rpad函数

1、  格式：rpad(str,n,pad);

2、  作用：用pad对字符串str从最右边进行填充，直到总长度达到n

3、  示例：select rpad('abc', 10, '*');结果为* abc ******

4、  注意：如果长度n的值小于字符串str现有长度，那么会对字符串进行截取（从左往右截）

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

一、日期和时间函数

(一) curdate函数

1、  格式：select  curdate();

2、  作用：获取日期，如果在后面+0，则将日期转为数值型，如：select  curdate()+0;

(二) curtime函数

1、  格式：select  curtime();

2、  作用：获取时间

(三) now函数

1、  格式：select  now();

2、  作用：获取日期和时间

(四) unix_timestamp函数

1、  格式1：select  unix_timestamp();获取当前时间距离1970年1月1号的秒值

2、  格式2：select  unix_timestam(日期时间);获取参数距离1970年1月1号的秒值

(五) from_unixtime函数

1、  格式：from_unixtime(时间戳)

2、  作用：返回时间戳对应的日期和时间

(六) year函数

1、  格式：year（日期）；

2、  作用：获取日期中的年份

(七) month函数：语法同上

(八) day函数：语法同上

(九) week函数：语法同上，作用是获取当前日期是这一年的第几周

(十) hour函数：语法同上

(十一) minute函数：语法同上

(十二) second函数：语法同上

(十三) monthname：语法同上，作用是以英文单词的形式返回月份名称

(十四) date_format函数

1、  作用：以不同的格式显示日期/时间数据

2、  格式：select  date_format(日期时间，‘格式’);

3、  示例：select  date_format(now()，‘%Y-%M-%D  %H：%I：%S  %W’);

4、  常用符号如下

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(十五) date_add函数：

1、  作用：向日期添加指定的时间间隔

2、  格式：date_add(date,  interval expr type)，date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

 

3、  示例：select date_add(now(), interval 3 month);结果为：2019-01-25 15:15:37

(十六) datediff函数：

1、  作用：返回两个日期之间的时间

2、  格式：datediff(日期1，日期2)；即datediff(结束日期，起始日期)

3、  示例：select datediff('2008-12-29','2008-12-28');返回结果为1，如果第一个日期比第二个日期大，返回结果为正数，否则返回结果为负数

(十七) period_add函数

1、  格式：period_add（P，N）；

2、  作用：对日期P加或减N个月份

3、  注意：P的格式为“YYYYMM” 或者 “YYMM”

4、  示例：select period_add('201803',1);结果为201804

二、信息函数

(一) version函数

1、  作用：获取数据库的版本

2、  格式：select  version();

(二) database函数/schema函数

1、  作用：获取当前正在访问的数据库的名称

2、  格式：select  database();

(三) user函数

1、  作用：获取当前用户

2、  格式：select  user();

三、高级函数

(一) if函数

1、  格式：if（表达式1，表达式2，表达式3）；

2、  作用：如果表达式1成立，结果为表达式2的值，否则结果为表达式3的值

(二) ifnull函数

1、  格式：ifnull（表达式1，表达式2）；

2、  作用：判断表达式1是否为空，如果为空，结果为表达式2，否则结果为表达式1

(三) nullif函数

1、  格式：nullif（表达式1，表达式2）；

2、  作用：判断表达式1和表达式2是否相等，如果相等，结果为null，否则结果为表达式1

(四) isnull函数

1、  格式：isnull（表达式）；

2、  作用：如果表达式的值为null，那么结果为1，否则为0

(五) case：类似于JavaScript中的switch语句

1、  格式1：作为简单的函数使用，枚举这个字段所有可能的值（注意：else可以省略）

select  case  字段名 when 表达式1  then  值1

                                   when 表达式2  then  值2

                                   ….

                                   else  值

                                   end  from 表名；

1)     示例：

 

2)     可以起别名

 

2、  格式2：作为搜索函数使用，搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略

select  case  when  条件1  then  值1

                       when  条件2  then  值2

                       when  条件3  then  值3

                       else  值4

                       end  from  表名；

示例：

 

四、如何将外部数据导入到MySQL数据库中：利用Navicat软件将Excel（销售明细数据_201005）导入到数据库中，并查询：2010年5月份每天的销量及收入：select left(销售日期,8) as 销售日期,sum(销售数量) as 销量总和, sum(销售收入) as 收入总和 from selling where left(销售日期,6)='201005' group by left(销售日期,8);