88bifa必发娱乐 26

关于SQL SERVER数据库学习总结

实验案例一:验证索引的作用

  对于SQL SERFVER数据库也学了有一阵子了,自己也对自己所学做了一些总结。

1、首先创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。

   我们首先学习数据库设计的一些知识点和用SQL语句建库。

88bifa必发娱乐 1

  设计数据库步骤:需求分析阶段,概要设计阶段,详细设计阶段,

2、向表中插入大量数据,数据越多,验证索引的效果越好。

   建数据库的SQL语句如下(包含了如果有该数据库先删除在建立数据库)

使用语句完成:While 1>0  Insert
into 学生表(姓名)  values(‘于美丽’)

use master
GO
if exists(select * from sysdatabases where name=’Wages’)
DROP database Wages
CREATE DATABASE Wages
 ON
 (
  NAME=’Wages_data’,
  FILENAME=’e:\project\Wages_data.mdf’,
  SIZE=5mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= ‘Wages_log’,
  FILENAME=’e:\project\Wages_log.ldf’,
  SIZE=3mb,
  FILEGROWTH=15%
 )
GO

上面语句是一个死循环,除非强制结束,如果1大于0就会一直向表中插入姓名

为了创建良好的数据库需满足三大范式。

如下图所示:

下面是创建表的SQL语句(包含了如果有该表先删除在建表)

88bifa必发娱乐 2

USE Wages
GO
if exists(select * from sysobjects where name=’WageInfo’)
DROP table WageInfo
CREATE TABLE WageInfo 
(
  CompanyID INT primary key IDENTITY(1,1),
  CompanyName varchar(50) NOT NULL,
  Poste varchar(50) NOT NULL,
)
GO
if exists(select * from sysobjects where name=’StudentInfo’)
DROP table StudentInfo
CREATE TABLE StudentInfo
(
  ID  INT primary key IDENTITY(1,1),
  Name  VARCHAR(50) NOT NULL,
  CompanyID INT ,
  PosteDate  DATETIME ,
  Wage  int,
)
GO

等待5分钟左右,打开表的属性,查看表的行数,当前为1032363,如下图所示:

下面是创建约束

88bifa必发娱乐 3

语法如下

3、 使用语句查询第900000行的数据,Select
* from 学生表 Where 学号=900000

alter table 表名

88bifa必发娱乐 4

add constraint 约束名 约束类型 具体的约束说明

4、打开“sql server
profiler ”工具进行跟踪,如下图所示:

示例

打开“sql server
profiler ”工具查看跟踪的信息,发现查询时间很长,cpu工作了265毫秒,reads:读了8649次,writes:写了10次,duration:总计花费2336毫秒完成查询。

创建外键约束

88bifa必发娱乐 5

alter table StudentInfo
add constraint pk_CompanyID foreign key(CompanyID) references
WageInfo(CompanyID)
GO

为了下面分析文件更准确,多执行几次Select*
from 学生表 Where 学号=900000

插入数据语句如下

然后把跟踪的结果保存在桌面上:

insert into WageInfo(CompanyName, Poste)values
(‘微软’,’职员’),
(‘百度’,’职员’),
(‘腾讯’,’职员’),
(‘爱奇艺’,’职员’),
(‘华为’,’职员’)
insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values
(‘张三’,1,’2016-05-05′,2500),
(‘李四’,2,’2016-05-06′,2600),
(‘王五’,3,’2016-05-07′,3000),
(‘赵二’,4,’2016-05-08′,4000),
(‘钱一’,5,’2016-05-09′,5000)
insert into StudentInfo(Name, CompanyID, PosteDate,
Wage)values(‘钱二’,null,’2016-05-09′,NULL)

 88bifa必发娱乐 6

然后我们学习了变量,变量分全局变量和局部变量。

5、打开“数据库引擎优化顾问”,添加跟踪文件,进行分析,发现索引建议,需要建立索引。

创建变量语法如下是

 88bifa必发娱乐 7

declare @变量名 数据类型

注意选择benet数据库中的学生表,然后点击“开始分析”

局部变量有两种赋值方法

 88bifa必发娱乐 8

set @变量名=value

索引类型为clusterd(聚集索引),索引列为“学号”。

select @变量名=value

6、按照“数据库引擎优化顾问”的索引建议建立聚集索引,并且选择“唯一”

区别是select可一次对多个变量赋值,set只能一次对一个变量赋值。

88bifa必发娱乐 9

