数据库原理与设计方法东南大学自动控制系邵家玉课件下载

《数据库原理与设计方法东南大学自动控制系邵家玉课件下载》由会员分享，可在线阅读，更多相关《数据库原理与设计方法东南大学自动控制系邵家玉课件下载（324页珍藏版）》请在装配图网上搜索。

1、单击此处编辑母版标题样式,,单击此处编辑母版文本样式,,第二级,,第三级,,第四级,,第五级,,,,*,数据库原理与设计方法,,,,课件下载：,,1,,联系方式,EMAIL,：,,,,,邵家玉@中国,,QQ: 171995639,,MSN,：,,,POPO: bistone,2,,参考书：,,1．,王能斌。数据库系统。电子工业出版社。,1995,年。,,2．,王能斌编。数据库系统原理。电子工业出版社。,2000,年。,,3．,王珊,,陈红。数据库系统原理教程。,,4．[,美,]J·D·,厄尔曼。数据库系统原理。,课程考核,3,,第一章,,Introduction,,1.1,

2、,Concepts,,1.1.1,,Data、,D,ata,B,ase、,D,ataBase,S,ystem、,D,ata,B,ase,M,anagement,S,ystem,,1.,,Data,,(,李明，男，,1972,，江苏，计算机系，,1990),,数据、信息、知识三者之间的关系：,,数据的语义即为信息，信息在计算机中的存储（表示形式）即为数据。从信息中提升、推理、推导出的新的信息即为知识。,,例如：,40,（数据）—,40,℃（信息）—发烧（知识）,4,,2.,,Database,——,DB,,3.,,Database Management System,——,DBMS,,4.,,d

3、atabase system,——,DBS,,数据库管理员,(database administrator,，简称,DBA),。,5,,6,,5.,,Data Model,,数据模型是用来描述数据的一组概念和定义。一般来说，数据的描述包括两个方面：,,(1),数据的静态特性,,它包括数据的基本结构、数据间的联系和数据中的约束。,,(2),数据的动态特性,,它指定义在数据上的操作。,,如文件系统。,,数据模型要面向现实世界，面向用户。,7,,数据模型要面向实现，面向计算机。,,1),,conceptual data model,,如,ER,模型、面向对象数据模型等。,,2),,logical d

4、ata model,,如关系数据模型、层次模型、网状模型等。,,3),,physical data model,,概念数据模型只用于数据库的设计，逻辑数据模型和物理数据模型用于,DBMS,的实现。,8,,6.,,Data Schema,,type:,型是该数据所属数据类型的说明。,,value:,值是型的一个实例,(instance,或,occurrence),。,,对某一类数据的结构、联系和约束的描述是型的描述，型的描述称为数据模式（,Data Schema,）。在同一数据模式下，可以有很多的值，即实例。,,例如，学生记录可以定义为图,1-3(a),的形式，这是数据模式。而图,1-3(b),

5、是其一个实例。,9,,10,,数据模型是描述数据的手段．而数据模式是用给定数据模型对具体数据的描述。,,美国国家标准协会,(ANSI),的,ANSI,／,X3,／,SPARC,报告把数据模式分为三级,(,见图,1-4),。,11,,1),,conceptual schema/logical schema,,2),,external schema,,3),,internal schema,,7.,,Database Instance,,数据模式是相对稳定的，而实例是相对变动的。数据模式反映一个单位的各种事物的结构、属性、联系和约束，实质上是用数据模型对一个单位的模拟。而实例反映数据库的某一时刻的

6、状态，也就是这一单位在此时的状态。,12,,1.1.2,,数据库技术的产生与发展,,1.,,人工管理阶段,,,人工管理数据具有如下特点：,,1),,数据不保存。,,2),,数据需要由应用程序自己管理，没有相应的软件系统负责数据的管理工作。,,3),,数据不共享。,,4),,数据不具有独立性。,,,人工管理阶段应用程序与数据之间的对应关系可用图,l-3,表示。,13,,2.,文件系统阶段,,用文件系统管理数据具有如下特点：,,1),数据可以长期保存。,,2),由专门的软件即文件系统进行数据管理。,,3),数据共享性差。,,4),数据独立性低。,14,,文件系统阶段应用程序与数据之间的关系如图,1

7、-4,所示。,3.,,数据库系统阶段,,用数据库系统来管理数据具有如下特点：,15,,1),,数据结构化,,学生人事记录,学号,,姓名,,性别,,系别,,年龄,,政治面貌,,家庭出身,,籍贯,,家庭成员,,奖惩情况,,图,1-5,16,,17,,18,,2),,数据的共享性好，冗余度低,,3),,数据独立性高,,4),,数据由,DBMS,统一管理和控制,,l,,数据的安全性,(security),,l,,数据的完整性,(integrity),,l,,,并发,(concurrency),控制,,l,,数据库恢复,(recovery),19,,量大,,持久,,共享,20,,1.1.3,,数据库技术

8、的研究领域,,1.,,数据库管理系统软件的研制,,2.,,数据库设计,,3.,,数据库理论,,1.2,,数据库工程与应用,,1.2.1,,数据库设计的目标与特点,21,,图 1-10,22,,1.2.2,,数据库设计方法,,新奥尔良方法：需求分析,(,分析用户要求,),、概念设计,(,信息分析和定义,),、逻辑设计,(,设计实现,),和物理设计,(,物理数据库设计,),。,,S,．,B,．,Yao,：需求分析、模式构成、模式汇总、模式重构、模式分析和物理数据库设计。,,I,．,R,．,Palmer,则主张把数据库设计当成一步接一步的过程，并采用一些辅助手段实现每一过程。,,此外，基于,E—R

