数据库—DML(数据更新)
数据插入
- 向基本表插入单个元组
- INSERT INTO T [(A1, …, Ak) ]VALUES (c1, …, ck)
- 其中T通常是基本表,也可以是视图,A1, …, Ak是T的属性,c1, …, ck是常量
- 将查询的结果(多个元组)插入基本表
- INSERT INTO T [(A1, …, Ak) ]
key 1:
INSERT INTO Students
VALUES (‘201616010’, ‘司马相如’, ‘男’, 1997-01-28, ‘2006’, ‘计算数学’, ‘MATH’)
key 2:
INSERT INTO Students (Sno, Sname, Sex, Birthday, Enrollyear, Speciality, Dno)
VALUES (‘200616010’, ‘司马相如’, ‘男’, 1985-01-28, ‘2006’, ‘计算数学’, ‘MATH’);
INSERT INTO Cardinf (Card-no, Name, Balance)
SELECT Tno, Tname, 100.00
FROM Teachers
WHERE Dno= ‘IE’;
数据删除
- DELETE语句格式为: DELETE FROM T [ WHERE <删除条件> ]
- 其中T通常是基本表,但也可以是某些视图
- <删除条件>与SELECT语句中的查询条件类似
DELETE FROM Students
WHERE Sno = ‘201624010’;
DELETE FROM Students;
DELETE FROM SC
WHERE Sno IN
(SELECT Sno
FROM Students
WHERE Speciality=‘计算机软件与理论’);
数据修改
- UPDATE语句格式为
- UPDATE T SET A1 = e1, …, Ak = ek [WHERE <修改条件> ]
- 其中T通常是基本表,但也可以是某些视图
- A1, …, Ak是T的属性,而e1, …, ek是表达式
- <修改条件>与SELECT语句中的查询条件类似
UPDATE Teachers
SET Title = ‘副教授’
WHERE Tno = ‘B050041’;
UPDATE SC
SET Grade = Grade + 5
WHERE Grade<60 AND Cno IN
(SELECT Cno
FROM Courses
WHERE Cname=‘软件工程’);