网络数据库技术6Mysql语言结构及内置函数

1、网络数据库技术网络数据库技术 MySQL语言结构及内置函数语言结构及内置函数 SQL语言的组成语言的组成 nDDL nDML nDCL nMySQL增加的语言元素 主要内容主要内容 n常量和变量常量和变量 n运算符与表达式运算符与表达式 n系统内置函数系统内置函数 一一. 常量和变量常量和变量 n常量常量 n在程序运行过程中,值不改变的量 常量常量 n1. 字符串常量 n2. 数值常量 n3. 十六进制常量 n4. 时间日期常量 n5. 位字段值 n6. 布尔值 n7. NULL值 1. 字符串字符串常量常量 n字符串是指用单引号或双引号括起来的字符序 列， n分为ASCII字符串常量和Uni

2、code 字符串常量。 n转义序列（以“”开始） 2. 数值数值常量常量 n数值常量可以分为整数常量和浮点数常量。 n整数常量即不带小数点的十进制数 n浮点数常量是使用小数点的数值常量 3. 十六进制常量十六进制常量 n一个十六进制值通常指定为一个字符串常量 n每对十六进制数字被转换为一个字符， n最前面有一个大写字母X或小写字x或0 x nX41 x41 0 x41 nCAST(.AS UNSIGNED)函数:字符串转数值 nHEX()函数:字符串或数字转换为十六进制格式的字符 串 A 4. 日期时间日期时间常量常量 n用单引号将表示日期时间的字符串括起来构成 nDATE类型， 1999-0

3、6-17 nTIME类型，12:30:43.00013 nDATETIME或TIMESTAMP类型 n 1999-06-17 12:30:43 5. 位字段位字段值值 nbvalue符号写位字段值,表示特殊的符号 nvalue是一个用0和1写成的二进制值。 n使用BIN函数可以将位字段常量显示为二进制 格式。 n使用OCT函数可以将位字段常量显示为数值型 格式。 6. 布尔布尔值值 n布尔值只包含两个可能的值：TRUE和FALSE nFALSE的数字值为“0” nTRUE的数字值为“1” 7. NULL值值 nNULL值可适用于各种列类型 n它通常用来表示“没有值”、“无数据”等意 义 n不同

4、于数字类型的“0”或字符串类型的空字符 串。 一一. 常量和变量常量和变量 n变量变量 n用于临时存放数据 n变量中的数据随程序的运行而变化 n两个属性 n变量名 n数据类型 变量变量 n1. 用户变量 n2. 系统变量 1. 用户用户变量变量 n先定义定义和初始化初始化。 n如果使用没有初始化的变量，它的值为NULL。 n定义和初始化语法格式为： nSET user_variable1expression1 ,user_variable2= expression2 , n也可以用其他SQL语句代替SET语句来为用户变量分配 一个值。在这种情况下，分配符必须为“:=”，而不能用 “=”，因为在

5、非SET语句中“=”被视为比较操作符。 例例 创建创建用户变量用户变量name并赋值为并赋值为“王林王林”。 SET name=王林王林; 注意：注意：符号必须放在一个用户变量的前面，以便将它符号必须放在一个用户变量的前面，以便将它 和列名区分开。和列名区分开。name的数据类型跟的数据类型跟 王林王林 的数据类型是的数据类型是 一样的，字符集和校对规则也是一样的。如果给一样的，字符集和校对规则也是一样的。如果给name变变 量重新赋不同类型的值，则量重新赋不同类型的值，则name的数据类型也会跟着改的数据类型也会跟着改 变。变。 还可以同时定义多个变量，中间用逗号隔开。还可以同时定义多个变量

