数据库实验总结

数据库实验总结【一】

试验内容

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 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),

/*主码有两个属性构成，必须作为表级完整性进行定义*/

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), /*约束条件的名字为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

(

sno 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),

/*表级完整性约束条件，jno是外码，被参照表是j*/

)

2、数据表的更改

在s表中添加一个concat 列

alter table s add concat varchar(20)

在s表中删除concat 列

alter table s drop column concat

更改s表 concat列的属性 把长度由20改为30

alter table s alter column concat varchar(30)

联系方式 名字为concat 修改属性为唯一的 属性名为con_concat

alter table s add constraint con_concat unique(concat)

删除约束关系con_concat

alter table s drop constraint con_concat

/*插入一个元组*/

insert into s valus(‘s1’,’精益’,20,’天津’) /*20不能写成’20’*/

试验中的问题的排除与总结：

1、在创建spj时

有三个实体所以从3个实体中取主码，还有一个数量属性也要写上

主码由那3个主码确定

2、更改一个数据库中数据表时一定要先使该数据库处于正在使用状态

3、constraint

是可选关键字，表示 primary key、not null、unique、foreign key 或 check 约束定义的开始。约束是特殊属性，用于强制数据完整性并可以为表及其列创建索引。

4、--go可以不加但是要注意顺序 注：go --注释 提示错误

5、注意添加一个空元素用 null

附 sql备份

--创建一个数据库 student

create database student

go

--在数据库student中创建表student course sc 注意顺序

use student

----------------------------------------------------------------

create table student

(

sno char(9) primary key, /*sno是主码 列级完整性约束条件*/

sname char(10) unique, /*sname取唯一值*/

ssex char(2),

sage smallint, /*类型为smallint*/

sdept char(20) /*所在系*/

)/*要加*/

-----------

数据库实验总结【二】

我在sql server 索引基础知识系列中,第一篇就讲了记录数据的基本格式。那里主要讲解的是，数据库的最小读存单元：数据页。一个数据页是8k大小。

对于数据库来说，它不会每次有一个数据页变化后，就存到硬盘。而是变化达到一定数量级后才会作这个 *** 作。 这时候，数据库并不是以数据页来作为 *** 作单元，而是以64k的数据(8个数据页，一个区)作为 *** 作单元。

区是管理空间的基本单位。一个区是八个物理上连续的页(即 64 kb)。这意味着 sql server 数据库中每 mb 有 16 个区。

为了使空间分配更有效，sql server 不会将所有区分配给包含少量数据的表。sql server 有两种类型的区：

统一区，由单个对象所有。区中的所有 8 页只能由所属对象使用。

混合区，最多可由八个对象共享。区中八页的每页可由不同的对象所有。

通常从混合区向新表或索引分配页。当表或索引增长到 8 页时，将变成使用统一区进行后续分配。如果对现有表创建索引，并且该表包含的行足以在索引中生成 8 页，则对该索引的所有分配都使用统一区进行。

为何会这样呢?

其实很简单：

读或写 8kb 的时间与读或写 64 kb的时间几乎相同。

在 8 kb 到 64 kb 范围之内，单个磁盘 i/o 传输 *** 作所花的时间主要是磁盘取数臂和读/写磁头运动的时间。

因此，从数学上来讲，当需要传输 64 kb 以上的 sql 数据时，

尽可能地执行 64 kb 磁盘传输是有益的，即分成数个64k的 *** 作。

因为 64 kb 传输基本上与 8 kb 传输一样快，而每次传输的 sql server 数据是 8 kb 传输的 8 倍。

我们通过一个实例来看 有and *** 作符时候的最常见的一种情况。我们有下面一个表，

create table [dbo].[member]( [member_no] [dbo].[numeric_id] identity(1,1) not null, [lastname] [dbo].[shortstring] not null, [firstname] [dbo].[shortstring] not null, [middleinitial] [dbo].[letter] null, [street] [dbo].[shortstring] not null, [city] [dbo].[shortstring] not null, [state_prov] [dbo].[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_dt] [datetime] not null default (dateadd(year,1,getdate())), [region_no] [dbo].[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 clustered, unique, primary key located on primary member_no

member_region_link nonclustered located on primary region_no

memberfirstname nonclustered located on primary firstname

当我们执行下面的sql查询时候，

select m.member_no, m.firstname, m.region_nofrom dbo.member as mwhere m.firstname like 'k%' and m.region_no >6 and m.member_no <5000go

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 <5000) a , -- 这个查询可以直接使用 memberfirstname 非聚集索引，而且这个非聚集索引覆盖了所有查询列-- 实际执行时，只需要 逻辑读取 3 次

(select m.member_no, m.region_no from dbo.member as mwhere m.region_no >6) b

-- 这个查询可以直接使用 member_region_link 非聚集索引，而且这个非聚集索引覆盖了所有查询列-- 实际执行时，只需要 逻辑读取 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 from dbo.member as mwhere m.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 <5000-- 1/3 * 1/ 2 数据) bwhere a.member_no = b.member_no

