SQL查询的艺术学习笔记--数据的更新和删除

use seldata
--补充：删除表中的重复记录
select * from course 
where cno in
(select cno from course group by cno having COUNT(cno)>1)
--查询表中行大于1(重复)的记录
select distinct * into #temp from teacher
delete teacher
go
insert teacher select * from #temp 
go 
drop table #temp
--查询不重复记录写入临时表，删除原表记录，再从临时表查询
--返回不重复记录
--同样针对如果字段重复值的可以用join on来进行处理
--update 语句的基本语法
update table_name
set column1=value1
    column2=value2
    ....
where serch_condition
--where 决定了更新的条件，不指定where则为更新所有行
use QNJB
Select COUNT(*)
From sysobjects s
Where
Exists
(Select * From syscolumns 
Where ID = s.ID and name like '%dname%')
--查询当前数据库下使用某个字段的数据表


--update 语句更新列值
use seldata
select * into new_teacher
from teacher
--复制表
select * from new_teacher
--更新单列数据
update new_teacher
set sal=sal+100
where sex='女'
select * from new_teacher
 
update new_teacher
set sal=sal-100
where sex='女'


select a.tno,a.tname,a.sal,a.dname,(a.sal-b.sal) as 工资差
from new_teacher as a, teacher as b 
where a.cno=b.cno
 --求两表中相同字段运算
 --多例数据的更新
update new_teacher
set sal=sal*1.1,
    age=age+1
select * from new_teacher  
--通过更新删除表中的数据
  update new_teacher
  set sal=null
  where dname='生物'
--将指定条件的工资清零
select * from new_teacher  where dname='生物'
--在对指定列做数据修改或者清空时
--可以通过聚合函数来进行统计
select COUNT(*) from new_teacher
where dname='生物'
--分别对各院系进行统计
select dname,COUNT(dname)as 人数 from new_teacher
group by dname 
--统过统计后我们再进行数据操作可以得知操作结果是否正确
--利用子查询更新多行值
select * 
into newteacher2
from teacher
select * from newteacher2
--复制表teacher
update newteacher2
set sal=sal+sal*0.5
where sex='男'
and sal<(select AVG(sal) from newteacher2 where sex='男')
select * from newteacher2
select a.tno,a.tname,a.cno,a.sal,b.sal as sal2,(a.sal-b.sal) as upsal
into #teacher2
from newteacher2 as a
left join teacher as b
on a.cno=b.cno
order by a.tno
--合并查询创建临时表
select * from #teacher2
select COUNT(upsal) from #teacher2 where upsal<>0 and upsal is not null
--统计增加工资人数
--根据外表值更新数据
update newteacher2 
set sal=sal+100
where cno in (select cno from course where cno=newteacher2.cno and ctime>=40)
or cno in (select cno from student where cno=newteacher2.cno group by cno having MAX(mark)>=85)
--第二子查询使用group去重复值
select * from newteacher2
select * from newteacher2 where cno in
(select a.cno from student as a,newteacher2 as b where a.cno=b.cno  group by a.cno having MAX(mark)>=85)


select a.tno,a.tname,a.cno,a.sal,(a.sal-b.sal) as upsal
from newteacher2 as a 
left join teacher as b
on a.cno=b.cno
where a.sal>0
--分步更新表
use seldata
update newteacher2
set sal=sal*0.95
where sal<=1500
and   sal>800
update newteacher2
set sal=sal*0.9
where sal>1500
--注意在变更数据需要考虑数据变化，影响确定数据更新的步骤
--delete删除表中的数据
--delete语句的基本语法
delete from table_name
where  serch_condition
--where确定删除符合条件行，没有where语句则删除所有行
delete from newteacher2
where tno=6
select * from newteacher2
--指定条件删除单行数据
delete from newteacher2
where sal<(select AVG(sal) from newteacher2 ) and sex='男'
select * from newteacher2
--delete 删除所有行
delete from  newteacher2
select * from newteacher2
rollback 


--truncate<截断表？> table 语句
--语法
truncate table table_tablename
--复制表内容：当两张表结构完全一致时可以使用不指定字段值进行内容复制如下列：
insert into newteacher2
select * from teacher
truncate table newteacher2
--直接清除掉表内容
select * from newteacher2


--通过更新视图更新表
--UPDATE更新视图数据更新至表的语法
update view_name
set column1=value1,
    column2=value2,
    .....
    columnN=valueN
where search_condition
--注意：可更新的视图必须是基于单个的底层表
--通过更新视图来更新表数据有两个好处
--A：可以限制用户可更新底层表中的列
--B：可以使列名具有更好的描述性 直白讲就是我们只更新视图可以更新指定列。而不操作数据表
create view view_newteacher
(vtno,vtname,vdname,vsal)
as 
select tno,tname,dname,sal
from teacher where sex='男' 
select * from view_newteacher
drop view view_newteacher
--创建视图
select COUNT(*) as 工资少于1000人数  from view_newteacher where vsal<1000
update view_newteacher
set vsal=vsal*1.1
where vsal<1000
--等于这条语句
update teacher
set sal=sal*1.1
where sal<1000  and sex='男'
 
--更新视图的数据
select * from teacher where sex='男'
select * from view_newteacher  
select COUNT(*) as 工资少于1000人数 from view_newteacher where vsal<1000   
update view_newteacher
set vsal=vsal/1.1
where vsal<1000


--通过视图删除表数据
--delete 语句删除表中数据基本语法
delete from view_name
where  serch_condition
select * from view_newteacher
delete from view_newteacher
where vsal<1000
select * from teacher
--上面语句等于：
delete from teacher
where sal<1000
and sex='男'