Sql Server

数据完整性：数据的正确和准确
- 实体完整性：主键
- 域完整性：数据类型、检查约束、非空
- 引用完整性：外键
- 自定义完整性：规则
主键
- 用来区分实体，必须唯一、不允许为空
- 选择主键的原则：最少性、稳定
外键
- 用来表示和其他表关系的列，可以设置为允许空
- 要求：数据类型要和主表的主键类型一致
- 创建外键后对表数据的操作影响：
  - 有外键的表是子表
  - 添加：先主后子
  - 删除：先子后主
  - 修改：不能使数据孤立
标识列
- 自动编号（只增不减）
- 标识种子、标识增量
- 数据类型必须是整数，我门们通常用作主键
问题
- 允许更改主键的值吗？能

二、SQL语句

//增  值需要引号的数据类型： 字符串、时间日期
//方式一
insert into(表名)[列表值]
values('值'，) 
//方式二
insert into 表名(姓名，地址)
Select Name,Address(旧表名的内容)
from student
//方式三
select 表名.列名
into 新表名(自动创建)
form 表名 
//方式四
insert 表名(name,sex,address)
select 是，男，翱翔 union
select 是，男，翱翔 union
select 是，男，翱翔 union            
select 是，男，翱翔 

//改
update 表名
set 列 多个条件逗号隔开
where 列 多个条件and隔开

//删
delete from  列表
where 列名

//查
select <列名>   多个用逗号
from <表名>
where <查询条件表达式>  多个用and
order by <排序的列名>（ase(正排序)或desc(倒排序)）

//所有的数据行和列
select * from 表名 

//(三种别名的方式 as、空格、写在前面)
select Scord as 学生 ,Sname 空格 姓名 ,地址=Saddress     form Student     where Saddress<>'河南新乡' 

 //拼接  （相加类型要统一）
select Scord+','+LastName    form Student

//常量列 (增加一列)
Select 姓名=Name , 地址=Saddress , '清华大学'as 学校名称    from Student

//返回限制的行数
select top 5 (percent) from Student  where sex=0 //返回5行   括号里的是百分

//升序降序
select course score       from Student       where score<80     order by sourse score desc(倒排序)
(排序都是按照第一列，从第一个依次往后走，一个不对后面的都不对)

//查询为空
is null or =' '

//内联外联
内联 inner join（用来连接两个表）on（设置条件）号必须相同，where要明确
外联 left join  （用来连接两个表 显示空的学生）on（设置条件）

//聚合函数
sum()(和)
avg()(平均分)
max()(最大值)
min()(最小值)
count()(求数量)
//聚合代码
select sum(studentresult) as 总分,min(StudentResult) as 最低分
 from result

//分组  通常和聚合函数一块使用，只有出现在分组中列，才允许出现在select 中
group by
//分组代码
select gradeid, sex, count(*)
 from student
 group by gradeid,sex

//筛选
having
//筛选代码
Select GradeId,count(*) from Student group by GradeId having count(*)>20

//模糊查询
like ： % _ [] [^]、between、in
//模糊代码
where Address like '%山东%'、where StudentName like '张_'
 SubjectId in(1,2,3) || SubjectId like '[1-3]'
between : select * from student where borndate between '2000-1-1' and '2019-1-1'
in : select * from student where address in('北京', '上海')

//完整Sql语句
select 列
 from 表
 where 条件
 group by 列
 having 条件
 order by 列

SQL代码

三、函数

1.字符串函数
charindex(寻找一个只定的一个字符串在另一个字符串起始的位置)    select charindex('jbns','Myjbns cours',1)
len(长度)   Select len>6
upper(转换大小写)
ltrim(清除左边的空格) rtrim(清除右边的空格)
right(从右边返回指定的字符串) leight(从左边返回制定的字符串)
replace (替换一个字符串中的字符)
stuff (删除指定长度的字符，并在该位置插入一个新字符)

2.日期函数
getdate (当前系统日期)
dateadd(将指定的数值添加到指定的日期部分后的日期)
datediff(两个日期之间的指定日期部分的间隔)
datename(指定的日期部分字符串形式)   clect datename(dw,'01-02-2009' )返回：星期六
datepart(整数形式)   clect datepart(dw,'01-02-2009' )返回：7     datepart(month,getdate()=8)  (8月份)

