SQL结构查询语言课件

1SQL概述

SQL的全称为structuredquerylanguage（结构化查询语言）。最早是在1974IBM的SanJose实验室的研究人员D.Chamberlin定义了“结构化英语查询语言”，简称SEQUEL。1976Chamberlin将其改称为SQL，正确的发音为‘s_q_l’。SQL是一种类英语的语言，用一些简单的英语句子构成基本的语法结构，具有简单易学、功能较强、操作灵活的特点。1976IBMSystemR上，实现了这种用英语描述关系代数运算的语言的研究。在20世纪70ORACLESQL商业化的关系数据库管理系统。

1

1SQL概述

SQL是一种非过程化的语言，它与通常的高级语言不同，使用SQL时，只要说明做什么，不需要说明怎么做，具体的操作全部由DBMS自动完成。例如，要查询所有女同学的姓名，只要写出SQL语句：SELECT姓名FROM学生WHERE性别=‘女’

21SQL概述

SQL语言按用途可划分为三个组成部分：数据定义语言（DDL）：在数据库系统中，数据库、表、视图、索引等都是对象，用于定义这些对象的SQL语句称为DDL。数据处理语言（DML）：用于插入、修改、删除和查询数据的SQL语句称为DML。数据控制语言（DCL）：用于实现数据完整性、安全性、一致性等控制的SQL语句称为DCL。32数据定义系课程图1一组关系模式和样本数据（a）

系编号系名称101数学102计算机103外语104经济课程号课程名学时系编号C1数学681C2英语853C3计算机682C4经济学5145

2数据定义

图1一组关系模式和样本数据（b）教工号姓

名性别职称工

资系编号2101葛小平女教授3420.001012203李长江男副教授3190.001022405姜立伟男副教授3140.001042104张丽丽女讲师224001012302康立华女教授3740.001032205王伟平男讲师2130.00102教工62数据定义

学

号课程编号分数1022C1881022C2672124C1772124C2952124C3454021C4874021C2784021C3671223C1661223C2893012C2933012C384成绩图1一组关系模式和样本数据（c）72数据定义 说明：本书选用SQLServer2000系统作为上机实验平台，所有例题和上机操作实验案例都在SQLServer2000查询分析器中运行通过，并且所选用的题目尽量符合SQL的标准格式，如果读者选用其他数据库管理系统作为实验环境，可能有微小的区别。

92数据定义

2.1建立数据库

绝大多数关系DBMS，使用CREATEDATABASE命令建立数据库，其语法格式： CREATEDATABASE<数据库名>

102数据定义

2.1建立数据库

例1

根据图1给出的实例，用SQL语言创建一个教学数据库。CREATEDATABASE教学当进入SQL_Server2000系统的查询分析器后，如图2所示，在查询窗口中输入命令：CREATEDATABASE教学，然后，点击绿色的►按钮执行命令，在窗格中显示运行的结果，创建了教学数据库。

112数据定义

2.2定义表结构 在SQL语言中，利用CREATETABLE语句创建表，其基本语法格式是：CREATETABLE<表名>（<列名1><数据类型>，<列名2><数据类型>，…，<列名n><数据类型>，PRIMARYKEY(<主码>)，FOREIGNKEY(<外码>)REFERENCES表名(<外码>)）注：PRIMARYKEY子句定义主码，实现实体完整性定义；FOREIGNKEY子句定义外码，实现参照完整性定义。

132数据定义

2.2定义表结构

表1常见的数据类型

数据类型说明范例CHAR(N)固定长度的字符串CHAR(8)：长度是8个字节INT整数类型SMALLINT短整型数类型NUMERIC(P[,D])共P位，其中小数位占d位NUMERIC(8,2)：整数6位，小数2位DATE日期型，包括年(4位)月日2001/10/13：2001年10月13日142数据定义

2.2定义表结构

举例说明CREATETABLE语句的使用方法。例2

创建一个简单的表，该表的关系模式是：系（系编号，系名称）。CREATETABLE系(系编号SMALLINT, 系名称CHAR(12))

152数据定义

2.2定义表结构说明：在SQL-89中，规定PRIMARYKEY子句定义主码，NOTNULL关键词定义属性非空，所以，要用这两个定义来实现实体完整性规则。在SQL-92中，规定PRIMARYKEY子句定义主码且主码非空，所以，用一个子句就可以定义实体完整性。172数据定义

2.2定义表结构

例4在创建教工表的定义中包括实体完整性和参照完整性的定义。CREATETABLE教工(教工号SMALLINTNOTNULL,

姓名CHAR(8)NOTNULL,

性别CHAR(2),

职称CHAR(8),

工资NUMERIC(8,2),

系编号SMALLINT, PRIMARYKEY(教工号), FOREIGNKEY(系编号)REFERENCES系(系编号))182数据定义

2.2定义表结构

例5

创建成绩表，包括实体完整性和参照完整性的定义。CREATETABLE成绩(学号CHAR(6)NOTNULL,

课程编号CHAR(8)NOTNULL,

分数NUMERIC(4,1),PRIMARYKEY(学号,课程编号),FOREIGNKEY(学号)REFERENCES学生(学号),FOREIGNKEY(课程编号)REFERENCES课程(课程编号))192数据定义

