数据查询与更新课件.ppt

SQL SERVER 2005 SQL SERVER 2005 案例教程案例教程关于数据查询与更新第1页，此课件共79页哦5.1 简单数据查询lSELECT语句的组成语句的组成子句解释SELECT后跟一组列的列表，或者一个星号表示想要返回所有的列FROM后跟一个表或者视图的名字，或者带有连接表达式的多个表WHERE后跟过滤规则ORDER BY后跟一组列的列表进行排序第2页，此课件共79页哦5.1.1 SELECT和FROM子句1、指定返回列名、指定返回列名【例例 5.1】返回返回XS表中所有的记录的表中所有的记录的XH（学号）（学号）、XM(姓名姓名)、CSRQ(出生日期出生日期)列。列。SELECT XH,XM,CSRQ FROM XS第3页，此课件共79页哦5.1.1 SELECT和FROM子句 2、返回所有列、返回所有列SELECT*FROM XS第4页，此课件共79页哦5.1.1 SELECT和FROM子句3、在、在SELECT子句后加入计算列子句后加入计算列（1）SELECT XH,XM+(+XB+),CSRQ FROM XS（2）SELECT XH,XM,GETDATE(),10,CSRQ FROM XS第5页，此课件共79页哦5.1.2 列别名和表别名1、原始列名原始列名 AS 别名。别名。SELECT XH AS 学号学号,XM+(+XB+)AS 姓名性别姓名性别,CSRQ AS 出生日期出生日期FROM XS第6页，此课件共79页哦5.1.2 列别名和表别名2、原始列名原始列名 别名别名 SELECT XH 学号学号,XM 姓名姓名,CSRQ 出生出生日期日期 FROM XS3、别名别名=原始列名原始列名SELECT学号学号=XH,姓名姓名=XM,出生日期出生日期=CSRQ FROM XS第7页，此课件共79页哦5.1.2 列别名和表别名l表别名：表别名：1、“始表名始表名 AS 别名别名”SELECT XH,XM,CSRQSELECT XH,XM,CSRQFROM XS aFROM XS a 2、“原始表名原始表名 别名别名”SELECT XH,XM,CSRQ SELECT XH,XM,CSRQ FROM XS AS aFROM XS AS a第8页，此课件共79页哦5.1.3 WHERE子句例：从例：从XS表中查找姓名为表中查找姓名为“曹敏曹敏”的同学的学的同学的学号，姓名，性别。号，姓名，性别。SELECT XH,XM,XB SELECT XH,XM,XB FROM XS FROM XS WHERE XM=WHERE XM=曹敏曹敏 第9页，此课件共79页哦5.1.3 WHERE子句lNULL值参与的比较：值参与的比较：当字段未设置值或变量未设值时，其值为当字段未设置值或变量未设值时，其值为NULL,即不包含任何值即不包含任何值（1）、当）、当ANSI_NULLS为为OFF时时(默认默认)，任何，任何与与NULL值的运算结果都为值的运算结果都为NULL,任何与任何与NULL的比较结果都为的比较结果都为UNKNOW(不确定不确定)。（2）当）当ANSI_NULLS为为ON时，除了时，除了=和和!=或或者者比较运算符外，其他比较运算符和比较运算符外，其他比较运算符和NULL比较也会得到比较也会得到UNKNOWN值。值。第10页，此课件共79页哦5.1.3 WHERE子句l例例1：SELECT*SELECT*FROM XS FROM XS WHERE SFZ=NULL OR SFZ!=NULLWHERE SFZ=NULL OR SFZ!=NULL结果：空结果：空例例2 2：SELECT*SELECT*FROM XS FROM XS WHERE SFZ is NULLWHERE SFZ is NULL第11页，此课件共79页哦5.1.3 WHERE子句l例例3：SET ANSI_NULLS OFFSELECT*FROM XS WHERE SFZ=NULL第12页，此课件共79页哦5.1.4 ORDER BY子句1、单个字段排序、单个字段排序SELECT*FROM XS ORDER BY XH -按学号进行排序按学号进行排序NULL排最前面第13页，此课件共79页哦5.1.4 ORDER BY子句2、多字段排序、多字段排序lORDER BY后面可以指定多个排序字段，字后面可以指定多个排序字段，字段之间以段之间以“，”分隔。分隔。l在指定了多列情况下，首先按第一列排序，在指定了多列情况下，首先按第一列排序，在第一列相同的情况下按第二列排序，以此在第一列相同的情况下按第二列排序，以此类推。类推。第14页，此课件共79页哦5.1.4 ORDER BY子句例：例：SELECT XH,XM,XB,DATEDIFF(DAY,CSRQ,GETDATE()/365 AS NL FROM XS ORDER BY XM,NL注意：汉字按拼音首字母排序注意：汉字按拼音首字母排序此处有空格第15页，此课件共79页哦5.1.4 ORDER BY子句降序排序：降序排序：如果没有特别说明，值是以升序序列进行排序的。你也可以如果没有特别说明，值是以升序序列进行排序的。你也可以选择使用选择使用ASC关键字指定升序排序。关键字指定升序排序。如果你希望以降序排列，则在如果你希望以降序排列，则在ORDER BY后面使用后面使用DESC关键字。关键字。例：例：SELECT*FROM XS ORDER BY CSRQ DESC 第16页，此课件共79页哦5.1.5 TOP子句lTOP子句用来返回排在前面特定数量的记录，而不管子句用来返回排在前面特定数量的记录，而不管符合条件的行是多少。有两种方式用来指定返回的行：符合条件的行是多少。有两种方式用来指定返回的行：指定排在前面的绝对记录数，如：指定排在前面的绝对记录数，如：SELECT TOP(5)*SELECT TOP(5)*FROM XS FROM XS ORDER BY CSRQ DESCORDER BY CSRQ DESC指定排在前面的记录数占总行数的百分比指定排在前面的记录数占总行数的百分比SELECT TOP(0.1)PERCENT*SELECT TOP(0.1)PERCENT*FROM XS FROM XS ORDER BY CSRQ DESCORDER BY CSRQ DESC第17页，此课件共79页哦5.1.5 TOP子句lWITH TIES子句子句WITH TIES子句用来解决在使用子句用来解决在使用TOP子句按子句按百分比返回后，与最后一名数据相同的记录百分比返回后，与最后一名数据相同的记录不能出现在结果集中的问题。不能出现在结果集中的问题。例：例：SELECT TOP(5)WITH TIES *FROM XS ORDER BY CSRQ DESC第18页，此课件共79页哦5.1.6 DISTINCT5.1.6 DISTINCT关键字关键字DISTINCT用来从结果集中去除重复的记录。用来从结果集中去除重复的记录。例：统计学生共来自于哪些民族。例：统计学生共来自于哪些民族。语句语句1：SELCET MZ FROM XS语句语句2：SELCET DISTINCT MZ FROM XS第19页，此课件共79页哦5.2 5.2 分组查询分组查询l5.2.1 聚合函数聚合函数特点：作用在多条记录上。换句话说，函数的返回值建立在特点：作用在多条记录上。换句话说，函数的返回值建立在对多条记录进行统计的基础上。对多条记录进行统计的基础上。常用聚合函数：常用聚合函数：SUM,COUNT,MAX,MIN,AVG1、计算总分、计算总分SELECT SUM(FS)SELECT SUM(FS)FROM CJ FROM CJ WHERE XQDM=21 AND XSDM=20WHERE XQDM=21 AND XSDM=20第20页，此课件共79页哦5.2.1 聚合函数2、计算最高分、最低分和平均分：、计算最高分、最低分和平均分：SELECT MAX(FS)最高分最高分,MIN(FS)最低分最低分,AVG(FS)平均分平均分 FROM CJ WHERE XQDM=5 AND XSDM=163483、统计班级人数、统计班级人数SELECT COUNT(*)FROM XS WHERE BJDM=30第21页，此课件共79页哦5.2.2 GROUP BY 子句lGROUP BY 子句子句将查询结果按某一列或多列值分组输出，值相等的为一组。l对查询结果分组的目的是使集函数作用于每一个组，即每一个组都有一个函数值。第22页，此课件共79页哦5.2.2 GROUP BY 子句例：查询每个同学的最高分、最低分和平均分。例：查询每个同学的最高分、最低分和平均分。SELECT XSDM,MAX(FS)最高分最高分,MIN(FS)最最低分低分,AVG(FS)平均分平均分 FROM CJ GROUP BY XSDM第23页，此课件共79页哦5.2.2 GROUP BY 子句例：每个同学在不同学期的最高分、最低分、例：每个同学在不同学期的最高分、最低分、平均分：平均分：SELECT XSDM,XQDM,MAX(FS)最高分最高分,MIN(FS)最低分最低分,AVG(FS)平均分平均分FROM CJ GROUP BY XSDM,XQDM第24页，此课件共79页哦5.2.2 GROUP BY 子句注意：注意：l出现在出现在GROUP BY 子句后面的字段列表应该也出现在子句后面的字段列表应该也出现在SELECT子句中，否则获得的数据可能没有意义。如：子句中，否则获得的数据可能没有意义。如：SELECT MAX(FS)SELECT MAX(FS)最高分最高分,MIN(FS),MIN(FS)最低分最低分,AVG(FS),AVG(FS)平均分平均分 FROM CJ FROM CJ GROUP BY XSDMGROUP BY XSDM上述语句执行后的结果集中你将无法确定成绩属于哪位上述语句执行后的结果集中你将无法确定成绩属于哪位学生学生第25页，此课件共79页哦5.2.2 GROUP BY 子句l出现在出现在SELECT子句中的字段列表除了聚合子句中的字段列表除了聚合函数外，都必须出现在函数外，都必须出现在GROUP BY 子句后面。子句后面。例如，下面的语句将会报错：例如，下面的语句将会报错：SELECT XSDM,SELECT XSDM,XQDMXQDM,MAX(FS),MAX(FS)最高分最高分,MIN(FS),MIN(FS)最低最低分分,AVG(FS),AVG(FS)平均分平均分FROM CJ FROM CJ GROUP BY XSDMGROUP BY XSDM第26页，此课件共79页哦5.2.3 HAVING子句lHAVING子句用来对分组后的数据进行筛选子句用来对分组后的数据进行筛选例：例：查询平均成绩大于查询平均成绩大于65分的学生的最高分、分的学生的最高分、最低分和平均成绩。最低分和平均成绩。SELECT XSDM,MAX(FS)最高分最高分,MIN(FS)最最低分低分,AVG(FS)平均分平均分FROM CJ GROUP BY XSDM HAVING AVG(FS)65第27页，此课件共79页哦5.2.3 HAVING子句lHAVING子句和子句和WHERE子句很相似，均用于子句很相似，均用于设置数据筛选条件。设置数据筛选条件。lWHERE子句对分组前的数据进行筛选，条件子句对分组前的数据进行筛选，条件中不能包含聚合函数；中不能包含聚合函数；lHAVING子句对分组过后的数据进行筛选，子句对分组过后的数据进行筛选，条件中经常包含聚合函数。条件中经常包含聚合函数。l HAVING子句必须和子句必须和GROUP BY子句联合使子句联合使用用第28页，此课件共79页哦5.3 联接查询l当返回的数据集中需要包含位于多个表中的当返回的数据集中需要包含位于多个表中的数据时，需要用到联接查询。及联接查询用数据时，需要用到联接查询。及联接查询用于在一个查询语句中同时返回多个表中的数于在一个查询语句中同时返回多个表中的数据据第29页，此课件共79页哦5.3.1 5.3.1 交叉联接交叉联接(CROSS JOIN)(CROSS JOIN)l交叉联接返回两个表的笛卡尔积。即：返回的数据集为一个表中的每一笛卡尔积。即：返回的数据集为一个表中的每一行与另一个表中每一行的组合。行与另一个表中每一行的组合。例：简单交叉联接例：简单交叉联接SELECT*FROM KC CROSS JOIN XQ返回的记录数量为两个表记录数的乘积返回的记录数量为两个表记录数的乘积上面语句可以省略上面语句可以省略CROSS JOIN关键字关键字,用用“，”将两个表连接起来，将两个表连接起来，即：即：SELECT*FROM KC,XQ该返回结果和交叉联接是基本是一致的，只是没有重复的字段。该返回结果和交叉联接是基本是一致的，只是没有重复的字段。第30页，此课件共79页哦5.3.2 内联接(INNER JOIN)l内联接是联接两个表最常用的方法，使用比较运算符进行内联接是联接两个表最常用的方法，使用比较运算符进行表间某表间某(些些)列数据的比较操作，并列出这些表中与联接条件列数据的比较操作，并列出这些表中与联接条件相匹配的数据行。相匹配的数据行。l内联接可以理解为从两个表的笛卡尔积中筛选出完全内联接可以理解为从两个表的笛卡尔积中筛选出完全符合联接条件的记录符合联接条件的记录例：例：显示成绩表中的所有数据，要求包含学生学号和姓名。显示成绩表中的所有数据，要求包含学生学号和姓名。SELECT XS.XH 学号学号,XS.XM 姓名姓名,KCDM 课程代码课程代码,FS 分分数数,CJ.XSDM,XS.XSDM FROM XS INNER JOIN CJ ON CJ.XSDM=XS.XSDM 第31页，此课件共79页哦5.3.3 外联接l外联接分为三种：外联接分为三种：左外联接左外联接(LEFT OUTER JOIN(LEFT OUTER JOIN或或LEFT JOIN)LEFT JOIN)右外联接右外联接(RIGHT OUTER JOIN(RIGHT OUTER JOIN或或RIGHT JOIN)RIGHT JOIN)全外联接全外联接(FULL OUTER JOIN(FULL OUTER JOIN或或FULL JOIN)FULL JOIN)l与内联接不同，外联接不仅列出与联接条件与内联接不同，外联接不仅列出与联接条件相匹配的行，还会显示左表相匹配的行，还会显示左表(左外联接时左外联接时)、右、右表表(右外联接时右外联接时)或两个表或两个表(全外联接时全外联接时)中所有中所有符合搜索条件的记录。符合搜索条件的记录。第32页，此课件共79页哦5.3.3 外联接l1、左外联接、左外联接左边表中的行都会出现在结果数据集中，如果左边表中的某一行在右边左边表中的行都会出现在结果数据集中，如果左边表中的某一行在右边表中没有匹配的行（不满足联接条件），则以空值取代右边表中的值。表中没有匹配的行（不满足联接条件），则以空值取代右边表中的值。l例：查看每个专业有哪些班级。例：查看每个专业有哪些班级。使用外联接：使用外联接：SELECT a.ZYDM,a.ZYMC,b.BJDM,b.ZYDM AS Expr1,b.BJMCFROM ZY AS a LEFT JOIN BJ AS b ON a.ZYDM=b.ZYDM使用内联接：使用内联接：SELECT a.ZYDM,a.ZYMC,b.BJDM,b.ZYDM AS Expr1,b.BJMCFROM ZY AS a JOIN BJ AS b ON a.ZYDM=b.ZYDM第33页，此课件共79页哦5.3.3 外联接l2、右外联接、右外联接右外联接和左外联接类似，不同的是把右边的表作为外部右外联接和左外联接类似，不同的是把右边的表作为外部表表(所有右边表中的行包含在结果数据集中所有右边表中的行包含在结果数据集中)。例：查看。例：查看每个专业有哪些班级。每个专业有哪些班级。lSELECT a.ZYDM,a.ZYMC,b.BJDM,b.ZYDM AS Expr1,b.BJMClFROM ZY AS a RIGHT JOINl BJ AS b ON a.ZYDM=b.ZYDM第34页，此课件共79页哦5.3.3 外联接l3、全外联接、全外联接l在全外联接中，位于在全外联接中，位于FULL JOIN两边的表中两边的表中的行都会包含在结果数据集中。的行都会包含在结果数据集中。第35页，此课件共79页哦5.3.4 5.3.4 自联接自联接列名列名说明说明YGDMYGDM员工代码员工代码YGXM员工姓名员工姓名SJLD该员工上级领导代码该员工上级领导代码员工表自联接所联接的两个表在物理上为同一张表，但在逻辑上可看作两个表，用不同的表别名加以区分第36页，此课件共79页哦5.3.4 5.3.4 自联接自联接例：查询每位员工上级领导例：查询每位员工上级领导SELECT A.*,B.YGXM FROM YG A LEFT JOIN YG B ON A.SJLD=B.YGDM第37页，此课件共79页哦5.3.5 多表联接l例例：显示学生信息，包括学生所在班级名称、：显示学生信息，包括学生所在班级名称、专业名称和和系部名称专业名称和和系部名称SELECT XB.XBMC,ZY.ZYMC,BJ.BJMC,XS.*SELECT XB.XBMC,ZY.ZYMC,BJ.BJMC,XS.*FROM XS INNER JOINFROM XS INNER JOIN BJ ON BJ.BJDM=BJ ON BJ.BJDM=XS.BJDM INNER JOINXS.BJDM INNER JOIN ZY ON BJ.ZYDM=ZY ON BJ.ZYDM=ZY.ZYDM INNER JOINZY.ZYDM INNER JOIN XB ON ZY.XBDM=XB.XBDM XB ON ZY.XBDM=XB.XBDM第38页，此课件共79页哦5.4 子查询 有两种子查询类型：有两种子查询类型：标准子查询和相关子查询。标准子查询和相关子查询。l标准子查询执行一次，结果反馈给外层查询，标准子查询执行一次，结果反馈给外层查询，它的返回值跟其外层查询没有联系（不相关）。它的返回值跟其外层查询没有联系（不相关）。l相关子查询在子查询中引用了外层查询中的相关子查询在子查询中引用了外层查询中的表，因此它的执行次数取决于其外层查询的表，因此它的执行次数取决于其外层查询的查询次数。查询次数。第39页，此课件共79页哦5.4.1 标准子查询与相关子查询l1、标准子查询、标准子查询 例例：查询与学号为查询与学号为“0630160219”的同学同班的学生信息。的同学同班的学生信息。步骤步骤1 1：我们先求这个同学所在班级的班级代码：我们先求这个同学所在班级的班级代码：SELECT BJDMSELECT BJDMFROM XSFROM XSWHERE XH=0630160219WHERE XH=0630160219步骤步骤2 2：面语句查询结果为：面语句查询结果为2323。从。从XSXS表中查询表中查询BJDMBJDM为为2323的学生信的学生信息：息：SELECT *SELECT *FROM XSFROM XSWHERE BJDM=23WHERE BJDM=23第40页，此课件共79页哦5.4.1 标准子查询与相关子查询步骤步骤3 3：将前两个语句整合：将前两个语句整合：SELECT *SELECT *FROM XSFROM XSWHERE BJDM=WHERE BJDM=(SELECT BJDM (SELECT BJDM FROM XS FROM XS WHERE XH=0630160219)WHERE XH=0630160219)注意事项：注意事项：1 1、必须用、必须用“（”和和“）”将子查询扩起来。将子查询扩起来。2 2、处于比较运算符其中一边的子查询，其返回值应该只能有一行、一列，、处于比较运算符其中一边的子查询，其返回值应该只能有一行、一列，其返回值的数据类型应该和运算符另一边的值（列）是一致或可以被系其返回值的数据类型应该和运算符另一边的值（列）是一致或可以被系统自动转换的。统自动转换的。3 3、子查询中不能检索包含数据类型为、子查询中不能检索包含数据类型为texttext和和imageimage的列。的列。第41页，此课件共79页哦5.4.1 标准子查询与相关子查询l2、相关子查询、相关子查询例例 ：查询存在同名的学生信息查询存在同名的学生信息1)1)：语句基本形式：语句基本形式：SELECT*FROM XS WHERE 该同学存在同名该同学存在同名2)判断指定学生是否重名：判断指定学生是否重名：（SELECT COUNT(*)FROM XS WHERE XM=XXXSELECT COUNT(*)FROM XS WHERE XM=XXX）113 3）将）将2 2）代入）代入1 1），并给），并给xsxs表别名表别名SELECT *SELECT *FROM XS aFROM XS aWHERE (SELECT COUNT(*)WHERE (SELECT COUNT(*)FROM XS b FROM XS b WHERE (b.XM=XXX)1)WHERE (b.XM=XXX)1)第42页，此课件共79页哦5.4.1 标准子查询与相关子查询4)将指定学生将指定学生xxx替换为外层被查询的学生替换为外层被查询的学生SELECT *SELECT *FROM XS aFROM XS aWHERE (SELECT COUNT(*)WHERE (SELECT COUNT(*)FROM XS b FROM XS b WHERE (b.XM=a.XM)1)WHERE (b.XM=a.XM)1)第43页，此课件共79页哦5.4.2 5.4.2 使用使用ALLALL、ANYANY、SOMESOME关键字关键字lALL 在条件语句中，表示必须满足每一个条在条件语句中，表示必须满足每一个条件项件项例：例：查询查询XS表中年龄最大的学生。表中年龄最大的学生。SELECT *FROM XSWHERE CSRQ=ALL (SELECT CSRQ FROM XS)第44页，此课件共79页哦5.4.2 5.4.2 使用使用ALLALL、ANYANY、SOMESOME关键字关键字lANY（SOME）在条件语句中，表示满足其在条件语句中，表示满足其中任何一个条件项中任何一个条件项例：取要出生日期不是最小的学生信息例：取要出生日期不是最小的学生信息SELECT *FROM XSWHERE CSRQ ANY (SELECT CSRQ FROM XS)第45页，此课件共79页哦5.4.3 EXISTS 和IN子查询lEXISTS 子查询被称为子查询被称为“存在子查询存在子查询”,它用来判断子查询相关表它用来判断子查询相关表中是否存在满足子查询条件的行，而对于这些行的具体数据，子中是否存在满足子查询条件的行，而对于这些行的具体数据，子查询并不关心也不会被返回。查询并不关心也不会被返回。l当内层查询结果为当内层查询结果为“存在存在”满足子查询条件的行，则外层的满足子查询条件的行，则外层的WHERR子句返回真值，否则返回假值。子句返回真值，否则返回假值。l由于由于EXISTS 子查询并不返回满足条件的数据，因此子查询并不返回满足条件的数据，因此SELECT子句后子句后的字段列表并无多大意义，通常将通过的字段列表并无多大意义，通常将通过EXISTS引入的子查询的字段引入的子查询的字段列表设为列表设为“*”，而不使用具体列名。，而不使用具体列名。lEXISTS 子查询语法如下：子查询语法如下：NOT EXISTS(子查询子查询)第46页，此课件共79页哦5.4.3 EXISTS 和IN子查询l例：查询例：查询XS表中曾参加过考试的学生信息。表中曾参加过考试的学生信息。SELECT *FROM XSWHERE EXISTS (SELECT *FROM CJ WHERE XSDM=XS.XSDM)第47页，此课件共79页哦5.4.3 EXISTS 和IN子查询lIN关键字用来确定给定的值（测试表达式）关键字用来确定给定的值（测试表达式）是否与子查询或列表中的值相匹配，它的作是否与子查询或列表中的值相匹配，它的作用相当于用相当于“=ANY”。lIN子查询语法如下：子查询语法如下：测试表达式测试表达式 NOT IN (子查询子查询 或者或者 表达式表达式列表列表 )第48页，此课件共79页哦5.4.3 EXISTS 和IN子查询l例例：查询位于班级代码为：查询位于班级代码为22、23或或24的班级中所有学生的信息。的班级中所有学生的信息。SELECT *FROM XSWHERE BJDM IN(22,23，24)l例：查询属于计算机信息管理专业（专业代码为例：查询属于计算机信息管理专业（专业代码为6）所有学生的信）所有学生的信息。息。SELECT *FROM XSWHERE BJDM IN(SELECT BJDM FROM BJ WHERE ZYDM=6)第49页，此课件共79页哦5.4.4 5.4.4 派生表派生表l一个子查询能够返回一个由行和列构成的数一个子查询能够返回一个由行和列构成的数据集，我们可以将这个数据集再看作一个表据集，我们可以将这个数据集再看作一个表来处理，这个由子查询得出的新表就是我们来处理，这个由子查询得出的新表就是我们说的说的“派生表派生表”l派生表有利于提高查询速度、简化操作派生表有利于提高查询速度、简化操作l派生表不是存储在数据库中的对象，它只在派生表不是存储在数据库中的对象，它只在查询期间有效。查询期间有效。第50页，此课件共79页哦5.4.4 5.4.4 派生表派生表例例 查询班级代码为查询班级代码为22的班级（的班级（06级商务英语级商务英语1班）在学期班）在学期代码为代码为5的学期（的学期（20062007学年第学年第1期）所有学生的考试期）所有学生的考试成绩，包含学号，姓名，课程名称和分数。成绩，包含学号，姓名，课程名称和分数。步骤步骤1：我们从：我们从CJ表中取出符合条件的记录表中取出符合条件的记录SELECT *FROM CJWHERE XQDM=5 AND XSDM IN (SELECT XSDM FROM XS WHERE BJDM=22)第51页，此课件共79页哦5.4.4 5.4.4 派生表派生表步骤步骤2：将上步结果集当作虚拟表将上步结果集当作虚拟表a使用，并与使用，并与KC、XS表联表联接以获取姓名、课程名称等信息接以获取姓名、课程名称等信息SELECT XS.XH,XS.XM,KC.KCMC,a.FSFROM a JOIN XS ON XS.XSDM=a.XSDM JOIN KC ON a.KCDM=KC.KCDM第52页，此课件共79页哦5.4.4 5.4.4 派生表派生表步骤步骤3：将第一步的语句替换第二步中虚拟表将第一步的语句替换第二步中虚拟表aSELECT XS.XH,XS.XM,KC.KCMC,a.FSFROM (SELECT XSDM,KCDM,XQDM,FS FROM CJ WHERE XQDM=5 AND XSDM IN (SELECT XSDM FROM XS WHERE BJDM=22)AS a JOIN XS ON XS.XSDM=a.XSDM JOIN KC ON a.KCDM=KC.KCDM第53页，此课件共79页哦5.5 联合查询lUNION关键字可以将两个或更多相互独立的关键字可以将两个或更多相互独立的SELECT语句的查询结果合并成一个集合，语句的查询结果合并成一个集合，即执行联合查询。即执行联合查询。l联合查询要求合并的几个数据集之间应该具联合查询要求合并的几个数据集之间应该具有相同的字段数目和类型。有相同的字段数目和类型。例：例：在同一个数据集中返回贺清青同学所有科在同一个数据集中返回贺清青同学所有科目的考试成绩和平均成绩。目的考试成绩和平均成绩。第54页，此课件共79页哦5.5 联合查询l第一步，返回贺清青同学所有科目的成绩：第一步，返回贺清青同学所有科目的成绩：SELECT KC.KCMC 科目科目,ABS(CJ.FS)成绩成绩FROM CJ INNER JOIN KC ON CJ.KCDM=KC.KCDM INNER JOIN XS ON CJ.XSDM=XS.XSDMWHERE XS.XM=贺清青贺清青l第二步，返回贺清青同学所有科目的平均分：第二步，返回贺清青同学所有科目的平均分：SELECT AVG(ABS(CJ.FS)FROM CJ INNER JOIN XS ON CJ.XSDM=XS.XSDMWHERE XS.XM=贺清青贺清青第55页，此课件共79页哦5.5 联合查询l第三步，为了满足两个结果集具有相同类型的字段，我们对上一语句改为下面这种形式：第三步，为了满足两个结果集具有相同类型的字段，我们对上一语句改为下面这种形式：SELECT 科目科目=平均分平均分,成绩成绩=(SELECT AVG(ABS(CJ.FS)FROM CJ INNER JOIN XS ON CJ.XSDM=XS.XSDMWHERE XS.XM=贺清青贺清青)l第四部第四部,将第一步和第三步的结果用将第一步和第三步的结果用UNION关键字集合并：关键字集合并：SELECT KC.KCMC 科目科目,ABS(CJ.FS)成绩成绩FROM CJ INNER JOIN KC ON CJ.KCDM=KC.KCDM INNER JOIN XS ON CJ.XSDM=XS.XSDMWHERE XS.XM=贺清青贺清青UNIONSELECT 科目科目=平均分平均分,成绩成绩=(SELECT AVG(ABS(CJ.FS)FROM CJ INNER JOIN XS ON CJ.XSDM=XS.XSDMWHERE XS.XM=贺清青贺清青)第56页，此课件共79页哦5.6 通用表表达式CTE lCTE 与派生表类似，不存储为数据库对象，并且只在查询期间有效。与派生表类似，不存储为数据库对象，并且只在查询期间有效。l与派生表的不同，与派生表的不同，CTE 可在同一查询中引用多次，可自引用。可在同一查询中引用多次，可自引用。l使用使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。可以获得提高可读性和轻松维护复杂查询的优点。lCTE 由表示由表示CTE的表达式名称、可选列列表和定义的表达式名称、可选列列表和定义CTE的查询组成。的查询组成。lCTE的基本语法结构如下：的基本语法结构如下：WITH CTE_NAME (列名列名1,.n)AS(CTE查询语句查询语句)l运行运行 CTE 的语句为：的语句为：SELECT*FROM CTE_NAME 第57页，此课件共79页哦5.6 通用表表达式CTEl例例 查询班级代码为查询班级代码为22的班级（的班级（06级商务英语级商务英语1班）在学期代码为班）在学期代码为5的学期（的学期（20062007学年第学年第1期）期）所有学生的考试成绩，包含学号，姓名，课程名称和分数。所有学生的考试成绩，包含学号，姓名，课程名称和分数。WITH CTE_CJAS(SELECT *FROM CJWHERE XQDM=5 AND XSDM IN (SELECT XSDM FROM XS WHERE BJDM=22)SELECT XS.XH,XS.XM,KC.KCMC,a.FSFROM CTE_CJ AS a JOIN XS ON XS.XSDM=a.XSDM JOIN KC ON a.KCDM=KC.KCDM第58页，此课件共79页哦5.8 排名函数l5.8.1 ROW_NUMBERROW_NUMBER函数用来返回结果集内每行的行号，第一行从函数用来返回结果集内每行的行号，第一行从 1 开始。其语法为：开始。其语法为：ROW_NUMBER()OVER()位于位于OVER后的括号中需要一个后的括号中需要一个ORDER BY 子句，该子句用来确定为子句，该子句用来确定为行分配唯一行分配唯一 ROW_NUMBER 的顺序。的顺序。例例：查询：查询06级商务英语级商务英语1班（班级代码为班（班级代码为22）的所有学生的学号、姓）的所有学生的学号、姓名、出生日期、家庭是地址，要求第一列为行号名、出生日期、家庭是地址，要求第一列为行号,并按学号排序。并按学号排序。SELECT ROW_NUMBER()OVER(ORDER BY XH)行号行号,XH 学号学号,XM 姓名姓名,CSRQ 出生日期出生日期,JTDZ 家庭地址家庭地址FROM XS WHERE BJDM=22 第59页，此课件共79页哦5.8.2 RANK5.8.2 RANK与与DENSE_RANKDENSE_RANKlRANK与与DENSE_RANK函数都用来进行名次的排列。函数都用来进行名次的排列。RANK函数在排定名次时会以排在前面的记录数为依据，函数在排定名次时会以排在前面的记录数为依据，而而DENSE_RANK在排定名次时只考虑排在前面的不同在排定名次时只考虑排在前面的不同数值的个数作为依据。数值的个数作为依据。lRANK与与DENSE_RANK的语法和的语法和ROW_NUMBER函数函数相似，即：相似，即：RANK()OVER()或或DENSE_RANK()OVER()第60页，此课件共79页哦5.8.2 RANK5.8.2 RANK与与DENSE_RANKDENSE_RANKl例例 查询班级代码为查询班级代码为22的班级（的班级（06级商务英语级商务英语1班）在学班）在学期代码为期代码为5的学期（的学期（20062007学年第学年第1期）所有学生的总期）所有学生的总分和名次。分和名次。l步骤步骤1：查询学生的总分信息：查询学生的总分信息：lSELECT XS.XH 学号学号,XS.XM 姓名姓名,SUM(CJ.FS)总分总分lFROM CJ INNER JOINl XS ON XS.XSDM=CJ.XSDM INNER JOINl KC ON CJ.KCDM=KC.KCDMlGROUP BY XS.XH,XS.XM ORDER BY 总分总分第61页，此课件共79页哦5.8.2 RANK5.8.2 RANK与与DENSE_RANKDENSE_RANKl步骤步骤2：在：在SELECT子句中添加子句中添加RANK 函数，并在函数，并在ORDER BY子句子句中指定排名的依据：中指定排名的依据：RANK()OVER(ORDER BY SUM(CJ.FS)最终结果如下：最终结果如下：SELECT XS.XH 学号学号,XS.XM 姓名姓名,SUM(CJ.FS)总分总分,RANK()OVER(ORDER BY SUM(CJ.FS)名次名次FROM CJ INNER JOIN XS ON XS.XSDM=CJ.XSDM INNER JOIN KC ON CJ.KCDM=KC.KCDMGROUP BY XS.XH,XS.XM 第62页，此课件共79页哦5.8.2 RANK5.8.2 RANK与与DENSE_RANKDENSE_RANKl将上述语句中将上述语句中RANK替换为替换为DENSE_RANK，则执行结果为：则执行结果为：第63页，此课件共79页哦5.9 数据更新l5.9.1 使用使用INSERT语句插入数据语句插入数据1、简单数据插入、简单数据插入使用使用INSERT语句向表中插入单行数据的语法如下：语句向表中插入单行数据的语法如下：INSERT INTO （字段列表）（字段列表）VALUES（值列表）（值列表）l例例 学生李金龙刚转到我校学生李金龙刚转到我校06级经济信息管理班（级经济信息管理班（BJDM=26），请），请将该生数据添加到向将该生数据添加到向XS表中。该生的登记信息如下：姓名：李金表中。该生的登记信息如下：姓名：李金龙