数据库系统原理 Database System Principles 四川大学计算机学院 段磊 leiduan@scu.edu.cn 2014.9
第三章关系数据库标准语言 SQL SQL 是 Structured Query Language 的缩写 最新的标准是 SQL2003 使用最广的标准是 SQL99 包含了很多核心 SQL 以外的包 增加对递归查询 临时视图的支持 增加对触发器的支持 现在商品化 DBMS 一般都支持 SQL92 规范的超集 / 子集
本章目录 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 SQL 查询 3.5 数据更新 3.6 视图 3.7 嵌入式 SQL 2014-10-09 数据库系统概论 - 第 3 章 3/198
3.1 SQL 概述 SQL 介于关系代数与关系演算之间 3.1.1 SQL 的特点 SQL 集数据查询 数据操纵 数据定义和数据控制功能于一体 其综合 强大 简洁使其成为国际标准 2014-10-09 数据库系统概论 - 第 3 章 4/198
3.1.1 SQL 的特点一 综合统一 1. SQL 集 DDL DML DCL 功能于一体 2. SQL 数据操作符的统一 查询 插入 删除 修改都只有一种操作符 2014-10-09 数据库系统概论 - 第 3 章 5/198
3.1.1 SQL 的特点 二 高度非过程化 SQL 进行数据操作, 只要提出 What to do, 无需告诉系统 How to do 充分体现关系系统的特点和优势 有利于提高数据的独立性 2014-10-09 数据库系统概论 - 第 3 章 6/198
3.1.1 SQL 的特点 三 面向集合的操作方式 关系运算 一次一集合 方式的体现 2014-10-09 数据库系统概论 - 第 3 章 7/198
3.1.1 SQL 的特点 四 一种语法结构 两种使用方式 既是自含式语言, 又是嵌入式语言 2014-10-09 数据库系统概论 - 第 3 章 8/198
3.1.1 SQL 的特点 五 语言简捷, 易学易用 SQL 设计巧妙, 核心功能只需 9 个动词 在语言上接近英语 数据查询 select 数据定义 create, drop, alter 数据操纵 insert, update, delete 数据控制 grant, revoke 2014-10-09 数据库系统概论 - 第 3 章 9/198
3.1.2 SQL 的基本概念 SQL 对象与三级模式结构的对应关系 外模式 -- 视图 (view) 部分基本表 模式 -- 基本表 (base table) 内模式 -- 存储文件 (stored file) 用户可用 SQL 对基本表和视图进行查询或其他操作, 基本表和视图一样, 都是关系 2014-10-09 数据库系统概论 - 第 3 章 10/198
3.1.2 SQL 的基本概念 基本表是本身独立存在的表, 在 SQL 中一个关系就对应一个表 一些 DBMS 中一个基本表对应一个存储文件, 有的 DBMS 一个数据库中的多个基本表对应一个存储文件 一个表可以带若干索引, 索引也存放在存储文件中 2014-10-09 数据库系统概论 - 第 3 章 11/198
3.1.2 SQL 的基本概念 存储文件的逻辑结构组成了关系数据库的内模式 存储文件的物理文件结构是任意的, 对用户透明 2014-10-09 数据库系统概论 - 第 3 章 12/198
3.1.2 SQL 的基本概念 视图是从基本表或其他视图中导出的表 它本身不独立存储在数据库中, 也就是说数据库中只存放视图的定义而不存放视图对应的数据, 这些数据仍存放在导出视图的基本表中, 因此视图是一个虚表 用户可以用 SQL 语言对视图和基本表进行查询 在用户眼中, 视图和基本表都是关系, 而存储文件对用户是透明的 2014-10-09 数据库系统概论 - 第 3 章 13/198
本章目录 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 SQL 查询 3.5 数据更新 3.6 视图 3.7 嵌入式 SQL 2014-10-09 数据库系统概论 - 第 3 章 14/198
3.2 学生 - 课程数据库 ( 通例 ) 学生表 Student(Sno, Sname, Sgender, Sage, Sdept) 课程表 Course(Cno, Cname, Cpno, Ccredit) 选课表 SC(Sno, Cno, Grade) 2014-10-09 数据库系统概论 - 第 3 章 15/198
本章目录 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 SQL 查询 3.5 数据更新 3.6 视图 3.7 嵌入式 SQL 2014-10-09 数据库系统概论 - 第 3 章 16/198
3.3 数据定义 对模式 基本表 视图 索引的定义 删除和修改 (9 种组合, 不能修改模式 视图和索引 ) 模式 create schema drop schema 表 create table drop table alter table 视图 create view drop view 索引 create index drop index 2014-10-09 数据库系统概论 - 第 3 章 17/198
3.3.1 模式的定义与删除 模式的定义 CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [... ] ] 2014-10-09 数据库系统概论 - 第 3 章 18/198
示例 定义学生课程模式 CREATE SCHEMA S_T; 为用户 WANG 定义模式 WANG CREATE SCHMA AUTHORIZATION WANG; 2014-10-09 数据库系统概论 - 第 3 章 19/198
Tips psql 下 \dn 命令可以查看当前数据库模式 postgres=# \dn List of schemas Name Owner --------------------+---------- information_schema postgres pg_catalog postgres pg_toast postgres public postgres s_t Admin 2014-10-09 数据库系统概论 - 第 3 章 20/198
3.3.1 模式的定义与删除 删除模式 DROP SCHEMA name [,...] [ CASCADE RESTRICT ] 2014-10-09 数据库系统概论 - 第 3 章 21/198
3.3.2 定义 删除和修改基本表 定义基本表一般格式 CREATE TABLE < 表名 > (< 列名 1>< 数据类型 >[ 列级完整性约束条件 ] [,< 列名 2>< 数据类型 >[ 列级完整性约束条件 ]]... [, 表级完整性约束条件 ]); 2014-10-09 数据库系统概论 - 第 3 章 22/198
3.3.2 定义 删除和修改基本表 数据类型 char(n) 长度为 n 的定长字符串 varchar(n) 最大长度为 n 的变长字符串 int 长整数 smallint 短整数 numeric(p,d) 定点数 (p, d) real 浮点数 date 日期, 格式为 YYYY-MM-DD Time 时间, 格式为 HH:MM:SS 2014-10-09 数据库系统概论 - 第 3 章 23/198
3.3.2 定义 删除和修改基本表 例 : 定义学生表的方法 下面的例子一般为标准 SQL CREATE TABLE student ( sno char(5) PRIMARY KEY, sname varchar(20) UNIQUE, sgender char(1), sage int, sdept char(15) ); 2014-10-09 数据库系统概论 - 第 3 章 24/198
3.3.2 定义 删除和修改基本表 略去 course 表, 下面定义 SC 表 CREATE TABLE SC( sno char(5), cno char(5), grade int, PRIMARY KEY (sno,cno), FOREIGN KEY (sno) REFERENCES student, FOREIGN KEY (cno) REFERENCES course ); 2014-10-09 数据库系统概论 - 第 3 章 25/198
3.3.2 定义 删除和修改基本表 约束更完整的形式 : CREATE TABLE SC( sno char(5), cno char(5), grade int, CONSTRAINT PK_sno_cno PRIMARY KEY (sno,cno), CONSTRAINT FK_sno FOREIGN KEY (sno) REFERENCES student, CONSTRAINT FK_cno FOREIGN KEY (cno) REFERENCES course, CONSTRAINT CK_grade CHECK (grade>=0 and grade <=100) ); 2014-10-09 数据库系统概论 - 第 3 章 26/198
3.3.2 定义 删除和修改基本表 上述例子可以看出 SQL 基本表定义的概貌 应注意完整性约束的定义 主码 :PRIMARY KEY 关键字 外码 :FOREIGN KEY 关键字 用户定义 :CHECK 关键字 2014-10-09 数据库系统概论 - 第 3 章 27/198
Tips postgresql 中 \d tablename 可以查看表的定义 postgres=# \d t Table "public.t" Column Type Modifiers --------+---------+----------- a integer not null b date Indexes: "t_pkey" PRIMARY KEY, btree (a) 2014-10-09 数据库系统概论 - 第 3 章 28/198
3.3.2 定义 删除和修改基本表 模式与表 每一个基本表属于某一个模式, 一个模式包含多个基本表 定义基本表所属模式 : 1. 在表名中明显地给出模式名 2. 在创建模式语句中同时创建表 3. 设置所属的模式, 在创建表时表名中不必给出模式名 2014-10-09 数据库系统概论 - 第 3 章 29/198
3.3.2 定义 删除和修改基本表 修改基本表 可增加新列, 删除列上的完整性约束, 修改列名及数据类型 ALTER TABLE < 表名 > [ADD < 新列名 >< 数据类型 >[ 完整性约束 ]] [DROP < 完整性约束名 >] [MODIFY < 列名 >< 数据类型 >]; 2014-10-09 数据库系统概论 - 第 3 章 30/198
补充 删除列 DROP [ COLUMN ] column [ RESTRICT CASCADE ] 增加表级完整性约束 ADD table_constraint 列更名 ALTER TABLE name RENAME [ COLUMN ] column TO new_column 2014-10-09 数据库系统概论 - 第 3 章 31/198
补充 PostgreSQL \h alter table cs2009=# \h alter table Command: ALTER TABLE Description: change the definition of a table Syntax: ALTER TABLE [ ONLY ] name [ * ] action [,... ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name ALTER TABLE name SET SCHEMA new_schema 2014-10-09 数据库系统概论 - 第 3 章 32/198
补充 PostgreSQL \h alter table where action is one of: ADD [ COLUMN ] column type [ column_constraint [... ] ] DROP [ COLUMN ] column [ RESTRICT CASCADE ] ALTER [ COLUMN ] column TYPE type [ USING expression ] ALTER [ COLUMN ] column SET DEFAULT expression ALTER [ COLUMN ] column DROP DEFAULT ALTER [ COLUMN ] column { SET DROP } NOT NULL ALTER [ COLUMN ] column SET STATISTICS integer ALTER [ COLUMN ] column SET STORAGE { PLAIN EXTERNAL EXTENDED MAIN } ADD table_constraint DROP CONSTRAINT constraint_name [ RESTRICT CASCADE ] DISABLE TRIGGER [ trigger_name ALL USER ] ENABLE TRIGGER [ trigger_name ALL USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name 2014-10-09 数据库系统概论 - 第 3 章 33/198
补充 PostgreSQL \h alter table DISABLE RULE rewrite_rule_name ENABLE RULE rewrite_rule_name ENABLE REPLICA RULE rewrite_rule_name ENABLE ALWAYS RULE rewrite_rule_name CLUSTER ON index_name SET WITHOUT CLUSTER SET WITHOUT OIDS SET ( storage_parameter = value [,... ] ) RESET ( storage_parameter [,... ] ) INHERIT parent_table NO INHERIT parent_table OWNER TO new_owner SET TABLESPACE new_tablespace 2014-10-09 数据库系统概论 - 第 3 章 34/198
思考 : 如何解决多个表的相互引用问题? 示例 教师表 Teacher(tno, name, dept) 院系表 Dept(dno, name, head) 教师表的 dept 引用 Dept 的 dno, 而院系表的 head 又引用了 Teacher 的 tno, 如何定义? 2014-10-09 数据库系统概论 - 第 3 章 35/198
思考 : 如何解决多个表的相互引用问题? 解决方案一 : create table teacher ( tno char(4) primary key, name varchar(15), dept char(4) foreign key references dept (dno) ); 类似定义 dept 有何问题? 2014-10-09 数据库系统概论 - 第 3 章 36/198
正确解决方案 create table teacher ( tno char(4) primary key, name varchar(15), dept char(4) ); create table dept ( dno char(4) primary key, name varchar(20), head char(4)); 2014-10-09 数据库系统概论 - 第 3 章 37/198
正确解决方案 alter table teacher add constraint dept_fkey foreign key (dept) references dept(dno); alter table dept add constraint head_fkey foreign key (head) references teacher(tno); 2014-10-09 数据库系统概论 - 第 3 章 38/198
3.3.2 定义 删除和修改基本表 删除基本表 DROP TABLE < 表名 >; 删除基本表后, 引用建立在此表上的视图可能出现问题 2014-10-09 数据库系统概论 - 第 3 章 39/198
3.3.3 建立和删除索引 作用 : 提高查询速度 如从 O(n) 到 O(log 2n ) 常需要建立索引的属性 常作为连接属性 常出现在查询条件中 2014-10-09 数据库系统概论 - 第 3 章 40/198
3.3.3 建立和删除索引 建立索引 CREATE [UNIQUE][CLUSTERED NONCLUSTERED] INDEX < 索引名 > ON < 表名 > (< 列名 > [< 次序 >,[ ]]); 2014-10-09 数据库系统概论 - 第 3 章 41/198
3.3.3 建立和删除索引 UNIQUE( 单一索引 ): 唯一索引, 不允许存在索引值相同的两行 CLUSTERED( 聚集索引 ): 索引项的顺序与表中记录的物理顺序一致 表中如果有多个记录在索引字段上相同, 这些记录构成一簇, 只有一个索引值 优点 : 查询速度快 缺点 : 维护成本高, 且一个表只能建一个聚簇索引 NONCLUSTERED( 非聚集索引 ) 作为非聚集索引, 行的物理排序独立于索引排序 非聚集索引的叶级包含索引行 (B+ 树 ) 2014-10-09 数据库系统概论 - 第 3 章 42/198
3.3.3 建立和删除索引 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC); 2014-10-09 数据库系统概论 - 第 3 章 43/198
3.3.3 建立和删除索引 删除索引 DROP INDEX < 索引名 >; 例删除 Student 表的 Stusname 索引 DROP INDEX Stusname; 2014-10-09 数据库系统概论 - 第 3 章 44/198
本章目录 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 SQL 查询 3.5 数据更新 3.6 视图 3.7 嵌入式 SQL 2014-10-09 数据库系统概论 - 第 3 章 45/198
3.4 SQL 查询 要求重点掌握 比较困难的是 2-3 层的嵌套查询 一般格式 : SELECT [ALL DISTINCT]< 目标列表达式 >[, 目标列表达式 ] FROM < 表名或视图名 >[, < 表名或视图名 >] [WHERE < 条件表达式 >] [GROUP BY < 列名 1> [HAVING < 条件表达式 >]] [ORDER BY < 列名 2> [ASC DESC]]; 2014-10-09 数据库系统概论 - 第 3 章 46/198
3.4 SQL 查询 简单的 SQL 查询与关系代数的联系 : SELECT A1,A2,,An FROM T1,T2,..,Tk WHERE F; 相当于 : A1,A2,,An δ F (T1 T2 Tk) 其中 F 中有的可能是连接条件, 与后面的广义笛卡儿集构成连接 2014-10-09 数据库系统概论 - 第 3 章 47/198
3.4 SQL 查询 含义 : 根据 WHERE 子句的条件表达式, 从 FROM 子句指定的基本表或视图中找出满足条件的元组, 再按 SELECT 子句中的目标列表达式, 选出元组中的属性值形成结果表 如果有 GROUP 子句, 则将 WHERE 选出元组按 < 列名 1> 的值进行分组, 即将该属性列值相等的元组分为一个组, 每个组产生结果表中的一条记录 通常会在每组中作用集函数 如果 GROUP 子句带 HAVING 短语, 则只有满足指定条件的组才予输出 如果有 ORDER 子句, 则结果表还要按 < 列名 2> 的值的升序或降序排序 2014-10-09 数据库系统概论 - 第 3 章 48/198
3.4.1 单表查询 比较简单 一 选择表中的若干列 1. 查询指定列 例 1 查询全体学生的学号与姓名 SELECT Sno,Sname FROM Student; < 目标列表达式 > 中各个列的先后顺序可以与表中的顺序不一致 也就是说, 用户在查询时可以根据应用的需要改变列的显示顺序 2014-10-09 数据库系统概论 - 第 3 章 49/198
3.4.1 单表查询 2. 查询全部列 例 3 查询全体学生的详细记录 SELECT * FROM Student; 该 SELECT 语句实际上是无条件地把 Student 表的全部信息都查询出来, 所以也称为全表查询, 这是最简单的一种查询 2014-10-09 数据库系统概论 - 第 3 章 50/198
3.4.1 单表查询 3. 查询经过计算的值 < 目标列表达式 > 不仅可以使用列名, 还可以使用算术表达式 字符串常量和函数等 例 5 查全体学生的姓名 出生年份和所有系, 要求用小写字母表示所有系名 SELECT Sname, 'Year of Birth:', 2004-Sage, LOWER(Sdept) FROM Student; 2014-10-09 数据库系统概论 - 第 3 章 51/198
3.4.1 单表查询 改变列标题 (SQL 标准用 AS 关键字 ) SELECT Sname AS Name, 'Year of Birth:' AS Birth, 2004-Sage AS BirthYear, LOWER(Sdept) AS Department FROM Student; 2014-10-09 数据库系统概论 - 第 3 章 52/198
3.4.1 单表查询二 选择表中的若干元组 1. 消除取值重复的行 ( 使用 DISTINCT) 例 6 查所有选修过课的学生的学号 SELECT Sno FROM SC; 结果中有重复的行 (SFW 默认允许重复 ) 一个学生选多少门课, 其学号就出现多少次 2014-10-09 数据库系统概论 - 第 3 章 53/198
3.4.1 单表查询 DISTINCT 用于消除重复 SELECT DISTINCT Sno FROM SC; 即可消除重复 2014-10-09 数据库系统概论 - 第 3 章 54/198
3.4.1 单表查询 2. 查询满足条件的元组 查询满足指定条件的元组可以通过 WHERE 子句实现 比较常用 =,>,<,>=,<=,!= 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN,NOT IN 空值 IS NULL, IS NOT NULL 多重条件 AND, OR, NOT 2014-10-09 数据库系统概论 - 第 3 章 55/198
3.4.1 单表查询 (1) 比较大小 例 7 查询计算机系全体学生的名单 SELECT Sname FROM Student WHERE Sdept = CS ; 2014-10-09 数据库系统概论 - 第 3 章 56/198
3.4.1 单表查询 补充例 : 查询选修 2 号课程成绩在 90 分以上 ( 含 ) 的学生的学号 SELECT Sno FROM SC WHERE Cno = 2 AND Grade>=90; 2014-10-09 数据库系统概论 - 第 3 章 57/198
3.4.1 单表查询 (2) 确定范围 BETWEEN AND 与 NOT BETWEEN AND 更接近自然语言, 语义更明确 通常可以表示为用逻辑与连接的两个比较 2014-10-09 数据库系统概论 - 第 3 章 58/198
3.4.1 单表查询 例 10 查询年龄在 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; 2014-10-09 数据库系统概论 - 第 3 章 59/198
3.4.1 单表查询 (3) 确定集合 IN 与 NOT IN 前面的例子还可表示为 : SELECT Sname, Sdept, Sage FROM Student WHERE Sage IN (20,21,22,23); 2014-10-09 数据库系统概论 - 第 3 章 60/198
3.4.1 单表查询 例 13 查询既不是计算机科学系 数学系, 也不是信息系的学生的姓名和性别 SELECT Sname, Sgender FROM Student WHERE Sdept NOT IN ( CS, MA, IS ); 2014-10-09 数据库系统概论 - 第 3 章 61/198
3.4.1 单表查询 例 13 查询既不是计算机科学系 数学系, 也不是信息系的学生的姓名和性别 SELECT Sname, Sgender FROM Student WHERE Sdept NOT IN ( CS, MA, IS ); 有没有问题? 2014-10-09 数据库系统概论 - 第 3 章 62/198
空值 (NULL) 的影响 实际上如果某些学生的 Sdept 为空 (NULL), 其学号姓名不会出现在结果中, 如果把 NOT IN 改为 IN, 这些学生的信息也不会出现在结果中! 在有 NULL 的情况下, 二值逻辑转化为三值逻辑,unknown 介于 true 和 false 之间 只有使 WHERE 条件为 true 的元组才被选出 2014-10-09 数据库系统概论 - 第 3 章 63/198
根据语义予以解决 选用修饰 IS TRUE IS NOT TRUE IS FALSE IS NOT FALSE SELECT Sname, Sgender FROM Student WHERE Sdept NOT IN ( CS, MA, IS ) IS NOT FALSE; 2014-10-09 数据库系统概论 - 第 3 章 64/198
根据语义予以解决 (4) 字符匹配 LIKE 通配符 % 和 _ 用法 : 相当于 Windows dos 等中查文件名的 * 和? 新的问题 : 如何表示 % 和 _ 本身? 2014-10-09 数据库系统概论 - 第 3 章 65/198
根据语义予以解决 (4) 字符匹配 LIKE 通配符 % 和 _ 用法 : 相当于 Windows dos 等中查文件名的 * 和? 新的问题 : 如何表示 % 和 _ 本身? 用 ESCAPE 换码转义 2014-10-09 数据库系统概论 - 第 3 章 66/198
根据语义予以解决 表示 % 和 _ 本身的方法 例 19 查询 DB_Design 课程的课程号和学分 SELECT Cno, Ccredit FROM Course WHERE Cname LIKE DB\_Design ESCAPE \ ; 2014-10-09 数据库系统概论 - 第 3 章 67/198
根据语义予以解决 书上用 \ 换码转义 不幸,postgres 恰好不能用 \ 实际上可以更灵活 ( 常用 #,&,! 等, 甚至用字母都可以!) 如下面的! 转义 : SELECT c1 FROM tb WHERE c1 LIKE '%10-15!% off%' ESCAPE '!' ; 2014-10-09 数据库系统概论 - 第 3 章 68/198
3.4.1 单表查询 (5) 涉及空值的查询 IS NULL 和 IS NOT NULL ( 注意 : 有的系统允许 NULL 比较, 可以使用 = 和 <>, 如 MS SQL Server 有的不允许, 如 Oracle SQL 标准不允许 ) 2014-10-09 数据库系统概论 - 第 3 章 69/198
3.4.1 单表查询 例 21 查询缺少成绩的学生的学号及相应课程号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL; 2014-10-09 数据库系统概论 - 第 3 章 70/198
3.4.1 单表查询 (6) 多重条件 ( 逻辑连接 ) 查询 WHERE 中用 AND 和 OR 连接多个条件 例 : 查询计算机科学系或数学系年龄在 18 到 23 岁的学生的姓名 SELECT Sname FROM Student WHERE (Sdept= CS OR Sdept= MA ) and Sage BETWEEN 18 AND 23; 2014-10-09 数据库系统概论 - 第 3 章 71/198
补充集合操作 关系代数中的,, 在 SQL 中的对应 并 UNION 交 INTERSECT 差 EXCEPT 两条 SQL 语句间使用 默认不消重 2014-10-09 数据库系统概论 - 第 3 章 72/198
补充集合操作 例查询数学系选了 3 号课程的学生的学号 (SELECT Sno FROM Student WHERE Sdept= MA ) INTERSECT (SELECT Sno FROM SC WHERE Cno= 3 ); 2014-10-09 数据库系统概论 - 第 3 章 73/198
补充集合操作 查询选了 1 号课程但是没有选 2 号课程的学生的学号 (SELECT Sno FROM SC WHERE Cno= 1 ) EXCEPT (SELECT Sno FROM SC WHERE Cno= 2 ); 2014-10-09 数据库系统概论 - 第 3 章 74/198
3.4.1 单表查询 三. ORDER BY 子句 ( 查询结果排序 ) 如果没有指定查询结果的显示顺序,DBMS 将按其最方便的顺序 ( 通常是元组在表中的先后顺序 ) 输出查询结果 ORDER BY 子句可明确指定结果序 默认为升序 (ASC),NULL 值最大 2014-10-09 数据库系统概论 - 第 3 章 75/198
3.4.1 单表查询 例 : 查询计算机系 (CS) 学生的学号和姓名, 按年龄从大到小排, 相同年龄的按学号升序排 SELECT Sno, Sname FROM Student WHERE Sdept= CS ORDER BY Sage DESC, Sno; 2014-10-09 数据库系统概论 - 第 3 章 76/198
3.4.1 单表查询 注 : 这里说 NULL 值最大, 仅仅针对 NULL 值排序的情况 如果取 NULL 值 的字段出现在条件表达式中, 将使条件计算为 NULL, 进而被排除于结果外 例如, 查找成绩在 90 分以上 ( 含 ) 的学生的学号 : SELECT Sno FROM SC WHERE Grade>=90; 成绩为 NULL 的学生的学号并不出现在结果中 2014-10-09 数据库系统概论 - 第 3 章 77/198
3.4.1 单表查询 四 使用集函数为了进一步方便用户, 增强检索功能,SQL 提供了许多集函数, 主要包括 : COUNT([DISTINCT ALL] *) 统计元组个数 COUNT([DISTINCT ALL] < 列名 >) 统计一列中值的个数 SUM([DISTINCT ALL] < 列名 >) 计算一列值的总和 ( 此列必须是数值型 ) AVG([DISTINCT ALL] < 列名 >) 计算一列值的平均值 ( 此列必须是数值型 ) MAX([DISTINCT ALL] < 列名 >) 求一列值中的最大值 MIN([DISTINCT ALL] < 列名 >) 求一列值中的最小值 2014-10-09 数据库系统概论 - 第 3 章 78/198
3.4.1 单表查询 如果指定 DISTINCT 短语, 则表示在计算时要取消指定列中的重复值 如果不指定 DISTINCT 短语或指定 ALL 短语 (ALL 为缺省值 ), 则表示不取消重复值 例 : 查询学生总人数 SELECT COUNT (*) FROM student; 或 SELECT COUNT (Sno) FROM student; 2014-10-09 数据库系统概论 - 第 3 章 79/198
3.4.1 单表查询 例 26 查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC; 学生每选修一门课, 在 SC 中都有一条相应的记录, 而一个学生一般都要选修多门课程, 为避免重复计算学生人数, 必须在 COUNT 函数中用 DISTINCT 短语 注 :NULL 值的影响 : COUNT(*) 总是返回记录的个数 COUNT( 字段 ) 返回指定字段值非空的记录个数 2014-10-09 数据库系统概论 - 第 3 章 80/198
3.4.1 单表查询 例如, 求 2 号课程总共有多少个得分 : SELECT COUNT(*) FROM SC WHERE Cno= 2 ; 结果中包含成绩为空的记录, 而 SELECT COUNT(Grade) FROM SC WHERE Cno= 2 ; 结果中则不包含成绩为空的记录 2014-10-09 数据库系统概论 - 第 3 章 81/198
3.4.1 单表查询 实际上, 除 count(*) 外,NULL 值均被聚集函数所忽略 postgres=# select * from t1; a b ---+---- 1 20 1 30 1 40 2 2014-10-09 数据库系统概论 - 第 3 章 82/198
3.4.1 单表查询 postgres=# select avg (b) from t1; avg ---------------------------- 30.0000000000000000 显然, 第四行对结果毫无影响 2014-10-09 数据库系统概论 - 第 3 章 83/198
3.4.1 单表查询 五 对查询结果分组 GROUP BY 子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组 对查询结果分组的目的是为了细化集函数的作用对象 如果未对查询结果分组, 集函数将作用于整个查询结果, 即整个查询结果只有一个函数值 否则, 集函数将作用于每一个组, 即每一组都有一个函数值 2014-10-09 数据库系统概论 - 第 3 章 84/198
3.4.1 单表查询 注 :SQL 规定, 所有带有 NULL 值的记录在分组时被作为一组 例, 对选 2 号课程的学生进行成绩分组, 计算每一成绩的人数 SELECT COUNT(*),Grade FROM SC WHERE Cno='2' GROUP BY Grade; 2014-10-09 数据库系统概论 - 第 3 章 85/198
3.4.1 单表查询 注 : 分组后, 一些详细信息可能损失, 不能出现在 SELECT 结果中 例如, 下面的查询 SELECT Sno,COUNT(*),Grade FROM SC WHERE Cno='2' GROUP BY Grade; 将出错, 想一想, 为什么? 2014-10-09 数据库系统概论 - 第 3 章 86/198
3.4.1 单表查询 一般来说, 分组查询的 SELECT 目标列中只允许出现聚集函数和 GROUP BY 子句中出现过的列 HAVING 条件针对的是 GROUP 分组, WHERE 针对的是元组, 两者并不矛盾 2014-10-09 数据库系统概论 - 第 3 章 87/198
3.4.1 单表查询 HAVING 条件针对的是 GROUP 分组, WHERE 针对的是元组, 两者并不矛盾 例 : 查询有三科或三科以上成绩在 80 分以上的学生学号 SELECT Sno FROM SC WHERE Grade>=80 GROUP BY Sno HAVING (COUNT(Cno)>=3); 2014-10-09 数据库系统概论 - 第 3 章 88/198
3.4.2 连接查询 一个数据库中的多个表之间一般都存在某种内在联系, 它们共同提供有用的信息 前面的查询都是针对一个表进行的 若一个查询同时涉及两个以上的表, 则称之为连接查询 连接查询主要包括等值连接 非等值连接查询 自身连接查询 外连接查询和复合条件连接查询 2014-10-09 数据库系统概论 - 第 3 章 89/198
3.4.2 连接查询 一 等值与非等值连接查询用来连接两个表的条件称为连接条件或连接谓词, 其一般格式为 : [< 表名 1>.]< 列名 1> < 比较运算符 > [< 表名 2>.]< 列名 2> 在实践中比较运算符为 = 的等值连接使用十分广泛 2014-10-09 数据库系统概论 - 第 3 章 90/198
3.4.2 连接查询 连接谓词中的列名称为连接字段 连接条件中的各连接字段类型必须是可比的, 但不必是相同的 例如, 可以都是字符型, 或都是日期型 ; 也可以一个是整型, 另一个是实型, 整型和实型都是数值型, 因此是可比的 但若一个是字符型, 另一个是整数型就不允许了, 因为它们是不可比的类型 2014-10-09 数据库系统概论 - 第 3 章 91/198
3.4.2 连接查询 从概念上讲 DBMS 执行连接操作的过程是, 首先在表 1 中找到第一个元组, 然后从头开始顺序扫描或按索引扫描表 2, 查找满足连接条件的元组, 每找到一个元组, 就将表 1 中的第一个元组与该元组拼接起来, 形成结果表中一个元组 表 2 全部扫描完毕后, 再到表 1 中找第二个元组, 然后再从头开始顺序扫描或按索引扫描表 2, 查找满足连接条件的元组, 每找到一个元组, 就将表 1 中的第二个元组与该元组拼接起来, 形成结果表中一个元组 重复上述操作, 直到表 1 全部元组都处理完毕为止 ( 注意 : 实际上要考虑查询的优化 ) 2014-10-09 数据库系统概论 - 第 3 章 92/198
3.4.2 连接查询 例 33 查询每个学生及其选修课程的情况学生情况存放在 Student 表中, 学生选课情况存放在 SC 表中, 所以本查询要把 Student 与 SC 表的数据通过两个表都具有的属性 Sno ( 外码连接 ) 实现的 这是一个等值连接 完成本查询的 SQL 语句为 : SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno; 2014-10-09 数据库系统概论 - 第 3 章 93/198
3.4.2 连接查询 例 34 自然连接 Student 和 SC 表 SELECT Student.Sno, Sname, Sgender, Sage, Sdept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno; 2014-10-09 数据库系统概论 - 第 3 章 94/198
3.4.2 连接查询 注 : 实际上, 上述作法只是基本连接形式之一, 称为 交叉连接 或 叉积连接 除交叉连接外, 还有 内连接 和 外连接 两种基本形式 2014-10-09 数据库系统概论 - 第 3 章 95/198
3.4.2 连接查询 例如, 例 33 可改为 内连接 形式 (SQL 标准 ): SELECT * FROM Student INNER JOIN SC ON Student.Sno=SC.Sno; 例 34 可改为 SELECT * FROM Student NATURAL INNER JOIN SC; 2014-10-09 数据库系统概论 - 第 3 章 96/198
3.4.2 连接查询 二 自身连接 连接操作不仅可以在两个表之间进行, 也可以是一个表与其自己进行连接, 这种连接称为表的自身连接 例 35 查询每一门课的间接先修课 ( 即先修课的先修课 ) 我们先来分析一下, 题目要求查询每一门课程的先修课的先修课, 在 课程 表即 Course 关系中, 只有每门课的直接先修课信息, 而没有先修课的先修课, 要得到这个信息, 必须先对一门课找到其先修课, 再按此先修课的课程号, 查找它的先修课程 2014-10-09 数据库系统概论 - 第 3 章 97/198
3.4.2 连接查询 我们可以为 Course 表取两个别名, 一个是 FIRST, 另一个是 SECOND, 也可以在考虑问题时就把 Course 表想成是两个完全一样表, 一个是 FIRST 表, 另一个是 SECOND 表 这两个表通过 FIRST 的 Cpno 与 SECOND 的 Cno 等值连接即可达到查询目的 这相当于将 Course 表与其自身连接后, 取第一个副本的课程号与第二个副本的先修课号做为目标列中的属性 2014-10-09 数据库系统概论 - 第 3 章 98/198
3.4.2 连接查询 书上的作法是 : SELECT FIRST.Cno, SECOND. Cpno FROM Course FIRST, Course SECOND WHERE FIRST. Cpno=SECOND.Cno; 2014-10-09 数据库系统概论 - 第 3 章 99/198
3.4.2 连接查询 书上的作法是 : SELECT FIRST.Cno, SECOND. Cpno FROM Course FIRST, Course SECOND WHERE FIRST. Cpno=SECOND.Cno; 这一作法实际上有问题 : 没有先行课的先行课的课程 ( 即某门课的先行课的先行课为 NULL) 也出现在结果中 请思考后, 自己修改正确 2014-10-09 数据库系统概论 - 第 3 章 100/198
3.4.2 连接查询 三 外连接 通常连接操作可能会丢失适配元组 引入外连接 (2.4 关系代数一节已经详细讨论 ) 左外连接 LEFT OUTER JOIN 右外连接 RIGHT OUTER JOIN 全外连接 FULL OUTER JOIN 2014-10-09 数据库系统概论 - 第 3 章 101/198
3.4.2 连接查询 SQL 标准外连接关键字为 OUTER 外连接时,OUTER 常省略 SELECT * FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno FULL JOIN Course ON SC.Cno=Course.Cno; 2014-10-09 数据库系统概论 - 第 3 章 102/198
补充连接的类型与条件 在 from 子句连接 Join type 连接类型 ( 失配元组如何处理 ) Join condition 连接条件 Join Types Join Conditions 等值连接, 且连接的列名相同 inner join left outer join right outer join full outer join natural on <predicate> using (A 1, A 2,..., A n ) 2014-10-09 数据库系统概论 - 第 3 章 103/198
3.4.2 连接查询 四 复合条件连接 上面各个连接查询中,WHERE 子句中只有一个条件, 即用于连接两个表的谓词 WHERE 子句中有多个条件的连接操作, 称为复合条件连接 2014-10-09 数据库系统概论 - 第 3 章 104/198
3.4.2 连接查询 例查询选修 2 号课程且成绩在 90 分以上的信息系或计算机系学生的学号和姓名 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND (Student.Sdept= IS OR Student.Sdept= CS ) AND SC.Cno='2' AND SC.Grade>90; 2014-10-09 数据库系统概论 - 第 3 章 105/198
3.4.3. 嵌套查询 在 SQL 语言中, 一个 SELECT-FROM-WHERE 语句称为一个查询块 将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询或子查询 例如 : SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno= 2 ); 2014-10-09 数据库系统概论 - 第 3 章 106/198
3.4.3. 嵌套查询 通常, 嵌套查询的求解方法是由里向外处理 即每个子查询在其上一级查询处理之前求解, 子查询的结果用于建立其父查询的查找条件 嵌套查询使得可以用一系列简单查询构成复杂的查询, 从而明显地增强了 SQL 的查询能力 以层层嵌套的方式来构造程序正是 SQL (Structured Query Language) 中 结构化 的含义所在 2014-10-09 数据库系统概论 - 第 3 章 107/198
3.4.3. 嵌套查询 一 带有 IN 谓词的子查询 带有 IN 谓词的子查询是指父查询与子查询之间用 IN 进行连接, 判断某个属性列值是否在子查询的结果中 由于在嵌套查询中, 子查询的结果往往是一个集合, 所以谓词 IN 是嵌套查询中最经常使用的谓词 例 39 查询与 刘晨 在同一个系学习的学生 2014-10-09 数据库系统概论 - 第 3 章 108/198
3.4.3. 嵌套查询 查询与 刘晨 在同一个系学习的学生, 可以首先确定 刘晨 所在系名, 然后再查找所有在该系学习的学生 所以可以分步来完成此查询 : 1 确定 刘晨 所在系名 SELECT Sdept FROM Student WHERE Sname=' 刘晨 '; 结果为 : Sdept ---------- IS 2014-10-09 数据库系统概论 - 第 3 章 109/198
3.4.3. 嵌套查询 2 查找所有在 IS 系学习的学生 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept='IS'; 结果为 : Sno Sname Sdept --------- ---------- ------- 95001 刘晨 IS 95004 张立 IS 2014-10-09 数据库系统概论 - 第 3 章 110/198
3.4.3. 嵌套查询 可表为嵌套查询的形式 : SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨 ); 本例中的查询也可以用我们前面学过的表的自身连接查询来完成 2014-10-09 数据库系统概论 - 第 3 章 111/198
3.4.3. 嵌套查询 例 40 查询选修了课程名为 信息系统 的学生学号和姓名 SELECT Sno, Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname= 信息系统 )); 2014-10-09 数据库系统概论 - 第 3 章 112/198
3.4.3. 嵌套查询 例 39 和例 40 中的各个子查询都只执行一次, 其结果用于父查询, 子查询的查询条件不依赖于父查询, 这类子查询称为不相关子查询 不相关子查询是最简单的一类子查询 2014-10-09 数据库系统概论 - 第 3 章 113/198
3.4.3. 嵌套查询 二 带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接 当用户能确切知道内层查询返回的是单值时, 可以用 > < = >= <=!= 或 <> 等比较运算符 2014-10-09 数据库系统概论 - 第 3 章 114/198
3.4.3. 嵌套查询 ( 按书上的说法 ) 在例 39 中, 由于一个学生只可能在一个系学习, 也就是说内查询刘晨所在系的结果是一个唯一值, 因此该查询也可以用比较运算符来实现, 其 SQL 语句如下 : SELECT Sno, Sname, Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 刘晨 ); 2014-10-09 数据库系统概论 - 第 3 章 115/198
3.4.3. 嵌套查询 这一作法可能有问题 : 如果两个或以上系都有叫做 刘晨 的学生, 查询将出错 换一个例子 : 查询选修 2 号课程的成绩最高的学生的学号 SELECT Sno FROM SC WHERE Cno= 2 AND Grade = ( SELECT MAX(Grade) FROM SC WHERE Cno= 2 ); 2014-10-09 数据库系统概论 - 第 3 章 116/198
3.4.3. 嵌套查询 需要注意的是, 子查询一定要跟在比较符之后, 下列写法是错误的 : SELECT Sno, Sname, Sdept FROM Student WHERE (SELECT Sdept FROM Student WHERE Sname=' 刘晨 ') = Sdept; 2014-10-09 数据库系统概论 - 第 3 章 117/198
3.4.3. 嵌套查询 例 40 中信息系统的课程号是唯一的, 但选修该课程的学生并不只一个, 所以例 40 也可以用 = 运算符和 IN 谓词共同完成 : SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno = ( SELECT Cno FROM Course WHERE Cname= 信息系统 )) ; 2014-10-09 数据库系统概论 - 第 3 章 118/198
相关子查询 例 40 找出每个学生超过他选修课程平均成绩的课程号 SELECT Sno, Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) 同一选课 FROM SC y WHERE y.sno=x.sno); 2014-10-09 数据库系统概论 - 第 3 章 119/198
相关子查询 例 40 找出每个学生超过他选修课程平均成绩的课程号 SELECT Sno, Cno FROM SC x 查这样的选课 WHERE Grade>=(SELECT AVG(Grade) FROM SC y WHERE y.sno=x.sno); 2014-10-09 数据库系统概论 - 第 3 章 120/198
相关子查询 例 40 找出每个学生超过他选修课程平均成绩的课程号 SELECT Sno, Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) 该选课成绩比选课学生的平均成绩要高 查这样的选课 FROM SC y WHERE y.sno=x.sno); 2014-10-09 数据库系统概论 - 第 3 章 121/198
相关子查询的一般求解过程 外层每一元组都被带入内层 内层得到结果 外层 WHERE 条件判断 如果为真, 当前元组被选出 ; 否则, 不被选出 重复, 直到外层没有元组为止 2014-10-09 数据库系统概论 - 第 3 章 122/198
3.4.3. 嵌套查询 三. 带有 ANY(SOME) 或 ALL 的子查询 比较运算符一般不能与集合比较 集合前加以谓词 ANY(SOME) 或 ALL 修饰后可以比较 Some R 意义为 Some in R R 中的某一个 All R 意义为 all in R, 或 all of R R 中的全部 2014-10-09 数据库系统概论 - 第 3 章 123/198
3.4.3. 嵌套查询 0 (5< some 5 ) = true 6 (read: 5 < some tuple in the relation) (5<some (5 = some 0 5 0 5 ) = false ) = true (5!= some 0 5 ) = true ( 注意 0 5) (= some) IN 要注意, (!=some) NOT IN 2014-10-09 数据库系统概论 - 第 3 章 124/198
3.4.3. 嵌套查询 0 (5< ALL 5 ) = false 6 (5<ALL (5 = ALL 6 9 0 5 ) = true ) = false 4 (5!= ALL ) = true 6 (!= ALL) NOT IN 要注意, ( = ALL) IN 2014-10-09 数据库系统概论 - 第 3 章 125/198
3.4.3. 嵌套查询 例 42 查询其他系中比 CS 系任一学生年龄小的学生名单 SELECT Sname, Sage FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept <> CS ; 2014-10-09 数据库系统概论 - 第 3 章 126/198
3.4.3. 嵌套查询 事实上, 用集函数实现子查询通常比直接用 ANY 或 ALL 查询效率要高 SELECT Sname, Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= CS ) AND Sdept <> CS ; 2014-10-09 数据库系统概论 - 第 3 章 127/198
3.4.3. 嵌套查询 例 43 查询其他系中比 CS 系所有学生年龄都小的学生名单 SELECT Sname, Sage FROM Student WHERE Sage < ALL (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept <> CS ; 2014-10-09 数据库系统概论 - 第 3 章 128/198
3.4.3. 嵌套查询 四. 带有 EXISTS 谓词的子查询 EXISTS 代表存在量词彐 带有 EXISTS 谓词的子查询不返回任何实际数据, 它只产生逻辑真值 true 或逻辑假值 false 常用于判断 x R,S S R,S=RR S=R,S R 非空等 通常是相关子查询 2014-10-09 数据库系统概论 - 第 3 章 129/198
3.4.3. 嵌套查询 例 44 查询所有选修了 1 号课程的学生姓名 涉及 Student 关系和 SC 关系, 我们可以在 Student 关系中依次取每个元组的 Sno 值, 用此 Student.Sno 值去检查 SC 关系, 若 SC 中存在这样的元组, 其 SC.Sno 值等于用来检查的 Student.Sno 值, 并且其 SC.Cno='1', 则取此 Student.Sname 送入结果关系 将此想法写成 SQL 语句就是 : SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1'); 2014-10-09 数据库系统概论 - 第 3 章 130/198
3.4.3. 嵌套查询 求解过程 首先取外层查询中 Student 表的第一个元组, 根据它与内层查询相关的属性值 ( 即 Sno 值 ) 处理内层查询, 若 WHERE 子句返回值为真 ( 即内层查询结果非空 ), 则取此元组放入结果表 ; 然后再检查 Student 表的下一个元组 ; 重复这一过程, 直至 Student 表全部检查完毕为止 2014-10-09 数据库系统概论 - 第 3 章 131/198
3.4.3. 嵌套查询 例 45 查询所有未修 1 号课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1'); ( 要求的学生不存在 1 号课程的选课记录 ) 2014-10-09 数据库系统概论 - 第 3 章 132/198
3.4.3. 嵌套查询 一些带 EXISTS 或 NOT EXISTS 谓词的子查询不能被其他形式的子查询等价替换, 但所有带 IN 谓词 比较运算符 ANY 和 ALL 谓词的子查询都能用带 EXISTS 谓词的子查询等价替换 例如带有 IN 谓词的例 39 可以用如下带 EXISTS 谓词的子查询替换 : SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept=S1.Sdept AND S2.Sname= 刘晨 ); 2014-10-09 数据库系统概论 - 第 3 章 133/198
3.4.3. 嵌套查询 SQL 语言中没有全称量词 (For all) 因此可以利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词 例 46 查询选修了全部课程的学生姓名 由于没有全称量词, 我们将题目的意思转换成等价的存在量词的形式 : 查询这样的学生姓名, 没有一门课程是他不选的 2014-10-09 数据库系统概论 - 第 3 章 134/198
3.4.3. 嵌套查询 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC 查这样的学生 不存在课程表中这样的课程 这门课程该生没有选 WHERE Student.Sno=SC.Sno AND Course.Cno=Cno)); 2014-10-09 数据库系统概论 - 第 3 章 135/198
3.4.3. 嵌套查询 方法 2: 用集函数 SELECT Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) = (SELECT COUNT(*) FROM Course)); 2014-10-09 数据库系统概论 - 第 3 章 136/198
3.4.3. 嵌套查询 思考 : SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno GROUP BY SC.Sno HAVING COUNT(*) = (SELECT COUNT(*) FROM Course)); 错在哪里? 2014-10-09 数据库系统概论 - 第 3 章 137/198
3.4.3. 嵌套查询 例 47 查询至少选修了学生 200215122 选修的全部课程的学生号码 本题的查询要求可以做如下解释, 查询这样的学生, 每门 200215122 号学生选修的课, 他都选修了 换句话说, 若有一个学号为 x 的学生, 对所有的课程 y, 只要学号为 200215122 的学生选修了课程 y, 则 x 也选修了 y; 那么就将他的学号选出来 它所表达的语义为 : 不存在这样的课程 y, 学生 200215122 选修了 y, 而学生 x 没有选 用 SQL 语 言可表示如下 : 2014-10-09 数据库系统概论 - 第 3 章 138/198
3.4.3. 嵌套查询 求这样的学生 SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno= 200215122' AND NOT EXISTS (SELECT * 而该生没有选 不存在这样的课程 这是 122 学生选的课 FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)); 2014-10-09 数据库系统概论 - 第 3 章 139/198
3.4.3. 嵌套查询 使用 EXISTS 更一般的形式 exists r r Ø not exists r r = Ø 用于检查包含关系 A 包含 B B-A = Ø B not exists (B except A) A 2014-10-09 数据库系统概论 - 第 3 章 140/198
3.4.3. 嵌套查询 解决例 47 类问题的一般方法 122 号学生选的课程 要求的学生选的课程 用于检查包含关系 A 包含 B B-A = Ø not exists (B except A) B A 2014-10-09 数据库系统概论 - 第 3 章 141/198
3.4.3. 嵌套查询 SELECT Sno FROM Student WHERE NOT EXISTS ( 122 号学 (SELECT Cno 生选的课程 FROM SC WHERE Sno= 200215122 ) EXCEPT (SELECT Cno 要求的学生 FROM SC 选的课程 WHERE SC.Sno=Student.Sno)); 2014-10-09 数据库系统概论 - 第 3 章 142/198
本章目录 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 SQL 查询 3.5 数据更新 3.6 视图 3.7 嵌入式 SQL 2014-10-09 数据库系统概论 - 第 3 章 143/198
3.5 数据更新 插入数据 INSERT 修改数据 UPDATE 删除数据 DELETE 2014-10-09 数据库系统概论 - 第 3 章 144/198
3.5.1 插入数据 一 插入单个元组插入单个元组的 INSERT 语句的格式为 : INSERT INTO < 表名 > [(< 属性列 1>[,< 属性列 2>...)] VALUES (< 常量 1> [,< 常量 2>]...) 2014-10-09 数据库系统概论 - 第 3 章 145/198
3.5.1 插入数据 如果某些属性列在 INTO 子句中没有出现 一般, 新记录在这些列上将取空值 在表定义时说明为 NOT NULL 的属性列不能取空值 主码属性不能取空值 如果 INTO 子句中没有指明任何列名 新插入的记录必须在每个属性列上均有值 VALUES 子句后值与属性列的顺序一致 2014-10-09 数据库系统概论 - 第 3 章 146/198
3.5.1 插入数据 例 3 插入一条选课记录 ( 200215128, 1 ) INSERT INTO SC (Sno, Cno) VALUES ( 200215128', '1'); 或 INSERT INTO SC VALUES ( 200215128, 1, NULL); 2014-10-09 数据库系统概论 - 第 3 章 147/198
3.5.1 插入数据 例 3 插入一条选课记录 ( 200215128, 1') INSERT INTO SC (Sno, Cno) VALUES ( 200215128', '1'); 或 INSERT INTO SC VALUES ( 200215128, 1, NULL); 推荐 2014-10-09 数据库系统概论 - 第 3 章 148/198
3.5.1 插入数据 二 插入子查询结果 子查询嵌套在 INSERT 语句中, 用以生成要插入的数据 插入子查询结果的 INSERT 语句的格式 INSERT INTO < 表名 > [(< 属性列 1> [,< 属性列 2>...)] 子查询 ; 实现批量插入一次将子查询的结果全部插入指定表中 2014-10-09 数据库系统概论 - 第 3 章 149/198
3.5.1 插入数据 例 4 对每一个系, 求学生人数及平均年龄, 并把结果存入数据库 对于这道题, 首先要在数据库中建立一个新表, 其中一列存放系名, 一列存放相应系的学生人数, 一列存放学生的平均年龄 CREATE TABLE Deptage (Sdept CHAR(15), Num INT, Avgage INT); 2014-10-09 数据库系统概论 - 第 3 章 150/198
3.5.1 插入数据 例 4 对每一个系, 求学生人数及平均年龄, 并把结果存入数据库 然后对数据库的 Student 表按系分组求平均年龄, 再把系名和平均年龄存入新表中 INSERT INTO Deptage(Sdept,Num, Avgage) SELECT Sdept, COUNT(*), AVG(Sage) FROM Student GROUP BY Sdept; 2014-10-09 数据库系统概论 - 第 3 章 151/198
3.5.2 修改数据 更新操作,UPDATE 语句 格式 UPDATE < 表名 > SET < 列名 >=< 表达式 >[,< 列名 >=< 表达式 >]... [WHERE < 条件 >]; 其功能是修改指定表中满足 WHERE 子句条件的元组 SET 子句用于指定修改方法, 即用 < 表达式 > 的值取代相应的属性列值 2014-10-09 数据库系统概论 - 第 3 章 152/198
3.5.2 修改数据 修改某一个元组的值 例 5 将学生 200215121 的年龄改为 22 岁 UPDATE Student SET Sage=22 WHERE Sno= 200215121'; 修改多个元组的值 例 6 将所有学生的年龄增加 1 岁 UPDATE Student SET Sage=Sage+1; 2014-10-09 数据库系统概论 - 第 3 章 153/198
3.5.2 修改数据 带子查询的修改语句子查询也可以嵌套在 UPDATE 语句中, 用以构造执行修改操作的条件 例 7 将计算机科学系全体学生的成绩加 10 分 UPDATE SC SET grade=grade+10 WHERE 'CS'=(SELECT Sdept FROM Student WHERE Sno=SC.Sno); 处理过程类似于相关子查询 2014-10-09 数据库系统概论 - 第 3 章 154/198
3.5.2 修改数据 解法 2: UPDATE sc SET grade=grade+10 WHERE sno IN (SELECT sno FROM student WHERE sdept = 'CS'); 推荐此法 2014-10-09 数据库系统概论 - 第 3 章 155/198
3.5.3 删除数据 删除元组 DELETE 语句 格式 DELETE FROM < 表名 > [WHERE < 条件 >]; DELETE 语句的功能是从指定表中删除满足 WHERE 子句条件的所有元组 DELETE 语句删除的是表中的数据, 而不是关于表的定义 2014-10-09 数据库系统概论 - 第 3 章 156/198
3.5.3 删除数据 删除元组 DELETE 语句 格式 DELETE FROM < 表名 > [WHERE < 条件 >]; DELETE 语句的功能是从指定表中删除满足 WHERE 子句条件的所有元组 DELETE 语句删除的是表中的数据, 而不是关于表的定义 要删除表的定义, 还记得 DROP 语句吗? 2014-10-09 数据库系统概论 - 第 3 章 157/198
3.5.3 删除数据一 删除某一个元组的值 例 8 删除学号为 200215128 的学生记录 DELETE FROM Student WHERE Sno= 200215128 ; 2014-10-09 数据库系统概论 - 第 3 章 158/198
3.5.3 删除数据 二 删除多个元组的值例 9 删除所有的学生选课记录 DELETE FROM SC; 这条 DELETE 语句将使 SC 成为空表, 它删除了 SC 的所有元组 2014-10-09 数据库系统概论 - 第 3 章 159/198
3.5.3 删除数据 三 带子查询的删除语句 子查询同样也可以嵌套在 DELETE 语句中, 用以构造执行删除操作的条件 例 10 删除计算机科学系所有学生的选课记录 DELETE FROM SC WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno); 2014-10-09 数据库系统概论 - 第 3 章 160/198
3.5.3 删除数据 三 带子查询的删除语句 子查询同样也可以嵌套在 DELETE 语句中, 用以构造执行删除操作的条件 例 10 删除计算机科学系所有学生的选课记录 DELETE FROM SC WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno); 请同学们自己思考 WHERE 子句中使用 IN 的删除 2014-10-09 数据库系统概论 - 第 3 章 161/198
本章目录 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 SQL 查询 3.5 数据更新 3.6 视图 3.7 嵌入式 SQL 2014-10-09 数据库系统概论 - 第 3 章 162/198
3.6 视图 从一个或多个基本表 ( 或视图 ) 导出的表, 是虚表 ; 使用户以不同方式看数据 ; 数据库中只存视图的定义, 不存数据 ; 对视图的更新有限制 主要目的 : 提供数据库保护 2014-10-09 数据库系统概论 - 第 3 章 163/198
3.6.1 定义视图 创建视图 CREATE VIEW 命令 格式 CREATE VIEW < 视图名 >[(< 列名 >[,< 列名 >]...)] AS < 子查询 > [WITH CHECK OPTION]; 通常不允许含有 ORDER BY 子句和 DISTINCT 短语 WITH CHECK OPTION 表示对视图进行 UPDATE INSERT 和 DELETE 操作时要保证更新 插入或删除的行满足视图定义中的谓词条件 ( 即子查询中的条件表达式 ) 2014-10-09 数据库系统概论 - 第 3 章 164/198
3.6.1 定义视图 例 1 建立信息系学生的视图 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept='IS'; 2014-10-09 数据库系统概论 - 第 3 章 165/198
3.6.1 定义视图 例 2 建立信息系学生的视图, 并要求进行修改和插入操作时仍须保证该视图只有信息系的学生 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept='IS' WITH CHECK OPTION; WITH CHECK OPTION 保证更新时满足视图定义的谓词条件 2014-10-09 数据库系统概论 - 第 3 章 166/198
3.6.1 定义视图 行列子集视图 从一个基本表中导出, 只是去掉了某些行或列 ( 保留原表的主码 ), 这样的视图称为行列子集视图 带表达式的视图 即带虚拟列的视图 分组视图 子查询带集函数和 GROUP BY 分组的视图 2014-10-09 数据库系统概论 - 第 3 章 167/198
3.6.1 定义视图 二 删除视图 删除视图的定义 DROP VIEW 语句 格式 DROP VIEW < 视图名 > [CASCADE RESTRICT]; CASCADE 级联删除 RESTRICT 受限删除默认 不会影响定义视图的基本表的数据和定义 2014-10-09 数据库系统概论 - 第 3 章 168/198
3.6.1 定义视图 例 8 删除视图 IS_S1 及其导出的所有视图 DROP VIEW IS_S1 CASCADE; 2014-10-09 数据库系统概论 - 第 3 章 169/198
补充删除基本表对视图的影响 2014-10-09 数据库系统概论 - 第 3 章 170/198
补充删除基本表对视图的影响 2014-10-09 数据库系统概论 - 第 3 章 171/198
补充删除基本表对视图的影响 2014-10-09 数据库系统概论 - 第 3 章 172/198
3.6.2 查询视图 DBMS 执行对视图的查询转换成对基本表的查询 将对视图的查询转换为对基本表的查询的过程称为视图的消解 (View Resolution) 现代 DBMS 对视图的消解一般不会出错 在应用中, 对基本表的查询和对视图的查询可以不做区别 2014-10-09 数据库系统概论 - 第 3 章 173/198
3.6.3 更新视图 对视图的更新, 最终要转换为对基本表的更新 为防止用户通过视图对数据进行增删改时, 无意或故意操作不属于视图范围内的基本表数据, 可在定义视图时加上 WITH CHECK OPTION 子句 2014-10-09 数据库系统概论 - 第 3 章 174/198
3.6.3 更新视图 例 13 向信息系学生视图 IS_S 中插入一个新的学生记录, 其中学号为 95029, 姓名为赵新, 年龄为 20 岁 INSERT INTO IS_Student VALUES('95029', ' 赵新 ', 20); DBMS 将其转换为对基本表的更新 : INSERT INTO Student(Sno, Sname, Sage, Sdept) VALUES('95029', ' 赵新 ', 20, 'IS'); 这里系统自动将系名 'IS' 放入 VALUES 子句中 2014-10-09 数据库系统概论 - 第 3 章 175/198
3.6.3 更新视图 通过视图更新基本表, 从理论上来说有的是可以更新的, 有的是根本不可能更新的 一般来说, 行列子集视图是可更新的 不同系统对视图的更新有不同的规定 Postgres 系统通过用户定义规则 (Rule) 来实现视图更新 2014-10-09 数据库系统概论 - 第 3 章 176/198
3.6.4 视图的优点 视图能够简化用户的操作 视图使用户能以多种角度看待同一数据 视图对重构数据库提供了一定程度的逻辑独立性 视图能够对机密数据提供安全保护 合理利用视图, 能方便复杂查询 2014-10-09 数据库系统概论 - 第 3 章 177/198
3.6.4 视图的优点 补充例查询各科总成绩高于平均总成绩的学生的学号和总成绩 create view student_total (sno, total) as select sno, sum(grade) from sc group by sno; create view student_total_avg(total_avg) as select avg(total) from student_total; select * from student_total,student_total_avg where total>total_avg; 2014-10-09 数据库系统概论 - 第 3 章 178/198
本章目录 3.1 SQL 概述 3.2 学生 - 课程数据库 3.3 数据定义 3.4 SQL 查询 3.5 数据更新 3.6 视图 3.7 嵌入式 SQL 2014-10-09 数据库系统概论 - 第 3 章 179/198
3.7 嵌入式 SQL SQL 语言提供了两种不同的使用方式 一种是在终端交互式方式下使用, 我们前面介绍的就是做为独立语言由用户在交互环境下使用的 SQL 语言 另一种是将 SQL 语言嵌入到某种高级语言如 Pascal COBOL FORTRAN C 中使用, 利用高级语言的过程性结构来弥补 SQL 语言在实现复杂应用方面的不足, 这种方式下使用的 SQL 语言称为嵌入式 SQL(Embedded SQL), 而嵌入 SQL 的高级语言称为主语言或宿主语言 2014-10-09 数据库系统概论 - 第 3 章 180/198
3.7.1 一般形式 1. 语句级接口如 PB Script 2. 调用级接口如 JDBC ODBC 在有的嵌入式 SQL 中, 为了能够区分 SQL 语句与主语言语句, 所有 SQL 语句都必须加前缀 EXEC SQL 如 :C 中嵌入可能形式 EXEC SQL DROP TABLE Student; 2014-10-09 数据库系统概论 - 第 3 章 181/198
3.7.3 嵌入式 SQL 与主语言间的通信 将 SQL 嵌入到高级语言中混合编程,SQL 语句负责操纵数据库, 高级语言语句负责控制程序流程 最重要的是两者之间的通信问题 : 1. 向主语言传递 SQL 语句的执行状态 ; 2. 主语言向 SQL 提供参数 ( 主变量实现 ); 3. SQL 查询结果交还主语言处理 2014-10-09 数据库系统概论 - 第 3 章 182/198
3.7.3 嵌入式 SQL 与主语言间的通信 一 SQL 通信区 SQL 语句执行后, 系统要反馈给应用程序若干信息, 主要包括描述系统当前工作状态和运行环境的各种数据, 这些信息将送到 SQL 通信区 SQLCA 中 应用程序从 SQLCA 中取出这些状态信息, 据此决定接下来执行的语句 如下所示的 SQLCA 数据连接字 2014-10-09 数据库系统概论 - 第 3 章 183/198
3.7.3 嵌入式 SQL 与主语言间的通信 SQLCA.DBMS = "MSS" SQLCA.Database = Student" SQLCA.LogId = "sa" SQLCA.LogPass = "" SQLCA.ServerName ="ZTQ\ZTQ_TEST" CONNECT USING SQLCA; 2014-10-09 数据库系统概论 - 第 3 章 184/198
3.7.3 嵌入式 SQL 与主语言间的通信 二 主变量 SQL 语句中使用主语言的变量 SQL 语句中主变量前加冒号 输入主变量, 输出主变量 ( 相对于 SQL 语句 ) 输入主变量 : 应用程序赋值,SQL 使用 实际上 SQL 把它当常量处理 输出主变量 : 由 SQL 语句对其赋值或设置状态信息, 返回应用程序 指示变量 : 附加在主变量上, 指示所指主变量的值 ( 空值 ) 或条件 2014-10-09 数据库系统概论 - 第 3 章 185/198
3.7.3 嵌入式 SQL 与主语言间的通信 三 游标 为处理集合结果而引入 四 程序实例 注意 : 定义游标后的 Select 目标列是表 属性名, 不是主变量 2014-10-09 数据库系统概论 - 第 3 章 186/198
3.7.3 不用游标的 SQL 语句 适用于全部说明性语句 数据定义 控制语句,Select 结果为单个记录的查询语句, 非 CURRENT 形式的 UPDATE 和 DELETE 语句 2014-10-09 数据库系统概论 - 第 3 章 187/198
3.7.4 使用游标的 SQL 语句 一 查询结果为多条记录的 SELECT 语句 本例要查询 deptname 系的所有学生的学号 姓名 性别和年龄 首先定义游标 SX, 将其与查询结果集 ( 即 deptname 系的所有学生的学号 姓名 性别和年龄 ) 相联系 ( 步骤 1) 这时相应的 SELECT 语句并没有真正执行 然后打开游标 SX, 这时 DBMS 执行与 SX 与相联系的 SELECT 语句, 即查询 deptname 系的所有学生的学号 姓名 性别和年龄 ( 步骤 2), 之后 SX 处于活动状态 2014-10-09 数据库系统概论 - 第 3 章 188/198
3.7.4 使用游标的 SQL 语句 接下来在一个循环结构中逐行取结果集中的数据, 分别将学号 Sno 姓名 Sname 性别 Ssex 和年龄 Sage 送至主变量 HSno HSname HSsex 和 HSage 中 ( 步骤 3) 主语言语句将对这些主变量做进一步处理 最后关闭游标 SX( 步骤 4) 这时 SX 不再与 deptname 系的学生数据相联系 被关闭的游标 SX 实际上可以再次被打开, 与新的查询结果相联系 例如, 可以在例 1 中再加上一层外循环, 每次对 deptname 赋新的值, 这样 SX 就每次和不同的系的学生集合相联系 2014-10-09 数据库系统概论 - 第 3 章 189/198
3.7.4 使用游标的 SQL 语句 String Cname,Cno='1'; DECLARE CX CURSOR FOR SELECT Cname FROM course; OPEN CX; FETCH CX INTO :cname; do while SQLCA.SQLCODE = 0 ddlb_1.additem(cname); FETCH CX INTO :cname; loop close CX; 2014-10-09 数据库系统概论 - 第 3 章 190/198
3.7.4 使用游标的 SQL 语句 二 CURRENT 形式的 UPDATE 和 DELETE 语句 2014-10-09 数据库系统概论 - 第 3 章 191/198
3.7.4 动态 SQL 查询对象 查询条件 要查询的属性列不确定 特点 : 在查询执行时临时组装 实现技术 : 实际不是 SQL 技术, 是程序设计技术 2014-10-09 数据库系统概论 - 第 3 章 192/198
3.7.4 动态 SQL Power Builder 的四种动态 SQL 格式 无参数 无返回结果的 SQL 语句 如 建表 插入元组值等场合 String Mysql Mysql= INSERT INTO Course & + VALUES( 5, JAVA ) ; EXECUTE IMMEDIATE : Mysql ; 2014-10-09 数据库系统概论 - 第 3 章 193/198
3.7.4 动态 SQL 需要参数, 无返回值 INT Emp_id_var = 56 PREPARE SQLSA FROM "DELETE FROM employee WHERE emp_id=?" ; EXECUTE SQLSA USING : Emp_id_var ; 2014-10-09 数据库系统概论 - 第 3 章 194/198
3.7.4 动态 SQL 有返回值的 编译时能确定参数个数和返回结果字段 有返回值的 编译时可能无法确定参数个数和返回结果字段 JDBC 相关内容参阅 课程设计 2014-10-09 数据库系统概论 - 第 3 章 195/198
作业 1. P127 2, 3, 4 2. 对 Student,SC,Course 表 (1) 求赵明选修的各门课程的课程名及其成绩 ; (2) 查询计算机系 (CS) 所有姓 王 学生的详细信息 ; (3) 求选修 数据库技术 成绩最高的学生姓名 ; (4) 查询选修 数据库原理 课程的所有学生的所在系 ; (5) 求所有课程的课程名及平均成绩 (6) 查询选课人数在 100 人以上, 平均成绩在 70-80( 含 ) 之间的课程号 2014-10-09 数据库系统概论 - 第 3 章 196/198
作业 作业通过发送电子邮件附件形式提交到助教老师邮箱 : 653703162@qq.com 作业文件名命名要求 : DB_ 学号 _ 姓名 _n.doc (n 为当章节序号 ) 如一个合法文件名 : DB_95002_ 张三 _3.doc 10 月 25 日前 Email 提交 2014-10-09 数据库系统概论 - 第 3 章 197/198
Any Question? Thank you! 2014-10-09 数据库系统概论 - 第 3 章 198/198