第四章 插入更新和删除
4.1 插入数据
insert into dept (deptno,dname,loc) values (50,'PROGRA','NEW YOURK');
4.2 从一个表向另一个表中复制
insert into dept_east(deptno,dname,loc)select deptno,dname,loc from dept where loc in ('NEW YORK','BOSTON');
4.3 复制表结构
create table dept_2 as select * from dept where 1 = 0; -- where is false 才没有数据复制过来
4.4 阻止对某几列插入 创建视图 让用户只操作视图中的列 当插入视图中时也相当于插入原表中
create view emp_new as select empno,ename,job from emp;
insert into emp_new (empno,ename,job) values(1,'weiguoyuan','DBA');
4.5 更新
update emp set sal = sal*1.1 where empno = 20;
4.6 用其他表中的值更新
用new_sal的sal替换 emp中的值 当deptno相同时，并将comm设置成sal的一半
update emp e set (e.sal,e.comm) = (select ns.sal,ns.sal/2 from new_sal ns where ns.deptno = e.deptno) where exists (select null from new_sal ns where ns.deptno = e.deptno); -- 后面的where语句用来判别emp中那些行需要更新 select null 语句中如果where语句满足条件 则会返回null exists就会判断为真
4.7 删除
delete from emp where empn0 = 927;
4.8 删除违反参照完整性的记录
delete from emp where not exists(select * from dept where dept.deptno=emp.deptno); delete from emp where deptno not in(setect deptno from dept);
4.9 删除重复记录
delete from dupes where id not in (select min(id) from dupes group by name);
4.10 删除从其他表引用的记录
delete from emp where deptno in (select deptno from dept_accidents group by deptno having count(*) >= 3); -- 删除发生3次事故以上的部门员工