实验五 连接查询、嵌套查询与联合查询 实验指导.doc

实验五 连接查询、嵌套查询与联合查询 实验指导一、连接查询前面的查询都是在单个表中进行的查询。在数据库的实际编程应用中，往往需要查询许多数据，有可能这些数据出现在两个或两个以上的表中，而我们希望这些数据出现在一个结果集中，这就要用到连接查询。在SQL Server中，可以使用两种语法形式：一种是ANSI连接语法形式，连接写在FROM子句中，使用JOINON关键字。另一种是早期SQL SERVER连接语法形式，连接写在WHERE子句中。在SQL SERER中可以设定兼容级别为80及以下（在对象资源管理器中展开相应的数据库，右击，选择“属性”，在弹出的“数据库属性”对话框中选择“选项”，有兼容级别设置）才能使用这种形式。在SQLServer2008中推荐使用ANSI形式的连接。两种连接的语法格式如下。SQL Server连接的语法格式：SELECT FROM 表1,表2WHERE 表1.列1 表2.列2其中比较运算符可以是：=、=、=、等。说明：l 连接的列（属性）名可不相同，但数据类型必须兼容。l 当是“=”时，称等值连接，否则为非等值连接。ANSI连接的语法形式如下：SELECT 表名.列名,nFROM 表名1 连接类型 JOIN表名2 ON 连接条件 ,nWHERE 查询条件连接查询包括以下几种类型。1. 内连接从两个或两个以上的表的组合中，挑选出符合连接条件的数据。如果数据无法满足连接条件，则将其丢弃。通常称这种方法为内部连接INNER JOIN。在内部连接中，参与连接的表的地位是平等的。它有两种形式：等值连接和自然连接。等值连接会产生冗余列，因为它将连接条件中的共享列显示两次。而自然连接则清除了等值连接产生的冗余列，因此我们所说的内连接在没有特殊说明时都指的是自然连接。例1 查询所有学生的选课信息（包含学号、姓名、课程编号）。用SQL Server形式进行连接：SELECT student.sno,sname,cnoFROM student,scWHERE student.sno=sc.sno用ANSI形式进行连接：SELECT student.sno,sname,cnoFROM student INNER JOIN sc ON student.sno=sc.sno2. 外连接在使用内连接查询时，只包含两表中都满足连接条件的行，而外连接会把某些不满足条件的行显示出来。在外部连接中，参与连接的表有主从之分。以主表的每行数据去匹配从表的数据列，符合连接条件的数据将直接返回到结果集中。对那些不符合连接条件的列将被填上NULL值后再返回到结果集中。对BIT类型的列，由于BIT数据类型不允许NULL值，因此将会被填上0值再返回到结果中。外部连接分为左外连接LEFT OUTER JOIN、右外连接RIGHT OUTER JOIN和全连接三种。以主表所在的方向区分外部连接。（1）左外连接。左外连接指主表在左边，即在连接两表时，保留左表中的不匹配行，右表的相应行用NULL或0值。例2 查询每个学生及其选修课程的情况包括没有选修课程的学生。SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)（2）右外连接。右外连接指主表在右边，即在连接两表时，保留右表中的不匹配行，左表的相应行用NULL或0值。（3）全连接。在连接两表时，保留两表中的不匹配行。全连接的语法只有ANSI形式一种，使用FULL OUTER JOIN关键字。全连接生成的结果记录集将会很多。一般不大使用，就不举例了。3. 无限制连接也称为交叉连接，它可能会产生一个庞大的结果集，因为其中包含了所连接的表中所有行的全部组合。如在某连接中，一个表的记录数是5，另一个表的记录数是6，那么结果集就有5*6（30）条记录。交叉连接在ANSI形式中使用CROSS JOIN 关键字。4. 自身连接连接除了可以在多个不同的表中进行外，也可以在同一个表中进行连接，即自身连接。自身连接可以看作一张表的两个副本之间进行的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。例3 查询每一门课的间接先修课（即先修课的先修课）。SELECT FIRST.Cno,SECOND.CpnoFROM Course FIRST INNER JOIN Course SECOND ON FIRST.Cpno=SECOND.Cno二、嵌套查询子查询指在一个外层查询中包含另一个内层查询，即在一个SELECT语句中的WHERE子句中，包含有另一个SELECT语句，或者将一个SELECT语句作为另一个SELECT语句的新增列。外层的查询成为主查询，WHERE子句中包含的SELECT语句被称为子查询。一般将子查询的查询结果作为主查询的查询条件。子查询也称为嵌套查询，可完成复杂的查询操作。通常情况下，嵌套查询都可以写成连接的形式，但是有时候写成连接形式比较复杂，不容易理解，因此将其写成嵌套的形式，以将复杂的查询分解成简单的、易理解的子查询。子查询与连接相比，由于子查询的执行需要增加一些附加的操作如排序等，而连接不需要增加附加操作，故一般情况下，连接优于子查询。使用子查询时要注意以下几点。（1）子查询需要用（）括起来。（2）子查询内可以再嵌套子查询。（3）子查询内的SELECT语句不能使用image、text、或ntext数据类型。（4）子查询返回的结果值的数据类型必须匹配新增列或WHERE子句中的数据类型。1. 子查询作为新增列 例4 用子查询显示每个学生的平均分。SELECT a.sno,avg_Grade=(SELECT AVG(Grade) FROM sc b WHERE a.sno=b.sno)FROM student a如果不用子查询，可以用分组查询。SELECT sno,AVG(Grade) as avg_GradeFROM scGROUP BY sno2. 使用IN关键字 语法格式为：WHERE 表达式 NOT IN（子查询）说明：l IN表示属于，即若表达式的值属于子查询返回的结果集中的值，则满足查询条件。NOT IN表示不属于。例5 查询与“刘晨”在同一个系学习的学生。SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=刘晨)3. 使用比较运算符语法格式：WHERE 表达式 比较运算符 ANY|ALL (子查询)ANY关键字表示任何一个（其中之一），只要与子查询中一个值相符合即可；ALL关键字表示所有（全部），要求与子查询中的所有值相符合。比较运算符包括：、=、=、=、。例6 找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno,CnoFROM SC xWHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno)例7 查询其他系中比计算机系某一个学生年龄小的学生姓名和年龄。SELECT Sname,SageFROM StudentWHERE Sage ANY(SELECT Sage FROM Student WHERE Sdept= CS) AND Sdept CS 4. 使用BETWEEN关键字语法格式：WHERE 表达式1 NOT BETWEEN (子查询) AND 表达式2或WHERE 表达式1 NOT BETWEEN 表达式2 AND (子查询)说明：l 使用BETWEEN关键字，则查询条件是表达式1的值必须介于子查询结果与表达式2值之间；而使用NOT BETWEEN关键字则正好相反。例8 找出每个学生的超过他选修课程平均成绩且小于90分的课程号。SELECT Sno,CnoFROM SC xWHERE Grade BETWEEN (SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno) AND 905. 使用EXISTS关键字 语法格式：WHERE NOT EXISTS (子查询)说明：l EXISTS关键字表示存在量词，带有EXISTS关键字的子查询不返回任何数据，只返回逻辑真值和逻辑假值。当子查询的结果不为空集时，返回逻辑真值，否则返回逻辑假值。NOT EXISTS则与EXISTS查询结果相反。例9 查询所有选修了1号课程的学生姓名。SELECT SnameFROM StudentWHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1)例10 查询至少选修了学生200215122选修的全部课程的学生号码。SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno=200215122 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)6. 子查询与数据更新 INSERT、UPDATE、DELETE这三种语句还能与子查询结合，实现更加灵活的数据更新操作。（1）子查询与INSERT语句。子查询与INSERT语句相结合，可以完成一批数据的插入。语法格式为：INSERT INTO （2）子查询与UPDATE语句。子查询与UPDATE语句相结合，一般是嵌在WHERE子句中，查询结果作为修改数据的条件依据之一，可以修改一批数据。语法格式为：UPDATE SET =,= ,NWHERE（3）子查询与DELETE语句。子查询与DELETE语句相结合，一般也是嵌在WHERE子句中，查询结果作为删除数据的条件依据之一，可以删除一批数据。语法格式为：DELETE FROM WHERE子查询与数据更新结合的操作在前面的实验四中已经讲过，这里就不赘述了。三、联合查询联合查询是指合并两个或多个查询语句的结果集。与连接查询先比，集合查询是增加记录的行数，而连接查