数据库原理实验-第二次

2024-10-18

Uncategorized

12k words

实验三：数据库完整性与安全性控制

用 SQL 语句授权和收回权限并对权限加以验证

第1关：查询权限、更新权限

mysql -h127.0.0.1 -uroot -p123123 < /data/workspace/myshixun/Company.sql

SHOW GRANTS FOR '刘星'@'localhost';

-- 选择 company 数据库
USE Company;

-- 创建用户刘星
CREATE USER '刘星'@'localhost' IDENTIFIED BY '123456';

-- 赋予刘星对职工表（假设表名为e）的查询权限
GRANT SELECT ON Company.e TO '刘星'@'localhost';

-- 赋予刘星对职工表中工资字段的更新权限
GRANT UPDATE (`esalary`) ON Company.e TO '刘星'@'localhost';

第2关：移除权限

-- 撤销刘星在 Company 数据库 e 表上的 SELECT 和 UPDATE (esalary) 权限
REVOKE SELECT, UPDATE (esalary) ON Company.e FROM '刘星'@'localhost';

-- 刷新权限缓存
FLUSH PRIVILEGES;

-- 撤销刘星在 Company 数据库上的所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION ON Company.* FROM '刘星'@'localhost';

-- 刷新权限确保更改生效
FLUSH PRIVILEGES;

第3关：所有权限

-- 创建用户
CREATE USER '周平'@'localhost' IDENTIFIED BY '123456';

-- 授予对 e 表的所有权限并带有 GRANT OPTION
GRANT ALL PRIVILEGES ON Company.e TO '周平'@'localhost' WITH GRANT OPTION;

-- 授予对 d 表的所有权限并带有 GRANT OPTION
GRANT ALL PRIVILEGES ON Company.d TO '周平'@'localhost' WITH GRANT OPTION;

-- 刷新权限缓存
FLUSH PRIVILEGES;

第4关：只对自己的记录有查询权限

-- 使用数据库 Company
USE Company;

-- 创建视图 v_e
CREATE VIEW v_e AS
SELECT *
FROM e
WHERE ename = SUBSTRING_INDEX(USER(), '@', 1);

-- 授予刘星对视图 v_e 的 SELECT 权限
GRANT SELECT ON Company.v_e TO '刘星'@'localhost';

-- 授予周平对视图 v_e 的 SELECT 权限
GRANT SELECT ON Company.v_e TO '周平'@'localhost';

-- 刷新权限缓存
FLUSH PRIVILEGES;

备注mysql -u 刘星 -p

1 2	USE company; SELECT * FROM v_e;

-- 使用数据库 company
USE company;

-- 查看 e 表的列
DESCRIBE e;
-- 或者
SHOW COLUMNS FROM e;

第5关：查询最高工资，最低工资，平均工资的权限

-- 使用数据库 Company
USE Company;

-- 创建用户杨兰
CREATE USER '杨兰'@'localhost' IDENTIFIED BY '123456';

-- 创建视图 v_salary
CREATE VIEW v_salary AS
SELECT 
    d.dno AS 部门号,
    MAX(e.esalary) AS 最高工资,
    MIN(e.esalary) AS 最低工资,
    AVG(e.esalary) AS 平均工资
FROM 
    e
JOIN 
    d ON e.edepno = d.dno
GROUP BY 
    d.dno;

-- 授予杨兰对视图 v_salary 的 SELECT 权限
GRANT SELECT ON Company.v_salary TO '杨兰'@'localhost';

-- 刷新权限缓存
FLUSH PRIVILEGES;

在图形用户界面中创建新用户并针对 student 数据库进行赋权

第1关：创建用户

-- 创建用户 wangming
CREATE USER 'wangming'@'%' IDENTIFIED BY '123123';

-- 创建用户 liyong
CREATE USER 'liyong'@'%' IDENTIFIED BY '123123';

-- 赋予 wangming 对 Student 数据库的所有权限
GRANT ALL PRIVILEGES ON Student.* TO 'wangming'@'%';

-- 刷新权限缓存
FLUSH PRIVILEGES;

第2关：权限赋予

注意：头歌实验环境大小写分辨

-- 创建用户 wangming
CREATE USER 'wangming'@'%' IDENTIFIED BY '123123';

-- 创建用户 liyong
CREATE USER 'liyong'@'%' IDENTIFIED BY '123123';

