西华师范大学计算机学院
Download
1 / 98

西华师范大学计算机学院 - PowerPoint PPT Presentation


  • 58 Views
  • Uploaded on

西华师范大学计算机学院. 第三章 关系数据库标准语言 SQL. 第三章 关系数据库标准语言 SQL. 3.1 SQL 概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.7 嵌入式 SQL 3.8 小结. SQL 的产生与发展.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' 西华师范大学计算机学院' - gavrilla-rios


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

3.1 SQL概述

3.2 数据定义

3.3 查询

3.4 数据更新

3.5 视图

3.6 数据控制

3.7 嵌入式SQL

3.8 小结


SQL的产生与发展

  • 1970年,美国IBM研究中心的E.F.Codd连续发表多篇论文,提出关系模型。1972年,IBM公司开始研制实验型关系数据库管理系统SYSTEM R,配制的查询语言称为SQUARE (Specifying Queries As Relational Expression )语言,在语言中使用了较多的数学符号。1974年,Boyce和Chamberlin把SQUARE修改为SEQUEL (Structured English QUEry Language )语言。后来SEQUEL简称为SQL (Structured Query Language ),即“结构式查询语言”,SQL的发音仍为“sequel”。

  • 1986年10月美国国家标准局(ANSI)数据库委员会批准了SQL作为关系数据库语言的美国标准。 同年公布了SQL标准文本(简称SQL-86)。 1987年6月, 国际标准化组织(ISO)将其采纳为国际标准。 在此之后, 对SQL进行修改和扩充的标准化工作不断进行着, 相继出现了SQL-89、 SQL2(1992)和SQL3(1999)。


SQL数据库的体系结构

  • 从图中可以看出,模式与基本表相对应,外模式与视图相对应,内模式对应于存储文件。基本表和视图都是关系。


视图与基本表

1.基本表(Base Table)

基本表是模式的基本内容。每个基本表都是一个实际存在的关系。

2.视图(View)

视图是外模式的基本单位,用户通过视图使用数据库中基于基本表的数据(基本表也可作为外模式使用)。一个视图虽然也是一个关系,但是它与基本表有着本质的区别。任何一个视图都是从已有的若干关系导出的关系,它只是逻辑上的定义,实际并不存在。在导出时,给出一个视图的定义(从哪几个关系中,根据什么标准选取数据,组成一个什么


视图与基本表(续)

名称的关系等),此定义存放在数据库(数据字典)中,但没有真正执行此定义(并未真正生成此关系)。当使用某一视图查询时,将实时从数据字典中调出此视图的定义;根据此定义以及现场查询条件,从规定的若干关系中取出数据,组织成查询结果,展现给用户。

因此,视图是虚表,实际并不存在,只有定义存放在数据字典中。

当然,用户可在视图上再定义视图,就像在基本表上定义视图一样,因为视图也是关系。


3.存储文件

存储文件是内模式的基本单位。每一个存储文件存储一个或多个基本表的内容。一个基本表可有若干索引,索引也存储在存储文件中。存储文件的存储结构对用户是透明的。

下面将介绍SQL的基本语句。各厂商的RDBMS实际使用的SQL语言,与标准SQL语言都有所差异及扩充。因此,具体使用时,应参阅实际系统的有关手册。


3 1 sql
3.1 SQL概述

  • SQL的特点

    • 1. 综合统一

    • 2. 高度非过程化

    • 3. 面向集合的操作方式

    • 4. 以同一种语法结构提供两种使用方法

    • 5. 语言简洁,易学易用


5. 语言简捷,易学易用


第三章关系数据库标准语言SQL

3.1 SQL概述

3.2 数据定义

3.3 查询

3.4 数据更新

3.5 视图

3.6 数据控制

3.7 嵌入式SQL

3.8 小结


3.2 数 据 定 义


3 2 1
3.2.1 表定义语句格式

CREATE TABLE <表名>

