数  据  库  基  础
This presentation is the property of its rightful owner.
Sponsored Links
1 / 109

数 据 库 基 础 第三章 SQL 语言 PowerPoint PPT Presentation


  • 90 Views
  • Uploaded on
  • Presentation posted in: General

数 据 库 基 础 第三章 SQL 语言. 汤 娜 中山大学计算机科学系 [email protected] 3.1 SQL 语言概况 3.2 SQL 数据定义语言 3.3 SQL 数据查询语言 3.3.1 单表查询 3.3.2 连接查询 3.3.3 嵌套查询 3.3.4 select 的一般格式 3.3.5 总结. 3.1 SQL 语言概况. SQL 简介

Download Presentation

数 据 库 基 础 第三章 SQL 语言

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql

数 据 库 基 础第三章 SQL语言

汤 娜

中山大学计算机科学系

[email protected]


Sql

3.1 SQL语言概况

3.2 SQL数据定义语言

3.3 SQL数据查询语言

3.3.1 单表查询

3.3.2 连接查询

3.3.3 嵌套查询

3.3.4 select的一般格式

3.3.5 总结


3 1 sql

3.1 SQL语言概况

  • SQL简介

    • 结构化查询语言SQL(Structured Query Language)是一种介于关系代数与关系演算之间的语言,其功能包括查询、操纵、定义和控制四个方面,是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。

    • SQL语言的版本包括:SQL-89,SQL-92, SQL3。

  • SQL特点

    • SQL语言之所以能够为用户和业界所接受,成为国际标准,是因为它是一个综合的、通用的、功能极强同时又简洁易学的语言。

    • SQL语言集数据查询(data query)、数据操纵(data manipulation)、数据定义(data definition)和数据控制(data control)功能于一体,充分体现了关系数据语言的特点和优点。


3 1 sql1

3.1 SQL语言概况

  • SQL的基本概念

    • SQL语言支持关系数据库三级模式结构。其中外模式对应于视图(View),模式对应于基本表,内模式对应于存储文件。

    • 基本表是本身独立存在的表,在SQL中一个关系就对应一个表。一些基本表对应一个存储文件,一个表可以带若干索引,索引也存放在存储文件中。   

    • 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。

    • 视图是从基本表或其他视图中导出的表,它本身不独立存储在数据库中,也就是说数据库中只存放视图的定义而不存放视图对应的数据,这些数据仍存放在导出视图的基本表中,因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中,视图和基本表都是关系,而存储文件对用户是透明的。


Sql

3.1 SQL语言概况

  • SQL的基本功能

    • (1)数据定义功能

      • ● 基本表的建立、取消与更改。

      • ● 索引的建立与取消。

      • ● 视图的创建与取消。

    • (2)数据查询功能(包括数据表和视图)

    • (3)数据更新功能

      • ●数据插入、删除、修改功能。

    • (4)数据控制功能

      • ●数据库保护功能(安全性和完整性保护)。

      • ●事务管理功能(数据库故障恢复和并发事务处理)。


Sql

3.1 SQL语言概况

  • SQL的其他一些功能:

    • ●与主语言的接口:SQL提供4条游标语句(见3.7节)用于解决SQL与主语言之间因数据不匹配所引起的接口问题。

    • ●存储过程:SQL还提供远程调用功能,在远程方式下客户机中的应用可通过网络调用服务器数据库中的存储过程。存储过程是一个由SQL语句所组成的过程,该过程在被应用程序调用后就执行SQL的语句系列,最后将结果返回应用。存储过程可为多个应用所共享。


Sql

3.2 SQL数据定义语言


3 2 sql

