openGauss数据库设计与应用 课件 章永来 第1-5章 -安装麒麟操作系统与openGauss数据库-关系数据库系统模型

课程介绍

数据库概论1.课程内容本课程的理论教学内容有：第1章麒麟操作系统的安装与使用第2章openGauss数据库系统的安装第3章openGauss数据库初步使用

*（教材第3章）

第4章高级SQL语言

*（教材第3章）

第5章关系数据库系统模型（教材第1，2章）

第6章数据库的保护技术（教材第二部分）

第7章关系数据库的设计理论*（教材第11章）

第8章数据库设计与实现（教材第12章）实验教学中，通过对数据库及数据库对象的各种操作实践，加深对数据库基础理论、基础知识以及SQL语言的理解。2.课程目标能够利用数据库系统的基本理论和数据库设计方法，针对实际问题的需求，合理地对数据进行建模，具有正确表达软件工程问题的能力；能够将概念模型转换为关系模式、并用数据库规范化理论、关系模式分解算法等关系数据库设计理论，正确地设计出合理的关系数据库模式；能够利用数据库完整性约束、安全性控制机制以及数据库恢复技术设计符合实际应用需求的数据库；能够使用关系型数据库管理和开发工具，依据实际应用场景在数据库软件上进行数据库的创建与管理，并了解关系型数据库软件在处理大规模数据以及半结构和非结构数据的局限性；3.课程地位《数据库概论》是计算机、软件工程等专业的一门核心课程。这是一门理论联系实际，实践性非常强的课程。通过本门课程的学习，为今后开展数据管理和信息系统开发等相关工作奠定基础。网盘(所需组件)地址：

4.课程考核方式

课程总成绩采用百分制，包括平时成绩和期末笔试成绩两部分，各占50%。平时成绩（50%）随堂测试成绩（20%）作业成绩（15%）实验成绩（15%）期末笔试成绩（50%）U+班级码：NQ0TCY5.课程配套教学资源西安电子科技大学出版社官网（）第1章麒麟操作系统的安装与使用1.1麒麟操作系统的安装（详见附件1）1.2数据管理技术的产生和发展1.2数据管理技术的产生和发展图0数据存储硬件的简史1.2数据管理技术的产生和发展一、数据管理：指对数据进行收集、组织、编码、存储、检索和维护等活动。二、数据管理技术的发展动力应用需求的推动计算机硬件的发展计算机软件的发展三、数据管理技术的发展过程1、手工管理阶段(20世纪40年代中--50年代中)2、文件系统阶段(20世纪50年代末--60年代中)3、数据库系统阶段(20世纪60年代末--现在)时期

20世纪40年代中--50年代中产生的背景应用需求 科学计算 硬件水平 无直接存取的存储设备软件水平 没有操作系统1.手工管理阶段1.2数据管理技术的产生和发展图1-1人工管理阶段的数据管理模型1.手工管理阶段1.2数据管理技术的产生和发展特点数据的管理者：用户（程序员），数据不保存数据面向的对象：某一应用程序数据的共享程度：无共享、冗余度极大数据的独立性：不独立，完全依赖于程序数据的结构化：无结构数据控制能力：应用程序自己控制1.手工管理阶段1.2数据管理技术的产生和发展2．文件系统管理阶段时期

20世纪50年代末--60年代中产生的背景应用需求 科学计算、管理 硬件水平 磁盘、磁鼓 软件水平 有文件系统 1.2数据管理技术的产生和发展图1-2文件管理阶段的数据管理模型

2．文件系统管理阶段1.2数据管理技术的产生和发展3．数据库系统管理阶段时期

20世纪60年代末以来产生的背景应用背景 大规模管理 硬件背景 大容量磁盘、磁盘阵列 软件背景 有数据库管理系统（DBMS）1.2数据管理技术的产生和发展图1-3数据库系统阶段的数据管理模型3．数据库系统管理阶段1.2数据管理技术的产生和发展特点它将过去在文件系统中以程序设计为核心、数据服从程序设计的数据管理模式改变为以数据库设计为核心、应用程序设计退居次位的数据管理模式。3．数据库系统管理阶段1.2数据管理技术的产生和发展2.1openGauss数据库系统的安装Linux常用命令介绍（详见附件2）第2章openGauss数据库系统的安装2.1openGauss数据库系统的安装（详见附件3）2.2其他常见数据库管理系统2.2其他常见数据库管理系统1.Oracle2.SQLServer3.DB24.Sybase5.MySQL6.Access思考内容

