交互式SQL语言——综合操作 写出每一种类型的SQL 测试用例,包括数据的定义和数据的操作，同时实现如下的安全性与完整性控制。理解和掌握自主存取控制权限的定义和维护方法，包括定义用户、定义角色、分配权限给角色、分配权限给用户和回收权限等基本功能，掌握数据库审计的设置和管理方法。对数据使用SQL语言进行完整性控制，掌握实体完整性和参照完整性的定义、修改、删除等各种基本功能。了解数据库完整性控制的多样方式方法，上机实践并加以比较

SQL语言综合操作包括数据定义和数据操作，同时实现安全性与完整性控制。以下是每一种类型的SQL测试用例。

1. 数据的定义

创建表：

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  emp_salary DECIMAL(10,2),
  emp_dept VARCHAR(50)
);

创建视图：

CREATE VIEW emp_view AS
SELECT emp_id, emp_name, emp_salary
FROM employee
WHERE emp_dept = 'IT';

创建存储过程：

CREATE PROCEDURE get_employee_data (IN emp_id INT)
BEGIN
  SELECT *
  FROM employee
  WHERE emp_id = emp_id;
END;

创建触发器：

CREATE TRIGGER update_emp_salary
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
  IF NEW.emp_salary < OLD.emp_salary THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Salary cannot be decreased.';
  END IF;
END;

2. 数据的操作

插入数据：

INSERT INTO employee (emp_id, emp_name, emp_salary, emp_dept)
VALUES (1, 'John Doe', 50000.00, 'IT');

更新数据：

UPDATE employee
SET emp_salary = 55000.00
WHERE emp_id = 1;

删除数据：

DELETE FROM employee
WHERE emp_id = 1;

查询数据：

SELECT *
FROM employee
WHERE emp_dept = 'IT';

3. 安全性控制

定义用户：

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';

定义角色：

CREATE ROLE 'admin';

分配权限给角色：

GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO 'admin';

分配权限给用户：

GRANT 'admin' TO 'user1'@'localhost';

回收权限：

REVOKE SELECT, INSERT, UPDATE, DELETE ON employee FROM 'admin';

4. 完整性控制

实体完整性：

ALTER TABLE employee
ADD CONSTRAINT emp_dept_check CHECK (emp_dept IN ('IT', 'HR', 'Finance'));

参照完整性：

CREATE TABLE department (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

ALTER TABLE employee
ADD CONSTRAINT emp_dept_fk FOREIGN KEY (emp_dept) REFERENCES department(dept_name);

测试不同的完整性控制方式，比较它们的优缺点。

实体完整性控制是在表级别上进行的，可以保证每条记录的数据都符合指定的条件。但是，如果需要修改约束条件，需要对表进行整体修改，影响较大。

参照完整性控制是在关系型数据库中经常使用的一种完整性控制方式。可以保证关联表之间的数据一致性，但是如果需要删除主表数据，需要先删除关联表数据，否则会引发外键约束的错误。