SQL复习-管理

设置能见度 CREATE VIEW view_name AS <expression>

CREATE VIEW Dpt_size(name, num_of_employee) AS (SELECT D.name, COUNT(*) FROM Departments D, Works_in W WHERE D.department_id = W.department_id GROUP BY W.department_id );

授权GRANT <sth> ON <table name or view name> TO <user/role list>

GRANT SELECT ON Departments TO Johnson, Brown；

GRANT UPDATE(budget) ON Departments TO manager；

REVOKE SELECT ON Departments FROM Johnson, Brown；

CREATE ROLE manager;

GRANT manager TO Brown;

设置条件（Assertions are checked whenever the involved tables

are updated --> very expensive!）

CREATE ASSERTION EmpsNoLessThanDepts CHECK ( (SELECT COUNT(*) FROM Departments) <=(SELECT COUNT(*) FROM Employees) );