数据库速成教程(共25页).doc

《数据库速成教程(共25页).doc》由会员分享，可在线阅读，更多相关《数据库速成教程(共25页).doc（25页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上数据库速成教程11常见数据库类型简介*11.1层次数据库11.2网状数据库21.3关系数据库*21.4其他非关系型数据库32关系型数据库模式*42.1用户模式52.2模式52.3内模式53完整性约束53.1实体完整性53.2域的完整性约束53.3引用完整性约束53.4自定义约束54关系数据库范式*54.1数据库第一范式*84.2数据库第二范式*84.3数据库第三范式*84.4第四范式和BCNF范式*94.5范式分解95SQL语言介绍*95.1DML*95.2DCL*115.3DDL*116数据库操作练习*126.1Sqlserver*126.2MySql*126.3O

2、racle127课时安排12数据库速成教程1 常见数据库类型简介*存取数据(OLTP) online transaction process，增删改查为主统计分析(OLAP) online analysis process 以查询分析为主数据库要学习两部分内容：l 如何设计数据库l 如何使用数据库数据结构和数据库的关系：数据结构使用起来比数据来的复杂，我们自己定义数据结构的各种操作Select order by where,insert,update,delete.数据检索，写入，修改等操作具有优势(Sql结构化查询语言)1.1 层次数据库定义：层次模型是数据库系统中最早使用的模型，它的数据结

3、构类似一颗倒置的树，每个节点表示一个记录类型，记录之间的联系是一对多的联系，基本特征是： * 一定有一个，并且只有一个位于树根的节点，称为根节点； * 一个节点下面可以没有节点，即向下没有分支，那么该节点称为叶节点； * 一个节点可以有一个或多个节点，前者称为父节点，后者称为子节点； * 同一父节点的子节点称为兄弟节点。 * 除根节点外，其他任何节点有且只有一个父节点；实例：XML特性：层次模型中，每个记录类型可以包含多个字段，不同记录类型之间、同一记录类型的不同字段之间不能同名。如果要存取某一类型的记录，就要从根节点开始，按照树的层次逐层向下查找，查找路径就是存取路径。 层次模型结构简单，容

4、易实现，对于某些特定的应用系统效率很高，但如果需要动态访问数据（如增加或修改记录类型）时，效率并不高。另外，对于一些非层次性结构（如多对多联系），层次模型表达起来比较繁琐和不直观。1.2 网状数据库网状模型可以看作是层次模型的一种扩展。它采用网状结构表示实体及其之间的联系。网状结构的每一个节点代表一个记录类型，记录类型可包含若干字段，联系用链接指针表示，去掉了层次模型的限制。网状模型的特征是： 1. 允许一个以上的节点没有父节点； 2. 一个节点可以有多于一个的父节点；由于网状模型比较复杂，一般实际的网状数据库管理系统对网状都有一些具体的限制。在使用网状数据库时有时候需要一些转换。网状模型与层

5、次模型相比，提供了更大的灵活性，能更直接地描述现实世界，性能和效率也比较好。网状模型的缺点是结构复杂，用户不易掌握，记录类型联系变动后涉及链接指针的调整，扩充和维护都比较复杂。1.3 关系数据库*关系模型是目前应用最多、也最为重要的一种数据模型。关系模型建立在严格的数学概念基础上，采用二维表格结构来表示实体和实体之间的联系。二维表由行和列组成。下面以教师信息表和课程表为例，说明关系模型中的一些常用术语：1. 关系（或表）：一个关系就是一个表，如上面的教师信息表和课程表。 2. 元组：表中的一行为一个元组（不包括表头）。 3. 属性：表中的一列为一个属性。 4. 主码（或关键字）：可以唯一确定一

6、个元组和其他元组不同的属性组。 5. 域：属性的取值范围。 6. 分量：元组中的一个属性值。 7. 关系模式：对关系的描述，一般表示为：关系名（属性1，属性2，. .，属性n）。 Student(sID,sSex,sBirth)关系模型中没有层次模型中的链接指针，记录之间的联系是通过不同关系中的同名属性【不一定，名称可不一样】来实现的。 关系模型的基本特征是： 1. 建立在关系数据理论之上，有可靠的数据基础； 2. 可以描述一对一，一对多和多对多的联系。 3. 表示的一致性。实体本身和实体间联系都使用关系描述。 4. 关系的每个分量的不可分性，也就是不允许表中表。 关系模型概念清晰，结构简单，

