PowerPoint 演示文稿

Transcription

1 主讲人 : 梅炳夫

2 第四章 结构化查询语言 -SQL

3 学习目标 掌握数据库模式的建立和删除 ; 掌握表 视图 索引的创建与撤消的语句格式及功能 ; 掌握数据的插入 删除和修改的语句格式及功能 ; 熟练掌握库查询语句 SELECT 的各种格式和相应功能

4 SQL 简介 SQL 是结构化查询语言 (Structured Query Language) 的缩写 SQL 具有 : 数据定义 数据操纵 数据控制的功能 在 SQL 中, 每个关系又叫基本表 (Table), 属性又叫字段 (Field) 或列 (Column), 元组又叫行 (Row) 一个数据库由若干个基本表组成, 通常一个基本表对应存储在外存数据库空间的一个存储文件中

5 PRAT 1 数据库模式的建立和删除

6 当需要在计算机中某个关系数据 库管理系统之下建立数据库时, 首先 就要使用数据库模式的建立命令定义 数据库名, 即模式名, 然后在其中定义各个基本表 视图等有关对象

7 1 建立数据库模式 CREATE {SCHEMA DATABASE} < 数据库名 > [AUTHORRIZATION < 所有者名 > ] 语句功能 : 在系统中建立一个只有名字的空数据库, 并定义出它的所有者名 语句举例 : Create schema xuesh authorization 刘勇 Create database 教学管理

8 2 删除数据库模式 DROP {SCHEMA DATABASE} < 数据库名 > 语句功能 : 在系统中删除一个数据库 同时删除库中的所有信息 语句举例 : drop database 学生库

9 PRAT 2 表结构的建立 修改和删除

10 在系统中命名了一个关系数据库之后, 接着就要在其中建 立关系数据库模式设计中的每个关系 ( 表 )

11 1 建立表结构 CREATE TABLE [< 数据库名 > < 所有者名 >] < 基本表名 > (< 列定义 >, [,< 表级完整性约束 >, ]) 语句功能 : 在当前或给定的数据库中定义一个表的结构 ( 关系模式 ) 语句说明 : 每个列的名字是一个用户定义的标识符, 列 名后面跟着其数据类型的描述 create table 工资表 ( 职工号 char (6), 姓名 char (6) not null, 性别 char (2), 年龄 int, 基本工资 float )

12 1 建立表结构 语句举例 : Create table 学生 ( 学生号 char(7) primary key, 姓名 char(6) not null unique, 性别 char(2) not null check( 性别 = 男 or 性别 = 女 ), 出生日期 datetimr check ( 出生日期 < ), 年级 int check ( 年级 >= 1 and 年级 <=4) )

13 1 建立表结构 语句举例 : Create table 课程 ( 课程号 char(4) primary key, 课程名 char(10) not null unique, 课程学分 int check ( 课程学分 >=1 and 课程学分 <=6) )

14 2 修改表结构 ALTER TABLE [< 数据库名 >.< 所有者名 >.] < 基本表名 > {ADD< 列定义 >, ADD< 表级完整性约束 >, DROP COLUMN< 列名 >, DROP 约束名, } 语句功能 : 向已定义过的表中添加一些列的定义或一些表级完整性约束, 或者从已定义过的表中删除一些列或一些完整性约束

15 2 修改表结构 语句举例 : Alter table 学生 add 籍贯 char(6) Alter table 学生 drop column 籍贯

16 2 删除表结构 DROP TABLE [< 数据库名 >.< 所有者名 >.] < 基本表名 > 语句功能 : 先删除掉与之关联的所有子表, 或者删除掉所有相应的外码约束 语句举例 : drop table 学生 1

17 PRAT 3 表内容的插入 修改和删除

18 当建立好一个表结构后, 它只是一个空表, 接着需要向它 插入数据, 即添加每个元组, 然后还可以修改和删除等操作

19 1 插入记录 单行插入 : INSERT [INTO] [< 数据库名 >.< 所有者名 >.] < 基本表名 > (< 列名 >, ) VALUES(< 列值 >, ) 多行插入 : INSERT [INTO] [< 数据库名 >.< 所有者名 >.] < 基本表名 > (< 列名 >, ) <SELECT 子句 >) 语句功能 : 向表中所指定的若干列插入一行或多行数据

