数据库原理与应用快速入门 课件 第6章 使用其他数据库对象

数据库原理与应用快速入门6.1使用视图6.2使用存储过程6.3使用触发器第6章

使用其他数据库对象6.4使用MySQL系统函数6.1.1理解视图的概念6.1.2定义视图6.1.3查询视图6.1使用视图6.1.4更新视图6.1.5删除视图6.1.1理解视图的概念所谓的视图，简单地说就是一段以特定名称保存起来SELECT查询。视图从表面上看，结构和表结构类似，而实际上，视图是一个从基本表中导出的虚拟表。基本表是实实在在存在的，而视图的虚拟的，它存放的只是一些代码。当用户要查看视图时，其实是先运行了这些代码来提取基本表内的信息，通过视图反映出来，而数据仍保存在基本表中。所以，当基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。视图就像一个窗口，透过它可以看到表中自己感兴趣的数据及其变化。6.1.1理解视图的概念对视图的操作和对表的操作方式相同，包括查询视图、插入数据、修改数据和删除数据。通过视图插入、修改和删除数据，因数据最终是保存到基本表里的，所以操作最终影响的是表中的数据。6.1.1理解视图的概念视图的作用一般表现为两个方面：一是整合数据，按需要呈现数据。二是辅助权限设置，实现数据安全。6.1.2定义视图SQL中使用CREATEVIEW命令定义视图，其一般格式为：CREATEVIEW<视图名>[(<列名1>[,<列名2>])…]

AS<子查询>

[WITHCHECKOPTION]6.1.2定义视图需要注意，在以下三种情况下，定义视图时必须明确指定组成视图的所有列名：1、某个目标列不是单纯的属性名，而是函数或列表达式；2、多表连接时选出了几个同名列作为视图的字段；3、需要在视图中为某个列启用新的更合适的名字。6.1.2定义视图数据库系统执行CREATEVIEW语句的结果，只是把视图的定义存入系统数据字典，并不执行其中的SELECT语句。只是在对视图进行查询时，才按视图的定义从基本表中将数据查出。6.1.2定义视图【例6-1】建立计算机应用班的学生的视图，视图名为“csstu1”。6.1.2定义视图【例6-2】建立计算机应用班的学生的视图，视图名为“csstu2”，并要求进行修改和插入操作时仍需要保证该视图只有计算机应用班的学生。6.1.2定义视图【例6-3】建立计算机应用班中选修了001号课程的学生的视图，视图命名为“cs001”，视图的列包括学号，姓名，课程号和成绩。6.1.2定义视图【例6-4】建立计算机应用班中选修了001号课程且成绩在90分以上的学生的视图，视图命名为“cs00190”，视图的字段包括学号，姓名，课程号和成绩。6.1.2定义视图【例6-5】建立一个反映学生年龄的视图，视图命名为“stuage”，视图的列包括学号，姓名和年龄。6.1.2定义视图【例6-6】建立为一个视图，统计每个学生课程的平均成绩，视图命名为“stuavgscore”,视图的字段包括学号和平均成绩。6.1.3查询视图对视图的查询其实就是对基本表的查询。6.1.3查询视图【例6-7】在视图“csstu1”的中找出电话号码以“22”开头的学生，查询结果包括学号，姓名和电话。6.1.3查询视图【例6-8】查询计算机应用班中选修了002号课程的学生,查询结果包括学号，姓名和课程号。6.1.3查询视图【例6-9】在视图“stuavgscore”中查询平均成绩在85分以上的学生学号和平均成绩。6.1.4更新视图更新视图是指通过视图来插入、删除和修改数据。因为视图是一个虚拟表，所以对视图的更新，最终要转换为对基本表的更新。6.1.4更新视图【例6-10】将视图“csstu1”中学号为“210102002”的学生姓名改为“欧阳毅”。6.1.4更新视图【例6-11】向视图“csstu2”中插入一个新的学生记录(stuId：210102031；stuName：赵小新；class：计算机应用；sex：男；birth：2003-11-16；telNo：2779812；Email:Zxxing@21cn.Com；comment：Null)。6.1.4更新视图【例6-12】删除视图“csstu2”中学号为“210102031”的记录。6.1.4更新视图总的来说，以下情况下视图不可更新：1、若视图是由两具以上基本表导出的，则此视图不允许更新。2、若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。3、若视图的字段来自集函数，则此视图不允许更新。4、若视图定义含有GROUPBY子句，则此视图不允许更新。6.1.4更新视图总的来说，以下情况下视图不可更新：5、若视图定义含有DISTINCT短语，则此视图不允许更新。6、若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。7、一个不允许更新的视图上定义的视图也不允许更新。6.1.5删除视图有时候，某些视图在完成一定业务之后就不需要了，这时管理员就应该把它们删除。需要注意的是，删除视图只是将视图的定义从系统数据字典中删除，而不等同于删除基本表。视图被删除了，而基本表依旧存在。6.1.5删除视图【例6-13】删除视图“csstu1”。谢谢！数据库原理与应用快速入门6.1使用视图6.2使用存储过程6.3使用触发器第6章