4.数字函数
rand(返回0到1之间的随机数float值) select rand( ) 返回：0.79288062146374           right( rand(), 4)产生后四位随机数
abs (去数值表达式的绝对值) select abs(-43) 返回43
ceiling (取大于或等于指定数值、表达式的最小整数) ceiling(43.5) 返回:44
power(取小) power(43.5) 返回43
power(取数值表达式的幂值) power(5,2) 返回：25
round(将数值表达式四舍五入为指定精度) round(43.543,1) 返回：43.500
sign(对于正数+1，对于负数返回-1，对于0则返回0)
sqrt(取浮点表达式的平方根) sqrt(9) 返回：3

5.系统函数
convert(用来转变数据类型)  在sql中，不会发生自动类型转换，需要CONVERT进行转换 可以限定日期转换格式
current_user(返回当前用户的名字)
datalength(返回用于指定表达式的字节数)
system_user(返回当前所登陆的计算机名字)
user_name(从给定的用户ID返回用户名)

函数

四、深入数据库

4.1为什么需要规范化数据库

　　消除不必要的数据冗余，获得合理的数据库设计，提高应用性能

4.2映射基数　　

　　通过联系与该实体关联的其他实体个数（实体集），箭头来表示映射基数

4.3软件设计过程：

　　设计问题如果将信息都放在一个表内，信息重复、更新异常、插入异常（无法显示某些信息）、删除异常（丢失有用信息）。

需求分析（五大范式解决方法）
- 第一范式确保每列的原子性（最小单位）
- 第二范式要求每个表只描述一件事（所有列就要依赖于主键息息相关）
- 第三范式满足第二范式的前提下，并且每列都和主键列直接相关，列和列之间不存在关系
- 第四范式关系模式至少符合3NF，且在关系模式中没有超过一个的多值事实
  - 多值事实：某个属性有若干值，这些值由另一个属性的一个值决定
- 第五范式每一个表都有一个主键，这是原表主键的真子集
  - 范式缺点
    - 操作性能受到相应影响所以适当的数据冗余列
概要设计
- 将各个实体转换为对应的表，将属性转化为列
- 标主键（最少性，稳定性）
- 一对多（谁多就有外键）一对一（主键跟主键关系）多对多（转换为两个多对一）
详细设计
编码测试

4.4设计数据库

进行规划和结构化的过程，扩展性、可靠性

收集信息
标识实体（一行中特定数据）
实体的每个属性（Attribute）
实体之间的关系（Relationship）
- 实体对应表中的一行数据，属性及为特征，联系是两个或多个实体之间的关系
- 实体用矩形表示一般是名词和实体集，用椭圆表示一般是名词和属性，使用菱形表示一般是动词和联系集，直线用来连接属性和实体集。

一个关系的属性名的集合成为关系模式（存储的信息）

4.5 什么是E-R图又有什么用？

实体-关系图。逻辑结构，清晰展视、

4.6在数据库服务器端实现或者执行业务规则的方法有？

使用存储过程或使用约束强制来实现业务规划。

五、手动创建数据库

5.1数据库由三部分组成

主要数据文件 .mdf
次要数据文件 .ndf
日志文件 .ldf

5.2数据文件的具体参数

　　--name 逻辑名
　　--filename 物理文件名称地址
　　--size 大小
　　--maxsize 增长的最大值
　　--filerowth 增长率

5.3代码操作

use master
go


if exists(select * from sysdatabases where name='YiEr')  //存在就删除
drop database YiEr

create database YiEr
on primary
(
   --数据文件的具体描述
   name='YiEr_data', --主数据文件的逻辑名称
   filename='D:S2优化数据库第二章Yi_data.mdf', --主数据文件物理名称
   size=10mb,  --主数据文件的初始大小
   maxsize=100mb, --主数据文件增长的最大值
   filegrowth=20%  --主数据文件的增长率
)
log on 
(
  --日志文件的具体描述
  name='YiEr_log',
  filename='D:S2优化数据库第二章Yi_data.ldf',
  size=3mb,
  maxsize=20mb,
  filegrowth=1mb
)
go

--创建科目表
create table subject
(
   --字段 类型 不允许为空 主键  标识列自增一从1开始
   subjectNo int not null primary key identity(1,1),
   subjectname nvarchar(50),
   classhour int,
   gradeid int
)
go

