计算机应用基础 上海大学计算中心

第6章 数据库技术 6.1.1 数据处理和数据管理 1.数据库领域中信息与数据的概念 2.数据处理与数据管理 数据处理：信息的收集、管理、加工、传播等一系列活动的总和 数据管理：对数据进行分类、组织、编码、存储、检索和维护 数据库技术：专门研究数据管理的技术

6.1.2 数据管理技术的发展 1.人工管理阶段（20世纪50年代中期以前） 2.文件系统阶段（50年代后期至60年代中期) 特点： 数据不保存在计算机内 没有专门的软件对数据进行管理 只有程序的概念，没有文件的概念 数据面向程序 2.文件系统阶段（50年代后期至60年代中期) 数据以文件形式长期保存在外存中 数据的逻辑结构与物理结构有了区别

3.数据库系统阶段(60年代后期) 6.1.3 数据库技术的特点和展望 文件组织已多样化 数据不再属于某个特定的程序 缺陷： 数据冗余 程序与数据之间的独立性不高 数据缺乏统一的管理和控制 3.数据库系统阶段(60年代后期) 6.1.3 数据库技术的特点和展望 特点： 数据整体结构化，数据的共享性高，冗余度小 程序与数据之间的独立性高 数据得到统一管理的控制

数据库技术中的基本术语： 数据库（DB）：存储在外存中的、有组织的、可共享的、冗余小的数据集合 数据库管理系统（DBMS）：位于用户与操作系统之间的一层数据管理软件，对数据库进行各种操作 数据库系统（DBS）：包括DBMS、DB、DBA（数据库管理员）及计算机系统、应用软件和众多操作者的综合系统 数据库技术展望： 能表达更复杂数据结构和语义表达能力的数据模型 结合各应用领域的点，研究适合该域的数据库技术 数据库技术与其它计算机技相互渗透，相互结合

6.2 数据模型 6.2.1 数据模型的概念 数据模型的定义：是现实世界数据特征的模拟和抽象 数据模型 的基本要求： 比较真实地模拟现实世界 容易被人理解 便于在计算机上实现 数据模型的两个层次： 概念模型（信息模型）：是现实世界的第一层抽象，是信息世界中数据征的描述 基本数据模型，是现实世界的第二层抽象，是计算机世界中数据之间关系的描述

数据模型的三个要素： 数据结构：数据模型 中最重要的一个方面，是所研究的数据对象及它们之间关系的描述，它反映了系统的静态特征 数据操作：指对数据库中各种数据对象允许执行的操作的集合，它反映了系统的动态特性 数据约束条件：一组完整性规则的集合，它给出了数据模型中的数据及其联系应具有的制约和依存规则，以确保数据的正确、有效和相容

6.2.2概念模型 信息世界中的基本概念： 实体：现实世界中客观存在并可相互区分的事物的抽象。例如：学生、课程、选课 属性：实体某一方面特性的抽象。例如：学生的学号、姓名 码：唯一标识实体的属性或属性组。例如：学生的学号、身份证号码 域：属性的取值范围。例如：身份证号码的取值范围为长度13的字符集 实体型：描述同类实体的方法，它由实体

实体集：同类实体的集合。例如：所有学生就是一个实体集 实体间的联系： 一对一联系：一个实体集中的每个实体最多与另一个实体集中的一个实体有联系 一对多联系：一个实体集中的每个实体与另一个实体集中的多个实体有联系，例如：专业与学生 多对多联系：一个实体集中的每个实体与另一个实体集中的多个实体有联系，例如：学生与课程

概念模型的表示方法---E-R图 学号 姓名 性别 系科 年龄 学生 N M 参考 课程 参考书 M N 选修 存放 成绩 库存量 N 1 书库

6.2.3 基本数据模型概述 层次模型：用树形结构表示实体类型 及实体间联系的数据模型。树的结点是记录类型，每个非根结点有且只有一个父结点。上一层记录类型和下一层记录类型之间的联系是1:N联系 信息 学院 计算机 通讯 电子 专业 学生 张 王 徐

网状模型：用有向图结构表示实体类型及实体间联系的数据类型 优点：简单、直观、查讯效率高 缺点：表示多对多的联系不便 网状模型：用有向图结构表示实体类型及实体间联系的数据类型 优点：较容易实现多对多的联系，查询效率高 缺点：数据结构复杂，编程也复杂 学生 课程 选修

