DB_lab_code

数据库实验一相关代码

CREATE DATABASE studentdb;-- 建库

USE studentdb;-- 选择数据库

-- 建表
CREATE TABLE student_info
(学号 char(4) not null primary key,姓名 char(8) not null ,性别 char(2) ,出生日期 date,家族住址 varchar(50));

 create table curriculum 
 (课程编号 char(4) primary key not null,课程名称 varchar(50),学分 int);

 create table grade 
 (学号 char(4) not null ,课程编号 char(4) not null ,分数 int ,constraint primary key(学号,课程编号));

 -- 插入数据
 insert into student_info values('0001','张阳平','男','2000-10-01','衡阳市东风路77号');
 insert into student_info values('0002','刘东阳','男','1998-12-09','东阳市八一北路33号');
 insert into student_info values('0003','马晓夏','女','1995-05-12','长岭市五一路763号');
 insert into student_info values('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号');
 insert into student_info values('0005','孙海洋','男','1995-04-03','长岛市解放路27号');
 insert into student_info values('0006','郭小斌','男','1997-11-10','南山市红旗路13号');
 insert into student_info values('0007','肖月玲','女','1996-12-07','东方市南京路11号');
 insert into student_info values('0008','张玲珑','女','1997-12-24','滨江市新建路97号');

 insert into curriculum values('0001','计算机应用基础',2);
 insert into curriculum values('0002','C语言课程设计',2);
 insert into curriculum values('0003','数据库原理及应用',2);
 insert into curriculum values('0004','英语',4);
 insert into curriculum values('0005','高等数学',4);

 insert into grade values('0001','0001',80);
 insert into grade values('0001','0002',91);
 insert into grade values('0001','0003',88);
 insert into grade values('0001','0004',85);
 insert into grade values('0001','0005',77);
 insert into grade values('0002','0001',73);
 insert into grade values('0002','0002',68);
 insert into grade values('0002','0003',80);
 insert into grade values('0002','0004',79);
 insert into grade values('0002','0005',73);
 insert into grade values('0003','0001',84);
 insert into grade values('0003','0002',92);
 insert into grade values('0003','0003',81);
 insert into grade values('0003','0004',82);
 insert into grade values('0003','0005',75);

 -- 5. 使用SQL语句ALTER TABLE修改curriculum表的“课程名称”列，使之为空。
 alter table curriculum modify 课程名称 varchar(50) null;
-- 6. 使用SQL语句ALTER TABLE修改grade表的“分数”列，使其数据类型为decimal(5,2)。
alter table grade modify 分数 decimal(5,2);
-- 7. 使用SQL语句ALTER TABLE为student_info表添加一个名为“备注”的数据列，其数据类型为varchar(50)。
alter table student_info add 备注 varchar(50);
-- 8. 使用SQL语句创建数据库studb，并在此数据库下创建表stu，表结构与数据与studentsdb的student_info表相同。
-- 创建数据库
create database studb;
-- 建表stu
create table stu
(学号 char(4) not null primary key,
 姓名 char(8) not null ,
 性别 char(2) ,
 出生日期 date,
 家族住址 varchar(50),
);
-- 插入数据
 insert into stu values('0001','张阳平','男','2000-10-01','衡阳市东风路77号');
 insert into stu values('0002','刘东阳','男','1998-12-09','东阳市八一北路33号');
 insert into stu values('0003','马晓夏','女','1995-05-12','长岭市五一路763号');
 insert into stu values('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号');
 insert into stu values('0005','孙海洋','男','1995-04-03','长岛市解放路27号');
 insert into stu values('0006','郭小斌','男','1997-11-10','南山市红旗路13号');
 insert into stu values('0007','肖月玲','女','1996-12-07','东方市南京路11号');
 insert into stu values('0008','张玲珑','女','1997-12-24','滨江市新建路97号');

 alter table stu add 备注 varchar(50);

-- 9. 使用SQL语句删除表stu中学号为0004的记录。
delete from stu where 学号='0004';
-- 10.使用SQL语句更新表stu中学号为0002的家庭住址为“滨江市新建路96号”。
update stu set 家族住址='滨江市新建路96号' where 学号='0002';
-- 11.删除表stud的“备注”列。
alter table stu drop column 备注;
-- 12.删除表stud。
drop table stu;
-- 13.删除数据库studb。
drop database studb;

