第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为非零值,表示语句执行出错。