- 熟练掌握基本表的各种完整性定义的方法。
- 掌握用户的创建以及使用方法。
- 掌握SQL语句对数据库和表进行安全控制的方法。
- 利用图形用户界面对Student库中，增加以下的约束和索引，并验证违反相关完整性时的数据库系统的违约处理。
(1) S表的非空约束：为出生日期添加非空约束。
(2) S表的主键约束：将学号(sno)设置为主键。
(3) S表的唯一约束：为姓名(sname)添加唯一约束(唯一键)。
(4) S表的缺省约束：为性别(sgender)添加默认值，其值为“男”。
- 利用图形用户界面针对Student库，给SC表添加外键约束，将sno,cno设置为外键，其引用表分别是S表和C表，外键名分别为：sc_fk_sno和sc_fk_cno。尝试不同的违反外键约束的策略并进行验证NO action/ restrict/ cascade/ set null。（10分）
NO ACTION
CASCADE
SET NULL
- 删除student库中的三张表，用SQL语言重新创建有以下约束的表。(10分）
- S表：基本约束要求同第1小题。
- C表：cpno是c表的外键，引用自C表的cno。
- SC表：外键约束如第2小题，同时要求成绩grade的有效值为[0,100]。另外，SC表增加新列id：将id设为主键，且自增字段，每次插入一条选课记录自动+1。
S表的创建
CREATE TABLE student.s ( SNO VARCHAR(45) NOT NULL, SNAME VARCHAR(45) NULL, SGENDER VARCHAR(45) NULL DEFAULT '男', SBIRTH VARCHAR(45) NOT NULL, SDEPT VARCHAR(45) NULL, PRIMARY KEY (`SNO`), UNIQUE INDEX `SNAME_UNIQUE` (`SNAME` ASC) VISIBLE);
C表的创建
CREATE TABLE student.c ( CNO VARCHAR(45) NOT NULL, CNAME VARCHAR(45) NULL, CPNO VARCHAR(45) NULL, CREDIT INT NULL, PRIMARY KEY (CNO), FOREIGN KEY (CPNO) REFERENCES student.c (CNO) );
SC表的创建
CREATE TABLE SC ( ID INT NOT NULL AUTO_INCREMENT, SNO VARCHAR(45) NULL, CNO VARCHAR(45) NULL, GRADE INT NULL CHECK(GRADE >= 0 AND GRADE <= 100), PRIMARY KEY (ID), CONSTRAINT sc_fk_cno FOREIGN KEY (CNO) REFERENCES c (CNO), CONSTRAINT sc_fk_sno FOREIGN KEY (SNO) REFERENCES s (SNO) );
- 用SQL语言针对student库添加或者删除以下的完整性约束。(10分）
(1) 增加sgender的取值约束：S表中的sgender取值只能是“男”或“女”。
ALTER TABLE S
ADD CHECK(SGENDER IN ('男','女')
);
(2) 删除第(1)小题所创建的sgender取值约束。
ALTER TABLE S DROp CONSTRAINT S_CHK_1;
(3) 删除SC表的外键约束。
ALTER TABLE SC DROP CONSTRAINT sc_fk_cno, DROP CONSTRAINT sc_fk_sno;
(4) 学生表S中增加新列tname(表示该学生的导师姓名)，且要求导师名必须全部是字母（大写小写均可以）、且长度不能低于8个字符。
ALTER TABLE S ADD COLUMN TNAME VARCHAR(45) NULL CONSTRAINT C CHECK(char_length(TNAME) >= 8 AND TNAME like '^[A-Za-z]+$');
- 在图形用户界面中创建新用户并针对student数据库进行赋权。（20分）
(1) 创建两个可以登录的本地用户: 王明，李勇。
(2) 完成以下的权限赋予：
① 用户王明对所有表均有select和insert的权力。
② 用户李勇对该数据库select，insert，delete, update和create权限。
进入后选择Add Account创建好本地用户后，进入Schema Privileges选择Add Entry授予权限。
- 用SQL语句授权和收回权限并对权限加以验证。（40分）
要求：首先创建职工表e和部门表d，表结构如下：
职工表e（职工号，姓名，年龄，职务，工资，部门号）
部门表d（部门号，名称，经理名，地址，电话号）
然后创建每道题中涉及的用户，针对每个小题先完成授权并进行权限验证，然后再收回权限并进行权限验证。
(1) 用户王明对两个表有查询权限。
CREATE USER 'wangming'@'localhost'; GRANT SELECT ON TABLE e TO 'wangming'@'localhost'; GRANT SELECT ON TABLE d TO 'wangming'@'localhost'; REVOKE SELECT ON TABLE e FROM 'wangming'@'localhost'; REVOKE SELECt ON TABLE d FROM 'wangming'@'localhost';
(2) 用户李勇对两个表有insert和delete权限。
CREATE USER 'liyong'@'localhost'; GRANT INSERT,DELETE ON TABLE e TO 'liyong'@'localhost'; GRANT INSERT,DELETE ON TABLE d TO 'liyong'@'localhost'; SHOW GRANTS FOR 'liyong'@'localhost'; REVOKE INSERT,DELETE ON TABLE e FROM 'liyong'@'localhost'; REVOKE INSERT,DELETe ON TABLE d FROM 'liyong'@'localhost';
(3) 每个职工只对自己的记录有查询权限。
CREATE VIEW view_employee AS SELECt * FROM e WHERe CONCAT(ENAME,'@localhost') = user(); GRANT SELECt ON view_employee TO 'wangming'@'localhost' , 'liyong'@'localhost' , 'liuxing'@'localhost' , 'zhangxin'@'localhost' , 'zhouping'@'localhost' , 'yanglan'@'localhost';
(4) 用户刘星对职工表有查询权限，对工资字段具有更新权限。
CREATE USER 'liuxing'@'localhost'; GRANT SELECT ON TABLE e TO 'liuxing'@'localhost'; GRANT UPDATE(salary) ON TABLE d TO 'liuxing'@'localhost'; REVOKE SELECT ON TABLE e FROM 'liuxing'@'localhost'; REVOKE UPDATe(salary) ON TABLE d FROM 'liuxing'@'localhost';
(5) 用户张新具有修改这两个表的结构的权限。
CREATE USER 'zhangxin'@'localhost'; GRANT ALTER ON TABLE e TO 'zhangxin'@'localhost'; GRANT ALTER ON TABLE d TO 'zhangxin'@'localhost'; REVOKE ALTER ON TABLE e FROM 'zhangxin'@'localhost'; REVOKE ALTER ON TABLE d FROM 'zhangxin'@'localhost';
(6) 用户周平具有对两个表的所有权限，并具有给其他用户授权的权限。
CREATE USER 'zhouping'@'localhost'; GRANT ALL ON TABLE e TO 'zhouping'@'localhost' WITH GRANT OPTION; GRANT ALL ON TABLE d TO 'zhouping'@'localhost' WITH GRANT OPTION; REVOKE ALL PRIVILEGES ON TABLE e FROM 'zhouping'@'localhost'; REVOKE ALL PRIVILEGES ON TABLE d FROM 'zhouping'@'localhost'; REVOKE GRANT OPTION ON TABLE e FROM 'zhouping'@'localhost'; REVOKE GRANT OPTION ON TABLE d FROM 'zhouping'@'localhost';
(7) 用户杨兰具有从每个部门职工中查询最高工资，最低工资，平均工资的权限，他不能查看每个人的工资。
CREATE VIEW view_yanglan AS SELECt max(salary),min(salary),avg(salary) FROM e; CREATE USER 'yanglan'@'localhost'; GRANT SELECt ON view_yanglan TO 'yanglan'@'localhost'; REVOKE SELECT ON view_yanglan FROM 'yanglan'@'localhost';
