软件技术基础第13讲数据库设计基础和SQL语言.ppt

《软件技术基础第13讲数据库设计基础和SQL语言.ppt》由会员分享，可在线阅读，更多相关《软件技术基础第13讲数据库设计基础和SQL语言.ppt（86页珍藏版）》请在三一办公上搜索。

1、软件技术基础第13讲数据库设计基础和SQL语言,计算机教学实验中心,2002年10月31日星期四,第4章作业,作业:1.第4章的68、17。2.OS存储管理的对象与任务是什么？常用的主存管理方法有哪几种？各自的特点是什么？OS设备管理包括哪些基本任务？思考题:1、第4章的13，1214。2.什么是实存储管理和虚存储管理？主处理机与设备的信息交换有哪几种基本方法，各有何特点？什么是设备分配和驱动？,2002年10月31日星期四,上讲内容提示（1）：,一、关系模型的数学定义和关系代数1.关系模型的数学定义：域、元组、笛卡尔乘积、关系、属性、关系模式、关系模型、关系数据库、完整性约束（实体、参照、用

2、户定义）,2002年10月31日星期四,上讲内容提示（2）：,2.数据库管理系统中的关系模型 关系的特点（表的特点）、数据库记录、字段、类型、值域 候选关键字、主关键字、外码，数据类型能体现数据结构的哪些特点（逻辑关系、存储方式、运算能力），字段类型,2002年10月31日星期四,上讲内容提示（3）：,3.关系代数与、或、非、并、交、补、属于、不属于、包含、真包含同类关系运算（并、交、差、选择、投影、联结、自然联结）4.数据库中的关系运算,2002年10月31日星期四,上讲内容提示（4）：,二、关系的规范化理论：不好的数据库结构有操作异常（冗余、插入、删除、更新）数据依赖：函数依赖、完全函数、

3、传递函数依赖范式1NF、2NF、3NF,2002年10月31日星期四,一、数据库设计基础,2002年10月31日星期四,1.需求分析,主要结果是系统分析说明书、数据流程图、数据字典,2002年10月31日星期四,数据流程图示例,2002年10月31日星期四,数据字典举例,2002年10月31日星期四,数据字典举例,2002年10月31日星期四,2。概念设计,概念设计的结果是E-R图，概念设计独立于数据库逻辑结构，独立于具体的数据库管理系统。根据需求分析得到的结果，画出各个部分的局部E-R图，表达出有哪些实体、实体间的联系以及实体的属性。然后将各个局部E-R图合并在一起得到系统的全局E-R图。,

4、2002年10月31日星期四,概念设计（2）,合并数据流图时注意消除E-R图中的冲突：（1）属性冲突（整、实、字符）（2）命名冲突（名字不同）（3）结构冲突（对象、属性、）,2002年10月31日星期四,概念设计E-R图示例,2002年10月31日星期四,3.逻辑设计,2002年10月31日星期四,逻辑结构设计步骤图示,2002年10月31日星期四,(1)E-R图向数据模型的转换,2002年10月31日星期四,(2)数据模型的优化,(3)定义用户子模式,2002年10月31日星期四,4.物理设计,2002年10月31日星期四,5.数据库实施,2002年10月31日星期四,6.数据库的运行和维护

5、,2002年10月31日星期四,数据库的安全性和完整性,安全性：数据和其他信息不能被有意或无意地破坏，不能被未经授权的人员读取和修改。（1）用户身份标识和鉴别（2）存取权限控制策略（3）加密技术的使用完整性：指数据的正确性和相容性，即合法拥护对数据的增删改必须符合一定的语义。基于列的完整性：如列的取值范围等基于行的完整性：一行中各列之间的约束基于关系的完整性：实体、参照和用户定义完整性触发器：,2002年10月31日星期四,7.数据库设计的基本步骤(大作业中库设计的基本要求),分析建立数据库的目的确定数据库中的表确定表中的字段确定主关键字确定表之间的关系优化设计(规范化)输入数据并新建其他DB

6、对象,2002年10月31日星期四,分析建立数据库的目的,首先考虑“为什么要建立DB及建立DB要完成的任务”。这是DB设计的第一步，也是DB设计的基础。与DB的最终用户进行交流，了解现行工作的处理过程，讨论应保存及怎样保存要处理的数据。要尽量收集与当前处理有关的各种数据表格。,2002年10月31日星期四,例案分析,建立数据库的目的：例如,实现“教学管理”数据库应用。功能要求：实现“教师、学生、课程、学习成绩”四个方面的综合管理教师可以查看学生的简况、成绩、课程学生可以选择教师、课程和查看成绩,2002年10月31日星期四,确定数据库中的表,从确定的DB所要解决的问题和收集的各种表格中，不一定

