第6章 查询技术.ppt

《第6章 查询技术.ppt》由会员分享，可在线阅读，更多相关《第6章 查询技术.ppt（34页珍藏版）》请在金锄头文库上搜索。

1、第6章 查询技术,SELECT 主要子句,SELECT ALL | DISTINCT TOP expression PERCENT WITH TIES INTO new_table FROM ,.n WHERE GROUP BY ALL group_by_expression ,.n WITH CUBE | ROLLUP HAVING ORDER BY order_expression ASC|DESC COMPUTE AVG|COUNT|MAX|MIN|SUM (expression) ,.n BY expression ,.n ,SELECT 主要子句,参数说明如下： SELECT子句用于

2、指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等。 INTO子句用于指定所要生成的新表的名称。 FROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。 WHERE子句用来限定查询的范围和条件。 GROUP BY子句是分组查询子句。 HAVING子句用于指定分组子句的条件。 GROUP BY子句、HAVING子句和集合函数一起可以实现对每个组生成一行和一个汇总值。 ORDER BY子句可以根据一个列或者多个列来排序查询结果，在该子句中，既可以使用列名，也可以使用相对列号。 ASC表示升序排列，DESC表示降序排列。 COMPUTE子句使用

3、集合函数在查询的结果集中生成汇总行。 COMPUTE BY子句用于增加各列汇总行。,6.1 基本SELECT语句,6.1.1 投影查询 6.1.2 条件查询,6.1.1 投影查询,最基本的 SELECT 语句仅有两个部分：要返回的列，和这些列源于的表。也就是说查询均为不使用WHERE子句的无条件查询，也称作投影查询。 例6-1 查询全体学生的学号、姓名和年龄。 程序清单如下： SELECT SNO, SN, AGE FROM S 例6-2 查询学生的全部信息。 程序清单如下： SELECT * FROM S 注意：用 * 表示表的全部列名，而不必逐一列出。,6.1.1 投影查询,例6-3 查询

4、选修了课程的学生号。 程序清单如下： SELECT DISTINCT SNO FROM SC 注意：应用DISTINCT消除查询结果以某列为依据的重复行。上例中，sc表中相同学号（SNO）的纪录只保留第一行，余下的具有相同学号的记录将从查询结果中清除。也就是每个同学保留一条选课纪录。 另外，利用投影查询可控制列名的顺序，并可通过指定别名改变查询结果的列标题的名字，如下例。 例6-4 查询全体学生的姓名、学号和年龄。 程序清单如下： SELECT SN NAME, SNO, AGE FROM S 注意：NAME为SN的别名，这里我们改变了列的显示顺序。,6.1.2 条件查询,当要在表中找出满足某

5、些条件的行时，则需使用WHERE子句指定查询条件。WHERE子句中，条件通常通过三部分来描述：列名；比较运算符；列名、常数。 条件查询又可分为以下几方面内容： 1、比较大小和确定范围； 2、部分匹配查询； 3、空值查询； 4、查询的排序,6.1.2 条件查询,1、比较大小和确定范围 例6-5 查询选修课程号为C1的学生的学号和成绩。 程序清单如下： SELECT SNO,SCORE FROM SC WHERE CNO=C1 例6-6 查询成绩高于85分的学生的学号、课程号和成绩。 程序清单如下： SELECT SNO,CNO,SCORE FROM SC WHERE SCORE85,6.1.2

6、条件查询,1、比较大小和确定范围 当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。 例6-7 查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。 程序清单如下： SELECT SNO，CNO，SCORE FROM SC WHERE（CNO=C1 OR CNO=C2） AND SCORE=85 SQL语句中也有一个特殊的 BETWEEN 运算符，用于检查某个值是否在两个值之间（包括等于两端的值）。 例6-8 查询工资在1000至1500之间的教师

7、的教师号、姓名及职称。 程序清单如下： SELECT TNO,TN,PROF FROM T WHERE SAL BETWEEN 1000 AND 1500 上面SQL语句等价于以下语句： SELECT TNO,TN,PROF FROM T WHERE SAL=1000 AND SAL=1500,6.1.2 条件查询,1、比较大小和确定范围 注意：在SELECT语句中可利用“IN”操作来查询属性值属于指定集合的元组。利用“NOT IN”可以查询指定集合外的元组。如下面两个例子。 例6-10 查询选修C1或C2的学生的学号、课程号和成绩。 程序清单如下： SELECT SNO, CNO, SCOR

8、E FROM SC WHERE CNO IN(C1, C2) 此语句也可以使用逻辑运算符“OR”实现。相应的程序清单如下： SELECT SNO, CNO, SCORE FROM SC WHERE CNO=C1 OR CNO= C2,6.1.2 条件查询,2、部分匹配查询 当不知道完全精确的値时，用户还可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。LIKE运算使我们可以使用通配符来执行基本的模式匹配。 使用LIKE运算符的一般格式为： LIKE 字符串常量的字符可以包含如表6-2所示的通配符。,表6-2 通配符及说明,6.1.2 条件查询,2、部分匹配查询 例6-12 查