实验二代码

一、 实验项目名称

实验二 数据查询

二、 实验内容

\1. 在studentsdb数据库中使用SELECT语句进行基本查询。

（1）在student_info表中，查询每个学生的学号、姓名、出生日期信息。

SELECT 学号,姓名,出生日期 FROM STUDENT_INFO;

（2）查询student_info表学号为 0002的学生的姓名和家庭住址。

SELECT 姓名,家族住址 FROM STUDENT_INFO 
WHERE 学号='0002';

（3）查询student_info表所有出生日期在95年以后的女同学的姓名和出生日期。

SELECT 姓名,出生日期 FROM STUDENT_INFO
WHERE 性别='女' AND 出生日期>='1995-01-01';

-- 日期的查询,疑问

\2. 使用select语句进行条件查询。

（1）在grade表中查询分数在70-80范围内的学生的学号、课程编号和成绩。

SELECT 学号,课程编号,分数 AS 成绩 FROM GRADE
WHERE 分数>=70 AND 分数<=80;

（2）在grade表中查询课程编号为0002的学生的平均成绩。

SELECT AVG(分数) FROM GRADE
WHERE 课程编号='0002';

（3）在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数。

SELECT COUNT(学号),COUNT(分数) FROM GRADE
WHERE 课程编号='0003';

-- 疑问,这两个数有区别吗?

（4）查询student_info的姓名和出生日期，查询结果按出生日期从大到小排序。

SELECT 姓名,出生日期 FROM STUDENT_INFO
ORDER BY 出生日期 DESC;

（5）查询所有姓名“张”的学生的学号和姓名。

SELECT 学号,姓名 FROM STUDENT_INFO
WHERE 姓名 like '张%';

\3. 对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，

查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序 .

SELECT 学号,姓名,性别,出生日期,家族住址 FROM STUDENT_INFO
ORDER BY 性别 ASC,学号 DESC;

\4. 使用GROUP BY子句查询grade表中各个学生的平均成绩。

SELECT 学号,AVG(分数) FROM GRADE 
GROUP BY 学号;

\5. 使用UNION运算符针student_info表中姓“刘”的学生的学号、姓名与姓“张”的学生的学号、姓名返回在一个表中。

(SELECT 学号,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '刘%')
UNION
(SELECT 学号,姓名 FROM STUDENT_INFO WHERE 姓名 LIKE '张%')

\6. 嵌套查询

（1）在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。

SELECT 姓名,出生日期 FROM STUDENT_INFO
WHERE 性别=
(SELECT 性别 FROM STUDENT_INFO WHERE 姓名='刘东阳');

（2）使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。

-- 感觉题目理解会有歧义,这种是二者只要学了一样就会查询出来
SELECT DISTINCT S.学号,姓名,性别 FROM STUDENT_INFO S
WHERE S.学号 IN
(SELECT DISTINCT G.学号 FROM GRADE G
 WHERE 课程编号='0002' OR 课程编号='0005')
 
-- 这种是0002与0005都学了的会查询出,但也不排斥其学了别的课程 
 SELECT DISTINCT S.学号,姓名,性别 FROM STUDENT_INFO S
WHERE S.学号 IN
(SELECT DISTINCT G1.学号 FROM GRADE G1,GRADE G2
 WHERE G1.课程编号='0002' AND G2.课程编号='0005'AND G1.学号=G2.学号)
 
 -- 想知道如何写出只学了0002和0005的课程而没有学别的课程.
 select 学号, 姓名, 性别from student_info s
where exists
	(select * from grade g1 join grade g2 on g1.`学号`=g2.`学号` where g1.`课程编号`='0002' and g2.`课程编号`='0005' and g1.`学号`= s.`学号`) 
	and not exists
		(select * from grade g3 where g3.`学号`=s.`学号` and 
	g3.`课程编号` in (select distinct(g4.`课程编号`) from grade g4 
			where g4.`课程编号`!='0002' and g4.`课程编号`!='0005'));