20 1 插入记录 语句举例 : Create table 职工 ( 职工号 char(6) primary key, 姓名 char(8) not null, 性别 char(2) not null, 年龄 int, 基本工资 float ); Insert into 职工 ( 职工号, 姓名, 性别, 年龄, 基本工资 )values( , 李羽, 女,28,3560) Insert into 职工 ( 职工号, 姓名, 性别, 年龄, 基本工资 ) select 职工号, 姓名, 性别, 年龄, 基本工资 from 职工 1 where 性别 = 男

21 2 修改记录 UPDATE [< 数据库名 >.< 所有者名 >.] < 目的表名 > SET< 列名 >= 表达式, [FROM< 源表名 >, ][WHERE< 逻辑表达式 >] 语句功能 : 按条件修改一个表中一些值 语句举例 : Update 职工 set 年龄 = 年龄 +1

22 2 修改记录 语句举例 : use 职工数据库 update 工资表 set 基本工资 = 基本工资 *1.2 Update 职工 set 基本工资 = 基本工资 *1.2 where 职工号 ='010405'

23 3 删除记录 DELETE [FROM] [< 数据库名 >.< 所有者名 >.] < 目的表名 > [FROM< 源表名 >, ][WHERE< 逻辑表达式 >] 语句功能 : 删除一个表中中满足条件的所有行 语句举例 : delete from 职工 where 年龄 >45

24 3 删除记录 语句举例 : delete 职工 form 职工 1 where 职工. 职工号 = 职工 1. 职工号 delete 职工

25 PRAT 4 视图的建立 修改和删除

26 视图与基本表的关系 : 是在基本表上建立的表 视图可以在任何一个或多个表上建立, 这些表包括基本表和称作逻辑表的视图在内

27 1 建立视图 语句格式 : CREATE VIEW < 视图名 > (< 列名 >, ) AS <SELECT 子句 > 语句功能 : 在当前数据库中根据 SELECT 子句的查询结果建立一个视图, 包括视图的结构和内容

28 1 建立视图 语句举例 : create view 职工名单 ( 职工号, 姓名 ) as select 职工号, 姓名 from 工资表 假定在教学库中建立的学生 课程 选课三个基本表如表 若教学库为当前库, 则执行下列语句 : create view 成绩视图表 ( 学生号, 姓名, 课程号, 课程名, 成绩 ) as select 选课. 学生号, 姓名, 选课. 课程号, 课程名, 成绩 from 学生, 课程, 选课 where 学生. 学生号 = 选课. 学生号 and 课程. 课程号 = 选课. 课程号 and 专业 = 电子

29 2 修改视图内容 通常需要对视图进行修改, 当然一般是修改由基本表中非主属性所对应的列, 若要修改主属性所对应的列, 最好到各自的基本表中去修改, 以便更好地满足关系的规范化和完整性的要求 语句格式 : UPDATE [ < 数据库名 >.< 所有者名 >.] < 视图名 > SET < 列名 >=< 表达式 >, [FROM< 源表名 >, ][WHERE< 逻辑表达式 >] 语句功能 : 按照一定条件对当前或指定数据库中的一些列值进行修改

30 2 修改视图内容 语句举例 : UPDATE [ < 数据库名 >.< 所有者名 >.] < 视图名 > SET < 列名 >=< 表达式 >, [FROM< 源表名 >, ][WHERE< 逻辑表达式 >] UPDATE 成绩视图表 SET 成绩 =80 WHERE 学生号 = and 课程号 = E002 此修改直接反映到基本表中, 即把对应的选课关系中该元组的成绩修改为 80, 当然也就自然反映到存储文件中

31 3 修改视图定义 语句格式 : ALTER VIEW < 视图名 > ( < 列名 >, )AS <SELECT 子句 > 语句功能 : 在当前数据库中修改已知视图的列, 它与 SELECT 子句的查询结果相对应 语句举例 : Create view 学生视图 ( 学号, 姓名 )as select 学号, 姓名 from 学生 Alter view 学生视图 ( 学号, 专业 )as select 学号, 专业 from 学生

32 4 删除视图 语句格式 : DROP VIEW < 视图名 > 语句功能 : 删除当前数据库中的一个视图 Drop view 成绩视图表

33 PRAT 5 SQL 查询