9、,模型的数据库设计方法，基于,3NF(,第三范式,),的设计方法，基于抽象语法规范的设计方法等。,23,,规范设计法在具体使用中又可以分为两类：手工设计和计算机辅助数据库设计。,,ORACLE Designer 2000,,1.2.3,,数据库设计步骤,1.,需求分析,,2.,概念结构设计,,3.,逻辑结构设计,图,1-11,4.,数据库物理设计,,5.,数据库实施,,6.,数据库运行和维护,24,,在数据库设计过程中必须注意以下问题。,,1.,数据库设计过程中要注意充分调动用户的积极性。,,2.,应用环境的改变、新技术的出现等都会导致应用需求的变化，因此设计人员在设计数据库时必须充分考虑到系

10、统的可扩充性，使设计易于变动。,,3.,系统的可扩充性最终都是有一定限度的。,,1.2.4,,Database Application,25,,各种用户的数据视图,26,,DBA,主要职责包括：,,1.,,设计与定义数据库系统,,2.,,帮助最终用户使用数据库系统,,3.,,监督与控制数据库系统的使用和运行,,4.,,改进和重组数据库系统，调优数据库系统的性能,,5.,,转储与恢复数据库,,6.,,重构数据库,27,,第二章,,Data Model,,数据模型应满足三方面要求：一是能比较真实地模拟现实世界；二是容易为人所理解；三是便于在计算机上实现。,,两类：概念模型也称信息模型，数据模型包括

11、网状模型、层次模型、关系模型。,,2.1,,数据模型的要素,,2.1.1,,数据结构,,2.1.2,,数据操作,,2.1.3,,数据的约束条件,28,,2.2,,概念模型——E-R Data Model,29,,2.2.1,,Concepts,,E-R,数据模型（,Entity-Relationship Data Model,）,,EER,数据模型（,Extended Entity-Relationship Data Model,）,,1．,实体,(entity),、实体集（,entity set,）,,entity set,与,entity,是型（,type,）与值（,value,）的关系（

12、类似于前述,data schema,与,database instance,）,,2．,属性（,attribute,）,,值集（,value set,）,,实体键（,entity key,）实体主键（,entity primary key,）,30,,3．,,联系（,relationship,）,,基数比约束（cardinality ratio constraint）,,参与约束（participation constraint）：部分参与、全参与,,结构约束（structural constraint）,,两个实体之间的联系可以分为三类：,,l,,一对一联系,(1:1),,l,,一对多联系,

13、(1:m),,l,,多对多联系,(m:n),,所有（,ownership,）关系——弱实体（,weak entity,）,31,,2.2.2,,E-R diagram,,用,E-R,数据模型对某一单位进行模拟，可以得到,ER,数据模式，,ER,数据模式可以,ER,图来直观地表示。,,entity:,,weak entity:,,relationship:,,attribute:,,示例：,32,,教职工,研究生,班级,职工编号,姓名,出生年月,职称,是否博导,是否硕导,学号,姓名,出生年月,学位类型,是否在职,课程,课程号,名称,开课学期,学时,上课地点,学分,班级号,信箱,33,,教职工,班

14、级,研究生,课程,班主任,C_G,导师,任课,可担任,选课,M,N,1,N,N,N,M,M,N,M,M,N,止,起,时间,止,起,时间,类型,性质,成绩,类型,类型,专业,方向,说明：,34,,1．,学位类型：硕士,/,博士,,2．,导师类型：主要指导老师、协助指导,,3．,研究生可能换导师，换专业、方向,,4．,选课性质：学位课,/,非学位课,,5．,任课类型：主讲,/,辅讲,,6．,可担任描述有哪些老师可以上哪些课,,7．,任课是指目前该课程的任课老师,,8．,开课学期：春,/,秋季,,9．,上课地点：目前该课程的上课教室,35,,问题：,,1．,课性质属性为什么不属于课程实体，而属于选课

15、联系？,,2．,专业、方向可不可以属于研究生？,,2.2.3,,EER data model,,1．,特殊化（,specialization,）和普遍化（,generalization,）,,全特殊化（,total specialization,）,/,部分特殊化（,partial specialization,）,,不相交特殊化（,disjoint specialization,）,/,重叠特殊化（,overlapping specialization,）,36,,2．,,聚集（,aggregation,）,,3．,,范畴（,category,）,37,,2.3,,Hierarchy Dat

16、a Model,,2.3.1,,层次数据模型的数据结构,,1．,层次模型的基本结构,图 TS数据模式,38,,图 TS数据模式的一个值,39,,2．,多对多联系在层次模型中的表示,,2.3.2,,层次数据模型的操纵与完整性约束,,2.3.3,,层次数据模型的存储结构,,2.3.4,,层次数据模型的优缺点,,层次数据模型的优点主要有：,,l,,层次数据模型本身比较简单，只需很少几条命令就能操纵数据库，比较容易使用。,,l,,对于实体间联系是固定的，且预先定义好的应用系统，采用层次模型来实现，其性能优于关系模型，不次于网状模型。,,l,,层次数据模型提供了良好的完整性支持。,40,,层次数据模

