数据库整理进化.docx

数据库整理进化.docx

《数据库整理进化.docx》由会员分享，可在线阅读，更多相关《数据库整理进化.docx（8页珍藏版）》请在冰豆网上搜索。

数据库整理进化

流程控制语句：

通过判断指定的某些值来控制程序运行方向的语句

begin…end：

设定一程序块

if…else：

判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。

case：

多分支语句

casecolor

when'yellow'then'yellowcolor'

when'blue'then'bluecolor'

else'expensive!

'

end

case

whenlistpriceisnullthen'notyetpriced'

whenlistprice<10then'veryreasonabletitle'

else'expensive!

'

end

while…continue…break循环语句

物理结构主要数据文件（.mdf）次要数据文件（.ndf）日志文件（.ldf）

逻辑结构数据表、视图、索引、数据类型、存储过程、触发器

文件组是数据库中数据文件的逻辑组合（不含日志文件）用途：

可以实现将数据分布在多个磁盘上并在查询过程中使用并行线程同时扫描多个磁盘加快查询速度改善系统性能

数据文件由若干个64kb的区组成，每个区由8个8kb大小的连续空间组成，被称为数据页（统一区混合区）

事务日志作用重新执行事务完成未完成事务数据库回滚之前的时间状态复制事务（服务器）（ps：

使数据库操作具有事务功能）特点个数1~n，分配速度比数据文件快对事务吞吐影响量小管理开销小

数据库类型：

（1）系统数据库（master：

记录所有系统级信息）（model：

为用户创建数据库提供模板）（msdb：

sqlserver代理服务使用的数据库，供代理程序调度警报作业、记录操作员操作）（tempdb保存所有临时表、临时存储进程）

（2）用户数据库

创建数据库

createdatabasesales_1

onprimary

（name=sales_1_data,

filename='d:

\sales_1.mdf',

size=10,

maxsize=unlimited,

filegrowth=20%）

logon

（name=sales_1_log,

filename='d:

\sales_1_log.ldf',

size=3,

maxsize=10,

filegrowth=2

alterdatabase语句修改数据库

add;remove;modify；

若要将数据文件或日志文件移至新位置，

usestudent

go

alterdatabasestudent

modifyfile（name=logical_file_name,filename='new_path/os_file_name'）

修改数据文件或日志文件的逻辑名称：

modifyfile（name=logical_file_name,newname=new_logical_name）

创建数据表步骤

（1）定义表结构（关键）

（2）设置约束

createtable表名（列名1数据类型,列名2数据类型，列名3数据类型）常用数据类型：

整型int日期型：

datetime不能设置长度;浮点型decimal（长度,小数）numeric（长度,小数）;字符型char（10）varchar（10）

用altertable的add子句向表中

altertable表名

添加列：

add列名数据类型

删除列：

drop列名数据类型

使用insert语句插入数据

insertinto表名（列名1，列名2，列名3）

values（'1001','王晓明',15,'20070101'）

使用update语句更新数据

update表名setcard_name='wanghong',card_credit=20

wherecard_id='1001'

使用delete语句删除数据

deletefrom表名wherecard_id='1001'

数据完整性（一致性正确性）

（1）实体完整性（行完整性）数据表中所有行都是唯一、确定、可区分的，主键、主要属性不能取空值

（2）参照完整性：

保证参照表与被参照表中数据的一致性（3）域完整性（列完整性）指定列数据具有正确数据类型、格式、取值范围，以确保数据正确性（4）用户定义完整性：

允许用户定义不属于其他完整性分类的特定规则

约束

（1）check约束→域完整性：

限制输入到一列或多列的值的范围

（2）default约束：

如果没有为列指定数据，系统将默认值赋给列（3）primarykey约束（主键约束）强制表的实体完整性（4）foreignkey约束（外键约束）定义表之间的参照完整性是先定义主表的主键，再对从表定义外键约束，保证数据一致性。

（5）unique约束：

确保表中的两个数据行在非主键列中没有相同的列值

区分primarykey与unique

（1）都强调唯一性，但一列或多列（非主键）用unique

（2）可对一个表定义多个unique约束，只可定义一个primarykey约束

规则：

向数据表中某列插入数据时，指定该列接受数据值的范围。

（规则≈check）（默认≈default）

数据查询：

select

