转载

9、SQL Server 操作数据

插入数据

使用Insert Into 插入

if(exists(select * from sys.databases where name = 'webDB'))  drop database webDB go --创建数据库 create database webDB on primary (  name = 'webDB',  filename='d:/webDB.mdf',  size = 5mb,  maxsize=unlimited,  filegrowth=10% ) log on (  name = 'webDB_log',  filename = 'd:/webDB.ldf',  size=3mb,  maxsize=50mb,  filegrowth=2mb ) use webDB go --创建表 create table student(  id int identity(1,1) primary key,  name varchar(20) not null,  sex char(2) not null,  age int ) --使用Insert Into 插入一行数据 insert into student (name,sex,age) values ('白大伟','男',26) --id为标识列 自动增长 无需为其添加值 --插入某个列 insert into student (name,sex) values ('魏力夫','妖') --如果插入全部列，可以不用写列名 insert into student values ('李东','男',37)

插入标识列Set Identity_Insert

--如果需要在标识列中插入一个值，可以使用Set Identity_Insert语句，但注意 主键不可重复！ set Identity_Insert student on --设置允许插入标识列 insert into student (id,name,sex,age) values (4,'梁不贱','男',24) set Identity_Insert student off --插入后 关闭

使用Insert Into 插入多行数据

--使用Insert Into 插入多行数据,使用逗号分割 insert into student (name,sex,age) values  ('张三','男',18), ('李四','女',19), ('王五','女',20)

使用Select语句插入

--使用Select 语句可以将现有表中的多行数据插入到目标表中 insert into student (name,sex,age) select Sname,Ssex,Sage from oldTable

使用Select Into插入

--使用Select Into 插入数据 --该方法其实是创建了一张新表，然后由select语句将所有行添加到新创建的表中。 select id,name,sex,age into newTable from student --这种方式创建的表，没有主键，索引，以及约束，并且表列的长度也会发生变化。不适合创建永久表

更新数据

使用Update语句更新数据

--需要注意的是，如果不限制范围，则整表都会更新 update student set name='王大锤' where id = 3

引用多表更新数据

--有时会需要引用另外的表，用来限制更新记录 create table [user] (  userID int identity(1,1) primary key,  userName varchar(30) not null,  passWord varchar(30) not null,  RoleId int not null ) create table [role] (  roleId int identity(1,1) primary key,  roleName varchar(30) not null,  roleContent varchar(50) not null ) insert into [user] values ('admin','admin',1),('editor','editor',2),('system','system',3) insert into [role] values ('管理员','网站管理员'),('编辑','网站编辑'),('系统','系统管理员') update [role] set roleContent = '只有user表中的用户名类似adm才会修改' from Role r inner join [user] u on r.roleId = u.RoleId where u.userName like 'adm%' --如果没有这个限制条件 则整表都更新 select * from [role]

删除数据

使用Delete删除

--如果不限制条件则整表都会被删除。 delete from student where id = 1

引用多表删除数据

--先删除user表中的admin delete from [user] where userName = 'Admin'  delete from [role] from [role] r left outer join [user] u on r.roleId = u.userID where u.RoleId is null --删除权限表中未被user表中引用的数据,管理员被删除  select * from [role]

使用truncate删除所有行

truncate table oldTable --truncate 会删除所有行,无法指定范围.

合并数据

Merge 语句是一个多种功能的混合语句，在一个查询中可以完成Insert、Update、Delete等功能。

根据与源表联接的结果，对目标表执行插入、更新或删除操作。源表中包含即将被添加（或更新）到目标表中的数据行，而目标表接受插入（或更新）操作，可以对两个表进行同步操作。

SQL Server 2008之前的版本中是没有的，所以以前都是先删掉再添加，或写一些分支条件判断存在否再insert 或update。

--创建源表 create table sourceTable(  id int,  content varchar(30) ) --创建目标表 create table targetTable(  id int,  content varchar(30) ) --插入测试数据 insert into sourceTable values (1,'S001'),(2,'S002'),(3,'S003'),(4,'S004'),(5,'S005') insert into targetTable values (1,'target001'),(2,'target002'),(6,'target006'),(7,'target007') select * from sourceTable --源表 --1  S001 --2  S002 --3  S003 --4  S004 --5  S005 select * from targetTable --目标表 --1  target001 --2  target002 --6  target006 --7  target007 --编写merge语句 merge into targetTable t --目标表 using sourceTable s   --源表 on t.id = s.id    --类似join 完成两表之间的匹配 when matched    --如果两表中有值被匹配，更新 then update set t.content = s.content  when not matched   --如果没有匹配结果，插入 then insert values(s.id,s.content) when not matched by source --目标表中存在但源表中不存在，删除 then delete; --再次查询，则两表同步

返回输出的数据

OUTPUT 子句可以把受影响的数据行返回给执行请求的任何接口，并且可以插入到一张表中。

OUTPUT子句可以引用inserted或deleted虚拟表，取决于需要修改前（deleted）或修改后（inserted）的数据。

输出insert语句的执行结果

insert into student output inserted.id,inserted.name,inserted.sex,inserted.age --inserted.* 所有 select '哈哈','女',24 --返回insert语句插入的记录，对于查找服务器生成的值并返回给应用程序是很有用的。

输出update语句的执行结果

update student set name='张振' output deleted.name as oldName,inserted.name as updateValue where id = 4 --返回修改之前的名字 和修改之后的名字 --可以用来追踪对数据库的删除操作

将OUTPUT结果插入一张表中

--创建审计表 create table db_Audit (  id int not null,  name varchar(50) not null,  sex  varchar(50) not null,  age int,  deleteDate datetime not null   constraint DF_deleteDate_TOday default(getdate()) --默认约束 当前时间 ) delete from student output deleted.id,deleted.name,deleted.sex,deleted.age into db_Audit(id,name,sex,age) where id <10 -- 将id小于10的全部删除并插入到审计表中 select * from db_Audit

Merge通过output子句，可以将刚刚做过变动的数据进行输出。

merge into targetTable t    --目标表 using sourceTable s            --源表 on t.id = s.id                --类似join 完成两表之间的匹配 when matched                --如果两表中有值被匹配，更新 then update set t.content = s.content  when not matched            --如果没有匹配结果，插入 then insert values(s.id,s.content) when not matched by source    --目标表中存在但源表中不存在，删除 then delete output $action as action,inserted.id as 插入的id, inserted.content as 插入的内容, deleted.id as 删除的id, deleted.content as 删除的内容;