使用其他数据库对象6.4使用MySQL系统函数6.2.1理解存储过程的概念6.2.2创建和执行存储过程6.2.3查看存储过程6.2使用存储过程6.2.4删除存储过程6.2.1理解存储过程的概念我们可以设想，将这些执行频率较高的操作事先用一段命令编写好，保存在数据库服务器端。这样，每次执行操作时，只需要输入简单的指令来执行这段命令，从而简化了操作，提高工作效率。这种方法就是使用存储过程。6.2.1理解存储过程的概念存储过程和表、视图一样，也是一种数据库对象。它是在服务器端保存和执行的一组SQL语句的集合，主要用于提高数据库中检索数据的速度，以及向表中写入或修改数据。用户需要执行存储过程时，只需要给出存储过程的名称和必要的参数即可。6.2.1理解存储过程的概念在数据库中，使用存储过程有以下优点：1、存储过程在服务器端运行，执行速度快、效率高。2、存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中。当需要再次执行时，只要从高速缓冲存储器中调用已编译好的二进制代码执行即可，从而提高了系统性能。3、确保数据库的安全。使用存储过程可以完成所有的数据库操作，并可以通过编程方式控制上述操作对数据库信息访问的权限。4、自动完成需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，从而大大方便了用户的使用，自动完成一些需要预先执行的任务。6.2.2创建和执行存储过程存储过程由CREATEPROCEDURE语句创建，其语法如下：CREATEPROCEDUREproc_name([proc_parameter])Routine_body6.2.2创建和执行存储过程各部分的参数解释如下：1、proc_name：用于指定要创建的存储过程的名称。2、proc_parameter：存储过程的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。参数的形式：[IN|OUT|INOUT]para_nametype其中，IN表示输入参数，用于接收程序执行过程中的外部输入值；OUT表示输出参数，用于将程序执行的结果输出；INOUT表示参数既可以接收输入也可以用于输出。如果声明时不指定参数类型，默认为IN参数。para_name表示参数的名称，type表示参数的数据类型，可以是MySQL中支持的任意数据类型。3、Routine_body：存储过程主体代码，可包含的任意数目和类型的SQL语句。可以用BEGIN...END来表示代码的开始和结束。6.2.2创建和执行存储过程【例6-14】在student数据库中创建名为“Stuproc1”的存储过程，用来查询班级为“计算机应用”的学生的学号、姓名和性别。6.2.2创建和执行存储过程存储过程创建完成后，可以使用CALL语句来执行，称为调用存储过程。CALL语句的语法格式如下：