17、型的缺点主要有：,,l,,现实世界中很多联系是非层次性的，如多对多联系、一个结点具有多个双亲等，层次模型表示这类联系的方法很笨拙，只能通过引入冗余数据,(,易产生不一致性,),或创建非自然的数据组织,(,引入虚拟结点,),来解决。,,l,,对插入和删除操作的限制比较多。,,l,,查询子女结点必须通过双亲结点。,,l,,由于结构严密，层次命令趋于程序化。,,2.4,,网状数据模型,,2.4.1,,网状数据模型的数据结构,41,,2.4.2,,网状数据模型的操纵与完整性约束,,2.4.3,,网状数据模型的存储结构,,2.4.4,,网状数据模型的优缺点,42,,网状数据模型的优点主要有：,,l,,能

18、够更为直接地描述现实世界，如一个结点可以有多个双亲、允许结点之间为多对多的联系等。,,l,,具有良好的性能，存取效率较高。,,网状数据模型的缺点主要有：,,l,,其,DDL,语言极其复杂。,,l,,数据独立性较差。由于实体问的联系本质上是通过存取路径指示的，因此应用程序在访问数据时要指定存取路径。,43,,2.5,,Relation Data Model,,2.5.1,,Concepts,,1.,,Attribute and Domain,,Domain:,第一范式,1NF(first nomal form) atomic data,,非第一范式（,Non-First Nomal Form,

19、）,NF,2,,空值：,NULL,,2.,,relation and tuple,,设有一命名为,R,的关系，它有属性,A,1,、,A,2,、…、,A,n,，其对应的城分别为,D,l,、,D,2,、…、,D,n,则关系,R,可表示为：,44,,R,＝,(D,1,/A,l,，,D,2,/A,2,，…，,D,n,/A,n,),,或,R,＝,(A,1,，,A,2,，…，,A,n,),,或,R,＝,(A,1,A,2,…,A,n,),,R.A,1,表示关系,R,的属性,A,1,。,,degree(arity):n,,R,的值：,r r(R),,r={t,1,,t,2,,,…,,t,m,},,t=

20、1,,v,2,,,…,,v,n,>,，,v,i,∈,D,i,,1,≤,i,≤,n,,笛卡尔乘积×,45,,A B A×B,D,,E,,F,,G,,D,,E,,F,,G,,1,,2,,5,,6,,1,,2,,5,,6,,3,,4,,7,,8,,1,,2,,7,,8,,×,,9,,0,,=,,1,,2,,9,,0,,3,,4,,5,,6,,3,,4,,7,,8,,3,,4,,9,,0,,46,,关系模式：,,SUDENT(,姓名，学号，性别，出生年份．籍贯，系别，入学年份,),,<,李彤，，女，,1975,，江苏，计算机系，,1993>,,投影：,R[X

21、] t[X] STUDENT[,姓名，性别,],,3.,,key,,定义：,如果关系的某一属性或属性组的值唯一地决定其他所有属性的值，也就是唯一地决定一个元组，而其任何真子集无此性质，则这个属性或属性组称为该关系的候选键,(candidate key),，或简称为键。,,superkey,primary key,alternate key all key (SUPPLY(,供应商，零件名，工程名,)),47,,prime attribute non-prime attribute,,foreign key,,COURSE(,课程名，,课程号,，学分，开课时间，先修课程号,),,

22、GRADE(,学号，课程号,，成绩,),,2.5.2,,Constraint,,R,＝,(D,1,/A,l,，,D,2,/A,2,，…，,D,n,/A,n,),,1.,,Domain integrity constraint,,2.,,Entity integrity constraint,,3.,,Referential integrity constraint,,4.,,General integrity constraint,48,,2.5.3,,Operation,,relational algebra operations,,1.,,Select operation,,σ,,(<,关

23、系名,>),49,,2.,,Project operation,,Π,<,属性表,>,(<,关系名,>),,Π,性别，籍贯、出生年份,(STUDENT),,若<属性表,2>,包含<属性表,1>,则：,,Π,<,属性表,1>,(Π,<,属性表,2>,(R))=,Π,<,属性表,1>,(R),50,,Π,姓名,(σ,性别=‘女’,(STUDENT)),,3.,,Set operation,,A,∩,B,≡,A-(A-B),,union compatibility,,Π,课程号,(COURSE)-,Π,先修课程号,(COURSE),,σ,系别=‘计算机系’,(STUDENT),∪σ,系别=‘电子系’

24、,(STUDENT),,R,×,S={|t,∈,R AND g,∈,S},,4,,Join operation,,R,,S=,σ,,(R,×,S),,连接条件：,<,条件,1>AND<,条件,2>AND,……,AND <,条件,k>,,θ连接：,A,i,θ,B,j,51,,等连接（,equijoin,）,,自然连接（,natural join,）,,例：,,GRADE,GRADE.,课程号,=COURSE.,课程号,（Π,课程名，课程号，学分,（,COURSE,））,,关系代数操作集,{,σ，Π，∪，-，×,},是完备的操作集。,,{,σ，Π，∪，-，,},,relationally comp

