数据库编程ppt课件.ppt

数据库编程 1 项目知识要点与目标 2 任务一MySQL语言结构 1 字符串常量字符串是指用单引号或双引号括起来的字符序列 分为ASCII字符串常量和Unicode字符串常量 ASCII字符串常量是用单引号括起来的 由ASCII字符构成的符号串 举例 hello Howareyou Unicode字符串常量与ASCII字符串常量相似 但它前面有一个N标志符 N代表SQL 92标准中的国际语言 NationalLanguage N前缀必须为大写 只能用单引号括起字符串 举例 N hello N Howareyou Unicode数据中的每个字符用两个字节存储 而每个ASCII字符用一个字节存储 在字符串中不仅可以使用普通的字符 也可使用几个转义序列 它们用来表示特殊的字符 例8 1 执行如下语句 SELECT This nIs nFour nLines 其中 n 表示回车 3 常量 2 数值常量数值常量可以分为整数常量和浮点数常量 3 十六进制常量一个十六进制值通常指定为一个字符串常量 每对十六进制数字被转换为一个字符 其最前面有一个大写字母 X 或小写字 x 4 日期时间常量日期时间常量 用单引号将表示日期时间的字符串括起来构成 日期型常量包括年 月 日 数据类型为DATE 表示为 1999 06 17 这样的值 时间型常量包括小时数 分钟数 秒数及微秒数 数据类型为TIME 如 12 30 43 00013 日期 时间的组合 数据类型为DATETIME或TIMESTAMP 如 1999 06 1712 30 43 4 常量 5 位字段值可以使用b value 符号写位字段值 value是一个用0和1写成的二进制值 直接显示b value 的值可能是一系列特殊的符号 例如 b 0 显示为空白 b 1 显示为一个笑脸图标 使用BIN函数可以将位字段常量显示为二进制格式 6 布尔值布尔值只包含两个可能的值 TRUE和FALSE FALSE的数字值为 0 TRUE的数字值为 1 7 NULL值NULL值可适用于各种列类型 它通常用来表示 没有值 无数据 等意义 并且不同于数字类型的 0 或字符串类型的空字符串 5 用户变量 变量用于临时存放数据 变量有名字及其数据类型两个属性 变量名用于标识该变量 变量的数据类型确定了该变量存放值的格式及允许的运算 MySQL中根据变量的定义方式 变量可分为用户变量和系统变量 1 用户变量用户可以在表达式中使用自己定义的变量 这样的变量叫做用户变量 在使用用户变量前必须定义和初始化 如果使用没有初始化的变量 它的值为NULL 定义和初始化一个变量可以使用SET语句语法格式为 SET user variable1 expression1 user variable2 expression2 其中 user variable1 user variable2为用户变量名 变量名可以由当前字符集的文字数字字符 和 组成 6 变量举例 例8 2 创建用户变量name并赋值为 王林 SET name 王林 还可以同时定义多个变量 中间用逗号隔开 例8 3 创建用户变量user1并赋值为1 user2赋值为2 user3赋值为3 SET user1 1 user2 2 user3 3 定义用户变量时变量值可以是一个表达式 例8 4 创建用户变量user4 它的值为user3的值加1 SET user4 user3 1 在一个用户变量被创建后 它可以以一种特殊形式的表达式用于其他SQL语句中 变量名前面也必须加上符号 例8 5 创建并查询用户变量name的值 SET name 王林 SELECT name 7 变量举例 例8 6 使用查询给变量赋值 SET student SELECT姓名FROMXSWHERE学号 081101 例8 7 查询表XS中名字等于例6 11中student值的学生信息 SELECT学号 姓名 专业名 出生日期FROMXSWHERE姓名 student 说明 在SELECT语句中 表达式发送到客户端后才进行计算 这说明在HAVING GROUPBY或ORDERBY子句中 不能使用包含SELECT列表中所设的变量的表达式 对于SET语句 可以使用 或 作为分配符 分配给每个变量的值可以为整数 实数 字符串或NULL值 也可以用其他SQL语句代替SET语句来为用户变量分配一个值 在这种情况下 分配符必须为 而不能用 因为在非SET语句中 被视为比较操作符 例8 8 执行如下语句 SELECT t2 t2 2 5ASt2 结果t2的值为7 8 系统变量 MySQL有一些特定的设置 当MySQL数据库服务器启动的时候 这些设置被读取来决定下一步骤 例如 有些设置定义了数据如何被存储 有些设置则影响到处理速度 还有些与日期有关 这些设置就是系统变量 和用户变量一样 系统变量也是一个值和一个数据类型 但不同的是 系统变量在MySQL服务器启动时就被引入并初始化为默认值 例8 9 获得现在使用的MySQL版本 SELECT VERSION 说明 在MySQL中 系统变量VERSION的值设置为版本号 在变量名前必须加两个 符号才能正确返回该变量的值 大多数的系统变量应用于其他SQL语句中时 必须在名称前加两个 符号 而为了与其他SQL产品保持一致 某些特定的系统变量是要省略这两个 符号的 如CURRENT DATE 系统日期 例8 10 获得系统当前时间 SELECTCURRENT TIME 9 运算符 1 算术运算符算术运算符在两个表达式上执行数学运算 这两个表达式可以是任何数字数据类型 算术运算符有 加 减 乘 除 和 求模 5种运算 2 比较运算符比较运算符 又称关系运算符 用于比较两个表达式的值 其运算结果为逻辑值 可以为三种之一 1 真 0 假 及NULL 不能确定 3 逻辑运算符逻辑运算符用于对某个条件进行测试 运算结果为TRUE 1 或FALSE 0 4 运算符优先级当一个复杂的表达式有多个运算符时 运算符优先级决定执行运算的先后次序 执行的顺序会影响所得到的运算结果 运算符优先级如下表所示 10 表达式 表达式就是常量 变量 列名 复杂计算 运算符和函数的组合 一个表达式通常可以得到一个值 与常量和变量一样 表达式的值也具有某种数据类型 可能的数据类型有字符类型 数值类型 日期时间类型 这样 根据表达式的值的类型 表达式可分为字符型表达式 数值型表达式和日期表达式 表达式按照形式还可分为单一表达式和复合表达式 单一表达式就是一个单一的值 如一个常量或列名 复合表达式是由运算符将多个单一表达式连接而成的表达式例如 1 2 3 a b 3 2008 01 20 INTERVAL2MONTH 表达式一般用在SELECT及SELECT语句的WHERE子句中 11 系统内置函数 1 有一组数 12 34 5 5 103 2 0 求这组数中的最大值和最小值2 变量x 12 54 y 10 63456 请用MySQL函数完成以下计算 1 求x和y的最大整数值和最小整数值 2 求x和y四舍五入的整数值 3 求y分别保留小数位数为2位和保留小数位数4位的值 4 求x的平方根和y的绝对值3 求字符 R 的ASCII值4 求ASCII码值为91 92 93组成的一个字符串5 设有字符串s1 ABCDEFG s2 yxz 请用MySQL函数完成以下运算 1 返回s1最左边的3个字符和最右边的3个字符 2 分别删除字符串s2首部空格 尾部空格 所有空格 3 返回字符串s1第3个字符开始的4个字符串 4 比较s1和s2两个字符串6 用MySQL函数显示当前日期 当前时间 当前年 当前月的英文 当期星期的英文名 当前日期减10天的日期7 加密显示当前数据库名 当前登录用户名和MySQL服务器的版本 12 系统内置函数 1 有一组数 12 34 5 5 103 2 0 求这组数中的最大值和最小值selectgreatest 12 34 5 5 103 2 0 least 12 34 5 5 103 2 0 2 变量x 12 54 y 10 63456 请用MySQL函数完成以下计算 1 求x和y的最大整数值和最小整数值Set x 12 54 Set y 10 63456 Selectfloor x floor y ceiling x ceiling y 2 求x和y四舍五入的整数值SELECTROUND x ROUND y 3 求y分别保留小数位数为2位和保留小数位数4位的值SELECTTRUNCATE y 2 TRUNCATE y 4 4 求x的平方根和y的绝对值SELECTSQRT x ABS y 3 求字符 R 的ASCII值SELECTASCII R 4 求ASCII码值为91 92 93组成的一个字符串SELECTCHAR 91 92 93 5 设有字符串s1 ABCDEFG s2 yxz 请用MySQL函数完成以下运算 1 返回s1最左边的3个字符和最右边的3个字符 SET s1 ABCDEFG SET s2 XYZ SELECTLEFT s1 3 right s1 3 2 分别删除字符串s2首部空格 尾部空格 所有空格 SELECTLTRIM s2 RTRIM s2 TRIM s2 3 返回字符串s1第3个字符开始的4个字符串selectsubstring s1 3 4 4 比较s1和s2两个字符串SELECTSTRCMP s1 s2 6 用MySQL函数显示当前日期 当前时间 当前年 当前月的英文 当期星期的英文名 当前日期减10天的日期selectnow CURTIME CURDATE YEAR now MONTHNAME now DAYNAME now DATE ADD now INTERVAL 10DAY 7 加密显示当前数据库名 当前登录用户名和MySQL服务器的版本SELECTDATABASE USER VERSION 13 任务二存储过程 使用存储过程的优点有 1 存储过程在服务器端运行 执行速度快 2 存储过程执行一次后 其执行规划就驻留在高速缓冲存储器 在以后的操作中 只需从高速缓冲存储器中调用已编译好的二进制代码执行 提高了系统性能 3 确保数据库的安全 使用存储过程可以完成所有数据库操作 并可通过编程方式控制上述操作对数据库信息访问的权限 14 创建存储过程 创建存储过程可以使用CREATEPROCEDURE语句语法格式 CREATEPROCEDURE存储过程名 参数 存储过程体参数 存储过程的参数 格式如下 IN OUT INOUT 参数名类型当有多个参数的时候中间用逗号隔开 存储过程可以有0个 1个或多个参数 MySQL存储过程支持三种类型的参数 输入参数 输出参数和输入 输出参数 关键字分别是IN OUT和INOUT 输入参数使数据可以传递给一个存储过程 当需要返回一个答案或结果的时候 存储过程使用输出参数 输入 输出参数既可以充当输入参数也可以充当输出参数 存储过程也可以不加参数 但是名称后面的括号是不可省略的 存储过程体 这是存储过程的主体部分 也叫做存储过程体 里面包含了在过程调用的时候必须执行的语句 这个部分总是以BEGIN开始 以END结束 但是 当存储过程体中只有一个SQL语句时可以省略BEGIN END标志 15 DELIMITER命令 在MySQL中 服务器处理语句的时候是以分号为结束标志的 但是在创建存储过程的时候 存储过程体中可能包含多个SQL语句 每个SQL语句都是以分号为结尾的 这时服务器处理程序的时候遇到第一个分号就会认为程序结束 这肯定是不行的 所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号 DELIMITER语法格式为 DELIMITER 说明 是用户定义的结束符 通常这个符号可以是一些特殊的符号 如两个 两个 等 当使用DELIMITER命令时 应该避免使用反斜杠 字符 因为那是MySQL的转义字符 例8 29 将MySQL结束符修改为两个斜杠 符号 DELIMITER 说明 执行完这条命令后 程序结束的标志就换为双斜杠符号 了 要想恢复使用分号 作为结束符 运行下面命令即可 DELIMITER 16 创建存储过程举例 例8 17 编写一个存储过程 实现的功能是删除一个特定会员的信息 DELIMITER CREATEPROCEDUREdel member INsfzCHAR 6 BEGINDELETEFROMMembersWHERE身份证号 sfz END DELIMITER 在关键字BEGIN和END之间指定了存储过程体 因为在程序开始用DELIMITER语句转换了语句结束标志为 所以BEGIN和END被看成是一个整体 在END后用 结束 当然 BEGIN END复合语句还可以嵌套使用 要想查看数据库中有哪些存储过程 可以使用SHOWPROCEDURESTATUS命令 SHOWPROCEDURESTATUS要查看某个存储过程的具体信息 可使用SHOWCREATEPROCEDUREsp name命令 其中sp name是存储过程的名称 SHOWCREATEPROCEDUREdel member 17 DECLARE语句 1 局部变量在存储过程中可以声明局部变量 它们可以用来存储临时结果 要声明局部变量必须使用DECLARE语句 在声明局部变量的同时也可以对其赋一个初始值 语法格式 DECLARE变量 类型 DEFAULT值 例8 18 声明一个整型变量和两个字符变量 DECLAREnumINT 4 DECLAREstr1 str2VARCHAR 6 说明 局部变量只能在BEGIN END语句块中声明 局部变量必须在存储过程的开头就声明 声明完后 可以在声明它的BEGIN END语句块中使用该变量 其他语句块中不可以使用它 18 SET语句 2 使用SET语句赋值要给局部变量赋值可以使用SET语句 语法格式为 SET变量名1 表达式1 变量名2 表达式2 例8 19 在存储过程中给局部变量num赋值为1 str1赋值为 hello SETnum 1 str1 hello 19 SELECT INTO语句 3 SELECT INTO语句使用这个SELECT INTO语法可以把选定的列值直接存储到变量中 因此 返回的结果只能有一行 语法格式为 SELECT列名 INTO变量名 数据来源表达式 例8 20 在存储过程体中将Book表中的书名为 计算机基础 的作者姓名和出版社的值分别赋给变量name和publish SELECT作者 出版社INTOname publishFROMBookWHERE书名 计算机基础 20 调用存储过程 存储过程创建完后 可以在程序 触发器或者存储过程中被调用 调用时都必须使用到CALL语句 其语法格式 CALL存储过程名 参数 例8 21 创建存储过程实现查询Members表中会员人数的功能 并执行它 首先创建查询Members表中会员人数的存储过程 CREATEPROCEDUREquery members SELECTCOUNT FROMMembers 这是一个不带参数的非常简单的存储过程 通常SELECT语句不会被直接用在存储过程中 调用该存储过程 CALLquery members 21 课堂练习 1 创建存储过程 实现给出员工编号 在employees表和salary表中删除该员工记录 并调用该存储过程 删除员工编号为020018的记录 2 创建存储过程 实现查询salary表中收入总额 并调用该的存储过程 22 删除存储过程 存储过程创建后需要删除时使用DROPPROCEDURE语句 在此之前 必须确认该存储过程没有任何依赖关系 否则会导致其他与之关联的存储过程无法运行 语法格式为 DROPPROCEDURE IFEXISTS 存储过程名 例8 22 删除存储过程query members DROPPROCEDUREIFEXISTSquery members 23 流程控制语句 在MySQL中 常见的过程式SQL语句可以用在一个存储过程体中 例如 IF语句 CASE语句 LOOP语句 WHILE语句 ITERATE语句和LEAVE语句 1 IF语句语法格式为 IF条件1THEN语句序列1 ELSEIF条件2THEN语句序列2 ELSE语句序列e ENDIF 24 IF语句举例 DELIMITER CREATEPROCEDURECOMPAR INK1INTEGER INK2INTEGER OUTK3CHAR 6 BEGINIFK1 K2THENSETK3 大于 ELSEIFK1 K2THENSETK3 等于 ELSESETK3 小于 ENDIF END DELIMITER 说明 存储过程中K1和K2是输入参数 K3是输出参数 例8 23 创建存储过程 判断两个输入的参数哪一个更大 25 存储过程创建完后 可以在程序 触发器或者存储过程中被调用 但是都必须使用到CALL语句 语法格式 CALLsp name parameter parameter为调用该存储过程使用的参数 这条语句中的参数个数必须总是等于存储过程的参数个数 如果是输出变量 前面加 调用该存储过程 CALLCOMPAR 3 6 K SELECT K 说明 3和6相当于输入参数K1和K2 用户变量K相当于输出参数K3 可以看到 由于3 6 输出参数K的值就为 小于 调用存储过程举例 26 例8 24 创建一个Bookstore数据库的存储过程 根据客户姓名和书名查询订单 如果订购册数小于5本不打折 订购册数在5 10本之间 订购单价打九折 订购册数大于10本 订购单价打八折 DELIMITER CREATEPROCEDUREdj update INc nameCHAR 8 INb nameCHAR 20 BEGINDECLAREbhCHAR 20 DECLAREsfzCHAR 18 DECLAREslTINYINT SELECT身份证号INTOsfzFROMMembersWHERE会员姓名 c name SELECT图书编号INTObhFROMBookWHERE书名 b name SELECT订购册数INTOslFROMSellWHERE身份证号 sfzAND图书编号 bh IFsl 5ANDsl10THENUPDATESellSET订购单价 订购单价 0 8WHERE身份证号 sfzAND图书编号 bh ENDIF ENDIF END DELIMITER 调用存储过程dj update CALLdj update 张三 网络数据库 调用存储过程举例 27 CASE语句 2 CASE语句语法格式为 CASE表达式WHEN值1THEN语句序列1 WHEN值2THEN语句序列2 ELSE语句序列e ENDCASE或者 CASEWHEN条件1THEN语句序列1 WHEN条件2THEN语句序列2 ELSE语句序列e ENDCASE第一种格式中表达式是要被判断的值或表达式 接下来是一系列的WHEN THEN块 每一块的值参数都要与表达式比较的值 如果为真 就执行语句序列中的SQL语句 如果前面的每一个块都不匹配就会执行ELSE块指定的语句 CASE语句最后以ENDCASE结束 第二种格式中CASE关键字后面没有参数 在WHEN THEN块中 条件指定了一个比较表达式 表达式为真时执行THEN后面的语句 第二种格式与第一种格式相比 更能够实现更为复杂的条件判断 使用起来更方便 28 例8 25 创建一个存储过程 当给定参数为 时返回 上升 给定参数为 时返回 下降 给定其他参数时返回 不变 DELIMITER CREATEPROCEDUREvar cp INstrVARCHAR 1 OUTdirectVARCHAR 4 BEGINCASEstrWHEN U THENSETdirect 上升 WHEN D THENSETdirect 下降 ELSESETdirect 不变 ENDCASE END DELIMITER 以上的CASE语句用第二种格式来写如下 CASEWHENstr U THENSETdirect 上升 WHENstr D THENSETdirect 下降 ELSESETdirect 不变 ENDCASE CASE语句举例 29 课堂练习 1 创建存储过程 输入员工编号bh 输出该员工的性别 2 创建存储过程 输入员工编号bh和级别jb两个参数 如果jb为A 该员工收入增加500元 jb为B 该员工收入增加300元 jb为C 该员工收入增加150元 jb为A B C以外的其他值 该员工收入增加50元 3 创建存储过程 比较两个员工的实际收入 如前者比后者高就输出0 否则输出1 并调用该存储过程比较 000001 和 108991 两员工的收入 30 课堂练习参考答案 1 创建存储过程createprocedurecp inid1char 6 inid2char 6 outbjint begindeclaresr1 sr2float 8 selectincome outcomeintosr1fromsalarywhereemployeeid id1 selectincome outcomeintosr2fromsalarywhereemployeeid id2 ifid1 id2thensetbj 0 elsesetbj 1 endif end2 调用存储过程 callcp 000001 108991 bj 3 输出结果select bj 31 循环语句 3 循环语句MySQL支持3条用来创建循环的语句 WHILE REPEAT和LOOP语句 WHILE语句语法格式为 开始标号 WHILE条件DO程序段ENDWHILE 结束标号 语句首先判断条件是否为真 为真则执行程序段中的语句 然后再次进行判断 为真则继续循环 不为真则结束循环 开始标号和结束标号是WHILE语句的标注 除非开始标号存在 否则结束标号 并且如果两者都出现 它们的名字必须是相同的 32 WHILE语句举例 例8 26 创建一个带WHILE执行5次循环的存储过程 DELIMITER CREATEPROCEDUREdowhile BEGINDECLAREaINTDEFAULT5 WHILEa 0DOSETa a 1 ENDWHILE END DELIMITER 33 REPEAT语句 REPEAT语句格式如下 开始标号 REPEAT程序段UNTIL条件ENDREPEAT 结束标号 用REPEAT语句替换例8 26的WHILE循环过程如下 REPEATa a 1 UNTILa 1 ENDREPEAT 说明 REPEAT语句和WHILE语句的区别在于 REPEAT语句先执行语句 后进行判断 而WHILE语句是先判断 条件为真时才执行语句 34 LOOP语句 LOOP语句语法格式如下 开始标号 LOOP程序段ENDLOOP 结束标号 说明 LOOP允许某特定语句或语句群的重复执行 实现一个简单的循环构造 程序段是需要重复执行的语句 在循环内的语句一直重复至循环被退出 退出时通常伴随着一个LEAVE语句 结构如下 LEAVElabel 例8 27 使用LOOP语句重写8 26的存储过程 DELIMITER CREATEPROCEDUREdoloop BEGINSET a 5 Label LOOPSET a a 1 IF a 1THENLEAVELabel ENDIF ENDLOOPLabel END DELIMITER 35 存储过程的嵌套 例8 28 创建一个存储过程sell insert 作用是向Sell表中插入一行数据 创建另外一个存储过程sell update 在其中调用第一个存储过程 如果给定参数为0 则修改由第一个存储过程插入记录的是否发货字段为 已发货 如果给定参数为1则删除第一个存储过程插入的记录 并将操作结果输出 第一个存储过程 向Sell表中插入一行数据CREATEPROCEDUREsell insert INSERTINTOSellVALUES 17ISBN7 301 06342 3 4 30 2013 03 05 NULL NULL NULL 第二个存储过程 调用第一个存储过 并输出结果CREATEPROCEDUREsell update INXINT 1 OUTSTRCHAR 8 BEGINCALLsell insert CASEWHENx 0THENUPDATESellSET是否发货 已发货 WHERE订单号 17 SETSTR 修改成功 WHENX 1THENDELETEFROMSellWHERE订单号 17 SETSTR 删除成功 ENDCASE END 36 存储过程的嵌套 接下来调用存储过程sell update来查看结果 CALLsell update 1 str SELECT str 结果为 删除成功CALLsell update 0 str SELECT str 结果为 修改成功 37 课堂练习 1 创建存储过程 输入一个数x 输出x 2 创建存储过程DO hz1 作用是向salary表中插入汇总行 合计 收入总和 支出总和 创建另外一个存储过程DO show2 在其中调用第一个存储过程 并输出汇总结果 38 任务三存储函数 存储函数也是过程式对象之一 与存储过程很相似 它们都是由SQL和过程式语句组成的代码片断 并且可以从应用程序和SQL中调用 然而 它们也有一些区别 1 存储函数不能拥有输出参数 因为存储函数本身就是输出参数 2 不能用CALL语句来调用存储函数 3 存储函数必须包含一条RETURN语句 而这条特殊的SQL语句不允许包含于存储过程中 创建存储函数使用CREATEFUNCTION语句 语法格式 CREATEFUNCTION存储函数名 参数 RETURNS类型函数体 39 创建存储函数 例8 29 创建一个存储函数 它返回Book表中图书数目作为结果 DELIMITER CREATEFUNCTIONnum book RETURNSINTEGERBEGINRETURN SELECTCOUNT FROMBook END DELIMITER RETURN子句中包含SELECT语句时 SELECT语句的返回结果只能是一行且只能有一列值 虽然此存储函数没有参数 使用时也要用 如num book 要查看数据库中有哪些存储函数 可以使用SHOWFUNCTIONSTATUS命令 SHOWFUNCTIONSTATUS调用存储函数存储函数创建完后 就如同系统提供的内置函数 如VERSION 所以调用存储函数的方法也差不多 都是使用SELECT关键字 语法格式为 SELECTsp name func parameter 调用 例8 29 存储函数 SelectNUM book 40 存储函数举例 例8 30 创建一个存储函数 返回Book表中某本书的作者姓名 DELIMITER CREATEFUNCTIONauthor book b nameCHAR 20 RETURNSCHAR 8 BEGINRETURN SELECT作者FROMBookWHERE书名 b name END DELIMITER 此存储函数给定书名 返回该书的作者 如要查询 计算机应用基础 的作者 用author book 计算机应用基础 41 例8 31 创建一个存储函数来删除Sell表中有但Book表中不存在的记录 CREATEFUNCTIONdel Sell1 b bhCHAR 20 RETURNSBOOLEANBEGINDECLAREbhCHAR 20 SELECT图书编号INTObhFROMBookWHERE图书编号 b bh IFbhISNULLTHENDELETEFROMSellWHERE图书编号 b bh RETURNTRUE ELSERETURNFALSE ENDIF END 存储函数举例 42 课堂练习 1 创建一个存储函数 返回员工的总人数EM NUM 2 创建一个存储函数 判断员工是否在研发部工作 若是则返回其学历 若不是则返回 NO 43 课堂练习参考答案 1 创建一个存储函数 返回员工的总人数EM NUM CREATEFUNCTIONEM NUM RETURNSIntegerRETURN SELECTcount FROMEmployees 2 创建一个存储函数 判断员工是否在研发部工作 若是则返回其学历 若不是则返回 NO CREATEFUNCTIONXL XHCHAR 6 RETURNSCHAR 10 BEGINDECLAREBM EDCHAR 6 SELECTdepartmentID educationINTOBM EDFROMemployeesWHEREemployeeID XH IFbm 4 THENRETURNED ELSERETURN NO ENDIF END 44 任务四触发器 创建触发器语法格式 CREATETRIGGER触发器名触发时间触发事件ON表名FOREACHROW触发器动作触发时间 触发器触发的时刻 有两个选项 AFTER和BEFORE 以表示触发器是在激活它的语句之前或之后触发 如果想要在激活触发器的语句执行之后执行几个或更多的改变 通常使用AFTER选项 如果想要验证新数据是否满足使用的限制 则使用BEFORE选项 触发事件 指明了激活触发程序的语句的类型 触发事件可以是下述值之一 INSERT 将新行插入表时激活触发器 例如 通过INSERT LOADDATA和REPLACE语句 UPDATE 更改某一行时激活触发器 例如 通过UPDATE语句 DELETE 从表中删除某一行时激活触发器 例如 通过DELETE和REPLACE语句 触发器动作 包含触发器激活时将要执行的语句 如果要执行多个语句 可使用BEGIN END复合语句结构 这样 就能使用存储过程中允许的相同语句 45 创建触发器举例 例8 34 创建一个表table1 其中只有一列a 在表上创建一个触发器 每次插入操作时 将用户变量str的值设为 TRIGGERISWORKING CREATETABLEtable1 aINTEGER CREATETRIGGERtable1 insertAFTERINSERTONtable1FOREACHROWSET str TRIGGERISWORKING 向table1中插入一行数据 INSERTINTOtable1VALUES 10 查看str的值 SELECT str 在MySQL触发器中的SQL语句可以关联表中的任意列 但不能直接使用列的名称去标志 那会使系统混淆 因为激活触发器的语句可能已经修改 删除或添加了新的列名 而列的旧名同时存在 因此必须用这样的语法来标志 NEW column name 或者 OLD column name NEW column name用来引用新行的一列 OLD column name用来引用更新或删除它之前的已有行的一列 对于INSERT语句 只有NEW是合法的 对于DELETE语句 只有OLD才合法 而UPDATE语句可以与NEW或OLD同时使用 46 创建触发器举例 例8 35 创建一个触发器 当删除表Book表中某图书的信息时 同时将Sell表中与该图书有关的数据全部删除 DELIMITER CREATETRIGGERbook delAFTERDELETEONBookFOREACHROWBEGINDELETEFROMSellWHERE图书编号 OLD 图书编号 END DELIMITER 现在验证一下触发器的功能 DELETEFROMBookWHERE图书编号 ISBN7 5006 6625 使用SELECT语句查看Sell表中的情况 SELECT FROMSellWHERE图书编号 ISBN7 5006 6625 这时可以发现 图书编号为 ISBN7 5006 6625 在Sell表中的所有信息已经被删除了 47 课堂练习 创建触发器 在Employees表中当删除员工信息的同时将Salary表中与该员工有关的数据全部删除 48 DELIMITER CREATETRIGGEREM DELETEAFTERDELETEONEmployeesFOREACHROWBEGINDELETEFROMsalaryWHEREemployeeID OLD employeeID END DELIMITER 课堂练习参考答案 49 创建触发器举例 例8 36 创建一个触发器 当修改Sell表中订购册数时 如果修改后的订购册数小于5本 则触发器将该对应的折扣