CALLproc_name([proc_parameter])6.2.2创建和执行存储过程【例6-15】调用存储过程“Stuproc1”，查看其执行结果。6.2.2创建和执行存储过程【例6-16】创建存储过程“Stuproc2”，根据用户输入的班级名，查询该班学生的学号、姓名和性别。6.2.2创建和执行存储过程【例6-17】调用存储过程“Stuproc2”，查询“软件技术”班的学生情况。6.2.2创建和执行存储过程【例6-18】创建存储过程“Stuproc3”，根据用户输入的学号和课程名，返回该课程的成绩。6.2.2创建和执行存储过程【例6-19】执行存储过程“Stuproc3”，查询学号为“210101001”、课程名为“数学”的成绩。6.2.2创建和执行存储过程【例6-20】创建存储过程“Stuproc4”，根据用户输入的学号，返回该学生的选课门数。6.2.2创建和执行存储过程【例6-21】执行存储过程“Stuproc4”，查询学号为“210101001”的学生的选课门数。6.2.3查看存储过程如果要了解存储过程的状态信息，可以使用SHOWSTATUS语句或SHOWCREATE语句来查看，也可以通过系统数据库information_schema来查询。6.2.3查看存储过程1、使用SHOWSTATUS语句查看存储过程的状态SHOWSTATUS语句的语法结构如下：SHOWPROCEDURESTATUS[LIKE'pattern']其中，LIKE'pattern'可选，用于匹配存储过程的名称。6.2.3查看存储过程【例6-22】使用SHOWSTATUS语句查看存储过程“Stuproc1”的状态。6.2.3查看存储过程2、使用SHOWCREATE语句查看存储过程的状态SHOWCREATE语句的语法格式如下：

SHOWCREATEPROCEDUREproc_name其中，proc_name表示要查看的存储过程的名称。6.2.3查看存储过程【例6-23】使用SHOWCREATE语句查看存储过程“Stuproc1”的状态。6.2.3查看存储过程3、通过系统数据库information_schema查看存储过程的状态MySQL将存储过程的信息存储在系统数据库information_schema中的Routines表里。可以通过查询该表来查看存储过程的信息，语法格式如下：SELECT*FROMinformation_schema.RoutinesWHEREROUTINE_NAME='proc_name';其中，ROUTINE_NAME列中存储的是存储过程的名称，proc_name表示要查询的存储过程名称。6.2.3查看存储过程【例6-24】从系统数据库information_schema中的Routines表里查看存储过程“Stuproc1”的状态。6.2.4删除存储过程如果存储过程已经不常用或不能满足业务的需要，我们就可以删除它。删除存储过程使用DROP命令，DROP命令可以将一个或者多个存储过程从当前数据库中删除，其语法形式如下：

DROPPROCEDURE[IFEXISTS]proc_name;其中，IFEXISTS子句可选，如果该存储过程不存在，它可以防止发生错误。proc_name表示要删除的存储过程的名称。6.2.4删除存储过程【例6-25】删除存储过程“Stuproc1”。谢谢！数据库原理与应用快速入门6.1使用视图6.2使用存储过程6.3使用触发器第6章

