SQL 知识及用法备忘录

  1 ---查询当前数据库一共有多少张表
  2 select COUNT(1) from sysobjects where xtype=‘U‘
  3 
  4 ---查询当前数据库有多少张视图
  5 select COUNT(1) from sysobjects where xtype=‘V‘
  6 
  7 ---查询当前数据库 有多少个存储过程
  8 select COUNT(1) from sysobjects where xtype=‘P‘
  9 
 10 -- row_number()函数用法 row_number() over (order by cloumn) from tableName
 11 select ROW_NUMBER() over (order by cardNo ),* from T_ValueCard
 12 
 13 
 14 select type from sysobjects group by type
 15 --
 16 select * from sysobjects 
 17 
 18 
 19 --触发器
 20 if not exists (select * from sysobjects where id=OBJECT_ID(‘Student‘) and name=‘Student‘)
 21 begin
 22     create table Student    --学生表
 23     (
 24         StudentID int primary key,    --学号
 25         StudentName nvarchar(15)    --姓名    
 26     )
 27 end
 28 
 29 if not exists (select * from sysobjects where id=OBJECT_ID(‘BorrowRecord‘) and name=‘BorrowRecord‘)
 30 begin
 31     create table BorrowRecord    --借书记录表
 32     (
 33         RecordID int primary key,    --流水号
 34         StudentID int,                --学号
 35         OutTime datetime,            --借出时间
 36         ReturnDate datetime            --归还时间
 37     )
 38 end
 39 
 40 --创建触发器语法
 41 --create trigger trigger_name           
 42 
 43 --on {table_name | view_name}           
 44 
 45 --{for | After | Instead of }            [ insert, update,delete ]           
 46 
 47 --as            sql_statement  
 48 
 49 --删除触发器  drop trigger trigger_name 
 50  
 51 -- 查看当前数据库的触发器
 52 --select * from  sysobjects where  type=‘TR‘
 53 --查看某个触发器
 54 --exec  sp_helptext ‘TStudent‘
 55 
 56 --触发器实例----触发器是属于隐式的
 57 --create trigger TStudent
 58 --on Student                --在Student表中创建触发器
 59 --for Update                --针对什么事件触发
 60 --as                        --时间触发后要做的事情
 61 --if update(StudentID)
 62 --begin
 63 --    Update BorrowRecord 
 64 --        set StudentID=i.StudentID
 65 --        from BorrowRecord br,Deleted d,Inserted i    --Deleted和Inserted为临时虚拟表
 66 --        where br.StudentID=d.StudentID
 67 --end
 68 --go
 69 
 70 --查询的时候临时生成自定义数据列
 71 select ‘Web‘ as SourceName,* from TM_Goods 
 72 --生成空列数据
 73 select ‘‘ as SourceName, * from TM_Goods
 74 
 75 
 76 
 77 --- 所查列的值出现特殊结果字符 给这个结果字符加单引号 如下所示的Sex字段
 78 select top 20 ID, GuestName,MemNo,CardNo, [Identity], BagNo,BoxNo,case Sex when ‘M‘ then ‘男‘ when ‘F‘ then ‘女‘ end as Sex,Holes, 
 79 case [Status] when 1 then ‘已预约‘ when 2 then ‘已来场登记‘ when 3 then ‘已球场登记‘ when 4 then ‘已出发‘
 80 when 5 then ‘已离场‘ when 6 then ‘已结账‘ when 7 then ‘退回结账‘ when -1 then ‘已取消预约‘ when -2 then ‘取消登记‘ 
 81 when 0 then ‘未知‘ end as [Status], 
 82 StrategyCode,   CreateTime,CheckinTime, Creator, CheckOutTime, CheckoutUser,
 83 LastEditTime, LastEditor ,ExpiryDate, MainCardNo,
 84 SalemanCode from Checkin 
 85 
 86 ---查询的时候如果结果里面包含空格或者特殊字符可以等量替换然后在进行查询
 87 select * from TM_Goods where REPLACE(Name,‘ ‘,‘‘)=REPLACE(‘海立得 车载型空气净化机‘,‘ ‘,‘‘)
 88 
 89 ---死锁有关脚本
 90 create  proc   [dbo].[p_lockinfo]     
 91   @kill_lock_spid   bit=1,     --是否杀掉死锁的进程,1   杀掉,   0   仅显示     
 92   @show_spid_if_nolock   bit=1   --如果没有死锁的进程,是否显示正常进程信息,1   显示,0   不显示     
 93   as     
 94   declare   @count   int,@s   nvarchar(1000),@i   int     
 95   select   id=identity(int,1,1),标志,     
 96     进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,     
 97     数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,     
 98     登陆时间=login_time,打开事务数=open_tran,   进程状态=status,     
 99     工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,     
100     域名=nt_domain,网卡地址=net_address     
101   into   #t   from(     
102     select   标志=‘死锁的进程‘,     
103       spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,     
104       status,hostname,program_name,hostprocess,nt_domain,net_address,     
105       s1=a.spid,s2=0     
106     from   master..sysprocesses   a   join   (     
107       select   blocked   from   master..sysprocesses   group   by   blocked     
108       )b   on   a.spid=b.blocked   where   a.blocked=0     
109     union   all     
110     select   ‘|_牺牲品_>‘,     
111       spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,     
112       status,hostname,program_name,hostprocess,nt_domain,net_address,     
113       s1=blocked,s2=1     
114     from   master..sysprocesses   a   where   blocked<>0     
115   )a   order   by   s1,s2     
116       
117   select   @count=@@rowcount,@i=1     
118       
119   if   @count=0   and   @show_spid_if_nolock=1     
120   begin     
121     insert   #t     
122     select   标志=‘正常的进程‘,     
123       spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,     
124       open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address     
125     from   master..sysprocesses     
126     set   @count=@@rowcount     
127   end     
128       
129   if   @count>0     
130   begin     
131     create   table   #t1(id   int   identity(1,1),a   nvarchar(30),b   Int,EventInfo   nvarchar(255))     
132     if   @kill_lock_spid=1     
133     begin     
134       declare   @spid   varchar(10),@标志   varchar(10)     
135       while   @i<=@count     
136       begin     
137         select   @spid=进程ID,@标志=标志   from   #t   where   id=@i     
138         insert   #t1   exec(‘dbcc   inputbuffer(‘+@spid+‘)‘)     
139         if   @标志=‘死锁的进程‘   exec(‘kill   ‘+@spid)     
140         set   @i=@i+1     
141       end     
142     end     
143     else     
144       while   @i<=@count     
145       begin     
146         select   @s=‘dbcc   inputbuffer(‘+cast(进程ID   as   varchar)+‘)‘   from   #t   where   id=@i     
147         insert   #t1   exec(@s)     
148         set   @i=@i+1     
149       end     
150     select   a.*,进程的SQL语句=b.EventInfo     
151     from   #t   a   join   #t1   b   on   a.id=b.id     
152   end
153 GO