25、lete,,5.,,Outer join operation,,6.,,Outer union operation,52,,2.5.4,,Relational Calculus,,1.,,Tuple Relational Calculus,,2.,,Domain Relational Calculus,53,,第三章,,Database Language SQL,,结构化查询语言,(structured query language,，简称,SQL),,3.1,,Introduction,,SQL,语言是,1974,年由,Boyce,和,Chamberlin,提出的。,,1975,年至,197

26、9,年,IBM System R,实现了这种语言。,,1986,年,10,月,,美国国家标准局,(,简称,ANSI) SQL-86,,1987,年国际标准化组织（简称,ISO,）也通过了这一标准。,,ANSI 1989,年第二次公布,SQL,标准,(SQL-89),,1992,年,SQL-92,标准,54,,目前,ANSI,正在酝酿新的,SQL,标准：,SQL3,。,,现在,SQL,已被重新解释成为：,Standard Query Language,,SQL,按其功能可分为四大部分：,,1.,,数据定义语言,(Data Definition Language,，简称,DDL),,2.,,查询

27、语言,(Query Language,，简称,QL),,3.,,数据操纵语言,(Data Manipulation Language,，简称,DML),,4.,,数据控制语言,(Data Control Language,，简称,DCL),55,,3.1.1,,SQL的特点,,1.,,综合统一,,2.,,高度非过程化,,3.,,面向集合的操作方式,,4.,,以同一种语法结构提供两种使用方式,,5.,,语言简洁，易学易用,,表,3-1 SQL,语言的动词,56,,3.1.2,,SQL语言的基本概念,57,,3.2,,数据定义,,表,3-2 SQL,的数据定义语句,3.2.1,,定义、删除与修改基

28、表,,1.,,定义基表,58,,CREATE TABLE,＜表名＞,(,＜列名＞＜数据类型＞,[,列级完整件约束条件,][,,＜列名＞＜数据类型＞,[,列级完整性约束条件,],……,],,[,＜表级完整性约束条件＞,],）；,,列级完整性约束条件格式：,,[NOT NULL [UNIQUE]] [DEFAULT,字值,|USER|NULL],,表级完整性约束条件有三个任选项。用于定义主键的,PRIMARY KEY,子句，用于定义外键的,FOREIGN KEY,子句和用于定义列值限制条件的,CHECK,子句。格式：,59,,[,，,PRIMARY KEY,（,<,列名,>,…）,],,[,，,F

29、OREIGN KEY [,外键名,],（,<,列名,>,…）,REFERENCES <,表名,>[ON DELETE,RESTRICT,|CASCADE|SET NULL]],,[,，,CHECK,（条件）……,],,IBM DB2 SQL,主要支持以下数据类型：,,SMALLINT,半字长二进制整数。,,INTEGER,或,INT,全字长二进制整数。,,DECIMAL(p[,q]),或,DEC(p[,q]),压缩十进制数，共,p,位，其中小数点后有,q,位。,0≤q≤p≤15,q,＝,0,时可以省略。,,FLOAT,双字长浮点数。,,CHARTER(n),或,CHAR(n),长度为,n,的定

30、长字符串。,,VARCHAR(n),最大长度为,n,的变长字符串。,60,,GRAPHIC(n),长度为,n,的定长图形字符串。,,VARGRAPHIC(n),最大长度为,n,的变长图形字符串。,,DATE,日期型，格式为,YYYY—MM—DD,。,,TIME,时间型，格式为,HH.MM.SS,。,,TIMESTAMP,日期加时间。,,例,1,建立,Student(,学生,),、,Course,（课程）、,SC,（选课）表。,,1,．,“,学生,”,表,student,由学号,(Sno),、姓名,(Sname),、性别,(Ssex),、年龄〔,Sage,〕、所在系,(Sdept)5,个属性组成

31、，可记为,,Student(Sno,，,Sname,，,Ssex,，,Sage,，,Sdept),,其中,sno,为主键。,61,,2,．,“,课程,”,表,course,由课程号,(Cno),、课程名,(Cname),、先修课号,(Cpno),、学分,(Ccredit)4,个属性组成，可记为：,,Course(Cno,，,Cname,，,Cpno,，,Ccredit),,其中,Cno,为主键。,,3,．,“,学生选课,”,表,SC,由学号,(Sno),、课程号,(Cno),、成绩,(Grade)3,个属性组成，其中,(Sno,，,Cno),为主键。,,CREATE TABLE Student

32、,,(Sno CHAR(5) NOT NULL UNIQUE,,,Sname VARCHAR(20) NOT NULL,,,Ssex CHAR(1),，,,Sage INT,,,Sdept CHAR(15),,,PRIMARY KEY(Sno)),；,62,,CREATE TABLE Course,,(Cno CHAR(1) NOT NULL,,,Cname VARCHAR(20),,,Cpno CHAR(1),,Ccredit DEC(2,1),,,PRIMARY KEY(Cno),,,FOREIGN KEY (Cpno) REFERENCES Cours

33、e ON DELETE RESTRICT),；,63,,CREATE TABLE SC,,(Sno CHAR(5) NOT NULL,,,Cno CHAR(1) NOT NULL,,,Grade DEC(4,1) DEFAULT NULL,,,PRIMARY KEY(Sno,Cno),,,FOREIGN KEY (Sno) REFERENCES Student ON DELETE CASCADE,,,FOREIGN KEY (Cno) REFERENCES Course ON DELETE RESTRICT);,64,,2.,,修改基表,,ALTER TABLE,＜表名＞,,[ADD,＜新

34、列名＞＜数据类型＞,[,完整性约束,]],,[DROP,＜完整性约束名＞,],,[MODIFY,＜列名＞＜数据类型＞,],；,,例,2,向,student,表增加,“,入学时间,”,列，其数据类型为日期型。,,ALTER TABLE Student ADD Scome DATE,；,,例,3,将年龄的数据类型改为半字长整数。,,ALTER TABLE Student MODIFY Sage SMALLINT;,,例,4,删除（撤消）,Student,表主键定义。,,ALTER TABLE Student DROP PRIMARY KEY,；,65,,3.,,删除基表,,DROP TABLE,

35、＜表名＞；,,例,5,删除,Student,表。,,DROP TABLE Student,；,,3.2.2,,建立与删除索引,,1.,,建立索引,,CREATE [UNIQUE] [CLUSTER] INDEX <,索引名,> ON <,表名,>(<,列名,> [,次序,] [,，,<,列名,> [,次序,]],…,),；,,排列次序，包括,ASC(,升序,),和,DESC(,降序,),两种，缺省值为,ASC,。,,CREATE CLUSTER INDEX Stusname ON Student(Sname);,66,,例,6,为学生,—,课程数据库中的,Student,，,Course,，,

36、SC 3,个表建立索引。其中,Student,表按学号升序建立唯一索引，,course,表按课程号升序建立唯一索引，,SC,表按学号升序和课程号降序建唯一索引。,,CREATE UNIQUE INDEX Stusno ON Student(Sno);,,CREATE UNIQUE INDEX Coucno ON Course(Cno);,,CREATE UNIQUE INDEX SCno ON SC(Sno ASC,，,Cno DESC);,,2.,,删除索引,,DROP INDEX,＜索引名＞；,,例,7,删除,Student,表的,Stusname,索引。,,DROP INDEX Stus

37、name;,67,,3.3,,查询,,SELECT [ALL| DISTINCT],＜目标列表达式＞,[,，＜目标列表达式＞,],…,,FROM,＜表名或视图名＞,[,，＜表名或视图名＞,],…,,[WHERE,＜条件表达式＞,],,[GROUP BY,＜列名,1,＞,[HAVING,＜条件表达式＞,]],,[ORDER BY,＜列名,2,＞,[ASC | DESC]],；,68,,3.3.1,,单表查询,,1.,,选择表中的若干列,,1),,查询指定列,,例,1,查询全体学生的学号与姓名。,,SELECT Sno,，,Sname,,FROM Student;,,例,2,查询全体学生的姓名、学