第3章openGauss数据库初步使用

（2025秋季学期）

数据库概论第3章openGauss数据库初步使用3.1数据库表的基本操作3.2SQL语言3.3单表查询3.4排序操作3.1数据库表的基本操作3.1.1基本表的创建、修改与删除3.1.2建立与删除索引3.1.1基本表的创建、修改与删除首先需要使用su命令切换到omm用户，然后启动openGauss数据库服务，最后使用默认的postgres数据库，命令如下：su-ommgs_om-tstartgsql-dpostgres-p154003.1.1基本表的创建、修改与删除图3-1进入数据库系统1．创建基本表图3-2创建Student表3.1.1基本表的创建、修改与删除1．创建基本表

CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。【例3-1】建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)UNIQUENOTNULL,/*列级完整性约束，Sno取值唯一*/

SnameCHAR(18)UNIQUE,

SsexCHAR(2),SageINT,

SdeptCHAR(15));

1．创建基本表3.1.1基本表的创建、修改与删除【例3-2】建立一个“课程”表Course，它由课程号Cno、课程名Cname、先修课Cpno、学分CCredit四个属性组成，其中先修课的取值参照课程号的取值。CREATETABLECourse(CnoCHAR(5),

CnameCHAR(20),

CpnoCHAR(5),

CCreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno)/*表级完整性约束，Cpno的取值参照被参照关系Course中Cno的值*/);1．创建基本表3.1.1基本表的创建、修改与删除【例3-3】建立一个“成绩”表SC，它由学号Sno、课程号Cno和成绩Grade共33个属性组成，其中学号和课程号的组合不能为空。其SQL语句如下：createtableSC(Snochar(5),Cnochar(5),Gradeint,primarykey(Sno,Cno));--学号和课程号的组合是主键1．创建基本表3.1.1基本表的创建、修改与删除数据类型描述Char(n)长度为n的定长字符串Varchar(n)长度为n的变长字符串INT长整数（也可以写作INTEGER）SMALLINT短整数NUMERIC(p,d)定点数，由p位数字（不包括符号、小数点）组成，小数后面有d位数字REAL取决于机器精度的浮点数DoublePrecision取决于机器精度的双精度浮点数FLOAT(n)浮点数，精度至少为n位数字DATE日期，包含年、月、日，格式为YYYY-MM-DDTIME时间，包含时、分、秒，格式为HH:MM:SS表3-1SQL中的主要数据类型3.1.1基本表的创建、修改与删除2．修改基本表

ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN<列名><数据类型>]；3.1.1基本表的创建、修改与删除图3-5修改列的长度2．修改基本表3.1.1基本表的创建、修改与删除图3-6

插入数据图3-7

查询全体学生2．修改基本表--引例

3.1.1基本表的创建、修改与删除【例3-4】向Student表增加“联系电话”列，其数据类型为字符型。

ALTERTABLEStudentADDSphoneCHAR(10);

在基本表中，新增加的列一律为空值。【例3-5】将年龄的数据类型改为短整数。

ALTERTABLEStudentALTERCOLUMNSageTYPESMALLINT;

修改原有的列定义有可能会破坏已有数据。

【例3-6】删除学生姓名必须取唯一值的约束。

ALTERTABLEStudentDROPUNIQUE(Sname);2．修改基本表3.1.1基本表的创建、修改与删除3．删除基本表DROPTABLE<表名>［RESTRICT|CASCADE］；RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除3.1.1基本表的创建、修改与删除【例3-7】删除Student表。

DROPTABLEStudent;

在SQL基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表的操作一定要格外小心。

3．删除基本表3.1.1基本表的创建、修改与删除3.1.2建立与删除索引

1．建立索引

2.删除索引1．建立索引语句格式

CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；关键字UNIQUE表示为表或视图创建唯一索引，即不允许存在索引值相同的两行，如果对已存在数据的表创建唯一索引，必须保证索引对应的值无重复值。关键字CLUSTER用于指定创建聚簇索引。在最经常查询的列上建立聚簇索引以提高查询效率。一个基本表上最多只能建立一个聚簇索引。经常更新的列不宜建立聚簇索引。3.1.2建立与删除索引【例3-8】为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Couse表按课程号升序建唯一索引，SC表按学号降序和课程号升序建唯一索引。CREATEUNIQUEINDEXStsnoONStudent(Sno);CREATEUNIQUEINDEXCocnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoDESC,Cno);1．建立索引3.1.2建立与删除索引