（3）使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。

SELECT 课程编号 ,分数 FROM GRADE 
WHERE 学号='0001' AND 分数 >some
(SELECT 分数 FROM GRADE WHERE 学号='0002');

（4）使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。

SELECT 课程编号 ,分数 FROM GRADE
WHERE 学号='0001' AND 分数 >ALL
(SELECT 分数 FROM GRADE WHERE 学号='0002');

\7. 连接查询

（1）查询分数在80-90范围内的学生的学号、姓名、分数。

SELECT DISTINCT S.学号,姓名,分数 FROM STUDENT_INFO S,GRADE G
WHERE S.学号=G.学号 AND 分数 BETWEEN 80 AND 90;

（2）使用INNER JOIN连接方式查询学习“数据库原理及应用”课程的学生学号、姓名、分数。

-- INNER JOIN 查询连接满足条件的元组
SELECT STUDENT_INFO.学号, 姓名,分数 FROM  GRADE
INNER JOIN STUDENT_INFO  ON STUDENT_INFO.学号=GRADE.学号
INNER JOIN CURRICULUM ON GRADE.课程编号=CURRICULUM.课程编号 
AND CURRICULUM.课程名称='数据库原理及应用';

（3）查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。

-- 求每个人的,用groupby
SELECT S.学号,姓名,MAX(分数)  FROM STUDENT_INFO S,GRADE G
WHERE S.学号=G.学号 GROUP BY S.学号;

（4）使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。

SELECT S.学号,姓名,SUM(分数) AS 总成绩 FROM STUDENT_INFO S
LEFT OUTER JOIN GRADE ON GRADE.学号=S.学号
GROUP BY 学号

-- 疑问:为什么把group by去除之后,只能查到一行.左外连接不是左表全部会存在吗?

（5）为grade表添加数据行：学号为0004、课程编号为0006、分数为76。

使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。

INSERT INTO GRADE VALUES('0004','0006',76);
SELECT GRADE.课程编号,课程名称,COUNT(学号) AS 选修人数 FROM CURRICULUM
RIGHT OUTER JOIN GRADE ON GRADE.课程编号=CURRICULUM.课程编号
GROUP BY 课程编号;

-- 疑问:去除on后面的条件之后就报错了.

实验三 代码 索引与视图

\1. 使用SQL语句ALTER TABLE分别删除studentsdb数据库的student_info表、grade表、curriculum表的主键索引。

ALTER TABLE STUDENT_INFO DROP PRIMARY KEY;
ALTER TABLE GRADE DROP PRIMARY KEY;
ALTER TABLE CURRICULUM DROP PRIMARY KRY;

\2. 使用SQL语句为curriculum表的课程编号创建唯一性索引，命名为cno_idx。

CREATE UNIQUE INDEX CNO_IDX ON CURRICULUM (课程编号);

\3. 使用SQL语句为grade表的“分数”字段创建一个普通索引，命名为grade_idx。

CREATE INDEX GRADE_IDX ON GRADE (分数);

\4. 使用SQL语句为grade表的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_sid_cid_idx。

CREATE INDEX GRADE_SID_CID_IDX ON GRADE (学号,课程编号);

\5. 查看grade表上的索引信息。

SHOW INDEX FROM GRADE；

\6. 使用SQL语句删除索引grade_idx。再次查看grade表上的索引信息。

DROP INDEX GRADE_IDX ON GRADE;
SHOW INDEX FROM GRADE;

\7. 使用SQL语句CREATE VIEW建立一个名为v_stu_c的视图，显示学生的学号、姓名、所学课程的课

程编号，并利用视图查询学号为0003的学生情况。

CREATE VIEW V_STU_C AS
SELECT S.学号,姓名,课程编号 FROM STUDENT_INFO S,GRADE G
WHERE S.学号=G.学号;

SELECT * FROM V_STU WHERE 学号='0003';

\8. 基于student_info表、curriculum表和grade表，建立一个名为v_stu_g的视图，视图包括所有学

生的学号、姓名、课程名称、分数。使用视图v_stu_g查询学号为0001的学生的课程平均分。

