第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表中的所有数据。SELEC

2、T*FROM 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现在学习的是第

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

6、INCT|ALL)计算一数值型列值的平均值 MAX(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【例

7、例5.10】统计编号为081101的学生的总成绩。SELECT 总成绩=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 姓名,学号,总学

8、分 FROM XSB WHERE 学号=081101【例例5.14】查询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表中姓“王”的学生情

10、况。SELECT *FROM XSB WHERE 姓名 LIKE 王%现在学习的是第16页，共55页【例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 学号,姓名

11、,专业,出生时间 FROM XSB WHERE 出生时间 BETWEEN 1989-1-1 and 1989-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 *FRO

12、M XSBWHERE 专业=计算机 or 专业=通信工程 or 专业=无线电现在学习的是第19页，共55页(5)空值比较空值比较：IS NULL【例5.23】查询总学分尚不定的学生情况。SELECT *FROM XSBWHERE 总学分 IS NULL 本例即查找总学分为空的学生，结果为空。现在学习的是第20页，共55页1、子查询、子查询 T-SQL允许SELECT多层嵌套使用，用来表示复杂的查询。子查询除了可以用在SELECT语句中，还可以用在INSERT、UPDATE及DELETE语句中。子查询通常与IN、EXIST谓词及比较运算符结合使用。5.2 高级查询高级查询现在学习的是第21页，共

13、55页(1)使用使用 IN的子查询的子查询【例5.24】查找选修了课程号为206的课程的学生的情况。SELECT *FROM 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

14、.26】查找选修了离散数学的学生学号。SELECT 学号FROM CJBWHERE 课程号=(SELECT 课程号课程号 FROM KCB WHERE 课程名=离散数学 )现在学习的是第24页，共55页（3）使用）使用ALL，ANY的子查询的子查询 ANY：与子查询结果中的任意值相同 ALL：与子查询结果中的所有值相同 注意：ANY、ALL必须与关系比较符同时使用。【例4.30】查找比所有计算机系的学生年龄都大的学生。SELECT *FROM XSBWHERE 出生时间 ALL(SELECT 出生时间 FROM XSB WHERE 专业=计算机)现在学习的是第25页，共55页【例5.27】查找

15、选修了206课程，且成绩不低于101课程的最低成绩的学生的学号。SELECT 学号 FROM CJBWHERE 课程号=206 AND 成绩!=ALL (SELECT 成绩 FROM CJB WHERE 课程号=206 )该语句与下列语句等价：该语句与下列语句等价：SELECT 姓名，专业 FROM XSBWHERE 课程号=206 AND 成绩=(SELECT MAX(成绩)FROM CJB WHERE 课程号=206 )现在学习的是第27页，共55页(4)使用使用EXISTS的子查询的子查询 EXISTS谓词用于测试子查询的结果是否存在，若子查询的结果集不为空，则EXISTS返回TRUE，

16、否则返回FALSE。【例5.29】查找选修206号课程的学生姓名。SELECT 姓名FROM XSBWHERE EXISTS (SELECT *FROM CJBWHERE 学号学号=XSB.学号学号 AND 课程号=206)现在学习的是第28页，共55页2、连接查询、连接查询 在FROM子句中，指定连接的表。在WHERE子句中，指定连接的条件。由此实现多表连接查询。连接查询分类：自然连接 自身连接 外连接现在学习的是第29页，共55页(1)自然连接自然连接【例5.30】查找选修了206课程且成绩在80分以上的学生姓名及成绩。SELECT 姓名,成绩FROM XSB,CJBWHERE XSB.学

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

18、 XSB.学号,姓名,课程名,成绩FROM XSB,KCB,CJBWHERE XSB.学号=CJB.学号 AND KCB.课程号=CJB.课程号AND 课程名=计算机基础 AND 成绩=80现在学习的是第32页，共55页(2)以以JOIN关键字指定的连接关键字指定的连接 T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。【例5.34】查找每个学生的情况以及选修的课程情况。SELECT *FROM XSB INNER JOIN CJB ON XSB.学号=CJB.学号该语句与下列语句等价：该语句与下列语句等价：SELECT *FROM XSB ,CJB WHERE

19、XSB.学号=CJB.学号可以不写现在学习的是第33页，共55页【例5.35】用FROM子句的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。(与例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.课程

20、号,a.成绩FROM CJB a JOIN CJB b ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号 该语句与下列语句等价：该语句与下列语句等价：SELECT 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）：结

21、果表中除了包括满足连接条件的行外，还包括左表的所有行；右外连接（右外连接（RIGHT OUTER JOIN）：）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；完全外连接（完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。其中的其中的OUTER关键字均可省略。关键字均可省略。现在学习的是第36页，共55页【例5.37】查找所有学生情况及他们选修的课程号，也包括未选修课程的学生情况。SELECT XSB.*,课程号FROM XSB LEFT JOIN CJB ON XSB.学号=CJB.学号【例5.38】查找被选修了的课程的选修情况

22、和所有开设的课程名。SELECT CJB.*,课程名FROM CJB RIGHT JOIN KCB ON CJB.课程号=KCB.课程号现在学习的是第37页，共55页(5)交叉连接交叉连接 交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。【例5.39】列出学生所有可能的选课情况。SELECT 学号,姓名,课程号,课程名FROM XSB CROSS JOIN KCB 该语句与下列语句等价：该语句与下列语句等价：SELECT 学号,姓名,课程号,课程名FROM XSB，KCB现在学习的是第38页，共55页3、G

23、ROUP BY 分组统计分组统计 GROUP BY子句主要用于根据字段对行分组。例如，根据学生所学的专业对XSB表中的所有行分组，结果是每个专业的学生成为一组，对组内数据进行统计。【例5.40】查询XSB中所有专业。方法方法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

24、平均成绩,COUNT(学号)AS 选修人数FROM CJBGROUP BY 课程号现在学习的是第40页，共55页 使用使用ROLLUP操作符操作符【例5.43】查询每个专业的男生、女生人数、以及专业学生总人数。SELECT 专业,性别,COUNT(*)AS 人数 FROM XSB GROUP BY 专业,性别 WITH ROLLUP 注：使用注：使用ROLLUP操作符后，将对操作符后，将对GROUP BY子句中各子句中各列产生回总行，即按性别不同值产生回总行，再按专列产生回总行，即按性别不同值产生回总行，再按专业产生回总行。业产生回总行。现在学习的是第41页，共55页【例5.44】可以将上述语

25、句与不带ROLLUP操作符的GROUP BY子句的执行情况做一个比较：SELECT 专业,性别,COUNT(*)AS 人数FROM XSBGROUP BY 专业,性别执行结果如下所示：现在学习的是第42页，共55页【例5.45】查询各专业每门课程的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。SELECT 课程名,专业,AVG(成绩)AS 平均成绩FROM CJB,KCB,XSBWHERE CJB.课程号=KCB.课程号 AND CJB.学号=XSB.学号GROUP BY 课程名,专业WITH ROLLUP现在学习的是第43页，共55页使用使用CUBE操作符操作符【例5.46】查询每个

26、专业的男生、女生人数、总人数及男生总数、女生总数、学生总人数。SELECT 专业,性别,COUNT(*)AS 人数FROM XSBGROUP BY 专业,性别 WITH CUBE现在学习的是第44页，共55页HAVING子句子句：对分组数据进行筛选：对分组数据进行筛选 与与WHERE子句类似，不过子句类似，不过HAVING子句的条件表达式子句的条件表达式中可以使用聚合函数，而中可以使用聚合函数，而WHERE子句中不可以。子句中不可以。【例5.47】查找平均成绩在85分以上的学生的学号和平均成绩。SELECT 学号,AVG(成绩)AS 平均成绩FROM CJBGROUP BY 学号HAVING

27、AVG(成绩)=85执行结果如下所示：现在学习的是第45页，共55页【例5.48】查找选修课程超过2门且成绩都在80分以上的学生的学号。SELECT 学号 FROM CJBWHERE 成绩=80GROUP BY 学号HAVING COUNT(*)2执行结果如下所示：现在学习的是第46页，共55页【例5.49】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT 学号,AVG(成绩)AS 平均成绩FROM CJBWHERE 学号 IN (SELECT 学号 FROM XSB WHERE 专业=通信工程 )GROUP BY 学号HAVING AVG(成绩)=85执行结果如下所示

28、：现在学习的是第47页，共55页4、ORDER BY子句子句 在在SELECT语句中，使用语句中，使用ORDER BY子句对查询结果进子句对查询结果进行排序。其中关键词行排序。其中关键词ASC：升序，：升序，DESC：降序。：降序。【例5.50】将通信工程专业的学生按出生时间先后排序。SELECT *FROM XSBWHERE 专业=通信工程ORDER BY 出生时间现在学习的是第48页，共55页【例例5.51】将计算机专业学生的将计算机专业学生的“计算机基础计算机基础”课程成绩课程成绩按降序排列。按降序排列。SELECT 姓名,课程名,成绩FROM XSB,KCB,CJBWHERE XSB.

29、学号=CJB.学号 AND CJB.课程号=KCB.课程号 AND 课程名=计算机基础AND 专业=计算机ORDER BY 成绩 DESC执行结果如下所示：现在学习的是第49页，共55页5、COMPUTE子句子句 COMPUTE子句用于分类汇总，将产生额外的汇总行。COMPUTE BY一般要与ORDER BY子句一起使用。【例5.52】查找通信工程专业学生的学号、姓名、出生时间，并产生一个学生总人数行。SELECT 学号,姓名,出生时间 FROM XSB WHERE 专业=通信工程 COMPUTE COUNT(学号)执行结果如下所示：现在学习的是第50页，共55页【例5.53】将学生按专业排序

30、，并汇总各专业人数和平均学分。SELECT 学号,姓名,出生时间,总学分FROM XSBORDER BY 专业COMPUTE COUNT(学号)，AVG(总学分)BY 专业执行结果如下所示：现在学习的是第51页，共55页6、SELECT中的其他语法中的其他语法(1)INTO 使用INTO子句可以将SELECT查询所得的结果保存到一个新建的表中。创建的表的结构由SELECT所选择的列决定，新创建的表中的记录由SELECT的查询结果决定。【例5.54】由XSB表创建“计算机系学生”表，包括学号和姓名 SELECT 学号,姓名 INTO CXSBFROM XSBWHERE 专业=计算机现在学习的是第

31、52页，共55页(2)UNION 联合联合 使用使用UNION子句可以将两个或多个子句可以将两个或多个SELECT查询的结果合并成一个结果集。查询的结果合并成一个结果集。规定：规定：(1)所有查询中的列数和列的顺序必须相同。所有查询中的列数和列的顺序必须相同。(2)数据类型必须兼容。数据类型必须兼容。【例5.55】查找学号为081101和学号为081210两位同学的信息。SELECT*FROM XSB WHERE 学号=081101 UNION ALL SELECT*FROM XSB WHERE 学号=081210;现在学习的是第53页，共55页 (3)FROM子句中使用子查询子句中使用子查询

32、【例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页