项目八 用多表联接查询管理教学计划

SQLServer2000基础教程【学习目标】1.两个表内联接查询的原理和语法。2.两个表自然联接查询和自联接查询的定义。3.两个表外联接查询的分类。4.两个表左联接查询、右联接查询、全联接查询以及交叉联接查询的原理和语法。项目八用多表联接查询管理教学计划【任务二】两个表的外联接查询【任务一】两个表的内联接查询【任务三】用子查询检查教学计划项目八用多表联接查询管理教学计划通过3个操作，介绍了内联接查询的语法，以及在什么情况下适于使用内联接查询。1.“操作一”讲解了内联接查询的语法，内联接查询的结果是主表和附表中能够满足关联条件的记录。内联接的联接词为innerjoin。2.“操作二”讲解了自然联接的概念，当查询结果是两个表中名称不同的列，可以用自然联接的方式书写查询语句。3.“操作三”讲解了什么是自联接查询，以及在什么情况下适于使用自联接查询。当查询需要对同一个表的记录进行比较时，适于采用自联接查询，同一个表在自联接中以不同角色出现。任务一两个表的内联接查询【基础知识】内联接查询的含义是：两个存在关联关系的表A和B，表A与表B内联接的查询结果为C，结果集C中只能包括表A与表B中满足关联条件（例如，相等）的记录。操作一“课程表”和“教师表”的内联接查询【基础知识】内联接查询语句语法。【任务目标】运用内联接查询，从“课程表”和“教师表”中查询各课程的授课教师信息，包括：“课程名称”、“课时”、“教师编号”和“授课教师姓名”。操作一“课程表”和“教师表”的内联接查询项目查询语句语法说明1select主表别名.列名,……附表别名.列名,……查询结果，列名或表达式。2from主表名主表别名查询来源的主表名及主表别名。3innerjoin附表名附表别名查询来源的附表名及附表别名。4on关联条件定义关联条件，即关系表达式或逻辑表达式。5其他where、groupby、having、orderby子句定义查询语句中的其他子句。操作一“课程表”和“教师表”的内联接查询【基础知识】自然联接是指：在内联接查询的select子句中出现的列名，虽然来自于不同的表，但列名不重复，“表别名.列名”的可以简写为“列名”。对于自然联接，SQLServer在编译、执行时不会因为不能确定某列的来源而提示错误信息。有兴趣的读者可以试验一下下列查询语句能否执行，并阅读提示信息。【任务目标】用自然联接改写“操作一”的“拓展任务”的查询语句，要求从“课程表”、“教师表”和“专业表”中查询“课程名称”、“课时”、“所属专业编号”、“专业名称”和“授课教师名称”。操作二“课程表”与“教师表”的自然联接【基础知识】在自联接查询中，虽然，数据来源是同一个表，但需要作为不同的角色来参与查询，必须为表定义不同角色的别名。自联接查询需要在where子句中设置条件，用来屏蔽相同记录和重复记录。如何设置屏蔽条件，将在以下示例中介绍。【任务目标】显示“课程表”中授课教师相同的“课程编号”、“课程名称”、“教师编号”，与其授课教师相同的其他“课程编号”、“课程名称”和“教师编号”。操作三对“课程表”的自联接通过4个操作，介绍外联接查询的种类、语法，以及在什么情况下使用哪种外联接查询。外联接查询可以分为4类：左联接查询、右联接查询、全联接查询和交叉联接查询。1.“操作一”讲解左联接查询的含义和联接词leftjoin的语法。左联接查询的结果可以来自于主表的全部记录，以及附表中与主表能够满足关联条件的记录，不能满足关联条件的列值为空值。任务二两个表的外联接查询2.“操作二”讲解右联接查询的含义和联接词rightjoin的语法。右联接查询的结果可以来自于附表的全部记录，以及主表中与附表能够满足关联条件的记录，不能满足关联条件的列值为空值。3.“操作三”讲解全联接查询的含义和联接词fulljoin的语法。全联接的结果可以是主表的全部记录和附表的全部记录，不能满足关联条件的列值为空值。4.“操作四”讲解交叉联接查询的含义和联接词crossjoin的语法。交叉联接的结果是主表中的每一条记录对应于附表中的全部记录。在交叉联接的结果中，记录数是主表记录数与附表记录数的乘积。任务二两个表的外联接查询【基础知识】左联接查询的含义是：两个存在关联关系的表A和B，表A与表B左联接的查询结果为C。C的列可以来自于A和B的列，C的记录中允许包括A的全部记录以及B中与A满足关联条件的记录，C中A与B不满足关联条件的列值为空值。操作一“教师表”与“课程表”的左联接查询【基础知识】左联接查询语句语法【任务目标】从“教师表”和“课程表”中查询教师的授课课程的“课程名称”，对于无授课安排的，显示“无授课安排”。操作一“教师表”与“课程表”的左联接查询项目查询语句语法说明1select主表别名.列名,……附表别名.列名,……查询结果，列名或表达式。2from主表名主表别名查询来源的主表名及主表别名。3leftjoin附表名附表别名查询来源的附表名及附表别名。4on关联条件定义关联条件，即关系表达式或逻辑表达式。5其他where、groupby、having、orderby子句定义查询语句中的其他子句。操作一“教师表”与“课程表”的左联接查询【基础知识】右联接的含义是：两个存在关联关系的表A和B，表A与表B右联接的查询结果为C。C的列可以来自于A和B的列，C的记录中可以包括B的全部记录以及表A中与B满足关联条件的记录，C中B与A不能够满足关联条件部分为空值。操作二“教师表”与“课程表”的右联接查询【基础知识】右联接查询语句语法【任务目标】对“教师表”和“课程表”作右联接查询，要求显示各课程的授课教师“姓名”，对于无授课安排的课程，显示“无授课教师”。操作二“教师表”与“课程表”的右联接查询项目查询语句语法说明1select主表别名.列名,……附表别名.列名,……查询结果，列名或表达式。2from主表名主表别名查询来源的主表名及主表别名。3rightjoin附表名附表别名查询来源的附表名及附表别名。4on关联条件定义关联条件，即关系表达式或逻辑表达式。5其他where、groupby、having、orderby子句定义查询语句中的其他子句。操作二“教师表”与“课程表”的右联接查询【基础知识】全联接的含义是：两个存在关联关系的表A和B，表A与表B全联接的查询结果为C。C的列可以来自于A和B的列，C的记录中可以包括A和B的全部记录，C中B与A以及A与B不满足关联条件的部分为空值。操作三“教师表”与“课程表”的全联接查询【基础知识】全联接查询语句语法【任务目标】用全联接查询改写“操作一”和“操作二”的查询语句，要求显示没有授课安排的教师，并显示没安排授课教师的课程。操作三“教师表”与“课程表”的全联接查询项目查询语句语法说明1select主表别名.列名,……附表别名.列名,……查询结果，列名或表达式。2from主表名主表别名查询来源的主表名及主表别名。3fulljoin附表名附表别名查询来源的附表名及附表别名。4on关联条件定义关联条件，即关系表达式或逻辑表达式。5其他where、groupby、having、orderby子句定义查询语句中的其他子句。操作三“教师表”与“课程表”的全联接查询【基础知识】交叉联接的含义是：两个存在关联关系的表A和B，表A与表B交叉联接的查询结果为C。C的列可以来自于A和B的列，C中可以包括A和B的全部记录。C的记录数是A的记录数与B的记录数的乘积，即A的每一条记录对应B的全部记录。交叉联接就是代数学中的笛卡尔乘积。操作四用交叉联接生成“学生考勤记录”【基础知识】全联接查询语句语法。【任务目标】用“学生表”和“考勤表”交叉联接预先生学生工考勤记录。项目查询语句语法说明1select主表别名.列名,……附表别名.列名,……查询结果，列名或表达式。2from主表名主表别名查询来源的主表名及主表别名。3crossjoin附表名附表别名查询来源的附表名及附表别名。4其他where、groupby、having、orderby子句定义查询语句中的其他子句。操作四用交叉联接生成“学生考勤记录”通过两个操作介绍另外两个联接子查询的联接谓词in和exists的含义和语法。1.“操作一”讲解in的用法，联接谓词in指定的列值是子查询结果集中的某个结果。2.“操作二”讲解exists的用法，联接谓词exists表示子查询的结果集不为空记录时才进行查询或更新操作。任务三用子查询检查教学计划【基础知识】联接谓词in/notin的含义是：确定指定列的值或表达式的值是否与子查询或列表中的值相匹配/不匹配。语法格式如下：列名/表达式in/notin（子查询）【任务目标】使用notin从“教师表”中查询没有授课安排的教师信息，包括“教师编号”、“教师姓名”、“职务”和“年龄”。操作一使用in的子查询【基础知识】联接谓词exists/notexists的含义是：根据指定子查询的结果是存在还是不存在，进一步决定是否执行查询操作。语法格式如下：exists/notexists（子查询）【任务目标】使用exists联接谓词，查询的结果与“任务1”相同，但是查询的触发条件不同，本任务，只有当“课程表”中存在为安排授课教师的课程，并且“教师表”中也存在未安排手机计划的教师时才执行查询操作。操作二使用exist的子查询