免费企业自助建站信息发布网,wordpress集成环境搭建,自己做平台需要多少钱,企业网络营销策划方案设计的例子3.1SQL概述及数据定义1.SQL概述#xff08;1#xff09;什么是SQL? SQL#xff08;Structured Query Language#xff09;结构化查询语言#xff0c;是关系数据库的标准语言 SQL是一个通用的、功能极强的关系数据库语言 #xff08;2#xff09;SQL的特点 ① 综合统一集…3.1SQL概述及数据定义1.SQL概述1什么是SQL?SQLStructured Query Language结构化查询语言是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言2SQL的特点① 综合统一集数据定义语言 DDL(Data Definition Language)数据操纵语言 DMLData Manipulation Language数据控制语言 DCL(Data Control Language) 功能于一体。可以独立完成数据库生命周期中的全部活动定义关系模式插入数据建立数据库对数据库中的数据进行查询和更新数据库重构和维护数据库安全性、完整性控制等用户数据库投入运行后可根据需要随时逐步修改模式不影响数据的运行。数据操作符统一② 高度非过程化非关系数据模型的数据操纵语言“面向过程”必须制定存取路径SQL只要提出“做什么”无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。③ 面向集合的操作方式非关系数据模型采用面向记录的操作方式操作对象是一条记录SQL采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合④ 以同一种语法结构提供多种使用方式SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言例如CCJava程序中供程序员设计程序时使用⑤ 语言简洁易学易用SQL功能极强完成核心功能只用了9个动词。SQL功能动词数据查询SELECT数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE3 SQL的基本概念SQL支持关系数据库三级模式结构基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图2.数据字典数据字典是关系数据库管理系统内部的一组系统表它记录了数据库中所有的定义信息包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理时数据字典中的信息是其重要依据。3.数据定义1数据定义概览SQL的数据定义功能: 模式定义、表定义、视图和索引的定义2模式的定义与删除—SCHEMA定义模式实际上定义了一个命名空间在这个空间中可以定义该模式包含的数据库对象例如基本表、视图、索引等。在CREATE SCHEMA中可以接受CREATE TABLECREATE VIEW和GRANT子句。CREATE SCHEMA 模式名 AUTHORIZATION 用户名[表定义子句|视图定义子句|授权定义子句]如果没有指定模式名那么模式名隐含为用户名① 定义模式dbo database owner 数据库的创建者,创建该对象的用户. guest 顾客 能够访问数据库中对象的数据, 要求dbo分配权限给guest, 一般给他查看的权限select这里我先创建一个数据库用户[例1]定义一个学生-课程模式S-TCREATE SCHEMA S-T AUTHORIZATION BitHachi; 为用户BitHachi定义了一个模式S-T如果没有指定模式名那么模式名隐含为用户名CREATE SCHEMA AUTHORIZATION BitHachi 模式名隐含为用户名BitHachi 这个不知道咋回事没出结果默认模式BitHachi没创建出来没显示[例2]为用户BitHachi创建了一个模式S-T并在其中定义了一个表TAB1。CREATE SCHEMA S-T AUTHORIZATION BitHachi CREATE TABLE TAB1( COL1 SMALLINT, COL2 INT, COL3 CHAR(20), COL4 NUMERIC(10,3), COL5 DECIMAL(5,2) );② 删除模式DROP SCHEMA 模式名 CASCADE|RESTRICTCASCADE(级联)删除模式的同时把该模式中所有的数据库对象全部删除RESTRICT(限制)如果该模式中定义了下属的数据库对象如表、视图等则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。以下是运行结果至于为什么是错误的暂且放在这里等熟悉相关知识之后再来解决。DROP SCHEMA S-T CASCADE;DROP SCHEMA S-T RESTRICT;3基本表的定义、删除与修改—TABLE① 定义基本表的标准格式CREATE TABLE 表名( 列名 数据类型[ 列级完整性约束条件 ] [列名 数据类型[ 列级完整性约束条件] ] ……… [表级完整性约束条件 ] );如果完整性约束条件涉及到该表的多个属性列则必须定义在表级上否则既可以定义在列级也可以定义在表级。示例② 数据类型SQL中域的概念用数据类型来实现定义表的属性时 需要指明其数据类型及长度,选用哪种数据类型,取值范围,要做哪些运算以下是通用数据类型不同数据库的数据类型可能有所不同可查相关文档。③ 修改基本表ALTER TABLE 表名 [ ADD 新列名 数据类型 [ 完整性约束 ] ] [ DROP 完整性约束名 ] [ ALTER COLUMN列名 数据类型 ][例8]向Student表增加“入学时间”列其数据类型为日期型。不论基本表中原来是否已有数据新增加的列一律为空值。ALTER TABLE Student ADD S_entrance DATE;[例9]将年龄的数据类型由字符型假设原来的数据类型是字符型改为整数。ALTER TABLE Student ALTER COLUMN Sage INT;[例10]增加课程名称必须取唯一值的约束条件。ALTER TABLE Course ADD UNIQUE(Cname);④ 删除基本表标准格式DROP TABLE 表名RESTRICT| CASCADERESTRICT删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象则此表不能被删除CASCADE删除该表没有限制。在删除基本表的同时相关的依赖对象一起删除[例11] 删除Student表DROP TABLE Student CASCADE ;基本表定义被删除数据被删除表上建立的索引、视图、触发器等一般也将被删除还是和上述删除模式情况一样加了CASCADE和RESTRUCT删除不了例12若表上建有视图选择RESTRICT时表不能删除CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE SdeptIS; DROP TABLE Student RESTRICT;[例12]如果选择CASCADE时可以删除表视图也自动被删除这里还是跟之前的情况一样删除不了可能是我用的数据库不同叭DROP TABLE Student CASCADE; --NOTICE: drop cascades to view IS_Student SELECT * FROM IS_Student; --ERROR: relation IS_Student does not exist4索引的建立与删除—INDEX建立索引的目的·加快查询速度·谁可以建立索引DBA 或 表的属主即建立表的人DBMS一般会自动建立以下列上的索引PRIMARY KEYUNIQUE谁维护索引DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引RDBMS中索引一般采用B树、HASH索引来实现B树索引具有动态平衡的优点HASH索引具有查找速度快的特点采用B树还是HASH索引 则由具体的RDBMS来决定索引是关系数据库的内部实现技术属于内模式的范畴CREATE INDEX语句定义索引时可以定义索引是唯一索引、非唯一索引或聚簇索引① 建立索引的标准格式语句格式CREATE [UNIQUE] [CLUSTER] INDEX 索引名 ON 表名(列名[次序][,列名[次序] ]…)[例13]CREATE CLUSTERED INDEX Stusname ON Student(Sname);在Student表的Sname姓名列上建立一个聚簇索引在最经常查询的列上建立聚簇索引以提高查询效率一个基本表上最多只能建立一个聚簇索引经常更新的列不宜建立聚簇索引[例14]为学生-课程数据库中的StudentCourseSC三个表建 立索引。CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);② 删除索引DROP INDEX 索引名 ON 表名;DROP INDEX 表名.索引名;删除索引时系统会从数据字典中删去有关该索引的描述。[例15] 删除Student表的Stusname索引DROP INDEX Stusno ON Student; //等价 DROP INDEX Student.Stusno;3.2数据查询1.SELECT语句的一般格式先从整体上了解一下SELECT的格式关键字的位置。SELECT [ALL|DISTINCT] 目标列表达式 [别名] [ 目标列表达式 [别名]] … FROM 表名或视图名 [别名] [ 表名或视图名 [别名]] … [WHERE 条件表达式] [GROUP BY列名1 [HAVING 条件表达式]] [ORDER BY 列名2 [ASC|DESC]2.单表查询1选择表中的若干列① 查询指定列查询指定列[例1] 查询全体学生的学号与姓名。SELECT Sno,Sname FROM Student;[例2] 查询全体学生的姓名、学号、所在系。SELECT Sname,Sno,Sdept FROM Student;② 查询全部列选出所有属性列在SELECT关键字后面列出所有列名 将目标列表达式指定为 *[例3] 查询全体学生的详细记录。SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; //两种方式 SELECT *FROM Student;③ 查询经过计算的值SELECT子句的目标列表达式可以为算术表达式字符串常量函数列别名❶ 算术表达式[例4] 查全体学生的姓名及其出生年份。这里假定目前年份是2004年。SELECT Sname,2004-Sage FROM Student;❷ 字符串常量及函数[例5] 查询全体学生的姓名、出生年份和所有系要求用小写字母表示所有系名这里假定目前年份是2004年。SELECT Sname,Year of Birth: , 2004-Sage, LOWER(Sdept) FROM Student;❸ 使用列别名改变查询结果的列标题SELECT Sname NAME,Year of Birth: BIRTH, 2000-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT FROM Student;2选择表中的若干元组行① 关键词DISTINCT去掉表中重复的行如果没有指定DISTINCT关键词则缺省为ALLSELECT Sno FROM SC; /*等价于*/ SELECT ALL Sno FROM SC;[例6] 查询选修了课程的学生学号。指定DISTINCT关键词去掉表中重复的行SELECT DISTINCT Sno FROM SC;② 查询满足条件的元组行常用的查询条件查询条件谓词比较!!!NOT上述比较运算符确定范围BETWEEN ANDNOT BETWEEN AND确定集合INNOT IN字符匹配LIKENOT LIKE空值IS NULLIS NOT NULL多重条件逻辑运算ANDORNOT❶ 比较大小[例7]查询计算机科学系全体学生的名单。SELECT Sname FROM Student WHERE SdeptCS;[例8] 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname,Sage FROM Student WHERE Sage 20;[例9]查询考试成绩有不及格的学生的学号。SELECT DISTINCT Sno FROM SC WHERE Grade60;❷ 确定范围谓词:BETWEEN … AND …NOT BETWEEN … AND …[例10] 查询年龄在20~23岁包括20岁和23岁之间的学生的SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;[例11] 查询年龄不在20~23岁之间的学生姓名、系别和年龄SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;❸ 确定集合谓词IN 值表,NOT IN 值表[例12]查询信息系IS、数学系MA和计算机科学系CS学生的姓名和性别。SELECT Sname,Ssex FROM Student WHERE Sdept IN ( IS,MA,CS );[例13]查询既不是信息系、数学系也不是计算机科学系的学生的姓名和性别。SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ( IS,MA,CS );❹ 字符匹配谓词[NOT] LIKE ‘匹配串’ [ESCAPE ‘ 换码字符’]匹配串为固定字符串[例14] 查询学号为201215121的学生的详细情况。SELECT * FROM Student WHERE Sno LIKE 201215121; /*等价于*/ SELECT * FROM Student WHERE Sno 201215121;匹配串为含通配符的字符串[例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 刘%;❺ 使用换码字符’将通配符转义为普通字符ESCAPE 表示“ ” 为换码字符[例19] 查询DB_Design课程的课程号和学分。SELECT Cno,Ccredit FROM Course WHERE Cname LIKE DB\_Design ESCAPE \;[例20] 查询以DB_开头且倒数第3个字符为 i的课程的详细情况。SELECT * FROM Course WHERE Cname LIKE DB\_%i_ _ ESCAPE \;❻ 涉及空值的查询谓词IS NULLIS NOT NULL“IS” 不能用 “” 代替[例21] 某些学生选修课程后没有参加考试所以有选课记录但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL;[例22] 查所有有成绩的学生学号和课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;❼ 多重条件查询逻辑运算符AND和 OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词[NOT] IN[NOT] BETWEEN … AND …[例23] 查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM Student WHERE Sdept CS AND Sage20;改写[例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;3ORDER BY子句ORDER BY子句可以按一个或多个属性列排序升序ASC降序DESC缺省值为升序当排序列含空值时ASC排序列为空值的元组最后显示DESC排序列为空值的元组最先显示[例24] 查询选修了3号课程的学生的学号及其成绩查询结果按分数降序排列。SELECT Sno,Grade FROM SC WHERE Cno 3 ORDER BY Grade DESC;[例25] 查询全体学生情况查询结果按所在系的系号升序排列同一系中的学生按年龄降序排列。SELECT * FROM Student ORDER BY Sdept,Sage DESC;4聚集函数聚集函数计数COUNT[DISTINCT|ALL] *COUNT[DISTINCT|ALL] 列名计算总和SUM[DISTINCT|ALL] 列名计算平均值AVG[DISTINCT|ALL] 列名最大最小值MAX[DISTINCT|ALL] 列名MIN[DISTINCT|ALL] 列名[例26] 查询学生总人数。SELECT COUNT(*) FROM Student;[例27] 查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno) FROM SC;[例28] 计算2号课程的学生平均成绩。SELECT AVG(Grade) FROM SC WHERE Cno 2;[例29] 查询选修2号课程的学生最高分数。SELECT MAX(Grade) FROM SC WHERE Cno 2;[例30]查询学生201215121选修课程的总学分数。SELECT SUM(Ccredit) FROM SC, Course WHERE Sno201215121 AND SC.CnoCourse.Cno;5GROUP BY子句GROUP BY子句分组细化聚集函数的作用对象未对查询结果分组聚集函数将作用于整个查询结果对查询结果分组后聚集函数将分别作用于每个组作用对象是查询的中间结果表按指定的一列或多列值分组值相等的为一组HAVING短语与WHERE子句的区别作用对象不同WHERE子句作用于基表或视图从中选择满足条件的元组HAVING短语作用于组从中选择满足条件的组。[例31] 求各个课程号及相应的选课人数。SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;[例32] 查询选修了2门以上课程的学生学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 2;5.连接查询连接查询同时涉及多个表的查询连接条件或连接谓词用来连接两个表的条件一般格式[表名1.]列名1 比较运算符 [表名2.]列名2[表名1.]列名1 BETWEEN [表名2.]列名2 AND [表名2.]列名3连接字段连接谓词中的列名称连接条件中的各连接字段类型必须是可比的但名字不必是相同的1连接操作的执行过程① 嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组然后从头开始扫描表2逐一查找满足连接件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组。表2全部查找完后再找表1中第二个元组然后再从头开始扫描表2逐一查找满足连接条件的元组找到后就将表1中的第二个元组与该元组拼接起来形成结果表中一个元组。重复上述操作直到表1中的全部元组都处理完毕② 排序合并法(SORT-MERGE)常用于连接首先按连接属性对表1和表2排序对表1的第一个元组从头开始扫描表2顺序查找满足连接条件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时对表2的查询不再继续找到表1的第二条元组然后从刚才的中断点处继续顺序扫描表2查找满足连接条件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时对表2的查询不再继续重复上述操作直到表1或表2中的全部元组都处理完毕为止③ 索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组依次根据其连接字段值查询表2的索引从中找到满足条件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组2等值与非等值连接查询等值连接连接运算符为[例33] 查询每个学生及其选修课程的情况SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno SC.Sno;自然连接[例34] 对[例33]用自然连接完成。SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno SC.Sno;3自身连接自身连接一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性因此必须使用别名前缀[例35]查询每一门课的间接先修课即先修课的先修课SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno SECOND.Cno;4连接JOINSQL join 用于把来自两个或多个表的行结合起来。标准格式SELECT column_name(s) FROM table1//左表 xxx JOIN table2//右表 ON table1.column_nametable2.column_name;分类INNER JOIN (JOIN)LEFT JOIN (LEFT OUTER JOIN)RIGHT JOIN (RIGHT OUTER JOIN)FULL JOIN (FULL OUTER JOIN)这里就以SC和Course两个表来检验这四类连接① INNER JOIN (JOIN)INNER JOIN关键字在表中存在至少一个匹配时返回行。SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC INNER JOIN Course ON (SC.CnoCourse.Cno); /*INNER JOIN 与 JOIN结果相同*/ SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC JOIN Course ON (SC.CnoCourse.Cno);② LEFT JOIN (LEFT OUTER JOIN)LEFT JOIN关键字从左表table1返回所有的行即使右表table2中没有匹配。如果右表中没有匹配则结果为 NULL。SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC LEFT JOIN Course ON (SC.CnoCourse.Cno); /*LEFT JOIN 与 LEFT OUTER JOIN结果相同*/ SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC LEFT OUTER JOIN Course ON (SC.CnoCourse.Cno);③ RIGHT JOIN (RIGHT OUTER JOIN)RIGHT JOIN关键字从右表table2返回所有的行即使左表table1中没有匹配。如果左表中没有匹配则结果为 NULL。SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC RIGHT JOIN Course ON (SC.CnoCourse.Cno); /*RIGHT JOIN 与 RIGHT OUTER JOIN结果相同*/ SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC RIGHT OUTER JOIN Course ON (SC.CnoCourse.Cno);④ FULL JOIN (FULL OUTER JOIN)FULL JOIN关键字只要左表table1和右表table2其中一个表中存在匹配则返回行。结合了 LEFT JOIN 和 RIGHT JOIN 的结果。SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC FULL JOIN Course ON (SC.CnoCourse.Cno); /*FULL JOIN 与 FULL OUTER JOIN结果相同*/ SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit FROM SC FULL OUTER JOIN Course ON (SC.CnoCourse.Cno);5复合条件连接复合条件连接WHERE子句中含多个连接条件[例37]查询选修2号课程且成绩在88分以上的所有学生SELECT Student.Sno, Sname FROM Student,SC WHERE Student.Sno SC.Sno AND /* 连接谓词*/ SC.Cno 2 AND SC.Grade 88; /* 其他限定条件 */[例38]查询每个学生的学号、姓名、选修的课程名及成绩SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course /*多表连接*/ WHERE Student.Sno SC.Sno and SC.Cno Course.Cno;6.嵌套查询1嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询一个例子SELECT Sname/*外层查询/父查询*/ FROM Student WHERE Sno IN (SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno 2);子查询的限制 ·不能使用ORDER BY子句·层层嵌套方式反映了 SQL语言的结构化有些嵌套查询可以用连接运算替代2不相关子查询子查询的查询条件不依赖于父查询由里向外 逐层处理。即每个子查询在上一级查询处理之前求解子查询的结果用于建立其父查询的查找条件。3相关子查询子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组根据它与内层查询相关的属性值处理内层查询若WHERE子句返回值为真则取此元组放入结果表然后再取外层表的下一个元组重复这一过程直至外层表全部检查完为止4带有IN谓词的子查询[例39] 查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成① 确定“刘晨”所在系名SELECT Sdept FROM Student WHERE Sname 刘晨;② 查找所有在CS系学习的学生。SELECT Sno,Sname,Sdept FROM Student WHERE Sdept CS;将第一步查询嵌入到第二步查询的条件中SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname 刘晨);此查询为不相关子查询。[例40]查询选修了课程名为“信息系统”的学生学号和姓名SELECT Sno,Sname /*③ 最后在Student关系中取出Sno和Sname*/ FROM Student WHERE Sno IN ( SELECT Sno /*② 然后在SC关系中找出选修了3号课程的学生学号*/ FROM SC WHERE Cno IN ( SELECT Cno /*① 首先在Course关系中找出 “信息系统”的课程号,为3号*/ FROM Course WHERE Cname 信息系统 ) );用连接查询实现[例40]SELECT Student.Sno,Sname FROM Student,SC,Course WHERE Student.Sno SC.Sno AND SC.Cno Course.Cno AND Course.Cname信息系统;5带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时可以用、、、、 、!或 等比较运算符。与ANY或ALL谓词配合使用例假设一个学生只可能在一个系学习并且必须属于一个系则在[例39]可以用 代替INSELECT Sno,Sname,Sdept FROM Student WHERE Sdept (SELECT Sdept FROM Student WHERE Sname 刘晨); /*两种方式都可以*/ SELECT Sno,Sname,Sdept FROM Student WHERE (SELECT Sdept FROM Student WHERE Sname 刘晨) Sdept ;例41找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno, Cno FROM SC x WHERE Grade (SELECT AVG(Grade) /*相关子查询*/ FROM SC y WHERE y.Snox.Sno );例41可能的执行过程1.从外层查询中取出SC的一个元组x将元组x的Sno值201215121传送给内层查询。SELECT AVG(Grade) FROM SC y WHERE y.Sno201215121;2.执行内层查询得到值88近似值用该值代替内层查询得到外层查询SELECT Sno, Cno FROM SC x WHERE Grade 88;3.执行这个查询得到200215121120021512134.外层查询取出下一个元组重复做上述1至3步骤直到外层的SC元组全部处理完毕。结果为:6带有ANYSOME或ALL谓词的子查询谓词语义ANY任意一个值ALL所有值需要配合使用比较运算符: ANY 大于子查询结果中的某个值 ALL 大于子查询结果中的所有值 ANY 小于子查询结果中的某个值 ALL 小于子查询结果中的所有值 ANY 大于等于子查询结果中的某个值 ALL 大于等于子查询结果中的所有值 ANY 小于等于子查询结果中的某个值 ALL 小于等于子查询结果中的所有值 ANY 等于子查询结果中的某个值ALL 等于子查询结果中的所有值通常没有实际意义!或ANY 不等于子查询结果中的某个值!或ALL 不等于子查询结果中的任何一个值[例42] 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄SELECT Sname,Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept CS) AND Sdept CS ; /*父查询块中的条件 */执行过程关系数据库管理系统Relational Database Management SystemRDBMS1.RDBMS执行此查询时首先处理子查询找出 CS系中所有学生的年龄构成一个集合(2019)2. 处理父查询找所有不是CS系且年龄小于 20 或 19的学生用聚集函数实现[例42]SELECT Sname,Sage FROM Student WHERE Sage (SELECT MAX(Sage) FROM Student WHERE Sdept CS) AND Sdept CS;[例43] 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。方法一用ALL谓词SELECT Sname,Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept CS) AND Sdept CS;方法二用聚集函数SELECT Sname,Sage FROM Student WHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept CS) AND Sdept CS;7带有EXISTS谓词的子查询EXISTS谓词存在量词 ∃带有EXISTS谓词的子查询不返回任何数据只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空则外层的WHERE子句返回真值若内层查询结果为空则外层的WHERE子句返回假值由EXISTS引出的子查询其目标列表达式通常都用* 因为带EXISTS的子查询只返回真值或假值给出列名无实际意义NOT EXISTS谓词若内层查询结果非空则外层的WHERE子句返回假值若内层查询结果为空则外层的WHERE子句返回真值[例44]查询所有选修了1号课程的学生姓名。思路分析本查询涉及Student和SC关系在Student中依次取每个元组的Sno值用此值去检查SC关系若SC中存在这样的元组其Sno值等于此Student.Sno值并且其Cno‘1’则取此Student.Sname送入结果关系1.用嵌套查询SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE SnoStudent.Sno AND Cno 1);2.用连接运算SELECT Sname FROM Student, SC WHERE Student.SnoSC.Sno AND SC.Cno 1;[例45] 查询没有选修1号课程的学生姓名。SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM SC WHERE SnoStudent.Sno AND Cno 1);不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换用EXISTS/NOT EXISTS实现全称量词(难点)SQL语言中没有全称量词∀For all可以把带有全称量词的谓词转换为等价的带有存在量词的谓词例[例39]查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换SELECT Sno,Sname,Sdept FROM Student S1 WHERE EXISTS(SELECT * FROM Student S2 WHERE S2.Sdept S1.Sdept AND S2.Sname 刘晨);[例46] 查询选修了全部课程的学生姓名。SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno Student.Sno AND Cno Course.Cno) );用EXISTS/NOT EXISTS实现逻辑蕴函(难点)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为[例47]查询至少选修了学生201215122选修的全部课程的学生号码。用NOT EXISTS谓词表示SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS(SELECT * FROM SC SCY WHERE SCY.Sno 201215122 AND NOT EXISTS(SELECT * FROM SC SCZ WHERE SCZ.SnoSCX.Sno AND SCZ.CnoSCY.Cno ) );7.集合查询1 集合操作的种类并操作UNION交操作INTERSECT差操作EXCEPT参加集合操作的各查询结果的列数必须相同对应项的数据类型也必须相同2集合操作举例[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。方法一SELECT * FROM Student WHERE Sdept CS UNION SELECT * FROM Student WHERE Sage19;UNION将多个查询结果合并起来时系统自动去掉重复元组。UNION ALL将多个查询结果合并起来时保留重复元组方法二SELECT DISTINCT * FROM Student WHERE Sdept CS OR Sage19;[例49] 查询选修了课程1或者选修了课程2的学生。SELECT Sno FROM SC WHERE Cno1 UNION SELECT Sno FROM SC WHERE Cno 2;[例50] 查询计算机科学系的学生与年龄不大于19岁的学生的交集SELECT * FROM Student WHERE SdeptCS INTERSECT SELECT * FROM Student WHERE Sage19;[例50] 实际上就是查询计算机科学系中年龄不大于19岁的学生SELECT * FROM Student WHERE Sdept CS AND Sage19;[例51] 查询选修课程1的学生集合与选修课程2的学生集合的交集SELECT Sno FROM SC WHERE Cno1 INTERSECT SELECT Sno FROM SC WHERE Cno2;[例51]实际上是查询既选修了课程1又选修了课程2 的学生SELECT Sno FROM SC WHERE Cno1 AND Sno IN (SELECT Sno FROM SC WHERE Cno2);[例52] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。SELECT * FROM Student WHERE SdeptCS EXCEPT SELECT * FROM Student WHERE Sage 19;[例52]实际上是查询计算机科学系中年龄大于19岁的学生SELECT * FROM Student WHERE Sdept CS AND Sage19;