第一章 数据库的设计
1.1 数据库设计
数据库中创建的数据库结构的种类，以及在数据实体之间建立的复杂关系是决定数据库系统效率的重要因素。
糟糕的数据库设计表现在以下两个方面：
*效率低下
*更新和检索数据时会出现许多问题
*数据操作异常、修改复杂、数据冗余
*程序性能受到影响
良好的数据库设计表现在以下几方面：
*效率高
*便于进一步扩展
*使得应用程序的开发变得更容易
1.2 设计数据库的步骤
*需求分析阶段：分析客户的业务和数据处理需求
*概要设计阶段：绘制数据库的Ｅ－Ｒ图，用于在项目团队内部、设计人员和客户之间进行沟通，确认需求信息的正确性和完整性
*详细设计阶段：将Ｅ－Ｒ图转换成多张表，进行逻辑设计，确认主外键关系，应用数据库设计的三大范式审核。之后选择具体的数据库（ＳＱＬ Ｓｅｒｖｅｒ等）物理实现。创建完毕后开始进入代码编写阶段，开发前端应用程序。
1.3 概念设计---绘制E-R图
1.实体
实体就是指现实世界中具有区分其他事务的特征或属性并与其他实体有联系的实体
例如：酒店管理系统中的客房（如1008客房，1018客房等），客人（如张三，李四，王五等）等。
实体一般是名词，它对应表中的一行数据，严格地说，实体是指表中一行特定数据，但我们在开发时，也常常把整个表称为一个实体
2. 属性
属性可以理解为实体的特征，属性对应表中的列
3.联系
联系是两个或多个实体之间的关联关系
实体用矩形表示，属性用椭圆表示，联系用菱形表示
4. 映射基数
映射基数
一对一 １：１ 一对多 １：Ｎ
多对一 Ｎ：１ 多对多 Ｍ：Ｎ
1.4 数据规范化
不规范的设计：
信息重复、更新异常、插入异常（无法表示某些信息）、删除异常（删除有用信息）
规范设计 在数据库设计时，有一些专门的规则，成为数据库的设计范式
第一范式
确保每列的原子性，如果每列或者每个属性值都是不可再分的最小数据单元，则满足第一范式
例：学生的电话和住址
作为一个列可以再分为电话和住址两列 故该表不满足第一范式
第二范式
确保表中的每列都和主键相关。如果满足第一范式，并且除了主键以外的其他列都依赖于该主键，则满足第二范式
例：学生表中有一个考试日期列
而考试日期与学生编号并无关联 故不满足第二范式
第三范式
是在第二范式的基础上更近一层，确保每列都和主键列直接相关，而不是间接相关。如果一个关系满足第二范式，并且除主键外的其他列都只能依赖于主键列，列和列之间不 存在相互依赖关系，则满第三范式
例：科目表包含了科目编号，
名称和对应年级 学生表包含了学号…………学生学习的科目列
科目和学号有关联 但是科目和年级也有关联 所以不符合第三范式
注意：
数据库满足的范式越高，其数据访问性能越低
通常按照数据库的第三设计范式审核数据库模型图中试图的结构
分析需求收集信息→绘制ＥＲ图→交流沟通→绘制数据库模型图
第二章 数据库的实现
1.1 T-SQL语句（增，删，改，查）
1.增(添加语句)
insert into 表名(列名1,列名2,列名3……) values (值1,值2,值3……)
2.删(删除数据)
delete from 表名 where(条件)
3.改(修改数据)
update 表名 set 列1=值1,列2=值2,……where(条件)
4.查(查询数据)
select 列1,列2,……from 表名 where (条件) order by (列名)
2.1 使用SQL语句创建和删除数据库
数据库文件由以下3部分组成
1.主数据文件：*.mdf
2.次要数据文件：*.ndf
3.日志文件：*.ldf
每个数据库至少要包含两个文件：一个数据文件和一个日志文件，数据文件中包含了数据库的数据和对象，如表，视图和索引等；
日志文件中包含了用于恢复数据库所需的信息。创建数据库时，次要文件可选。一个数据库可以有多个数据库文件和多个日志文件。
2.2 创建，删除数据库
语法：
use master --设置当前数据库为master,以便访问 sysdatabases 表
go
if exists (select * from sysdatabases where name='数据库名') --查询是否有该数据库
drop database 数据库名 --如果有则删除
create database 数据库名 --创建数据库
on primary
(
--主数据文件的具体描述
name='数据库名_data'， --逻辑文件名
filename='D:\project\数据库名_data.mdf'， --物理文件名
size=5MB , --大小
maxsize=100MB, --最大容量
filegrowth=15% --增长量
)
log on
(
--日志文件的具体描述
name='数据库名_log', --日志文件名
filename='D:\project\数据库名_data.log' , --日志文件地址
size=2MB, --大小
filegrowth=1MB --日志文件增长量
)
go
2.2 创建，删除数据库
use 数据库名 --设置当前数据库为已创建的数据库,以便在该数据库中建表
go
if exists (select * from sysobjects where name='表名') --查询在该数据库中是否有该表
drop table 表名 --如果有则删除
create table 表名 --创建数据库
（
列名 数据类型 列的特征
StudentNo int identity(1,1) not null, --学号，整数型，自增（从1开始，依次加1）非空（必填）
Phone nvarchar(50) null --联系电话，字符串型，可以为空
）
go
2.3 使用SQL语句创建和删除约束
--添加主键约束，要求主键列数据唯一，并且不允许为空(将studentNo作为主键)
alter table student
add constraint PK_studentNo primary key (studentNo)
--添加唯一约束，要求该列的值必须唯一，允许为空(身份证号唯一,因为每个人的身份证号全国唯一)
alter table student
add constraint UQ_identityCard unique (identityCard)
--添加默认约束(如果不填定地址,则默认为”地址不详”)
alter table student
add constraint DF_address defatult (‘地址不详’) for address
--添加检查约束(要求出生日期在1980年1月1日之后)
alter table student
add constraint CK_bornDate check (bornDate>=‘1980-01-01’)
--添加外键约束(主表student和从表result建立关系关联列为studentNo)
alter table result
add constraint FK_studentNo foreign key (studentNo) references student(studentNo) go
--删除约束
alter table 表名
drop constraint 约束名
例，删除Student表中地址列默认约束的语句如下
alter table student
drop constraint DF_Address
第三章 SQL编程
1.1 局部变量
T-SQL 中，局部变量的名称必须以标记@作为前缀。
语法，
set @变量名 变量类型
declare @变量名 变量类型
例，
set @num int --声明一个存放学号的变量
declare @name varchar(8) --声明存放姓名变量名，最多可以存储8个字符
示例：
--查找李文才的信息
declare @name varchar(8) --学生姓名
set @name='李文才' --使用set赋值
select StudentNo,StudentName,BornDate,Address from Student
where StudentName=@name
--查找与李文才学号相邻的学生信息
declare @StudentNo int --学号
--使用select 赋值
select @StudentNo=StudentNo from Student where StudentName=@name
select StudentNo,StudentName,BornDate,Address from Student
where (StudentNo=@StudentNo+1) or (StudentNo=@StudentNo-1)
go
从以上示例可以看出，局部变量可用于在上下语句中传递数据
set和select的区别
1. set一次只能为一个变量赋值,而select能同时为多个变量赋值
2. set只能赋一个固定的值,而select能动态的赋值(值来自于表内)
select的作用
3. 当表达式返回多个值时，set 将会报错，select 将返回的最后一个值赋给变量
4. 当表达式返回值时，set 变量被赋值为NULL，select 变量保持原值
示例：
declare @addr nvarchar(100),@name nvarchar(100) --声明多个局部变量
set @addr =' ', @name='张三' --发生语法错误
select @addr='北京', @name='张三' --为两个局部变量@addr和@name赋值
set @addr=(select Address from Student) --发生语法错误
select @addr=Address from Student --最后一条记录的Adress列值
set @addr=(select Address from Student where 1<0)
--查询无结果时，@addr被赋值为Null
select @addr='北京'
select @addr=Address from Student where 1<0
--查询无结果时，@addr保持原值
1.2 全局变量
SQL Server 中所有的全局变量都用两个@@符号作为前缀
@@error --最后一个T-SQl错误的错误号
@@identity --最后一次插入的标识值
@@servername --本地服务器名称
@@version --SQL Server的版本信息
1.3 输出语句
语法：
print 局部变量或字符串
select 局部变量 as 自定义列名
使用print 语句要求以单个变量或字符串表达式作为参数，而“+”运算符作为连接两个字符串的链接符，
要求“+”运算符两侧的操作数的数据类型必须一致
print '当前错误号'+convert(varchar(5),@@error)
2.1 数据类型转换
语法：
cast (表达式 as 数据类型)
cast(@studentno as nvarchar(32))
convert(数据类型，表达式)
convert(nvarchar(32),@studentno)
两者的不同:
convert()可以转换日期格式，而cast()不可以
convert(nvarchar(32),'2016-08-01',120)
2.2 逻辑控制语句
1. if-else语句
声明变量number,并赋值,然后判断是偶数还是奇数,结果如下:
当前值为11,它是一个奇数
declare @number int
set @number=12
if(@number%2=0)
print '该数为偶数'
else
print '该数为奇数'
2. while 语句
输出九九次'我爱你'
declare @i int=1
while(@i<=99)
begin
print '第'+convert(varchar,@i)+'我爱你'
set @i+=1
end
不停的提高学生笔试成绩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
2.3 case 多分支语句
语法：
case
when 条件1 then 结果1
when 条件2 then 结果2
end
set @result=case
when @age<12 then '小学生'
when @age<17 then '初中生'
when @age<22 then '高中生'
when @age<28 then '大学生'
else '超人'
end
--输出
print @name+'是一个'+@result
第四章 高级查询
1.1 IN 和 NOT IN 子查询
1.IN 子查询
IN:确定给定的值是否与子查询或列表中的值相匹配,如果匹配则返回真，可以返回多条记录。
使用方法： 在需要子查询返回多数据时使用。
语法：
select 列表 from 表名 where 列名 in（子查询）
2.NOT IN子查询
NOT IN:确定给定的值是否与子查询或列表中的值相匹配,如果不匹配则反回真。
使用方法： 在需要子查询返回多数据时使用。
语法：
select 列表 from 表名 where 列名 not in（子查询）
3.EXISTS 子查询
EXISTS: exists 关键字能够检测数据是否存在，如果存在返回真。
语法 if exists(子查询) 语句
示例： 查询本校学生有没有叫张三的
if exists( select * from student where studentname='张三' )
begin
print '有叫张三的'
end
4.NOT EXISTS 子查询
NOT EXISTS: exists 关键字能够检测数据是否存在，如果不存在返回真
语法
if not exists(子查询) 语句 示例： 查询本校学生是不是没有叫张三的
if not exists( select * from student where studentname='张三' )
begin
print '没有叫张三的'
end
2.1 相关子查询
1：非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
2：相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。数据是否存在，如果不存在返回真
示例：检索出在work表中每一个部门的最高基本工资的职工资料
select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称)
第六章 事务，视图和索引
1.事务
事务时作为单个逻辑工作单元执行的一系列操作，一个逻辑单元必须有4个属性
即：
1.原子性
2.一致性
3.隔离性
4.持久性
执行事务
begin transaction --开始事务
commit transaction --提交事务
rollback transaction --回滚事务
2.1 视图
创建视图
语法：
create view vw_name
as
<select 语句>
删除视图
语法：
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 --查看视图结果
使用视图注意事项：
每个视图可以使用多个表
与查询相似，一个视图可以嵌套另一个视图，但最好不要超过3层
视图定义中的select 语句不能包括以下内容
*order by 子句，除非在select 语句的选择列表中也有一个top子句
*into 关键字
*引用临时表或表变量
2. 索引
索引分类：
1.唯一索引 (unique) --不允许具有相同的索引值
2.主键索引 --要求主键中的每一个值是非空，唯一的，当在查询总使用主键索引时，它还允许快速访问数据
3.聚集索引 (clustered) --表中各行的物理顺序与键值的逻辑（索引）顺序相同
4.非聚集索引 (nonclustered) --一个表只能创建一个聚集索引，但可以有多个非聚集索引，若设置某列为主键，则该列默认为聚集索引
--填充因子（fillfactor）
创建索引
语法：
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
第七章 存储过程
1.1 存储过程的概念
存储过程是在数据库管理系统保存的，预先编译的，能实现某种功能的SQL程序，
它是数据库应用中运用比较广泛的一种数据对象。
1.2 为什么需要存储过程？
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权
1.3 存储过程的优点：
1.模块化程序设计
2.执行速度块，效率高
3.减少网络流量
4.具有良好的安全性
2.1 系统存储过程
SQL_SERVER 提供系统存储过程，它们是一组预编译的T-SQL语句，系统存储过程提供了管理数据库和更新表的机制，
并充当从系统表中检索信息的快捷方式。
-----------常用的系统存储过程-------------
exec sp_databases
不带参数,列出服务器 上的所有数据库信息,包括数据库名称和数据库大小
exec sp_helpdb Result
报告有关指定数据库或所有数据库的信息
exec sp_renamedb '旧数据库名称','新数据库名称'
更改数据库名称
exec sp_tables Student
返回当前环境下可查询的表或视图的信息
exec sp_columns Result
返回某个表或视图的列信息,包括列的数据类型和长度等
exec sp_help student
查看某个数据库对象的信息,如列名,主键,约束,外键,索引等
exec sp_helpconstraint Student
查看某个表的约束
exec sp_helpindex
查看某个表的索引
exec sp_stored_procedures Student
显示存储过程的列表
exec sp_password
添加或修改登录账户的密码
exec sp_helptext
显示默认值,未加密的存储过程,用户定义的存储过程,触发器或视图的实际文本
3.1 用户自定义的存储过程
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'
（注意：加密存储过程前应该备份原始存储过程，且加密应该在部署到生产环境前完成。）