地点：基地

时间：2014.03.11

-----------------------------------------------------------------------------

一、删除

  数据库元组的删除与查询非常类似。当然元组删除是删除操作的最小单位，我们并不能只删除某些属性上的值，比如：

delete from r where p;

delete from instructor;

值得注意的是delete命令只能作用于一个关系。如果想从多个关系中删除元组，必须在每个关系上都delete一下。

实例

1.从instructor关系中删除与Finance系教师相关的所有元组。

delete from instructor
where dept_name=‘Finance‘;

delete from instructor
where salary between 13000 and 150000;

delete from instructor
where dept_name in (select dept_name
                              from department
                               where building=‘Watson‘);

-----------------------------------------------------------------------------

二、插入

最简单的insert语句是单元组插入请求。例如：

insert into course
        values(‘CS-437‘,‘Database Systems‘,‘Comp.Sci.‘,4);

insert into course(course_id,title,dept_name,credits)
        valuses(‘CS-437‘,‘Database System‘,‘Comp.Sci‘,4);

insert into course(title,course_id,crdits,dept_name)
        values(‘Database System‘,‘CS-437‘,4,‘Comp.Sci‘);

insert into instructor
         slelect ID,name,dept_name,18000
         from student
         where dept_name=‘Music‘ and to_cred>144;

三、更新

在某些场景下，我们可能并不希望改变整个元组，而只是对元组某个属性上的值做出改变，这需要用到更新语句update，例如将所有教师的工资增长5%:

update instructor
set salary=salary*1.05;

若只给工资低于70000美元的教师长工资，可这样写

update instructor
set salary =salary*1.05
where salary<70000;

update instructor
set salary=salary*1.05
where salary<(select avg(salary)
                     form instructor);

upadate instructor
set salary=case
     when slaray<=100000 then salary*1.05
     else salary*1.03
     end

case
  when pred1 then result1
  when pred2 then result2
  when pred3 then result3
  ...
  else result0
end

SQL——数据库的修改（删除、插入与更新）,古老的榕树,5-wow.com