第四章 插入、新建和删除 （SQL server经典实例）

第四章 插入、新建和删除

/****** SSMS 的 SelectTopNRows 命令的脚本  ******/
SELECT TOP (1000) [DEPTNO]
      ,[DNAME]
      ,[LOC]
  FROM [Text_02].[dbo].[dept]

  --插入新记录
insert into dept (deptno, dname, loc)
values  (50 , 'PROGRAMMING' , 'BALTIMORE' )
--若报错：字符串被截断，可修改dname数据类型nchar（--）
delete from dept where deptno=50 

--多行插入
insert into dept (deptno, dname, loc)
values (1, 'A','B') , (1, 'C','D')

--插入时使用默认值
create table D (id integer default 0)
insert into D  values (default)
insert into D (id) values (default)--指明某一列插入默认值
select * from D

create table C (id integer default 0, foo varchar(10))
insert into C (foo) values ('Bar')
select * from C

--insert语句的values列表可以显式指定null
insert into C (id, foo) values (null,'Brighten')  --null覆盖默认值

--复制数据到另一个表
create table dept_east (deptno smallint ,  dname nchar(20),  loc nchar(10))

insert into dept_east (deptno, dname, loc)
select deptno, dname ,loc
from dept
where loc in ('NEW YORK','BOSTON')

select * from dept_east 

--复制表定义，但不复制数据
select * into dept_2 from dept 
								where 1=0
select * from dept_2


--向一个简单视图插入数据===对基础表插入数据
--所以创建只有部分列的视图，则只能对这些列进行插入
--而没有放入视图的列 相当于被禁止插入数据
create view new_emps as select empno, ename, job  from emp
 insert into new_emps ( empno, ename, job) values (1, 'Jonasthan', 'Editor')
 select * from new_emps 
 select * from emp

 --更新记录
 --为部门编号为20的员工统一加薪10%
 select deptno, ename, sal from emp where deptno =20 order by 1,3

 update emp 
		set sal = (sal*1.1)
		where deptno=20

 select deptno, ename, sal from emp where deptno =20 order by 1,3


 --当满足一定条件时，更新记录
 --当员工出现在EMP_BONUS表中时。将工资上涨20%
 update emp 
			set sal=sal*1.20
			where empno in (select empno from emp_bonus)

 select e.empno , e.ename , e.sal from emp e ,  emp_bonus  eb
		where  eb.empno=e.empno


--使用另一个表的数据来更新记录
--step1 创建新工资表
create table new_sal (DEPTNO smallint , sal smallint)
insert into new_sal values (10,4000)
select * from new_sal

--使用new_sal表的数据来更新emp表中的部分员工的工资和提成
--deptno列相匹配时，emp的sal更新为new_sal的sal
--emp的comm列更新为new_sal的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

	select e.deptno, e.ename,  e.sal, e.comm from
				emp  e , new_sal ns
		 where  ns.deptno=e.deptno

--删除全表记录 
delete from emp --之后没有where，会删除emp表的全部记录

--删除指定记录
delete from emp where empno=1
select * from emp

--删除违反参数完整性的记录
--删除一些所属部门不存在的员工
delete from emp where deptno not in (select deptno from dept)
--使用not exists

delete from emp where not exists (			   --emp的deptno代到dept匹配
		select * from dept							       --相同行结果非空，判为false ，不删
				where dept.deptno=emp.deptno   --不同行结果为空，判为true ，执行delete
				)

--删除重复数据
--step1 创建有重复数据的表
create table dupes  (id  integer ,name varchar(20))
		insert into dupes values (1,'NAPOLEON')
		insert into dupes values (2,'DYNAMITE')
		insert into dupes values (3,'NAPOLEON')
		insert into dupes values (4,'SHE SELLS')
		insert into dupes values (5,'SEA  SHELLS')
		insert into dupes values (6,'SEA  SHELLS')
		insert into dupes values (7,'SEA  SHELLS')

		select* from dupes order by 1
--step2 删除名字相同的行
		delete from dupes 
				where id  not in
					( select  min (id) from dupes group by name)  --若 名字相同，则取id最小组成一组数据


--删除被其他表参照的记录
--step1 创建的dept_accidents表， 记录发生事故的部门以及事故的类型
create table dept_accidents (deptno integer , accident_name  varchar(20))
		insert into dept_accidents values (10,'BROKEN FOOT')
		insert into dept_accidents values (10,'FLESH WOUND')
		insert into dept_accidents values (20,'FIRE ')
		insert into dept_accidents values (20,'FIRE')
		insert into dept_accidents values (20,'FLOOD')
		insert into dept_accidents values (30,'BRUISED GLUTE')

select * from 	dept_accidents 

--对于发生三起以上事故的部门，希望从emp表上删掉这些部门的员工记录
delete from emp 
				where deptno in (
				select deptno from dept_accidents group by deptno having count (*) >=3 )

select deptno from dept_accidents group by deptno having count (*) >=3   --分组后，计组内行数

插入新记录

复制数据到另一个表

复制表定义，但不复制数据

指定列禁止插入数据

更新纪录

使用另一个表的数据来更新记录

删除指定记录

删除违反参数完整性的记录

删除重复数据

删除被其他表参照的记录