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 删除的内容;