SQLServer复习参考.docx
《SQLServer复习参考.docx》由会员分享，可在线阅读，更多相关《SQLServer复习参考.docx（73页珍藏版）》请在冰豆网上搜索。
SQLServer复习参考
SQLServer数据库设计
一、数据库设计的必要性
在实际的软件项目中，如果系统中需要存储的数据量比较大，需要设计的表比较多，表与表之间的关系比较复杂，那我们就需要进行规范的数据库设置。
如果不经过数据库的设计，我们构建的数据库不合理、不恰当，那么数据库的维护、运行效率会有很大的问题。
这将直接影响到项目的运行性和可靠性。
二、什么是数据库设计
数据库设计实际上就是规划和结构化数据库中的数据对象以及这些数据对象之间的关系过程。
三、数据库设计的重要性
Ø不经过设计的数据库或是设计糟糕的数据库很可能导致
1、数据库运行效率地下
2、更新、删除、添加数据出现问题
Ø良好设计的数据库
1、执行效率高
2、使应用程序更便于开发
3、扩展性好
4、维护性好
四、数据模型
数据模型就像是数据间联系的一个轮廓图，整个模型就像一个框架。
如果按照记录间联系的表示方式，对数据模型进行分类，可以分为：
层次模型、网状模型、关系模型。
前两种又称为格式化数据模型。
数据模型的好坏直接影响到数据库的性能，所以数据模型的选择是数据库设计的首要任务。
Ø实体-关系（E-R）数据模型
E-R数据模型（Entity-Relationshipdatamodel），即实体-关系数据模型。
E-R数据模型不同于传统的关系数据模型，它不是面向实现，而是面向现实物体的。
Ø实体（Entity）
数据是用来描述现实中的物体的，而描述的对象都是形形色色的，有具体的、也有抽象的；有物理上存在的、也有概念性的。
凡是可以互相区别而且可以被人们认识的事、物、概念等统统抽象为实体。
多个相同的类型的实体可以称为实体集（Entityset）。
因此，在E-R数据模型中，也有型与值之分；实体可以作为型来定义，每个实体可以是它的实例和值。
Ø属性（Attribute）
实体一般具体若干特征，这些特征称为实体的属性。
而每个属性都有自己的取值范围，在E-R数据模型中称为值集（valueset）。
在同一实体集中，每个实体的属性及其值集都是相同的，但可能取不同的值。
属性对应数据库表的列。
Ø关系（Relationship）
实体之间会有各种关系，这些关系抽象为联系。
不但实体可以有属性，关系也可以有属性。
五、数据库设计步骤
Ø数据库设计可以分为以下几个阶段
1、需求分析阶段：
分析客户的业务需求，特别是数据方面的需求
2、概要设计阶段：
绘制数据库的E-R图，并确认需求文档的正确性和完整性，E-R图是项目的设计人员、开发人员、测试人员，以及和客户进行沟通的重要凭据
3、详细设计阶段：
将概要设计阶段的E-R图转换为数据库表，进行逻辑设计，确定各个表之间的主外键关系，运用数据库的三范式进行审核，并进行技术评审。
最后决定选哪种数据库（Oracle、SQLServer、MySQL）来建库、建表。
Ø需求分析阶段：
数据库系统分析
秀气分析阶段的重点是调查、收集、分析客户的业务数据需求以及数据的安全性、完整性需求等。
需求分析步骤：
1、确认业务需求
2、标识关系实体
3、标识每个实体的具有的属性
4、确认实体之间的关系
Ø概要设计阶段：
绘制E-R图
作为数据库设计者，你需要和项目组内其他成员分享你的设计思路，共同研讨数据库设计的合理性、安全性、完整性，并确认是否符合客户的业务需求。
那么使用E-R图，这种图形化的表示方式最为直观。
*E-R图中的实体、属性和关系
上面的简单E-R图可以看出用户和收支之间的关系。
在上图中可以看出：
用矩形表示实体，实体是一般名词；椭圆表示属性，一般也是名词；菱形表示关系，一般是动词。
*映射基数
映射基数表示可以通过关系与该实体的个数。
对于实体集A和B之间的二元关系，可能的映射基数有：
1、一对一：
也就是A实体中最多只有一个B实体的关联，而B实体的最多只有一个A实体的关联。
用E-R图表示：
2、一对多：
A实体可以与B实体任意数量的进行关联，B中的实体最多与A中的一个实体关联。
E-R图表示：
3、多对一：
A实体最多与一个B实体进行关联，而B实体可以和任意多个A实体进行关联。
E-R图表示：
4、多对多：
A实体可以有多个B实体，而B实体也可以有任意多个A实体。
E-R图表示：
*E-R图
E-R图可以以图形化的方式将数据库的整个逻辑结构表示出来，组成部分有：
1、矩形表示实体集
2、椭圆表示属性
3、菱形表示关系、
4、直线用来连接实体集与属性、实体集和关系
5、直线箭头表示实体集之间映射基数
Ø详细设计阶段：
将E-R图转换为表
步骤如下：
1、将各个实体转换为对应的表，将各属性转换为对应的列
2、标识每张表的主键
3、将实体之间的关系转换为表与表之间的主外键关系
六、数据库设计规范化
Ø数据库设计中经常出现的问题
1、数据冗余大
2、插入数据异常
3、删除异常
4、更新异常
Ø规范设计
一个较好的关系数据库模型，它的每个关系中的属性一定要满足某种内在的语义条件，即要按一定的规范设计关系模型，这就是设计的规范化。
在数据库设计时，有一些专门的规则，称为数据库的设计范式，遵循这些规则，就可以创建出良好的数据库，数据库著名的三大范式理论：
1、第一范式（1NF）
第一范式是满足关系数据库模型所要遵循的最基本的条件范式，几关系中的每个属性必须是不可再分的简单项，不能是属性组合，即属性的取值是不可拆分的原子值。
2、第二范式（2NF）
第二范式是在第一范式的基础上，确保表中的每列都和主键相关。
其定义是如果一个关系满足1NF，并且除了主键关系外的其他列都依赖于该主键，则满足第二范式。
3、第三范式（3NF）
第三范式是在第二范式的基础上进行的，第三范式的目标是确保每列都和主键列直接相关，而不是间接相关的。
其定义是：
如果一个关系满足2NF，并且除主键外的其他列都不传递依赖于该主键。
Ø规范化和性能关系
为了满足三大范式，数据库的性能可能会有一定程度的降低。
所以，在实际数据库设计中，我们既要尽量满足三大范式，从而避免数据冗余和各种数据库的操作异常，同时也要考虑数据的访问性能。
有时候，为了提高数据库的访问效率，适当的允许少量数据冗余咧存在，才是最适合的数据库设计方案。
SQLServer数据库基础编程
ØGo批处理语句
用于同时执行多个语句
Ø使用、切换数据库
usemaster
go
Ø创建、删除数据库
方法1、
--判断是否存在该数据库，存在就删除
if（exists（select*fromsys.databaseswherename='testHome'））
dropdatabasetestHome
go
--创建数据库，设置数据库文件、日志文件保存目录
createdatabasetestHome
on（
name='testHome',
filename='c:
\data\students.mdf'
）
logon（
name='testHome_log',
filename='c:
\data\testHome_log.ldf'
）
go
方法2（设置文件大小）、
if（exists（select*fromsys.databaseswherename='testHome'））
dropdatabasetestHome
go
createdatabasetestHome
--默认就属于primary主文件组，可省略
onprimary（
--数据文件的具体描述
name='testHome_data',--主数据文件的逻辑名
fileName='c:
\testHome_data.mdf',--主数据文件的物理名
size=3MB,--主数据文件的初始大小
maxSize=50MB,--主数据文件增长的最大值
fileGrowth=10%--主数据文件的增长率
）
--日志文件的具体描述，各参数含义同上
logon（
name='testHome_log',
fileName='c:
\testHome_log.ldf',
size=1MB,
fileGrowth=1MB
）
go
方法3（设置次数据文件）、
if（exists（select*fromsys.databaseswherename='testHome'））
dropdatabasetestHome
go
createdatabasetestHome
--默认就属于primary主文件组，可省略
onprimary（
--数据文件的具体描述
name='testHome_data',--主数据文件的逻辑名
fileName='c:
\testHome_data.mdf',--主数据文件的物理名
size=3MB,--主数据文件的初始大小
maxSize=50MB,--主数据文件增长的最大值
fileGrowth=10%--主数据文件的增长率
）,
--次数据文件的具体描述
（
--数据文件的具体描述
name='testHome2_data',--主数据文件的逻辑名
fileName='c:
\testHome2_data.mdf',--主数据文件的物理名
size=2MB,--主数据文件的初始大小
maxSize=50MB,--主数据文件增长的最大值
fileGrowth=10%--主数据文件的增长率
）
--日志文件的具体描述，各参数含义同上
logon（
name='testHome_log',
fileName='c:
\testHome_log.ldf',
size=1MB,
fileGrowth=1MB
）,
（
name='testHome2_log',
fileName='c:
\testHome2_log.ldf',
size=1MB,
fileGrowth=1MB
）
go
Ø基本数据类型
精确数字类型
类型
描述
bigint
bigint数据类型用于整数值可能超过int数据类型支持范围的情况，范围：
-2^63到2^63-1，
存储空间8字节
int
整数数据类型，范围在-2^31到2^31-1，存储空间4字节
smallint
整数，范围在-2^15到2^15-1，存储空间2字节
tinyint
范围在0到255，存储空间1字节
bit
可以取值为1、0或NULL的整数数据类型，每8个bit占一个字节，16bit就2个字节，24bit就3个字节
decimal
带固定精度和小数位数的数值数据类型，有效值从-10^38+1到10^38-1
numeric
同上
money
货币或货币值的数据类型，范围在-922,337,203,685,477.5808到922,337,203,685,477.5807
smallmoney
货币类型，-214,748.3648到214,748.3647
近似数字类型
类型
描述
float
表示浮点数值数据的大致数值数据类型。
浮点数据为近似值；范围-1.79E+308至-2.23E-308、0
以及2.23E-308至1.79E+308
real
real的SQL-92同义词为float（24），范围在-3.40E+38至-1.18E-38、0以及1.18E–38
至3.40E+38
日期时间类型
类型
描述
datetime
表示某天的日期和时间的数据类型，范围在1753年1月1日到9999年12月31日
smalldatetime
范围在1900年1月1日到2079年6月6日
字符串类型
类型
描述
char
固定长度或可变长度的字符数据类型，范围在范围为1至8,000字节
text
最大长度为2^31-1
varchar
固定长度或可变长度的字符数据类型，最大存储大小是2^31-1个字节
Unicode字符串类型
类型
描述
nchar
字符数据类型，长度固定，在必须在1到4,000之间
nvarchar
可变长度Unicode字符数据。
最大存储大小为2^31-1字节
ntext
长度可变的Unicode数据，最大长度为2^30-1（1,073,741,823）个字符
二进制字符串类型
类型
描述
binary
长度为n字节的固定长度二进制数据，范围从1到8,000的值。
存储大小为n字节。
varbinary
可变长度二进制数据。
n可以取从1到8,000的值。
最大的存储大小为2^31-1字节
image
长度可变的二进制数据，从0到2^31-1（2,147,483,647）个字节
Ø判断表或其他对象及列是否存在
--判断某个表或对象是否存在
if（exists（select*fromsys.objectswherename='classes'））
print'存在';
go
if（exists（select*fromsys.objectswhereobject_id=object_id（'student'）））
print'存在';
go
if（object_id（'student','U'）isnotnull）
print'存在';
go
--判断该列名是否存在，如果存在就删除
if（exists（select*fromsys.columnswhereobject_id=object_id（'student'）andname='idCard'））
altertablestudentdropcolumnidCard
go
if（exists（select*frominformation_schema.columnswheretable_name='student'andcolumn_name='tel'））
altertablestudentdropcolumntel
go
Ø创建、删除表
--判断是否存在当前table
if（exists（select*fromsys.objectswherename='classes'））
droptableclasses
go
createtableclasses（
idintprimarykeyidentity（1,2）,
namevarchar（22）notnull,
createDatedatetimedefaultgetDate（）
）
go
if（exists（select*fromsys.objectswhereobject_id=object_id（'student'）））
droptablestudent
go
--创建table
createtablestudent（
idintidentity（1,1）notnull,
namevarchar（20）,
ageint,
sexbit,
cidint
）
go
Ø给表添加字段、修改字段、删除字段
--添加字段
altertablestudentaddaddressvarchar（50）notnull;
--修改字段
altertablestudentaltercolumnaddressvarchar（20）;
--删除字段
altertablestudentdropcolumnnumber;
--添加多个字段
altertablestudent
addaddressvarchar（22）,
telvarchar（11）,
idCardvarchar（3）;
--判断该列名是否存在，如果存在就删除
if（exists（select*fromsys.columnswhereobject_id=object_id（'student'）andname='idCard'））
altertablestudentdropcolumnidCard
go
if（exists（select*frominformation_schema.columnswheretable_name='student'andcolumn_name='tel'））
altertablestudentdropcolumntel
go
Ø添加、删除约束
--添加新列、约束
altertablestudent
addnumbervarchar（20）nullconstraintno_ukunique;
--增加主键
altertablestudent
addconstraintpk_idprimarykey（id）;
--添加外键约束
altertablestudent
addconstraintfk_cidforeignkey（cid）referencesclasses（id）
go
--添加唯一约束
altertablestudent
addconstraintname_ukunique（name）;
--添加check约束
altertablestudentwithnocheck
addconstraintcheck_agecheck（age>1）;
altertablestudent
addconstraintck_agecheck（age>=15andage<=50）
--添加默认约束
altertablestudent
addconstraintsex_defdefault1forsex;
--添加一个包含默认值可以为空的列
altertablestudent
addcreateDatesmalldatetimenull
constraintcreateDate_defdefaultgetDate（）withvalues;
-----多个列、约束一起创建--------
altertablestudentadd
/*添加id主键、自增*/
idintidentityconstraintidprimarykey,
/*添加外键约束*/
numberintnull
constraintuNumberreferencesclasses（number）,
/*默认约束*/
createDatedecimal（3,3）
constraintcreateDatedefault2010-6-1
go
--删除约束
altertablestudentdropconstraintno_uk;
Ø插入数据
insertintoclasses（name）values（'1班'）;
insertintoclassesvalues（'2班','2011-06-15'）;
insertintoclasses（name）values（'3班'）;
insertintoclassesvalues（'4班',default）;
insertintostudentvalues（'zhangsan',22,1,1）;
insertintostudentvalues（'lisi',25,0,1）;
insertintostudentvalues（'wangwu',24,1,3）;
insertintostudentvalues（'zhaoliu',23,0,3）;
in
升级会员 