MySQL数据库-实验九数据库设计

爱喝矿泉水的猛男

已于 2024-02-20 21:43:44 修改

阅读量1.6k

点赞数 55

分类专栏： MySQL数据库实验文章标签：数据库 mysql

于 2024-02-19 13:38:29 首次发布

本文链接：https://blog.csdn.net/weixin_62858623/article/details/136168321

版权

MySQL数据库实验专栏收录该内容

9 篇文章 1 订阅

订阅专栏

（1）根据所选题目进行系统需求分析和系统设计，画出系统的 E-R 图，给出实体或联

系的属性，标明联系的种类；

（2）把E-R图转换为关系模式

（3）根据关系规范理论进行数据库的逻辑设计，给出数据库表的设计，数据库表设计

(4) 对给定的逻辑数据模型选取一个最适合应用环境的物理结构，进行数据库的物理

设计，考虑不同的 DBMS 选型，进行设计方案对比，对物理结构进行评价，评价的重点是

时间和空间效率；

（5）在 My SQL 数据库中创建数据库并使用 SQL 语句创建相应的数据库表；

(6) 通过导入文件的方式在数据库表中输入若干条测试数据，也可以直接插入若干条

（7）自行设计若干问题并编写 SQL 语句完成对所提问题的实现，SQL 语句要求至少

12 条，自定义功能要求，并对 SQL 语句运行，要求包括下面的语句：

（8）对于 select 语句，需要给出相应的数据查询结果的截图。

（9）对自定义的复杂的查询要求或复杂报表生成要求，以及复杂的自定义完整性要求，使用存储过程和触发器来实现，并进行实验验证，给出验证结果截图。

自问自答：

实验九数据库设计

一、实验目的

掌握数据库设计的过程和方法

二、实验内容和要求

每人从给出的题目中选择一个，进行数据库设计，通过网络平台或者图书馆查找相关文献进行需求分析，按照下面的实验步骤完成设计要求。

我选的题目要求是：

(5)在线考试系统设计

传统的纸质考试模式,从组卷到阅卷,教师工作量巨大,人为阅卷方式往往容易造成误判,

人为因素较大,给阅卷工作带来了不确定性，人工组卷容易造成试题片面性和题目的针对性

不足，人工评卷也不利于成绩的分析和统计。

针对当前培训考试中存在的问题,进行以下设计:1）开发一套能够实现自动判卷、成绩

统计分析的在线考试系统;2）在软件功能设计上,设计基础信息管理、题库信息管理、试卷

管理、账户管理等多个功能模块;3）针对考试试题的组卷上,给出自由组卷、随机组卷等多

种组卷模式;4）成绩统计分析模块,实现过程分为数据生成、图表构建以及图标展示。

三、实验步骤

（1）根据所选题目进行系统需求分析和系统设计，画出系统的 E-R 图，给出实体或联

系的属性，标明联系的种类；

（2）把E-R图转换为关系模式

（1）学生（学号，学生姓名，年级，学生手机号，学生身份证号，学生性别，班编号）

（2）教师（教师工号，教师姓名，教师手机号，教师身份证，教师性别）

（3）专业（专业编号，专业名称）

（4）班级（班编号，班名，学院名称，专业编号）

（5）课程（课程编号，课程名称）

（6）试卷（试卷编号，试卷名称，创建教师，创建时间，考试时间，课程编号）

（7）试题（试题编号，课程编号，试题名称，试题类型，试题题干，选项A,选项B，选项C，选项D，填空区，答案，解析）

（8）教师课程明细（教师工号，课程编号）

（9）试卷题目明细（试卷编号，试题编号，分值）

（10）学生试卷明细（学号，试卷编号，得分）

（11）学生试题明细（学号，试题编号，得分）

注释：标记下划线的是主键，字体加粗的是外键

（3）根据关系规范理论进行数据库的逻辑设计，给出数据库表的设计，数据库表设计

参照下表：

表1 ：数据库表概述

