MySQL心得6--MySQL语言结构--函数_MySQL

bitsCN.com 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；
作者 tianyazaiheruan bitsCN.com