SQL数据更新

2022-12-18 13:18:35

1 插入数据
- 1.1 直接插入元组
- 1.2 插入子查询结果
2 修改数据
- 2.1 基于本关系的数据修改
- 2.2 基于外部关系的修改
3 删除数据
- 3.1 基于本关系的数据删除
- 3.2 基于外部关系的删除
4 数据完整性检查
5 更新视图 (View)[^1]

1 插入数据

1.1 直接插入元组

INSERT INTO 关系名 [(属性名1, ..., 属性名n)] VALUES (表达式1, ..., 表达式n);

列出的属性名与表达式个数相同;
表达式i的值是该元组属性i的值;
若没给出属性名列表, 则表示插入一条完整的元组, 其属性顺序与关系模式中定义的属性顺序相同;
若只列出部分属性名, 则该元组的其他属性值为空.

例:

INSERT INTO Student
VALUES ('MA-002', 'Cindy', 'F', 19, 'Math');

INSERT INTO Student (Sno, Sname, Sage, Ssex)
VALUES ('MA-002', 'Cindy', 19, 'F');

1.2 插入子查询结果

INSERT INTO 关系名 (子查询);
将一个查询的全部结果插入到另一个关系中.

例:
将计算机系学生的学号, 姓名, 选课数, 平均分插入到关系CS_Grade(Sno, Sname, Amt, AvgGrade)中.
INSERT INTO CS_Grade
(SELECT Sno, Sname, COUNT(*), AVG(Grade)
FROM Student NATURAL JOIN SC
WHERE Sdept = 'CS'
GROUP BY Sno, Sname);

2 修改数据

2.1 基于本关系的数据修改

UPDATE 关系名 SET 属性名1 = 表达式1, ..., 属性名n = 表达式n [WHERE 修改条件];

若WHERE子句存在, 则将满足修改条件的元组中属性i的值修改为表达式i的值;
若WHERE子句不存在, 则将所有元组中属性i的值修改为表达式i的值;
修改条件仅涉及该关系本身.

例:
将学号为MA-002的学生的年龄修改为20岁.
UPDATE Student SET Sage = 20 WHERE Sno = 'MA-002';
将所有学生的年龄增加1岁.
UPDATE Student SET Sage = Sage + 1;

2.2 基于外部关系的修改

待修改的元组在一个关系中, 而修改条件涉及其他的关系.

例:

UPDATE SC SET Grade = 0 WHERE 'CS' =
(SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno);

UPDATE SC SET Grade = 0 WHERE Sno IN
(SELECT Sno FROM Student WHERE Sdept = 'CS');

UPDATE SC SET Grade = 0 WHERE EXISTS
(SELECT * FROM Student
WHERE Student.Sno = SC.Sno AND Sdept = 'CS');

3 删除数据

3.1 基于本关系的数据删除

DELETE FROM 关系名 [WHERE 删除条件];

若WHERE子句存在, 则将满足删除条件的元组从关系中删除;
若WHERE子句不存在, 则将所有元组从关系中删除 (这与DROP TABLE不同, 因为它并不删除关系本身);
删除条件仅涉及该关系本身.

例:
将学号为MA-002的学生元组删除.
DELETE FROM Student WHERE Sno = 'MA-002';

3.2 基于外部关系的删除

待删除的元组在一个关系中, 而删除条件涉及其他的关系.

例:

DELETE FROM SC WHERE 'CS' =
(SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno);

DELETE FROM SC WHERE Sno IN
(SELECT Sno FROM Student WHERE Sdept = 'CS');

DELETE FROM SC WHERE EXISTS
(SELECT * FROM Student
WHERE Student.Sno = SC.Sno AND Sdept = 'CS');

4 数据完整性检查

数据修改可能导致数据库违反完整性约束, 需要对数据完整性进行检查.

4.1 实体完整性检查

插入元组或对主键中属性的值进行修改时检查;
检查主键中各属性的值是否为空. 只要有一个为空, 就拒绝插入或修改;
检查主键值是否唯一. 若不唯一, 则拒绝插入或修改.

4.2 用户定义完整性检查

插入或修改元组时进行检查;
若不满足用户定义完整性约束, 则拒绝插入或修改该元组.

4.3 参照完整性检查

有四种情况可能会破坏参照完整性.

情况1
在参照关系 (如SC) 中插入一个元组, 该元组的外键 (如Sno) 的值在被参照关系 (如Student) 的主键 (如Sno) 值中找不到, 即悬空 (dangling). 若发生这种情况, 则拒绝插入该元组.
情况2
修改参照关系 (如SC) 中的一个元组, 造成该元组的外键 (如Sno) 的值在被参照关系 (如Student) 的主键 (如Sno) 值中找不到. 若发生这种情况, 则拒绝修改该元组.
情况3
从被参照关系 (如Student) 中删除一个元组, 造成参照关系 (如SC) 中某些元组的外键 (如Sno) 的值在被参照关系 (如Student) 的主键 (如Sno) 值中找不到. 如果发生这种情况, 则采取下列处理方法之一:
- 当参照关系 (如SC) 中没有任何元组的外键值与被参照关系 (如Student) 中待删除元组的主键值相对应时, 方可删除该元组; 否则拒绝删除该元组.
- 级联 (cascade) 删除参照关系 (如SC) 中相关联的元组.
- 将参照关系 (如SC) 中相关联元组的外键 (如Sno) 值置为空 (NULL).
情况4
修改被参照关系 (如Student) 中的一个元组, 造成参照关系 (如SC) 中某些元组的外键 (如Sno) 的值在被参照关系 (如Student) 的主键 (如Sno) 值中找不到. 如果发生这种情况, 则采取下列处理方法之一:
- 当参照关系 (如SC) 中没有任何元组的外键值被参照关系 (如Student) 中待需改元组的主键值相对应时, 方可修改该元组; 否则拒绝修改该元组.
- 级联 (cascade) 修改参照关系 (如SC) 中相关联的元组.
- 将参照关系 (如SC) 中相关联元组的外键 (如Sno) 值置为空 (NULL).
  
  在FOREIGN KEY子句中声明, 当违反参照完整性约束时, 如何处理:
  在FOREIGN KEY子句的末尾加上

ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION
ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION

RESTRICT或NO ACTION: 拒绝删除或修改, 缺省处理方式;
CASCADE: 级联删除或修改;
SET NULL: 置为空值.

例:

FOREIGN KEY (Sno) REFERENCES Strdent(Sno)
ON DELETE RESTRICT
ON UPDATE RESTRICT

5 更新视图 (View)^[1]

和基本关系不同, 不是所有视图都可以更新;
视图可修改的 (updatable) 条件: 视图中的元组和基础关系中的元组存在1对1关系;
如果视图的定义符合以下情况之一, 则视图不可修改 (non-updatable):
- 包含聚集函数
- 包含DISTINCT
- 包含GROUP BY
- 包含UNION
- FROM子句中包含子查询
- FROM子句中包含不可更新的视图
- 包含特定类型连接
- WHERE子句包含相关子查询
视图可插入的 (insertable) 条件:
- 视图可修改
- 视图的属性不能定义为表达式
- 视图中不能包含基本关系中同一属性的多个副本

https://dev.mysql.com/doc/refman/5.5/en/view-updatability.html ↩︎

码农公寓