分区表与后台编程技术

分区表

• 将表中的数据按水平方式划分成不同的子集。
• 合理地使用分区可提高数据库性能。
• 如果表中大量的数据都是经常使用的数据，而且他们的操作方式基本一样的，则最好不要使用分区表。
• 创建分区表步骤：
  • 创建分区函数
  • 创建分区方案
  • 按分区方案创建表

例：对表dbo.tb_StudentInfo1的StdInfoBirthD字段进行分区2007年之前，2007至2009年之间，大于2009年分别至三个独立的文件组

1. 创建分区函数

   create partition function myRangePF(int)
   as range left for values(1, 10,20)
   
   

2. 创建分区方案

   • 分到同多个文件组

     create partition scheme myRangePS
     as partition myRangePF
     to ([primary], 'fgroup1', 'fgroup2')
     

   • 分到一个文件组

     create partition scheme myRangePS
     as partition myRangePF
     all to ('fgroup1')
     

3. 依据分区方案创建表

   create table test(
   	id int primary,
   	name varchar(8)
   )
   on myRangePS(id)
   

4. 完整代码

   -- 对表dbo.tb_StudentInfo1的StdInfoBirthD字段进行分区2007年之前，2007至2009年之间，大于2009年分别至三个独立的文件组
   
   -- 创建分区函数
   create partition function BrithOrderPF(datetime)
   	as range left for values('2007', '2009')
   
   -- 为StuSelDB添加两个文件组用于分区方案
   alter database StuSelDB add filegroup fgroup3
   go
   
   alter database StuSelDB add filegroup fgroup4
   go
   
   -- 为fgroup3文件组分配文件
   alter database StuSelDB add file
   (
   	name=for_partition1,
   	filename='D:\sql server\for_partition1.ndf',
   	size=10MB,
   	filegrowth=10%
   )
   to filegroup fgroup3
   go
   
   alter database StuSelDB add file
   (
   	name=for_partition2,
   	filename='D:\sql server\for_partition2.ndf',
   	size=10MB,
   	filegrowth=10%
   )
   to filegroup fgroup4
   go
   
   -- 创建分区方案
   create partition scheme BrithOrderPS
   	as partition BrithOrderPF
   	to ([PRIMARY], fgroup3, fgroup4)
   go
   
   -- 根据分区方案创建表dbo.tb_StudentInfo1
   create table tb_StudentInfo1
   (
   	stdinfoID int identity(1,1) not null,
   	StdInfoBirthD datetime not null,
   	stdclassid int not null
   )
   on BrithOrderPS(StdInfoBirthD)
   go
   
   -- 添加数据
   insert into tb_StudentInfo1 values
   					('2002-1-2', 3),
   					('2008-1-2', 3),
   					('2010-1-4', 4),
   					('2009-1-1', 4)
   go
   
   
   --查询数据分区信息
   select *, $partition.BrithOrderPF(StdInfoBirthD) as '所属分区' from tb_StudentInfo1
   

存储过程

1. 优点

• 改善查询性能
• 减少网络流量
• 增强应用程序的安全性
• 允许模块化程序设计

2.创建存储过程

1. 无输入无输出类型

   --创建
   create procedure p_stuInfo
   as 
   	select stdInfoID, stdInfoName, stdInfoGreade from StdInfo
   	where stdInfoSex='男'
   	
   -- 执行
   exec p_stuInfo
   

2. 有输入无输出

   • 传入参数方式有：
     • 按参数位置传递值
     • 按参数名传递值

   -- 创建
   create procedure p_stuInfo @gender varchar(2)
   as
   	select stdInfoID, stdInfoName, stdInfoGreade from StdInfo
   	where stdInfoSex=@gender
   
   -- 执行
   -- 按位置
   execute p_stuInfo '男'
   -- 按参数
   exec p_stuInfo @gender='男'
   

3. 有输入有输出

   -- 创建
   create procedure p_stuInfo 
   @gender varchar(2), @grade int, @name varchar(8) output
   as
   	select @name=stdInfoName from StdInfo 
   	where stdInfoSex=@gender and stdInfoGrade=@grade
   
   -- 执行
   declare @name varchar(8)
   exec p_stuInfo @gender='男', @grade=3, @name output
   

3.删除存储过程

drop procedure p_stuInfo

用户定义函数