序号	中文表名	英文表名	表功能说明
1	学生信息表	Student	用于记录所有学生的基本信息
2	教师信息表	Teacher	记录所有教师的基本信息
3	专业表	Major	记录已存在的所有专业
4	班表	Class	记录现有的班级
5	课程表	Course	记录所有的已经开设的课程
6	试卷表	Papers	记录所有试卷的基本信息
7	试题表	Questions	记录所有的试题（试题库）
8	教师课程明细表	Teacher_courses	记录所有教师教授的课程
9	试卷题目明细表	Paper_questions	记录组成每套试卷的试题
10	学生试卷明细表	Student_papers	记录每个学生对应试卷的得分情况
11	学生试题明细表	Student_questions	记录每个学生对应试卷每个试题的得分情况

表2：学生信息表(Student)

序号	字段中文名	字段英文名	类型	约束	备注
1	学号	Sid	int	Primary key
2	学生姓名	Sname	Varchar(5)	Not null
3	年级	Sgrade	int	Not null
4	班级编号	Cid	Int	Foreign key	外键，参考班级表的Cid字段
5	学生性别	Ssex	Char(1)	Not null
6	学生手机号	Sphone	Char(11)	Unique,Not null
7	学生身份证号	Sident_number	Char(18)	Unique,Not null

表3：专业表（Major）

序号	字段中文名	字段英文名	类型	约束	备注
1	专业编号	Mid	Int	Primary key
2	专业名称	Mname	Varchar(20)	Unique,Not null

表4：班级表(Class)

序号	字段中文名	字段英文名	类型	约束	备注
1	班级编号	Cid	Int	Primary key
2	班名	Cname	Varchar(15)	not null
3	专业编号	Mid	Int	Foreign key	外键，参照专业表的mid字段
4	学院名称	College	Varchar(15)	Not null

表5：教师信息表(Teacher)

序号	字段中文名	字段英文名	类型	约束	备注
1	教师工号	Tid	Int	Primary key
2	教师姓名	Tname	Varchar(5)	Not null
3	教师性别	Tsex	Char(1)	Not null
4	教师手机号	Tphone	Char(11)	Unique,Not null
5	教师身份证号	Tident_number	Char(18)	Unique,Not null

表6：课程表(Course)

序号	字段中文名	字段英文名	类型	约束	备注
1	课程编号	Lid	Int	Primary key
2	课程名称	Lname	Varchar(15)	Unique,Not null

表7：试卷表(Papers)

序号	字段中文名	字段英文名	类型	约束	备注
1	试卷编号	Pid	Int	Primary key
2	试卷名称	Pname	Varchar(50)	Not null
3	创建教师编号	Tid	Int	Not null
4	创建时间	Ptime	Date	Not null
5	考试时间	Ptesttime	Date	Not null
6	课程编号	Lid	Int	Foreign key	外键，参照课程表中的Lid字段

表8：试题表(Questions)

序号	字段中文名	字段英文名	类型	约束	备注
1	试题编号	Qid	Int	Primary key
2	课程编号	Lid	int	Foreign key	参照Course表中Lid字段
3	试题名称	Qname	Varcahr(10)
4	试题类型	Qtype	Varchar(10)	Not null
5	试题题干	Qstems	Varchar(300)	Not null
6	选项A	Qa	Varchar(100)
7	选项B	Qb	Varchar(100)
8	选项C	Qc	Varchar(100)
9	选项D	Qd	Varhar(100)
10	填空区	Qblanks	Varchar(300)
11	答案	Qanswer	Varchar(300)	Not null
12	解析	Qparse	Varchar(300)

表9：教师课程明细表(Teacher_courses)

序号	字段中文名	字段英文名	类型	约束	备注
1	教师工号	Tid	Int	Foreign key	Tid外键参照教师表中Tid字段；Lid外键参照课程表中Lid字段；Tid 和 Lid 联合作为主键
2	课程编号	Lid	Int	Foreign key	Tid外键参照教师表中Tid字段；Lid外键参照课程表中Lid字段；Tid 和 Lid 联合作为主键

表10：试卷题目明细表(Paper_questions)