(<列名> <数据类型>[ <列级完整性约束条件> ]

[,<列名> <数据类型>[ <列级完整性约束条件>] ] …

[,<表级完整性约束条件> ] );

  • <表名>:所要定义的基本表的名字。在一个数据库中,不允许有两个基本表同名(应该更严格的说,任何两个关系都不能同名,包括视图)。

  • <列名>:组成该表的各个属性(列)。一个表中不能有两列同名。


  • <类型>:规定了该列的数据类型。各具体DBMS所提供的数据类型是不同的。但下面的数据类型几乎都是支持的:

    INT或INTEGER 全字长二进制整数

    SMALLINT 半字长二进制整数

    DEC(p[,q])

    压缩十进制数,共p位,其中小数点后有q位,


FLOAT 双字长的浮点数

CHAR(n)或CHARTER(n) 长度为n的定长字符串

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

DATE 日期型,格式为YYYY―MM―DD

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

TIMESTAMP 日期加时间


  • <列的完整性约束>:该列上数据必须符合的条件。

  • <表级完整性约束>:对整个表的一些约束条件,常见的有定义主码(外码),各列上数据必须符合的关联条件等。

  • SQL只要求语句的语法正确就可以,对格式不作特殊规定,不区分大小写。一条语句可以放在多行上,字和符号间有一个或多个空格分隔。一般每个列定义单独占一行(或数行),每个列定义中相似的部分对齐(这不是必须的),从而增加可读性,一目了然。


例题

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

CREATE TABLE Student

(Sno CHAR(5) NOT NULL UNIQUE,

Sname CHAR(20) UNIQUE,

Ssex CHAR(1) ,

Sage INT,

Sdept CHAR(15));


定义基本表(续)

  • 常用完整性约束

    • 主码约束: PRIMARY KEY

    • 唯一性约束:UNIQUE

    • 非空值约束:NOT NULL

    • 参照完整性约束: FOREIGN KEY

      PRIMARY KEY与UNIQUE的区别?

      在一个表中只能定义一个PRIMARYKEY,不允许空值;而UNIQUE可定义多个,且允许空值


例题 (续)

  • [例2] 建立一个“学生选课”表SC,它由学号Sno、课程号Cno,修课成绩Grade组成,其中(Sno, Cno)为主码。

    • CREATE TABLE SC(

    • Sno CHAR(5) ,

    • Cno CHAR(3) ,

    • Grade int,

    • Primary key (Sno, Cno));



[例] 创建职工表。

CREATE TABLE Employee

(Eno CHAR(4) NOT NULL UNIQUE

Ename CHAR(8),

Sex CHAR(2),

Age INT,

Marry CHAR(1),

Title CHAR(6),

Dno CHAR(2));

  • 执行后,数据库中就新建立了一个名为Employee的表,此表尚无元组(即为空表)。此表的定义及各约束条件都自动存放进数据字典。


二、修改基本表

ALTER TABLE <表名>

[ ADD <新列名> <数据类型> [ 完整性约束 ] ]

[ DROP <完整性约束名> ]

[ MODIFY <列名> <数据类型> ];

  • <表名>:要修改的基本表

  • ADD子句:增加新列和新的完整性约束条件

  • DROP子句:删除指定的完整性约束条件

  • MODIFY子句:用于修改列名和数据类型


例题

  • 例 在基本表S中增加一个地址(ADDRESS)列,可用下列语句:

    ALTER TABLE S ADD ADDRESS VARCHAR(30);

    【例】在学校School表中增加“联系电话”列, 数据类型为字符型, 长度为15。

    ALTER TABLE School ADD Phone CHAR(15)

  • 注意: 新增加的列的约束不能定义为“NOT NULL”, 因为基本表在增加一列后, 原有元组在新增加的列上的值都被定义为空值(NULL)。


语句格式(续)

  • 删除属性列

    直接/间接删除

    • 把表中要保留的列及其内容复制到一个新表中

    • 删除原表

    • 再将新表重命名为原表名

      直接删除属性列:(新)

      例:ALTER TABLE Student Drop Scome;

  • 例 在基本表S中删除年龄(AGE)列,并且把引用该列的所有视图和约束也一起删除,可用下列语句:

    ALTER TABLE S DROP AGE CASCADE;


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

ALTER TABLE Student MODIFY Sage SMALLINT;

  • 例 在基本表S中S# 的长度修改为6,可用下列语句:

    ALTER TABLE S MODIFY S# CHAR(6);

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


