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'; |
|