序号	字段中文名	字段英文名	类型	约束	备注
1	试卷编号	Pid	Int	Foreign key	Pid外键参照试卷表的Pid字段；Qid外键参照试题表的Qid字段；二者联合作为主键
2	试题编号	Qid	Int	Foreign key	Pid外键参照试卷表的Pid字段；Qid外键参照试题表的Qid字段；二者联合作为主键
3	分值	Scores	Int	Not null

表11：学生试卷明细表(Student_papers)

序号	字段中文名	字段英文名	类型	约束	备注
1	学号	Sid	Int	Foreign key	Sid外键参照学生信息表中的Sid字段；Pid参照试卷表中的Pid字段；二者联合作为主键
2	试卷编号	Pid	Int	Foreign key	Sid外键参照学生信息表中的Sid字段；Pid参照试卷表中的Pid字段；二者联合作为主键
3	得分	Pgrades	Int	Not null

表12：学生试题明细表(Student_questions)

序号	字段中文名	字段英文名	类型	约束	备注
1	学号	Sid	Int	Foreign key	Sid外键参照学生信息表中的Sid字段；Qid参照试题表中的Qid字段；二者联合作为主键
2	试题编号	Qid	Int	Foreign key	Sid外键参照学生信息表中的Sid字段；Qid参照试题表中的Qid字段；二者联合作为主键
3	得分	Qgrades	Int	Not null

(4) 对给定的逻辑数据模型选取一个最适合应用环境的物理结构，进行数据库的物理

设计，考虑不同的 DBMS 选型，进行设计方案对比，对物理结构进行评价，评价的重点是

时间和空间效率；

在设计在线考试系统的数据库物理结构时，需要考虑多个方面的因素。首先，数据量是一个重要的考虑因素。由于在线考试系统需要存储大量试题和答案数据，因此需要选择具备高性能和高扩展性的DBMS，以确保数据库能够处理大规模的数据处理任务。

其次，并发访问量也是一个重要的考虑因素。自动组卷阅卷系统需要支持多用户并发操作，因此需要选择具备高并发能力的DBMS，以确保数据库能够同时处理多个客户端请求，而不会出现瓶颈或性能问题。

第三，数据库安全性也是必须考虑的因素。自动组卷阅卷系统涉及到试题和答案等敏感数据，因此需要选择具备高安全性的DBMS，以确保数据库内容不会被未经授权的访问或篡改。

最后，查询效率是另一个必须优化的考虑因素。自动组卷阅卷系统需要频繁进行试题和答案的查询操作，因此需要选择具备高查询效率的DBMS，以确保数据库能够快速响应查询请求。

基于以上因素，MySQL和PostgreSQL都是适合用于在线考试系统的DBMS。这两种DBMS都具备高性能、高并发、高可靠性和高安全性等优点，适合应用于大规模数据处理和高并发访问的场景。由于我对于My_SQL比较熟悉，所以我选择的是My_SQL。

在进行MySQL和PostgreSQL的数据库物理设计时，可以采用多种策略来提高时间和空间效率，例如分库分表、索引优化、数据压缩和备份恢复等。其中，分库分表可以将试题和答案等数据按照业务特性进行分割，以提升数据库的并发处理能力和扩展性。索引优化可以针对常用的查询操作，建立合适的索引，以提升查询效率。数据压缩可以对较大的数据表采用数据压缩技术来减少存储空间占用。备份恢复可以定期进行数据库备份和恢复，以确保数据安全性和可靠性。

评价物理结构时，需要重点考虑时间和空间效率。针对时间效率，可以通过优化索引、查询语句和数据库参数等方式来提升数据库的查询和操作速度。针对空间效率，可以通过压缩数据和合理分配物理存储空间等方式来减少数据库的存储空间占用。同时，还需要考虑数据库的可靠性、安全性和容错能力等方面的指标，以确保数据库能够稳定运行并处理大量数据。

（5）在 My SQL 数据库中创建数据库并使用 SQL 语句创建相应的数据库表；

