关系数据库标准语言SQL

1、关系数据库标准语言SQL第三章 关系数据库标准语言SQL3.1 SQL概述概述3.2 数据定义数据定义3.3 查询查询3.4 数据更新数据更新3.5 视图视图3.6 数据控制数据控制3.7 嵌入式嵌入式SQL关系数据库标准语言SQLSQL历史 SQ是什么？ SQL是一个通用的、功能极强的关系数据库的标准语言。 SQL语言是介乎于关系代数和元组演算之间的一种结构化查询语言。 本章详细介绍SQL的核心部分内容：数据定义、数据查询、数据更新和嵌入式SQL。 历史 1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。 1972年，IBM公司开始研制实验型关系数据库管理系统S

2、YSTEM R，配制的查询语言称为SQUARE (Specifying Queries As Relational Expression )语言，在语言中使用了较多的数学符号。 1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL (Structured English QUEry Language )语言。后来SEQUEL简称为SQL (Structured Query Language )，即“结构式查询语言”，SQL的发音仍为“sequel”。现在SQL已经成为一个标准 。关系数据库标准语言SQLSQL概述 SQL的功能SQL数据定义功能SQL模式、基本表和索引的

3、创建和撤销SQL数据查询功能SELECT语句的句法，SELECT语句的几种形式及各种限定，基本表的联接操作，SQL3中的递归查询SQL数据修改功能或数据操纵插入、删除和修改语句视图的创建和撤销，对视图更新操作的限制SQL数据控制功能GRANT，REVOKE 嵌入式SQL：预处理方式，使用规定，使用技术，卷游标，动态SQL语句关系数据库标准语言SQL数据库的体系结构 用户用户1 1用户用户2 2用户用户3 3用户用户4 4视图视图1 1视图视图1 1基本表基本表1 1基本表基本表2 2基本表基本表3 3基本表基本表4 4存储文件存储文件1 1存储文件存储文件2 2存储文件存储文件3 3存储文件存

4、储文件4 4SQLSQL数据库的体系结构数据库的体系结构SQLSQL用户用户ViewViewB a s e B a s e tabletableStoreStored d filefile关系数据库标准语言SQL基本概念基本表是本身独立存在的表，在SQL中一个关系就是一个基本表。一个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。存储文件存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构是任意的，对用户是透明的。视图 是从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，

5、因此视图是一个虚表。视图在概念上于基本表等同，用户可以在视图上再定义视图。关系数据库标准语言SQL基本概念 主要特点包括： 综合统一 高度非过程化 面向集合的操作方式 以同一种语法结构提供两种使用方式 语言简捷，易学易用SQL语言支持关系数据库三级模式结构。其中: 外模式 对应于 视图和部分基本表， 模式 对应于 基本表， 内模式 对应于 存储文件。用户可以用SQL语言对基本表和视图进行查询或其他操作，基本表和视图一样，都是关系。关系数据库标准语言SQLSQL的组成 核心SQL主要有四个部分: 数据定义语言即SQL DDL，用于定义SQL模式、基本表、视图、索引等结构。 数据操纵语言即SQL

6、DML。数据操纵分成数据查询和数据更新两类。其中数据更新又分成插入、删除和修改三种操作。 嵌入式SQL语言的使用规定。这一部分内容涉及到SQL语句嵌入在宿主语言程序中的规则。 数据控制语言即SQL DCL，这一部分包括对基本表和视图的授权、完整性规则的描述、事务控制等内容。 返回关系数据库标准语言SQLSQL数据定义功能域定义*SQL提供的主要数据类型(也称为“域类型”)有：（1）数值型（2）字符串型（3）位串型（4）时间型基本表的定义索引的定义数据库的建立与撤消SQL数据定义特点关系数据库标准语言SQL定义基本表格式如下： （列级完整性约束 ，列级完整性约束，），primary key（列名