7、实体、实体联系和查询结果都采用关系表示，用户比较容易理解。另外，关系模型的存取路径对用户是透明的，程序员不用关心具体的存取过程，减轻了程序员的工作负担，具有较好的数据独立性和安全保密性。 关系模型也有一些缺点，在某些实际应用中，关系模型的查询效率有时不如层次和网状模型。为了提高查询的效率，有时需要对查询进行一些特别的优化1.4 其他非关系型数据库TRS文本检索系统文档型数据库如果有个OA系统，文件的签字，安全管理2 关系型数据库模式*SQL语言支持关系数据库三级模式结构，如图3.1所示。其中外模式对应于视图(View)和部分基本表(Base Table) ，模式对应于基本表，内模式对应于存储文

8、件。 2.1 用户模式用户看到的（报表，视图（n个有关联物理表组成），综合性页面，有可能是基本表）2.2 模式基本的物理表如何知道要有多少个物理表，需要分析依据是什么，用户模式你看到了什么，范式进行分解那么如何设计这样的数据库用户表帖子表用户等级论坛分类表1欠钱借贷时间2Xx人电话家庭住址欠钱借贷时间3Xx人电话家庭住址欠钱借贷时间UseruserIDXx人电话家庭住址LoanRecordID欠钱借贷时间userID2.3 内模式表内部的数据在磁盘是如何存储和查询的（数据库提供商完成DBMS）以Access为例说明3 完整性约束3.1 实体完整性拥有每个记录（元组）的唯一性标识的列-id列。3

9、.2 域的完整性约束列的取值范围，由其属性决定（tinyInt,smallInt），或者是用户的check约束，外键，主键3.3 引用完整性约束外键有效性，外键可以是自己表，也可以是外来的3.4 自定义约束触发器可以做到数据约束的功能除数据类型和大小外，表中的列还具有其它属性。这些属性在确保数据库中的数据完整性方面有重要作用： 数据完整性是指列中每个事件都有正确的数据值。 数据值必须是正确的数据类型，并且位于正确的域内。 引用完整性表示得到正常维护的表之间的关系。 表中的数据只应指向另一个表中的现有行，不应指向不存在的行。用于维护这两种完整性的对象包括： 约束 规则 默认值 触发器 4 关系数

10、据库范式*第一范式对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.第二范式第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.第三范式第三范式要求非主键列互不依赖.第四范式第四范式禁止主键列和非主键列一对多关系不受约束什么是“不好”的关系范式规范化关系数据库的公式（若干个）关系模式有如下“毛病”（1）数据冗余。（2）更新异常（不一致性的危险）。（3）插入异常。如果某供应者没有供应任何货物，则我们无

11、法记录他的名称和地址。（4）删除异常。如果一个供应者供应的所有货物都被删除，则我们无可奈何地丢失了该供应者的名称和地址。记录编号A人电话借/还1a10002a-10003b5004范式：规范数据库的若干公式没有冗余，最简单的操作员工ID员工姓名性别区域ID区号所在地构造数据库必须遵循一定的规则。在关系数据库中，这种规则就是范式。范式是符合某一种级别的关系模式的集合。关系数据库中的关系必须满足一定的要求，即满足不同的范式。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）和第六范式（6NF）。满足最低要求的范式是第一范式（

12、1NF）。在第一范式的基础上进一步满足更多要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式（3NF）就行了。下面我们举例介绍第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。一、什么是“不好”的关系模式关系模式有如下“毛病”（1）数据冗余。（2）更新异常（不一致性的危险）（3）插入异常。如果某供应者没有供应任何货物，则我们无法记录他的名称和地址。（4）删除异常。如果一个供应者供应的所有货物都被删除，则我们无可奈何地丢失了该供应者的名称和地址。二、函数依赖（一）函数依赖的定义设R（A1 ，A2 ，An ）是一个关系模式，X和Y是A1 ，A2 ，An 的子

13、集，若只要关系r是关系模式R的可能取值，则r中不可能有两个元组在X上的属性值相等，而在Y上的属性值不等，则称“X函数决定Y”，或“Y函数依赖于X，记作XY。注意，函数依赖XY的定义要求关系模式R的任何可能的r都能满足上述条件。（二）函数依赖的逻辑蕴含设RU，F是一个关系模式，X，Y是U中属性组，若在RU，F的任何一个满足F函数依赖的关系r上，都有函数依赖XY成立，则称F逻辑蕴含XY。（三）Key码设K为关系模式R中的属性或属性组，若KU在F+中，而找不到K的任何一个真子集K，能使KU在F+中，则称K为关系模式R的候选码。当候选码多于一个时，选定其中一个做主码。包含在任何一个候选码中的属性叫做主