使用其他数据库对象6.4使用MySQL系统函数6.3.1理解触发器的概念6.3.2创建和激活触发器6.3.3查看触发器6.3使用触发器6.3.4删除触发器6.3.1理解触发器的概念触发器是一种特殊类型的存储过程，它不是由用户通过命令来执行的，而是在用户对数据表执行了插入、删除或修改等操作时自动激活执行的。触发器用于执行一定的业务规则，保证数据完整性，也用于实现数据库的某些管理任务和附加功能。触发器通过特定事件激活而执行，而存储过程通过存储过程名称直接调用。可以说，存储过程像遥控炸弹，我们可以根据需要控制它何时爆炸；而触发器却像地雷，一旦踩中就会爆炸。6.3.1理解触发器的概念触发器是一个功能强大的工具，主要优点包括：1、触发器自动执行。当对表中的数据作了预定义的操作之后立即被激活执行。2、触发器可以针对多个表进行操作，从而对相关表进行级联更改。3、触发器可以实现复杂的数据完整性约束，更适合在大型数据库管理系统中用于保障数据的完整性。6.3.2创建和激活触发器触发器由CREATETRIGGER语句创建，其语法如下：CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtable_nameFOREACHROWtrigger_statement;6.3.2创建和激活触发器各部分的参数解释如下：1、trigger_name：用于指定要创建的触发器的名称。2、trigger_time：表示触发器触发的时机，可以指定为BEFORE或AFTER，即触发程序是在激活它的语句之前或之后执行。3、trigger_event：表示激活触发器的语句类型，包括INSERT、UPDATE、DELETE。INSERT表示新的数据行插入表示激活触发器。UPDATE表示表中的数据发生更改时激活触发器。DELETE表示删除表中的数据时激活触发器。6.3.2创建和激活触发器各部分的参数解释如下：4、table_name：表示建立触发器的表名。5、FOREACHROW：表示触发器的执行间隔，触发器每隔一行执行一次动作，而不是对整个表执行一次。6、trigger_statement：指定触发器执行的SQL语句，可以是一条语句，或者使用BEGIN...END包含的多条语句。6.3.2创建和激活触发器MySQL中定义了OLD和NEW，用于表示触发器所在的表，激活触发器的那一行数据。当执行INSERT操作激活触发器时，NEW用来表示将要或已经插入的新数据。当执行DELETE操作激活触发器时，OLD用来表示将要或已经删除的原数据。当执行UPDATE操作激活触发器时，OLD用来表示将要或已经修改的原数据，NEW用来表示将要或已经修改的新数据。使用方法为：

NEW.colName或OLD.colName

其中，colName表示相应数据表的某一列名。6.3.2创建和激活触发器【例6-26】创建一个名为“del_stu”的触发器，当一名学生退学时，将该学生的学号、姓名、班级和电话号码存入delstudents表中。1、首先创建一张delstudents表：6.3.2创建和激活触发器【例6-26】创建一个名为“del_stu”的触发器，当一名学生退学时，将该学生的学号、姓名、班级和电话号码存入delstudents表中。2、随后创建触发器del_stu：6.3.2创建和激活触发器【例6-26】创建一个名为“del_stu”的触发器，当一名学生退学时，将该学生的学号、姓名、班级和电话号码存入delstudents表中。3、尝试删除学生表中的一条记录：6.3.2创建和激活触发器【例6-26】创建一个名为“del_stu”的触发器，当一名学生退学时，将该学生的学号、姓名、班级和电话号码存入delstudents表中。4、查询delstudents表，验证删除的学生记录是否保存进了delstudents表。6.3.2创建和激活触发器【例6-27】创建一个名为“up_sc”的触发器，当students表里学生的学号发生更改时，同时更改sc表中对应的学生的学号。6.3.3查看触发器如果要了解数据库中已经存在的触发器的定义、状态和语法信息等，可以使用SHOWTRIGGERS语句来查看，也可以通过系统数据库information_schema中的triggers表来查询。6.3.3查看触发器1、使用SHOWTRIGGERS语句查看触发器信息使用SHOWTRIGGERS语句查询触发器信息的语句如下：SHOWTRIGGERS;6.3.3查看触发器2、通过系统数据库information_schema中的triggers表查看触发器信息MySQL中，所有触发器的定义都保存在information_schema数据库中的triggers表中。可以通过SELECT命令来查看，语法格式如下：SELECT*FROMinformation_schema.triggerswherecondition;其中，condition表示查询条件。6.3.3查看触发器【例6-28】通过SELECT命令查询触发器“del_stu”的信息。6.3.4删除触发器如果系统中的触发器已经不能满足业务的需要，我们就可以删除它。删除触发器使用DROP命令，其语法形式如下：

DROPTRIGGER[schema_name.]trigger_name;

其中，schema_name表示数据库名称，可以省略。如果省略了schema_name，则表示从当前数据库中删除触发器。trigger_name表示要删除的触发器的名称。6.3.4删除触发器【例6-29】删除触发器“del_stu”。谢谢！数据库原理与应用快速入门6.1使用视图6.2使用存储过程6.3使用触发器第6章