1.两类用户定义函数

• 标量函数：只返回单个数据

• 表值函数：分为内联表值函数和多语句表值函数，返回一个表

2.实例

• 标量函数

  -- 创建
  create function f_count_std(@grade int)
  returns int
  as
  begin
  	declare @nums int
  	select @nums=count(stdInfoID) from stdInfo
  	where stdInfoGrade=@grade
  	return @nums
  end
  
  -- 执行
  -- 单独执行
  select f_count_std(3)
  
  -- 放到复杂的sql中
  select stdInfoGrade, f_count_std(3) from stdInfo
  where stdInfoGrade=3
  

• 表值函数

  • 内联表值函数

    -- 创建
    create function f_stdInfo(@grade int)
    returns table
    as
    	return(
    		select stdInfoName from stdInfo 
    		where stdInfoGrade=@grade
    	)
    -- 执行
    select f_stdInfo
    

  • 多语句表值函数

    在定义函数时可以调用另一个已经定义好的函数

    --创建
    create function f_stdInfo(@grade int)
    returns @f_stdInfo(
    	姓名 varchar(8),
    	性别 varchar(2)
    )
    as
    	insert into @f_stdInfo
    	select stdInfoName, stdInfoSex from stdInfo
    	where stdInfoGrade=@grade
    return
    
    --执行
    select f_stdInfo(3)
    

3.删除

drop function f_stdInfo

触发器

1.优点

• 可完成比check更复杂的数据约束

2.触发器类型

• 前触发器
  • 关键字：instead of
  • 在表或视图上，在insert、delete、upgrade最多定义一个instead of触发器
• 后触发器
  • 关键字：for|after
  • 同一种操作上可建立多个触发器

3.临时表

• inserted：用于存储insert和update语句后半部分所影响的行（即增加或更新后的行）
• deleted：用于存储delete和update语句前半部分影响的行（即删除或更新前的行）

注：update可以看成是先删除再添加

4.实例

• 后触发器

  • insert

    /*
    创建INSERT触发器,如果向教师信息表TeachInfo中插入记录时，
    检查该记录的院系编号在院系信息表DepInfo中是否存在，如果不存在，则不允许插入
    */
    create trigger trig_insertTeachInfo
    on TeachInfo 
    after insert
    as
    begin transaction
    if exists (select * from inserted a
    		   where a.DepInfoID not in(
    		   select DepInfoID from DepInfo))
    begin
    	raiserror('数据一致性验证', 16, 1)
    	rollback transaction
    end
    else
    	commit transaction
    		
    --测试
    insert TeachInfo(DepInfoID, TeachTypeID, TeachInfoNum, TeachInfoName) values(9, 2, '123452', '张三') 
    insert TeachInfo(DepInfoID, TeachTypeID, TeachInfoNum, TeachInfoName) values(8, 2, '123452', '张三') 
    

  • delete

    /*
     创建DETLETE触发器，当删除学生信息表StudentInfo中已退学的学生资料时，
     自动删除学生选课表StudentCourse中此学生的信息
    */
    create trigger trig_deleteStudentInfo
    on dbo.StudentInfo 
    after delete
    as
    begin
    	declare @stdInfoID int
    	select @stdInfoID=deleted.StdInfoID from deleted
    	delete dbo.StudentCourse
    	where StdInfoID=@stdInfoID
    end
    
    --测试
    delete dbo.StudentInfo where StdInfoID=2
    
    select * from StudentCourse where StdInfoID=2
    
    

  • update

    /*
    创建UPDATE触发器，当班级信息表ClassInfo中的主键ClassInfoID进行修改时，
    必须对学生表中相应的classInfoID也进行修改
    */
    --更新分两步：先删除后插入
    --deleted,逻辑表，存放删除之后的记录
    create trigger trig_updateClassInfo
    on dbo.ClassInfo after update
    as
    begin transaction
    if update(ClassInfoID)
    begin
    	update dbo.StudentInfo
    	set ClassInfoID=(select ClassInfoID from inserted)
    	where ClassInfoID=(select ClassInfoID from deleted)
    end
    
    --测试
    update dbo.ClassInfo set ClassInfoID=444 where ClassInfoID=4
    select * from dbo.StudentInfo where ClassInfoID=4
    update dbo.ClassInfo set ClassInfoID=4 where ClassInfoID=444