2.3修改表结构 用SQL语言的ALTERTABLE语句可以修改表的结构。下面结合实例说明ALTERTABLE语句的使用方法。1.增加新的属性，所有元组在这个新属性上都将赋值NULL。 语句格式是：

ALTERTABLE<表名>ADD<新属性名><域类型>例6在系表中添加一个电话号码属性。

ALTERTABLE系ADD电话号码CHAR(8)

212数据定义

2.3修改表结构2.修改属性的性质，语句格式是：

ALTERTABLE<表名>MODIFY<属性名><新的域类型>注意：在SQLSERVER2000系统中规定修改属性的性质，语句格式为：ALTERTABLE<表名>ALTERCOLUMN<属性名><新的域类型>例7

修改系表中电话号码属性的宽度。

ALTERTABLE系ALTERCOLUMN电话号码CHAR(13)

222数据定义

2.3修改表结构

删除一个属性，语句格式是：ALTERTABLE<表名>DROP<属性名> 注意：在SQLSERVER2000系统中应该写成：ALTERTABLE<表名>DROPCOLUMN<属性名>

例8

删除系表中电话号码属性。SQL语句是：ALTERTABLE系DROPCOLUMN电话号码

232数据定义

2.4删除表例9删除教工表。DROPTABLE教工注意：系统不允许删除已由REFERENCES子句定义的被参照表。假设在教工表中定义外码<系编号>参照系表，若要删除系表，就必须先删除教工表，否则系统拒绝删除操作。

252数据定义

2.4删除表

例10假设在教学数据库中创建了系、教工、学生、课程、成绩5个表，并且定义了参照完整性规则。如果要删除学生表，则必须先删除成绩表。DROPTABLE成绩DROPTABLE学生

262数据定义

2.5创建索引

例11

对教授表的教工号属性建立一个名为SCODEX的索引。CREATEINDEXSCODEXON教授(教工号)SQL语言允许建立唯一性索引。对侯选码建立唯一性索引，可以防止输入重复的数据。

292数据定义

2.5创建索引例12对学生表的学号属性建立唯一性索引。CREATEUNIQUEINDEXSCODEXON学生(学号)说明：建立了唯一性索引之后，若输入一个重复的学号时，系统将产生错误提示：

duplicatevalueinindex.

303数据的基本操作

本节将介绍SQL语言的数据操作功能，包括插入、更新和删除数据的操作命令。313数据的基本操作

1插入数据在SQL语言中，数据插入语句有三种格式。第一种格式是在表中插入一个元组，语法格式是：INSERTINTO<表名>VALUES(<值1>,<值2>,.....,<值N>)323数据的基本操作

1插入数据例13在系表中插入一行数据。INSERTINTO系VALUES(102,'管理科学')显示所插入的数据

：SELECT*FROM系显示结果：

系编号系名称102管理科学333数据的基本操作

1插入数据

例14向教工表中插入一行数据。INSERTINTO教工VALUES(2001,'葛小平','女','教授',3420.00,102)显示所插入的数据

：SELECT*FROM教工显示结果：

教工号姓名性别职称工资系编号2001葛小平女教授3420.00102343数据的基本操作

1插入数据

说明：这种格式适合向表中插入一整行数据。值得注意的是所列值的顺序必须与属性的顺序和类型一致。

353数据的基本操作

1插入数据

第二种格式用于在表中插入部分属性的值，语法格式是：INSERTINTO<表名>（<列名表>）VALUES(<值列表>)

363数据的基本操作

1插入数据

例15向教工表中插入一个教工号、姓名和工资。INSERTINTO教工(教工号,姓名,工资)VALUES(2109,'田新民',2650)显示所插入的数据：SELECT*FROM教工显示结果：

教工号姓名性别职称工资系编号2001葛小平女教授3420.001022109田新民2650.00373数据的基本操作

1插入数据说明：这种格式适合在表的部分属性上插入数据。应注意的是值的顺序应该与所列属性的顺序和类型相一致。

383数据的基本操作

1插入数据

第三种格式是将VALUES子句换成一个查询语句，语法格式是：INSERTINTO<表名>（<列名表>）

SELECT<列名表>一个查询语句

FROM<表名>393数据的基本操作

1插入数据例16将教工表的姓名和工资拷贝到一个酬金表中。

403数据的基本操作

1插入数据

（1）

创建一个酬金表。CREATETABLE酬金(教工姓名CHAR(8),工资NUMERIC(8,2))

413数据的基本操作

1插入数据（2）

将教工表中姓名和工资两列数据拷贝到酬金表中。INSERTINTO酬金(教工姓名,工资)SELECT姓名,工资FROM教工

423数据的基本操作

1插入数据（3）显示操作的结果：SELECT*FROM酬金

显示结果：

教工姓名工资葛小平3420.00田新民2650.00433数据的基本操作

2更新数据SQL语言的数据更新语句的格式是：

UPDATE<表名>SET<列名1>=<新值1>，<列名2>=<新值2>，……WHERE<条件表达式>

443数据的基本操作

2更新数据

例17将教工表中职称为“教授”的工资增加10%。UPDATE教工SET工资=工资*1.1WHERE职称='教授'只修改职称为教授的元组的工资属性值。

