关系数据库语言SQL.ppt

第三章 关系数据库语言SQL,3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制,结构化查询语言SQL,SQL介于关系代数与关系演算之间 SQL集数据查询、数据操纵、数据定义和数据控制功能 于一体。 1、综合统一（DDL,DML,DCL） 2、高度非过程化 3、面向集合的操作方式 4、两种使用方式使用统一的语法格式 5、语言简便易学,1、综合统一,SQL集DDL、DML、DCL功能于一体。 SQL数据操作符的统一。(查询、插入、删除、修改都 只有一种操作符。),2、高度非过程化,SQL进行数据操作，只要提出“What to do”，无需 告诉系统“How to do” 充分体现关系系统的特点和优势 有利于提高数据的独立性,3、面向集合的操作方式,关系运算“一次一集合”方式的体现。,4、两种使用方式使用同一种语法结构,既是自含式语言，又是嵌入式语言。,5、语言简便易学,SQL设计巧妙，核心功能只需9个动词。在语言上 接近英语。,SQL对象与三级模式结构的对应关系,外模式 视图、部分基本表 模式 基本表 内模式 存储文件,SQL,视图1,视图2,基本表1,基本表2,基本表3,基本表4,存储文件1,存储文件2,外模式,模式,内模式,T-SQL的定义功能,对基本表、视图、索引的定义、删除和修改。,基本表是本身独立存在的表，在SQL中一个关系就对应 一个表。 一些DBMS中一个基本表对应一个存储文件，有的DBMS一 个数据库中的多个基本表对应一个存储文件 一个表可以带若干索引，索引也存放在存储文件中.,定义删除和修改基本表,一、基本表的定义 CREATE TABLE （ 列名完整性约束条件 , 列名完整性约束条件. ,）,完整性约束： NOT NULL：属性值不能取空值（不用于表级） DEFAULT： 给定列的默认值（不用于表级） UNIQUE： 属性值唯一（不唯一） PRIMARY KEY（列名） CHECK（验证条件）: 例如CHECK (AGE16) FOREIGN KEY（列名） REFERENCE （表名2.列名）,例：建立一个学生数据库，里面包括三个基本表：学生表 (Student)，课程表(Course)，以及选修表(SC)。,Student,Course,SC,定义学生表的基本SQL语句如下：,CREATE TABLE Student( Sno CHAR(4) PRIMARY KEY, Sname VARCHAR(10) NOT NULL, Gender CHAR(2) CHECK(Gender=男 OR Gender=女), Age INT CHECK(Age=15 AND Age=45), Dept VARCHAR(20) DEFAULT 计算机系 ),CREATE TABLE Course ( Cno CHAR(3) Cname VARCHAR(10) NOT NULL, Teacher VARCHAR(10) NOT NULL, PRIMARY KEY(Cno),定义课程表的基本SQL语句如下：,CREATE TABLE SC( Sno CHAR(4) NOT NULL, Cno CHAR(3) NOT NULL, Grade INT CHECK(Grade=0 AND Grade=100), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student (Sno), FOREIGN KEY (Cno) REFERENCES Course (Cno),定义选修表的基本SQL语句如下：,约束更完整的形式： CREATE TABLE SC( Sno CHAR(4), Cno CHAR(3), 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) );,二、修改基本表 ALTER TABLE ALTER COLUMN ADD COLUMN 约束 DROP COLUMN ADD PRIMARY KEY (列名,N) ADD FOREIGN KEY(列名) REFERENCES 表名(列名),例1：为课程表增加新列学分，类型为整数 ALTER TABLE Course ADD Credit INT 基本表中原来已有数据，新增加的列一律为空值。,例2：将课程名的数据类型转换为长度为30的字符串 ALTER TABLE Course ALTER COLUMN Cname CHAR(30),例3：将课程表的学分列删除掉,例4：将选修表的成绩列的检查约束删除掉,ALTER TABLE Course DROP COLUMN Credit,ALTER TABLE Course DROP CONSTAINT CK_grade,T-SQL实现完整性,1、实现实体完整性，主键约束(PRIMARY KEY),ALTER TABLE ADD CONSTRAINT 约束名（以PK_开头） PRIMARY KEY ( ,N),2、实现参照完整性，外键约束(FOREIGN KEY()REFERENCES),ALTER TABLE ADD CONSTRAINT 约束名 （以FK_开头） FOREIGN KEY ( ) REFERENCES 引用表名,3、实现用户自定义完整性，其他约束,添加UNIQUE约束 ALTER TABLE ADD CONSTRAINT 约束名 （以IX_开头） UNIQUE ( ,N),添加默认值（DEFAULT）约束 ALTER TABLE ADD CONSTRAINT 约束名 （以DF_开头） DEFAULT 默认值 FOR ,添加检查（CHECK）约束 ALTER TABLE ADD CONSTRAINT 约束名 （以CK_开头） CHECK (逻辑表达式),三、删除基本表 DROP TABLE ,T-SQL的查询功能,SELECT FROM WHERE GROUP BY组的提取条件 ORDER BY SELECT用于指定输出的字段，FROM用于指定数据的来源表， WHERE用于指定元组的选择条件。GROUP BY是对选择的结果 进行分组操作，ORDER BY用于对查询结果进行排序操作。,简单的SQL查询与关系代数的联系： SELECT A1,A2,An FROM T1,T2,Tk WHERE F; 相当于： A1,A2,An F(T1T2 Tk) 其中F中有的可能是连接条件，与后面的广义笛卡儿集构 成连接。,单表查询,一、选择表中的若干列 1.查询指定列 语法：SELECT FROM 表名 例1、查询全体学生的学号与姓名。 SELECT Sno,Sname FROM Student; 中各个列的先后顺序可以与表中的顺序 不一致。也就是说，用户在查询时可以根据应用的需要改 变列的显示顺序。,2.查询全部列 例2 查询全体学生的详细记录 SELECT * FROM Student; 该SELECT语句实际上是无条件地把Student表的全部信 息都查询出来，所以也称为全表查询，这是最简单的一种 查询。,不仅可以使用列名，还可以使用算术 表达式、字符串常量和函数等。 3. 查询经过计算的值 例3 查全体学生的姓名、出生年份 SELECT Sname,2006-Sage FROM Student;,改变列标题（MS SQL SERVRE)用AS SELECT Sname AS Name, 2006-Sage AS BirthYear FROM Student;,二、选择表中的若干元组 1.消除取值重复的行(使用DISTINCT) 例4 查所有选修过课的学生的学号 SELECT Sno FROM SC; 结果中有重复的行。 一个学生选多少门课，其学号就出现多少次。,DISTINCT用于消重 SELECT DISTINCT Sno FROM SC; 即可消除重复。,2. 查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。,(1) 比较大小 使用比较运算符=,=,!= 或 NOT加比较运算符 例5 查询计算机系全体学生的名单。 SELECT Sname FROM Student WHERE Dept = 计算机,补充例：查询选修001号课程成绩在90分以上(含) 的学生的学号。 SELECT Sno FROM SC WHERE Cno = 001 AND Grade=90;,(2) 确定范围 BETWEEN AND 与NOT BETWEEN AND 更接近自然语言，语义更明确。 但都可以表示为用逻辑与连接的两个比较。,例6 查询年龄在2023岁（包括）之间的学生的姓名、 系别和年龄。 SELECT Sname, Dept, Age FROM Student WHERE Age BETWEEN 20 AND 23;,当然也可表示为： SELECT Sname, Dept, Age FROM Student WHERE Age=20 AND Age =23;,(3) 确定集合 IN与NOT IN 例7 查询计算机系和外语系的学生姓名和年龄 SELECT Sname, Age FROM Student WHERE Dept IN (计算机, 外语系);,前面的例6也可以用谓词IN表达： SELECT Sname, Dept, Age FROM Student WHERE Age IN (20,21,22,23),(4)字符匹配 LIKE（主要用于模糊查询） 通配符：% _ %：表示任意长度的字符串（包括0个或多个字符） _：表示单个字符 ：匹配 中的任意一个字符。例如ABC表示匹配A,B,C 中的任何一个字符，a-z表示匹配a到z之间的任一字符。 ：不匹配 中的任意一个字符。例如ABC表示不匹配 A,B,C中的任何一个字符。,%,%表示0或多个字符 例8 查询姓李的学生的基本情况 SELECT * FROM Student WHERE Sname LIKE 李%,_,_表示单个字符 例9 查询姓名中第二个字是平的学生的基本情况 SELECT * FROM Student WHERE Sname LIKE _平%,表示匹配括号中的任何字符 例10 查询姓名第二字为平或勇的学生姓名。 SELECT Sname FROM Student WHERE Sname LIKE _平勇%, ,表示不匹配括号中的任何字符 例11 查询姓名第二字不是为平和勇的学生姓名。 SELECT Sname FROM Student WHERE Sname LIKE _平勇%,新的问题：如何表示通配符%、_、和本身？,转义字符ESCAPE,例12 查询以MPEG_2开头的课程名 SELECT Cname FROM Course WHERE Cname LIKE MPEG_2% ESCAPE ,(5)涉及空值的查询 IS NULL和IS NOT NULL (注意：有的系统允许NULL比较，可以使用和，如 SQL Server。有的不允许，如Oracle。) 例13 查询缺少成绩的学生的学号及相应课程号。 SELECT Sno,Cno FROM SC WHERE Grade is NULL,(6) 逻辑连接查询 WHERE中用AND和OR连接多个条件。 例14 查询计算机科学系或数学系年龄在18到23岁的 学生的姓名。 SELECT Sname FROM Student WHERE (Dept=计算机 OR Dept=外语系) AND Age BETWEEN 18 AND 23;,三. 查询结果排序 如果没有指定查询结果的显示顺序，DBMS将按其最方便 的顺序（通常是元组在表中的先后顺序）输出查询结果。 ORDER BY 子句可明确指定结果序。 SELECT FROM WHERE ORDER BY ASC/DESC 默认为升序(ASC)，NULL值最小。,例15：查询计算机系(CS)学生的学号和姓名，按年龄从大到 小排，相同年龄的按学号升序排。 SELECT Sno, Sname FROM Student WHERE Dept=计算机 ORDER BY Age DESC, Sno ASC,注：这里说“NULL值最小”，仅仅针对NULL值排序的情况。 如果取“NULL值”的字段出现在条件表达式中，将使条件计 算为NULL，进而被排除于结果外。 例如，查找成绩在90分以下(含)的学生的学号： SELECT Sno FROM SC WHERE Grade=90； 成绩为NULL的学生的学号并不出现在结果中。,四、使用集函数 为了进一步方便用户，增强检索功能，SQL提供了许多集 函数，主要包括： COUNT(*) 统计元组个数 COUNT(DISTINCT|ALL ) 统计一列中值的个数 SUM(DISTINCT|ALL ) 计算一列值的总和 AVG(DISTINCT|ALL ) 计算一列值的平均值 MAX(DISTINCT|ALL ) 求一列值中的最大值 MIN(DISTINCT|ALL ) 求一列值中的最小值,如果指定DISTINCT短语，则表示在计算时要取消指定列 中的重复值。如果不指定DISTINCT短语或指定ALL短语 （ALL为缺省值），则表示不取消重复值。 例16：查询学生总人数 SELECT COUNT (*) FROM student; 或 SELECT COUNT (Sno) FROM student;,例17 查询选修了课程的学生人数 SELECT COUNT(DISTINCT Sno) FROM SC; 学生每选修一门课，在SC中都有一条相应的记录，而一 个学生一般都要选修多门课程，为避免重复计算学生人 数，必须在COUNT函数中用DISTINCT短语.,注：NULL值的影响： COUNT(*)总是返回记录的个数 COUNT(字段)返回指定字段值非空的记录个数。 例18，求001号课程总共有多少个得分： SELECT COUNT(*) FROM SC WHERE Cno001； 结果中包含成绩为空的记录，而,SELECT COUNT(Grade) FROM SC WHERE Cno001； 结果中则不包含成绩为空的记录。,补充例：查询学号为1000的最高分，最低分和平均分？,SELECT MAX(Grade),MIN(Grade),AVG(Grade) FROM SC WHERE Sno = 1000,五、对查询结果分组 GROUP BY子句可以将查询结果表的各行按一列或多列 取值相等的原则进行分组。 对查询结果分组的目的是为了细化集函数的作用对象。 如果未对查询结果分组，集函数将作用于整个查询结果， 即整个查询结果只有一个函数值。否则，集函数将作用于 每一个组，即每一组都有一个函数值。,注：SQL规定，所有NULL值的记录在分组时被作为一组。 例19.对选002号课程的学生进行成绩分组，计算每一成 绩的人数。 SELECT COUNT(*),Grade FROM SC WHERE Cno=002 GROUP BY Grade;,例20：查询每个学生选修课程的门数。 查询每门课程的选修学生人数。,SELECT Sno, COUNT(*) FROM SC GROUP BY Sno SELECT Cno, COUNT(*) FROM SC GROUP BY Cno,注：分组后，一些详细信息可能损失，不能出现在SELECT 结果中。 例如，下面的查询 SELECT Sno,COUNT(*),Grade FROM SC WHERE Cno=2 GROUP BY Grade; 将出错，想一想，为什么？,一般来说，分组查询的SELECT目标列中只允许出现聚集 函数和GROUP BY子句中出现过的列。,分组后可使用HAVING条件对分组的结果进行筛选。 HAVING子句针对的是 GROUP分组，WHERE针对的是元组， 两者并不矛盾。 例21. 查询选修了三门或三门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING (COUNT(Cno)=3);,？查询有三科或三科以上成绩在80分以上的学生学号。,SELECT Sno FROM SC WHERE Grade=80 GROUP BY Sno HAVING (COUNT(Cno)=3);,三种对查询结果的操作可以单独使用，也可以结合起来使 用完成更多更复杂的查询。,例22、统计每个系有多少学生，结果按人数的降序排列。,SELECT Dept, COUNT(*) FROM Student GROUP BY Dept ORDER BY 2 DESC,例23. 求出男同学每一年龄（要求查找超过2人的）有多少 人？要求查询结果按人数升序排列，人数相同的按年龄降 序排列。,SELECT Age,COUNT(*) FROM Student WHERE Gender =男 GROUP BY Age HAVING COUNT(*)2 ORDER BY 2,Age DESC,连接查询,一个数据库中的多个表之间一般都存在某种内在联系， 它们共同提供有用的信息。前面的查询都是针对一个表进行 的。若一个查询同时涉及两个以上的表，则称之为连接查询 。连接查询主要包括等值连接、非等值连接查询、自身连接 查询、外连接查询和复合条件连接查询。,一、等值与非等值连接查询 用来连接两个表的条件称为连接条件或连接谓词，其 一般格式为： WHERE . . 在实践中比较运算符为的等值连接使用十分广泛。,连接谓词中的列名称为连接字段。连接条件中的各连接 字段类型必须是可比的，但不必是相同的。例如，可以都是 字符型，或都是日期型；也可以一个是整型，另一个是实型 ，整型和实型都是数值型，因此是可比的。但若一个是字符 型，另一个是整数型就不允许了，因为它们是不可比类型。,从概念上讲DBMS执行连接操作的过程是，首先在表1中找 到第一个元组，然后从头开始顺序扫描或按索引扫描表2， 查找满足连接条件的元组，每找到一个元组, 就将表1中的 第一个元组与该元组拼接起来，形成结果表中一个元组。表 2全部扫描完毕后，再到表1中找第二个元组，然后再从头开 始顺序扫描或按索引扫描表2，查找满足连接条件的元组， 每找到一个元组, 就将表1中的第二个元组与该元组拼接起 来，形成结果表中一个元组。重复上述操作，直到表1全部 元组都处理完毕为止。(注意：实际上要考虑查询的优化),一、广义笛卡尔积,不带连接谓词的连接 很少使用 例： SELECT Student.* ,SC.* FROM Student, SC,二、等值与非等值连接查询,等值连接、自然连接、非等值连接,例24 查询每个学生及其选修课程的情况。 学生情况存放在Student表中，学生选课情况存放在 SC表中，所以本查询要把Student与SC表的数据通过两个 表都具有的属性Sno (外码连接)实现的。这是一个等值连 接。完成本查询的SQL语句为： SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno;,自然连接,等值连接的一种特殊情况，公共属性相等，并把目标列中重复的属性列去掉。 例25 对前例用自然连接完成。 SELECT Student.Sno，Sname，Ssex，Sage， Sdept，Cno，Grade FROM Student，SC WHERE Student.Sno = SC.Sno；,注：实际上，上述作法只是基本连接形式之一，称为 “交叉连接”或“叉积连接”。 除交叉连接外，还有“内连接”和“外连接”两种基本形 式。,例如，例24可改为“内连接”形式： SELECT * FROM Student INNER JOIN SC ON Student.Sno=SC.Sno;,三、自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表 与其自己进行连接，这种连接称为表的自身连接。 需要给表起别名以示区别； 由于所有属性名都是同名属性，因此必须使用别名前缀,例25. 查询至少选修了001和002号课程的学生学号。,SELECT X.Sno FROM SC X, SC Y WHERE X.Sno=Y.Sno AND X.Cno=001 Y.Cno= 002,四、外连接 在通常的连接操作中，只有满足连接条件的元组才能作 为结果输出，如在例24和例25的结果表中没有关于那些没有 选课的学生的信息，因为他们在SC表中没有相应的选课元组 。但是有时我们想以Student表为主体列出每个学生的基本 情况及其选课情况，若某个学生没有选课，则只输出其基本 情况信息，其选课信息为空值即可，这时就需要使用外连接 （Outer Join）。,外连接的语法结构： SELECT FROM 表1 LEFT|RIGHT|FULL OUTER JOIN 表2 ON ,例24的交叉连接解法。 SELECT Student.Sno, Sname, Gender, Age, Dept, Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno(*);,这一作法MS SQL Server不支持。 MS SQL Server采用外连接形式： SELECT * FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno;,有认为：这是右外连接。有分歧。 实际上，SQL对左、右、全外连接的定义是： 左向外联接的结果集返回左表的所有行，而不仅仅是 联接列所匹配的行。如果左表的某行在右表中没有匹 配行，则在相关联的结果集行中右表的所有选择列表 列均为空值。 右向外联接返回右表的所有行。如果右表的某行在左 表中没有匹配行，则将为左表返回空值。 全外部联接返回左表和右表中的所有行。当某行在另 一个表中没有匹配行时，则另一个表的选择列表列包 含空值。如果表之间有匹配行，则整个结果集行包含 基表的数据值。,复杂例：对上例修改，再外连接Course SELECT * FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno FULL JOIN Course ON SC.Cno=Course.Cno;,五、复合条件连接 上面各个连接查询中，WHERE子句中只有一个条件，即 用于连接两个表的谓词。WHERE子句中有多个条件的连接 操作，称为复合条件连接。 例26. 查询选修002号课程且成绩在90分以上的外语系 或计算机系学生的学号和姓名。,SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND (Student.Dept=计算机 OR Student.Dept=外语系) AND SC.Cno=002 AND SC.Grade90,例27：查询女同学基本情况和其选修的课程号和成绩。,SELECT Student.* ,Cno, Grade FROM Student, SC WHERE Student.Sno=SC.Sno AND Student.Gender =女,嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个 查询块。将一个查询块嵌套在另一个查询块的WHERE子句 或HAVING短语的条件中的查询称为嵌套查询或子查询。 外层的SQL语句我们称为外层查询或父查询，内层的SQL语 句我们称为内层查询或子查询，而且SQL语句允许多重嵌 套，子查询还可以嵌套其他的子查询。,SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno=002),嵌套查询的求解方法是由里向外处理。即每个子查询在 其上一级查询处理之前求解，子查询的结果用于建立其父 查询的查找条件。 嵌套查询使得可以用一系列简单查询构成复杂的查询， 从而明显地增强了SQL的查询能力。以层层嵌套的方式来 构造程序正是 SQL(Structurred Query Language)中“结 构化”的含义所在。,嵌套查询分类,不相关子查询 子查询的查询条件不依赖于父查询 相关子查询 子查询的查询条件依赖于父查询,一、带有IN谓词的子查询 带有IN谓词的子查询是指父查询与子查询之间用IN进 行连接，判断某个属性列值是否在子查询的结果中。由 于在嵌套查询中，子查询的结果往往是一个集合，所以 谓词IN是嵌套查询中最经常使用的谓词。,例28.查询与“张三”在同一个系学习的学生姓名。 查询与“张三”在同一个系学习的学生，可以首先确定“张 三”所在系名，然后再查找所有在该系学习的学生。所以可 以分步来完成此查询： 确定“张三”所在系名 SELECT Dept 结果为 FROM Student Dept WHERE Sname=张三; - 计算机,查找所有在计算机学习的学生姓名。 SELECT Sname FROM Student WHERE Sdept=计算机; 结果为 sname - 张三 李四 王五,可表为嵌套查询的形式： SELECT Sname FROM Student WHERE Dept IN (SELECT Dept FROM Student WHERE Sname=张三); 本例中的查询也可以用我们前面学过的表的自身连接查询 来完成。,例29：查询选修了课程号为001的学生姓名。 SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE SC.Cno=001),例30：查询选修了操作系统的学生姓名 SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname=操作系统),例29和例30中的各个子查询都只执行一次，其结果用于 父查询，子查询的查询条件不依赖于父查询，这类子查询 称为不相关子查询。不相关子查询是最简单的一类子查询。,相关子查询例，查询选修了001号课程的学生姓名。,SELECT Sname FROM Student WHERE 001 IN( SELECT Cno FROM SC WHERE Sno=Student.Sno),二、带有比较运算符的子查询 带有比较运算符的子查询是指父查询与子查询之间用比较 运算符进行连接。当用户能确切知道内层查询返回的是单值 时，可以用、 =、等比较运算符。,在例28中，由于一个学生只可能在一个系学习，也就是说 内查询张三所在系的结果是一个唯一值，因此该查询也可 以用比较运算符来实现，其SQL语句如下： SELECT Sno, Sname, Dept FROM Student WHERE Dept = (SELECT Sdept FROM Student WHERE Sname=张三);,这一作法可能有问题：如果两个或两个以上系都有叫做 “张三”的学生，查询将出错。 换一个例子：查询选修002号课程的成绩最高的学生的 学号。,SELECT Sno FROM SC WHERE Cno=002 AND Grade= (SELECT MAX(Grade) FROM SC WHERE Cno=002),需要注意的是，子查询一定要跟在比较符之后，下列写法 是错误的： SELECT Sno, Sname, Dept FROM Student WHERE (SELECT Dept FROM Student WHERE Sname=张三) = Dept; 这一作法SQL SERVER支持,例30中操作系统的课程号是唯一的，但选修该课程的学生 并不只一个，所以例30也可以用=运算符和IN谓词共同完成 SELECT Sno, Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno =( SELECT Cno FROM Course WHERE Cname=操作系统),三、带有限量谓词的子查询（SOME/ANY，ALL） 限量谓词必须与比较运算符结合起来使用。 SOME 大于子查询结果中的某个值 ALL 大于子查询结果中的所有值 SOME 不等于子查询结果中的某个值（无意义） ALL 不等于子查询结果中的所有值（无意义） ALL 不等于子查询结果中的任何一个值（NOT IN）,例31. 查询所有未选课的学生姓名。可用谓词（NOT IN） SELECT Sname FROM Student WHERE Sno NOT IN(SELECT Sno FROM SC),当然，上个例子也可以改写为用限量谓词表示：,SELECT Sname FROM Student WHERE SnoALL(SELECT Sno FROM SC),例32：检索选修了001课程的学生学号和姓名,SELECT Sno, Sname FROM Student WHERE Sno =SOME(SELECT Sno FROM SC WHERE Cno=001),例33：检索平均成绩最高的学生学号。,SELECT Sno FROM SC GROUP BY Sno HAVING AVG(Grade)=ALL(SELECT AVG(Grade) FROM SC GROUP BY Sno),例34：查询其他系中比计算机系所有年龄都小的学生 姓名和年龄。,SELECT Sname ,Age FROM Student WHERE Age计算机系,当然，上个例子也可以改写为用其他方式表示：,SELECT Sname,Age FROM Student WHERE Age计算机系,例35：查询至少有一门成绩超过学生1001一门成绩的学 生学号。,SELECT Sno FROM SC WHERE Grade SOME(SELECT Grade FROM SC WHERE Sno=1001) AND Sno1001,四、带存在量词EXISTS的子查询 EXISTS代表存在量词彐。带有EXISTS谓词的子查询不 返回任何实际数据，它只产生逻辑真值“true”或逻辑假值 “false“。,例36 查询所有选修了001号课程的学生姓名 查询所有选修了001号课程的学生姓名涉及Student关 系和SC关系，我们可以在Student关系中依次取每个元组 的Sno值，用此Student.Sno值去检查SC关系，若SC中存在 这样的元组，其SC.Sno值等于用来检查的Student.Sno值， 并且其SC.Cno=001，则取此Student.Sname送入结果关系。 将此想法写成SQL语句就是：,SELECT Sno, Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Student.Sno=SC.Sno AND Cno=001),使用存在量词EXISTS后，若内层查询结果非空，则外层 的WHERE子句返回真值，否则返回假值。 由EXISTS引出的子查询，其目标列表达式通常都用*，因 为带EXISTS的子查询只返回真值或假值，给出列名亦无实际 意义。 这类查询与我们前面的不相关子查询有一个明显区别，即子 查询的查询条件依赖于外层父查询的某个属性值（在本例中 是依赖于Student表的Sno值），我们称这类查询为相关子查 询.,求解相关子查询不能象求解不相关子查询那样，一次将子 查询求解出来，然后求解父查询。相关子查询的内层查询由 于与外层查询有关，因此必须反复求值。从概念上讲，相关 子查询的一般处理过程是： 首先取外层查询中Student表的第一个元组，根据它与内层 查询相关的属性值（即Sno值）处理内层查询，若WHERE子句 返回值为真（即内层查询结果非空），则取此元组放入结果 表；然后再检查Student表的下一个元组；重复这一过程， 直至Student表全部检查完毕为止。,与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量 词NOT EXISTS后，若内层查询结果为空，则外层的WHERE 子句返回真值，否则返回假值。,例37 查询所有未修001号课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=001);,一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形 式的子查询等价替换，但所有带IN谓词、比较运算符、 SOME和ALL谓词的子查询都能用带EXISTS谓词的子查询等 价替换。例如带有IN谓词的例28可以用如下带EXISTS谓词 的子查询替换： SELECT Sno, Sname, Dept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept=S1.Dept AND S2.Sname=张三);,由于带EXISTS量词的相关子查询只关心内层查询是否有 返回值，并不需要查具体值，因此其效率并不一定低于不 相关子查询，甚至有时是最高效的方法。 SQL语言中没有全称量词（For all）。因此必须利用 谓词演算将一个带有全称量词的谓词转换为等价的带有存 在量词的谓词。,例38.查询选修了全部课程的学生姓名 由于没有全称量词，我们将题目的意思转换成等价的存 在量词的形式：查询这样的学生姓名，没有一门课程是他 不选的。该查询涉及三个关系，存放学生姓名的Student 表，存放所有课程信息的Course表，存放学生选课信息的 SC表。其SQL语句为：,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);,方法2：用集函数 SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) = (SELECT COUNT(*) FROM Course);,思考： SELECT Sname FROM Student,SC WHERE Student.Sno=SC.Sno GROUP BY SC.Sno HAVING COUNT(*) = (SELECT COUNT(*) FROM Course); 错在哪里？,SQL语言中也没有蕴函（Implication）逻辑运算。因此也 必须利用谓词演算将一个逻辑蕴函的谓词转换为等价的带 有存在量词的谓词： 例39.查询至少选修了学生1001选修的全部课程的学生学号,本题的查询要求可以做如下解释，查询这样的学生，凡 是1001选修的课，他都选修了。换句话说，若有一个学 号为x的学生，对所有的课程y，只要学号为1001的学生 选修了课程y，则x也选修了y；那么就将他的学号选出来。 它所表达的语义为：不存在这样的课程y，学生1001选修 了y，而学生x没有选。用SQL语言可表示如下：,SELECT DISTINCT Sno FROM SC X WHERE NOT EXISTS ( SELECT * FROM SC Y WHERE Y.Sno=1001 AND NOT EXISTS (SELECT * FROM SC Z WHERE Z.Sno=X.Sno AND Z.Cno=Y.Cno),集合查询,SELECT 语句的查询结果是元组的集合，多个SELECT语句 的结果可进行集合操作。集合操作主要有并操作UNION，交 操作INTERSECT和差操作EXCEPT。 注：集合操作的两个表必须有相同的表列数，在对应列上的 数据类型必须相同。,例40.查询计算机系或者年龄不大于19岁的学生姓名,SELECT Sname FROM Student WHERE Sdept=计算机系 UNION SELECT Sname FROM Student WHERE Sage=19,UNION,注：系统在并操作过程中自动去掉重复元组，如果要保留 重复的元组，使用UNION ALL操作符。查看下例输出结果,SELECT * FROM Student WHERE Sdept=计算机系 UNION ALL SELECT * FROM Student WHERE Sage=19,例41.查询选修了001或者002号课程的学生学号,SELECT Sno FROM SC WHERE Cno=001 UNION SELECT Sno FROM SC WHERE Cno=002,SELECT DISTINCT Sno FROM SC WHERE Cno=001 OR Cno=002,等价于,INTERSECT,例42.查询计算机系中年龄不大于19岁的学生基本信息,SELECT * FROM Student WHERE Sdept=计算机系 INTERSECT SELECT * FROM Student WHERE Sage=19,上例等价于,SELECT * FROM Student WHERE Sdept=计算机系AND Sage=19,例43.查询至少选修了001和002号课程的学生学号,SELECT Sno FROM SC WHERE Cno=001 INTERSECT SELECT Sno FROM SC WHERE Cno=002,EXCEPT,例43.查询计算机系中年龄不大于19岁的学生基本信息,SELECT * FROM Student WHERE Sdept=计算机系 EXCEPT SELECT * FROM Student WHERE Sage19,查询功能总结,1、设有两个关系R(A, B, C)和S(A, B, C)，用SQL语句表达 下列关系代数表达式。,SELECT A FROM R,SELECT R .* FROM R WHERE B=b2,SELECT R.*, S.* FROM R, S,SELECT R.* FROM R WHERE EXISTS (SELECT * FROM S WHERE R.A=S.A AND R.B=S.B AND R.C=S.C),SELECT * FROM R WHERE NOT EXISTS (SELECT * FROM S WHERE R.A=S.A AND R.B=S.B AND R.C=S.C),SELECT R.A, R.B, S.C FROM R,S WHERE R.B=S.B,1、查询至少选修了两门课程的学生学号。,SELECT DISTINCT X.Sno FROM SC X, SC Y WHERE X.Sno=Y.Sno AND X.CnoY.Cno,SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)=2,2、查询至少选修陈国老师一门课程的学生姓名。,SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Teacher=陈国),3、查询张三没有选修的课程名称。,SELECT Cname FROM Course WHERE Cno NOT IN(SELECT Cno FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sname=张三),4、统计有学生选修的课程门数。,SELECT COUNT(DISTINCT Cno) FROM SC,5、求陈国老师所授课程每门课程的学生平均成绩,SELECT Cno, AVG(Grade) FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Teacher=陈国) GROUP BY Cno,T-SQL的数据更新,一、插入数据 1、插入单个元组 语句的格式为： INSERT INTO (,.) VALUES ( ,.),如果某些属性列在INTO子句中没有出现，则新记录在这些 列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值。 如果INTO子句中没有指明任何列名，则新插入的记录必须 在每个属性列上均有值。,例1：往学生选修课程表中插入新的选课记录： 学生95301选修课程1003； 学生95302选修1001，成绩为87 INSERT INSERT INTO Student(Sno,Cn