第1章 数据库的设计< xmlnamespace prefix ="o" ns ="urn:schemas-microsoft-com:office:office" />
1. 设计数据库时的步骤：
1.需求分析。
2.概要设计。
3.详细设计。
4.代码编写。
5.运行测试。
6.打包发行。
设计数据库时要符合三大范式：
1.不允许有套表出现。即：确保每列的原子性。例如：“地址”可以分为国家、省、市、区等。有的程序把“姓名”分为“姓”和“名”。
2.每一行只能有唯一的单位来决定。即：除了主键外的其他列都依赖于该键。
3.确保每列都和主键列直接相关，而不是间接相关。
第2章 数据库的实现
1. 使用SQL语句创建和删除数据库
数据库文件由3部分组成：
（1）主数据文件：*.mdf。
（2）次数据文件：*.ndf。
（3）日志文件：*.ldf。
2.创建数据库
create database [数据库名] //创建数据库
on [primary] //primary可以省略，代表指定的主文件组中的文件<此文件为数据文件>
（
name=‘逻辑文件名', --主数据文件的逻辑名称 //后的单引号可以去掉 //有黄色地板的表示是必须有的
filename='物理文件名', --主数据文件的物理名称 //后的单引号必须有 引号内必须有盘符例如 D:\文件名.mdf
size=大小， --主数据文件的初始大小
maxsize=最大容量， --主数据文件的增长的最大值
filegrowth=增长量 --主数据文件的增长率
）
log on //指明日志文件的明确定义
(
所需要的参数同上
)
3.删除数据库
drop database 数据库名
例：
use master //设置当前数据库为master，以便访问sysdatabases表
go
if exists(select name from sysdatabases where name='stuDB')
drop database stuDB
go
create database stuDB
on
(
...
)
log on
(
...
)
go
4.创建表
create table 表名
（
字段1 数据类型列的特征，
字段2 数据类型列的特征
...
）
“列的特征”包括是否为空（null）、是否是标示列（自动编号）、是否有默认值、是否为主键等。
列属性“IDENTITY（起始值，递增量）”表示列为自动编号，也称为标识列。
4.删除表
drop table 表名
例：
use stuDB
go
if exists(select name from sysobjects where name='stuInfo')
drop table stuInfo --删除表
go
create table stuInfo --创建表
(
stuName varchar(20) not null, --学员姓名，非空（必填）
stuNo char(6) not null, --学号，非空
stuAge int not null, --年龄
stuID numeric(18,0), --身份证号，numeric(18,0)代表数字，小数位数为0
stuSeat smallint identity(1,1), --座位号，自动编号（标识列），从1开始增
stuAddress text --住址
)
go
5.删除表使用SQL语句创建和删除约束
a.主键约束（Primary Key constraint)：要求主键列数据唯一，并且不允许为空。
b.唯一约束（Unique constraint）:要求该列唯一，允许为空，但只能出现一个空值。
c.检查约束（Check Constraint）:某列取值范围限制、格式限制等，如有关年龄约束。
d.默认约束（Default Constraint）:某列的默认值，如我们的男性学员较多，性别默认为“男”。
e.外键约束（Foreign Key Constraint）：用于在两表之间建立关系，需要指定引用主表的哪一列。
6.添加约束
alter table 表名 add constraint 约束名约束类型具体的约束说明
约束名的命名规则采用“约束类型_约束字段”这样的形式。
例：
alter table stuInfo add constraint pk_stuNo primary key(stuno) --添加主键约束
alter table stuInfo add constraint uq_stuID unique(stuID) --添加唯一约束
alter table stuInfo add constraint df_stuAddress default('地址不详') from stuAddress --地址不填默认为“地址不填”
alter table stuInfo add constraint fk_stuNo foreign key(stuNo) references stuInfo(stuNo) --添加外键约束
添加约束时还可以直接在创建表时直接添加约束
添加外键约束 列名类型列的特征 foreign key 表名(主键列)
7.删除约束
Alter table 表名 drop constraint 约束名
例：
Alter table stuInfo drop constraint DF_stuAddress.
8.使用SQL语句创建登录
第一关：需要登录到SQL Server系统，即需要登录账户。
第二关：需要访问某个数据库（相当于我们的单元楼），即需要成为该数据库的用户.。
第三关：需要访问数据表中的表（相当于打开我们的房间），即需要数据库管理员DBA给自己授权，如增添、修改、删除、查询等权限。
9.创建登录账户
A. SQL身份验证：适合于非Windows平台的用户或Internet用户，需要提供账户和密码。
Exec sp_addlogin ‘账户名’,’密码’
例：
Exec sp_addlogin ‘zhangsan’,’< xmlnamespace prefix ="st1" ns ="urn:schemas-microsoft-com:office:smarttags" />1234’ ---账户名为zhangsan密码为1234
B. Windows身份验证：适合于Windows平台用户，不需要提供密码，和Windows集成验证。
Exec sp_grantlogin ‘windows域名\域账户’
例：
Exec sp_grantlogin ‘jbtraining\s26301’
10.创建数据库用户
创建数据库用户需要调用系统存储过程sp_grantdbaccess
调用语法：
Exec sp_grantdbaccess ‘登录账户’,’数据库用户’ ---“数据库用户”为可选参数，默认为
登录账户，即数据库用户默认和登录账户同名。
例：
Use useDB
Go
Exec sp_grantdbaccess ‘jbtraining\s26301’,’s26301DBUser’
Exec sp_grantdbaccess ‘zhangsan’,’zhangsanDBUser’
11.给数据库用户授权
常用的权限包括添加数据(insert)、删除数据（delete）、更新数据（update）、查看数据（select）、创建表（create table）等操作。
Grant 权限 [on 表名] to 数据库用户
例：
User stuDB
Go
Grant select, insert,update,select on stuInfo to zhangsanDBUser --stuInfo代表表名，
zhangsanDBUser代表数据库用户。
第3章 T-SQL编程
1． 使用变量
T-SQL中的变量分为局部变量和全局变量。局部变量的使用也是先声明，再赋值。而全局变量由系统定义和维护，可以直接使用，一般不自定义全局变量。
2．局部变量
局部变量的名称必须以标记@作为前缀
声明局部变量的语句如下：
declare @variable_name DateType
其中variable_name为局部变量的名称，DateType为数据类型
例：
declare @name varchar(8) --声明一个存放学员姓名的变量name，最多可以存储8个字符
declare @seat int --声明一个存放学员座位号的变量seat
局部变量的赋值有两种方法：使用set语句或select语句。
语法：
set @variable_name=value
或
select @variable_name=value
3．全局变量
SQL Server中的所有全局变量都使用两个@标志作为前缀。
全局变量
| 变 量 | 含 义 |
| @@error | 最后一个T-SQL错误的错误号 |
| @@identity | 最后一次插入的标识值 |
| @@language | 当前使用的语言的名称 |
| @@max_connections | 可以创建的同时连接的最大数目 |
| @@rowcount | 受上一个SQL语句影响的行数 |
| @@servername | 本地服务器的名称 |
| @@servicename | 该计算机上的SQL服务的名称 |
| @@timeticks | 当前计算机上每刻度的微秒数 |
| @@transcount | 当前连接打开的事务数 |
| @@version | SQL Server的版本信息 |
4．输出语句
常用的输出语句有两种：
print 局部变量或字符串
select 局部变量as 自定义列名
例：
print ‘服务器的名称：’+@@servername
select @@servername as ‘服务器名称’
5． if-else条件语句
语法：
if(条件)
语句或语句块
else
语句或语句块
同Java语言一样，else为可选。
如果有多条语句，需要使用语句块，语句块使用begin…end表示，其作用类似于Java语言的“{}”符号。
if(条件)
begin
语句1
语句2
…
end
else
….
6． while循环语句
语法：
while （条件）
语句或语句块
[break]
使用break关键字从最内层while循环中退出。
7．CASE多分支语句
语法
case
when 条件1 then 结果1
when 条件2 then 结果2
[else 其他结果]
end ---注意的是 在 case分支语句中后面要加end
case语句表示如果“条件1”成立，则执行“结果1”，其余类推。
8． 批处理语句
在SQL Server中“go”就是批处理的标志。批处理的主要好处是简化数据库的管理。
go关键字标志着批处理的结束。
第4章 高级查询
1. 简单子查询
例：
select …from 表1 where 字段1 > (子查询)
注意：将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。
在用select进行查询时where条件后尽量不要用“%”，前面加“%”则不通过索引进行查询。
可以将多表间的数据组合在一起，替换连接（join）查询。
多表连接查询都可以用子查询替换，但反过来说却不一定，有的子查询不能用表连接来替换，子查询比较灵活、方便、形式多样，适合于作为查询的筛选条件，，而表连接更适合于查看多表的数据。
2. in和not in子查询
使用in 和not in进行子查询后面的子查询可以返回多条记录。
3. exists和not exists子查询
exists可以作为where语句的子查询，但一般用于if语句的存在检测。基本语法如下：
if exists(子查询)
语句
如果子查询的结果非空，则exists(子查询)将返回真（true），否则返回假（false）。
第5章 事务、索引和视图
1. 事务
事务（transaction）是单个的工作单元。如果某个事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。
Transact-SQL使用下列语句来管理事务。
开始事务：begin transaction。
提交事务：commit transaction。
回滚（撤销）事务：rollback transaction。
事务的分类有以下3种:
(1)显式事务：用begin transaction明确指定事务的开始
（2）隐式事务 （3）自动提交事务
例：(银行转账系统)
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 = @error --累计是否有错误
print ‘查看转账事务过程中的余额’
if @errorSum <>0
begin
print ’交易失败，回滚事务’
rollback transaction
end
else
begin
print ‘交易成功，提交事务，写入硬盘，永久保存’
commit transaction
end
2. 索引
索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排列次序排列这些指针。 索引相当于字典中按拼音或笔画排序的目录。
索引可以分为3类：
（1）唯一索引：不允许两行具有相同的索引值。（一般情况下大多数据库不允许创建唯一索引）
创建了唯一约束，将自动创建唯一索引，尽管唯一索引有助于找到信息，但为了获得最佳性能，最好使用主键约束。
（2）主键约束：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。有利于快速访问数据。
（3）聚集索引：在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。
聚集索引比非聚集索引有更快的数据访问速度。
在SQL Server中，一个表只能创建一个聚集索引，但可以有多个非聚集索引，设置某列为主键，该列就默认为聚集索引。
unique（唯一索引）、、clustered（聚集索引）
使用T-SQL语句创建索引
语法：
create [unique] [clustered | nonclustered] index index_name
on table_name (column_name[,coumn_name]…)
[with
fillfactor=x
]
其中：
unique指定唯一索引，可选。
clustered、nonclustered指定是聚集索引还是非聚集索引，可选。。
fillfactor表示填充因子，指定一个0~100的值，该值指示索引页填满的空间所占的百分比。
例：
/*笔试列创建非聚集索引*/
create nonclustered index IX_stuMarks_writtenExam
on stuMarks(writtenExam)
with fillfactor = 30
运用索引进行查询
select * from stuMarks (index=IX_stuMarks_writtenExam) where writtenExam between 1 and 33
使用索引可以加快数据检索速度。
创建索引要用在（1）该列频繁搜索（2）该列用于对数据进行排序
3. 视图
视图是保存在数据库中的select查询。是一张虚拟的表。
对查询执行的大多数操作也可在视图上进行。
使用视图的原因有2：
（1）出于安全考虑。用户不必看到整个数据库结构，而隐藏部分数据；
（2）符合用户日常业务逻辑，使他们对数据更容易理解。
使用T-SQL语句创建视图
语法如下：
create view view_name
as
<select语句>
例：
if exists(select name from sysobjects where name=’view_stuInfo_stuMarks’)
drop view view_stuInfo_stuMarks
go
create view view_stuInfo_stuMarks
as
select 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,机试成绩=labExam, 平均分=(writtenExam+labExam)/2 from stuInfo
left join stuMarks on stuInfo.stuNo = stuMarks.stuNo
go
/*使用视图：视图是一个虚拟表，可以像物理表一样打开*/
select * from view_stuInfo_stuMarks
第6章 存储过程
1. 存储过程
存储过程类似于java语言中的方法。
存储过程可包含逻辑控制语句和数据操作语句，它可以接收参数、输出参数、返回单个或多个结果集以及返回值。
SQL Server中的存储过程有：
（1）接收输入参数，并向调用过程或语句返回值
（2）包含在数据库中执行操作或调用其他存储过程的编程语句。
（3）向调用过程返回状态值，指示执行过程是否成功。
存储过程的优点
（1）允许模块化程序设计
只需创建一次存储过程并将其存储在数据库中，以后即可在程序中调用该过程任意次。
（2）允许更快的执行
（3）减少网络流量
（4）可作为安全机制使用
2. 常用的系统存储过程
所有系统存储过程的名称都以“sp_”开头，并存放在master数据库中。
常用的系统存储过程
| 系统存储过程 | 说 明 |
| sp_databases | 列出服务器上的所有数据库 |
| sp_helpdb | 报告有关指定数据库或所有数据库的信息 |
| sp_renamedb | 更改数据库的名称 |
| sp_tables | 返回当前环境下可查询的对象的列表 |
| sp_columns | 返回某个表列的信息 |
| sp_help | 查看某个表的所有信息 |
| sp_helpconstraint | 查看某个表的约束 |
| sp_helpindex | 查看某个表的索引 |
| sp_stored_procedures | 列出当前环境中的所有存储过程 |
| sp_password | 添加或修改登录账户的密码 |
| sp_helptext | 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本 |
还有一个常用的扩展存储过程：xp_cmdshell。它可以完成DOS命令下的一些操作，
语法如下：
exec xp_cmdshell DOS命令 [no_output]
3. 创建不带参数的存储过程
使用T-SQL语句创建存储过程的语法：
create proc[edure] 存储过程名
[ {@参数1 数据类型} [=默认值][output],
…..,
{@参数n 数据类型} [=默认值][output]
]
as
sql语句
例：
if exists(select * from sysobjects where name=’proc_stu’)
drop procedure proc_stu
go
create procedure proc_stu
as
declare @writtenAvg float,@labAvg float --笔试平均风和机试平均分变量
select @writtenAvg=avg(writtenExam), @labAvg=avg(labExam) from stuMarks
print ‘笔试平均分：’+convert(varchar(5), @writtenAvg)
print ‘机试平均分：’+convert(varchar(5), @labAvg)
if(@writtenAvg>70 and @labAvg >70)
print ‘本班考试成绩：优秀’
else
print ‘本班考试成绩：较差’
print ‘------------------------------------------------------’
print ‘参加考试没有通过的学员：’
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam<60 or labExam <60
go
/*调用执行存储过程*/
exec proc_stu
4. 创建带输入参数的存储过程
存储过程的参数有两种：
（1）输入参数：可以在调用时向存储过程传递参数，此类参数可用来在存储过程中传入值。
（2）输出参数：如果希望返回值，则可以使用输出参数，输出参数后有“output”标记，执行存储过程后，将把返回值存放在输出参数中，可供其他T-SQL语句读取访问。
语法：
create proc[edure] 存储过程名
[ {@参数1 数据类型} [=默认值][output],
…..,
{@参数n 数据类型} [=默认值][output]
]
as
sql语句
如果参数后面有“output”关键字，表示此参数为输出参数，否则视为普通输入参数，输入参数还可以设置默认值。
例：
if exists(select * from sysobjects where name=’proc_stu’)
drop procedure proc_stu
go
create procedure proc_stu
@writtenPass int = 60,
@labPass int = 60
as --注意声明的变量要放在as前
print ‘笔试及格线：’+convert(varchar(5), @writtenPass)
print ‘机试及格线：’+convert(varchar(5), @labPass)
print ‘------------------------------------------------------’
print ‘参加考试没有通过的学员：’
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam<@writtenPass or labExam <@labPass
go
/*-----调用哪个存储过程---*/
exec proc_stu --都采用默认值，笔试和机试及格线都为60
exec proc_stu 64 --机试采用默认：笔试及格线64分，机试及格线55分
exec proc_stu 60,55 --都不采用默认：笔试及格线60分，机试及格线55分
5. 创建带输出参数的存储过程
需要使用输出（output）参数。
例：
if exists(select * from sysobjects where name=’proc_stu’)
drop procedure proc_stu
go
create procedure proc_stu
@notpassSum int output, --output关键字，否则视为输入参数
@writtenPass int = 60,
@labPass int = 60
as --注意声明的变量要放在as前
print ‘笔试及格线：’+convert(varchar(5), @writtenPass)
print ‘机试及格线：’+convert(varchar(5), @labPass)
print ‘------------------------------------------------------’
print ‘参加考试没有通过的学员：’
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam<@writtenPass or labExam <@labPass
/*--统计并返回没有通过考试的学员人数--*/
select @notpassSum=count(stuNo) from stuMarks
where writtenExam<@writtenPass or labExam <@labPass
go
/*-----调用哪个存储过程---*/
declare @sum int –-定义变量，用于存放调用存储过程时返回的结果
exec proc_stu @sum out ,64 ---调用时也带output关键字，机试及格线默认为60
print ’--------------------------------------------’
if @sum>=3
print ‘未通过人数：’ + convert(varchar(5),@sum)+’人，超过过60%,及格分数线还应下调’
else
print ‘未通过人数：’ + convert(varchar(5),@sum) +’人，已控制在60%以下,及格分数线适中’
6. 处理错误信息
raiserror语句的语法如下：
raiserror ({msg_id | msg_str}{,severity,state}[with option[,….n]])
其中
msg_id：在sysmessages系统表中指定的用户定义错误信息。
msg_str：用户定义的特定信息，最长255个字符。
severity：与特定信息相关联，表示用户定义的严重级别。用户可使用级别为0~18。、
19~25级是为sysadmin固定角色的成员预留的，并且需要指定with log选项。20~25级错误被认为是致命错误。
state：表示错误的状态，是1~127的值。
option：指示是否将错误记录到服务器错误日志中。
例：
raisreeor(‘及格线错误，请指定0-100之间的分数，统计中断退出’,16,1)
引发系统错误，指定错误的严重级别为16，调用状态为1（默认）。错误的严重级别大于10，将自动设置系统全局变量@@error为非零值，表示语句执行出错。
878
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