453数据的基本操作

2更新数据例18将教工表中所有人的工资增加5%。UPDATE教工SET工资=工资*1.05修改所有元组的工资属性的值。

463数据的基本操作

3删除数据SQL语言中，删除数据语句的格式是：DELETEFROM<表名>WHERE<条件表达式>

473数据的基本操作

3删除数据例19从教工表中删除教工号为2001的教工。DELETEFROM教工WHERE教工号=2001查看删除的结果：SELECT*FROM教工显示结果：

教工号姓名性别职称工资系编号2109田新民2650.00483数据的基本操作

3删除数据例20删除酬金表的所有数据。DELETEFROM酬金查看删除的结果：SELECT*FROM酬金显示结果：

说明：这个语句只删除酬金表的数据，不删除表的结构，此时，酬金表是一个空表。

教工姓名工资494数据的查询操作本节将介绍SQL语言的数据查询的基本功能。其中包括查询语句的基本结构、更名运算、元组变量、字符串操作、排序操作和分组操作。

504数据的查询操作

4.1查询语句的基本结构

SQL查询语句的一般格式：SELECT<列名L>,<列名2>,…,<列名N>FROM<表1>,<表2>,…,<表M>WHERE<条件表达式>

514数据的查询操作

4.1查询语句的基本结构查询语句的三个子句分别实现关系代数的一种运算：SELECT子句对应关系代数中的投影运算，列出查询结果中的属性。FROM子句对应关系代数中的笛卡儿积，列出查询需要搜索的关系。WHERE子句对应关系代数中的选择运算，条件表达式中所涉及的属性将属于FROM子句所列的关系。

524数据的查询操作

4.1查询语句的基本结构查询语句等价于关系代数表达式：A1,A2,...An(σp(R1×R2×…×Rm))其中：Ai表示属性i，Rj表示关系j，P表示选择条件。这个表达式的含义是：首先计算R1、R2到Rm的笛卡儿乘积，然后，选择满足条件P的元组，最后对选择的结果进行投影。

534数据的查询操作

4.1查询语句的基本结构从查询语句等价的关系代数表达式，可以分析SQL查询语句的执行过程：构造FROM子句中关系的笛卡儿积。根据WHERE子句中的条件表达式，进行选择操作。根据SELECT子句给出的列名进行投影操作。

544数据的查询操作

4.1查询语句的基本结构假设有关系R、S和一个SQL查询语句，如图3所示。分析SQL查询语句与关系代数运算的对应关系。

RS

图3关系R、S和一个SQL查询语句

ABC

a1b1c2a2b2c2a3b1c4ADE

a1d1e2a2d2e3查询语句：SELECTR.A,B,D,EFROMR,SWHERER.A=S.A554数据的查询操作

4.1查询语句的基本结构分析这个查询语句执行的过程：1.先计算RS

ABCADEa1b1c2a1d1e2a1b1c2a2d2e3a2b2c2a1d1e2a2b2c2a2d2e3a3b1c4a1d1e2a3b1c4a2d2e3564数据的查询操作

4.1查询语句的基本结构2.依据条件<R.A=S.A>进行选择操作

ABCADEa1b1c2a1d1e2A2b2c2a2d2e3574数据的查询操作

4.1查询语句的基本结构进行投影操作

R.ABDEa1b1d1e2a2b2d2e3584数据的查询操作

4.1查询语句的基本结构说明：实际上，SQL不一定完全按照这个步骤进行操作。通常是采取某种等价的算法，例如，先选择，再做笛卡儿乘积，最后投影，用减少笛卡儿乘积次数的方法，来提高查询效率。

594数据的查询操作

4.2SELECT子句SQL查询语句的运算结果仍然是一个关系，这个关系的属性由SELECT子句的列名表给出。SELECT子句的参数有多种形式。

604数据的查询操作

4.2SELECT子句

1.指定某个或某一些属性。例21列出所有教授的姓名。SELECT姓名FROM教工WHERE职称='教授'查询结果：姓名葛小平康立华614数据的查询操作

4.2SELECT子句例22列出所有教授的姓名和工资：SELECT姓名,工资FROM教工WHERE职称='教授'查询结果：姓名工资葛小平3420.00康立华3740.00624数据的查询操作

4.2SELECT子句

2.指定FROM子句中所有关系的属性都将出现在查询结果中。例23列出教授的所有信息。SELECT*FROM教工WHERE职称='教授'

查询结果：

教工号姓名性别职称工资系编号2001葛小平女教授3420.001022302康立华女教授3740.00103634数据的查询操作

4.2SELECT子句

利用关键词DISTINCT消除重复出现的元组。例24列出教工表中的系编号。（注意有重复的系编号）

SELECT系编号

FROM教工

查询结果：系编号101102104101103102644数据的查询操作

4.2SELECT子句例25列出教工表中的系编号并消除重复的元组。SELECTDISTINCT系编号FROM教工查询结果：

系编号101102104103654数据的查询操作

4.2SELECT子句

4.在SELECT子句中还可以对输出的属性重命名。例26已知学分=学时/17，计算每一门课程的学分数。SELECT课程名,学时/17AS学分FROM课程

查询结果：

课程名学分数学4英语5计算机6经济学3664数据的查询操作