14、属性。不包含在任何候选码中的属性叫做非主属性。最简单的情况，单个属性是码。最极端的情况，整个属性组是码，称做全码。（四）函数依赖的公理系统设F是属性组U上的一组函数依赖，于是有如下推理规则:增广律:若XY为F所逻辑蕴含，且ZU，则XZYZ为F所逻辑蕴含。传递律:若XY及YZ为F所逻辑蕴含，则XZ为F所逻辑蕴含。注意:由自反律所得到的函数依赖均是平凡的函数依赖，事实上自反律的应用只依赖于U，不依赖于F。根据Armstrong公理系统的3条推理规则可以得到下面3条很有用的推理规则:（1）合并规则:由XY，XZ，有XYZ。（2）伪传递规则:由XY，WYZ，有XWZ。三、1NF、2NF、3NF、BCN

15、F（一）第一范式（1NF）及进一步规范化关系模式需要满足一定的条件，不同程度的条件称做不同的范式。最低要求的条件是元组的每个分量必须是不可分的数据项，这叫做第一范式，简称1NF，是最基本的规范化，在第一范式的基础上进一步增加一些条件，则为第二范式。以此类推，还有第三范式，Boyce-Codd范式，等等。函数依赖XY不仅给出了对关系的值的限制，而且给出了数据库中应该存储的某种联系:从X的值应该知道与之联系的惟一Y值。若X不含码，则有麻烦了。码是一个元组区别于其他元组的依据，同时也是一个元组赖以存在的条件。在一个关系中，不可能存在两个不同的元组在码属性上取值相同，也不可能存在码或码的一部分为空值的

16、元组。若某关系模式的属性间有函数依赖XY，而X又不包含码，那么在具有相同X值的所有元组中，某个特定的Y值就会重复出现，这是数据冗余，随之而来的是更新异常问题;某个X值与某个特定的Y值相联系，这是数据库中应存储的信息，但由于X不含码，这种X与Y相联系的信息可能因为码或码的一部分为空值而不能作为一个合法的元组在数据库中存在，这是插入异常或删除异常问题。第二范式、第三范式和Boyce-Codd范式就是不同程度地限制关系模式中X不包含码的函数依赖XY的存在。（二）第二范式（2NF）若关系模式R1NF，且每一个非主属性完全函数依赖于码，则R2NF。2NF就是不允许关系模式的属性之间有这样的函数依赖XY，

17、其中X是码的真子集，Y是非主属性。即不允许有非主属性对码的部分函数依赖。（三）第三范式（3NF）若关系模式R2NF，且每一个非主属性都不传递依赖于码，则R3NF。3NF就是不允许关系模式的属性之间有这样的非平凡函数依赖XY，其中X不包含码，Y是非主属性。X不包含码有两种情况，一种情况X是码的真子集，这是2NF不允许的，另一种情况X不是码的真子集，这是3NF不允许的。（四）Boyce-Codd范式（BCNF）若关系模式R1NF，且对于每一个非平凡的函数依赖XY，都有X包含码，则RBCNF。BCNF是3NF的进一步规范化，即限制条件更严格。3NF不允许有X不包含码，Y是非主属性的非平凡函数依赖XY

18、。BCNF则不管Y是主属性还是非主属性，只要X不包含码，就不允许有XY这样的非平凡函数依赖。因此，若RBCNF，则必然R3NF。然而，BCNF又是概念上更加简单的一种范式，判断一个关系模式是否属于BCNF，只要考察每个非平凡函数依赖XY的决定因素X是否包含码就行了。1NF，2NF，3NF，BCNF的相互关系是:BCNF3NF2NF1NF在函数依赖的范畴内，BCNF达到了最高的规范化程度。四、多值依赖和4NF 多值依赖多值依赖的定义是:设R是属性集U上的一个关系模式，X、Y是U的子集，Z=U-X-Y。若在R的任一关系r中，只要存在元组t，s，使得tX=sX,就必然存在元组w，vr（w，v可以与s

