数据库基础之函数

一、字符函数

1、拼接字符串

concat（str1,str2,...sn）//返回结果为连接参数产生的字符串，如果任何一个参数为NULL，则返回值为NULL
concat_ws （x , s1 , s2 ,....）//第一个参数 x 是其他参数的分隔符，分隔符可以是一个字符串，也可以是其他参数 
repeat( str,x ) //返回str重复x次的结果

案例

 select repeat('abc',3) //abcabcabc

2、截取字符串

left（str,x） //返回字符串str最左边的x个字符
right（str,x） //返回字符串str最右边的x个字符
substring（str，x,y） //返回从字符串str x位置起y个字符长度的字符
//如果n是负数。则字符串的截取起始位置从字符串结尾开始

案例

select right('12345',3)//345
select left('12345',3)//123
select substring('123456',2,3)//234
select substring('123456','-1',3);//6

3、字符串的比较

strcmp(s1,s2)  //比较字符串s1和s2的ASCII，如果s1小返回-1，如果s1大返回1，如果两这相等，返回0

案例

select strcmp('1234','1234567')//-1

4、字符串的替换

insert(str ,x,y,instr) //将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
replace（str,a,b） 用字符串b替换字符串str中所有出现的字符串a

案例

 select insert('1234567',4,1,'wo');//123wo567
 select replace('oldstring','old','new');//newstring

5、字符串的填充

lpad( str,n,pad  ) //用字符串pad对str最左边进行填充，直到长度为n个字符串长度
rpad( str,n,pad  ) //用字符串pad对str最右边进行填充，直到长度为n个字符串长度

select lpad('temp',10,'ad')//adadadtemp
select rpad('hello',10,'wg');//hellowgwgw

6、空格去除

ltrim( str ) //去掉字符串str左侧的空格
rtrim( str ) //去掉字符串str右侧的空格
trim（str） //去掉字符串str两侧空格

7、字符串长度

length( str ) //返回字符串str的字符长度，包括前后空格

案例

select length(' ab  ')//5

8、大小写转换

lower（str） //将字符串str中所有字符变为小写

upper(str ) //将字符串str中所有字符变为大写

select lower('HELLO') //hello
select upper('world') //WORLD

二、数值函数

1、取近似值

ceil (n) //向上取整
floor (n) //向下取整
round(n) //四舍五入
round( n，m ) //其值保留到小数点后面的m位，.若m为负，则保留n值到小数点左边m位，返回值是整型或浮点型
truncate( n,m ) //返回值是整型或浮点型
//返回被舍去至小数点后m位的数字n，
//若m的值为0，则结果为整数若m的值为负数，则截去n小数点左起第m位开始后面所有低位的值

案例

select round(24.4)//24
select  floor(24.7)//24
select  ceil(24.1)//25

select round(28.55,1)//28.6
select round(28.55,0)//29
select round(28.55,-1)//30

select truncate(28.55,1)//28.5
select truncate(28.55,0)//28
select truncate(28.55,-1)//20

2、取模，绝对值和随机数