DROPINDEX<索引名>；

删除索引时，系统会从数据字典中删去有关该索引的描述。【例3-9】删除Student表的Ssname索引。

DROPINDEXstudent.Ssname;2．删除索引3.1.2建立与删除索引3.2SQL语言SQL(StructuredQueryLanguage)是结构化查询语言的简称。是一种介于关系代数和关系演算之间的结构化非过程查询语言，是专为数据库而建立的指令集，其功能包括数据的定义、查询、更新和数据控制等多个方面。关系数据库的标准语言。3.2.1SQL的产生与发展

标准

发布日期

SQL/861986.10SQL/891989年

SQL/921992年

SQL991999年

SQL20032003年3.2.2SQL的特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供多种使用方式5.语言简洁，易学易用1.综合统一数据定义语言（DDL）、数据查询语言（DQL）、数据操纵语言（DML）、数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护；数据库安全性、完整性控制等。3.2.2SQL的特点2.高度非过程化

SQL只要提出“做什么”，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。3.2.2SQL的特点3.面向集合的操作方式SQL采用集合操作方式操作对象、查询结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合3.2.2SQL的特点4.以同一种语法结构提供多种使用方式SQL是自含式语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言

SQL能够嵌入到高级语言（例如C、C++、Java）程序中，供程序员设计程序时使用3.2.2SQL的特点5.语言简洁，易学易用SQL功能极强，完成核心功能只用了9个动词。表3.2SQL语言的动词

SQL

功

能

动

词

数

据

查

询

SELECT

数

据

定

义

CREATE，DROP，ALTER

数

据

操

纵

INSERT，UPDATE

DELETE

数

据

控

制

GRANT，REVOKE

3.2.2SQL的特点3.2.3SQL的体系结构用户视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模式内模式SQL支持关系数据库三级模式结构3.2.3SQL的体系结构基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图3.2.4SQL的组成数据定义语言（DataDefinitionLanguage,

DDL）数据操纵语言（DataManipulationLanguage,DML）数据控制语言（DataControlLanguage，DCL）数据查询语言(DataQueryLanguage，DQL)1．数据定义语言DDL是SQL中负责数据结构定义与数据库对象定义的语言，用于执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。

语句功能说明CREATE

创建数据库或数据库对象不同数据库对象，其CREATE语句的语法形式不同ALTER对数据库或数据库对象进行修改不同数据库对象，其ALTER语句的语法形式不同DROP删除数据库或数据库对象不同数据库对象，其DROP语句的语法形式不同表4-2DDL主要语句及功能3.2.4SQL的组成2．数据操纵语言实现对数据库的基本操作，可对数据库中各种对象中的数据进行插入、删除和修改。

语句功能说明INSERT将数据插入到表或视图中UPDATE修改表或视图中的数据既可修改表或视图的一行数据，也可修改一组或全部数据DELETE从表或视图中删除数据可根据条件删除指定的数据表4-3DML主要语句及功能3.2.4SQL的组成3．数据控制语言用于安全管理，确定哪些用户可以查看或修改数据库中的数据。语句功能说明GRANT

授予权限

可把语句许可或对象许可的权限授予其他用户和角色REVOKE

收回权限

与GRANT的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限DENY

收回权限，并禁止从其他角色继承许可权限

功能与REVOKE相似，不同之处：除收回权限外，还禁止从其他角色继承许可权限表4-4DCL主要语句及功能3.2.4SQL的组成4．数据查询语言用来检索数据库表中的数据。语句功能说明SELECT

从表或视图中检索数据是使用最频繁的SQL语句之一表4-5DQL主要语句及功能

3.2.4SQL的组成3.3单表查询语句格式

SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…

FROM

<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

学生-课程模式:

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

3.3单表查询3.3单表查询如果Ssex需要用两个字符表示（如'男'或'女'），那么需要修改表定义，将Ssex

列的类型从

CHAR(2)

改为

CHAR(3)

或更长，以适应数据。其SQL语句如下：altertableStudentaltercolumnSsextypechar(3);3.3单表查询图3-6

插入数据3.3单表查询查询仅涉及一个表：1、选择表中的若干列2、选择表中的若干元组1．选择表中的若干列（1）查询指定列

在SELECT子句指定<目标列表达式>中指定要查询的属性列。【例3-10】查询全体学生的姓名、所在系。

SELECTSname,SdeptFROMStudent;

<目标列表达式>中各个列的先后顺序可以与数据库表中的列顺序不一致。用户可以根据应用的需要改变列的显示顺序。3.3单表查询（2）查询全部列

