2015第五章SQL语言.ppt

第五章 关系数据库标准语言SQL,课程安排,SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询的基本结构 SQL单关系查询 SQL多关系查询 SQL嵌套查询,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL概貌及特点 SQL的基本概念 SQL数据基本定义功能 SQL查询的基本结构 SQL单关系查询 SQL多关系查询,SQL的概貌与特点,SQL的产生与发展 结构化查询语言(Structured Query Language)简称SQL。 1974年:Boyce和Chamberlin提出，IBM的原型系统Systerm R上实现。 1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。 1989年4月，ISO提出了具有完整性特征的SQL89标准。 1992年11月又公布了SQL92标准，在此标准中，把数据库分为三个级别：基本集、标准集和完全集。 1999年，SQL发布的版本包括框架、SQL基础部分， SQL调用接口， 永久存储模块，宿主语言绑定 ， 外部数据管理 ，对象语言绑定等多个部分。 2003和2006版本又包含了XML相关内容。,SQL的概貌与特点,一体化的特点(相对于非关系模式的数据语言) 集数据定义语言、数据操纵语言、数据控制语言于一体 根据需要随时修改模式 实体和联系均用关系表示 两种使用方式，统一的语法格式 高度非过程化 面向集合的操作方式 语言简洁、易学易用,SQL语言的组成部分,数据定义语言（DDL : Data Definition Language） 负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，操作的对象包括关系表、视图、索引等。 数据操纵语言（DML : Data Manipulation Language） 其语句包括动词SELECT，INSERT，UPDATE和DELETE。 它们分别用于查询、添加、修改和删除表中的行。,SQL语言的组成部分,数据控制语言（DCL : Data Control Language） 在SQL语言中，是一种可对数据访问权进行控制的指令， 由 GRANT 和 REVOKE 两个指令组成。 事务处理语言（TPL: Transaction Processing Language ） 它的语句能确保被DML语句影响的表的所有行及时得以更新。包括BEGIN TRANSACTION，COMMIT和ROLLBACK。,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL操纵功能 SQL视图定义 SQL索引 SQL的完整性实现,SQL概述 SQL概貌及特点 SQL的基本概念 SQL数据基本定义功能 SQL查询语句基本结构 SQL单关系查询 SQL多关系查询,数据库三级模式,SQL语言也支持关系数据库三级模式体系结构 外模式：视图+一些基本表 模式：基本表 内模式：存储文件(逻辑结构）,SQL的基本概念,视图1,视图2,模式,存储文件1,存储文件2,基本表2,基本表1,基本表3,基本表4,SQL,外模式,内模式,SQL语言多样性,各种不同的数据库对SQL语言的支持与标准存在着细微的不同，因为有的产品的开发先于标准的公布，另外，各产品开发商为了达到特殊的性能或新的特性，需要对标准进行扩展。,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据修改操作 SQL视图定义 SQL附加操作 SQL的完整性实现,SQL概述 SQL数据基本定义功能 模式定义 基本表定义 查询语句基本结构 SQL单关系查询 SQL多关系查询,样板模式-学生选课应用,学生表Student (Sno, Sname, Ssex, Sage, Sdept ) 学生（学号，姓名，性别，年龄，所在系） 课程表Course ( Cno, Cname, Cpno, Ccredit) 课程（课程号，课程名，先行课，学分） 学生选课表SC (Sno, Cno, Grade) 选课（学号，课程号，成绩）,SQL数据基本定义功能,SQL提供了三级模式结构的定义功能，相关的定义语句如下：,创建模式基本语句 CREATE SCHEMA AUTHORIZATION 拥有DBA权限或者获得了DBA授予的Create Schema权限的用户 例子：定义一个学生-课程模式S-T Create schema “S-T” authorization Wang 不指明模式名则隐含为 模式名为用户名 例子：Create schema authorization Wang 定义一个命名空间,SQL数据基本定义功能-定义模式,CREATE SCHEMA AUTHORIZATION | 例子： Create schema test authorization Zhang create table Tab1 (A1 int, A2 char(10), A3 char(20),SQL数据基本定义功能-定义模式,删除模式基本语句 DROP SCHEMA 例子： Drop schema Zhang cascade,SQL数据基本定义功能-删除模式,SQL数据基本定义功能-模式,模式与表的关系 每一个基本表都属于某一个模式 一个模式包含多个基本表 定义基本表的模式 在表名中明显地给出模式名 Create table “S-T”.Students() 在创建模式语句中同时创建表 设置所属的模式，在创建表时表名中不必给出模式名 当用户创建表时若没有指定模式，系统根据搜索路径来确定该对象所属的模式,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据操作功能 SQL视图定义 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 模式定义 基本表定义 SQL查询语句基本结构 SQL单关系查询 SQL多关系查询,SQL数据基本定义功能-创建表,SQL基本建表语句 CREATE TABLE 表名 ( 数据类型 列级约束, 数据类型 列级约束, , 表级约束)。,SQL数据基本定义功能-创建表,创建表Students Create table Students (Sno char(11) , Sname char(20), Ssex char(2) Sage int, Sdept char(20) , primary key (Sno ) ),Create table Students ( sno char(11) primary key, Sname char(20), Ssex char(2), Sage int, Sdept char(20),表级约束,列级约束,Students,(Sno, Sname, Ssex, Sage, Sdept ),创建表Courses Create table Courses ( Cno char(4), Cname char(40), Cpno char(4), Ccredit int, primary key (Cno ) , Foreign Key （Cpno） REFERENCES Courses (Cno),表级约束,Courses,( Cno, Cname, Cpno, Ccredit),定义外键,SQL数据基本定义功能-创建表,创建表SC Create table SC ( Sno char(9), Cno char(4), Grade int, primary key (Sno , Cno ), foreign key(Sno ) references Students (Sno), foreign key (Cno ) references Courses (Cno ) ),表级约束,SC,(Sno, Cno, Grade),SQL数据基本定义功能-创建表,char(n) ：固定长度为n的字符串 varchar(n)： 可变长度为n的字符串 smallint： 短整数 int： 长整数 numeric(p,s) ：定点数。由p位数字组成，包括s 位小数 float(n)： 精度至少为n位数字的浮点数 real：取决于机器精度的浮点数 double precision：取决于机器精度的双精度浮点数 date： 日期，格式为年、月、日,YYYY-MM-DD time： 时间，格式为时、分、秒, HH:MM:SS,SQL基本定义功能-数据类型,SQL基本定义功能-表的修改,修改 添加列|完整性约束： ALTER TABLE ADD 完整性约束 删除列|完整性约束： ALTER TABLE DROP 完整性约束 修改列： ALTER TABLE ALTER COLUMN ,SQL基本定义功能-表的修改,修改表：在学生表中添加字段Sclass，字符类型，长度20 Alter table Students add Sclass char(20) 将年龄的数字类型由字符型改为整型 Alter table Students alter column Sage int 增加课程名称必须取唯一值的约束条件 Alter table Courses add unique (Cname),SQL基本定义功能-表的删除,删除表 Drop table restrict|cascsde Restrict: 表删除有限制条件，不能被其他表的约束所引用，不能有视图、触发器、存储过程或函数 Cascade:把一个基本表的定义连同表上的所有记录、索引以及由此基本表导出的所有视图都删除，并释放相应的存储空间 删除表Students： Drop table Students cascade,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据修改操作 SQL视图 SQL附加操作 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结构 SQL单关系查询 SQL多关系查询,SQL查询语句基本结构,SQL查询语句的基本结构 SELECT DISTINCT , FROM , WHERE Group by having Order By asc | desc ;,SQL查询语句基本结构,SQL查询语句的三个子句 Select: 对应关系代数的投影运算，列出查询结果中的属性 From: 对应关系代数的笛卡尔集运算，列出表达式求值中需扫描的关系 Where:对应关系代数的选择谓词，包括一个作用在from子句中关系属性上的谓词。,Select A1, A2, , An From r1, r2, , rm Where P,A1,A2,An(P(r1 r2 rm),样板模式S-C,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),Sailors(sid, sname, rating, age) Boats(bid, bname, color) Reserve(sid, bid, day),样板模式S-B-R,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结构 SQL单关系查询 属性列查询 元组查询 SQL多关系查询,单表查询-属性列查询,单表查询查询内容仅涉及一个表 查询指定的属性列 例子：查询全体学生的学号和姓名 select Sno, Sname from Students;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),顺序无关性,单表查询-属性列查询,用* 表示关系的全部属性 例子：查询全体学生的基本情况 select * from Students;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),单表查询-属性列查询,包含运算符的查询结果属性 例子：查询全体学生的姓名及出生年份 select Sname, 2014-Sage from Students;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),常量属性和函数 例子：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名 select Sname, Year of birth: , 2014-Sage, islower(Sdept) from Students;,示意答案,单表查询-属性列查询,用户可以通过指定别名来改变查询结果的列标题 select Sname NAME, Year of birth BIRTH, 2014-Sage BIRTHDAY, islower(Sdept) DEPARTMENT from Students;,单表查询-属性列查询,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结构 SQL单关系查询 属性列查询 元组查询 SQL多关系查询,消除取值重复的行 查询选修了课程的学生学号 select Sno from SC; 消除重复行 select distinct Sno from SC;,Sno 11001 11001 11001 11002 11002,Sno 11001 11002,与关系代数中投影操作不同，SQL语句中默认不消除重复，需要使用distinct关键字消除重复行,单表查询-元组查询,查询满足条件的元组：WHERE子句 常用查询条件 查询条件 谓词 比较 =, , =, , !, ! not + 上述比较运算符（例如not=） 确定范围 between and, not between and 确定集合 in, not in 字符匹配 like, not like 空值 is null, is not null 多重条件 and, or,单表查询-元组查询,比较查询 查询年龄在20岁以下的学生姓名及其年龄 select Sname, Sage from Students where Sage 20; 查询考试成绩有不及格的学生的学号 select distinct sno from SC where Grade60,单表查询-元组查询,单表查询-元组查询,范围查询 查询年龄在2023岁之间的学生姓名、所在系，年龄 select Sname, Sdept, Sage from Students where Sage between 20 and 23; 与betweenand相对的谓词是not betweenand,下限,上限,确定集合查询 查询信息系、数学系和计算机系学生的姓名和性别 select Sname, Ssex from Students where Sdept in (IS, MA, CS); 与in相对的谓词是Not in,单表查询-元组查询,注意：字符类型的查询条件需要使用引号标注属性值,字符匹配查询 用谓词like进行字符串的匹配 not like escape 通配符 %：任意长度的字符串。 例如：a%bacb, addgb,ab _：任意单个字符 例如：a_bacb, adb,单表查询-元组查询,查询所有姓刘的学生的姓名、学号和性别 select Sname, Sno, Ssex from Students where Sname like 刘%； 查询所有不姓刘的学生的姓名、学号和性别 select Sname, Sno, Ssex from Students where Sname not like 刘%；,单表查询-元组查询,单表查询-元组查询,查询姓欧阳且全名为三个汉字的学生姓名 select Sname from Students where Sname like 欧阳_; 查询以C开始和结束，并且有至少3个字母组成的系的学生的学号 select Sno from Students where Sdept like C_%C;,查询DB_Design课程号和学分 select Cno, Ccredit from Courses where Cname like DB_Design escape ; 查询以”DB_”开头，且倒数第3个字符为i的课程的详细情况 select * from Courses where Cname like DB_%i_ escape ;,单表查询-元组查询,如果用户查询的字符串本身包含通配符%或_，用escape短语，对通配符转义,单表查询-元组查询,查询学号为20140001的学生的详细情况 select * from students where sno like 20140001,如果Like后面的匹配串中不含通配符，可以用=运算符取代like谓词，用！=或（不等于）运算符取代not like,空值查询 查询缺考学生的学号和课号 select Sno, Cno from SC where Grade is null; 查询有成绩的学生的学号和课号 select Sno, Cno from SC where Grade is not null;,单表查询-元组查询,Is 不能用“=”,复合查询 查询计算机系年龄在20岁以下的学生的姓名 select Sname from Students where Sdept=CS and Sage20; 查询信息系、数学系和计算机系学生的姓名和性别 select Sname, Ssex from Students where Sdept=CS or Sdept=IS or Sdept=MA,单表查询-元组查询,and优先级高于or 可以用（）改变优先级,In谓词是多个or运算符的缩写,单表查询,对查询结果排序：ORDER BY子句 ORDER BY asc | desc asc 升序；desc降序 例子：查询选修了3号课程的学生学号和成绩，要求查询结果按成绩降序排列 select Sno, Grade from SC where Cno=3 order by Grade desc;,单表查询,例子：查询全体学生的情况，查询结果按系号升序排列，同一系的学生按年龄降序排列 select * from Students order by Sdept asc, Sage desc;,对于空值，若按升序排列，含空值的元组将在最后显示，若按降序排列，空值的元组将最先显示,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结构 SQL单关系查询 SQL多关系查询 集合操作 连接操作,样板模式S-C,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),Sailors(sid, sname, rating, age) Boats(bid, bname, color) Reserve(sid, bid, day),样板模式S-B-R,多关系查询,集合操作 多个SQL的查询结果可以进行集合操作 多关系的连接操作 等值连接 自身连接 外连接 复合条件连接,多关系查询-集合操作,集合操作主要有: union、intersect和except 参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同 例子：查询计算机系的学生以及年龄不大于19岁的学生 不用union，如何实现该查询？,Union select * from Students where Sage = 19;,select * from Students where Sdept=CS,select * from Students where Sdept=CS or Sage = 19;,例子：查询计算机系的学生与年龄不大于19岁的学生的交集 select * from Students where Sdept=CS intersect select * from Students where Sage=19; 不用intersect，如何实现该查询？,多关系查询-集合操作,select * from Students where Sdept=CS and Sage = 19;,例子：查询计算机系的学生与年龄不大于19岁的学生的差集 select * from Students where Sdept=CS except select * from Students where Sage=19; 不用except，如何实现该查询？,多关系查询-集合操作,select * from Students where Sdept=CS and Sage 19;,关于消除重复元组的讨论 在select中 缺省情况下：保留重复元组 使用distinct消除重复元组 在union, intersect, except中 缺省情况下：消除重复元组 在union, intersect, 或except后加上all，保留重复元组,多关系查询-集合操作,查找rating7且年龄25的水手编号 复合条件 使用Intersect来代替and,Select S.sid from Sailors S where S.rating7 and S.age25,Select S.sid from Sailors S where s.rating7 intersect select S2.sid from Sailors S2 where S2.age25,多关系查询-集合操作,Sailors(sid , sname, rating, age) Boats(bid, bname, color) Reserve(sid, bid, day),课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结构 SQL单关系查询 SQL多关系查询 集合操作 连接操作,多关系查询,集合操作 多个SQL的查询结果可以进行集合操作 多关系的连接操作 等值连接 自身连接 外连接 复合条件连接,多表连接查询,等值与非等值连接查询 连接条件：Where子句中用来连接两个表的条件。 . 或.BETWEEN .AND . 比较运算符：=、=、）等；,等值连接,非等值连接,连接字段,多表连接查询,等值与非等值连接查询 查询每个学生及其选修课的情况 select Students.*, SC.* from Students, SC where Students.Sno = SC.Sno; Students.Sno Sname SC.Sno Cno 11001 李勇 11001 1 ,连接谓词,自然连接 在等值连接中把目标列中的重复属性列去掉 select Students.*, Cno, Grade from Students, SC where Students.Sno = SC.Sno;,多表连接查询,多表连接查询,等值与非等值连接查询 查询租借过船只的水手姓名和租借的日期 select S, Reserve.date from Sailors, Reserve where Sailors.Sid = Reserve.Sid; sname date Dustin 2011-05-01 ,连接谓词,多表连接查询,自身连接 一个表与其自身进行的连接 查询每门课的课号及其间接先修课的课号 C1: Courses ( Cno, Cname, Cpno, Credit) C2: Courses ( Cno, Cname, Cpno, Credit) select C1.Cno, C2.Cpno from Courses C1, Courses C2 where C1.Cpno=C2.Cno;,自身连接:查询每门课的课号及其间接先修课的课号,多表连接查询,外连接: 在一般连接中,只有满足条件的元组才可以作为结果输出, 外连接则不同(左、右外连接)，把舍弃的元组保留在结果关系中，而在其他属性上填空值。 左外连接列出左边关系中所有的元组，右外连接列出右边关系中所有的元组。 查询学校内学生及雇员的情况。 (内)连接：既是学生，又是雇员。 左外连接：是学生，可以不是雇员。 右外连接：可以不是学生，但，是雇员。 全外连接：可以不是学生，可以不是雇员。,Students(name, dept) Employee(name, salary),多表连接查询,左外连接:是学生，可以不是雇员。,Students(name, dept) Employee(name, salary),From子句的写法：Students LEFT OUTER JOIN Employee,多表连接查询,全外连接: 可以不是学生，可以不是雇员。,Students(name, dept) Employee(name, salary),From子句的写法：Students FULL OUTER JOIN Employee,多表连接查询,多表连接查询,外连接 查询所有水手的情况及其租借船只信息 select Sailors.*, Reserve.* from Sailors left out join Reserve on (Sailors.Sid = Reserve.Sid) sailors.sid sname rating age bid date 22 Dustin 7 45 02 2011-05-01 58 Rusty 10 35 null null ,复合条件连接 查询选修2号课程且成绩在90分以上的所有学生 select Students.* from Students, SC where Students.Sno=SC.Sno and Cno=2 and Grade90;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),多表连接查询,连接谓词,一般查询条件谓词,复合条件连接 查询每个学生的学号、姓名、选修的课程名和成绩 select Students.Sno, Sname, Cname, Grade from Students, Courses, SC where Students.Sno=SC.Sno and SC.Cno=Courses.Cno;,Students ( Sno, Sname, Ssex, Sage, Sdept) Courses ( Cno, Cname, Cpno, Credit) SC ( Sno, Cno, Grade),多表连接查询,Select S.sname From Sailors S, Boats B, Reserves R Where S.sid=R.sid and R.bid=B.bid And (B.color=red or B.color=green),查找租用过红船或绿船的水手名字,多表连接查询,可以使用Union来代替or运算,查找租用过红船或绿船的水手名字,SELECT S.sname From Sailors S, Boats B, Reserves R Where S.sid = R.sid and R.bid = B.bid And B.color = red UNION SELECT S.sname From Sailors S, Boats B, Reserves R Where S.sid = R.sid and R.bid = B.bid And B.color = green,多表连接查询,查找租用过红船和绿船的水手名字 错误的写法 正确的写法,Select S.sname from Sailors S, Reverses R, Boats B where S.sid=R.sid and R.bid=B.bid and B.color=red and B.color=green,Select S.sname from Sailors S, Reverses R1, Boats B1, Reverses R2, Boats B2 where S.sid=R1.sid and R1.bid=B1.bid and S.sid=R2.sid and R2.bid=B2.bid and B1.color=red and B2.color=green,多表连接查询,查找租用过红船和绿船的水手名字-Intersect查询,Select S.sname from Sailors S, Reverses R, Boats B where S.sid=R.bid and R.bid=B.bid and B.color=red intersect select S2.sname from Sailors S2, Reverses R2, Boats B2 where S2.sid=R2.bid and R2.bid=B2.bid and B2.color=green,思考：第二部分的from子句和第一部分的from子句是否可以相同？,可以相同,多表连接查询,课程安排,SQL嵌套查询 不相关子查询 相关子查询 SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结 SQL单关系查询 SQL多关系查询,嵌套查询,查询块(SELECT-FROM-WHERE) 一个查询块可以放在另一个查询块的where子句或having短语的条件中 甚至是SELECT子句和FROM子句中 父查询和子查询 子查询中不可以使用order by 子句，order by只能对最终结果排序 多个简单查询构造一个复杂的查询,嵌套查询,不相关子查询和相关子查询 不相关子查询 不相关子查询求解方法由里向外 相关子查询 相关子查询求解方法由外向里,嵌套查询-不相关子查询,带有in的子查询：子查询结果为多值 查询与刘晨在一个系学习的学生 要求：不使用连接查询,Students ( Sno, Sname, Ssex, Sage, Sdept),select * from Students where Sdept in,(select Sdept from Students where Sname=刘晨)；,不相关子查询求解方法由里向外,带有in的子查询：子查询结果为多值 查询与刘晨在一个系学习的学生 要求：使用连接查询,select S2.* from Students S1, Students S2, where S1.Sname = 刘晨 and S1.Sdept = S2.Sdept;,有些嵌套查询可以用连接查询代替,Students ( Sno, Sname, Ssex, Sage, Sdept),嵌套查询-不相关子查询,select Sno, Sname from Students where Sno in,嵌套查询-不相关子查询,(select Cno from Courses where Cname=信息系统),(select Sno from SC where Cno in );,查询选修了课程名为信息系统的学生学号和姓名,带有比较运算符的子查询：子查询结果为单值的情况 一个能成为元组字段值的原子值称为标量 查询与学号20140001学生同龄的学生 要求：不使用连接查询,Students ( Sno, Sname, Ssex, Sage, Sdept),select * from Students where Sage = (select Sage from Students where Sno=20140001);,嵌套查询-不相关子查询,嵌套查询-不相关子查询,带有比较运算符的子查询：子查询结果为单值的情况 例子：查询与刘晨在一个系学习的学生 select * from Students where Sdept = (select Sdept from Students where Sname=刘晨);,注意：子查询一定要跟在比较符之后 不能用=表示值和表间的等值判断,嵌套查询-不相关子查询,样板模式 Sailors(sid , sname, rating, age) Boats(bid, bname, color) Reserves(sid, bid, day),查找租用过103号船只的水手的名字 Select S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103),问题：不使用嵌套查询该怎么做？,嵌套查询-不相关子查询,查找rating7且年龄25的水手编号 不相关子查询,Select S.sid from Sailors S where S.rating 7 And S.sid in ( select S2.sid from Sailors S2 where S2.age 25 ),嵌套查询-不相关子查询,查找租用过红船和绿船的水手名字 使用In查询来改写Intersect查询,Select S.name from Sailors S, Reverses R, Boats B where S.sid=R.sid and R.bid=B.bid and B.color=red And S.sid IN (select S2.sid from Sailors S2, Boats B2, Reserves R2 where S2.sid=R2.sid and R2.bid=B2.bid and B2.color=green),嵌套查询-不相关子查询,带有any或all的子查询：必须同比较运算符同用 any all 查询比信息系某一学生年龄小的其他系学生姓名和年龄 select Sname, Sage from Students where Sage any (select Sage from Students where Sdept=IS) and Sdept != IS ;,嵌套查询-不相关子查询,用集函数实现上述查询(效率比any和all高) select Sname, Sage from Students where Sage (select max(Sage) from Students where Sdept=IS) and Sdept!=IS ; 查询比信息系所有学生年龄都小的其他系的学生姓名和年龄(思考题) Any和all和集函数的对应关系参见教材,嵌套查询-不相关子查询,查找比Harotio级别高的水手 Select * FROM Sailors S WHERE S.ratingany(SELECT S2.rating FROM Sailors S2 WHERE S2.sname=Horatio),嵌套查询-不相关子查询,课程安排,SQL嵌套查询 不相关子查询 相关子查询 SQL分组聚集查询 SQL数据操纵功能 SQL视图 SQL索引 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结 SQL单关系查询 SQL多关系查询,嵌套查询,查询块(SELECT-FROM-WHERE) 一个查询块可以放在另一个查询块的where子句或having短语的条件中 甚至是SELECT子句和FROM子句中 父查询和子查询 子查询中不可以使用order by 子句，order by只能对最终结果排序 多个简单查询构造一个复杂的查询,嵌套查询,不相关子查询和相关子查询 不相关子查询 不相关子查询求解方法由里向外 相关子查询 相关子查询求解方法由外向里,带有exists的子查询：子查询结果为true或false 查询所有选修了1号课程的学生姓名 select Sname from Students where exists (select * from SC where Sno=Students.Sno and Cno=1); 相关子查询求解方法由外向里(样板数据库),思考：查询没有选修1号课程的学生姓名,嵌套查询-相关子查询,带有exists的子查询：子查询结果为true或false 查询没有选修了1号课程的学生姓名 select Sname from Students where not exists (select * from SC where Sno=Students.Sno and Cno=1); 相关子查询求解方法由外向里(样板数据库),嵌套查询-相关子查询,嵌套查询-相关子查询(思考题),带有exists的子查询：子查询结果为true或false 查询没有选修1号课程的学生姓名 select Sname from Students where exists (select * from SC where Sno=Students.Sno and Cno != 1);,上述查询是否正确?,嵌套查询-相关子查询,SQL中没有全称量词,但是 x(P) ( x (P) ) 例子: 查询选修了全部课程的学生姓名,分析: 求学生x, 对于任意一门课y, P: 学生x选修了y, 则: y(P)(y(P),x: Students.Sno y: Courses.Cno,例子: 查询选修了全部课程的学生姓名 select Sname from Students where not exists (select * from Course where not exists (select * from SC where Sno=Students.Sno and Cno=Course.Cno);,嵌套查询-相关子查询,例子: 查询选修了全部课程的学生姓名,当x=11001时,找不到一个课程,使P不成立,嵌套查询-相关子查询,分析: 求学生x, 对于任意一门课y, P: 学生x选修了y, 则: y(P)(y(P),SQL中没有逻辑蕴含运算,但是 (pq) pq 例子: 查询至少选修了学生11002选修的全部课程的学生学号 分析: 查询学号u, 对于任意一门课v, 只要11002选修, 则u必然选修。 如果: p: 11002选修v q: u选修v 则: v(p q) (v(p q) (v(pq) v(p q) ),嵌套查询-相关子查询,例子: 查询至少选修了学生11002选修的全部课程的学生学号 p: 11002选修v, q: u 选修v v(p q) v(p q) select Distinct Sno from SC X where not exists (select * from SC Y where Sno=11002 and not exists (select * from SC Z where Sno=X.Sno and Cno=Y.Cno);,u: X.Sno v: Y.Cno,Y ,X ,Z ,嵌套查询-相关子查询,查找租用过103号船只的水手的名字 Select S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid),相关子查询,嵌套查询-相关子查询,嵌套查询-From子查询,From子句中的子查询：嵌套子查询的另一个作用是在FROM子句中当关系使用. 例子：查询成绩在90分以上的女生的姓名 select Sname from Students S, (select Sno from SC where grade90) T where Ssex=F and S.sno=T.sno,课程安排,SQL嵌套查询 SQL分组聚集查询 SQL数据修改操作 SQL视图 SQL附加操作 SQL的完整性实现,SQL概述 SQL数据基本定义功能 SQL查询语句基本结 SQL单关系查询 SQL多关系查询,SQL分组聚集查询,分组和聚集操作符被用来扩展关系代数，其功能是根据元组的一个或一组属性将关系中的元组划分成组，然后对关系中的其它列使用聚集操作进行相关统计。 聚集操作是对关系中的每个分组单独进行的。 在查询结果中，每个分组对应结果关系中的一个元组。,SQL使用SELECT子句中的聚集函数实现聚集操作，使用GROUP BY子句进行分组定义，使用HAVING子句设定分组的选择条件。,SQL分组聚集查询,SQL提供的主要聚集函数 count( distinct|all *) count( distinct|all ) sum( distinct|all ) avg( distinct|all ) max ( distinct|all ) min ( distinct|all ),查找最年长的水手的年龄和名字 错误写法 Select S.s