创建数据库代码

5.4注意:

默认情况下数据文件和日志文件都放在一个目录下，为了提高安全性和存储速度分别放置不同的驱动器上,设置不定时增长会降低性能，增长至较小也会影响数据库性能，也不能过大，不然浪费空间。
在应用程序测试、维护期间，切忌不采取任何措施就执行drop语句删除数据库中的数据，会人为的造成破坏，给客户带来不必要的重大损失，正确方法与数据库管理员联系，进行备份并确认，在执行删除操作。

5.5约束

约束的类型
  --主键约束 primary key constraint (pk_)
  --非空约束 not null
  --唯一约束 uique constraint(uq_)
  --检查约束 check constraint(ck_)
  --默认约束 default constraint(df_)
  --外键约束 foregin key constraint(fk_)

//添加约束
alter table 
           add constraint  约束类型
//删除约束
alter table 表名 
          drop constraint 约束名

//示例：
--增加学生约束
alter table student
  add constraint pk_stdentno primary key(stdentno),--主键
  constraint uq_shenfen unique(shenfen),--唯一
  constraint df_address default('地址不详') for address,--默认
  constraint ck_bront check(bront>='1980-01-01'),--检查
  constraint fk_gradeid foreign key (gradeid) references grade(gradeid)--外键约束

约束代码

六、变量

6.1 哪些方法可以提高性能

　　少用联表联查、可以用子查询代替、少用select *

6.2 局部变量

　　性能好。注意：名称必须有@作为前轴

语法：
声明存放东西的变量
         declare @name varchar(8) 文本类型给长度
变量赋值方法有两种：
    ①set @变量名 = 值 
    特点 : 侧重点-以知的值 （不支持多变量赋值，当表达式返回多值会报错，表达式未返回值时会被赋null）
    ②select @变量名 = 值 
    特点 : 侧重点-查出的值（支持多变量赋值，当表达式返回多值时会返回那最后一条，表达式未返回值时会保留原来的值）

示例：
declare @stname nvarchar(10)
set @stname='洛飞'
select StudentNo,StudentName,borndate from Student where StudentName=@stname
declare @stno int
select @stno=studentno from Student where StudentName=@stname
select StudentNo,StudentName,borndate from Student where StudentNo=@stno+1 or StudentNo=@stno-1

6.3 全局变量

　　全局变量不能声明有两个@@符号作为前轴

@@error（判断上一句是否出错如果没错则返回0否则是大于0的整数）
@@identity 默认是空，最后一次插入的标识值
@@rowcount 受上一个SQL语句影响的行数
@@servername 服务器名称

输出语法：
     print 局部变量或'字符串'
     select 局部变量 as 自定义列名

示例：
print '服务器的名称'+@@servername
print 'SQL Sever的版本'+@@version
select @@SERVERNAME as '服务器名称'
select @@version as 'SQL Sever的版本'

6.4 类型转换

　　在sql中，不会发生自动类型转换，需要CONVERT(nvarcahr(20),11),还有第三个参数进行转换 CAST没前面性能好

示例：
cast (表达式 as 数据类型)
cast(@studentno as nvarchar(32))

-------------------------------

insert into Student(StudentName)
values('武松')
print '当前错误号'+convert(varchar(5),@@error)

update Student set BornDate='1970-7-8' where StudentNo=10011
print '当前错误号'+convert(varchar(5),@@error)

6.5 逻辑控制语句

顺序结果控制语句：begin-end（相当于一个大括号，表示开始和结束）
分支结构控制语句：if-else 、case-end
循环结构控制语句：where

事列：
  一、最近一次考试不及格的学生，如果有加2分，高于95的不再加分

　　declare @subjectid int ,@subjectname nvarchar(10),@jinriqi datetime,@count int
　　set @subjectname='走进java编程世界'
　　select @subjectid=subjectid from [Subject] where SubjectName=@subjectname
　　select @jinriqi=max(ExamDate) from Result where SubjectId=@subjectid
　　select @count=COUNT(*) from Result where StudentResult<60 and ExamDate=@jinriqi and SubjectId=@subjectid