3.2 SQL数据定义语言

  • 定义基本表

    一般格式如下:CREATE TABLE <表名>(<列名><数据类型> [列级完整性约束条件] [,<列名> <数据类型> [列级完整性约束条件]...) [,<表级完整性约束条件>];其中<表名>是所要定义的基本表的名字,它可以由一个或多个属性(列)组成。建表的同时通常还可以定义与该表有关的完整性约束条件,这些完整性约束条件被存入系统的数据字典中,当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上,否则既可以定义在列级也可以定义在表级。


3 2 sql1

3.2 SQL数据定义语言

  • 定义基本表(续)

  • 下面我们以一个“学生-课程”数据库为例说明Sql语句的各种用法。“学生-课程”数据库中包括三个表:

  • “学生”表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成,可记为: Student(Sno,Sname,Ssex,Sage,Sdept) Sno

  • “课程”表Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、老师(Teacher)四个属性组成,可记为: Course(Cno,Cname,Cpno,teacher) Cno

  • “学生选课”表SC由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成,可记为: SC(Sno,Cno,Grade) (SNO, CNO)


3 2 sql2

3.2 SQL数据定义语言

  • 定义基本表(续)

    例1: 建立一个“学生”表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成,其中学号属性不能为空,并且其值是唯一的。

    CREATE TABLE Student (

    Sno CHAR(5) NOT NULL UNIQUE,

    Sname CHAR(20),

    Ssex CHAR(1),

    Sage INT,

    Sdept CHAR(15));


3 2 sql3

3.2 SQL数据定义语言

  • 定义基本表(续)

  • 定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同,例如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的定长字符串。

    VARCHAR(n) 最大长度为n的变长字符串。

    GRAPHIC(n) 长度为n的定长图形字符串。VARGRAPHIC(n) 最大长度为n的变长图形字符串。

    DATE 日期型,格式为YYYY-MM-DD。

    TIME时间型, 格式为HH.MM.SS。

    TIMESTAMP 日期加时间。


3 2 sql4

3.2 SQL数据定义语言

  • 修改基本表

  • 一般格式为: ALTER TABLE <表名>[ADD <新列名><数据类型>[完整性约束]][DROP<完整性约束名><完整性约束名>] [MODIFY<列名> <数据类型><数据类型>];其中<表名>指定需要修改的基本表,ADD子句用于增加新列和新的完整性约束条件,DROP子句用于删除指定的完整性约束条件,MODIFY子句用于修改原有的列定义。


3 2 sql5

3.2 SQL数据定义语言

  • 修改基本表(续)

  • 例2 向Student表增加“入学时间”列,其数据类型为日期型

    ALTER TABLE Student ADD Scome DATE;

    不论基本表中原来是否已有数据,新增加的列一律为空值。

  • 例3 将年龄的数据类型改为半字长整数

    ALTER TABLE Student MODIFY Sage SMALLINT;

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

  • 例4 删除关于学号必须取唯一值的约束

    ALTER TABLE Student DROP UNIQUE(Sno);

  • 说明 :

    SQL没有提供删除属性列的语句,用户只能间接实现这一功能,即先原表中要保留的列及其内容复制到一个新表中,然后删除原表,并将新表重命名为原表名。但sql server2000提供了删除列


3 2 sql6

3.2 SQL数据定义语言

  • 删除基本表

    一般格式为:DROP TABLE<表名>

    例5 删除Student表

    DROP TABLEStudent

    基本表定义一旦删除,表中的数据、在此表上建立的索引都将自动被删除掉,而建立在此表上的视图虽仍然保留,但已无法引用。因此执行删除操作一定要格外小心。


3 2 sql7

3.2 SQL数据定义语言

  • 建立索引

  • 一般格式为:CREATE [UNIQUE] [CLUSTER] INDEX <索引名>ON <表名> (<列名>[<次序>][,<列名>[<次序>]]...);其中,<表名>指定要建索引的基本表的名字。索引可以建在该表的一列或多列上,各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序,包括ASC(升序)和DESC(降序)两种,缺省值为ASC。


3 2 sql8

3.2 SQL数据定义语言

  • 建立索引(续)

  • 例6 为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引,Couse表按课程号升序建唯一索引,Sno、Cno表按学号升序和课程号降序建唯一索引。

    CREATE UNIQUE INDEX Stusno ON Student(Sno);CREATE UNIQUE INDEX Coucno ON Couse(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

  • 删除索引

  • 一般格式

    DROP INDEX <索引名>


3 3 sql

3.3 SQL数据查询语言

  • SELECT语句

    一般格式为:SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...

    FROM <表名或视图名>[,<表名或视图名>] …

    [WHERE <条件表达式>]

    [GROUP BY <列名1>[HAVING <条件表达式>]]

    [ORDER BY <列名2> [ASC|DESC]];整个SELECT语句的含义是

    • 将FROM子句指定的基本表或视图做笛卡尔集,

    • 再根据WHERE子句的条件表达式,从中找出满足条件的元组;

    • 如果有GROUP子句,则将结果按<列名1>的值进行分组,该属性列值相等的元组为一个组,每个组产生结果表中的一条记录。如果GROUP子句带HAVING短语,则只有满足指定条件的组才予输出。

    • 再根据SELECT子句中的目标列表达式形成结果表。

    • 如果有ORDER子句,则结果表还要按<列名2>的值的升序或降序排序。

      Select语句的强大体现在where子句中。


3 3 sql 1

3.3 SQL数据查询语言(1)(单表查询)

  • 一、选择表中的若干列

    1). 查询指定列

    例1: 查询全体学生的学号与姓名

    SELECT Sno,Sname

    FROM Student;

  • 说明:<目标列表达式> 中各个列的先后顺序可以与表中的顺序不一致。也就是说,用户在查询时可以根据应用的需要改变列的显示顺序。

    例2: 查询全体学生的姓名、学号、所在系

    SELECT Sname, Sno, Sdept

    FROM Student;

  • 说明:这时结果表中的列的顺序与基表中不同,是按查询要求,先列出姓名属性,然后再列学号属性和所在系属性。


3 3 sql 11

3.3 SQL数据查询语言(1) 单表查询(续)

2). 查询全部列

例3:查询全体学生的详细记录

SELECT * FROM Student;

  • 说明:该SELECT语句实际上是无条件地把Student表的全部信息都查询出来,所以也称为全表查询,这是最简单的一种查询。 也可以不用*用列名来改变列的顺序

    3).查询经过计算的值

    SELECT子句的<目标列表达式>不仅可以是表中的属性列,也可以是有关表达式,即可以将查询出来的属性列经过一定的计算后列出结果。


3 3 sql 12

3.3 SQL数据查询语言(1) 单表查询(续)

例4:查全体学生的姓名及其出生年份

SELECT Sname, 2004-Sage

FROM Student;

  • 说明:本例中,<目标列表达式>中第二项不是通常的列名,而是一个计算表达式,是用当前的年份(假设为2004年)减去学生的年龄,这样,所得的即是学生的出生年份。输出的结果为:

    Sname 2004 -Sage

    ————————

    李勇 1976

    刘晨 1977

    王名 1978

    张立 1978

  • <目标列表达式>不仅可以是算术表达式,还可以是字符串常量、函数等


3 3 sql 13

3.3 SQL数据查询语言(1) 单表查询(续)

例5:查全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名

SELECT Sname, 'Year of Birth:', 2004 -Sage, ISLOWER(Sdept)

FROM Student

  • 结果为:

    Sname 'Year of Birth:‘ 2004-Sage ISLOWER(Sdept)

    ——————————————————————

    李勇 Year of Birth: 1976 cs

    刘晨 Year of Birth: 1977 if

    王名 Year of Birth: 1978 ma

    张立 Year of Birth: 1978 if


3 3 sql 14

3.3 SQL数据查询语言(1) 单表查询(续)

  • 用户可以通过指定别名来改变查询结果的列标题,这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例,可以如下定义列别名

    SELECT Sname NAME, 'Year of Birth:‘ BIRTH,

    2004 -Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENT

    FROM Student;

  • 结果为:

    NAME BIRTH BIRTHDAY DEPARTMENT

    ——————————————————————

    李勇 Year of Birth: 1976 cs

    刘晨 Year of Birth: 1977 if

    王名 Year of Birth: 1978 ma

    张立 Year of Birth: 1978 if


3 3 sql 15

3.3 SQL数据查询语言(1) 单表查询(续)

二、选择表中的若干元组

1). 消除取值重复的行

例6: 查所有选修过课的学生的学号

SELECT Sno

FROM SC;

