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

1. 数据的定义

创建一个包含以下字段的表格：
- id (主键，自增长)
- name (字符串，长度不超过20个字符)
- age (整数，范围在18到60之间)
- gender (字符串，只能是"Male"或"Female")
- email (字符串，符合电子邮件格式)
- phone (字符串，符合电话号码格式)

用以下 SQL 语句创建数据表格：

CREATE TABLE UserInfo (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    age INT CHECK (age BETWEEN 18 AND 60),
    gender VARCHAR(6) CHECK (gender IN ('Male', 'Female')),
    email VARCHAR(50) UNIQUE NOT NULL,
    phone VARCHAR(20) NOT NULL
)

2. 数据的操作

插入数据：

INSERT INTO UserInfo (name, age, gender, email, phone) 
VALUES ('John', 25, 'Male', 'john@example.com', '123-456-7890')

更新数据：

UPDATE UserInfo SET age = 30 WHERE name = 'John'

删除数据：

DELETE FROM UserInfo WHERE id = 1

查询数据：

SELECT * FROM UserInfo WHERE gender = 'Female' AND age > 20

3. 安全性与完整性控制

创建两个用户，一个管理员用户和一个普通用户：

CREATE LOGIN admin WITH PASSWORD = 'password';
CREATE LOGIN user WITH PASSWORD = 'password';

为管理员用户创建数据库用户，并且将其分配为 db_owner 角色：

CREATE USER admin FOR LOGIN admin;
EXEC sp_addrolemember 'db_owner', 'admin';

为普通用户创建数据库用户，并且将其分配为 db_datareader 和 db_datawriter 角色：

CREATE USER user FOR LOGIN user;
EXEC sp_addrolemember 'db_datareader', 'user';
EXEC sp_addrolemember 'db_datawriter', 'user';

为数据表格 UserInfo 添加实体完整性约束，确保 name、email 和 phone 字段的非空性：

ALTER TABLE UserInfo ADD CONSTRAINT CK_UserInfo_Name CHECK (name IS NOT NULL);
ALTER TABLE UserInfo ALTER COLUMN email VARCHAR(50) NOT NULL;
ALTER TABLE UserInfo ALTER COLUMN phone VARCHAR(20) NOT NULL;

为数据表格 UserInfo 添加参照完整性约束，确保 gender 字段只能是 'Male' 或 'Female'：

ALTER TABLE UserInfo ADD CONSTRAINT FK_UserInfo_Gender CHECK (gender IN ('Male', 'Female'));

4. 自主存取控制权限

为管理员用户授予 SELECT、INSERT、UPDATE 和 DELETE 数据表格 UserInfo 的权限：

GRANT SELECT, INSERT, UPDATE, DELETE ON UserInfo TO admin;

为普通用户授予 SELECT 数据表格 UserInfo 的权限：

GRANT SELECT ON UserInfo TO user;

撤销普通用户对数据表格 UserInfo 的 SELECT 权限：

REVOKE SELECT