while(@count>0)　　　　
begin
      update Result set StudentResult+=2 where SubjectId=@subjectid and ExamDate=@jinriqi and StudentResult<95
      --检查循环是否能退出
      select @count=COUNT(*) from Result where StudentResult<60 and ExamDate=@jinriqi and SubjectId=@subjectid
end
-----
while(1=1)
begin
      update Result set StudentResult+=2 where SubjectId=@subjectid and ExamDate=@jinriqi and StudentResult<95
      if(@count>0)
      break
      select @count=COUNT(*) from Result where StudentResult<60 and ExamDate=@jinriqi and SubjectId=@subjectid
end


二、查询学号是's1101001'的学生'走进java编程世界'课程最近一次考试的成绩，并输出学会姓名和考试的对应信息，成绩大于85显示优秀，大于70显示良好，大于60显示中等，否则显示差

　　declare @riqi datetime,@subid int
　　select @subid=subjectid from Subject where SubjectName='走进java编程世界'
　　select @riqi=MAX(ExamDate) from Result where StudentNo='s1101001' and SubjectId=@subid

select 学号=studentno,成绩=StudentResult,等级=case 
       when studentresult>=85 then'优秀'
       when StudentResult between 70 and 85 then '良好'
       when StudentResult between  69 and 70 then '中等'
       else  '差'
     end
from Result
where SubjectId=@subid and @riqi=ExamDate

三、统计并显示java课程最近一次考试平均分，如果大于70则显示成绩优秀，并显示前三名考试信息，小于70成绩较差显示后三名

　　declare @subjectid1 int ,@subjectname1 nvarchar(10),@jinriqi1 datetime,@chengji int
　　set @subjectname1='走进java编程世界'
　　select @subjectid1=subjectid from [Subject] where SubjectName=@subjectname1
　　select @jinriqi1=max(ExamDate) from Result where SubjectId=@subjectid1
　　select @chengji=avg(studentresult) from Result where ExamDate=@jinriqi1 and SubjectId=@subjectid1

if(@fen>70)
 begin
     print'考试成绩优秀前三名：'  
     select * from Result where ExamDate=@riqi and SubjectId=@subid  order by StudentResult desc
 end
   else
 begin
     print'考试成绩优秀后三名：'
     select * from Result where ExamDate=@riqi and SubjectId=@subid  order by StudentResult
 end

6.6批处理

GO指令（标志着批处理的结束）是一条或多条语句的集合，编译成一个可执行的单元，称为执行计划，从而提高执行效率

好处：能够简化数据库的管理

注意：如果有多条语句需要与begin-end结合使用，表示一个完整的语句块

6.7子查询

方法一：
子查询
    简洁
语法：
    select * from where 列1>(子查询)
  注意：
     将子查询和比较运算符联合使用，必须保证返回的值不能多于一个
  解决方法
    '=' 改为 in  （not in 则相反）
检查数据是否存在
    if exists  （非空则true 反则false）(NOT exists  )

方法二：
语法：
    select (子查询) from 表名  
  注意：
    结果为单行行列
语法：
    select * from（子查询）as 表的别名
   注意：
    必须指定别名，还可以给列重命名


示例：
--查询比张三年龄小的学生
----变量
    declare @nianli datetime
    select @nianli=borndate from Student where StudentName='张三'
    select studentno,studentname,borndate from Student where BornDate>@nianli
----子查询
select StudentNo 学号 ,StudentName 姓名,BornDate 日期 from Student where BornDate>(select BornDate from Student where StudentName='张三')

--查询参加最近一次走进Java编程世界考试成绩的学生的最高分最低分
 select max(studentresult),min(studentresult) from Result 
where ExamDate=(select max(ExamDate) from Result
where SubjectId=(select subjectid from Subject where SubjectName='走进Java编程世界') )

--查询张三的出租房屋信息
--连表查询
select dname 区县,sname 街道,htname 户类型,price 租金,topic 标题,contents 描述,htime 日期,copy 备注 from hos_house as h
 inner join hos_district as d on h.HMID=d.DID
 inner join hos_type as t on h.HTID=t.HTID
 inner join hos_street as s on s.SID=h.SID
 inner join sys_user as u on u.UID=h.UID
 where UNAME='张三'
 --子查询
 select  区县=(select dname from hos_district where DID=h.HMID),
 街道=(select sname from hos_street where SID=h.SID),
 户类型=(select htname  from hos_type where HTID=h.HTID),price 租金,topic 标题,contents 描述,htime 日期,copy 备注
  from hos_house as h
  where UID=(select uid from sys_user where UNAME='张三')
