第4章 数据库查询

1、第第4 4章章 数据库查询数据库查询本章教学目标本章教学目标n了解数据库查询的作用；n掌握简单查询的基本方法；n掌握统计查询的基本方法；n掌握连接查询的基本方法；n掌握子查询的基本方法；n掌握建立和使用视图的基本方法。本章教学任务本章教学任务 数据库的简单查询数据库的简单查询 数据库的统计查询数据库的统计查询 数据库的连接查询数据库的连接查询 数据库的子查询数据库的子查询1234 创建和使用视图创建和使用视图5任务任务4.1 4.1 数据库的简单查询数据库的简单查询 回顾复习回顾复习 任务描述任务描述 任务准备任务准备 任务演示任务演示1234 课堂实践课堂实践5 课堂讨论课堂讨论6 小结与作

2、业小结与作业7回顾复习回顾复习1.SQL Server1.SQL Server数据库建立有哪两种方法？数据库建立有哪两种方法？ 图形化建立和图形化建立和T-SQLT-SQL语句建立。语句建立。2.2.数据库的数据操作有哪几种？数据库的数据操作有哪几种？ 插入数据、修改数据、删除数据。插入数据、修改数据、删除数据。 任务描述任务描述 1.1.投影查询投影查询 查询客户的信息，要求结果中的列标题分别为客查询客户的信息，要求结果中的列标题分别为客户编号、客户联系人和客户电话。户编号、客户联系人和客户电话。 2.2.选择查询选择查询 查询商品的信息，要求显示价格在查询商品的信息，要求显示价格在5000

3、5000元及以下元及以下且商品名称里面含有且商品名称里面含有“美的美的”两个字的商品的编两个字的商品的编号、名称和价格。号、名称和价格。 任务准备任务准备 3.3.排序查询排序查询 查询客户的信息，要求按照客户积分由高到低进查询客户的信息，要求按照客户积分由高到低进行排序，显示客户的编号、客户单位、客户联系行排序，显示客户的编号、客户单位、客户联系人和客户积分。人和客户积分。 任务准备任务准备客户程序客户程序查询请求查询请求查询结果集查询结果集SQL SERVERA B C D E F G SELECT * FROM SALES查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储查

4、询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来任务准备任务准备n 简单查询简单查询：这里主要是指对数据库中的一个数据：这里主要是指对数据库中的一个数据表进行的数据库查询，主要涉及内容有选择项的表进行的数据库查询，主要涉及内容有选择项的处理、选择条件的设计及函数的应用等。处理、选择条件的设计及函数的应用等。n SELECTSELECT语句语句：SELECTSELECT语句是语句是SQLSQL语言中最核心的语言中最核心的语句，语句基本格式如下：语句，语句基本格

5、式如下： SELECT FROM WHERE 任务准备任务准备SELECT * FROM tb_Reader WHERE Sex = 男男男男马小伟马小伟200007男男王大林王大林200001男男孙大伟孙大伟200005任务准备任务准备n WHEREWHERE子句与查询条件子句与查询条件 任务准备任务准备&下面技术知识请同学们课内或课外进行自主学习下面技术知识请同学们课内或课外进行自主学习 教材知识完善与拓展中的内容：教材知识完善与拓展中的内容： SELECT SELECT语句结构语句结构 WHEREWHERE子句中的查询查件子句中的查询查件: :运算符等运算符等| 在课堂讨论中相关

6、知识可以进行交流讨论在课堂讨论中相关知识可以进行交流讨论任务演示任务演示 参考教材参考教材P128-P130P128-P130 1. 1.投影查询投影查询 2.2.选择查询选择查询 3.3.排序查询排序查询 任务演示任务演示1.1.查询客户的信息，结果中的列标题分别显示为客查询客户的信息，结果中的列标题分别显示为客户编号、客户联系人和客户电话。户编号、客户联系人和客户电话。 USE DB_CRMSELECT CID AS 客户编号,CContact AS 客户联系人,CPhone AS 客户电话FROM TB_Customer任务演示任务演示2.2.查询商品的信息，显示价格在查询商品的信息，显