例题

[例4] 删除学生姓名必须取唯一值的约束。

ALTER TABLE Student DROP UNIQUE(Sname);


三、删除基本表

DROP TABLE <表名>;

基本表删除 数据、表上的索引都删除

表上的视图往往仍然保留,但无法引用

删除基本表时,系统会从数据字典中删去有关该

基本表及其索引的描述


例题

[例5] 删除Student表

DROP TABLEStudent ;


3 2 2
3.2.2 建立与删除索引

  • 建立索引是加快查询速度的有效手段

  • 建立索引

    • DBA或表的属主(即建立表的人)根据需要建立

    • 有些DBMS自动建立以下列上的索引

      • PRIMARY KEY

      • UNIQUE

  • 维护索引

    • DBMS自动完成

  • 使用索引

    • DBMS自动选择是否使用索引以及使用哪些索引


一、建立索引

  • 语句格式

    CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

    • 用<表名>指定要建索引的基本表名字

    • 索引可以建立在该表的一列或多列上,各列名之间用逗号分隔

    • 用<次序>指定索引值的排列次序,升序:ASC,降序:DESC。缺省值:ASC

    • UNIQUE表明此索引的每一个索引值只对应唯一的数据记录

    • CLUSTER表示要建立的索引是聚簇索引

    • 本语句建立索引的排列方式为:首先以第一个列的值排序;该列值相同的记录,按下一列名的值排序;以此类推。


例题

[例6] 为学生-课程数据库中的Student,Course,SC三个表建立索引。其中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);


建立索引 (续)

  • 唯一值索引

    • 对于已含重复值的属性列不能建UNIQUE索引

    • 对某个列建立UNIQUE索引后,插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束


建立索引 (续)

  • 聚簇索引

    • 建立聚簇索引后,基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致

      例:

      CREATE CLUSTER INDEX Stusname ON

      Student(Sname);

      在Student表的Sname(姓名)列上建立一个聚簇索引,而

      且Student表中的记录将按照Sname值的升序存放


建立索引 (续)

  • 在一个基本表上最多只能建立一个聚簇索引

  • 聚簇索引的用途:对于某些类型的查询,如经常执行对某些列的查询,可提高查询效率

  • 聚簇索引的适用范围

    • 很少对基表进行增删操作

    • 很少对其中的变长列进行修改操作


二、删除索引

DROP INDEX <索引名>;

  • 删除索引时,系统会从数据字典中删去有关该索引的描述。

    [例7] 删除Student表的Stusname索引。

    DROP INDEX Stusname;


3.3 查 询

3.3.1 概述

3.3.2 单表查询

3.3.3 连接查询

3.3.4 嵌套查询

3.3.5 集合查询

3.3.6 小结


  • SELECT—FROM—WHERE句型

    在关系代数中最常用的式子是下列表达式:

    πA1,…,An(σF(R1×…×Rm))

    这里R1、…、Rm为关系,F是公式,A1、…、An为属性。

    针对上述表达式,SQL为此设计了SELECT—FROM—WHERE句型:

    SELECT A1,…,An

    FROM R1,…,Rm

    WHERE F

    这个句型是从关系代数表达式演变来的,但WHERE子句中的条件表达式F要比关系代数中公式更灵活。


3 3 1
3.3.1 概述

  • 语句格式

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

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

    [ WHERE <条件表达式> ]

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

    [ ORDER BY <列名2> [ ASC|DESC ] ];