4.2SELECT子句

5.SELECT子句中可以包含表达式例27显示教授的工资和提高10%的工资额。 SELECT姓名,工资,工资*1.1AS'工资*1.1' FROM教工WHERE职称='教授'查询结果：

姓名工资工资*1.1葛小平3420.003762.00康立华3740.002915.00674数据的查询操作

4.2SELECT子句例28显示田平平同学出生100天的日期。SELECT姓名,出生年月+100AS'出生年月+100'FROM学生WHERE姓名='田平平'查询结果：

姓名出生年月出生年月+100田平平08/05/198011/13/1980684数据的查询操作

4.2SELECT子句

在后面的章节还会介绍SELECT子句的一些用法，例如，SELECT子句的参数还可以是算术函数。

694数据的查询操作

4.3WHERE子句

WHERE子句中将给出查询语句的选择条件，条件表达式中可使用逻辑运算符、比较运算符和算术运算符。WHERE子句中使用的运算符如下：1.比较运算符<、<=、>、>=、=、<>，用于字符串表达式、算术表达式，以及特殊的类型（如日期类型）的比较。比较表达式运算的结果是逻辑值真（T）或假（F），即表达式成立为真，否则为假。

704数据的查询操作

4.3WHERE子句例29列出教工表中工资在3000元以上的名单。SELECT姓名 FROM教工 WHERE工资>=3000714数据的查询操作

4.3WHERE子句例30列出学生表中1980年1月1日之后出生的学生名单。

SELECT姓名FROM学生WHERE出生年月>='1980-1-1'注意：进行比较的数据类型必须一致。

724数据的查询操作

4.3WHERE子句2.逻辑运算符AND（逻辑与）、OR（逻辑或）、NOT（逻辑非），可将多个比较表达式连接起来，构成复杂的逻辑表达式，表示复杂的条件。逻辑表达式运算的结果仍是逻辑值真（T）或假（F）。

734数据的查询操作

4.3WHERE子句例31列出学生表中在1980年1月1日之后出生的男同学名单。

SELECT姓名FROM学生WHERE出生年月>='1980-1-1'AND性别='男'744数据的查询操作

4.3WHERE子句例32列出教工表中教授或副教授中工资低于3000元的名单。

SELECT姓名FROM教工WHERE(职称='教授'OR职称='副教授')AND工资<3000754数据的查询操作

4.4FROM子句

FROM子句实现关系的笛卡儿积运算。WHERE子句实现选择操作，SELECT子句的列名表实现投影。若FROM子句中所列的关系数大于1，整个查询语句将实现关系代数的连接运算。

764数据的查询操作

4.4FROM子句例33显示男学生的姓名和所在的系名称。学生的姓名是学生关系的属性，系名称是系关系的属性，很显然，这两个数据来自两个表，由连接操作实现。关系代数表达式为：姓名,系名称σ性别=”男”(学生⋈系)

774数据的查询操作

4.4FROM子句实现这个查询的SQL语句：

SELECT姓名,系名称FROM学生,系WHERE学生.系编号=系.系编号AND性别='男'注意：由于系编号出现在多个表中，为了防止混淆，需要在属性前面加前缀。如果一个属性只在FROM子句所列关系中出现一次，可不加前缀。

784数据的查询操作

4.4FROM子句查询结果：姓名系名称郭黎明计算机何漓江外语郭黎明经济王海洋经济794数据的查询操作

4.4FROM子句

SQL允许在FROM子句中定义表的别名（简名），重写例33中的SQL语句。SELECT姓名,系名称FROM学生R,系SWHERER.系编号=S.系编号AND性别='男'对同一个关系中两个元组进行比较时，使用别名非常有用。

805字符串操作通常字符串的定界符是英文的双引号或单引号，有些DBMS系统可能规定比较特殊，例如，SQLServer系统的字符串定界符只能是英文的单引号，所以，本文例题采用单引号作为字符串的定界符。SQL语言中字符串操作符是LIKE或NOTLIKE。常用的字符串匹配符号有：百分号％表示任意子字符串。下划线

＿

表示任何一个字符。

815字符串操作字符串匹配符号的用法举例：'Be%'表示任何以”Be”开头的字符串。'%idge%'表示任何包含”idge”的字符串。'______'表示只含有六个字符的字符串。'______%'表示至少含有六个字符的字符串。

825字符串操作

1.字符串操作符LIKE的应用方法例35列出学生表中姓”王”的学生名单。

SELECT姓名FROM学生WHERE姓名LIKE’王％’

输出结果是：

姓名王海洋王立平835字符串操作例36假设不确切知道某人姓名中的某个字，可用下列命令查询：SELECT姓名FROM教工WHERE姓名LIKE'王_平'输出结果是：

姓名王伟平845字符串操作

2.字符串操作符NOTLIKE的应用方法例37查出所有不是教授的教工姓名。SELECT姓名FROM教工WHERE职称NOTLIKE‘教授’输出结果：

姓名李长江姜立伟张丽丽王伟平855字符串操作

有些系统的SQL语言中，还提供了很多专门用于字符串操作的函数。例如，连接（“||”）、提取子字符串、计算字符串长度、大小写转换等函数。

865字符串操作

