MySQL入门快来看看MySQL函数就是这么简单
MySQL为我们提供了丰富的函数,所谓函数是对输入值返回一个特定运算结果的值,在数据管理和数据操作过程中不同场景下通常要使用不同的函数,例如:计算一个班级的平均成绩,统计电商平台的总金额等等,通过使用这些函数使得数据库的功能变的更加强大,MySQL提供了不同功能的函数,大致有以下几类:数学函数、字符串函数、日期时间函数、条件判断函数、系统信息函数、加密函数等其他函数。数学函数
在本小节我们将学习数学函数。获取绝对值
ABS(X):返回X的绝对值SELECT ABS(-3.14);
运行SQL语句后我们可以发现结果输出了3.14,获取了-3.14的绝对值。
我们也可以在数据表中的字段上使用此函数。我们创建表测试该函数。#创建表并添加测试数据 CREATE TABLE TEMP1(ID INT); INSERT INTO TEMP1 (ID) VALUES (10); INSERT INTO TEMP2 (ID) VALUES (-20); #测试ABS函数 SELECT ABS(ID) FROM TEMP1;
三角函数和获取圆周率
PI():返回圆周率
SIN(X):返回X的正弦值,其中X为弧度制
COS(X):返回X的余弦值,其中X为弧度制
TAN(X):返回X的正切,其中X为弧度制
COT(X):返回X的余切,其中X为弧度制SELECT PI(),SIN(PI()),COS(PI()),TAN(PI()),COT(PI());平方根函数和求余函数
SQRT(X):返回非负数X的二次方根,如果X为负数则会返回NULL
MOD(X,Y):返回X被Y除后的余数,MOD()对于带小数部分的数值也起作用,返回除法运算后的余数SELECT SQRT(4),SQRT(-4),MOD(5,3),MOD(5.3,3);向上取整和向下取整
CEIL(X):返回不小于X(或者说大于X)的最小整数值,即向上取整
CEILING(X):作用同上
FLOOR(X):返回不大于X(或者说小于)的最大整数值,即向下取整SELECT CEIL(3.14),CEIL(-3.14),FLOOR(3.14);获取随机数
RAND(x)返回一个随机浮点值v,范围在0到1之间(0 v 1.0)。若已指定一个整数参数x,则它被用作种子值,用来产生重复序列。select rand(),rand(),rand();
使用RAND()获取随机数时,可以发现每次获得的随机数都不相同
下面我们使用RAND(x)来获取随机数,从结果可以发现当参数相同时,产生的随机数是相同的。不同的参数产生的随机数不同。select rand(10),rand(10),rand(10);近似值函数
ROUND(x)返回最接近于参数x的整数,对x值进行四舍五入。
使用ROUND(x)对浮点数进行四舍五入取整SELECT ROUND(-3.14),ROUND(2.56),ROUND(1.14);
从结果可以看出,四舍五入处理后,只保留了各个值的整数部分。
ROUND(x,y)返回最接近于参数x的数,其值保留到小数点后面y位,若y为负值,则将保留x值到小数点左边y位,并且会四舍五入。SELECT ROUND(1.28,1),ROUND(1.38,0),ROUND(223.3,-1),ROUND(-1118.56,-2);
TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0,则结果不带有小数点或不带有小数部分。若y设为负数,则截去(归零)x小数点左起第y位开始后面所有低位的值。SELECT TRUNCATE(1.31,1),TRUNCATE(1.99,1),TRUNCATE(1.99,0),TRUNCATE(19.99,-1);
TRUNCATE(1.31,1)和TRUNCATE(1.99,1)都保留小数点后1位数字,返回值分别为1.3和1.9;TRUNCATE(1.99,0)返回整数部分值1;TRUNCATE(19.99,-1)截去小数点左边第1位后面的值,并将整数部分的1位数字置0,结果为10。
需要注意的是:y为负数时,保留小数点左边相应位数直接保存为0,不会四舍五入。幂运算函数
POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值。SELECT POW(2,5);
弧度函数与角度函数
RADIANS(x)将参数x由角度转化为弧度。SELECT RADIANS(90);
DEGREES(x)将参数x由弧度转化为角度。SELECT DEGREES(3.14);3.141592653589793238462643字符串函数
字符串函数主要用来处理数据库中的字符串数据。MySQL中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。本节将介绍各种字符串函数的功能和用法。获取字符串字符数
CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。LENGTH(str)返回值为字符串的字节长度,使用utf8(UNICODE的一种变长字符编码,又称万国码)编码字符集时,一个汉字是2字节,一个数字或字母算1字节。SELECT CHAR_LENGTH("张"),CHAR_LENGTH("DATE");拼接字符串
CONCAT(s1,s2,…)返回结果为连接参数产生的字符串,或许有一个或多个参数。如有任何一个参数为NULL,则返回值为NULL。如果所有参数均为非二进制字符串,则结果为非二进制字符串。SELECT CONCAT("Hello","World"),CONCAT("MY",NULL,"SQL");
在CONCAT_WS(x,s1,s2,…)中,CONCAT_WS代表CONCAT With Separator,是CONCAT()的特殊形式。第一个参数x是其他参数的分隔符,分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其他参数。如果分隔符为NULL,则结果为NULL。函数会忽略任何分隔符参数后的NULL值。SELECT CONCAT_WS("-","HELLO","WORLD"),CONCAT_WS(NULL,"HELLO","WORLD"),CONCAT_WS(",","HELLO",NULL,"WORLD");大小写字母转换
LOWER (str)或者LCASE (str)可以将字符串str中的字母字符全部转换成小写字母。SELECT LOWER("HELLO");
使用UPPER函数或者UCASE函数将字符串中所有字母字符转换为大写
删除空格
LTRIM(s)返回字符串s,字符串左侧空格字符被删除。SELECT CONCAT("(",LTRIM(" BOOK "),")");
RTRIM(s)返回字符串s,字符串右侧空格字符被删除。SELECT CONCAT("(",RTRIM(" BOOK "),")");
TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项,在未指定情况下,删除空格。SELECT CONCAT("(",TRIM(" BOOK "),")");
从结果中可以看出当未指定参数s时,删除了 两端的空格。SELECT TRIM("123" FROM "1234512345123");
从结果可以看出函数删除了两端的重复字符串123,并没有删除中间的重复字符串替换函数
REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。SELECT REPLACE("xxx.bytecollege.cn","x","w");
REPLACE()函数将字符串中的"x",全部替换成了"w";截取字符串
SUBSTRING(s,n,len)带有len参数的格式,从字符串s返回一个长度与len字符相同的子字符串,起始于位置n。也可能对n使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的n字符,即倒数第n个字符,而不是字符串的开头位置。SELECT SUBSTRING("HELLOWORLD",5),SUBSTRING("HELLOWORLD",2,5),SUBSTRING("HELLO",-3),SUBSTRING("HELLOWORLD",-3,2);
MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。
如果对len使用的是一个小于1的值,则结果始终为空字符串。
字符串逆序
REVERSE(s)将字符串s反转,返回的字符串的顺序和s字符串顺序相反。SELECT REVERSE("HELLO");日期和时间函数
日期和时间函数主要用来处理日期和时间值,一般的日期函数除了使用DATE类型的参数外,也可以使用DATETIME或者TIMESTAMP类型的参数,但会忽略这些值的时间部分。相同的,以TIME类型值为参数的函数,可以接受TIMESTAMP类型的参数,但会忽略日期部分,许多日期函数可以同时接受数字和字符串类型的两种参数,本节将介绍各种日期和时间函数的功能和用法。获取当前日期
CURDATE()和CURRENT_DATE()函数的作用相同,将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回
获取当前时间
CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE() 4个函数的作用相同,均返回当前日期和时间值,格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS
获取月份
MONTH(date)函数返回date对应的月份,范围值为1~12。SELECT MONTH("2020-02-13");获取星期
WEEK(d)计算日期d是一年中的第几周,取值范围是0-52;按周日为第一天。SELECT WEEK("2018-02-11");
WEEKOFYEAR(d)计算某天位于一年中的第几周,范围是1~53,周一为第一天。SELECT WEEKOFYEAR("2018-02-11");获取天数
DAYOFYEAR(d)函数返回d是一年中的第几天,范围是1~366。SELECT DAYOFYEAR("2018-02-11");
DAYOFMONTH(d)函数返回是d时间月份中的第几天。
获取年份、小时、分钟和秒钟的函数
YEAR(date)返回date对应的年份,范围是1970~2069。
HOUR(time)返回time对应的小时,范围是0-23;
MINUTE(time)返回time对应的分钟数,范围是0~59。
SECOND(time)返回time对应的秒数,范围是0~59。SELECT YEAR("2018-02-11 10:11:11"),MINUTE("2018-02-11 10:11:11"),SECOND("2018-02-11 10:11:11");计算日期和时间的函数
计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()
在DATE_ADD(date,INTERVAL expr type)和DATE_SUB(date,INTERVAL exprtype)中,date是一个DATETIME或DATE值,用来指定起始时间。expr是一个表达式,用来指定从起始日期添加或减去的时间间隔值。对于负值的时间间隔,expr可以以一个负号‘-’开头。type为关键词,指示了表达式被解释的方式。
将日期和时间格式化的函数
DATE_FORMAT(date,format)根据format指定的格式显示date值,主要format格式如表SELECT DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i:%s");条件判断函数
条件判断函数也称为控制流程函数,根据满足的不同条件,执行相应的流程。MySQL中进行条件判断的函数有IF、IFNULL和CASE。本节将分别介绍各个函数的用法。IF(expr,v1,v2)函数
IF(expr, v1, v2):如果表达式expr是TRUE(expr <> 0 and expr <> NULL),则返回值为v1;否则返回值为v2。IF()的返回值为数字值或字符串值SELECT IF(1>2,2,3),IF(1<2,"YES","NO"),IF("TEST"="TEST1","YES","NO");
IFNULL(v1,v2)函数
IFNULL(v1,v2):假如v1不为NULL,则IFNULL()的返回值为v1;否则其返回值为v2。IFNULL()的返回值是数字或者字符串根据实际情况决定SELECT IFNULL(1,10),IFNULL(NULL,10),IF(1/0,"WRONG");
IFNULL(1,2)虽然第二个值也不为空,但返回结果依然是第一个值;IFNULL(NULL,10)第一个值为空,因此返回10;"1/0"的结果为空,因此IFNULL(1/0, "wrong")返回字符串"wrong"。CASE函数
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]…[ELSE rn+1] END:如果expr值等于某个vn,则返回对应位置THEN后面的结果;如果与所有值都不相等,则返回ELSE后面的rn+1。SELECT CASE 2 WHEN 1 THEN "一" WHEN 2 THEN "二" ELSE "WRONG" END;
CASE后面的值为2,与第二条分支语句WHEN后面的值相等,因此返回结果为"二"。CASE WHEN v1 THEN r1 [WHEN v2 THEN r2]… ELSE rn+1] END:某个vn值为TRUE时,返回对应位置THEN后面的结果;如果所有值都不为TRUE,则返回ELSE后的rn+1系统信息函数获取MySQL版本号、连接数和数据库名的函数
VERSION()返回指示MySQL服务器版本的字符串
CONNECTION_ID()返回MySQL服务器当前连接的次数,每个连接都有各自唯一的ID。SELECT VERSION(),CONNECTION_ID();
获取用户名的函数
USER()、CURRENT_USER()、SYSTEM_USER()和SESSION_USER()这几个函数返回当前被MySQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的MySQL账户。一般情况下,这几个函数的返回值是相同的。SELECT USER(),CURRENT_USER(),SYSTEM_USER();获取字符串的字符集
CHARSET(str)返回字符串str自变量的字符集。SELECT CHARSET("ABC"),CHARSET(VERSION());
获取最后一个自动生成的ID值的函数
LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。CREATE TABLE STUDENT( ID INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ) INSERT INTO STUDENT (NAME) VALUES ("张三"); SELECT LAST_INSERT_ID();
查看已经插入的数据可以发现,最后一条插入的记录的Id字段值为2,使用LAST_INSERT_ID()查看最后自动生成的Id值:加密函数
加密函数主要用来对数据进行加密和界面处理,以保证某些重要数据不被别人获取。这些函数在保证数据库安全时非常有用。本节将介绍各种加密函数的作用和使用方法。加密函数MD5(str)
MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回,若参数为NULL,则会返回NULL。SELECT MD5("ADMIN");
加密函数SHA(str)
SHA(str)从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。SHA加密算法比MD5更加安全。SELECT SHA("ADMIN");