两种方法：一种方法就是在SELECT命令字后面列出所有的列名；另一种方法是可以简单地将<目标列表达式>指定为*，这样做列的显示顺序与其在基表中的顺序相同。【例3-11】查询选修课的情况。

SELECTSno,Cno,GradeFROMSC;

等价于：SELECT*FROMSC;1．选择表中的若干列3.3单表查询（3）查询经过计算的列

【例3-12】查找全体学生的学号，姓名以及出生年份。

SELECTSno,Sname,2021-SageFROMStudent;【例3-13】查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。

SELECTSname,2021-Sage,LOWER(Sdept)FROMStudent;

用户可以通过指定别名来改变查询结果的列标题。

【例3-14】SELECTSnameNAME,2021-SageBIRTHDAY,LOWER(Sdept)DEPARTMENTFROMStudent;1．选择表中的若干列3.3单表查询2．选择表中的若干元组

SELECT语句中使用“*”查询所有列以及使用WHERE子句指定选择的条件来实现的。（1）消除取值重复的行如果没有指定DISTINCT关键词，则缺省为ALL；指定DISTINCT关键词，去掉表中重复的行。【例3-15】查询学生所在的系名

SELECTDISTINCTSdeptFROMStudent;3.3单表查询通过WHERE子句实现对满足指定条件的元组进行查询。

查询条件谓词比较=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR，NOT表3-7常用的查询条件3.3单表查询2．选择表中的若干元组（WHERE子句）【例3-16】查询所在系是“网络工程系”全体学生的名单。SELECTSnameFROMStudentWHERESdept=‘网络工程系';【例3-17】查询所有年龄不小于22岁的学生学号，姓名及其年龄。SELECTSno,Sname,SageFROMStudentWHERESage>=22;或SELECTSno,Sname,SageFROMStudentWHERENOTSage<22;【例3-18】查询考试成绩有不及格的学生的学号及课程号。

SELECTSno,CnoFROMSCWHEREGrade<60;或SELECTSno,CnoFROMSCWHERENOTGrade>=60;2．选择表中的若干元组（比较大小）3.3单表查询【例3-19】查询年龄在18-20岁(包括18岁和20岁)之间的学生的学号、姓名、系别和年龄。

SELECTSno,Sname,Sdept,SageFROMStudentWHERESageBETWEEN18AND20;或SELECTSno,Sname,Sdept,SageFROMStudentWHERESage>=18ANDSage<=20;

【例3-20】查询年龄不在18-20岁之间的学生的学号、姓名、系别和年龄。

SELECTSno,Sname,Sdept,SageFROMStudentWHERESageNOTBETWEEN18AND20;2．选择表中的若干元组（确定范围）3.3单表查询【例3-21】查询“网络工程系”、“信息工程系”和“计算机系”学生的姓名和年龄。