关系模型：用二维表格表达实体集 学生（学号，姓名，性别，年龄，系科) 课程（课程号，课程名，先修课程号，学时，学分） 选修（学号，课程号，成绩） 优点：有严格的理论基础，概念单一；数据结构简单、清晰、易理解；存取路径对用户透明，程序与数据之间具有较高独立性 缺点：查讯效率不如非关系模型

面象对象模型：用面向对象观点来描述现实世界中事物（对象）的逻辑结构、对象立联系等的模型 优点：能够完整地描述现实世界中更复杂的数据结构

6.3 数据库的体系结构 6.3.1 概念模式 6.3.2 数据库的三级模式 模式：是数据库中数据的逻辑结构和特征的描述 模式：是数据库中全部数据的逻辑结构的特征的描述，是全体用户数据的最小并集，是DBMS管理和操作的主要对象 外模式（子模式 、用户模式）：是用户可看见和使用的局部数据的逻辑结构的特征的描述，是用户的数据视图 内模（存储模式）：是数据库中数据的物理结构和存储方式的描述

外部级 （单个用户视图） 概念级 （全局视图） 内部级 （存储视图） 三级模式结构

数据的独立性：是指应用程序和数据库的数据结构之间相互独立，不受影响 6.3.3 数据库的两级映象与数据独立性 两级映象： 模式/内模式映象：存在于概念级和内部级之间，用于定义概念模式和内模式之间的独立性 外模式/模式映象：存在于外部级和概念级之间，用于定义外模式和概念模式之间的独立性 数据的独立性：是指应用程序和数据库的数据结构之间相互独立，不受影响 物理数据的独立性 逻辑数据独立性

6.4 关系模型及SQL语言 6.4.1 关系模型 关系模型的组成用特点 关系模型的数据结构—关系 6.4.1 关系模型 关系模型的组成用特点 关系模型由关系数据结构、关系操作集合和关系完整性约束三部分组成 关系模型的数据结构—关系 关系就是一张二维表格

学号 姓名 性别 出生年月 专业 家庭地址 联系电话 物理 表头称为关系模式（表的结构） 表中的每一行称为一个元组（记录） 2001 张敏 女 1981.6 数学 大连路25号 53427578 2002 刘丰 男 1980.9 物理 中山路16号 61439258 表头称为关系模式（表的结构） 表中的每一行称为一个元组（记录） 表的每一列称为一个属性（字段） 表格中的某一具体值称为一个属性值（字段值） 元组的集合称为关系 列的取值范围称为域，同列具有相同的域，不同列可有相同的域 若关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选码（候选键） 若一个关系有多个候选码，则选取定其中一个为主码（主键）

关系的三种类型： 基本关系（基本表）：是实际存在的表，它是实际存储数据的逻辑表示 查询表：是查询结果对应的表，是一种临时表 视图表：是由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据

学号 姓名 性别 出生年月 专业 家庭地址 联系电话 物理 关系模型中关系的性质 每一列中的属性值的类型必须相同 2001 张敏 女 1981.6 数学 大连路25号 53427578 2002 刘丰 男 1980.9 物理 中山路16号 61439258 关系模型中关系的性质 每一列中的属性值的类型必须相同 行列的顺序位置变化不影响所描述的内容 不能有任意两个元组完全相同 表中属性名不能相同 属性值是基本数据项，不可分割

关系的三类完整性约束 导师编号 姓名 性别 职称 301 程星 女 教授 308 刘成柄 男 副教授 316 李慈宏 学号 姓名 研究方向 实体完整性：关系中元组的主键值不能为空 导师编号 姓名 性别 职称 301 程星 女 教授 308 刘成柄 男 副教授 316 李慈宏 导师 主键 研究生 学号 姓名 研究方向 导师编号 98104 程明 计算机 308 98016 刘英 通讯 316 98107 刘伟海 自动化 98110 李刚强 318 外键 允许为空值 错误值

关系操作与关系数据语言 参照完整性规则：外键必须是另一个表的主键的有效值，或者是一个“空值”。 用户定义的完整性规则：由用户定义的针对某些具体要求的约束条件。如学生成绩应大于或等于零 关系操作与关系数据语言 关系操作：选择、投影、连接、除、并、交、差等查询操作和增加、删除、修改等更新操作 关系语言：SQL

学生关系模式 S（SNO,SNAME,AGE,SEX,SDEPT) 学习关系模式 SC(SNO,CNO,GRADE) 课程关系模式 C(CNO,CNAME,CDEPT,TNAME)

