数据库原理、技术与应用-MySQL(视频教学+题库+AI赋能版) 课件 第5-10章 索引和数据完整性 --使用PHP语言进行Web应用开发

第5章索引和数据完整性5.3实施数据完整性索引概述5.2索引的操作CONTENTS目录

5.15.1索引概述5.1.1索引的概念索引是与表或视图关联的物理结构，可以加快从表或视图中检索行的速度索引包含由表或视图中的一列或多列生成的键，这些键存储在一个B树结构中，使MySQL可以快速有效地查找与键值关联的行索引的优点：（1）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性（2）可以大大加快数据的查询速度，这也是创建索引的主要原因5.1.1索引的概念（续）（3）在实现数据的参照完整性方面，可以加速表和表之间的连接（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间增加索引的不利方面：（1）创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加（2）索引需要占用磁盘空间，每一个索引还要占用一定的物理空间（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护5.1.2索引的分类（1）普通索引（index/key）：索引的列可以包括重复的值（2）唯一索引（unique）：保证了列不包含重复的值，但可以为空（3）主键索引（primarykey）：又称为聚簇索引，使用B-Tree构建（4）全文索引（fulltext）：在定义索引的列上支持值的全文查找（5）空间索引（spatial）：对空间数据类型的字段建立的索引只包含单个列的索引叫做单列索引，也可以组合多个列以创建复合索引5.1.3索引的特点所有的MySQL列类型都能被索引索引是在存储引擎中实现的，每种存储引擎的索引不一定完全相同对于CHAR和VARCHAR列，可以索引列的前缀MySQL能对多个列创建组合索引，一个组合索引可以由最多15个列组成5.1.3索引的特点（续）设计索引的准则：（1）根据需要为表设置索引，避免对经常更新的表进行过多的索引（2）避免对经常更新的表进行过多的索引，应该在经常查询的字段上创建索引（3）不要为包含很少唯一值的列创建索引（4）如果索引包含多个列，应考虑列的顺序（5）当唯一性是某种数据本身的特征时，指定唯一索引（6）在频繁进行排序或分组的列上建立索引5.2索引的操作5.2.1使用Workbench创建索引1．建表时定义PRIMARYKEY或UNIQUE约束，系统自动创建索引2．使用Workbench创建索引步骤：（1）选择要创建索引的表，单击右键选择AlterTable菜单（2）单击Indexes（索引）选项卡（3）输入索引名称，选择索引类型（PRIMARY/UNIQUE/普通）（4）选择包含在索引中的列（5）单击Apply按钮创建索引5.2.2使用SQL语句创建索引1．在创建表的同时创建索引：CREATETABLEtable_name(col_namedata_type,[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]);5.2.2使用SQL语句创建索引（续）例5-1创建表student_3，在name列上设置唯一索引：CREATETABLEstudent_3(nameVARCHAR(20)NOTNULL,briefTEXTNOTNULL,UNIQUEINDEXname_UNIQUE(nameDESC));5.2.2使用SQL语句创建索引（续）2．使用ALTERTABLE语句为已有表创建新的索引：ALTERTABLEtable_nameADDINDEXindex_name(column_list);ALTERTABLEtable_nameADDUNIQUEindex_name(column_list);ALTERTABLEtable_nameADDPRIMARYKEY(column_list);5.2.2使用SQL语句创建索引（续）例5-2在学生表的姓名列上建立索引：ALTERTABLEstudentADDINDEX(name);例5-3在课程表的代码列和名称列上建立唯一索引：ALTERTABLEcourseADDUNIQUE(id,name);5.2.3查看索引效果使用EXPLAIN语句来分析SQL查询的执行计划可以查看该SQL查询有没有使用索引，有没有做全表扫描EXPLAIN输出字段说明：（1）select_type：所使用的SELECT查询类型（2）table：读取的数据表的名字（3）type：本数据表与其他数据表之间的关联关系5.2.3查看索引效果（续）（4）possible_keys：给出了MySQL在搜索数据记录时可选用的各个索引（5）key：是MySQL实际选用的索引（6）key_len：索引按字节计算的长度（7）ref：关联关系中另一个数据表里的数据列名（8）rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数（9）Extra：提供了与关联操作有关的信息5.2.4复合索引复合索引是指对表的多个列创建索引以下语句在student表的姓名、性别、民族列上创建了复合索引：ALTERTABLEstudentADDINDEX(name,gender,national);只有在查询条件中使用了这些索引字段的左边字段时，复合索引才会被使用，使用复合索引时遵循最左前缀集合在本例中，当查询条件中包含(name)、(name,gender)或(name,gender,national)时，复合索引才会被使用5.2.5查看与修改索引使用SQL语句查看索引：SHOWINDEXFROMtable_name;例5-4查看课程表的索引：SHOWINDEXFROMcourse;输出字段说明：Table：表名Non_unique：是否非唯一（0=唯一，1=可重复）Key_name：索引名称Column_name：列名5.2.6删除索引删除索引的SQL语句：ALTERTABLEtable_nameDROPINDEXindex_name;ALTERTABLEtable_nameDROPPRIMARYKEY;DROPINDEXindex_nameONtable_name;注意：删除PRIMARYKEY索引时，不需要索引名，因为一个表只有一个PRIMARYKEY5.2.6删除索引（续）例5-5删除学生表内名为id的索引：ALTERTABLEstudentDROPINDEXid;例5-6删除课程表的主键约束索引：ALTERTABLEcourseDROPPRIMARYKEY;5.3实施数据完整性5.3.1数据完整性的概念数据完整性是指数据的正确性、有效性和一致性具体来讲，完整性包括：（1）实体完整性：保证每行数据是唯一的可识别的（2）域完整性：保证列的数据符合要求的取值范围（3）参照完整性：保证表之间的引用关系正确（4）用户自定义完整性：根据具体业务逻辑的数据规则设置5.3.2主键约束（PRIMARYKEY）主键约束用于唯一标识表中的每一行一个表只能包含一个PRIMARYKEY约束在PRIMARYKEY约束中定义的所有列都必须定义为NOTNULL定义列级主键约束：[CONSTRAINTconstraint_name]PRIMARYKEY定义表级主键约束：[CONSTRAINTconstraint_name]PRIMARYKEY(column_name[,...])5.3.2主键约束（续）例5-8为选课表增加主键约束，设置学号和课程编号的组合为主键：ALTERTABLEstudent_courseADDCONSTRAINTPK_st_id_course_idPRIMARYKEY(student_id,course_id);注意：（1）一个表只能包含一个PRIMARYKEY约束（2）在PRIMARYKEY约束中定义的所有列都必须定义为NOTNULL5.3.3外键约束（FOREIGNKEY）外键约束定义了表与表之间的关系，用于建立和加强两个表的数据之间的连接定义表级外键约束的语法：[CONSTRAINTconstraint_name]FOREIGNKEY(column_name[,...])REFERENCESref_table[(ref_column[,...])][ONDELETE{CASCADE|NOACTION}]5.3.3外键约束（FOREIGNKEY）（续）[ONUPDATE{CASCADE|NOACTION}]CASCADE：级联操作，当主键表中某行被删除或更新时，外键表中相应数据也做相同的删除或更新操作5.3.3外键约束（续）例5-9为选课表增加两个外键约束：ALTERTABLEstudent_courseADDCONSTRAINTFK_st_idFOREIGNKEY(student_id)REFERENCESstudent(id);ALTERTABLEstudent_courseADDCONSTRAINTFK_course_idFOREIGNKEY(course_id)REFERENCEScourse(id);5.3.3外键约束（续）