4.几个特殊的运算符BETWEENAND查询条件是在某个范围之间NOTBETWEENAND不在某个范围之间

875字符串操作例38检索工资在1000元到2000元范围内的职工信息。SELECT*FROM职工

WHERE工资BETWEEN1000AND2000等价于SELECT*FROM职工

WHERE工资>=1000AND工资<=2000886排列元组的显示顺序在SQL的查询语句中，可以利用ORDERBY子句，对查询结果进行排序。ORDERBY子句是查询语句的可选项，语法格式是：SELECT<列名l>,<列名2>,…,<列名n>FROM<表1>,<表2>,…,<表m>WHERE<条件表达式>ORDERBY<列名表>[DESC][ASC]其中：DESC表示降序，ASC表示升序，若缺省默认为升序。

896排列元组的显示顺序例39按学号的升序，显示系编号等于101的学生信息：SELECT*FROM学生WHERE系编号=101ORDERBY学号

查询结果：

学号姓名性别出生年月系编号1022田平平女08/05/801011134包立琪女03/14/811011223姜明明男12/05/801011354王立平女12/05/81101907集合查询

在第2章介绍了关系代数并、交和差运算的概念。本节将用SQL语言实现这些关系代数的运算。SQL-92在关系上的UNION、INTERSECT和EXCEPT操作对应于关系代数中并（∪）、交（∩）、差（－）运算。

917集合查询设有关系R和S如图4所示。RS图4关系R和S

下面举例说明在SQL语言中，实现并、交、差运算的方法。

ABCDa1b1c1d1a2b2c2d2a3b2c3d1a4b4c4d3ABCa1b1c1a5b1c2a6b3c3a4b4c4927集合查询

7.1并操作

例40求R和S在属性A、B、C上投影的并集，其关系代数表达式是：A,B,C(R)

∪

A,B,C(S)

SQL语句是：(SELECTA,B,CFROMR)UNION(SELECTA,B,CFROMS)937集合查询

7.1并操作运行结果：

说明：与SELECT语句不同，UNION操作自动消除重复的元组。如果想保留所有重复，可以用UNIONALL代替UNION。

ABCa1b1c1a2b2c2a3b2c3a4b4c4a5b1c2a6b3c3947集合查询

7.1并操作例41求关系R和S在属性B上投影的并操作，要保留重复元组。关系代数表达式是：

B(R)∪B(S)SQL语句是：(SELECTBFROMR)UNIONALL(SELECTBFROMS)957集合查询

7.1并操作运行结果：

说明：UNIONALL操作可以保留重复的元组。

Bb1b2b2b4b1b1b3b4967集合查询

7.2交操作例42求关系R和S在属性A、B、C上投影的交集。关系代数表达式是：

A,B,C(R)∩A,B,C(S)SQL语句：(SELECTA,B,CFROMR)INTERSECT(SELECTA,B,CFROMS)977集合查询

7.2交操作运行结果：

说明：INTERSECT操作自动消除重复。如果想保留所有重复，可以用INTERSECTALL代替INTERSECT。

ABCa1b1c1a4b4c4987集合查询

7.2交操作

例43

求属于R且也属于S的B值，要求保留重复值。关系代数表达式是：

B(R)∩B(S)）SQL语句是：

(SELECTBFROMR)INTERSECTALL(SELECTBFROMS)997集合查询

7.2交操作

运行结果：

说明：INTERSECTALL操作可以保留重复的元组。注：SQLSERVER2000的T-SQL语言并不直接支持INTERSECT操作符，可用EXISTS可以模拟INTERSECT操作。

Bb1b4b1b1b41007集合查询

7.3差操作例44求关系R和S在属性A、B、C上投影的差集。关系代数表达式是：

A,B,C(R)-A,B,C(S)SQL语句是：(SELECTA,B,CFROMR)EXCEPT(SELECTA,B,CFROMS)1017集合查询

7.3差操作运行结果是：

说明：EXCEPT操作能够自动消除重复。如果想保留所有重复，可以用EXCEPTALL代替EXCEPT。

ABCa2b2c2a3b2c31027集合查询

7.3差操作例45找出属于R，且不属于S的B值，要求保留重复值。关系代数表达式：B(R)-B(S)SQL语句：(SELECTBFROMR)EXCEPTALL(SELECTBFROMS)1037集合查询

7.3差操作运行结果：

说明：EXCEPTALL将保留重复。在SQLSERVER2000的TRANSACTION_SQL语言并不直接支持EXCEPT操作符，可以用NOTEXISTS模拟EXCEPT操作。

Bb2b21048聚集函数聚集函数是对一组值进行计算，并返回单个值的函数。聚集函数经常与查询语句的GROUPBY子句一同使用，在查询结果中生成汇总值。表2中列举SQL的聚集函数。

1058聚集函数

表2SQL的聚集函数

函数名功能参数类型AVG求平均值数值SUM求总和数值MAX求最大值数值、其他类型MIN求最小值数值、其他类型COUNT计数数值、其他类型1068聚集函数

其中AVG和SUM函数的参数必须是数值型，其他函数的参数还可以是非数值型，如字符串。聚集函数只能作为SELECT和HAVING子句的参数。除了COUNT函数之外，其他聚集函数忽略空值。