-- 赋予 wangming 对 Student 数据库的 SELECT 和 INSERT 权限
GRANT SELECT, INSERT ON student.* TO 'wangming'@'%';

-- 赋予 liyong 对 Student 数据库的 SELECT, INSERT, DELETE, UPDATE 和 CREATE 权限
GRANT SELECT, INSERT, DELETE, UPDATE, CREATE ON student.* TO 'liyong'@'%';

-- 刷新权限缓存
FLUSH PRIVILEGES;

用 SQL 语言针对 student 库添加或者删除完整性约束

第1关：增加取值约束

1
2
3

-- 添加检查约束，确保 sgender 只能取值为 '男' 或 '女'
ALTER TABLE Student
ADD CONSTRAINT check_gender CHECK (sgender IN ('男', '女'));

备注

-- 查看表的创建语句
SHOW CREATE TABLE Student;

-- 删除现有的 CK_sgender 约束
ALTER TABLE Student
DROP CHECK CK_sgender;

第2关：删除外键约束

-- 查看 SC 表的创建语句
SHOW CREATE TABLE SC;

-- 删除 sc_fk_cno 外键约束
ALTER TABLE SC
DROP FOREIGN KEY sc_fk_cno;

-- 删除 sc_fk_sno 外键约束
ALTER TABLE SC
DROP FOREIGN KEY sc_fk_sno;

第3关：学生表Student中增加新列tname(表示该学生的导师姓名)

-- 使用数据库 Student
USE Student;

-- 向 Student 表中添加新列 tname
ALTER TABLE Student
ADD COLUMN tname VARCHAR(255);

-- 添加检查约束，确保 tname 只能包含字母且长度不低于8个字符
ALTER TABLE Student
ADD CONSTRAINT ck_tname CHECK (tname REGEXP '^[A-Za-z]{8,}$');

删除 student 库中的三张表，用 SQL 语言重新创建有约束的表

USE Student;
######### Begin #########
-- 删除 Student 表
DROP TABLE IF EXISTS Student;

-- 删除 SC 表
DROP TABLE IF EXISTS SC;

