第13章MySQL函数

第13章MySQL函数MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。例如，字符串连接函数可以很方便的将多个字符串连接在一起。教学目标：数学函数字符串函数日期和时间函数条件判断函数系统信息函数加密函数格式化函数教学内容：13.1MySQL函数简介MySQL函数是MySQL数据库提供的内部函数。这些内部函数可以帮助用户更加方便的处理表中的数据。本小节中将简单介绍MySQL中包含哪几类函数，以及这几类函数的的使用范围和作用。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数等。SELECT语句及其条件表达式都可以使用这些函数。同时，INSERT、UPDATE、DELECT语句及其条件表达式也可以使用这些函数。例如，表中的某个数据是负数，现在需要将这个数据显示为正数。这就可以使用绝对值函数。从上面可以知道，MySQL函数可以对表中数据进行相应的处理，以便得到用户希望得到的数据。这些函数可以使MySQL数据库的功能更加强大。13.2数学函数数学函数是MySQL中常用的一类函数。主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等。13.2.1绝对值函数ABS(x)和返回圆周率的函数PI()ABS(x)用来求绝对值；PI()用来返回圆周率。SELECTABS(0.5),ABS(-0.5),PI();13.2.2平方根函数SQRT(x)和求余函数MOD(x,y)SQRT(x)用来求平方根；MOD(x,y)用来求余数。【示例13-2】下面将演示SQRT(x)、MOD(x,y)这两个函数的使用。mysqlSELECTSQRT(16),SQRT(2),MOD(5,2);+--------------+---------------------------+--------------+|SQRT(16)|SQRT(2)|MOD(5,2)|+--------------+---------------------------+--------------+|4|1.4142135623731|1|+--------------+---------------------------+--------------+1rowinset(0.00sec)13.2.3获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)CEIL(x)和CEILING(x)这两个函数返回大于或等于x的最小整数；FLOOR(x)函数返回小于或等于x的最大整数。SELECTCEIL(2.3),CEIL(-2.3),CEILING(2.3),CEILING(-2.3);SELECTCEIL(2.3),CEIL(-2.3),CEILING(2.3),CEILING(-2.3);13.2.4获取随机数的函数RAND()和RAND(x)RAND()和RAND(x)这两个函数都是返回0～1的随机数。但是RAND()返回的数是完全随机的，而RAND(x)函数的x相同时返回的值是相同的。SELECTRAND(),RAND(),RAND(2),RAND(2);13.2.5四舍五入函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)ROUND(x)函数返回离x最近的整数，也就是对x进行四舍五入处理；ROUND(x,y)函数返回x保留到小数点后y位的值，截断时需要进行四舍五入处理；TRUNCATE(x,y)函数返回x保留到小数点后y位的值。SELECTROUND(2.3),ROUND(2.5),ROUND(2.53,1),ROUND(2.55,1);CATESELECTTRUNCATE(2.53,1),TRUNCATE(2.55,1);13.2.6符号函数SIGN(x)SIGN(x)函数返回x的符号，x是负数、0、正数分别返回-1、0、1。SELECTSIGN(-2),SIGN(0),SIGN(2);13.2.7幂运算函数POW(x,y)、POWER(x,y)和EXP(x)POW(x,y)和POWER(x,y)这两个函数计算x的y次方，即xy；EXP(x)函数计算e的x次方，即ex。SELECTPOW(3,2),POWER(3,2),EXP(2);13.2.8对数运算函数LOG(x)和LOG10(x)函数LOG(x)函数计算x的自然对数；LOG10(x)函数计算以10为底的对数。其中，EXP(x)和LOG(x)这两个函数互为反函数。SELECTLOG(7.38905609893065),LOG10(100);13.2.9角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)RADIANS(x)函数将角度转换为弧度；DEGREES(x)函数将弧度转换为角度。这两个函数互为反函数。SELECTRADIANS(180),DEGREES(3.1415926);13.2.10正弦函数SIN(x)和反正弦函数ASIN(x)SIN(x)函数用来求正弦值，其中x是弧度；ASIN(x)函数用来求反正弦值。ASIN(x)中x的取值必须在-1到1之间。否则返回的结果将会是NULL。SELECTSIN(0.5253598775598),ASIN(0.5);13.2.11余弦函数COS(x)和反余弦函数ACOS(x)COS(x)函数用来求余弦值，其中x是弧度；ACOS(x)函数用来求反余弦值。COS(x)和ACOS(x)互为反函数。并且，ACOS(x)中x的取值必须在-1到1之间。否则返回的结果将会是NULL。SELECTCOS(1.04719),ACOS(0.5);13.2.12正切函数、反正切函数和余切函数TAN(x)函数用来求正切值，其中x是弧度；ATAN(x)和ATAN2(x)用来求反正切值；COT(x)函数用来求余切值。TAN(x)与ATAN(x)、ATAN2(x)互为反函数。而且TAN(x)返回值是COT(x)返回值的倒数。SELECTTAN(0.78398),ATAN(1),ATAN2(1);SELECTCOT(1),1/TAN(1);13.3字符串函数字符串函数是MySQL中最常用的一类函数。字符串函数主要用于处理表中的字符串。字符串函数包括求字符串长度、合并字符串、在字符串中插入子串、大小字母之间切换等函数。13.3.1计算字符串字符数的函数和字符串长度的函数CHAR_LENGTH(s)函数计算字符串s的字符数；LENGTH(s)函数计算字符串s的长度。SELECTS,CHAR_LENGTH(S),LENGTH(S)FROMt2;13.3.2合并字符串的函数CONCAT(s1,s2,…)和CONCAT_WS(x,s1,s2,…)CONCAT(s1,s2,…)函数和CONCAT_WS(x,s1,s2,…)函数都可以将s1、s2等多个字符串合并成一个字符串。但CONCAT_WS(x,s1,s2,…)可以将各字符串直接用参数x隔开。SELECTCONCAT('bei','ji','ng'),CONCAT_WS('-','bei','ji','ng');13.3.3替换字符串的函数INSERT(s1,x,len,s2)INSERT(s1,x,len,s2)函数将字符串s1中x位置开始长度为len的字符串用s2替换。SELECTs,INSERT(s,4,4,'FANG')FROMt2;13.3.4字母大小写转换函数UPPER(s)函数和UCASE(s)函数将字符串s的所有字母变成大写字母；LOWER(s)函数和LCASE(s)函数将字符串s的所有字母变成小写字母。SELECTUPPER('mysql'),UCASE('mysql'),LOWER('MYSQL'),LCASE('MYSQL');13.3.5获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)LEFT(s,n)函数返回字符串s的前n个字符；RIGHT(s,n)函数返回字符串s的后n个字符。SELECTs,LEFT(s,3),RIGHT(s,3)FROMt2;13.3.6填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)LPAD(s1,len,s2)函数将字符串s2填充到s1的开始处，使字符串长度达到len；RPAD(s1,len,s2)函数将字符串s2填充到s1的结尾处，使字符串长度达到len。SELECTs,LPAD(s,10,'+-'),RPAD(s,10,'+-');13.3.7删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)LTRIM(s)函数将去掉字符串s开始处的空格；RTRIM(s)函数将去掉字符串s结尾处的空格；TRIM(s)函数将去掉字符串s开始处和结尾处的空格。SELECTCONCAT('+','me','+'),CONCAT('+',LTRIM('me'),'+');SELECTCONCAT('+',RTRIM('me'),'+'),CONCAT('+',TRIM('me'),'+');13.3.8删除指定字符串的函数TRIM(s1FROMs)TRIM(s1FROMs)函数将去掉字符串s中开始处和结尾处的字符串s1。【示例13-20】下面将演示TRIM(s1FROMs)函数的使用。mysqlSELECTTRIM('ab'FROM'ababddddabddab');+-------------------------------------------------+|TRIM('ab'FROM'ababddddabddab')|+-------------------------------------------------+|ddddabdd|+-------------------------------------------------+1rowinset(0.00sec)13.3.9重复生成字符串的函数REPEAT(s,n)REPEAT(s,n)函数将字符串s重复n次。【示例13-21】下面将演示REPEAT(s,n)函数的使用。mysqlSELECTREPEAT('mysql-',2);+---------------------------+|REPEAT('mysql-',2)|+---------------------------+|mysql-mysql-|+---------------------------+1rowinset(0.05sec)13.3.10空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)SPACE(n)函数返回n个空格；REPLACE(s,s1,s2)函数将字符串s2替代字符串s中的字符串s1。SELECTCONCAT('+',SPACE(4),'+'),REPLACE('mysql','sql','book');13.3.11比较字符串大小的函数STRCMP(s1,s2)STRCMP(s1,s2)函数用来比较字符串s1和s2。如果s1大于s2，结果返回1；如果s1等于s2，结果返回0；如果s1小于s2，结果返回-1。SELECTSTRCMP('abc','abb'),STRCMP('ABC','ABC'),STRCMP('abc','aba');13.3.12获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)SUBSTRING(s,n,len)函数和MID(s,n,len)函数从字符串s的第n个位置开始获取长度为len的字符串。【示例13-24】下面将演示SUBSTRING(s,n,len)函数和MID(s,n,len)函数的使用。mysqlSELECTs,SUBSTRING(s,4,3),MID(s,4,3)FROMt2;+---------+--------------------------