7、 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件）;说明：如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。注意：定义表时每一个定义语句之间用逗号分隔，最后一条语句不用逗号。每个SQL语句以分号结束。关系数据库标准语言SQL示例数据库DEPT(Dno , DNAME , DEAN)Student(Sno , SNAME , SEX , AGE , Dno)COURSE(Cno , CName , PCno , CREDIT)SC(Sno , Cno , SCORE)P

8、ROF(Pno , PNAME, AGE, Dno , SAL)PC(Pno , Cno)关系数据库标准语言SQL例子例1 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。CREATETABLE Student ( Sno CHAR(5) NOT NULL UNIQUE,Sname CHAR(20),Ssex CHAR(1),Sage INT,Sdept CHAR(15) );关系数据库标准语言SQL例子*：例2CREATE TABLE Course（cno char(4) no

9、t null，cname char(10) not null，PCno char(4) , CREDIT int,PRIMARY KEY(CNO) )；例3CREATE TABLE SC (SNO char(5) NOT NULL，CNO char(4) NOT NULL，GRADE SMALLINT，PRIMARY KEY(SNO,CNO) ，FOREIGN KEY(SNO) REFERENCES S(SNO) ，FOREIGN KEY(CNO) REFERENCES C(CNO) ) ；关系数据库标准语言SQL常见域类型*char（n）或charter（n） ：固定长度的字符串。varch

10、ar（n）：最大长度为n的可变长字符串。Int或integer：全字长二进制整数。smallint：半字长二进制整数。numeric（p，d）：定点数，小数点左边p位，右边q位。Decimal(p,q)或Dec (p,q)压缩十进制数,共P位，其中小数点后有q位，0=q=p 0)关系数据库标准语言SQL修改基本表结构格式为：ALTER TABLE ADD 完整性约束DROP MODIFY ；ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除制定的完整性约束条件，MODIFY子句用于修改原有的列定义，包括修改列名和数据类型。示例alter table PROF add LOCATI

11、ON char30关系数据库标准语言SQL例2 向Student表增加“入学时间”列，其数据类型为日期型ALTER TABLE Student ADD Scome DATE; 不论基本表中原来是否已有数据，新增加的列一律为空值。 例3 将年龄的数据类型改为半字长整数ALTER TABLE Student MODIFY Sage SMALLINT; 修改原有的列定义有可能会破坏已有数据。 例4 删除关于学号必须取唯一值的约束ALTER TABLE Student DROP UNIQUE(Sno); 说明：SQL没有提供删除属性列的语句，用户只能间接实现这一功能，即先原表中要保留的列及其内容复制到

12、一个新表中，然后删除原表，并将新表重命名为原表名。 关系数据库标准语言SQL删除基本表格式为：DROP TABLE 示例5 ：删除Student表DROP TABLEStudent 撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除。关系数据库标准语言SQL索引：加快查询速度 建立索引的一般格式为：CREATE UNIQUE CLUSTER INDEX ON （，）； 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，可选ASC（升序）或DESC（降序），缺省值为ASC。 UNIQUE表明此索引的每一个索引值只对应唯一的数

13、据记录。 CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。用户可以在最常查询的列上建立聚簇索引以提高查询效率。显然在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。 示例 create cluster index s-index on S(S#)关系数据库标准语言SQL例子例6为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引，CREATE UNIQUE INDEX Stu

14、sno ON Student(Sno);Couse表按课程号升序建唯一索引，CREATE UNIQUE INDEX Coucno ON Couse(Cno);Sno、Cno表按学号升序和课程号降序建唯一索引。CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); 关系数据库标准语言SQL删除索引一般格式：DROP INDEX ；索引的有关说明什么时候适合建立索引？可以动态地定义索引，即可以随时建立和删除索引。不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。索引是否越多越好？应该在使用频率高的、经常用于连接的列上建

