9、SQL Server 操作数据

插入数据

使用Insert Into 插入

if(exists(select * from sys.databases where name = 'webDB'))  drop database webDBgo--创建数据库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 webDBgo--创建表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,ageinto newTablefrom 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 rinner join [user] uon r.roleId = u.RoleIdwhere u.userName like 'adm%' --如果没有这个限制条件 则整表都更新select * from [role]

删除数据

使用Delete删除

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

引用多表删除数据

--先删除user表中的admindelete from [user] where userName = 'Admin'delete from [role]from [role] rleft outer join [user] uon r.roleId = u.userIDwhere 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    S005select * 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 studentoutput 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 updateValuewhere 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 studentoutput deleted.id,deleted.name,deleted.sex,deleted.ageinto 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 deleteoutput $action as action,inserted.id as 插入的id,inserted.content as 插入的内容,deleted.id as 删除的id,deleted.content as 删除的内容;