CREATE VIEW V_STU_G AS
SELECT S.学号,S.姓名, C.课程名称 ,G.分数 
FROM STUDENT_INFO S,CURRICULUM C,GRADE G
WHERE S.学号=G.学号 AND G.课程编号=C.课程编号;

SELECT AVG(分数) FROM V_STU_G
WHERE 学号='0001';

\9. 使用SQL语句修改视图v_stu_g，显示学生的学号、姓名、性别。

ALTER VIEW V_STU_G AS
SELECT 学号,姓名,性别 FROM STUDENT_INFO;

10.利用视图v_stu_g为student_info表添加一行数据：学号为0010、姓名为陈婷婷、性别为女。

INSERT INTO V_STU_G VALUES('0010','陈婷婷','女');

11.利用视图v_stu_g删除学号为0010的学生记录。

DELETE FROM V_STU_G WHERE 学号='0100';

12.利用视图v_stu_g修改姓名为张青平的学生的高等数学的分数为87。

CREATE OR REPLACE VIEW V_STU_G AS
SELECT S.学号,S.姓名, C.课程名称 ,G.分数 
FROM STUDENT_INFO S,CURRICULUM C,GRADE G
WHERE S.学号=G.学号 AND G.课程编号=C.课程编号;

UPDATE  V_STU_G 
SET 分数=87
WHERE 姓名='张青平' AND 课程名称='高等数学';

13.使用SQL语句删除视图v_stu_c和v_stu_g。

DROP  VIEW V_STU_C,V_STU_G;

实验四代码 完整性

\1. 创建students数据库，在该数据库下创建表stu，并同时创建约束，表结构及约束要求如表1所示。

表1 stu的表结构

字段	类型	是否为空	约 束
学号	char(4)	否	主键
姓名	char(8)	是
性别	char(2)	是
出生日期	date	是

CREATE DATABASE STUDENTS;
USE STUDENTS;
CREATE TABLE STU (
    学号 CHAR(4) NOT NULL PRIMARY KEY,
    姓名 CHAR(8) NULL,
    性别 CHAR(2) NULL,
    出生日期 DATE NULL
);

\2. 创建表sc，并同时创建约束，表结构及约束要求如表2所示。

表2 sc的表结构

字段	类型	是否为空	约 束
学号	char(4)	否	外键参照stu表的学号列（约束名fk_sno)
课号	char(4)	否
成绩	decimal(5,2)	是	0≦成绩≦100

设置（学号，课号）为主键。

CREATE TABLE SC (
学号 CHAR(4) NOT NULL ,
    课号 CHAR(4) NOT NULL,
    成绩 DECIMAL(5,2) NULL CHECK(0<=成绩<=100),
    CONSTRAINT FK_SNO FOREIGN KEY(学号) REFERENCES STU(学号)
);

\3. 创建表course，并同时创建约束，表结构及约束要求如表3所示。

表3 course的表结构

字段	类型	是否为空	约 束
课号	char(4)	否
课名	char(20)	是	唯一约束（约束名uq_cname)
学分	int	是

CREATE TABLE COURSE(
课号 CHAR(4) NOT NULL,
    课名 CHAR(20) NULL ,
    学分 INT NULL,
    CONSTRAINT UQ_CNAME UNIQUE(课名)
)

\4. 在course表的课号列建立主键约束。

ALTER TABLE COURSE ADD PRIMARY KEY(课号);

\5. 在sc表的课号列建立外键约束fk_cno，参照course表的课号列的取值，要求实现级联更新。

ALTER TABLE SC ADD CONSTRAINT FK_CNO 
FOREIGN KEY(课号) REFERENCES COURSE(课号) ON UPDATE CASCADE;

\6. 在stu表的姓名列建立唯一约束名uq_sname。

ALTER TABLE STU ADD CONSTRAINT UQ_SNAME UNIQUE(姓名);

\7. 在course表的学分列建立检查约束ck_xf，检查条件为学分>0。

ALTER TABLE COURSE ADD CONSTRAINT CK_XF 
CHECK(学分>0);

\8. 删除sc表的外键约束fk_cno，fk_sno。

ALTER TABLE SC DROP CONSTRAINT FK_CNO;
ALTER TABLE SC DROP CONSTRAINT FK_SNO;

