第5章数据库的查询精选PPT.ppt

《第5章数据库的查询精选PPT.ppt》由会员分享，可在线阅读，更多相关《第5章数据库的查询精选PPT.ppt（55页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第5章章 数据库的查询数据库的查询第1页，本讲稿共55页5.1 基本查询基本查询语法格式：语法格式：SELECT DISTINCT|ALL FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC说明：说明：SELECT：指出要查询输出的字段名或值表达式。ALL:表示保留满足条件的所有记录（缺省）。DISTINCT:表示去掉重复记录。属性名表：属性名表：可以使用通配符“*”，表示所有的列。第2页，本讲稿共55页1、选择列、选择列（1）选择所有列：）选择所有列：使用“*”表示选择一个表或视图中的所有列【例例5.1】查询XSB表中的所有数据。SELECT*FROM

2、 XSB GO（2）选择表中指定的列：）选择表中指定的列：使用SELECT语句选择表中的某些列【例例5.2】查询XSB表中每个同学的姓名、专业和总学分。SELECT 姓名,专业,总学分 FROM XSB GO第3页，本讲稿共55页(3)定义列别名定义列别名【例例5.3】查询XSB表中计算机系同学的学号、姓名和总学分，结果中各列的标题分别指定为number、name和mark。SELECT 学号 AS number,姓名 AS name,总学分 AS markFROM XSBWHERE 专业=计算机第4页，本讲稿共55页(4)替换列中的数据：替换列中的数据，要使用替换列中的数据：替换列中的数据，

3、要使用CASE表达式表达式【例例5.4】查询XSB表中计算机系各同学的学号、姓名和总学分，若总学分为空值，显示“尚未选课”；若总学分小于50，显示“不及格”；若总学分在50与52之间，显示“合格”；若总学分大于52，显示“优秀”。列标题更改为“等级”。SELECT 学号,姓名,等级等级=CASE WHEN 总学分总学分 IS NULL THEN 尚未选课尚未选课 WHEN 总学分总学分=50 and 总学分总学分=52 THEN 合格合格 ELSE 优秀优秀 END FROM XSB WHERE 专业=计算机第5页，本讲稿共55页（5）计算列值：）计算列值：对列值可以进行计算，即对列值进行横向

4、统计并显示结果。【例例5.5】按120分计算成绩显示学号为081101的学生的成绩情况。SELECT 学号,课程号,成绩成绩120=成绩成绩*1.20 FROM CJB WHERE 学号=081101第6页，本讲稿共55页(6)消除结果集中的重复行消除结果集中的重复行 当表只选择某些列时，可能会出现重复行。例如，若对XSB表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT关键字消除结果集中的重复行。【例例5.6】对XSB表只选择专业和总学分，消除结果集中的重复行。SELECT DISTINCT 专业,总学分 FROM XSB第7页，本讲稿共55页(7)限制结果集返回行数限制

5、结果集返回行数 如果查询返回的结果集的行数非常多，可以使用TOP选项限制其返回的行数。表达式：是指定行数或占百分比的数。【例例5.7】对XSB表选择姓名、专业和总学分，只返回结果集的前6行。SELECT TOP 6 姓名,专业,总学分 FROM XSB第8页，本讲稿共55页(8)聚合函数：聚合函数：对某列进行纵向统计，显示统计结果 常用的聚合函数：常用的聚合函数：COUNT(DISTINCT|ALL*)统计记录个数 COUNT(DISTINCT|ALL)统计一列中值的个数 SUM(DISTINCT|ALL)计算一数值型列值的总和 AVG(DISTINCT|ALL)计算一数值型列值的平均值 MA

6、X(DISTINCT|ALL)求一列值的最大值 MIN(DISTINCT|ALL)求一列值的最小值第9页，本讲稿共55页【例例5.8】在XSB表中，统计计算机系学生的最高总学分，最低总学分，平均总学分。SELECT 最高总学分=MAX(总学分)，最低总学分=MIN(总学分)，平均总学分=AVG(总学分)FROM XSB WHERE 专业=计算机第10页，本讲稿共55页【例例5.9】统计选修了101课程的学生人数和平均成绩。SELECT 学生人数=COUNT(*),平均成绩=AVG(成绩)FROM CJB WHERE 课程号=101【例例5.10】统计编号为081101的学生的总成绩。SELEC