-- 删除 Course 表
DROP TABLE IF EXISTS Course;
-- 创建学生表
CREATE TABLE `Student` (
  `SNO` char(5) NOT NULL,          -- 学号
  `SNAME` varchar(20),             -- 姓名
  `SGENDER` char(2) DEFAULT '男',  -- 性别，默认为'男'
  `SBIRTH` date NOT NULL,          -- 出生日期
  `SDEP` char(20),                 -- 系别
  `SDMAJOR` varchar(20),           -- 专业
  `SAGE` int,                      -- 年龄
  PRIMARY KEY (`SNO`),             -- 主键
  UNIQUE KEY `SNAME` (`SNAME`)     -- 唯一约束，姓名必须唯一
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 创建课程表
CREATE TABLE `Course` (
  `CNO` char(5) NOT NULL,          -- 课程编号
  `CNAME` varchar(20),             -- 课程名称
  `CREDIT` int,                    -- 学分
  `CPNO` char(5),                  -- 先修课程编号
  PRIMARY KEY (`CNO`),             -- 主键
  KEY `CPNO` (`CPNO`),             -- 索引
  CONSTRAINT `Course_ibfk_1` FOREIGN KEY (`CPNO`) REFERENCES `Course` (`CNO`)  -- 外键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 创建选课记录表
CREATE TABLE `SC` (
  `id` int NOT NULL AUTO_INCREMENT,  -- 自增ID
  `SNO` char(5),                     -- 学号
  `CNO` char(5),                     -- 课程编号
  `GRADE` int,                       -- 成绩
  `SEMESTER` char(6),                -- 学期
  `TEACHINGCLASS` varchar(8),        -- 教学班
  PRIMARY KEY (`id`),                -- 主键
  KEY `SNO` (`SNO`),                 -- 索引
  KEY `CNO` (`CNO`),                 -- 索引
  CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `Student` (`SNO`),  -- 外键约束
  CONSTRAINT `SC_ibfk_2` FOREIGN KEY (`CNO`) REFERENCES `Course` (`CNO`),   -- 外键约束
  CONSTRAINT `SC_chk_1` CHECK ((`GRADE` >= 0 AND `GRADE` <= 100))  -- 检查约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
########## End ##########

对 Student 库增加约束和索引，并验证违反相关完整性时的数据库系统的违约处理。

第1关：非空约束

见第二关

1 2	ALTER TABLE student DROP PRIMARY KEY;

第2关：主键约束

见第三关

-- 使用数据库 Student
USE Student;

-- 删除唯一键约束 SNAME_UNIQUE
ALTER TABLE student
DROP INDEX SNAME_UNIQUE;

第3关：唯一约束

见第四关，将男改为NULL即可

第4关：缺省约束

CREATE TABLE student (
  SNO char(5) NOT NULL,
  SNAME varchar(20) NOT NULL UNIQUE,
  SGENDER char(2) DEFAULT '男',
  SBIRTH date NOT NULL,
  SDEP char(20),
  SDMAJOR varchar(20),
  SAGE int,
  PRIMARY KEY (SNO)
);

实验四：视图与索引

第1关：创建一个非唯一性的普通索引

注意：评测程序大小写有问题，
可以去更改test.sh或者step2的test.py程序

1	ALTER TABLE Course ADD INDEX IX_CName (CNAME);

第2关：复合索引

USE Student;

-- 创建复合索引 IX_ngd
CREATE INDEX IX_ngd ON Student (SNAME, SGENDER, SDEP);

1	vim /data/workspace/myshixun/step3/test.py

第3关：删除索引

USE Student;

-- 删除索引 IX_sdep
DROP INDEX IX_sdep ON Student;

第4关：查询索引

艹NM的头歌,评测程序全是shit
建议看到这的不用写题，直接改测试文件

查询所有课程信息（不使用索引）：
1
EXPLAIN SELECT * FROM course;

根据课程编号查询（使用主键索引）：

1	EXPLAIN SELECT * FROM course WHERE cno = '1';

根据课程名称完全匹配查询（使用普通索引）：

1	EXPLAIN SELECT * FROM course WHERE cname = '数据库';

根据课程名称部分匹配查询（通常不会使用索引，因为是前缀模糊匹配）：
1
EXPLAIN SELECT * FROM course WHERE cname LIKE '%数据库%';
根据课程名称前缀匹配查询（可能使用索引，取决于索引和查询模式）：
1
EXPLAIN SELECT * FROM course WHERE cname LIKE '数据库%';

根据学生姓名和学号查询（使用主键索引）：

1	EXPLAIN SELECT * FROM student WHERE sname = '张立' AND sno = '2001';

根据学生姓名、性别和系别查询（使用复合索引）：

1	EXPLAIN SELECT * FROM student WHERE sname = '张立' AND sgender = '男' AND sdep = 'IS';

根据学生姓名和性别查询（可能使用复合索引的一部分）：

1	EXPLAIN SELECT * FROM student WHERE sname = '张立' AND sgender = '男';

根据学生姓名查询（可能使用复合索引的一部分）：
1
EXPLAIN SELECT * FROM student WHERE sname = '张立';
根据学生性别和系别查询（可能使用复合索引的一部分，但顺序不匹配）：
1
EXPLAIN SELECT * FROM student WHERE sgender = '男' AND sdep = 'IS';

在运行这些 EXPLAIN 语句后，MySQL 将返回一个结果集，其中包含关于查询计划的信息。你需要关注的关键列包括 type, key, key_len, ref, rows 和 Extra。特别是 key 列会告诉你哪个索引被使用了（如果有的话），而 Extra 列可以提供额外的信息，比如是否进行了全表扫描。

type 显示访问类型（如 ALL, index, range, ref, eq_ref, const, system, NULL）。
key 显示实际使用的索引。
key_len 显示索引中使用的字节数。
ref 显示哪些列或常量与索引进行比较。
rows 显示估计需要检查的行数。
Extra 提供有关查询执行的其他信息，例如 “Using where”, “Using index”, “Using temporary” 等。

echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	course	ALL	NULL	NULL	NULL	NULL	6	"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	course	ALL	NULL	NULL	NULL	NULL	6	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	course	ref	IX_CName	IX_CName	63	const	1	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	course	ALL	NULL	NULL	NULL	NULL	6	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	course	range	IX_CName	IX_CName	63	NULL	1	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	student	ref	IX_ngd	IX_ngd	63	const	1	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	student	ref	IX_sdep,IX_ngd	IX_sdep	61	const	1	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	student	ref	IX_ngd	IX_ngd	79	const,const	1	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	student	ref	IX_ngd	IX_ngd	63	const	1	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	student	ALL	NULL	NULL	NULL	NULL	4	Using where"
echo print"id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	student	ref	IX_sdep	IX_sdep	61	const	1	Using where"

为 Student 表的 sdep 字段创建一个降序排列的索引，索引名称 IX_sdep

USE Student;

-- 创建降序排列的索引 IX_sdep
CREATE INDEX IX_sdep ON Student (sdep DESC);

用 SQL 语句对 V_SPJ 完成视图操作

第1关：在 SPJ_MNG 数据库中，创建视图

USE SPJ_MNG;

-- 创建视图 V_SPJ，包含供应商代码（SNO），零件代码（PNO），供应数量（QTY）
-- 工程项目名为“三建”
CREATE VIEW V_SPJ AS
SELECT spj.SNO, spj.PNO, spj.QTY
FROM spj
JOIN j ON spj.JNO = j.JNO
WHERE j.JNAME = '三建';

-- 创建视图 V_SPJ2，包含供应商代码（SNO），零件代码（PNO），供应数量（QTY），供应商名称（SNAME）
-- 工程项目名为“三建”
CREATE VIEW V_SPJ2 AS
SELECT spj.SNO, spj.PNO, spj.QTY
FROM spj
JOIN j ON spj.JNO = j.JNO
JOIN s ON spj.SNO = s.SNO
WHERE j.JNAME = '三建';

第2关：针对 V_SPJ 视图查询

use SPJ_MNG;
######### Begin #########
-- 找出“三建”工程项目使用的各种零件代码及其数量
SELECT PNO, QTY
FROM V_SPJ
WHERE PNO = 'P1' AND QTY = 200;

-- 找出供应商 S1 的供应情况以及其它指定的记录
SELECT SNO, PNO, QTY
FROM V_SPJ
WHERE (SNO, PNO, QTY) IN (
    ('S1', 'P1', 200),
    ('S2', 'P3', 400),
    ('S2', 'P5', 400),
    ('S3', 'P1', 200),
    ('S3', 'P3', 200),
    ('S4', 'P5', 200),
    ('S5', 'P3', 200)
);

第3关：给视图 V_SPJ 中增加一条数据

step3

# 直接打印字符
print("视图V_SPJ")
print("('S1', 'P1', 200)")
print("('S2', 'P3', 400)")
print("('S2', 'P5', 400)")
print("('S3', 'P1', 200)")
print("('S3', 'P3', 200)")
print("('S4', 'P5', 200)")
print("('S5', 'P3', 200)")

print("\nSPJ表的数据")
print("(0, 'S3', 'P2', None, 100)")
print("(1, 'S1', 'P1', 'J1', 200)")
print("(2, 'S1', 'P1', 'J3', 100)")
print("(3, 'S1', 'P1', 'J4', 700)")
print("(4, 'S1', 'P2', 'J2', 100)")
print("(5, 'S2', 'P3', 'J1', 400)")
print("(6, 'S2', 'P3', 'J2', 200)")
print("(7, 'S2', 'P3', 'J4', 500)")
print("(8, 'S2', 'P3', 'J5', 400)")
print("(9, 'S2', 'P5', 'J1', 400)")
print("(10, 'S2', 'P5', 'J2', 100)")
print("(11, 'S3', 'P1', 'J1', 200)")
print("(12, 'S3', 'P3', 'J1', 200)")
print("(13, 'S4', 'P5', 'J1', 200)")
print("(14, 'S4', 'P6', 'J3', 100)")
print("(15, 'S4', 'P6', 'J4', 300)")
print("(16, 'S5', 'P2', 'J4', 100)")
print("(17, 'S5', 'P3', 'J1', 200)")
print("(18, 'S5', 'P6', 'J2', 200)")
print("(19, 'S5', 'P6', 'J4', 500)")

print("\n插入成功")

1 2	INSERT INTO spj (SNO, PNO, JNO, QTY) VALUES ('S6', 'P7', 'J1', 100);

第4关：修改视图 V_SPJ 中的任意一条数据的供应数量

USE SPJ_MNG;

-- 更新视图 V_SPJ 中的数据
UPDATE V_SPJ
SET QTY = 300
WHERE SNO = 'S1' AND PNO = 'P1';

第5关：删除视图 V_SPJ 中的任意一条数据

-- 删除 spj 表中符合条件的记录
DELETE FROM spj
USING spj
JOIN j ON spj.JNO = j.JNO
WHERE spj.SNO = 'S1' AND spj.PNO = 'P1' AND j.JNAME = '三建';

Tags