38、号、所在系。,,SELECT Sname,，,Sno,，,Sdept,,FROM Student,；,,2),,查询全部列,,例,3,查询全体学生的详细记录,,SELECT *,,FROM Student,；,69,,3),,查询经过计算的值,,例,4,查询全体学生的姓名及其出生年份。,,SELECT Sname,，,2004-Sage,,FROM Student,；,,例,5,查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所在系名,,SELECT Sname,，‘,Year of Birth,：,’,，,2004-Sage,，,ISLOWER(Sdept),,FROM Studen

39、t;,,,,SELECT Sname NAME,，‘,Year of Birth,：’,BIRTH,，,2004-Sagc BIRTHDAY,，,ISLOWER(Sdept) DEPARTMENT,,FROM Student,；,70,,结果为：,,NAME BIRTH BIRTHDAY DEPARTMENT,,,李勇,Year of Birth,：,1976 cs,,,刘晨,Year of Birth,：,1977 if,,,王名,Year of Birth,：,1978 ma,,,张立,Yea

40、r of Birth,：,1978 if,,2.,,选择表中的若干元组,,1),,消除取值重复的行,,例,6,查询所有选修过课的学生的学号。,,SELECT Sno,,FROM SC,；,71,,假设,SC,表中有下列数据：,,Sno Cno Grade,,95001 1 92,,95001 2 85,,95001 3 88,,95002 2 90,,95002 3 80,,执行上面的,SELECT,语句后，结果为：,,Sno,,95001,,95001,,95001,,95002,,95002,72,,SELEC

41、T DISTINCT Sno,,FROM SC,；,,执行结果为：,,Sno,,95001,,95002,,,,SELECT Sno,,FROM SC,；,,与,,SELECT ALL Sno,,FROM SC,；,,完全等价。,73,,2),,查询满足条件的元组,,表,3-5,常用的查询条件,①比较大小,,＝,,等于,,＞,,大于,,＜,,小于,74,,＞＝,,大于等于,,＜＝,,小于等于,,！＝或＜＞,,不等于,,有些产品中还包括：,,！＞,,不大于,,！＜,,不小于,,逻辑运算符,NOT,可与比较运算符同用，对条件求非。,,例,7,查计算机系全体学生的名单。,,SELECT Sname,