全局变量只能用不能自己创建和赋值!!!

7、再次执行Select *
from 学生表Where 学号=900000

输出语句

 

88bifa必发娱乐,print 和select

8、打开sql server
profiler查看跟踪的时间,发现查询时间大幅提升,说明索引可以提高查询速度。

use MySchool

88bifa必发娱乐 10

go

发现总计时间为1毫秒,几乎忽略不计

select * from StuInfos

 

1.班级表   班级编号 (主键)   班级名   (长度固定3位)   班级人数 (默认30)

实验案例二:分别练习创建各种索引

  if exists(select * from sysobjects where name=’Classes’)

1、创建聚集索引

drop table Classes

目前tstudent表中没有任何索引也没有主键

  go  

 88bifa必发娱乐 11

 create table Classes  

为tstudent表创建聚集索引

 (   clsid int identity(1,1), –班级编号  

88bifa必发娱乐 12

‘ clsname varchar(3), –班级名称   clsnums int –班级人数   )   

选中studentID,单击左上侧的主键按钮

go   

 88bifa必发娱乐 13

‘alter table Classes

为Tstuden表的studentID创建主键就同时创建了聚集索引

add constraint pk_clsid_Classes   primary key(clsid)  

88bifa必发娱乐 14

 alter table Classes

2、创建组合索引

为成绩表创建组合索引,因为一个学生不能为一门学科录入两次成绩,所以将成绩表中的studentID和subjectID创建组合索引

 88bifa必发娱乐 15

88bifa必发娱乐 16

88bifa必发娱乐 17

88bifa必发娱乐 18

解决办法:

菜单栏—-工具—-选项

找到设计器(designers),将标记处的勾去掉,单击“确定”

 88bifa必发娱乐 19

这样组合索引就创建成功了。

 88bifa必发娱乐 20

add constraint ck_clsname_Classes   check(len(clsname)=3)  

3、创建唯一索引

创建唯一性约束的时候就会创建唯一性索引,不能有重复值

为Tstudent表创建唯一非聚集索引

create unique nonclustered index U_cardID on TStudent(cardID)

88bifa必发娱乐 21

 alter table Classes

4、创建非聚集索引—可以有重复值

为Tstudent表的姓名列创建非聚集索引

88bifa必发娱乐 22

使用命令查看表上的索引

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name=’Tstudent’)

Indid中1代表聚集索引

Indid中2代表唯一非聚集索引

Indidz中3代表非聚集索引

88bifa必发娱乐 23

使用sp_help 
Tstudent也可以查看到相关表的信息

88bifa必发娱乐 24

实验案例三:创建视图

方法一:在图形界面下创建视图(以Myschool数据库为例)

创建一个视图,分别来自三个的表的三个列,并重命名列,生成的视图名为student_info,如下图所示:

 88bifa必发娱乐 25

通过查询语句查看视图:select   *   
from  student_info

 

方法二:使用语句创建视图(以schoolDB数据库为例)

进行数据库设计的时候,一个表有很多列,我们可以在表上创建视图,只显示指定的列。

Select语句可以作为一个视图

select Sname,sex,Class from dbo.TStudent where Class=’网络班’

add constraint df_clsnums_Classes   default 30 for clsnums   

1、创建视图,筛选行和列

create view netstudent

as

select Sname,sex,Class from dbo.TStudent where Class=’网络班’

从视图中查找数据:

select * from netstudent where sex=’男’

创建视图,更改列的表头,计算列,产生计算列

selectStudentID,Sname,sex,cardID,Birthday,Email,Class

from dbo.TStudent

88bifa必发娱乐 26

go  

2、创建视图,更改列的表头

create view V_Tstudent1

as

select StudentID  学号,Sname姓名,sex  性别,cardID  身份证号码,Birthday  生日,Class  班级 from dbo.TStudent

 

select * from V_Tstudent1

 

以后再去查询的时候就非常方便了。

 

实验案例四:存储过程

1、常用的系统存储过程

exec sp_databases     
–列出当前系统中的数据库

exec sp_renamedb  ‘mybank’,’bank’  
–改变数据库名称(单用户访问)

use MySchool

go

exec sp_tables     
                 –当前数据库中可查询对象的列表

exec sp_columns student           
–查看表student中列的信息

exec sp_help student              
–查看表student的所有信息

exec sp_helpconstraint student      
–查看表student表的约束

exec sp_helptext view_student_result  
–查看视图的语句文本

