SQLServer数据库（二）

数据库设计：就是将数据库中的数据库实体及这些数据库实体之间的关系，进行规划和结构化的过程。

项目开发过程：

需求分析

概要设计

详细设计

代码编写

运行测试

打包发行

数据库的系统分析基本步骤：收集信息、标识实体、标识每个实体需要存储的详细信息、标识实体之间的关系。

实体，就是指现实世界中具有区分其它事物的特征或属性，并与其他实体有联系的实体。实体一般是名词，对应表中的一行数据，但我们在开发时，也常常把整个表称为一个实体。

属性可以理解为实体的特征。

联系是两个或多个实体之间的关联关系。一般为动词。

映射基数表示通过联系与实体关联的其他实体的个数。

一对一、一对多、多对多、多对一

1:1、1:N、M:N、N:1

实体关系图（E-R图）组成部分：

矩形表示实体集

椭圆形表示属性

菱形表示联系集

直线表示连接属性和实体集，也用来连接实体集和联系集。

关系模式：一个关系描述的属性名的集合。

三大范式：

第一范式，确保每列的原子性。

第二范式，确保每列都和主键相关。

第三范式，确保每列都和主键列直接相关。

数据库文件，主数据文件：xxx.mdf、次要数据文件：xxx.ndf、日志文件：xxx.ldf

数据库至少有一个数据文件和一个日志文件，实际应用中，为了保证的安全性，提高存储逼真数据库管理员会将数据文件

和日志文件分别放置在不同的驱到器上。

创建数据库，下面使用T-SQL创建数据库：

createdatabase数据库名

on[primary]

(

<数据文件参数>[,...n][<文件组参数>]

)

[logon]

(

{<日志文件参数>[,...n]}

)

文件参数：

(

[name=逻辑文件名，]

filename=带文件名的物理文件路径

[,size=大小mb]

[,maxsize={最大容量|unlimited}]

[,filegrowth=增长量mb，也可以是百分比]

)[,...n]

文件组参数：

filegroup文件组名<文件参数>[,...n]

其中，[]表示可选的部分，{}表示必需的部分。

数据库名：数据库的名称，最长为128个字符。

primary:该选项是一个关键字，指定主文件组中的文件。

logon:指明事务日志文件的明确定义。

name:指定数据库的逻辑名称，这是在SQLServer中使用的名称，是数据库在SQLServer中的标识符。

filename:指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和name的逻辑名称一一对应。

size:指定数据库的初始容量大小。

maxsize:指定操作系统文件可以增长到的最大值。

filegrowth:指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。

例： ifdb_id(‘MySchool’)isnotnull

dropdatabaseMySchool

go

createdatabaseMySchool

onprimary

(

name=’MySchool_data’,

filename=’D:ProjectsMySchool_data.mdf’,

size=10mb,

maxsize=100mb,

filegrowth=10%

)

logon

(

name=’MySchool_log’,

filename=’D:ProjectsMySchool_log.ldf’,

size=2mb,

maxsize=10mb,

filegrowth=1mb

)

go

删除数据库：

dropdatabasedatabase_name;

exists(查询语句)检测某个查询是否存在。如果查询结果为null，则整个exists语句返回false，反之true。

例：ifexists(select*fromsysdatabaseswherename=’MySchool’)

dropdatabaseMySchool

go

创建表：

createtabletable_name

(column_name数据类型列的特征[,...n])

列的特征包括：

是否为空null/notnull

是否为标识列identity(1,1)

是否为主键primarykey

是否有默认值=值

...

表的清单存放在该数据库的系统表sysobjects中。

删除表：droptabletable_name

