MySQL数据库基础与实例教程第6章

孔祥盛MySQL数据库基础与实例教程之MySQL编程基础为了便于MySQL代码维护，以及提高MySQL代码的重用性，MySQL开发人员经常将频繁使用的业务逻辑封装成存储程序，MySQL的存储程序分为四类：函数、触发器、存储过程以及事件。内容一览123本章首先介绍了MySQL编程的基础知识，然后讲解了自定义函数的实现方法，接着介绍了MySQL常用的系统函数，最后结合“选课系统”，编写自定义函数模拟实现了中文全文检索。4自定义函数MySQL编程基础知识系统函数中文全文检索的模拟实现内容一览123MySQL程序设计结构是在SQL标准的基础上增加了一些程序设计语言的元素，其中包括常量、变量、运算符、表达式、流程控制以及函数等内容。用户自定义变量常量运算符与表达式45begin-end语句块重置命令结束标记6.1MySQL编程基础知识按照MySQL的数据类型进行划分，可以将常量划分为字符串常量、数值常量、十六进制常量、日期时间常量、二进制常量以及NULL。6.1.1常量1．字符串常量字符串常量是指用单引号或双引号括起来的字符序列。select'I\'ma\teacher'ascol1,you'reastude\ntascol2;由于大多编程语言（例如Java、C等）使用双引号表示字符串，为了便于区分，在MySQL数据库中推荐使用单引号表示字符串。6.1.1常量2．数值常量数值常量可以分为整数常量（例如2013）和小数常量（例如5.26、101.5E5），这里不再赘述。6.1.1常量3．日期时间常量日期时间常量是一个符合特殊格式的字符串。例如'14:30:24'是一个时间常量，'2008-05-1214:28:24'是一个日期时间常量。日期时间常量的值必须符合日期、时间标准，例如'1996-02-31'是错误的日期常量。6.1.1常量3．日期时间常量日期时间常量是一个符合特殊格式的字符串。例如'14:30:24'是一个时间常量，'2008-05-1214:28:24'是一个日期时间常量。日期时间常量的值必须符合日期、时间标准，例如'1996-02-31'是错误的日期常量。6.1.1常量4.布尔值布尔值只包含两个可能的值：true和false。说明：使用select语句显示布尔值true或者false时，会将其转换为字符串“0”或者字符串“1”。6.1.1常量5．二进制常量二进制常量由数字“0”和“1”组成。二进制常量的表示方法：前缀为“b”，后面紧跟一个“二进制”字符串。例如下面的select语句输出三个字符。其中b‘111101’表示“等号”，b‘1’表示“笑脸”，b‘11’表示“心”。selectb'111101',b'1',b'11';6.1.1常量6．十六进制常量十六进制常量由数字“0”到“9”及字母“a”到“f”或“A”到“F”组成（字母不区分大小写）。十六进制常量有两种表示方法。6.1.1常量6．十六进制常量第一种表示方法：前缀为大写字母“X”或小写字母“x”，后面紧跟一个“十六进制”字符串。例如selectX'41',x'4D7953514C';其中X'41'表示大写字母A。x'4D7953514C'表示字符串MySQL。6.1.1常量6．十六进制常量第二种表示方法：前缀为“0x”，后面紧跟一个“十六进制数”（不用引号）。例如select0x41,0x4D7953514C;其中0x41表示大写字母A。0x4D7953514C表示字符串MySQL。6.1.1常量小结：可以看到，使用select语句显示十六进制数时，会将十六进制数自动转换为“字符串”再进行显示。如果需要将一个字符串或数字转换为十六进制格式的字符串，可以用hex()函数实现。例如selecthex('MySQL');hex()函数将“MySQL”字符串转换为十六进制数4D7953514C。6.1.1常量小结：十六进制数与字符之间存在一一对应关系，利用这个特点，可以模拟实现中文全文检索。7.NULL值NULL值可适用于各种字段类型，它通常用来表示“值不确定”、“没有值”等意义，NULL值参与算术运算、比较运算以及逻辑运算时，结果依然为NULL。6.1.1常量变量分为系统变量（以@@开头）以及用户自定义变量。用户自定义变量分为用户会话变量（以@开头）以及局部变量（不以@开头）。6.1.2用户自定义变量1．用户会话变量MySQL客户机1定义了会话变量，会话期间，该会话变量一直有效；MySQL客户机2不能访问MySQL客户机1定义的会话变量；MySQL客户机1关闭或者MySQL客户机1与服务器断开连接后，MySQL客户机1定义的所有会话变量将自动释放，以便节省MySQL服务器的内存空间。6.1.2用户自定义变量实例MySQL客户机1MySQL客户机2MySQL服务器会话1会话2服务器内存会话1使用的用户会话变量仅在会话1的服务器连接中有效。在会话2中无效，或者关闭会话1服务器连接后无效会话1会话2会话2使用的用户会话变量仅在会话2的服务器连接中有效。在会话1中无效，或者关闭会话2服务器连接后无效用户会话变量6.1.2用户自定义变量1．用户会话变量系统会话变量与用户会话变量的共同之处在于：变量名大小写不敏感。系统会话变量与用户会话变量的区别在于：1．用户会话变量一般以一个“@”开头；系统会话变量以两个“@”开头。2．系统会话变量无需定义可以直接使用。6.1.2用户自定义变量（1）用户会话变量的定义与赋值一般情况下，用户会话变量的定义与赋值会同时进行。用户会话变量的定义与赋值有两种方法：使用set命令或者使用select语句。方法一：使用set命令定义用户会话变量，并为其赋值，语法格式如下：set@user_variable1＝expression1[,@user_variable2=expression2,…]6.1.2用户自定义变量说明：用户会话变量的数据类型是根据赋值运算符“=”右边表达式的计算结果自动分配的。也就是说，等号右边的值（包括字符集和字符序）决定了用户会话变量的数据类型（包括字符集和字符序）。6.1.2用户自定义变量方法二：使用select语句定义用户会话变量，并为其赋值，语法格式有两种。第一种语法格式：select@user_variable1:＝expression1[,user_variable2:=expression2,…]第二种语法格式：selectexpression1into@user_variable1,expression2into@user_variable2,…6.1.2用户自定义变量方法二：使用select语句定义用户会话变量，说明：第一种语法格式中需要使用“:＝”赋值语句，原因在于“=”是为“比较”保留的。第一种与第二种语法格式的区别在于：第一种语法格式中的select语句会产生结果集，第二种语法格式中的select语句，仅仅用于会话变量的定义及赋值（但不会产生结果集）。6.1.2用户自定义变量2．用户会话变量与SQL语句任务布置1：上机操作，完成本书场景描述1：用户会话变量与SQL语句的任务要求。注意：哪些方法的select语句产生结果集？哪些方法的select语句不产生结果集？6.1.2用户自定义变量3．局部变量declare命令专门用于定义局部变量及对应的数据类型。局部变量必须定义在存储程序中（例如函数、触发器、存储过程以及事件中），并且局部变量的作用范围仅仅局限于存储程序中，脱离存储程序，局部变量没有丝毫意义。局部变量主要用于下面三种场合。6.1.2用户自定义变量3．局部变量场合一：局部变量定义在存储程序的begin-end语句块（稍后介绍）之间。此时局部变量首先必须使用declare命令定义，并且必须指定局部变量的数据类型。只有定义局部变量后，才可以使用set命令或者select语句为其赋值。6.1.2用户自定义变量3．局部变量场合二：局部变量作为存储过程或者函数的参数使用，此时虽然不需要使用declare命令定义，但需要指定参数的数据类型。6.1.2用户自定义变量3．局部变量场合三：局部变量也可以用在SQL语句中。数据检索时，如果select语句的结果集是单个值，可以将select语句的返回结果赋予局部变量，局部变量也可以直接嵌入到select、insert、update以及delete语句的条件表达式中。6.1.2用户自定义变量4．局部变量与用户会话变量的区别（1）用户会话变量名以“@”开头，而局部变量名前面没有“@”符号。6.1.2用户自定义变量（2）局部变量使用declare命令定义（存储过程参数、函数参数除外），定义时必须指定局部变量的数据类型；局部变量定义后，才可以使用set命令或者select语句为其赋值。用户会话变量使用set命令或select语句定义并进行赋值，定义用户会话变量时无需指定数据类型。诸如“declare@student_noint;”的语句是错误语句，用户会话变量不能使用declare命令定义。6.1.2用户自定义变量4．局部变量与用户会话变量的区别（3）用户会话变量的作用范围与生存周期大于局部变量。局部变量如果作为存储过程或者函数的参数，此时在整个存储过程或函数内中有效；如果定义在存储程序的begin-end语句块中，此时仅在当前的begin-end语句块中有效。用户会话变量在本次会话期间一直有效，直至关闭服务器连接。6.1.2用户自定义变量4．局部变量与用户会话变量的区别（4）如果局部变量嵌入到SQL语句中，由于局部变量名前没有“@”符号，这就要求局部变量名不能与表字段名同名，否则将出现无法预期的结果。6.1.2用户自定义变量关于局部变量的其他说明：在MySQL数据库中，由于局部变量涉及begin-end语句块、函数、存储过程等知识，局部变量的具体使用方法将结合这些知识稍后一块儿进行讲解。declare命令尽量写在begin-end语句块的开头，尽量写在任何其他语句的前面。6.1.2用户自定义变量根据运算符功能的不同，可将MySQL的运算符分为算术运算符、比较运算符、逻辑运算符以及位操作运算符。1．算术运算符算术运算符用于两个操作数之间执行算术运算。常用的算术运算符有：+（加）、-（减）、*（乘）、/（除）、%（求余）以及div（求商）等6种运算符。6.1.3运算符与表达式2．比较运算符比较运算符（又称关系运算符）用于比较操作数之间的大小关系，其运算结果要么为true、要么为false、要么为NULL（不确定）。运算符含义=等于大于小于=大于等于=小于等于、!=不等于=相等或都等于空isnull是否为NULLbetween…and…是否在区间内in是否在集合内like模式匹配regexp正则表达式模式匹配6.1.3运算符与表达式select'ab'='ab','ab'='ab','b''a',NULL=NULL,NULL=NULL,NULLisNULL;结论：字符串进行比较时，会截掉字符串尾部的空格字符，然后进行比较。6.1.3运算符与表达式3．逻辑运算符逻辑运算符（又称布尔运算符）对布尔值进行操作，其运算结果要么为true、要么为false、要么为NULL（不确定）。运算符含义not或!逻辑非and或&&逻辑与or或||逻辑或xor逻辑异或6.1.3运算符与表达式4．位运算符位运算符对二进制数据进行操作（如果不是二进制类型的数，将进行类型自动转换），其运算结果为二进制数。使用select语句显示二进制数时，会将其自动转换为十进制数显示。6.1.3运算符与表达式4．位运算符运算符运算规则&按位与|按位或^按位异或~按位取反位右移位左移6.1.3运算符与表达式[开始标签:]begin[局部]变量的声明;错误触发条件的声明;游标的声明;错误处理程序的声明;业务逻辑代码;end[结束标签];6.1.5重置命令结束标记delimiter$$select*fromstudentwherestudent_namelike'张_'$$delimit