数据库实验总结范文.docx
- 文档编号:10423237
- 上传时间:2023-02-11
- 格式:DOCX
- 页数:10
- 大小:18.70KB
数据库实验总结范文.docx
《数据库实验总结范文.docx》由会员分享,可在线阅读,更多相关《数据库实验总结范文.docx(10页珍藏版)》请在冰豆网上搜索。
数据库实验总结范文
数据库实验总结范文
试验内容
1、数据表的建立
基本表《简单的》带有主键
带有外码约束的(外码来自其他表或者本表)
2、数据表的修改
添加删除列
修改列属性类型
添加删除约束(约束名)
元组的添加,修改,删除
删除数据表
试验过程
1、createtablestudent
(
snochar(9)primarykey,/*sno是主码列级完整性约束条件*/
snamechar(20)unique,/*sname取唯一值*/
ssexchar
(2),
sagesmallint,/*类型为smallint*/
sdeptchar(20)/*所在系*/
);
createtablecourse
(
ochar(4)primarykey,/*列级完整性约束条件,o是主码*/
amechar(40),
cpnochar(4),/*cpno的含义是先行课*/
reditsmallint,
foreignkey(cpno)referencescourse(o)
/*表级完整性约束条件,cpno是外码,被参照表是course,被参照列是o*/
);
createtablesc
(
snochar(9),
ochar(4),
gradesmallint,
primarykey(sno,o),
/*主码有两个属性构成,必须作为表级完整性进行定义*/
foreignkey(sno)referencesstudent(sno),
/*表级完整性约束条件,sno是外码,被参照表是student*/
foreignkey(o)referencescourse(o),
/*表级完整性约束条件,o是外码,被参照表示course*/
);
例1、createtables
(
ovarchar(3),/*变长的字符串,输入2个字符就是两个字符不会补空格*/
snamevarchar(20),
statusint,
cityvarchar(20),
constraintpk_snoprimarykey(sno),/*约束条件的名字为pk_sno*/
);
createtablep
(
pnovarchar(3),
pnamevarchar(20),
colorvarchar(3),
weightint,
constraintpk_pnoprimarykey(pno),/*约束条件的名字是pk_pno*/
);
createtablej
(
jnovarchar(3),
jnamevarchar(20),
cityvarchar(20),
constraintpk_jnoprimarykey(jno)/*约束条件的名字为pk_jno*/
);
例2、createtablespj
(
snovarchar(3),/*第一个表中的主码*/
pnovarchar(3),
jnovarchar(3),
qtyint,/*数量*/
constraintpk_spjprimarykey(sno,pno,jno),/*主码由3个属性组成*/
foreignkey(sno)referencess(sno),
/*表级完整性约束条件,sno是外码,被参照表是s*/
foreignkey(pno)referencesp(pno),
/*表级完整性约束条件,pno是外码,被参照表是p*/
foreignkey(jno)referencesj(jno),
/*表级完整性约束条件,jno是外码,被参照表是j*/
);
2、数据表的更改
在s表中添加一个concat列
altertablesaddconcatvarchar(20)
在s表中删除concat列
altertablesdropcolumnconcat
更改s表concat列的属性把长度由20改为30
altertablesaltercolumnconcatvarchar(30)
联系方式名字为concat修改属性为唯一的属性名为con_concat
altertablesaddconstraintcon_concatunique(concat)
删除约束关系con_concat
altertablesdropconstraintcon_concat
/*插入一个元组*/
insertintosvalus(‘s1’,’精益’,20,’天津’)/*20不能写成’20’*/
试验中的问题的排除与总结:
1、在创建spj时
有三个实体所以从3个实体中取主码,还有一个数量属性也要写上
主码由那3个主码确定
2、更改一个数据库中数据表时一定要先使该数据库处于正在使用状态
3、constraint
是可选关键字,表示primarykey、notnull、unique、foreignkey或check约束定义的开始。
约束是特殊属性,用于强制数据完整性并可以为表及其列创建索引。
4、--go可以不加但是要注意顺序注:
go--注释提示错误
5、注意添加一个空元素用null
附sql备份
--创建一个数据库student
createdatabasestudent
go
--在数据库student中创建表studentcoursesc注意顺序
usestudent
----------------------------------------------------------------
createtablestudent
(
snochar(9)primarykey,/*sno是主码列级完整性约束条件*/
snamechar(10)unique,/*sname取唯一值*/
ssexchar
(2),
sagesmallint,/*类型为smallint*/
sdeptchar(20)/*所在系*/
);/*;要加*/
-----------
我在sqlserver索引基础知识系列中,第一篇就讲了记录数据的基本格式。
那里主要讲解的是,数据库的最小读存单元:
数据页。
一个数据页是8k大小。
对于数据库来说,它不会每次有一个数据页变化后,就存到硬盘。
而是变化达到一定数量级后才会作这个操作。
这时候,数据库并不是以数据页来作为操作单元,而是以64k的数据(8个数据页,一个区)作为操作单元。
区是管理空间的基本单位。
一个区是八个物理上连续的页(即64kb)。
这意味着sqlserver数据库中每mb有16个区。
为了使空间分配更有效,sqlserver不会将所有区分配给包含少量数据的表。
sqlserver有两种类型的区:
统一区,由单个对象所有。
区中的所有8页只能由所属对象使用。
混合区,最多可由八个对象共享。
区中八页的每页可由不同的对象所有。
通常从混合区向新表或索引分配页。
当表或索引增长到8页时,将变成使用统一区进行后续分配。
如果对现有表创建索引,并且该表包含的行足以在索引中生成8页,则对该索引的所有分配都使用统一区进行。
为何会这样呢?
其实很简单:
读或写8kb的时间与读或写64kb的时间几乎相同。
在8kb到64kb范围之内,单个磁盘i/o传输操作所花的时间主要是磁盘取数臂和读/写磁头运动的时间。
因此,从数学上来讲,当需要传输64kb以上的sql数据时,
尽可能地执行64kb磁盘传输是有益的,即分成数个64k的操作。
因为64kb传输基本上与8kb传输一样快,而每次传输的sqlserver数据是8kb传输的8倍。
我们通过一个实例来看有and操作符时候的最常见的一种情况。
我们有下面一个表,
createtable[dbo].[member]([member_no][dbo].[numeric_id]identity(1,1)notnull,[lastname][dbo].[shortstring]notnull,[firstname][dbo].[shortstring]notnull,[middleinitial][dbo].[letter]null,[street][dbo].[shortstring]notnull,[city][dbo].[shortstring]notnull,[state_prov][dbo].[statecode]notnull,[country][dbo].[countrycode]notnull,[mail_code][dbo].[mailcode]notnull,[phone_no][dbo].[phonenumber]null,[photograph][image]null,[issue_dt][datetime]notnulldefault(getdate()),[expr_dt][datetime]notnulldefault(dateadd(year,1,getdate())),[region_no][dbo].[numeric_id]notnull,[corp_no][dbo].[numeric_id]null,[prev_balance][money]nulldefault(0),[curr_balance][money]nulldefault(0),[member_code][dbo].[status_code]notnulldefault(''))
这个表具备下面的四个索引:
索引名细节索引的列
member_corporation_linknonclusteredlocatedonprimarycorp_no
member_identclustered,unique,primarykeylocatedonprimarymember_no
member_region_linknonclusteredlocatedonprimaryregion_no
memberfirstnamenonclusteredlocatedonprimaryfirstname
当我们执行下面的sql查询时候,
selectm.member_no,m.firstname,m.region_nofromdbo.memberasmwherem.firstnamelike'k%'andm.region_no>6andm.member_no<5000go
sqlserver会根据索引方式,优化成下面方式来执行。
selecta.member_no,a.firstname,b.region_nofrom(selectm.member_no,m.firstnamefromdbo.memberasmwherem.firstnamelike'k%'andm.member_no<5000)a,--这个查询可以直接使用memberfirstname非聚集索引,而且这个非聚集索引覆盖了所有查询列--实际执行时,只需要逻辑读取3次
(selectm.member_no,m.region_nofromdbo.memberasmwherem.region_no>6)b
--这个查询可以直接使用member_region_link非聚集索引,而且这个非聚集索引覆盖了所有查询列--实际执行时,只需要逻辑读取10次
wherea.member_no=b.member_no
不信,你可以看这两个sql的执行计划,以及逻辑读信息,都是一样的。
其实上面的sql,如果优化成下面的方式,实际的逻辑读消耗也是一样的。
为何sqlserver不会优化成下面的方式。
是因为and操作符优化的另外一个原则。
1/26的数据和1/6的数据找交集的速度要比1/52的数据和1/3的数据找交集速度要慢。
selecta.member_no,a.firstname,b.region_nofrom(selectm.member_no,m.firstnamefromdbo.memberasmwherem.firstnamelike'k%'--1/26数据)a,
(selectm.member_no,m.region_nofromdbo.memberasmwherem.region_no>6andm.member_no
当然,我们要学习sql如何优化的话,就会用到查询语句中的一个功能,指定查询使用哪个索引来进行。
比如下面的查询语句
selectm.member_no,m.firstname,m.region_nofromdbo.memberasmwith(index(0))wherem.firstnamelike'k%'andm.region_no>6andm.member_no<5000go
selectm.member_no,m.firstname,m.region_nofromdbo.memberasmwith(index
(1))wherem.firstnamelike'k%'andm.region_no>6andm.member_no6andm.member_no6andm.member_no<5000go
这里index计算符可以是0,1,指定的一个或者多个索引名字。
对于0,1的意义如下:
如果存在聚集索引,则index(0)强制执行聚集索引扫描,index
(1)强制执行聚集索引扫描或查找(使用性能最高的一种)。
如果不存在聚集索引,则index(0)强制执行表扫描,index
(1)被解释为错误。
总结知识点:
简单来说,我们可以这么理解:
sqlserver对于每一条查询语句。
会根据实际索引情况(sysindexes系统表中存储这些信息),分析每种组合可能的成本。
然后选择它认为成本最小的一种。
作为它实际执行的计划。
成本代价计算的一个主要组成部分是逻辑i/o的数量,特别是对于单表的查询。
and操作要满足所有条件,这样,经常会要求对几个数据集作交集。
数据集越小,数据集的交集计算越节省成本。
的项目中,竟然出现了滥用聚集索引的问题。
看来没有培训最最基础的索引的意义,代价,使用场景,是一个非常大的失误。
这篇博客就是从这个角度来罗列索引的基础知识。
使用索引的意义
索引在数据库中的作用类似于目录在书籍中的作用,用来提高查找信息的速度。
使用索引查找数据,无需对整表进行扫描,可以快速找到所需数据。
使用索引的代价
索引需要占用数据表以外的物理存储空间。
创建索引和维护索引要花费一定的时间。
当对表进行更新操作时,索引需要被重建,这样降低了数据的维护速度。
创建索引的列
主键
外键或在表联接操作中经常用到的列
在经常查询的字段上最好建立索引
不创建索引的列
很少在查询中被引用
包含较少的惟一值
定义为text、ntext或者image数据类型的列
heaps是stagingdata的很好选择,当它没有任何index时
excellentforhighperformancedataloading(parallelbulkloadandparallelindexcreationafterload)
excellentasapartitiontoapartitionedvieworapartitionedtable
聚集索引提高性能的方法,在前面几篇博客中分别提到过,下面只是一个简单的大纲,细节请参看前面几篇博客。
何时创建聚集索引?
clusteredindex会提高大多数table的性能,尤其是当它满足以下条件时:
独特,狭窄,静止:
最重要的条件
持续增长的,最好是只向上增加。
例如:
identity
date,identity
guid(onlywhenusingnewsequentialid()function)
聚集索引唯一性(独特型的问题)
由于聚集索引的b+树结构的叶子节点必须指向具体数据。
如果你要建立聚集索引的列不唯一,并且你指定的创建的聚集索引是非唯一的聚集索引,则会有以下情况:
如果未使用unique属性创建聚集索引,数据库引擎将向表自动添加一个四字节uniqueifier列。
必要时,数据库引擎将向行自动添加一个uniqueifier值,使每个键唯一。
此列和列值供内部使用,用户不能查看或访问。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 总结 范文