假设SC表中有下列数据

Sno      Cno    Grade------- ------- ------- 95001      1       92 95001      2       85 95001      3       88 95002      2       90 95002      3       80


3 3 sql 16

3.3 SQL数据查询语言(1) 单表查询(续)

例6: (续)执行上面的SELECT语句后,结果为:Sno -------  95001  95001 95001  95002  95002 该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行,必须指定DISTINCT短语:

SELECT DISTINCT Sno FROM SC; 执行结果为:  Sno -------  95001   95002


3 3 sql 17

3.3 SQL数据查询语言(1) 单表查询(续)

 2).查询满足条件的元组

查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。

表3-3 常用的查询条件

查询条件 谓 词

------------------------------------------------------------------------------

比较 上述比较运算符

确定范围 BETWEEN AND, NOT BETWEEN AND

确定集合 IN, NOT IN

字符匹配 LIKE, NOT LIKE

空值 IS NULL, IS NOT NULL

多重条件 NOT,AND, OR


3 3 sql 18

3.3 SQL数据查询语言(1) 单表查询(续)

  • (a) 比较

    例7: 查计算机系全体学生的名单

    SELECT Sname FROM Student

    WHERE Sdept = 'CS';

    例8: 查所有年龄在20岁以下的学生姓名及其年龄

    SELECT Sname, Sage FROM Student WHERE Sage <20; 或 SELECT Sname, SageFROM Student WHERE NOT Sage>= 20;


3 3 sql 19

3.3 SQL数据查询语言(1) 单表查询(续)

例9:查考试成绩有不及格的学生的学号

SELECT DISTINCT Sno FROM SC WHERE Grade <60; 这里使用了DISTINCT短语,当一个学生有多门课程不及格,他的学号也只列一次。


3 3 sql 110

3.3 SQL数据查询语言(1) 单表查询(续)

(b) 确定范围

例10: 查询年龄在20至23岁之间的学生的姓名、系别、和年龄

SELECT Sname, Sdept, SageFROM Student WHERE Sage BETWEEN 20 AND 23;

  • 与BETWEEN...AND...相对的谓词是NOT BETWEEN...AND...。

    例11:查询年龄不在20至23岁之间的学生姓名、系别和年龄。

    SELECT Sname, Sdept, Sage FROM StudentWHERE Sage NOT BETWEEN 20 AND 23;


3 3 sql 111

3.3 SQL数据查询语言(1) 单表查询(续)

(c) 确定集合

例12: 查信息系(IS)、数学系(MA)和计算机科学系(CS)的学生的姓名和性别

SELECT Sname, Ssex FROM Student WHERE Sdept IN ('IS', 'MA', 'CS') 与IN相对的谓词是NOT IN,用于查找属性值不属于指定集合的元组。

例13:查既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别

SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('IS', 'MA', 'CS')


3 3 sql 112

3.3 SQL数据查询语言(1) 单表查询(续)

(d) 字符匹配

谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下: [NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']  其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串,也可以含有通配符%和_。%(百分号) 代表任意长度(长度可以为0)的字符串。_(下横线) 代表任意单个字符。

例14: 查所有姓刘的学生的姓名、学号和性别

SELECT Sname, Sno, SsexFROM Student WHERE Sname LIKE '刘%';


3 3 sql 113

3.3 SQL数据查询语言(1) 单表查询(续)

  • 例15: 查姓“欧阳”且全名为三个汉字的学生的姓名

    SELECT Sname FROM Student WHERE Sname LIKE ‘欧阳_ _'; 注意,由于一个汉字占两个字符的位置,所以匹配串欧阳后面需要跟2个_。

    例16: 查名字中第二字为“阳”字的学生的姓名和学号

    SELECT Sname, Sno FROM Student WHERE Sname LIKE '__阳%';

    例17: 查所不姓刘的学生姓名

    SELECT Sname, Sno, Ssex

    FROM Student

    WHERE Sname NOT LIKE ‘刘%’;


3 3 sql 114

3.3 SQL数据查询语言(1) 单表查询(续)

  • 如果用户要查询的匹配字符串本身就含有%或_,比如要查名字DB_Design的课程的学分,应如何实现呢?这时就要使用ESCAPE ‘ ’短语对通配符进行转义了。

    例18: 查DB_Design课程的课程号和任课老师

    SELECT Cno, teacher

    FROM Course

    WHERE Cname LIKE ’DB\_Design’ ESCAPE ’\’

  • 说明:ESCAPE ’\’短语表示\为换码字符,这样匹配串中紧跟在\后面的字符”_”不再具有通配符的含义,而是取其本身含义,被转义为普通的”_”字符。


3 3 sql 115

3.3 SQL数据查询语言(1) 单表查询(续)

例19:查以”DB_”开头,且倒数第三个字符为i的课程的详细情况

SELECT *

FROM Course

WHERE Cname LIKE ’DB\_%i_ _ ’ ESCAPE ’\’;

  • 说明:注意这里的匹配字符串’DB\_%i_ _ _’。第一个_前面有换码字符\,所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符\,所以它们仍作为通配符。其执行结果为:

    Cno Cname teacher

    ------------------------------------------

    8 DB_Design 王诚

    10 DB_Programing 何名名

    13  DB_DBMS Design 李桑


3 3 sql 116

3.3 SQL数据查询语言(1) 单表查询(续)

(e) 涉及空值的查询

例20:某些学生选修某门课程后没有参加考试,所以有选课记录,但没有考试成绩,下面我们来查一下缺少成绩的学生的学号和相应的课程号

SELECT Sno, Cno FROM SCWHERE Grade IS NULL; 注意这里的'IS'不能用等号('=') 代替。

例21:查所有有成绩的记录的学生学号和课程号

SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL;


3 3 sql 117

3.3 SQL数据查询语言(1) 单表查询(续)

(f)多重条件查询

  • 逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中,则AND的优先级高于OR,但用户可以用括号改变优先级。

    例22:查CS系年龄在20岁以下的学生姓名

    SELECT Sname FROM Student WHERE Sdept='CS' AND Sage<20; 例12 中的IN谓词实际上是多个OR运算符的缩写,因此例12中的查询也可以用OR运算符写成如下等价形式:

    SELECT Sname, Ssex FROM Student WHERE Sdept='IS' OR Sdept='MA' OR Sdept='CS';


3 3 sql 118

3.3 SQL数据查询语言(1) 单表查询(续)

(3). 对查询结果排序

  • 如果没有指定查询结果的显示顺序,DBMS将按其最方便的顺序(通常是元组在表中的先后顺序)输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序(ASC)或降序(DESC)重新排列查询结果,其中升序ASC为缺省值。

    例23:查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列

    SELECT Sno, Grade

    FROM SC

    WHERE Cno=‘3'

    ORDER BY Grade DESC;

  • 前面已经提到,可能有些学生选修了3号课程后没有参加考试,即成绩列为空值。用ORDER BY子句对查询结果按成绩排序时,若按升序排,成绩为空值的元组将最后显示,若按降序排,成绩为空值的元组将最先显示。


3 3 sql 119

3.3 SQL数据查询语言(1) 单表查询(续)

例24: 查询全体学生情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列

SELECT * FROM Student ORDER BY Sdept, Sage DESC;


3 3 sql 120

3.3 SQL数据查询语言(1) 单表查询(续)

(4) 使用集函数

  • 为了进一步方便用户,增强检索功能,SQL提供了许多集函数,主要包括:


Sql

  • 如何处理null

    • null是一个常量,仅在数值和字符串类型的列中有意义,代表的是没有意义或者是不确定的值。例如,学生选了课程,当成绩没有出来时grade字段的值应该为空;或者工资表中一个行政人员在课时补贴一栏的值为null,因为它不可能有课时补贴的收入


Sql

  • 例一

    select * from sc where grade <60 or grade>=60

    (不会选取所有记录,那些grade的值不会选取出来)

  • 例二:有两个关系R,S如下图

    select * from R,S where R.B=S.D的结果如下图


Sql

  • 对于集合运算

  • COUNT(列名) NULL值不会计算进去

  • SUM 不影响

  • AVG=SUM/COUNT 所以NULL值不会计算进去

  • MAX

  • MIN

  • 思考题:sql server 中的min函数是否会考虑null值? Example

    • select count(*) from customers; 5 / 6

    • select count(cid) from customers; /* null values not counted * / 5 / 6

    • select count(distinct city) from customers; 3 / 3

    • select count(city) from customers 5 / 5


3 3 sql 121

3.3 SQL数据查询语言(1) 单表查询(续)

(4) 使用集函数

  • 如果指定DISTINCT短语,则表示在计算时先要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL为缺省值),则表示不取消重复值。

  • 只能用于SELECT子句和HAVING子句中

    例25:查询学生总人数

    SELECT COUNT(*) FROM Student;

    例26: 查询选修了课程的学生人数

    SELECT COUNT(DISTINCT Sno)

    FROM SC;

  • 说明:学生每选修一门课,在SC中都有一条相应的记录,而一个学生一般都要选修多门课程,为避免重复计算学生人数,必须在COUNT函数中用DISTINCT短语。


3 3 sql 122

3.3 SQL数据查询语言(1) 单表查询(续)

  • (4) 使用集函数

    例27:计算1号课程的学生平均成绩

    SELECT AVG(Grade) FROM SC

    WHERE Cno='1';

    例28:查询学习1号课程的学生最高分数

    SELECT MAX(Grade)FROM SC

    WHERE Cno='1';

    注意:集合函数只能用在select子句和having子句中

    SELECT sno FROM sc WHERE sc.grade = max(sc.grade ); 语法错误,应写为:

    SELECT sno FROM sc WHERE sc.grade = (select max(sc.grade ) from sc );


3 3 sql 123

3.3 SQL数据查询语言(1) 单表查询(续)

(5) 对查询结果分组

  • GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。 对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组,集函数将作用于整个查询结果,即整个查询结果只有一个函数值。否则,集函数将作用于每一个组,即每一组都有一个函数值。

    例29:查询各个课程号与相应的选课人数

    SELECT Cno, COUNT(Sno)

    FROM SC

    GROUP BY Cno;

  • 该SELECT语句对SC表按Cno的取值进行分组,所有具有相同Cno值的元组为一组,然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为:

    CnoCOUNT(Sno) ------ ----------   1       22   2       34   3       44   4       33   5       48


3 3 sql 124

3.3 SQL数据查询语言(1) 单表查询(续)

  • (5) 对查询结果分组

  • 如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用HAVING短语指定筛选条件。

    例30:查询选修了3门以上课程的学生的学号

    SELECT Sno

    FROM SC

    GROUP BY Sno

    HAVING COUNT(*)>3;

  • 说明:查选修课程超过3门的信息系学生的学号,首先基本表中的学生。然后求其中每个学生选修了几门课,为此需要用GROUP BY子句按Sno进行分组,再用集函数COUNT对每一组计数。如果某一组的元组数目大于3,则表示此学生选修的课超过3门,应将他的学生号选出来。HAVING短语指定选择组的条件,只有满足条件(即元组个数>3)的组才会被选出来。   WHERE子句与HAVING短语的根本区别在于作用对象不同。WHERE子句作用于基本表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。

  • 思考题,请找出总分超过600分的学生


Sql

  • 注意:select子句中如果有集合函数,则不允许出现包含其他的字段的表达式,但如果有groupby 子句,则允许group by 子句出现的字段。

  • 如果在查询语句中有group by 子句,则select子句则不允许出现包含其他的字段的表达式, 允许group by 子句出现的字段和集合函数表达式。

  • 例1 select sno,sum(grade)

    from sc /*not valid*/

  • 例2 select sno,cno,sum(grade)

    from sc group by sno /*not valid*/

  • 例3 select sno,sum(grade)

    from sc group by sno /*valid*/


3 3 sql 2

3.3 SQL数据查询语言(2) 连接查询

连接查询

  • 一个数据库中的多个表之间一般都存在某种内在联系,它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表,则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接查询(连接 二、自身连接)、外连接查询(连接 三、外连接)和复合条件连接查询(连接 四、复合条件连接)。

    (1) 等值与非等值连接查询

  • 用来连接两个表的条件称为连接条件或连接谓词,其一般格式为:[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 其中比较运算符主要有:=、>、<、>=、<=、!=<=、!=


3 3 sql 21

3.3 SQL数据查询语言(2) 连接查询

例32:查询每个学生及其选修课程的情况

SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno

  • 连接运算中有两种特殊情况,一种称为卡氏积连接,另一种称为自然连接。卡氏积是不带连接谓词的连接。两个表的卡氏积即是两表中元组的交叉乘积,也即其中一表中的每一元组都要与另一表中的每一元组作拼接,因此结果表往往很大。

  • 如果是按照两个表中的相同属性进行等值连接,且目标列中去掉了重复的属性列,但保留了所有不重复的属性列,则称之为自然连接。

    例33:自然连接Student和SC表

    SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade

    FROM Student, SC

    WHERE Student.Sno=SC.Sno;


3 3 sql 22

3.3 SQL数据查询语言(2) 连接查询

(2) 自连接

  • 连接操作不仅可以在两个表之间进行,也可以是一个表与其自己进行连接,这种连接称为表的自连接。

    例34:查询每一门课的先修课课名

    完成该查询的SQL语句为: SELECT FIRST.Cno, FIRST.Pcno , SECOND. Cname FROM Course FIRST, Course SECONDWHERE FIRST.Pcno=SECOND.Cno;

    (3) 外连接

  • 在通常的连接操作中,只有满足连接条件的元组才能作为结果输出,如在例32和例33的结果表中没有关于95003和95004两个学生的信息,原因在于他们没有选课,在SC表中没有相应的元组。但是有时我们想以Student表为主体列出每个学生的基本情况及其选课情况,若某个学生没有选课,则只输出其基本情况信息,其选课信息为空值即可,这时就需要使用外连接(Outer Join)。这样,我们就可以如下改写例33:


3 3 sql 23

3.3 SQL数据查询语言(2) 连接查询

  • SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student JOIN SC ON Student.Sno=SC.Sno

    可以有inner join;outer join;left outer join;right outer join

    (4) 复合条件连接

  • 上面各个连接查询中,WHERE子句中只有一个条件,即用于连接两个表的谓词。WHERE子句中有多个条件的连接操作,称为复合条件连接。

    例35:查询选修2号课程且成绩在90分以上的所有学生

    SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;

    SELECT Student.Sno, Sname FROM Student inner join SC on Student.Sno=SC.Sno WHERE SC.Cno='2' AND SC.Grade>90;


3 3 sql 24

3.3 SQL数据查询语言(2) 连接查询

  • 连接操作除了可以是两表连接,一个表与其自身连接外,还可以是两个以上的表进行连接,后者通常称为多表连接。

    例36:查询每个学生及其选修的课程名其及成绩

    SELECT Student.Sno, Sname, Course.Cname, SC.Grade FROM Student, SC, Course WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno;


3 3 sql 3

3.3 SQL数据查询语言(3) 嵌套查询

  • 嵌套查询概述

    • 一个SELECT-FROM-WHERE语句称为一个查询块

    • 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

    • 基本原理:

      基本特征是一个查询语句中WHERE子句的逻辑条件含有另一个查询语句。但查询语句的结果是一张表,表就是元组的集合,因而可以将WHERE子句内的查询语句看作是一个集合。在这种观点之下,WHERE子句所涉及到的逻辑条件就可以转化为“元素x与集合S”或者“集合S1与集合S2”之间的关系表示。


Sql

嵌套查询(续)

  • 子查询的限制

    • 不能使用ORDER BY子句

    • 外层select语句的变量可以用在子查询中,但反之则不行

  • 嵌套查询分类

    • 不相关子查询

      子查询的查询条件不依赖于父查询

    • 相关子查询

      子查询的查询条件依赖于父查询


Sql

不相关子查询的范例:

SELECT s1.Sno,s1.Sname,s1.Sdept

FROM Student s1

WHERE s1.Sdept IN

(SELECT s2.Sdept

FROM Student s2

WHERE s2.Sname= ‘ 刘晨 ’);

相关子查询的范例:

查询选修了c02课程的学生姓名

select sname from student where ‘c02’ in

(select cno from sc where sc.sno=student.sno)


Sql

嵌套查询求解方法

  • 不相关子查询

    是由里向外逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。

  • 相关子查询

    • 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表;

    • 然后再取外层表的下一个元组;

    • 重复这一过程,直至外层表全部检查完为止。


Sql

引出子查询的谓词

  • 带有IN谓词的子查询

  • 带有比较运算符的子查询

  • 带有ANY或ALL谓词的子查询

  • 带有EXISTS谓词的子查询


Sql

一、带有IN谓词的子查询

格式:expr [ NOT ] IN (subquery) | expr [ NOT ] IN (val { ,val… })

[例37] 查询与“刘晨”在同一个系学习的学生。

此查询要求可以分步来完成

① 确定“刘晨”所在系名

SELECT Sdept

FROM Student

WHERE Sname= ' 刘晨 ';

结果为: Sdept

IS


Sql

带有IN谓词的子查询(续)

② 查找所有在IS系学习的学生。

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept= ' IS ';

结果为:

Sno Sname Sdept

95001 刘晨 IS

95004 张立 IS


Sql

构造嵌套查询

将第一步查询嵌入到第二步查询的条件中

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname= ‘ 刘晨 ’);

此查询为不相关子查询。DBMS求解该查询时也是分步去做的。


Sql

带有IN谓词的子查询(续)

用自身连接完成本查询要求

SELECT S1.Sno,S1.Sname,S1.Sdept

FROM Student S1,Student S2

WHERE S2.Sname = '刘晨' AND S1.Sdept = S2.Sdept ;


Sql

带有IN谓词的子查询(续)

父查询和子查询中的表均可以定义别名

SELECT Sno,Sname,Sdept

FROM Student S1

WHERE S1.Sdept IN

(SELECT Sdept

FROM Student S2

WHERE S2.Sname= ‘ 刘晨 ’);


Sql

带有IN谓词的子查询(续)

[例38]查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname ③ 最后在Student关系中

FROM Student 取出Sno和Sname

WHERE Sno IN

(SELECT Sno ② 然后在SC关系中找出选

FROM SC 修了3号课程的学生学号

WHERE Cno IN

(SELECT Cno ① 首先在Course关系中找出“信

FROM Course 息系统”的课程号,结果为3号

WHERE Cname= ‘信息系统’));


Sql

带有IN谓词的子查询(续)

  • 用连接查询实现上例

    SELECT Sno,Sname

    FROM Student,SC,Course

    WHERE Student.Sno = SC.Sno AND

    SC.Cno = Course.Cno AND

    Course.Cname=‘信息系统’;


Sql

  • 不相关子查询总结

    • 当查询涉及到多个表时,使用嵌套结构逐次求解,就可以将复杂的问题转化为多个相对简单的查询,使得语句层次分明,层层嵌套方式反映了 SQL语言的结构化


Sql

  • 相关子查询

    • 查询选修了c02课程的学生姓名,

      select sname from student where ‘c02’ in

      (select cno from sc where sc.sno=student.sno)

    • 注意:外层select语句的变量可以用在子查询中,但反之则不行

      查询选修了c02课程的学生姓名,并且成绩为A的

      select sname from student where sc.grade=‘A’ AND ‘c02’ in

      (select cno from sc where sc.sno=student.sno)

      /*not valid*/


Sql

二、带有比较运算符的子查询

  • 当能确切知道内层查询返回单值时,可用比较运算符(>,<,=,>=,<=,!=或< >)。与ANY或ALL谓词配合使用

  • 格式:expr  { SOME| ANY | ALL } (subquery)  is some operator in the set { <,<=,=,<>,>,>=}


Definition of some clause

0

5

6

Definition of Some Clause

(5< some

) = true

(read: 5 < some tuple in the relation)

0

) = false

