Use Demo go createproc proc4 as declare@iSalaryint set@iSalary= (selectSum(salary) from emp) print@iSalary go exec proc4 createproc proc5 ( @iSalaryint output ) as set@iSalary= (selectSum(salary) from emp) print@iSalary go declare@salsint declare@salsbackint exec proc5 @sals output set@salsback=@sals print@salsback insertinto emp(userid,deptcode,salary) values('ede','developer','100') select*from emp setidentity_insert dbo.emp off select@@identityfrom emp insertinto emp(userid,deptcode,salary)values('w3q[=we3w','dev','22') select@@identityfrom emp altertable dbo.emp { alter colume } select*from dbo.emp select userid,deptcode,salary,test,indentity from dbo.emp createtable tab4 ( id intidentity(1,1) primarykey, code uniqueidentifierdefaultnewid(), name varchar(50) ) insertinto tab4(name) values('mahui') select*from tab4 where id ='mahui' selectcoalesce(null,null,null,1) createtable table1 ( a int, b int, c int, d int ) createtable table2 ( a int, b int, c int, d int ) createclusteredindex ix_a on dbo.table1(a) createindex ix_b on dbo.table1(b) createindex ix_c on dbo.table1(c) createindex ix_d on dbo.table1(d) createclusteredindex ix_a on dbo.table2(a) createindex ix_b on dbo.table2(b) createindex ix_c on dbo.table2(c) createindex ix_d on dbo.table2(d) declare@iint set@i=0 while (@i<5) begin insertinto table1(a,b,c,d) values(1,1,1,1) set@i=@i+1 end declare@iint set@i=0 while (@i<200000) begin insertinto table2(a,b,c,d) values(1,1,1,1) set@i=@i+1 end select a from table1 where a=1 select a from table2 where a=1 select a from table1 where b=1 select a from table2 where b=1 select b from table1 where b='1' select b from table2 where b='1' select b from table1 where b='1' select b from table2 where b='1' select a,b from table1 where a =1 select a,b from table2 where a =1 select a,b from table1 where b =1 select a,b from table2 where b =1 select a,b,c from table1 where a =1 select a,b,c from table2 where a =1 select a,b,c from table1 where b =1 select a,b,c from table2 where b =1 select b,c from table1 where b =1 select b,c from table2 where b =1 select b,c from table1 where a =1 select b,c from table2 where a =1 SELECTdatepart(YY,GetDate()) SELECTdatepart(DD,GetDate()) SELECTdatepart(MM,GetDate()) SELECTdatepart(SS,GetDate()) SELECTdatepart(Minute,GetDate())
select EmployeeID,[2001],[2002],[2003],[2004]into #temp from (select EmployeeID,year(OrderDate) as Freight_year ,Freight from Purchasing.PurchaseOrderHeader) as ii pivot ( sum(Freight) for Freight_year in ([2001],[2002],[2003],[2004]) )as sa select* from(select*from #temp)as oo unpivot ( Freight for Freight_year in ([2001],[2002],[2003],[2004]) )as dd
CreateDataBase Trans use Trans createtable NonPrimaryKey ( UserID varchar(50), Salary int ) insertinto NonPrimaryKey values('mahui','20') insertinto NonPrimaryKey values('Qiuwh','60') insertinto NonPrimaryKey values('HuangH','80') insertinto NonPrimaryKey values('HAHA','100') insertinto NonPrimaryKey values('mahui','20') insertinto NonPrimaryKey values('mahui','20') BeginTran Update NonPrimaryKey Set Salary=200where UserID ='Qiuwh' update NonPrimaryKey Set UserID='haha'where Salary ='100' select@@spid rollback select*from NonPrimaryKey with (holdlock)事务结束后释放锁 select*from NonPrimaryKey with (Repeatableread,paglock)跟holdlock是一样的 select*from NonPrimaryKey with (Repeatableread,paglock)paglock可以指定锁的颗粒 select*from NonPrimaryKey with (nolock)/with (ReadUnCommited)不加任何锁 select*from NonPrimaryKey with (readpast)上共享锁,但是跳过排他锁,不读取未提交数据 select*from NonPrimaryKey with (nowait)碰到排他锁就抛异常 set lock_timeout 3000 DBCC UserOptions select*from NonPrimaryKey begintran altertable NonPrimaryKey add test varchar(20) rollback select@@trancount rollback begintran select*from NonPrimaryKey with (holdlock) settransactionisolationlevelreaduncommitted settransactionisolationlevelreadcommitted settransactionisolationlevelrepeatableread settransactionisolationlevelSerializable settransactionisolationlevel Snapshot //2005新特性 sp_help NonPrimaryKey select*from NonPrimaryKey BeginTran settransactionisolationlevelreaduncommitted update NonPrimaryKey set UserID='yujia'where Salary ='20' insertinto NonPrimaryKey values('YULA','120') Rollback BeginTran settransactionisolationlevelreadcommitted update NonPrimaryKey set UserID='yujia'where Salary ='20' insertinto NonPrimaryKey values('YULA','120') Rollback BeginTran settransactionisolationlevelrepeatableread update NonPrimaryKey set UserID='yujia'where Salary ='20' insertinto NonPrimaryKey values('YULA','120') Rollback BeginTran settransactionisolationlevelSerializable insertinto NonPrimaryKey values('HAHA','120') update NonPrimaryKey set UserID='YULA'where Salary ='20' Rollback BeginTran settransactionisolationlevel Snapshot insertinto NonPrimaryKey values('HAHA','120') update NonPrimaryKey set UserID='YULA'where Salary ='20' Rollback --dead lock createtable t1 ( id intprimarykey, val varchar(20) notnull ) createtable t2 ( id intprimarykey, val varchar(20) notnull ) insertinto t1 values(1,'11111') insertinto t2 values(2,'11111') --session 1 begintran update t1 set val='new value'where id =1--t1时刻 update t2 set val='new value'where id =2--t3时刻 commit --session 2 begintran update t2 set val='new value'where id =1--t2时刻 update t1 set val='new value'where id =1--t4时刻 commit DbCC UserOptions select*from t2 sp_lock xact_state() =-1表示事务是活动的,但是出现了严重的错误 xact_state() = 1表示事务是活动的,并且事务可以被commit,即使出现了一些错误 xact_state() = 0表示事没有活动的 用begin try end try begin catch end catch 来保证运行正确,不会出现xact_state() = 1中可能出现的出现了部分错误结果还是能提交的问题
-----------------------------------------------------Day3----------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------- createtable EmpSalary ( UserID varchar(50) primarykey, DeptCode varchar(50) notnull, Salary float ); insertinto EmpSalary values('chenb','dev1',300); insertinto EmpSalary values('lizm','dev1',200); insertinto EmpSalary values('weisg','dev1',null); insertinto EmpSalary values('qiuwh','rd',100); insertinto EmpSalary values('zhumd','rd',200); insertinto EmpSalary values('yuanlh','rd',100); select*from EmpSalary selectavg(distinctisnull(salary,0)) from EmpSalary selectcount (distinct Salary) from EmpSalary select*from empsalary update empsalary set salary =-100where userid ='chenb' select salary from empsalary selectabs(salary) from empsalary selectcount ( distinctabs(salary)) from empsalary selectavg(isnull(salary,50)) from EmpSalary selectsin(pi()/6) droptable ProductSale; createtable ProductSale ( id intidentityprimarykey, Product varchar(50), SaleMonth int, Sale int ); insertinto ProductSale values('Book',1,140); insertinto ProductSale values('Book',2,220); insertinto ProductSale values('Book',3,360); insertinto ProductSale values('Book',4,240); insertinto ProductSale values('Toy',1,220); insertinto ProductSale values('Toy',2,310); insertinto ProductSale values('Toy',3,120); insertinto ProductSale values('Toy',4,220); --------------------------------------------- insertinto ProductSale Values(null,1,0); insertinto ProductSale Values(null,2,0); insertinto ProductSale Values(null,3,0); insertinto ProductSale Values(null,4,0); select*from ProductSale select DeptCode,sum(salary) as total from EmpSalary groupby deptcode select DeptCode,Sum() select product,sum(sale) as'sale' from productsale groupby product orderby product select salemonth,sum(sale) as'sale' from productsale groupby salemonth orderby salemonth select*from productsale selectcount(distinct(sale)) from productsale select product,salemonth,sum(sale)as'sale',id from productsale groupby product,salemonth,id with rollup orderby product,salemonth select product,salemonth,sum(sale)as'sale' from productsale groupby product,salemonth with rollup orderby product,salemonth select product,salemonth,sum(sale)as'sale' from productsale groupby salemonth,product with rollup orderby product,salemonth select product,salemonth,sum(sale)as'sale' ,id from productsale groupby salemonth,product,id with rollup orderby product,salemonth select product,salemonth ,id,sum(sale)as'sale' from productsale groupby salemonth,product,id with cube orderby product,salemonth,id select product,salemonth,sum(sale) as'sale' from productsale groupby salemonth,product with cube orderby product,salemonth select*from empsalary groupby deptcode having salary>100 select product=case when (product isnull) and (grouping(product)=1) and (salemonth isnotnull) then'xiaoji' when (product isnull) and (grouping(product)=1) and (salemonth isnull) and (grouping(salemonth)=1) then'zongji' else product end, salemonth,sum(sale) as'sale',grouping(product),grouping(salemonth) from productsale groupby salemonth,product with rollup orderby salemonth,product selectisnull(product,0) product =case when (product isnull) and (salemonth isnotnull) then'xiaoji' when (product isnull) and (salemonth isnull) then'zongji' else product end, salemonth,sum(sale) as'sale',grouping(product),grouping(salemonth) from productsale groupby salemonth,product with rollup orderby salemonth,product selectisnull(product,0) as product salemonth,sum(sale) as'sale',grouping(product),grouping(salemonth) from productsale groupby salemonth,product with rollup orderby salemonth,product select*from productsale orderby product,salemonth desc computesum(sale)by product,salemonth select*from productsale select product ,sale, rank() over(partition by product orderby sale ) as'rank' from productsale select product ,sale, dense_rank() over(partition by product orderby sale ) as'denserank' from productsale select product ,sale, row_number() over(partition by product orderby sale ) as'denserank' from productsale select product ,sale, ntile(2) over(partition by product orderby sale ) as'denserank' from productsale select product ,sale, denserank() over(partition by product orderby sale ) as'denserank' from productsale deletefrom productsale where product isnull select product,[1]as'1',[2]as'2',[3]as'3',[4]as'4' from ( select product,salemonth,sale from productsale )as ps pivot ( sum(sale) for salemonth in ([1],[2],[3],[4]) )as pvt select*from productsale select id, salemonth,book,toy from ( select id, product,salemonth,sale from productsale )as ps pivot ( sum(sale) for product in (book,toy) )as pvt select*from productsale groupby salemonth,product select product,[1]as'1',[2]as'2',[3]as'3',[4]as'4' from ( select product,salemonth,sale from productsale )as ps pivot ( sum(sale) for salemonth in ([1],[2],[3],[4]) )as pvt select*from (select product,[1],[2],[3],[4]from #temp)as tp unpivot ( sale for salemonth in([1],[2],[3],[4]) ) as pvt select EmployeeID,[2001],[2002],[2003],[2004]into #temp from (select EmployeeID,year(OrderDate) as Freight_year ,Freight from Purchasing.PurchaseOrderHeader) as ii pivot ( sum(Freight) for Freight_year in ([2001],[2002],[2003],[2004]) )as sa select* from(select*from #temp)as oo unpivot ( Freight for Freight_year in ([2001],[2002],[2003],[2004]) )as dd ------------------------------------------------------------------------------------------------------------------------ -------------------------------------Day4----------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ --使用连接 createDatabase demo use demo createtable Employee ( UserID varchar(50) primarykey, DeptCode varchar(50), Salary int ); insertinto Employee values('qiuwh', 'rd', 100); insertinto Employee values('yuanlh', 'rd', 200); insertinto Employee values('liying', 'dev1', 300); insertinto Employee values('chenb', 'dev1', 100); insertinto Employee values('wuyang', 'dev2', 300); insertinto Employee values('longj', 'dev2', 200); createtable Dept ( DeptCode varchar(50) primarykey, DeptName varchar(50) ); insertinto Dept values('rd', '研发中心'); insertinto Dept values('dev1', '开发一部'); insertinto Dept values('mgrcenter', '管理中心'); -----使用CROSS JOIN生成测试数据 CREATETABLE FirstName (firstname nvarchar(50) notnull) CREATETABLE MiddleName (middlename nvarchar(50) notnull) CREATETABLE LastName (lastname nvarchar(50) notnull) INSERTinto FirstName values ('Jack') INSERTinto FirstName values ('Jill') INSERTinto FirstName values ('Tom') INSERTinto FirstName values ('Dick') INSERTinto FirstName values ('Harry') INSERTinto FirstName values ('Robert') INSERTinto FirstName values ('Peter') INSERTinto FirstName values ('David') INSERTinto FirstName values ('Susan') INSERTinto FirstName values ('Mary') INSERTinto MiddleName values ('A') INSERTinto MiddleName values ('B') INSERTinto MiddleName values ('C') INSERTinto MiddleName values ('D') INSERTinto MiddleName values ('E') INSERTinto MiddleName values ('F') INSERTinto MiddleName values ('G') INSERTinto MiddleName values ('H') INSERTinto MiddleName values ('I') INSERTinto MiddleName values ('J') INSERTinto LastName values ('Smith') INSERTinto LastName values ('Lamb') INSERTinto LastName values ('Ron') INSERTinto LastName values ('Peterson') INSERTinto LastName values ('Paul') INSERTinto LastName values ('Black') INSERTinto LastName values ('Brown') INSERTinto LastName values ('Adams') INSERTinto LastName values ('Diaz') INSERTinto LastName values ('Hall') -------使用UNION use demo createtable A ( val int); createtable B ( val int); insertinto A values(1); insertinto A values(2); insertinto A values(2); insertinto A values(3); insertinto A values(4); insertinto B values(2); insertinto B values(4); insertinto B values(6); insertinto B values(6); insertinto B values(7); select*from Employee select*from Dept --------------------inner join------------------------ select* from Employee e innerjoin Dept d on e.DeptCode = d.deptcode select* from Employee e join Dept d on e.DeptCode = d.deptcode select* from Employee e , Dept d where e.DeptCode = d.deptcode ----------------left join---------------------------- select* from Employee e leftjoin Dept d on e.DeptCode = d.deptcode ----------------right join---------------------------- select* from Employee e rightjoin Dept d on e.DeptCode = d.deptcode --left join equal to right join select e.userId,e.deptcode,e.salary,d.deptcode,d.deptname from Dept d rightjoin Employee e on e.DeptCode = d.deptcode except----------what's union? select e.userId,e.deptcode,e.salary,d.deptcode,d.deptname from Employee e leftjoin Dept d on e.DeptCode = d.deptcode ---------------------cross join----------------------- select* from FirstName crossjoin Middlename crossjoin lastname -----------------------------Multitable join--------------- use AdventureWorks select* from Purchasing.Vendor v innerjoin Purchasing.ProductVendor pv on v. VendorID = pv. VendorID innerjoin Production.Product p on p.ProductID = pv.ProductID ---------------------join self---------------------------------- select e.EmployeeID,e.LoginID,e.ManagerID,f.LoginID from HumanResources.Employee e leftjoin HumanResources.Employee f on e.ManagerID = f.EmployeeID select e.ProductID,e.ListPrice,f.ProductID,f.ListPrice from Production.Product e join Production.Product f on e.ListPrice < f.ListPrice orderby e.ProductID select a,count(c) from ( select e.ProductID a,e.ListPrice b ,f.ProductID c ,f.ListPrice d from Production.Product e join Production.Product f on e.ListPrice < f.ListPrice )as haha groupby a ---------------------union ---------------------------------------- -----------可以用group by在子句里,但是不能用order bY 在子句里 ----------order by 只能在所有子句的外面最后使用 use Demo droptable A,B select*from A union select*from B select*from A unionall select*from B select*from A unionall select*from B orderby val select*from A except select*from B orderby val select*from A intersect select*from B orderby val selecttop2*from Employee selecttop10percent*from Employee declare@iint set@i=50 selecttop (@i)percent*from Employee select[Name] from Production.Product where ListPrice> ( selectavg( ListPrice) from Production.Product ) select* from Production.Product prod where ProductModelID in ( select ProductModelID from Production.ProductModel where Name like'Road%' ) select tmp.Name,prod.ProductID from Production.Product prod, ( select ProductModelID,Name from Production.ProductModel where Name like'Road%' ) tmp where tmp.ProductModelID = prod.ProductModelID except select tmp.Name,prod.ProductID from Production.Product prod join Production.ProductModel tmp on tmp.ProductModelID = prod.ProductModelID and tmp.Name like'Road%' - -------all ----------some|any -------------------不允许在子查询中使用order by子句,但是,如果top子句被指定的话,这是可以使用order by 子句的 select*from Production.Product Where ProductModelID in ( selecttop10 ProductModelID from Production.ProductModel where Name like'Road%' orderby ProductModelID ) ----------------------不允许在子查询中使用Computer子句 ----------------------text ,ntext ,image 大对象是不能放进子查询里的 --on same Table selectdistinct ProductID,OrderQty from Sales.SalesOrderDetail as sod_outer where OrderQty= ( selectMax(OrderQty) from Sales.SalesOrderDetail as sod_inner where sod_outer.ProductID= sod_inner.ProductID ) --except select ProductID, Max(OrderQty) as OrderQty from Sales.SalesOrderDetail groupby ProductID selectdistinct ProductID, Max(OrderQty) as OrderQty from Sales.SalesOrderDetail groupby ProductID select SalesOrderID,ProductID,OrderQty from Sales.SalesOrderDetail as sod_outer where OrderQty>= ( selectavg(OrderQty)*10 from Sales.SalesOrderDetail as sod_inner where sod_outer.ProductID= sod_inner.ProductID ) ----difference table select*from Sales.SalesPerson select*from Sales.SalesOrderHeader select ss_outer.SalesPersonID from Sales.SalesPerson as ss_outer where2000<= ( select ss_outer.CommissionPct*ss_inner.TotalDue) from Sales.SalesOrderHeader as ss_inner where ss_outer.SalesPersonID= ss_inner.SalesPersonID ) select ss_outer.SalesPersonID from Sales.SalesPerson as ss_outer where2000<= ( selectmax(ss_inner.TotalDue)* ss_outer.CommissionPct from Sales.SalesOrderHeader as ss_inner where ss_outer.SalesPersonID= ss_inner.SalesPersonID ) orderby ss_outer.SalesPersonID select SalesPersonID from Sales.SalesPerson groupby SalesPersonID having SalesPersonID=288 select*from Production.Product select*from Production.ProductInventory ----在exists后面可以直接用*来代替列名是可以的,因为它并不真的取值出来 selectdistinct ProductID,Name from Production.Product pr whereexists ( select*from Production.ProductInventory inv where pr.ProductID = inv.ProductID and Quantity>500 ) except select pr.ProductID,pr.Name from Production.Product pr join Production.ProductInventory inv on pr.ProductID = inv.ProductID and Quantity>500 select*from Production.Product where ProductModelID in ( select ProductModelID from Production.ProductModel where Name like'Road%' ) except select*from Production.Product pp whereExists ( select ProductModelID from Production.ProductModel ppm where ppm.Name like'Road%'and pp.ProductModelID = ppm.ProductModelID ) with TopSales(SalespersonID,NumSales)as ( select SalesPersonID,Count(*) from Sales.SalesOrderHeader GroupBY SalesPersonID ) selecttop(5) *from TopSales where SalespersonID isnotnull orderby NumSales desc use demo with Emp as ( select*from Employee where deptCode='rd' ), Dep as ( select*from Dept ) select*from Emp,Dep where Emp.DeptCode = dep.DeptCode]]] first test with Managers as ( select EmployeeID,LoginID,Title,ManagerID From HumanResources.Employee where EmployeeID =107 unionall Select e.EmployeeID,e.LoginID,e.Title,e.ManagerID From HumanResources.Employee e innerjoin Managers mgr on e.EmployeeID= mgr.ManagerID ) select*From Managers option (maxrecursion 2) USE[Demo] GO /**//****** Object: Table [dbo].[EmpSalary] Script Date: 07/19/2006 10:17:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATETABLE[dbo].[EmpSalary]( [UserID][varchar](50) COLLATE Chinese_PRC_CI_AS NOTNULL, [DeptCode][varchar](50) COLLATE Chinese_PRC_CI_AS NOTNULL, [Salary][float]NULL, PRIMARYKEYCLUSTERED ( [UserID]ASC )WITH (IGNORE_DUP_KEY =OFF) ON[PRIMARY] ) ON[PRIMARY] GO SET ANSI_PADDING OFF
---------------------day 5------------------------------------------------ begin tran MyTran insert into Employee(UserID,DeptCode,Salary)Values('test1','rd',100) insert into Employee(UserID,DeptCode,Salary)Values('test2','rd',100) rollback tran MyTran commit tran MyTran exec SP_LOCK select * from Employee --在另一个会话中取不到数据 select * from Employee with(nolock) --取到数据,包括未提交的已被修改的数据,因此该数据可能为脏 select * from Employee with(readpast)--取到数据,但是不包括未提交的数据 ---演示如何使用save tran begin tran MyTran insert into Employee(UserID,DeptCode,Salary)Values('test4','rd',100) save tran MyTran insert into Employee(UserID,DeptCode,Salary)Values('test5','rd',100) rollback tran MyTran -------------第一次回滚倒最近记录点 rollback tran MyTran -------------第二次回滚也是滚到最近记录点(本例子只能一个记录点 所以回滚到开头) commit tran MyTran begin tran MyTran insert into Employee(UserID,DeptCode,Salary)Values('test4','rd',100) save tran MyTran insert into Employee(UserID,DeptCode,Salary)Values('test5','rd',100) save tran MyTran insert into Employee(UserID,DeptCode,Salary)Values('test3','rd',100) rollback tran MyTran -------------第一次回滚倒最近记录点 rollback tran MyTran -------------第二次回滚也是滚到最近记录点(本例子只能一个记录点 所以回滚到开头) rollback tran MyTran commit tran MyTran delete from Employee where UserID in ('test1','test4','test2','test5','test3') select @@trancount ----------事务的嵌套层数 ----------------------演示嵌套事务 begin tran MyTran_out select @@trancount insert into Employee(UserID,DeptCode,Salary)Values('test4','rd',100) select @@trancount begin tran MyTran_Inner select @@trancount insert into Employee(UserID,DeptCode,Salary)Values('test5','rd',100) select @@trancount commit tran MyTran_inner select @@trancount rollback tran select @@trancount --外层回滚,全部回滚,而且不能单独回滚内部事务--- commit tran MyTran --名字将被忽略 rollback tran MyTran_out ---可以证明:事务回滚是先回到记录点 ---------------插入一行记录到表中 select * from Employee begin tran insert into Employee (UserID,DeptCode,Salary) output Inserted.UserID,Inserted.DeptCode,Inserted.Salary Values('test2','rd',100) rollback tran delete from Employee where UserID ='test2' --select into 会创建一个新的表,如果该表已存在,会抱错 select UserID,DeptCode,Salary into #temp from Employee select * from #temp ----------insert into/output into 不会创建新表,而是使用已经存在的表 insert into #temp select UserID ,DeptCode,Salary From Employee select * from #temp drop table #temp create Proc sp_get as select UserID,DeptCode,Salary From Employee insert into #temp exec sp_get create table Demo1 ( id int identity(1,1) primary key, val int, result as (abs(val)) ) insert into Demo1 output Inserted.id,Inserted.val,Inserted.result values(-23) --------------output 可以用来返回由数据库自己维护的字段的值,该值并不由应用程序维护,所以最好在output中输出 insert into Demo1 output Inserted.id,Inserted.val,Inserted.result select * from(select 20as val union select 30as result) exec sp_lock ------------delete 会做日志,而Truncate不会,因而比较快 select * from Sales.SalesPersonQuotaHistory select * from Sales.SalesPerson Begin Tran delete Sales.SalesPersonQuotaHistory from Sales.SalesPersonQuotaHistory -----------attention:如果删除的时候涉及到连接多表,则必须先指定删除哪张表,可以是别名 join Sales.SalesPerson on Sales.SalesPersonQuotaHistory.SalesPersonID = Sales.SalesPerson.SalesPersonID and Sales.SalesPerson.SalesYTD>2500000 rollback Tran -----------------update--------------------------------- use Demo update Employee set Salary =200 ,DeptCode ='mgr' output Deleted.Salary,Inserted.Salary where UserID='qiuwh' select * from Production.Product select * from Purchasing.ProductVendor Begin Tran Update Production.Product set ListPrice =2*ListPrice,Name ='BingoSoft' where ProductID in (select ProductID from Purchasing.ProductVendor where VendorID=5) rollback Tran Begin Tran Update Production.Product set ListPrice =2*ListPrice from Production.Product join Purchasing.ProductVendor PPV on PPV.ProductID = Production.Product .ProductID and PPV.VendorID=5 Rollback Tran 怎样修改表?!!!!!!!!!!!!!! Log表? 类别(修改) Create Table Mahui ( ID int identity not null, Salary int Null ) insert into Mahui values(1) insert into Mahui values(2) insert into Mahui values(3) insert into Mahui values(null) insert into Mahui values(null) insert into Mahui values(null) select * from Mahui select Sum(Salary) from Mahui Drop table LogEmployee Create Table LogEmployee ( Opration varchar(20) not null, Description Varchar(100), OccurTime DateTime, ) drop Trigger Trg_Insert_Employee Create Trigger Trg_Insert_Employee on dbo.Employee for insert as declare @Descrip varchar(50) declare @UserID varchar (50) declare @DeptCode varchar (50) declare @Salary int declare @Sa varchar (20) set @UserID=(select Inserted.UserID from inserted) set @DeptCode=(select Inserted.DeptCode from inserted) set @Salary=(select Inserted.Salary from inserted) set @Sa = Convert(varchar(20),@Salary) set @Descrip='UserID:'+@UserID+';''Deptcode:'+@DeptCode+';'+'Salary:'+@Sa insert into LogEmployee(Opration,Description,OccurTime)values('插入',@Descrip,GetDate()) drop Trigger Trg_Delete_Employee Create Trigger Trg_Delete_Employee on dbo.Employee for delete as declare @Descrip varchar(50) declare @UserID varchar (50) declare @DeptCode varchar (50) declare @Salary int declare @Sa varchar (20) set @UserID=(select deleted.UserID from deleted) set @DeptCode=(select deleted.DeptCode from deleted) set @Salary=(select deleted.Salary from deleted) set @Sa = Convert(varchar(20),@Salary) set @Descrip='UserID:'+@UserID+';'+'Deptcode:'+@DeptCode+';'+'Salary:'+@Sa insert into LogEmployee(Opration,Description,OccurTime)values('删除',@Descrip,GetDate()) drop Trigger Trg_Update_Employee Create Trigger Trg_Update_Employee on dbo.Employee for Update as declare @Descrip varchar(50) declare @UserIDOld varchar (50) declare @DeptCodeOld varchar (50) declare @SalaryOld int declare @SaOld varchar (20) set @UserIDOld=(select Inserted.UserID from inserted) set @DeptCodeOld=(select Inserted.DeptCode from inserted) set @SalaryOld=(select Inserted.Salary from inserted) set @SaOld = Convert(varchar(20),@SalaryOld) declare @UserIDNew varchar (50) declare @DeptCodeNew varchar (50) declare @SalaryNew int declare @SaNew varchar (20) set @UserIDNew=(select deleted.UserID from deleted) set @DeptCodeNew=(select deleted.DeptCode from deleted) set @SalaryNew=(select deleted.Salary from deleted) set @SaNew = Convert(varchar(20),@SalaryNew) set @Descrip='UserID:'+@UserIDNew+'-->'+@UserIDOld+';'+'Deptcode:'+@DeptCodeNew+'-->'+@DeptCodeOld +';'+'Salary:'+@SaNew+'-->'+@SaOld insert into LogEmployee(Opration,Description,OccurTime)values('更新',@Descrip,GetDate()) select * from Employee insert into Employee(UserID,DeptCode,Salary)values('11111','dev1','200') update Employee set UserID='mimi',DeptCode='dev1',Salary='12' where UserID =33333 select * from Employee where UserID =11111 delete from Employee where UserID ='11111' select * from LogEmployee Create Trigger Trg_Insert_Update_Delete_Employee on dbo.Employee for insert,update,delete as if insert(Opration) Begin insert into MyLog(Opration,Description)('插入',inserted.UserID+inserted.Deptcode+inserted.Salary,GetDate()) End if Update(any) Begin insert into MyLog(Opration,NewValue1,NewValue2,NewValue3)('修改',deleted.UserID,inserted.UserID,deleted.Deptcode,inserted.Deptcode,deleted.Salary,inserted.Salary) End if Delete(Opration) Begin insert into MyLog(Opration,OldValue1,OldValue2,OldValue3)('删除',deleted.UserID,deleted.Deptcode,deleted.Salary) End