1078聚集函数1.求平均值函数聚集函数AVG用于计算列中数值的平均值，所以函数AVG的参数必须是数值型。

1088聚集函数例46求教工表中教授工资的平均值。

SELECTAVG(工资)AS平均工资FROM教工WHERE职称=‘教授’查询结果：

平均工资35801098聚集函数2.求总和函数聚集函数SUM用于计算列中数值的总和，函数SUM的参数必须是数值型。

1108聚集函数例47求所有教工的工资总和。

SELECTSUM(工资)AS工资总和

FROM教工

查询结果：工资总和1786001118聚集函数求最大值和最小值函数聚集函数MAX用于求列中的最大值，MIN求列中的最小值，这两个的参数允许是数值型的，也可以是其他数据类型（如字符型和时间型的数据）。

1128聚集函数例48

找出教工中最高工资和最低工资。

SELECTMAX(工资)，MIN(工资)FROM教工查询结果：

MAX(工资)MIN(工资)3740.002130.001138聚集函数

例49显示学生中最早和最晚的出生日期。

SELECTMIN(出生日期),MAX(出生日期)FROM学生查询结果：

注意：出生日期是日期型数据，日期型数据的特点，最大数据恰好是最小年龄。

MIN(出生日期)MAX(出生日期)10/05/197904/13/19821148聚集函数4.计数器函数聚集函数COUNT统计表中的行数。COUNT函数有3种格式：

COUNT（*）统计所有行数，包括含有空值的行。COUNT（表达式）计算每一行中表达式的值，并返回非空值的行数。COUNT(DISTINCT(表达式))计算每一行表达式的值，并返回唯一值且非空值的行数。

1158聚集函数例50查询学生的总人数。

SELECTCOUNT(*)AS人数FROM学生查询结果：

人数101168聚集函数

例51假设在学生表中增加一名新生，只插入该生的学号和姓名。执行下列插入语句：

INSERTINTOSTUDENT(SNO,SNAME)VALUES('991033','葛小燕')

分别利用COUNT（）函数的三种格式，观察COUNT（）函数的使用方法。

1178聚集函数（1）

如果要统计学生表的行数，即包括系编号为空值的行在内，则使用第一种格式。

SELECTCOUNT(*)FROM学生

查询结果：

COUNT(*)111188聚集函数（2）如果要统计学生表中系编号非空值的行数，即不包括系编号为空值的行，则使用第二种格式。SELECTCOUNT(系编号)FROM学生

查询结果：

COUNT(系编号)101198聚集函数（3）如果要统计成绩表中所有选课的学生人数，即不管一名学生选了几门课程，都只计算一次，则使用第三种格式。SELECTCOUNT(DISTINCT(学号))AS人数FROM成绩查询结果：

人数51209GROUPBY和HAVING子句在SQL语言的查询语句中，可以用GROUPBY子句实现对元组的分组功能。还可以利用HAVING子句对GROUPBY分组的结果进行筛选，保留满足条件的分组。GROUPBY子句HAVING子句是查询语句的可选项，语法格式是：SELECT<列名l>,<列名2>,…,<列名n>FROM<表1>,<表2>,…,<表m>WHERE<条件表达式>[GROUPBY<分组表达式>] [HAVING<筛选条件表达式>]

1219GROUPBY和HAVING子句

9.1GROUPBY子句在GROUPBY子句中的分组表达式可以是一个属性或者多个属性，其功能是将在分组表达式上具有相同值的元组放在一个组内。

1229GROUPBY和HAVING子句

9.1GROUPBY子句例52

统计学生表中男生和女生的人数。SELECT性别,COUNT(*)FROM学生 GROUPBY性别

查询结果：

性别COUNT(*)男5女51239GROUPBY和HAVING子句

9.1GROUPBY子句

例52统计成绩表中，每一门课程的平均成绩。SELECT课程编号,AVG(分数)FROM成绩GROUPBY课程编号查询结果：

注意：带有GROUPBY子句的查询语句中，SELECT子句的列名中必须包括分组表达式，还可以包括集聚函数，除此而外不能有其他列名。

课程编号AVG(分数)C170C284C365C4871249GROUPBY和HAVING子句

9.1GROUPBY子句例53查询教工表中每一种职称的最高工资和最低工资。SELECT职称,MAX(工资),MIN(工资)

FROM教工GROUPBY职称

查询结果：

职称MAX(工资)MIN(工资)教授3740.003420.00副教授3190.003140.00讲师224002130.001259GROUPBY和HAVING子句

9.2HAVING子句使用GROUPBY子句时，可以利用HAVING子句对GROUPBY分组的结果进行筛选，保留满足条件的分组。HAVING子句的格式：HAVING<条件表达式>1269GROUPBY和HAVING子句

9.2HAVING子句HAVING与WHERE子句都有<条件表达式>，注意两者之间的区别。WHERE子句中的<条件表达式>是在GROUPBY分组之前起作用，而HAVING子句的<条件表达式>是在形成分组后起作用，所以，在HAVING的条件表达式中可以使用聚集函数（这一点与WHERE不同）。

1279GROUPBY和HAVING子句

9.2HAVING子句例54