\9. 删除stu表的主键约束。

ALTER TABLE STU DROP PRIMARY KEY;

10.删除course表的唯一约束uq_cname。

ALTER TABLE COURSE DROP INDEX UQ_CNAME

11.创建测试表test，包含一个字段date_time，字段类型varchar(50)；

创建触发器test_trig，实现在stu表中每插入一条学生记录后，则自动在test表中追加一条插入成功时的日期时间。SYSDATE()函数用来获取当前的日期和时间。

为stu表插入一条记录引发触发器，查看test表的内容。

CREATE TABLE TEST(
DATE_TIME VARCHAR(50)
)
-- 触发器
CREATE TRIGGER TEST_TRIG AFTER INSERT ON STU
FOR EACH ROW
INSERT INTO TEST VALUES(SYSDATE());

-- 测试
INSERT INTO STU VALUES('1000','张三','男','1999-01-02');

SELECT * FROM TEST;

12.在course表上创建触发器del_trig，当course表上删除一门课程时，级联删除sc表该课程的记录。

删除course表的一条记录，查看sc表相应记录是否被自动删除。

CREATE TRIGGER DEL_TRIG AFTER DELETE ON COURSE
FOR EACH ROW 
DELETE FROM SC WHERE SC.课号=OLD.课号;

-- 测试
DELETE FROM COURSE WHERE 课号='1';

SELECT * FROM SC;

实验五 存储过程和存储函数

二、 实验内容

\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。

-- 下面这种解答会出现错误
-- [Err] 1172 - Result consisted of more than one row
-- select into 只给一个赋值
DELIMITER@@
CREATE PROCEDURE  STU_GRADE(
OUT NAME VARCHAR(8),
OUT CNAME VARCHAR(8),
OUT SCORE INTEGER(3)
)
BEGIN 
SELECT 姓名,课程名称,分数 INTO NAME, CNAME,SCORE 
FROM STUDENT_INFO S,GRADE G,CURRICULUM C
WHERE S.学号='0001' AND S.学号=G.学号 AND G.课程编号=C.课程编号;
END@@

DELIMITER;
CALL STU_GRADE(@NAME,@CNAME,@SCORE);
SELECT @NAME,@CNAME,@SCORE;

-- 正解
DELIMITER@@
CREATE PROCEDURE  STU_GRADE(
)
BEGIN 
SELECT 姓名,课程名称,分数
FROM STUDENT_INFO S,GRADE G,CURRICULUM C
WHERE S.学号='0001' AND S.学号=G.学号 AND G.课程编号=C.课程编号;
END@@

DELIMITER;
CALL STU_GRADE();

\3. 使用studentsdb数据库中的student_info表、curriculum表、grade表。

（1）创建存储过程stu_name，当任意输入一个学生的姓名时，查看其课程的最高分、最低分、平均分。

（2）调用存储过程stu_name。

（3）删除存储过程stu_name。

DELIMITER@@
CREATE PROCEDURE STU_NAME(
IN SNAME VARCHAR(8)
)
BEGIN
SELECT 姓名,MAX(分数) 最高分,MIN(分数) 最低分,AVG(分数) 平均分
FROM STUDENT_INFO S,CURRICULUM C,GRADE G
WHERE S.姓名=SNAME AND S.学号=G.学号 ;
END@@
-- 注意语句的分号结束
DELIMITER;

CALL STU_NAME('刘东阳');
DROP PROCEDURE STU_NAME;

\4. 使用studentsdb数据库中的grade表。

（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生选修课程的门数。

（2）执行存储过程stu_g_r，输入学号0002。

（3）显示0002号学生的选课门数。

DELIMITER@@
CREATE PROCEDURE STU_G_R(
IN SNO VARCHAR(6),
    OUT NUM INTEGER(2)
)
BEGIN
SELECT COUNT(课程编号) 课程门数 INTO NUM FROM GRADE G
WHERE 学号=SNO;
END@@

DELIMITER;
CALL STU_G_R('0002',@num);
SELECT @NUM;

\5. 使用studentsdb数据库中的curriculum表、grade表。

