第6章 MySQL语言结构6.1 常量和变量常量和变量6.2 运算符与表达式运算符与表达式6.3 系统内置函数系统内置函数第第6 6章章 MySQLMySQL语言结构语言结构在MySQL数据库中，SQL语言由以下几部分组成。（1）数据定义语言（DDL）。用于执行数据库的任务，对数据库及数据库中的各种对象进行创建、删除、修改等操作。如前所述，数据库对象主要包括：表、默认约束、规则、视图、触发器、存储过程等。DDL包括的主要语句及功能如表6.1所示。表6.1 DDL主要语句及功能语 句功 能说 明CREATE创建数据库或数据库对象不同数据库对象，其CREATE语句的语法形式不同ALTER对数据库或数据库对象进行修改不同数据库对象，其ALTER语句的语法形式不同DROP删除数据库或数据库对象不同数据库对象，其DROP语句的语法形式不同第第6 6章章 MySQLMySQL语言结构语言结构（2）数据操纵语言（DML）。用于操纵数据库中各种对象，检索和修改数据。DML包括的主要语句及功能如表6.2所示。表6.2 DML主要语句及功能语 句功 能说 明SELECT从表或视图中检索数据是使用最频繁的SQL语句之一INSERT将数据插入到表或视图中UPDATE修改表或视图中的数据既可修改表或视图的一行数据，也可修改一组或全部数据DELETE从表或视图中删除数据可根据条件删除指定的数据（3）数据控制语言（DCL）。用于安全管理，确定哪些用户可以查看或修改数据库中的数据，DCL包括的主要语句及功能如表6.3所示。表6.3 DCL主要语句及功能语 句功 能说 明GRANT授予权限可把语句许可或对象许可的权限授予其他用户和角色REVOKE收回权限与GRANT的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限第第6 6章章 MySQLMySQL语言结构语言结构（4）MySQL增加的语言元素。这部分不是SQL标准所包含的内容，而是为了用户编程的方便增加的语言元素。这些语言元素包括常量、变量、运算符、函数、流程控制语句和注解等。本章将具体讨论使用MySQL这部分增加的语言元素。每个SQL语句都以分号结束，并且SQL处理器忽略空格、制表符和回车符。6.1 6.1 常量和变量常量和变量6.1.1 常量常量1. 字符串常量字符串是指用单引号或双引号括起来的字符序列，分为ASCII字符串常量和Unicode 字符串常量。ASCII字符串常量是用单引号括起来的，由ASCII字符构成的符号串。举例：helloHow are you!Unicode 字符串常量与ASCII字符串常量相似，但它前面有一个N标志符（N代表 SQL-92标准中的国际语言（National Language）。N前缀必须为大写。只能用单引号括起字符串。举例：NhelloNHow are you!Unicode 数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。在字符串中不仅可以使用普通的字符，也可使用几个转义序列，它们用来表示特殊的字符，见表 6.4。每个转义序列以一个反斜杠（“”）开始，指出后面的字符使用转义字符来解释，而不是普通字符。注意 NUL 字节与 NULL 值不同，NUL 为一个零值字节，而 NULL 代表没有值。6.1.1 6.1.1 常量常量表6.4 字符串转移序列表序 列含 义0一个ASCII 0 (NUL)字符n一个换行符r一个回车符（Windows中使用rn作为新行标志）t一个定位符b一个退格符Z一个ASCII 26字符（CTRL+Z）一个单引号（“”）一个双引号(“”)一个反斜线（“”）%一个“%”符。它用于在正文中搜索“%”的文字实例，否则这里“%”将解释为一个通配符_一个“_”符。它用于在正文中搜索“_”的文字实例，否则这里“_”将解释为一个通配符6.1.1 6.1.1 常量常量【例6.1】 执行如下语句：SELECT ThisnIsnFournLines;结果为：其中，“n”表示回车。有以下几种方式可以在字符串中包括引号： 在字符串内用单引号“”引用的单引号“”可以写成“”（两个单引号）。 在字符串内用双引号“”引用的双引号“”可以写成“”（两个双引号）。 可以在引号前加转义字符（“” ）。 在字符串内用双引号“”引用的单引号“”不需要特殊处理，不需要用双字符或转义。同样，在字符串内用单引号“”引用的双引号“”也不需要特殊处理。6.1.1 6.1.1 常量常量【例6.2】 执行下面的语句：SELECT hello, hello, hello, hello, hello;结果为：注意：语句中第4个“hello”中间是两个单引号而不是一个双引号。2. 数值常量数值常量可以分为整数常量和浮点数常量。整数常量即不带小数点的十进制数，例如：1894，2，+145345234，2147483648。浮点数常量是使用小数点的数值常量，例如：5.26，1.39，101.5E5，0.5E2。6.1.1 6.1.1 常量常量3. 十六进制常量 MySQL支持十六进制值。一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母“X”或小写字“x”。在引号中只可以使用数字“0”到“9”及字母“a”到“f”或“A”到“F”。例如：X41表示大写字母A。x4D7953514C表示字符串MySQL。十六进制数值不区分大小写，其前缀“X”或“x”可以被“0x”取代而且不用引号。即X41可以替换为0x41，注意：“0x”中x一定要小写。十六进制值的默认类型是字符串。如果想要确保该值作为数字处理，可以使用CAST（.AS UNSIGNED）。【例6.3】 执行如下语句：SELECT 0x41, CAST(0x41 AS UNSIGNED);6.1.1 6.1.1 常量常量如果要将一个字符串或数字转换为十六进制格式的字符串，可以用HEX()函数。【例6.4】 将字符串CAT转换为16进制。SELECT HEX(CAT);结果为：验证：十六进制值通常用来存储图像（如JPG）和电影（如AVI）等格式的数据。6.1.1 6.1.1 常量常量4. 日期时间常量日期时间常量：用单引号将表示日期时间的字符串括起来构成。日期型常量包括年、月、日，数据类型为DATE，表示为“1999-06-17”这样的值。时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为TIME，表示为“12:30:43.00013”这样的值。MySQL 还支持日期/时间的组合，数据类型为DATETIME或TIMESTAMP，如“1999-06-17 12:30:43”。DATETIME和TIMESTAMP的区别在于：DATETIME的年份在10009999之间，而TIMESTAMP的年份在19702037之间，还有就是TIMESTAMP在插入带微秒的日期时间时将微秒忽略。TIMESTAMP还支持时区，即在不同时区转换为相应时间。需要要特别注意的是，MySQL 是按年-月-日的顺序表示日期的。中间的间隔符“-”也可以使用如“”、“”或“%”等特殊符号。如下是时间常量的例子：14:30:24如下是日期时间常量的例子：2008-05-12 14:28:24:00日期时间常量的值必须符合日期和时间的标准，如这样的日期是错误的：1996-02-31。6.1.1 6.1.1 常量常量5. 位字段值可以使用bvalue符号写位字段值。value是一个用0和1写成的二进制值。直接显示bvalue的值可能是一系列特殊的符号。例如，b0显示为空白，b1显示为一个笑脸图标。使用BIN函数可以将位字段常量显示为二进制格式。使用OCT函数可以将位字段常量显示为数值型格式。【例6.5】 执行下列语句：SELECT BIN(b111101+0), OCT(b111101+0);结果为：6.1.1 6.1.1 常量常量6. 布尔值布尔值只包含两个可能的值：TRUE和FALSE。FALSE的数字值为“0”，TRUE的数字值为“1”。【例6.6】 获取TRUE和FALSE的值。SELECT TRUE, FALSE;结果：7. NULL值NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义，并且不同于数字类型的“0”或字符串类型的空字符串。6.1.2 6.1.2 变量变量1. 用户变量用户可以在表达式中使用自己定义的变量，这样的变量叫做用户变量。用户可以先在用户变量中保存值，然后在以后引用它，这样可以将值从一个语句传递到另一个语句。在使用用户变量前必须定义和初始化。如果使用没有初始化的变量，它的值为NULL。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。定义和初始化一个变量可以使用SET语句，语法格式为：SET user_variable1expression1 ,user_variable2= expression2 , 其中，user_variable1、user_variable2为用户变量名，变量名可以由当前字符集的文字数字字符、“.”、“_”和“$”组成。当变量名中需要包含了一些特殊符号（如空格、#等）时，可以使用双引号或单引号将整个变量括起来。expression1、expression2为要给变量赋的值，可以是常量、变量或表达式。【例6.7】 创建用户变量name并赋值为“王林”。SET name=王林;注意：符号必须放在一个用户变量的前面，以便将它和列名区分开。“王林”是给变量name指定的值。name的数据类型是根据后面的赋值表达式自动分配的。也就是说，name的数据类型跟 王林 的数据类型是一样的，字符集和校对规则也是一样的。如果给name变量重新赋不同类型的值，则name的数据类型也会跟着改变。还可以同时定义多个变量，中间用逗号隔开。6.1.2 6.1.2 变量变量【例6.8】 创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3。SET user1=1, user2=2, user3=3;定义用户变量时变量值可以是一个表达式。【例6.9】 创建用户变量user4，它的值为user3的值加1。SET user4=user3+1;在一个用户变量被创建后，它可以以一种特殊形式的表达式用于其他SQL语句中。变量名前面也必须加上符号。【例6.10】 查询例6.7中创建的变量name的值。SELECT name;结果为：6.1.2 6.1.2 变量变量【例6.11】 使用查询给变量赋值。USE XSCJSET student=(SELECT 姓名 FROM XS WHERE 学号=081101);【例6.12】 查询表XS中名字等于例6.11中student值的学生信息。SELECT 学号, 姓名, 专业名, 出生日期 FROM XSWHERE 姓名=student;查询结果为：说明：在SELECT语句中，表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或ORDER BY子句中，不能使用包含SELECT列表中所设的变量的表达式。对于SET语句，可以使用“=”或“:=”作为分配符。分配给每个变量的值可以为整数、实数、字符串或NULL值。也可以用其他SQL语句代替SET语句来为用户变量分配一个值。在这种情况下，分配符必须为“:=”，而不能用“=”，因为在非SET语句中“=”被视为比较操作符。【例6.13】 执行如下语句：SELECT t2:=(t2:=2)+5 AS t2;结果t2的值为7。6.1.2 6.1.2 变量变量2. 系统变量MySQL有一些特定的设置，当MySQL数据库服务器启动的时候，这些设置被读取来决定下一步骤。例如，有些设置定义了数据如何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。和用户变量一样，系统变量也是一个值和一个数据类型，但不同的是，系统变量在MySQL服务器启动时就被引入并初始化为默认值。附录G中列出了绝大多数的系统变量。【例6.14】 获得现在使用的MySQL版本。SELECT VERSION ;结果为：6.1.2 6.1.2 变量变量说明：在MySQL中，系统变量VERSION的值设置为版本号。在变量名前必须加两个符号才能正确返回该变量的值。大多数的系统变量应用于其他SQL语句中时，必须在名称前加两个符号，而为了与其他SQL产品保持一致，某些特定的系统变量是要省略这两个符号的。如CURRENT_DATE（系统日期）、CURRENT_TIME（系统时间）、CURRENT_TIMESTAMP（系统日期和时间）和CURRENT_USER（SQL用户的名字）。【例6.15】 获得系统当前时间。SELECT CURRENT_TIME;结果为：6.1.2 6.1.2 变量变量在MySQL中，有些系统变量的值是不可以改变的，例如VERSION和系统日期。而有些系统变量是可以通过SET语句来修改的，例如SQL_WARNINGS。语法格式为：SET system _var_name = expr | GLOBAL | SESSION system_var_name = expr | global.| session. system_var_name = expr说明：system_var_name为系统变量名，expr为系统变量设定的新值。名称的前面可以添加GLOBAL或SESSION等关键字。指定了GLOBAL或global.关键字的是全局系统变量（global system variable）。指定了SESSION或session.关键字的则为会话系统变量（local system variable)。SESSION和session.还有一个同义词LOCAL和local.。如果在使用系统变量时不指定关键字，则默认为会话系统变量。（1）全局系统变量当MySQL启动的时候，全局系统变量就初始化了，并且应用于每个启动的会话。如果使用GLOBAL（要求SUPER权限）来设置系统变量，则该值被记住，并被用于新的连接，直到服务器重新启动为止。【例6.16】 将全局系统变量sort_buffer_size的值改为25000。 SET global.sort_buffer_size=25000; 注意：如果在使用SET GLOBAL时同时使用了一个只能与SET SESSION同时使用的变量，或者如果在设置一个全局变量时未指定GLOBAL（或），则MySQL会产生错误。6.1.2 6.1.2 变量变量（2）会话系统变量会话系统变量（session system variable）只适用于当前的会话。大多数会话系统变量的名字和全局系统变量的名字相同。当启动会话的时候，每个会话系统变量都和同名的全局系统变量的值相同。一个会话系统变量的值是可以改变的，但是这个新的值仅适用于正在运行的会话，不适用于所有其他会话。【例6.17】 将当前会话的SQL_WARNINGS变量设置为TRUE。SET SQL_WARNINGS =ON;说明：这个系统变量表示如果不正确的数据通过一条INSERT语句添加到一个表中，MySQL是否应该返回一条警告。默认情况下，这个变量是关闭的，设为ON表示返回警告。【例6.18】 对于当前会话，把系统变量SQL_SELECT_LIMIT的值设置为10。这个变量决定了SELECT语句的结果集中的最大行数。SET SESSION.SQL_SELECT_LIMIT=10;SELECT LOCAL .SQL_SELECT_LIMIT;结果为：6.1.2 6.1.2 变量变量说明：在这个例子中，关键字SESSION放在系统变量的名字前面（SESSION和LOCAL可以通用）。这明确地表示会话系统变量SQL_SELECT_LIMIT和SET语句指定的值保持一致。但是，名为SQL_SELECT_LIMIT的全局系统变量的值仍然不变。同样地，改变了全局系统变量的值，同名的会话系统变量的值保持不变。MySQL对于大多数系统变量都有默认值。当数据库服务器启动的时候，就使用这些值。也可以在C盘MYSQL文件夹下的my.ini选项文件中修改这些值。当数据库服务器启动的时候，这个文件被自动读取。如果要将一个系统变量值设置为MySQL默认值，可以使用DEFAULT关键字。【例6.19】 把SQL_SELECT_LIMIT的值恢复为默认值。SET LOCAL.SQL_SELECT_LIMIT=DEFAULT;使用SHOW VARIABLES语句可以得到系统变量清单。SHOW GLOBAL VARIABLES返回所有全局系统变量，而SHOW SESSION VARIABLES返回所有会话系统变量。如果不加关键字就默认为SHOW SESSION VARIABLES。【例6.20】 得到系统变量清单。SHOW VARIABLES;要获得与样式匹配的具体的变量名称或名称清单，需使用LIKE子句，语句如下：SHOW VARIABLES LIKE max_join_size;SHOW GLOBAL VARIABLES LIKE max_join_size;要得到名称与样式匹配的变量的清单，需使用通配符“%”，例如：SHOW VARIABLES LIKE character%;6.2 6.2 运算符与表达式运算符与表达式6.2.1 算术运算符算术运算符算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型。算术运算符有：+（加）、（减）、*（乘）、/（除）和%（求模）5种运算。（1）“+”运算符“+”运算符用于获得一个或多个值的和：SELECT 1.2+3.09345, 0.00000000001+0.00000000001; （2）“”运算符“”运算符用于从一个值中减去另一个值，并可以更改参数符号：SELECT 200201, 0.140.1, 2, 23.4;6.2.1 6.2.1 算术运算符算术运算符注意：若该操作符与BIGINT同时使用，则返回值也是一个BIGINT。这意味着在可能产生263的整数运算中应当避免使用减号“”，否则会出现错误。其中，+（加）和（减）运算符还可用于对日期时间值（如DATETIME）进行算术运算。例如：SELECT 2008-01-20+ INTERVAL 22 DAY;结果为：说明：INTERVAL关键字后面跟一个时间间隔，22 DAY表示在当前的日期基础上加上22天。当前日期为2008-01-20，加上22天后为2008-02-11。（3）“*”运算符“*”运算符用来获得两个或多个值的乘积：SELECT 5*12,5*0, 11.2*8.2, 19530415* 19540319;6.2.1 6.2.1 算术运算符算术运算符（4）“/”运算符“/”运算符用来获得一个值除以另一个值得到的商：SELECT 12/2, 1.6/0.1, 23/7, 23.00/7.00000,1/0;显然，除以零的除法是不允许的，如果这样做，MySQL会返回NULL：（5）“%”运算符“%”运算符用来获得一个或多个除法运算的余数：SELECT 12%5, 32%7,3%0;6.2.2 6.2.2 比较运算符比较运算符比较运算符（又称关系运算符），用于比较两个表达式的值，其运算结果为逻辑值，可以为三种之一：1（真）、0（假）及 NULL（不能确定）。表6.5列出了在MySQL中可以使用的各种比较运算符。表6.5 比较运算符运 算 符含 义运 算 符含 义=等于大于、!=不等于小于相等或都等于空=大于等于有关比较运算已经在第4章SELECT语句的WHERE子句中介绍过，这里再做一些补充。比较运算符可以用于比较数字和字符串。数字作为浮点值比较，而字符串以不区分大小写的方式进行比较（除非使用特殊的BINARY关键字）。前面已经介绍了在运算过程中MySQL能够自动地把数字转换为字符串，而在比较运算过程中，MySQL能够自动地把字符串转换为数字。6.2.2 6.2.2 比较运算符比较运算符下面这个例子说明了在不同的情况下MySQL以不同的方式处理数字和字符串。【例6.21】 执行下列语句：SELECT 5 = 5ab,5=5ab;结果为：（1）“=”运算符“=”运算符用于比较表达式的两边是否相等，也可以对字符串进行比较，示例如下：SELECT 3.14=3.142,5.12=5.120, a=A,A=B,apple=banana;6.2.2 6.2.2 比较运算符比较运算符注意：因为在默认情况下MySQL以不区分大小写的方式比较字符串，所以表达式a=A的结果为真。如果想执行区分大小写的比较，可以添加BINARY关键字，这意味着对字符串以二进制方式处理。当在字符串上执行比较运算时，MySQL将区分字符串的大小写。示范 如下：SELECT Apple=apple , BINARY Apple=apple;（2）“”运算符与“=”运算符相对立的是“”运算符，它用来检测表达式的两边是否不相等，如果不相等则返回真值，相等则返回假值。示例如下：SELECT 55 ,56,aa,5a5b;6.2.2 6.2.2 比较运算符比较运算符SELECT NULLNULL, 0NULL, 00;（3）“=”，“”运算符=，运算符用来比较表达式的左边是小于或等于、大于或等于、小于还是大于它的右边，示例如下：SELECT 1010, 109, 103.142;6.2.3 6.2.3 逻辑运算符逻辑运算符逻辑运算符用于对某个条件进行测试，运算结果为TRUE（1）或FALSE（0）。MySQL提供的逻辑运算符如表6.6所示。表6.6 逻辑运算符运 算 符运 算 规 则运 算 符运 算 规 则NOT或!逻辑非OR或|逻辑或AND或&逻辑与XOR逻辑异或（1）NOT运算符逻辑运算符中最简单的NOT运算符，它对跟在它后面的逻辑测试判断取反，把真变假，假变真。例如：SELECT NOT 1, NOT 0, NOT(1=1),NOT(109);6.2.3 6.2.3 逻辑运算符逻辑运算符（2）AND运算符AND运算符用于测试两个或更多的值（或表达式求值）的有效性，如果它的所有成分为真，并且不是NULL，它返回真值，否则返回假值。例如：SELECT (1=1) AND (910),(a=a) AND (c10), (a=b) OR (12);6.2.3 6.2.3 逻辑运算符逻辑运算符（4）XOR运算符如果包含的值或表达式一个为真而另一个为假并且不是NULL，那么它返回真值，否则返回假值。例如：SELECT (1=1) XOR (2=3), (12) XOR (9位右移位XOR位左移（1）“|”运算符和“&”运算符“|”运算符用于执行一个位的或操作，而“&”用于执行一个位的与操作。例如：SELECT 13|28, 3|4,13&28, 3&4;6.2.4 6.2.4 位运算符位运算符说明：本例中13|8表示按13和8的二进制位按位进行与（OR）操作。（2）运算符运算符分别用于向左和向右移动位，例如：SELECT 11;说明：本例中1的二进制为向左移动7位，最后得到的十进制数为128。64的二进制位向右移动1位，最后得到的十进制数为32。（3）“”运算符“”运算符执行位异或（XOR）操作：SELECT 10,125,12323;6.2.4 6.2.4 位运算符位运算符（4）“”运算符“”运算符执行位取反操作，并返回64位整型结果：SELECT 18446744073709551614, 1;6.2.5 6.2.5 运算符优先级运算符优先级除了以上的运算符，MySQL还提供了其他一些常用的运算符，如BETWEEN运算符、IN运算符、IS NULL 和IS NOT NULL运算符、LIKE运算符、REGEXP运算符等，这些在第4章SELECT语句中的WHERE子句中已经有过介绍，这里就不再展开讨论。下面讨论一下运算符的优先顺序。当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的顺序会影响所得到的运算结果。运算符优先级如表6.8所示。在一个表达式中按先高（优先级数字小）后低（优先级数字大）的顺序进行运算。表6.8 运算符优先级运 算 符优先级运 算 符优先级+（正）、（负）、（按位NOT）1NOT6*（乘）、/（除）、%（模）2AND7+（加）、（减）3ALL、ANY、BETWEEN、IN、LIKE、OR、SOME8=，=，=，!= ，! ，!b。当表达式的结果是由不同类型数据组成的一行值，这种表达式叫做行表达式。例如，（学号，王林，计算机，50*10），当学号列的值为081101时，这个行表达式的值就为：（081101，王林，计算机，500）。当表达式的结果为0个、1个或多个行表达式的集合，那么这个表达式就叫做表表达式。表达式按照形式还可分为单一表达式和复合表达式。单一表达式就是一个单一的值，如一个常量或列名。复合表达式是由运算符将多个单一表达式连接而成的表达式，例如：1+2+3，a=b+3，2008-01-20+ INTERVAL 2 MONTH。表达式一般用在SELECT及SELECT语句的WHERE子句中。6.3 6.3 系统内置函数系统内置函数6.3.1 数学函数数学函数数学函数用于执行一些比较复杂的算术操作。MySQL支持很多的数学函数。附录F中列出了比较重要的一些。若发生错误，所有的数学函数都会返回NULL。下面对一些常用的数学函数进行举例。（1）GREATEST()和LEAST()函数GREATEST()和LEAST()是数学函数中经常使用的函数，它们的功能是获得一组数中的最大值和最小值。例如：SELECT GREATEST(10,9,128,1),LEAST(1,2,3);结果为：6.3.1 6.3.1 数学函数数学函数数学函数用于执行一些比较复杂的算术操作。MySQL支持很多的数学函数。附录F中列出了比较重要的一些。若发生错误，所有的数学函数都会返回NULL。下面对一些常用的数学函数进行举例。（1）GREATEST()和LEAST()函数GREATEST()和LEAST()是数学函数中经常使用的函数，它们的功能是获得一组数中的最大值和最小值。例如：SELECT GREATEST(10,9,128,1),LEAST(1,2,3);结果为：数学函数还可以嵌套使用，例如：SELECT GREATEST(2,LEAST(0,3), LEAST(1,GREATEST(1,2);结果为：6.3.1 6.3.1 数学函数数学函数注意：MySQL不允许函数名和括号之间有空格。（2）FLOOR()和CEILING()函数FLOOR()用于获得小于一个数的最大整数值，CEILING()函数用于获得大于一个数的最小整数值，例如：SELECT FLOOR(-1.2), CEILING(-1.2), FLOOR(9.9), CEILING(9.9);结果为：（3）ROUND()和TRUNCATE()函数ROUND()函数用于获得一个数的四舍五入的整数值：SELECT ROUND(5.1),ROUND(25.501),ROUND(9.8);结果为：6.3.1 6.3.1 数学函数数学函数TRUNCATE()函数用于把一个数字截取为一个指定小数个数的数字，逗号后面的数字表示指定小数的个数：SELECT TRUNCATE(1.54578, 2),TRUNCATE(76.12, 5);结果为：（4）ABS()函数ABS()函数用来获得一个数的绝对值，例如：SELECT ABS(878),ABS(8.345);结果为：6.3.1 6.3.1 数学函数数学函数（5）SIGN()函数SIGN()函数返回数字的符号，返回的结果是正数（1）、负数（1）或者零（0)：SELECT SIGN(2),SIGN(2),SIGN(0);结果为：（6）SQRT()函数SQRT()函数返回一个数的平方根：SELECT SQRT(25),SQRT(15),SQRT(1);结果为：6.3.1 6.3.1 数学函数数学函数（7）POW()函数POW()函数以一个数作为另外一个数的指数，并返回结果：SELECT POW(2,2),POW(10, 2),POW(0,3);结果为：说明：第一个数表示是2的2次方，第二个表示10的2次方。（8）SIN()、COS()和TAN()函数SIN()、COS()和TAN()函数返回一个角度（弧度）的正弦、余弦和正切值：SELECT SIN(1),COS(1),TAN(RADIANS(45);结果为：6.3.1 6.3.1 数学函数数学函数（9）ASIN()、ACOS()和ATAN()函数ASIN()、ACOS()和ATAN()函数返回一个角度（弧度）的反正弦、反余弦和反正切值：SELECT ASIN(1),ACOS(1),ATAN(DEGREES(45);结果为：如果使用的是角度而不是弧度，可以使用DEGREES()和RADIANS()函数进行转换。（10）BIN()、OTC()和HEX()函数BIN()、OTC()和HEX()函数分别返回一个数的二进制、八进制和十六进制值，这个值作为字符串返回：SELECT BIN(2),OCT(12),HEX(80);结果为：6.3.2 6.3.2 聚合函数聚合函数MySQL有一组函数是特意为求和或者对表中的数据进行集中概括而设计的。这一组函数就叫做聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。通过把聚合函数（如COUNT和SUM）添加到带有一个GROUP BY子句的SELECT语句块中，数据就可以聚合。聚合意味着是求一个和、平均、频次及子和，而不是单个的值。有关聚合函数的内容请参考4.2.1节，这里不再讨论。6.3.3 6.3.3 字符串函数字符串函数因为MySQL数据库不仅包含数字数据，还包含字符串，因此MySQL有一套为字符串操作而设计的函数。在字符串函数中，包含的字符串必须要用单引号括起。附录F中列出了很多的字符串函数，下面对其中重要的一些进行介绍。（1）ASCII()函数语法格式：ASCII (char)返回字符表达式最左端字符的ASCII值。参数char的类型为字符型的表达式，返回值为 整型。【例6.22】 返回字母A的ASCII码值。SELECT ASCII(A);结果为：6.3.3 6.3.3 字符串函数字符串函数（2）CHAR()函数语法格式：CHAR (x1,x2,x3,) 将x1、x2的ASCII码转换为字符，结果组合成一个字符串。参数x1，x2，x3为介于0255之间的整数，返回值为字符型。【例6.23】 返回ASCII码值为65、66、67的字符，组成一个字符串。SELECT CHAR(65,66,67);结果为：6.3.3 6.3.3 字符串函数字符串函数（3）LEFT和RIGHT函数语法格式：LEFT | RIGHT ( str ,x ) 分别返回从字符串str左边和右边开始指定x个字符。【例6.24】 返回KC表中课程名最左边的3个字符。USE XSCJSELECT LEFT(课程名, 3) FROM KC;执行结果为：6.3.3 6.3.3 字符串函数字符串函数（4）TRIM、LTRIM 和RTRIM函数语法格式：TRIM | LTRIM | RTRIM(str)使用LTRIM和RTRIM分别删除字符串中前面的空格和尾部的空格，返回值为字符串。参数str为字符型表达式，返回值类型为varchar。TRIM删除字符串首部和尾部的所有空格。【例6.25】 执行如下语句：SELECT TRIM( MySQL );（5）RPAD和LPAD函数语法格式：RPAD | LPAD( str, n, pad)使用RPAD和LPAD分别用字符串pad对字符串str的右边和左边进行填补直至str中字符数目达到n个，最后返回填补后的字符串。若str中的字符个数大于n，则返回str的前n个字符。【例6.26】 执行如下语句：SELECT RPAD(中国加油,8, !), LPAD(welcome,10, *);结果为：6.3.3 6.3.3 字符串函数字符串函数（6）REPLACE函数语法格式：REPLACE (str1 , str2 , str3 )REPLACE函数用于用字符串str3替换str1中所有出现的字符串str2。最后返回替换后的字符串。【例6.27】 执行如下语句：SELECT REPLACE(Welcome to CHINA, o, K);结果为：6.3.3 6.3.3 字符串函数字符串函数（7）CONCAT函数语法格式：CONCAT(s1,s2,sn)CONCAT函数用于连接指定的几个字符串。【例6.28】 执行如下语句：SELECT CONCAT(中国, 加油);结果为：（8）SUBSTRING函数语法格式：SUBSTRING (expression , Start, Length ) 返回expression中指定的部分数据。参数expression可为字符串、二进制串、text、image字段或表达式。Start、Length均为整型，前者指定子串的开始位置，后者指定子串的长度（要返回字节数）。如果 expression 是字符类型和二进制类型，则返回值类型与expression的类型相同。如果为text类型，返回的是varchar类型。6.3.3 6.3.3 字符串函数字符串函数【例6.29】 如下程序在一列中返回XS表中所有女同学的姓氏，在另一列中返回名字。USE XSCJSELECT SUBSTRING(姓名, 1,1) AS 姓, SUBSTRING(姓名, 2, LENGTH(姓名)1) AS 名 FROM XS WHERE 性别=0 ORDER BY 姓名;执行结果为：6.3.3 6.3.3 字符串函数字符串函数说明：LENGTH函数的作用是返回一个字符串的长度。（9）STRCMP函数语法格式：STRCMP(s1,s2)STRCMP函数用于比较两个字符串，相等返回0，s1大于s2返回1，s1小于s2返回1。【例6.30】 执行如下语句：SELECT STRCMP(A, A), STRCMP(ABC, OPQ),STRCMP(T, B);结果为：6.3.4 6.3.4 日期和时间函数日期和时间函数MySQL有很多日期和时间数据类型，所以有相当多的操作日期和时间的函数。附录F中列出了大多数的日期和时间函数。下面介绍几个比较重要的函数。（1）NOW()使用NOW()函数可以获得当前的日期和时间，它以YYYY-MM-DD HHMMSS的格式返回当前的日期和时间：SELECT NOW();（2）CURTIME()和CURDATE()CURTIME()和CURDATE()函数比NOW更为具体化，它们分别返回的是当前的时间和日期，没有参数：SELECT CURTIME(),CURDATE();（3）YEAR()YEAR()函数分析一个日期值并返回其中关于年的部分：SELECT YEAR(20080512142800),YEAR(1982-11-02);6.3.4 6.3.4 日期和时间函数日期和时间函数（4）MOTNTH()和MONTHNAME()MOTNTH()和MONTHNAME()函数分别以数值和字符串的格式返回月的部分：SELECT MONTH(20080512142800), MONTHNAME(1982-11-02);（5）DAYOFYEAR()，DAYOFWEEK()和DAYOFMONTH()DAYOFYEAR()，DAYOFWEEK()和DAYOFMONTH()函数分别返回这一天在一年、一星期及一个月中的序数：SELECT DAYOFYEAR(20080512),DAYOFMONTH(2008-05-12);6.3.4 6.3.4 日期和时间函数日期和时间函数SELECT DAYOFWEEK(20080512);（6）DAYNAME()和MONTHNAME()相似，DAYNAME()以字符串形式返回星期名：SELECT DAYNAME(2008-06-01);6.3.4 6.3.4 日期和时间函数日期和时间函数（7）WEEK()和YEARWEEK()WEEK()函数返回指定的日期是一年的第几个星期，而YEARWEEK()函数返回指定的日期是哪一年的哪一个星期：SELECT WEEK(2008-05-01),YEARWEEK(20080501);（8）HOUR()、MINUTE()和SECOND()HOUR()，MINUTE()和SECOND()函数分别返回时间值的小时、分钟和秒的部分：SELECT HOUR(155300),MINUTE(15:53:00),SECOND(143415);6.3.4 6.3.4 日期和时间函数日期和时间函数（9）DATE_ADD()和DATE_SUB()DATE_ADD()和DATE_SUB()函数可以对日期和时间进行算术操作，它们分别用来增加和减少日期值，其使用的关键字如表6.9所示。表6.9 DATE_ADD()函数和DATE_SUB()函数使用的关键字关 键 字间隔值的格式关 键 字间隔值的格式DAY日期MINUTE分钟DAY_HOUR日期小时MINUTE_ SECOND分钟秒DAY_MINUTE日期小时分钟MONTH月DAY_SECOND日期小时分钟秒SECOND秒HOUR小时YEAR年HOUR_MINUTE小时分钟YEAR_MONTH年-月HOUR_ SECOND小时分钟秒6.3.4 6.3.4 日期和时间函数日期和时间函数DATE_ADD()和DATE_SUB()函数的语法格式为：DATE_ADD | DATE_SUB(date, INTERVAL int keyword)date是需要的日期和时间，INTERVAL关键字表示一个时间间隔。int表示需要计算的时间值，keyword已经在表6.9中列出。DATE_ADD函数是计算date加上间隔时间后的值，DATE_SUB则是计算date减去时间间隔后的值。举例：SELECT DATE_ADD(1986-08-08, INTERVAL 17 DAY);SELECT DATE_SUB(1998-08-20 10:25:35, INTERVAL 20 MINUTE);6.3.4 6.3.4 日期和时间函数日期和时间函数日期和时间函数在SQL语句中应用相当广泛。【例6.31】 求XS表中女学生的年龄。SELECT 学号,姓名, YEAR(NOW()YEAR(出生日期) AS 年龄FROM XSWHERE 性别=0;查询结果为：6.3.5 6.3.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上不可用。6.3.5 6.3.5 加密函数加密函数（4）PASSWORD函数格式为：PASSWORD(str)返回字符串str加密后的密码字符串，适合于插入到MySQL的安全系统。该加密过程不可逆，和UNIX密码加密过程使用不同的算法。主要用于MySQL的认证系统。【例6.32】 返回字符串“MySQL”的加密版本。SELECT PASSWORD(MySQL);6.3.6 6.3.6 控制流函数控制流函数MySQL有几个函数是用来进行条件操作的。这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。（1）IFNULL和NULLIF函数IFNULL函数的语法格式为：IFNULL(expr1,expr2)此函数的作用是：判断参数expr1是否为NULL，当参数expr1为NULL时返回expr2，不为NULL时返回expr1。IFNULL的返回值是数字或字符串。【例6.33】 执行如下语句：SELECT IFNULL(1,2), IFNULL(NULL, MySQL), IFNULL(1/0, 10);结果为：6.3.6 6.3.6 控制流函数控制流函数NULLIF函数的语法格式为：NULLIF(expr1,expr2)NULLIF函数用于检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等就返回第一个参数。【例6.34】 执行如下语句：SELECT NULLIF(1,1), NULLIF(A, B), NULLIF(2+3, 3+4);结果为：6.3.6 6.3.6 控制流函数控制流函数（2）IF函数和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件 测试。语法格式如下：IF(expr1,expr2,expr3)这个函数有3个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数；如果为假，IF()将会返回第三个参数。【例6.35】 判断2*4是否大于95，是则返回“是”，否则返回“否”。SELECT IF(2*495, 是, 否);结果为：6.3.6 6.3.6 控制流函数控制流函数【例6.36】 返回XS表名字为两个字的学生姓名、性别和专业名。性别值如为0则显示为“女”，为1则显示为“男”。SELECT 姓名, IF(性别=0, 女, 男) AS 性别, 专业名 FROM XS WHERE 姓名 LIKE _;结果为：6.3.7 6.3.7 格式化函数格式化函数MySQL还有一些函数是特意为格式化数据设计的。（1）FORMAT()函数语法格式为：FORMAT(x, y)FORMAT()函数把数值格式化为以逗号间隔的数字序列。FORMAT()的第一个参数x是被格式化的数据，第二个参数y是结果的小数位数。例如：SELECT FORMAT(11111111111.23654,2), FORMAT(5468,4);结果为：6.3.7 6.3.7 格式化函数格式化函数（2）DATE_FORMAT()和TIME_FORMAT()函数DATE_FORMAT()和TIME_FORMAT()函数可以用来格式化日期和时间值。语法格式如下：DATE_FORMAT/ TIME_FORMAT(date | time, fmt)其中，date和time是需要格式化的日期和时间值，fmt是日期和时间值格式化的形式，表6.10列出了MySQL中的日期/时间格式化代码。表6.10 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）6.3.7 6.3.7 格式化函数格式化函数举例：SELECT DATE_FORMAT(NOW(), %W,%d,%M, %Y %r);注意：这两个函数是对大小写敏感的。（3）INET_NTOA()和INET_ATON()函数MySQL中的INET_NTOA()和INET_ATON()函数可以分别把IP地址转换为数字或者进行相反的操作。如下面的例子所示：SELECT INET_ATON(192.168.1.1);6.3.8 6.3.8 类型转换函数类型转换函数MySQL提供CAST()函数进行数据类型转换，它可以把一个值转换为指定的数据类型。语法格式：CAST(expr, AS type)expr是CAST函数要转换的值，type是转换后的数据类型。在CAST函数中MySQL支持这几种数据类型：BINARY、CHAR、DATE、TIME、DATETIME、SIGNED和UNSIGNED。通常情况下，当使用数值操作时，字符串会自动地转换为数字，因此下面例子中两种操作得到相同的结果：SELECT 1+99, 1+CAST(99 AS SIGNED);6.3.8 6.3.8 类型转换函数类型转换函数字符串可以指定为BINARY类型，这样它们的比较操作就成为大小写敏感的。使用CAST()函数指定一个字符串为BINARY和字符串前面使用BINARY关键词具有相同的作用。【例6.37】 执行如下语句：SELECT a=BINARY A, a=CAST(A AS BINARY);结果为：说明：两个表达式的结果都为零表示两个表达式都为假。MySQL还可以强制将日期和时间函数的值作为一个数而不是字符串输出。【例6.38】 将当前日期显示成数值形式。SELECT CAST(CURDATE() AS SIGNED);结果为：6.3.9 6.3.9 系统信息函数系统信息函数MySQL还具有一些特殊的函数用来获得系统本身的信息，表6.11列出了大部分信息 函数。表6.11 MySQL信息函数函 数功 能DATABASE()返回当前数据库名BENCHMARK(n，expr)将表达式expr重复运行n次CHARSET(str)返回字符串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同步RELEASE_LOCK(str)释放由字符串str命名的锁定USER()或SYSTEM_USER()返回当前登录用户名VERSION()返回MySQL服务器的版本6.3.9 6.3.9 系统信息函数系统信息函数下面对其中一些信息函数进行举例：（1）DATABASE()、USER()和VERSION()函数可以分别返回当前所选数据库、当前用户和MySQL版本信息：SELECT DATABASE(),USER(), VERSION();结果为：（2）BENCHMARK() 函数用于重复执行n次表达式expr。它可以被用于计算MySQL 处理表达式的速度。结果值通常为零。另一种用处来自 MySQL客户端内部，能够报告问询执行的次数，根据经过的时间值可以推断服务器的性能。例如：SELECT BENCHMARK(10000000, ENCODE(hello,goodbye);结果为：6.3.9 6.3.9 系统信息函数系统信息函数这个例子中，MySQL计算ENCODE(hello，goodbye)表达式10 000 000次需要2.16秒。（3）FOUND_ROWS()函数用于返回最后一个SELECT语句返回的记录行的数目。如最后执行的SELECT语句是：SELECT * FROM XS;之后执行如下语句：SELECT FOUND_ROWS();结果为：6.3.9 6.3.9 系统信息函数系统信息函数说明：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语句所返回的行数。结果如下：