exec sp_stored_procedures     
–返回当前数据库中的存储过程列表

 

 

2、常用的扩展存储过程(在C盘下创建一个文件夹bank)

exec xp_cmdshell  ‘mkdir 
c:\bank’,no_output  –创建文件夹c:\bank

exec xp_cmdshell  ‘dir
c:\bank\’               –查看文件

如果执行不了上面的语句,请开启下面的功能。然后再次执行上面的两条语句。

 

若xp_cmdshell作为服务器安全配置的一部分而被关闭,请使用如下语句启用:

exec sp_configure  ‘show advanced
options’, 1   –显示高级配置选项(单引号中的只能一个空格隔开)

go

reconfigure                                   
–重新配置

go

exec sp_configure 
‘xp_cmdshell’,1                 –打开xp_cmdshell选项

go

reconfigure                          
         –重新配置

go

 

 

3、用户自定义的存储过程(以schoolDB数据库为例,计算网络管理专业的平均分)

use schoolDB

go

if exists  (select *  from  sysobjects
where name=’usp_getaverageresult’)

drop  procedure
 usp_getaverageresult

go

create  procedure
 usp_getaverageresult

as

declare  @subjectid  nvarchar(4)

select @subjectid=subjectid  from 
dbo.TSubject where subJectName=’网络管理’

declare  @avg decimal (18,2)

select  @avg=AVG(mark) from  dbo.TScore
wheresubJectID=@subjectid

print ‘网络管理专业平均分是:’+convert(varchar(5),@avg)

go

exec usp_getaverageresult

 

实验案例五:触发器

 

(Myschool数据库为例)

创建触发器(禁止修改admin表中数据):

create trigger  reminder

on  admin

for update

as

print ‘禁止修改,请联系DBA’

rollback transaction

go

 

执行语句,查看错误信息:

update Admin set  LoginPwd=’123′ 
where LoginId=’benet’

select *  from  Admin

 

实验案例六:创建触发器

(Myschool数据库为例)

要求:创建一个触发器,以确保student表中的数据不会被删除。

create  trigger stu_del

on  student

for  delete

as

print
  ‘你不具备删除管理员信息的权限’

rollback  transaction

go

 

执行一条delete语句,测试结果。

delete   from  Student where 
StudentName=’喜洋洋

 

文章参考微信公众号:L宝宝聊IT

 insert into Classes select ‘t10’,25 union select ‘t11’,18 union select
‘t12’,23

2.外键约束   为学生表添加一个clsid列来表示其班级   alter table StuInfos
add clsid int   外键约束   

alter table StuInfos with nocheck –不对表现有的数据作限制操作

add constraint fk_StuInfos_Classes    foreign key(clsid) references
Classes(clsid)   

删除约束   

alter table StuInfos drop constraint fk_StuInfos_Classes

3.建表同时建约束,老师表   编号     (主键 标识列)   名称     (非空)

性别     (男或女,默认男)   年龄     (12-60)   电话     (唯一)   班级编号
(外键)   

if exists(select * from sysobjects where name=’TeaInfos’)

drop table TeaInfos  

 go  

 create table TeaInfos  

 ( id int primary key identity(1,1), –编号

name varchar(10) not null, –名称

sex char(2) check(sex=’男’ or sex=’女’) default ‘男’, –性别

age int check(age>=12 and age<=60), –年龄

tel char(11) unique, –电话

clsid int foreign key references Classes(clsid) –班级   )

  go

一:操作变量 –1– 声明变量@temp值为1并打印出来 声明变量关键字:

declare   declare @temp int –声明   

set @temp=1       –赋值  

 print @temp       –输出   

  declare @temp int=1 –声明的同时赋值  

 print @temp         –输出   

 

赋值方式一:用set 输出方式一:用print   declare @temp int –声明   

select @temp=1 –赋值   select @temp   –输出

赋值方式二:用select 输出方式二:用select —

2– 声明三个变量存储’姓名、性别、年龄’,再打印信息,格式如下:
姓名:杰达姆,性别:男,年龄:18 –声明

declare @name varchar(10),         @sex char(2),         @age int

–赋值 select @name=’杰达姆’,@sex=’男’,@age=18

–输出 print  ‘姓名:’+@name+’,性别:’+@sex+’,年龄:’   +cast(@age as
varchar)   

–convert(varchar,@age)

–两个转型函数:   1.convert — 语法:  convert(类型,数据)   2.cast   

