《数据的高级操作》PPT课件.ppt

第6章,数据的高级操作,第6章 数据的高级操作,6.1 连接查询 6.2 子查询 6.3 案例应用举例,6.1 连接查询,6.1.1交叉连接查询 6.1.2等值与非等值连接查询 6.1.3自身连接查询 6.1.4外连接查询 6.1.5复合连接条件查询,6.1.1交叉连接查询,1.交叉连接的连接过程 (交叉连接又称非限制连接，将两个表不加约束的连接在一起，产生的结果集 的记录为两个表中记录的交叉乘积) 例如，有一个学生1表和一个单科成绩表，如表6. 1、表6. 2所示，两个表交叉连接后产生的结果如表6. 3所示，结果集是两个表记录的交叉乘积，列是两个表列的集合。,姓名,表6. 1,表6. 2,表6. 3,6.1.1交叉连接查询,6.1.1交叉连接查询,2.交叉连接的语法格式如下： SELECT 列名列表 FROM 表名1 CROSS JOIN 表名2 3.交叉查询学生1表和单科成绩表，代码如下： USE STUDENT GO SELECT * FROM 学生1 CROSS JOIN 单科成绩,6.1.2等值与非等值连接查询,例6.1 ：用等值连接的方法连接学生1表和单科成绩表代码如下： use student go select * from 学生inner join 单科成绩on 学生1.学号=单科成绩.学号 go,用来连接两个表的条件称为连接条件，连接条件一般格式为： . . 而比较运算符为“=”时，称为等值连接，其他运算符的连接为非等值连接,等值连接的语法格式： Select 列名列表 From 表名1inner join 表名2 On 表名1.列名=表名2.列名,6.1.2等值与非等值连接查询,例6.2 ：自然连接学生1表和单科成绩表。代码如下： use student go select 学生1.学号,姓名,成绩 from 学生join 单科成绩on 学生1.学号=单科成绩.,在等值连接中，把目标列中重复的属性列去掉则为自然连接,6.1.2等值与非等值连接查询,为了简便，在查询语句时可以使用表的别名，分配别名时，可以使用as关键字，也可以不使用。格式如下： 数据表名 as 表别名 数据表名 表别名 【6.4】使用表别名自然连接“学生1”表和“单科成绩”表。 Use student Go Select s.学号,姓名,成绩 from 学生 as s inner join 单科成绩 as c on s.学号=c.学号 go,6.1.3自身连接查询,一个表的两个副本之间的连接 称为自身连接查询 。 使用条件：当需要同时考查一个表中的多行（两行及以上）时 例6.3 ：查询选修了两门或两门以上课程的学生的学号和课程号。代码如下： USE student GO SELECT DISTINCT a.学号,a.课程号 FROM 课程注册 AS a JOIN 课程注册 AS b ON a.学号=b.学号 AND a.课程号!=b.课程号 GO,6.1.4外连接查询,外连接有分为左外连接、右外连接、全外连接三种。外连接除产生内连接生成的结果集外，还可以使一个表（左、右外连接）或两个表（全外连接）中的不满足连接条件的记录也出现在结果集中。,6.1.4外连接查询,1.左外连接 左外连接就是将左表作为主表，主表中所有记录分别与右表的每一条记 录进行连接，结果集中除了满足连接条件的记录外，还有主表中不满足 连接条件的记录，在右表的相应列上自动填充null值。格式如下： Select 列名列表 From 表名1 left outer join 表名2 on 表名1.列名=表名2.列名 例6.4：将学生1表左外连接单科成绩表。代码如下： USE STUDENT GO SELECT * FROM 学生1 LEFT OUTER JOIN 单科成绩 ON 学生1.学号=单科成绩.学号,6.1.4外连接查询,2.右外连接 右外连接就是将右表作为主表，主表中所有记录分别与左表的每一条记 录进行连接，结果集中除了满足连接条件的记录外，还有主表中不满足 连接条件的记录，在左表的相应列上自动填充null值。格式如下： Select 列名列表 From 表名1 right outer join 表名2 on 表名1.列名=表名2.列名 例6.5 ：将学生1表右外连接单科成绩表。代码如下： USE STUDENT GO SELECT * FROM 学生1 RIGHT OUTER JOIN 单科成绩 ON 学生1.学号=单科成绩.学号 GO,6.1.4外连接查询,3.全连接 全外连接就是将左表所有记录分别与右表的每一条记录进行连接，结果 集中除了满足连接条件的记录外，还有左、右表中不满足 连接条件的记录，在左、右表的相应列上自动填充null值。格式如下： Select 列名列表 From 表名1 full outer join 表名2 On 表名1.列名=表名2.列名 例6.6 ：用全连接的方法连接学生1表与单科成绩表。代码如下： USE STUDENT GO SELECT * FROM 学生1 FULL OUTER JOIN 单科成绩 ON 学生1.学号=单科成绩.学号 GO,6.1.5复合连接条件查询,On 连接表达式中有多个连接条件，称为复合连接条件。连接操作除了可以是两个表的连接，一个表与其自身的连接外，还可以是两个以上的表进行连接，称为多表连接,6.1.5复合连接条件查询,例6.7：查询成绩在75分以上的学生的学号、姓名、专业代码和专业学级，选修课的学期、课程号、成绩，任课教师的教师编号、姓名。代码如下： USE student GO SELECT B.课程号,C.教师编号,C.姓名,A.学号,A.姓名,B.专业代码,B.专业学级,B.学期,B.成绩 FROM 学生 AS A JOIN 课程注册 AS B ON A.学号=B.学号 AND B.成绩75 JOIN 教师 AS C ON B.教师编号=C.教师编号 GO,6.2 子查询,SELECT 姓名 FROM 学生WHERE 学号 IN （,SELECT 学号 FROM 课程注册 WHERE 课程号=0002 ）,将一个查询块嵌套在另一个查询块的where子句或having条件中的查询称之为嵌套查询。 以下的例题中：括号的查询块“select 学号 from 课程注册 where 课程号=0002”是嵌套在上层“SELECT 姓名 FROM 学生WHERE 学号 in”的where条件中的。括号中的查询块称为称为子查询或内层查询，而包含子查询的查询块称为父查询或外层查询。,6.2 子查询,6.2.1带有IN运算符的子查询 6.2.2带有比较运算符的子查询 6.2.3带有ANY 或ALL运算符的子查询 6.2.4带有EXISTS运算符的子查询,3.3 查询,一、带有IN谓词的子查询 例37 查询与“张震”在同一个系学习的学生。 SELECT * FROM Student WHERE Sdept IN( SELECT Sdept FROM Student WHERE Sname=张震),5.4.1带有IN运算符的子查询,例6.8 ：使用“系部”表和“班级”表，查询计算机系和经济管理系的班级信息。代码如下： USE student GO SELECT * FROM 班级 WHERE 系部代码IN (SELECT 系部代码 FROM 系部 WHERE (系部名称=计算机系 OR 系部名称=经济管理系 ) ) GO,例6.8(1) 查询选修了课程名称为“sql server 2005”的学生的学号和姓名。 Use student Go Select 学号,姓名 From 学生 Where 学号in( Select 学号 From 课程注册 Where 课程号in( Select 课程号 From 课程 Where 课程名称=sql server 2005 ) ),6.2.2带有比较运算符的子查询,例6.9 ：列出选修了“0001”号课程，其成绩高于该课程平均分的学生的信息。代码如下： USE student GO SELECT * FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 课程注册 WHERE 成绩 (SELECT AVG(成绩) FROM 课程注册 WHERE 课程号=0001 ) And 课程号=0001 ) GO,带有ANY 或ALL运算符的子查询,any 大于子查询结果中的某个值 all 大于子查询结果中的所有值 =any 大于等于子查询结果中的某个值 =all 大于等于子查询结果中的所有值 = any 小于等于子查询结果中的某个值 =all 小于等于子查询结果中的所有值 =any 等于子查询结果中的某个值,3.3 查询,ANY和ALL谓词与集函数的等价关系：,注：用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数。,例 查询其它班中年龄小于“060101001”班中的某个学生的学生姓名、年龄和班级代码 use student go SELECT 姓名,年龄,班级代码 FROM 学生 where 年龄060101001,6.2.3带有ANY 或ALL运算符的子查询,例 查询其它班中年龄小于“060101001”班中所有学生的学生姓名、年龄和班级代码 use student go SELECT 姓名,年龄,班级代码 FROM 学生 where 年龄060101001,带有EXISTS运算符的子查询,EXISTS谓词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则返回真值 若内层查询结果为空，则返回假值 由EXISTS引出的子查询，其目标列表达式通常 都用* ，因为带EXISTS的子查询只返回真值或假 值，给出列名无实际意义,6.2.4带有EXISTS运算符的子查询,例6.11：查询所有选修了“0001”课程的学生的学号与姓名。代码如下： USE student GO SELECT 学号,姓名 FROM 学生 WHERE EXISTS (SELECT * FROM 课程注册 WHERE 学号=学生.学号 AND 课程号=0001) GO,3.3 查询,例42 查询没有选修1号课程的学生姓名。 此查询可以理解为：输出那些学生的姓名，当在选课表中不存在他(她)选修1号课的记录。 SQL语句： SELECT Sname FROM Student WHERE NOT EXISTS ( SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = C1 )； 注：此例用连接运算无法实现！ 此查询为相关子查询。,3.3 查询,注: (1)一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换 (2)所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 例如 查询与“张震”在同一个系学习的学生。 可以理解为：输出那些学生，当他(她)所在系与“张震”所在系相同时。 用带EXISTS谓词的子查询替换： SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS ( SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 张震 ；,3.3 查询,集合查询 将两个SELECT-FROM-WHERE查询块用集合操作语句联结起来。 集合操作命令： 并操作(UNION) 交操作(INTERSECT) 差操作(MINUS) 语句形式 UNION 注：参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同,3.3 查询,例45 查询数学系的学生及年龄不大于18岁的学生。 方法一： SELECT * FROM Student WHERE Sdept = MA UNION SELECT * FROM Student WHERE Sage = 18； 方法二： SELECT DISTINCT * FROM Student WHERE Sdept = MA OR Sage = 18；,执行效率分析 如果分别对Sdept和sage建立索引，则使用方法一的效率高。,3.3 查询,例46查询数学系的学生与年龄小于18岁的学生的交集。 SELECT * FROM Student WHERE Sdept=MA AND Sage=18,3.3 查询,例47查询数学系的学生与年龄不大于18岁的学生的差集。 方法一 SELECT Sno FROM Student WHERE Sdept = CS AND Sage 18 ;,3.3 查询,二、DISTINCT的使用 DISTINCT用于区分相同的记录，将多条相同的记录作为一条处理。 例：查询成绩不及格的学生学号。 SELECT DISTINCT Sno FROM SC WHERE Grade = 80 ) ;,正确的查询语句： SELECT DISTINCT Sno, Sname, Cno, Grade FROM Student, SC WHERE Student.Sno = SC.Sno AND Grade = 80 ;,6.3 案例应用举例,6.3.1自动注册必修课 6.3.2重修未取得学分的必修课 6.3.3查询学生各门课程的成绩 6.3.4查询学生的总学分,