目 录
实验一 数据库和表的管理 1
实验二 数据查询 5
实验三 索引和视图 7
实验四 数据完整性 9
实验五 存储过程和函数 11
实验六 数据库的安全管理 13
实验七 数据库的备份与恢复 15
实验一 数据库和表的管理
一、实验目的和要求：
1. 了解MySQL数据库的逻辑结构和物理结构的特点。
2. 学会使用SQL语句创建、选择、删除数据库。
3. 学会使用SQL语句创建、修改、删除表。
4. 学会使用SQL语句对表进行插入、修改和删除数据操作。
5. 了解MySQL的常用数据类型。
二、实验内容：
1. 使用SQL语句创建数据库studentsdb。
2. 使用SQL语句选择studentsdb为当前使用数据库。
3. 使用SQL语句在studentsdb数据库创建数据表student_info、curriculum、grade，三个表的数据结构如表1-表3所示。
表1 student_info表结构
列名 数据类型 允许NULL值 主键
学号 char(4) 否 是
姓名 char(8) 否 否
性别 char(2) 是 否
出生日期 date 是 否
家族住址 varchar(50) 是 否
表2 curriculum表结构
列名 数据类型 允许NULL值 主键
课程编号 char(4) 否 是
课程名称 varchar(50) 是 否
学分 int 是 否
表3 grade表结构
列名 数据类型 允许NULL值 主键
学号 char(4) 否 是
课程编号 char(4) 否 是
分数 int 是 否
4. 使用SQL语句ALTER TABLE修改curriculum表的“课程名称”列，使之为空。
5. 使用SQL语句ALTER TABLE修改grade表的“分数”列，使其数据类型为decimal(5,2)。
6. 使用SQL语句ALTER TABLE为student_info表添加一个名为“备注”的数据列，其数据类型为varchar(50)。
7. 使用SQL语句创建数据库studb，并在此数据库下创建表stu，表结构与数据与studentsdb的student_info表相同。
8. 删除表stud的“备注”列。
9. 删除表stud。
10. 删除数据库studb。
三、实验器材：
微机+操作系统+MySQL5.7。
四、实验步骤：
1.启动MySQL5.7的MySQL5.7command line client 平台创建数据库studentsdb。
2.打开studentsdb数据库，为该数据库创建数据表，并对表的结构和数据进行管理。
3.创建studb数据库，并创建stu数据表。
4.删除studb数据库。
五、实验结果：
1、CREATE DATABASE studentsdb
2、use studentsdb
3、CREATE TABLE student info(
学号CHAR(4) NOT NULL PRIMARY KEY,
姓名CHAR(8) NOT NULL,
性别CHAR(2),
出生日期 DATE,
家庭住址 VARCHAR(50))
CREATE TABLE curriculum(
课程编号CHAR(4) NOT NULL PRIMARY KEY,
课程名称 VARCHARI(50),
学分INT))
CREATE TABLE grade（
学号CHAR(4) NOT NULL,
课程编号CHAR(4) NOT NULL,
分数INT，
PRIMARY KEY(学号,课程编号)）
4、ALTER TABLE curriculum
MODIFY COLUMN课程名称 VARCHAR(50)
5、ALTER TABLE grade
MODIFY COLUMN分数 DECIMAL(5,2);
6、
ALTER TABLE
ADD备注 VARCHAR(50)
7、CREATE DATABASE STUDB;
USE STUDB;
CREATE TABLE STU
AS SELECT * FROM studentsdb.student _info;
8、ALTER TABLE stu
DROP COLUMN备注;
9、DROP TARLE stu
10、DROP DATABASE studb
六、实验小结：
实验二 数据查询
一、实验目的和要求：
1. 掌握使用SQL的SELECT语句进行基本查询的方法。
2. 掌握使用SELECT语句进行条件查询的方法。
3. 掌握SELECT语句的GROUP BY、ORDER BY以及UNION子句的作用和使用方法。
4. 掌握嵌套查询的方法。
5. 掌握连接查询的操作方法。
二、实验内容：
1. 在studentsdb数据库中使用SELECT语句进行基本查询。
（1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。
（2）查询student_info表学号为 0002的学生的姓名和家庭住址。
（3）查询student_info表所有出生日期在95年以后的女同学的姓名和出生日期。
2. 使用select语句进行条件查询。
（1）在grade表中查询分数在70-80范围内的学生的学号、课程编号和成绩。
（2）在grade表中查询课程编号为0002的学生的平均成绩。
（3）在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数。
（4）查询student_info的姓名和出生日期，查询结果按出生日期从大到小排序。
（5）查询所有姓名“张”的学生的学号和姓名。
3. 对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序。
4. 使用GROUP BY子句查询grade表中各个学生的平均成绩。
5. 使用UNION运算符针student_info表中姓“刘”的学生的学号、姓名与姓“张”的学生的学号、姓名返回在一个表中。
6. 嵌套查询
（1）在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。
（2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。
（3）使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。
（4）使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。
7. 连接查询
（1）查询分数在80-90范围内的学生的学号、姓名、分数。
（2）使用INNER JOIN连接方式查询学习“数据库原理及应用”课程的学生学号、姓名、分数。
（3）查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。
（4）使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。
三、实验器材：
微机+操作系统+MySQL5.7。
四、实验步骤
1.启动MySQL5.7的MySQL5.7command line client 平台打开数据库studentsdb。
2.根据查询要求完成各种类型的数据查询，并应得到正确的查询结果。
五、实验结果
1、SELECT学号,姓名,出生日期 FROM student-info；
SELECT姓名,家庭住址 FROM student info WHERE学号=1002；
SELECT姓名,出生日期 FROM student info
NERE出生日期>=11996-01-01AND性别=女;
2、SELECT* FROM grade WE分数 BETWEEN70AD80；
SELECT AVG(分数)平均分 FROM grade WHERE课程编号=10002;
SELECT COUNT()选课人数,CO(分数)有成绩人数 FROM grade
NERE课程编号=10003；
SELECT姓名,出生日期 FROM student info ORDER BY出生日期DEsc；
SELECT学号,姓名 FROM student info WI姓名LEE'张';
3、SELECT学号,姓名,性别,出生日期,家庭住址 FROM student inf
ORDER BY性别ASC,学号DESC;
4、SELECT学号,AVG(分数)平均成绩 FROM grade GROUP BY学号;
5、select 学号,姓名 FROM student info WHEF姓名 like‘刘%’
union
select学号,姓名 FROM student info WHERE姓名 like‘张%’
6、select 姓名,出生日期 FROM student info
where性别=(SElect性别 FROM student info WHERE姓名='刘东阳);
select s.学号,姓名,性别 ROM student _info,grade
Where 学号IN(Select 学号 FROM grade
Where 课程编号in(‘0002’,’0005’);
Select 课程编号,分数 FROM grade
Where 学号=’0001’ AND 分数>AnY( SELECT 分数 FROM grade
Where 学号=’0002’);
Select 课程编号,分数 FROM grade
学号=’0001’ and 分数>ALL( SELECT分数 FROM grade
B学号=’0002’);
7、SelECT S.学号,姓名,分数 FROM student _info s, grade g
s.学号=g.学号 ANd分数 BETHEEN 80 AnD 90;
select s.学号,姓名,分数 FROM student _info s INNER JOIN grade g
on s.学号=g.学号 iNNER JOIN curriculun c ON g.课程编号=c.课程编号
where 课程名称=’数据库原理及应用’;
SeLECT S.学号,姓名,MAX(分数) 最高成绩
FROM student_ info s, grade g
wHERE S,学号=g.学号
GROUP BY s.学号;
SELECT S.学号,姓名,5Um(分数) 总成绩
FROM student _info s LEFT OUTER JOIN grade g ON s.学号=g.学号
GROUP BY S.学号;
六、实验小结：
实验三 索引和视图
一、实验目的和要求：
1. 学会使用SQL语句CREATE INDEX创建索引。
2. 学会使用SQL语句DROP INDEX删除索引。
3. 学会使用SQL语句CREATE VIEW创建视图的用法。
4. 掌握使用SQL语句ALTER VIEW修改视图的方法。
5. 了解删除视图的SQL语句DROP VIEW的用法。
二、实验内容：
1. 使用SQL语句ALTER TABLE分别删除studentsdb数据库的student_info表、grade表、curriculum表的主键索引。
2. 使用SQL语句为curriculum表的课程编号创建唯一性索引，命名为cno_idx。
3. 使用SQL语句为grade表的“分数”字段创建一个普通索引，命名为grade_idx。
4. 使用SQL语句为grade表的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_sid_cid_idx。
5. 查看grade表上的索引信息。
6. 使用SQL语句删除索引grade_idx。再次查看grade表上的索引信息。
7. 使用SQL语句CREATE VIEW建立一个名为v_stu_c的视图，显示学生的学号、姓名、所学课程的课程编号，并利用视图查询学号为0003的学生情况。
8. 基于student_info表、curriculum表和grade表，建立一个名为v_stu_g的视图，视图包括所有学生的学号、姓名、课程名称、分数。使用视图v_stu_g查询学号为0001的学生的课程平均分。
9. 使用SQL语句修改视图v_stu_g，显示学生的学号、姓名、性别。
10.利用视图v_stu_g为student_info表添加一行数据：学号为0010、姓名为陈婷婷、性别为女。
11.利用视图v_stu_g删除学号为0010的学生记录。
12.利用视图v_stu_g修改姓名为张青平的学生的高等数学的分数为87。
13.使用SQL语句删除视图v_stu_c和v_stu_g。
三、实验器材：
微机+操作系统+MySQL5.7。
四、实验步骤：
1.启动MySQL5.7的MySQL5.7command line client 平台打开数据库studentsdb。
2.根据要求创建视图和索引，并对视图和索引进行修改和删除。
五、实验结果：
1、ALTER TABLE student info DROP PRIMARY KEY:
ALTER TABLE curriculum DROP PRIMARY KEY;
ALTER TABLE grade DROP PRIMARY KEY；
2、CREATE UNIQUE INDEX cno idx ON curriculum(课程编号)
3、CREATE INDEX grade idx ON grade(分数);
4、CREATE INDEX grade sid cid idx ON grade(学号,课程编号)
5、SHOW INDEX FROM grade
6、DROP INDEX grade idx ON grade
SHOW INDEX FROM grade
7、CREATE VIEW v _stu
SELECT S.学号,姓名,课程编号 FROM student info s, grade g
wHERE S.学号=g.学号;
SELECT * FROM v _stu
wHERE学号=’0003’
8、CREATE VIEW v _stu _g
SELECT S.学号,姓名,课程名称,分数
FROM student info s, grade g, curriculum c
wERE s.学号=g.学号AND g.课程编号=c.课程编号;
SELECT AVG(分数) 平均分 FROM v stu g WHERE学号=’0001’
9、ALTER VIEW v_ stu _g
SELECT学号,姓名,性别 FROM student _info;
10、 InSERT INTO v _stu_g.(学号,姓名,性别)
VALUES(‘0010’,’陈婷婷’,’女’)
11、DELETE FROM v Btu9ERE学号=‘0010’
12、UPDATE grade set分数=87
wHERE学号=( SELECT 学号FOM v_s tu _g WHERE 姓名=’张青平’) AND
课程编号=( SELEC课程编号 FROM curriculum where课程名称=’高等数学’)
六、实验小结：
实验四 数据完整性
一、实验项目和要求：
1. 掌握使用SQL语句CREATE TABLE定义约束的方法。
2. 掌握使用SQL语句ALTER TABLE增加或删除约束的方法。
3. 了解约束的各种类型。
4. 掌握使用SQL语句CREATE TRIGGER创建触发器的方法。
5. 掌握引发触发器的方法。
6. 掌握使用SQL语句DROP TRIGGER删除触发器的方法。
二、实验内容：
1. 创建students数据库，在该数据库下创建表stu，并同时创建约束，表结构及约束要求如表1所示。
表1 stu的表结构
字段 类型 是否为空 约 束
学号 char(4) 否 主键
姓名 char(8) 是
性别 char(2) 是
出生日期 date 是
2. 创建表sc，并同时创建约束，表结构及约束要求如表2所示。
表2 sc的表结构
字段 类型 是否为空 约 束
学号 char(4) 否 外键参照stu表的学号列（约束名fk_sno)
课号 char(4) 否
成绩 decimal(5,2) 是 0≦成绩≦100
设置（学号，课号）为主键。
3. 创建表course，并同时创建约束，表结构及约束要求如表3所示。
表3 course的表结构
字段 类型 是否为空 约 束
课号 char(4) 否
课名 char(20) 是 唯一约束（约束名uq_cname)
学分 int 是
4. 在course表的课号列建立主键约束。
5. 在sc表的课号列建立外键约束fk_cno，参照course表的课号列的取值，要求实现级联更新。
6. 在stu表的姓名列建立唯一约束名uq_sname。
7. 在course表的学分列建立检查约束ck_xf，检查条件为学分>0。
8. 删除sc表的外键约束fk_cno，fk_sno。
9. 删除stu表的主键约束。
10.删除course表的唯一约束uq_cname。
11.创建测试表test，包含一个字段date_time，字段类型varchar(50)；
创建触发器test_trig，实现在stu表中每插入一条学生记录后，则自动在test表中追加一条插入成功时的日期时间。SYSDATE()函数用来获取当前的日期和时间。
为stu表插入一条记录引发触发器，查看test表的内容。
12.在course表上创建触发器del_trig，当course表上删除一门课程时，级联删除sc表该课程的记录。
删除course表的一条记录，查看sc表相应记录是否被自动删除。
三、实验器材：
微机+操作系统+MySQL5.7。
四、实验步骤：
1.启动MySQL5.7的MySQL5.7command line client 平台创建数据库students。
2.在students下创建带有约束的数据表，并对表的约束进行修改和删除。
3.在对应数据表上创建触发器，并测试触发器效果。
五、实验结果：
1、CREATE DATABASE students
CREATE TABLE stu(
学号CHAR(4) PRIMARY KEY,
姓名CHAR(B),
性别CHAR(2),
出生日期 date);
2.create table sc (学号CHAR(4) NOT NULL,
课号CHAR(4),
成绩 DECIMAL(5,2)CECK(成绩 BETWEEN O AND100),
PRIMARY KEY(课号，学号)，
FOREIGN KEY(课号) REFERENCES course(课号)
3、CREATE TABLE course(
课号CHAR(4) NOT NULL，
课名 char(20),
CONSTRAINT ug cname UNIQUE(课名));
4、alter table course ADD PRIMARY KEY(课号);
5、alter table sc ADD FOREIGN KEY(课号) REFERENCES course(课号)
ON UPDATE CASCADE;
6、ALTER TABLE stu
uniqUE(姓名)
7、alter table course check(学分>0);
11、CREATE TRIGGER del_trig
AFTER
FOR EACH ROW
DELETE FROM SC WHERE 课号=0LD.课号；
Delete from course where课号=’1’
六、实验小结：
实验五 存储过程和函数
一、实验目的和要求：
1. 掌握通过SQL语句CREATE PROCEDURE创建存储过程的方法。
2. 掌握使用SQL语句CALL调用存储过程的方法。
3. 掌握使用SQL语句ALTER PROCEDURE修改存储过程的方法。
4. 掌握使用SQL语句DROP PROCEDURE删除存储过程的方法。
5. 掌握使用CREATE FUNCTION创建存储函数的方法。
6. 掌握使用SQL语句ALTER FUNCTION修改存储函数的方法。
7. 掌握使用SQL语句DROP FUNCTION删除存储函数的方法。
二、实验内容：
1. 输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名的学生的各科成绩。
DELIMITER @@
CREATE PROCEDURE stu_info(IN name CHAR(8))
BEGIN
SELECT s.学号,姓名,课程编号,分数 FROM student_info s,grade g
WHERE s.学号=g.学号 and 姓名=name;
END @@
使用CALL命令执行存储过程stu_info，其参数值为'张青平'。
DELIMITER ;
CALL stu_info('张青平');
2. 使用studentsdb数据库中的student_info表、curriculum表、grade表。
（1）创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。
（2）调用存储过程stu_grade。
3. 使用studentsdb数据库中的student_info表、curriculum表、grade表。
（1）创建存储过程stu_name，当任意输入一个学生的姓名时，查看其课程的最高分、最低分、平均分。
（2）调用存储过程stu_name。
（3）删除存储过程stu_name。
4. 使用studentsdb数据库中的grade表。
（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生选修课程的门数。
（2）执行存储过程stu_g_r，输入学号0002。
（3）显示0002号学生的选课门数。
5. 使用studentsdb数据库中的curriculum表、grade表。
（1）创建一个存储函数num_func，统计指定课程名称的选课人数。
（2）执行存储函数num_func，查看“C语言程序设计”选课人数。
6. 使用studentsdb数据库中的curriculum表、grade表。
（1）创建一个存储函数avg_func，通过游标统计指定课程的平均分。
（2）执行存储函数avg_func，查看“C语言程序设计”课程平均分。
（3）删除存储函数avg_func。
三、实验器材：
微机+操作系统+MySQL5.7。
四、实验步骤：
1.启动MySQL5.7的MySQL5.7command line client 平台打开数据库studentsdb。
2.创建存储过程和函数，并执行存储过程和函数。
五、实验结果：
2、
(1)CREATE PROCEDURE stu_grade()
SELECT姓名,课程名称,分数 FROM student_info s, grade g, curriculun c
Where s.学号=g.学号
And g.课程编号=c.课程编号AND s.学号=’001’;
END;
(2)CALL stu_grade()
3、(1)
CREATE PROCEDURE stu_name(INt name CHAR(8))
SELECT姓名,MAX(分数)最高分,MIN(分数)最低分,AVG(分数)平均分
FROM student_info s, grade g, curriculum c
WHERE S.学号=g.学号AND g.课程编号=c.课程编号AND姓名=nane
eND;
(2)
Call stu_name(’张青平’);
(3)DROP PROCEDURE stu_name
4、(1)CREATE PROCEDURE stu_g_r(INt cno CHAR(4), OUT num INT)
BEGIN
SELECT count(*) INTO num FROM grade WHERE课程编号=cno;
End;
(2)CALL stu g r('0002,(num)
(3)
SELECT @num;
5、(1)CREATE FUNCTION num_func( cname VARCHAR(50))
RETURNS INT
BEGIN
DECLARE nun INT:
SELECT COUNT(*) INTO num FROM grade g, curriculum c
WHERE g.课程编号=c.课程编号AND课程名称=cname
RETURN num;
(2)SELECT nun fund(O语言程序设计)
6、DELIMITER (
CREATE FUNCTION avg_func( cname VARCHAR(50))
RETURNS DECIMAL
BEGIN
DECLARE v_avg DECIMAL
DECLARE avg_cur CURSOR FOR SELECT avg(br W) EROM grade g, curriculum c
WhERE g.课程编号=c.课程编号 aND课程名称=cname;
CLOSE avg_cur;
Return v_avg;
End;
(2)
SELECT avg_fund(“C语言程序设计”) 课程平均分
(3)
Drop fUNCTION avg_fur;
六、实验小结：
实验六 数据库的安全管理
一、实验项目和要求：
1. 掌握用户账号的创建、查看、修改、删除的方法。
2. 掌握用户权限设置方法。
3. 掌握角色的创建、删除方法。
二、实验内容：
1. 在本地主机创建用户账号st_01，密码为123456。
2. 查看MySQL下所有用户账号列表。
3. 修改用户账号st_01的密码为111111。
4. 使用studentsdb数据库中的student_info表。
（1）授予用户账号st_01查询表的权限。
（2）授予用户账号st_01更新家庭住址列的权限。
（3）授予用户账号st_01修改表结构的权限。
5. 使用studentsdb数据库中的student_info表。
（1）创建存储过程cn_proc，统计student_info表中的学生人数。
（2）授予用户账号st_01调用cn_proc存储过程的权限。
（3）以用户账号st_01连接MySQL服务器，调用cn_proc存储过程查看学生人数。
6. 使用studentsdb数据库。
（1）授予用户账号st_01在studentsdb数据库上创建表、删除表、查询数据、插入数据的权限。
（2）以用户账号st_01连接MySQL服务器，创建新表st_copy，与表student_info完全相同。
（3）以用户账号st_01连接MySQL服务器，删除表st_copy。
7. 撤消用户账号st_01在studentsdb数据库上创建表、删除表、查询数据、插入数据的权限。
8. 撤消用户账号st_01所有权限.
9. 使用studentsdb数据库中的student_info表。
（1）创建本地机角色student。
（2）授予角色student查询student_info表的权限。
（3）创建本地机用户账号st_02，密码为123。
（4）授予用户账号st_02角色student的权限。
（5）以用户账号st_02连接MySQL服务器，查看student_info表信息。
（6）撤消用户账号st_02角色student的权限。
（7）删除角色student。
10.删除用户账号st_01、st_02。
三、实验器材：
微机+操作系统+MySQL5.7。
四、实验步骤：
1.启动MySQL5.7的MySQL5.7command line client 平台，创建用户和角色；
2.打开数据库studentsdb，为用户和角色授予权限，以及收回权限；
2.删除用户和角色。
五、实验结果：
1、CREATE USER st_ o1 @localhost IDENTIFIED BY ‘123456’；
2、SELECT* FROM user;
3、SET PASSWORD FOR st o1(@localhost=‘111111.’
4、（1）GRANT SELECT ON TABLE studentsdb s tudent _info To st o1@localhost:
（2）GRANT UPDATE(家庭住址) ON TABLE studentsdb.student_info to
S_t01@1ocalhost
（3）GRANT ALTER ON TABLE studentsdb.student info TO st _01@localhost
六、实验小结：
实验七 数据库的恢复与备份
一、实验目的和要求
1. 了解备份和恢复的基本概念。
2. 掌握使用MySQL命令进行数据库备份的操作方法。
3. 掌握使用MySQL命令进行数据库恢复的操作方法。
二、实验内容：
1. 使用mysqldump命令备份数据库studentsdb的所有表，存于D:\下，文件名为all_tables.sql。
2. 在MySQL服务器上创建数据库student1，使用mysql命令将备份文件all_tables.sql恢复到数据库student1中。
3. 使用mysqldump命令备份数据库studentsdb的所有表，存于D:\下，文件名为s_c.sql。
4. 在MySQL服务器上创建数据库student2，使用mysql命令将备份文件s_c.sql恢复到数据库student2中。
5. 使用mysqldump命令将 studentsdb数据库中的grade表中的记录导出到文本文件。
6. 删除数据库student1的grade表中全部记录。
使用mysqlimport命令将grade.txt文件中的数据导入到student1的grade表中。
7. 使用SELECT…INTO OUTFILE语句备份studentsdb数据库中的curriculum表的数据到文本文件c.txt。要求字段之间用“｜”隔开，字符型数据用双引号括起来。
8. 删除数据库student1的curriculum表中全部记录。
使用LOAD DATA INFILE语句将c.txt文件中的数据导入到student1的curriculum表中。
9.使用二进制日志恢复数据库。
（1）完全备份数据库。使用mysqldump命令备份所有数据库到D:\的all_db.sql。
（2）删除studentsdb数据库student_info表的所有记录。
（3）使用mysqladmin进行增量备份。
三、实验器材：
微机+操作系统+MySQL5.7。
四、实验步骤：
启动MySQL5.7的MySQL5.7command line client 平台，对数据库进行备份和恢复；对数据库进行数据导入和导出。
五、实验结果：
六、实验小结：