— 语法:  case(数据 as 类型)

–3– select * from StuInfo

打印张秋丽的信息(来自于student数据库的数据),格式如下: 姓名:张秋丽
性别:男 年龄:18

–声明 declare  @name varchar(10)=’张秋丽’, @sex char(2), @age int

–赋值:来自于表内 select @sex=stuSex,@age=stuAge from stuinfo  where
stuName=@name

–输出 print ‘姓名:’+@name print ‘性别:’+@sex print ‘年龄:’+cast(@age as
varchar)

–4– 查询李文才的左右同桌的信息 declare @seat int select @seat=stuSeat
from stuinfo where stuname=’李文才’ select * from stuinfo  where
stuSeat=@seat-1 or stuSeat=@seat+1

–5– 查询比张秋丽大10岁以上的学生信息 declare @age int  select
@age=stuAge from stuinfo where stuName=’张秋丽’ select * from stuinfo
where stuAge>=10+@age 

总结: set和select的区别  

 1. set一次只能为一个变量赋值,而select能同时为多个变量赋值  

 2. set只能赋一个固定的值,而select能动态的赋值(值来自于表内)
select的作用  

 1. 查询SQL语句,如: select * from 表名  

 2. 赋值操作,   如: select 变量名=值  

 3. 输出操作,   如: select 变量名 二:控制流程结构:if,else

–1– 声明变量number,并赋值,然后判断是偶数还是奇数,结果如下:
当前值为11,它是一个奇数 declare @number int set @number=12
if(@number%2=0) print ‘该数为偶数’ else print ‘该数为奇数’ –

-2– 根据输入的年龄显示人的生长阶段
未成年人<18,青年人<30,成年人<60,老年人<100,超人>=100

declare @age int set @age=21

if(@age<18) print ‘未成年人’ else if(@age<30)

print ‘青年人’

else if(@age<60)

print ‘成年人’

else if(@age<100)

print ‘老年人’

else print ‘超人’

select * from StuInfo select * from StuExam

–3– 统计笔试平均分,如果平均分超过70分打印’优秀’
同时显示前三名学员的考试信息否则      打印’较差’显示后三名学生信息

declare @avgScore int select @avgScore=AVG(writtenExam) from StuExam
if(@avgScore>=70) begin print ‘本班成绩优秀’

select top 3 * from StuExam order by writtenExam desc end else begin
print ‘本班成绩较差’ select top 3 * from StuExam order by writtenExam
end

三:循环结构 –1– 输出九九次’我爱你’ declare @i int=1  while(@i<=99)
begin print ‘第’+convert(varchar,@i)+’我爱你’ set @i+=1 end

–2– 计算1-100的累加和,结果格式:1-100的累加和为:xxx declare  @i int=1,
@sum int=0 while(@i<=100) begin set @sum+=@i set @i+=1 end print
‘1-100的累加和为:’+convert(varchar,@sum)

–3– 不停的提高学生笔试成绩2分,让所有学生的笔试成绩都及格

declare @count int –用来记录不及格的人数 while(1=1) begin
–计算不及格的人数 select @count=COUNT(*) from StuExam   where
writtenExam<60 –判断 if(@count=0)   break –退出死循环 else   update
StuExam set writtenExam+=2 end select * from StuExam

四:多分支语句

–1– 请声明变量@name表示学生姓名,对该学生年龄进行划等级 具体如下:
12岁前显示:’小学生’ 12-17显示’初中生’  18-22显示’高中生’
23-28显示’大学生’ 28以上’超人’ 结果格式:xxx是一个xxx

declare  @name varchar(20)=’小强’, @age int=23, @result varchar(10)

–多分支 set @result=case     when @age<12 then ‘小学生’     when
@age<17 then ‘初中生’     when @age<22 then ‘高中生’     when
@age<28 then ‘大学生’     else ‘超人’    end

–输出 print @name+’是一个’+@result

–2– 显示学号、笔试成绩、等级,数据如下:  笔试成绩:90以上的–A等     
   80以上的– B等         70以上的– C等         60以上的– D等     
   60以下的– E等 stuNo   writtenExam   grade s25303  60            D等
s25302  40            E等 s25301  77            C等 s25318  45          
 E等 select  stuNo, writtenExam, grade=case    when writtenExam>=90
then ‘A等’    when writtenExam>=80 then ‘B等’    when
writtenExam>=70 then ‘C等’    when writtenExam>=60 then ‘D等’  
 else ‘E等’     end from StuExam –3–