--为户型和房屋所在的区县和街道，至少有两个街道都有出租房屋的区县制作出租房清单
select count(*)街道数量,d.DNAME 县区 from hos_street as s 
inner join hos_district as d on s.SDID=d.DID
where SID in (select sid from hos_house)
group by DNAME having COUNT(*)>=2


select 户型=(select HTNAME from hos_type where hos_type.HTID=hos_house.HTID),
姓名=(select uname from sys_user where sys_user.UID=hos_house.UID),
县区=(select dname from hos_district where hos_district.DID=(select SDID from hos_street where hos_street.SID=hos_house.SID)),
街道=(select sname from hos_street where hos_street.SID=hos_house.SID)
from hos_house
where SID in
(select sid from hos_street where SDID in 
    (
    select DID from hos_district as d
    inner join (select sdid from hos_street where  sid in (select sid from hos_house)) as s on s.SDID=d.DID
    group by DID
    having COUNT(*) >=2
    )
)
order by 县区


--查询每个季度出租房屋的数量
select DATEPART(qq,htime) as 季度, ' 合计'as 县区, ''as 街道,' 'as 户型,count(*) as 房屋数量 from hos_house group by datepart(qq,htime)
union
--查询每个季度每个区县出租屋的数量
select datepart(qq,htime),dname,街道=' 小计','' as 户型,COUNT(*)from hos_house as h
inner join hos_street as s on s.SID=h.SID
inner join hos_district as d on d.DID=s.SDID
group by DATEPART(qq,htime),DNAME
union
--查询每个季度每个区县每个类型出租房数量
select datepart(qq,htime),dname,sname,htname,COUNT(*)from hos_house as h
inner join hos_street as s on s.SID=h.SID
inner join hos_district as d on d.DID=s.SDID
inner join hos_type as t on t.HTID=h.HTID
group by DATEPART(qq,htime),DNAME,SNAME,HTNAME




drop table bank
create table bank
(
    name nvarchar(10) not null,
    Mymoney money not null check(mymoney>=1)
)
go
insert into bank(name,Mymoney)
values('张三',1000)
insert into bank(name,Mymoney)
values('李四',1)
---
print '转账前'
select * from bank
declare @sumerror int=0
begin transaction--起点

    update bank set Mymoney-=5000 where name='张三'
set @sumerror+=@@error
    update bank set Mymoney+=5000 where name='李四'
set @sumerror+=@@error
if(@sumerror=0)
    begin
        commit transaction--提交
        print '转账成功'
    end
else
    begin
        rollback transaction--回滚
        print '转账失败'
    end
print '转账后'
select * from bank


declare @error int
begin transaction
--转学生
select * into stu from Student where Gradeid in(select GradeId from Grade where GradeName='Y2')
set @error+=@@error
--转考试
select * into res from Result where SubjectId in(select SubjectId from Grade where Gradename='Y2' )
set @error+=@@error
    delete from Result where studentno in(select StudentNo from res)
set @error+=@@error
    delete from Student where studentno in(select StudentNo from stu)
set @error+=@@error

if(@error=0)
    begin
        commit transaction
        print '成功'
    end
else
    begin
        rollback transaction
        print '失败'
    end

案例

6.8分页

必须知道每页的数量和当前的页码

1.临时表

加#号就是临时表

将原始数据重新标号（放到临时表中）进行查询

起始位置 = 每页数量*（页码-1）

终止位置 = 每页数量 * 页码

使用临时表#表示声明添加一个标识列identity（int,1,1）

每次查询完要将新表删除

而临时表会在下次重启服务器的时候自动删除

2.子查询

top (每页的数量的变量) 必须加括号

查询第n页的数据

select top @数量 from 表名 where 主键 not in (select top ((页码-1)*数量) 主键 from 表明)

七、事务、视图、索引

7.1事务

　　事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作。多个操作作为一个整体向系统提交，要么都执行、要么都不执行。事务是一个不可分割的工作逻辑单元。

四个属性：　

