MySQL笔记整理5 - 函数

content
函数
函数的应用

一、函数

1.1 数学函数

1.1.1 官方文档：https://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html

1.1.2 CEIL(X) 或 CEILING(X) 向上取整函数(取大于x的最小整数)

select CEIL(3.2); # 4
select CEIL(3); # 3
select CEIL(3.8) # 4
select CEIL(-3.2); # -3
select CEIL(-3.8); # -3
select CEILING(3.6); # 4

1.1.3 FLOOR(X) 向下取整函数(取小于x的最大整数)

select FLOOR(5.8); # 5
select floor(5.2); # 5
select FLOOR(-3.7); # -4
select FLOOR(-5.2); # -6

1.1.4 ROUND(X) 取近似值函数(四舍五入)

select ROUND(4.28); # 4
select ROUND(4.5); # 5
select ROUND(-2.8); # -3
select ROUND(-2.49); # -2

1.1.5 POW(X,Y) 或 POWER(X,Y) 幂运算函数(求x的y次方的结果)

select POW(2,3); # 8
select pow(8, -1); # 0.125
select POWER(2,4); # 16

1.1.6 MOD(N,M) 取模函数(取n除以m的余数)

select MOD(5,3); # 2
select MOD(10,6); # 4

1.1.7 RAND()随机数函数，产生[0,1)。每次调用随机产生一个数。

select RAND();

1.1.8 PI() π函数。返回π的值

select PI();

1.2 字符串函数

1.2.1 官方文档：https://dev.mysql.com/doc/refman/5.5/en/string-functions.html

1.2.2 LENGTH(str) 返回str字符串占用的字节长度

select LENGTH(‘a’);# 1
select LENGTH(‘ab’); # 2
select LENGTH(‘我’); # 3
select LENGTH(‘我是’); # 6

1.2.3 CHAR_LENGTH(str) 返回str字符串的字符个数

select CHAR_LENGTH(‘a’); # 1
select CHAR_LENGTH(‘abc’) # 3
select CHAR_LENGTH(‘我’); # 1
select CHAR_LENGTH(‘我是人工智能ai1809班’); # 13

1.2.4 SUBSTR(str FROM pos FOR len) 或 SUBSTR(str,pos,len)

SUBSTRING(str FROM pos FOR len) 或 SUBSTRING(str,pos,len)
字符串截取函数。
字符串中的字符的索引从左往右编就是从1开始
字符串中的字符的索引从右往左编就是从-1开始
pos：表示起始字符索引
len：截取的字符的长度，省略的时候表示截取到最后
select SUBSTR(‘hello world’ FROM 1 FOR 8); # hello wo
select SUBSTR(‘hello world’ FROM 3); # llo world
select SUBSTR(‘hello world’, 1, 8); # hello wo
select SUBSTR(‘hello world’, 3);# llo world
select SUBSTR(‘hello world’,-2,8); # ld
select SUBSTRING(‘hello world’ FROM 1 FOR 8); # hello wo
select SUBSTRING(‘hello world’, 1, 8); # hello wo

1.2.5 UPPER(str)或UCASE(str) 把str中的字符都转换为大写

select UPPER(‘hello’); # HELLO
select UCASE(‘Hello’); # HELLO

1.2.6 LOWER(str) 把str中的字符都转换为小写

select LOWER(‘Hello’); # hello
select LOWER(‘HELLO’); # hello

1.2.7 REPLACE(str,from_str,to_str) 替换str中的from_str为to_str

select REPLACE(‘hello’,‘l’,‘x’); # hexxo

1.2.8 TRIM(str) 去除str字符串两端的空格