语句格式

  • SELECT子句:指定要显示的属性列

  • FROM子句:指定查询对象(基本表或视图)

  • WHERE子句:指定查询条件

  • GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中使用集函数。

  • HAVING短语:筛选出只有满足指定条件的组

  • ORDER BY子句:对查询结果表按指定列值的升序或降序排序


  • 整个语句的执行过程如下:

    (1)先构造from子句中关系(如基本表或视图)的笛卡儿积。

    (2)根据where 子句中的条件表达式进行关系代数的选择运算,即选取满足WHERE子句中给出的条件表达式的元组。

    (3)按SELECT子句中给出的列名或列表达式求值输出。

    (4)按GROUP子句中指定列的值分组,同时提取满足HAVING子句中组条件表达式的那些组。

    (5)ORDER子句对输出的目标表进行排序,按附加说明ASC升序排列,或按DESC降序排列。


  • WHERE 子句中的条件表达式中常使用下列操作符:

    • 算术比较运算符: =(等于), <(小于),

      >(大于), <=(小于等于),

      >=(大于等于), !=或<>(不等于)。

    • 逻辑运算符: AND(与), OR(或), NOT(非)。

    • 合运算符: UNION(并), INTERSECT(交)

      , EXCEPT(差)。

    • 集合成员资格运算符: IN, NOT IN。

    • 谓词: EXISTS(存在量词), ALL, ANY, UNIQUE。

    • 聚合函数: AVG(平均值), MIN(最小值)MAX(最大值), SUM(和), COUNT(计数)

    • 确定范围: BETWEEN AND, NOT BETWEEN AND。

    • 字符匹配: LIKE, NOT LIKE。

    • 空值: IS NULL, IS NOT NULL。


