数据库系统原理及MySQL应用教程（第二版）
实验指导书
2019年9月24日
目录
实验1：概念模型（E-R图）画法与逻辑模式转换实验 ................................................................. 3 实验2：关系的完整性、规范化理解与应用实验 ............................................................................ 7 实验3：利用POWERDESIGNER设计数据库应用系统实验 .............................................................. 10 实验4： MYSQL安装创建和维护数据库实验 ................................................................................ 26 实验5：数据表的创建与修改管理实验 ......................................................................................... 27 实验6：MYSQL数据库表的数据插入、修改、删除操作实验 ........................................................ 30 实验7：MYSQL数据库表数据的查询操作实验 ............................................................................... 31 实验8：索引创建与管理操作 ........................................................................................................ 35 实验9：视图创建与管理实验 ........................................................................................................ 37 实验10：存储过程与函数的创建管理实验 ................................................................................... 39 实验11：触发器创建与管理实验 .................................................................................................. 42 实验12：数据库的安全机制管理上机实验 ................................................................................... 44 实验13：数据库的备份与还原上机实验 ....................................................................................... 45 实验14：MYSQL日志管理上机实验 ................................................................................................ 46 实验15：使用PHP访问MYSQL数据库上机实验 ............................................................................ 47
实验1：概念模型（E-R图）画法与逻辑模式转换实验
一、实验目的及要求
1.了解E-R图构成要素以及各要素图元。 2.掌握概念模型E-R图的绘制方法。
3.掌握概念模型向逻辑模型的转换原则和步骤。 二、验证性实验 1.实验内容
(1) 某同学需要设计开发班级信息管理系统，希望能够管理班级与学生信息的数据库，其中学生信息包括学号，姓名，年龄，性别，班号；班级信息包括班号、年级号、班级人数。
①确定班级实体和学生实体的属性。
学生：学号，姓名，年龄，性别，班号 班级：班号、班主任、班级人数
②确定班级和学生之间的联系，给联系命名并指出联系的类型。
一个学生只能属于一个班级，一个班级可以有很多学生，所以和学生间是1对多关系，即1:n
③确定联系本身的属性。
属于
④画出班级与学生关系的E-R图。
学号 学生 N 年龄 姓名 班号 属于 1 班级 班号 班级人数 班主任 ⑤将E-R图转化为关系模式，写出各关系模式并标明各自的码。
学生（学号，姓名，年龄，性别，班号），其码为：学号 班级（班号、班主任、班级人数），其码为：班号
2) 请为电冰箱经销商设计一套存储生产厂商和产品信息的数据库，要求生产厂商的信息包括厂商名称、地址、电话；产品的信息包括品牌、型号、价格；生产厂商生产某产品的数量和日期。 要求：
① 确定产品实体和生产厂商实体的属性。
生产厂商：厂商名称，地址，电话 产品：品牌，型号，价格
② 确定产品和生产厂商之间的联系，给联系命名并指出联系的类型。
一个生产厂商可以生产多个产品，一个产品也可以有很多生产厂商生产，所以产品和生产厂商间是m对多关系，即m:n ③ 确定联系本身的属性。
生产
④ 画出产品与生产厂商关系的E-R图。
⑤ 将E-R图转化为关系模式，写出表的关系模式并标明各自的码。 生产厂商（厂商名称，地址，电话），其码为：厂商名称 产品（品牌，型号，价格），其码为：品牌，型号 生产（厂商名称，品牌，型号，数量，日期），其码为：厂商名称，品牌，型号
3) 设计能够表示学校与校长信息的数据库，其中需要展示学校信息的学校编号，学校名，校长号，地址和校长的信息有校长号，姓名，出生日期。
①确定学校实体和校长实体的属性。
学校：学校编号，学校名，校长号，地址 校长：校长号，姓名，出生年月
②确定学校和校长之间的联系，给联系命名并指出联系的类型。
一个校长只能管理一个学校，一个学校只能有一个校长，所以学校和校长是1对1的联系,即1:1
③确定联系本身的属性。
管理
④画出学校与校长关系的E-R图。
校长号 校长 1 姓名 出生年月 管理 校长号 1 学校 地址 学校名 学校编号
⑤将E-R图转化为关系模式，写出表的关系模式并标明各自的码或外码。 学校（学校编号，学校名，校长号，地址），其码为：学校编号 校长（校长号，姓名，年龄），其码为：校长号
4) 设某汽车运输公司想开发车辆管理系统，其中，车队信息：车队号、车队名等；车辆信息有牌照号、厂家、出厂日期等；司机信息有司机编号、姓名、电话等。 车队与司机之间存在“聘用”联系，每个车队可聘用若干司机，但每个司机只能应聘于一个车队，车队聘用司机有“聘用开始时间”和“聘期”两个属性； 车队与车辆之间存在“拥有”联系，每个车队可拥有若干车辆，但每辆车只能属于一个车队； 司机与车辆之间存在着“使用”联系，司机使用车辆有“使用日期”和“公里数”两个属性，每个司机可使用多辆汽车，每辆汽车可被多个司机使用。
①确定实体和实体的属性。 车队：车队号，车队名）
车辆：车牌照号，厂家，生产日期 司机：司机编号，姓名，电话，车队号
②确定实体之间的联系，给联系命名并指出联系的类型。
车队与车辆联系类型是1：n，联系名称：拥有，车队与司机联系类型是1：n，联系名称为聘用；车辆和司机联系类型为m:n，联系名称为：使用。
③确定联系本身的属性。
联系“聘用”的属性有“聘用开始时间”和“聘期”两个属性，联系“使用“有“使用日期”和“公里数”两个属性。
④画出E-R图。
⑤将E-R图转化为关系模式，写出表的关系模式并标明各自的码。 车队（车队号，车队名） ，其码为：车队号；
车辆（车牌照号，厂家，生产日期，车队号） ，其码为：车牌照号； 司机（司机编号，姓名，电话，车队号，聘用开始时间，聘期），其码为：司机编号 ； 使用（司机编号，车辆号，使用日期，公里数），其码为：司机编号，车辆号。 三、设计性实验
1、设计能够表示出顾客与商品关系的数据库，其中商品信息包括商品编号，商品名称，产地；顾客信息包括顾客号，地址，商品号。顾客与商品之间会产生交易时间、金额信息。
①确定顾客实体和商品实体的属性。
②确定顾客和商品之间的联系，给联系命名并指出联系的类型。 ③确定联系本身的属性。
④画出顾客与商品关系的E-R图。
⑤将E-R图转化为关系模式，写出表的关系模式并标明各自的码。
2、某房产交易公司，需要存储房地产交易中客户，业务员和合同三者信息的数据库。其中客户信息主要有客户编号，购房地址；业务员信息有员工号，姓名，年龄；合同信息有合同编号，合同名称，合同有效时间。其中，一个业务员可接待多个客户，每个客户只签署一个合同。
①确定客户实体，业务员实体和合同实体的属性。
②确定客户，业务员和合同三者之间的联系，给联系命名并指出联系的类型。 ③确定联系本身的属性。
④画出客户，业务员和合同三者关系E-R图。
⑥ 将E-R图转化为关系模式，写出表的关系模式并标明各自的码。 四、观察与思考
如果有10个不同的实体集，它们之间存在着12个不同的二元联系（二元联系是指两个实体集之间的联系），其中3个1:1联系，4个1:N联系，5个M:N联系，那么根据ER模型转换成关系模型的规则，这个ER结构转换成的关系模式个数至少有多少个。
实验2：关系的完整性、规范化理解与应用实验
一、实验目的
（1）了解关系模型的基本概念，掌握候选码和主码的确定 （2）掌握并应用完整性规则 （3）掌握关系规范化的定义和方法
二、验证性实验
1、某同学开发了X公司员工管理系统，其中部门信息表和员工信息表分别见表1和表2。
表1部门信息表
部门代码 0001 0002 0003 0004 部门名 生产部 营销部 客服部 财务部 负责人 李华江 张丽 王欣 张克云 地点 北京海淀 安徽阜阳 浙江宁波 浙江杭州 表2员工信息表
员工代码 201501 201302 201403 201104 姓名 王梅 李想 张丽 李华江 家庭住址 宁波 阜阳 柳州 鞍山 联系电话 86960986 85438769 87893542 82849873 邮政编码 310006 310010 310017 310101 部门代码 0001 0003 0002 0001 请你分析：
(1)确定部门表和员工表中的候选码，并陈述理由。
部门表：部门代码，（负责人，部门名） 理由：部门代码可以唯一标识一个部门，负责人和部门名也可以唯一标识一个部门 员工表：员工代码
理由：员工代码能唯一标识一个员工 （2)选择部门表和员工表的关键字。
部门表：部门代码 员工表：员工代码
（3）在部门表和员工表的结构中标注主关键字。
部门表（部门代码，部门名，负责人，地点）
员工表（员工代码，姓名，家庭住址，联系电话，邮政编码，部门代码） （4）在员工表中确定可能的组合关键字，并陈述理由。
员工代码理由：只有员工代码能唯一标识一个员工 （5）确定在部门表和员工表中共有的属性。
部门代码
（6）指出哪个表中的属性是外关键字。
员工表中的部门代号是外关键字
（7）确定哪个表是主表，哪个表是从表。
主表是员工表，从表是部门表
（8）部门表和员工表是如何通过关键字实施数据完整性的。
部门表中，部门代号不能为空，这就保证了部门的存在性； 员工表中，员工代码不能为空，这说明有这样的员工；
三、设计性实验
1、仿照第1题，已知系信息表和学生信息表分别见表3和表4
表3系表
系编号 0001 0002 0003 系名 计算机系 软件工程系 英语系 系主任 孙阳 胡梅 张政要 联系电话 86960986 85438769 67893542
表4 学生表
学号 1508160001 1508160002 1508160003 1508160004 1508160005 1508160006 名称 张一横 欧阳妹 张广 李吴 萨日花 臧男 性别 男 女 女 男 女 男 出生年份 1995 1996 1997 1994 1991 1998 系编号 0001 0002 0002 0003 0003 0001 家庭地址 北京市海淀区 北京市昌平区 湖南省永州市 山东省枣庄市 天津市南开区 湖南省衡阳市 请你分析 ：
(1)确定系表和学生表中的候选码，并陈述理由。
系表： 理由： 学生表： 理由：
(2)选择系表和学生表的主码。
系表： 学生表：
(3)在系表和学生表的结构中用下划线标注主关键字。
系表（系编号，系名，负责人，联系电话）
学生表（学号，姓名，性别，出生年份，系编号，家庭地址） (4)在学生表中确定可能的组合关键字，并陈述理由。 (5)确定在系表和学生表中表示相同意思的属性。
系表的 属性和学生表的 属性
(6)指出哪个表中的哪个属性是外关键字，这个外关键字的存在说明了两张表之间是什么联系？
(7)确定哪个表是主表，哪个表是从表。
(8)系表和学生表应如何实施三种数据完整性。
系表的实体完整性应怎么实施： 学生表的实体完整性应怎么实施：
这两张表中的参照完整性应怎么实施的：
2、某同学设计了图书在线交易系统，设计了如下订单表，请你用规范化理论将该表进行分解，使之满足3NF的规范化要求。
订单号 订户代号 姓名 地址 书号 书名 出版单位 单价 20150808001 U2015003 郭倩 北京海淀区17号 20150808001 U2015003 胡东 浙江宁波东路8号 20150808001 U2015003 王梅 安徽阜阳颍东路6号 20150808002 U2015004 张青 辽宁沈阳开发区11号 20150808002 U2015004 欧阳宝 海南三亚环城路123号 08785 统计学 人民大学出版社 29 34 03421 宏观经济学 高教出版社 35 65 01022 04532 03422 计算机组成原理 数据库技术及应用 管理学原理 高教出版社 27 35 机械出版社 28 20 高教出版社 32 订购数量 30 四、观察与思考 1．有如下所示两张表：如果向关系P中插入新行，新行的值分别列出如下。哪些行能够插入？若不能插入，为什么？
表5 供应商关系S（主码是“供应商号”）
供应商号 B01 S10 T20 Z01
表6 零件关系P（主码是“零件号”，外码是“供应商号”） 零件号 010 312 201 颜色 红 白 蓝 供应商号 B01 S10 T20 供应商名 红星 宇宙 黎明 立新 所在城市 北京 上海 天津 重庆 A．(‘037’，‘绿’，null) B．(null，‘黄’，‘T20’) C．(‘201’，‘红’，‘T20’) D．(‘105’，‘蓝’，‘B01’)
E．(‘101’，‘黄’，‘T11’)
2．非规范化数据表带来的不利影响是什么?
实验3：利用PowerDesigner设计数据库应用系统实验
一、实验目的
（1）了解数据库设计的过程
（2）学会用PowerDesigner等数据库设计工具经行数据库设计 （3）学会从实际需求进行数据库的设计
二、验证性实验
1、根据详细步骤完成奖学金模块数据库设计
用PowerDesigner软件设计奖学金模块。该模块的功能设计中有4个实体，具体信息如下表所示。
班级表（t_class） 字段名称 classId className classTeacher 字段说明 班级号 班级名称 班主任
学生表（t_stu） 字段名称 studentId name sex 字段说明 学号 姓名 性别
成绩表（t_grade） 字段名称 gradeId chinese maths extraActivities 字段说明 成绩单编号 语文 数学 课外活动
奖学金表（t_ scholarship）
字段名称 scholarshipId scholarshipName department money (1) 利用PowerDesigner软件设计概念模型
概念模型也称为信息模型，以ER图理论为基础，并对其经行了扩充。该模型是从用户
字段说明 奖学金编号 奖学金名称 奖学金设立机构 奖学金额度 的观点对信息经行建模，主要用于数据库的概念数据模型设计。利用PowerDesigner软件设计概念模型时，一般会经过创建实体，添加实体之间的关系两个阶段。
① 创建实体
打开PowerDesigner软件，选择File -> New Model ，打开New Model对话框，然后在该对话框中选择“Conceptual Data Model”模型类型，同时设置“Model name”模型名称的信息，如下图3-1所示：
图3-1 选择设计概念数据模型选项
单击Ok后进入概念数据模型主界面，为了保证概念数据模型图能绘制到一个表格中，我们要通过选择View -> Zoom In 命令，对图标窗口中的表格经行放大，如下图3-2所示，
图3-2 在图表窗口中设置工作区
为便于查看，我们对中间的表格进行标注，方法是通过选择Toolbox面板中的Free Symbols中的Text工具按钮，对其标注为奖学金模块，如下图3-3所示。如果没找到Toolbox面板，可以通过选择菜单View -> Toolbox 来调出Toolbox面板。
图3-3利用Text工具经行标注
接着我们通过选择Toolbox中的Entity工具 添加班级实体,如下图3-4所示。
图3-4 添加班级实体
双击实体，弹出实体属性对话框Entity Properties。在General选项卡中，设置用于标识实体名称的Name选项为“班级表”，设置用于标识实体代码的Code选项为“t_class”,设置用于对实体注释的Comment选项为”这是关于班级的表格“，如下图3-5所示。
图3-5 实体属性对话框Entity Properties
·单击用来设置实体属性的Attributes选项卡，其中Name字段用于标识字段名称，Code字段用于标识字段代码，Data Type字段、Length字段和Precision字段用于设置字段的类型。对班级表实体属性设置如图3-6所示：
图3-6 实体属性对话框Entity Properties
单击确定，得到最终的关于班级的实体信息如下图3-7所示。
图3-7 最终的班级实体
下面我们以同样的方式设计”奖学金模块“中的学生、成绩、奖学金实体如下图3-8 至 图3-13所示：
图3-8 学生实体普通属性
图3-9学生实体所具有的属性
图3-10成绩实体普通属性
图3-11成绩实体所具有的属性
图3-12奖学金实体普通属性
图3-13奖学金实体所具有的属性
最终，关于奖学金模块的4个实体对象具体信息如下图3-14所示。
图3-14 奖学金模块的实体
② 添加实体之间的关系
在数据库设计中，实体之间存在三种关系，分别是”一对一关系“，”一对多关系“，”多对多关系“。在此次设计中，我们先添加班级与学生之间的关系。选择Toolbox中的Relationship工具
,为创建好的班级实体和学生实体添加联系，如下图3-15所示：
图3-15 为班级实体和学生实体添加联系
添加成功后，双击关系图标，弹出Relationship Properties对话框，设置班级实体与学生实体之间的属性信息。在General选项卡中，设置用来标识实体间关系名称的Name选项为班级学生关系，设置用来标识实体间关系代码的Code选项为class_stu_r,设置对实体经行注释的Comment选项为班级与学生的关系，如下图3-16所示：
图3-16 关系普通属性
在Cardinalities选项卡中，存在一个Cardinalities选项组，可用来设置实体间的各种关系。班级与学生是一对多关系，我们选择One-Many选项，如下图3-17所示
图3-17 设置实体之间的关系
单击确定，就设置好了班级与学生实体之间的关系，如下图3-18所示；
图3-18 班级与学生实体之间的关系
以同样的方式创建并设置其他的实体之间的关系。学生实体与成绩实体是一对一关系One-One，学生实体与奖学金实体是多对多关系Many-Many，设置如下图3-19 至图3-22所示：
图3-19 关系普通属性
图3-20 设置实体之间的关系
图3-21 关系普通属性
图3-22 设置实体之间的关系
至此，关于奖学金模块的概念模型设计完成，如图3-23所示
图3-23 奖学金模块的概念模型
(2) 利用PowerDesigner软件转换生成物理数据模型
物理数据模型，就是根据计算机系统的特点，为给定的概念数据模型确定合理的存储结构和存取方法。其中合理主要指设计出的物理数据库占用的存储空间少，对数据库上数据的操作能有更高的效率。
当概念数据模型设计完成后，我们选择Tool->Generate Physical Data Model命令，弹出”PDM Generation Option“对话框，然后在该对话框中设置DBMS为MySQL5.0，同时设置name，Code都为scholarshipPDM，如下图3-24所示：
图3-24 选择设计物理数据模型选项
单击确定，在物理模型主界面中会根据概念模型，结合所给出的数据库管理系统设计出合理的表和表之间的关系，具体信息如下图3-25所示：
图3-25 奖学金模块的物理数据模型
至此，即完成了订单管理模块的物理数据模型。 (3) 生成数据库创建脚本
生成物理数据模型后，就可以利用PowerDesigner软件，将其转换为数据库脚本。打开物理数据模型scholarshipPDM，选择Database -> Generate Database 命令，打开Generate Database对话框，然后在对话框中设置数据库脚本的名称和位置，如下图3-26所示：
图3-26 设置数据库脚本信息对话框
打开数据库脚本文件，具体内容如下： /*==============================================================*/ /* DBMS name: MySQL 5.0 */ /* Created on: 2015/4/18 19:14:24 */ /*==============================================================*/ drop table if exists stu_scholarship_r; drop table if exists \"t_ scholarship\"; drop table if exists t_class; drop table if exists t_grade; drop table if exists t_stu; /*==============================================================*/ /* Table: stu_scholarship_r */ /*==============================================================*/ create table stu_scholarship_r ( studentId int not null, scholarshipId int not null, primary key (studentId, scholarshipId) ); alter table stu_scholarship_r comment '学生与奖学金之间的关系'; /*==============================================================*/ /* Table: \"t_ scholarship\" */ /*==============================================================*/ create table \"t_ scholarship\" ( scholarshipId int not null, scholarshipName varchar(20), department varchar(40), money float(8,2), primary key (scholarshipId) ); alter table \"t_ scholarship\" comment '这是关于奖学金信息的表格'; /*==============================================================*/ /* Table: t_class */ /*==============================================================*/ create table t_class ( classId int not null, className varchar(20), classTeacher varchar(20), primary key (classId) ); alter table t_class comment '关于班级的表格'; /*==============================================================*/ /* Table: t_grade */ /*==============================================================*/ create table t_grade ( gradeId int not null, studentId int, chinese smallint, maths smallint, extraActivities smallint, primary key (gradeId) ); alter table t_grade comment '这是关于成绩的信息'; /*==============================================================*/ /* Table: t_stu */ /*==============================================================*/ create table t_stu ( studentId int not null, classId int, gradeId int, name varchar(20), sex numeric(1,0), primary key (studentId) ); alter table t_stu comment '这是关于学生的信息'; alter table stu_scholarship_r add constraint FK_stu_scholarship_r foreign key (studentId) references t_stu (studentId) on delete restrict on update restrict; alter table stu_scholarship_r add constraint FK_stu_scholarship_r2 foreign key (scholarshipId) references \"t_ scholarship\" (scholarshipId) on delete restrict on update restrict; alter table t_grade add constraint FK_stu_grade_r2 foreign key (studentId) references t_stu (studentId) on delete restrict on update restrict; alter table t_stu add constraint FK_class_stu_r foreign key (classId) references t_class (classId) on delete restrict on update restrict; alter table t_stu add constraint FK_stu_grade_r foreign key (gradeId) references t_grade (gradeId) on delete restrict on update restrict; 三、设计性实验
1、用PowerDesigner软件为在线图书销售系统中的订单管理模块设计数据库。
该模块的功能设计中有4个实体，具体信息如下表所示。
用户表（t_user） 字段名称 userId username sex 字段说明 用户编号 用户名 用户性别 书籍表（t_book） 字段名称 bookId bookName bookPrice 字段说明 书籍编号 书籍名称 书籍价格
订单表（t_order） 字段名称 orderId createTime totalPrice
字段说明 订单编号 下单时间 订单总价格 订单明细表（t_ item）
字段名称 itemId bookNumber 字段说明 订单明细编号 书籍数量 (1)利用PowerDesigner软件设计概念模型。
① 创建实体 ② 添加属性
③ 设置每个实体的主码 ④ 添加实体之间的联系
(2)利用PowerDesigner软件转换成物理数据模型。
(3)利用PowerDesigner软件生成创建数据库表的SQL脚本，并在MySQL中生成数据库。 2、根据下面的“交通违章处罚通知书”设计数据库。
图中显示一张交通违章处罚通知单，根据这张通知单所提供的信息，设计一个存储相关信息的E-R模型，并将这个E-R模型转换成关系数据模型，要求标注各关系模式的主键和外键（其中：一张违章通知书可能有多项处罚，例如：警告+罚款）。
图3-27
（1） 找出实体、实体的属性、实体的主码。 （2） 找出实体间的联系及联系类型。
（3） 用PowerDesigner画出ER图。 （4） 选择MySQL作为DBMS，把ER图转换成物理模型，根据日常生活中的情况合理设置
数据类型，其中通知书编号长度请参照示例“TZ11719”，警察编号长度是3个字符。在MySQL中创建违章数据库（wzdb），并利用PowerDesigner生成所有的数据表。
3、根据提供的网页，设计数据库（另外上交打印的报告）
下面所提供的网页是关于图书检索的。图1中下拉框的数据要求从数据库中读取。根据图1中的检索条件，在图2列表中得到符合条件的图书列表。
图3-28 检索条件选择
图3-29 图书列表
（1）用PowerDesigner画出ER图，要求包含网页中所需的所有属性，设置每个实体的主码。 （2）选择MySQL作为DBMS，转换成物理模型，设置合理的数据类型。
（3）生成建表SQL脚本，并在MySQL中创建readbook数据库，并生成相应数据表。
四、观察与思考
（1）使用PowerDesigner将概念模型转换成物理模型后，实体、属性、联系有哪些变化？ （2）PowerDesigner工具中的自动的模型转换是否符合模型转换的理论规则？
（3）*尝试设计一个一对一的实体联系，看看PowerDesigner工具将如何处理？结合模型转换的理论规则，说说PowerDesigner工具这样处理是否妥当？你是否能想出更有创新的处理办法？
实验4： MySQL安装创建和维护数据库实验
一、实验目的
（1）掌握在Windows 平台下安装与配置MySQL 5.7的方法。 （2）掌握启动服务并登录MySQL 5.7数据库的方法和步骤。 （3）了解手工配置MySQL 5.7的方法。 （4）掌握MySQL 数据库的相关概念。
（5）掌握使用MySQL Workbench/Navicat等客户端工具和SQL 语句创建数据库的方法。 （6）掌握使用MySQL Workbench/Navicat等客户端工具和SQL 语句删除数据库的方法。
二、实验内容
（1）在Windows 平台下安装与配置MySQL 5.7版。 （2）在服务对话框中，手动启动或者关闭MySQL 服务。 （3）使用Net 命令启动或关闭MySQL 服务。
（4）分别用MySQL Workbench/Navicat等客户端工具和命令行方式登录MySQL。
（5）在my.ini 文件中将数据库的存储位置改为D:\\MYSQL\\DATA，重启服务后，然后创建数据库。
（6）创建数据库。
① 使用MySQL Workbench/Navicat等客户端工具创建教学管理数据库JXGL。 ② 使用SQL 语句创建数据库MyTestDB。 （7）查看数据库属性。
① 在MySQL Workbench/Navicat等客户端工具中查看创建后的JXGL 数据库和MyTestDB 数据库的状态，查看数据库所在的文件夹。
② 利用SHOW DATABASES 命令显示当前的所有数据库。 （8）删除数据库。
① 使用MySQL Workbench/Navicat等客户端 图形工具删除JXGL 数据库。 ② 使用SQL 语句删除MyTestDB 数据库。
③ 利用SHOW DATABASES 命令显示当前的所有数据库。
三、观察与思考
MySQL的数据库文件有几种？扩展名分别是什么？
实验5：数据表的创建与修改管理实验
一、实验目的
(1) 掌握表的基础知识。
(2) 掌握使用Navicat或其他第三方管理工具和SQL语句创建表的方法。 (3) 掌握表的修改、查看、删除等基本操作方法。 (4) 掌握表中完整性约束的定义。 (5) 掌握完整性约束的作用
二、实验内容
(一) 表定义与修改操作
在schoolInfo数据库中创建一个 teacherInfo 表，表结构如下： 字段名 id num name sex address 字段描述 编号 教工号 姓名 性别 家庭住址 数据类型 INT(4) INT(10) VARCHAR(20) VARCHAR(4) DATETIME VARCHAR(50) 主键 是 否 否 否 否 否 外键 非空 否 否 否 否 否 否 是 是 是 是 否 否 唯一 是 是 否 否 否 否 自增 是 否 否 否 否 否 birthday 出生日期 按照下列要求进行表定义操作：
(1)首先创建数据库schoolInfo。 CREATE DATABASE schoolInfo; (2)创建 teacherInfo 表。 CREATE TABLE teacherInfo (
id INT(4) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, num INT(10)NOT NULL UNIQUE , Name VARCHAR(20)NOT NULL, Sex VARCHAR(4)NOT NULL, Birthday DATETIME, Address VARCHAR(50) )；
(3)将 teacherInfo 表的name字段的数据类型改为 VARCHAR(30)。 ALTER TABLE teacherInfo MODIFY name VARCHAR(30) NOT NULL; (4)将birthday宁段的位置改到sex字段的前面。
ALTER TABLE teacherInfo MODIFY birthday DATETIME AFTER name; (5)将num字段改名为t_id。
ALTER TABLE teacherInfo CHANGE numtid INT(10)NOT NULL; (6)将 teacherInfo 表的address字段删除。 ALTER TABLE teacherInfo DROPaddress;
(7)在 teacherInfo 表中增加名为wages的字段，数据类型为FLOAT。 ALTER TABLE teacherInfo ADDwages FLOAT; (8)将 teacherInfo 表改名为 teacherInfo Info。
ALTER TABLE teacherInfo RENAME teacherInfo lnfo; (9)将 teacherInfo 表的存储引擎更改为MyISAM类型。
ALTER TABLE teacherInfo ENGINE=MyISAM; （二）创建staffinfo数据库,并在定义department表和worker表,完成两表之间的完整性约束。
Department表的结构 字段名 d_id d_name address 字段描述 部门号 部门名 部门位置 数据类型 INT(4) VARCHAR(20) VARCHAR(50) VARCHAR(20) 主键 外键 非空 唯一 是 否 否 否 否 否 否 否 是 是 否 否 是 是 否 否 自增 否 否 否 否 function 部门职能 Worker表的结构 字段名 id num d_id name sex address 字段描述 编号 员工号 部门号 姓名 性别 家庭住址 数据类型 INT(4) INT(10) INT(4) VARCHAR(20) VARCHAR(4) DATE VARCHAR(50) 主外键 键 是 否 否 否 否 否 否 否 否 是 否 否 否 否 非空 唯一 自增 是 是 否 是 是 否 否 是 是 否 否 否 否 否 是 否 否 否 否 否 否 birthday 出生日期 按照下列要求进行表操作：
(1)在staffinfo数据库下创建department表和worker表。 (2)删除department表。 操作如下：
（1）创建department表，代码如下： CREATE TABLE department(
d_id INT(4)NOT NULL UNIQUE PRIMARY KEY, d_name VARCHAR(20)NOT NULL UNIQUE , function VARCHAR(50), address VARCHAR(50) );
（2）创建worker表，代码如下： CREATE TABLE worker(
id INT(4)NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, numINT(10)NOT NULL UNIQUE , d_id INT(4),
name VARCHAR(20) NOT NULL, sex VARCHAR(4) NOT NULL, birthday DATE, address VARCHAR(50),
CONSTRAINT worker_fk FOREIGN KEY(d_id) REFERENCES department(d_id)
);
DROP TABLE department;
（3）删除department表，代码如下： (4）删除worker表的外键约束，代码如下：
ALTER TABLE worker DROP FOREIGN KEY worker_fk; （5）重新删除department表，代码如下：
DROP TABLE department;
三、观察与思考
1、关于NOT NULL
(1) 在定义基本表语句时，NOT NULL参数的作用是什么？ (2)主码列修改成允许NULL能否操作？为什么？ 2、关于外码
(1) 根据下面设计的表结构，Employee表的外键能否设置成功？思考外码设置需要注意哪些问题？
Department表的结构 字段名 dno dname 字段名 eno dno name 字段描述 部门号 部门名 字段描述 员工号 姓名 数据类型 INT(4) VARCHAR(20) 数据类型 INT(10) VARCHAR(20) 主键 外键 Employee表的结构 主键 外键 √ 所在部门号 Char(10) (2) 如果主表无数据，从表的数据能输入吗？ (3) 先创建从表，再创建主表是否可以？ 3、关于主码和唯一约束
(1) 唯一约束列是否允许NULL值？
(2) 一张表可以设置几个主码，可以设置几个唯一约束？
实验6：MySQL数据库表的数据插入、修改、删除操作实验
一、实验目的
1．掌握MySQL数据库表的数据插入、修改、删除操作SQL语法格式 2．掌握数据表的数据的录入、增加和删除的方法 二、验证性实验
1．学校教师管理数据库中的teacherInfo表，其表的定义如下表所示，请完成如下操作。
字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 num 教工号 INT(10) 是 否 是 是 否 name 姓名 VARCHAR(20) 否 否 是 否 否 sex 性别 VARCHAR(4) 否 否 是 否 否 birthday 出生日期 DATETIME 否 否 否 否 否 address 家庭住址 VARCHAR(50) 否 否 否 否 否 （1）向teacherInfo表中插入记录。写出INSERT语句的 INSERT INTO teacherInfo VALUES(1001,'张龙','男','1984-11-08','北京市昌平区'); INSERT INTO teacherInfo VALUES(1002,'李梅','女','1970-01-21','北京市海淀区'); INSERT INTO teacherInfo VALUES(1003,'王一丰','男','1976-10-30','北京市昌平区'); INSERT INTO teacherInfo VALUES(1004,'赵六','男','1980-06-05','北京市顺义区'); （2）更新教工号为1003的记录，将生日（birthday）改为“1982-11-08”。UPDATE语句的 UPDATE teacherInfo SET birthday='1982-11-08' WHERE num=1003;
（3）将性别（sex）为“男”的记录的家庭住址（address）都变为“北京市朝阳区”。UPDATE语句的
UPDATE teacherInfo SET address='北京市朝阳区' WHERE sex='男'; （4）删除教工号（num）为1002的记录删除。DELETE语句的
DELETE FROM teacherInfo WHERE num=1002; 三、设计性试验
某超市的食品管理的数据库的Food表，Food表的定义如表所示，请完成插入数据、更新数据和删除数据。
Food表的定义 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 foodid INT(4) 食品编号 是 否 是 是 是 Name VARCHAR(20) 食品名称 否 否 是 否 否 Company VARCHAR(30) 生产厂商 否 否 是 否 否 价格（单Price FLOAT 否 否 是 否 否 位：元） Product_time YEAR 生产年份 否 否 否 否 否 保质期（单Validity_time INT(4) 否 否 否 否 否 位：年） address VARCHAR(50) 厂址 否 否 否 否 否 按照下列要求进行操作： (1)采用3种方式，将表的记录插入到Food表中。
方法一：不指定具体的字段，插入数据： 'QQ饼干','QQ饼干厂',2.5,'2018',3,'北京'。
方法二：依次指定food表的字段，插入数据： 'MN牛奶','MN牛奶厂',3.5,'2019',1,'河北')。
方法三：同时插入多条记录，插入数据：
'EE果冻','EE果冻厂',1.5,'2017',2,'北京', 'FF咖啡','FF咖啡厂',20,'2012',5,'天津', 'GG奶糖','GG奶糖',14,'2013',3,'广东'; 分别写出相应语句。
(2)将“MN牛奶厂”的厂址(address)改为“内蒙古”，并且将价格改为3.2。 (3)将厂址在北京的公司的保质期(validity_time)都改为5年。
(4)删除过期食品的记录。若当前时间-生产年份(producetime)>保质期（validity_time），则视为过期食品。
(5)删除厂址为“北京”的食品的记录。 四、观察与思考
1．对于删除的数据，如何实现”逻辑删除“（即数据库中的数据不删除，给用户的感觉是删除了）？
2．DROP命令和DELETE命令的本质区别是什么？
3．利用INSERT、UPDATE和DELETE命令可以同时对多个表进行操作吗？
实验7：MySQL数据库表数据的查询操作实验
一、实验目的
1．掌握SELECT 语句的基本语法格式。 2．掌握SELECT 语句的执行方法。
3．掌握SELECT 语句的 GROUP BY 和 ORDER BY 子句的作用。 二、验证性实验 题目要求：
在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。Bumen表和yuangong表的定义如表所示。
表bumen表的定义 字段名 d_id d_name function address 字段名 id name sex birthday d_id 字段描述 部门号 部门名称 部门职能 工作地点 字段描述 员工号 姓名 性别 年龄 部门号 数据类型 主键 外键 非空 唯一 自增 INT(4) 是 否 是 是 否 VARCHAR(20) 否 否 是 是 否 VARCHAR(20) 否 否 否 否 否 VARCHAR(30) 否 否 否 否 否 表yuangong表的定义
数据类型 主键 外键 非空 唯一 自增 INT(4) 是 否 是 是 否 VARCHAR(20) 否 否 是 否 否 VARCHAR(4) 否 否 是 否 否 INT(4) 否 否 否 否 否 VARCHAR(20) 否 是 是 否 否 salary 工资 Float 否 否 否 否 否 address 家庭住址 VARCHAR(50) 否 否 否 否 否 bumen表的练习数据：
1001,'人事部','人事管理','北京' 1002,'科研部','研发产品','北京' 1003,'生产部','产品生产','天津' 1004,'销售部','产品销售','上海' yuangong表的练习数据：
8001,'韩鹏','男',25,1002,4000,'北京市海淀区' 8002,'张峰','男',26,1001,2500,'北京市昌平区' 8003,'欧阳','男',20,1003,1500,'湖南省永州市' 8004,'王武','男',30,1001,3500,'北京市顺义区' 8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区' 8006,'呼延','男',28,1003,1800,'天津市南开区'
然后在bumen表和yuangong表查询记录。查询的要求如下： （1）查询yuangong表的所有记录。 SELECT * FROM yuangong;
或者列出yuangong表的所有字段名称。
SELECT id,name,sex,age,d_id,salary,address FROM yuangong; （2）查询yuangong表的第四条到第五条记录。
SELECT id,name,sex,age,d_id,salary,address FROM yuangongLIMIT3,2; （3）从bumen表查询部门号（d_id）、部门名称（d_name）和部门职能（function）。 SELECT d_id,d_name,function FROM yuangong;
（4）从yuangong表中查询人事部和科研部的员工的信息。先从bumen表查询出人事部和科研部的部门号。然后到yuangong表中去查询员工的信息。 SELECT * FROM yuangong WHERE d_id=ANY(
SELECT d_id FROM bumen
WHERE d_nameIN('人事部','科研部')); 或者使用下面的代码。 SELECT * FROM yuangong WHERE d_idIN(
SELECT d_id FROM bumen
WHERE d_name='人事部'ORd_name='科研部');
（5）从yuangong表中查询年龄在25到30之间的员工的信息。可以通过两种方式来查询。 第一种方式:
SELECT * FROM yuangong WHERE age BETWEEN 25 AND 30; 第二种方式:
SELECT * FROM yuangong WHERE age>=25 AND age<=30;
（6）查询每个部门有多少员工。先按部门号进行分组，然后用COUNT()函数来计算每组的人数。
SELECT d_id,COUNT(id) FROM yuangong GROUP BY d_id; 或者给COUNT(id)取名为sum。
SELECT d_id,COUNT(id) AS sum FROM yuangong GROUP BY d_id;
（7）查询每个部门的最高工资。先按部门号进行分组，然后用MAX()函数来计算最大值。 SELECT d_id,MAX(salary) FROM yuangong GROUP BY d_id; （8）用左连接的方式查询bumen表和yuangong表。 使用 LEFT JOINON 来实现左连接。 SELECT bumen.d_id,d_name,function,bumen.address,id,name,age,sex, salary,yuangong.address
FROM bumen LEFT JOIN yuangong ON yuangong.d_id=bumen.d_id;
（9）计算每个部门的总工资。先按部门号进行分组，然后用SUM()函数来求和。 SELECT d_id,SUM(salary) FROM yuangong GROUP BY d_id; （10）查询yuangong表，按照工资从高到低的顺序排列。 SELECT * FROM yuangong ORDER BY salary DESC;
（11）从bumen表和yuangong表中查询出部门号，然后使用UNION合并查询结果。 SELECT d_id FROM yuangong UNION SELECT d_id FROM bumen;
（12）查询家是北京市员工的姓名、年龄、家庭住址。这里使用 LIKE 关键字。 SELECT name,age,address FROM yuangong WHERE address LIKE '北京%'; 三、设计性试验
将在student表和score表上进行查询。Student表和score表的定义如表所示：
Student表的内容 字段名 Num name Sex birthday bumen address 数据类型 主键 外键 非空 唯一 自增 INT(10) 是 否 是 是 否 VARCHAR(20) 否 否 是 否 否 VARCHAR(4) 否 否 是 否 否 DATETIME 否 否 否 否 否 VARCHAR(20) 否 否 否 否 是 VARCHAR(50) 否 否 否 否 否 score表的内容 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 Id 编号 INT(10) 是 否 是 是 否 C_name 课程名 VARCHAR(20) 否 否 否 否 否 Stu_id 学号 INT(10) 否 是 是 否 否 grade 成绩 INT(10) 否 否 否 否 否 字段描述 学号 姓名 性别 出生年份 院系 家庭住址 表创建成功后，查看两个表的结构。 Student练习数据如下：
901,'张军','男',1985,'计算机系','北京市海淀区' 902,'张超','男',1986,'中文系','北京市昌平区' 903,'张美','女',1990,'中文系','湖南省永州市' 904,'李五一','男',1990,'英语系','辽宁省阜新市' 905,'王芳','女',1991,'英语系','福建省厦门市' 906,'王桂','男',1988,'计算机系','湖南省衡阳市' score表练习数据如下： 901,'计算机',98 901,'英语',80 902,'计算机',65 902,'中文',88
903,'中文',95 904,'计算机',70 904,'英语',92 905,'英语',94 906,'计算机',90 906,'英语',85
然后按照下列要求进行表操作：
（1）查询student表的所有记录。
方法一：用”*“。
方法二：列出所有的列名。
（2）查询student表的第二条到第四条记录。
（3）从student表查询所有学生的学号、姓名和院系的信息。 （4）查询计算机系和英语系的学生的信息。
方法一：使用IN关键字 方法二：使用OR关键字
（5）从student表中查询年龄为18到22岁的学生的信息。
方法一：使用BETWEEN AND 关键字来查询 方式二：使用 AND 关键字和比较运算符。
（6）student表中查询每个院系有多少人，为统计的人数列取别名sum_of_bumen。 （7）从score表中查询每个科目的最高分。
（8）查询李五一的考试科目（c_name）和考试成绩（grade）。 （9）用连接查询的方式查询所有学生的信息和考试信息。 （10）计算每个学生的总成绩（需显示学生姓名）。 （12）计算每个考试科目的平均成绩。
（13）查询计算机成绩低于95的学生的信息。
（14）查询同时参加计算机和英语考试的学生的信息。 （15）将计算机成绩按从高到低进行排序。
（16）从student表和score表中查询出学生的学号，然后合并查询结果。 （17）查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。 （18）查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。 四、观察与思考
1、LIKE的通配符有哪些？分别代表什么含义？ 2、知道学生的出生日期，如何求出其年龄？
3、IS能用“=”来代替吗？如何周全地考虑“空数据”的情况？
4、关键字ALL和DISTINCT有什么不同的含义？关键字ALL是否可以省略不写？
5、聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？ 6、WHERE子句与HAVING子句有何不同？
7、 count(*)、count(列名)、count(distinct 列名)三者的区别是什么？通过一个实例说明。 8、内连接与外连接有什么区别？ 9、“=”与IN在什么情况下作用相同？
实验8：索引创建与管理操作
一、实验目的：
(1) 理解索引的概念与类型。
(2) 掌握创建、更改、删除索引的方法。
(3) 掌握维护索引的方法。 二、验证性实验
在job数据库中有登录用户信息：userlogin表和个人信息：information 表。具体如表结构所示。
USERLOGIN 表的结构 字段名 id name info 字段描述 编号 用户名 附加信息 数据类型 INT(4) VARCHAR(20) VARCHAR(20) TEXT 主键 外键 非空 唯一 是 否 否 否 数据类型 INT(4) VARCHAR(20) VARCHAR(4) DATE VARCHAR(50) VARCHAR(20) BLOB 否 否 否 否 是 是 是 否 是 否 否 否 自增 是 否 否 否 唯一 是 否 否 否 否 否 否 自增 是 否 否 否 否 否 否 Password 密码 information表的结构 字段名 id Name Sex Birthday Address Tel pic 字段描述 编号 姓名 性别 出生日期 家庭地址 电话号码 照片 主键 外键 非空 是 否 否 否 否 否 否 否 否 否 否 否 否 否 是 是 是 否 否 否 否 请在上述2表上完成如下操作：
1、在name字段创建名为index_name的索引
CREATE INDEX index_name ON information (name(10)); 2、创建名为index_bir的多列索引
CREATE INDEX index_bir ON information (birthday,address); 3、用 ALTER TABLE 语句创建名为index_id的惟一性索引 ALTER TABLE information ADD INDEX index_id(idASC); 4、删除 userlogin 表上的index_ userlogin 索引 DROP INDEX index_userlogin ON userlogin ; 5、查看 userlogin 表的结构
SHOW CREATE TABLE userlogin ;
6、删除information 表上的index_name索引 DROP INDEX index_name ON information ; 7、查看information表的结构
SHOW CREATE TABLE information; 三、设计性试验
在数据库job下创建worklnfo表。创建表的同时在id字段上创建名为index_id的唯一性索引，而且以降序的格式排列。
Worklnfo表结构信息如下： 字段名 id Name Type Address Wages Contents extra 字段描述 编号 职位名称 职位类型 工作地点 工资 工作内容 附加信息 数据类型 INT(4) VARCHAR(20) VARCHAR(10) VARCHAR(50) YEAR TINYTEXT TEXT 主键 外键 非空 是 否 否 否 否 否 否 否 否 否 否 否 否 否 是 是 否 否 否 否 否 唯一 是 否 否 否 否 否 否 自增 是 否 否 否 否 否 否 请完成如下操作：
1、使用 CREATE INDEX 语句为name字段创建长度为10的索引index_name。 2、使用 ALTER TABLE 语句在type和address上创建名为index_t的索引。 3、使用 ALTER TABLE 语句将workInfo表的存储引擎更改为MyISAM类型。 4、使用 ALTER TABLE 语句在extra字段上创建名为index_ext的全文索引。 5、使用 DROP 语句删除workInfo表的惟一性索引index_id。
四、观察与思考
(1)数据库中索引被破坏后会产生什么结果？ (2)视图上能创建索引吗？
(3) MySQL中组合索引创建的原则是什么？ (4)主键约束和唯一约束是否会默认创建唯一索引？
实验9：视图创建与管理实验
一、实验目的
1．理解视图的概念。
2．掌握创建、更改、删除视图的方法。 3．掌握使用视图来访问数据的方法。
二、验证性实验
在job数据库中，有聘任人员信息表：Work_lnfo表，其表结构如下表所示：
字段名 Id Name Sex Age Address Tel 字段描述 编号 名称 性别 年龄 家庭地址 电话号码 数据类型 INT(4) VARCHAR(20) VARCHAR(4) INT(4) VARCHAR(50) VARCHAR(20) 主键 外键 非空 是 否 否 否 否 否 否 否 否 否 否 否 是 是 是 否 否 否 唯一 是 否 否 否 否 否 自增 否 否 否 否 否 否 其中表中练习数据如下：
1.'张明','男',19,'北京市朝阳区','1234567' 2.'李广','男',21,'北京市昌平区','2345678' 3.'王丹','女',18,'湖南省永州市','3456789' 4.'赵一枚','女',24,'浙江宁波市','4567890' 按照下列要求进行操作：
1．创建视图info_view，显示年龄大于20岁的聘任人员id,name,sex,address信息。 CREATE VIEW info_view(id,name,sex,address)AS SELECT id,name,sex,address FROM work_info WHERE age>20 WITH LOCAL CHECK OPTION;
2．查看视图info_view的基本结构和详细结构。 查看基本结构： DESC info_view; 查看详细结构：
SHOW CREATE VIEW info_view;
3．查看视图info_view的所有记录。 SELECT * FROM info_view;
4．修改视图info_view，满足年龄小于20岁的聘任人员id,name,sex,address信息。 ALTER VIEW info_view(id,name,sex,address) AS SELECT id,name,sex,address FROM work_info WHERE age<20 WITH LOCAL CHECK OPTION;
5．更新视图，将id号为3的聘任员的性别，由“男“改为“女”。 UPDATE info_view SET sex='女' WHERE id=3; 6．删除info_view视图。 DROP VIEW info_view; 三、设计性试验
在学生管理系统中，有学生信息表studentinfo表，其表结构如下： 字段名 Number 字段描述 学号 数据类型 INT(4) 主键 外键 非空 唯一 是 否 是 是 自增 否 Name Major age 姓名 专业 年龄 VARCHAR(20) VARCHAR(20) INT(4) 否 否 否 否 否 否 是 否 否 否 否 否 否 否 否 请完成如下操作：
1.使用CREATE VIEW语句来创建视图college_view，显示studentinfo表中的number,name,age,major，并将字段名显示为：student_num,student_name,student_age,department。
2.执行SHOW CREATE VIEW语句来查看视图的详细结构。 3.更新视图。向视图中插入如下3条记录： 0901,'张三',20,'外语' 0902,'李四',22,'计算机' 0903,'王五',19,'计算机'
4.修改视图，使视图中只显示专业为“计算机”的信息。 5.删除视图。
四、观察与思考
1．通过视图中插入的数据能进入到基本表中去吗？ 2．WITH CHECK OPTION能起什么作用？
3．修改基本表的数据会自动反映到相应的视图中去吗？
4．哪些视图中的数据不可以增删改操作？
实验10：存储过程与函数的创建管理实验
一、实验目的：
1. 理解存储过程和函数的概念。 2. 掌握创建存储过程和函数的方法。 3. 掌握执行存储过程和函数的方法。
4. 掌握游标的定义、使用方法。 二、验证性实验
1．某超市的食品管理的数据库的Food表，Food表的定义如表所示， Food表的定义 字段名 foodid Name Company Price Product_time Validity_time address 字段描述 食品编号 食品名称 生产厂商 价格（单位：元） 生产年份 保质期（单位：年） 厂址 数据类型 INT(4) VARCHAR(20) VARCHAR(30) FLOAT YEAR INT(4) VARCHAR(50) 主键 是 否 否 否 否 否 否 外键 否 否 否 否 否 否 否 非空 是 是 是 是 否 否 否 唯一 是 否 否 否 否 否 否 自增 是 否 否 否 否 否 否 各列有如下数据： 'QQ饼干','QQ饼干厂',2.5,'2008',3,'北京' 'MN牛奶','MN牛奶厂',3.5,'2009',1,'河北' 'EE果冻','EE果冻厂',1.5,'2007',2,'北京' 'FF咖啡','FF咖啡厂',20,'2002',5,'天津' 'GG奶糖','GG奶糖',14,'2003',3,'广东' （1）在food表上创建名为Pfood_price_count的存储过程。其中存储过程Pfood_price_count有3个参数。输入参数为price_infol和price_info2，输出参数为count。存储过程的满足：查询food表中食品单价高于price_infol且低于price_info2的食品种数，然后由count参数来输出，并且计算满足条件的单价的总和。 代码如下：
//使用“DELIMITER &&”将SQL语句的结束符号变成&& DELIMITER &&
CREATE PROCEDURE Pfood_price_count (IN price_info1 FLOAT,IN price_info2 FLOAT, OUT count INT )
READS SQL DATA BEGIN
//定义变量temp DECLARE temp FLOAT; //定义游标match_price DECLARE match_price CURSOR FOR SELECT price FROM food; //定义条件处理。如果没有遇到关闭游标，旧退出存储过程
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE match_price; //为临时变量sum赋值 SET @sum=0;
//用SELECT … INTO 语句来为输出变量count赋值 SELECT COUNT(*) INTO count FROM food WHERE price>price_info1 AND price //执行条件语句 IF temp>price_info1 AND temp //将SQL语句的结束符号变成“;” DELIMITER ; (2)使用CALL语句来调用存储过程。查询价格在2至18之间的食品种数。代码如下： CALL Pfood_price_count(2,18,@count) ; (3)使用SELECT语句查看结果。代码如下： SELECT @count,@sum; 其中，count是存储过程的输出结果：sum是存储过程中的变量，sum中的值满足足条件的单价的总和。 (4)使用DROP语句删除存储过程Pfood_price_count。代码如下： DROP PROCEDURE Pfood_price_count ; (5)使用存储函数来实现（1）的要求。存储函数的代码如下： DELIMITER && CREATE FUNCTION Pfood_price_count1(price_info1 FLOAT,price_info2 FLOAT ) RETURNS INT READS SQL DATA BEGIN RETURN (SELECT COUNT(*) FROM food WHERE price>price_info1 AND price SELECT Pfood_price_count1(2,18); （7）删除存储函数 DROP FUNCTION Pfood_price_count1; 注：存储函数只能返回一个值，所以只实现了计算满足条件的食品种数。使用RETURN来将计算的食品种数返回回来。调用存储函数与调用MySQL内部函数的方式是一样的。 三、设计性实验 学校教师管理数据库中的teacherInfo表，其表的定义如下表所示，请完成如下操作。 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 num 教工号 INT(10) 是 否 是 是 否 name 姓名 VARCHAR(20) 否 否 是 否 否 sex 性别 VARCHAR(4) 否 否 是 否 否 birthday 出生日期 DATETIME 否 否 否 否 否 address 家庭住址 VARCHAR(50) 否 否 否 否 否 向teacherInfo表中插入记录： 1001,'张龙','男','1984-11-08','北京市昌平区' 1002,'李梅','女','1970-01-21','北京市海淀区' 1003,'王一丰','男','1976-10-30','北京市昌平区' 1004,'赵六','男','1980-06-05','北京市顺义区' （1）创建名为teachernfo1的存储过程。要求：存储过程teachernfo1有3个参数。输入参数为teacherid和type，输出参数为info。满足：根据编号(teacherid)来查询teachernfo表中的记录。如果type的值为1时，将姓名(name)传给输出参数info;如果type的值为2时，将年龄传给输出参数info;如果type为其他值，则返回字符串“Error”。 （2）调用存储过程，参数值teacher id为2，type为1。 （3）使用DROP PRODECURE语句来删除存储过程 （4） 创建名为teacherinfo2的存储函数。要求：存储过程teacherinfo2有两个参数：teacher id和type。满足：根据编号(teacher id)来查询teacher表中的记录。如果type的值是1时，则返回姓名(name)值；如果type的值是2时，则返回年龄；如果type为其他值，则返回字符串“Error”。 （5）使用SELECT语句调用teacherinfo2存储函数。 （6）使用DROP FUNCTION语句来删除teacherinfo2存储函数。 四、观察与思考 (1) 什么时候适合通过创建存储过程来实现？ (2)功能相同的存储过程和存储函数的不同点有哪些？ (3)使用游标对于数据检索的好处有哪些？ 实验11：触发器创建与管理实验 一、实验目的 1．理解触发器的概念与类型。 2．理解触发器的功能及工作原理。 3．掌握创建、更改、删除触发器的方法。 4．掌握利用触发器维护数据完整性的方法。 二、验证性实验 某同学定义产品信息product表，主要信息有：产品编号、产品名称、主要功能、生产厂商、c厂商地址，生成product表的SQL代码如下： CREATE TABLE product ( id INT(10) NOT NULL UNIQUE PRIMARY KEY , name VARCHAR(20) NOT NULL , function VARCHAR(50) , company VARCHAR(20) NOT NULL, address VARCHAR(50) ); 在对product表进行数据操作时，需要对操作的内容和时间进行记录。于是定义了operate表，其表生成SQL语句为： CREATE TABLE operate ( op_id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT , op_name VARCHAR(20) NOT NULL , op_tiem TIME NOT NULL ); 请完成如下任务： 1．在product表上分别创建BEFORE INSERT、AFTER UPDATE和AFTER DELETE3个触发器，触发器的名称分别为Tproduct_bf_insert、Tproduct_af_update和Tproduct_af_del。执行语句部分都是向operate表插入操作方法和操作时间。 （1）创建Tproduct_bf_insert触发器SQL代码如下： CREATE TRIGGER Tproduct_bf_insert BEFORE INSERT ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'Insert product', now()); （2）创建Tproduct_af_update触发器的SQL代码如下： CREATE TRIGGER Tproduct_af_update AFTER UPDATE ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'Update product', now()); （3）创建Tproduct_af_del触发器的SQL代码如下： CREATE TRIGGER Tproduct_af_del AFTER DELETE ON product FOR EACH ROW INSERT INTO operate VALUES(null, 'delete product', now()); 2．对product表分别执行INSERT、UPDATE和DELETE操作，分别查看operate表。 （1）对product表中插入一条记录：1, 'abc','治疗感冒', '北京abc制药厂','北京市昌平区' SQL代码：INSERT INTO product VALUES(1, 'abc','治疗感冒', '北京abc制药厂','北京市昌平区'); （2）更新记录，将产品编号为1的厂商住址：改为“北京市海淀区”。 SQL代码：UPDATE product SET address='北京市海淀区' WHERE id=1; （3）删除产品编号为1的记录。 SQL代码：DELETE FROM product WHERE id=1; 3．删除Tproduct_bf_update触发器 DROP TRIGGER Tproduct_bf_insert; 三、设计性实验 1．在product表上分别创建AFTER INSERT、BEFORE UPDATE和BEFORE DELETE 3个触发器， 触发器的名称分别为product_af_insert、product_bf_update和Tproduct_bf_del。执行语句部分都是向operate表中插入操作方法和操作时间。 2．查看product_bf_del触发器的基本结构。 3．对product表分别执行如下INSERT、UPDATE和DELETE操作，分别查看operate表。 INSERT INTO product VALUES(2, '止血灵','止血', '北京止血灵制药厂','北京市昌平区'); UPDATE product SET address='天津市开发区' WHERE id=2; DELETE FROM product WHERE id=2; 4．删除product_bf_update触发器。 三、观察与思考 1．能否在当前数据库中为其他数据库创建触发器？ 2．触发器何时被激发？ 实验12：数据库的安全机制管理上机实验 一、实验目的： (1) 理解My SQL的权限系统的工作原理。 (2) 理解My SQL账户及权限的概念。 (3) 掌握管理My SQL账户和权限的方法。 (4) 学会创建和删除普通用户的方法和密码管理的方法。 (5) 学会如何进行权限管理。 二、验证性实验 1.实验任务如下： (1) 使用root用户创建Testuser1用户，初始密码设置为123456。让该用户对所有数据库拥有SELECT、CREATE、DROP、SUPER权限。 GRANT SELECT,create,drop,super ON *.* TO Testuser1@localhost identified by '123456' with grant option; (2) 创建Testuser2用户，该用户没有初始密码。 CREATE USER Testuser2@localhost; (3) 用Testuser2用户登录，将其密码修改为000000。 SET PASSWORD = PASSWORD('000000'); (4) 用Testuser1用户登录，为Testuser2用户设置CREATE和DROP权限。 GRANT create,drop ON *.* TO Testuser2@localhost ; (5) 用Testuser2用户登录，验证其拥有的CREATE和DROP权限。 CREATE TABLE jxgl.t1(id int); DROP TABLE jxgl.t1; (6) 用root用户登录，收回Testuser1用户和Testuser2用户的所有权限。 REVOKE ALL ON *.* FROM Testuser1@localhost,Testuser2@localhost; (7) 删除Testuser1用户和Testuser2用户。 DROP USER Testuser1@localhost,Testuser2@localhost; (8) 修改root用户的密码。 UPDATE mysql.user SET password=PASSWORD(\"000000\") WHERE user='root'; 三、设计性实验 (1)将使用root用户创建examl用户，初始密码设置为123456。让该用户对所有数据库拥有SELECT、CREATE、DROP、SUPER和GRANT权限。 (2)创建用户exam2，该用户没有初始密码。 (3)用exam2登录，将其密码设置为686868。 (4)用examl登录，为exam2设置CREATE和DROP权限。 (5)用root用户登录，收回examl和exam2的所有权限。 (7) 删除exam1用户和exam2用户。 (8) 修改root用户的密码。 四、观察与思考 1、新创建的MySQL用户能否在其他机器上登录MySQL数据库？ 实验13：数据库的备份与还原上机实验 一、实验目的： 1.理解MySQL备份的基本概念。 2.掌握各种备份数据库的方法。 3.掌握如何从备份中恢复数据。 4. 掌握数据库迁移的方法。 5. 掌握表的导入与导出的方法。 二、验证性实验 (1)使用mysqldump命令备份JXGL数据库，生成的gbak.sql文件存储在D:\\mysqlbak。 mysqldump -u root -proot jxgl > d:\\mysqlbak\\gbak.sql (2)使用mysqldump命令备份JXGL数据库中的course表和sc表，生成的cs.sql文件存储在D:\\mysqlbak。 mysqldump -u root -proot jxgl course sc > d:\\mysqlbak\\cs.sql (3)使用mysqldump命令同时备份两个数据库，具体数据库自定。 mysqldump -u root -proot --databases jxgl readbook > d:\\mysqlbak\\grbak.sql (4)将JXGL数据库删除，分别使用mysql命令和source命令将JXGL数据库的备份文件gbak.sql恢复到数据库中。 mysql -u root –proot DROP DATABASE jxgl; CREATE DATABASE jxgl; USE jxgl; source d:/mysqlbak/gbak.sql (5)将数据库中的course表和sc表删除，分别使用mysql命令和source命令将备份文件cs.sql恢复到JXGL数据库中。 DROP TABLE course,sc; source d:/mysqlbak/cs.sql 三、设计性实验 对JXGL数据库中的score表进行备份和还原的操作。本节要求的操作如下: (1)使用mysqldump命令来备份score表。备份文件存储在D:\\backup路径下。 (2)使用mysql命令来还原score表。 (3)使用SELECT INTO … OUTFILE来导出score表中的记录。记录存储到D:\\backup\\score.txt中。 (4)使用mysqldump命令，将score表的记录导出到XML文件中。这个XML文件存储在D:\\backup中。 四、观察与思考 1.如何选择备份数据库的方法？ 2.如何升级MySQL数据库？ 实验14：MySQL日志管理上机实验 一、实验目的 1.了解日志的含义、作用和优缺点。 2.掌握二进制日志、错误日志和通用查询日志的管理。 二、验证性实验 执行以下操作步骤。 (1)启动二进制日志功能，并且将二进制日志存储到d:\\binlog目录下。二进制日志文件命名为binlog。 将log-bin选项加入到my.cnf或者my.ini配置文件中。在配置文件的[mysqld]组中加入下面的代码：log-bin = d:\\binlog 。配置完成后，二进制文件将存储在d:\\binlog目录下，而且第一个二进制文件的完整名称将是binlog.000001。 (2)启动服务后，查看二进制日志。 启动MySQL服务，在d:\\binlog录下可以找到binlog.000001，然后可以使用mysqlbinlog命令来查看二进制日志。先切换到C:\\目录下，然后再执行mysqlbinlog命令，语句如下：c: \\mysql> mysqlbinlog d:\\binlog\\binlog.000001 (3)向studentinfo数据库下的sc使用mysqlbinlog语句来查看二进制日志文件，mysqlbinlog命令如下：c: \\mysql> mysqlbinlog d:\\binlog\\binlog.000001 (4)暂停二进制日志功能，然后再删除score表中的所有记录。 后面需要删除sc表中的所有记录，而此时不希望这个删除语句被记录到二进制日志中。因此使用SET语句来暂停二进制日志功能，SET语句的代码如下：SET SQL_LOG_BIN=0; (5)重新开启二进制日志功能。 可以使用SET语句来重新开启二进制日志功能，SET语句如下：SET SQL_LOG_BIN=1 ;执行该语句之后，二进制日志功能将可以继续使用。 (6)使用二进制日志来恢复sc表。 使用EXIT退出MySQL数据库，然后执行下面的语句： mysqlbinlog binlog.000001 | mysql –u root –p执行该语句之后，再次登录到MySQL数据库中。然后查询score表中的记录是否恢复成功。 (7)删除二进制日志。 使用RESET MASTER语句可以删除二进制日志。 三、设计性实验 按如下要求，完成任务。 (1)将错误日志的存储位置设置为C:\\LOG目录下。 (2)开启通用查询日志，并设置该日志存储在C:\\LOG目录下。 (3)开启慢查询日志，并设置该日志存储在C:\\LOG目录下。设置时间值为10秒。 (4)查看错误日志、通用查询日志和慢查询日志。 (5)删除错误日志。 (6)删除通用查询日志和慢查询日志。 四、观察与思考 1.平时应该开启什么日志？ 2.如何使用二进制日志？ 实验15：使用PHP访问MySQL数据库上机实验 一、实验目的 1、了解用PHP操作MySQL的流程 2、掌握使用PHP对MYSQL的增删改查的基本操作 二、验证性实验 1、MySQL数据库的用户名：root，密码：1234。MySQL中有个teacherInfo数据库，teacherInfo数据库中有一张teachers表，表字段类型如下，要求连接数据库并向表中插入如下数据： teachers表字段 字段名 id num name sex birthday address id 1 2 3 4 connect_errno){ printf(\"Connect failed:%s\\n\ exit(); } mysqli_query($mysqli,\"set names 'utf8'\"); $sql= \"insert into teachers values (1, 201501, '张永生', '男', '1994-11-08', '北京市海淀区'), (2, 201502, '李二嘎', '女', '1970-01-21', '北京市朝阳区'), (3, 201503, '王富贵', '男', '1976-10-30', '北京市海淀区'), (4, 201504, '赵甜贵', '男', '1990-06-05', '北京市顺义区')\"; if($mysqli->query($sql)){ echo \" 插入成功 插入的记录数：\}else{ echo \" 插入失败 字段描述 编号 教工号 姓名 性别 出生日期 家庭住址 num 201501 201502 201503 201504 数据类型 INT(4) INT(10) VARCHAR(20) VARCHAR(4) DATETIME VARCHAR(50) 主键 是 否 否 否 否 否 外键 否 否 否 否 否 否 非空 是 是 是 是 否 否 唯一 是 是 否 否 否 否 自增 是 否 否 否 否 否 teachers表的数据 name sex birthday 张永生 李二嘎 王富贵 赵甜贵 男 男 女 男 1994-11-08 1970-01-21 1976-10-30 1990-06-05 address 北京市朝阳区 北京市海淀区 湖南省永州市 宁省阜新市 ?> （2）使用query()函数把张永生老师的地址改为北京市海淀区 $sql= \"update teachers set address = '北京市海淀区' where name = '张永生' \"; if($mysqli->query($sql)){ echo \"更新成功\"; echo \" 更新的记录数：\ }else{ } echo \" 更新失败 （3）使用multi_query()函数查询地址是北京市海淀区的老师名称，并删除李二嘎和赵甜贵的信息记录。 $sql= \" select * from teachers where address = '北京市海淀区' ; delete from teachers where address = '北京市海淀区'\"; $re = $mysqli->multi_query($sql); if($re){ $result = $mysqli->store_result(); $num = $result->field_count; $info = $result->fetch_fields(); echo \" 查询成功 teachers表中地址是北京市海淀区的所有记录信息： \ $row->sex,\"\\ } $result->close(); if($mysqli->next_result()) { echo \"删除成功\"; echo \" 删除的记录数：\ } else{ echo \" 删除失败 查询失败 } （4）关闭所有对象 $connection->close(); 三、设计性实验 完成一个学生信息管理系统，实现信息的添加、修改、删除等各项基本功能。具体任务如下： （1）编写一个添加数据的insert.php程序，向数据库StudentInfo中的表students添加一条名字为“孙凯”的学生基本信息：'1309070338', '孙凯', '男', '1993-10-11', '1102', '商务1301'。 （2）编写一个检索数据的PHP程序find.php，在数据库StudentInfo的表students中查询姓名为“王丽”的学生以及她的专业和年龄。 （3）写一个读取指定结果集中记录号的记录的PHP程序seek.php，在数据库StudentInfo的表students中查询第2位学生的姓名。 （3）编写一个修改数据的PHP程序update.php，将数据库StudentInfo中的表students中一个名为“孙凯”的学生出生年月修改为'1995-10-11',。 （4）编写一个删除数据的PHP程序dele.php将数据库StudentInfo中的表students的一个名为“孙凯”的学生基本信息删除。 四、观察与思考 查资料，研究选择mysql接口还是选择mysqli接口来访问MySQL? 因篇幅问题不能全部显示，请点此查看更多更全内容