注意：（1）FOREIGNKEY约束只能引用所引用的表的PRIMARYKEY或UNIQUE约束中的列（2）FOREIGNKEY约束仅能引用位于同一服务器上的同一数据库中的表5.3.4非空值约束（NOTNULL）非空值约束限制的数据列不能为空当表数据发生变化时，对于有非空值约束的字段必须给出确定的值使用SQL语句设置NOTNULL约束：ALTERTABLE表名MODIFYCOLUMN列名数据类型NOTNULL;5.3.4非空值约束（NOTNULL）（续）例5-10为课程表的课程名称字段设置非空值约束：ALTERTABLEcourseMODIFYCOLUMNnameVARCHAR(20)NOTNULL;注意：NULL不是零或空白，NULL表示该列的值未知或没有值5.3.5唯一性约束（UNIQUE）唯一性约束限制约束的列在表的范围内不允许有两行包含相同的非空值唯一性约束的特点：（1）唯一性约束用于限定非主键的一列或多列组合的取值不重复（2）一个表可以定义多个唯一性约束，但只能定义一个主键约束（3）主键约束不能用于定义允许空值的列（4）每个UNIQUE约束都生成一个索引5.3.5唯一性约束（UNIQUE）（续）例5-11创建表student_2，要求学生身份证号st_identity具有唯一性：CREATETABLEstudent_2(st_idCHAR(8),st_identityCHAR(18),CONSTRAINTuk_st_identityUNIQUE(st_identity));5.3.6检查约束（CHECK）检查约束对输入列或整个表中的值设置检查条件，通常是一个取值范围以限制输入值定义检查约束的语法：[CONSTRAINTconstraint_name]CHECK(logical_expression)5.3.6检查约束（CHECK）（续）

例5-12为选课表的成绩列添加检查约束：ALTERTABLEstudent_courseADDCONSTRAINTmark_checkCHECK(mark>=0ANDmark<=100);注意：（1）对每列可以指定多个CHECK约束（2）列级CHECK约束只能引用被约束的列5.3.7默认值约束（DEFAULT）默认约束通过定义列的默认值，确保在没有为某列指定数据时由MySQL来指定列的值定义默认约束的语法：[CONSTRAINTconstraint_name]DEFAULTconstant_expression5.3.7默认值约束（DEFAULT）（续）注意：（1）每列中只能有一个默认约束（2）默认约束只能用于INSERT语句（3）约束表达式不能应用于数据类型为timestamp的列和具有AUTO_INCREMENT属性的列例5-13修改学生表，设置is_bonus字段默认约束为0：ALTERTABLEstudentMODIFYCOLUMNis_bonusintDEFAULT0;本章小结索引是与表或视图关联的物理结构，包含由一列或多列生成的键，存储在B树结构中，可大大加快数据库的检索速度。索引分类：普通索引、唯一索引、主键索引、全文索引和空间索引。可在创建表时同时创建，也可通过CREATEINDEX语句单独创建；通过DROPINDEX或ALTERTABLE...DROPINDEX删除索引。数据完整性分为四种类型：实体完整性、域完整性、参照完整性和用户自定义完整性。实体完整性通过主键约束（PRIMARYKEY）、唯一性约束（UNIQUE）、非空约束（NOTNULL）和自增属性（AUTO_INCREMENT）实现，保证每行记录的唯一性。域完整性通过FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL约束实现，限制字段的值域。第6章视图6.4视图的应用6.3视图的管理视图介绍6.2创建视图CONTENTS目录