示例

  • 例 对教学数据库的基本表S、SC、C中数据进行查询和计算。

    ①统计每一年龄选修课程的学生人数

    SELECT AGE,COUNT(DISTINCT S.S#)

    FROM S,SC

    WHERE S.S#=SC.S#

    GROUP BY AGE;

    由于要统计每一个年龄的学生人数,因此要把满足WHERE子句中条件的查询结果按年龄分组,在每一组中的学生年龄相同。此时的SELECT子句应对每一组分开进行操作,在每一组中,年龄只有一个值,统计的人数是这一组中的学生人数。


示例

  • ②求基本表S中男同学的每一年龄组(超过50人)有多少人?要求查询结果按人数升序排列,人数相同按年龄降序排列。

    SELECT AGE,COUNT(S#)NUMBER

    FROM S

    WHERE SEX='M'

    GROUP BY AGE

    HAVING COUNT(*)> 50

    ORDER BY NUMBER,AGE DESC;


示例数据库

学生-课程数据库

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

  • 课程表:Course(Cno,Cname,Cpno,Ccredit)

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


3.3 查 询

3.3.1 概述

3.3.2 单表查询

3.3.3 连接查询

3.3.4 嵌套查询

3.3.5 集合查询

3.3.6 小结


3 3 2
3.3.2 单表查询

查询仅涉及一个表,是一种最简单的查询操作

一、选择表中的若干列(即是对表作投影运算)

二、选择表中的若干元组

三、对查询结果排序

四、使用集函数

五、对查询结果分组


查询指定列

[例1] 查询全体学生的学号与姓名。

SELECT Sno,Sname

FROM Student;

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

SELECT Sname,Sno,Sdept

FROM Student;

【例】查询所有学校的代码和计划招生人数。

SELECT Sccode, Plnum

FROM School


查询全部列

[例3] 查询全体学生的详细记录。

SELECT Sno,Sname,Ssex,Sage,Sdept

FROM Student;

SELECT *

FROM Student;

这里“*”号代表所有列。


例】 查询全体考生来自哪些民族。

SELECT DISTINCT Nation

FROM Examinee

由于许多考生来自同一民族(如汉族), 所以查询结果有重复项。 本例SELECT子句中加上了DISTINCT短语(缺省为ALL), 即可消除重复项。


3. 查询经过计算的值

SELECT子句的<目标列表达式>为表达式

  • 算术表达式

  • 字符串常量

  • 函数

  • 列别名


3. 查询经过计算的值

[例4] 查全体学生的姓名及其出生年份。

SELECT Sname,2005-Sage

FROM Student;

输出结果:

Sname 2005-Sage

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

李勇 1976

刘晨 1977

王名 1978

张立 1978


3. 查询经过计算的值

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

SELECT Sname,'Year of Birth: ',2005-Sage,

ISLOWER(Sdept)

FROM Student;


例题(续)

输出结果:

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

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

李勇 Year of Birth: 1976 cs

刘晨 Year of Birth: 1977 is

王名 Year of Birth: 1978 ma

张立 Year of Birth: 1977 is


例】查询所有学校实际招生人数与计划招生人数的差额。

SELECT Sccode, ′差额是′, Renum-Plnum

FROM School

查询结果如下:

  • Sccode 差额是 Renum-Plnum

  • 300 差额是 70

  • 301 差额是 323

  • 303 差额是 4

  • 304 差额是 55

  • 317 差额是 84

  • 318 差额是 -79

  • 320 差额是 -92

  • 321 差额是 -17


[例5.1] 使用列别名改变查询结果的列标题

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

2005-Sage BIRTHDAY,ISLOWER(Sdept) DEPARTMENT

FROMStudent;

输出结果:

NAME BIRTH BIRTHDAY DEPARTMENT

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

李勇 Year of Birth: 1976 cs

刘晨 Year of Birth: 1977 is

王名 Year of Birth: 1978 ma

张立 Year of Birth: 1977 is


二、选择表中的若干元组

  • 消除取值重复的行

  • 查询满足条件的元组


1. 消除取值重复的行

  • 在SELECT子句中使用DISTINCT短语

    假设SC表中有下列数据

    Sno Cno Grade

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

    95001 1 92

    95001 2 85

    95001 3 88

    95002 2 90

    95002 3 80


All distinct
ALL 与DISTINCT

[例6] 查询选修了课程的学生学号。

(1) SELECT Sno

FROM SC;

或(默认 ALL)

SELECT ALL Sno

FROM SC;

结果: Sno

-------

95001

95001

95001

95002

95002


例题(续)

(2) SELECT DISTINCT Sno

FROM SC;

结果:

Sno

-------

95001

95002


例题(续)

  • 注意 DISTINCT短语的作用范围是所有目标列

    例:查询选修课程的各种成绩

    错误的写法

    SELECT DISTINCT Cno,DISTINCT Grade

    FROM SC;

    正确的写法

    SELECT DISTINCT Cno,Grade

    FROM SC;


2.查询满足条件的元组

WHERE子句常用的查询条件


(1) 比较大小

在WHERE子句的<比较条件>中使用比较运算符

  • =,>,<,>=,<=,!= 或 <>,!>,!<,

  • 逻辑运算符NOT + 比较运算符

    [例8] 查询所有年龄在20岁以下的学生姓名及其年龄。

    SELECT Sname,Sage

    FROM Student

    WHERE Sage < 20; 或

    SELECT Sname,Sage

    FROM Student

    WHERE NOT Sage >= 20;


(1) 比较大小

  • [例]列出帐号不是A-201的客户:

    select customer-name

    from depositor

    where account-number <> A-201

  • [例]查询考分是600分以上的考生考号, 考分。

    SELECT Exno, Exgrade

    FROM Examinee

    WHERE Exgrade〉600


(2) 确定范围

  • 使用谓词 BETWEEN … AND …

    NOT BETWEEN … AND …

    [例10] 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。

    SELECT Sname,Sdept,Sage

    FROM Student

    WHERE Sage BETWEEN 20 AND 23;


(2) 确定范围

  • [例] 查询“贷款额在90 000-100 000美元之间的贷款的贷款号”,可以使用 between比较,书写如下:

    select number

    from loan

    where amount between 90 000 and 100 000


例题(续)

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

SELECT Sname,Sdept,Sage

FROM Student

WHERE Sage NOT BETWEEN 20 AND 23;


(3) 确定集合

也称成员测试(IN)。它测试数据值是否与一列目标值匹配。

使用谓词 IN <值表>, NOT IN <值表>

<值表>:用逗号分隔的一组取值

[例12]查询信息系(IS)、数学系(MA)和计

算机科学系(CS)学生的姓名和性别。

SELECT Sname,Ssex

FROM Student

WHERE Sdept IN ( 'IS','MA','CS' );

【例】列出在Rye, Stamford, Harrison 的客户。

select name, street

from customer

where city in (Rye, Stamford, Harrison)


(3) 确定集合

同样能通过NOT IN形式检查数据值是否不匹配任何目标值。

【例】查询在银行中有贷款的用户,他的名字既不是“Smith”,也不是“Jones”。

select distinct customername

from borrower

where customername not in (“Smith”,“Jones” );

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

SELECT Sname,Ssex

FROM Student

WHERE Sdept NOT IN ( 'IS','MA','CS' );


(4) 字符串匹配

  • [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

    <匹配串>:指定匹配模板

    匹配模板:固定字符串或含通配符的字符串

    当匹配模板为固定字符串时,

    可以用 = 运算符取代 LIKE 谓词

    用 != 或 < >运算符取代 NOT LIKE 谓词


通配符

  • % (百分号) 代表任意长度(长度可以为0)的字符串

    • 例:a%b表示以a开头,以b结尾的任意长度的字符串。如acb,addgb,ab 等都满足该匹配串

  • _ (下横线) 代表任意单个字符

    • 例:a_b表示以a开头,以b结尾的长度为3的任意字符串。如acb,afb等都满足该匹配串


Escape
ESCAPE 短语:

  • 当用户要查询的字符串本身就含有 % 或 _ 时,要使用ESCAPE '<换码字符>' 短语对通配符进行转义。


例题

1) 匹配模板为固定字符串

[例14] 查询学号为95001的学生的详细情况。

SELECT *

FROM Student

WHERE Sno LIKE '95001';

等价于:

SELECT *

FROM Student

WHERE Sno = '95001';


例题(续)

2) 匹配模板为含通配符的字符串

[例15] 查询所有姓刘学生的姓名、学号和性别。

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname LIKE ‘刘%’;


例题(续)

匹配模板为含通配符的字符串(续)

[例16] 查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECT Sname

FROM Student

WHERE Sname LIKE ‘欧阳_ _';


例题(续)

匹配模板为含通配符的字符串(续)

[例17] 查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECT Sname,Sno

FROM Student

WHERE Sname LIKE '_ _阳%';


例题(续)

匹配模板为含通配符的字符串(续)

[例18] 查询所有不姓刘的学生姓名。

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname NOT LIKE '刘%';


例题(续)

3)若通配符本身就是字符串内容,则可增加短语ESCAPE解释之。如:

WHERE name LIKE′C\ _%′ESCAPE′\′,则紧跟在\后的_不是通配符,仅是一个字符而已

[例19] 查询DB_Design课程的课程号和学分。

SELECT Cno,Ccredit

FROM Course

WHERE Cname LIKE 'DB\_Design'

ESCAPE '\'


例题(续)

[例] 查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。

SELECT *

FROM Course

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

[例]从Emp表中查找Ino的前4位为′1998′的元组,显示Eno列值(相同值只需显示一次)。

SELECT DISTINCT Eno

FROM Emp

WHERE Ino LIKE′1998%′


(5) 涉及空值的查询

  • 使用谓词 IS NULL 或 IS NOT NULL

  • “IS NULL” 不能用 “= NULL” 代替

    [例21] 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

    SELECT Sno,Cno

    FROM SC

    WHERE Grade IS NULL;


例题(续)

[例22] 查所有有成绩的学生学号和课程号。

SELECT Sno,Cno

FROM SC

WHERE Grade IS NOT NULL;

【例】查询没有开设银行分行的城市,则用如下语句实现:

select city

from branch

where branch_name is null


(6) 多重条件查询

用逻辑运算符AND和 OR来联结多个查询条件

  • AND的优先级高于OR

  • 可以用括号改变优先级

    可用来实现多种其他谓词

  • [NOT] IN

  • [NOT] BETWEEN … AND …


例题

[例] 查询在Perridge分行贷款而且贷款额多于$1300的贷款号。

select number

from loan

where branch_name=“Perridge” and amount>1300;

[例] 查询计算机系年龄在20岁以下的学生姓名。

SELECT Sname

FROM Student

WHERE Sdept= 'CS' AND Sage<20;


改写[例12]

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

SELECT Sname,Ssex

FROM Student

WHERE Sdept IN ( 'IS','MA','CS' )

可改写为:

SELECT Sname,Ssex

FROM Student

WHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS ';


改写[例10]

[例10] 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。SELECT Sname,Sdept,Sage

FROM Student

WHERE Sage BETWEEN 20 AND 23;

可改写为:

SELECT Sname,Sdept,Sage