42、,FROM Student,,WHERE Sdept,＝‘,CS,’；,75,,例,8,查所有年龄在,20,岁以下的学生姓名及其年龄。,,SELECT Sname,，,Sage,,FROM student,,WHERE Sage,＜,20,；,,或,,SELECT Sname,，,Sage,,FROM student,,WHERE NOT Sage>=20,；,,例,9,查考试成绩有不及格的学生的学号。,,SELECT DISTINCT Sno,,FROM SC,,WHERE Grade,＜,60,；,76,,②确定范围,,谓词,BETWEEN,．．．,AND,．．．和,NOT BETWEEN

43、,．．．,AND,．．．可以用来查找属性值在,(,或不在,),指定范围内的元组，其中,BETWEEN,后是范围的下限,(,即低值,),，,AND,后是范围的上限,(,即高值,),。,,例,10,查询年龄在,20,至,23,岁之间的学生的姓名、系别和年龄。,,SELECT Sname,，,Sdept,，,Sage,,FROM Student,,WHERE Sage BETWEEN 20 AND 23,；,,与,BETWEEN,．．．,AND,．．．相对的谓词是,NOT BETWEEN,．．．,AND,．．．。,77,,例,11,查询年龄不在,20,至,23,岁之间的学生姓名、系别和年龄。,,SE

44、LECT Sname,，,Sdept,，,Sage,,FROM Student,,WHERE Sage NOT BETWEEN 20 AND 23,；,,③确定集合,,谓词,IN,可以用来查找属性值属于指定集合的元组。,,例,12,查信息系,(IS),、数学系,(MA),和计算机科学系,(CS),的学生的姓名和性别。,,SELECT Sname,．,Ssex,,FROM Student,,WHERE Sdept IN(,‘,IS,’，‘,MA,’，‘,CS,’,),；,,与,IN,相对的谓词是,NOT IN,，用于查找属性值不属于指定集合的元组。,78,,例,13,查既不是信息系、数学系，也不

45、是计算机科学系的学生的姓名和性别。,,SELECT Sname,．,Ssex,,FROM Student,,WHERE Sdept NOT IN(,‘,IS,’，‘,MA,’，‘,CS,’,),；,,④字符匹配,,谓词,LIKE,可以用来进行字符串的匹配。其一般语法格式如下：,,[NOT] LIKE ‘,＜匹配串＞,’,,其含义是查找指定的属性列值与＜匹配串＞相匹配的元组，＜匹配串＞可以是一个完整的字符串，也可以含有通配符％和,_,。其中：,,％,(,百分号,),代表任意长度,(,长度可以为,0),的字符串。,79,,例如,a%b,表示以,a,开头，以,b,结尾的任意长度的字符串，,acb,a

46、defb,ab,等都满足该匹配串。,,_(,下划线,),代表任意单个宁符。例如,a_b,表示以,a,开头,,,以,b,结尾，长度为,3,的字符串，,acb,adb,等都满足该匹配串。,,例,14,查询学号为,95001,的学生的详细情况,,SELECT *,,FROM Student,,WHERE Sno LIKE,‘,9500l,’,;,,该语句实际上与下面的语句完全等价：,,SELECT *,,FROM Student,,WHERE Sno=,‘,9500l,’,;,80,,例,15,查所有姓刘的学生的姓名、学号和性别。,,SELECT Sname,Sno,Ssex,,FROM Stude

47、nt,,WHERE Sname LIKE ‘,刘,%’,；,,例,16,查姓,“,欧阳,”,且全名为,3,个汉字的学生的姓名。,,SELECT Sname,,FROM Student,,WHERE Sname LIKE ‘,欧阳,__’,；,,例,17,查名字中第二字为,“,阳,”,字的学生的姓名和学号。,,SELECT Sname,，,Sno,,FROM Student,,WHERE Sname LIKE ’__,阳％,’,；,81,,例,18,查所有不姓刘的学生姓名。,,SELECT Snamc,，,Sno,，,Ssex,,FROM Student,,WHERE Sname NOT LIK

48、E ‘,刘,%’,；,,⑤涉及空值的查询,,谓词,IS NULL,和,IS NOT NULL,可用来查询空值和非空值。,,例,19,某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面来查一下缺少成绩的学生的学号和相应的课程号。,,SELECT Sno,，,Cno,,FROM SC,,WHERE Grade IS NULL,；,82,,例,20,查所有有成绩的记录的学生学号和课程号。,,SELECT Sno,，,Cno,,FROM SC,,WHERE Grade IS NOT NULL,；,,⑥多重条件查询,,例,21,查,CS,系年龄在,20,岁以下的学生姓名,,SEL

49、ECT Sname,,FROM Student,,WHERE Sdept=,‘,CS,’,AND Sage<20;,,例,12,中的,IN,谓词实际上是多个,OR,运算符的缩写，因此，例,l2,中的查询也可以用,OR,运算符写成如下等价形式：,83,,SELECT Sname,．,Ssex,,FROM Student,,WHERE Sdept=,‘,IS,’,OR Sdept=,‘,MA,’,OR Sdept=,‘,CS,’；,,3.,,对查询结果排序,,例,22,查询选修了,3,号课程的学生的学号及其成绩，查询结果按分数的降序排列。,,SELECT Sno,，,Grade,,FROM SC,

