SQL考点专题

一、创建表：主码与外码的定义 (CREATE TABLE)

这是最基础的，但考试最容易在外码上扣分。

格式模板：

CREATE TABLE 表名 (
    列名 数据类型 [列级约束],
    ...
    /* 表级约束区域 */
    PRIMARY KEY (主码列List),    /* 定义主码 */
    FOREIGN KEY (外码列) REFERENCES 被参照表(被参照列) /* 定义外码 */
);

教材经典例子（学生-课程-选课）：
假设有 Student 表（Sno是主码），现在创建 SC (选课) 表。

CREATE TABLE SC (
    Sno   CHAR(9),       /* 学号 */
    Cno   CHAR(4),       /* 课程号 */
    Grade SMALLINT,      /* 成绩 */
    
    /* 1. 定义主码：因为一个学生修一门课才唯一，所以是(Sno, Cno)组合主码 */
    PRIMARY KEY (Sno, Cno),
    
    /* 2. 定义外码：Sno引用Student表，Cno引用Course表 */
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

注意点：外码的类型和长度必须与被参照表的主码完全一致。

---

二、修改表结构 (ALTER TABLE)

1. 增加新列 (ADD)

/* 给学生表增加“入学时间”列，日期型 */
ALTER TABLE Student ADD S_entrance DATE;

2. 修改原有的列 (ALTER COLUMN)
注意：教材中有时也会用 MODIFY，但标准SQL常用 ALTER COLUMN 或直接 MODIFY，考试写清楚意图即可。

/* 将年龄的数据类型改为整数 */
ALTER TABLE Student ALTER COLUMN Sage INT;

3. 删除列 (DROP)

/* 删除“入学时间”这一列 */
ALTER TABLE Student DROP COLUMN S_entrance;

---

三、带子查询的修改语句 (UPDATE)

格式模板：

UPDATE 表名
SET 列名 = 新值
WHERE 列名 IN (SELECT ...);  /* 或者用 EXISTS */

例子（把计算机系所有学生成绩置0）：
这涉及两张表：Student（有系别Sdept）和 SC（有成绩Grade）。

UPDATE SC 
SET Grade = 0 
WHERE Sno IN ( 
    SELECT Sno 
    FROM Student 
    WHERE Sdept = 'CS'   /* 找出所有计算机系的学生学号 */
);

解析：先在子查询里把CS系学号找出来，外层UPDATE语句只要发现Sno在这个名单里，就把Grade改成0。

---

四、视图 (VIEW)

1. 创建视图 (CREATE VIEW)
视图是虚表，不存储数据，只存储定义。

/* 建立计算机系学生的视图，并要求进行修改和插入时仍需保证该视图只有计算机系学生 */
CREATE VIEW CS_Student
AS 
SELECT Sno, Sname, Sage 
FROM Student 
WHERE Sdept = 'CS'
WITH CHECK OPTION; /* 关键点：透过视图修改数据时，自动检查是否满足 WHERE Sdept='CS' */

2. 视图相关 SQL (查询视图)
查询视图和查询表一样写，不用变。

SELECT * FROM CS_Student WHERE Sage < 20;

3. 视图是否都可以更新？(考点)
答案：不是。

• 行列子集视图（从单个基本表导出，只去了几行几列，包含主码）：一般可以更新。
• 不可更新的情况：
  • SELECT子句中包含聚合函数（SUM, AVG, COUNT等）。
  • 使用了 GROUP BY 子句。
  • 使用了 DISTINCT。
  • 由多个表连接导出的视图（部分系统允许更新，但限制很多，王珊教材一般认为较难更新）。

---

五、授权控制 (DCL - GRANT/REVOKE)

1. 授权 (GRANT)
格式： GRANT 权限 ON 对象类型 对象名 TO 用户 [WITH GRANT OPTION];

例子：

/* 把查询Student表的权限授给用户 U1 */
GRANT SELECT ON TABLE Student TO U1;

/* 把对表SC的全部操作权限授给用户 U2，并且允许U2把这个权限转授给别人 */
GRANT ALL PRIVILEGES ON TABLE SC TO U2 WITH GRANT OPTION;

• WITH GRANT OPTION：意味着U2拿到权限后，可以做“二房东”，把权限再分给U3。如果不写，U2只能自己用，不能传。
• 不允许循环授权：如图片5所示，U1->U2->U3->U1 是被禁止的。

2. 收回权限 (REVOKE)
格式： REVOKE 权限 ON 对象类型 对象名 FROM 用户 [CASCADE | RESTRICT];

例子：

/* 收回 U1 对 Student 表的查询权限 */
REVOKE SELECT ON TABLE Student FROM U1;

/* 如果 U2 之前把权限传给了 U3，收回 U2 权限时，系统会自动把 U3 的权限也收回（级联收回） */
REVOKE ALL PRIVILEGES ON TABLE SC FROM U2; 

---

六、 数据库角色 (ROLE)

角色（Role）是为了解决批量授权麻烦的问题。
核心逻辑：权限 -> 角色 -> 用户。

1. 创建角色

CREATE ROLE R1;

2. 给角色授权

就像给用户授权一样，先把权限给这个“空壳”角色。

/* 把Student表的查询、更新权限给角色 R1 */
GRANT SELECT, UPDATE ON TABLE Student TO R1;

3. 将角色授予用户

把 R1 这个身份给用户 U1、U2、U3，他们就同时拥有了 R1 的所有权限。

GRANT R1 TO U1, U2, U3;

4. 收回角色

REVOKE R1 FROM U1;

注：修改角色的权限（如 REVOKE SELECT ON TABLE Student FROM R1），所有拥有该角色的用户权限也会随之改变。

---

七、 修改表结构与完整性约束 (ALTER TABLE)

1. 添加约束 (Add Constraint)

公式：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 (列名);

• ① 加主码 (Primary Key)
  场景：建表时忘了写主码，或者表结构变更。

  ALTER TABLE Student 
  ADD CONSTRAINT PK_Sno PRIMARY KEY (Sno);

• ② 加外码 (Foreign Key)
  场景：建立两个表之间的关联。

  ALTER TABLE SC 
  ADD CONSTRAINT FK_Sno FOREIGN KEY (Sno) REFERENCES Student(Sno);

  注意：REFERENCES 指向被参照表的主码。

• ③ 加检查约束 (CHECK)
  场景：限制性别只能是’男’或’女’，成绩0-100等。

  ALTER TABLE Student 
  ADD CONSTRAINT CK_Gender CHECK (Ssex IN ('男', '女'));

2. 删除列 (DROP COLUMN)

删除列时常涉及到 CASCADE (级联) 和 RESTRICT (限制)。

/* 删除 Student 表的 S_entrance 列 */
ALTER TABLE Student DROP COLUMN S_entrance CASCADE;

• CASCADE: 如果该列被视图或约束引用，引用对象也一起删除。
• RESTRICT: 如果该列被引用，则拒绝删除（默认通常是这个）。

3. 直接修改列 (ALTER COLUMN)

用于修改列的数据类型或长度。

/* 将 Sage 列的数据类型改为 INT */
ALTER TABLE Student ALTER COLUMN Sage INT;

(注：某些具体的数据库系统如MySQL可能用 MODIFY，但王珊教材标准SQL通常用 ALTER COLUMN)

4. 删除约束 (DROP CONSTRAINT)

如果题目让你“取消”某个外码或主码，需要用 DROP CONSTRAINT。

ALTER TABLE Student DROP CONSTRAINT PK_Sno;

---

👉 点击这里阅读下一篇：《E-R 图专题》