FROM Student

WHERE Sage>=20 AND Sage<=23;


示例

[例]从表Salary中选取符合下列条件的元组:

①基本工资(Basepay)在600与700之间;

②工龄工资(Service)为6,7,13,25;

③职工号(Eno)前三位为100(共有4位)。 SELECT *

FROM Salary

WHERE (Basepay BETWEEN 600 AND 700) AND (Service IN (6.0,7.0,13.0,25.0)) AND Eno LIKE ‘100_’


三、对查询结果排序

使用ORDER BY子句

  • 可以按一个或多个属性列排序

  • 升序:ASC;降序:DESC;缺省值为升序

  • 若按多列排序, 则列名间用“,”号隔开。 排列次序为: 先按第一列值排序, 第一列值相同时, 再按第二列排序

    当排序列含空值时

  • ASC:排序列为空值的元组最后显示

  • DESC:排序列为空值的元组最先显示


对查询结果排序(续)

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

SELECT Sno,Grade

FROM SC

WHERE Cno= ' 3 '

ORDER BY Grade DESC;


查询结果

Sno Grade

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

95010

95024

95007 92

95003 82

95010 82

95009 75

95014 61

95002 55


例】 查询考分在500分以上的少数民族考生信息, 并按考分从高到低排序。

SELECT *

FROM Examinee

WHERE Exgrade>500 AND Nation<>′汉′

ORDER BY Exgrade DESC


对查询结果排序(续)

[例25] 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。

SELECT *

FROM Student

ORDER BY Sdept,Sage DESC;


四、使用集函数

5类主要集函数

  • 计数

    COUNT([DISTINCT|ALL] *)

    COUNT([DISTINCT|ALL] <列名>)

  • 计算总和

    SUM([DISTINCT|ALL] <列名>)

  • 计算平均值

    AVG([DISTINCT|ALL] <列名>)


使用集函数(续)

求最大值

MAX([DISTINCT|ALL] <列名>)

求最小值

MIN([DISTINCT|ALL] <列名>)

  • DISTINCT短语:在计算时要取消指定列中的重复值

  • ALL短语:不取消重复值

  • ALL为缺省值


使用集函数 (续)

[例26] 查询学生总人数。

SELECT COUNT(*)

FROM Student;

[例27] 查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)

FROM SC;

注:用DISTINCT以避免重复计算学生人数


例】 统计填报第一志愿代码为301, 考分〉640的考生人数。

SELECT COUNT(*)

FROM Examinee, Ewill

WHERE Exgrade>640 AND Scode1=′301′ AND Examinee.Exno=Ewill.Exno

查询结果:

1

  • SQL不允许在用count(*)时使用 distinct。

  • SQL不允许对聚集函数进行复合。因而,如max(avg(…)) 等是不允许的。


使用集函数 (续)

[例28] 计算1号课程的学生平均成绩。

SELECT AVG(Grade)

FROM SC

WHERE Cno= ' 1 ';

  • sum和avg 的输入必须是数字,而其他函数还可作用在非数字数据类型如字符串、日期/时间上。

    [例29] 查询选修1号课程的学生最高分数。

    SELECT MAX(Grade)

    FROM SC

    WHER Cno= ' 1 ';


五、对查询结果分组

使用GROUP BY子句分组

细化集函数的作用对象

  • 未对查询结果分组,集函数将作用于整个查询结果

  • 对查询结果分组后,集函数将分别作用于每个组


Group by
使用GROUP BY子句分组

[例30] 求各个课程号及相应的选课人数。

SELECT Cno,COUNT(Sno)

FROM SC

GROUP BY Cno; 

结果

Cno COUNT(Sno)

1 22

2 34

3 44

4 33

5 48


对查询结果分组 (续)

  • 分组方法:按指定的一列或多列值分组,值相等的为一组

  • 使用GROUP BY子句后,SELECT子句的列名列表中只能出现分组属性和集函数


例题

[例32] 查询有3门以上课程是90分以上的

学生的学号及(90分以上的)课程数

SELECT Sno, COUNT(*)

FROM SC

WHERE Grade>=90

GROUP BY Sno

HAVING COUNT(*)>=3;


ad