数据库试验总结

1、数据库实验总结数据库实验总结【一】试验内容1、数据表的建立基本表简单的带有主键带有外码约束的(外码来自其他表或者本表)2、数据表的修改添加删除列修改列属性类型添加删除约束(约束名)元组的添加，修改，删除删除数据表试验过程1、create table student(sno char(9) primary key , /*sno 是主码 列级完整性约束 条件*/sname char(20) unique, /*sname取唯一值 */ssex char(2),sage smallint, /*类型为 smallint*/sdept char(20) /*所在系 */);create table

2、course(cno char(4) primary key, /*列级完整性约束条件，cno是主码*/cname char(40),cpno char(4), /*cpno的含义是先行课*/ccredit smallint,foreign key (cpno) references course(cno)/*表级完整性约束条件，cpno是外码，被参照表是 course , 被参照列是cno*/);create table sc(sno char(9),cno char(4),grade smallint,primary key (sno,cno),/*主码有两个属性构成，必须作为表级完整性进

3、行定义*/foreign key (sno) references student(sno),/*表级完整性约束条件，sno是外码，被参照表是student*/foreign key (cno) references course(cno),/*表级完整性约束条件,cno是外码,被参照表示course*/);例 1、create table s(cno varchar(3), /* 变长的字符串,输入2个字符就是两个字符不会补空格*/sname varchar(20),status int,city varchar(20),constraint pk_sno primary key(sno),

4、 /*为 pk_sno*/);create table p(pno varchar(3),pname varchar(20),color varchar(3),weight int,constraint pk_pno primary key (pno), /* 是 pk_pno*/);create table j(jno varchar(3),jname varchar(20),city varchar(20),constraint pk_jno primary key(jno) /*约束条件的名字约束条件的名字约束条件的名字为pk_jno*/);例 2、create table spj(sn

5、o varchar(3), /*第一个表中的主码*/pno varchar(3),jno varchar(3),qty int, /*数量*/constraint pk_spj primary key(sno,pno,jno), /*主码由3个属性组成*/foreign key(sno) references s(sno),/*表级完整性约束条件，sno是外码，被参照表是s*/foreign key(pno) references p(pno),/*表级完整性约束条件，pno是外码，被参照表是p*/foreign key(jno) references j(jno),/*表级完整性约束条件，j

6、no是外码，被参照表是j*/);2、数据表的更改在s表中添加一个 concat列alter table s add concat varchar(20)在s表中删除concat列alter table s drop column concat更改s表concat列的属性 把长度由20改为30alter table s alter column concat varchar(30)联系方式名字为concat修改属性为唯一的属性名为con_concatalter table s add constraint con_concat unique(concat)删除约束关系 con_concatalt

7、er table s drop constraint con_concat/*插入一个元组*/insert into s valus( si,精益,20,天津)/*20不能写成20 */试验中的问题的排除与总结：1、在创建spj时有三个实体所以从 3个实体中取主码，还有一个数量属性也 要写上主码由那3个主码确定2、更改一个数据库中数据表时一定要先使该数据库处于正在 使用状态3、constraint是可选关键字，表示 primary key、not null unique foreign key或check约束定义的开始。约束是特殊属性，用于强制数据 完整性并可以为表及其列创建索引。4、-go可

8、以不加但是要注意顺序注：go -注释提示错误5、注意添加一个空元素用 null附sql备份-创建一个数据库studentcreate database studentgo-在数据库student中创建表student course sc注意顺序use studentcreate table student(sno char(9) primary key, /*sno 是主码 列级完整性约束条件*/sname char(10) unique, /*sname取唯一值 */ssex char(2),sage smallint, /*类型为 smallint*/sdept char(20) /*所在

9、系 */);/*;要加*/数据库实验总结【二】我在sql server索引基础知识系列中，第一篇就讲了记录数据的基本格式。那里主要讲解的是，数 据库的最小读存单元：数据页。一个数据页是8k大小。对于数据库来说，它不会每次有一个数据页变化后，就存到 硬盘。而是变化达到一定数量级后才会作这个操作。这时候，数据库并不是以数据页来作为操作单元，而是以64k的数据（8个数据页，一个区）作为操作单元。区是管理空间的基本单位。一个区是八个物理上连续的页（即64 kb）。这意味着sql server 数据库中每 mb有16个区。为了使空间分配更有效，sql server不会将所有区分配给包含少量数据的表。sq

10、l server有两种类型的区：统一区，由单个对象所有。区中的所有8页只能由所属对象使用。混合区，最多可由八个对象共享。区中八页的每页可由不同 的对象所有。通常从混合区向新表或索引分配页。当表或索引增长到8页时，将变成使用统一区进行后续分配。如果对现有表创建索引，并且该表包含的行足以在索引中生成8页，则对该索引的所有分配都使用统一区进行。为何会这样呢？其实很简单：读或写8kb的时间与读或写64 kb的时间几乎相同。在8 kb到64 kb范围之内，单个磁盘i/o 传输操作所花 的时间主要是磁盘取数臂和读/写磁头运动的时间。因此，从数学上来讲，当需要传输64 kb以上的sql数据时，尽可能地执行6