15、索引。一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。例7 删除Student表的索引Stusname（假设已存在）DROP INDEX Stusname; 关系数据库标准语言SQL数据库的建立与撤消 有的数据库系统支持多库。建立一个新数据库create database 数据库名撤消一个数据库drop database 数据库名指定当前数据库database 数据库名指定当前数据库close database 数据库名关系数据库标准语言SQLSQL数据定义特点* SQL中，任何时候都可以执行一个数据定义语句，随时修改数据库结构。而在非关系型的数

16、据库系统中，必须在数据库的装入和使用前全部完成数据库的定义。若要修改已投入运行的数据库，则需停下一切数据库活动，把数据库卸出，修改数据库定义并重新编译，再按修改过的数据库结构重新装入数据。数据库定义不断增长（不必一开始就定义完整）。数据库定义随时修改（不必一开始就完全合理）。可进行增加索引、撤消索引的实验，检验其对效率的影响。关系数据库标准语言SQLSQL数据查询功能*SQL数据查询基本结构select子句重复元组的处理from子句where子句更名运算字符串操作元组显示顺序集合操作分组和聚集函数空值嵌套子查询派生关系视图关系的连接关系数据库标准语言SQL查询 数据库查询是数据库的核心操作。S

17、QL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。其一般格式为： SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC； 关系数据库标准语言SQLSELECT语句的含义 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组， 再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。 如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一组。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定