50、,WHERE Cno,＝‘,3,’,,ORDER BY Grade DESC,；,,例,23,查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。,84,,SELECT *,,FROM Student,,ORDER BY Sdept, Sage DESC;,,4.,,使用集函数,,COUNT([DISTINCT | ALL ] *),统计元组个数,,COUNT([DISTINCT | ALL],＜列名＞,),统计一列中值的个数,,SUM([DISTINCT | ALL],＜列名＞,),计算一列值的总和,(,此列必须是数值型,),,AVG([DISTINCT | ALL]

51、,＜列名＞,),计算一列值的平均值,(,此列必须是数值型,),,MAX([DISTINCT | ALL],＜列名＞,),求一列值中的最大值,,MIN([DISTINCT | ALL],＜列名＞,),求一列值中的最小值,85,,例,24,查询学生总人数。,,SELECT COUNT,（,*,）,,FROM Student;,,例,25,查询选修了课程的学生人数。,,SELECT COUNT(DISTINCT Sno),,FROM SC;,,例,26,计算,1,号课程的学生平均成绩。,,SELECT AVG(Grade),,FROM SC,,WHERE Cno,＝,‘l’,；,,例,27,查询学习

52、,l,号课程的学生最高分数。,,SELECT MAX,（,Grade,）,,FROM SC,,WHERE Cno,＝‘,1’,；,86,,5.,,对查询结果分组,,例,28,查询各个课程号与相应的选课人数。,,SELECT Cno,，,COUNT(Sno),,FROM SC,,GROUP BY Cno,；,,例,29,查询信息系选修了,3,门以上课程的学生的学号，为简单起见，假设,SC,表中有一列,Dept,，它记录了学生所在系。,,SELECT Sno,,FROM SC,,WHERE Dept,＝‘,IS,’,,GROUP BY Sno,,HAVING COUNT(*),＞,3,；,87,,

53、3.3.2,,连接查询,,1.,,等值与非等值连接查询,,[,＜表名,1,＞．,],＜列名,1,＞＜比较运算符＞,[,＜表名,2,＞．,],＜列名,2,＞,,其中比较运算符主要有：＝、＞、＜、＞＝、＜＝、！＝。,,此外，连接谓词还可以使用下面形式：,,[,＜表名,1,＞．,],＜列名,1,＞,BETWEEN[,＜表名,2,．,]<,列名,2,＞,AND[,＜表名,2,＞．,]<,列名,3>,,当连接运算符为＝时，称为等值连接。使用其它运算符称为非等值连接。,88,,例,30,查询每个学生及其选修课程的情况。,,SELECT Student.*,，,SC.*,,FROM Student,，,SC

54、,,WHERE Student.Sno,＝,SC.Sno;,,,例,31 Student,表和,SC,表的笛卡尔积。,,SELECT Student.*,，,SC.*,,FROM Student,，,SC,,,,例,32,自然连接,Student,表和,SC,表。,,SELECT Student.Sno,，,Sname, Ssex, Sage, Sdept, Cno, Grade,,FROM Student,，,SC,,WHERE Student.Sno,＝,SC.Sno;,89,,或,,SELECT Student.*,，,Cno, Grade,,FROM Student,，,SC,,WH

55、ERE Student.Sno,＝,SC.Sno;,,2.,,自身连接,,例,33,查询每一门课的间接先修课,(,即先修课的先修课,),。,90,,SELECT FIRST.Cno,，,SECOND.Cpno,,FROM Course FIRST,，,Course SECOND,,WHERE FIRST.Cpno,＝,SECOND.Cno,；,,Cno Cpno,,1 7,,3 5,,5 6,,3.,,外连接,,例,34,,SELECT Student.Sno,，,Sname, Ssex, Sage, Sdept, Cno, Grade,,F

56、ROM Student,，,SC,,WHERE Student.Sno,＝,SC.Sno(*);,91,,Student.Sno,，,Sname, Ssex, Sage, Sdept, Cno, Grade,,9500l,李勇,,男,20 CS 1 92,,9500l,李勇,,男,20 CS 2 85,,9500l,李勇,,男,20 CS 3 88,,95002,刘晨,,女,19 IS 2 90,,95002,刘晨,,女,19 IS 3

57、 80,,95003,王名,,女,18 MA,,95004,张立,,男,18 IS,,4.,,复合条件连接,,例,35,查询选修,2,号课程且成绩在,90,分以上的所有学生。,,SELECT Student.Sno,Sname,,FROM Student, SC,,WHERE Student .Sno,＝,SC.Sno AND,,SC.Cno,＝,’2’AND SC.Grade,＞,90;,92,,结果表为；,,Student.Sno Sname,,95002,刘晨,,例,36,查询每个学生选修的课程名及其成绩。,,SELECT Student.Sno,

58、，,Sname,，,Cname,，,Grade,,FROM Student,，,SC,，,Course,,WHERE Student.Sno,＝,SC,．,Sno and SC,．,Cno,＝,COURSE,．,Cno,；,,93,,3.3.3,,嵌套查询,,SELECT Sname,,FROM Student,,WHERE Sno IN,,(SELECT Sno,,FROM SC,,WHERE Cno,＝‘,2,’,),；,,1.,,带有,IN,谓词的子查询,,例,37,查询与,“,刘晨,”,在同一个系学习的学生。,,查询与,“,刘晨,”,在同一个系学习的学生，可以首先确定,“,刘晨,”,所