11、4 kb磁盘传输是有益的，即分成数个 64k的 操作。因为64 kb传输基本上与8 kb传输一样快，而每次传输的 sql server 数据是8 kb 传输的8倍。我们通过一个实例来看有and操作符时候的最常见的一种 情况。我们有下面一个表，createtabledbo.member(member_nodbo.numeric_id identity(1,1) not null, lastname dbo.shortstringnot null,firstnamedbo.shortstringnot null, middleinitialdbo.letter null, street dbo.s

12、hortstring not null, city dbo.shortstringnot null, state_provdbo.statecode not null, country dbo.countrycode not null, mail_code dbo.mailcode not null, phone_no dbo.phonenumber null, photograph image null, issue_dt datetime not null default (getdate(), expr_dtdatetimenot null default(dateadd(year,1,

13、getdate(),region_nodbo.numeric_id not null, corp_no dbo.numeric_id null, prev_balance money null default (0), curr_balance money null default (0), member_code dbo.status_code not null default () 这个表具备下面的四个索引： 索引名细节索引的列member_corporation_link nonclustered located on primary corp_no member_ident clust

14、ered, unique, primary key located on primary member_no member_region_link nonclustered located on primary region_no memberfirstname nonclustered located on primaryfirstname当我们执行下面的sql查询时候， select m.member_no, m.firstname, m.region_nofromdbo.member as mwherem.firstname like k% and m.region_no 6 and m

15、.member_no sql server 会根据索引方式，优化成下 面方式来执行。select a.member_no,a.firstname,b.region_nofrom(select m.member_no, m.firstname from dbo.member as m where m.firstname like k% and m.member_no (select m.member_no, m.region_no from dbo.member as mwhere m.region_no 6) b-这个查询可以直接使用 member_region_link 非聚集索 引，而且这

16、个非聚集索引覆盖了所有查询列 -实际执行时，只需 要逻辑读取10次where a.member_no = b.member_no不信，你可以看这两个 sql的执行计划，以及逻辑读信息， 都是一样的。其实上面的sql ,如果优化成下面的方式，实际的逻辑读消耗 也是一样的。为何sql server 不会优化成下面的方式。是因为and操作符优化的另外一个原则。1/26的数据和1/6的数据找交集的速度要比1/52的数据和1/3的数据找交集速度要慢。select a.member_no,a.firstname,b.region_nofrom(select m.member_no, m.firstname

17、 from dbo.member as mwherem.firstname like k% - 1/26数据)a,(select m.member_no, m.region_no from dbo.member as mwhere m.region_no 6 and m.member_no 当然，我们要学 习sql如何优化的话，就会用到查询语句中的一个功能，指定查 询使用哪个索引来进行。比如下面的查询语句select m.member_no, m.firstname, m.region_nofrom dbo.member as mwith (index (0)where m.firstname

18、 like k% and m.region_no 6 and m.member_no select m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (1)where m.firstname like k% and m.region_no 6 and m.member_no 6 and m.member_no 6 and m.member_no 这 里index计算符可以是0,1,指定的一个或者多个索引名字。 对于0 , 1的意义如下：如果存在聚集索引，则index(0)强制执行聚集索引扫描，index(

19、1)强制执行聚集索引扫描或查找(使用性能最高的一种)。如果不存在聚集索引，则 index(0)强制执行表扫描，index(1) 被解释为错误。总结知识点：简单来说，我们可以这么理解： sql server对于每一条查询 语句。会根据实际索引情况(sysindexes 系统表中存储这些信息), 分析每种组合可能的成本。然后选择它认为成本最小的一种。作 为它实际执行的计划。成本代价计算的一个主要组成部分是逻辑i/o的数量，特别是对于单表的查询and操作要满足所有条件，这样，经常会要求对几个数据集 作交集。数据集越小，数据集的交集计算越节省成本。的项目中，竟然出现了滥用聚集索引的问题。看来没有培训 最最基础的索引的意义，代价，使用场景，是一个非常大的失误。 这篇博客就是从这个角度来罗列索引的基础知识。使用索引的意义索引在数据库中的作用类似于目录在书籍中的作用，用来提 高查找信息的速度。使用索引查找数据，无需对整表进行扫描，可以快速找到所 需数据。使用索引的代价索引需要占用数据表以外的物理存储空间。创建索引和维护索引要花费一定的时间。当对表进行更新操作时，索引需要被重建，这样降低了数据 的维护速度。创建索引的列主键外键或在表联接操作中经常用到的列在经常查询的字段上最好建立索引不创建索引的列很少