7、示价格在50005000元及以下且商元及以下且商品名称里面含有品名称里面含有“美的美的”两个字的商品的编号、两个字的商品的编号、名称和价格。名称和价格。 USE DB_CRMSELECT PID,PName,PPriceFROM TB_ProductWHERE PPrice=2课堂实践课堂实践（1 1）按照任务过程，完成三个子任务查询。）按照任务过程，完成三个子任务查询。（2 2）查询商品表中属于家用电器的各种商品的最高价）查询商品表中属于家用电器的各种商品的最高价格和最低价格。格和最低价格。（3 3）统计有反馈信息的客户人数。）统计有反馈信息的客户人数。（4 4）统计业务员表中的各个部门的男

8、女员工人数。）统计业务员表中的各个部门的男女员工人数。（5 5）统计至少订购过两次的客户编号。）统计至少订购过两次的客户编号。（6 6）在客户订购表中统计商品被订购数量超过）在客户订购表中统计商品被订购数量超过500500个个的商品编号。的商品编号。课堂讨论课堂讨论n 聚合函数主要用在哪些地方？聚合函数主要用在哪些地方？n GROUP BYGROUP BY的功能？的功能？n HAVINGHAVING子句的功能？子句的功能？n 知识完善与拓展中的相关内容。知识完善与拓展中的相关内容。小结与作业小结与作业n 本任务主要包括以下知识：本任务主要包括以下知识： 1.1.聚合函数聚合函数 2. GROU

9、P BY2. GROUP BY语句语句 3. HAVING3. HAVING语句语句n 完成综合实训完成综合实训4 4：统计查询：统计查询，完成本章选择题第，完成本章选择题第1 1、2 2、3 3题（题（P155P155） 。任务任务4.3 4.3 数据库的连接查询数据库的连接查询 回顾复习回顾复习 任务描述任务描述 任务准备任务准备 任务演示任务演示1234 课堂实践课堂实践5 课堂讨论课堂讨论6 小结与作业小结与作业7回顾复习回顾复习1.GROUP BY1.GROUP BY子句的作用？子句的作用？ 对数据进行分组。对数据进行分组。2.HAVING 2.HAVING 子句的作用？子句的作用？

10、 用于限定对统计组的查询用于限定对统计组的查询 。任务描述任务描述 1.1.使用谓词实时连接查询使用谓词实时连接查询 查询编号为查询编号为“SM001”SM001”的业务员发展客户的情况，的业务员发展客户的情况，要求显示业务员编号、业务员姓名、客户编号、要求显示业务员编号、业务员姓名、客户编号、客户姓名、客户联系方式。客户姓名、客户联系方式。 2.2.内连接查询内连接查询 查询每个业务员的任务计划，要求显示业务员编查询每个业务员的任务计划，要求显示业务员编号、姓名、任务的编号以及实施情况。号、姓名、任务的编号以及实施情况。任务描述任务描述 3.3.自连接查询自连接查询 查询部门不同但岗位级别一

11、样的业务员信息，要查询部门不同但岗位级别一样的业务员信息，要求显示业务员编号、姓名、所在部门和岗位级别。求显示业务员编号、姓名、所在部门和岗位级别。 4.4.外连接查询外连接查询 查询每个客户对商品的反馈情况，包括没反馈信查询每个客户对商品的反馈情况，包括没反馈信息的客户情况，要求显示客户编号、客户联系人、息的客户情况，要求显示客户编号、客户联系人、商品编号、反馈时间、反馈内容和解决情况。商品编号、反馈时间、反馈内容和解决情况。任务准备任务准备n 连接查询连接查询：连接查询主要应用于多表进行查询，：连接查询主要应用于多表进行查询，通过各个表中相同属性列的相关性进行数据查通过各个表中相同属性列的