#创建学生信息表 

create table Student(

 Sid int ,

 Sname varchar(5)  not null,

 Sgrade int     not null,

 Cid int ,

 Ssex char(1) not null,

 Sphone char(11) unique  not null,

 Sident_number char(18) unique not null,

 primary key (Sid),

 foreign key (Cid) references  Class(Cid)

);



#创建专业表 

create table Major (

 Mid int ,

 Mname varchar(20) unique not null,

 primary key (Mid)

);



#创建班级表 

create table Class (

    Cid int ,

    Cnumber varchar(10) not null,

    Mid int ,

    College varchar(15),

    primary key  (Cid),
   foreign key (Mid) references Major(Mid)

);



#创建教师信息表  

create table Teacher(

    Tid int ,

    Tname varchar(5) not null,

    Tsex char(1) not null,

    Tphone char(11) unique not null,

    Tident_number char(18) unique not null,

    primary key  (Tid)

);



#创建课程表  

create table Course(

    Lid int ,

    Lname varchar(15) unique not null,

    primary key (Lid)

);

#创建试卷表 

create table Papers(

    Pid int ,

    Pname varchar(50) not null,

    Tid int not null,

    Ptime date not null,

    Ptesttime date not null,

    Lid int ,

    primary key (Pid),

    foreign key (Lid) references Course(Lid)

);



#创建试题表 

create table Questions (

    Qid int ,

    Qname varchar(10),

    Qtype varchar(10) not null,

    Qstems varchar(300) not null,

    Qa varchar(100),

    Qb varchar(100),

    Qc varchar(100),

    Qd varchar(100),

    Qblanks varchar(300),

    Qanswer varchar(300) not null,

    Qparse varchar(300),

    primary key  (Qid)

);



#创建教师课程明细表

create table Teacher_courses(

 Tid int ,

 Lid int,



foreign key (Tid) references Teacher(Tid),

foreign key (Lid) references Course(Lid),

primary key (Tid,Lid)

);



#创建试卷题目明细表

create table Paper_questions(

    Pid int ,

    Qid int ,

    Scores int not null,

    foreign key (Pid) references Papers(Pid),

    foreign key (Qid) references Questions(Qid),

    primary key (Pid,Qid)

);



#学生试卷明细表

create table Student_papers(

    Sid int ,

    Pid int ,

    Pgrades int not null,

    primary key (Sid,Pid),

    foreign key (Sid) references Student(Sid),

    foreign key (Pid) references Papers(Pid)

);



#学生试题明细表

create table Student_questions(

    Sid int ,

    Qid int,

    Qgrades int  not null,

    foreign key (Sid) references Student(Sid),



    foreign key(Qid) references Questions(Qid),

    primary key(Sid,Qid)

);

(6) 通过导入文件的方式在数据库表中输入若干条测试数据，也可以直接插入若干条

测试数据；（注释：我使用的数据来自于两个方面：一是依靠python连接数据库依赖faker代码随机生成的符合约束条件的信息直接插入到数据库表中；二是一些数据自己根据实际情况将数据填写到.txt文档中，然后准换成.csv格式的文件进一步导入到数据库中。）

每个表的部分信息情况如下：

（这些数据能够支撑我完成后面的实验所以我没有过多的追求数据量）

Student:

Class:

Course:

Major:

Papers:

Questions:

Teacher:

Paper_questions:

Student_papers:

Student_questions:

Teacher_course:

（7）自行设计若干问题并编写 SQL 语句完成对所提问题的实现，SQL 语句要求至少

12 条，自定义功能要求，并对 SQL 语句运行，要求包括下面的语句：

create table（要求包含实体完整性、参照完整性和用户自定义完整性的定义）、create

index、 create view、 select 语句 (至少 5 条，要求把 from、where、group by、having、order by 等子句用上，并实现多表查询)、insert、 delete、update、grant、revoke 语句

（8）对于 select 语句，需要给出相应的数据查询结果的截图。