9、询所有姓张的教师的教师号和姓名。 程序清单如下： SELECT TNO, TN FROM T WHERE TN LIKE 张% 例6-13 查询姓名中第二个汉字是“力”的教师号和姓名。 程序清单如下： SELECT TNO, TN FROM T WHERE TN LIKE _力%,6.1.2 条件查询,3、空值查询 某个字段没有值称之为具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。 例6-15 查询没有考试成绩的学生的学号和相

10、应的课程号。 程序清单如下： SELECT SNO, CNO FROM SC WHERE SCORE IS NULL 注意：这里的空值条件为IS NULL，不能写成SCORE=NULL。,6.1.2 条件查询,4查询的排序 当需要对查询结果排序时，应该在SELECT语句中使用ORDER BY子句。ORDER BY 子句包括了一个或多个用于指定排序顺序的列名，排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。ORDER BY子句必须出现在其他子句之后。 ORDER BY 子句支持使用多列。可以使用以逗号分隔的多个列作为排序依据：查询结果将先按指定的第一列进行排序，然后再按指定的下一列

11、进行排序。 例6-16 查询选修C1 的学生学号和成绩，并按成绩降序排列。 程序清单如下： SELECT SNO, SCORE FROM SC WHERE CNO=C1 ORDER BY SCORE DESC,6.2 分组查询,6.2.1 聚合函数和GROUP BY子句 6.2.2 GROUP BY 和 WHERE 子句、HAVING 子句,6.2.1 聚合函数和GROUP BY子句,GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的聚合值。如果聚合函数没有使用 GROUP BY 子句，则只为 SELECT 语句报告一个聚合值。常用

12、的聚合函数，如表6-3所示。,表6-3常用的聚合函数,6.2.1 聚合函数和GROUP BY子句,例6-18通过查询求学号为S1学生的总分和平均分。 程序清单如下： SELECT SUM(SCORE) AS TotalScore, AVG(SCORE) AS AveScore FROM SC WHERE SNO = S1 注意：函数SUM和AVG只能对数值型字段进行计算。 例6-19 通过查询求选修C1号课程的最高分、最低分及之间相差的分数 程序清单如下： SELECT MAX(SCORE) AS MaxScore, MIN(SCORE) AS MinScore, MAX(SCORE)- MI

13、N(SCORE) AS Diff FROM SC WHERE (CNO = C1),6.2.1 聚合函数和GROUP BY子句,例6-20 通过查询求管理系学生的总数。 程序清单如下： SELECT COUNT(SNO) FROM S WHERE DEPT=管理 例6-21 通过查询求学校中共有多少个系。 程序清单如下： SELECT COUNT(DISTINCT DEPT) AS DeptNum FROM S 注意：加入关键字DISTINCT后表示消去重复行，可计算字段“DEPT“不同值的数目。COUNT函数对空值不计算，但对零进行计算。 例6-22 统计有成绩的学生的人数。 程序清单如下：

14、 SELECT COUNT (SCORE) FROM SC 注意：上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。,6.2.1 聚合函数和GROUP BY子句,例6-23 利用特殊函数COUNT(*)求计算机系学生的总数 程序清单如下： SELECT COUNT(*) FROM S WHERE DEPT=计算机 注意：上例中，COUNT（*）用来统计元组的个数。此函数不消除重复行，也不允许使用DISTINCT关键字。 在分组查询中，只要表达式中不包括聚合函数，就可以按该表达式分组。如下例所示。 例6-24 查询每位学生的学号及其选课的门数。 程序清单如下： SELECT CNO,C

15、OUNT(*) AS C_NUM FROM SC GROUP BY CNO GROUP BY子句按CNO的值分组，所有具有相同CNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位学生选课的门数。 例6-25统计各年度出生的雇员人数 程序清单如下。 Use adventureworks SELECT DATEPART(year, birthdate ) AS Year, COUNT(*) AS NumberOfemployees FROM humanresources.employee GROUP BY DATEPART(year, birthdate),6.2.2 GROUP B

16、Y 和 WHERE 子句、HAVING 子句,可以在包含 GROUP BY 子句的查询中使用 WHERE 子句。在完成任何分组之前，将消除不符合 WHERE 子句中的条件的行。若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。 例6-26 在分组查询中使用WHERE条件，查询计算机系的学生学号及平均成绩。 程序清单如下： SELECT sno, AVG(score) AS AverageScore FROM sc WHERE sno=(select sno from s where dept=计算机) GROUP BY sno ORDER BY sno GO,6.2.2 GROUP BY 和 WHERE 子句、HAVING 子句,例6-27 在分组查询中使用HAVING条件，查询平均成绩大于85的学生学号及平均成绩。 程序清单如下： SELECT sno, AVG(score) AS AverageScore FROM sc GROUP BY sno HAVING AVG(score) 85 GO