本书主要包括15个实验，内容涵盖数据库、表、数据查询、数据操作、完整性约束、索引、视图、MySQL程序设计、存储过程、游标、触发器、MySQL数据库安全基础、SQL注入、MySQL安全配置与管理、数据库的备份与恢复，教材内容基本涵盖了数据库原理课程的主要知识点，同时还对数据库上的安全设置及相关技术进行了介绍，读者通过上机实验及测试习题可以掌握数据库及安全上的各项技术及操作，本书适合作为高等学校网络空间安全及计算机相关专业学生学习用书。

实验教材，通俗易懂。

李燕，女，1980年1月生于甘肃省兰州市，甘肃政法大学网络空间安全学院教师，研究生学历，教授职称。2003年7月起至今一直从事教学工作，主要担任《数据库原理》、《数据库原理及安全》《数据科学导论》、《Python程序设计》等课程的教学工作。研究方向为网络空间安全、计算机视觉、应用安全。参与并完成国自然一项（排名第三），主持并完成省自然科学基金一项、校级重点科研创新项目一项，主持并完成校级重点教改项目一项，主编教材一部，参编教材多部。2020年度获得甘肃省普通高等学校青年教师成才奖，2017年主持完成甘肃省教学成果奖并获教育厅级，2020年参加校青年教师教学竞赛获一等奖，获优秀教学奖一等奖一次，多次组织本科生、研究生参加挑战杯、赛佰特杯、人工智能挑战赛、研究生数学建模等比赛并获奖。

