《MySQL语言结构》PPT课件.ppt

第6章MySQL语言结构 6 1常量和变量 6 2运算符与表达式 6 3系统内置函数 第6章MySQL语言结构 在MySQL数据库中 SQL语言由以下几部分组成 1 数据定义语言 DDL 用于执行数据库的任务 对数据库及数据库中的各种对象进行创建 删除 修改等操作 如前所述 数据库对象主要包括 表 默认约束 规则 视图 触发器 存储过程等 DDL包括的主要语句及功能如表6 1所示 表6 1DDL主要语句及功能 第6章MySQL语言结构 2 数据操纵语言 DML 用于操纵数据库中各种对象 检索和修改数据 DML包括的主要语句及功能如表6 2所示 表6 2DML主要语句及功能 3 数据控制语言 DCL 用于安全管理 确定哪些用户可以查看或修改数据库中的数据 DCL包括的主要语句及功能如表6 3所示 表6 3DCL主要语句及功能 第6章MySQL语言结构 4 MySQL增加的语言元素 这部分不是SQL标准所包含的内容 而是为了用户编程的方便增加的语言元素 这些语言元素包括常量 变量 运算符 函数 流程控制语句和注解等 本章将具体讨论使用MySQL这部分增加的语言元素 每个SQL语句都以分号结束 并且SQL处理器忽略空格 制表符和回车符 6 1常量和变量 6 1 1常量1 字符串常量字符串是指用单引号或双引号括起来的字符序列 分为ASCII字符串常量和Unicode字符串常量 ASCII字符串常量是用单引号括起来的 由ASCII字符构成的符号串 举例 hello Howareyou Unicode字符串常量与ASCII字符串常量相似 但它前面有一个N标志符 N代表SQL 92标准中的国际语言 NationalLanguage N前缀必须为大写 只能用单引号括起字符串 举例 N hello N Howareyou Unicode数据中的每个字符用两个字节存储 而每个ASCII字符用一个字节存储 在字符串中不仅可以使用普通的字符 也可使用几个转义序列 它们用来表示特殊的字符 见表6 4 每个转义序列以一个反斜杠 开始 指出后面的字符使用转义字符来解释 而不是普通字符 注意NUL字节与NULL值不同 NUL为一个零值字节 而NULL代表没有值 6 1 1常量 表6 4字符串转移序列表 6 1 1常量 例6 1 执行如下语句 SELECT This nIs nFour nLines 结果为 其中 n 表示回车 有以下几种方式可以在字符串中包括引号 在字符串内用单引号 引用的单引号 可以写成 两个单引号 在字符串内用双引号 引用的双引号 可以写成 两个双引号 可以在引号前加转义字符 在字符串内用双引号 引用的单引号 不需要特殊处理 不需要用双字符或转义 同样 在字符串内用单引号 引用的双引号 也不需要特殊处理 6 1 1常量 例6 2 执行下面的语句 SELECT hello hello hello hel lo hello 结果为 注意 语句中第4个 hello 中间是两个单引号而不是一个双引号 2 数值常量数值常量可以分为整数常量和浮点数常量 整数常量即不带小数点的十进制数 例如 1894 2 145345234 2147483648 浮点数常量是使用小数点的数值常量 例如 5 26 1 39 101 5E5 0 5E 2 6 1 1常量 3 十六进制常量MySQL支持十六进制值 一个十六进制值通常指定为一个字符串常量 每对十六进制数字被转换为一个字符 其最前面有一个大写字母 X 或小写字 x 在引号中只可以使用数字 0 到 9 及字母 a 到 f 或 A 到 F 例如 X 41 表示大写字母A x 4D7953514C 表示字符串MySQL 十六进制数值不区分大小写 其前缀 X 或 x 可以被 0 x 取代而且不用引号 即X 41 可以替换为0 x41 注意 0 x 中x一定要小写 十六进制值的默认类型是字符串 如果想要确保该值作为数字处理 可以使用CAST ASUNSIGNED 例6 3 执行如下语句 SELECT0 x41 CAST 0 x41ASUNSIGNED 6 1 1常量 如果要将一个字符串或数字转换为十六进制格式的字符串 可以用HEX 函数 例6 4 将字符串CAT转换为16进制 SELECTHEX CAT 结果为 验证 十六进制值通常用来存储图像 如JPG 和电影 如AVI 等格式的数据 6 1 1常量 4 日期时间常量日期时间常量 用单引号将表示日期时间的字符串括起来构成 日期型常量包括年 月 日 数据类型为DATE 表示为 1999 06 17 这样的值 时间型常量包括小时数 分钟数 秒数及微秒数 数据类型为TIME 表示为 12 30 43 00013 这样的值 MySQL还支持日期 时间的组合 数据类型为DATETIME或TIMESTAMP 如 1999 06 1712 30 43 DATETIME和TIMESTAMP的区别在于 DATETIME的年份在1000 9999之间 而TIMESTAMP的年份在1970 2037之间 还有就是TIMESTAMP在插入带微秒的日期时间时将微秒忽略 TIMESTAMP还支持时区 即在不同时区转换为相应时间 需要要特别注意的是 MySQL是按年 月 日的顺序表示日期的 中间的间隔符 也可以使用如 或 等特殊符号 如下是时间常量的例子 14 30 24 如下是日期时间常量的例子 2008 05 1214 28 24 00 日期时间常量的值必须符合日期和时间的标准 如这样的日期是错误的 1996 02 31 6 1 1常量 5 位字段值可以使用b value 符号写位字段值 value是一个用0和1写成的二进制值 直接显示b value 的值可能是一系列特殊的符号 例如 b 0 显示为空白 b 1 显示为一个笑脸图标 使用BIN函数可以将位字段常量显示为二进制格式 使用OCT函数可以将位字段常量显示为数值型格式 例6 5 执行下列语句 SELECTBIN b 111101 0 OCT b 111101 0 结果为 6 1 1常量 6 布尔值布尔值只包含两个可能的值 TRUE和FALSE FALSE的数字值为 0 TRUE的数字值为 1 例6 6 获取TRUE和FALSE的值 SELECTTRUE FALSE 结果 7 NULL值NULL值可适用于各种列类型 它通常用来表示 没有值 无数据 等意义 并且不同于数字类型的 0 或字符串类型的空字符串 6 1 2变量 1 用户变量用户可以在表达式中使用自己定义的变量 这样的变量叫做用户变量 用户可以先在用户变量中保存值 然后在以后引用它 这样可以将值从一个语句传递到另一个语句 在使用用户变量前必须定义和初始化 如果使用没有初始化的变量 它的值为NULL 用户变量与连接有关 也就是说 一个客户端定义的变量不能被其他客户端看到或使用 当客户端退出时 该客户端连接的所有变量将自动释放 定义和初始化一个变量可以使用SET语句 语法格式为 SET user variable1 expression1 user variable2 expression2 其中 user variable1 user variable2为用户变量名 变量名可以由当前字符集的文字数字字符 和 组成 当变量名中需要包含了一些特殊符号 如空格 等 时 可以使用双引号或单引号将整个变量括起来 expression1 expression2为要给变量赋的值 可以是常量 变量或表达式 例6 7 创建用户变量name并赋值为 王林 SET name 王林 注意 符号必须放在一个用户变量的前面 以便将它和列名区分开 王林 是给变量name指定的值 name的数据类型是根据后面的赋值表达式自动分配的 也就是说 name的数据类型跟 王林 的数据类型是一样的 字符集和校对规则也是一样的 如果给name变量重新赋不同类型的值 则name的数据类型也会跟着改变 还可以同时定义多个变量 中间用逗号隔开 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 11 使用查询给变量赋值 USEXSCJSET student SELECT姓名FROMXSWHERE学号 081101 例6 12 查询表XS中名字等于例6 11中student值的学生信息 SELECT学号 姓名 专业名 出生日期FROMXSWHERE姓名 student 查询结果为 说明 在SELECT语句中 表达式发送到客户端后才进行计算 这说明在HAVING GROUPBY或ORDERBY子句中 不能使用包含SELECT列表中所设的变量的表达式 对于SET语句 可以使用 或 作为分配符 分配给每个变量的值可以为整数 实数 字符串或NULL值 也可以用其他SQL语句代替SET语句来为用户变量分配一个值 在这种情况下 分配符必须为 而不能用 因为在非SET语句中 被视为比较操作符 例6 13 执行如下语句 SELECT t2 t2 2 5ASt2 结果t2的值为7 6 1 2变量 2 系统变量MySQL有一些特定的设置 当MySQL数据库服务器启动的时候 这些设置被读取来决定下一步骤 例如 有些设置定义了数据如何被存储 有些设置则影响到处理速度 还有些与日期有关 这些设置就是系统变量 和用户变量一样 系统变量也是一个值和一个数据类型 但不同的是 系统变量在MySQL服务器启动时就被引入并初始化为默认值 附录G中列出了绝大多数的系统变量 例6 14 获得现在使用的MySQL版本 SELECT VERSION 结果为 6 1 2变量 说明 在MySQL中 系统变量VERSION的值设置为版本号 在变量名前必须加两个 符号才能正确返回该变量的值 大多数的系统变量应用于其他SQL语句中时 必须在名称前加两个 符号 而为了与其他SQL产品保持一致 某些特定的系统变量是要省略这两个 符号的 如CURRENT DATE 系统日期 CURRENT TIME 系统时间 CURRENT TIMESTAMP 系统日期和时间 和CURRENT USER SQL用户的名字 例6 15 获得系统当前时间 SELECTCURRENT TIME 结果为 6 1 2变量 在MySQL中 有些系统变量的值是不可以改变的 例如VERSION和系统日期 而有些系统变量是可以通过SET语句来修改的 例如SQL WARNINGS 语法格式为 SETsystem var name expr GLOBAL SESSION system var name expr global session system var name expr说明 system var name为系统变量名 expr为系统变量设定的新值 名称的前面可以添加GLOBAL或SESSION等关键字 指定了GLOBAL或 global 关键字的是全局系统变量 globalsystemvariable 指定了SESSION或 session 关键字的则为会话系统变量 localsystemvariable SESSION和 session 还有一个同义词LOCAL和 local 如果在使用系统变量时不指定关键字 则默认为会话系统变量 1 全局系统变量当MySQL启动的时候 全局系统变量就初始化了 并且应用于每个启动的会话 如果使用GLOBAL 要求SUPER权限 来设置系统变量 则该值被记住 并被用于新的连接 直到服务器重新启动为止 例6 16 将全局系统变量sort buffer size的值改为25000 SET global sort buffer size 25000 注意 如果在使用SETGLOBAL时同时使用了一个只能与SETSESSION同时使用的变量 或者如果在设置一个全局变量时未指定GLOBAL 或 则MySQL会产生错误 6 1 2变量 2 会话系统变量会话系统变量 sessionsystemvariable 只适用于当前的会话 大多数会话系统变量的名字和全局系统变量的名字相同 当启动会话的时候 每个会话系统变量都和同名的全局系统变量的值相同 一个会话系统变量的值是可以改变的 但是这个新的值仅适用于正在运行的会话 不适用于所有其他会话 例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变量 说明 在这个例子中 关键字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 使用SHOWVARIABLES语句可以得到系统变量清单 SHOWGLOBALVARIABLES返回所有全局系统变量 而SHOWSESSIONVARIABLES返回所有会话系统变量 如果不加关键字就默认为SHOWSESSIONVARIABLES 例6 20 得到系统变量清单 SHOWVARIABLES 要获得与样式匹配的具体的变量名称或名称清单 需使用LIKE子句 语句如下 SHOWVARIABLESLIKE max join size SHOWGLOBALVARIABLESLIKE max join size 要得到名称与样式匹配的变量的清单 需使用通配符 例如 SHOWVARIABLESLIKE character 6 2运算符与表达式 6 2 1算术运算符算术运算符在两个表达式上执行数学运算 这两个表达式可以是任何数字数据类型 算术运算符有 加 减 乘 除 和 求模 5种运算 1 运算符 运算符用于获得一个或多个值的和 SELECT1 2 3 09345 0 00000000001 0 00000000001 2 运算符 运算符用于从一个值中减去另一个值 并可以更改参数符号 SELECT200 201 0 14 0 1 2 23 4 6 2 1算术运算符 注意 若该操作符与BIGINT同时使用 则返回值也是一个BIGINT 这意味着在可能产生 263的整数运算中应当避免使用减号 否则会出现错误 其中 加 和 减 运算符还可用于对日期时间值 如DATETIME 进行算术运算 例如 SELECT 2008 01 20 INTERVAL22DAY 结果为 说明 INTERVAL关键字后面跟一个时间间隔 22DAY表示在当前的日期基础上加上22天 当前日期为2008 01 20 加上22天后为2008 02 11 3 运算符 运算符用来获得两个或多个值的乘积 SELECT5 12 5 0 11 2 8 2 19530415 19540319 6 2 1算术运算符 4 运算符 运算符用来获得一个值除以另一个值得到的商 SELECT12 2 1 6 0 1 23 7 23 00 7 00000 1 0 显然 除以零的除法是不允许的 如果这样做 MySQL会返回NULL 5 运算符 运算符用来获得一个或多个除法运算的余数 SELECT12 5 32 7 3 0 6 2 2比较运算符 比较运算符 又称关系运算符 用于比较两个表达式的值 其运算结果为逻辑值 可以为三种之一 1 真 0 假 及NULL 不能确定 表6 5列出了在MySQL中可以使用的各种比较运算符 表6 5比较运算符 有关比较运算已经在第4章SELECT语句的WHERE子句中介绍过 这里再做一些补充 比较运算符可以用于比较数字和字符串 数字作为浮点值比较 而字符串以不区分大小写的方式进行比较 除非使用特殊的BINARY关键字 前面已经介绍了在运算过程中MySQL能够自动地把数字转换为字符串 而在比较运算过程中 MySQL能够自动地把字符串转换为数字 6 2 2比较运算符 下面这个例子说明了在不同的情况下MySQL以不同的方式处理数字和字符串 例6 21 执行下列语句 SELECT5 5ab 5 5ab 结果为 1 运算符 运算符用于比较表达式的两边是否相等 也可以对字符串进行比较 示例如下 SELECT3 14 3 142 5 12 5 120 a A A B apple banana 6 2 2比较运算符 注意 因为在默认情况下MySQL以不区分大小写的方式比较字符串 所以表达式 a A 的结果为真 如果想执行区分大小写的比较 可以添加BINARY关键字 这意味着对字符串以二进制方式处理 当在字符串上执行比较运算时 MySQL将区分字符串的大小写 示范如下 SELECT Apple apple BINARY Apple apple 2 运算符与 运算符相对立的是 运算符 它用来检测表达式的两边是否不相等 如果不相等则返回真值 相等则返回假值 示例如下 SELECT55 56 a a 5a 5b 6 2 2比较运算符 SELECTNULLNULL 0NULL 00 3 运算符 运算符用来比较表达式的左边是小于或等于 大于或等于 小于还是大于它的右边 示例如下 SELECT10 10 10 9 103 142 6 2 3逻辑运算符 逻辑运算符用于对某个条件进行测试 运算结果为TRUE 1 或FALSE 0 MySQL提供的逻辑运算符如表6 6所示 表6 6逻辑运算符 1 NOT运算符逻辑运算符中最简单的NOT运算符 它对跟在它后面的逻辑测试判断取反 把真变假 假变真 例如 SELECTNOT1 NOT0 NOT 1 1 NOT 10 9 6 2 3逻辑运算符 2 AND运算符AND运算符用于测试两个或更多的值 或表达式求值 的有效性 如果它的所有成分为真 并且不是NULL 它返回真值 否则返回假值 例如 SELECT 1 1 AND 9 10 a a AND c d 3 OR运算符如果包含的值或表达式有一个为真并且不是NULL 不需要所有成分为真 它返回1 若全为假则返回0 例如 SELECT 1 1 OR 9 10 a b OR 1 2 6 2 3逻辑运算符 4 XOR运算符如果包含的值或表达式一个为真而另一个为假并且不是NULL 那么它返回真值 否则返回假值 例如 SELECT 1 1 XOR 2 3 1 2 XOR 9 10 6 2 4位运算符 位运算符在两个表达式之间执行二进制位操作 这两个表达式的类型可为整型或与整型兼容的数据类型 如字符型 但不能为image类型 位运算符如表6 7所示 表6 7位运算符 1 运算符和 6 2 4位运算符 说明 本例中13 8表示按13和8的二进制位按位进行与 OR 操作 2 运算符 运算符分别用于向左和向右移动位 例如 SELECT1 1 说明 本例中1的二进制为向左移动7位 最后得到的十进制数为128 64的二进制位向右移动1位 最后得到的十进制数为32 3 运算符 运算符执行位异或 XOR 操作 SELECT1 0 12 5 123 23 6 2 4位运算符 4 运算符 运算符执行位取反操作 并返回64位整型结果 SELECT 18446744073709551614 1 6 2 5运算符优先级 除了以上的运算符 MySQL还提供了其他一些常用的运算符 如BETWEEN运算符 IN运算符 ISNULL和ISNOTNULL运算符 LIKE运算符 REGEXP运算符等 这些在第4章SELECT语句中的WHERE子句中已经有过介绍 这里就不再展开讨论 下面讨论一下运算符的优先顺序 当一个复杂的表达式有多个运算符时 运算符优先级决定执行运算的先后次序 执行的顺序会影响所得到的运算结果 运算符优先级如表6 8所示 在一个表达式中按先高 优先级数字小 后低 优先级数字大 的顺序进行运算 表6 8运算符优先级 6 2 6表达式 前面已经涉及表达式这个概念 这里简单讨论一下 表达式就是常量 变量 列名 复杂计算 运算符和函数的组合 一个表达式通常可以得到一个值 与常量和变量一样 表达式的值也具有某种数据类型 可能的数据类型有字符类型 数值类型 日期时间类型 这样 根据表达式的值的类型 表达式可分为字符型表达式 数值型表达式和日期表达式 表达式还可以根据值的复杂性来分类 当表达式的结果只是一个值 如一个数值 一个单词或一个日期 这种表达式叫做标量表达式 例如 1 2 a b 当表达式的结果是由不同类型数据组成的一行值 这种表达式叫做行表达式 例如 学号 王林 计算机 50 10 当学号列的值为081101时 这个行表达式的值就为 081101 王林 计算机 500 当表达式的结果为0个 1个或多个行表达式的集合 那么这个表达式就叫做表表达式 表达式按照形式还可分为单一表达式和复合表达式 单一表达式就是一个单一的值 如一个常量或列名 复合表达式是由运算符将多个单一表达式连接而成的表达式 例如 1 2 3 a b 3 2008 01 20 INTERVAL2MONTH 表达式一般用在SELECT及SELECT语句的WHERE子句中 6 3系统内置函数 6 3 1数学函数数学函数用于执行一些比较复杂的算术操作 MySQL支持很多的数学函数 附录F中列出了比较重要的一些 若发生错误 所有的数学函数都会返回NULL 下面对一些常用的数学函数进行举例 1 GREATEST 和LEAST 函数GREATEST 和LEAST 是数学函数中经常使用的函数 它们的功能是获得一组数中的最大值和最小值 例如 SELECTGREATEST 10 9 128 1 LEAST 1 2 3 结果为 6 3 1数学函数 数学函数用于执行一些比较复杂的算术操作 MySQL支持很多的数学函数 附录F中列出了比较重要的一些 若发生错误 所有的数学函数都会返回NULL 下面对一些常用的数学函数进行举例 1 GREATEST 和LEAST 函数GREATEST 和LEAST 是数学函数中经常使用的函数 它们的功能是获得一组数中的最大值和最小值 例如 SELECTGREATEST 10 9 128 1 LEAST 1 2 3 结果为 数学函数还可以嵌套使用 例如 SELECTGREATEST 2 LEAST 0 3 LEAST 1 GREATEST 1 2 结果为 6 3 1数学函数 注意 MySQL不允许函数名和括号之间有空格 2 FLOOR 和CEILING 函数FLOOR 用于获得小于一个数的最大整数值 CEILING 函数用于获得大于一个数的最小整数值 例如 SELECTFLOOR 1 2 CEILING 1 2 FLOOR 9 9 CEILING 9 9 结果为 3 ROUND 和TRUNCATE 函数ROUND 函数用于获得一个数的四舍五入的整数值 SELECTROUND 5 1 ROUND 25 501 ROUND 9 8 结果为 6 3 1数学函数 TRUNCATE 函数用于把一个数字截取为一个指定小数个数的数字 逗号后面的数字表示指定小数的个数 SELECTTRUNCATE 1 54578 2 TRUNCATE 76 12 5 结果为 4 ABS 函数ABS 函数用来获得一个数的绝对值 例如 SELECTABS 878 ABS 8 345 结果为 6 3 1数学函数 5 SIGN 函数SIGN 函数返回数字的符号 返回的结果是正数 1 负数 1 或者零 0 SELECTSIGN 2 SIGN 2 SIGN 0 结果为 6 SQRT 函数SQRT 函数返回一个数的平方根 SELECTSQRT 25 SQRT 15 SQRT 1 结果为 6 3 1数学函数 7 POW 函数POW 函数以一个数作为另外一个数的指数 并返回结果 SELECTPOW 2 2 POW 10 2 POW 0 3 结果为 说明 第一个数表示是2的2次方 第二个表示10的 2次方 8 SIN COS 和TAN 函数SIN COS 和TAN 函数返回一个角度 弧度 的正弦 余弦和正切值 SELECTSIN 1 COS 1 TAN RADIANS 45 结果为 6 3 1数学函数 9 ASIN ACOS 和ATAN 函数ASIN ACOS 和ATAN 函数返回一个角度 弧度 的反正弦 反余弦和反正切值 SELECTASIN 1 ACOS 1 ATAN DEGREES 45 结果为 如果使用的是角度而不是弧度 可以使用DEGREES 和RADIANS 函数进行转换 10 BIN OTC 和HEX 函数BIN OTC 和HEX 函数分别返回一个数的二进制 八进制和十六进制值 这个值作为字符串返回 SELECTBIN 2 OCT 12 HEX 80 结果为 6 3 2聚合函数 MySQL有一组函数是特意为求和或者对表中的数据进行集中概括而设计的 这一组函数就叫做聚合函数 聚合函数常常用于对一组值进行计算 然后返回单个值 通过把聚合函数 如COUNT和SUM 添加到带有一个GROUPBY子句的SELECT语句块中 数据就可以聚合 聚合意味着是求一个和 平均 频次及子和 而不是单个的值 有关聚合函数的内容请参考4 2 1节 这里不再讨论 6 3 3字符串函数 因为MySQL数据库不仅包含数字数据 还包含字符串 因此MySQL有一套为字符串操作而设计的函数 在字符串函数中 包含的字符串必须要用单引号括起 附录F中列出了很多的字符串函数 下面对其中重要的一些进行介绍 1 ASCII 函数语法格式 ASCII char 返回字符表达式最左端字符的ASCII值 参数char的类型为字符型的表达式 返回值为整型 例6 22 返回字母A的ASCII码值 SELECTASCII A 结果为 6 3 3字符串函数 2 CHAR 函数语法格式 CHAR x1 x2 x3 将x1 x2 的ASCII码转换为字符 结果组合成一个字符串 参数x1 x2 x3 为介于0 255之间的整数 返回值为字符型 例6 23 返回ASCII码值为65 66 67的字符 组成一个字符串 SELECTCHAR 65 66 67 结果为 6 3 3字符串函数 3 LEFT和RIGHT函数语法格式 LEFT RIGHT str x 分别返回从字符串str左边和右边开始指定x个字符 例6 24 返回KC表中课程名最左边的3个字符 USEXSCJSELECTLEFT 课程名 3 FROMKC 执行结果为 6 3 3字符串函数 4 TRIM LTRIM和RTRIM函数语法格式 TRIM LTRIM RTRIM str 使用LTRIM和RTRIM分别删除字符串中前面的空格和尾部的空格 返回值为字符串 参数str为字符型表达式 返回值类型为varchar TRIM删除字符串首部和尾部的所有空格 例6 25 执行如下语句 SELECTTRIM MySQL 5 RPAD和LPAD函数语法格式 RPAD LPAD str n pad 使用RPAD和LPAD分别用字符串pad对字符串str的右边和左边进行填补直至str中字符数目达到n个 最后返回填补后的字符串 若str中的字符个数大于n 则返回str的前n个字符 例6 26 执行如下语句 SELECTRPAD 中国加油 8 LPAD welcome 10 结果为 6 3 3字符串函数 6 REPLACE函数语法格式 REPLACE str1 str2 str3 REPLACE函数用于用字符串str3替换str1中所有出现的字符串str2 最后返回替换后的字符串 例6 27 执行如下语句 SELECTREPLACE WelcometoCHINA o K 结果为 6 3 3字符串函数 7 CONCAT函数语法格式 CONCAT s1 s2 sn CONCAT函数用于连接指定的几个字符串 例6 28 执行如下语句 SELECTCONCAT 中国 加油 结果为 8 SUBSTRING函数语法格式 SUBSTRING expression Start Length 返回expression中指定的部分数据 参数expression可为字符串 二进制串 text image字段或表达式 Start Length均为整型 前者指定子串的开始位置 后者指定子串的长度 要返回字节数 如果expression是字符类型和二进制类型 则返回值类型与expression的类型相同 如果为text类型 返回的是varchar类型 6 3 3字符串函数 例6 29 如下程序在一列中返回XS表中所有女同学的姓氏 在另一列中返回名字 USEXSCJSELECTSUBSTRING 姓名 1 1 AS姓 SUBSTRING 姓名 2 LENGTH 姓名 1 AS名FROMXSWHERE性别 0ORDERBY姓名 执行结果为 6 3 3字符串函数 说明 LENGTH函数的作用是返回一个字符串的长度 9 STRCMP函数语法格式 STRCMP s1 s2 STRCMP函数用于比较两个字符串 相等返回0 s1大于s2返回1 s1小于s2返回 1 例6 30 执行如下语句 SELECTSTRCMP A A STRCMP ABC OPQ STRCMP T B 结果为 6 3 4日期和时间函数 MySQL有很多日期和时间数据类型 所以有相当多的操作日期和时间的函数 附录F中列出了大多数的日期和时间函数 下面介绍几个比较重要的函数 1 NOW 使用NOW 函数可以获得当前的日期和时间 它以YYYY MM DDHH MM SS的格式返回当前的日期和时间 SELECTNOW 2 CURTIME 和CURDATE CURTIME 和CURDATE 函数比NOW更为具体化 它们分别返回的是当前的时间和日期 没有参数 SELECTCURTIME CURDATE 3 YEAR YEAR 函数分析一个日期值并返回其中关于年的部分 SELECTYEAR 20080512142800 YEAR 1982 11 02 6 3 4日期和时间函数 4 MOTNTH 和MONTHNAME MOTNTH 和MONTHNAME 函数分别以数值和字符串的格式返回月的部分 SELECTMONTH 20080512142800 MONTHNAME 1982 11 02 5 DAYOFYEAR DAYOFWEEK 和DAYOFMONTH DAYOFYEAR DAYOFWEEK 和DAYOFMONTH 函数分别返回这一天在一年 一星期及一个月中的序数 SELECTDAYOFYEAR 20080512 DAYOFMONTH 2008 05 12 6 3 4日期和时间函数 SELECTDAYOFWEEK 20080512 6 DAYNAME 和MONTHNAME 相似 DAYNAME 以字符串形式返回星期名 SELECTDAYNAME 2008 06 01 6 3 4日期和时间函数 7 WEEK 和YEARWEEK WEEK 函数返回指定的日期是一年的第几个星期 而YEARWEEK 函数返回指定的日期是哪一年的哪一个星期 SELECTWEEK 2008 05 01 YEARWEEK 20080501 8 HOUR MINUTE 和SECOND HOUR MINUTE 和SECOND 函数分别返回时间值的小时 分钟和秒的部分 SELECTHOUR 155300 MINUTE 15 53 00 SECOND 143415 6 3 4日期和时间函数 9 DATE ADD 和DATE SUB DATE ADD 和DATE SUB 函数可以对日期和时间进行算术操作 它们分别用来增加和减少日期值 其使用的关键字如表6 9所示 表6 9DATE ADD 函数和DATE SUB 函数使用的关键字 6 3 4日期和时间函数 DATE ADD 和DATE SUB 函数的语法格式为 DATE ADD DATE SUB date INTERVALintkeyword date是需要的日期和时间 INTERVAL关键字表示一个时间间隔 int表示需要计算的时间值 keyword已经在表6 9中列出 DATE ADD函数是计算date加上间隔时间后的值 DATE SUB则是计算date减去时间间隔后的值 举例 SELECTDATE ADD 1986 08 08 INTERVAL17DAY SELECTDATE SUB 1998 08 2010 25 35 INTERVAL20MINUTE 6 3 4日期和时间函数 日期和时间函数在SQL语句中应用相当广泛 例6 31 求XS表中女学生的年龄 SELECT学号 姓名 YEAR NOW YEAR 出生日期 AS年龄FROMXSWHERE性别 0 查询结果为 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函数使用UNIXcrypt 系统加密字符串 ENCRYPT str salt 函数接收要加密的字符串和用于加密过程的salt 一个可以确定唯一口令的字符串 在Windows上不可用 6 3 5加密函数 4 PASSWORD函数格式为 PASSWORD str 返回字符串str加密后的密码字符串 适合于插入到MySQL的安全系统 该加密过程不可逆 和UNIX密码加密过程使用不同的算法 主要用于MySQL的认证系统 例6 32 返回字符串 MySQL 的加密版本 SELECTPASSWORD MySQL 6 3 6控制流函数 MySQL有几个函数是用来进行条件操作的 这些函数可以实现SQL的条件逻辑 允许开发者将一些应用程序业务逻辑转换到数据库后台 1 IFNULL和NULLIF函数IFNULL函数的语法格式为 IFNULL expr1 expr2 此函数的作用是 判断参数expr1是否为NULL 当参数expr1为NULL时返回expr2 不为NULL时返回expr1 IFNULL的返回值是数字或字符串 例6 33 执行如下语句 SELECTIFNULL 1 2 IFNULL NULL MySQL IFNULL 1 0 10 结果为 6 3 6控制流函数 NULLIF函数的语法格式为 NULLIF expr1 expr2 NULLIF函数用于检验提供的两个参数是否相等 如果相等 则返回NULL 如果不相等就返回第一个参数 例6 34 执行如下语句 SELECTNULLIF 1 1 NULLIF A B NULLIF 2 3 3 4 结果为 6 3 6控制流函数 2 IF函数和许多脚本语言提供的IF 函数一样 MySQL的IF 函数也可以建立一个简单的条件测试 语法格式如下 IF expr1 expr2 expr3 这个函数有3个参数 第一个是要被判断的表达式 如果表达式为真 IF 将会返回第二个参数 如果为假 IF 将会返回第三个参数 例6 35 判断2 4是否大于9 5 是则返回 是 否则返回 否 SELECTIF 2 4 9 5 是 否 结果为 6 3 6控制流函数 例6 36 返回XS表名字为两个字的学生姓名 性别和专业名 性别值如为0则显示为 女 为1则显示为 男 SELECT姓名 IF 性别 0 女 男 AS性别 专业名FROMXSWHERE姓名LIKE 结果为 6 3 7格式化函数 MySQL还有一些函数是特意为格式化数据设计的 1 FORMAT 函数语法格式为 FORMAT x y FORMAT 函数把数值格式化为以逗号间隔的数字序列 FORMAT 的第一个参数x是被格式化的数据 第二个参数y是结果的小数位数 例如 SELECTFORMAT 11111111111 23654 2 FORMAT 5468 4 结果为 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 10MySQL日期 时间格式化代码 6 3 7格式化函数 举例 SELECTDATE FORMAT NOW W d M Y r 注意 这两个函数是对大小写敏感的 3 INET NTOA 和INET ATON 函数MySQL中的INET NTOA 和INET ATON 函数可以分别把IP地址转换为数字或者进行相反的操作 如下面的例子所示 SELECTINET ATON 192 168 1 1 6 3 8类型转换函数 MySQL提供CAST 函数进行数据类型转换 它可以把一个值转换为指定的数据类型 语法格式 CAST expr AStype expr是CAST函数要转换的值 type是转换后的数据类型 在CAST函数中MySQL支持这几种数据类型 BINARY CHAR DATE TIME DATETIME SIGNED和UNSIGNED 通常情况下 当使用数值操作时 字符串会自动地转换为数字 因此下面例子中两种操作得到相同的结果 SELECT1 99 1 CAST 99 ASSIGNED 6 3 8类型转换函数 字符串可以指定为BINARY类型 这样它们的比较操作就成为大小写敏感的 使用CAST 函数指定一个字符串为BINARY和字符串前面使用BINARY关键词具有相同的作用 例6 37 执行如下语句 SELECT a BINARY A a CAST A ASBINARY 结果为 说明 两个表达式的结果都为零表示两个表达式都为假 MySQL还可以强制将日期和时间函数的值作为一个数而不是字符串输出 例6 38 将当前日期显示成数值形式 SELECTCAST CURDATE ASSIGNED 结果为 6 3 9系统信息函数 MySQL还具有一些特殊的函数用来获得系统本身的信息 表6 11列出了大部分信息函数 表6 11MySQL信息函数 6 3 9系统信息函数 下面对其中一些信息函数进行举例 1 DATABASE USER 和VERSION 函数可以分别返回当前所选数据库 当前用户和MySQL版本信息 SELECTDATABASE USER VERSION 结果为 2 BENCHMARK 函数用于重复执行n次表达式expr 它可以被用于计算MySQL处理表达式的速度 结果值通常为零 另一种用处来自MySQL客户端内部 能够报告问询执行的次数 根据经过的时间值可以推断服务器的性能 例如 SELECTBENCHMARK 10000000 ENCODE hello goodbye 结果为 6 3 9系统信息函数 这个例子中 MySQL计算ENCODE hello goodbye 表达式10000000次需要2 16秒 3 FOUND ROWS 函数用于返回最后一个SELECT语句返回的记录行的数目 如最后执行的SELECT语句是 SELECT FROMXS 之后执行如下语句 SELECTFOUND ROWS 结果为 6 3 9系统信息函数 说明 SELECT语句可能包括一个LIMIT子句 用来限制服务器返回客户端的行数 在有些情况下 需要不用再次运行该语句而得知在没有LIMIT时到底该语句返回了多少行 为了知道这个行数 包括在SELECT语句中选择SQL CALC FOUND ROWS 随后调用FOUND ROWS 例如 执行如下语句 SELECTSQL CALC FOUND ROWS FROMXSWHERE性别 1LIMIT5 之后可以使用FOUND ROWS 函数 显示在没有LIMIT子句的情况下SELECT语句所返回的行数 结果如下