34 SQL 集数据定义 数据更新 数据控制 数据查询等 功能为一体 SQL 查询对应 SELECT 语句

35 1 SELECT 语句格式 SELECT [ALL DISTINCT] { < 表达式 1> [[AS]< 列名 1>] [,< 表达式 2> [[AS]< 列名 2>] * < 表别名 >.* } [ INTO < 基本表名 > ] FROM < 表名 1> [[AS]< 表别名 1>][,< 表名 2> [ [AS] < 表别名 2> ] ] [ WHERE < 逻辑表达式 1> ] [GROUP BY < 分组列名 1>[,< 分组列名 2 > ] [HAVING < 逻辑表达式 2> ] [ ORDER BY < 排序列名 1> [ ASC DESC ] [,< 排序列名 2> [ ASC DESC ] ]] 语句功能 : 根据一个或多个表按条件进行查询, 产生出一个新表, 该表被显示出来或者被命名保存起来

36 1 SELECT 语句格式 在查询语句中, 包含了关系运算 : 选择 投影 连接 笛卡儿积 并 SELECT 实现投影运算 FROM 实现连接运算 [ WHERE ] 实现选择运算省略连接条件实现笛卡儿积运算在两个查询语句之间使用 UNION 关键字实现并运算

37 2 SELECT 选项 在该选项中,ALL/DISTINC 为任选项 语句举例 :P87 首先创建两个基本表 Create database 商品库 Use 商品库 Create table 商品表 1 ( 商品代号 ) Create table 商品表 2 ( 商品代号 ) 另一个数据库为教学库, 包含学生 课程 选课三个基本表 注意 : 使用时, 要把它设为当前库

38 2 SELECT 选项 例 4-1 从商品库的商品表 1 中查询出每种商品的分类名 SELECT 分类名 FROM 商品表 1 或 SELECT ALL 分类名 FROM 商品表 1 查询结果为 :

39 2 SELECT 选项 例 4-2 从商品库的商品表 1 中查询出所有商品的不同分类名 SELECT DISTINCT 分类名 FROM 商品表 1 查询结果为 :

40 2 SELECT 选项 SELECT [ALL DISTINCT] { < 表达式 1> [[AS]< 列名 1>] [,< 表达式 2> [[AS]< 列名 2>] * < 表别名 >.* } 表达式 1, 表达式 2 为计算目的表中相应列值 可以通过 AS 选项定义对应的列名 * 表明所有的字段

41 2 SELECT 选项 例 4-3 从商品库的商品表 1 中查询出分类名为 电视机 的所有商品 SELECT *; FROM 商品表 1; Where 分类名 = 电视机 查询结果为 :

42 2 SELECT 选项 例 4-4 从商品库的商品表 1 中查询出单价低于 2000 元的每一种商品的商品代号 分类名和单价 SELECT 商品代号, 分类名, 单价 FROM 商品表 1 where 单价 <2000 查询结果为 :

43 2 SELECT 选项 例 4-5 从商品库的商品表 1 中查询出每一种商品的价值 SELECT 商品代号, 单价 * 数量 as 价值 FROM 商品表 1 查询结果为 :

44 2 SELECT 选项 例 4-6 从商品库的商品表 1 和商品表 2 中查询出按商品代号进行自然连接的结果 SELECT 商品表 1.*, 产地, 品牌 FROM 商品表 1, 商品表 2 Where 商品表 1. 商品代号 = 商品表 2. 商品代号 查询结果为 :

45 2 SELECT 选项 在 SELECT 语句中, 使用的列函数有 : COUNT ([ALL DISTINCT]< 列名 > *) MAX( < 列名 > ) 求出对应的列的最大值 MIN ( < 列名 > )( 数值 字符 日期 ) AVG ( < 列名 > ) SUM ( < 列名 > )

46 2 SELECT 选项 例 4-7 从商品库的商品表 1 中查询出不同分类名的个数 SELECT COUNT(DISTINCT 分类名 )AS 分类种数 FROM 商品表 1 查询结果为 :

47 2 SELECT 选项 例 4-8 从商品库的商品表 1 中查询出所有商品的最大数量 最小数量 平均数量及数量总和 SELECT max ( 数量 ) as 最大数量,min ( 数量 ) as 最小数量, avg ( 数量 ) as 平均数量, sum ( 数量 ) as 总和 FROM 商品表 1 查询结果为 :

48 2 SELECT 选项 例 4-9 从商品库的商品表 1 中查询出分类名为 电视机 的商品的种数 最高价 最低价及平均价 SELECT count(*) as 种数, max ( 单价 ) as 最高价,min ( 单价 ) as 最低价, avg ( 单价 ) as 平均价 FROM 商品表 1 WHERE 分类名 = 电视机 查询结果为 :

49 2 SELECT 选项 例 4-10 从商品库的商品表 1 中查询出所有商品的最高价值 最低价值及总价值 SELECT max ( 单价 * 数量 ) as 最高价值,min ( 单价 * 数量 ) as 最低价值, sum ( 单价 * 数量 ) as 总价值 FROM 商品表 1 查询结果为 :

50 3 FROM 选项 SELECT FROM < 表名 1> [[AS]< 表别名 1>][,< 表名 2> [ [AS] < 表别名 2> ] ] [ WHERE < 逻辑表达式 1> ] 该选项提供用于查询的基本表和视图 若基本表和视图不在当前数据库中, 则必须加上数据库名和使用者名作为前缀

51 3 FROM 选项 例 4-11 从教学库中查询出每个学生选修每门课程的学生号 姓名 课程号 课程名 成绩等数据 SELECT x. 学生号, x. 姓名, y. 课程号, y. 课程名, z. 成绩, FROM 学生 x, 课程 y, 选课 z Where x. 学生号 =z. 学生号 and y. 课程号 = z. 课程号 查询结果为 :

52 3 FROM 选项 例 4-11 从教学库中查询出每个学生选修每门课程的学生号 姓名 课程号 课程名 成绩等数据 不使用表别名 : SELECT 学生. 学生号, 姓名, 课程. 课程号, 课程名, 选课. 成绩, FROM 学生, 课程, 选课 Where 学生. 学生号 = 选课. 学生号 and 课程. 课程号 = 选课. 课程号 查询结果为 :P93

53 4 WHERE 选项 SELECT FROM [ WHERE < 逻辑表达式 > ] 该子句的功能是指定源表之间的连接条件和对记录的筛选条件 在该选项中的 < 逻辑表达式 > 既可包含连接条件, 也可以包含筛选条件, 还可以同时包含着两种条件 ( 用 AND 连接成 ) 若在命令的 WHERE 选项中省略连接条件, 则表示将每个源表按笛卡儿积连接 筛选条件的作用是从源表或连接后生成的中间表中选择出所需要的行 筛选条件的一般比较式 的格式 : < 表达式 1>< 比较符 >< 表达式 2>

54 4 WHERE 选项 例 4-12 从商品表 1 中查询出单价大于 1500, 同时数量大于等于 10 的商品 SELECT 商品代号, 单价, 数量 FROM 商品表 1 Where 单价 >1500 and 数量 >=10 查询结果为 :P94

55 4 WHERE 选项 例 4-13 从商品库中查询出产地为南京或无锡的所有商品的商品代号 分类名 产地和品牌 SELECT x. 商品代号, 分类名, 产地, 品牌 FROM 商品表 1 as x, 商品表 2 as y Where x. 商品代号 =y. 商品代号 and ( 产地 = 南京 or 产地 = 无锡 ) 查询结果为 :P94

56 4 WHERE 选项 例 4-14 从教学库中查询出选修至少两门课程的学生学号 SELECT distinct c1. 学生号 FROM 选课 c1, 选课 c2 Where c1. 学生号 =c2. 学生号 and c1. 课程号 <> C2. 课程号 对同一个表, 定义了 c1 和 c2 别名, 作为两个不同的表使用 前一个比较式作为连接条件, 后一个比较式作为筛选条件 查询结果为 :P95

57 4 WHERE 选项 例 4-15 从教学库中查询出选修了课程名为 操作系统 课程的每个学生的姓名 SELECT 姓名 ( 学生. 姓名 ) FROM 学生 x, 课程 y, 选课 z Where x. 学生号 =z. 学生号 and y. 课程号 =z. 课程号 and 课程名 = 操作系统 筛选条件 : 课程. 课程名 = 操作系统 连接条件 :x. 学生号 =z. 学生号 and y. 课程号 =z. 课程号 查询结果为 :P95

58 4 WHERE 选项 在新版的 SQL 中, 已经把查询连接条件从 WHERE 选项中转移到 FROM 选项中 在 FROM 选项中的语法格式为 :( 隐含双重循环 ) 中间连接 :FROM < 表名 1> INNER JION < 表名 2> ON < 表名 1>. < 连接列名 1> < 比较符 > < 表名 2>. < 连接列名 2> ( 一般的连接 ) 左连接 :FROM < 表名 1> LEFT [OUTER] JION < 表名 2> ON < 表名 1>. < 连接列名 1> < 比较符 > < 表名 2>. < 连接列名 2> 右连接 : FROM < 表名 1> RIGHT [OUTER] JION < 表名 2> ON < 表名 1>. < 连接列名 1> < 比较符 > < 表名 2>. < 连接列名 2> P95 解释

59 4 WHERE 选项 例 4-16 从教学库中查询出所有学生的选课情况, 要求没选修任何课程的学生信息也反映出来 SELECT * FROM 学生 left join 选课 on 学生. 学生号 = 选课. 学生号 查询结果为 :P95

60 4 WHERE 选项 若要查询出所有课程被学生选修的情况 SELECT * FROM 课程 left join ( 选课 inner join 学生 on 学生. 学生号 = 选课. 学生号 )on 课程. 课程号 = 选课. 课程号 该语句首先实现选课关系和学生关系的中间连接, 产生出中间表, 然后作为右表再同课程关系进行左连接 查询结果为 :P95

61 4 WHERE 选项 若要从教学库中查询出所有电子专业的学生选课的全部情况 SELECT * FROM 课程 inner join ( 选课 inner join 学生 on 学生. 学生号 = 选课. 学生号 )on 课程. 课程号 = 选课. 课程号 Where 专业 = 电子 它与下面的传统查询语句等效 SELECT * FROM 课程, 选课, 学生 Where 学生. 学生号 = 选课. 学生号 and 课程. 课程号 = 选课. 课程号 and 专业 = 电子

62 4 WHERE 选项 用于查询语句中的专门比较式又叫判断式, 它实现单值与集合数据之间的比较 常用以下六种格式 1. 格式之一 < 列名 > < 比较符 > ALL(< 子查询 >) < 子查询 > 是一条完整的 SELECT 语句, 被嵌套在该比较式中使用 当 < 子查询 > 的查询结果中的每一个值都满足所给的比较条件时, 此比较式的值才为真, 否则为假 < 列名 > < 比较符 > ALL(< 子查询 >) 在查询语句中出现的每个子查询, 同样可以对一个或多个表进行查询, 并且它还能够使用在外层查询上所指定的源表中的列, 不过它必须带有表名前缀, 否则将被视为子查询中所指定源表中的列 在含有子查询的 SELECT 语句执行时, 当执行到 WHERE 选项需要用到子查询结果时, 则该子查询将被 立即执行 在一个子查询的 WHERE 选项中还可以使用 SELECT 语句, 它是该子查询的子查询 也就是说, SELECT 命令能够嵌套使用, 嵌套的层次不受限制, 通常只需嵌套二至三层就可以满足应用的需要了

63 4 WHERE 选项 例 4-17 SELECT * FROM 商品表 1 Where 单价 >all(select 单价 from 商品表 1 where 分类名 = 洗衣机 ) 该语句从商品表 1 中查询出单价比分类名为 洗衣机 的所有商品的单价都高的商品 执行此语句时, 每次取一个商品记录的单价字段的值同子查询结果 ( 每次子查询结果都相同, 即为 468 和 873 P87) 进行比较, 若它大于查询结果中的所有值, 则就把该记录查询出来 查询结果 P98

64 4 WHERE 选项 例 4-18 SELECT x.*, 品牌 FROM 商品表 1 x inner join 商品表 2 y on x. 商品代号 = y. 商品代号 Where 数量 <all(select 数量 from 商品表 1 where 分类名 = 洗衣机 or 分类名 = 微波炉 ) 该语句按商品代号连接商品表 1 和商品表 2, 查询出数量小于分类名为 洗衣机 或 微波炉 的每一个商品数量的所有元组 查询结果 P98

65 4 WHERE 选项 例 4-18 SELECT x.*, 品牌 FROM 商品表 1 x inner join 商品表 2 y on x. 商品代号 = y. 商品代号 Where 数量 <all(select 数量 from 商品表 1 where 分类名 = 洗衣机 or 分类名 = 微波炉 ) 该语句按商品代号连接商品表 1 和商品表 2, 查询出数量小于分类名为 洗衣机 或 微波炉 的每一个商品数量的所有元组 查询结果 P98

66 4 WHERE 选项 2. 格式之二 < 列名 > < 比较符 > {ANY SOME}(< 子查询 >) 当 < 子查询 > 的查询结果中的任一个值满足所给的比较条件时, 此比较式的值才为真 例 4-19 从商品库中查询出产地与品牌为 春兰 的商品的产地相同的所有商品的商品代号 分类名 品牌 产地等属性的值 SELECT x. 商品代号,x. 分类名,y. 品牌,y. 产地 FROM 商品表 1 x inner join 商品表 2 y on x. 商品代号 = y. 商品代号 Where 产地 =some(select 产地 from 商品表 2 where 品牌 = 春兰 ) 查询结果 P99

67 4 WHERE 选项 例 4-20 从教学库中查询出选修了课程名为 C++ 语言 的所有学生的姓名和成绩 该查询采用双重查询语句处理 在外查询中需投影出学生. 姓名和选课. 成绩, 所以需要对学生和选课关系进行等值连接, 该查询的筛选条件是选课关系的课程号必须等于 C++ 语言 课程的课程号, 而 C++ 语言 课程的课程号可以通过对课程关系的子查询求出 从教学库中查询出选修了课程名为 C++ 语言 的所有学生的姓名和成绩 SELECT 姓名, 成绩 FROM 学生 x inner join 选课 y on x. 学生号 = y. 学生号 Where 课程号 =any (select 课程号 from 课程 where 课程名 = C++ 语言 ) 查询结果 P99

68 4 WHERE 选项 例 4-21 从商品库中查询出所有商品中单价最高的商品和单价最低的商品 SELECT * FROM 商品表 1 Where 单价 =any(select max( 单价 ) from 商品表 1)or 单价 =any(select min( 单价 ) from 商品表 1) 此查询只涉及到商品表 1, 要查询出单价最高和单价最低的商品, 首先必须知道单价最高值和最低值, 然后用每种商品的单价同其比较, 符合则选择出来 采用双重查询实现 查询结果 P100 从商品库中查询出所有商品中单价最高的商品和单价最低的商品 SELECT * FROM 商品表 1 Where 单价 =any(select max( 单价 ) from 商品表 1)union 单价 =any(select min( 单价 ) from 商品表 1) 该查询中的子查询是两个查询的并 查询结果 P100

69 4 WHERE 选项 3. 格式之三 < 列名 > [NOT] BETWEEN < 开始值 > AND < 结束值 > 与下面的逻辑表达式等效 : < 列名 > >= < 开始值 > AND < 列名 > <= < 结束值 > ( 不选 NOT) < 列名 > < < 开始值 > OR < 列名 > > < 结束值 > ( 选 NOT) 例 4-22 从商品表 1 中查询出单价在 1000 到 2000 元之间的所有商品 SELECT * FROM 商品表 1 Where 单价 between 1000 and 2000 查询结果 P100

70 4 WHERE 选项 4. 格式之四 [NOT] EXISTS (< 子查询 >) 当 < 子查询 > 的查询结果中至少存在一个元组时, 表明查询结果非空, 此判断式为真 带 NOT 关键字, 则相反 表明查询结果为空, 此判断式为真 例 4-23 从教学库中查询出选修至少一门课程的所有学生 SELECT * FROM 学生 Where exits(select * from 选课 where 选课. 学生号 = 学生. 学生号 ) 查询结果 P100 若要查询出没有选修任何课程的学生, 只要在该查询语句中加入 NOT 关键字

71 4 WHERE 选项 例 4-24 从教学库中查询出与姓名为 王明 的学生选课至少有一门相同的所有学生 SELECT * FROM 学生 x Where x. 姓名 <> 王明 and exits(select y. 课程号 from 选课 y where y. 学生号 =x. 学生号 and y. 课程号 =any (select w. 课程号 from 学生 z, 选课 w where z. 学生号 =w. 学生号 and z. 姓名 = 王明 )) 查询结果 P100 若在该查询语句中加入 NOT 关键字, 则查询出与王明同学的选课完全不同的所有学生 此查询也可通过如下语句实现 SELECT * FROM 学生 x Where x. 姓名 <> 王明 and x. 学生号 =any (select y. 学生号 from 选课 y, 选课 z where y. 课程号 =z. 课程号 and z. 学生号 =any(select w. 学生号 from 学生 w where w. 姓名 = 王明 ))

72 4 WHERE 选项 例 4-25 从教学库中查询出选修了课程表中所有课程的学生 SELECT * FROM 学生 Where not exits(select * from 课程 where not exits (select * from 选课 where 学生. 学生号 = 选课. 学生号 and 课程. 课程号 = 选课. 课程号 )) 查询结果 P102

73 4 WHERE 选项 5. 格式之五 < 列名 > [NOT] IN {(< 常量表 >) (< 子查询 >)} 当 < 列名 > 所指定列的当前值包含在由 < 常量表 > 所给定的值之内时, 则此判断式为真, 否则为假 若在 IN 关键字后面不是使用 < 常量表 >, 而是使用 < 子查询 >, 则当由 < 列名 > 所指定列的当前值包含在子查询结果之中时, 判断式为真, 否则为假 若选用了 NOT 关键字, 则判断结果正好相反 该格式若为 < 列名 > IN (< 子查询 >), 则与 < 列名 > =any (< 子查询 >) 格式等价

74 4 WHERE 选项 例 4-26 从学生表中查询出专业为计算机 电气 通信的所有学生 SELECT * FROM 学生 Where 专业 in ( 计算机, 电气, 通信 ) 查询结果 P102 例 4-27 从教学库中查询出选修了课程名为 操作系统 的所有学生 SELECT * FROM 学生 Where 学生号 in (select 学生号 from 选课, 课程 where 选课. 课程号 = 课程. 课程号 and 课程名 = 操作系统 ) 查询结果 P103

75 4 WHERE 选项 5. 格式之六 < 字符型列名 > [NOT] LIKE < 字符表达式 > 当 < 字符型列名 > 的当前值与 < 字符型表达式 > 的值相匹配时, 此判断式为真 带 NOT 关键字, 则相反 通常所使用的 < 字符型表达式 > 为字符常量, 若在其中使用下划线 (_) 则表示能和任何一个字符匹配, 若使用百分号 (%) 则表示能和任意多个 ( 含零个 ) 任意字符相匹配

76 4 WHERE 选项 例 4-28 从商品表 1 中查询出商品代号以字符串 dsj 开头 的所有商品 SELECT * FROM 商品表 1 Where 商品代号 like dsj% 查询结果 P103 SELECT * FROM 商品表 1 Where 商品代号 like dsj_1%

77 5 GROUP BY 选项 该选项中的 < 分组列名 1> < 分组列名 2> 等必须是出现在 SELECT 选项中的被投影的表达式所指定的列名 语句执行时将按该选项中给定的分组列 ( 通常只有一个 ) 对连接和选择后得到的所有元组进行分组, 使得分组列值相同的元组为一组, 形成结果表中的一个元组 当选项中含有多个分组列时, 则首先按第一个列值进行分组, 若第一个列值相同, 再按第二个列值进行分组 通常在 SELECT 选项中使用列函数对列值相同的每一组进行有关统计

78 5 GROUP BY 选项 例 4-29 从学生表中查询出每个专业的学生数 SELECT 专业 as 专业名,count( 专业 ) 学生数 FROM 学生 Group by 专业该语句首先按学生专业属性的值进行分组, 然后对每个分组选择并投影出一个元组, 该元组包含专业值和该专业值的个数 查询结果 P103

79 5 GROUP BY 选项 例 4-30 从教学库中查询出每个学生的学生号 姓名及所选课程的门数 SELECT x. 学生号,y. 姓名,count(x. 学生号 ) 选课门数 FROM 选课 x, 学生 y Where x. 学生号 = y. 学生号 Group by x. 学生号,y. 姓名 查询结果 P104 学生号姓名选课门数

80 5 GROUP BY 选项 例 4-31 均价 从商品表 1 中查询出每一类 ( 即分类名相同 ) 商品的最高价 最低价和平 SELECT 分类名,max( 单价 ) as 最高价,min( 单价 ) as 最低价,avg( 单价 ) as 平均价 FROM 商品表 1 Group by 分类名 查询结果 P105 分类名最高价最低价平均价

81 6 HAVING 选项 该选项中的 < 逻辑表达式 > 是一个筛选条件 该选项通常跟在 GROUP BY 子句后面用来从分组统计中筛选出部分统计结果, 因此该选项中的逻辑表达式通常带有字段函数 例 4-32 从学生表中查询出专业的学生数多于 1 人的专业名及人数 SELECT 专业 as 专业名,count( 专业 ) 学生数 FROM 学生 Group by 专业 having count( 专业 )>1 查询结果 P105 专业名 学生数

82 6 HAVING 选项 例 4-33 从教学库中查询出选修课程超过 1 门的学生 SELECT * FROM 学生 Where 学生号 in(select 学生号 from 选课 Group by 学生号 having count( 学生号 )>1) 查询结果 P106

83 6 HAVING 选项 例 4-34 从教学库中查询出选课门数超过学生号为 学生的选课门数的所有学生 SELECT * FROM 学生 Where 学生号 in (select 学生号 from 选课 Group by 学生号 having count( 学生号 )>( select count(*) from 选课 Where 学生号 = )) 查询结果 P106 若将 > 改为 <, 则能够查询出选课门数小于学生号为 学生的选课 门数的所有学生

84 7 ORDER BY 选项 该选项中的 < 排序列名 1>, < 排序列名 2> 是需要使查询结果按其进行排序的列 它们可以是源表中的列名, 也可以是 SELECT 选项中所给表达式的顺序号 ( 即对应查询结果中的列号 ) 或定义的列名 对于每个排序, 还可以指定排序方式 :ASC 升序,DESC 降序, 默认升序排列 该选项只能用在最外层的查询语句中, 不能在子查询中使用 例 4-35 从商品表 1 中按单价升序查询出所有商品记录 SELECT * FROM 商品表 1 order by 单价 查询结果 P105

85 7 ORDER BY 选项 例 4-36 从商品表 1 中查询出单价比平均价高的所有商品, 并使结果按降序排列 SELECT * FROM 商品表 1 Where 单价 >all(select avg( 单价 ) from 商品表 1) order by 单价 desc 查询结果 P107 例 4-37 从教学库中查询出所有学生的学号及所选课的门数, 按门数升序排列结果 SELECT 学生. 学生号,count( 学生, 学生号 ) as 选课门数 FROM 学生, 选课 Where 学生. 学生号 = 选课. 学生号 Group by 学生. 学生号 Order by 选课门数 查询结果 P106

86 7 ORDER BY 选项 例 4-38 从教学库中查询出所有学生的信息即所选课程的门数, 按门数升序排列结果 SELECT 学生.*,count( 学生. 学生号 ) as 选课门数 FROM 学生, 选课 Where 学生. 学生号 = 选课. 学生号 Group by 学生. 学生号, 姓名, 性别, 专业 Order by 选课门数 查询结果 P108 也可以采用如下查询语句 也可以采用如下查询语句 SELECT 学生.*, 选课门数 FROM 学生,(select 学生号,count( 学生号 ) 选课数 from 选课 group by 学生号 )as alias Where 学生. 学生号 =alias. 学生号 Order by 选课门数 查询结果 P108

87 8 INTO 选项 该选项能够根据查询结果自动建立一个基本表, 通常作为临时表使用 可以根据需要任意建立和删除 例 4-35 从教学库中查询出每个学生的学生号 选课门数和总成绩, 把查询结果保存到学生选课门数及总成绩统计表中 SELECT 学生. 学生号,count( 学生. 学生号 ) as 选课门数 sum( 成绩 ) as 总成绩 Into 学生选课门数及总成绩统计表 FROM 学生, 选课 Where 学生. 学生号 = 选课. 学生号 Group by 学生. 学生号 查询结果 P109

88 1. 关系数据结构 小结 : 关系运算 要明确域 笛卡儿积 关系 关系模式 码等的概念 2. 关系完整性 实体完整性 参照完整性 用户定义的完整性 3. 关系代数 传统的集合运算 : 并 交 差 笛卡儿积 ; 专门的关系运算 : 选择 投影 连接

89 思考 : 习题四 P110

90 谢谢观看