19、，t相同），使得wX=X=tX=sX，而wY=tY,wZ=sZ;Y=sY,Z=tZ,则称Y多值依赖于X，记做XY。若XY，而Z= ，则称XY为平凡的多值依赖。在关系模式WSC中，存在多值依赖WS。多值依赖具有以下性质:（1）若XY，则XZ，其中，Z=U-X-Y，即多值依赖具有对称性。（2）若XY，则XY，即函数依赖可以看作多值依赖的特殊情况。因为当XY时，对于X的每一个值x，都有Y的一个确定值y与之对应。（3）若XY在R（U）上成立，且YY，我们不能断言XY在R（U）上成立。这也是因为多值依赖的定义中涉及了U中除X，Y之外的其余属性Z，考虑XY是否成立时涉及的其余属性Z=U-X-Y比确定XY成

20、立时涉及的其余属性Z=U-X-Y包含的属性列多，因此XY不一定成立。4.1 数据库第一范式* 在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。 所谓第一范式（1NF）是指数据库表的每一列都是不可分割【应用上无需再细化了】的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。例如，对于图3-2 中的员工信息表，不能将员工信息都放在一列中显

21、示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表中只出现一次。简而言之，第一范式就是无重复的列。 比如全文检索（整篇文章都放入一个字段，包含了N多实际要查询的关键字，所以不能使用关系数据库）就网站来说，如何设计一个帖子表存放帖子信息，一个列只能放置一个原子信息，你的应用领域标题价格人气100/2ISBN书名Isbn作者id编号Isbn作者idIsbn作者id人气指数：（回帖率*100+点击率5） /发布时间4.2 数据库第二范式* 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）

22、。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。如员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。 第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键

23、字。帖子ID标题内容UserID发贴者等级帖子ID点击回帖数人气指数帖子ID帖子标题内容市时间联系人IDQQ联系人电话拆分为=2个表Books表ISBNAuthor1Author2Author3作者不定数，获取所有的作者？4.3 数据库第三范式*满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，

24、则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。【传递依赖】传递依赖三角形ID直角边A直角边B斜边C面积学生ID省市 区街道作者ID年龄出生年月4.4 第四范式和BCNF范式*【一对多的主键和非主键之间要受到互相约束，如果不受约束会违背这个范式】没有直接依赖，但是有间接的依赖，如果把这两个列放在一起那违背第四凡是学生和教师的教学关系sIDTeacherIDcourseID1111211312112212313studentIDcourseIDscore1167129021782250一个仓库有n个管理员，一个管理员在一个仓库工作，

25、每个仓库有m个商品管理员ID仓库ID产品ID产品价格数量1100176100021001761000310017610001100221002仓库ID和管理员表，仓库+产品studentIDcourseIDscore0010011670020011870030011第四范式禁止主键列和非主键列一对多关系不受约束，解决组合主键的问题成绩列表courseIDStudentID成绩数据库设计外模式入手，分析模式的物理表，设定关系表的设计Users用户表，Topics帖子列表发帖的记录和跟贴的记录要不要放在同一个表中？为什么 限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。在BCNF的前提

26、下，消除多值依赖。函数依赖和多值依赖是两种最重要的数据依赖。如果只考虑函数依赖，则属于BCNF的关系模式规范化程度已最高了。如果考虑多值依赖，则属于4NF的关系模式规范化程度是最高的了。 举例： product|agent | factory CarA1F1 BusA1F2 CarA2F2 在这里，Car的定位，必须由 agent 和 Factory才能得到（所以主键由agent和factory组成）,可以通过 product依赖了agent和factory两个属性 所以正确的是 表1表2： product|agentfactory | product CarA1F1Car BusA1F2Ca

27、r CarA2F2Bus第5范式（5NF）： 关系模式R中的每一个连接依赖, 都是由R的候选键所蕴含, 称R是第五范式的。它要求能从由原始表分解和转换而得的新表中精确重建出原始表。利用第五范式可以确定在分解和转换过程中有没有数据丢失。是将表分割成尽可能小的块，为了排除在表中所有的冗余。 看到定义，就知道是要消除连接依赖,并且必须保证数据完整。 举例： A|B|C a1b1c1 a2b1c2 a1b2 c1 a2b2c2 如果要定位到特定行，必须三个属性都为关键字。 所以关系要变为 三个关系，分别是A 和B,B和C ，C和A 如下： 表1表2表3 A|BB| CC|A a1b1b1c1c1a1