from

where

groupbyhaving

orderby

（1）distinct丢弃select指定列的重复记录；all保留结果表中所有的行；

（2）into语句可以在查询数据的基础上创建新的数据表。

（3）between下限and上限

（4）in（‘’‘’‘’）

（5）'%'任意长度字符''单个长度字符

（6）isnull／isnotnull

（7）like字符串匹配

（8）substring（列名，开始位数，持续位数）=''and''

orderby按照排序列名进行排序（升序：

asc默认降序：

desc）

内连接:

用比较运算符比较两个表中列值，将两个表中满足连接条件的行组合起来作为结果。

等值连接:

连接条件：

等号；select列表：

（*）结果集：

显示冗余列数据

在where子句中定义连接

select*

from表名1，表名2

where表名1.列名1=表名2.列名2

orderby列名3

在from子句中定义连接

select*

from表名1innerjoin表名2on表名1.列名1=表名2.列名2

where条件

orderby列名3

不等值连接：

连接条件：

除等号以外比较运算符

select表名1.列名1，表名2.列名2

from表名1innerjoin表名2on表名1.列名1>表名2.列名2

where条件

orderby列名3

自然连接：

对结果集的冗余列数据进行限制的连接。

视图：

基于某个查询结果的一个虚拟表，用来查看数据的窗口，数据库中只存放视图的定义（select语句）语句结果构成视图。

（视图名+视图定义）

视图优点：

数据保密、简化数据查询操作、保证数据的逻辑独立性。

缺点：

当更新视图中的数据时，实际上是对基表的数据进行更新。

某些视图是不能更新数据的（union等集合操作符、groupby子句、函数、distinct、连接表的视图）

视图功能：

（1）将用户限定在数据表中的特定行上

（2）将用户限定在特定行上（3）将多个表中的列连接起来（4）提供聚合信息而非详细信息

创建视图

Usestudent

Go

Select*from表名

Go

createview视图名

as

Select内容From表名Where条件

go

索引作用：

快速存取数据；保证数据的一致性；实现参照完整性；使用groupby、orderby子句进行查询时，可以减少排序和分组的时间。

索引：

以数据表的列为基础建立的数据库对象，保留表中排序的索引列，并记录索引列在数据表的物理存储位置，实现了表中数据的逻辑排序。

优点：

提高查询表中数据的速度表盒表之间的连接加快缺点：

维护索引耗费时间索引占用物理空间

建立索引思路：

（1）定义在主键的列

（2）定义在外键的列（3）经常查询的列（4）很少涉及到重复值多→不要建立（5）数据类型text,ntext,image,bit→不要建立

聚集索引（clusteredindex）：

行的物理存储顺序与索引顺序完全相同。

非聚集索引（nonclusteredindex）不改变表中数据记录的物理存储顺序，索引仅仅记录指向表中行的位置的指针，数据记录与索引分开存储（b-tree）

区别：

建立聚集索引时，系统将对表中数据按列进行排序，表中数据记录的物理位置发生了移动。

因为行是经过排序的，所以每个表中只能有一个聚集索引。

聚集索引速度快，维护困难，搜索范围值时候有效。

唯一索引：

索引值必须唯一。

创建索引

CREATEINDEX索引类型索引名ON表名

过程：

独立放置且拥有不同功能的语句。

存储过程：

一组完成特定功能的t-sql语句集，经编译后以特定的名称存储在数据库中，用户通过指定存储过程的名字并给出参数来执行存储过程。

存储过程特点：

（1）允许模块化的程序设计

（2）更快的执行速度（3）有效降低网络流量（4）较好的安全机制

存储过程类型：

（1）系统存储过程：

主要存储在master数据库中并以sp-为前缀，主要从系统中获取信息

（2）本地存储过程：

用户自行创建并存储在用户数据库的存储过程（3）临时存储过程（本地临时存储过程：

以“＃”作为名称第一个字符，存放于temp数据库，只有创建并连接的用户能够执行；全局临时存储过程：

以“＃＃”开头，存放于temp数据库，任何用户都可以使用。

名称不能与其他用户重复）ps：

断开连接，临时存储过程都会删除（4）远程存储过裎（5）扩展存储过程

创建存储过程

创建不带参数的存储过程

CREATEPROCEDURE存储过程名ASselect*fromexam