SELECTSname,SageFROMStudentWHERESdeptIN(‘网络工程系',‘计算机系',‘信息工程系');【例3-22】查询不是“网络工程系”也不是“软件工程系”和“计算机系”的学生的姓名和年龄。

SELECTSname,SageFROMStudentWHERESdeptNOTIN(‘网络工程系',‘计算机系',‘软件工程系');2．选择表中的若干元组（确定集合）3.3单表查询【例3-23】查询学号为“0906064201”的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE

‘0906064201';【例3-24】查询学号不是“0906064201”的学生的详细情况。

SELECT*FROMStudentWHERESno

NOTLIKE

‘0906064201';

等价于：

SELECT*FROMStudentWHERESno!=‘0906064201'2．选择表中的若干元组（字符匹配）3.3单表查询【例3-25】查询所有姓“王”的学生的学号、姓名和年龄。

SELECTSno,Sname,SageFROMStudent

WHERESnameLIKE

‘王%';【例3-26】查询姓“皇甫”且全名为三个汉字的学生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE

‘皇甫__'

注意：由于一个汉字占两个字符的位置，所以匹配串“皇甫”后面需要跟两个“_”。2．选择表中的若干元组（字符匹配）3.3单表查询【例3-27】查询名字中带“丽”字的学生的姓名和学号。

SELECTSname,SnoFROMStudentWHERESnameLIKE

‘%丽%';【例3-28】查询所有非“王”姓的学生的姓名及学号。

SELECTSno,SnameFROMStudentWHERESnameNOTLIKE‘王%';

2．选择表中的若干元组（字符匹配）3.3单表查询

查询的字符串本身就含有%或_，这时就要使用ESCAPE‘<换码字符>’。【例3-29】查询“网络编程_2”这门课程的课程号。

SELECTCnoFROMCourseWHERECnameLIKE‘网络编程\_2'ESCAPE‘\';

注意：ESCAPE‘\’中的‘\’为换码字符，表示匹配串“网络编程\_2”中，跟在‘\’后面的‘_’不再具有通配符的含义，而转义为普通的“_”匹配字符。2．选择表中的若干元组（字符匹配）3.3单表查询【例3-30】查询缺少成绩的学生的学号和相应的课程号。

SELECTSno,CnoFROMSCWHEREGradeISNULL;【例3-31】查询所有有成绩的学生课程号和学号。

SELECTCno,SnoFROMSCWHEREGradeISNOTNULL;2．选择表中的若干元组（涉及空值的查询）3.3单表查询【例3-32】查询“网络工程系”年龄大于18岁的学生的学号。

SELECTSnoFROMStudentWHERESdept=‘网络工程'ANDSage>18;2．选择表中的若干元组（多重条件查询）3.3单表查询3.4排序操作ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示【例3-33】查询“计算机系”学生的学号及其年龄，并且查询结果按年龄的降序排列。

SELECTSno,SageFROMStudentWHERESdept=‘计算机系'

ORDERBYSageDESC;

注意：对于空值，若按降序排列，含空值的元素将最先显示。若按升序排，空值的元素将最后显示。

【例3-34】

查询全体学生的选课情况，查询结果按所选课的课程号升序排列，同一门课程的元组按成绩的降序排列。

SELECT*FROMSCORDERBYCno,GradeDESC;3.4排序操作

介绍了SQL的发展、组成、特点及SQL的基本功能和基本语法，其中重点是SQL的基本功能和基本语法。SQL的基本功能包括数据定义、数据查询、数据更新和数据控制4个方面。数据定义部分介绍了数据库及基本表、索引创建、修改和删除；数据查询部分介绍了单表查询与排序操作。其他部分将在后续章节中介绍。本章小结思考内容

第4章高级SQL语言

（2025秋季学期）

数据库概论第4章高级SQL语言4.1数据库应用系统配置4.2聚集函数4.3分组查询4.4多表查询4.5集合查询4.6嵌套查询4.7数据更新4.8SQL数据控制4.9视图的定义及操作4.10嵌入式SQL4.11动态SQL简介4.1数据库应用系统配置1.安装前准备安装数据库执行系统前需要准备的组件如下：(1)虚拟化软件：建议安装VMwareWorkstationPro17。(2)麒麟操作系统：Kylin-Server-V10-SP3-General-Release-2303-X86_64。(3)数据库系统：openGauss-5.0.2。(4)数据库执行器源代码包：openGauss_performer.rar。(5)eclipse集成开发环境软件：eclipse-jee-2022-12-R-linux-gtk-x86_64.tar.gz。(6)数据库连接器：openGauss-6.0.0-RC1-JDBC.tar.gz。4.1数据库应用系统配置2.数据库执行器的应用（详见附件3）图4-17执行器运行成功界面4.2聚集函数聚集函数：计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>）

计算平均值AVG（[DISTINCT|ALL]<列名>）最大最小值

MAX（[DISTINCT|ALL]<列名>）

MIN（[DISTINCT|ALL]<列名>）4.2聚集函数【例4-1】求选修课程号是“01”课程的学生的平均成绩。

SELECTAVG(Grade)

课程01平均成绩

FROMSCWHERECno=‘01';【例4-2】查询选修了“01”课程的学生的最高分和最低分。

SELECTMAX(Grade)

课程01的最高分,MIN(Grade)

课程01的最低分

FROMSCWHERECno=‘01';4.2聚集函数【例4-3】查询学生的总人数。

SELECTCOUNT(*)

学生总人数

FROMStudent;【例4-4】查询选修了课程的学生总人数。

SELECTCOUNT（DISTINCTSno）FROMSC

注意：学生每选修一门课，在SC中都有一条相应的记录。一个学生要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。4.3分组查询GROUPBY子句：细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组

作用对象是查询的中间结果表按指定的一列或多列值分组，值相等的为一组4.3分组查询【例4-5】查询各系的学生人数。

SELECTSdept,COUNT(*)学生数

FROMStudentGROUPBYSdept;【例4-6】查询选修各门课程的平均成绩和选修该课程的人数。

SELECTCno,AVG(Grade)平均成绩,COUNT(Sno)选修人数

FROMSCGROUPBYCno;4.3分组查询【例4-7】查询平均成绩在80分以上的学生的学号和平均成绩。

SELECTSno,AVG(Grade)平均成绩

FROMSCGROUPBYSno

HAVINGAVG(Grade)>=80;【例4-8】查询选修课程超过3门且成绩都在75分以上的学生的学号。

SELECTSnoFROMSCWHEREGrade>=75

GROUPBYSnoHAVINGCOUNT(*)>34.4多表查询1．等值与非等值连接查询2．自身连接查询3.外连接1．等值与非等值连接查询连接条件一般格式：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>连接字段：连接条件中的列名称，各连接字段类型必须是可比的，但名字不必是相同的当比较运算符为“=”时，称为等值连接，否则称为非等值连接。当等值连接字段相同，并且在SELECT子句中去除重复字段时，则称为自然连接。4.4多表查询连接操作的执行过程(嵌套循环法)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕。4.4多表查询【例4-9】查询每个学生的基本信息及学生选修课的情况。

SELECTStudent.*,SC.*

FROMStudent,SC

WHEREStudent.Sno=SC.Sno

1．等值与非等值连接查询4.4多表查询Student.Sno

Sname

Ssex

Sage

Sdept

SC.SnoCno

Grade09001

李强男

21网络工程09001018509001

李强

男

21网络工程

09001029009001

李强男

21网络工程

09001038509002

刘帅男

20网络工程09002018709002

刘帅

男

20

网络工程

090020292查询的执行结果：

表4-4例4-9的执行结果1．等值与非等值连接查询4.4多表查询【例4-10】自然连接查询。

SELECTStudent.*,SC.Cno,SC.GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno

若选的字段名在各个表中是唯一的，则可以省略字段名前的表名，本例也可以写成：

SELECTStudent.*,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno1．等值与非等值连接查询4.4多表查询【例4-44】查询选修了“01”号课程且成绩在75分以上的学生的姓名及成绩。

SELECTSname,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno

ANDCno=‘01'ANDGrade>=751．等值与非等值连接查询4.4多表查询执行结果：SnameGrade李强85刘帅87表4-5例4-11的结果1．等值与非等值连接查询4.4多表查询假设课程表Course的内容如表4-6所示。CnoCnameCcredit01离散数学402数据库303数据结构204操作系统305软件工程2表4-6Course表1．等值与非等值连接查询4.4多表查询【例4-12】多表查询。查询选修了“离散数学”课程且成绩在80分以上的学生的学号、姓名、课程名和成绩。

SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,Course,SCWHEREStudent.Sno=SC.Sno

ANDCourse.Cno=SC.Cno

ANDCname=‘离散数学'ANDGrade>=801．等值与非等值连接查询4.4多表查询执行结果SnoSnameCnameGrade09001李强离散数学8509002刘帅离散数学871．等值与非等值连接查询4.4多表查询2．自身连接查询

自身连接：一个表与自身进行连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀【例4-46】自身连接实例。查询不同课程成绩相同的学生的学号、课程号和成绩。

SELECTa.Sno,a.Cno,b.Cno,a.GradeFROMSCa,SCb

WHERE

a.Grade=b.GradeANDa.Sno=b.SnoANDa.Cno!=b.Cno4.4多表查询2．自身连接查询查询结果如表4-8所示。SnoCnoCnoGrade0900101038509001030185表4-8例4-13的结果4.4多表查询3.外连接外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出4.4多表查询左外连接、右外连接外连接中不匹配的分量用NULL表示。外连接的语法格式为：<表名>LEFT|RIGHT[OUTER]JOIN<表名>ON<连接条件>

其中表名为需连接的表，LEFT[OUTER]JOIN称为左外连接，RIGHT[OUTER]JOIN称为右外连接。4.4多表查询3.外连接【例4-14】查询学生的基本情况及选修课的课程号情况，要求查询结果中包含未选课的学生信息。

SELECTStudent.*,CnoFROMStudentLEFTOUTERJOINSC ONStudent.Sno=SC.Sno4.4多表查询SnoSnameSsexSageSdeptCno09001李强男21网络工程0109001李强男21网络工程0209001李强男21网络工程0309002刘帅男20网络工程0109002刘帅男20网络工程0209003王海女18计算机NULL09004张坤男20软件工程NULL查询结果：

4.4多表查询【例4-15】查询选修了的课程情况及已开设的所有选修课的课程名。

SELECTSC.*,CnameFROMSCRIGHTJOINCourseONSC.Cno=Course.Cno4.4多表查询3.外连接Sno

Cno

GradeCname09001

0185离散数学09001

0290数据库09001

0385数据结构09002

0187离散数学09002

0292数据库NULLNULLNULL操作系统NULLNULLNULL软件工程查询结果

表4-10例4-15的结果4.4多表查询3.外连接4.5集合查询集合操作的种类并操作UNION

交操作INTERSECT

差操作EXCEPT其语法格式为：

<SELECT语句>UNION[ALL]|INTERSECT|EXCEPT<SELECT语句>关键字ALL表示合并的结果中包含所有行，重复的行不去掉；不使用ALL则表示在合并时相同的行要去掉。参加集合操作的各查询结果的列数必须相同；对应列的数据类型也必须相同。4.5集合查询【例4-16】

查询性别是“男”的学生及年龄小于22岁的学生。SELECT*FROMStudentWHERESsex=‘男'UNIONSELECT*FROMStudentWHERESage<224.5集合查询【例4-16】

查询性别是“男”的学生及年龄小于22岁的学生。

也可以使用OR操作查询来代替，但OR操作查询不会去除重复的行。SELECT*FROMStudentWHERESsex=‘男'ORSage<224.5集合查询【例4-17】

查询性别是“男”并且年龄小于22岁的学生。SELECT*FROMStudentWHERESsex=‘男'INTERSECTSELECT*FROMStudentWHERESage<224.5集合查询【例4-17】

查询性别是“男”并且年龄小于22岁的学生。

也可以使用AND操作查询来代替。

SELECT*FROMStudentWHERESsex=‘男'ANDSage<224.5集合查询【例4-18】查询既选修了“01”号课程又选修了“03”课程的学生的学号。

SELECTSnoFROMSCWHERECno=‘01'

INTERSECTSELECTSnoFROMSCWHERECno=‘03'4.5集合查询【例4-19】查询性别是“男”但年龄不小于22岁的学生。SELECT*FROMStudentWHERESsex=‘男'EXCEPTSELECT*FROMStudentWHERESage<22可用下面的查询来代替。SELECT*FROMStudentWHERESsex=‘男'ANDSage>=224.6嵌套查询嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询例如：SELECTSnameFROMStudent/*外层查询/父查询*/WHERESnoIN

（SELECTSnoFROMSC/*内层查询/子查询*/WHERECno=‘2'）

；4.6嵌套查询子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接查询替代4.6嵌套查询嵌套查询求解方法:不相关子查询，相关子查询不相关子查询：子查询的查询条件不依赖于父查询由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。4.6嵌套查询相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止4.6嵌套查询1.带有IN谓词的子查询2.带有比较运算符的子查询3.带有ANY（SOME）或ALL谓词的子查询4.带有EXISTS谓词的子查询4.6嵌套查询1.带有IN谓词的子查询【例4-20】查询选修了“01”号课程的学生的信息。

SELECT*FROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno=‘01')

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。4.6嵌套查询本例也可以使用连接查询来实现：

SELECT*FROMStudent,SCWHEREStudent.Sno=SC.SnoANDCno=‘01'4.6嵌套查询1.带有IN谓词的子查询【例4-21】查询选修“数据库”的学生的基本情况。

SELECT*FROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname=‘数据库')

);4.6嵌套查询1.带有IN谓词的子查询本例也可以使用连接查询来实现：

SELECT*FROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDCname=‘数据库';4.6嵌套查询1.带有IN谓词的子查询2．带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用4.6嵌套查询【例4-55】查询“01”号课程的成绩高于“李强”的学生的学号。

SELECTSnoFROMSCWHERECno=‘01'ANDGrade>(SELECTGradeFROMSCWHERECno=‘01'ANDSno=(SELECTSnoFROMStudentWHERESname=‘李强'));4.6嵌套查询2．带有比较运算符的子查询3.带有ANY或ALL谓词的子查询谓词语义

ANY：任意一个值

ALL：所有值4.6嵌套查询

>ANY 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值

<ANY 小于子查询结果中的某个值

<ALL 小于子查询结果中的所有值

>=ANY 大于等于子查询结果中的某个值

>=ALL 大于等于子查询结果中的所有值

<=ANY 小于等于子查询结果中的某个值

<=ALL 小于等于子查询结果中的所有值

=ANY 等于子查询结果中的某个值

=ALL等于子查询结果中的所有值（通常没有实际意义）

!=（或<>）ANY 不等于子查询结果中的某个值

!=（或<>）ALL 不等于子查询结果中的任何一个值4.6嵌套查询3.带有ANY或ALL谓词的子查询【例4-23】查询比所有“计算机”系的学生年龄都大的学生信息。

SELECT*FROMStudentWHERESage>ALL

（SELECTSageFROMStudentWHERESdept=‘计算机'）3.带有ANY或ALL谓词的子查询4.6嵌套查询本例也可以使用聚集函数来实现：

SELECT*FROMStudentWHERESage>

(SELECTMAX(Sage)FROMStudentWHERESdept=‘计算机')3.带有ANY或ALL谓词的子查询4.6嵌套查询【例4-24】查询课程号为“02”且成绩低于课程号为“01”的最高成绩的学生的学号、课程号和成绩。

SELECTSno,Cno,GradeFROMSCWHERECno=‘02’ANDGrade<ANY(SELECTGrade FROMSC WHERECno=‘01')3.带有ANY或ALL谓词的子查询4.6嵌套查询也可以使用聚集函数来实现：SELECTSno,Cno,GradeFROMSCWHERECno=‘02'ANDGrade<(SELECTMAX(Grade)

FROMSCWHERECno=‘01')3.带有ANY或ALL谓词的子查询4.6嵌套查询4．带有EXISTS谓词的子查询1.EXISTS谓词存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOTEXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值4.6嵌套查询【例4-25】查询选修了“02”号课程的学生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*

FROMSCWHEREStudent.Sno= SC.SnoANDCno=‘02')；4．带有EXISTS谓词的子查询4.6嵌套查询【例4-24】查询没有选修“02”号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=‘02')4．带有EXISTS谓词的子查询4.6嵌套查询

不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词

（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(

x)P≡

(

x(

P))

4．带有EXISTS谓词的子查询4.6嵌套查询例：查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS

(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=‘刘晨')；4．带有EXISTS谓词的子查询4.6嵌套查询[例]查询选修了全部课程的学生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno

））；4．带有EXISTS谓词的子查询4.6嵌套查询

用EXISTS/NOTEXISTS实现逻辑蕴含SQL语言中没有蕴含(Implication)逻辑运算可以利用谓词演算将逻辑蕴含谓词等价转换为：p

q≡

p∨q4．带有EXISTS谓词的子查询4.6嵌套查询

[例]查询至少选修了学生200215122选修的全部课程的学生号码。解题思路：用逻辑蕴含表达：查询学号为x的学生，对所有的课程y，只要200215122学生选修了课程y，则x也选修了y。形式化表示： 用p表示谓词“学生200215122选修了课程y”

用q表示谓词“学生x选修了课程y”

则上述查询为:(

y)p

q

4．带有EXISTS谓词的子查询4.6嵌套查询等价变换：

(

y)p

q≡

(

y(

(p

q))≡

(

y(

(

p∨q)))≡

y(p∧

q)变换后语义：不存在这样的课程y，学生200215122选修了y，而学生x没有选。4．带有EXISTS谓词的子查询4.6嵌套查询用NOTEXISTS谓词表示：

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=‘200215122'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoAND SCZ.Cno=SCY.Cno))；4．带有EXISTS谓词的子查询4.6嵌套查询4.7数据更新4.7.1数据插入4.7.2数据修改4.7.3数据删除4.7.1数据插入两种插入数据方式1.插入单个元组2.插入子查询结果可以一次插入多个元组1.插入单个元组语句格式

INSERT INTO<表名>[(<属性列1>[，<属性列2>…)] VALUES(<常量1>[，<常量2>]…)；功能将新元组插入到指定的表中4.7.1数据插入INTO子句属性列的顺序可与表定义中的顺序不一致没有指定属性列指定部分属性列

VALUES子句

提供的值必须与INTO子句匹配值的个数值的类型4.7.1数据插入【例4-28】将一个新学生记录(学号：09005；姓名：王丽；性别：女；年龄：19岁；所在系：计算机，插入到Student表中。

INSERTINTOStudent

VALUES(‘09005',‘王丽',‘女',19,‘计算机');4.7.1数据插入【例4-29】向选课表SC中插入一条选课学号为“09005”课程号为“03”的记录。

INSERT

INTOSC(Sno,Cno)

VALUES(‘09005',‘03');

新插入的元组在Grade列上取NULL4.7.1数据插入2.插入子查询结果语句格式

INS