原子性
- 事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行
一致性
- 当事务完成时，数据必须处于一致状态
隔离性
- 并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
永久性
- 事务完成后,它对数据库的修改被永久保持

//语法：
//开始事务
begin transaction

//提交事务
commit transaction

//回滚
rollback transaction

//一旦事务提交或回滚，则事务结束
//判断某条语句是否出错
使用全局变量@@error

//错误累计
SET @errorSum=@errorSum+@@ERROR


//事务嵌套
//显示事务
用begin transaction明确指定事务的开始

//隐形事务
通过设置set implicit_transactions on语句，将隐性事务模式设置为打开
其后的T-SQL语句自动启动一个新事务
提交或回滚一个事务后，下一个 T-SQL 语句又将启动一个新事务

//自动提交事务
SQL Server 的默认模式
每条单独的 T-SQL 语句视为一个事务

BEGIN TRANSACTION 
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT 
SET @errorSum=0                                             --初始化为0，即无错误
/*--转账：张三的账户减少1000元，李四的账户增加1000元*/
UPDATE bank SET currentMoney = currentMoney - 1000
   WHERE customerName = '张三'
SET @errorSum = @errorSum + @@ERROR --累计是否有错误
UPDATE bank SET currentMoney = currentMoney + 1000
   WHERE customerName = '李四'
SET @errorSum = @errorSum + @@ERROR


IF @errorSum<>0  --如果SQL语句执行出错
  BEGIN
    PRINT '交易失败，回滚事务'
    ROLLBACK TRANSACTION 
  END  
ELSE
  BEGIN
    PRINT '交易成功，提交事务，写入硬盘，永久的保存'
    COMMIT TRANSACTION   
  END
GO
PRINT '查看转账事务后的余额'
SELECT * FROM bank  
GO

事务案例

7.2视图

　　视图是一张虚拟表：

- 表示保存综合数据
- 结构和数据是建立在对表的查询基础上
- 数据存放在视图所引用的原始表中
- 一个表可以创建多个视图

　　用处：

- 筛选表中的行
- 防止未经允许用户访问敏感数据
- 降低数据库的复杂程度
- 将多个物理数据库抽象为一个逻辑数据库

视图语法：
创建视图语法：

  create view vw_name
  as
    <select 语句>
　go

 删除视图语法：
  drop view vw——name

示例：
  if exists(select  * from sysobjects where name='vw_result')     --检测视图是否存在，视图记录在系统表sysobjects中
    drop view vw_result    --如果有则删除
 go

create view vw_result    --创建视图
as
<select 语句>
go

//查看视图结果
select * from vw_result

注意：ORDER BY 子句，除非在 SELECT 语句的选择列表中也有一个 TOP 子句、INTO 关键字、引用临时表或表变量

7.3索引

　　根据索引键查找数据

- 通过使用索引能大大提高数据库的检索速度，改善数据库性能
- 加强行的唯一性
- 需要更多存储空间，需要更长时间处理

　　类型：

- 唯一索引
  - 不允许有相同的索引值
- 主键索引
  - 唯一索引的特殊类型
- 聚集索引
  - 表中各行的物理顺序与键值的逻辑顺序相同
- 非聚集索引
  - 非聚集索引指定表的逻辑顺序

　　使用场景：

- 频繁搜索的列
- 经常用作查询选择的列
- 经常排序、分组的列
- 经常用作连接的列（主键/外键）

　　不推荐：

- 仅包含几个不同值的列
- 表中仅包含几行

　　注意：索引应该尽量小，在字节数小的列上建立索引、避免在ORDER BY子句中使用表达式、根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

--创建索引语法：
 create 索引类型 index 索引名
 on 表名（列名）

  示例：
   --检测是否存在该索引（索引放在系统表sysindexes中）
if exists(select name from sysindexes where name='ix_studentname' )
  drop index Student.ix_studentname          --删除索引（一定要在表名后显示该索引）

    --创建非聚集索引：填充因子为30%
 create nonclustered index ix_studentname
    on Student(StudentName)
          with fillfactor=30   --填充因子30%
go

--删除索引语法：
drop index table_name.index_name

八、存储

8.1 概念

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

8.2用处

只在创造时编译，提高执行速度。
可复杂操作。
可重复
安全性高

8.3 存储过程

