MySQL数据库 课件汇 姜云桥 任务3.5 连接查询 - 5.2 备份与恢复的应用

主讲教师：姜云桥任务3.5连接查询子项目3数据库的查询开发任务目标1.能利用内连接完成多表数据的查询2.能利用左外连接完成多表数据的查询3.能利用右外连接完成多表数据的查询4.能利用自连接完成数据的查询知识目标1.掌握内连接innerjoin的语法2.掌握左外连接LEFTjoin的语法3.掌握右外连接RIGHTjoin的语法4.掌握自连接的语法能力目标任务要求利用数据库jxgl中的student(学生信息表)、course（课程表）、score（成绩表）、department（院系单位表）、teacher（教师表）和teach（讲授表）6张完成内连接的各种查询，左外连接的各种查询，右外连接的各种查询以及自连接的查询。任务必备知识想一想如何进行连接查询？任务必备知识3.5.1内连接内连接用于返回两个表中满足连接条件的数据，使用关键字innerjoin表示，也可以简写成join，其语法格式如下所示:fromtable_1innerjointable_2oncondition_1innerjointable_3oncondition_2...innerjointable_noncondition_n任务必备知识3.5.1内连接【任务3.5.1】查询”李军”各科成绩mysql>selectst.s_name,sc.c_no,sc.mark->fromstudentstjoinscoresconst.s_no=sc.s_no->wherest.s_name='李军';任务必备知识3.5.1内连接【任务3.5.2】查询每位同学的姓名、最高分、最低分、平均分mysql>selectst.s_name,max(sc.mark),min(sc.mark),avg(sc.mark)->fromstudentstjoinscoresconst.s_no=sc.s_no->groupbyst.s_name;任务必备知识3.5.1内连接【任务3.5.3】查询平均成绩大于等于75分的同学的姓名、学号和平均成绩（保留一位小数）mysql>selectst.s_name,st.s_no,round(avg(sc.mark),1)as'平均成绩'->fromstudentstjoinscoresconst.s_no=sc.s_no->groupbyst.s_no->havingavg(sc.mark)>=75;任务必备知识3.5.1内连接【任务3.5.4】查询同时学过课程”A001”和”A002”学生的基本信息mysql>selectst.s_no,st.s_name,st.s_sex,st.s_birth,st.d_no,st.s_address,st.s_phone->fromstudentstjoinscoresconst.s_no=sc.s_no->groupbyst.s_no->havingsum(if(sc.c_noin('A001','A002'),1,0))>1;任务必备知识3.5.1内连接【任务3.5.5】查询”吴“姓同学课程的成绩，显示姓名，课程名，成绩mysql>selectst.s_name,c.c_name,sc.mark->fromstudentstjoinscoresconst.s_no=sc.s_no->joincourseconc.c_no=sc.c_no->wherest.s_namelike'吴%';任务必备知识3.5.2左外连接左外连接语法如下：fromtable_1leftjointable_2oncondition_1leftjointable_3oncondition_2...leftjointable_noncondition_n任务必备知识3.5.2左外连接【任务3.5.6】查询每位老师的授课信息mysql>select*fromteacherleftjointeachonteacher.t_no=teach.t_no;LeftjoinLeftjoin任务必备知识3.5.2左外连接【任务3.5.7】查询各系部名称和对应的学生人数，按照人数降序排列mysql>selectd.d_name,count(s.s_name)asstudent_num->fromdepartmentdleftjoinstudentsond.d_no=s.d_no->groupbyd.d_no->orderbystudent_numdesc;任务必备知识3.5.3右外连接fromtable_1rightjointable_2oncondition_1rightjointable_3oncondition_2...rightjointable_noncondition_n任务必备知识【任务3.5.8】通过右外连接方式实现【任务3.5.6】mysql>select*fromteachrightjointeacheronteacher.t_no=teach.t_no;3.5.3右外连接任务必备知识【任务3.5.9】通过右外连接实现【任务3.5.7】mysql>selectd.d_name,count(s.s_name)asstudent_num ->fromstudentsrightjoindepartmentdond.d_no=s.d_no ->groupbyd.d_no->orderbystudent_numdesc;3.5.3右外连接任务必备知识【任务3.5.10】从教师授课表teach中找出授课相同的组合，即同一门课存在多个教师授课mysql>selectdistinctt1.t_no,t1.c_no ->fromteacht1jointeacht2ont1.c_no=t2.c_noandt1.t_no<>t2.t_no;3.5.4自连接任务实施1.查询各部门名称及其对应的员工数量，结果如下所示:任务实施2.查询各学历对应的平均福利、最高福利、最低福利，结果如下:任务实施3.查询各部门的名称及其对应的男、女人数，结果如下:任务实施4.查询”王”姓员工的基本收入，结果如下:任务实施5.仅查询“学院”的名称及对应的职称的人数，结果按系部名称和职称排序，其中职称的顺序依次为教授、副教授、讲师、助讲:任务实施6.查询各部门名称，及其对应的平均基本收入（保留两位有效小数），结果如下:任务实施要求撰写实训总结，把完成任务过程中主要操作、命令及关键步骤的截图，遇到的问题与解决方法、未解决或需要进一步探讨的问题、以及任务实践过程中的收获和经验教训的内容写入总结并提交。学习活动展示学习产品（学习超市）：优化（完善）设计学习产品展示提交点评归纳什么是内连接，如何实现？什么是左外连接，如何实现？什么是右外连接，如何实现？什么是自连接，如何实现？成绩考核考核项目完成度线上各个考核项目完成情况课堂预习问题回答课堂表现布置作业任务书3.6主讲教师：姜云桥谢谢观看THANKYOU主讲教师：姜云桥任务3.6子查询与union查询子项目3数据库的查询开发任务目标1.能熟练使用子查询方式完成各种查询需求2.能使用集合查询完成查询需求知识目标1.掌握子查询可以出现的位置。2.掌握标量子查询3.掌握列子查询4.掌握行子查询5.掌握表子查询6.掌握集合查询能力目标任务要求利用数据库jxgl中的student(学生信息表)、course（课程表）、score（成绩表）、department（院系单位表）、teacher（教师表）和teach（讲授表），完成标量子查询、列子查询、行子查询、表子查询以及合并查询任务必备知识想一想什么是子查询，分为几种类型？任务必备知识3.6.1子查询子查询是指嵌套在其他sql语句（如insert、delete、select、update等）中的查询语句。子查询也称为内查询，必须位于括号之中；包含子查询的查询被称为外查询。MySQL中的子查询按照返回结果可以分为以下几种类型：标量子查询：返回单个值（一行一列）的子查询。列子查询：返回单列结果（多行一列）的子查询。行子查询（RowSubquery）：返回单行结果（一行多列）的子查询，标量子查询是行子查询的一个特例。表子查询（TableSubquery）：返回一个虚拟表（多行多列）的子查询，行子查询是表子查询的一个特例。任务必备知识3.6.1子查询按照内查询与外查询之间的关系可分为：非关联子查询：内查询不依赖外查询的信息，可独立运行。关联子查询：内查询引用外查询的字段，无法独立运行。任务必备知识一、标量子查询是指子查询返回的是单一值的标量，如一个数字或一个字符串。可以使用=、>、<、>=、<=、<>等操作符对子查询的标量结果进行比较。3.6.1子查询【任务3.6.1】查询成绩表score，返回大于平均成绩的记录mysql>select*->fromscore->wheremark>(selectavg(mark)fromscore);任务必备知识二、列子查询指子查询返回的结果集是N行一列，该结果通常来自对表的某个字段查询返回，可以使用in、any、some和all等操作符，含义如下。3.6.1子查询任务必备知识3.6.1子查询【任务3.6.2】查询授课任务的老师的信息mysql>select*fromteacherwheret_noin(selectdistinctt_nofromteach);任务必备知识三、行子查询子查询返回的结果集是一行N列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。3.6.1子查询【任务3.6.3】查询与”刘光明“同学同性别同系部的学生的基本信息mysql>select*->fromstudent->where(s_sex,d_no)=(selects_sex,d_nofromstudentwheres_name='刘光明')->ands_name<>'刘光明‘；任务必备知识四、表子查询子查询返回的结果集是N行N列的一个表数据。表子查询通常用于FROM子句或者查询条件中。3.6.1子查询【任务3.6.4】使用表子查询获取各系部名称和学生的数量mysql>selectd.d_nameas'系部名称',ifnull(s.num,0)as'学生数量'->fromdepartmentdleftjoin(-> selectd_no,count(*)asnumfromstudentgroupbyd_no->)s->ond.d_no=s.d_no;任务必备知识3.6.1子查询任务必备知识五、关联子查询内部查询引用了外部查询列，即内部查询会对外部查询的每行进行一次计算，需要注意，使用关联子查询是最容易出现错误的地方。3.6.1子查询【任务3.6.5】使用关联子查询获得各个系部的教师的数量，显示系部名称，教师数量mysql>selectd.d_nameas'系部名称',->(selectcount(*)fromteachertwheret.d_no=d.d_no)as'教师数量' ->fromdepartmentd;任务必备知识3.6.1子查询+--------------+--------------+|系部名称|教师数量|+--------------+--------------+|人文学院|2||信息学院|5||外语学院|2||建工学院|2||机电学院|2||珠宝学院|2||管理学院|2||金融学院|3|+--------------+--------------+任务必备知识六、exists操作符exists检查指定查询是否产生某些行。根据子查询是否返回行，exists仅返回true或false，不会返回unkown，这是和其他操作符（如in、any、all等）的区别，notexists同样如此，只不过功能相反。3.6.1子查询任务必备知识3.6.1子查询【任务3.6.6】返回不存在女性学生的系部名称mysql>selectd.d_name->fromdepartmentd->wherenotexists(->select1fromstudentstwherest.d_no=d.d_noandst.s_sex='女');任务必备知识3.6.2合并查询union操作符用于将两个查询结果合并成一个结果集。基本语法格式如下：selectcol_a1,col_a2,...fromtable1union[distinct|all]selectcol_b1,col_b2,...fromtable2;任务必备知识【任务3.6.7】通过unionall查询成绩大于90和小于60的学生的学号mysql>selects_nofromscorewheremark>90->unionall->selects_nofromscorewheremark<60;3.6.2合并查询任务必备知识【任务3.6.8】通过union查询成绩大于90和小于60的学生的学号mysql>selects_nofromscorewheremark>90->union->selects_nofromscorewheremark<60;3.6.2合并查询任务必备知识【任务3.6.9】通过union查询成绩大于90和小于60的学生的学号，并按学号降序显示mysql>selects_nofromscorewheremark>90->union->selects_nofromscorewheremark<60->orderbys_nodesc;3.6.2合并查询任务必备知识【任务3.6.10】通过查询成绩表score成绩第一和最后一名的学生的学号和成绩mysql>(selects_no,markfromscoreorderbymarkdesclimit1) ->union ->(selects_no,markfromscoreorderbymarklimit1);3.6.2合并查询任务实施在人事管理数据库rsgl中进行如下操作1.查询和赵剑同职称、学历、政治背景的员工的姓名、职称、学历、政治背景，结果如下:任务实施2.查询没有员工的部门的名称，结果如下:3.查年龄大于50，并且不是信息学院与外语学院的员工姓名和性别,结果如下:任务实施4.查询收入最高（收入=基本收入+福利+补贴-公积金-税-其他）和最低的员工编号和收入，结果如下:5.查询超过基本收入平均值的员工的姓名，结果如下:任务实施要求撰写实训总结，把完成任务过程中主要操作、命令及关键步骤的截图，遇到的问题与解决方法、未解决或需要进一步探讨的问题、以及任务实践过程中的收获和经验教训的内容写入总结并提交。学习活动展示学习产品（学习超市）：优化（完善）设计学习产品展示提交点评归纳什么是子查询？学习各种类型的子查询学习关联子查询和无关子查询学习集合的union、intersect、except操作成绩考核考核项目完成度线上各个考核项目完成情况课堂预习问题回答课堂表现布置作业任务书4.1主讲教师：姜云桥谢谢观看THANKYOU主讲教师：姜云桥任务4.1视图的开发子项目4数据库高级应用开发任务背景出于安全的原因，有时要隐藏一些重要的数据信息。例如，社会保险基金表包含着客户的很多重要信息，如果只显示姓名、地址等基本信息，而不显示社会保险号和工资数等重要信息。任务目标1.能创建视图2.能查询、修改和删除视图3.能通过视图对数据进行操作知识目标1.理解视图2.了解视图特性3.掌握视图的创建,查询、修改和删除视图的方法能力目标任务要求本任务将从认识视图着手，学习视图的创建、查询、修改和删除方法，并学会通过视图对数据进行查询和统计，通过视图向基表进行数据更新的操作。任务必备知识想一想试解释视图？任务必备知识4.1.1视图的概念视图（view）是一种虚拟存在的表，本质上就是一个存储在数据库中的查询语句，因此，视图并不存储数据，数据来自定义视图时所引用的表，并且在引用视图时动态生成。合理使用视图可以给我们带来许多好处：简单：将复杂的查询定义为视图，直接基于视图进行操作，隐藏具体的实现。数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响。安全：使用视图的用户只能访问他们被允许访问的内容，如此可以限制访问某些敏感信息，例如身份证号、工资等。任务必备知识想一想如何创建视图？任务必备知识语法格式如下:create[orreplace]viewview_name[(column_list)]asselect_statement;4.1.2创建视图注意:虽然MySQL支持在视图定义中使用orderby子句，但是不建议在视图的定义中使用orderby子句，因为它并不能保证最终排序的结果，而且可能由于不必要的排序降低查询的性能。任务必备知识一、基于单表创建【任务4.1.1】基于student表创建包含学号、姓名、性别、院系的视图view_studentmysql>createorreplaceviewview_student->as->selects_no,s_name,s_sex,d_nofromstudent;4.1.2创建视图任务必备知识二、基于多表创建【任务4.1.2】创建视图view_mark，包括姓名、课程名和成绩字段mysql>createorreplaceviewview_mark->as->selectst.s_name,cs.c_name,sc.mark->fromstudentstjoinscoresconst.s_no=sc.s_no->joincoursecsonsc.c_no=cs.c_no;4.1.2创建视图任务必备知识三、基于视图创建【任务4.1.3】基于视图view_mark创建新视图view_mark_statistics，统计每位学生的总成绩、平均成绩。mysql>createorreplaceviewview_mark_statistics->as->selects_name,sum(mark)assum_mark,avg(mark)asavg_mark->fromview_mark->groupbys_name;4.1.2创建视图任务必备知识想一想如何查看视图？任务必备知识4.1.3查看视图查看视图是查看数据库中已存在的视图的定义。查看视图的方法包括showtables语句、desc语句和showcreateview语句等。一、查看已创建的视图视图是一个虚表，所以视图的查询还是如同查询基本表一样，用showtables命令进行查看,可看到新创建的视图。任务必备知识【任务4.1.4】查看已创建的视图mysql>showtableslike'view_%';4.1.3查看视图二、查看视图结构和查看表结果一样，通过desc命令可以查看视图的结构。【任务4.1.5】使用desc命令查看视图view_mark的结构mysql>descview_mark;任务必备知识三、查看视图的定义类似查看表的定义，通过showcreateview语句可以查看视图的定义4.1.3查看视图【任务4.1.6】查看视图view_mark的定义mysql>showcreateviewview_mark\G任务必备知识想一想如何使用视图？任务必备知识4.1.4使用视图一、查询数据【任务4.1.7】通过视图view_mark,查询选修了“操作系统”课程且成绩及格以上的学生mysql>selects_name,c_name,mark->fromview_mark->wherec_name='操作系统'andmark>=60;任务必备知识4.1.4使用视图二、插入数据【任务4.1.8】往视图view_student插入下列数据mysql>insertintoview_student(s_no,s_name,s_sex,d_no)->values('122008','张三','男','D001'),->('122013','李四','男','D002');任务必备知识4.1.4使用视图三、更新数据【任务4.1.9】更新视图view_student中“张三”的院系编号为“D003”mysql>updateview_student->setd_no='D003'->wheres_name='张三';任务必备知识4.1.4使用视图【任务4.1.10】通过视图view_mark修改“张群”的MYSQL课程的成绩为76mysql>updateview_mark->setmark=76->wheres_name='张群'andc_name='MYSQL';说明：若一个视图依赖于多个基本表，则一次更新只能修改一个基本表的数据，不能同时修改多个基本表的数据。任务必备知识4.1.4使用视图四、删除数据【任务4.1.11】通过视图view_student删除“章伟峰”的记录mysql>deletefromview_student ->wheres_name='章伟峰';任务必备知识想一想如何删除视图？任务必备知识4.1.5删除视图【任务4.1.12】删除视图view_mark、view_mark_statistics、view_studentdropview[ifexists]view_name1,view_name2,...说明：（1）声明了ifexists，视图不存在的话，也不会出现错误信息。（2）一次性删除多个视图时，视图之间通过逗号（,）分隔。mysql>dropviewifexistsview_mark,view_mark_statistics,view_student;任务实施在rsgl数据库中，进行如下操作：1.创建视图，统计每个系部博士、硕士、本科的人数，查询该视图运行效果如下:任务实施2.

