优化数据库设计的总结

   第一章     数据库的设计

1：在概要设计的阶段绘制E-R图。在详细设计阶段，形成数据库模型图。

2：设计数据库分成4步：

01.收集信息02.绘制E-R图（标识实体，标识实体属性，标识实体之间的关系）03.创建数据库模型图04.转换成数据库表。

3：三大范式

01：第一范式：确保每列的原子性（每个列都不允许再拆分）。

02：第二范式：在满足第一范式的基础上，要求表中除了主键列外的其他列都要依赖于主键而存在（保证表有主键）。

03：第三范式：在满足第二范式的基础上，要求表中除了主键列外的其他列都直接依赖于主键列（保证表有外键）。

主键和外键在多表中的重复出现不属于数据冗余，非键字段的重复出现才是数据冗余。

数据库的设计范式和性能要取一个平衡，设计范式等级越高，数据库性能越低。

      第二章     数据库的实现

1数据库文件：

    主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。在一个数据库中只能存在一个mdf主数据文件，文件隶属于primary文件组。

    次要数据文件扩展名是.ndf.次要数据库是可选的，通过将每个文件放在不同的磁盘驱动器上，次要文件可用户将数据分散到多个磁盘上。另外如果数据库超过了单个Windows文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。

    事务日志的文件扩展名是.ldf,事物日志文件保存用于恢复数据库的日志信息。每个数据库可以有多个且必须至少有一个日志文件。

创建数据库：create database 数据库名

              On [primary]--主数据文件

               (

                  Name=’文件名’ ,--逻辑文件名

                  FileName=’E:\文件名.mdf’,--物理文件名

                  Size=5mb,--初始大小

                  Filegrowth=1mb--增长量（可写值或百分比）

               )

              LOG ON

              （

                Name=’文件名_log’,--不能和主数据文件名重复

                FileName=’D:\文件名_log.ldf’,

                Size=5mb,

                Filegrowth=5%

               ）

创建数据库要在master数据库下，查询地址为SysdataBase表

创建表的查询地址为sysobjects表

SQL Server数据库管理系统的数据库完整性分为：

01：实体完整性：实体完整性要求每一个表中的主键字段都不能为空或者重复的值。实体完整性指表中行的完整性。

02：域完整性：指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。

03：引用完整性：引用完整性同为（参照完整性）指被引用表中的关键字和引用表中的外部主关键字之间的关系。例如，学生和班级关系中，删除某个班级元组之前，必须先删除相应的引用该班级的学生元组。这就是引用完整性。

04：自定义完整性：为了保证关系数据库的准确性而自定义的约束条件。

添加主键约束(将studentNO作为主键)

Alter  Table  student

Add  constraint  PK_StudentNO  primary  key (StudentNO)

添加唯一约束

Alter  Table  Student

Add  constraint  UQ_IDcard unique(IDcard)

添加默认约束

Alter  table  Student

Add  constraint  DF_Address  Default(‘地址不详’)  for  Address

添加检查约束

Alter  Table  Student

Add  constraint  CK_BornDate  check(BornDate>=’日期’)

添加外键约束

Alter  Table  Student  

Add  FK_StudentNO  foreign  key(StudentNO)  References

Result(StudentNO) 

Delete和Truncate以及drop的区别：

001.DELETE可以根据where条件，但是Truncate不可以

002.Delete 删除数据，再次写入数据，编号不会从1开始，但是Truncate会从1开始

003.Delete删除数据会将删除的过程记录到日志中（ldf）,但是Truncate不会

004.Drop是直接将表结构一出，delete和Truncate删除的只是数据。

     第三章     SQL编程

定义局部变量的时候，必须给出了INT和Money之外的数据类型指定长度，如果不指定长度，默认长度为1.

赋值方法SET和SELECT的区别：

01：给多个变量赋值的时候用SELECT，set不支持

02：当数据来源于数据表的时候，只能用SELECT，不能用SET.

03：如没有返回结果，SET方式返回的是NULL，而SELECT保持默认值。

全局变量：用于不能定义全局变量，只可以使用全局变量

@@Error:获取最后一条SQL语句的错误号，一般用@Error捕获约束错误，而不是SQL语句本身的语法错误。

作用：在处理复杂的操作时，可以让所有操作一次性回滚，每执行一个操作就获取一下@@error的值，判断@@error的值是否大于0.

@@identity:如果前一条语句是Insert,那么返回新插入数据行的标识列的值。（主键标识列的编号可以不是主键）

@@servername:获取安装了SQL Server的服务器名称。

@@version:查看SQL Server版本。

SQL  Server数据类型转换的两种方式：

CAST(要转换的变量 AS 目标类型)

Convert(目标类型，要转换的变量)

Floor和Ceiling函数

Select  floor(1.9999)向下取整，和四舍五入没关系（返回1）

Select  Ceiling(1.0)向上取整  （返回1）

逻辑控制语句:if  else, while, case  end 

在T-SQL中，只有while一种循环，没有DO-while和For循环并且没有while（true）的写法，可以使用while(1=1)代替。SQL中比较是否相等，用但等号（=）。

            第四章     高级查询

简单子查询（嵌套子查询）的执行机制：将子查询的结果作为外层富查询的一个条件，先执行子查询，再执行富查询。子查询语句必须用小括号括起来，然后通过比较运算符连接起来。

