實驗目的

• 熟練掌味訓本表的各種完整性定義的方法，
• 掌握用戶的創建以及使用方法，
• 掌握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';