当然，我们要学习sql 如何优化的话，就会用到查询语句中的一个功能，指定查询使用哪个索引来进行。

比如下面的查询语句

select m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (0))where m.firstname like 'k%' and m.region_no >6 and m.member_no <5000go

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 <5000goselect m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (membercovering3))where m.firstname like 'k%' and m.region_no >6 and m.member_no <5000goselect m.member_no, m.firstname, m.region_nofrom dbo.member as m with (index (memberfirstname, member_region_link))where m.firstname like 'k%' and m.region_no >6 and m.member_no <5000go

这里 index 计算符可以是 0 ，1， 指定的一个或者多个索引名字。对于 0 ，1 的意义如下：

如果存在聚集索引，则 index(0) 强制执行聚集索引扫描，index(1) 强制执行聚集索引扫描或查找(使用性能最高的一种)。

如果不存在聚集索引，则 index(0) 强制执行表扫描，index(1) 被解释为错误。

总结知识点：

简单来说，我们可以这么理解：sql server 对于每一条查询语句。会根据实际索引情况(sysindexes 系统表中存储这些信息)，分析每种组合可能的成本。然后选择它认为成本最小的一种。作为它实际执行的计划。

成本代价计算的一个主要组成部分是逻辑i/o的数量，特别是对于单表的查询。

and *** 作要满足所有条件，这样，经常会要求对几个数据集作交集。数据集越小，数据集的交集计算越节省成本。

的项目中，竟然出现了滥用聚集索引的问题。看来没有培训最最基础的索引的意义，代价，使用场景，是一个非常大的失误。这篇博客就是从这个角度来罗列索引的基础知识。

使用索引的意义

索引在数据库中的作用类似于目录在书籍中的作用，用来提高查找信息的速度。

使用索引查找数据，无需对整表进行扫描，可以快速找到所需数据。

使用索引的代价

索引需要占用数据表以外的物理存储空间。

创建索引和维护索引要花费一定的时间。

当对表进行更新 *** 作时，索引需要被重建，这样降低了数据的维护速度。

创建索引的列

主键

外键或在表联接 *** 作中经常用到的列

在经常查询的字段上最好建立索引

不创建索引的列

很少在查询中被引用

包含较少的惟一值

定义为 text、ntext 或者 image 数据类型的列

heaps是staging data的很好选择，当它没有任何index时

excellent for high performance data loading (parallel bulk load and parallel index creation after load)

excellent as a partition to a partitioned view or a partitioned table

聚集索引提高性能的方法，在前面几篇博客中分别提到过，下面只是一个简单的大纲，细节请参看前面几篇博客。

何时创建聚集索引?

clustered index会提高大多数table的性能，尤其是当它满足以下条件时：

独特, 狭窄, 静止： 最重要的条件

持续增长的，最好是只向上增加。例如：

identity

date, identity

guid (only when using newsequentialid() function)

聚集索引唯一性(独特型的问题)

由于聚集索引的b+树结构的叶子节点必须指向具体数据。如果你要建立聚集索引的列不唯一，并且你指定的创建的聚集索引是非唯一的聚集索引，则会有以下情况：

如果未使用 unique 属性创建聚集索引，数据库引擎 将向表自动添加一个四字节 uniqueifier 列。必要时，数据库引擎 将向行自动添加一个 uniqueifier 值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。

这个主要要着重写几个方面，你一定要多写，写的详细一点。

1，管理系统的数据库需求【这个算是概况】

2，数据库对象模型设计 【设计核心部分】

3，数据库的创建以及表间关系，存储过程，视图，触发器的设计和定义。【标准定义部分】

4，数据库的优化设计，【这个你要是没有经验，就多测试，多写测试结果】

【着重在测试上多写点啊，比如，20万数据和100万数据的时候，你是如何优化查询的】

少说这里能写个几千字。

5，数据访问组件的代码结构，既然是管理系统，肯定要涉及数据访问。你把这个多写一点，比如不同组件之间的数据访问的差异，优势，弊端，你是如何改进的之类的。

7，BUG调试日志，开发这么个系统，肯定是中途经历了不少的错误和修改，你把这个过程写一点。

8，记录下你对于这个系统从设计到开发的经验，总结。

这么下来，怎么着也得1万字吧，要是再把参考的资料加上，恐怕能打印一小本了。

