--4.1插入新纪录
插入多行记录时只需要提供多行values即可 如果没有指定目标列,就必须为所有列指定值,并按照DBMS中select * 显式出来的顺序插入。
insert into dept (deptno,dname,loc)
values (1,'A','B'),
(2,'B','C')
--4.2插入默认值
创建一个表,ID默认为0 通过default关键字标注
create table D (id integer default 0)
插入时的使用
insert into D (id) values (default)
所有值都使用默认值
insert into D default values
--4.3用null覆盖默认值
在有默认值的列上显式的插入Null值
insert into d (id, foo) values (null, 'Brighten')
--4.4将一张表的行复制到另一张表的行
只需要在insert语句后跟一个返回所需行的查询即可
insert into dept_east (deptno,dname,loc)
select deptno,dname,loc
from dept
where loc in ( 'NEW YORK','BOSTON' )
--4.5复制表定义
创建一个表,与另一个表的结构相同,但不包含数据行:在一个不返回任何行的查询中使用Into
select *
into dept_2
from dept
where 1 = 0
--4.6同时插入多张表
DB2、Oracle 支持 SQL Server暂不支持
--4.7 禁止在特定列中插入值
如果想禁止用户在表的某列中插入数据,可以新建一个视图,将允许插入的列暴露给用户,只允许用户填 充该视图的数据,并在后台将视图中的数据转移到真正的表中
--4.8修改表中的记录
修改表中的全部行或部分行:如将部门号为20的员工薪水提高10%
select deptno,ename,sal
from emp
where deptno = 20
order by 1,3
--4.9 仅当存在匹配行时才更新
你想更新一张表的行,条件是在另一张表也存在相应的行。如果出现在奖金表中的员工薪水提高20%
使用子查询找出EMP表中那些也同样存在与表EMP—Bouns表中的员工
update emp
set sal=sal*1.20
where empno in ( select empno from emp_bonus )
也可以用exists代替in
update emp
set sal = sal*1.20
where exists ( select null
from emp_bonus
where emp.empno=emp_bonus.empno )
在SELECT列表中的NULL并不影响结果的执行,反而会提高执行效率
--4.10 使用来自另一张表中的值进行更新
如果员工的 EMP.DEPTNO 与 NEW_SAL 表中的某一行的 DEPTNO 相同,就将其 EMP.SAL 更新为该行的 SAL 值,并将其 EMP.COMM 更新为该行的 SAL 值的 50%。
update e
set e.sal = ns.sal,
e.comm = ns.sal/2
from emp e,
new_sal ns
where ns.deptno = e.deptno
--4.11 合并记录
根据另一张表来更新表,如果存在相同记录就更新,如果不存在,就插入。更新后不满足条件的数据进行删除。
merge into emp_commission ec
using (select * from emp) emp
on (ec.empno=emp.empno)
when matched then
update set ec.comm = 1000
delete where (sal < 2000)
when not matched then
insert (ec.empno,ec.ename,ec.deptno,ec.comm)
values (emp.empno,emp.ename,emp.deptno,emp.comm);
--4.12 删除表中的所有记录
delete from emp
--4.13 删除特定记录
delete from emp where deptno=10
--4.14 删除单条记录
delete from emp where empno=7782
--4.15 删除违反引用完成性的记录
当一张表中引用了另一张表中不存在的记录时,需要删除该记录。
delete from emp
where deptno not in (select deptno from dept)
--4.16 删除重复记录
使用子查询根据姓名查找出每个姓名的最小ID,并删除ID不在此表中的记录(及多余的重复记录)
delete from dupes
where id not in ( select min(id)
from dupes
group by name )
--4.18 删除在另一张表中引用了的记录
将所属部门发生事故不少于3次的部门的所属员工从EMP表中删除
delete from emp
where deptno in ( select deptno
from dept_accidents
group by deptno
having count(*) >= 3 )
《SQL经典实例》第四章 插入、更新和删除
于 2023-05-17 15:20:26 首次发布