ifexists(select*fromsysobjectswherename=’student’

droptablestudent

删除数据库和表都可以使用下面的简便方式

删除数据库：ifdb_id(‘database_name’)isnotnull

dropdatabasedatabase_name

删除表：ifobject_id(‘table_name’)isnotnull

droptabletable_name

修改表结构：添加列、改变列的长度、改变列的数据类型、删除列

altertabletable_name

[altercolumncolumn_name[新数据类型]]

[add<new_column_name><数据类型>[完整性约束]]

[drop<完整性约束名>]

例： 添加列： altertableBook

addbNamevarchar(10);

修改列： altertableBook

altercolumnbNamechar(10);

删除列： altertableBook

dropcolumnbname;

表操作

查看表：execsp_helpBook

重命名表：execsp_rename[当前表名],[新表名]

常用约束类型：

主键约束(PrimaryKeyConstraint):要求主键列数据唯一，并且不允许为空

非空约束(NotNull):要求列不能存在空值

唯一约束(UniqueConstraint):要求该列的值必须唯一，允许为空，但只能出现一个空值，主键一般都具有唯一约束

检查约束(CheckConstraint):某列的取值范围限制、格式限制等。

默认约束(DefaultConstraint):指定某列的默认值。如果插入数据时没有插入该列数据，具使用默认值

外键约束(ForeignKeyConstraint):用于在两表之间建立关系，需要指定引用主表的哪一列。一般引用主表的主键列

添加约束，语法：altertabletable_name

addconstraint约束名约束类型具体的说明

--添加主键约束

altertablestudent

addconstraintPK_studentNoprimarykey(studentNo)--如果要使用组合主键，可在括号内加列名，用逗号

隔开。

--添加唯一约束

altertablestudent

addconstraintUQ_student_identityCardunique(identityCard)

--添加默认约束

altertablestudent

addconstraintDF_student_addressdefault(‘地址不详’)foraddress

--添加检查约束

altertablestudentwithnocheck --这里的withnocheck可以建立检查约束时不对旧数据进行检测

addconstraintCK_student_bornDatecheck(bornDate>=’1980-01-01’)

--添加外键约束

altertableresult

addconstraintFK_result_studentforeignkey(studentNo)referencesstudent(studentNo)

删除约束：

altertabletable_name

dropconstraintconstraint_name

创建序列：

createsequencesequence_name

startwith种子

incrementby增量

得到当前序列值：nextvalueforsequence_name

局部变量，以@开头，全局变量以@@开头，全局变量只能读取不能声明。

声明局部变量：declare@vaviable_name<数据类型>

给局部变量赋值：

set@variable_name=表达式,可以是一个查询,但是必须返回的是一个值 --这种是直接把值赋给变量

select@variable_name=valuefromtable_namewhere....--这种是查询出值之后再赋给变量

例： declare@namevarchar(8) --声明变量

declare@studentNoint--声明变量

set@name=’李文才’ --使用set赋值

select@studentNo=studentNofromstudentwherestudentName=@name

set语句与select语句的区别：

全局变量：

输出语句：

print变量或字符串 --直接在消息框中显示信息

select变量as自定义列名--当作查询结果输出

例： print‘服务器的名称’+@@servername

print‘SQLServer的版本’+@@version

select@@serverNameas‘SQLServer的版本’

select@@versionas‘SQLServer的版本’

设置不显示受影响行数：setnocounton;

数据类型转换：

cast(表达式as数据类型)

convert(数据类型(长度),表达式[,样式]) --第三个参数可略，一般只对日期格式有效，如101,102

逻辑控制语句：

begin-end语句,作用类似于编程语言的”{}”,表示语句块的开始和结束。在一个语句块中可以包含另一个语句块。

begin

语句或语句块

end--可以在end后加注释，这样可提高代码的可读性

if-else条件语句,属于分支结构控制语句：

if(条件)

begin

语句或语句块

end

else

begin

语句或语句块

end

while循环语句，可以根据某些条件重复执行一条SQL语句或一个语句块。可以使用continue和break语句来控制

while(条件)

begin

语句或语句块

[break|continue]

end

case-end多分支语句，计算一组条件表达式，并返回其中一个符合条件的结果。像是if-elseif-else

case

when条件1then结果1

when条件2then结果2

[else其他结果]

end

try-catch,数据库中的异常处理：

begintry

语句或语句块

endtry

begincatch

语句或语句块

endcatch

go指令，它把一条或多条SQL语句作成一个可执行单元，称为执行计划。这样的话，这一个执行单元和其它的执行单元就不

会互相干扰，相当于逻辑断路，从而提部执行效率。

如： useMySchool

go

子查询，在语句中嵌入查询语句。

select...from表1where列1>(子查询)--这里的”>”运算符可以换成其他的运算符号。但是将子查询和比较运

算符联合使用，必须保证子查询返回的值不能多于一个。

编写查询时，尽量写列名列表，这样会提高执行效率，以及减少服务器压力。

子查询可以在多表间查询符合条件的数据，从而替换表连接(join)查询。这个时候在查询结果要有别名才行

表连接都可以用子查询替换，但反过来不一定。子查询比较灵活、方便、形式多样，适合作为查询的筛选条件;而表连接更

适合于查看多表的数据。

在给查询出来的表达式取别名时，如果用“=”号，别名前置;如果用“as”或空格“”，别名后置。

当为某个表命名了别名后，在select语句中出现该表的列需要指定表名时，就必须统一使用该表的别名;否则语法错误。

in和notin子查询，用于限定列值的范围。

例： selectstudentNo,studentNamefromStudent

wherestudentNoin{ --语法上in和notin是一个样的。

selectstudentNofromResult

}

in子查询，是指定限制某列的筛选范围只能是子查询结果集中的值。

notin子查询，是指定限制某确的筛选范围不能包含子查询结果集中的值。

exists和notexists子查询

exists子查询：exists(子查询)--如果子查询的结果为NULL，则exists(子查询)返回false，否则返回true

notexists子查询：notexists(子查询) --效果和exists(子查询)相反，为NULL返回true,否则返回false

可以利用exists和notexists子查询来作为if-else判断语句、while循环、where子句的条件

给结果集添加行号列：

select列名=row_number()over(orderby列名asc[desc])fromtable_name --结果集中添加了一列

子查询注意事项

1.子查询语句可以嵌套在SQL语句中任何表达式出现的位置，select,from,where,groupby,having

selecttname=(selecttnamefromtopicwheretopic.tid=news.ntid),ntitle,nauthorfromnews

此查询代替了内联接查询

select*from(子查询)as表的别名 --可以把子查询的结果当作一个表进行表联接查询。

2.在子查询的select子句中不能出现text,ntext或image数据类型的列，不支持在子查询语句中查询二进制的数据。

3.只出现在子查询中而没有出现在父查询中的表不能包含在输出列中。

事务(Transaction)是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，永久保存。如果事

务遇到错误且必须取消或回滚，则所有数据更改均被清除。其实每一句SQL语句都是一个很小很小的事务。

事务是作为单个逻辑工作单元执行的一系列操作。

一个逻辑工作单元的4个属性，ACID：

1.原子性(Atomicity):事务是一个完整的操作。事务的各元素是不可分的（原子的）。

2.一致性(Consistency):当事务完成时，数据必须外于一致状态。

3.隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应该以任何形

式依赖于或影响其他事务。

4.持久性(Durability):事务的持久性指不管系统是否发生了故障，事务外理的结果都是永久的。

执行事务的语法

开始事务：begintransaction

提交事务：committransaction

回滚事务：rollbacktransaction

事务的分类

1.显式事务：begintransaction明确指定事务的开始。（最常用）

2.隐式事务：通过设置setimplicit_transactionson语句，将隐式事务模式设置为打开。当以隐式事务操作时，

SQLServer将在提交或回滚事务后自动启动新事务。只要提交或回滚事务即可。

3.自动提交事务：这是SQLServer的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交

。如果错误，则自动回滚。

编写事务的原则：

1.事务尽可能简短。

2.事务中访问的数据量尽量最少。

3.查询数据时尽量不要使用事务。

4.在事务处理过程中尽量不要出现等待用户输入的操作。事务长时间占用资源，有可能造成系统阻塞。

执行大型事务时，判断是否需要回滚，可以用一个变量@err把每一条数据库操作语句的@@error累加起来。如果到最后这个变量@err都是0，则证明这个事务没有出错，可以永久化这个操作。否则回滚事务。

视图(view)，是保存在数据库中的select查询。使用视图的原因有：一个是出于安全考虑，用户不必看到整个数据库的结构

，而隐藏部分数据;另一个是符合用户日常业务逻辑，使他们对数据更容易理解。可以对视图中的数据进行增删改，但一

般用作查询。

视图是一种虚拟表通常是作为来自一个表或多个表的行或列的子集合成的。

用途：

筛选表中的行。

防止未经许可的用户访问敏感数据。

将多个物理数据表抽象为一个逻辑数据表。

对最终用户的好处：

结果更容易理解

获得数据更容易

对开发人员的好处：

限制数据检索更容易

维护应用程序更方便

创建视图：

createviewview_name

as

<select语句>

删除视图：dropviewview_name --视图在当前数据库下表sysobjects中。删除视图后，原始表不影响。

查询视图：select*fromview_name --把视图当作一般物理表来用

使用视图的注意事项：

1.每个视图中可以使用多个表。

2.与查询相似，一个视图可以嵌套另一个视图，但最好不要超过3层。

3.视图定义中的select语句不能包括下列内容：

orderby子句，除非在select语句的选择列表中也有一个top子句。

into关键字。

引用临时表或表变量。

索引(index)，作通过使用索引，大大提高数据库的检索速度，改善数据库性能。

索引分类：

1.唯一索引，不允许两行具有相同的索引值。若创建了唯一约束，则将自动创建唯一索引。尽管唯一索引有助于找到

信息，但为了获得最佳性能，仍建立使用主键约束。

2.主键索引，定义一个主键列，将自动建立主键索引，要求主键中的的每个值是非空，唯一的。

3.聚集索引，表中各行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引。主键默认聚集索引

4.非聚集索引，建立在索引页上，当查询数据时可以从索引中找到记录存放的位置。非聚集索引法中条行数据存放的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引有更快的数据访问速度。

5.复合索引，可以将多个列组合作为索引，只有用到复合索引的第一列或整个复合索引列作为条件完成数据查询时才

会用到该索引。

6.全文索引，是一种特殊类型的基于标记的功能性索引，由SQLServer中全文引擎服务创建和维护。主要用于大量 文字中搜索字符串，此时使用全文索引的效率将大大高于使用like关键字的效率。

创建索引：

create[unique][clustered|nonclustered]indexindex_name

ontable_name(column_name[,...n])

[withfillfactor=x]

--unique指指唯一索引，可选。

--clustered、nonclustered指定是聚集索引还是非聚集索引，可选。

--fillfactor表示填充因子，指定一个0~100的值，该值指示索引页填洪的空间所占的百分比。

删除索引：dropindextable_name.index_name

--删除表时，该表的所有索引将同时被删除。

--如果要删除退给所有索引，则先要删除非聚集索引，再删除聚集索引。

调用索引： select*fromtable_name

with(index=index_name)

where条件

查看索引：

1.用系统存储过程sp_helpIndex查看：execsp_helpIndextable_name --查看该表下的索引

2.用视图sys.indexes查看：select*fromsys.indexes

优化SQL语句：

1.查询时减少使用“*”返回全部列，不要返回不务要的列。

2.索引列的值应该尽量小，在字节数小的列上建立索引。

3.where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前。

4.避免在orderby子句中使用表达式。

5.根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理。

存储过程,(Procedure)是SQL语句和控制语句的预编译集合，保存在数据库里，可由应用程序调用执行，而且允许用户声明

变量、逻辑控制语句及其他强大的编程功能。类似于方法、函数

优点：

1.模块化程序设计。

2.执行速度快，效率高。

3.减少流量。

4.具有良好的安全性。

系统存储过程，用户可以通过系统存储过程访问和更新系统表。名称以“sp_”开头，并存放在Resource数据库中。可以在

任何数据库中运行系统存储过程，但执行的结果会反映在当前数据库中。

执行存储过程：

exec[ute]procedure_name[参数值列表]

--exec是execute的简写，如果是执行语句第一句，可以不用写exec。

扩展存储过程(ExtendedStoredProcedures)是SQLServer提供的各类系统存储过程中的一类。

execxp_cmdshellDOS命令 [no_output] --no_output为可选参数，设置执行DOS命令后是否输出返回信息。

例: execsp_configure‘showadvancedoption’,1

go

reconfigure

go

execsp_configure‘xp_cmdshell’,1

go

reconfigure

go

execxp_cmdshell‘DOS命令’

用户自定义存储过程，一个完整的存储过程包括：输入参数和输出参数、T-SQL语句、存储过程的返回值

创建存储过程：

createproc[edure]procedure_name

[ {@参数1数据类型}[=默认值][output],

.....,

{@参数n数据类型}[=默认值][output]

]

as

begin

SQL语句

end

删除存储过程：dropproc[edure]procedure_name --存储过程信息在数据库表sysobjects中

创建带输入参数的存储过程：

输入参数：可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值。

输出参数：如果希望返回值，则可以使用输出参数，输出参数后有“output”标记。

如果存储过程的参数后面有“outpu”关键字，则表示此参数为输出参数;否则视为输入参数，输入参数还可以设置为默认值default。

执行带参数的存储过程：

exec[ute][返回变量=]存储过程[@参数1=]参数值1[output]|[default],

.....,

[@参数n=]参数值n[output]|[default]

为了调用方便，最好将有默认值的参数放在存储过程参数列表的最后。

创建存储过程时，参数有“output”关键字，则调用时也需要要变量后跟随“output”关键字。

执行带有输出参数的存储过程，如果不按参数顺序传递参数值，则要指定参数名。一旦某个参数按“@参数名=值”格式传

递数据，那么之后的参数必须以同样的格式传递数据

例: execusp_query_num@UnpassNumoutput,@TotalNumoutput,@pass=50,@SubjectName=’JavaLogic’

raiserror语句，返回用户定义的错误信息，可指定严重级别，设置系统变量记录所发生的错误。

raiserror({msg_id|msg_str}{,severity,state}[withoption[,.....n]])

--msg_id:在sysmessages系统表中指定的用户定义错误信息。

--msg_str:用户定义的特定信息，最长为255个字符。

--severity:与特定信息相关联，表示用户定义的严重性级别。用户可使用的为0~18级，19~25级是为sysadmin固定角

色的成员预留的，并且需要指定withlog选项，20~25级被认为是致命错误。

--state:表示错误的状态，是1~255中的值。

--option:错误的自定义选项，可以是下列任一值。

log:在MiscosoftSQLServer数据库引擎实例的错误日志和应用程序日志中记录错误。

nowait:将消息立即发送给客户端。

seterror:将@@error值和error_number值设置为msg_id或50000,不用考虑严重级别。

例: begintry

....

endtry

begincatch

raiserror(‘错误信息’,16,1)

return --立即返回，退出存储过程

endcatch

错误严重级别大于10,将自动设置系统全局变量@@error为非零值，表示意识执行错误。判断语句执行是否出错。

运行sql语句的文本：

1）.exec(@sql),N’参数列表’,参数值列表

2）.execsp_executesql@sql,N’参数’,参数

注意： 1.参数的数目、类别、顺序必须和存储过程中的要一致。后一种方法可以用输出参数，用表与存储过程相同。

2.语句进行拼接时，要注意长度是否不够。

3.拼接参数时，两个单引号‘’视为一个单引号’ 如：wherename=’’’+@name+’’’;

4.NULL不能被拼接。

insert时，如果用序列，那么最好定义一个变量存它，否则之后不好调用。

如果用标识列，可以用@@identity来获得当前插入记录的编号。