28、a1b2b1c2c1a24.5 范式分解规范化过程中将一个关系模式分解为若干个关系模式，应该保证分解后产生的模式与原来的模式等价。常用的等价标准有要求分解是具有无损连接性的和要求分解是保持函数依赖的两种。将一个关系模式RU，F分解为若干个关系模式R1U1 ，F1 ，R2U2，F2 ，RmUn ，Fn （其中，U=U1U2Un ，Fi 为F在Ui上的投影）,这意味着相应地将存在一个二维表r中的数据分散到若干个二维表r1，r2，rn中去（其中ri是r在属性组Ui上的投影）。我们当然希望这样的分解不丢失信息，也就是说，希望能通过对关系r1,r2 ，r n 的自然连接运算重新得到关系r中的所有信息。设

29、关系模式RU，F分解为关系模式R1U1 ，F1 ，R 2U2 ，F2 ，RnUn ，Fn ，若F + =（F1F2Fn）+，即F所逻辑蕴含的函数依赖一定也由分解得到的各个关系模式中的函数依赖所逻辑蕴含，则称关系模式R的这个分解是保持函数依赖的。（二）关于模式分解的几个事实（1）分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。具有无损连接性的分解不一定保持函数依赖，保持函数衣赖的分解不一定具有无损连接性。（2）若要求分解具有无损连接性，那么模式分解一定可达到BCNF。（3）若要求分解保持函数依赖，那么模式分解可达到3NF，但不一定能达到BCNF。（4）若要求分解既具有无损连接性，又保持

30、函数依赖，则模式分解可以达到3NF，但不一定能达到BCNF。书本的表要怎样做：ISBN号书名编著者作者表编著者：ISBN+作者ID编著者作者ID1编著者作者ID2实用性考虑：设计的时候要把视图分解为基本表用的时候要组合（组成视图）3NF如何体现教学中的教师学生课程等关系，一个学生和教师在一起，请问如何设计才能知道这个教师是否给这个学生上过课程。仓库进销存中的产品，管理人员，仓库编号如何设计（不得违背第四范式）二手论坛表要如何设计 F5 数据库设计过程5.1 外部模式-内部的转换5.1.1 逻辑设计设计表的名称，主键【类型也要制定】进行表的外键关联5.1.2 物理设计设计每个表字段的类型，名称数

31、据完整性/添加的字段要符合第二范式，5.2 过程中采用范式进行分解用户表交易信息表地区信息表【省，市，县】发布信息类别【大类表，三个小分类表】做一下她的后台数据库，要求有基本表，关系，如果有时间，视图给我几个设计数据库的过程是按照范式来分解，步骤是按照模式从外到内进行6 SQL语言介绍*6.1 DDL*Create ,drop ,alter(增删改数据元数据)合并和使用（联合增删改查）结构化查询语言，非常高级语言，负责业务逻辑6.2 DML*增删改查（select,update,insert,delete）数据6.2.1 查询数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数

32、据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为:SELECT ALLIDISTINCT ， FROM ， WHERE GROUPBY HAVING ORDERBY ASqDESC: 整个SELECT语旬的含义是，根据WHERE子句的条件表达式，从FROM 子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的 组才予输出。如果有ORDER子句，则结果表还要按的值的

33、升序或降序排序。 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3.3所示。 表3.3常用的查询条件查询条件 谓词 比较 =， ， =， =， !=， ， !， !b.au_id and a.city=b.city- order by a.zip- select supplierID ,country from suppliers-找出在相同国家的供应商的id对select a.supplierID,b.supplierID,a.country from suppliers ainner join suppliers b on a.supplierID b.

34、supplierID and a.country=b.country-找出98年比97年销售额增加的员工ID-97年比96年业务额增加的员工ID-两个表，98年度的员工销售份额，97年员工销售额-创建了2个视图98的销售额，97的select a.employeeID from (SELECT a.EmployeeID, SUM(b.UnitPrice * b.Quantity) * (1 - b.Discount) AS totalFROM dbo.Orders a INNER JOIN dbo.Order Details b ON a.OrderID = b.OrderID AND DATEPART(yyyy, a.OrderDate) = 1998GROUP BY a.EmployeeID) a inner join (SELECT a.EmployeeID, SUM(b.UnitPrice * b.Quantity) * (1 - b.Discount) AS