1、数据库原理重要知识点 一数据库系统概述1.数据库系统的模式结构 (1).三级模式结构：外模式（子模式，用户模式）对应于视图和基本表 模式（逻辑模式，概念模式）基本表 内模式（存储模式）存储文件 A.模式：数据库中全体数据逻辑结构和特征的描述，处于中间层。一个数据库只有一个模式，是数据库的整体逻辑。整个数据库的实际存储的抽象表示。 B.外模式：位于最外层。 一般是模式的子集，一个数据库可以有多个外模式 概念模式的某一部分的抽象表示。 C.内模式：最内内层。一个数据库只能有一个内模式。 是数据库的实际存储表示。 二.关系数据库 三.关系数据库的标准语言SQL.基本了解1.视图：是从一个或几个基本表
2、导出的的表是一个虚拟表。2.数据文件：存放数据库数据和对象的文件。 主数据文件：扩展名为“.mdf”。 次要数据文件：扩展名为“.ndf”。3.事务日志文件：记录数据库更新情况的文件，扩展名为“.ldf”。4.文件组：将多个数据文件集合起来形成的一个整体，每个文件组有一个组名。 日志文件不分组，它不属于任何文件组。.创建数据库1.格式： create database database_name on primary (指明数据文件或主文件组) ( name=logical_file_name,filename= os_file_name , ( D:/logical_file_name.md
3、f )size=size,maxsize=maxsize,filegrowth=growth_increment ) filegroup filegroup_name(name=logical_filename,filename= os_file_name ,size=size,maxsiaze=maxsize,filegrowth=growth_increment) log on (指明事务日志文件) collate collate_name (指明数据库使用的校验方式) for load / for attach2.修改数据库：alter database database_name(1)
4、.add file (name=logical_file_name,newname=new_logical_name,filename= os_file_name ,size=size,maxsize=maxsize,filegrowth=growth_increment) to filegroup filegroup_name (2).remove file logical_file_name(3).add filegroup filegroup_name(4).remove filegroup filegrouo_name(5).modify file (6).modify name=ne
5、w_dbname(7).collate collation_name (更改排序规则)Chinese_PRC_ ：对中国大陆地区简体字unicode的排序规则 ._BIN：二进制排序。 ._CI (CS)：是否区分大小写，CI不区分，CS 区分。 ._AI (AS)：是否区分重音，AI不区分，AS区 分。 ._KI (KS)：是否区分假名类型，KI不区分， KS区分。 ._WI (WS)：是否区分宽度，WI不区分，WS 区分。3.删除数据库：drop database database_name4.用系统存储过程查看数据库信息：(1).显示数据库结构： Sp_helpdb dbname= na
6、me 例：exec Sp_helpdb Northwind (2).显示文件信息：Sp_helpfile filename= name 例：exec Sp_helpfile orders(3).显示文件组信息：Sp_helpfilegroup filegroupname= name 例：use Northwindexec Sp_helpfilegroup .创建与使用数据表1.数据类型：(1).精确数值类型：numeric，decimal 例：numeric( P , S )numeric(8,3)P代表精度，S表示小数位数(2).近似浮点数类型：float，real. 例：1.21E+10(
7、3).日期时间类型：datetime，smalldatetime 例：1998-06-08 15:30:00(4).货币类型：money，smallmoney(5).sql_variant 数据类型可以存储除文本，图形数据和timestamp 类型数据以外的其它任何合法的SQL Server数据。(6).table数据类型用于存储对表或视图处理后的结果集。2.创建数据表：create table ( default 列约束 ) 创建临时表：Create table #table_name( default 列约束 )3.定义数据表的约束(1).数据库完整性机制：约束，默认（default），规
8、则（rule）,触发器（trigger）, 存储过程（procedure）。(2).完整性约束的基本语法格式： constraint (3).五种类型的完整性约束：null / not null格式： constraint null | not null .unique 约束(唯一约束)：指明基本表在某一列或多个列的 组合上的取值必须唯一。系统自动为唯一键建立唯一索引，唯一键允许为空， 为保证系统的唯一性，最多只可出现一个null值。格式：A.列约束： constraint uniqueB.表约束： constraint unique( 列名).primary key ( 主键约束 )：定义基
9、本表的主键，起唯一标识 作用，其值不能为null，也不能重复。 通过建立唯一索引来保证基本表在主键列取值的唯 一性。与unique约束的区别：a.在一个基本表中只能定义一个primary key约 束，但可以定义多个unique约束。b.对于指定的primary key的一个列或多个列的 组合，其中任何一个列都不能出现null值，而 对于unique所约束的唯一键。则允许null值。c.不能为同一个列或一组列既定义unique约束， 又定义primary key约束。格式：定义列约束： constraint primary key定义表约束：即将某些列的组合定义为主键。 constraint
10、primary key() .foreign key (外键约束)：指定某一个列或一组列作为外部键。A.从表：包含外部键的表。B.主表：包含外部键所引用的主键或唯一键的表称 为主表。C.系统保证从表在外部键上的取值是主表中某一个 主键值或唯一键值，或者取空值，以保证两表之 间的连接，确保了实体的参照完整性。格式： constraint foreign key references().check约束：用来检查字段值所允许的范围，以此保证域的完整性。格式： constraint check ( )例：建立一张包含完整性定义的学生表S。 create table S(SNo char(6) con
11、straint S_Prim primary key,SN char(20) constraint SN_Cons not null,Sex char(2) constraint Sex_Cons not null default 男,Age int constraint Age_Cons not null constraint Age_Chk check (Age between 15 and 50),Dept char(10) constraint Dept_Cons not null)(4).删除约束格式：Alter table table_nameDrop constraint con
12、straint_name(5).修改约束格式：Alter table table_nameAdd constraint clustered 排序方式 Alter column Drop constraint (6).禁止使用约束：就是禁止使用在现有数据上的约束检查。当向表中添加约束时，也可以禁用对现有数据的约束检查。例：创建如下约束就可以实现禁用检查约束Alter table ScoreWith nocheckAdd constraint CK_final112 check ( ( final=0 and final=100 ) )go 在加载新数据时禁用约束检查。可以禁用在现有的check和
13、foreign key约束上的约束检查。以下情况禁止使用约束检查：已经确定数据与约束一致。想载入与约束不一致的数据，载入后可以执行查询来改变数 据，然后使约束重新有效。4.规则 ：一种数据库对象，属于逐步取消的数据库完整性手段。 (1).创建规则：格式：Create rule rule_nameAs condition_expression (定义规则条件) 例：Create rule score_ruleAs score between 0 and 100(2).绑定规则：格式：sp_bindrule rulename= rule , 创建了的规则名称 objname= object_nam
14、e 绑定了规则的列或自定义 , futureonly= futureonly_flag 仅将规则绑定到自 定义时才使用例：将score_rule规则绑定到score表的usually列上。Exec sp_bindrule score_rule , score.usually(3).解除列上绑定的规则：格式：Sp_unbindrule objname= object_name 要解除的规则 , futureonly= futureonly_flag 仅用于解除自定义例：解除绑定到score表的usually的规则。Exec sp_unbindrule score . usually(4).删除规
15、则：格式：Drop rule rule_name 5.默认值：是一种数据库对象，属于逐步取消的数据的完整性手段。(1).创建默认值create default ：格式：Create default default_nameAs constant_expression 只包含常量表达式例：Create default type_defaultAs 必修(2).利用存储过程( sp_bindefault )绑定默认值：格式：Sp_bindefault defname= default objname= object_name , futureonly= futureonly_flag 例：将typ
16、e_default默认值绑定到course表的type列上Exec sp_bindefault type_default , course . type(3).解除默认值对象的绑定：格式：Sp_unbindefault objname= object_name , futureony= futureonly_flag例：Exec sp_unbindefault course . type(4).删除默认值对象：格式：Drop default default_name(4).修改数据表1.三种修改方式：(1). add 方式：用于增加新列和完整性约束。格式：Alter table table_n
17、ameAdd | 注意：使用此方式增加的新列自动填充null值，所以不能为增加的 新列指定not null(2).alter 方式：用于修改某些列。格式：Alter table table_nameAlter column null | not null 注意：.不能改变列名。 .不能将含有空值的列的定义修改为not null 约束。 .若列中已有数据，则不能减少该列的宽度，也不能改变其数 据类型。 .只能修改null / not null 约束，其它类型的约束在修改前必 须先将约束删除，然后再重新添加修改过的约束定义。(3).drop方式：只用于删除完整性约束定义。格式：Alter tabl
18、e Drop constraint Column column_name /删除列 2.删除基本表 格式： Drop table table_name 注意：只能删除自己建立的表，不能删除别人建立的表(5).视图和索引及统计信息及其应用索引：1.索引的作用：加快查询速度，保证行的唯一性。是针对一个表建立的。优点：加速数据检查：加速连接、排序和分组：优化查询：强制实行行的唯一性：2.索引的分类：A.聚集索引与非聚集索引：.聚集索引：按照索引的字段排列记录，并且按照排好的顺 序将记录存储在表中。适合范围查找。.非聚集索引：按照索引的字段排列记录，但排列的结果不 会存储在表中，而是存储在另外的位置。
19、在检索记录时，聚集索引会比非聚集索引速度快。一个表中只能有一个聚集索引，而非聚集索引可以有多个。B.唯一索引：表示表中每一个索引值只对应唯一的数据记录。常用在 primary key 字段上，以区别每一个记录。 当表中有被设置为unique的字段时，系统自动建立一个非聚集索引。 当表中有被设置为primary key的字段时，系统自动建立一个聚集索引。 C.复合索引：将两个字段或多个字段组合起来建立的索引，单独的字段 允许有重复的值。3.创建索引的原则：应避免对经常更新的表创建大量的索引。若表的数据量大且更新少而查询较多时可以创建多个索引提高性能。当视图包含聚合、表连接或两者的组合时，在视图上
20、创建索引。可以对唯一列或非空列创建聚集索引每个表只能创建一个索引在包含大量重复值的列上创建索引，查询时间会较长若查询语句中存在计算列，则可考虑对计算列值创建索引索引大小限制，最大键列数位16，最大索引键大小为900字节。4.创建索引：create index 格式： Create unique (唯一) cluster (聚集) | nonclustered(非聚集) index On ( ASC (升序) DESC (降序) ASC (默认) on filegroup_name / 在给定的文件组上创建索引 Default / 在默认的文件至上创建索引5.查看与修改索引：(1).Sp_hel
21、pindx 存储过程可以返回表中的所有索引信息格式：Sp_helpindex objname =name(2).sp_help:用于显示数据库对象或数据类型的基本信息。格式：Sp_help objname= name (3)Sp_rename 存储过程可以更改索引名称。 格式：Sp_helpindex 数据表名.原索引名，新索引名(4).修改索引：格式：Alter index index_name | all On / 索引所基于的表或视图的名称Rebulid /索引重建 disable / 将索引标记为已禁用 | reorganize / 索引重组| set ( ) / 指定不重新生成或重新
22、组织索引的索引选项。 / 不能为已禁用的索引指定set5.索引重组：格式：Alter index index_name | all on objectReorganize partition=partition_number /指定只重新生成或重组索引 的一个分区 with ( lob_compaction )= on | off ) /压缩所有包含大型对象的页例：重组teaching数据库中student表上的IDX_sc索引Alter index IDX_sc on dbo.studentreorganize6.索引重建：将删除已存在的索引并创建一个新索引。 格式：Alter index
23、index_name | all On Rebulid /对于填充因子等索引选项的重新设置。 with ( , n ) | partition=partition_number /指定只重新生成(组成)的一个分区 with ( ,n ) /单个分区重建索引参数 例：重新设置teaching数据库student表上的ID_sname索引，设置填充 因子，将填充因子设置为80%，设置中间排序结果存储在tempdb中。Alter index IDX_sname on dbo.studentRebulidWith ( pad_index=on , fillfactor=80% , sort_in_te
24、mpdb=on )使用带drop_existing子句的create index 语句重建索引例：重新生成teaching数据库中student表上的C_studentno索引，将 其修改为聚集唯一索引，并指定该索引的叶级页的填充度为 70%。Create unique clustered index C_studentnoOn dbo.student(studentno)With(pad_index=on,fillfactor=70,drop_existing=on)7.删除索引：drop index 格式：Drop index 数据表名.索引名注意：该命令不能删除有create table
25、 或alter table 命令创建的 primary key或unique约束索引，也不能删除系统表中的索 引。 .视图1.概念：视图是从一个或多个表及其他视图中通过select语句导出的虚拟 表。2.优点：简化数据查询和处理：视图可以为用户集中数据。屏蔽数据库的复杂性。安全性。 保证数据的逻辑独立性。3.创建视图：create view格式：Create view ( ) with encryption / 对create view 语句文本的条目进行加密。 | schemabinding /将视图绑定到基础表的架构上。 | view_metadata /返回有关视图的元数据信息。As w
26、ith check option 注：在子查询中不许使用order by 子句和distinct 短语，如需 排列则可在视图定以后，对视图查询时再进行排序。例：在teaching数据库中创建一个名为v_course的视图，包含所有类别 为“必修”的信息。Create view v_courseAs Select *From courseWhere type=必修注：在创建视图的select查询语句中包含order by子句时，在 select语句的选择列表中必须包含top子句。例：在teaching数据库中创建一个名为v_final的视图，包含学生学号、 姓名、课程号、课程名和期末成绩，按学号
27、升序排列，相同学号的 记录按课程号升序排列。Create view v_finalAs Select top(100)percent student.SNo,student.SN, course.CNo,course.CN,score.finalfrom student ,course,scorewhere student.SNo=score.SNo and course.CNo=score.CNo order by student.SNo,course.CNo注：在一般情况下，不必再视图创建时指定列名，SQL Server使视图中的列于定 义视图的查询所引用的列具有相同的名称和数据类型。但是在有些情况下必须 指定列名，包括：视图中的任何列都是从算数表达式、内置函数或常量派生而来。视图中有两列或多列原应具有相同的名称。希望为视图中的列指定一个与其原列不同的名称。例：在teaching数据库中创建一个名为v_max的视图，查询每个班最高分的课程 名和分数，按班级号升序排列create view v_maxas select top
