第1关 执行 CREATE USER 创建以2022100904为用户名的用户，同时设置其密码为root1234

1
2
3
4
5
6
7
#请在此处添加实现代码
########## Begin ##########
CREATE USER '2022100904'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root1234';

########## End ##########
#以下代码禁止删除
SELECT mysql.user.Host, mysql.user.User FROM mysql.user WHERE USER = '2022100904';

第2关 给予创建的用户2022100904在mydata数据库中授予"J" 表 SELECT 权限

1
2
3
4
5
6
7
8
USE mydata;
#请在此处添加实现代码
########## Begin ##########
GRANT SELECT ON mydata.J TO '2022100904'@'localhost';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';

第3关 给予创建的用户2022100904、2022100908授予"J"表 的ALL PRIVILEGES权限

1
2
3
4
5
6
7
8
9
USE mydata;
#请在此处添加实现代码
########## Begin ##########
GRANT ALL ON mydata.J TO '2022100904'@'localhost';
GRANT ALL ON mydata.J TO '2022100908'@'localhost';
########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';
SHOW GRANTS FOR '2022100908'@'localhost';

第4关 给予创建的用户2022100904在mydata数据库中授予“J”表的UPDATE和INSERT权限

1
2
3
4
5
6
7
8
9
USE mydata;

#请在此处添加实现代码
########## Begin ##########
GRANT UPDATE,INSERT ON mydata.J TO '2022100904'@'localhost' WITH GRANT OPTION;

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';

第5关 回收用户2022100904在mydata数据库中授予的 “J” 表 SELECT权限

1
2
3
4
5
6
7
8
USE mydata;
#请在此处添加实现代码
########## Begin ##########
REVOKE SELECT ON mydata.J FROM '2022100904'@'localhost';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';

第6关 回收用户2022100904、2022100908在mydata数据库的中授予 的J表ALL PRIVILEGES权限

1
2
3
4
5
6
7
8
9
10
11
USE mydata;

#请在此处添加实现代码
########## Begin ##########
REVOKE ALL ON mydata.J FROM '2022100904'@'localhost';
REVOKE ALL ON mydata.J FROM '2022100908'@'localhost';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';
SHOW GRANTS FOR '2022100908'@'localhost';

第7关 回收用户2022100904在mydata数据中授予的“J”表UPDATE和INSERT的权限

1
2
3
4
5
6
7
8
9
USE mydata;

#请在此处添加实现代码
########## Begin ##########
REVOKE UPDATE,INSERT ON mydata.J FROM '2022100904'@'localhost';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';

第8关 创建角色名为 R1的角色

1
2
3
4
5
6
7
#请在此处添加实现代码
########## Begin ##########
CREATE ROLE 'R1';

########## End ##########
#以下代码禁止删除
SELECT mysql.user.Host, mysql.user.User FROM mysql.user WHERE USER = 'R1';

第9关 给予创建的角色R1在mydata数据库中授予"J" 表 的SELECT 、UPDATE、INSERT权限

1
2
3
4
5
6
7
8
9
USE mydata;

#请在此处添加实现代码
########## Begin ##########
GRANT SELECT,UPDATE,INSERT ON mydata.J TO 'R1';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR 'R1';

第10关 给予创建的用户2022100904授予角色R1权限

1
2
3
4
5
6
7
8
9
USE mydata;

#请在此处添加实现代码
########## Begin ##########
GRANT 'R1' TO '2022100904'@'localhost';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';

第11关 给予已授权限的角色R1在mydata数据库中再次授予"J" 表 的DELETE权限

1
2
3
4
5
6
7
8
9
USE mydata;

#请在此处添加实现代码
########## Begin ##########
GRANT DELETE ON mydata.J TO 'R1';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR 'R1';

第12关 回收角色R1 对 J 表的 INSERT 权限

1
2
3
4
5
6
7
8
USE mydata;
#请在此处添加实现代码
########## Begin ##########
REVOKE INSERT ON mydata.J FROM 'R1';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR 'R1';

第13关 通过角色R1回收用户2022100904的被授予J表的SELECT 、UPDATE、INSERT权限

1
2
3
4
5
6
7
8
9
USE mydata;

#请在此处添加实现代码
########## Begin ##########
REVOKE 'R1' FROM '2022100904'@'localhost';

########## End ##########
#以下代码禁止删除
SHOW GRANTS FOR '2022100904'@'localhost';

第14关 编写一个名为INSERT_S的触发器，在S表执行 INSERT 语句后被激发

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
USE mydata;

#请在此处添加实现代码
########## Begin ##########
DELIMITER $$
CREATE TRIGGER INSERT_S
 AFTER INSERT ON S
 FOR EACH ROW