对于成绩表中分数在60以上的行按照学号分组，其中只包含选课数大于2且平均分超过70的学号、选课数和平均分。SELECT学号,COUNT(课程编号),AVG(分数)FROM成绩 WHERE分数>=60GROUPBY学号HAVINGCOUNT(课程编号)>2ANDAVG(分数)>70分析这个语句的WHERE、GROUPBY和HAVING子句执行的顺序。已知成绩表如图4所示。

1289GROUPBY和HAVING子句

9.2HAVING子句成绩

图4成绩表

学号课程号分数1022C101881022C102672124C101562124C102952124C103454021C104874021C102784021C103671223C101661223C102893012C102933012C103841299GROUPBY和HAVING子句

9.2HAVING子句（1）这个语句首先执行WHERE子句，选择满足条件的10行（去除不及格的2行）。

学号课程号分数1022C101881022C102672124C102954021C104874021C102784021C103671223C101661223C102893012C102933012C103841309GROUPBY和HAVING子句

9.2HAVING子句（2）然后执行GROUPBY子句，将10行按照学号分成5组。

学号课程号分数1022C101881022C102672124C102954021C104874021C102784021C103671223C101661223C102893012C102933012C103841319GROUPBY和HAVING子句

9.2HAVING子句（3）最后执行HAVING子句，以“选课数大于2且平均分超过70”为条件筛选分组的结果，最后满足条件的组只有1组。

运行结果

：

学号课程号分数4021C104874021C102784021C10367学号COUNT(课程号)

AVG(分数)

4021377.331329GROUPBY和HAVING子句

9.2HAVING子句例55

统计成绩表中选修人数超过2以上的课程编号和人数。

SELECT课程编号,COUNT(*)FROM成绩GROUPBY课程编号HAVINGCOUNT(*)>2

首先，按照课程编号分成4组，再过滤掉选修人数小于或等于2的组。

13310空值在SQL语言中，允许使用NULL值表示某个属性的值为空（即没有值）。对于空值的处理方法和原则是一个容易混淆的问题。

13410空值

10.1ISNULL和ISNOTNULL运算符在SQL语言中，允许在条件表达式中使用特殊的运算符号ISNULL测试属性值是否为空值，或者用ISNOTNULL测试是否为非空值。下面举例说明这两个运算符的使用方法。假设有贷款表如图5所示。

图5贷款关系

帐号姓名金额G10020关平之200300T20078李连玉K65743姜海洋30092.00H89765田平平30200.60H76890方一华19080.4013510空值

10.1ISNULL和ISNOTNULL运算符例56找出贷款表中金额为空值的帐号和姓名。

SELECT帐号,姓名FROM贷款WHERE金额ISNULL

查询结果：

帐号姓名T20078李连玉13610空值

10.1ISNULL和ISNOTNULL运算符例57找出贷款表中贷款金额非空的帐号和姓名。

SELECT帐号,姓名FROM贷款WHERE金额ISNOTNULL

查询结果：

帐号姓名G10020关平之K65743姜海洋H89765田平平H76890方一华13710空值

10.2空值的处理原则

1.在算术运算和比较运算中对NULL的处理方法：若算术运算（+、-、*或/）的参数中含有NULL，则该算术表达式的运算结果是NULL。若比较运算中有NULL作为比较对象，则比较的结果视为假（false）。SQL92规定，比较运算中有NULL作为比较对象，则比较的结果是“不知道（unknown）”，而不用假（false）来表示。几乎其他所有情况下，将unknown作为false。13810空值

10.2空值的处理原则2.聚集函数对NULL的处理原则：SUM、AVG、MIN、MAX函数对参数中的空值（NULL）忽略不计。若所有参数值都是空值（NULL），则函数的返回值是NULL。COUNT函数对参数中的空值（NULL）也计数。若所有参数值都是空值（NULL），则函数的返回值是0。

13911关系的连接操作利用连接操作，可以根据表与表之间的逻辑联系从两个或多个表中查询数据。早期的SQL版本，实现连接方法是在FROM子句中给出连接操作的表名，在WHERE子句中给出连接和选择的条件。

14011关系的连接操作例58从学生、课程和成绩表中，产生数据库课程的成绩单。SELECT姓名,课程名,分数FROM学生S,课程C,成绩GWHERES.学号=G.学号ANDG.课程编号=C.课程号ANDC.课程名='数据库'14111关系的连接操作SQL-92提供更丰富的连接操作，包括：内连接、条件连接、自然连接、左外连接、右外连接、全连接，同时增加了在FROM子句定义连接条件的方法。FROM子句的语法格式：FROM<表1><连接类型><表2>[ON(<连接条件>)]14211关系的连接操作例58题的SQL语句也可以等价为：SELECT姓名，课程名，分数FROM学生SJOIN成绩GON(S.学号=G.学号)JOIN课程CON(G.课程号=C.课程号)WHEREC.课程名称=’数据库’14311关系的连接操作

说明：利用FROM子句定义连接条件，不仅可以简化连接条件的表达方法，而且能够提高查询效率。因为，在SQL系统中FROM、WHERE和HAVING子句执行的逻辑顺序是：FROM子句中的连接条件。WHERE子句中的连接条件与选择条件。HAVING子句中的筛选条件。在使用支持SQL-92标准的系统时，建议用FROM子句定义连接条件。表3中列出各种连接操作的类型。