（9）对自定义的复杂的查询要求或复杂报表生成要求，以及复杂的自定义完整性要求，使用存储过程和触发器来实现，并进行实验验证，给出验证结果截图。

自问自答：

（1）查询各个班级的数据结构绪论章节检测卷的平均成绩，并按照平均成绩降序排列：

select c.*,AVG(sp.Pgrades) as avgscore

from Class c,Student_papers sp,Student s

where c.Cid = s.Cid and sp.Sid = s.Sid and sp.Pid = 1

group by c.Cid

order by avgscore desc;

（2）查询每个老师所教授课程数量，并只显示教授课程数量等于3的教师信息；

select t.* ,count(tc.Lid) as course_number

from Teacher t,Teacher_courses tc

where t.Tid = tc.Tid

group by t.Tid,t.Tname

having  course_number=3；

（3）查询每个专业的学生人数，并只显示学生人数超过 50 人的专业并且按照降序显示：

select m.* ,count(s.Sid) as number

from Major m,Class c,Student s

where m.Mid = c.Mid and s.Cid = c.Cid

group by m.Mid

having number>50

order by number desc;


select m.* ,avg(linshi.avg_class) as avg_major

from

(select c.Cid,c.Mid,avg(sp.Pgrades) as avg_class

from Student_papers sp,Student s,Class c

where Pid = 1  and s.Cid = c.Cid  and sp.Sid = s.Sid

group by  c.Cid) as linshi,Major m

where linshi.Mid = m.Mid

group by m.Mid

having avg_major >40

order by avg_major desc;

（4）找出每个专业的编号为1的试卷的平均成绩且只显示平均成绩大于40的专业和平均分数，并按平均成绩降序排列：


select m.* ,avg(linshi.avg_class) as avg_major

from

(select c.Cid,c.Mid,avg(sp.Pgrades) as avg_class

from Student_papers sp,Student s,Class c

where Pid = 1  and s.Cid = c.Cid  and sp.Sid = s.Sid

group by  c.Cid) as linshi,Major m

where linshi.Mid = m.Mid

group by m.Mid

having avg_major >40

order by avg_major desc;

（5）查找所有教授数据结构这门课的教师，并显示教师的基本信息，并按照教师编号升序的形式显示：

select *

from teacher

where Tid in

      (select  Tid from Teacher_courses where Lid = (

          select Lid from Course where Lname = '数据结构'

          ))

order by Tid asc;

（6）对于 Student_papers 表的 Sid 字段创建索引：

对于 Course 表的 Lname 字段创建索引：

create index Sidindex on Student_papers(Sid);



create index Lnameindex on Course(Lname);

(7) 创建一个视图，显示每个老师所出试卷做题的学生人数和平均分：

CREATE VIEW Teacher_Paper_Stats AS

SELECT

    T.Tid AS Teacher_ID,

    T.Tname AS Teacher_Name,

    P.Pid AS Paper_ID,

    (SELECT COUNT(*) FROM Papers WHERE Tid = T.Tid) AS Total_Students,

    (SELECT AVG(Pgrades) FROM Student_papers WHERE Pid = P.Pid) AS Average_Grade

FROM Teacher T, Papers P;

(8)有以位新入职的教师，请插入她的信息，属性自定义：

insert into Teacher(Tid, Tname, Tsex, Tphone, Tident_number) values

    (9999,'梅艳芳','女',15130274355,130625478956485221);

查询验证：

select * from teacher where Tid =9999;

（9）假设我们要删除名为"相贵芳"的教师以及他所教授的所有课程、试卷和学生的试题情况，可以按照以下步骤进行操作：

-- 首先删除学生试题明细表中与该教师相关的数据

DELETE FROM Student_questions

WHERE Sid IN (SELECT Sid FROM Student_papers WHERE Pid IN (SELECT Pid FROM Papers WHERE Tid IN (SELECT Tid FROM Teacher WHERE Tname = '相贵芳')));



-- 然后删除学生试卷明细表中与该教师相关的数据

DELETE FROM Student_papers

WHERE Pid IN (SELECT Pid FROM Papers WHERE Tid IN (SELECT Tid FROM Teacher WHERE Tname = '相贵芳'));