使用其他数据库对象6.4使用MySQL系统函数6.4.1使用字符串函数6.4.2使用数学函数6.4.3使用日期和时间函数6.4使用MySQL系统函数6.4.4使用加密函数6.4.1使用字符串函数字符串函数用于对字符串型数据进行处理，常用的MySQL字符串函数如表6.1所示。表6.1常用字符串函数函数名称功能描述LENGTH(str)计算字符串长度函数，返回字符串的字节长度CHAR_LENGTH(str)计算字符串字符数函数，返回字符串包含的字符数LOWER(str)小写字母转换函数，将字符串转换为小写字符UPPER(str)大写字母转换函数，将字符串转换为大写字符LEFT(str,n)左子串函数，返回字符串最左边的n个字符RIGHT(str,n)右子串函数，返回字符串从右边开始的n个字符LTRIM(str)删除前导空格函数，返回删除了前导空格之后的字符表达式RTRIM(str)删除尾随空格函数，返回删除了尾随空格之后的字符表达式TRIM(str)删除空格函数，返回删除了前导和尾随空格之后的字符表达式CONCAT(str1,str2,...)合并字符串函数，返回由多个字符串连接后的字符串REPLACE(str,str1,str2)替换函数，使用str2替换字符串str中所有的字符串str1SUBSTRING(str,n,len)获取子串函数，从字符串str的起始位置n开始，返回一个长度与len相同的子字符串REVERSE(str)字符串反转函数，返回与原始字符串顺序相反的字符串INSERT(str1,x,len,str2)替换字符串函数，将字符串str1中x位置开始，长度为len的字符串用str2替换6.4.1使用字符串函数【例6-30】计算字符串“database”和“数据库”的长度。6.4.1使用字符串函数【例6-31】将字符串“database”和“DATABASE”进行大小写转换。6.4.1使用字符串函数【例6-32】分别返回字符串字符串“database”最左边的3个字符和从右边开始的4个字符。6.4.1使用字符串函数【例6-33】去掉字符串“database”中的空格。6.4.1使用字符串函数【例6-34】将字符串“data”和“base”拼接起来。6.4.1使用字符串函数【例6-35】将字符串“”中的字符“x”替换为“w”。6.4.1使用字符串函数【例6-36】将字符串“”从第5个字符开始，返回长度为5的字符串。6.4.1使用字符串函数【例6-37】实现字符串“database”的顺序反转。6.4.1使用字符串函数【例6-38】将字符串“”从第5个字符开始，长度为5的字符串替换为“oracle”。6.4.2使用数学函数数学函数用于对数值数据进行处理，常用的MySQL数学函数如表6.2所示。表6.2常用数学函数函数名称功能描述ABS(x)返回x的绝对值SQRT(x)返回非负数x的二次方根MOD(x,y)返回x被y除后的余数CEIL(x)和CEILING(x)两个函数功能相同，返回不小于x的最小整数，即向上取整FLOOR(x)返回不大于x的最大整数值，即向下取整RAND(x)生成一个0-1的随机数。如指定整数x，则用于产生重复序列ROUND(x,y)对参数x按y的精度进行四舍五入。SIGN(x)返回参数x的符号，正、负和零分别用1、-1和0进行表示。POW(x,y)和POWER(x,y)两个函数功能相同，返回x的y次乘方的结果SIN(x)返回参数x的正弦值ASIN(x)返回参数x的反正弦值COS(x)返回参数x的余弦值ACOS(x)返回参数x的反余弦值TAN(x)返回参数x的正切值ATAN(x)返回参数x的反正切值COT(x)返回参数x的余切值6.4.2使用数学函数【例6-39】求数字5、-2.5、0的绝对值。6.4.2使用数学函数【例6-40】将数字24.58分别四舍五入到小数点后