7、T 总成绩=SUM(成绩)FROM CJB WHERE 学号=081101第11页，本讲稿共55页【例例5.11】统计备注不为空的学生人数 SELECT 学生人数=COUNT(备注)FROM XSB【例例5.12】统计总学分超过50分的学生人数。SELECT 学生人数=COUNT(总学分)FROM XSB WHERE 总学分50第12页，本讲稿共55页2、WHERE子句：对记录的筛选子句：对记录的筛选(1)使用比较运算符使用比较运算符【例例5.13】查询XSB表中学号为081101同学的情况。SELECT 姓名,学号,总学分 FROM XSB WHERE 学号=081101【例例5.14】查询

8、XSB表中总学分大于50的同学的情况。SELECT 姓名,学号,出生时间,总学分 FROM XSB WHERE 总学分50第13页，本讲稿共55页【例例5.15】查询XSB表中通信工程专业总学分大于等于42的同学的情况。SELECT*FROM XSB WHERE 专业=通信工程 AND 总学分=42【例例5.16】查询XSB表中通信工程专业和计算机专业的同学的情况。SELECT*FROM XSB WHERE 专业=通信工程 OR 专业=计算机 第14页，本讲稿共55页通通 配配 符符说说 明明%代表代表0个或多个字符个或多个字符_（下画线）（下画线）代表单个字符代表单个字符 指定范围（如指定范

9、围（如a-f、0-9）或集合（如）或集合（如abcdef）中的任何单个字符中的任何单个字符指定不属于范围（如指定不属于范围（如 a-f、0-9）或集合）或集合（如（如abcdef）的任何单个字符）的任何单个字符通配符列表(2)字符匹配运算符：字符匹配运算符：LIKE LIKE谓词用于指出一个字符串是否与指定的字符串相匹配第15页，本讲稿共55页【例5.17】查询XSB表中姓“王”且单名的学生情况。SELECT *FROM XSB WHERE 姓名 LIKE 王_【例5.18】查询XSB表中姓“王”的学生情况。SELECT *FROM XSB WHERE 姓名 LIKE 王%第16页，本讲稿共5

10、5页【例5.19】查询XSB表中学号中倒数第3个数字为1且倒数第1个数在1到5之间的学生学号，姓名及专业。SELECT 学号,姓名,专业 FROM XSB WHERE 学号 LIKE%1_12345【例5.20】查询XSB表不姓王的学生学号和姓名。SELECT 学号,姓名 FROM XSB WHERE 学号 NOT LIKE 王%第17页，本讲稿共55页(3)范围比较运算符：范围比较运算符：BETWEEN and【例例5.21】查询XSB表中在1989年出生的学生情况。SELECT 学号,姓名,专业,出生时间 FROM XSB WHERE 出生时间 BETWEEN 1989-1-1 and 1

11、989-12-31该语句与下列语句等价：该语句与下列语句等价：SELECT 学号,姓名,专业,出生时间 FROM XSB WHERE 出生时间=1989-1-1 and 出生时间=1989-12-31第18页，本讲稿共55页(4)集合运算符：集合运算符：IN【例5.22】查询XSB表中专业为“计算机”或“通信工程”或“无线电”的学生的情况。SELECT *FROM XSBWHERE 专业 IN (计算机,通信工程,无线电)该语句与下列语句等价：该语句与下列语句等价：SELECT *FROM XSBWHERE 专业=计算机 or 专业=通信工程 or 专业=无线电第19页，本讲稿共55页(5)空

12、值比较空值比较：IS NULL【例5.23】查询总学分尚不定的学生情况。SELECT *FROM XSBWHERE 总学分 IS NULL 本例即查找总学分为空的学生，结果为空。第20页，本讲稿共55页1、子查询、子查询 T-SQL允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。5.2 高级查询高级查询第21页，本讲稿共55页(1)使用使用 IN的子查询的子查询【例5.24】查找选修了课程号为206的课程的学生的情况。SELECT *FROM