If  exists(子查询)子查询返回的结果必须是一个结果集，而不是一个bool值。子查询的列可以跟单个列名，也可以跟星号，但是不能跟聚合函数，因为聚合函数返回的值永远是真。聚合函数也是结果集的一种，不能作为Exists判定的依据。

结果集：用一个表结构将数据呈现出来，如果没有结果，返回的是一个空表.

在子查询的SELECT子句中不能出现TEXT、NTEXT、IMAGE数据类型。

Distinct关键字进行去重操作。

IN子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否存在于某个范围中。

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

         第六章 事物、视图、索引

事物是单个的工作单元。事物是一种机制、一个操作序列，它包含了一组数据库操作命令

、并把所有的命令作为一个整体一起向系统提交或撤销操作请求，这一组数据库命令要么都执行，要么都不执行。事物的4个属性特征（ACID）：

01：Atomicity(原子性):指参与事物的语句作为一个整体，后者全部完成，或者全部失败，不可拆分。

02：一致性（Consistemcy）：当事物完成的时候，所有数据的状态必须是一致的，所有的数据约束必须是有效的，所有数据库内部的数据存储机制也必须正确。

03:隔离性（Isolation）：指不同的事物之间是相互隔离的。并发的事物操作智能看到其他事物开始之前或者之后的状态。所有的事物操作必须不依赖于其他并发事务操作的中间过程。04:持久性（Durability）:指在事物完成后，数据里的状态被永久的改变。

BEGIN  TRANSACTION语句后面的SQL语句对数据库的更新操作都将记录在事物日志中，直至遇到ROLLBACK  TRANSACTION语句或COMMIT  Transaction语句,

视图是一张虚拟表，存储的是SQL语句，而非数据。

使用视图的原因：一个是出于安全考虑，用户不必看到整个数据库的结构，而隐藏部分数据；另一个是符合用户日常业务逻辑，使他们对数据更容易理解。

每个视图中可以使用多个表。一个视图可以嵌套另一个视图，但最好不要超过三层。视图定义中的select语句不能包括：  order  by子句，除非在SELECT语句的选择列表中也有一个Top子句。  Into关键字。  引用临时表或变量。

索引：一张表中只能有一个聚集索引，默认这个聚集索引就是主键列。一张表中可以有多个非聚集索引，一般不超过249个。

创建非聚集索引：create NonClustered index Studentinfo_PosteDate--索引名

                on StudentInfo(PosteDate)--表明（列名）

索引优缺点： 优点：01.加快访问速度。02.加强行的唯一性。

             缺点：01带索引的表在数据库中需要更多的存储空间02.操纵数据的命令需要更长的处理时间，因为他们需要对索引键进行更新。

创建索引的指导原则：01频繁搜索的列02经常用作查询选择的列03经常排序分组的列04经常用作连接的列。

不要使用下面的列创建索引：01仅包含几个不同值得列。02表中仅包含几行。

使用索引时注意事项：01查询时减少使用*返回全部列，不要返回不需要的列02索引应该尽量小，在字节数小的列上建立索引03WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前04避免在ORDER  BY子句中使用表达式05根据业务数据发生频率，定期重新生成或重新组长索引，进行碎片整理。

索引分类：

唯一索引：不允许两行具有相同的索引值。

主键索引：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。

聚集索引：表中个性的物理顺序与键值的逻辑顺序相同.

非聚集索引：建立在索引页上，当查询数据时可以从索引中找到记录存放的记录。

复合索引：只有用到复合索引的第一列或整个复合索引作为条件完成数据查询时才会使用。

全文索引：是一种特殊类型的基于标记的功能性索引，全文索引主要用于在大量文本文字中搜索字符串，使用全文索引的效率大大高于使用T-SQL的Like关键字的效率。

        第七章 存储过程

存储过程是在数据库管理系统中保存的，预先编译的，语序用户声明变量、逻辑控制语句及其他强大的编程功能，能实现某种功能的SQL程序，它是数据库应用比较广泛的一种数据对象。

存储过程的的有点：

01：模块化程序设计02：执行速度快，效率高03：减少网络流量04具有良好的安全性

系统存储过程一般以’sp_’开头，并存放在Resource数据库中。

扩展存储过程的名称通常以’xp_’开头 使用编程语言存储的外部存储过程 以DLL形式单独存在。

用户自定义存储过程：用户自己创建的存储过程。

用Alter  procedure 存储过程名可以不用删除直接修改原存储过程。

分页存储过程

Select  top  5  *  from  Student  where  Student  not  in  (select  top  5  studentNO  from  Student  order by  StudentNO )  order  by  StudentNO 

因双TOP双Order by语句TOP后不能更参数所以只能使用

Select * from (Select *,row_numnber() over(order by StudentNO)  as  id  from  Student) as temp where id between  6 and 10.

Create  procedure  usp_Paging

@currentPage  int,

@PageCount  int

As

Select * from (Select *,row_numnber() over(order by StudentNO)  as  id  from  Student) as temp  where  id  between  (@currentPage-1)*@PageCount+1  and  @currentPage*@PageCount 

Execute  usp_Paging  5, 5

--查询某个日期，只取其年月日值进行对比的方法：select Convert(varchar(7),getdate(),120)查询年、月、日、时、分、秒、毫秒是根据varchar的长度变化截取值的 。120是中国式时间书写方式。

优化数据库设计的总结,古老的榕树,5-wow.com