6、，中间用逗号隔开。 例例 【例例】 创建用户变量创建用户变量user1并赋值为并赋值为1，user2赋值为赋值为 2，user3赋值为赋值为3。 SET user1=1, user2=2, user3=3; 定义用户变量时变量值可以是一个表达式。定义用户变量时变量值可以是一个表达式。 【例例】 创建用户变量创建用户变量user4，它的值为，它的值为user3的值加的值加1。 SET user4=user3+1; 【例例】 查询上例中创建的变量查询上例中创建的变量name的值。的值。 SELECT name; 例例 使用查询给变量赋值。使用查询给变量赋值。 SET student=(SELECT

7、 XM FROM XS WHERE 学号学号=0001); 查询查询表表XS中名字等于上例中中名字等于上例中student值的学生信息值的学生信息。 SELECT * FROM XS WHERE XM=student; 说明说明 HAVING、GROUP BY或或ORDER BY子句中，不能使用包含子句中，不能使用包含 SELECT列表中所设的变量的表达式。列表中所设的变量的表达式。 对于对于SET语句，可以使用语句，可以使用“=”或或“:=”作为分配符。作为分配符。 也可以用其他也可以用其他SQL语句代替语句代替SET语句来为用户变量分配一个值。语句来为用户变量分配一个值。 在这种情况下，分

8、配符必须为在这种情况下，分配符必须为“:=”，而不能用，而不能用“=”，因为在非，因为在非SET语语 句中句中“=”被视为比较操作符。被视为比较操作符。 【例例】 执行如下语句：执行如下语句： SELECT t2:=(t2:=2)+5 AS t2; 结果结果t2的值为的值为7。 2. 系统系统变量变量 nMySQL特定的设置 nVERSION n特定的系统变量是要省略这两个符号的 nCURRENT_DATE（系统日期）、 CURRENT_TIME（系统时间）、 CURRENT_TIMESTAMP（系统日期和时间） 和CURRENT_USER（SQL用户的名字）。 全局系统变量全局系统变量 n指

9、定了GLOBAL或global.关键字 n当MySQL启动的时候，全局系统变量就初始化 了，并且应用于每个启动的会话。 n如果使用GLOBAL（要求SUPER权限）来设 置系统变量，则该值被记住，并被用于新的连 接，直到服务器重新启动为止。 会话系统变量会话系统变量 n只适用于当前的会话 n当启动会话的时候，每个会话系统变量都和同 名的全局系统变量的值相同。 n一个会话系统变量的值是可以改变的，但是这 个新的值仅适用于正在运行的会话，不适用于 所有其他会话。 系统变量系统变量 nMySQL对于大多数系统变量都有默认值对于大多数系统变量都有默认值。当 数据库服务器启动的时候，就使用这些值。 n也

10、可以在MySQL安装文件夹下的my.ini选项文 件中修改这些值。当数据库服务器启动的时候， 这个文件被自动读取。 n如果要将一个系统变量值设置为MySQL默认值， 可以使用DEFAULT关键字。 SHOW VARIABLES命令命令 n使用SHOW VARIABLES语句可以得到系统变量清单。 nSHOW GLOBAL VARIABLES返回所有全局系统变量， nSHOW SESSION VARIABLES返回所有会话系统变 量。 n如果不加关键字就默认为默认为SHOW SESSION VARIABLES。 二二. 运算符与表达式运算符与表达式 n1. 算术运算符算术运算符 n2. 比较运算

11、符比较运算符 n3. 逻辑运算符逻辑运算符 n4. 位运算符位运算符 n5. 运算符优先级运算符优先级 n6. 表达式表达式 1. 算术运算算术运算符符 n五种: n+（加）、（减）、*（乘）、/（除）和% （求模） 2. 比较比较运算符运算符 n又称关系运算符 n用于比较两个表达式的值，其运算结果为逻辑 值，可以为三种之一：1（真）、0（假）及 NULL（不能确定） 运 算 符含 义运 算 符含 义 =等于大于、!=不等于 小于相等或都等于空 =大于等于 3. 逻辑运算逻辑运算符符 n逻辑运算符用于对某个条件进行测试，运算结 果为TRUE（1）或FALSE（0） 算术、逻辑运算算术、逻辑运算

12、 n比较运算 运算符运算符说明说明 IS , IS NOT判定布尔值判定布尔值 IS True , IS NOT True , IS NULL ,=大于，大于等于大于，大于等于 ,=小于，小于等于小于，小于等于 =等于等于 , !=不等于不等于 BETWEEN m AND nm 和和n之间的值之间的值 IN, NOT IN检查是否在（或不在）一组值之中检查是否在（或不在）一组值之中 4. 位位运算符运算符 n位运算符在两个表达式之间执行二进制位操作， 这两个表达式的类型可为整型或与整型兼容的 数据类型（如字符型，但不能为image类型） 5. 运算符优先级运算符优先级 6. 表达式表达式 n表