-- 接着删除试卷表中与该教师相关的数据

DELETE FROM Papers

WHERE Tid IN (SELECT Tid FROM Teacher WHERE Tname = '相贵芳');



-- 然后删除教师课程明细表中与该教师相关的数据

DELETE FROM Teacher_courses

WHERE Tid IN (SELECT Tid FROM Teacher WHERE Tname = '相贵芳');



-- 最后删除教师表中的数据

DELETE FROM Teacher

WHERE Tname = '相贵芳';

查询验证：

select  * from teacher where Tname ='相贵芳' ;

（10）当学生试图加入一个班级时，检查该班级的人数是否已满，如果已满则拒绝加入。（触发器）

首先我查询了一下每个班的人数，选了人数最多的29级人数上限：

select c.*,count(s.Sid)

    from Class c,Student s

    where c.Cid = s.Cid

group by c.Cid;

CREATE TRIGGER before_insert_student

BEFORE INSERT ON Student

FOR EACH ROW

BEGIN

    DECLARE current_count INT;

    SELECT COUNT(*) INTO current_count FROM Student WHERE Cid = NEW.Cid;

    IF current_count >= 29THEN

        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '班级人数已满，无法加入';

    END IF;

END;
#验证

insert into student values(9999,'梅西',2023,2211,'男',15130274399,123456789101213149);

验证成功！

（11）定义一个存储过程 proc1，能够根据2023年(年份作为输入参数，整数)各个班所有试卷的综合平均分，把该年平均分超过某个阈值（该阈值为输入参数，为整数）的班表（Class）中增加新的属性备注（comment）字段且更新为优秀班级（“优秀班级”）（要求使用游标完成）。（存储过程！！！）



CREATE PROCEDURE proc1 (IN input_year INT, IN threshold_score INT)

BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE class_id INT;

    DECLARE paper_id INT;

    DECLARE avg_score FLOAT;



    -- 游标声明

    DECLARE cur CURSOR FOR

        SELECT distinct c.Cid, sp.Pid

        FROM Student_papers sp, Papers p ,Class c,Student s

        WHERE YEAR(p.Ptime) = input_year AND sp.Pid = p.Pid and sp.Sid =s.Sid and s.Cid = c.Cid;



    -- 游标结果集为空时设置done为1

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



    -- 创建临时表存储计算结果

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_table

    (

        Cid       INT,

        avg_score FLOAT

    );



    -- 打开游标

    OPEN cur;



    read_loop: LOOP

        -- 从游标中读取数据

        FETCH cur INTO class_id, paper_id;



        -- 如果游标结果集为空则退出循环

        IF done = 1 THEN

            LEAVE read_loop;

        END IF;

        -- 计算班级每套试卷的平均分

        SELECT AVG(Pgrades) INTO avg_score

        FROM Student_papers sp,Class c,Student s

        WHERE  sp.Sid = s.Sid and s.Cid = c.Cid and c.Cid = class_id

        group by c.Cid;



        -- 将计算结果插入临时表

        INSERT INTO temp_table (Cid,avg_score) VALUES (class_id, avg_score);

    END LOOP;



    -- 关闭游标

    CLOSE cur;

    -- 更新班级表中的备注字段

    UPDATE Class

    SET comment = '优秀班级'

    WHERE Cid IN (SELECT distinct  Cid FROM temp_table where temp_table.avg_score>threshold_score  );

    -- 查看临时表数据

    select distinct * from temp_table;



    -- 删除临时表

    DROP TEMPORARY TABLE IF EXISTS temp_table;



END ;

call proc1(2023,48);



select * from Class;

我设置时间为20123年，阈值为48分，则运行后结果如下：

同时我将每个班的综合平均分显示出来如下，经过验证没有问题：

（12）创建manage1角色为其赋予所有表的查询，删除，更新，插入权限

#13创建manage1角色为其赋予所有表的查询，删除，更新，插入权限

-- 创建角色

CREATE ROLE manage1;