mod（n,m） 取模函数，同n%m
abs (n）n的绝对值
rand（） 0~1内的随机数

案例

select mod(22,10) //2
select abs(-1) //1
select rand() //0.17553753794686638

三、流程函数

1、IF

IF(expr1,t,f)	//如果expr1是真, 返回t, 否则返回f

IFNULL(t,f)	//如果t不是NULL,返回t, 否则返回 f

2、WHEN

CASE 
WHEN [value1] THEN[result1]
… 
ELSE[default] 
END	
//如果value是真, 返回result1,否则返回default

四、加密函数

1、可逆转

encode()//加密，使用key作为密钥加密字符串str，结果是一个二进制字符串，它以BLOB类型存储
decode() //解密。使用key作为密钥解密加密字符串str

案例

SELECT ENCODE('123456','key')  //�mL�l�         
SELECT DECODE(ENCODE('123456','key'),'key') //123456

2、不可逆转加密

//加密字符串，加密后的值以32位16进制数字的二进制字符串形式返回，
//若参数为NULL，则返回NULL
MD5()

//创建一个经过加密的密码字符串，适合于插入到MySQL的安全系统。
//该加密过程不可逆，主要用于MySQL的认证系统。
//若参数为NULL，则返回NULL
PASSWORD（）

案例

SELECT MD5('123456')//e10adc3949ba59abbe56e057f20f883e  
SELECT MD5('123456') = MD5('123456') //1

SELECT PASSWORD(1234) = PASSWORD(1234) //1
SELECT PASSWORD(1234) //*A4B6157319038724E3560894F7F932C8886EBFCF 

五、聚合函数

聚合函数会把每组查询值聚合成一行

MAX（）//返回某列的最大值

MIN（） //返回某列的最小值

AVG()    //返回某列的平均值

SUM（） //返回某列值的和

COUNT()  //返回某列的行数

六、日期时间函数

1、返回当前时间

//将当前日期按照 YYYY-MM-DD或 YYYYMMDD格式的值返回
//具体格式根据函数在字符串或者数字语境中而定
curdate() 
current_date()


//将当前时间以 HH:MM:SS或者HHMMSS的格式返回
//具体格式根据函数在字符串或者数字语境中而定
curtime()
current_time()

now()//返回的是整条语句执行时的时间
sysdate()//返回的是sysdate()函数执行时的时间
//返回格式为YYYY-MM-DD HH:MM:SS 
//或者YYYYMMDDHHMMSS具体格式根据函数在字符串或者数字语境中而定

案例1

select curdate() //2018-11-25
select current_date(); //2018-11-25

select curtime()//00:02:32
select current_time() //00:02:32 

案例2

mysql> select now(), sleep(2),sysdate();

+---------------------+----------+---------------------+
| now()               | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2018-11-24 23:51:05 |        0 | 2018-11-24 23:51:07 |
+---------------------+----------+---------------------+

mysql> select sysdate(),sleep(2),now();

+---------------------+----------+---------------------+
| sysdate()           | sleep(2) | now()               |
+---------------------+----------+---------------------+
| 2018-11-24 23:52:14 |        0 | 2018-11-24 23:52:14 |
+---------------------+----------+---------------------+

2、日期格式化

date_fromat(date,format) //根据format指定的格式显示date值

SELECT DATE_FORMAT('2018-9-4','%Y-%m');  #2018-09


%b : 月份的缩写名称（ Jan...Dec）

SELECT DATE_FORMAT('2018-9-26','%b');  #Sep

%c : 月份的数字形式（1，2，..12）

SELECT DATE_FORMAT('2018-9-26','%c');  #9

%m : 月份的数字形式（01，02，..12）

SELECT DATE_FORMAT('2018-9-26','%m');  # 09

%M : 月份英语名称

SELECT DATE_FORMAT('2018-9-26','%M');  #September

%d : 该月日期，数字形式(01,02,03,04，.22... )

SELECT DATE_FORMAT('2018-9-4','%d');  #04

%e : 该月日期，数字形式(1,2,3,4，..22.. )


SELECT DATE_FORMAT('2018-9-4','%e');  #4


%Y : 4位数形式表示年份


SELECT DATE_FORMAT('2018-9-4','%Y');  #2018


%y : 2位数形式表示年份


SELECT DATE_FORMAT('2018-9-4','%y');  #18

3、日期时间详细信息

week(date) 返回所给的日期是一年中的第几周

year(date) 返回所给日期是哪一年

hour(time) 所给日期的小时

minute(time) 所给日期的分钟

mysql> select
now(),
week(now()),
year(now()),
hour(now()),
minute(now());

±--------------------±------------±------------±------------±--------------+
| now() | week(now()) | year(now()) | hour(now()) | minute(now()) |
±--------------------±------------±------------±------------±--------------+
| 2018-11-25 13:33:41 | 47 | 2018 | 13 | 33 |
±--------------------±------------±------------±------------±--------------+

monthname(date) 返回date月份的英文名

mysql> select monthname(now());

±-----------------+
| monthname(now()) |
±-----------------+
| November |
±-----------------+

unix_timestamp(date) 返回日期date的unix时间戳

from_unixtime( unixtime ) 返回unxitime时间戳的日期值

时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总毫秒数。通俗的讲， 时间戳是一份能够表示一份数据在一个特定时间点已经存在的完整的可验证的数据。 它的提出主要是为用户提供一份电子证据， 以证明用户的某些数据的产生时间。 在实际应用上， 它可以使用在包括电子商务、 金融活动的各个方面， 尤其可以用来支撑公开密钥基础设施的 “不可否认” 服务
mysql> select unix_timestamp(now()) ,from_unixtime(1543123563);

±----------------------±--------------------------+
| unix_timestamp(now()) | from_unixtime(1543123563) |
±----------------------±--------------------------+
| 1543123604 | 2018-11-25 13:26:03 |
±----------------------±--------------------------+

4、日期时间之间的运算

date_add( date , INTERVAL expr type)
//date 是一个DATETIME或者DATE值，用来指定起始时间是一个字符串
//expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值
//type为关键字，他指示了表达式被解释的方式 如 YEAR,MONTH,DAY,WEEK,HOUR

MYSQL中的日期间隔类型

表达式类型 描述 格式
+-------------+--------------+---------------+
| 表达式类型   | 描述         |   格式         |
+-------------+--------------+---------------+
| HOUR        |     小时     |    hh         |
| MINUTE      |     分       |    mm         |
| SECOND      |     秒       |    ss         |
| YEAR        |     年       |    YY         |
| MONTH       |     月       |    MM          |
| DAY         |     日       |    DD          |
| YEAR_MONTH  |   年和月      |   YY-MM       |
| DAY_HOUR    |   日和小时    |   DD hh       |
| DAY_MINUTE  |   日和分钟    |   DD hh:mm     |
| DAY_SECOND  |   日和秒      |   DD hh:mm:ss  |
| HOUR_SECOND |   小时和秒    |   hh:ss        |
+-------------+--------------+---------------+

SELECT DATE_ADD('2018-10-10',INTERVAL 1 YEAR );  #2019-10-10


SELECT DATE_ADD('2018-10-10',INTERVAL -1 YEAR ); #2017-10-10

datediff（date1 , date2）// 返回date1减date2的天数

mysql> select datediff('1993-09-26',now());

+------------------------------+
| datediff('1993-09-26',now()) |
+------------------------------+
|                        -9191 |
+------------------------------+