.创建视图，统计系部编号为“A001”的员工的姓名、编号、性别、出生日期，查询视图运行效果如下：:任务实施3.创建视图，统计每个系的最高基本工资，最低基本工资，平均基本工资（保留2位小数），查询视图，运行效果如下:任务实施4.创建视图，统计暂时没有员工的系部名称、系部编号，查询视图运行效果如下:5.删除以上创建的视图。任务实施要求撰写实训总结，把完成任务过程中主要操作、命令及关键步骤的截图，遇到的问题与解决方法、未解决或需要进一步探讨的问题、以及任务实践过程中的收获和经验教训的内容写入总结并提交。学习活动展示学习产品（学习超市）：优化（完善）设计学习产品展示提交点评归纳试解释视图视图的特性如何创建、查看、使用、修改和删除视图？成绩考核考核项目完成度线上各个考核项目完成情况课堂预习问题回答课堂表现布置作业任务书4.2主讲教师：姜云桥谢谢观看THANKYOU主讲教师：姜云桥任务4.2索引的开发子项目4数据库高级应用开发任务背景由于数据库在执行一条SQL语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。在进行涉及多个表连接，包括了许多搜索条件（例如大小比较、Like匹配等），而且表数据量特别大的查询时，在没有索引的情况下，MySQL需要执行的扫描行数会很大，速度也会很慢。任务目标1.能创建索引2.能管理索引知识目标1.了解索引2.了解索引的分类3.理解索引的设计原则4.掌握创建索引的方法5.掌握管理索引的方法能力目标任务要求本任务将从认识索引、索引的分类以及索引的设计原则等方面着手，介绍创建和管理索引的方法。特别要注意的是，索引并不是越多越好，要正确认识索引的重要性和设计原则，创建合适的索引。任务必备知识想一想索引是什么？任务必备知识4.2.1索引的概念索引是一种特殊的数据库结构，可以用来快速查询数据库表中的记录。对于索引的理解可以直接类比查字典。当我们需要查找某个汉字的意思时，我们并不是从头挨着找到汉字，而是通过拼音索引快速锁定汉字所在的页码，进而找到对应汉字的解释。如果我们并不知道汉字的读音，可以通过部首笔画索引快速锁定汉字的页码，从而找到汉字的读音和解释。任务必备知识4.2.1索引的概念日常使用的索引主要包括如下几种：普通索引（index）：索引的关键字是index，这是最基本的索引，它没有任何限制。唯一性索引（unique）：关键字是unique。与普通索引类似，但是unique索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。在一个表上可以创建多个unique索引。主键索引（primarykey）：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。也可通过修改表的方法增加主键，但一个表只能有一个主键索引。任务必备知识想一想创建索引有几种方式，分别是？任务必备知识创建索引有3种方式，分别是:1.创建表的时候创建索引2.在已经存在的表上创建索引3.使用ALTERTABLE语句来创建索引4.2.2索引的建立任务必备知识一、创建表时创建【任务4.2.1】创建表student2，s_no为主键索引，s_name为唯一性索引，并在s_address列上前6位字符创建索引创建表的时候可以直接创建索引，这种方式最简单、方便。4.2.2索引的建立任务必备知识4.2.2索引的建立mysql>createtablestudent2->(->s_nochar(6)primarykeycomment'学号',->s_namevarchar(20)uniquecomment'姓名',->sexchar(2)default'男'comment'性别',->birthdatecomment'出生日期',->d_nochar(6)comment'所在系部',->addressvarchar(20)comment'家庭地址',->phonevarchar(20)comment'联系电话',->indexaddress_index(address(6))->);任务必备知识二、用createindex语句创建1.创建普通索引如果表已建好，可以使用createindex语句建立索引。createindexindex_nameontbl_name(col_name(length)[asc|desc],...)4.2.2索引的建立【任务4.2.2】student的d_no字段经常作为查询条件，建立普通索引mysql>createindexd_no_indexonstudent(d_no);任务必备知识2.创建唯一性索引4.2.2索引的建立【任务4.2.3】在course表的c_name列上建立一个唯一性索引c_name_indexmysql>createuniqueindexc_name_indexoncourse(c_name);像课程表的课程名，部门表的部门名，商品表的商品名之类的字段，一般情况下是，可建立一个唯一性索引。任务必备知识3.创建多列索引4.2.2索引的建立【任务4.2.4】在score表的s_no和c_no列上建立一个复合索引score_indexmysql>createindexscore_indexonscore(s_no,c_no);可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。任务必备知识三、通过altertable语句创建索引在已经存在的表上可以用altertable语句创建索引。altertabletbl_nameadd{unique|index|primarykey}[index_name](col_name(length)[asc|desc],...)4.2.2索引的建立任务必备知识4.2.2索引的建立【任务4.2.5】在teacher表上建立t_no主键索引（还未建立主键），建立t_name和d_no的复合索引，以加速表的检索速度mysql>altertableteacher ->addprimarykey(t_no), ->addindexmark_index(t_name,d_no);任务必备知识4.2.2索引的建立【任务4.2.6】在department表中的d_name创建唯一性索引mysql>altertabledepartment ->adduniqueindexd_name_index(d_name);分析与讨论（1）主键索引必定是唯一的，唯一性索引不一定是主键。（2）一张表上只能一个主键，但可以有一个或者多个唯一性索引。任务必备知识想一想MySQL如何查看索引？任务必备知识如果想要查看表中创建的索引的情况，可以使用showindexfromtbl_name语句4.2.3索引的查看【任务4.2.7】查看表student2中的索引mysql>showindexfromstudent2\g任务必备知识想一想MySQL如何删除索引？任务必备知识删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。对于已经存在的索引，可以通过dropindexindex_nameontbl_name来删除。4.2.4索引的删除【任务4.2.8】删除course表的mark索引mysql>dropindexmark_indexoncourse;也可用ALTERTABLE语句删除：altertabletbl_namedrop{indexindex_name|primarykey}任务必备知识4.2.4索引的删除【任务4.2.9】删除course表上的主键索引mysql>altertablecoursedropprimarykey;【任务4.2.10】删除course表上的唯一性索引c_name_indexmysql>altertablecoursedropindexc_name_index;任务必备知识（1）删除唯一性索引，如同删除普通索引一样，用dropindex语句即可，不能写成dropuniqueindex，但是创建唯一性索引要写成adduniqueindex。（2）删除主键索引，则需要altertable直接使用dropprimarykey子句进行删除，不需要提供索引名称，因为一个表中只有一个主键分析与讨论任务实施在rsgl数据库中完成下列操作：1.在employee表上通过createindex语句在birth字段创建名为index_birth的索引，创建完成后可查询到如下输出（省略了部分输出）：mysql>showindexfromemployee\G***************************2.row***************************Table:employeeNon_unique:1Key_name:index_birthSeq_in_index:1Column_name:birth……任务实施2.在employee表上通过altertable语句在e_name字段创建名为index_name的唯一性索引，创建完成后可查询到如下输出（省略部分输出）：mysql>showindexfromemployee\G***************************3.row***************************Table:employeeNon_unique:1Key_name:index_nameSeq_in_index:1Column_name:e_name……任务实施3.在employee表上通过createindex语句在d_id和education字段上创建多列索引。***************************4.row***************************Table:employeeNon_unique:1Key_name:index_mulSeq_in_index:1Column_name:d_id……***************************5.row***************************Table:employeeNon_unique:1Key_name:index_mulSeq_in_index:2Column_name:education……任务实施4.删除employee表上的唯一性索引，删除完成后查看索引信息，确保删除。5.删除employee表上的主键索引，删除完成后查看索引信息，确保删除。6.删除employee表上的多列索引，删除完成后查看索引信息，确保删除。任务实施要求撰写实训总结，把完成任务过程中主要操作、命令及关键步骤的截图，遇到的问题与解决方法、未解决或需要进一步探讨的问题、以及任务实践过程中的收获和经验教训的内容写入总结并提交。学习活动展示学习产品（学习超市）：优化（完善）设计学习产品展示提交点评归纳索引是？MySQL的索引分类？索引的设计原则？创建索引有几种方式，分别是？如何查看索引？如何删除索引？成绩考核考核项目完成度线上各个考核项目完成情况课堂预习问题回答课堂表现布置作业任务书4.3主讲教师：姜云桥谢谢观看THANKYOU主讲教师：姜云桥任务4.3存储过程和函数的开发子项目4数据库高级应用开发任务背景银行经常需要计算用户的利息，但不同类别的用户的利率是不一样的。这就可以将计算利率的SQL代码写成一个程序存放起来，用指定的用户类别作参数。这样的程序叫作存储过程或者存储函数。任务目标1.能创建存储过程2.能执行存储过程3.能修改和删除存储过程知识目标1.了解DElimitER命令2.理解存储过程3.掌握创建存储过程的方法4.掌握修改和删除存储过程的方法能力目标任务要求通过本次任务，使学生能够创建、执行、修改和删除存储过程；掌握创建基本的存储过程的方法，掌握创建带有变量的存储过程的方法，理解创建带有流程控制语句的存储过程的方法。任务必备知识想一想试解释存储过程？银行经常需要计算用户的利息，但不同类别的用户的利率是不一样的。这就可以将计算利率的sql代码写成一个程序存放起来，用指定的用户类别作参数。这样的程序叫作存储过程或者存储函数。使用时只要调用这个存储过程或者存储函数，根据指定的用户类别，就可以将不同类别用户的利息计算出来。任务必备知识任务必备知识4.3.1存储过程和函数的基本操作一、创建存储过程创建存储过程可以使用createprocedure语句。createprocedure[ifnotexists]sp_name([in|out|inout]param_nametype,...)routine_body任务必备知识【任务4.3.1】创建存储过程sp_delete_student，用指定的学号作为参数删除某一学生的记录4.3.1存储过程和函数的基本操作mysql>delimiter$$mysql>createproceduresp_delete_student(innochar(8))->begin->deletefromstudentwheres_no=no;->end$$mysql>delimiter;任务必备知识【任务4.3.2】创建带输出参数的存储过程sp_count_student，求学生人数4.3.1存储过程和函数的基本操作mysql>delimiter$$mysql>createproceduresp_count_student(outstu_cntintunsigned)->begin->selectcount(*)intostu_cntfromstudent;->end$$mysql>delimiter;任务必备知识【任务4.3.3】创建存储过程sp_search_teacher，以指定的系别号为参数，查找某学院的老师姓名、所在院系名称4.3.1存储过程和函数的基本操作mysql>delimiter$$mysql>createproceduresp_search_teacher(innochar(8))->begin->selectt.t_name,d.d_name->fromteachertjoindepartmentdont.d_no=d.d_no->wheret.d_no=no;->end$$mysql>delimiter;任务必备知识4.3.1存储过程和函数的基本操作二、调用存储过程创建完存储过程之后，调用存储过程需要使用call语句。callsp_name(parameter,...)【任务4.3.4】调用存储过程sp_delete_student,删除学号为122001的学生的信息mysql>callsp_delete_student('122001');任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.5】调用存储过程sp_count_student,统计学生人数mysql>callsp_count_student(@num);mysql>select@num;【任务4.3.6】调用存储过程sp_search_teacher,查询院系编号D001的教师姓名和院系名称mysql>callsp_search_teacher('D001');任务必备知识4.3.1存储过程和函数的基本操作三、查看存储过程showprocedurestatus[like'pattern'|whereexpr]说明：（1）like匹配存储过程的名称。（2）where可以指定更多的过滤条件。1.查看存储过程的状态信息任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.7】查看存储过程sp_search_teacher的状态信息mysql>showprocedurestatuslike'sp_search_teacher'\G***************************1.row***************************Db:jxglName:sp_search_teacherType:PROCEDUREDefiner:root@localhostModified:2023-01-2523:07:38Created:2023-01-2523:07:38Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8mb4_0900_ai_ci任务必备知识4.3.1存储过程和函数的基本操作类似与查看数据库和数据表的定义，存储过程的定义可通过SHOWCREATEPROCEDURE命令查看。2.查看存储过程的定义【任务4.3.8】查看存储过程sp_search_teacher的定义mysql>showcreateproceduresp_search_teacher\G任务必备知识4.3.1存储过程和函数的基本操作四、删除存储过程可以使用dropprocedure删除已经存在的存储过程。dropprocedure[ifexists]sp_name;【任务4.3.9】删除存储过程sp_search_teachermysql>dropproceduresp_search_teacher;任务必备知识4.3.1存储过程和函数的基本操作五、存储函数MySQL存储函数和存储过程类似，也是存储在数据库中的程序，其查看、删除过程和存储过程一样，只不过将procedure换成function即可，主要在于创建和调用过程稍有不同。创建过程：createfunction[ifnotexists]sp_name(param_nametype,...)returnstype[deterministic|nosql|readssqldata]routine_body任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.10】定义存储函数func_count_score,获取score表中分数超过指定成绩的记录数mysql>delimiter$$mysql>createfunctionfunc_count_score(sdecimal)returnsint->deterministic->begin->declares_countint;->selectcount(*)intos_countfromscorewheremark>=s;->returns_count;->end$$mysql>delimiter;任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.11】使用自定义的存储函数func_count_score，获取超过90分的成绩记录数mysql>selectfunc_count_score(90)as‘优秀记录数';任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.12】查看存储函数func_count_score状态信息mysql>showfunctionstatuslike'func_count_score'\G***************************1.row***************************Db:jxglName:func_count_scoreType:FUNCTIONDefiner:root@localhostModified:2023-01-2617:12:49Created:2023-01-2617:12:49Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8mb4_0900_ai_ci1rowinset(0.01sec)或者执行下列语句mysql>showfunctionstatuswherename='func_count_score'\G任务必备知识4.3.1存储过程和函数的基本操作【任务4.3.13】删除存储函数func_count_scoremysql>dropfunctionfunc_count_score;任务必备知识4.3.1存储过程和函数的基本操作存储过程和存储函数除了参数和返回值方式不同之外，最重要的区别在于存储过程可以修改数据库对象的状态，而存储函数却不能。这也意味这存储函数中主要以查询为主，可以放在查询语句中使用，存储过程却不行。任务必备知识一、变量1.变量声明存储过程和函数可以定义和使用变量,它们可以用来存储临时结果。用户可以使用declare关键字来定义变量，这些变量的作用范围只适用于begin…end程序段中，所以是局部变量。过程和函数使用的局部变量必须在开头就声明。在声明局部变量的同时也可以为其赋一个初始值：declarevar_nametype[defaultvalue];4.3.2存储过程和函数的编程功能任务必备知识2.变量赋值给变量赋值有两种方式，一种通过SET语句：setvar1=expr1,var2=expr2,...;另外一种方式通过selectINTO语句完成赋值：selectexpr1,expr2,...intovar1,var2,...from...;4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.14】创建一个存储函数，根据课程号查询不及格学生的数量mysql>delimiter$$mysql>createfunctionfunc_fail_count(nochar(4))returnsint->readssqldata->begin->declares_countintdefault0;->selectcount(*)intos_countfromscorewherec_no=noandmark<60;->returns_count;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.14】创建一个存储函数，根据课程号查询不及格学生的数量调用函数，查询课程号A002不及格人数mysql>selectfunc_fail_count('A002')as'A002不及格人数';+---------------------+4.3.2存储过程和函数的编程功能任务必备知识二、条件控制语句1.If语句MySQL提供了两种条件控制语句：if语句和case语句IF语句可根据不同的条件执行不同的操作。ifsearch_conditionthenstatement_listelseifsearch_conditionthenstatement_list...elsestatement_listendif4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.15】创建一个存储过程，根据指定的参数（学号）查看某位学生的不及格科目数，如果不及格科目数超过2门（含2门），则输出“启动成绩预警！”并输出该生的成绩单，否则输出“成绩在可控范围”4.3.2存储过程和函数的编程功能任务必备知识首先，创建存储过程：mysql>delimiter$$mysql>createprocedureproc_query(innochar(8),outstrvarchar(20))->begin->declarefail_counttinyintunsigneddefault0;->selectcount(*)intofail_countfromscorewheres_no=noandmark<60;->iffail_count>=2then->setstr='启动成绩预警';->else->setstr='成绩在可控范围';->endif;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能任务必备知识调用存储过程，查询学号122001的成绩状况：mysql>callproc_query('122001',@msg);mysql>select@msg;4.3.2存储过程和函数的编程功能任务必备知识1.case语句case语句可以用于构造复杂的条件判断，类似于case表达式，case语句也存在两种形式：简单case语句和搜索case语句4.3.2存储过程和函数的编程功能任务必备知识casecase_valuewhenwhen_valuethenstatement_listwhenwhen_valuethenstatement_list...elsestatement_listendcase;4.3.2存储过程和函数的编程功能（1）简单case语句任务必备知识【任务4.3.16】创建一个存储过程，根据专业类型（专业课4分、专业基础课3分、必须课2分、选修课1分）更新学分，参数为专业类型首先，创建存储过程（不能创建函数，函数中不能更新表格数据）4.3.2存储过程和函数的编程功能任务必备知识mysql>delimiter$$mysql>createprocedureproc_update_credit(intypevarchar(10))->begin->casetype->when'专业课'then->updatecoursesetc_credit=4wherec_type=type;->when'专业基础课'then->updatecoursesetc_credit=3wherec_type=type;->when'必修课'then->updatecoursesetc_credit=2wherec_type=type;->else->updatecoursesetc_credit=1;->endcase;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能任务必备知识casewhensearch_conditionthenstatement_listwhensearch_conditionthenstatement_list...elsestatement_listendcase;4.3.2存储过程和函数的编程功能（2）搜索case语句任务必备知识【任务4.3.17】创建一个存储函数，根据学号输出学生的成绩等级，平均分>=90优秀，>=80良好，>=60及格，否则不及格首先，创建存储函数:4.3.2存储过程和函数的编程功能任务必备知识mysql>delimiter$$mysql>createfunctionfunc_score_level(nochar(8))returnsvarchar(20)->readssqldata->begin->declaremsgvarchar(20);->declareavg_markdecimal(4,1)default0;->->selectavg(mark)intoavg_markfromscorewheres_no=no;->case->whenavg_mark>=90thensetmsg='优秀';->whenavg_mark>=80thensetmsg='良好';->whenavg_mark>=60thensetmsg='及格';->elsesetmsg='不及格';->endcase;->returnmsg;->end$$mysql>delimiter;4.3.2存储过程和函数的编程功能调用存储函数，根据传入的成绩输出等级：mysql>selectfunc_score_level('122003');任务必备知识三、循环控制语句1.loop语句[label:]loopstatement_listendloop[label]4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.18】创建存储过程，使用loop语句求10以内奇数之和创建存储过程:mysql>delimiter$$mysql>createproceduresp_loop_sum()->begin->declareiintdefault0;->declarei_sumintdefault0;->label:loop->seti=i+1;->ifi>10thenleavelabel;4.3.2存储过程和函数的编程功能->endif;->ifi%2=0theniteratelabel;->elseseti_sum=i_sum+i;->endif;->endlooplabel;->selecti_sum;->end$$mysql>delimiter;任务必备知识【任务4.3.18】创建存储过程，使用loop语句求10以内奇数之和调用存储过程：mysql>callsp_loop_sum();4.3.2存储过程和函数的编程功能任务必备知识三、循环控制语句2.while语句[label:]whilesearch_conditiondostatement_listendwhile[label]4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.19】创建存储过程，使用while语句求10以内奇数之和4.3.2存储过程和函数的编程功能创建存储过程：mysql>delimiter$$mysql>createproceduresp_while_sum()->begin->declareiintdefault0;->declarei_sumintdefault0;->->label:whilei<10do->seti=i+1;->ifi%2=1then->seti_sum=i_sum+i;->endif;->endwhilelabel;->->selecti_sum;->end$$mysql>delimiter;任务必备知识调用存储过程：mysql>callsp_while_sum();4.3.2存储过程和函数的编程功能【任务4.3.19】创建存储过程，使用while语句求10以内奇数之和任务必备知识四、repeat语句[label:]repeatstatement_listuntilsearch_conditionendrepeat[label]4.3.2存储过程和函数的编程功能任务必备知识【任务4.3.20】创建存储过程，使用repeat语句求10以内