59、在系名，然后再查找所有在该系学习的学生。所以可以分步来完成此查询：,94,,①确定,“,刘晨,”,所在系名,,SELECT Sdept,,FROM Student,,WHERE Sname=,‘刘晨’；,,结果为：,IS,,②查找所有在,IS,系学习的学生。,,SELECT Sno,Sname,Sdept,,FROM Student,,WHERE Sdept=,‘,IS,’；,,分步写查询毕竟比较麻烦，上述查询实际上可以用子查询来实现，即将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。,SQL,语句如下：,95,,SELECT Sno,，,Sname,，,Sdept,,FROM St

60、udent,,WHERE Sdept IN,,(SELECT Sdept,,FROM Student,,WHERE Sname,＝‘刘晨’,),；,,本例中的查询也可以用前面学过的表的自身连接查询来完成：,,SELECT S1.Sno,，,S1.Sname,，,S1.Sdept,,FROM Student S1,Student S2,,WHERE S1.Sdept=S2.Sdept AND S2.Sname=,‘刘晨’；,96,,本例中父查询和子查询均引用了,Student,表．也可以像表的自身连接查询那样用别名将父查询中的,Student,表与子查询中的,Student,表区分开：,,SEL

61、ECT S1.Sno,，,S1.Sname,，,S1.Sdept,,FROM Student S1,,WHERE S1.Sdept IN,,(SELECT S2.Sdept,,FROM Student S2,,WHERE S2.Sname,＝,’,刘晨,’),；,,例,38,查询选修了课程名为信息系统的学生学号和姓名。,,完成此查询的基本思路是：,97,,①首先在,Course,关系中找出,‘,信息系统,’,课程的课程号,Cno,。,,②然后在,SC,关系中找出,Cno,等于第一步给出的,Cno,集合中某个元素的,Sno,。,,③最后在,Student,关系中选出,Sno,等于第二步中求出,S

62、no,集合中某个元素的元组。取出,Sno,和,Sname,送入结果表列。,,将上述想法写成,SQL,语句就是：,,SELECT Sno,Sname,,FROM Student,,WHERE Sno IN,98,,(SELECT Sno,,FROM SC,,WHERE Cno IN,,(SELECT Cno,,FROM Course,,WHERE Cname,＝,’,信息系统,’)),；,,DBMS,按照由内向外的原则求解此,SQL,语句，首先处理最内层查询块，即课程名‘信息系统,’,的课程号：,,SELECT Cno,,FROM Course,,WHERE Cname,＝‘信息系统’,,查询结

63、果为,3,。从而可以把上面的,SQL,语句简化为：,99,,SELECT Sno,Sname,,FROM Student,,WHERE Sno IN,,(SELECT Sno,,FROM SC,,WHERE Cno IN,（‘,3,’）,),；,,对此,SQL,语句再处理内层查询，,,SELECT Sno,,FROM SC,,WHERE Cno IN,（‘,3,’）,,结果为,95001,和,95002,。从而可以把上面的,SQL,语句进一步简化为：,,SELECT Sno,Sname,,FROM Student,,WHERE Sno IN,（‘,95001,’，‘,95002,’）；,100

64、,,这样就可以求得最终结果。,,本查询同样可以用连接查询实现：,,SELECT Student.Sno,Sname,,FROM Student,，,SC,，,Course,,WHERE Student.Sno,＝,SC.Sno AND,,SC.Cno,＝,Course.Cno AND,,Course.Cname,＝,’,信息系统,’,；,,2.,,带有比较运算符的子查询,,带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用＞、＜、＝、＞＝、＜＝、,!,＝或＜＞等比较运算符。,101,,例如，在例,37,中，由于一个学生只可能在一个系

65、学习，也就是说内查询刘晨所在系的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其,SQL,语句如下；,,SELECT S1.Sno,，,S1.Sname,，,S1.Sdept,,FROM Student S1,,WHERE S1.Sdept =,,(SELECT S2.Sdept,,FROM Student S2,,WHERE S2.Sname,＝,’,刘晨,’),；,,需要注意的是，子查询一定要跟在比较符之后。下列写法是错误的：,102,,SELECT S1.Sno,，,S1.Sname,，,S1.Sdept,,FROM Student S1,,WHERE (SELECT S2.Sd

66、ept,,FROM Student S2,,WHERE S2.Sname,＝,’,刘晨,’)=S1.Sdept,；,,例,38,中信息系统的课程号是唯一的，但选修该课程的学生并不止一个，所以例,38,也可以用,=,运算符和,IN,谓词共同完成：,103,,SELECT Sno,Sname,,FROM Student,,WHERE Sno IN,,(SELECT Sno,,FROM SC,,WHERE Cno=,,(SELECT Cno,,FROM Course,,WHERE Cname,＝,’,信息系统,’)),；,104,,3.,,带有,ANY,或,ALL,谓词的子查询,,＞,ANY,大于子查询结果中的某个值,,＜,ANY,小于子查询结果中的某个值,,＞＝,ANY,大于等于子查询结果中的某个值,,＜＝,ANY,小于等于子查询结果中的某个值,,＝,ANY,等于子查询结果中的某个值,,!,＝,ANY,或＜＞,ANY,不等于子查询结果中的某个值,,＞,ALL,大于子查询结果中的所有值,,＜,ALL,小于子查询结果中的所有值,,＞＝,ALL,大于等于子查询结果中的所有值,,＜＝,ALL,小于