sql 作业
2018年04月09日
⁄ 综合
⁄ 共 33779字 ⁄ 字号
小 中 大
Use Demo
go
create proc proc4
as
declare @iSalary int
set @iSalary = (select Sum(salary) from emp)
print @iSalary
go
exec proc4
create proc proc5
(
@iSalary int output
)
as
set @iSalary = (select Sum(salary) from emp)
print @iSalary
go
declare @sals int
declare @salsback int
exec proc5 @sals output
set @salsback = @sals
print @salsback
insert into emp(userid,deptcode,salary) values('ede','developer','100')
select * from emp
set identity_insert dbo.emp off
select @@identity from emp
insert into emp(userid,deptcode,salary)values('w3q[=we3w','dev','22')
select @@identity from emp
alter table dbo.emp
{
alter colume
}
select * from dbo.emp
select userid,deptcode,salary,test,indentity from dbo.emp
create table tab4
(
id int identity(1,1) primary key,
code uniqueidentifier default newid(),
name varchar(50)
)
insert into tab4(name) values('mahui')
select * from tab4 where id = 'mahui'
select coalesce(null,null,null,1)
create table table1
(
a int,
b int,
c int,
d int
)
create table table2
(
a int,
b int,
c int,
d int
)
create clustered index ix_a
on dbo.table1(a)
create index ix_b
on dbo.table1(b)
create index ix_c
on dbo.table1(c)
create index ix_d
on dbo.table1(d)
create clustered index ix_a
on dbo.table2(a)
create index ix_b
on dbo.table2(b)
create index ix_c
on dbo.table2(c)
create index ix_d
on dbo.table2(d)
declare @i int
set @i=0
while (@i<5)
begin
insert into table1(a,b,c,d) values(1,1,1,1)
set @i=@i+1
end
declare @i int
set @i=0
while (@i<200000)
begin
insert into 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
SELECT datepart(YY,GetDate())
SELECT datepart(DD,GetDate())
SELECT datepart(MM,GetDate())
SELECT datepart(SS,GetDate())
SELECT datepart(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
select * from Employee --取不到数据
select * from Employee with(nolock) --取到数据
select * from Employee with(readpast)--取到数据
update Employee set Salary = 900
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'E:datamssql2005pubs.mdf',
@filename2 = N'E:datamssql2005pubs_log.ldf';
sp_dbcmptlevel pubs, 90
insert into dbo.Contact(id,name,university,major)values(100,'sda','bb','cc')
drop proc InputForText
Create proc InputForText
(
@input int
)
as
declare @buffer varchar(50)
set @buffer = convert(varchar(50),@input)--------------------转换为字符串
--------------------------------------------------------------上面的先放着，用来处理数字输入
Create table NumberToChar ------------------------------------用来保存数字和字母之间映射的表
(
number int,
GetText varchar(10)
)
insert into NumberToChar(number,GetText)values(2,'a')
insert into NumberToChar(number,GetText)values(2,'b')
insert into NumberToChar(number,GetText)values(2,'c')
insert into NumberToChar(number,GetText)values(3,'d')
insert into NumberToChar(number,GetText)values(3,'e')
insert into NumberToChar(number,GetText)values(3,'f')
insert into NumberToChar(number,GetText)values(4,'g')
insert into NumberToChar(number,GetText)values(4,'h')
insert into NumberToChar(number,GetText)values(4,'i')
insert into NumberToChar(number,GetText)values(5,'j')
insert into NumberToChar(number,GetText)values(5,'k')
insert into NumberToChar(number,GetText)values(5,'l')
insert into NumberToChar(number,GetText)values(6,'m')
insert into NumberToChar(number,GetText)values(6,'n')
insert into NumberToChar(number,GetText)values(6,'o')
insert into NumberToChar(number,GetText)values(7,'p')
insert into NumberToChar(number,GetText)values(7,'q')
insert into NumberToChar(number,GetText)values(7,'r')
insert into NumberToChar(number,GetText)values(7,'s')
insert into NumberToChar(number,GetText)values(8,'t')
insert into NumberToChar(number,GetText)values(8,'u')
insert into NumberToChar(number,GetText)values(8,'v')
insert into NumberToChar(number,GetText)values(9,'w')
insert into NumberToChar(number,GetText)values(9,'x')
insert into NumberToChar(number,GetText)values(9,'y')
insert into NumberToChar(number,GetText)values(9,'z')
select * from NumberToChar
drop function Get_StrArrayLength
CREATE function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
drop function Get_StrArrayStrOfIndex
CREATE function Get_StrArrayStrOfIndex
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int ------------取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
return substring(@str,@start,@location-@start)
end
----假设分割符号为，
drop proc InputForText
Create proc InputForText
(
@input varchar(50)
)
as
/**//*下面的几个表用来存储一些临时记录
*/
Create table #WordsStore
(
id int identity,
WordPicker varchar(50)
)
Create table #WordsOutput
(
id int identity,
WordPicker varchar(50)
)
Create table #WordsInput
(
id int identity,
WordPicker varchar(50)
)
Create table #WordsTemp
(
id int identity,
WordPicker varchar(50),
WordPicker2 varchar(50)
)
/**//*把输入的数字序列转换为字母组合，存放在临时表#WordsStore中
*/
declare @CharGet varchar(10)--CharGet依次返回输入的数字
declare @CharBuffer varchar(100)
declare @Next int --用来在遍历数字中做指针
declare @Seacher int
set @Next = 1
set @CharBuffer=''--初始值为空
while @Next<=dbo.Get_StrArrayLength(@input,',')
begin
Set @CharGet = dbo.Get_StrArrayStrOfIndex(@input,',',@Next)
set @Next = @Next +1
declare @IsExist int
set @IsExist = 0
set @IsExist = (select count(*) from #WordsStore)
if @IsExist = 0
begin
insert into #WordsStore Select GetText from dbo.NumberToChar where number =@CharGet
end
else
begin
insert into #WordsInput Select GetText from dbo.NumberToChar where number =@CharGet
insert into #WordsTemp select #WordsStore.WordPicker,#WordsInput.WordPicker from #WordsStore join #WordsInput on 1=1
declare @Col1 varchar(50)
declare @Col2 varchar(50)
declare @total varchar(50)
delete from #WordsStore
Declare JoinCols Cursor for
select WordPicker,WordPicker2 from #WordsTemp
open JoinCols
Fetch JoinCols into @Col1,@Col2
while @@Fetch_Status = 0
begin
Fetch JoinCols into @Col1,@Col2
set @total=@Col1+@Col2
insert into #WordsStore(WordPicker)values(@total)
end
close JoinCols
Deallocate JoinCols
delete from #WordsInput
delete from #WordsTemp
end
end
-------------------------- 到这里为止，完成的功能是把输入的拼音保留在#WordsStore中
Create Table #RestoreResult
(------------------------- 该临时表用来存放最后搜索到的能匹配的人名记录
id int,
name varchar(20),
university varchar(50),
major varchar(50)
)
Declare @StartPY varchar(50)
Declare @SeachName varchar(50)
Declare PingYing Cursor for
select distinct WordPicker from #WordsStore
open PingYing
Fetch PingYing into @StartPY
while @@Fetch_Status = 0
begin----------------------遍历拼音的组合
Declare @WordToChoose varchar(1024)
Declare SeachWord Cursor for
select words from dbo.Dictionary where py like @StartPY+'%'
open SeachWord
Fetch SeachWord into @WordToChoose
while @@Fetch_Status = 0
begin-----------------查询匹配拼音的汉字
declare @Point int
set @Point = 1
while @Point<=Len(@WordToChoose)
begin-------------查询匹配汉字的人名
set @SeachName = SubString(@WordToChoose,@Point,1)
insert into #RestoreResult select * from dbo.Contact where name like '%'+@SeachName+'%'
set @Point=@Point+1
end
Fetch SeachWord into @WordToChoose
end
close SeachWord
Deallocate SeachWord
Fetch PingYing into @StartPY
end
close PingYing
Deallocate PingYing
------------------------匹配的记录保存在#RestoreResult中
select * from #RestoreResult
print @buffer
exec InputForText '4,8,4'
select * from dbo.Dictionary
select * from dbo.Contact
select * from NumberToChar
select * from #WordsInput
Declare @OutName varchar(20)
set @OutName='辉'
select * from dbo.Contact where name like '%'+@OutName+'%'
sp_lock 52
select @@spid
select db_name(11)
sp_helpdb
select object_name(2073058421)
select db_id('Trans')
select object_id('NonPrimaryKey')
1:120:0 第一个文件第120页第0行
select * from sys.database_files
在数据库的views里的system views里的metadata很有用
如果该行有聚集索引,则上一个索引的行索
select @@Trancount
sp_lock 52
select @@spid
sp_help 'Trans'
sp_help NonPrimaryKey
select object_name(2105058535)
Create DataBase Trans
use Trans
create table NonPrimaryKey
(
UserID varchar(50),
Salary int
)
insert into NonPrimaryKey values('mahui','20')
insert into NonPrimaryKey values('Qiuwh','60')
insert into NonPrimaryKey values('HuangH','80')
insert into NonPrimaryKey values('HAHA','100')
insert into NonPrimaryKey values('mahui','20')
insert into NonPrimaryKey values('mahui','20')
Begin Tran
Update NonPrimaryKey Set Salary=200 where 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
begin tran
alter table NonPrimaryKey
add test varchar(20)
rollback
select @@trancount
rollback
begin tran
select * from NonPrimaryKey with (holdlock)
set transaction isolation level read uncommitted
set transaction isolation level read committed
set transaction isolation level repeatable read
set transaction isolation level Serializable
set transaction isolation level Snapshot //2005新特性
sp_help NonPrimaryKey
select * from NonPrimaryKey
Begin Tran
set transaction isolation level read uncommitted
update NonPrimaryKey set UserID='yujia' where Salary ='20'
insert into NonPrimaryKey values('YULA','120')
Rollback
Begin Tran
set transaction isolation level read committed
update NonPrimaryKey set UserID='yujia' where Salary ='20'
insert into NonPrimaryKey values('YULA','120')
Rollback
Begin Tran
set transaction isolation level repeatable read
update NonPrimaryKey set UserID='yujia' where Salary ='20'
insert into NonPrimaryKey values('YULA','120')
Rollback
Begin Tran
set transaction isolation level Serializable
insert into NonPrimaryKey values('HAHA','120')
update NonPrimaryKey set UserID='YULA' where Salary ='20'
Rollback
Begin Tran
set transaction isolation level Snapshot
insert into NonPrimaryKey values('HAHA','120')
update NonPrimaryKey set UserID='YULA' where Salary ='20'
Rollback
--dead lock
create table t1
(
id int primary key,
val varchar(20) not null
)
create table t2
(
id int primary key,
val varchar(20) not null
)
insert into t1 values(1,'11111')
insert into t2 values(2,'11111')
--session 1
begin tran
update t1 set val='new value' where id =1 --t１时刻
update t2 set val='new value' where id =2 --t３时刻
commit
--session 2
begin tran
update t2 set val='new value' where id =1 --t２时刻
update t1 set val='new value' where id =1 --t４时刻
commit
DbCC UserOptions
select * from t2
sp_lock
xact_state() = -1表示事务是活动的，但是出现了严重的错误
xact_state() = １表示事务是活动的，并且事务可以被commit,即使出现了一些错误
xact_state() = ０表示事没有活动的
用begin try
end try
begin catch
end catch
来保证运行正确，不会出现xact_state() = １中可能出现的出现了部分错误结果还是能提交的问题
-----------------------------------------------------Day3-----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
create table EmpSalary
(
UserID varchar(50) primary key,
DeptCode varchar(50) not null,
Salary float
);
insert into EmpSalary values('chenb','dev1',300);
insert into EmpSalary values('lizm','dev1',200);
insert into EmpSalary values('weisg','dev1',null);
insert into EmpSalary values('qiuwh','rd',100);
insert into EmpSalary values('zhumd','rd',200);
insert into EmpSalary values('yuanlh','rd',100);
select * from EmpSalary
select avg(distinct isnull(salary,0)) from EmpSalary
select count (distinct Salary) from EmpSalary
select * from empsalary
update empsalary set salary = -100 where userid = 'chenb'
select salary from empsalary
select abs(salary) from empsalary
select count ( distinct abs(salary)) from empsalary
select avg(isnull(salary,50)) from EmpSalary
select sin(pi()/6)
drop table ProductSale;
create table ProductSale
(
id int identity primary key,
Product varchar(50),
SaleMonth int,
Sale int
);
insert into ProductSale values('Book',1,140);
insert into ProductSale values('Book',2,220);
insert into ProductSale values('Book',3,360);
insert into ProductSale values('Book',4,240);
insert into ProductSale values('Toy',1,220);
insert into ProductSale values('Toy',2,310);
insert into ProductSale values('Toy',3,120);
insert into ProductSale values('Toy',4,220);
---------------------------------------------
insert into ProductSale Values(null,1,0);
insert into ProductSale Values(null,2,0);
insert into ProductSale Values(null,3,0);
insert into ProductSale Values(null,4,0);
select * from ProductSale
select DeptCode,sum(salary) as total from EmpSalary group by deptcode
select DeptCode,Sum()
select product,sum(sale) as 'sale'
from productsale
group by product
order by product
select salemonth,sum(sale) as 'sale'
from productsale
group by salemonth
order by salemonth
select * from productsale
select count(distinct(sale)) from productsale
select product,salemonth,sum(sale)as 'sale',id
from productsale
group by product,salemonth,id with rollup
order by product,salemonth
select product,salemonth,sum(sale)as 'sale'
from productsale
group by product,salemonth with rollup
order by product,salemonth
select product,salemonth,sum(sale)as 'sale'
from productsale
group by salemonth,product with rollup
order by product,salemonth
select product,salemonth,sum(sale)as 'sale' ,id
from productsale
group by salemonth,product,id with rollup
order by product,salemonth
select product,salemonth ,id,sum(sale)as 'sale'
from productsale
group by salemonth,product,id with cube
order by product,salemonth,id
select product,salemonth,sum(sale) as 'sale'
from productsale
group by salemonth,product with cube
order by product,salemonth
select * from empsalary group by deptcode having salary>100
select product= case
when (product is null) and (grouping(product)=1) and (salemonth is not null) then 'xiaoji'
when (product is null) and (grouping(product)=1) and (salemonth is null) and (grouping(salemonth)=1) then 'zongji'
else product end,
salemonth,sum(sale) as 'sale',grouping(product),grouping(salemonth)
from productsale
group by salemonth,product with rollup
order by salemonth,product
select isnull(product,0) product = case
when (product is null) and (salemonth is not null) then 'xiaoji'
when (product is null) and (salemonth is null) then 'zongji'
else product end,
salemonth,sum(sale) as 'sale',grouping(product),grouping(salemonth)
from productsale
group by salemonth,product with rollup
order by salemonth,product
select isnull(product,0) as product
salemonth,sum(sale) as 'sale',grouping(product),grouping(salemonth)
from productsale
group by salemonth,product with rollup
order by salemonth,product
select * from productsale
order by product,salemonth desc
compute sum(sale)by product,salemonth
select * from productsale
select product ,sale, rank() over(partition by product order by sale ) as 'rank'
from productsale
select product ,sale, dense_rank() over(partition by product order by sale ) as 'denserank'
from productsale
select product ,sale, row_number() over(partition by product order by sale ) as 'denserank'
from productsale
select product ,sale, ntile(2) over(partition by product order by sale ) as 'denserank'
from productsale
select product ,sale, denserank() over(partition by product order by sale ) as 'denserank'
from productsale
delete from productsale where product is null
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 group by 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-----------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
--使用连接
create Database demo
use demo
create table Employee
(
UserID varchar(50) primary key,
DeptCode varchar(50),
Salary int
);
insert into Employee values('qiuwh', 'rd', 100);
insert into Employee values('yuanlh', 'rd', 200);
insert into Employee values('liying', 'dev1', 300);
insert into Employee values('chenb', 'dev1', 100);
insert into Employee values('wuyang', 'dev2', 300);
insert into Employee values('longj', 'dev2', 200);
create table Dept
(
DeptCode varchar(50) primary key,
DeptName varchar(50)
);
insert into Dept values('rd', '研发中心');
insert into Dept values('dev1', '开发一部');
insert into Dept values('mgrcenter', '管理中心');
-----使用CROSS JOIN生成测试数据
CREATE TABLE FirstName (firstname nvarchar(50) not null)
CREATE TABLE MiddleName (middlename nvarchar(50) not null)
CREATE TABLE LastName (lastname nvarchar(50) not null)
INSERT into FirstName values ('Jack')
INSERT into FirstName values ('Jill')
INSERT into FirstName values ('Tom')
INSERT into FirstName values ('Dick')
INSERT into FirstName values ('Harry')
INSERT into FirstName values ('Robert')
INSERT into FirstName values ('Peter')
INSERT into FirstName values ('David')
INSERT into FirstName values ('Susan')
INSERT into FirstName values ('Mary')
INSERT into MiddleName values ('A')
INSERT into MiddleName values ('B')
INSERT into MiddleName values ('C')
INSERT into MiddleName values ('D')
INSERT into MiddleName values ('E')
INSERT into MiddleName values ('F')
INSERT into MiddleName values ('G')
INSERT into MiddleName values ('H')
INSERT into MiddleName values ('I')
INSERT into MiddleName values ('J')
INSERT into LastName values ('Smith')
INSERT into LastName values ('Lamb')
INSERT into LastName values ('Ron')
INSERT into LastName values ('Peterson')
INSERT into LastName values ('Paul')
INSERT into LastName values ('Black')
INSERT into LastName values ('Brown')
INSERT into LastName values ('Adams')
INSERT into LastName values ('Diaz')
INSERT into LastName values ('Hall')
-------使用UNION
use demo
create table A ( val int);
create table B ( val int);
insert into A values(1);
insert into A values(2);
insert into A values(2);
insert into A values(3);
insert into A values(4);
insert into B values(2);
insert into B values(4);
insert into B values(6);
insert into B values(6);
insert into B values(7);
select * from Employee
select * from Dept
--------------------inner join------------------------
select *
from Employee e
inner join 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
left join Dept d on e.DeptCode = d.deptcode
----------------right join----------------------------
select *
from Employee e
right join Dept d on e.DeptCode = d.deptcode
--ｌｅｆｔ ｊｏｉｎ ｅｑｕａｌ ｔｏ ｒｉｇｈｔ ｊｏｉｎ
select e.userId,e.deptcode,e.salary,d.deptcode,d.deptname
from Dept d
right join 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
left join Dept d on e.DeptCode = d.deptcode
---------------------cross join-----------------------
select *
from FirstName
cross join Middlename
cross join lastname
-----------------------------Multitable join---------------
use AdventureWorks
select *
from Purchasing.Vendor v
inner join Purchasing.ProductVendor pv on v. VendorID = pv. VendorID
inner join Production.Product p on p.ProductID = pv.ProductID
---------------------join self----------------------------------
select e.EmployeeID,e.LoginID,e.ManagerID,f.LoginID
from HumanResources.Employee e
left join 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
order by 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
group by a
---------------------union ----------------------------------------
-----------可以用group by在子句里，但是不能用order bY 在子句里
----------order by 只能在所有子句的外面最后使用
use Demo
drop table A,B
select * from A
union
select * from B
select * from A
union all
select * from B
select * from A
union all
select * from B
order by val
select * from A
except
select * from B
order by val
select * from A
intersect
select * from B
order by val
select top 2 * from Employee
select top 10 percent * from Employee
declare @i int
set @i = 50
select top (@i)percent * from Employee
select [Name]
from Production.Product
where ListPrice>
(
select avg( 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
(
select top 10 ProductModelID from Production.ProductModel
where Name like 'Road%'
order by ProductModelID
)
----------------------不允许在子查询中使用Computer子句
----------------------text ,ntext ,image 大对象是不能放进子查询里的
--on same Table
select distinct ProductID,OrderQty
from Sales.SalesOrderDetail as sod_outer
where OrderQty=
(
select Max(OrderQty)
from Sales.SalesOrderDetail as sod_inner
where sod_outer.ProductID= sod_inner.ProductID
)
--except
select ProductID, Max(OrderQty) as OrderQty
from Sales.SalesOrderDetail
group by ProductID
select distinct ProductID, Max(OrderQty) as OrderQty
from Sales.SalesOrderDetail
group by ProductID
select SalesOrderID,ProductID,OrderQty
from Sales.SalesOrderDetail as sod_outer
where OrderQty>=
(
select avg(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
where 2000<=
(
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
where 2000<=
(
select max(ss_inner.TotalDue)* ss_outer.CommissionPct
from Sales.SalesOrderHeader as ss_inner
where ss_outer.SalesPersonID= ss_inner.SalesPersonID
)
order by ss_outer.SalesPersonID
select SalesPersonID
from Sales.SalesPerson
group by SalesPersonID
having SalesPersonID=288
select * from Production.Product
select * from Production.ProductInventory
----在exists后面可以直接用＊来代替列名是可以的，因为它并不真的取值出来
select distinct ProductID,Name
from Production.Product pr
where exists
( 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
where Exists
(
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
Group BY SalesPersonID
)
select top(5) * from TopSales where SalespersonID is not null
order by 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
union all
Select e.EmployeeID,e.LoginID,e.Title,e.ManagerID
From HumanResources.Employee e
inner join 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
CREATE TABLE [dbo].[EmpSalary](
[UserID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[DeptCode] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Salary] [float] NULL,
PRIMARY KEY CLUSTERED
(
[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 20 as val union select 30 as 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