（1）创建一个存储函数num_func，统计指定课程名称的选课人数。

（2）执行存储函数num_func，查看“C语言程序设计”选课人数。

-- 正确
DELIMITER @@
CREATE FUNCTION NUM_FUNC(CNAME VARCHAR(20))
RETURNS INT
BEGIN
RETURN (SELECT COUNT(学号) FROM GRADE G,CURRICULUM C
WHERE 课程名称=CNAME AND C.课程编号=G.课程编号);
END@@

SELECT NUM_FUNC('C语言课程设计');

-- 错在哪了？？？
DELIMITER @@
CREATE FUNCTION NUM_FUNC(CNAME VARCHAR(20))
RETURN INT

BEGIN
DECLEAR X INT;
SELECT COUNT(学号) INTO X FROM GRADE G,CURRICULUM C
WHERE 课程名称=CNAME AND C.课程编号=G.课程编号;
RETURN X;
END@@

SELECT NUM_FUNC('C语言程序设计');

\6. 使用studentsdb数据库中的curriculum表、grade表。

（1）创建一个存储函数avg_func，通过游标统计指定课程的平均分。

（2）执行存储函数avg_func，查看“C语言程序设计”课程平均分。

（3）删除存储函数avg_func。

-- 游标的使用注意要声明，打开，赋值，关闭
DELIMITER@@
CREATE FUNCTION AVG_FUNC(CNAME VARCHAR(20))
RETURNS INT 

BEGIN
DECLARE AVG_SCORE INT;--声明变量
DECLARE AVG_CURSOR CURSOR FOR 
SELECT AVG(分数) 平均分 FROM GRADE G,CURRICULUM C
WHERE 课程名称=CNAME AND C.课程编号=G.课程编号;
OPEN AVG_CURSOR;
FETCH AVG_CURSOR INTO AVG_SCORE;
CLOSE AVG_CURSOR;
RETURN AVG_SCORE;
END@@

DELIMITER;
--CALL AVG_FUNC('C语言程序设计'); 注意过程调用与函数调用的区别。
SELECT AVG_FUNC('C语言课程设计');

DROP FUNCTION AVG_FUNC;--删除存储函数

实验六 安全管理

\1. 在本地主机创建用户账号st_01，密码为123456。

CREATE USER st_01@localhost IDENTIFIED BY '123456';

\2. 查看MySQL下所有用户账号列表。

USE MYSQL;
SELECT * FROM user;

\3. 修改用户账号st_01的密码为111111。

SET PASSWORD FOR st_01@localhost='111111';

\4. 使用studentsdb数据库中的student_info表。

（1）授予用户账号st_01查询表的权限。

（2）授予用户账号st_01更新家庭住址列的权限。

（3）授予用户账号st_01修改表结构的权限。

GRANT SELECT ON TABLE STUDENTDB.STUDENT_INFO 
TO st_01@localhost ;

GRANT UPDATE(家族住址) ON TABLE STUDENT.STUDENT_INFO
TO st_01@localhost;

GRANT ALTER ON STUDENT.STUDENT_INFO
TO st_01@localhost;

\5. 使用studentsdb数据库中的student_info表。

（1）创建存储过程cn_proc，统计student_info表中的学生人数。

（2）授予用户账号st_01调用cn_proc存储过程的权限。

（3）以用户账号st_01连接MySQL服务器，调用cn_proc存储过程查看学生人数。

DELIMITER @@
CREATE PROCEDURE CN_PROC()
BEGIN
SELECT COUNT(*) FROM STUDENT_INFO;
END@@
DELIMITER;

GRANT EXECUTE ON PROCEDURE STUDENTDB.CN_PROC
TO st_01@localhost;

CALL CN_PROC();

\6. 使用studentsdb数据库。

（1）授予用户账号st_01在studentsdb数据库上创建表、删除表、查询数据、插入数据的权限。

（2）以用户账号st_01连接MySQL服务器，创建新表st_copy，与表student_info完全相同。

（3）以用户账号st_01连接MySQL服务器，删除表st_copy。

GRANT CREATE,DROP,SELECT,INSERT ON studentdb
TO st_01@localhost;