请根据平均分和下面的评分规则,编写T-SQL语句查询学员的成绩 优 :90分以上
   良 :80-89分     中 :70-79分     差 :60-69分     极差 :60分以下
        

select AVG(writtenExam),A=case      when Avg(writtenExam)>90 then
‘优’      when Avg(writtenExam)>80 and Avg(writtenExam)<89 then
‘良’      when Avg(writtenExam)>70 and Avg(writtenExam)<79 THEN
‘中’      when Avg(writtenExam)>60 and Avg(writtenExam)<69 then
‘差’      when Avg(writtenExam)<60 then ‘极差’      end      from
stuExam    

–4– 问题: 根据如下规则对机试成绩进行反复加分,     
 直到机试平均分超过85分为止       请编写T-SQL语句实现,注:(循环+多分支)
90分以上:  不加分 80-89分:   加1分 70-79分:   加2分 60-69分:   加3分
60分以下:  加5分 declare @a int   while(1=1) begin select
@a=AVG(labExam)from dbo.stuExam if(@a<85) begin update dbo.stuExam
set labExam =case          when labExam>=90 then  labExam        
 when labExam>=80 then labExam+1          when labExam>=70 then
labExam+2          when labExam>=60 then labExam+3          else     
    labExam+5         end end else break end select AVG(labExam) AS
机试成绩平均分 from dbo.stuExam

查询

子查询:查询中再查询,通常是以一个查询作为条件来供另一个查询使用
语法:
   select 列表 from 表名 
 where >(子查询)
注意:
 使用比较运算符时,要求子查询只能返回一条或空的记录!
示例:
 要求查出比我小的学生信息
 select * from student
 where studentno in
 (select studentno from student
 where studentname=’我’)

NOT IN:确定给定的值是否与子查询或列表中的值相匹配,如果不匹配则反回真。
使用方法:
 在需要子查询返回多数据时使用。
语法:
 select 列表 from 表名 
 where 列名 not in(子查询)
示例:
 查询未参加过考试的学生信息
 select  * from student 
 where studentno not in
 (select studentno from Result)

1.EXISTS 子查询
 EXISTS: exists 关键字能够检测数据是否存在,如果存在返回真。
语法
 if exists(子查询)
 语句
示例:
 查询本校学生有没有叫张三的
 if exists( select * from student
 where studentname=’张三’ )
 begin
 print ‘有叫张三的’
 end

  1. NOT EXISTS 子查询
     NOT EXISTS: exists 关键字能够检测数据是否存在,如果不存在返回真
    语法
     if not exists(子查询)
     语句
    示例:
     查询本校学生是不是没有叫张三的
     if not exists( select * from student
     where studentname=’张三’ )
     begin
     print ‘没有叫张三的’
     end

1:非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
 2:相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。数据是否存在,如果不存在返回真

示例:检索出在work表中每一个部门的最高基本工资的职工资料  select * from
work a where 基本工资=(select max(基本工资) from work b where
a.部门名称=b.部门名称)

 事物视图索引

语法

begin transaction

commit transaction

rollback transaction

视图

创建视图索引

if exists(select * from sysobjects where name=’视图名’)

drop view 视图名

create view  视图名

as

SQL语句

索引

if exists(select name from sysindex where name=’索引’)

drop index 表名。索引名

create 索引类型 index 索引名

on 表名(列名)

with fillfactor=30

GO

一 存储过程的概念

存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

为什么需要存储过程?

1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

存储过程的优点:

1.模块化程序设计

2.执行速度块,效率高

3.减少网络流量

4.具有良好的安全性

 

二 系统存储过程

SQL_SERVER 提供系统存储过程,它们是一组预编译的T-SQL语句,系统存储过程提供了管理数据库和更新表的机制,并充当从系统表中检索信息的快捷方式。

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

三 用户自定义的存储过程

1.创建不带参数的存储过程

Create proc usp_selectstu

As

Select StudentName,Gender,GradeId,Phone from dbo.Student

 

调用存储过程:exec
usp_selectstu

2.创建带入参数的存储过程

Create proc usp_stuInfo @gradeid int=2       (默认)

As

Select * from student where gradeId=@gradeid

调用存储过程:exec usp_stuInfo
2

3.创建带出参数的存储过程

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

As

Select @gradeid=gradeid from student where  studentname=@name

print @gradeid

 

调用存储过程:

declare @id int

exec usp_selectGrade ‘李小龙’,@id output 

 

  4、 带通配符参数存储过程

Create proc usp_one  @name nvarchar(10)

as

select * from dbo.Student where StudentName like @name

 

exec usp_one ‘李%’

   5、 不缓存存储过程

缓存就是数据交换的缓冲区(称作Cache),当某一硬件要读取数据时,会首先从缓存中查找需要的数据,如果找到了则直接执行,找不到的话则从内存中找。由于缓存的运行速度比内存快得多,故缓存的作用就是帮助硬件更快地运行。

Sql
Server系统内存管理在没有配置内存最大值,很多时候我们会发现运行Sql
Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存根据系统默认预留使用为准,至少4M),才会清除一些缓存释放少量的内存为新的缓存腾出空间。

这些内存一般都是Sql Server运行时候用作缓存的,例如你运行一个select语句,
执行个存储过程,调用函数;

  1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql
    Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

2.执行命令缓存:在执行存储过程,自定函数时,Sql
Server需要先二进制编译再运行,编译后的结果也会缓存起来,
再次调用时就无需再次编译。

 

create proc proc_temp with recompile as     select * from student
exec proc_temp

 

6,加密存储过程

exec sp_helptext 储存过程名      可以查看储存过程代码

create proc proc_temp_encryption

with encryption

as

    select * from student;

go

–存储过程的内容不会被轻易看到(虽然解密也是有可能的)。

–应用这个,我们可以对某些关键的存储过程进行加密。

–但此时,存储过程仍然能被execute、alter和drop。

exec proc_temp_encryption;

exec sp_helptext ‘proc_temp’

exec sp_helptext ‘proc_temp_encryption’

(注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。) 

 

一 存储过程的概念

存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

为什么需要存储过程?

1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

存储过程的优点:

1.模块化程序设计

2.执行速度块,效率高

3.减少网络流量

4.具有良好的安全性

 

二 系统存储过程

SQL_SERVER 提供系统存储过程,它们是一组预编译的T-SQL语句,系统存储过程提供了管理数据库和更新表的机制,并充当从系统表中检索信息的快捷方式。

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

三 用户自定义的存储过程

1.创建不带参数的存储过程

Create proc usp_selectstu

As

Select StudentName,Gender,GradeId,Phone from dbo.Student

 

调用存储过程:exec
usp_selectstu

2.创建带入参数的存储过程

Create proc usp_stuInfo @gradeid int=2       (默认)

As

Select * from student where gradeId=@gradeid

调用存储过程:exec usp_stuInfo
2

3.创建带出参数的存储过程

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

As

Select @gradeid=gradeid from student where  studentname=@name

print @gradeid

 

调用存储过程:

declare @id int

exec usp_selectGrade ‘李小龙’,@id output 

 

  4、 带通配符参数存储过程

Create proc usp_one  @name nvarchar(10)

as

select * from dbo.Student where StudentName like @name

 

exec usp_one ‘李%’

   5、 不缓存存储过程

缓存就是数据交换的缓冲区(称作Cache),当某一硬件要读取数据时,会首先从缓存中查找需要的数据,如果找到了则直接执行,找不到的话则从内存中找。由于缓存的运行速度比内存快得多,故缓存的作用就是帮助硬件更快地运行。

Sql
Server系统内存管理在没有配置内存最大值,很多时候我们会发现运行Sql
Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存根据系统默认预留使用为准,至少4M),才会清除一些缓存释放少量的内存为新的缓存腾出空间。

这些内存一般都是Sql Server运行时候用作缓存的,例如你运行一个select语句,
执行个存储过程,调用函数;

  1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql
    Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

2.执行命令缓存:在执行存储过程,自定函数时,Sql
Server需要先二进制编译再运行,编译后的结果也会缓存起来,
再次调用时就无需再次编译。

 

create proc proc_temp with recompile as     select * from student
exec proc_temp

 

6,加密存储过程

exec sp_helptext 储存过程名      可以查看储存过程代码

create proc proc_temp_encryption

with encryption

as

    select * from student;

go

–存储过程的内容不会被轻易看到(虽然解密也是有可能的)。

–应用这个,我们可以对某些关键的存储过程进行加密。

–但此时,存储过程仍然能被execute、alter和drop。

exec proc_temp_encryption;

exec sp_helptext ‘proc_temp’

exec sp_helptext ‘proc_temp_encryption’

(注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。)