　　存储过程可通过调用执行，还允许用户声明变量，逻辑控制语句和数据操作语句，可以通过接受参数、输出参数、返回单个或多个返回值

存储过程和视图的区别：跟视图相比可有无返回值，更加的灵活，所以更推荐使用视图

 --系统存储过程（提供管理数据库和更新表机制，并允许快捷方式）
    --以"sp_"开头 
        sp_tables (返回当前环境下可查询的表或视图的信息)
        sp_columns(返回某个表或视图的列信息，包括列的数据类型和长度)
    语法：
        execute  存储过程名称 [参数值]
   --扩展存储过程（允许使用其他编程语言，提供外部程序接口，以DLL形式单独存在）
    --以"xp_"开头 
        xp_cmdshell (使用存储过程创建目录)
    语法：
        exec xp_cmdshell Dos命令 [no_output] (最后一个作为可选参数，执行DOS命令是否返回输出)
   --用户组自定义存储过程
     三部分：
        --输入参数和输出参数
        --在存储过程执行SQL语句
        --存储过程的返回值
    --创建不带参语法：
        create procedure 存储过程名
         as
               SQL语句
         go
    删除语法：drop procedure 存储过程名 go
    执行语法：exec 存储过程名称 go

    --创建带餐语法:
        create procedure 存储过程名
             [@参数 1=] 参数值 1 [output] [defalut]（分别表示输出、默认）
         as
               SQL语句
         go
       注意：
        顺序要一致、前面加@后面就必须加@、指定参数不用顺序一致、输出参数就是方法的返回值

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'
（注意：加密存储过程前应该备份原始存储过程，且加密应该在部署到生产环境前完成。）

语法

8.4 raiserror干啥玩意儿的？
　用于返回用户定义的错误信息，因为print打印错误信息在C#里获取不到，这玩意儿可以
　　语法：
　　 raiserror({msg_id | msg_str },{severity,state} [with option [ .....n] ] ) （分别表示消息、级别、状态）

九、触发器

9.1 概念

　　触发器是一种特殊类型的存储过程，一种高级约束，主要是通过事件进行进行触发而被执行的,自动执行表的增删改过程，数据更新时将会自动调用

9.2 用处

主要保存因用户操作而被影响的原数据或新数据值
处理复杂的逻辑关系
强化约束实现复杂的约束关系
跟踪变化可以侦测数据库内的操作
触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。当更改外键且新值与主键不匹配时，此类触发器就可能发生作用

9.3 需要

CHECK约束只能根据逻辑表达式或同一表中的另一列来验证列值。如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器。
约束只能通过标准的系统错误信息传递错误信息。如果应用程序要求使用自定义信息和较为复杂的错误处理，则必须使用触发器。

9.4 分类

insert 触发器：插入数据时触发（用于确保插入数据有效）
update 触发器：更新时触发（多用于约束）
delete 触发器：删除时触发（多用于备份）
1. ABS函数,可以直接获取到绝对值函数

9.5触发器有几个表，干什么的？

　　①插入表(inserted)（用于存储INSERT和UPDATE语句所影响的行的副本）
　　②删除表(delete)（用于存储DELETE和UPDATE语句所影响的行的副本）
注意：这两表都是逻辑表，并且是由系统管理，动态存储在内存中，因此是只读的，可以引用，当触发器完成时它们也被删除。

创建触发器时需要注意以下问题？
  --create trigger必须是批处理中的第一条语句（go），并且只能应用到一个表中。
  --只能在当前的数据库中创建，但可以引用当前数据库的外部对象。
  --在同一条create trigger语句中，可以为多种用户操作（如INSERT和UPDATE）定义相同的触发器操作。
  --当触发器触发时，将向调用应用程序返回结果。若要避免由于执行了触发器而向应用程序返回结果就要包含向用户返回结果的SELECT语句或进行变量赋值的语句的触发器需要特殊处理，这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用setnocount语句以避免返回任何结果集


触发器语法：
  --
  create trigger name --触发器的名
     on student --执行触发器的表或试图
      with encryption --加密syscomments防止触发器作为SQL Server复制的一部分发布
       for{[delete,insert,update]}--指定在哪个表或视图上执行哪些数据修改语句时将激活触发器的关键字,可以有多个
as
      --SQL语句
go

闆

主要保存因用户操作而被影响的原数据或新数据值