13、XSB WHERE 学号 IN (SELECT 学号 FROM CJB WHERE 课程号=206)第22页，本讲稿共55页【例5.25】查询未选修离散数学的学生名。SELECT 姓名FROM XSBWHERE 学号 NOT IN (SELECT 学号学号 FROM CJB WHERE 课程号 IN (SELECT 课程号课程号 FROM KCB WHERE 课程名=离散数学)第23页，本讲稿共55页（2）使用比较运算符的子查询）使用比较运算符的子查询【例5.26】查找选修了离散数学的学生学号。SELECT 学号FROM CJBWHERE 课程号=(SELECT 课程号课程号 FROM KCB

14、 WHERE 课程名=离散数学 )第24页，本讲稿共55页（3）使用）使用ALL，ANY的子查询的子查询 ANY：与子查询结果中的任意值相同 ALL：与子查询结果中的所有值相同 注意：ANY、ALL必须与关系比较符同时使用。【例4.30】查找比所有计算机系的学生年龄都大的学生。SELECT *FROM XSBWHERE 出生时间 ALL(SELECT 出生时间 FROM XSB WHERE 专业=计算机)第25页，本讲稿共55页【例5.27】查找选修了206课程，且成绩不低于101课程的最低成绩的学生的学号。SELECT 学号 FROM CJBWHERE 课程号=206 AND 成绩!=ALL

15、 (SELECT 成绩 FROM CJB WHERE 课程号=206 )该语句与下列语句等价：该语句与下列语句等价：SELECT 姓名，专业 FROM XSBWHERE 课程号=206 AND 成绩=(SELECT MAX(成绩)FROM CJB WHERE 课程号=206 )第27页，本讲稿共55页(4)使用使用EXISTS的子查询的子查询 EXISTS谓词用于测试子查询的结果是否存在，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。【例5.29】查找选修206号课程的学生姓名。SELECT 姓名FROM XSBWHERE EXISTS (SELECT *FROM C

16、JBWHERE 学号学号=XSB.学号学号 AND 课程号=206)第28页，本讲稿共55页2、连接查询、连接查询 在FROM子句中，指定连接的表。在WHERE子句中，指定连接的条件。由此实现多表连接查询。连接查询分类：自然连接 自身连接 外连接第29页，本讲稿共55页(1)自然连接自然连接【例5.30】查找选修了206课程且成绩在80分以上的学生姓名及成绩。SELECT 姓名,成绩FROM XSB,CJBWHERE XSB.学号=CJB.学号 AND 课程号=206 AND 成绩=80第30页，本讲稿共55页【例例5.31】查找学生的选课情况，显示姓名,专业,课程号,成绩 SELECT 姓名

17、,专业,课程号,成绩FROM XSB,CJBWHERE XSB.学号=CJB.学号【例例5.32】查找学生的选课情况，显示姓名,专业,课程名,成绩 SELECT 姓名,专业,课程名,成绩FROM XSB,CJB,KCBWHERE XSB.学号=CJB.学号 AND XSB.学号=CJB.学号第31页，本讲稿共55页【例5.33】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XSB.学号,姓名,课程名,成绩FROM XSB,KCB,CJBWHERE XSB.学号=CJB.学号 AND KCB.课程号=CJB.课程号AND 课程名=计算机基础 AND

18、成绩=80第32页，本讲稿共55页(2)以以JOIN关键字指定的连接关键字指定的连接 T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。【例5.34】查找每个学生的情况以及选修的课程情况。SELECT *FROM XSB INNER JOIN CJB ON XSB.学号=CJB.学号该语句与下列语句等价：该语句与下列语句等价：SELECT *FROM XSB ,CJB WHERE XSB.学号=CJB.学号可以不写第33页，本讲稿共55页【例5.35】用FROM子句的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名

19、及成绩。(与例5.33等价)SELECT XSB.学号,姓名,课程名,成绩FROM XSB JOIN CJB JOIN KCB ON CJB.课程号=KCB.课程号 ON XSB.学号=CJB.学号 WHERE 课程名=计算机基础 AND 成绩=80第34页，本讲稿共55页(3)自自身身连接连接【例5.36】查找不同课程成绩相同的学生的学号、课程号和成绩。SELECT a.学号,a.课程号,b.课程号,a.成绩FROM CJB a JOIN CJB b ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号 该语句与下列语句等价：该语句与下列语句等价：SELEC

20、T a.学号,a.课程号,b.课程号,a.成绩FROM CJB a ,CJB b WHERE a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课 程号 第35页，本讲稿共55页(4)外连接外连接 指定了OUTER关键字的连接为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括三种：左外连接（左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；右外连接（右外连接（RIGHT OUTER JOIN）：）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；完全外连接（完全外连接（FULL

21、OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。其中的其中的OUTER关键字均可省略。关键字均可省略。第36页，本讲稿共55页【例5.37】查找所有学生情况及他们选修的课程号，也包括未选修课程的学生情况。SELECT XSB.*,课程号FROM XSB LEFT JOIN CJB ON XSB.学号=CJB.学号【例5.38】查找被选修了的课程的选修情况和所有开设的课程名。SELECT CJB.*,课程名FROM CJB RIGHT JOIN KCB ON CJB.课程号=KCB.课程号第37页，本讲稿共55页(5)交叉连接交叉连接 交叉连接实际上是将两个表进

22、行笛卡尔积运算，结果表是由第一个表的每行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。【例5.39】列出学生所有可能的选课情况。SELECT 学号,姓名,课程号,课程名FROM XSB CROSS JOIN KCB 该语句与下列语句等价：该语句与下列语句等价：SELECT 学号,姓名,课程号,课程名FROM XSB，KCB第38页，本讲稿共55页3、GROUP BY 分组统计分组统计 GROUP BY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XSB表中的所有行分组，结果是每个专业的学生成为一组，对组内数据进行统计。【例5.40】查询XSB中所有专业。方法方

23、法1：SELECT 专业 FROM XSB GROUP BY 专业 方法方法2：SELECT DISTINCT 专业 FROM XSB第39页，本讲稿共55页【例5.41】求各专业的学生数。SELECT 专业,COUNT(*)AS 学生数FROM XSBGROUP BY 专业【例5.42】求被选修的各门课程的平均成绩和选课人数。SELECT 课程号,AVG(成绩)AS 平均成绩,COUNT(学号)AS 选修人数FROM CJBGROUP BY 课程号第40页，本讲稿共55页 使用使用ROLLUP操作符操作符【例5.43】查询每个专业的男生、女生人数、以及专业学生总人数。SELECT 专业,性别

24、,COUNT(*)AS 人数 FROM XSB GROUP BY 专业,性别 WITH ROLLUP 注：使用注：使用ROLLUP操作符后，将对操作符后，将对GROUP BY子句中子句中各列产生回总行，即按性别不同值产生回总行，再按各列产生回总行，即按性别不同值产生回总行，再按专业产生回总行。专业产生回总行。第41页，本讲稿共55页【例5.44】可以将上述语句与不带ROLLUP操作符的GROUP BY子句的执行情况做一个比较：SELECT 专业,性别,COUNT(*)AS 人数FROM XSBGROUP BY 专业,性别执行结果如下所示：第42页，本讲稿共55页【例5.45】查询各专业每门课程

25、的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。SELECT 课程名,专业,AVG(成绩)AS 平均成绩FROM CJB,KCB,XSBWHERE CJB.课程号=KCB.课程号 AND CJB.学号=XSB.学号GROUP BY 课程名,专业WITH ROLLUP第43页，本讲稿共55页使用使用CUBE操作符操作符【例5.46】查询每个专业的男生、女生人数、总人数及男生总数、女生总数、学生总人数。SELECT 专业,性别,COUNT(*)AS 人数FROM XSBGROUP BY 专业,性别 WITH CUBE第44页，本讲稿共55页HAVING子句子句：对分组数据进行筛选：对分组数

26、据进行筛选 与与WHERE子句类似，不过子句类似，不过HAVING子句的条件表达式子句的条件表达式中可以使用聚合函数，而中可以使用聚合函数，而WHERE子句中不可以。子句中不可以。【例5.47】查找平均成绩在85分以上的学生的学号和平均成绩。SELECT 学号,AVG(成绩)AS 平均成绩FROM CJBGROUP BY 学号HAVING AVG(成绩)=85执行结果如下所示：第45页，本讲稿共55页【例5.48】查找选修课程超过2门且成绩都在80分以上的学生的学号。SELECT 学号 FROM CJBWHERE 成绩=80GROUP BY 学号HAVING COUNT(*)2执行结果如下所示

27、：第46页，本讲稿共55页【例5.49】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT 学号,AVG(成绩)AS 平均成绩FROM CJBWHERE 学号 IN (SELECT 学号 FROM XSB WHERE 专业=通信工程 )GROUP BY 学号HAVING AVG(成绩)=85执行结果如下所示：第47页，本讲稿共55页4、ORDER BY子句子句 在在SELECT语句中，使用语句中，使用ORDER BY子句对查询结果进子句对查询结果进行排序。其中关键词行排序。其中关键词ASC：升序，：升序，DESC：降序。：降序。【例5.50】将通信工程专业的学生按出生时间

28、先后排序。SELECT *FROM XSBWHERE 专业=通信工程ORDER BY 出生时间第48页，本讲稿共55页【例例5.51】将计算机专业学生的将计算机专业学生的“计算机基础计算机基础”课程成课程成绩按降序排列。绩按降序排列。SELECT 姓名,课程名,成绩FROM XSB,KCB,CJBWHERE XSB.学号=CJB.学号 AND CJB.课程号=KCB.课程号 AND 课程名=计算机基础AND 专业=计算机ORDER BY 成绩 DESC执行结果如下所示：第49页，本讲稿共55页5、COMPUTE子句子句 COMPUTE子句用于分类汇总，将产生额外的汇总行。COMPUTE BY一

29、般要与ORDER BY子句一起使用。【例5.52】查找通信工程专业学生的学号、姓名、出生时间，并产生一个学生总人数行。SELECT 学号,姓名,出生时间 FROM XSB WHERE 专业=通信工程 COMPUTE COUNT(学号)执行结果如下所示：第50页，本讲稿共55页【例5.53】将学生按专业排序，并汇总各专业人数和平均学分。SELECT 学号,姓名,出生时间,总学分FROM XSBORDER BY 专业COMPUTE COUNT(学号)，AVG(总学分)BY 专业执行结果如下所示：第51页，本讲稿共55页6、SELECT中的其他语法中的其他语法(1)INTO 使用INTO子句可以将S

30、ELECT查询所得的结果保存到一个新建的表中。创建的表的结构由SELECT所选择的列决定，新创建的表中的记录由SELECT的查询结果决定。【例5.54】由XSB表创建“计算机系学生”表，包括学号和姓名 SELECT 学号,姓名 INTO CXSBFROM XSBWHERE 专业=计算机第52页，本讲稿共55页(2)UNION 联合联合 使用使用UNION子句可以将两个或多个子句可以将两个或多个SELECT查询的结果合并成一个结果集。查询的结果合并成一个结果集。规定：规定：(1)所有查询中的列数和列的顺序必须相同。所有查询中的列数和列的顺序必须相同。(2)数据类型必须兼容。数据类型必须兼容。【例

31、5.55】查找学号为081101和学号为081210两位同学的信息。SELECT*FROM XSB WHERE 学号=081101 UNION ALL SELECT*FROM XSB WHERE 学号=081210;第53页，本讲稿共55页 (3)FROM子句中使用子查询子句中使用子查询【例5.56】从XSB表中查找总学分大于50的男同学的姓名和学号。SELECT 姓名,学号,总学分 FROM (SELECT 姓名,学号,性别,总学分 FROM XSB WHERE 总学分50 )AS STUDENTWHERE 性别=1 该语句与下列语句等价：该语句与下列语句等价：SELECT 姓名,学号,总学分 FROM XSBWHERE 性别=1 and 总学分50第54页，本讲稿共55页【例5.57】查找1990之前出生的学生的姓名和专业，分别使用别名 stu_name 和 dept 表示。SELECT m.stu_name,m.deptFROM (SELECT*FROM XSB WHERE 出生时间19900101)AS m(num,stu_name,sex,birthday,dept,score,mem)该语句与下列语句等价：该语句与下列语句等价：SELECT stu_name=姓名,dept=专业FROM XSB WHERE YEAR(出生时间)1990 第55页，本讲稿共55页