1.创建索引
--聚集索引
create clustered index index_name
on table(column_name)
创建非聚集索引
create nonclustered index index_name on table(column_name)
:聚簇(或者叫做聚集,cluster)索引和非聚簇索引。
字典的拼音目录就是聚簇(cluster)索引,笔画目录就是非聚簇索引。这样查询“G到M的汉字”就非常快,而查询“6划到8划的字”则慢。
聚簇索引是一种特殊索引,它使数据按照索引的排序顺序存放表中。聚簇索引类似于字典,即所有词条在字典中都以字母顺序排列。聚簇索引实际上重组了表中的数据,所以你只能在表中建立一个聚簇索引。
当数据按值的范围查询时,聚簇索引就显得特别有用。因为所有SQLServer都必需先找到所查询范围的第一行,然后依次下去,直到该范围的最后一个值找到为止,并且保证了所有其他值也落在这个范围内。举一个例子,一个应用程序要查找首字母位于G和P之间的姓名列表,SQLServer首先找到以字母G开头的名字,取出所有记录,直到找到以字母P开头的名字为止,这种方法使得查询过程非常高效。
进行大量数据改动的表不适宜用聚簇索引,因为SQLServer将不得不在表中维护行的次序。如果要索引的值极少,例如一个列包含的全都是1和0,创建聚簇索引就不是个好主意。如果表经常由一个指定的列来排序,该列将是簇索引的最佳候选列。这是因为表中的数据已经为你排好序了。如果访问一个表并使用BETWEEN、<、>、>=或<=操作符来返回一个范围的值时,应该考虑使用聚簇索引。
2.视图create view ordersWithNum
as
select *,row_number() over(order by oprice desc) as num
from TblOrders
3.触发器
create trigger bak_order
on tblorders //在这个表上的触发器
after insert
as
begin
insert into tblOrders_bak(oname,ocount,oprice)
select oname,ocount,oprice from inserted
end
create trigger update_order
on tblorders
instead of update
as
begin
update tblOrders_bak set ocount=(select ocount from inserted)
where oname in (select oname from inserted)
end
4.游标
关系数据库中的操作会对整个行集产生影响。由 SELECT 语句返回的行集包括所有满足该语句 WHERE 子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序,特别是交互式联机应用程序,并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。
游标通过以下方式扩展结果处理:
允许定位在结果集的特定行。
从结果集的当前位置检索一行或多行。
支持对结果集中当前位置的行进行数据修改。
--游标的定义
<pre name="code" class="csharp">--游标的定义
declare s1 cursor for select ClassId,pp from Class
--在访问游标中某行的数据时,需要声明变量
declare @ClassId int,@pp int
--打开游标
open s1
--通过游标从结果集中取数据,注意:每次取一行的值
fetch s1 into @ClassId,@pp
while(@@FETCH_STATUS=0)
begin
set @pp=@pp+1
update Class set pp=@pp where ClassId=@ClassId
fetch s1 into @ClassId,@pp
end
--关闭游标
close s1
--释放游标
deallocate s1
5.自定义函数
CREATE FUNCTION getSUM
(
@CNO int --参数
)
RETURNS int --返回值类型
AS
BEGIN --函数体开始
declare @sum int
select @sum=sum(price) from BOOKS where bno in (select bno from BORROW where cno=@CNO)
return @sum
END --函数体结束
--调用函数
select dbo.getSUM(2)
6.事务
事务的好处在于,当你需要对多个表进行相关性操作时,使用BeginTrans()开启事务,然后操作,如果中间哪个表更新出错了,或者数据有问题,你可以用RollBack()方法在取消之前的相关性操作,如果没有错,那就使用Commit()方法提交你的修改。另外,在这个过程中,所有你改动的内容只对你自己有效,别人不能知道你改了什么,也不能对你正在改的表做修改,直到你提交之后,别人才能修改这些表,检索出你修改过的内容
实现一个功能如下:
一个借书卡号借走某书号的书,则该书的库存量减少1,当某书的库存量不够1本的时候,该卡号不能借该书
create PROCEDURE pro_jieshu
@cno int, --卡号
@bno int, --书号
@date datetime --借书时间
AS
BEGIN
begin tran
declare @quantity int
select @quantity=quantity from books where bno=@bno
insert into borrow values(@cno,@bno,@date)
update books set quantity=@quantity-1 where bno=@bno
if(@quantity>0)
begin
commit tran
end
else
begin
print '已无库存'
rollback
end
END