12、相关性进行数据查询。询。 n 谓词连接谓词连接：在：在SELECTSELECT语句的语句的WHEREWHERE子句中使用比子句中使用比较运算符给出连接条件对多张表进行连接，这较运算符给出连接条件对多张表进行连接，这种表示形式称为连接谓词表示形式。种表示形式称为连接谓词表示形式。n JOINJOIN连接连接：JOINJOIN连接又分为内连接、外连接和连接又分为内连接、外连接和交叉连接，其中外连接又分为左外连接、右外交叉连接，其中外连接又分为左外连接、右外连接和完全外连接。连接和完全外连接。任务准备任务准备n 连接谓词表示形式基本格式如下：连接谓词表示形式基本格式如下： SELECT FROM ，

13、 WHERE . . 其中：连接操作符主要为：其中：连接操作符主要为：=、=、=、!=、!、!任务准备任务准备n JOINJOIN连接连接 SELECT FROM table_source join_type table_source ON search_condition任务准备任务准备n 内连接内连接。内连接按照。内连接按照ONON所指定的连接条件合并所指定的连接条件合并两个表，返回满足条件的行。两个表，返回满足条件的行。n 外连接外连接。外连接的结果集不但包含满足连接条。外连接的结果集不但包含满足连接条件的行，还包括相应表中的所有行。件的行，还包括相应表中的所有行。n 左外连接左外连接。

14、结果集中除了包括满足连接条件的。结果集中除了包括满足连接条件的行外，还包括左表的所有行。行外，还包括左表的所有行。任务准备任务准备n 右外连接。结果集中除了包括满足连接条件的行右外连接。结果集中除了包括满足连接条件的行外，还包括右表的所有行。外，还包括右表的所有行。n 完全外连接。结果集中除了包括满足连接条件的完全外连接。结果集中除了包括满足连接条件的行外，还包括两个表的所有行。行外，还包括两个表的所有行。 任务演示任务演示 参考教材参考教材P137-P139 P137-P139 1. 1.使用谓词实施连接查询使用谓词实施连接查询 2.2.内连接查询内连接查询 3.3.自连接查询自连接查询 4

15、.4.外连接查询外连接查询 任务演示任务演示 1.1.询编号为询编号为“SM001”SM001”的业务员发展客户的情况，的业务员发展客户的情况，显示业务员编号、业务员姓名、客户编号、客户显示业务员编号、业务员姓名、客户编号、客户姓名、客户联系方式。姓名、客户联系方式。USE DB_CRMSELECT TB_Salesman.SID,SName,CID,CContact,CPhoneFROM TB_Salesman,TB_CustomerWHERE TB_Salesman.SID=TB_Customer.SID任务演示任务演示 2.2.查询每个业务员的任务计划，显示业务员编号、查询每个业务员的任

16、务计划，显示业务员编号、姓名、任务的编号以及实施情况。姓名、任务的编号以及实施情况。USE DB_CRMSELECT TB_Salesman.SID,SName,TID,TPerformFROM TB_Salesman INNER JOIN TB_TaskON TB_Salesman.SID=TB_Task.SID任务演示任务演示 3.3.查询部门不同但岗位级别一样的业务员信息，查询部门不同但岗位级别一样的业务员信息，显示业务员编号、姓名、所在部门和岗位级别。显示业务员编号、姓名、所在部门和岗位级别。 USE DB_CRMSELECT S1.SID,S1.SName,S1.SDID,S1.SP

17、ostIDFROM TB_Salesman AS S1 JOIN TB_Salesman AS S2ON S1.SDID!=S2.SDID AND S1.SPostID=S2.SPostID任务演示任务演示 4.4.查询每个客户对商品的反馈情况，包括没反馈查询每个客户对商品的反馈情况，包括没反馈信息的客户情况，显示客户编号、客户联系人、信息的客户情况，显示客户编号、客户联系人、商品编号、反馈时间、反馈内容和解决情况。商品编号、反馈时间、反馈内容和解决情况。 USE DB_CRMSELECT TB_Customer.CID,CContact,PID,FTime,FContent,FResolve