6.16.1视图介绍6.1视图介绍视图是关系数据库中提供给用户以多种角度观察数据库中数据的重要机制视图的定义包含一系列带有名称的列和数据行，但不存储任何物理数据数据库中只存放视图的定义，数据来自于定义视图查询所引用的基本表可以基于一个或多个基本表创建视图，也可以基于其他视图来创建视图视图的优点1.简化用户操作使用户将注意力集中在所关心的数据上，简化数据操作2.保障数据安全用户只能查询和修改他们所能见到的数据可限制用户对基表的行子集、列子集或多表连接数据的访问3.保持数据逻辑上的独立性视图的优点（续）逻辑独立性：数据库重构时（如增加新关系或增加新字段），应用程序不会受影响视图作为应用程序和数据表之间的中介，可使应用程序和数据表相互独立对视图的操作与对表的操作一样，可以进行查询、修改和删除，修改视图中的数据时，相应基础表的数据也会发生变化6.2创建视图CREATEVIEW语法CREATE[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW视图名[(属性清单)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];ALGORITHM：UNDEFINED（自动选择）/MERGE（合并视图与语句）/TEMPTABLE（结果存入临时表）CREATEVIEW语法（续）WITHCHECKOPTION：更新视图时保证在视图权限范围之内CASCADED（默认）：更新视图时满足所有相关视图和表的条件LOCAL：更新视图时满足该视图本身定义的条件即可例6-1创建score_view视图在教学管理数据库中创建score_view视图，选择学生表和成绩表中的数据显示学生选修课程的成绩情况：CREATEVIEWscore_viewASSELECTstudent.id,,student_course.course_id,student_course.markFROMstudentINNERJOINstudent_courseONstudent.id=student_course.student_id;查询视图数据执行SQL语句查询视图数据：SELECT*FROMscore_view;视图建立后，可以像查询普通表一样查询视图视图数据实际来自基础表，视图本身不存储数据6.3视图的管理6.3.1查看视图可以使用SQL语句查看视图，语法格式如下：SHOWCREATEVIEWview_name例6-2查看视图score_view：SHOWCREATEVIEWscore_view执行后显示创建视图的SQL语句可右键选择"OpenvalueinViewer"查看完整内容6.3.2修改视图——CREATEORREPLACEVIEW修改视图是指修改数据库中已存在视图的定义语法格式：CREATEORREPLACE[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW视图[(属性清单)]ASSELECT语句[WITH[CASCADED|LOCAL]CHECKOPTION];6.3.2修改视图——ALTERVIEWALTERVIEW语句改变视图的定义，但不影响所依赖的存储过程或触发器语法格式：ALTERVIEW[ALGORITHM={MERGE|TEMPTABLE|UNDEFINED}]view_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION];例6-3修改score_view视图修改score_view视图，显示学生学号、姓名、课程编号、课程名称、学分和成绩：ALTERVIEWscore_viewASSELECTstudent.idstudent_id,student_name,course.idcourse_id,course_name,course.markcourse_mark,student_course.markstudent_markFROMstudentINNERJOINstudent_courseONstudent.id=student_course.student_id

INNERJOINcourseONcourse.id=student_course.course_id;以上SQL修改了视图的列结构，增加了课程名称和学分字段6.3.3删除视图1.使用Workbench删除视图展开数据库视图列表，右键单击对应视图，选择"DropView"菜单2.使用SQL语句删除视图DROPVIEW{view_name}[,…]view_name是要删除的视图名称，可同时删除多个视图例6-4：DROPVIEWscore_view;6.4视图的应用视图应用——检索数据视图建立后，可以用任一种查询方式检索数据，可使用连接、GROUPBY子句、子查询等及其任意组合例6-5查询score_view中'李思思'同学所选课程名称和成绩：SELECTcourse_name,student_markFROMscore_viewWHEREstudent_name='李思思';视图应用——添加数据可以通过视图向基础表插入数据：INSERTINTO视图名VALUES(列值1，列值2，…，列值n)例6-6基于学生表建立视图，利用视图插入数据：CREATEVIEWstudent_viewASSELECTid,name,mark,majorFROMstudent;INSERTINTOstudent_viewVALUES('S0609','何雅静',576,'会计学');通过视图添加数据注意事项（1）插入的列值个数、数据类型应与视图定义保持一致（2）若视图只选择了部分列，基础表其余不允许为空且无默认值的列将导致插入失败（3）使用WITHCHECKOPTION时，插入数据必须符合视图SELECT中设定的条件（4）视图列使用了数学表达式或聚合函数，无法对视图插入数据（5）SELECT语句使用了DISTINCT、UNION、GROUPBY或HAVING，无法插入数据视图应用——修改数据通过视图用UPDATE语句更改基础表的数据：UPDATE视图名SET列1=值1,列2=值2,…WHERE逻辑表达式例6-7利用student_view修改学号S0609同学的入学成绩为589：UPDATEstudent_viewSETmark=589WHEREid='S0609'注意：若视图包含多个基础表，每次更新只能影响一个基本表使用WITHCHECKOPTION，修改后数据必须满足视图定义范围视图应用——删除数据通过视图删除基础表数据行：DELETEFROM视图名WHERE逻辑表达式;例6-8利用student_view删除学号为S0609同学的记录：DELETEFROMstudent_viewWHEREid='S0609'注意（1）：视图引用多个表时，无法用DELETE命令删除多个表的数据注意（2）：删除条件中指定的列必须是视图定义包含的列使用视图的注意事项（1）只能在当前数据库中创建视图（2）可以基于数据表创建视图，也可以基于其他视图建立视图（3）视图的建立和删除不影响基本表（4）利用视图更新（添加、修改、删除）数据直接影响基本表（5）若视图引用多个表，只有影响其中一个基本表时，才可执行UPDATE、DELETE或INSERT语句更新视图本章小结视图是关系数据库中提供给用户以多种角度观察数据库中数据的重要机制，是数据库三级模式结构中的外模式。数据库中只存放视图的定义，不存储任何实际数据，视图是一个虚拟表。创建视图使用CREATEVIEW语句，可指定ALGORITHM（MERGE/TEMPTABLE/UNDEFINED）和WITHCHECKOPTION参数。修改视图可使用CREATEORREPLACEVIEW或ALTERVIEW语句。删除视图使用DROPVIEW语句。视图的应用：通过视图可以检索数据、添加数据、修改数据和删除数据，这些操作会直接影响基础表中的数据。若视图引用多个表，每次更新只能影响其中一个基本表。使用视图的注意事项：只能在当前数据库中创建视图；视图的建立和删除不影响基本表；利用视图更新数据会直接影响基本表。第7章SQL程序设计7.4程序流程控制语句7.3游标应用运算符与表达式7.2变量应用7.5函数CONTENTS目录

7.1触发器

7.6存储过程

7.7事件

7.87.1运算符与表达式7.1运算符与表达式SQL运算符共有5类：算术运算符、位运算符、逻辑运算符、比较运算符、连接运算符1.算术运算符：+（加）、-（减）、*（乘）、/（除）、%（取模），优先级从高到低：*、/、%→+、-2.位运算符：&（与）、|（或）、^（异或）、~（取反）例7-2：171&73=9；15|12=15；15^12=3；~1=-23.比较运算符：>、<、>=、<=、=、<>（不等于）运算符与表达式（续）4.逻辑运算符：AND（与）、OR（或）、NOT（非）AND：两个操作数都为TRUE结果才为TRUEOR：两个操作数都为FALSE结果才为FALSENOT：单目运算，对操作数取反5.字符串连接：使用CONCAT函数例：CONCAT('MySQL','数据库管理系统')='MySQL数据库管理系统'运算符优先级优先级由高到低：①括号（）②正、负、取反：+、-、~③乘除求模：*、/、%④加减：+、-⑤比较运算符：=、>、<、>=、<=、<>⑥逻辑：NOT→AND→OR（优先级依次降低）例7-1算术运算符应用使用'+'将课程表中低于2的课程学分增加1：UPDATEcourseSETmark=mark+1WHEREmark<2;7.2变量应用7.2.1局部变量局部变量：作用范围在BEGIN…END之间定义语法：DECLAREvar_namedata_type[DEFAULTvalue];赋值语法：SETvar_name=value;DECLARExintDEFAULT0;SETx=100;从表查询赋值：SELECTAVG(score)INTOavg_scoreFROMstudent_course;7.2.2会话变量与全局变量会话变量：仅在当前连接有效，连接关闭时消失SET@session_variable_name=value;例：SET@my_session_var='Hello,MySQL!';全局变量：对所有连接有效SETGLOBALmax_connections=300;（重启后失效）SETPERSISTmax_connections=300;（永久有效，MySQL8.0新增）7.3游标应用7.3游标应用游标用于保存一次查询返回的多条记录，逐条进行处理分析游标使用流程：声明→打开→读取→关闭1.声明游标：DECLAREcursor_nameCURSORFORselect_statement;2.打开游标：OPENcursor_name;3.读取数据：FETCHcursor_nameINTOvar_1,var_2…4.关闭游标：CLOSEcursor_name;游标应用示例例7-3定义游标，返回student表中女生的id、name、gender：DECLAREcursor_studentCURSORFORSELECTid,name,genderFROMstudentWHEREgender='女';例7-4读取游标数据：FETCHcursor_studentINTOid,name,gender;注：游标只能在函数和存储过程中使用7.4程序流程控制语句7.4.1条件执行语句IF…ELSEIFboolean_expressionTHEN{sql_statement|statement_block}--条件为真时执行[ELSE{sql_statement|statement_block}]--条件为假时执行ENDIF;IF…ELSE语句可以嵌套使用例7-5IF条件执行示例判断选课表中是否存在平均成绩高于85分的课程：DELIMITER//CREATEPROCEDUREexample_if()BEGINIFEXISTS(SELECTcourse_idFROMstudent_courseGROUPBYcourse_idHAVINGAVG(mark)>85)例7-5IF条件执行示例（续）THENSELECT'有课程平均成绩高于85分';ELSESELECT'没有课程平均成绩高于85分';ENDIF;END//调用：CALLexample_if();注：DELIMITER//将结束符从分号改为//，避免与存储过程内部分号冲突7.4.1CASE函数CASEinput_expressionWHENwhen_expressionTHENresult_expression[…n][ELSEelse_result_expression]ENDCASE;执行过程：计算CASE后表达式的值，依次与WHEN中的值比较，匹配则返回THEN后的值例7-6CASE函数示例根据输入值决定输出内容：DELIMITER//CREATEPROCEDUREexample_case(INxint)BEGINCASExWHEN1THENSELECT1;WHEN2THENSELECT2;ELSESELECT3;RETURN语句RETURN语句用于结束程序执行，并返回值给调用程序语法：RETURN[expression]注意：RETURN只能用于函数中在存储过程、触发器和事件中，使用LEAVE语句例7-7RETURN语句示例：返回变量x和y中的最大值7.4.2WHILE循环WHILEcondition_expressionDO…ENDWHILE;当condition_expression为TRUE时，执行循环体；为FALSE时退出例7-8用WHILE计算1+3+5+7+…，直到和≥100：结果sum=100（可使用LEAVE语句跳出循环）例7-9嵌套WHILE循环计算sum=1!+2!+…+10!DELIMITER//CREATEFUNCTIONexample_fac()RETURNSintBEGINDECLAREsum,a,b,facint;SETsum=0;SETa=1;WHILEa<=10DO--外层：累加各阶乘例7-9嵌套WHILE循环（续）SETfac=1;SETb=1;WHILEb<=aDO--内层：计算a的阶乘SETfac=fac*b;SETb=b+1;ENDWHILE;SETsum=sum+fac;SETa=a+1;ENDWHILE;RETURNsum;END//REPEAT循环与LOOP循环REPEAT循环（先执行后判断）：REPEAT…UNTILconditionENDREPEAT例7-10：sum=1+2+…+100，结果5050LOOP循环（无条件，用LEAVE退出）：LOOP…ENDLOOP;例7-11：计算100以内奇数和，结果2500ITERATE：跳转到循环开始（与LEAVE的区别）7.5函数7.5.1数学函数常用数学函数：ABS(x)：绝对值CEIL(x)：向上取整FLOOR(x)：向下取整ROUND(x,y)：四舍五入到y位（y为负数时在小数点左边取整）SQRT(x)：平方根RAND()：0~1的随机数MOD(x,y)：取模例7-12：SELECTround(62.45613,3)→62.456例7-13：SELECTsqrt(121)→117.5.1字符串函数常用字符串函数：LENGTH(s)：字符串长度SUBSTRING(s,start,len)：取子串UPPER(s)/LOWER(s)：大小写转换TRIM(s)：去首尾空格CONCAT(s1,s2)：连接字符串CHAR(n)：返回ASCII码对应字符例7-14：SELECTsubstring('MySQL',3,3)→'SQL'；SELECTchar(65)→'A'7.5.1日期函数与系统函数常用日期函数：CURDATE()：当前日期NOW()：当前日期时间DAYNAME(d)：星期几ADDDATE(d,n)：加n天ADDTIME(t,n)：加时间例7-15：SELECTcurdate()→2024-12-26例7-17：SELECTadddate('2023-03-01',60)→2023-04-307.5.1加密函数与系统信息函数系统信息函数：DATABASE()：当前数据库名SYSTEM_USER()：当前连接用户VERSION()：MySQL版本加密函数：SHA1(str)：SHA1加密MD5(str)：MD5加密例7-21：SELECTsha1('p123456'),md5('p123456');7.5.2用户自定义函数——创建CREATEFUNCTIONname([func_parameter[,...]])RETURNStype[CHARACTERISTIC...]routine_body在MySQL8.0中需先设置：SETGLOBALlog_bin_trust_function_creators=TRUE;例7-22创建函数course_avg，根据课程代号返回该课程的平均成绩：CREATEFUNCTIONcourse_avg(course_idvarchar(10))RETURNSfloat...7.5.2用户自定义函数——调用与管理调用：SELECTfunction_name([parameter[,…]]);例7-23：SELECTcourse_avg('c001');→83.3091查看：SHOWCREATE{PROCEDURE|FUNCTION}name;修改：ALTERFUNCTIONname[CHARACTERISTIC...];删除：DROPFUNCTION[IFEXISTS]name;Workbench：右键Functions→Create/DropFunction7.6触发器7.6触发器概述触发器由INSERT、UPDATE、DELETE命令事件触发特定操作满足触发条件时，数据库系统自动执行触发器触发器的主要作用：（1）强化约束：比CHECK语句更复杂的约束（2）跟踪变化：侦测操作，禁止未经许可的更新（3）级联运行：自动影响其他表的数据7.6.1创建触发器CREATETRIGGER触发器名称BEFORE|AFTER触发事件ON表名FOREACHROWBEGIN执行语句列表ENDBEFORE/AFTER：触发时机（事件前/后）；FOREACHROW：每行触发7.6.2查看与删除触发器查看触发器：SHOWTRIGGERS;SHOWCREATETRIGGERtrigger_name;删除触发器：DROPTRIGGER[IFEXISTS]trigger_name;NEW和OLD：触发器的两个临时表，分别表示新数据和旧数据7.7存储过程7.7存储过程概述存储过程是包含程序流、逻辑及对数据库查询的一组SQL语句经过预编译后存储在数据库内，通过调用在服务器上执行存储过程的优点：（1）减少网络流量：减少客户端与服务器之间的数据传输（2）执行速度快：预编译，减少执行时的分析工作（3）安全性高：通过授权控制对存储过程的访问7.7.1创建存储过程CREATEPROCEDUREsp_name([parameter[,...]])[characteristics...]routine_body参数类型：IN（输入）、OUT（输出）、INOUT（输入输出）例7-30创建存储过程：CREATEPROCEDUREget_student_info(INsidchar(10))BEGINSELECT*FROMstudentWHEREid=sid;END//7.7.2调用与管理存储过程调用：CALLsp_name([parameter[,…]]);查看：SHOWCREATEPROCEDUREsp_name;修改：ALTERPROCEDUREsp_name[CHARACTERISTIC...];删除：DROPPROCEDURE[IFEXISTS]sp_name;存储过程与函数的区别：函数有RETURN返回值，存储过程用OUT参数输出结果7.8事件7.8事件概述事件（EventScheduler）：在指定时间执行SQL语句取代操作系统计划任务，在数据库内部实现定时任务事件与触发器的区别：触发器由数据操作事件触发（INSERT/UPDATE/DELETE）事件由时间调度触发（定时执行）7.8.2创建事件CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE]DOevent_body;例7-35每隔1分钟向student_count表中插入一条数据：CREATEEVENTe_student_countONSCHEDULEEVERY1MINUTE...7.8.3修改与删除事件修改事件：ALTEREVENTevent_name[ONSCHEDULE...][DOevent_body];临时关闭：ALTEREVENTe_student_countDISABLE;重新开启：ALTEREVENTe_student_countENABLE;删除事件：DROPEVENT[IFEXISTS]event_name;例7-37：DROPEVENTIFEXISTSe_student_count;本章小结MySQL程序扩展：在标准SQL基础上增加了复杂的表达式、变量和程序流程控制功能，支持结构化编程。变量：分为局部变量（作用范围在BEGIN...END之间，用DECLARE声明）、会话变量（当前连接有效，以@开头）和全局变量（整个服务器周期有效，以@@开头）。游标：用于保存一次查询的多条记录，可逐条处理。使用流程为：声明（DECLARECURSOR）→打开（OPEN）→使用（FETCH）→关闭（CLOSE）。游标只能在函数和存储过程中使用。流程控制：IF...ELSE用于条件判断；CASE用于多分支处理；WHILE、REPEAT、LOOP用于循环控制；LEAVE用于退出循环；ITERATE用于重新开始循环。函数：分为内置函数（系统提供）和用户定义函数（UDF，用CREATEFUNCTION创建）。函数必须有返回值，且只能在RETURN语句中返回值。触发器：由INSERT、UPDATE、DELETE事件触发，自动执行预设的程序语句。每个触发器自动创建NEW和OLD两个临时表，分别保存触发后的新值和触发前的旧值。存储过程：包含程序流、逻辑及对数据库查询的一组SQL语句，经预编译后存储在数据库内。通过CALL语句调用，可接受输入/输出参数，具有强大数据处理能力。事件（事件调度器）：用于在指定时间自动执行某些语句，取代操作系统计划任务。通过CREATEEVENT创建，由事件调度器线程自动管理执行。第8章数据备份与恢复8.4数据复制8.3数据库迁移数据备份8.2数据恢复8.5二进制日志文件CONTENTS目录

8.18.1数据备份8.1.1使用Workbench工具备份步骤：主窗口→Server→DataExport选择要备份的数据库和表，选择保存目录，单击StartExport导出对象（ObjectstoExport）：DumpStoredProceduresandFunctions：导出存储过程和函数DumpEvents：导出事件DumpTriggers：导出触发器导出选项：导出到项目目录/导出到单独文件8.1.2使用命令行程序备份——mysqldumpmysqldump命令将数据库数据备份成文本文件（.sql）1.备份单个数据库：mysqldump-uusername-pdbname[table1table2]>BackupName.sql例8-1：mysqldump-uroot-pstudentcourse>D:\student.sql2.备份多个数据库：mysqldump-uusername-p--databasesdbname1dbname2>BackupName.sqlmysqldump备份（续）例8-2：mysqldump-uroot-p--databasesstudentmysql>D:\data.sql3.备份所有数据库：mysqldump-uusername-p--all--databases>BackupName.sql例8-3：mysqldump-uroot-p--all--databases>D:\data.sql备份文件内容：CREATETABLE语句+INSERT语句文件开头注释中记录了MySQL版本、主机名和数据库名8.1.3直接复制文件备份最简单的备份方法：直接复制MySQL数据库文件特点：速度快，操作简单注意事项：操作前最好先停止MySQL服务，保证数据文件的完整性不适用于InnoDB存储引擎的表适用于MyISAM等存储引擎8.2数据恢复8.2.1使用Workbench工具恢复步骤：主窗口→Server→DataImport选择导入文件和准备导入的数据库单击StartImport按钮，导入数据8.2.2使用MySQL命令恢复使用mysql命令来恢复mysqldump备份的数据：mysql-uroot-p[dbname]<backup.sql指定dbname：恢复该数据库下的表不指定dbname：如数据库不存在则会报错例8-4：mysql-uroot-p<D:\all.sql输入密码后完成数据恢复8.3数据库迁移8.3.1相同类型MySQL数据库迁移先备份再恢复：mysqldump-hhost1-uroot-password=password1--all-databases>data.sqlmysql-hhost2-uroot-password=password2<data.sql可直接实现MySQL数据库间的迁移8.3.2不同数据库间迁移——导出文本文件不同类型数据库间迁移采用文本文件中转的方式1.用SELECT...INTOOUTFILE导出文本文件：SELECT[列名]FROMtable[WHERE条件]INTOOUTFILE'目标文件'[option];常用选项：FIELDSTERMINATEDBY（字段分隔符）FIELDSENCLOSEDBY（字段引用符）SELECTINTOOUTFILE示例例8-5导出student数据库course表，字段逗号分隔，字符型数据加双引号：SELECT*FROMstudent.courseINTOOUTFILE'd:\course.txt'FIELDSTERMINATEDBY'\,'OPTIONALLYENCLOSEDBY'\"'LINESTERMINATEDBY'\r\n';mysqldump导出文本&LOADDATA导入2.用mysqldump导出文本文件：mysqldump-uroot-pPassword-T目标目录dbnametable[option];加-xml导出XML格式；加-html导出HTML格式3.用LOADDATA导入文本文件：LOADDATALOCALINFILE'data.txt'INTOTABLEtable_name;例8-7：LOADDATALOCALINFILE'd:\course.txt'INTOTABLEcourse;8.4数据复制8.4.1MySQL数据复制介绍数据复制：主服务器（Master）→从服务器（Slave）自动同步数据复制的功能：（1）水平扩展：负载分散到多个从机，读写分离（2）高可用性：主机出错时迁移到从机（3）数据备份：从机执行备份，避免主机性能下降（4）在线分析：从机提供查询分析服务MySQL数据复制原理基础：二进制日志文件（BinaryLog）主服务器启用二进制日志后，所有操作以事件方式记录从服务器读取主服务器的二进制日志并在本地回放通过这种方式实现主从数据同步8.4.2主从复制配置（1）1.安装从数据库服务器（版本与主服务器相同）2.修改主服务器my.ini配置：[mysqld]log-bin='mysql-bin'（必须）启用二进制日志server-id=1（必须）服务器唯一ID3.修改从服务器配置：server-id=28.4.2主从复制配置（2）4.重启主从服务器上的MySQL服务5.在主服务器上建立账号sync并授权：CREATEUSER'sync'@'%'IDENTIFIEDWITHmysql_native_passwordBY'p01';GRANTREPLICATIONSLAVEON*.*TOsync;6.查看主服务器状态：SHOWMASTERSTATUS;7.配置并启动从服务器：CHANGEREPLICATIONSOURCETO...;STARTREPLICA;8.4.2主从复制配置（3）8.查看从服务器状态：SHOWSLAVESTATUS;Slave_IO和Slave_SQL进程必须都为YES状态9.复制效果测试：在主服务器插入记录，在从服务器查询确认数据已同步8.5二进制日志文件8.5.1开启日志文件在my.ini的[mysqld]下添加配置：log-bin='mysql-bin'--日志文件前缀名max_binlog_size=20M--单个日志文件大小（默认1G）binlog-do-db=student--只记录student库binlog-ignore-db=student--除student库不记录MySQL默认关闭二进制日志（开启会带来约1%的性能损耗）8.5.2管理binlog1.RESETMASTER：复位日志，删除所有日志，从0001重新开始2.PURGEMASTERLOGSTO'mysql-bin.000005';--删除000005前的日志PURGEMASTERLOGSBEFORE'2023-03-2311:00:00';--删除指定时间前的日志3.设置过期天数（my.ini）：EXPIRE_LOGS_DAYS=3--日志保存3天后自动删除使用日志恢复数据完整恢复：mysqlbinlogbin.000001|mysql-uroot-p基于时间点恢复（跳过误操作时段）：mysqlbinlog--stop-datetime='2024-02-2109:59:59'mysql-bin.000001|mysql...mysqlbinlog--start-datetime='2024-02-2110:02:01'mysql-bin.000001|mysql...基于位置恢复（精确控制起止位置）：mysqlbinlog--stop-position=462mysql-bin.000002|mysql...增量备份与恢复1.完整备份（每周日23:00）：MYSQLDUMP--SINGLE-TRANSACTION--FLUSH-LOGS--all-databases>full.sql2.增量备份（每天23:00）：mysqladminflush-logs（生成新的binlog文件，备份旧文件）3.恢复流程：先恢复完整备份，再依次导入增量binlog文件mysqlbinlogbin-log.000002bin-log.000003|mysql-uroot-p本章小结数据备份：可使用Workbench的"DataExport"功能，或使用mysqldump命令将数据库导出为SQL脚本文件或分隔文本文件。直接复制数据库文件也是一种备份方式。数据恢复：可使用Workbench的"DataImport"功能，或使用mysql命令执行SQL脚本恢复数据。恢复前需确保目标数据库已存在（或备份文件包含CREATEDATABASE语句）。数据库迁移：同类型数据库之间（如MySQL→MySQL）采用先备份再恢复的方法；不同类型数据库之间采用先导出为文本格式文件（如CSV、TXT）再导入的方法，也可借助第三方工具（如Navicat的"数据传输"）。数据复制：是MySQL实现性能扩展的重要方法。配置一台主服务器（Master）和一台或多台从服务器（Slave），系统自动将主服务器的数据同步到从服务器。配置步骤包括：开启二进制日志、创建同步账号、获取主服务器二进制日志坐标、在从服务器上配置并启动复制。二进制日志：包含了所有更新了数据或潜在更新了数据的所有语句。开启二进制日志后，可进行增量备份与恢复，在数据库发生故障时能够最大限度地恢复数据。通过SHOWBINARYLOGS管理日志，通过mysqlbinlog工具将日志恢复到数据库中。第9章数据库安全管理账号管理9.2权限管理CONTENTS目录

9.19.1账号管理9.1.1创建账号MySQL用户分根用户（root，超级管理员）和普通用户CREATEUSER用于创建MySQL账号，需拥有全局CREATEUSER权限格式：CREATEUSER用户名[IDENTIFIEDBY'口令'];示例：CREATEUSERuser01IDENTIFIEDBY'p123456';--带密码创建注意：CREATEUSER创建的用户默认没有任何权限9.1.2删除账号/9.1.3修改账号删除账号：DROPUSER用户名;将从权限表删除用户所有信息示例：DROPUSERuser01;--删除用户user01修改账号：RENAMEUSER旧名TO新名;示例：RENAMEUSERuser01TOuser02;注意：旧账户不存在或新账户已存在时会出错9.1.4更改用户的口令创建后可随时修改口令：SETPASSWORDFOR用户名='新口令'示例：SETPASSWORDFORuser01='p123456';修改当前登录用户口令：SETPASSWORD='p123456';口令加密存储，保证安全性用户若丢失密码，需管理员使用SETPASSWORD重置9.2权限管理9.2权限管理概述权限管理安全原则：使用GRANT和REVOKE命令管理访问权限（1）最小权限原则：只授予能满足需要的最小权限（2）限定主机原则：限制用户登录主机（指定IP地址或IP段）（3）密码复杂度原则：设置满足复杂度要求的密码（4）定期清理原则：及时回收权限或删除不需要的用户9.2.1权限的层级（1）全局层级：适用于所有数据库，存储在mysql.user表（2）数据库层级：适用于指定数据库的所有对象，存于mysql.db和mysql.host（3）表层级：适用于指定表的所有列，存储在mysql.tables_priv表（4）列层级：适用于指定表的单一列，存储在mysql.columns_priv表（5）子程序层级：适用于存储过程和函数，可授予全局或数据库层级权限变更后需执行FLUSHPRIVILEGES重载授权表使之生效9.2.2权限管理——GRANT与REVOKE例9-1授予student库所有表SELECT权限：GRANTSELECTONstudent.*TOuser01;例9-2查询user01用户的权限：SHOWGRANTSFORuser01;例9-3撤消SELECT权限：REVOKESELECTONstudent.*FROMuser01;例9-4一次性授予多个权限：GRANTSELECT,INSERT,UPDATEONstudent.*TOuser01;9.2.2权限管理（续）例9-5所有权限+授权权限+限定主机：GRANTALLPRIVILEGESON*.*TOuser01@''WITHGRANTOPTION;总结：CREATEUSER创建账号→GRANT授予权限→用户可操作数据库REVOKE撤消权限→DROPUSER删除账号→账号失效9.2.2权限管理（续）权限分5个层级：全局/数据库/表/列/子程序FLUSHPRIVILEGES使权限变更立即生效本章小结账号管理：CREATEUSER：创建用户账号，可指定密码；DROPUSER：删除用户账号；RENAMEUSER：修改用户账号名；SETPASSWORDFOR：更改用户口令。权限层级：权限分为五个层级——全局层级（影响整个MySQL服务器）、数据库层级（影响特定数据库中的所有对象）、表层级（影响特定表的所有列）、列层级（影响特定表的特定列）、子程序层级（影响存储过程和函数）。权限管理原则：最小权限原则（只授予所需最小权限）、限定主机原则（限制登录的IP地址）、密码复杂度原则（设置复杂密码）、定期清理原则（及时回收权限或删除不需要的用户）。权限操作：GRANT：授予用户权限，可指定权限类型、对象范围和访问主机；SHOWGRANTS：查看用户的权限；REVOKE：撤销用户的权限。修改权限后需执行FLUSHPRIVILEGES重载权限表使其生效。第10章使用PHP语言开发Web应用10.4Web应用开发概述10.3使用PHP访问MySQLPHP语言概述10.2PHP语法介绍10.5网上商城开发实例CONTENTS目录

10.110.1PHP语言概述10.1.1初识PHPPHP（HypertextPreprocessor）：超文本预处理器，通用开源脚本语言PHP在服务器端执行，常用于网站编程，混合了C、Java、Perl语法主要特点：开源免费、语法快捷、数据库连接广泛（MySQL/ODBC/Oracle等）面向过程和面向对象可并用，PHP可在Windows、Linux等多平台运行应用：网站开发、行业网站设计、数据库实时更新与维护10.1.2PHP开发环境的安装与配置推荐使用XAMPP：完全免费、易于安装的PHP开发套件XAMPP包含：Apache服务器、PHP、Perl、MySQL/MariaDB"X"代表跨平台（Windows/Linux/MacOS均可运行）安装步骤：下载XAMPP→双击安装→配置Apache（httpd.conf）关键配置：DocumentRoot（如D:/xampp/htdocs）+监听端口（默认80）配置完成后单击"Start"按钮启动Apache服务器10.2PHP语法介绍10.2.1PHP的变量与数据类型变量规则：以$开头，首字符为字母或下划线，大小写敏感变量赋值即创建：$x=5;$y=6;$z=$x+$y;echo$z;数据类型：字符串（单/双引号均可）、整数（十进制/十六进制/八进制）浮点数：$x=10.365;$x=2.4e3;$x=8E-5;逻辑值：true/false；NULL表示未知或不存在；使用var_dump()查看类型值10.2.1数组类型（1）索引数组：通过数字索引访问（索引起始编号为0）$animals=array("dog","cat","pig");$animals[0]="rabbit";（2）关联数组：通过键访问，用foreach遍历键/值对$ages=array("Bill"=>"35","Steve"=>"37","Elon"=>"43");（3）多维数组：包含一个或多个数组的数组$scores=array(array("语文",85),array("计算机",92),array("数据库",80));10.2.2程序流程控制条件判断语句：if($score>90){echo"优秀";}if($score>=60){echo"及格";}else{echo"不及格";}if/elseif/else多分支结构10.2.2程序流程控制（续）

循环语句：（1）while循环：while($x<=5){echo$x;$x++;}（2）for循环：for($x=0;$x<=10;$x++){echo$x;}（3）foreach循环：遍历数组中的每个键/值对foreach($colorsas$value){echo$value;}10.2.3函数/10.2.4处理前端数据函数定义：functiongreet(){echo"Helloworld!";}带返回值：functionadd($a,$b){return$a+$b;}前端数据提交：GET（可见，约2000字符限制）/POST（不可见，无限制）$_REQUEST：保存GET方法提交的数据（URL参数），$_REQUEST['page']$_POST：保存POST方法提交的数据（表单数据），$_POST['name']10.3使用PHP访问MySQL10.3.1连接与关闭数据库连接：$conn=mysqli_connect("主机","用户名","密码")ordie("失败！");选择数据库：mysqli_select_db($conn,"数据库名")或连接时直接指定：mysqli_connect("主机","用户","密码","库名")关闭：mysqli_close($conn);//脚本结束后自动关闭10.3.2/10.3.3创建数据库与执行SQL执行SQL：$result=mysqli_query($conn,"SQL语句");SELECT语句返回结果集（mysqli_result对象）INSERT/UPDATE/DELETE语句返回TRUE或FALSE创建数据库示例：mysqli_query($conn,"CREATEDATABASEtest");选择数据库后建表：mysqli_query($conn,"CREATETABLE...");10.3.4读取数据mysqli_fetch_array($result)：逐行读取，返回关联+索引混合数组mysqli_fetch_assoc($result)：只返回关联数组mysqli_num_rows($result)：查询结果集中的记录数典型查询模式：$result=mysqli_query($conn,"SELECT*FROMstudent");while($row=mysqli_fetch_array($result)){echo$row["id"];}mysqli_close($conn);10.3.5综合练习——数据列表与新增student_list.php：查询数据表，以HTML表格输出