select TRIM(’ hello world '); # hello world

1.2.9 LPAD(str,len,padstr) 左填充函数

在str字符串的左边填充padstr,填充完以后总长度是len
select LPAD(‘hello’,8,’*’); # *hello
select LPAD(‘hello’,5,’’); # hello
SELECT LPAD(‘hello’,3,’’); # hel

1.2.10 RPAD(str,len,padstr) 右填充函数

在str字符串的右边填充padstr,填充完以后总长度是len
select RPAD(‘hello’,8,’’); # hello**
select RPAD(‘hello’,5,’’); # hello
select RPAD(‘hello’,3,’’); # hel

1.3 日期函数

1.3.1 官方文档：https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

1.3.2 NOW() 或 SYSDATE() 获取当前时间（yyyy-mm-dd hh:mm:ss）

select NOW(); # 2018-11-20 11:01:18
select SYSDATE(); # 2018-11-20 11:01:18

1.3.2 CURRENT_DATE() 或 CURDATE() 获取当前日期(yyyy-mm-dd)

select CURRENT_DATE(); # 2018-11-20
select CURDATE(); # 2018-11-20

1.3.3 CURRENT_TIME() 或 CURTIME() 获取当前时间

select CURRENT_TIME(); # 11:06:38
select CURTIME(); # 11:06:38

1.3.4 YEAR(date) 获取date日期中的年份

select year(‘2016-01-13’); # 2016
select YEAR(NOW()); # 2018

1.3.5 MONTH(date) 获取date日期中的月份

select MONTH(‘2018-6-15’); # 6
select MONTH(NOW()); # 11

1.3.6 DAY(date) 获取date日期中的天

select DAY(‘2018-6-15’); # 15
select DAY(NOW()); # 20

1.3.7 LAST_DAY(date) 获取date日期的最后一天

select LAST_DAY(‘2018-6-15’); # 2018-06-30
select LAST_DAY(‘2018-2-15’); # 2018-02-28
select LAST_DAY(NOW()); # 2018-11-30

1.3.8 HOUR() MINUTE() SECOND()

1.3.9 DATE_ADD(date,INTERVAL expr unit) 增加日期

date 被修改的日期
INTERVAL 关键字，表示间隔
expr 间隔的值
unit 单位。即把间隔值修改到哪
select DATE_ADD(‘2018-12-13’,INTERVAL 2 MONTH); # 2019-02-13

1.3.10 DATE_SUB(date,INTERVAL expr unit) 减少日期

date 被修改的日期
INTERVAL 关键字，表示间隔
expr 间隔的值
unit 单位。即把间隔值修改到哪
select DATE_SUB(‘2018-12-13’,INTERVAL 2 MONTH); # 2018-10-13

1.4 (分组)聚合函数

1.4.1 官方文档：https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html

1.4.2 MAX(expr) 求一组数据中的最大值

求emp表中的最大工资
select MAX(sal) from emp; # 5000
求emp表中的最大工资以及他的名字
可以运行但结果不对
#select ename,MAX(sal) from emp;
直接报错where条件中的聚合函数没有查询的对象
#select ename,MAX(sal) from emp where sal=MAX(sal);

1.4.3 MIN(expr) 求一组数据中的最小值

求emp表中的最小工资
select MIN(sal) from emp;

1.4.4 AVG(expr) 求一组数据的平均值

求emp表中所有员工的平均工资
select AVG(sal) from emp;

1.4.5 SUM(expr) 求一组数据的和

求emp表中的所有员工的工资总和
select SUM(sal) from emp;

1.4.6 COUNT(expr) 求一组数据的个数

注意：expr可以是表中的列，表示的是该列不为null的记录个数
用*，表示记录的所有的列，就可正确的获取记录的总个数
select COUNT(sal) from emp;
select COUNT(comm) from emp;
select COUNT(*) from emp;

1.5 加密函数

1.5.1 官方文档：https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html

1.5.2 进行MD5加密

select MD5(‘hello’); # 5d41402abc4b2a76b9719d911017c592
select SHA1(‘hello’); # aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d
select PASSWORD(‘hello’); # *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119

二、 函数的应用

判断stu_info表中的年龄的奇偶性
select MOD(age,2) from stu_info;
查看stu_info表中的学生名字的长度
select CHAR_LENGTH(name) from stu_info;
在银行存储开户人的信息的时候，通过函数获取开户的日期然后插入
insert into bank(name,pwd,money,date) values(‘lucy’,‘567’,1000,‘2018-11-20 14:14:16’);
insert into bank(name,pwd,money,date) values(‘rose’,‘897’,2000,NOW());
在银行存储开户人的信息的时候，通过函数把用户的密码加密之后在插入
insert into bank(name,pwd,money,date) values(‘nancy’,PASSWORD(‘897’),5000,NOW());