数据库编程ppt课件

数据库编程,1,项目知识要点与目标,2,任务一 MySQL语言结构,1字符串常量 字符串是指用单引号或双引号括起来的字符序列，分为ASCII字符串常量和Unicode 字符串常量。 ASCII字符串常量是用单引号括起来的，由ASCII字符构成的符号串。举例：hello，How are you! Unicode 字符串常量与ASCII字符串常量相似，但它前面有一个N标志符（N代表 SQL-92标准中的国际语言（National Language）。N前缀必须为大写。只能用单引号括起字符串。 举例：Nhello，NHow are you! Unicode 数据中的每个字符用两个字节存储，而每个ASCII字符用一个字节存储。 在字符串中不仅可以使用普通的字符，也可使用几个转义序列，它们用来表示特殊的字符。 【例8.1】 执行如下语句： SELECT ThisnIsnFournLines; 其中，“n”表示回车。,3,常量,2数值常量 数值常量可以分为整数常量和浮点数常量。 3十六进制常量 一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母“X”或小写字“x”。 4日期时间常量 日期时间常量：用单引号将表示日期时间的字符串括起来构成。日期型常量包括年、月、日，数据类型为DATE，表示为“1999-06-17”这样的值。 时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为TIME，如“12:30:43.00013”。 日期/时间的组合，数据类型为DATETIME或TIMESTAMP，如“1999-06-17 12:30:43”。,4,常量,5位字段值 可以使用bvalue符号写位字段值。value是一个用0和1写成的二进制值。直接显示bvalue的值可能是一系列特殊的符号。例如，b0显示为空白，b1显示为一个笑脸图标。 使用BIN函数可以将位字段常量显示为二进制格式。 6布尔值 布尔值只包含两个可能的值：TRUE和FALSE。 FALSE的数字值为“0”，TRUE的数字值为“1”。 7NULL值 NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义，并且不同于数字类型的“0”或字符串类型的空字符串。,5,用户变量,变量用于临时存放数据，变量有名字及其数据类型两个属性，变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。MySQL中根据变量的定义方式，变量可分为用户变量和系统变量。 1用户变量 用户可以在表达式中使用自己定义的变量，这样的变量叫做用户变量。在使用用户变量前必须定义和初始化。如果使用没有初始化的变量，它的值为NULL。 定义和初始化一个变量可以使用SET语句 语法格式为： SET user_variable1expression1 ,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 姓名 FROM XS WHERE 学号=081101); 【例8.7】 查询表XS中名字等于例6.11中student值的学生信息。 SELECT 学号, 姓名, 专业名, 出生日期 FROM XS WHERE 姓名=student; 说明：在SELECT语句中，表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或ORDER BY子句中，不能使用包含SELECT列表中所设的变量的表达式。 对于SET语句，可以使用“=”或“:=”作为分配符。分配给每个变量的值可以为整数、实数、字符串或NULL值。 也可以用其他SQL语句代替SET语句来为用户变量分配一个值。在这种情况下，分配符必须为“:=”，而不能用“=”，因为在非SET语句中“=”被视为比较操作符。 【例8.8】 执行如下语句： SELECT t2:=(t2:=2)+5 AS t2; 结果t2的值为7。,8,系统变量,MySQL有一些特定的设置，当MySQL数据库服务器启动的时候，这些设置被读取来决定下一步骤。例如，有些设置定义了数据如何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。和用户变量一样，系统变量也是一个值和一个数据类型，但不同的是，系统变量在MySQL服务器启动时就被引入并初始化为默认值。 【例8.9】 获得现在使用的MySQL版本。 SELECT VERSION ; 说明：在MySQL中，系统变量VERSION的值设置为版本号。在变量名前必须加两个符号才能正确返回该变量的值。 大多数的系统变量应用于其他SQL语句中时，必须在名称前加两个符号，而为了与其他SQL产品保持一致，某些特定的系统变量是要省略这两个符号的。如CURRENT_DATE（系统日期） 【例8.10】 获得系统当前时间。 SELECT CURRENT_TIME;,9,运算符,1算术运算符 算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型。算术运算符有：+（加）、（减）、*（乘）、/（除）和%（求模）5种运算。 2比较运算符 比较运算符（又称关系运算符），用于比较两个表达式的值，其运算结果为逻辑值，可以为三种之一：1（真）、0（假）及 NULL（不能确定）。 3、逻辑运算符 逻辑运算符用于对某个条件进行测试，运算结果为TRUE（1）或FALSE（0）。 4、运算符优先级 当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的顺序会影响所得到的运算结果。运算符优先级如下表所示。,10,表达式,表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表达式通常可以得到一个值。与常量和变量一样，表达式的值也具有某种数据类型，可能的数据类型有字符类型、数值类型、日期时间类型。这样，根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期表达式。 表达式按照形式还可分为单一表达式和复合表达式。 单一表达式就是一个单一的值，如一个常量或列名。 复合表达式是由运算符将多个单一表达式连接而成的表达式 例如：1+2+3，a=b+3，2008-01-20+ INTERVAL 2 MONTH。 表达式一般用在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),求这组数中的最大值和最小值 select greatest(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; Select floor(x),floor(y),ceiling(x),ceiling(y); （2）求x和y四舍五入的整数值 SELECT ROUND(x),ROUND(y); （3）求y分别保留小数位数为2位和保留小数位数4位的值 SELECT TRUNCATE(y, 2),TRUNCATE(y, 4); （4）求x的平方根和y的绝对值 SELECT SQRT(x),ABS(y); 3、求字符R的ASCII值 SELECT ASCII(R); 4、求ASCII码值为91、92、93组成的一个字符串 SELECT CHAR(91,92,93); 5、设有字符串s1=ABCDEFG,s2= yxz ,请用MySQL函数完成以下运算： （1） 返回s1最左边的3个字符和最右边的3个字符。 SET s1=ABCDEFG;SETs2= XYZ ; SELECT LEFT(s1, 3) ,right(s1,3); （2）分别删除字符串s2首部空格、尾部空格、所有空格。 SELECT LTRIM(s2),RTRIM(s2),TRIM(s2); （3）返回字符串s1第3个字符开始的4个字符串 select substring(s1,3,4); （4）比较s1和s2两个字符串 SELECT STRCMP(s1, s2); 6、用MySQL函数显示当前日期、当前时间、当前年、当前月的英文、当期星期的英文名，当前日期减10天的日期 select now(),CURTIME(),CURDATE(),YEAR(now(),MONTHNAME(now(), DAYNAME(now(),DATE_ADD(now(), INTERVAL -10 DAY); 7、加密显示当前数据库名，当前登录用户名 和MySQL服务器的版本 SELECT DATABASE(),USER(),VERSION();,13,任务二 存储过程,使用存储过程的优点有： （1）存储过程在服务器端运行，执行速度快。 （2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 （3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。,14,创建存储过程,创建存储过程可以使用CREATE PROCEDURE语句 语法格式： CREATE PROCEDURE 存储过程名 (参数,.)存储过程体 参数：存储过程的参数，格式如下： 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 $ CREATE PROCEDURE del_member(IN sfz CHAR(6) BEGIN DELETE FROM Members WHERE 身份证号=sfz; END $ DELIMITER ; 在关键字BEGIN和END之间指定了存储过程体，因为在程序开始用DELIMITER语句转换了语句结束标志为“$”，所以BEGIN和END被看成是一个整体，在END 后用“$”结束。当然，BEGIN-END复合语句还可以嵌套使用。 要想查看数据库中有哪些存储过程，可以使用SHOW PROCEDURE STATUS命令。 SHOW PROCEDURE STATUS 要查看某个存储过程的具体信息，可使用SHOW CREATE PROCEDURE sp_name命令，其中sp_name是存储过程的名称。 SHOW CREATE PROCEDURE del_member,17,DECLARE语句,1. 局部变量 在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。 语法格式： DECLARE 变量,. 类型 DEFAULT 值 【例8.18】 声明一个整型变量和两个字符变量。 DECLARE num INT(4); DECLARE str1, str2 VARCHAR(6); 说明：局部变量只能在BEGINEND语句块中声明。 局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGINEND语句块中使用该变量，其他语句块中不可以使用它。,18,SET语句,2. 使用SET语句赋值 要给局部变量赋值可以使用SET语句。 语法格式为： SET 变量名1 = 表达式1 ,变量名2 = 表达式2 . 【例8.19】 在存储过程中给局部变量num赋值为1，str1赋值为hello。 SET num=1, str1= hello;,19,SELECT.INTO语句,3. SELECT.INTO语句 使用这个SELECTINTO语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。 语法格式为： SELECT 列名 ,. INTO 变量名 ,. 数据来源表达式 【例8.20】 在存储过程体中将Book表中的书名为“计算机基础”的作者姓名和出版社的值分别赋给变量name和publish。 SELECT作者,出版社 INTO name, publish FROM Book WHERE 书名= 计算机基础;,20,调用存储过程,存储过程创建完后，可以在程序、触发器或者存储过程中被调用，调用时都必须使用到CALL语句， 其语法格式： CALL 存储过程名( 参数 ,.) 【例8.21】 创建存储过程实现查询Members表中会员人数的功能,并执行它。 首先创建查询Members表中会员人数的存储过程： CREATE PROCEDURE query_members() SELECT COUNT(*) FROM Members; 这是一个不带参数的非常简单的存储过程，通常SELECT语句不会被直接用在存储过程中。 调用该存储过程： CALL query_members();,21,课堂练习,1、创建存储过程，实现给出员工编号，在employees表和salary表中删除该员工记录。并调用该存储过程，删除员工编号为020018的记录。 2、创建存储过程，实现查询salary表中收入总额，并调用该的存储过程。,22,删除存储过程,存储过程创建后需要删除时使用DROP PROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。 语法格式为： DROP PROCEDURE IF EXISTS 存储过程名 【例8.22】 删除存储过程query_members()。 DROP PROCEDURE IF EXISTS query_members();,23,流程控制语句,在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、LOOP语句、WHILE语句、ITERATE语句和LEAVE语句。 （1）IF语句 语法格式为： IF 条件1 THEN 语句序列1 ELSEIF条件2 THEN语句序列2 . ELSE 语句序列e END IF,24,IF语句举例,DELIMITER $ CREATE PROCEDURE COMPAR (IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) ) BEGIN IF K1K2 THEN SET K3= 大于; ELSEIF K1=K2 THEN SET K3= 等于; ELSE SET K3= 小于; END IF; END$ DELIMITER ; 说明：存储过程中K1和K2是输入参数，K3是输出参数。,【例8.23】 创建存储过程，判断两个输入的参数哪一个更大。,25,存储过程创建完后，可以在程序、触发器或者存储过程中被调用，但是都必须使用到CALL语句。 语法格式： CALL sp_name(parameter,.) parameter为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。 如果是输出变量，前面加 调用该存储过程： CALL COMPAR(3, 6, K); SELECT K; 说明：3和6相当于输入参数K1和K2，用户变量K相当于输出参数K3。可以看到，由于36，输出参数K的值就为“小于”。,调用存储过程举例,26,【例8.24】 创建一个Bookstore数据库的存储过程，根据客户姓名和书名查询订单，如果订购册数小于5本不打折，订购册数在5-10本之间，订购单价打九折，订购册数大于10本，订购单价打八折。 DELIMITER $ CREATE PROCEDURE dj_update(IN c_name CHAR(8), IN b_name CHAR(20) BEGIN DECLARE bh CHAR(20); DECLARE sfz CHAR(18); DECLARE sl TINYINT; SELECT 身份证号 INTO sfz FROM Members WHERE 会员姓名=c_name; SELECT 图书编号 INTO bh FROM Book WHERE 书名=b_name; SELECT 订购册数 INTO sl FROM Sell WHERE 身份证号=sfz AND 图书编号=bh; IF sl=5 AND sl10 THEN UPDATE Sell SET 订购单价=订购单价*0.8 WHERE 身份证号=sfz AND 图书编号=bh; END IF; END IF; END$ DELIMITER ; 调用存储过程dj_update： CALL dj_update (张三, 网络数据库);,调用存储过程举例,27,CASE语句,（2）CASE语句 语法格式为： CASE 表达式 WHEN 值1 THEN 语句序列1 WHEN值2 THEN语句序列2 . ELSE语句序列e END CASE 或者： CASE WHEN 条件1 THEN 语句序列1 WHEN条件2 THEN语句序列2 . ELSE语句序列e END CASE 第一种格式中表达式是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的值参数都要与表达式比较的值，如果为真，就执行语句序列中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。 第二种格式中CASE关键字后面没有参数，在WHEN-THEN块中，条件指定了一个比较表达式，表达式为真时执行THEN后面的语句。 第二种格式与第一种格式相比，更能够实现更为复杂的条件判断，使用起来更方便。,28,【例8.25】 创建一个存储过程，当给定参数为时返回“上升”，给定参数为时返回“下降”，给定其他参数时返回“不变”。 DELIMITER $ CREATE PROCEDURE var_cp (IN str VARCHAR(1), OUT direct VARCHAR(4) ) BEGIN CASE str WHEN U THEN SET direct =上升; WHEN D THEN SET direct =下降; ELSE SET direct =不变; END CASE; END$ DELIMITER ; 以上的CASE语句用第二种格式来写如下： CASE WHEN str= U THEN SET direct =上升; WHEN str= D THEN SET direct =下降; ELSE SET direct =不变; END CASE;,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、创建存储过程 create procedure cp(in id1 char(6),in id2 char(6),out bj int) begin declare sr1,sr2 float(8); select income-outcome into sr1 from salary where employeeid=id1; select income-outcome into sr2 from salary where employeeid=id2; if id1id2 then set bj=0; else set bj=1; end if; end 2、 调用存储过程： call cp(000001,108991,bj) 3、输出结果 select bj;,31,循环语句,（3）循环语句 MySQL支持3条用来创建循环的语句：WHILE、REPEAT和LOOP语句。 WHILE语句语法格式为： 开始标号: WHILE条件 DO 程序段 END WHILE 结束标号 语句首先判断条件是否为真，为真则执行程序段中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。 开始标号和结束标号是WHILE语句的标注。除非开始标号存在，否则结束标号，并且如果两者都出现，它们的名字必须是相同的。,32,WHILE语句举例,【例8.26】 创建一个带WHILE执行5次循环的存储过程。 DELIMITER $ CREATE PROCEDURE dowhile() BEGIN DECLARE a INT DEFAULT 5; WHILE a 0 DO SET a = a1; END WHILE; END$ DELIMITER ;,33,REPEAT语句, REPEAT语句格式如下： 开始标号: REPEAT 程序段 UNTIL 条件 END REPEAT 结束标号 用REPEAT语句替换例8.26的WHILE循环过程如下： REPEAT a=a1; UNTIL a1; END REPEAT; 说明：REPEAT语句和WHILE语句的区别在于：REPEAT语句先执行语句，后进行判断；而WHILE语句是先判断，条件为真时才执行语句。,34,LOOP语句, LOOP语句语法格式如下： 开始标号: LOOP 程序段 END LOOP 结束标号 说明：LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造，程序段是需要重复执行的语句。在循环内的语句一直重复至循环被退出，退出时通常伴随着一个LEAVE 语句。结构如下：LEAVE label 【例8.27】 使用LOOP语句重写8.26的存储过程。 DELIMITER $ CREATE PROCEDURE doloop() BEGIN SET a=5; Label: LOOP SET a=a1; IF a1 THEN LEAVE Label; END IF; END LOOP Label; END$ DELIMITER ;,35,存储过程的嵌套,【例8.28】 创建一个存储过程sell_insert()，作用是向Sell表中插入一行数据。创建另外一个存储过程sell_update，在其中调用第一个存储过程，如果给定参数为0，则修改由第一个存储过程插入记录的是否发货字段为已发货，如果给定参数为1则删除第一个存储过程插入的记录，并将操作结果输出。 第一个存储过程：向Sell表中插入一行数据 CREATE PROCEDURE sell_insert() INSERT INTO Sell VALUES(17,430103198608201963, ISBN7-301-06342-3,4, 30, 2013-03-05, NULL, NULL, NULL); 第二个存储过程：调用第一个存储过，并输出结果 CREATE PROCEDURE sell_update(IN X INT(1), OUT STR CHAR(8) BEGIN CALL sell_insert(); CASE WHEN x=0 THEN UPDATE Sell SET 是否发货=已发货 WHERE 订单号=17; SET STR=修改成功; WHEN X=1 THEN DELETE FROM Sell WHERE 订单号=17; SET STR=删除成功; END CASE; END,36,存储过程的嵌套,接下来调用存储过程sell_update来查看结果： CALL sell_update (1, str); SELECT str; 结果为：删除成功 CALL sell_update (0, str); SELECT str; 结果为：修改成功,37,课堂练习,1、创建存储过程，输入一个数x ，输出x!。 2、创建存储过程DO_hz1，作用是向salary表中插入汇总行（合计，收入总和，支出总和）。创建另外一个存储过程DO_show2，在其中调用第一个存储过程，并输出汇总结果。,38,任务三 存储函数,存储函数也是过程式对象之一，与存储过程很相似。它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。然而，它们也有一些区别： （1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数； （2）不能用CALL语句来调用存储函数； （3）存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。 创建存储函数使用CREATE FUNCTION语句。 语法格式： CREATE FUNCTION 存储函数名 (参数,.) RETURNS 类型 函数体,39,创建存储函数,【例8.29】 创建一个存储函数，它返回Book表中图书数目作为结果。 DELIMITER $ CREATE FUNCTION num_book() RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM Book); END$ DELIMITER ; RETURN子句中包含SELECT语句时，SELECT语句的返回结果只能是一行且只能有一列值。 虽然此存储函数没有参数，使用时也要用（），如num_book()。 要查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUS命令。 SHOW FUNCTION STATUS 调用存储函数 存储函数创建完后，就如同系统提供的内置函数（如VERSION()），所以调用存储函数的方法也差不多，都是使用SELECT关键字。 语法格式为： SELECT sp_name (func_parameter,.) 调用【例8.29】存储函数: Select NUM_book(),40,存储函数举例,【例8.30】 创建一个存储函数，返回Book表中某本书的作者姓名。 DELIMITER $ CREATE FUNCTION author_book(b_name CHAR(20) RETURNS CHAR(8) BEGIN RETURN (SELECT 作者 FROM Book WHERE 书名= b_name); END$ DELIMITER ; 此存储函数给定书名，返回该书的作者。如要查询计算机应用基础的作者，用author_book(计算机应用基础)。,41,【例8.31】创建一个存储函数来删除Sell表中有但Book表中不存在的记录。 CREATE FUNCTION del_Sell1(b_bh CHAR(20) RETURNS BOOLEAN BEGIN DECLARE bh CHAR(20); SELECT 图书编号 INTO bh FROM Book WHERE 图书编号=b_bh; IF bh IS NULL THEN DELETE FROM Sell WHERE 图书编号=b_bh; RETURN TRUE; ELSE RETURN FALSE; END IF; END$,存储函数举例,42,课堂练习,1、创建一个存储函数，返回员工的总人数EM_NUM() 2、创建一个存储函数，判断员工是否在研发部工作，若是则返回其学历，若不是则返回“NO”。,43,课堂练习参考答案,1、创建一个存储函数，返回员工的总人数EM_NUM() CREATE FUNCTION EM_NUM() RETURNS Integer RETURN ( SELECT count( * ) FROM Employees); 2、创建一个存储函数，判断员工是否在研发部工作，若是则返回其学历，若不是则返回“NO”。 CREATE FUNCTION XL(XH CHAR(6) RETURNS CHAR(10) BEGIN DECLARE BM,ED CHAR(6); SELECT departmentID,education INTO BM,ED FROM employees WHERE employeeID =XH; IF bm=“4” THEN RETURN ED; ELSE RETURN “NO”; END IF; END,44,任务四 触发器,创建触发器语法格式： CREATE TRIGGER 触发器名 触发时间 触发事件 ON 表名 FOR EACH ROW 触发器动作 触发时间：触发器触发的时刻，有两个选项：AFTER和BEFORE，以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行几个或更多的改变，通常使用AFTER选项；如果想要验证新数据是否满足使用的限制，则使用BEFORE选项。 触发事件：指明了激活触发程序的语句的类型。触发事件可以是下述值之一： INSERT：将新行插入表时激活触发器。例如，通过INSERT、LOAD DATA和REPLACE语句。 UPDATE：更改某一行时激活触发器。例如，通过UPDATE语句。 DELETE：从表中删除某一行时激活触发器。例如，通过DELETE和REPLACE语句。 触发器动作：包含触发器激活时将要执行的语句。如果要执行多个语句，可使用BEGIN . END复合语句结构。这样，就能使用存储过程中允许的相同语句。,45,创建触发器举例,【例8.34】 创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将用户变量str的值设为“TRIGGER IS WORKING”。 CREATE TABLE table1(a INTEGER); CREATE TRIGGER table1_insert AFTER INSERT ON table1 FOR EACH ROW SET str= TRIGGER IS WORKING ; 向table1中插入一行数据： INSERT INTO table1 VALUES(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 $ CREATE TRIGGER book_del AFTER DELETE ON Book FOR EACH ROW BEGIN DELETE FROM Sell WHERE 图书编号=OLD.图书编号; END$ DELIMITER ; 现在验证一下触发器的功能： DELETE FROM Book WHERE 图书编号=ISBN 7-5006-6625; 使用SELECT语句查看Sell表中的情况： SELECT * FROM Sell WHERE 图书编号=ISBN 7-5006-6625; 这时可以发现，图书编号为ISBN 7-5006-6625在Sell表中的所有信息已经被删除了。,47,课堂练习,创建触发器，在Employees表中当删除员工信息的同时将Salary表中与该员工有关的数据全部删除。,48,DELIMITER $ CREATE TRIGGER EM_DELETE AFTER DELETE ON Employees FOR EACH ROW BEGIN DELETE FROM salary WHERE employeeID=OLD.employeeID; END$ DELIMITER ;,课堂练习参考答案,49,创建触发器举例,【例8.36】 创建一个触发器，当修改Sell表中订购册数时，如果修改后的订购册数小于5本，则触发器将该对应的折扣修改为1,否则，折扣修改为0.8。 DELIMITER $ CREATE TRIGGER sell_update BEFORE UPDATE ON Sell FOR EACH ROW BEGIN IF NEW.订购册数5 THEN UPDATE Book SET 折扣=1 WHERE 图书编号=NEW.图书编号; ELSE UPDATE Book SET 折扣=0.8 WHERE 图书编号=NEW.图书编号; END IF; END$ DELIMITER ;,50,【例8.37】 创建触发器，实现当向Sell表插入一行数据时，根据订购册数对Book进行修改。如果订购册数10，Book表中折扣在原折扣基础上再打0.95折，否则折扣不变。 DELIMITER $ CREATE TRIGGER Sell_ins AFTER INSERT ON Sell FOR EACH ROW BEGIN IF NEW.订购册数10 THEN UPDATE Book SET 折扣=折扣*.95 WHERE 图书编号=NEW.图书编号; END IF; END$ DELIMITER ;,创建触发器举例,51,课堂练习,创建触发器，实现当向Employees表插入一行数据时，对Salary表也插入一行，EmployeeID与Employees表中的EmployeeID相同，InCome和outcome为0.,52,DELIMITER $ CREATE TRIGGER EM_INSERT AFTER INSERT ON Employees FOR EACH ROW BEGIN INSERT INTO SALARY VALUES(NEW. EmployeeID,0,0); END$ DELIMITER ;,课堂练习参考答案,53,触发器中调用存储过程,【例8.38】 假设Bookstore数据库中有一个与Members表结构完全一样的表member_backup，创建一个触发器，在Members表中添加数据的时候，调用存储过程，将member_backup表中的数据与Members表同步。 首先，定义存储过程：创建一个与Members表结构完全一样的表member_backup DELIMITER $ CREATE PROCEDURE data_copy() BEGIN REPLACE member_backup SELECT * FROM Members; END$ 接着创建触发器：调用存储过程data_copy() DELIMITER $ CREATE TRIGGER members_ins AFTER INSERT ON Members FOR EACH ROW CALL data_copy(); DELIMITER ;,54,触发器的删除,和其他数据库对象一样，使用DROP语句即可将触发器从数据库中删除。 语法格式： DROP TRIGGER 触发器名 【例8.39】 删除触发器members_ins。 DROP TRIGGER members_ins; 创建触发器使用CREATE TRIGGER语句 ，要查看数据库中有哪些触发器可以使用SHOW TRIGGERS命令。 SHOW TRIGGERS,55,课堂练习,创建触发器，实现若将Employees表中员工的工作年限增加n年，收入增加n*500。,56,DELIMITER $ CREATE TRIGGER EM_update AFTER update ON Employees FOR EACH ROW BEGIN DECLARE years INT(1); set years=NEW.workyear-OLD.workyear; IF years=0 THEN UPDATE salary SET income=income+500*years where employeeID=NEW.employeeid; END IF; END$ DELIMITER ;,课堂练习参考答案,57,