DROP TABLE ST_COPY;

\7. 撤消用户账号st_01在studentsdb数据库上创建表、删除表、查询数据、插入数据的权限。

REVOKE CREATE,DROP,SELECT,INSERT ON STUDENTDB.STUDENT_INFO
FROM st_01;

\8. 撤消用户账号st_01所有权限.

REVOKE ALL PRIVILEGES,GRANT OPTION
FROM st_01@localhost;

\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。

-- 这个需要数据库5.8版本之后才能进行验证，
-- 由于我的是5.6版本，因此下面语句没有进行验证
1.
use mysql;
CREATE ROLE 'student'@'localhost';
2.
grant select on studentdb.student_info
to 'student'@'localhost';
3.
create user st_02@localhost identied by '123';
4.
grant 'student'@'localhost' to st_02@localhost;

5.
select * from studentdb.student_info;
6.
revoke all privileges,grant option
from 'student'@'localhost';
7.
drop role 'student'@'localhost';

10.删除用户账号st_01、st_02。

drop user st_01@localhost,st_02@localhost;

实验七相关代码 备份与恢复

\1. 使用mysqldump命令备份数据库studentsdb的所有表，存于D:\下，文件名为all_tables.sql。

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqldump -u root -h localhost -p studentdb>D:\all_tables.sql

-- 注意如果C盘不能直接运行musqldump命令时，需要进入所安装的mysql的根目录下进行命令编写

补充：基本命令行操作

需要了解两个命令
盘符:例如想进入D盘d:
cd进入到当前盘某个目录。
cd \进入当前盘根目录.
cd \windows进入到当前盘Windows目录
cd..退出到上一 级目录
注:进入含有特殊字符目录时需要加引号例如cd "c:\program files"

注意：进入某目录时cd后面有一个空格

\2. 在MySQL服务器上创建数据库student1，使用mysql命令将备份文件all_tables.sql恢复到数据库student1中。

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysql -u root -p student1 < d:\all_tables.sql

\3. 使用mysqldump命令备份数据库studentsdb的所有表，存于D:\下，文件名为s_c.sql。

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqldump -u root -h localhost -p studentdb>D:\s_c.sql

\4. 在MySQL服务器上创建数据库student2，使用mysql命令将备份文件s_c.sql恢复到数据库student2中。

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysql -u root -p student2<d:\s_c.sql

\5. 使用mysqldump命令将 studentsdb数据库中的grade表中的记录导出到文本文件。

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqldump -u root -p -T "D:/" studentdb grade --lines-terminated-by=\r\n

\6. 删除数据库student1的grade表中全部记录。

使用mysqlimport命令将grade.txt文件中的数据导入到student1的grade表中。

C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqlimport -u root -p student1 "D:/grade.txt" --lines-terminated-by=\r\n

\7. 使用SELECT…INTO OUTFILE语句备份studentsdb数据库中的curriculum表的数据到文本文件c.txt。要求字段之间用“｜”隔开，字符型数据用双引号括起来。

USE studentdb;

select * from curriculum 
into outfile 'D:/c.txt' 
fields terminated by '|' 
optionally enclosed by '"' 
lines terminated by '\r\n'

\8. 删除数据库student1的curriculum表中全部记录。

使用LOAD DATA INFILE语句将c.txt文件中的数据导入到student1的curriculum表中。

use student1;

LOAD DATA INFILE 'D:/c.txt'
INTO TABLE student1.curriculum
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'

9.使用二进制日志恢复数据库。

（1）完全备份数据库。使用mysqldump命令备份所有数据库到D:\的all_db.sql。

（2）删除studentsdb数据库student_info表的所有记录。

（3）使用mysqladmin进行增量备份。

（4）使用mysql命令恢复all_db.sql文件的完全备份。

（5）使用mysqlbinlog命令恢复增量备份。

(1)C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqldump -u root -h localhost -p --single-transaction --flush-logs --master-data=2 --all-databases>D:\all_db.sql

(2)
use studentdb;
DELETE FROM student_info;

(3)C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysqladmin -u root -h localhost -p flush-logs

(4)C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin>mysql -u root -p<D:\all_db.sql

(5)