SNO SNAME AGE SEX SDEPT S1 程宏 19 男 计算机 S3 刘莎莎 18 女 通讯 S4 刘刚可 20 法学 S6 将天云 国贸 S9 王莉 21 SNO CNO GRADE S3 C3 87 S1 C2 88 S4 79 S9 C4 83 76 S6 68 C1 78 64 86 学生关系 CNO CNAME CDEPT TNAME C2 离散数学 计算机 汪宏伟 C3 高等数学 通讯 钱红 C4 数据结构 马良 C1 计算机原理 李兵 学习关系 课程关系

6.4.2 SQL概述 SQL语言的产生及发展 SQL语言的组成及特点 SQL：Structured Query Language 组成： 数据定义语言DDL：主要定义数据库的逻辑结构，包括定义基本表、索引和视图，还有对基本表、索引和视图的删除及修改。有CREATE、DROP、ALTER等语句 数据操作语言DML：包括数据查询、插入、删除和修改。有SELECT、INSERT、DELETE、UPDATE等语句 数据控制语言DCL：主要有对基本表和视图的授权、事务控制等与数据库系统安全有关的操作。有GEANT和REVOKE等语句

主要特点 综合统一 面向集合的操作方式 高度非过程化 以同一种语法结构提供两种使用方式 语言简捷，易学易用

用户1 用户2 用户3 视图1 视图2 外模式 模式 基表1 基表1 基表1 基表1 存储文件1 存储文件2 内模式

6.4.3 数据定义 SQL中的数据类型 字符串型 CHAR(n) 长度为n的定长字符串 VARCHAR(n) 最大长度为n的变长字符串 数值型 SMALLINT 短整数 INT或INTEGER 长整数 DECIMAL(p,s)或NUMERIC(p,s)具有p位数字和s位小数的定点数 REAL 单精度浮点型 FLOAT或DOUBLE PRECISION 双精度浮点型 日期型 DATE 日期，格式为YYYY-MM-DD TIME 时间，格式为：HH:MM:SS

2.数据库的创建 3.基表（关系）的创建、修改和删除 VFP和SQL Server CREATE DATABASE 关系数据库名 创建基表 CREATE TABLE <表名>(<列名><数据类型>[<列级完整性约束条件>] [,<列名><数据类型>[<列级完整性约束条件>]] …… [,<表级完整性>] )

例1：创建一个学生表s，它由学号sno、姓名Sname、性别Ssex、年龄sage、所在系Sdep五个属性组成，其中学号为主键，姓名取值不能为空且要唯一，性别只能取男或女 CREATE TABLE S(Sno CHAR(6) PRIMARY KEY, Sname CHAR(8) NOT NULL UNIQUE, Ssex CHAR(2) CHECK (Ssex IN (‘男’,’女’))， Sage SMALLINT, Sdept CHAR(14) )

例2:创建一个选修表SC，它由学号Sno、课程号Cno、成绩Grade三个属性组成。其中（学号，课程号）为主键，成绩取值为空或在0 到100之间 CREATE TABLE SC(Sno CHAR(6) FOREIGN KEY REFERENCES S(Sno), Cno CHAR(6) FOREIGN KEY REFERENCES C(Cno), Grade SMALLINT CHECK ( Grade IS NULL OR Grade BETWEEN 0 AND 100), PRIMARY KEY (Sno,Cno)

例3：在基表S中增加电话号码(Phone)属性 修改基表 例3：在基表S中增加电话号码(Phone)属性 ALTER TABLE S ADD Phone CHAR(8) 例4：在基表中删除电话号码(Phone)属性 ALTER TABLE S DROP Phone 删除基表 例5：删除基表SC DROP TABLE SC

4.索引的创建和删除 创建索引 CREATE [UNIQUE] INDEX <索引号> ON <基表名>(<列名>[<次序>],<列名>[<次序>]]…) 例6：为S和SC两个表建立索引。其中S表按年龄升序建索引，SC表按学号升序和课程号降序建唯一索引 CREATE INDEX S_Sage ON S(Sage) CREATE UNIQUE INDEX SC_SnoCno ON SC(Sno,Cno DESC)

删除索引 DROP INDEX <索引名> 例7：删除SC表上的SC_SnoCno索引 DROP INDEX SC_SnoCno