BEGIN
    INSERT INTO N_S VALUES ('S6', '深技大', '20', '深圳','root@localhost');
END$$
DELIMITER ;

########## End ##########
#以下代码禁止删除
#将记录插入S表
INSERT INTO S VALUES ('S6', '深技大', '20', '深圳');
#查看N_S表
SELECT * FROM N_S;

第15关 编写一个名为UPDATE_S的触发器，检查S表的STATUS，只允许0-100之间，如果超过100后，改为100。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
USE mydata;

#请在此处添加实现代码
########## Begin ##########
DELIMITER $$
CREATE TRIGGER UPDATE_S
BEFORE UPDATE ON S
FOR EACH ROW
BEGIN
   IF(NEW.STATUS <0) OR (NEW.STATUS>100)
      THEN SET NEW.STATUS=100;
   END IF;
END$$
DELIMITER ;

########## End ##########
#以下代码禁止删除
#更新S表
UPDATE S SET S.STATUS = 300 WHERE S.CITY = '天津';
#查看S表
SELECT * FROM S;

第16关 编写一个名为UPDATE_SPJ的触发器，SPJ表中有更新某条记录的QTY时，自动更新表SPJ_SUMQTY表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
USE mydata;

#请在此处添加实现代码
########## Begin ##########
DELIMITER $$
CREATE TRIGGER UPDATE_SPJ
AFTER UPDATE ON SPJ
FOR EACH ROW
BEGIN
    UPDATE SPJ_SUMQTY SET SUMQTY = SUMQTY + NEW.QTY - OLD.QTY
    WHERE PNO = NEW.PNO AND JNO = NEW.JNO;
END $$
DELIMITER ;

########## End ##########
#以下代码禁止删除
#更新SPJ表
UPDATE SPJ SET SPJ.QTY = SPJ.QTY + 200 WHERE SPJ.JNO = 'J5';
#查看SPJ_SUMQTY表
SELECT * FROM SPJ_SUMQTY;

第17关 编写一个名为DELETE_SPJ的触发器，当SPJ表中有删除某条记录时，自动更新表SPJ_SUMQTY表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE mydata;

#请在此处添加实现代码
########## Begin ##########

DELIMITER $$
CREATE TRIGGER DELETE_SPJ
AFTER DELETE ON SPJ
FOR EACH ROW
BEGIN
    UPDATE SPJ_SUMQTY SET SUMQTY = SUMQTY - OLD.QTY
    WHERE SPJ_SUMQTY.PNO = OLD.PNO AND SPJ_SUMQTY.JNO = OLD.JNO;
    DELETE FROM SPJ_SUMQTY WHERE SPJ_SUMQTY.SUMQTY <=0;
END $$
DELIMITER ;

########## End ##########
#以下代码禁止删除
#删除SPJ表的某条记录
DELETE FROM SPJ WHERE SPJ.SNO = 'S2' AND SPJ.PNO = 'P3' AND SPJ.JNO = 'J5';
DELETE FROM SPJ WHERE SPJ.SNO = 'S2' AND SPJ.PNO = 'P3' AND SPJ.JNO = 'J1';
#查看SPJ_SUMQTY表
SELECT * FROM SPJ_SUMQTY;

第18关 编写一个名为INSERT_SPJ的触发器，当SPJ表中有插入某条记录时，自动更新表SPJ_SUMQTY表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE mydata;

#请在此处添加实现代码
########## Begin ##########
DELIMITER $$
CREATE TRIGGER INSERT_SPJ
AFTER INSERT ON SPJ
FOR EACH ROW
BEGIN
    UPDATE SPJ_SUMQTY SET SUMQTY = SUMQTY + NEW.QTY
    WHERE SPJ_SUMQTY.PNO = NEW.PNO AND SPJ_SUMQTY.JNO =NEW.JNO;
    IF NEW.JNO NOT IN (SELECT JNO FROM SPJ_SUMQTY)
    OR NEW.PNO NOT IN (SELECT PNO FROM SPJ_SUMQTY)
        THEN INSERT INTO SPJ_SUMQTY VALUES (NEW.JNO,NEW.PNO,NEW.QTY);
    END IF;
END $$
DELIMITER ;

########## End ##########
#以下代码禁止删除
#将记录插入SPJ表
INSERT INTO SPJ VALUES ('S6', 'P1', 'J6', 200);
INSERT INTO SPJ VALUES ('S6', 'P3', 'J5', 300);
#查看SPJ_SUMQTY表
SELECT * FROM SPJ_SUMQTY;