(5< some

5

0

) = true

(5 = some

5

0

(5 some

) = true (since 0  5)

5

(= some)  in

However, ( some)  not in


Definition of all clause

0

5

6

Definition of all Clause

  • F <comp> all r t r (F <comp> t)

(5< all

) = false

6

) = true

(5< all

10

4

) = false

(5 = all

5

4

(5 all

) = true (since 5  4 and 5  6)

6

(all)  not in

However, (= all)  in


Sql

带有比较运算符的子查询(续)

例:假设一个学生只可能在一个系学习,并且必须属于一个系,则在[例37]可以用 = 代替IN:

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept =(

SELECT Sdept

FROM Student

WHERE Sname= ‘ 刘晨 ’);


Sql

带有比较运算符的子查询(续)

子查询一定要跟在比较符之后

错误的例子:

SELECT Sno,Sname,Sdept

FROM Student

WHERE ( SELECT Sdept

FROM Student

WHERE Sname= ‘ 刘晨 ’ )

= Sdept;


Any all

三、带有ANY或ALL谓词的子查询

谓词语义

  • SOME/ANY:任意一个值

  • ALL:所有值


Any all1

带有ANY或ALL谓词的子查询(续)

需要配合使用比较运算符

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

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

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

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

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

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

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

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

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

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

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

!=(或<>)ALL不等于子查询结果中的任何一个值


Any all2

带有ANY或ALL谓词的子查询(续)

[例39] 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

SELECT Sname,Sage

FROM Student

WHERE Sage < ANY (SELECT Sage

FROM Student

WHERE Sdept= ' IS ')

AND Sdept <> ' IS ' ;

/* 注意这是父查询块中的条件 */


Any all3

带有ANY或ALL谓词的子查询(续)

结果

Sname Sage

王敏 18

执行过程

1.DBMS执行此查询时,首先处理子查询,找出

IS系中所有学生的年龄,构成一个集合(19,18)

2. 处理父查询,找所有不是IS系且年龄小于

19 或 18的学生


Any all4

=

<>或!=

<

<=

>

>=

ANY/SOME

IN

--

<MAX

<=MAX

>MIN

>= MIN

ALL

--

NOT IN

<MIN

<= MIN

>MAX

>= MAX

带有ANY或ALL谓词的子查询(续)

  • ANY和ALL谓词有时可以用集函数实现

    • ANY与ALL与集函数的对应关系

    • 用集函数实现子查询通常比直接用ANY或ALL查询效率要高,因为前者通常能够减少比较次数


Any all5

带有ANY或ALL谓词的子查询(续)

[例39']:用集函数实现[例39]

SELECT Sname,Sage

FROM Student

WHERE Sage <

(SELECT MAX(Sage)

FROM Student

WHERE Sdept= ' IS ')

AND Sdept <> ' IS ’;


Any all6

带有ANY或ALL谓词的子查询(续)

[例40] 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。

方法一:用ALL谓词

SELECT Sname,Sage

FROM Student

WHERE Sage < ALL

(SELECT Sage

FROM Student

WHERE Sdept= ' IS ')

AND Sdept <> ' IS ’;

查询结果为空表。


Any all7

带有ANY或ALL谓词的子查询(续)

方法二:用集函数

SELECT Sname,Sage

FROM Student

WHERE Sage <

(SELECT MIN(Sage)

FROM Student

WHERE Sdept= ' IS ')

AND Sdept <>' IS ’;


Exists

四、带有EXISTS谓词的子查询

1. EXISTS谓词

2. NOT EXISTS谓词

3. 不同形式的查询间的替换

4.用EXISTS/NOT EXISTS实现全称量词

(除法)

5. 用EXISTS/NOT EXISTS实现逻辑蕴函

6. 用EXISTS/NOT EXISTS实现差


Exists1

带有EXISTS谓词的子查询(续)

  • 1. EXISTS谓词

    • 存在量词

    • 格式:exists(subsquery)

    • 带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。

      • 若内层查询结果非空,则返回真值

      • 若内层查询结果为空,则返回假值

    • 由EXISTS引出的子查询,其目标列表达式通常都用* ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义

  • 2. NOT EXISTS谓词


Exists2

带有EXISTS谓词的子查询(续)

[例41] 查询所有选修了1号课程的学生姓名。

思路分析:

  • 本查询涉及Student和SC关系。

  • 在Student中依次取每个元组的Sno值,用此值去检查SC关系。

  • 若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其Cno= '1',则取此Student.Sname送入结果关系。


Exists3

带有EXISTS谓词的子查询(续)

  • 用嵌套查询

    SELECT Sname

    FROM Student

    WHERE EXISTS

    (SELECT *

    FROM SC /*相关子查询*/

    WHERE Sno=Student.Sno AND Cno= ' 1 ');

    求解过程


Exists4

带有EXISTS谓词的子查询(续)

  • 用连接运算

    SELECT Sname

    FROM Student, SC

    WHERE Student.Sno=SC.Sno AND

    SC.Cno= '1';


Exists5

带有EXISTS谓词的子查询(续)

[例42] 查询没有选修1号课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno = Student.Sno AND Cno='1');

此例用连接运算难于实现


Exists6

带有EXISTS谓词的子查询(续)

3. 不同形式的查询间的替换

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换

所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。


Exists7

带有EXISTS谓词的子查询(续)

例:[例37]查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换:

SELECT Sno,Sname,Sdept

FROM Student S1

WHERE EXISTS

(SELECT *

FROM Student S2

WHERE S2.Sdept = S1.Sdept AND

S2.Sname = ' 刘晨 ‘);


Exists8

带有EXISTS谓词的子查询(续)

4.用EXISTS/NOT EXISTS实现全称量词(难点)

  • SQL语言中没有全称量词 (For all)

  • 对应关系代数中的除法

  • 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:

    (x)P ≡ ( x( P))


Exists9

带有EXISTS谓词的子查询(续)

[例43] 查询选修了全部课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM Course

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno= Student.Sno

AND Cno= Course.Cno);


Exists10

带有EXISTS谓词的子查询(续)

5. 用EXISTS/NOT EXISTS实现逻辑蕴函(难点)

  • SQL语言中没有蕴函(Implication)逻辑运算

  • 可以利用谓词演算将逻辑蕴函谓词等价转换为:

    p  q ≡  p∨q


Exists11

带有EXISTS谓词的子查询(续)

[例44] 查询至少选修了学生95002选修的全部课程的学生号码。

解题思路:

  • 用逻辑蕴函表达:查询学号为x的学生,对所有的课程y,只要95002学生选修了课程y,则x也选修了y。

  • 形式化表示:

    用P表示谓词 “学生95002选修了课程y”

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

    则上述查询为: (y) p  q


Exists12

带有EXISTS谓词的子查询(续)

  • 等价变换:

    (y)p  q ≡  (y ((p  q ))

    ≡  (y (( p∨ q)

    ≡ y(p∧q)

  • 变换后语义:不存在这样的课程y,学生95002选修了y,而学生x没有选。


Exists13

带有EXISTS谓词的子查询(续)

  • 用NOT EXISTS谓词表示:

    SELECT DISTINCT Sno

    FROM SC SCX

    WHERE NOT EXISTS

    (SELECT *

    FROM SC SCY

    WHERE SCY.Sno = ' 95002 ' AND

    NOT EXISTS

    (SELECT *

    FROM SC SCZ

    WHERE SCZ.Sno=SCX.Sno AND

    SCZ.Cno=SCY.Cno));


Exists14

带有EXISTS谓词的子查询(续)

  • 6.实现差运算

    (1)检索不学C2课的学生学号

    select sno

    from sc sc1

    where not exist

    (select *

    from sc sc2

    where sc2.sno=sc1.sno and sc2.cno=‘c2’)

    思考题: 检索不学C2课的学生姓名


Sql

空值以及空集的讨论

  • expr IN (subquery)

  • expr  { SOME| ANY} (subquery)

  • expr  {ALL } (subquery)

    • expr  all (subquery) is false if and only if the comparison if False for at least one value returned by the subquery

  • EXIST (subquery)

    • exists(subquery) is true iff subquery is a non-empty set.

    • not exists(subquery) is true iff subquery is an empty set


Sql

select grade from sc sc1 where grade >=all

(select grade from sc sc2 where sc1.sno <> sc2.sno); 整张表只有一条记录时


Set operations

Set Operations

  • The set operations union, intersect, and except operate on relations and correspond to the relational algebra operations 

    • 格式:subquery {UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]} subquery

      • 例子

      • Each of the above operations automatically eliminates duplicates; to retain all duplicates use the orresponding multiset versions union all, intersect all and except all.


Sql

  • Suppose a tuple occurs m times in r and n times in s, then, it occurs:

    • m + n times in r union all s

    • min(m,n) times in rintersect all s

    • max(0, m – n) times in rexcept all s

  • r表中值A出现5次,S表中值A出现3次

    • union 只出现一次 union all 出现8次

    • intersect只出现一次 intersect all出现3次

    • except只出现一次except all 出现2次 r-s

      except 不出现except all不出现 s-r


  • Derived relations

    Derived Relations(派生关系)

    • Find the average account balance of those branches where the average account balance is greater than $1200.

      select branch-name, avg-balancefrom (select branch-name, avg (balance)from accountgroup by branch-name)as result (branch-name, avg-balance)where avg-balance > 1200

      Note that we do not need to use the having clause, since we compute the temporary (view) relation result in the from clause, and the attributes of result can be used directly in the where clause.


    Joined relations

    Join Types

    Join Conditions

    inner join

    left outer join

    right outer join

    full outer join

    natural

    on <predicate>

    using (A1, A2, ..., An)

    Joined Relations

    • Join operations take two relations and return as a result another relation.

    • These additional operations are typically used as subquery expressions in the from clause

    • Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join.

    • Join type – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated.


    Joined relations datasets for examples

    loan-number

    branch-name

    amount

    L-170

    L-230

    L-260

    Downtown

    Redwood

    Perryridge

    3000

    4000

    1700

    customer-name

    loan-number

    Jones

    Smith

    Hayes

    L-170

    L-230

    L-155

    Joined Relations – Datasets for Examples

    • Relation loan

    • Relation borrower

    • Note: borrower information missing for L-260 and loan information missing for L-155


    Joined relations examples

    loan-number

    branch-name

    amount

    customer-name

    loan-number

    L-170

    L-230

    Downtown

    Redwood

    3000

    4000

    Jones

    Smith

    L-170

    L-230

    loan-number

    branch-name

    amount

    customer-name

    loan-number

    L-170

    L-230

    L-260

    Downtown

    Redwood

    Perryridge

    3000

    4000

    1700

    Jones

    Smith

    null

    L-170

    L-230

    null

    Joined Relations – Examples

    • loan inner join borrower onloan.loan-number = borrower.loan-number

    • loan left outer join borrower onloan.loan-number = borrower.loan-number


    Joined relations examples1

    loan-number

    branch-name

    amount

    customer-name

    L-170

    L-230

    Downtown

    Redwood

    3000

    4000

    Jones

    Smith

    loan-number

    branch-name

    amount

    customer-name

    L-170

    L-230

    L-155

    Downtown

    Redwood

    null

    3000

    4000

    null

    Jones

    Smith

    Hayes

    Joined Relations – Examples

    • loan natural inner joinborrower

    • loan natural right outer join borrower


    Joined relations examples2

    loan-number

    branch-name

    amount

    customer-name

    L-170

    L-230

    L-260

    L-155

    Downtown

    Redwood

    Perryridge

    null

    3000

    4000

    1700

    null

    Jones

    Smith

    null

    Hayes

    Joined Relations – Examples

    • loan full outer join borrower using (loan-number)

    • Find all customers who have either an account or a loan (but not both) at the bank.

    select customer-namefrom (depositor natural full outer join borrower)where account-number is null or loan-number is null


    1 select

    总结1: SELECT语句的一般格式

    Subquery::= SELECT [ALL|DISTINCT] <目标列表达式> as [别名] [ ,<目标列表达式> as [别名]] …

    FROM <tablesource>

    [WHERE<条件表达式>]

    [GROUP BY <列名1>[,<列名1’>] ...

    [HAVING <条件表达式>]]

    | subquery [UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]] subquery

    Select statement::=

    subquery [ ORDER BY (result-colname [ASC|DESC] {, result- colname [ASC|DESC] . . .})];


    Sql

    总结2

    • 1.八种运算的sql语句的表示

      • 已知R的属性有A1,A2,……,An 和B1,B2,……,Bm;

      • 关系S的属性有B1,B2,……,Bm 和C1,C2,……,C

      • a. Projection

        • 关系代数的表示:

        • SQL语句的表示:SELECT A1,A2,……,An FROM R

      • b. Selection

        • 关系代数的表示:C(R)

        • SQL语句的表示: SELECT * FROM R WHERE C

      • c. Production

        • 关系代数的表示:RS

        • SQL语句的表示: SELECT * FROM R ,S

        • 等价的写法:

        • SELECT A1,A2,……,An, R.B1, R. B2,……, R. Bm, S.B1, S. B2,……, S. Bm, C1,C2,……,C

        • FROM R ,S


    Sql

    • d. Join

      • 关系代数的表示:R  S

      • SQL语句的表示:

      • SELECT A1,A2,……,An, R.B1, R. B2,……, R. Bm, C1,C2,……,C

      • FROM R ,S

      • WHERE R.B1= S.B1 AND R. B2= S. B2 AND …… AND R. Bm =S. Bm

    • 已知R的属性有A1,A2,……,Am;关系S的属性有A1,A2,……,Am

    • e. Union

      • 关系代数的表示:R S

      • SQL语句的表示:SELECET * FROM R

      • UNION

      • SELECT * FROM S

    • f. difference

      • 关系代数的表示:R-S

      • SQL语句的表示:

      • SELECT * FROM R

      • WHERE NOT EXIST (SELECT * FROM S

      • WHERE R.A1= S.A1 AND R. A2= S. A2 AND …… AND R. Am =S. Am)


    Sql

    • 如果:R[A]-S[A]

      • SQL语句的表示:SELECT * FROM R

      • WHERE A NOT IN (SELECT A FROM S )

      • 或者

      • select * from R where A<>all ( select A from S )

      • g. Intersection

      • 关系代数的表示: R  S

      • SQL语句的表示:

      • (1)SELECT * FROM R

      • WHERE EXIST (SELECT * FROM S

      • WHERE R.A1= S.A1 AND R. A2= S. A2 AND …… AND R. Am =S. Am)

      • (2) SELECT R.* FROM R, S

      • WHERE R.A1= S.A1 AND R. A2= S. A2 AND …… AND R. Am =S. Am

      • 如果是单列

      • SELECT * FROM R

      • WHERE COL IN (SELECT COL FROM S )

      • 或者

      • SELECT * FROM R

      • WHERE COL =SOME (SELECT COL FROM S )


    Sql

    • 2.三种子查询的关系

      • 三种形式:IN, , EXIST

      • a.2种形式expr in() ; expr not in()

      • b.12种形式

      • expr >some/any() ; expr >=some/any() ; expr =some/any() ;

      • expr <some/any() ; expr < =some/any() ; expr <>some/any() ;

      • expr >all() ; expr >=all() ; expr =all() ;

      • expr <all() ; expr < =all() ; expr <>all() ;

      • c. 2种形式 Exist () ;not Exist ()

      • 等效关系:

      • a. expr in() 等价于 expr =some/any()

      • b. expr not in() 等价于expr <>all()

      • c. select * from R where col = some/any( select col from S )等价的exist语句为:

      • select * from R where exist (select * from s where R.col =S.col)


    Sql

    • d. Select * from R where col > (>=,<,<=,<>) some/any( select col from S )等价的exist语句为:

    • select * from R where exist (select * from R.col >S.col)

    • e. select * from R where col <>all ( select col from S )等价的exist语句为:

    • select * from R where not exist (select * from R.col =S.col)

    • f. select * from R where col >(>=,<,<=,=)all ( select col from S )等价的exist语句为:

    • select * from R where not exist (select * from R.col <= S.col)


  • Login