18、FROM TB_Customer LEFT JOIN TB_FeedbackON TB_Customer.CID=TB_Feedback.CID课堂实践课堂实践（1 1）按照任务过程，完成四个子任务查询。）按照任务过程，完成四个子任务查询。（2 2）查询每个部门的业务员信息，要求显示业务员编）查询每个部门的业务员信息，要求显示业务员编号、姓名，所在部门、部门名称。号、姓名，所在部门、部门名称。（3 3）统计至少订购过两次的客户信息，要求显示客户）统计至少订购过两次的客户信息，要求显示客户编号、客户单位和客户联系人。编号、客户单位和客户联系人。（4 4）查询每位客户的订购信息，不包括没有订购任何

19、）查询每位客户的订购信息，不包括没有订购任何商品的客户订购信息，要求显示客户编号、客户单位、商品的客户订购信息，要求显示客户编号、客户单位、客户联系人、商品编号、订购时间和订购数量。客户联系人、商品编号、订购时间和订购数量。课堂实践课堂实践（5 5）查询每位业务员的任务实施信息，包括没有任务）查询每位业务员的任务实施信息，包括没有任务计划的业务员任务实施信息，要求显示业务员编号、计划的业务员任务实施信息，要求显示业务员编号、姓名、所在部门、任务编号和实施情况。姓名、所在部门、任务编号和实施情况。（6 6）查询商品优惠情况，但商品类型不同的商品信息，）查询商品优惠情况，但商品类型不同的商品信息，

20、要求显示商品编号、商品名称、商品类型和商品优惠要求显示商品编号、商品名称、商品类型和商品优惠情况。情况。课堂讨论课堂讨论n 连接查询的功能？连接查询的功能？n 左外连接与右外连接的差别？左外连接与右外连接的差别？n 知识完善与拓展中的相关内容。知识完善与拓展中的相关内容。小结与作业小结与作业n 本任务主要包括以下知识：本任务主要包括以下知识： 1. 1. 谓词连接谓词连接 2. JOIN2. JOIN连接连接n 完成综合实训完成综合实训4 4：连接查询：连接查询，完成本章简答题第，完成本章简答题第2 2题（题（P156P156） 。任务任务4.4 4.4 数据库的子查询数据库的子查询 回顾复习

21、回顾复习 任务描述任务描述 任务准备任务准备 任务演示任务演示1234 课堂实践课堂实践5 课堂讨论课堂讨论6 小结与作业小结与作业7回顾复习回顾复习1.1.连接查询主要功能是什么？连接查询主要功能是什么？ 实现多表的查询。实现多表的查询。2.2.外连接分为哪些？外连接分为哪些？ 左外连接、右外连接和完成外连接。左外连接、右外连接和完成外连接。任务描述任务描述 1.IN1.IN子查询子查询 查询有商品反馈信息的客户信息，要求显示客户查询有商品反馈信息的客户信息，要求显示客户编号和姓名。编号和姓名。 2.2.比较子查询比较子查询 查询积分最高的客户信息，要求显示客户编号、查询积分最高的客户信息，

22、要求显示客户编号、客户单位、客户联系人和客户积分。客户单位、客户联系人和客户积分。任务描述任务描述 3.EXISTS3.EXISTS子查询子查询 查询从来没有购买商品的客户信息，要求显示客查询从来没有购买商品的客户信息，要求显示客户编号、客户单位、客户联系人和客户电话。户编号、客户单位、客户联系人和客户电话。任务准备任务准备n 子查询子查询：在查询条件中，可以使用另一个查询：在查询条件中，可以使用另一个查询的结果作为条件的一部分，作为查询条件一部的结果作为条件的一部分，作为查询条件一部分的查询称为子查询。分的查询称为子查询。n ININ子查询用于判断一个给定值是否在子查询的子查询用于判断一个给