6.4.4 数据查询 格式 SELECT [ALL|DISTINCT] <目标列表达式>[, <目标列表达式>]… FROM <基表名或视图名>[,<基表名或视图名>]… [WHERE <记录过滤条件>] [GROUP BY <列名>[,<列名>]… [HAVING <小组过滤条件>] [ORDER BY <列名> [ASC|DESC][,<列名>[ASC|DESC]]…]

S表 C表 Sno Sname Ssex Sage sdept 99001 张敏 女 20 MA 99002 刘丰 男 21 IS 99003 王翔 19 CS 99004 陆逸 18 S表 Cno Cname Cpno Ctime Ccredit 1 数据库 5 72 4 2 高等数学 108 6 3 信息系统 54 操作系统 数据结构 7 数据处理 C语言 7218 C表

Sno Cno Grade 99001 2 82 6 89 7 94 99002 50 63 99003 56 5 51 1 75 99004 87 3 88 92

1.单表查询 选择表中的若干列 例8:查询全体学生的全部信息 SELECT * FROM S 例9:查询全体学生的姓名、学号、年龄 SELECT Sname,Sno,Sage 例10:查询全体学生的姓名、性别及出生年份 SELECT Sname,SSex,2002-Sage

选择表中的若干行 例11：查询被学生选修过了的课程号 SELECT Cno FROM SC 有重复记录（元组） SELECT DISTINCT Cno FORM SC 无重复记录 例12:查询全体女学生的姓名、年龄和系科 SELECT Sname,Sage,Sdept FROM S WHERE Ssex=‘女’

例13：查询全体年龄在20岁以下的女生的姓名和系科 SELECT Sname,SSex,Sdept FROM S WHERE Sage<20 例14：查询全体年龄在20岁以下的女生的姓名和系科 SELECT Sname,Sdept WHERE Sage<20 AND Ssex=‘女’

例15：查询学时数在60到70之间的课程号和课程名 SELECT Cno,Cname FROM C WHERE Ctime>=60 AND Ctime<=70 WHERE Ctime BETWEEN 60 AND 70 例16：查询学时数小于60或大于70的课程号和课程名 FROM C WHERE Ctime<60 OR Ctime>70

例17:查询至少选修了1 号、5号或7号课程中任意一门课程的学生学号 SELECT DISTINCT Sno FROM SC WHERE Cno=‘1’ OR Cno=‘5’ OR Cno=‘7’ WHERE Cno IN(‘1’,’5’,’7’) 例18：查询既不是物理系、信息系，也不是计算机系的学生学号和姓名 SELECT Sno,Sname FROM S WHERE Sdept!=‘PH’ AND Sdep!=‘IS’ AND Sdep!=‘CS’

例19：查询没有先修课程的课程号和课程名 SELECT Cno,Cname FROM C WHERE Cpno IS NULL 例20：查询姓张的学生的姓名、性别和系科 SELECT Sname,Ssex,Sdept FROM S WHERE Sname LIKE ‘张%’

例21:查姓姓名中第二个字为“丰”的学生的姓名、性别和系科 SELECT Sname,Ssex,Sdept FROM S WHERE Sname LIKE ‘_丰%’ 例22:查询姓名中有“伟”字的学生的姓名，性别和科系 WHERE Sname LIKE ‘%伟%’

例23：查询不姓张的学生的姓名、性别和科系 SELECT Sname,Ssex,Sdept FROM S WHERE Sname NOT LIKE ‘张%’ 对查询结果排序 例24：查询选修了7号课程的学生和成绩，并按成绩降序排列 SELECT Sno,Grade FROM SC WHERE Cno=‘7’ ORDER BY Grade DESC

例25：查询选修表的所有信息。查询结果按课程号升序排列，同一门课程按成绩降序排列 SELECT车员Cno,Sno,Grade FROM SC ORDER BY Cno,Grade DESC

2.集函数与分组查询 集函数 COUNT（*）统计元组的个数 COUNT([DISTICT|ALL]<列名>) SUM([DISTICT|ALL]<列名>) AVG([DISTICT|ALL]<列名>) MAX([DISTICT|ALL]<列名>) MIN([DISTICT|ALL]<列名>)

例26:找出全体男生的总人数 SELECT COUNT(*) FROM S WHERE Ssex=‘男’ 例27：找出选修了课程的学生总人数 SELECT COUNT(DISTICT Sno) FROM SC 例28：找出7号课程的最高分、最低分和平均分 SELECT MAX(Grade),MIN(Grade),AVG(Grade) WHERE Cno=‘7’

分组查询 例29:找出各个系的学生人数 SELECT Sdept ,COUNT(*) FROM S GROUP BY sdept 例30:找出各个系男女学生的人数 SELECT Sdept,Ssex,COUNT(*) GROUP BY Sdept,Ssex

HAVING短语 例31:找出选修了3门以上课程的学生的学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)>3 例32:找出每门课程的成绩在80分以上的学生的学号 HAVING MIN(Grade)>80

