SQL Server 删除、插入、修改操作
一、插入操作 INSERT
1、插入单个元组
格式:INSERT INTO 表名[(列名,…)] VALUES (列值,…);
INSERT INTO student VALUES('2003001' ,'Rain Man',18,'男','电商','管理学院','徐州')
INSERT INTO SC (Sno,Cno) VALUES ('2003001' , 'C003');
2、插入子查询的结果:
格式:INSERT INTO 表名[(列名,…)] (子查询);
INSERT INTO S_G(sno,AVG_G)
(
SELECT sno,AVG(GRADE) FROM SC
WHERE Sno IN
(
SELECT Sno FROM Student
WHERE SEX=‘男’)
GROUP BY Sno
HAVING AVG(GRADE)>80
);
二、删除操作 DELETE
格式: DELETE FROM 表名 [WHERE 条件];
只能对整个元组操作,不能只删除某些属性上的值
只能对一个关系起作用,若要从多个关系中删除元组,则必须对每个关系分别执行删除命令
DELETE FROM SC 清空SC表
DELETE FROM SC WHERE GRADE Is NULL;
DELETE FROM SC
WHERE
Cno='C004' AND Grade < ( SELECT AVG(GRADE) FROM SC WHERE Cno='C004')
三、修改操作 UPDATE
格式: UPDATE 表名 [别名]
SET 列名=表达式,…
[WHERE 条件];
格式: UPDATE 表名 [别名]
SET (列名,…) = (子查询)
[WHERE 条件];
UPDATE EMPLOYEE
SET JOB='MANAGER',Salary= Salary*1.2
WHERE NAME = 'MARTIN';
UPDATE EMPLOYEE
SET Salary=( SELECT 1.1*AVG(Salary) FROM EMPLOYEE WHERE JOB='SALESMEN')
WHERE JOB='SALESMEN';