資料庫系統概論：一些其他的SQL語句

目錄

• • 1. 存取控制：授予與收回
  • 2. 審計：AUDIT和NOAUDIT
  • 3. 參照完整性規則
  • 4. 完整性限制命名子句
  • 5. 斷言
  • 6. 觸發器

1. 存取控制：授予與收回

1. GRANT

//将查詢SC表的權限給U1
GRANT SELECT ON TABLE SC TO U1
WITH CHECK OPTION;//允許U1使用者可将将權限授予其他使用者
//把course和student表的全部操作權限授予使用者U2和U3
GRANT ALL PRIVILEGES ON TABLE COURSE,STUDENT TO U2,U3;
//對屬性列授權時，必須指出相應的屬性列名
GRANT UPDATE(SNO),SELECT ON TABLE SC TO PUBLIC;
           

1. REVOKE

REVOKE SELECT ON TABLE SC FROM PUBLIC;
//将使用者U5權限收回的同時級聯收回由U5派發的權限
REVOKE INSERT ON TABLE SC FROM U5 CASCADE;
           

1. 建立角色

2. 審計：AUDIT和NOAUDIT

1. 對修改SC表結構或修改SC表資料的操作進行審計

1. 取消對SC表的一切審計

3. 參照完整性規則

CREATE TABLE SC(
SNO CHAR(9),
CNO CHAR(8),
GRADE INT,
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES STUDENT(SNO)
	ON DELETE CASCADE//當删除STUDENT表的元組時，級聯删除SC表中相應的元組)
	ON UPDATE CASCADE,//
FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
	ON DELETE NO ACTION//當删除COURSE表的元組時造成與SC表不一緻時，拒絕删除)
	ON UPDATE CASCADE,//
           

4. 完整性限制命名子句

1. 定義

CREATE TABLE STUDENT(
SNO INT CONSTRAINT C1 CHECK(SNO BETWEEN 1000 AND 2000),
SNAME CHAR(20) CONSTRAINT C2 NOT NULL,
SEX CHAR(4) CONSTRAINT C3 CHECK(SEX IN('MAN','WOMEN')),
	CONSTRAINT STUDENT_KEY PRIMARY KEY(SNO));
           

1. 修改

//先将其删掉
ALTER TABLE STUDENT DROP CONSTRAINT C4;
//再添加
ALTER TABLE STUDENT ADD CONSTRAINT C5 CHECK(SNO BETWEEN 1000 AND 2000);
           

5. 斷言

1. 限制每一門課程最多60名學生選修

CREATE ASSERTION ASS_SC_COUN
	CHECK(60>=(SELECT COUNT(*) FROM SC GROUP BY CNO));
           

1. 删除斷言

6. 觸發器

1. 當對表SC的成績屬性進行修改時，若分數增加了10%，則将此次操作記錄到另一個表S_U中

CREATE TRIGGER SC_T
AFTER UPDATE OF GRADE ON SC
REFERENCING
	OLDROW AS OLDTUPLE,
	NEWROW AS NEWTUPLE
FOR EACH ROW//行級觸發器，每執行一次GRADE更新，下面的規則就執行一次
WHEN(NEWTUPLE.GRADE>=1.1*OLDTUPLE)
	INSERT INTO S_U(SNO,CNO,OLDTUPLE,NEWGRADE)
	VALUES(OLDTUPLE.SNO,OLDTUPLE.CNO,OLDTUPLE.GRADE,NEWTUPLE.GRADE)
           

1. 定義一個BEFORE行級觸發器，為教師表TEACHER定義完整性規則，教授的工資不得低于4000元，如果低于4000元，則自動改為4000

CREATE TRIGGER INSERT_UPDATE_TEACHER
BEFORE INSERT OR UPDATE ON TEACHER
REFERENCING NEWROW AS NEWTUPLE
FOR EACH ROW
BEGIN
	IF(NEWTUPLE.JOB='教授')AND(NEWTUPLE.SAL<4000)
	THEN NEWTUPLE.SAL:=4000;//使用插入或更新後的新值
	END IF;
END;