SQLServer数据库（二）

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

项目开发过程：

需求分析

概要设计

详细设计

代码编写

运行测试

打包发行

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

 

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

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

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

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

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

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

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

矩形表示实体集

椭圆形表示属性

菱形表示联系集

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

 

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

 

三大范式：

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

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

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

 

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

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

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

 

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

create database 数据库名

on [primary]

(

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

)

[log on]

(

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

)

文件参数：

(

[name=逻辑文件名，]

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

[,size=大小mb]

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

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

)[,...n]

文件组参数：

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

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

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

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

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

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

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

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

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

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

例： if db_id(‘MySchool’) is not null

drop database MySchool

go

create database MySchool

on primary

(

name=’MySchool_data’,

filename=’D:\Projects\MySchool_data.mdf’,

size=10mb,

maxsize=100mb,

filegrowth=10%

)

log on

(

name=’MySchool_log’,

filename=’D:\Projects\MySchool_log.ldf’,

size=2mb,

maxsize=10mb,

filegrowth=1mb

)

go

 

删除数据库：

drop database database_name;

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

例：if exists(select * from sysdatabases where name=’MySchool’)

drop database MySchool

 go

 

创建表：

create table table_name

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

列的特征包括：

是否为空 null/not null

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

是否为主键 primary key

是否有默认值  =值

...

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

删除表：drop table table_name

if exists(select * from sysobjects where name=’student’

drop table student

 

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

删除数据库：if db_id(‘database_name’) is not null

drop database database_name

删除表：if object_id(‘table_name’) is not null

drop table table_name

 

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

alter table table_name

[alter column column_name [新数据类型]]

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

[drop <完整性约束名>]

例： 添加列： alter table Book

add bName varchar(10);

修改列： alter table Book

alter column bName char(10);

删除列： alter table Book

drop column bname;

表操作

查看表：exec sp_help Book

重命名表：exec sp_rename [当前表名],[新表名]

 

常用约束类型：

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

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

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

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

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

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

 

添加约束，语法：alter table table_name

add constraint 约束名 约束类型 具体的说明

--添加主键约束

alter table student

add constraint PK_studentNo primary key (studentNo) --如果要使用组合主键，可在括号内加列名，用逗号

隔开。

--添加唯一约束

alter table student

add constraint UQ_student_identityCard unique (identityCard)

--添加默认约束

alter table student 

add constraint DF_student_address default(‘地址不详’) for address

--添加检查约束

alter table student with nocheck --这里的with nocheck 可以建立检查约束时不对旧数据进行检测

add constraint CK_student_bornDate check (bornDate>=’1980-01-01’)

--添加外键约束

alter table result

add constraint FK_result_student foreign key(studentNo) references student(studentNo)

删除约束：

alter table table_name

drop constraint constraint_name

 

创建序列：

create sequence sequence_name

start with 种子

increment by 增量

得到当前序列值：next value for sequence_name

 

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

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

给局部变量赋值：

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

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

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

declare @studentNo int  --声明变量

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

select @studentNo=studentNo from student where studentName=@name

set语句与select语句的区别：

对比项	set	select
同时对多个变量赋值	不支持	支持
表达式返回多个值时	出错	将返回的最后一个值赋给变量
表达式未返回值	变量被赋值为NULL	变量保持原值

 

全局变量：

变量	含义
@@error	最后一个T-SQL错误的错误号，>0则有错，=0则无错
@@identity	最后一次插入的标识值
@@language	当前使用的语言的名称
@@max_connections	可创建的、同时连接的最大数目
@@rowcount	受上一个SQL语句影响的行数
@@servername	本地服务器的名称
@@servicename	该计算机上的SQL服务器名称
@@timeticks	当前计算机上每刻度的微秒数
@@transcount	当前连接打开的事务数
@@version	SQL Server的版本信息

 

输出语句：

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

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

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

print ‘SQL Server的版本’+@@version

select @@serverName as ‘SQL Server的版本’

select @@version as ‘SQL Server的版本’

设置不显示受影响行数：set nocount on;

 

数据类型转换：

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-else if-else

case 

when 条件1 then 结果1

when 条件2 then 结果2

[else 其他结果]

end

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

begin try

语句或语句块

end try

begin catch

语句或语句块

end catch

 

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

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

如： use MySchool

go

 

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

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

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

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

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

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

适合于查看多表的数据。

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

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

 

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

例： select studentNo,studentName from Student 

where studentNo in { --语法上in 和not in 是一个样的。

select studentNo from Result

}

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

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

 

exists和not exists子查询

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

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

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

 

给结果集添加行号列：

select 列名=row_number() over(order by 列名 asc[desc]) from table_name --结果集中添加了一列

 

子查询注意事项

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

select tname=(select tname from topic where topic.tid=news.ntid),ntitle,nauthor from news

此查询代替了内联接查询

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

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

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

 

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

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

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

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

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

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

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

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

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

 

执行事务的语法

开始事务：begin transaction

提交事务：commit transaction

回滚事务：rollback transaction

 

事务的分类

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

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

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

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

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

 

编写事务的原则：

1.事务尽可能简短。

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

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

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

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

 

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

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

般用作查询。

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

用途：

筛选表中的行。

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

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

对最终用户的好处：

结果更容易理解

获得数据更容易

对开发人员的好处：

限制数据检索更容易

维护应用程序更方便

 

创建视图：

create view view_name

as

<select语句>

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

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

使用视图的注意事项：

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

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

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

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

into关键字。

引用临时表或表变量。

 

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

索引分类：

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

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

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

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

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

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

会用到该索引。

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

 

创建索引：

create [unique][clustered|nonclustered] index index_name

on table_name (column_name[,...n])

[with fillfactor=x]

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

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

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

删除索引：drop index table_name.index_name

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

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

调用索引： select * from table_name 

with(index=index_name)

where 条件 

查看索引：

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

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

 

优化SQL语句：

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

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

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

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

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

 

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

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

优点：

1.模块化程序设计。

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

3.减少流量。

4.具有良好的安全性。

 

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

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

系统存储过程	说明
sp_databases	列出服务器上所有数据库信息，包括数据库名称和数据库大小
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	重命名数据库名称
sp_tables	返回当前环境下可查询的表或视图的信息
sp_columns	返回某个表或视图的列信息，包括列的数据类型和长度等
sp_help	查看某个数据库对象的信息，如列名、主键、约束、外键、索引等
sp_helpconstraint	查看某个表的的约束
sp_helpindex	根据某个表的索引
sp_stored_procudures	显示存储过程的列表
sp_password	添加或修改登录账户的密码
sp_helptext	显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

 

执行存储过程：

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

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

 

扩展存储过程(Extended Stored Procedures)是SQL Server提供的各类系统存储过程中的一类。

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

例: exec sp_configure ‘show advanced option’,1

go

reconfigure

go

exec sp_configure ‘xp_cmdshell’,1

go

reconfigure

go

exec xp_cmdshell ‘DOS命令’

 

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

创建存储过程：

create proc[edure] procedure_name

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

.....,

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

]

as

begin

SQL语句

end

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

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

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

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

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

执行带参数的存储过程：

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

 .....,

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

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

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

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

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

例: exec usp_query_num @UnpassNum output,@TotalNum output,@pass=50,@SubjectName=’Java Logic’

 

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~255中的值。

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

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

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

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

例: begin try

....

end try

begin catch

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

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

end catch

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

 

 

运行sql语句的文本：

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

2）.exec sp_executesql @sql,N’参数’,参数

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

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

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

4.NULL不能被拼接。

 

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

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