实验1 数据库操作 ................................................................................................1 1.1 实验目的 ......................................................................................................................1 1.2 知识储备 ......................................................................................................................1 1.2.1 数据库介绍 ..........................................................................................................1 1.2.2 SQL概述 ..............................................................................................................2 1.2.3 数据库的备份 ......................................................................................................4 1.3 实验内容 ......................................................................................................................6 1.3.1 创建数据库 ..........................................................................................................6 1.3.2 修改数据库 ..........................................................................................................6 1.3.3 数据库的分离和附加 ..........................................................................................7 1.3.4 数据库的备份和恢复 ..........................................................................................8 1.3.5 删除数据库 ........................................................................................................10 1.4 巩固加油站 ................................................................................................................11 实验2 数据库表操作 ..........................................................................................12 2.1 实验目的 ....................................................................................................................12 2.2 知识储备 ....................................................................................................................12 2.2.1 MySQL数据类型 ...............................................................................................12 2.2.2 表结构 ................................................................................................................15 2.3 实验内容 ....................................................................................................................16 2.3.1 创建数据库表 ....................................................................................................16 2.3.2 修改数据库表结构 ............................................................................................18 2.3.3 删除数据库表 ....................................................................................................20 2.4 巩固加油站 ................................................................................................................21 实验3 数据操作 .................................................................................................23 3.1 实验目的 ....................................................................................................................23 3.2 知识储备 ....................................................................................................................23 3.2.1 数据更新 ............................................................................................................23 3.2.2 复制数据表 ........................................................................................................24 3.3 实验内容 ....................................................................................................................243.3.1 插入数据 ............................................................................................................24 3.3.2 修改数据 ............................................................................................................28 3.3.3 删除数据 ............................................................................................................29 3.3.4 复制数据库表 ....................................................................................................30 3.4 巩固加油站 ................................................................................................................32 实验4 数据查询 .................................................................................................33 4.1 实验目的 ....................................................................................................................33 4.2 知识储备 ....................................................................................................................33 4.2.1 LIMIT限制查询结果的条数 .............................................................................33 4.2.2 HAVING关键字 .................................................................................................34 4.2.3 聚集函数 ............................................................................................................34 4.2.4 连接 ....................................................................................................................34 4.3 实验内容 ....................................................................................................................35 4.3.1 单表查询 ............................................................................................................37 4.3.2 连接查询 ............................................................................................................48 4.3.3 嵌套查询 ............................................................................................................49 4.3.4 集合查询 ............................................................................................................51 4.4 巩固加油站 ................................................................................................................52 实验5 完整性约束设置 .......................................................................................54 5.1 实验目的 ....................................................................................................................54 5.2 知识储备 ....................................................................................................................54 5.2.1 主键（主码） ....................................................................................................54 5.2.2 外键（外码） ....................................................................................................56 5.2.3 关系的完整性 ....................................................................................................58 5.2.4 MySQL查看表中的约束 ..................................................................................58 5.3 实验内容 ....................................................................................................................59 5.3.1 实体完整性约束 ................................................................................................59 5.3.2 参照完整性约束 ................................................................................................60 5.3.3 用户定义的完整性约束 ....................................................................................61 5.4 巩固加油站 ................................................................................................................67 实验6 索引 .........................................................................................................69 6.1 实验目的 ....................................................................................................................696.2 知识储备 ....................................................................................................................69 6.2.1 索引的定义 ........................................................................................................69 6.2.2 索引的作用 ........................................................................................................69 6.2.3 索引的分类 ........................................................................................................70 6.2.4 索引的优缺点 ....................................................................................................70 6.2.5 索引的使用场景 ................................................................................................71 6.2.6 索引与约束的关系 ............................................................................................71 6.3 实验内容 ....................................................................................................................72 6.3.1 创建索引 ............................................................................................................72 6.3.2 查看索引 ............................................................................................................74 6.3.3 删除索引 ............................................................................................................75 6.3.4 索引重命名 ........................................................................................................76 6.4 巩固加油站 ................................................................................................................77 实验7 视图 .........................................................................................................78 7.1 实验目的 ....................................................................................................................78 7.2 知识储备 ....................................................................................................................78 7.2.1 视图的定义 ........................................................................................................78 7.2.2 视图的优缺点 ....................................................................................................79 7.2.3 视图的使用场景 ................................................................................................80 7.2.4 视图与基本表的区别 ........................................................................................80 7.3 实验内容 ....................................................................................................................81 7.3.1 创建视图 ............................................................................................................81 7.3.2 查看视图 ............................................................................................................83 7.3.3 修改视图 ............................................................................................................84 7.3.4 删除视图 ............................................................................................................85 7.3.5 更新视图 ............................................................................................................86 7.4 巩固加油站 ................................................................................................................89 实验8 MySQL程序设计 .....................................................................................91 8.1 实验目的 ....................................................................................................................91 8.2 知识储备 ....................................................................................................................91 8.2.1 常量 ....................................................................................................................91 8.2.2 变量 ....................................................................................................................93 8.2.3 运算符及表达式 ................................................................................................948.2.4 流程控制语句 ....................................................................................................94 8.2.5 函数 ....................................................................................................................98 8.3 实验内容 ..................................................................................................................101 8.4 巩固加油站 ..............................................................................................................105 实验9 存储过程 ...............................................................................................106 9.1 实验目的 ..................................................................................................................106 9.2 知识储备 ..................................................................................................................106 9.2.1 存储过程的定义 ..............................................................................................106 9.2.2 存储过程的分类 ..............................................................................................107 9.2.3 存储过程的优缺点 ..........................................................................................107 9.2.4 存储过程的使用场景 ......................................................................................108 9.2.5 存储过程和存储函数的对比 ..........................................................................108 9.3 实验内容 ..................................................................................................................109 9.3.1 创建存储过程 ..................................................................................................109 9.3.2 调用储存过程 ..................................................................................................110 9.3.3 查看存储过程 ..................................................................................................113 9.3.4 修改存储过程 ..................................................................................................115 9.3.5 删除存储过程 ..................................................................................................116 9.4 巩固加油站 ..............................................................................................................116 实验10 游标.....................................................................................................118 10.1 实验目的 ................................................................................................................118 10.2 知识储备 ................................................................................................................118 10.3 实验内容 ................................................................................................................119 10.3.1 声明游标 ........................................................................................................119 10.3.2 打开游标 ........................................................................................................120 10.3.3 使用游标 ........................................................................................................120 10.3.4 关闭游标 ........................................................................................................121 10.4 巩固加油站 ............................................................................................................123 实验11 触发器 .................................................................................................125 11.1 实验目的 ................................................................................................................125 11.2 知识储备 ................................................................................................................125 11.2.1 触发器的定义 ................................................................................................12511.2.2 触发器的分类 ................................................................................................125 11.2.3 触发器的应用场景 ........................................................................................126 11.2.4 触发器的优缺点 ............................................................................................127 11.2.5 处理错误的方式 ............................................................................................127 11.3 实验内容 ................................................................................................................127 11.3.1 创建触发器 ....................................................................................................127 11.3.2 查看触发器 ....................................................................................................130 11.3.3 修改和删除触发器 ........................................................................................132 11.4 巩固加油站 ............................................................................................................133 实验12 数据库安全基础 ...................................................................................134 12.1 实验目的 ................................................................................................................134 12.2 知识储备 ................................................................................................................134 12.2.1 数据库安全性控制的一般方法 ....................................................................134 12.2.2 MySQL权限系统 ...........................................................................................134 12.3 实验内容 ................................................................................................................136 12.3.1 MySQL用户管理 ...........................................................................................136 12.3.2 MySQL权限管理 ...........................................................................................137 12.3.3 MySQL角色管理 ...........................................................................................138 12.3.4 MySQL 8新变化 ............................................................................................139 12.4 巩固加油站 ............................................................................................................141 实验13 SQL注入 .............................................................................................142 13.1实验目的 ................................................................................................................142 13.2 知识储备 ................................................................................................................142 13.2.1 SQL注入的基础知识 ....................................................................................142 13.2.2 union注入.......................................................................................................147 13.2.3 截取字符串 ....................................................................................................150 13.2.4 REGEXP正则匹配 ........................................................................................150 13.3 实验内容 ................................................................................................................152 13.3.1 联合查询注入 ................................................................................................152 13.3.2 盲注 ................................................................................................................157 13.3.3 POST注入和堆叠注入 ..................................................................................169 13.4 巩固加油站 ............................................................................................................189实验14 安全配置与管理 ...................................................................................190 14.1 实验目的 ................................................................................................................190 14.2 知识储备 ................................................................................................................190 14.2.1 MySQL权限表与其存取过程 .......................................................................190 14.2.2 MySQL的权限分布 .......................................................................................191 14.3 实验内容 ................................................................................................................196 14.3.1 MySQL用户授权操作 .................................................................................196 14.3.2 MySQL权限安全配置原则 .........................................................................204 14.3.3 MySQL安全规划 ........................................................................................204 14.3.4 MySQL文件授权管理 .................................................................................211 14.3.5 MySQL备份还原操作 .................................................................................220 14.4 巩固加油站 ............................................................................................................224 实验15 数据库的备份与恢复 ...........................................................................225 15.1 实验目的 ................................................................................................................225 15.2 知识储备 ................................................................................................................225 15.2.1 备份的分类 .................................................................................................225 15.2.2 备份策略 .....................................................................................................225 15.3 实验内容 ................................................................................................................226 15.3.1 MySQL具体的备份实现操作 .....................................................................226 15.3.2 备份案例 .....................................................................................................228 15.4 巩固加油站 ............................................................................................................230