23、定值是否在子查询的结果集中，格式为：结果集中，格式为： Expression NOT IN ( subquery )Expression NOT IN ( subquery ) 任务准备任务准备n 比较子查询。比较子查询。它让表达式的值与子查询的结果进它让表达式的值与子查询的结果进行比较运算，格式为：行比较运算，格式为： expression expression 比较运算符比较运算符 ALL | SOME | ANY ALL | SOME | ANY ( subquery )( subquery )任务准备任务准备n EXISTSEXISTS子查询子查询。带。带EXISTSEXISTS的子查

24、询不返回任何记的子查询不返回任何记录，只产生逻辑值录，只产生逻辑值TRUETRUE或者或者FALSEFALSE，它的作用是，它的作用是在在WHEREWHERE子句中测试子查询返回的行是否存在。子句中测试子查询返回的行是否存在。n 格式为：格式为：n NOT EXISTS ( subquery ) NOT EXISTS ( subquery )任务演示任务演示 参考教材参考教材P142-P143P142-P143 1.IN 1.IN子查询子查询 2.2.比较子查询比较子查询 3.EXISTS3.EXISTS子查询子查询任务演示任务演示 1.1.查询有商品反馈信息的客户信息，显示客户编查询有商品反

25、馈信息的客户信息，显示客户编号和姓名。号和姓名。 USE DB_CRMSELECT CID,CContactFROM TB_CustomerWHERE CID IN(SELECT CID FROM TB_Feedback)任务演示任务演示 2.2.查询积分最高的客户信息，显示客户编号、客查询积分最高的客户信息，显示客户编号、客户单位、客户联系人和客户积分。户单位、客户联系人和客户积分。 USE DB_CRMSELECT CID,CCompany,CContact,CIntegrationFROM TB_CustomerWHERE CIntegration =all(SELECT CIntegr

26、ation FROM TB_Customer)任务演示任务演示 3.3.查询从来没有购买商品的客户信息，显示客户查询从来没有购买商品的客户信息，显示客户编号、客户单位、客户联系人和客户电话。编号、客户单位、客户联系人和客户电话。 USE DB_CRMSELECT CID,CCompany,CContact,CPhoneFROM TB_CustomerWHERE NOT EXISTS (SELECT * FROM TB_Buy WHERE TB_Customer.CID=CID)课堂实践课堂实践（1 1）按照任务实施过程的要求完成各子任务并检查）按照任务实施过程的要求完成各子任务并检查实施结果。

27、实施结果。（2 2）使用子查询，查询客户积分高于）使用子查询，查询客户积分高于7070的客户信息，的客户信息，要求显示客户编号、客户信用档案编号、客户品要求显示客户编号、客户信用档案编号、客户品德及素质评分。德及素质评分。（3 3）使用子查询，统计至少订购过两次的客户信息，）使用子查询，统计至少订购过两次的客户信息，要求显示客户编号、客户单位和客户联系人。要求显示客户编号、客户单位和客户联系人。课堂实践课堂实践（4 4）使用子查询，查询不良记录评分最高的客户信）使用子查询，查询不良记录评分最高的客户信息，要求显示客户编号、客户单位和客户联系人。息，要求显示客户编号、客户单位和客户联系人。（5