18、条件的组才予输出。 如果有ORDER BY子句，则结果表还要按的值的升序或降序排序。 DISTINCT（ALL）表示选出的记录中不包括（包括）重复记录 注意：语法中方括号里的内容是可选项，根据功能的不同可以选也可以不选，尖括号中的内容是必选项。关系数据库标准语言SQLSQL数据查询基本结构基本结构select A1 , A2 , , Anfrom r1 , r2 , , rmwhere P A1 , A2 , , An(p(r1 r2 rm)示例给出所有老师的姓名。select PNAMEfrom PROF关系数据库标准语言SQLselect子句目标列形式 可以为列名、* 、算术表达式、聚集函

19、数。“*”：表示“所有的属性”。例：给出所有老师的信息。select *from PROF带， ， 的算术表达式例：给出所有老师的姓名及税后工资额。select PNAME，SAL 0.95from PROF关系数据库标准语言SQL重复元组的处理语法约束 缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct或unique指明。示例 找出所有选修课程的学生。 select distinct SNO from SC关系数据库标准语言SQLfrom子句（）说明 from子句列出查询的对象表。当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系。示

20、例例：找出工资低于500的职工的姓名、工资、系别。 select PNAME , SAL , DNAME from PROF , DEPT where SAL P2.SAL关系数据库标准语言SQL元组显示顺序命令order by 列名 asc | desc示例按系名升序列出老师姓名，所在系名，同一系中老师按姓名降序排列。 select DNAME，PNAME from PROF，DEPT where PROF.DNO = DEPT.DNO order by DNAME asc，PNAME desc关系数据库标准语言SQL单表查询 选择表中的若干列 1查询指定列 中各个列的先后顺序可以与表中的顺

21、序不一致。用户可以根据应用的需要改变列的现实顺序。 2查询全部列 3查询经过计算的值 不仅可以是算术表达式，还可以是字符串常量、函数等。注意算数表达式、字符串常量、函数做目标列表达式。用字符串常量做目标列表达式时，必须用单引号把字符串引起来，表示一个字符串常量。 用户可以通过指定别名来改变查询结果的列标题，这对于含算数表达式、常量、函数名的目标列表达式尤为有用。 关系数据库标准语言SQL查询指定列例子例1查询全体学生的学号与姓名SELECT Sno,SnameFROM Student; 中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。例2查询

22、全体学生的姓名、学号、所在系SELECT Sname, Sno, SdeptFROM Student; 这时结果表中的列的顺序与基表中不同，是按查询要求，先列出姓名属性，然后再列学号属性和所在系属性。关系数据库标准语言SQL查询全部列例子例3查询全体学生的详细记录SELECT *FROM Student;该SELECT语句实际上是无条件地把Student表的全部信息都查询出来，所以也称为全表查询，这是最简单的一种查询。关系数据库标准语言SQL查询经过计算的值例子SELECT子句的不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 例4 查全体学生的姓

23、名及其出生年份SELECT Sname, 2005-SageFROM Student; 本例中，中第二项是一个计算表达式。v例例4输出的结果为：输出的结果为： Sname 1996-Sage-李勇李勇1976刘晨刘晨1977王名王名1978张立张立1977-关系数据库标准语言SQL例5查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名SELECT Sname, Year of Birth:, 1996-Sage, ISLOWER(Sdept)FROM Student;结果为： Sname Year of Birth:1996-SageISLOWER(Sdept)-李勇 Year o

24、f Birth:1976cs刘晨 Year of Birth:1977is王名 Year of Birth:1978ma张立 Year of Birth:1977is关系数据库标准语言SQL 用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以如下定义列别名 SELECTSname NAME, Year of Birth:BIRTH,1996-SageBIRTHDAY,ISLOWER(Sdept) DEPARTMENTFROMStudent;结果为： NAMEBIRTH BIRTHDAY DEPARTMENT -李勇Year o

25、f Birth: 1976cs刘晨Year of Birth: 1977is王名Year of Birth: 1978 ma张立Year of Birth: 1977 is关系数据库标准语言SQL选择表中的若干元组 1消除取值重复的行 查询结果里可能包含了许多重复行。如果想去掉结果表中的重复行，必须指定DISTINCT短语。如果没有指定DINTINCT短语，则缺省为ALL，即保留结果表中取值重复的行。 关系数据库标准语言SQL例6 查所有选修过课的学生的学号SELECT SnoFROM SC;假设SC表中有下列数据 Sno Cno Grade- - - 95001 1 92 95001 2 8

26、5 95001 3 88 95002 2 90 95002 3 80 执行左面的执行左面的SELECT语句后，结语句后，结果为：果为： Sno - 95001 95001 95001 95002 95002 查询结果里包含了查询结果里包含了许多重复的行。许多重复的行。如果想去掉结果表中的重复行，必须指定如果想去掉结果表中的重复行，必须指定DISTINCT短语：短语： SELECT DISTINCT Sno FROM SC; 执行结果为：执行结果为： Sno - 95001 95002 -关系数据库标准语言SQL查询满足条件的元组查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常

27、用的查询条件如书上表3.3。 比较大小 确定范围 确定集合字符匹配 涉及空值的查询多重条件查询关系数据库标准语言SQL表3-3 常用的查询条件查询条件谓 词比较 =,=,=,!=,!,! NOT+上述比较运算符确定范围BETWEEN AND, NOT BETWEEN AND确定集合IN, NOT IN字符匹配LIKE, NOT LIKE空值IS NULL, IS NOT NULL多重条件AND, OR 关系数据库标准语言SQL比较大小例7查计算机系全体学生的名单SELECT Sname FROM Student WHERE Sdept = CS; 例8查所有年龄在20岁以下的学生姓名及其年龄S

28、ELECT Sname, Sage FROM Student WHERE Sage = 20; 关系数据库标准语言SQL例9查考试成绩有不及格的学生的学号SELECT DISTINCT Sno FROM SCWHERE Grade 60; 这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。 关系数据库标准语言SQL确定范围例10 查询年龄在20至23岁之间的学生的姓名、系别、和年龄SELECT Sname, Sdept, SageFROM Student WHERE Sage BETWEEN 20 AND 23; 与BETWEEN.AND.相对的谓词是NOT BE

29、TWEEN.AND.。 例11 查询年龄不在20至23岁之间的学生姓名、系别和年龄。SELECT Sname, Sdept, Sage FROM StudentWHERE Sage NOT BETWEEN 20 AND 23; 关系数据库标准语言SQL确定集合例12 查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别SELECT Sname, Ssex FROM Student WHERE Sdept IN (IS, MA, CS) 与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。 例13 查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别S

30、ELECT Sname, Ssex FROM Student WHERE Sdept NOT IN (IS, MA, CS) 关系数据库标准语言SQL例：查询至少选修了1号和3号课程学生的学号 SELECT SNO FROM SC WHERE CNO=1 AND SNO IN(SELECT SNO FROM SC WHERE CNO=3); 关系数据库标准语言SQL字符匹配语法格式如下： NOT LIKE ESCAPE 其含义是查找指定的属性列值与相匹配的元组。可以是一个完整的字符串，也可以含有通配符%和_。其中： %（百分号）代表任意长度（长度可以为0）的字符串。 _（下横线）代表任意单个字

31、符。 如果用户要查询的字符串本身就包含有%或_，这时就要使用ESCAPE 短语对通配符进行转义了。 关系数据库标准语言SQL示例列出姓名以“张”打头的教师的所有信息。 select * from PROF where PNAME like 张%列出名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的系的所有信息。 select * from PROF where PNAME like % d ESCAPE 关系数据库标准语言SQL例14 查所有姓刘的学生的姓名、学号和性别SELECT Sname, Sno, SsexFROM Student WHERE Sname LIKE 刘%

32、; 例15 查姓“欧阳”且全名为三个汉字的学生的姓名SELECT Sname FROM Student WHERE Sname LIKE 欧阳_; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个_。 关系数据库标准语言SQL例16 查名字中第二字为“阳”字的学生的姓名和学号SELECT Sname, Sno FROM Student WHERE Sname LIKE _阳%; 例17 查所以不姓刘的学生姓名SELECT Sname, Sno, SsexFROM StudentWHERE Sname NOT LIKE 刘%;关系数据库标准语言SQL如果用户要查询的匹配字符串本身就

33、含有%或_，比如要查名字为DB_Design的课程的学分，应如何实现呢？这时就要使用ESCAPE 短语对通配符进行转义了。 例18 查DB_Design课程的课程号和学分SELECT Cno, CcreditFROM CourseWHERE Cname LIKE DB_Design ESCAPE ESCAPE 短语表示为换码字符，这样匹配串中紧跟在后面的字符”_”不再具有通配符的含义，而是取其本身含义，被转义为普通的”_”字符。关系数据库标准语言SQL例19查以”DB_”开头，且倒数第三个字符为i的课程的详细情况SELECT *FROM CourseWHERE Cname LIKE DB_%i

34、_ ESCAPE ;注意：这里的匹配字符串DB_%i_。第一个_前面有换码字符，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符，所以它们仍作为通配符。其执行结果为： CnoCnameCcredit-8 DB_Design 410DB_Programing 213DB_DBMS Design4 关系数据库标准语言SQL涉及空值的查询例20某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号SELECT Sno, Cno FROM SCWHERE Grade IS NULL; 注意这里的IS不能用等号（=）

35、代替。 例21查所有有成绩的记录的学生学号和课程号SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL; 关系数据库标准语言SQL多重条件查询逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。例22查CS系年龄在20岁以下的学生姓名SELECT Sname FROM Student WHERE Sdept=CS AND Sage20;关系数据库标准语言SQL 例12 中的IN谓词实际上是多个OR运算符的缩写，因此例12中的查询也可以用OR运算符写成如

36、下等价形式：SELECT Sname, Ssex FROM Student WHERE Sdept=IS OR Sdept=MA OR Sdept=CS; 附：例12查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别SELECT Sname, Ssex FROM Student WHERE Sdept IN (IS, MA, CS) 关系数据库标准语言SQL对查询结果排序 如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查

37、询结果，其中升序ASC为缺省值。 对于空值，若按升序排，含空值的元组将最后显示。若按降序排，空值的元组将最先显示。多列排序时，排序列的顺序不一样，排序优先级也不同，前面的优先级高。关系数据库标准语言SQL例23 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列SELECT Sno, GradeFROM SCWHERE Cno=3 ORDER BY Grade DESC;例24 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列 SELECT * FROM Student ORDER BY Sdept, Sage DESC; 关系数据库标准语言SQL四、使

38、用集函数 为增强检索功能，SQL提供了许多集函数，主要包括： COUNT(DISTINCT|ALL *) 统计元组个数 COUNT(DISTINCT|ALL ) 统计一列中值的个数 SUM(DISTINCT|ALL ) 计算一列值的总和（此列必须是数值型） AVG(DISTINCT|ALL ) 计算一列值的平均值（此列必须是数值型）MAX(DISTINCT|ALL ) 求一列值中的最大值 MIN(DISTINCT|ALL ) 求一列值中的最小值 如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语，则表示不取消重复值。 关系数据库标

39、准语言SQL例25 查询学生总人数 SELECT COUNT(*) FROM Student; 例26 查询选修了课程的学生人数SELECT COUNT(DISTINCT Sno)FROM SC;学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。关系数据库标准语言SQL例27 计算1号课程的学生平均成绩 SELECT AVG(Grade) FROM SC WHERE Cno=1; 例28 查询学习1号课程的学生最高分数SELECT MAX(Grade) FROM SC WHERE Cno=1; 关

40、系数据库标准语言SQL对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。 HAVING短语指定选择组的条件，只有满足条件的组才会被选出来。HAVING短语的条件由集函数构造。 关系数据库标准语言SQL关系数据库标准语言SQL例30 查询信息系选修了3门以上课程的学生的学号SELECT SnoFROM SCWHERE Sdept=IS;GROUP BY SnoHAVING

41、COUNT(*)3; 查选修课程超过3门的信息系学生的学号，首先需要通过WHERE子句从基本表中求出信息系的学生。 然后求其中每个学生选修了几门课，为此需要用GROUP BY子句按Sno进行分组，再用集函数COUNT对每一组计数。 如果某一组的元组数目大于3，则表示此学生选修的课超过3门，应将他的学生号选出来。HAVING短语指定选择组的条件，只有满足条件（即元组个数3）的组才会被选出来。 关系数据库标准语言SQL分组和聚集函数（）分组命令group by 列名 having 条件表达式 group by将表中的元组按指定列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值。hav

42、ing则对分组进行选择，只将聚集函数作用到满足条件的分组上。聚集函数平均值：avg最小值：min最大值：max总和：sum记数：count关系数据库标准语言SQL分组和聚集函数（）示例列出各系的老师的最高、最低、平均工资。 select DNO，max(SAL)，min(SAL)，avg(SAL) from PROF group by DNO列出及格的学生的平均成绩。 select SNO，avg(SCORE) from SC group by SNO having min(SCORE) = 60关系数据库标准语言SQL空值（）空值测试is not null测试指定列的值是否为空值。示例找出年

43、龄值为空的老师姓名。 select PNAME from PROF where AGE is null不可写为where AGE = null关系数据库标准语言SQL空值（）注意事项除is not null之外，空值不满足任何查找条件。如果null参与算术运算，则该算术表达式的值为null。如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown。如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。 例：select sum(SAL) from PROF 例：select count(*) from PROF关系数据库标准语言SQL集合操

44、作（）命令集合并：union集合交：intersect集合差： except示例求选修了001或002号课程的学生号。（select SNO from SC where CNO = 001）union all（select SNO from SC where CNO = 002）关系数据库标准语言SQL集合操作（）求选修了001和002号而没有选003号课程的学生号。（select SNO from SC where CNO = 001 or CNO = 002 ）except（select SNO from SC where CNO = 003）提示 集合操作自动去除重复元组，如果要保留重复

45、元组的话，必须用all关键词指明。关系数据库标准语言SQL连接查询 什么是连接查询？若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，也是查询中最难的一部分。 连接查询主要包括等值连接非等值连接查询、自然连接自身连接查询外连接查询复合条件连接查询关系数据库标准语言SQL等值与非等值连接查询 连接查询中用来连接两个表的条件称为连接条件或连接谓词。其一般格式为： . . 其中比较运算符主要有：=、=、=、!=、!= 此外连接谓词词还可以使用下面形式：. BETWEEN . AND . 当连接运算符为=号时，称为。其它称为。 连接谓词中的列名称为。连接条件中的各连

46、接字段类型必须时可比的，但不必是相同的。 关系数据库标准语言SQLDBMS执行连接操作的过程首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第2个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕为止。 关系数据库标准语言SQL例32 查询每个学生及其选修课程的情况学生情况存放在Student表中，学生选课情况存放在SC表中，所以本查询实际上同时涉及S

47、tudent与SC两个表中的数据。这两个表之间的联系是通过两个表都具有的属性Sno实现的。要查询学生及其选修课程的情况，就必须将这两个表中学号相同的元组连接起来。这是一个等值连接。完成本查询的SQL语句为： SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno; 关系数据库标准语言SQL广义笛卡儿积是不带连接谓词的连接。两个表的广义笛卡儿积即是两表中元组的交叉乘积，连接结果会产生一些没有意义的元组，所以这种运算没有什么意义。 若在等值连接中把目标列中重复的属性列去掉则为自然连接。 关系数据库标准语言SQL例33自然连接

48、Student和SC表SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SCWHERE Student.Sno=SC.Sno;在本查询中，由于Sname、 Ssex、 Sage、Sdept、Cno和Grade属性列在Student与SC表中是唯一的，因此引用时可以去掉表名前缀。而Sno在两个表都出现了，因此引用时必须加上表名前缀。该查询的执行结果不再出现SC.Sno列。关系数据库标准语言SQL自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自然连接。 例34：查询每一门

49、课程的间接先行课。 SELECT FIRST.CNO,SECOND.CPNO FROM COURSE FIRST,COURSE SECOND WHERE FIRST.CPNO=SECOND.CNO; 参见page104为清楚起见，我们可以为为清楚起见，我们可以为Course表取两个别名，表取两个别名，一个是一个是FIRST，另一个是另一个是SECOND，也可以在考也可以在考虑问题时就把虑问题时就把Course表想成是两个完全一样表，表想成是两个完全一样表，一个是一个是FIRST表，另一个是表，另一个是SECOND表。表。 结果表如下：结果表如下： Cno Pcno - - 1 7 3 5 5

50、6 关系数据库标准语言SQL结合连接查询的执行过程详细说明该语句的执行顺序。 例：对于PC表（PC,HD）分别为PC的型号和硬盘，查询在两种或两种以上PC机上出现的硬盘。 SELECT DISTINCT HD FROM PC PC1,PC PC2 WHERE PC1.PCPC2.PC AND PC1.HD=PC2.HD 关系数据库标准语言SQL外连接 什么时候需要外连接？ 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。有时不能满足我们的要求。例如以STUDENT表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，只输出其基本情况信息，其选课信息为空值即可，这就需要外连

51、接, 外连接的表示方法为 在连接谓词的某一边加符号*。外连接就好像是为符号*所在边的表增加一个“万能”的行，这个行全部由空值组成。它可以和另一边的表中所有不满足连接条件的元组进行连接。 SELECT Student.Sno,sname,ssex,sage,sdept,cno,grade FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO(*); 关系数据库标准语言SQLStudent.Sno Sname Ssex Sage Sdept Cno Grade- - - - - - - 95002 刘晨 女 19 IS 3 80 95003 王名 女 18 MA 950

52、04 张立 男 18 IS 上例中外连接符*出现在连接运算符的右边，所以也称其为右外连接。相应地，如果外连接符出现在连接运算符的左边，则称为左外连接。 关系数据库标准语言SQL复合条件连接 连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。 例35：查询每个学生的学号、姓名、选修的课程名及成绩。 SELECT Student.Sno, Sname, Course.Cname, SC.Grade FROM Student, SC, Course WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno; 关系

53、数据库标准语言SQL例35查询选修2号课程且成绩在90分以上的所有学生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90; 连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。关系数据库标准语言SQL嵌套查询 在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。例如： SELECT S

54、name FROM StudentWHERE Sno IN SELECT Sno FROM SC WHERE Cno=2;在这个例子中，下层查询块 SELECT Sno FROM SC WHERE Cno=2是嵌套在上层查询块 SELECT Sname FROM Student WHERE Sno IN 的WHERE条件中的。 关系数据库标准语言SQL说明上层的查询块又称为外层查询或父查询或主查询，下层查询块又称为内层查询或子查询。SQL语言允许多层嵌套查询。即一个子查询中还可以嵌套其它子查询。需要特别指出的是，子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句永远只

55、能对最终查询结果排序。关系数据库标准语言SQL说明嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式来构造程序正是 SQL(Structurred Query Language)中“结构化”的含义所在。关系数据库标准语言SQL分步完成查询例37 查询与“刘晨”在同一个系学习的学生查询与“刘晨”在同一个系学习的学生，可以首先确定“刘晨”所在系名，然后再查找所有在该系学习的学生。所以可以分步来完成此查询： 第步，确定“刘晨”所在系名 S

56、ELECT Sdept FROM StudentWHERE Sname=刘晨; 结果为： Sdept - IS 关系数据库标准语言SQL分步完成查询第步，查找所有在IS系学习的学生。SELECT Sno, Sname, Sdept FROM Student WHERE Sdept=IS; 结果为： Sno Sname Sdept - - - 95001 刘晨 IS 95004 张立 IS 分步写查询毕竟比较麻烦，上述查询实际上可以用子查询来实现，即将第一步查询嵌入到第二步查询中，用以构造第二步查询的条件。关系数据库标准语言SQL嵌套查询SQL语句如下： SELECT Sno, Sname, S

57、deptFROM Student WHERE Sdept IN （SELECT Sdept FROM Student WHERE Sname=刘晨）; 本例中的查询也可以用我们前面学过的表的自身连接查询来完成：SELECT Sno, Sname, Sdept FROM Student S1, Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname=刘晨; 关系数据库标准语言SQL嵌套与连接的方法对比可见，实现同一个查询可以多种方法，当然不同的方法其执行效率可能会有差别，甚至会差别很大。 例38 查询选修了课程名为信息系统的学生学号和姓名 SELECT

58、 Sno, Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname=信息系统);结果为结果为: Sno Sname - - 95001 李勇李勇 95002 刘晨刘晨 关系数据库标准语言SQL嵌套与连接的方法对比本查询同样可以用连接查询实现：SELECT Sno, SnameFROM Student, SC, Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname

59、=信息系统; 例37和例38中的各个子查询都只执行一次，其结果用于父查询，子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。不相关子查询是最简单的一类子查询。 关系数据库标准语言SQL带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用、 =、=、!=或等比较运算符。 在例37中，由于一个学生只可能在一个系学习，也就是说内查询刘晨所在系的结果是一个唯一值，因此该查询也可以用比较运算符来实现，其SQL语句如下： SELECT Sno, Sname, Sdept FROM Student WHERE Sd

60、ept = (SELECT Sdept FROM Student WHERE Sname=刘晨;) 关系数据库标准语言SQL需要注意的是，子查询一定要跟在比较符之后，下列写法是错误的：SELECT Sno, Sname, Sdept FROM Student WHERE (SELECT Sdept FROM Student WHERE Sname=刘晨) = Sdept; 关系数据库标准语言SQL例38中信息系统的课程号是唯一的，但选修该课程的学生并不只一个，所以例38也可以用=运算符和IN谓词共同完成： SELECT Sno, Sname FROM StudentWHERE Sno IN (