7、能够找出生成这些表格结构的线索。因此，不要急于建立表，而应先在纸上进行设计。为了能更合理地确定出DB中应包含的表，应按下列原则对信息进行分类：（设计原则）：表中不应包含重复信息，并且信息不应在表之间复制；若每条信息只保存在一个表中，只需在一处进行更新，这样效率高，同时也消除了包含不同信息的重复项的可能性；每个表应该只包含关于一个主题的信息 可以独立于其他主题来维护每个主题的信息。例如，将教师和学生的信息分开，这样当删除一个学生信息是不会影响教师信息。,2002年10月31日星期四,分析要建立哪些数据表,为使DB的信息满足规范化要求，信息应按不同主题分开，单独建表。这样应建立“教师、学生、课程和

8、成绩”四个表。建立下列数据表：学生简况表教师授课表课程名称表学生成绩表,2002年10月31日星期四,确定表中的字段,表确定后，就要确定表应该包含哪些字段。在确定所需字段时，要注意每个字段包含的内容应该与表的主题相关，而且应包含相关主题所需的全部信息。还要注意不要包含需要推导或计算的数据，一定要以最小逻辑部分作为字段来保存信息。,2002年10月31日星期四,教师授课表,分析：基本特征信息应包括：姓名、性别、职称、学历、专业特长其它特征信息：工作时间、政治面貌、联系电话、课程编号、所属院系综合分析：为满足管理目标基本要求，选取下列字段：教师序号、姓名、性别、职称、课程编号、电话,2002年10

9、月31日星期四,学生简况表,分析：基本特征信息应包括：学号、姓名、性别、班级、出生日期其它特征信息：籍贯、政治面貌、宿舍、所属专业、特长综合分析：为满足管理目标基本要求，选取下列字段：学号、姓名、性别、班级、出生日期、简历,2002年10月31日星期四,课程名称表,分析：基本特征信息应包括：课程编号、教师序号、课程名称其它特征信息：学分、课时、课程先决条件、课时综合分析：为满足管理目标基本要求，选取下列字段：课程编号、课程名称、课时,2002年10月31日星期四,学生成绩表,分析：基本特征信息应包括：学号、课程编号、成绩其它特征信息：学分、教师序号、课程名称综合分析：为满足管理目标基本要求，选

10、取下列字段：学号、课程编号、成绩,2002年10月31日星期四,确定主关键字,为保证在不同表中的信息发生联系，每个表都有一个能够唯一确定每条记录的字段或字段组，该字段或字段组被称为主关键字。如果表中没有可作为主关键字的字段，可在表中增加一个字段，该字段的值为序列号，以此来标识不同记录。（自动编号）,2002年10月31日星期四,例案中数据表的主关键字,根据上述确定表的字段分析：学生简况表的主关键字为“SNO”教师授课表的主关键字为“TID”“TID”是人为加入的主关键字？课程名称表的主关键字为“CNO”学生成绩表的主关键字为“SNO”和“CNO”,2002年10月31日星期四,确定表之间的关系

11、,在确定了DB的表、表中的字段以及主关键字之后，还需要确定表之间的关系。在DB中，可以存在一个以上的表，且表与表之间存在着一定的关系。只有分析并建立起表之间的关系，才能将表中的相关信息联系在一起。注：用工具栏中“关系”和“显示表”按钮实现关系的确定。实际上是：建立联结关系,2002年10月31日星期四,优化设计,在设计完所需要的表、字段和关系之后，用户还应该检查一下所做的设计，找出设计中的不足加以改进。实际上，现在改变DB设计中的不足比表中填满了数据以后再修改要容易得多。,2002年10月31日星期四,优化设计案例分析,从以上设计中不难看出，教师库存在数据冗余。如果某教师讲授课程多于一门，他的

