SQL Server 作业

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