例33:找出平均分在90分以上，且没有一门课程成绩在80分以下的学生的学号 SELECT Sno FROM SC GROUP BY Sno HAVING AVG(Grade)>90 AND MIN(Grad)>=80 例34:找出至少有2门课程不及格的学生的学号 WHERE Grade<60 HAVING COUNT(Cno)>=2

3.连接查询 连接查询：将几个数据表连接成一个临时表，再从该表中查询 连接的执行过程 举例： 例35:找出每个学生的学号、姓名、课程号和成绩 SELECT S.sno,Sname,Cno,Grade FROM S,SC WHERE S.Sno=SC.Sno

例36：找出选修了“数据结构”课程的学生的学号及成绩 SELECT Sno,Grade FROM SC,C WHERE SC.Cno=C.Cno AND Cname=‘数据结构’ 或 SELECT Sno ,Grade FROM SC JOIN C ON SC.Cno=C.Cno WHERE Cname=‘数据结构’

例37：找出选修了“数据结构”课程的学生的姓名及成绩 SELECT Sname,Grade FROM S,SC,C WHERE S.Sno=SC.Sno AND SC.Cno=C.Cno AND Cname=‘数据结构’

4.嵌套查询 例38:找出选修了1号课程的学生的姓名用性别 SELECT Sname,Ssex FROM WHERE Sno IN (SELECT Sno FROM SC WHERE Cno=‘1’)

例39：找出与“陆逸”在同一个系学习的学生的姓名、性别及年龄 SELECT Sname,Ssex,Sage FROM S WHERE Sdept IN (SELECT Sdept WHERE Sname=‘陆逸’)

例41:找出没有选修1号课程的学生的姓名与性别 SELECT Sname,Ssex FROM S WHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno=‘1’)

6.4.5 数据更新 1.插入数据 插入一个元组 格式:INSERT <表名>[(<属性列1>[,..])] VALUES(<常量1>[,<常量2>…]) 例42:将一个学生记录(99001,张敏,女,20,MA )插入到S表中 INSERT INTO S VALUES (‘99001’,’第敏’,’女’,20,’MA’)

例43:将一个选课记录(99001,2)插入到SC表中 INSERT INTO SC(Sno,Cno) VALUES(‘99001’,’2’)

插入子查询结果 列44:找出每个学生所有已考课程的平均成绩,并将结果存入数据库 CREATE TABLE SAvgGrade (Sno CHAR(6), AvgGrade SMALLINT) INSERT INTO SAvgGrade(sno,AvgGrade) FROM SC WHERE Grade is NOT NULL GROUP BY Sno

2.修改数据 修改一个元组 例45:将学号为99001的学生的年龄改为19岁 UPDATE S SET Sage=19 WHERE Sno=‘99001’ 修改多个元组 例46:将所有学生的年龄增加1岁 SET Sage=Sage+1

用子查询来表达修改条件 例47:将”C语言”课程的所有成绩都置为零 UPDATE SET Grade=0 WHERE Cno IN (SELECT Cno FROM C WHERE Cname=‘C语言’)

3.删除数据 删除一个元组 例48:将99001号学生的2号课程选课记录删除 DELETE FROM SC WHERE Sno=‘99001’ AND Cno=‘2’ 删除多个元组 例49:将99001号学生的所有选课记录都删除 WHERE Sno=‘99001’

用子查询来表达删除条件 例50:删除所有”C语言”课程的选课记录 DELETE FROM SC WHERE Cno IN (SELECT Cno FROM C WHERE Cname=‘C语言’) 4.更新数据与数据库的致性

6.4.6视图 视图是从一个或几个基表导出的表，它本身不独立存储在数据库中 1.定义视图 CREATE VIEW <视图名>[(<列名1>[,<列名2>…])] As <子查询> 例51:建立计算机系全体学生视图 CREATE VIEW CS_S AS SELECT Sno,Sname,Ssex,Sage FROM S WHERE Sdept=‘CS’

例52:建立选修了“数据库”课程的所有学生的视图 AS SELECT S.Sno,Sname,Ssex,Sage,Grade CREATE VIEW S_DBS(Sno,Sname,Ssex,Sage,Grade) AS SELECT S.Sno,Sname,Ssex,Sage,Grade FROM S,SC,C WHERE S.Sno=SC.SNO AND SC.Cno=C.Cno AND Cname=‘数据库’ 例53：建立“数据库”课程成绩在80分以上的所有学生的视图 CREATE VIEW S_AVG(Sno,AVG_G) SELECT Sno,AVG(Grade) FROM SC WHERE Grade IS NOT NULL GROUP BY Sno