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 刪除的內容;

9、SQL Server 操作資料