-- 赋予所有表的查询、删除、更新、插入权限

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.student to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.teacher to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.student_questions to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.student_papers to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.teacher_courses to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.class to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.major to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.papers to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.paper_questions to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.questions to manage1;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.course to manage1;

（14）创建student0用户并授予查看student,questions,class,major,papers，course的权限：

-- 创建用户

create USER 'student0'@'localhost' IDENTIFIED WITH mysql_native_password BY '210753';




-- 授予查看student, questions, class, major, papers, course的权限

GRANT SELECT ON shiyan9.student TO 'student0'@'localhost';

GRANT SELECT ON shiyan9.questions TO 'student0'@'localhost';

GRANT SELECT ON shiyan9.class TO 'student0'@'localhost';

GRANT SELECT ON shiyan9.major TO 'student0'@'localhost';

GRANT SELECT ON shiyan9.papers TO 'student0'@'localhost';

GRANT SELECT ON shiyan9.course TO 'student0'@'localhost';

//登录student0用户

（15）student0用户，查看专业是物联网工程的所有班级信息中pid=1的试卷成绩大于60分的人数并按降序显示：

SELECT COUNT(*) AS num_students, C.Cid

FROM Student_papers SP, Student S, Class C, Major M

WHERE SP.Sid = S.Sid

AND S.Cid = C.Cid

AND C.Mid = M.Mid

AND M.Mname = '物联网工程'

AND SP.Pid = 1

AND SP.Pgrades > 60

GROUP BY C.Cid

ORDER BY num_students DESC;

（16）student0无法查看teacher表信息：

（17）创建admin用户，为其创建一个角色admin_didi0,为其授予所有表的查看和修改的权限：

-- 创建用户
CREATE USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY '210753';

-- 创建角色

CREATE ROLE admin_didi0;



-- 赋予所有表的查看和修改权限给角色

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.student to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.teacher to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.student_questions to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.student_papers to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.teacher_courses to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.class to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.major to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.papers to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.paper_questions to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.questions to admin_didi0;

GRANT SELECT, DELETE, UPDATE, INSERT ON shiyan9.course to admin_didi0;



-- 将角色授予给用户

GRANT admin_didi0 TO 'admin'@'%';
-- 激活角色

set default role all to 'admin'@'%';

（18）通过用户admin修改吕浩的pid=1的试卷总分为100：

//登录admin用户

、

吕浩原来试卷1的成绩为40分：

UPDATE Student_papers

SET Pgrades = 100

WHERE Sid = (SELECT Sid FROM Student WHERE Sname = '吕浩')

AND Pid = 1;

执行更新操作后：

（19）回收admin的权限

REVOKE ALL PRIVILEGES ON shiyan9.* FROM 'admin'@'%';

（20）创建一个表，要求显示学生的姓名、手机号、所在班级、专业，所在学院：

CREATE table StudentDetails AS

SELECT S.Sname, S.Sphone, C.Cnumber, M.Mname, C.College

FROM Student S,Class C,Major M

where S.Cid = C.Cid and  C.Mid = M.Mid ;



select * from StudentDetails;

（21）为修改试题得分设计一个触发器，修改完试题分数后自动计算该学生对应试卷的总分；（触发器）

更新试题分数前：

#设计一个和触发器修改一个试题得分后自动更新学生对应试卷的总分

CREATE TRIGGER update_total_grade

AFTER UPDATE ON Student_questions

FOR EACH ROW

BEGIN

    DECLARE total_grade INT;

    SELECT SUM(Qgrades) INTO total_grade FROM Student_questions,paper_questions WHERE Sid = NEW.Sid AND Pid = (SELECT Pid FROM Paper_questions WHERE Qid = NEW.Qid) ;

    UPDATE Student_papers SET Pgrades = total_grade WHERE Sid = NEW.Sid AND Pid = (SELECT Pid FROM Paper_questions WHERE Qid = NEW.Qid);

END;





update  student_questions

set Qgrades = 20  where  Sid = 13 and Qid = 4;

更新之后：