执行存储过程方式：

直接打存储过程名；用EXECUTE存储过程名

创建带参数的存储过程

CREATEPROCEDURE存储过程名@变量名数据类型AS查询语句select*fromexamwheret_number=@tnum

该存储过程有3种方式：

例：

stu_pro2'20040301'

例：

executestu_pro2'20040301'

例：

executestu_pro2@tnum='20040301'

创建带参数且有返回值的存储过程

例：

CREATEPROCEDUREstu_pro3@tnumchar（8）,@cnumchar（6）,@gradechar（10）output

ASdeclare@errorvalueint

set@errorvalue=0

select@grade=t_gradefromexamwheret_number=@tnumandc_number=@cnum

if（@@ERROR<>0）

set@errorvalue=@@ERROR

return@errorvalue

执行：

declare@returnvalueint,@tnumchar（8）,@cnumchar（6）,@gradechar（10）

select@tnum=20040301,@cnum=100101

execute@returnvalue=stu_pro3@tnum,@cnum,@gradeoutput

select@returnvalueasa

select@gradeasb

触发器：

一种存储过程，基表被修改时自动执行的内嵌过程，主要通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。

触发器主要作用：

能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。

优点：

（1）check约束有更复杂的数据完整性，可以引用其他表中的列来完成数据完整性约束

（2）使用自定义错误信息（3）实现数据库中多张表的级联修改（4）比较数据库修改前后数据的状态

创建触发器

创建INSERT触发器

CREATETRIGGER触发器名ON表名/视图名AFTERINSERTAS触发内容

例：

创建在exam表中当输入分数大于100就拒绝这条记录插入

createtriggerinsert_dataonexamafterinsertas

if（selectt_gradefrominserted）>100

begin

print'notover100'

rollback

end

事务：

由一系列的数据查询操作或更新操作构成的（原子性）

事务特性：

（1）原子性：

一个事务中的所有操作是一个逻辑上的不可分割的单位

（2）一致性：

数据库中的数据满足各种完成性规则（3）隔离性：

一个事务的修改必须与其他事务的修改隔离（4）持久性：

事务完成后其对数据库的修改将永久保存

事务控制语句：

（1）使用begintransaction建立事务

（2）使用committransaction标识事务结束（3）使用commitwork语句标识事务结束（4）使用rollbacktransaction回滚到事务的指定点（5）使用rollbackwork回滚到事务的起点（6）savetransaction设置保存点

事务管理模式：

（1）显式事务：

由用户在其中定义事务的启动和结束

（2）隐式事务:

在当前事务提交或回滚后，自动启动新事务（3）自动事务模式（默认）每个sql语句在成功执行完成后，都被自动提交；如果遇到错误，则自动回滚该语句

事务日志内容：

各个事务的开始标记、结束标记、所有更新操作

事务日志：

数据库中已发生的所有修改和执行每次修改的事务的一连串记录

原则：

（1）事务次序严格按并发事务执行的时间次序

（2）必须先写事务日志文件，后写数据库文件

游标：

一种数据访问机制，为了查看或者处理结果集中的数据，允许访问单独行，提供了在结果集中向前或者向后浏览数据的能力。

优点：

（1）允许应用程序对由查询语句返回的结果集中的每一行执行相同或不同的操作

（2）提供对基于游标位置的表中的行进行删除和更新的能力（3）面向集合的数据库管理系统和面向行的程序设计通过游标沟通起来

步骤：

声明或创建游标→打开游标→推进游标指针从游标的结果集中提取数据→逐行处理操作游标指针所指向的行数据→关闭和释放游标

备份：

在某种介质上存储数据库的拷贝

还原：

利用数据库的备份，将数据库返回到备份时的状态

数据库完整备份：

对数据库内的所有对象都进行备份

数据库差异备份：

只备份自从上次数据库全部备份后数据库变动的部分。

事务日志备份：

只备份数据库的事务处理记录（必须和一次完整备份一起才可以恢复数据库，而且有时间顺序）

文件及文件组备份：

针对单一数据库文件或者是文件夹做备份和恢复（必须搭配事务日志备份ps一致性）优点：

便利，有弹性，可以仅针对受损的数据库文件做恢复

还原方式

（1）简单还原模型

（2）完整还原模型（3）大容量日志记录还原