28、5）使用子查询，查询比上海客户信用得分高的浙）使用子查询，查询比上海客户信用得分高的浙江的客户信息，要求显示客户编号、客户单位和江的客户信息，要求显示客户编号、客户单位和客户联系人。客户联系人。（6 6）使用子查询，查询在）使用子查询，查询在20132013年没有任务计划的业年没有任务计划的业务员信息，要求显示业务员编号、姓名和所在部务员信息，要求显示业务员编号、姓名和所在部门。门。课堂讨论课堂讨论n 子查询的功能？子查询的功能？n 各种子查询是否可以互换？各种子查询是否可以互换？n 知识完善与拓展中的相关内容。知识完善与拓展中的相关内容。小结与作业小结与作业n 本任务主要包括以下知识：本任务

29、主要包括以下知识： 1. IN1. IN子查询子查询 2. 2. 比较子查询比较子查询 3. EXISTS3. EXISTS子查询子查询 n 完成综合实训完成综合实训4 4：子查询：子查询，完成本章填空题第，完成本章填空题第2 2、3 3题（题（P156P156） 。任务任务4.5 4.5 创建和使用视图创建和使用视图 回顾复习回顾复习 任务描述任务描述 任务准备任务准备 任务演示任务演示1234 课堂实践课堂实践5 课堂讨论课堂讨论6 小结与作业小结与作业7回顾复习回顾复习1.1.什么是子查询？什么是子查询？ 在查询条件中，可以使用另一个查询的结果作在查询条件中，可以使用另一个查询的结果作为

30、条件的一部分，作为查询条件一部分的查询为条件的一部分，作为查询条件一部分的查询称为子查询。称为子查询。2.2.子查询主要有哪几种？子查询主要有哪几种？ ININ子查询、比较子查询、子查询、比较子查询、EXISTSEXISTS子查询。子查询。任务描述任务描述 1.1.创建视图创建视图 创建一个名称为创建一个名称为V_CustomerV_Customer的视图，视图包括客的视图，视图包括客户表的客户编号、客户单位、客户联系人、客户户表的客户编号、客户单位、客户联系人、客户电话、客户积分和客户信用等级。电话、客户积分和客户信用等级。 2.2.管理视图管理视图 对对V_CustomerV_Custom

31、er视图进行修改，要求添加负责客户视图进行修改，要求添加负责客户的业务员编号。的业务员编号。 任务描述任务描述 3.3.利用视图进行数据查询利用视图进行数据查询 查询查询V_CustomerV_Customer中业务员中业务员SM001SM001联系的客户信息。联系的客户信息。 4.4.利用视图进行数据更新利用视图进行数据更新 将业务员将业务员SM001SM001联系的客户积分加联系的客户积分加1010。 任务准备任务准备n 视图是一张视图是一张虚拟表虚拟表，它表示一张表的部分数据或它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表多张表的综合数据，其结构和数据是建立在对表的查

32、询基础上。的查询基础上。n 视图中并不存放数据，而是存放在视图所引用的视图中并不存放数据，而是存放在视图所引用的原始表（基表）中。原始表（基表）中。n 同一张原始表，根据不同用户的不同需求，可以同一张原始表，根据不同用户的不同需求，可以创建不同的视图。创建不同的视图。任务准备任务准备n 使用图形化界面创建视图使用图形化界面创建视图 任务准备任务准备n 使用图形化界面创建视图使用图形化界面创建视图 任务准备任务准备n T-SQLT-SQL创建视图的语法：创建视图的语法： CREATE VIEW . view_name (column ,.n ) ASselect_statement WITH C

33、HECK OPTION 任务准备任务准备n T-SQLT-SQL修改视图的语法：修改视图的语法：ALTER VIEW . view_name (column ,.n )ASselect_statement 任务准备任务准备n T-SQLT-SQL删除视图的语法：删除视图的语法：DROP VIEW view_name (column ,.n )任务演示任务演示 参考教材参考教材P145-P148P145-P148 1. 1.创建视图创建视图 2.2.管理视图管理视图 3.3.利用视图进行数据查询利用视图进行数据查询 4.4.利用视图进行数据更新利用视图进行数据更新任务演示任务演示 1.1.创建视图创建视图任务演示任务演示 1.1.创建视图创建