MySQL心得6-2--MySQL语言结构--函数
改章只是简介，不重要，具体用到什么函数可以查MySQL帮助或附录F！！！
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…） |
%p |
AM或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() |
返回当前客户的连接ID |
|
FOUND_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；