12、相关信息就要重复多次（存在部分依赖），这是在表设计中要尽量避免的。做如下分解：将课程信息分离出来，形成“教师课程表”，为了形成联系，在该表中增加“课程编号”字段。,教师序号,教师姓名,教师性别,课程编号,职称,电话号码,教师序号,教师姓名,教师性别,电话号码,职称,课程编号,教师序号,教师授课表,教师简况表,教师授课表,2002年10月31日星期四,优化后的数据表间的联系,学生情况表,教师简况表,课程名称表,学生成绩表,CNO,返回,SNO,CNO,教师授课表,TID,2002年10月31日星期四,优化后的数据表,最后得到的数据表共5个：教师简况表 教师序号、姓名、性别、职称、电话学生简况表

13、学号、姓名、性别、班级、出生日期、简历课程名称表 课程编号、课程名称、课时学生成绩表 学号、课程编号、分数教师授课表 课程编号、教师序号,2002年10月31日星期四,数据表结构设计(创建前的表设计1),数据表共5个：教师简况表中文名称 字段名 类型 长度 规则 定义 备注教师序号 TNO C 7 关键字（主键）姓名、TNAME C 8性别、TSEX C 2职称、TTITLE C 8 建立查询字段电话 TTEL C 7学生简况表学号、SNO C 8 关键字（主键）姓名、SNAME C 8性别、SSEX C 2 建立查询字段班级、SCLASS C 10出生日期、SBIRTHDAY D 1简历 S

14、RESUME M,2002年10月31日星期四,数据表结构设计(创建前的表设计2),课程名称表中文名称 字段名 类型 长度 规则 定义 备注课程编号、CNO C 8 关键字（主键）课程名称、CNAME C 20课时 CTIMES N 3学生成绩表学号、SNO C 8课程编号、CNO C 8 建立查询字段分数 SCORE N 3教师授课表 课程编号、CNO C 8 建立查询字段教师序号 TNO C 7,2002年10月31日星期四,二、关系数据库标准语言SQL,1。SQL概述（1）发展SQL语言，1974年有Boyce 和Chamberlin提出1975-1979年IBM在System R关系数

15、据库系统原型中实现1986年10月 ANSI标准 SQL-861989年 ISO标准 SQL-891992年 ISO SQL-92标准,2002年10月31日星期四,Structured Query language(SQL),Data Defination Language(DDL)Data Manipulation Language(DML)Data Control Language(DCL),2002年10月31日星期四,（2）SQL特点,综合统一：集数据定义语言（DDL）、数据操纵语言（DML）和数据控制语言DCL于一体，综合管理功能于一身。高度非过程化：用户只需提出“做什么”，而不需

16、指明“怎么做”。面向集合的操作方式：用户查询、修改、的数据不是一条记录，而是记录的集合。,2002年10月31日星期四,SQL特点（2）,以同一语法结构提供两种使用方式：用户可以从键盘使用SQL命令，也可以将SQL语句嵌入其他高级语言中。(PHP VC示例)语言简洁、易于使用：语句很少，数据定义：CTEATE DROP ALTER数据操纵：INSERT UPDATE DELETE数据查询：SELECT数据控制：GRANT REVOTE可以对基本表和视图进行查询,2002年10月31日星期四,ASP读取Access数据库-连接,2002年10月31日星期四,ASP读取Access数据库-显示,编

17、 名连接地址,2002年10月31日星期四,ASP读取Access数据库-关闭,2002年10月31日星期四,ASP读取Access数据库-建立表,2002年10月31日星期四,VB读取Aeecss数据库示例,Dim strdbname As StringDim strrsname As StringDim intrecs As IntegerDim strfilter As Stringmy_str=这是VB读取数据库的示例strdbname=d:test1.mdbstrrsname=select*from t1 where class=计算机01 order by score ascSet

