MySql结构语言 ————函数

1.数学函数
　　数学函数用于执行一些比较复杂的算术操作。MySQL支持很多的数学函数。若发生错误，所有的数学函数都会返回NULL。
　　（1）greatest()和least()函数
　　是数学函数中经常使用的函数，它们的功能是获得一组数中的最大值和最小值。例如：SELECTGREATEST(10,9,128,1),LEAST(1,2,3);
　　数学函数还可以嵌套使用，例如：
　　SELECT GREATEST(-2,LEAST(0,3)),LEAST(1,GREATEST(1,2));
　　注意：MySQL不允许函数名和括号之间有空格。
　　（2）FLOOR()和CEILING()函数
　　floor()用于获得小于一个数的最大整数值，ceiling()函数用于获得大于一个数的最小整数值，例如：
　　SELECT FLOOR(-1.2), CEILING(-1.2),FLOOR(9.9), CEILING(9.9);
　　结果为：-2,-1,9,10;
　　（3）ROUND()和TRUNCATE()函数
　　round()函数用于获得一个数的四舍五入的整数值：
　　SELECTROUND(5.1),ROUND(25.501),ROUND(9.8);
　　结果为：5,26,10;
　　truncate()函数用于把一个数字截取为一个指定小数个数的数字，逗号后面的数字表示指定小数的个数：
　　SELECT TRUNCATE(1.54578, 2),TRUNCATE(-76.12, 5);
　　结果为：1.54,-76.12000;
　　（4）ABS()函数:用来获得一个数的绝对值
　　（5）sign()函数
　　返回数字的符号，返回的结果是正数（1）、负数（-1）或者零（0)：
　　SELECT SIGN(-2),SIGN(2),SIGN(0);
　　结果为：-1,1,0;
　　（6）sqrt()函数返回一个数的平方根
　　（7）POW()函数
　　pow()函数以一个数作为另外一个数的指数，并返回结果：
　　SELECTPOW(2,2),POW(10, -2),POW(0,3);
　　结果为：4,0.01,0；
　　（8）SIN()、COS()、TAN()、ASIN()、ACOS()和ATAN()函数
　　分别返回一个角度（弧度）的正弦、余弦、正切、反正弦、反余弦和反正切值
　　如果使用的是角度而不是弧度，可以使用degrees()和radians ()函数进行转换。
　　（9）BIN()、OTC()和HEX()函数
　　BIN()、OTC()和HEX()函数分别返回一个数的二进制、八进制和十六进制值，这个值作为字符串返回；
　　2.聚合函数
　　MySQL有一组函数是特意为求和或者对表中的数据进行集中概括而设计的。这一组函数就叫做聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。通过把聚合函数（如COUNT和SUM）添加到带有一个GROUPBY子句的SELECT语句块中，数据就可以聚合。聚合意味着是求一个和、平均、频次及子和，而不是单个的值。
　　3. 字符串函数
　　因为MySQL数据库不仅包含数字数据，还包含字符串，因此MySQL有一套为字符串操作而设计的函数。在字符串函数中，包含的字符串必须要用单引号括起。
　　（1）ASCII()函数
　　语法格式： ASCII (char)
　　返回字符表达式最左端字符的ASCII值。参数char的类型为字符型的表达式，返回值为整型。
　　例： 返回字母A的ASCII码值。SELECTASCII('A'); 结果为：65
　　（2）CHAR()函数
　　语法格式： CHAR (x1,x2,x3,…)
　　将x1、x2……的ASCII码转换为字符，结果组合成一个字符串。参数x1，x2，x3……为介于0～255之间的整数，返回值为字符型。
　　例： 返回ASCII码值为65、66、67的字符，组成一个字符串。
　　SELECTCHAR(65,66,67); 结果为：ABC
　　（3）LEFT和RIGHT函数
　　语法格式： left | right ( str ,x )
　　分别返回从字符串str左边和右边开始指定x个字符。
　　例： SELECT LEFT(课程是什么, 2)
　　执行结果为：课程；
　　（4）TRIM、LTRIM 和RTRIM函数（删除）
　　语法格式： trim | ltrim |rtrim(str)
　　使用LTRIM和RTRIM分别删除字符串中首（左边）空格和尾（右边）空格，返回值为字符串。参数str为字符型表达式，返回值类型为varchar。TRIM删除字符串首部和尾部的所有空格。
　　（5）RPAD和LPAD函数（填补）
　　语法格式： rpad |lpad( str, n, pad)
　　使用RPAD和LPAD分别用字符串pad对字符串str的右边和左边进行填补直至str中字符数目达到n个，最后返回填补后的字符串。若str中的字符个数大于n，则返回str的前n个字符。
　　例： 执行如下语句：
　　SELECT RPAD('中国加油',8, '!'),LPAD('welcome',10, '*');
　　结果为：中国加油！！！！（共八个），***welcome（共十个）；
　　（6）REPLACE函数
　　语法格式： replace (str1 , str2 , str3 )
　　REPLACE函数用于用字符串str3替换str1中所有出现的字符串str2。最后返回替换后的字符串。
　　例： 执行如下语句：
　　SELECTREPLACE('Welcome to CHINA','o', 'K');
　　结果为：WelcKme tK CHINA
　　（7）CONCAT函数
　　语法格式： concat(s1,s2,…sn)
　　CONCAT函数用于连接指定的几个字符串。
　　例： 执行如下语句：SELECT CONCAT('中国', '加油');
　　结果为：中国加油
　　（8）SUBSTRING函数
　　语法格式： substring (expression , Start, Length )
　　返回expression中指定的部分数据。参数expression可为字符串、二进制串、text、image字段或表达式。Start、Length均为整型，前者指定子串的开始位置，后者指定子串的长度（要返回字节数）。如果 expression是字符类型和二进制类型，则返回值类型与expression的类型相同。如果为text类型，返回的是varchar类型。
　　例： 如下程序在一列中返回XS表中所有女同学的姓氏，在另一列中返回名字。
　　USEXSCJ；SELECT SUBSTRING(姓名,1,1) AS 姓, SUBSTRING(姓名, 2,LENGTH(姓名)-1) AS 名
　　FROM XS WHERE 性别=0 ORDER BY 姓名;
　　说明：LENGTH函数的作用是返回一个字符串的长度。
　　（9）STRCMP函数
　　语法格式： strcmp (s1,s2)
　　STRCMP函数用于比较两个字符串，相等返回0，s1大于s2返回1，s1小于s2返回-1。
　　例： 执行如下语句： SELECT STRCMP('A', 'A'), STRCMP('ABC', 'OPQ'),STRCMP('T', 'B');
　　结果为：0，-1，1；
　　4. 日期和时间函数
　　MySQL有很多日期和时间数据类型，所以有相当多的操作日期和时间的函数。
　　（1）NOW()
　　使用NOW()函数可以获得当前的日期和时间，它以YYYY-MM-DD HH∶MM∶SS的格式返回当前的日期和时间：
　　（2）CURTIME()和CURDATE()
　　curtime()和curdate()函数比NOW更为具体化，它们分别返回的是当前的时间和日期，没有参数：
　　（3）YEAR()
　　YEAR()函数分析一个日期值并返回其中关于年的部分：
　　SELECTYEAR(20080512142800),YEAR('1982-11-02');
　　结果为：2008,1982；
　　（4）MONTH()和MONTHNAME()
　　month()和monthname()函数分别以数值和字符串的格式返回月的部分：
　　（5）DAYOFYEAR()，DAYOFWEEK()和DAYOFMONTH()
　　dayofyear()，DAYOFWEEK()和DAYOFMONTH()函数分别返回这一天在一年、一星期及一个月中的序数：
　　（6）DAYNAME()
　　和MONTHNAME()相似，dayname()以字符串形式返回星期名：
　　（7）WEEK()和YEARWEEK()
　　WEEK()函数返回指定的日期是一年的第几个星期，而YEARWEEK()函数返回指定的日期是哪一年的哪一个星期：
　　（8）HOUR()、MINUTE()和SECOND()
　　HOUR()，MINUTE()和SECOND()函数分别返回时间值的小时、分钟和秒的部分：
　　（9）DATE_ADD()和DATE_SUB()
　　DATE_ADD()和DATE_SUB()函数可以对日期和时间进行算术操作，它们分别用来增加和减少日期值，其使用的关键字如下表所示。
关 键 字间隔值的格式关 键 字间隔值的格式DAY日期MINUTE分钟DAY_HOUR日期∶小时MINUTE_ SECOND分钟∶秒DAY_MINUTE日期∶小时∶分钟MONTH月DAY_SECOND日期∶小时∶分钟∶秒SECOND秒HOUR小时YEAR年HOUR_MINUTE小时∶分钟YEAR_MONTH年-月HOUR_ SECOND小时∶分钟∶秒　　DATE_ADD()和DATE_SUB()函数的语法格式为：
　　DATE_ADD | DATE_SUB(date, INTERVAL int keyword)
　　date是需要的日期和时间，INTERVAL关键字表示一个时间间隔。int表示需要计算的时间值，keyword已经在上表中列出。DATE_ADD函数是计算date加上间隔时间后的值，DATE_SUB则是计算date减去时间间隔后的值。
　　举例： SELECT DATE_ADD('1986-08-08',INTERVAL 17 DAY);
　　5. 加密函数（非重点）
　　MySQL特意设计了一些函数对数据进行加密。这里简单介绍如下几个函数。
　　（1）AES_ENCRYPT和AES_DECRYPT函数
　　语法格式为：aes_encrypt aes_decrypt(str,key)
　　AES_ENCRYPT函数返回的是密钥key对字符串str利用高级加密标准（AES）算法加密后的结果，结果是一个二进制的字符串，以BLOB类型存储。而AES_DECRYPT函数用于对用高级加密方法加密的数据进行解密。若检测到无效数据或不正确的填充，函数会返回NULL。AES_ENCRYPT和AES_DECRYPT函数可以被看做MySQL中普遍使用的最安全的加密函数。
　　（2）ENCODE和DECODE函数
　　语法格式为： encode |deCODE(str,key)
　　ENCODE函数用来对一个字符串str进行加密，返回的结果是一个二进制字符串，以BLOB类型存储。DECODE函数使用正确的密钥对加密后的值进行解密。与上面的AES_ENCRYPT和AES_DECRYPT函数相比，这两个函数加密程度相对较弱。
　　（3）ENCRYPT函数
　　使用UNIX crypt()系统加密字符串，encrypt(str，salt)函数接收要加密的字符串和用于加密过程的salt（一个可以确定唯一口令的字符串）。在Windows上不可用。
　　（4）PASSWORD函数
　　格式为： password(str)
　　返回字符串str加密后的密码字符串，适合于插入到MySQL的安全系统。该加密过程不可逆，和UNIX密码加密过程使用不同的算法。主要用于MySQL的认证系统。
　　例： 返回字符串“MySQL”的加密版本。
　　SELECTPASSWORD('MySQL');
　　6. 控制流函数
　　MySQL有几个函数是用来进行条件操作的。这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。
　　（1）IFNULL和NULLIF函数
　　IFNULL函数的语法格式为： IFNULL(expr1,expr2)
　　此函数的作用是：判断参数expr1是否为NULL，当参数expr1为NULL时返回expr2，不为NULL时返回expr1。IFNULL的返回值是数字或字符串。
　　例： 执行如下语句：
　　SELECT IFNULL(1,2), IFNULL(NULL, 'MySQL'), IFNULL(1/0,10);
　　结果为：1，MySQL，10.0000；
　　NULLIF函数的语法格式为：NULLIF(expr1,expr2)
　　NULLIF函数用于检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等就返回第一个参数。
　　例： 执行如下语句：
　　SELECTNULLIF(1,1), NULLIF('A', 'B'), NULLIF(2+3, 3+4);
　　结果为：null，A，5；
　　（2）IF函数
　　和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试。
　　语法格式如下： IF(expr1,expr2,expr3)
　　这个函数有3个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数；如果为假，IF()将会返回第三个参数。
　　例： 判断2*4是否大于9–5，是则返回“是”，否则返回“否”。
　　SELECTIF(2*4>9–5, '是', '否'); 结果为：是；
　　例：返回XS表名字为两个字的学生姓名、性别和专业名。性别值如为0则显示为“女”，为1则显示为“男”。
　　SELECT 姓名, IF(性别=0, '女', '男') AS 性别, 专业名
　　 FROMXS WHERE姓名LIKE '__';
　　7. 格式化函数
　　MySQL还有一些函数是特意为格式化数据设计的。
　　（1）FORMAT()函数
　　语法格式为： format(x, y)
　　FORMAT()函数把数值格式化为以逗号间隔的数字序列。FORMAT()的第一个参数x是被格式化的数据，第二个参数y是结果的小数位数。
　　例如： SELECT FORMAT(111111.23654,2), FORMAT(-5468,4);
　　结果为：111111.23,-5468.0000；
　　（2）DATE_FORMAT()和TIME_FORMAT()函数
　　用来格式化日期和时间值。语法格式如下：
　　DATE_FORMAT/TIME_FORMAT(date | time, fmt)
　　其中，date和time是需要格式化的日期和时间值，fmt是日期和时间值格式化的形式，下表列出了MySQL中的日期/时间格式化代码。
关 键 字间隔值的格式关 键 字间隔值的格式%a缩写的星期名（Sun，Mon…）%pAM或PM%b缩写的月份名（Jan，Feb…）%r时间，12小时的格式%d月份中的天数%S秒（00，01）%H小时（01，02…）%T时间，24小时的格式%I分钟（00，01…）%w一周中的天数（0，1）%j一年中的天数（001，002…）%W长型星期的名字（Sunday，Monday…）%m月份，2位（00,01…）%Y年份，4位%M长型月份的名字（January，February）　　举例：SELECT DATE_FORMAT(NOW(), '%W,%d,%M,%Y %r');
　　注意：这两个函数是对大小写敏感的。
　　（3）INET_NTOA()和INET_ATON()函数
　　MySQL中的inet_ntoa()和inet_aton()函数可以分别把IP地址转换为数字或者进行相反的操作。如下面的例子所示：
　　SELECTINET_ATON('192.168.1.1');
　　8. 类型转换函数
　　MySQL提供cast()函数进行数据类型转换，它可以把一个值转换为指定的数据类型。
　　语法格式： CAST(expr, AS type)
　　expr是CAST函数要转换的值，type是转换后的数据类型。
　　在CAST函数中MySQL支持这几种数据类型：BINARY、CHAR、DATE、TIME、DATETIME、SIGNED和UNSIGNED。
　　通常情况下，当使用数值操作时，字符串会自动地转换为数字，因此下面例子中两种操作得到相同的结果：
　　SELECT1+'99', 1+CAST('99' AS SIGNED);
　　字符串可以指定为binary类型，这样它们的比较操作就成为大小写敏感的。使用CAST()函数指定一个字符串为BINARY和字符串前面使用BINARY关键词具有相同的作用。
　　例： 执行如下语句：
　　SELECT'a'=BINARY 'A', 'a'=CAST('A' AS BINARY);
　　结果为：0,0；（两个表达式的结果都为零表示两个表达式都为假）
　　MySQL还可以强制将日期和时间函数的值作为一个数而不是字符串输出。
　　例： 将当前日期显示成数值形式。
　　SELECTCAST(CURDATE() AS SIGNED);
　　9. 系统信息函数
　　MySQL还具有一些特殊的函数用来获得系统本身的信息，下表列出了大部分信息函数。
函 数功 能DATABASE()返回当前数据库名benchmark(n，expr)将表达式expr重复运行n次charsetstr)返回字符串str的字符集Connection_id()返回当前客户的连接IDFOUND_ROWS()将最后一个SELECT查询（没有以LIMIT语句进行限制）返回的记录行数返回GET_LOCK(str，dur)获得一个由字符串str命名的并且有dur秒延时的锁定IS_FREE_LOCK(str)检查以str命名的锁定是否释放LAST_INSERT_ID()返回由系统自动产生的最后一个AUTOINCREMENT ID的值master_pos_wait(log，pos，dur)锁定主服务器dur秒直到从服务器与主服务器的日志log指定的位置pos同步please_lock(str)释放由字符串str命名的锁定USER()或SYSTEM_USER()返回当前登录用户名version()返回MySQL服务器的版本　　（1）DATABASE()、USER()和VERSION()函数可以分别返回当前所选数据库、当前用户和MySQL版本信息：
　　 （2）BENCHMARK() 函数用于重复执行n次表达式expr。它可以被用于计算MySQL处理表达式的速度。结果值通常为零。另一种用处来自 MySQL客户端内部，能够报告问询执行的次数，根据经过的时间值可以推断服务器的性能。例如：
　　SELECT BENCHMARK(10000000, ENCODE('hello','goodbye'));
　　结果为：0；
　　这个例子中，MySQL计算ENCODE('hello'，'goodbye')表达式10 000 000次需要2.16秒。
　　（3）FOUND_ROWS()函数用于返回最后一个SELECT语句返回的记录行的数目。
　　如最后执行的SELECT语句是：SELECT * FROM XS;
　　之后执行如下语句：SELECT FOUND_ROWS();
　　结果为：22；
　　说明：SELECT语句可能包括一个LIMIT子句，用来限制服务器返回客户端的行数。在有些情况下，需要不用再次运行该语句而得知在没有LIMIT 时到底该语句返回了多少行。为了知道这个行数，包括在SELECT语句中选择SQL_CALC_FOUND_ROWS，随后调用FOUND_ROWS()。例如，执行如下语句：
　　SELECT SQL_CALC_FOUND_ROWS * FROM XS WHERE性别=1 LIMIT 5;
　　之后可以使用FOUND_ROWS()函数，显示在没有LIMIT子句的情况下SELECT语句所返回的行数。
　　结果如下：14；