sql_day4

-------------------------------day_4-------------------
--------------------------系统数据库的研究-------------
-----查看所有的列------
select * from sys.all_columns
------------查看所有对象---------
select * from sys.all_objects
--------查看部分列-----
select * from sys.columns
------------查看有哪些用户数据库------
select * from sys.databases where database_id >= 4
-------------当前数据库有哪些表-----
select * from sys.tables
----------查看表的列的详细信息------------------
exec sys.sp_columns 'orders'
-----------------有关这个表的相关内容-------------------
exec  sp_help 'orders'
--------sp表示存储过程----------只能对于存储过程,查看他的脚本文本---------
exec sp_helptext 'GetAvgPriceByCategoryName'
------------------使用拓展存储过程-------------------------
exec xp_cmdshell 'mkdir c:\text'

--------------------------------------------------------------------------------
----------------------分页显示表的数据的存储过程---------------------------
-----------------------------------------------------------------------
alter proc GetPage
@PageIndex int = 1,
@PageSize int = 30,
@PageCount int output
as
  declare @start int, @end int
  set @start = (@PageIndex - 1) * @PageSize + 1
  set @end = @start + @PageSize - 1
  -------------row_number函数让我们对查询结果自动编号-----------------
  select row_number()over(order by CustomerId) as [no],* into #temp from Orders
  ----------用于捕获最近一次执行的列数-------------------------
  declare @rows int
  set @rows = @@rowcount---获取得到的行数
  set @PageCount = @rows/@PageSize
  if(@rows % @PageSize <> 0)
    set @PageCount = @PageCount + 1
   
  select * from #temp where [no] between @start and @end
 
  declare @p int
  exec GetPage 10,30,@PageCount = @p output
  select @p
----------------------------------------------------------------------------------

---------------------------------------------------------------------------
-----------------转到banksystem中---------------------------------------
-------------------------阻止对卡号的变更--------------------------------------
------触发器是对表的操作,下面创建一个针对Account表的update的触发器--
create trigger NoUpdateCardNo on Accounts for update
as
   if(update(CardNo))
   begin
      raiserror('不能更改银行卡号',16,1)
      rollback transaction
   end
  
update Accounts set CardNo = 'sddfs'

 

 

---------------------------------清空表中的数据----------------------
truncate table Transactions
delete from

------------------------去掉触发器-------------------------------
drop trigger NoUpdateCardNo

 

--------------deleted , inserted的利用-----------------------------
--------------向产品表中插入数据，单价不能超过同类产品平均价-------
create trigger NoLargeAvgPrice on Products for insert
as
   declare @avgPrice money
   select @avgPrice = avg(UnitPrice) from Products
   where CategoryId =
   (
      select CategoryId from inserted
   )
   declare @price money
   select @price = UnitPrice from inserted
   if(@price > @avgPrice)
   begin
     raiserror('插入产品的价格不能高于产品的品均价',16,1)
     rollback transaction
   end

insert into Products values(79,'Cofee',1,1,'24 - 12 oz bottles',18.8,20,30,20,0)
------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------