18、 db=DBEngine.OpenDatabase(strdbname)db.Execute(insert into t1 values(计算机01,04003006,管理学,40),2002年10月31日星期四,VB读取Aeecss数据库示例(2),Set rs=db.OpenRecordset(strrsname,dbOpenDynaset)strrsname=select*from t1 where score=60 order by score descSet rs2=db.OpenRecordset(strrsname,dbOpenDynaset),2002年10月31日星期四,2.

19、数据定义,SQL的数据定义语句操作对象 创建 删除 修改表 CREATE TABLE DROP TABLE ALTER TABLE视图 CREATE VIEW DROP VIEW 索引 CREATE INDEX DROP INDEX,2002年10月31日星期四,(1)建立与修改基本表,语法：CREATE TABLE tbl_name(create_definition,.)table_options select_statement,2002年10月31日星期四,SQL举例使用的数据库设计,学生成绩表:scores班级class char(8)学号snochar(8)主键姓名snamecha

20、r(10)主键课程号cnochar(4)外码分数scoreint课程管理表:courses课程编号cnochar(4)主键课程名称cnamechar(10)学时数choursint学分creditint,2002年10月31日星期四,创建表举例,建立表 scores 主键(sno,cno)CREATE TABLE scores(class char(8),sno char(8),sname char(10),cno char(4),score int,sdate date,CONSTRAINT scno PRIMARY KEY(sno,cno)建立表 courses 主键(cno)CREATE

21、 TABLE courses(cno char(4)primary key,cname char(10),chours int,credit int),2002年10月31日星期四,(2)修改基本表,ALTERTABLE table_name ADD newcol type constraintMODIFY|ALTER col_name type DROP constraintDROP COLUMN col_name,2002年10月31日星期四,修改基本表举例（1）,增加两个字段sex,meno1ALTER TABLE scores add column sex char(2),memo1

22、char(20)修改字段长度:ALTER TABLE scores alter column sname char(8)修改字段类型ALTER TABLE scores alter column sname int,2002年10月31日星期四,修改基本表举例（2）,ALTER TABLE scores alter column sname char(8)NULLALTER TABLE scores alter column score int NULLALTER TABLE scores alter column sdate date NULLALTER TABLE scores alter

23、 column sex char(2)NULLALTER TABLE scores alter column memo1 char(30)NULL,2002年10月31日星期四,修改基本表举例（3）,改回原状ALTER TABLE scores ALTER COLUMN sname char(10)删除字段ALTER TABLE scores DROP COLUMN sex,memo1增加外码(建立两个表之间的联系联结)ALTER TABLE scores add CONSTRAINT foreign_cno FOREIGN KEY(cno)REFERENCES courses(cno),20

24、02年10月31日星期四,(3)建立索引,CREATE UNIQUECLUSTER INDEXIndex_name ON table_name(col1 ASC|DESC,)建立索引举例CREATE INDEX c_name on courses(cname ASC),2002年10月31日星期四,删除基本表与索引,DROP TABLE table_nameDROP INDEX index_name举例删除表DROP TABLE courses删除索引DROP INDEX c_name ON courses,2002年10月31日星期四,3.数据操纵,可以使用INSERT语句在表建好的表中存储

25、数据，其基本格式是：INSERTINTO table_name(column_list)VALUES(value1,value2,.)上述格式表示，一次可以插入多个数据字段。,2002年10月31日星期四,(1)数据录入,如果要用一个现成表的数据建立一个新表，则可以用下列格式完成：INSERT INTO tbl_name(col_name,.)SELECT.INSERT语句里的SELECT子句不能包含ORDER BY子句。如果tbl_name后面没有跟col_name，所有的字段值必须在VALUES()表中或由SELECT提供。任何没有明确给出值的字段都将被置为其默认值,2002年10月31日

26、星期四,插入数据举例（1）,向表中插入数据INSERT INTO courses VALUES(1011,软件基础,48,3)INSERT INTO courses(cno,cname,chours,credit)VALUES(1012,计算机网络,32,2)INSERT INTO courses(cname,cno,chours,credit)VALUES(数据库原理,1013,64,4)INSERT INTO courses(cname,cno,chours,credit)VALUES(数据库原理,1014,64,4),2002年10月31日星期四,插入数据举例（2）,INSERT INT

27、O scores(class,sno,sname,cno,score,sdate)VALUES(管工01,00094001,张家口,1012,90,2002-12-12)INSERT INTO scores(class,sno,sname,cno,score,sdate)VALUES(管工01,00094002,张网页,1012,92,2002-12-12)INSERT INTO scores(class,sno,sname,cno,score,sdate)VALUES(管工02,00094012,李逵,1012,50,2002-12-12),2002年10月31日星期四,(2)数据更新,更新

28、数据库中现存的数据，需要用到UPDATE语句，其的格式如下：UPDATE tbl_name SET col_name1=expr1,col_name2=expr2,.WHERE condition SET子句指出需要修改的字段并给出正确的值；WHERE子句是可选的，用来选择需要更新的记录。如果没有WHERE子句，则表示表中所有的记录将被全部更新。,2002年10月31日星期四,数据更新举例,更新数据UPDATE courses SET cname=数据结构 WHERE cno=1014 and chours=64UPDATE courses SET chours=48,credit=3 WHE

29、RE cname=计算机网络,2002年10月31日星期四,(3)删除记录,DELETE语句 删除数据库中整个记录或者多个记录。不能仅仅删除记录中的某一列。不影响表的结构和属性。语法如下：DELETE FROM table_name WHERE condition,2002年10月31日星期四,删除标题表中所有的行：Delete From scores删除数据满足条件的行DELETE FROM courses WHERE credit=3 and cname=“软件基础”DELETE FROM sample WHERE id1000;DELETE FROM sample WHERE id100

30、0 AND value100;,删除记录举例（1）,2002年10月31日星期四,4.数据查询,SELECT用来从表中检索数据记录。语法SELECT ALL/DISTINCT fields-listFROM table_names_listWHERE condition AND|OR conditionGROUP BY column,columnHAVING conditionsORDER BY column ASC|DESC,column ASC|DESC,2002年10月31日星期四,查询说明,2002年10月31日星期四,查询举例（1）,查询SELECT*FROM coursesSELE

31、CT*FROM courses where credit=3SELECT class,sno,sname,cname,score,chours,credit FROM scores,courses(注意,这是笛卡尔乘积),2002年10月31日星期四,查询举例(自然联结)（2）,SELECT class,sno,sname,cname,score,chours,credit FROM scores,courses where o=o(自然联结)使用JOINSELECT o,ame,scores.sno,scores.sname,scores.score FROM courses INNER J

32、OIN scores ON o=o,2002年10月31日星期四,查询举例（3）,SELECT Count(scores.sno)AS sno之Count,avg(score)AS 表达式1 FROM scores;,2002年10月31日星期四,查询举例（4）,SELECT scores.class,Count(scores.sno)AS sno之Count,Count(scores.sname)AS sname之Count,Avg(scores.score)AS score之Avg FROM scores GROUP BY scores.class ORDER BY scores.clas

33、s,Count(scores.sno)DESC;,2002年10月31日星期四,查询举例（5）,查找姓张的人的信息SELECT class,sno,sname,cname,score,chours,credit FROM scores,courses where o=o and sname like“张*”SELECT*FROM scores WHERE score BETWEEN 60 and 100,2002年10月31日星期四,除使用LIKE外,还可以使用IN BETWEEN 等关键字,In关键字用来判断一个表达式的值是否等于一组值中的任何一个。这组值必须用逗号分隔。Select Aut

34、hor_ID,Author_Name From AuthorWhere Author_Name IN(“Sam Adams”,“Maria Phillips”)Order By Author_Name,2002年10月31日星期四,可以在WHERE子句中使用的关键字,2002年10月31日星期四,带限制条件的检索,LIKE关键字检索以某个特殊的字符开始或是满足某个特定的通配符模式的值。,2002年10月31日星期四,带限制条件的检索（续）,使用下列语句来检索出第一个名字是三个字母并且以字母S开头的作者的记录。Select*From AuthorWhere Author_Name LIKE“S-

35、“,2002年10月31日星期四,带限制条件的检索（续）,使用下列语句从标题表中检索出标题以字母V开头的书的详细信息：Select*From Title Where Title LIKE“V%“,2002年10月31日星期四,带限制条件的检索（续）,使用下列语句从标题表中检索出标题不以字母V开头的书的信息：Select*From Title Where Title LIKE“V%“,2002年10月31日星期四,带限制条件的检索（续）,如下在WHERE子句中使用关键字IS NULL检索表中空值：Select Author_ID,Author_Name From Author Where Pho

36、ne_No IS NULL,2002年10月31日星期四,带限制条件的检索（续）,关键字BETWEEN 罗列出值在给定范围内的行。列出在1980和1990年（注意包括这两个年份）之间出版的图书：Select*From Title Where Year BETWEEN 1980 AND 1990,2002年10月31日星期四,带限制条件的检索（续）,关键字NOT IN、NOT LIKE、NOT BETWEEN和IS NOT NULL的用法相应地与关键字IN、LIKE、BETWEEN和IS NULL相反。,2002年10月31日星期四,作业,P1941、思考题：1、2、3、4、5、2、作业：15p2211、思考题：7.1、7.3、7.42、作业题：7.5P247 写出7-(2)、(4)、(6)、(8)的SQL查询语句实验、课件、大作业相关要求：http:/202.117.35.70/studyhard/,