唯一需要注意的是，外键字段的数据类型必须和主键的数据类型相同。但是有些系统可以允许这条规则有一个例外，它允许在数字和自动编号（autonumbering）字段（例如在SQL服务器系统中访问Identity和AutoNumber）之间建立关系。此外，外键的值可以是空（Null），尽管强烈建议在没有特别原因的情况下，不要让外键为空。你有可能永远都不会有机会来使用需要这项功能的数据库。 现在回到我们的示例关系表，并开始输入合适的外键。（请继续在纸上打草稿——在你的数据库系统中创建真正的数据表还为时过早。要知道在纸上纠正错误要容易得多。）要记住，你正在把主键的值添加到关系表里。只要调用实体之间的关系就行了，而其他的就简单了： 书籍和分类是有关系的。 书籍和出版社是有关系的。 书籍和作者是有关系的。 作者和邮政编码（ZIP）是有关系的。 邮政编码和城市是有关系的。 城市和州是有关系的。 这一步并不是一成不变的，你可能会发现在规范化的过程中加入外键会更容易一些。在把字段移动到一个新的数据表时，你可能要把这个新数据表的主键添加到原来的数据表里，将其作为外键。但是，在你继续规范化剩余数据的时候，外键常常会发生改变。你会发现在所有这些数据表被全部规范化之后，一次添加所有的外键，这样效率会更高。 *** 作数据表 现在让我们一次 *** 作一个数据表，就从Books数据表开始，它在这个时候只有三个字段。很明显，Authors、Categories和Publishers数据表的主键会被添加到Books里。当你完成的时候，Books数据表就有了七个字段： Books Title (PK) ISBN (PK) Price FirstNameFK (FK) Authors.FirstName many-to-many LastNameFK (FK) Authors.LastName many-to-many CategoryFK (FK) Categories.Category many-to-many PublisherFK (FK) Publishers.Publisher one-to-many 要记住，Authors数据表里的主键是一个基于姓和名两个字段的复合关键字。所以你必须要把这个两个字段都添加到Books数据表里。要注意，外键字段名的结尾包含有FK这个后缀。加入这个后缀有助于提高可读性和自我归档。通过名称这种方式来区别外键会使得追踪它们更简单。如果主键和外键的名称不同，这没有关系。 这里出现了三种关系：Books和Authors、Books和Categories，以及Books和Publishers。这三种关系中所存在的两种问题可能没有那么明显： Books和Authors之间的关系：一本书可以有多个作者。 Books和Categories之间的关系：一本书可以被归入多个类。 这两者的关系是多对多的关系。先前我告诉过你，数据表不能直接实现这样的关系，而需要第三个联系表来实现。（Books和Publishers的关系是一对多的关系，就像现在所说的，这样是没有问题的。） 这两个新发现的多对多关系将需要一个联系表来包含来自每个数据表的主键，并将其作为外键。新的联系表是：BooksAuthorsmmlink TitleFK (FK) Books.Title one-to-many ISBNFK (FK) Books.ISBN one-to-many FirstNameFK (FK) Authors.FirstName one-to-many LastNameFK (FK) Authors.LastName one-to-many BooksCategoriesmmlink TitleFK (FK) Books.Title one-to-many ISBNFK (FK) Books.ISBN one-to-many CategoryFK (FK) Categories.Category one-to-many 没有必要更改Categories、Authors或者Publishers数据表。但是，你必须把FirstNameFK、LastNameFK和CategoryFK这三个外键从Books里移走： Books Title (PK) ISBN (PK) Price PublisherFK (FK) Publishers.Publisher one-to-many 现在，让我们转到Authors数据表上来，它现在有两个字段。每个作者都和ZIPCodes数据表中的邮政编码的值相关。但是，每个邮政编码会和多个作者相关。要实现这种一对多的关系，就要把ZIPCodes数据表中的主键添加进Authors数据表作为外键： Authors FirstName (PK) LastName (PK) ZIPCodeFK (FK) ZIPCodes.ZIPCode one-to-many 至此，你已经准备好了处理剩下的地址部分了。看到它们被分在不同的数据表里是很让人奇怪的，但是这是遵照BCNF正确规范化数据的结果。每个邮政编码的值只会有一个对应的城市值和州值。每个城市和州的值只会被输入进其对应的数据表里一次。ZIPCodes和Cities数据表需要外键字段来实现这些关系： ZIPCodes ZIPCode (PK) CityFK (FK) Cities.City one-to-many Cities City (PK) StateFK (FK) States.State one-to-many States State (PK) 从一个到九个 最后，你有了九个数据表：Books、Authors、Categories、Publishers、ZIPCodes、Cities、States、BooksAuthorsmmlink和BooksCategoriesmmlink。图A是这个示例数据表的数据库最终的图形形式。很难想像一个简单的数据表会被分成九个数据表。 图A 最初的一个数据表现在需要九个数据表了 由于这个示例数据库很简单，你可能会问这些关系有什么作用。看起来仍在保存冗余的数据，只不过形式不同罢了——通过外键来实现。这是因为我们的数据表现在只有很少几个字段。试想一下有十几个字段的数据表，会是什么样的一个情形。需要承认的是，你仍然需要把数据表的主键作为外键保存进关系表里，但是至多可能最多增加一到两个字段。比较一下为这个数据表里的每一条记录都添加十几个条目的情形吧。

---