13、达式就是常量、变量、列名、复杂计算、运 算符和函数的组合 n一个表达式通常可以得到一个值。与常量和变 量一样，表达式的值也具有某种数据类型 三三. 系统内置函数系统内置函数 n（1）数学函数）数学函数 n（2）聚合函数）聚合函数 n（3）字符串函数）字符串函数 n（4）日期和时间函数）日期和时间函数 n（5）加密函数）加密函数 n（6）控制流函数）控制流函数 n（7）格式化函数）格式化函数 n（8）类型转换函数）类型转换函数 n（9）系统信息函数）系统信息函数 聚合函数聚合函数 n对数据进行集中概括 n面向一组数据，对数据进行聚合运算后，返回 单个值 n常见应用 n带group by子句的se

14、lect语句中，完成统计功能 n基本语法 nselect function(列） from 表名 group by； 常用的聚合函数常用的聚合函数 函数名函数名说明说明 COUNT(DISTINCT 列)返回列去重后的行数 COUNT(列)/COUNT(*)返回列的行数/全部行数 MAX(列)返回列的最大值 MIN(列)返回例的最小值 SUM(列)返回列的总和 AVG(列)返回列的平均值 GROUP_CONCAT(列)返回一组值的连接字符串 （MySQL独有） song.song_list select * from song_list; count说明说明 基本一样去掉null group_

15、concat() n显示每张专辑的歌曲列表 group_concat() n其返回值的最大长度默认为1024 SET GLOBAL | SESSION group_concat_max_len = val ; n可以改变其长度 使用聚合函数作表行列转换使用聚合函数作表行列转换 表行列转换实现表行列转换实现 可以省略 预定义函数预定义函数 n预定义函数面向单值数据，返回一对一的处理结 果（聚合函数可以理解为多对一） n基本语法 nselect function(列） from 表名； nselect * from 表名 where 列名 function(value); 预定义函数字符串函数预定

16、义函数字符串函数 函数名函数名说明说明 LENGTH()返回列的返回列的字节数字节数 CHAR_LENGTH()返回列的返回列的字符数字符数 TRIM(),RTRIM(),LTRIM()去除去除两边两边空格空格/右边右边/左边左边 SUBSTRING(str , pos , len)从从pos位置截取位置截取(长度为长度为len的）字符串的）字符串str LOCATE(substr , str , pos)返回返回substr在在str字符串中的位置字符串中的位置 REPLACE(str , from_str , to_str)将将str字符串中字符串中from_str替换成替换成to_str

17、 LOWER() , UPPER()字符串转换为小写字符串转换为小写/大写大写 LEFT/RIGHT(str , x)返回字符串返回字符串str左边（右边）左边（右边）x个字符个字符 实例实例 nlength() vs. char_length() 实例实例substring() 实例实例locate() 实例实例left()/right() 预定义函数时间处理函数预定义函数时间处理函数 函数名函数名说明说明 CURDATE()当前日期日期 CURTIME()当前时间时间 NOW()当前时间和日期时间和日期 UNIX_TIMESTAMP()当前时间戳时间戳 FROM_UNIXTIME()转换为

18、时间戳 DATE_FORMAT(date , format)按指定格式指定格式显示时间 DATE_ADD(date , INTERVAL expr unit)计算指定日期日期向后加向后加一段时间的日期 DATE_SUB(date , INTERVAL expr unit)计算指定日期日期向前减向前减一段时间的日期 实例当前时间实例当前时间 实例时间戳实例时间戳 实例时间格式实例时间格式 MySQL5.7 手册手册 Date format MySQL5.7手册手册 Date format 实例实例时间日期运算时间日期运算1 MySQL5.7手册手册 DATE_ADD(date , INTERVAL expr unit) DATE_SUB(date , INTERVAL expr unit) 实例时间日期实例时间日期运算运算2 其它常用时间日期函数实例其它常用时间日期函数实例 预定义函数预定义函数数字数字处