14411关系的连接操作表3连接操作一览表

连接名称SQL-92关键字内连接INNERJOIN自然连接NATURALJOIN交叉连接CROSSJOIN左外连接LEFTOUTERJOIN右外连接RIGHTOUTERJOIN全连接FULLOUTERJOIN自连接SELFJOIN14511关系的连接操作

11.1内连接关系R和S进行连接操作，且连接条件为P。若连接操作的结果中只包含R与S在P上相匹配的行，这种连接属于内连接（INNERJOIN）。用FROM子句表示内连接的子句：FROMR[INNER]JOINS[ON(<连接条件>)]其中，INNER可省略。

14611关系的连接操作

11.1内连接假设有导师与研究生2个表，如图6所示。

导师研究生图6导师与研究生表

教工号姓名性别T001江海男T002代宁女T005潘涛男T008田立女学号姓名导师号99001李南T00199002刘星T00299003王海T00299004张力T00199006郭天14711关系的连接操作

11.1内连接例59查询研究生与其导师的情况。此查询涉及教师和研究生2个表，连接条件是：<研究生.导师号=教师.教工号>。关系代数表达式是：

教师

⋈研究生

SELECT*FROM研究生AINNERJOIN教师BONA.导师号=B.教工号也可以写成等价的SQL语句：SELECT*FROM研究生A,导师BWHEREA.导师号=B.教工号14811关系的连接操作

11.1内连接查询结果是：

这是一个内连接的例子，查询结果中只包含满足连接条件的4行。

学号

姓名

导师号

教工号姓名

性别

99001李南T001T001江海男99002刘星T002T002代宁女99003王海T002T002代宁女99004张力T001T001江海男14911关系的连接操作

11.2自然连接设关系R和S进行连接操作，其连接条件为P。如果P是R与S中连接属性的等值比较，称为等值连接。若R与S进行等值连接，且R与S的连接属性名相同，称为自然连接。自然连接是等值连接的特例。自然连接的实现方法是在内连接的基础上，用SELECT子句消除重复列。

15011关系的连接操作

11.2自然连接例60根据学生、课程和成绩表，输出“计算机”课程的成绩单，包括姓名和分数。这个查询是三个表的自然连接，其关系代数表达式是：

∏姓名，分数（σ课程名=’数据库’（学生⋈成绩⋈课程））

SELECT姓名，分数

FROM学生AJOIN成绩BON（A.学号=B.学号）JOIN课程CON（B.课程号=C.课程号）WHEREC.课程名=’计算机’15111关系的连接操作

11.2自然连接等价的SQL语句：SELECT姓名,分数FROM学生A,成绩B,课程CWHEREA.学号=B.学号ANDB.课程编号=C.课程号ANDC.课程名='计算机'

查询结果：

这是一个自然连接的实例，查询结果中包含满足连接条件元组，且没有重复列。

姓名分数

郭黎明45何明慧67何漓江8415211关系的连接操作

11.3交叉连接

设有关系R和S，R与S的笛卡儿乘积称为交叉连接。在SQL中，实现R与S交叉连接的方法是在FROM子句中定义交叉连接类型：FROMRCROSSJOINS15311关系的连接操作

11.3交叉连接例61根据图6中导师与研究生表，计算导师与研究生关系的笛卡儿乘积。SELECT*FROM导师

CROSSJOIN研究生等价的SQL语句：SELECT*FROM导师,研究生

15411关系的连接操作

11.3交叉连接运行结果：这是一个交叉连接的实例，查询结果中包含20行。

15511关系的连接操作

11.4左外连接关系R与S进行连接操作，连接条件为P。若连接操作的结果中除了R与S在P上内连接结果之外，还包括左边关系R在内连接操作中不相匹配的元组，而其对应于S的属性赋予空值，这种连接称为左外连接（LEFTOUTERJOIN）。实现左外连接的SQL子句：FROMRLEFTOUTERJOINSON(<连接条件>)

15611关系的连接操作

11.4左外连接例62查询教师指导研究生的情况，包括不指导研究生的教师。

SELECT*FROM导师ALEFTOUTERJOIN研究生BON(A.教工号=B.导师号)15711关系的连接操作

11.4左外连接

查询结果：可见左外连接操作的结果中除了满足连接条件元组的之外，还包含左边关系不满足连接条件的元组，而其对应的右边属性全部填充NULL。

教工号姓名性别学号姓名导师号T001江海男99001李南T001T002代宁女99002刘星T002T002代宁女99003王海T002T001江海男99004张力T001T005潘涛男NULLNULLNULLT008田立女NULLNULLNULL15811关系的连接操作

11.5右外连接关系R与S进行连接操作，连接条件为P。若连接操作的结果中除了R与S在P上内连接结果之外，还包括右边关系S在内连接操作中不相匹配的元组，而其对应于R的属性赋予空值，这种连接称为右外连接（RIGHTOUTERJOIN）。实现右外连接的SQL子句：FROMRRIGHTOUTERJOINSON(<连接条件>)

15911关系的连接操作

11.5右外连接例63查询所开课程的选修情况，包括没有任何学生选修的课程。SELECT学号,课程名,分数FROM成绩ARIGHTOUTERJOIN

课程B