《情境四-公司管理数据库系统的查询》由会员分享，可在线阅读，更多相关《情境四-公司管理数据库系统的查询（16页珍藏版）》请在金锄头文库上搜索。
1、- 可编辑情境四公司管理数据库系统的查询模块 1 公司管理数据库系统的简单查询一、教学目标1准确理解Transact-SQL查询语句的基本结构2能根据需要灵活使用查询语句的各子句二、工作任务公司所有雇员的信息都存储在companyinfo数据库的employee表中，该表中包含了雇员的雇员ID 、姓名、性别、出生年月、雇佣日期、特长、薪水7 个字段和相应的记录，请利用 Transact-SQL的 SELECT 语句实现下列查询操作。1 查询所有雇员的信息，要求输出的结果格式如表4-1 所示。表 4-1 雇员的信息表姓名性别出生年月雇佣日期特长薪水2 查询所有 男雇员的信息。3 按照如表 4-2
2、 所示的格式，列出所有雇员的姓名和薪水。表 4-2 雇员的姓名和薪水表雇员的姓名雇员的酬金4 查询所有薪水超过3000 元的雇员的信息。5 查询所有薪水在2000 元至 3000 元之间的雇员的姓名和雇佣日期，并按雇佣日期的先后排列。6 统计各种特长的雇员人数。7 查询所有姓 章的雇员的信息。三、相关实践知识（一）按照任务1 的要求查询公司所有雇员的信息。操作步骤如下：1连接 companyinfo数据库，有以下两种方法：从“查询分析器” 窗口的工具栏的下拉列表中选中公司管理数据库companyinfo。在查询分析器窗口中输入如下命令，并运行。USE companyinfo在查询分析器窗口中输
3、入以下查询语句- 可编辑SELECT 姓名，性别，出生年月，雇佣日期，特长，薪水FROM employeeGO2。执行查询语句方法。（二）按照任务2 查询所有 男雇员的信息。查询语句如下：SELECT *FROM employeeWHERE 性别 = 男（三）按照任务3 规定的格式，列出所有雇员的姓名和薪水。为了使查询的结果更加友好，可以改变列的标题，即指定列的别名。已知在employee表中有雇员的“姓名”和“薪水”字段，但任务要求显示结构为“雇员的姓名”和“雇员的酎金” ，可以用AS 引导给出列的别名。查询语句如下：SELECT 姓名AS 雇员的姓名 , 薪水AS 雇员的酬金FROM em
4、ployee（四）按照任务4 列出所有薪水超过的3000元的雇员的信息。查询语句如下：SELECT * FROM employeeWHERE 薪水 =3000（五） 按照任务5 列出所有薪水在2000元至 3000元之间的雇员的姓名、 雇佣日期和薪水，并按雇佣日期的先后排列。查询语句如下：SELECT 姓名 , 雇佣日期 , 薪水FROM employeeWHERE 薪水between 2000 and 3000ORDER BY 雇佣日期（六）按照任务6 统计各种特长的雇员人数。查询语句如下：SELECT 特长， count(*) 人数FROM employeeGROUP BY 特长执行结果如
5、下：- 可编辑特长人数- - 唱歌2二胡2钢琴2古筝2会计1计算机8书法4跳舞2武术1演讲1音乐2游泳1（所影响的行数为12 行）（七）列出所有姓章的雇员的信息。查询语句如下：SELECT *FROM employeeWHERE 姓名LIKE 章 %四、相关知识使用数据库和数据表的主要目的是存储数据，以便在需要时进行检索、统计或组织输出，通过Transact-SQL的 SELECT 语句可以从表或视图中迅速、方便地检索数据。在众多的Transact-SQL语句中， SELECT 语句是使用频率最高的一个。查询的最基本方式是使用SELECT 语句，按照用户给定的条件从SQL Server 200
6、0数据库中取出数据，并将数据通过一个或多个结果集返回给用户。（一） SELECT 语句结构SELECT 语句的主要的子句可归纳如下：SELECT ALL| DISTINCT , INTO FROM , WHERE GROUP BY HAVING ORDER BY ASC | DESC - 可编辑其中， 包含子句SELECT ，INTO ，FROM ，WHERE ，GROUP BY ，HAVING ，ORDER BY 等，每个子句都有各自的用法和功能。SELECT 子句：指定由查询返回的列。INTO 子句：将检索结果存储到新表或视图中。FROM 子句：用于指定引用的列所在的表和视图。WHERE
7、子句：指定用于限制返回的行的搜索条件。GROUP BY 子句：指定用来放置输出行的组，并且如果SELECT 子句 中包含聚合函数，则计算每组的汇总值。HAVING子句：指定组或聚合的搜索条件。HAVING 通常与GROUP BY 子句一起使用。如果不使用GROUP BY 子句， HAVING 的行为与WHERE 子句一样。ORDER BY 子句：指定结果集的排序。（二） SELECT 子句SELECT 子句的功能，用于返回指定列的数据集。SELECT 语句的常规使用格式：SELECT ALL | DISTINCT TOP N PERCENT 列名 1, 列名 2, 列名NFROM 表名或视图名
8、其中参数的含义如下：ALL：指定在结果集中可以显示重复行。ALL 是默认设置。DISTINCT ：指定在结果集中只能显示惟一行，即表示输出无重复的所有记。TOP N PERCENT ：指定只从查询结果集中输出前N 行。如果还指定了PERCENT，则只从结果集中输出前百分之N 行。1查询所有的列在 SELECT 子句中，通配符“*”表示输出指定的表或视图中所有的列。【例 4.1 】从 companyinfo数据库的客户表(customer)中检索所有客户的所有信息。USE companyinfoGOSELECT *FROM customerGO 2查询特定的列【例 4.2 】从 companyi
9、nfo数据库的客户表(customer)中检索所有客户的公司名称、联系人姓名、地址。USE companyinfoGOSELECT 公司名称，联系人姓名，地址- 可编辑FROM customerGO 【例 4.3 】从 companyinfo数据库的雇员表（employee）中检索所有的雇员的姓名和特长。USE companyinfoGOSELECT 姓名，特长FROM employeeGO 3指定特定列的列名【例 4.4 】查询每个人的姓名和薪水降低30% 后的信息。有三种方法：（1）采用符合ANSI 规则的标准方法，在列表达式后面给出列名。相应的命令如下：USE companyinfoGO
10、SELECT 姓名 , 薪水原薪水 , 薪水 -薪水 *0.3 现薪水 FROM employeeGO（2）用“ = ”来连接列表达式。相应的命令如下：USE companyinfoGOSELECT 姓名 , 原薪水 = 薪水 , 现薪水 = 薪水 -薪水 *0.3FROM employeeGO（3）用 AS 关键字来连接列表达式和指定的列名。相应的命令如下：USE companyinfoGOSELECT 姓名 , 薪水as 原薪水 , 薪水 -薪水 *0.3 as 现薪水 FROM employeeGO4.删除重复的列【例 4.5 】从 companyinfo数据库的订单表(p_order)
11、中，检索已经被订购的产品的产品名。USE companyinfoGO- 可编辑SELECT DISTINCT 产品名FROM p_orderGO 5使用 TOP 关键字SELECT TOP N | TOP N PERCENT 列名 1, 列名 2, 列名NFROM 表名其中参数如下；TOP N ：表示返回最前面的N 行， N 表示返回的行数。TOP N PERCENT：表示返回的前面的N% 行。【例 4.6 】查询 companyinfo数据库的订单表(p_order) 中最前面的10 行纪录。USE companyinfoGOSELECT top 10 *FROM p_orderGO 【例
12、4.7 】查询 companyinfo数据库的订单表(p_order)中的前面的 10% 记录。USE companyinfoGOSELECT top 10 percent *FROM p_order6使用计算列【例 4.8 】显示每种产品的价格降低30% 的产品信息。USE companyinfoGOSELECT 产品名，库存量，单价，单价-单价 *0.3FROM productGO（二）INTO 子句INTO 子句用于创建新表并将查询的结果插入新表中，其语法如下：INTO 新表名【例 4.9 】使用 INTO 子句创建一个包含employee表中姓名和薪水字段，且名为new_employe
13、e的新表。USE companyinfoGO- 可编辑SELECT 姓名 , 薪水INTO new_employeeFROM employeeGO（三）WHERE子句使用 WHERE 子句的目的是为了从表格的数据集中过滤出符合条件的行。使用 WHERE子句可以限制查询的范围，提高查询效率。语法格式如下：SELECT 列名 1 , 列名 2, 列名 NFROM 表名WHERE 搜索条件1使用算术表达式【例 4.10 】 查询 companyinfo数据库的雇员表（employee）中，特长是 计算机的雇员的信息。USE companyinfoGO SELECT *FROM employee WH
14、ERE 特长 = 计算机 GO【例 4.11 】 在 companyinfo数据库的雇员表 （employee）中，查询薪水超过3000元的雇员的姓名和薪水。USE companyinfoGO SELECT 姓名，薪水FROM employee WHERE 薪水 =3000GO2使用逻辑表达式在 Transact-SQL语句中，常用的逻辑运算符分别是：NOT ：非运算，对表达式的否定。AND ：与运算，连接多个条件，所有的条件都成立时为真。OR： 或运算，连接多个条件，只要有一个条件成立就为真。【例 4.12 】在 companyinfo数据库的雇员表（employee）中，查询特长为书法 或
15、- 可编辑钢琴 的所有雇员的雇员ID 、姓名和特长。USE companyinfoGO SELECT 雇员 ID ，姓名，特长FROM employeeWHERE 特长 = 书法 or 特长 = 钢琴 GO查询结果如下：雇员 ID 姓名特长- - - 2 李立三书法10 姜玲娜书法12 金林皎书法15 刘启芬钢琴22 钱其娜书法26 欧阳天民钢琴3使用范围表达式使用 BETWEEN 关键字可以更方便地表示查询数据的范围。语法格式为：表达式NOT BETWEEN 表达式 1 AND 表达式 2 【例 4.13 】 在 companyinfo数据库的雇员表（employee）中，查询薪水在3000
16、元至 4000 元的雇员的姓名和薪水。SELECT 姓名，薪水FROM employee WHERE 薪水 between 3000 and 4000【例 4.14 】查询库存量大于200 或小于 100 的产品的产品名、库存量和单价。SELECT 产品名，库存量，单价FROM productWHERE 库存量not between 200 and 1004使用 IN 关键字同 BETWEEN 关键字一样， IN 的引入也是为了更方便地限制检索数据的范围，灵活使用 IN 关键字，可以用简洁的语句实现结构复杂的查询。语法格式为：- 可编辑表达式NOT IN (表达式 1 , 表达式 2 , 表达
17、式 N)【例4.15 】在雇员表（employee）中，查询所有特长为计算机 、钢琴 、 书法 的雇员的雇员ID 、姓名、特长。USE companyinfoGO SELECT 雇员 ID ，姓名 , 特长FROM employeeWHERE 特长in ( 计算机 ， 钢琴 ，书法 )GO 5通配符的使用LIKE 子句在大多数情况下会与通配符配合使用。通配符的含义见教材表4-4 。【例4.16 】在雇员表（employee）中，查询所有姓名中含有“利”字的雇员的雇员ID 和姓名。SELECT 雇员 ID ，姓名FROM employeeWHERE 姓名LIKE %利%GO【例 4.17 】查询
18、雇员表（employee）中所有雇员ID 满足第 2 个字符为“ 1”的雇员的姓名和出生年月。SELECT 雇员 ID ，姓名，出生年月FROM employeeWHERE 雇员 ID LIKE _1%GO6使用 NULL 关键字在 WHERE 子句中不能使用比较运算符对空值进行判断，只能使用空值表达式来判断某个表达式是否为空值。如下所示：表达式IS NULL或表达式IS NOT NULL（四）ORDER BY子句应用程序中经常需要对检索得到的数据集进行排序。可以利用ORDER BY 子句实现。语法格式为：ORDER BY 表达式 1 ASC | DESC ，表达式2 ASC | DESC ,
19、N【例 4.18 】在 companyinfo数据库的订单表(p_order) 中，查询产品名，数量和订货- 可编辑日期，并按的订货日期的降序显示。USE companyinfoGO SELECT 产品名，数量，订货日期FROM p_orderORDER BY 订货日期 /DESCGO说明： （ 1）如果在 SELECT 中同时指定了TOP，则 ORDER BY 无效。 （2）空值被视为最低的值。（五）GROUP BY子句使用GROUP BY 子句可以对数据按照某列进行分组。GROUP BY 子句的作用是把FROM子句中的关系按分组属性划分为若干组，同一组内所有的记录在分组属性上是相同的。其语
20、法格式如下：GROUP BY 分组表达式【例 4.19 】统计订单表(p_order) 中所有产品的已订购总额。USE companyinfoGOSELECT sum ( 数量 ) FROM p_order【例 4.20 】 在订单表 (p_order) 中，查询每类产品（按产品ID 分类）的订购总和。USE compamyinfoSELECT 产品 ID , 产品名 , sum( 数量 ) as 总数量FROM p_order GROUP BY 产品 ID 【例 4.21 】在订单表 (p_order) 中，按“产品ID”分类，求出各类产品的价格总和、平均价格及各类产品的数量。USE com
21、pamyinfo GO SELECT 产品 ID, sum( 单价 ) 价格总和 , avg( 单价 ) 平均单价 , count(*)FROM p_orderGROUP BY 产品 ID GO （六）HAVING子句HAVING 通常与GROUP BY 子句一起使用，用于指定组或聚合的搜索条件。语法格- 可编辑式如下：HAVING 搜索条件 当 HAVING 与 GROUP BY ALL 一起使用时， HAVING 子句替代ALL。 在 HAVING 子句中不能使用TEXT、IMAGE 和 NTEXT 数据类型。【例 4.22 】查询平均价格超过10 元的产品的种类。USE companyi
22、nfoGOSELECT 类别 ID , avg( 单价 ) 平均价格 FROM ProductGROUP By l 类别 IDHAVING avg( 单价 )10GO （七）COMPUTE子句在 SELECT 语句中，使用COMPUTE 子句，表示既显示查看结果的明细行，又显示汇总行，即使用COMPUTE子句将产生额外的汇总行，可以计算分组的汇总值，也可以计算整个结果集的汇总值。语法格式：COMPUTE 聚合函数名(列名) , N BYexpression , N 其中，聚合函数见表4-5 。【例 4.23 】查找雇员表（ empoyee）中各雇员的姓名、出生年月和特长，并产生一个雇员总人数行
23、。USE companyinfoGOSELECT 姓名，出生年月，特长FROM employeeCOMPUTE count(雇员 ID 模块 2 公司管理数据库系统的多表查询一、教学目标使用联接查询和嵌套查询的方法实现companyinfo数据库系统的多表查询。二、工作任务公司将与产品订单有关的数据保存在订单表（p_order ） 中，此表中共包含了6 个字段，分别是：订单ID 、产品 ID、数量、雇员ID 、客户 ID 和订货日期；在产品表（product）中包含了产品ID、产品名、类别ID 、单价、库存量5 个字段；在顾客表（customer）中- 可编辑包含了客户ID 、公司名称、联系人
24、姓名、联系方式、地址和邮编6 个字段；在类别表（category）中包含了类别ID、类别名和说明3 个，且每个表中都包含了相应的记录。请利用 Transact-SQL的 SELECT 语句实现下列查询操作。1 查询已订购了产品的公司的公司名称，联系人姓名和所订产品的产品名称和数量。2 查询所有订购了鼠标 产品的公司的公司名称和联系方式。3 查询客户名为 通恒机械 的公司所订购产品的产品名和数量。4 查询 鼠标 所属的类别名和相应的说明。三、相关实践知识点（一）按照任务1 查询已订购了产品的公司的公司名称，联系人姓名和所订产品的产品名称和数量。操作步骤与简单查询相同，首先连接服务器，然后连接co
25、mpanyinfo数据库，接着在查询分析器输入查询语句，最后执行语句。在此模块中，不再详细介绍。此任务的查询语句如下：SELECT 公司名称，联系人姓名，产品名，数量FROM customer INNER JOIN p_order ON customer. 客户 ID=p_order.客户 ID通过客户 ID 将客户表customer与订单表p_order进行内连接， 这样可以同时从两个表中获取信息。表联接条件经常使用“主键= 外键”的形式。（二）按照任务2 查询所有订购了鼠标 产品的公司的公司名称和联系方式。由于 鼠标 为产品的名称，顾客的名称与联系方式分别属于不同的表，因而需使用多张进行查
26、询。查询语句如下：SELECT 公司名称 ,联系方式FROM customerWHERE 客户 ID=(SELECT 客户 IDFROM p_orderWHERE 产品 ID=(SELECT 产品 IDFROM productWHERE 产品名 = 鼠标 )首先在产品表中查询鼠标的产品ID 号，然后根据产品ID 号，在订单表p_order表将订购 鼠标 的客户 ID 查出，最后根据客户ID 在客户表customer中将公司的名称和联系方式查出。（三）任务3 查询客户名为通恒机械 的公司所订购产品的产品ID 和数量。- 可编辑查询语句如下：SELECT 公司名称，产品ID ，数量FROM cus
27、tomer as A INNER JOIN p_order as BON A.客户 ID=B. 客户 IDWHERE A. 公司名称 = 通恒机械 为了便于操作，将客户表（customer）定义别名A，订单表（ p_order ）定义别名B，所以在 ON 子句和 WHERE 子句方便的书写为A.客户 ID 和 B.客户 ID 。（四）任务4 查询 鼠标 所属的类别名和相应的说明。查询语句如下：SELECT 类别名，说明FROM categoryWHERE 类别 ID=(SELECT 类别 ID FROM productWHERE 产品名 = 鼠标 )四、相关知识点（一）用联接进行多表查询通过联
28、接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接将定义SQL Server 2000如何使用一个表中的记录来选择相关联数据表中的记录。联接条件通过以下方法定义两个表在查询中的关联方式：指定每个表中要用于联接的字段。 典型的联接条件在一个表中指定外键，在另一个表中指定与其关联的键。指定比较各字段的值时要使用逻辑运算符（= 、 等） 。联接查询包括内联接、外联接和交叉联接。1 内联接内联接也叫自然联接，它是联接两个表的常用方法。内联接通过使用比较运算符，根据需要联接的数据表中公共的字段值来匹配两个表中的记录，将两个表中满足联接条件的记录组合起来作为结果。内联接有两种形式的语法结构：（
29、1） SELECT FROM 表 1 inner JOIN 表 2ON 表 1.列 = 表 2.列（2）SELECT FROM 表 1,表 2WHERE 表 1.列= 表 2.列2外联接在自然联接中， 只有在两个表中匹配的记录才能在结果集中出现。而在外联接中可以只限制一个表，而对另外一个表不加限制（即所有的行都出现在结果集中）。- 可编辑外联接分为左外联接、右外联接和全外联接。左外联接是对联接条件中左边的表不加限制；右外联接是对联接条件中右边的表不加限制；全外联接对两个表都不加限制，所有两个表中的记录都会包括在结果集中。（1）左外联接的语法为：SELECT FROM 表 1 LEFT OUTE
30、R JOIN 表 2ON 表 1.列 1= 表 2.列 2包括第一个命名表（ “左”表，出现在JOIN 子句的最左边）中的所有行。不包括右表中的不匹配行。（2）右外联接的语法为：SELECT FROM 表 1 RIGHT OUTER JOIN 表 2ON 表 1.列 1= 表 2.列 2包括第二个命名表（ “右”表，出现在JOIN 子句的最右边）中的所有行。不包括左表中的不匹配行。（3）全外联接的语法为：SELECT FROM 表 1 FULL OUTER JOIN 表 2ON 表 1.列 1= 表 2.列 2包括所有联接表中的所有记录，不论它们是否匹配。（二）用嵌套查询的方式实现多表查询在实
31、际应用中，经常要用到多层查询。在SQL Server 2000中，将一条SELECT 语句作为另一条SELECT 语句的一部分称为嵌套查询。外层的 SELECT 语句被称为外部查询或父查询，内层的SELECT 语句成为内部查询或子查询。Transact-SQL语言允许多层嵌套，但是子查询语句中不允许出现ORDER BY 子句，ORDER BY 子句永远只能对最终查询结果排序。1 带有比较运算符的子查询在该方式下， 通过子查询返回一个单一的数据，该数据可以参加相关表达式的运算。当子查询返回的是单值时，可以使用 ， ， = ，= ，!= 或 等比较运算符。【例 4.24 】查询 东南实业 所订购的
32、产品的订单号和订购数量。分析：查询东南实业的订单号和订购数量，可以首先由公司名称= 东南实业 在customer表中查出该公司相应的客户ID ，然后通过客户ID 在 p_order表中，查询所有该客户 ID 下的订单ID 和数量。所以可以分为两步执行。第一步：确定东南实业 的客户 ID- 可编辑SELECT 客户 IDFROM customerWHERE 公司名称 = 东南实业 执行结果：客户 ID2 第二步：查询客户ID 为“ 2”定购的订单号和产品名SELECT 订单 ID，数量FROM p_orderWHERE 客户 ID=2分布执行较麻烦。可以使用嵌套查询来一步实现，将第一步查询嵌套在
33、第二步查询中，以构成第二步的查询条件。SELECT 订单 ID，数量FROM p_orderWHERE 客户 ID= （SELECT 客户 IDFROM costomerWHERE 公司名称 = 东南实业 ）说明： 在这种方法下通过子查询取得的数据必须是惟一的，不能返回多值， 否则运行将出现错误。2 带有 IN 关键词的子查询使用 IN 关键词联接父查询和子查询的关系，通过IN 关键词判断查询的某项属性值是否在子查询结果中。此时通常子查询的返回结果往往是一个集合。【例 4.25 】查询订购了类别ID 为“ 2”的所有订单的订单ID 和订货日期。分析：产品的类别ID 放在产品表product中，
34、订单相关的信息存放在p-order表中。因此需要通过产品ID 将产品表product和订单表p-order联接起来；而一个类别的产品可能不止一件产品，因此通过WHERE 子句从产品表product中所有的产品ID 。在通过子查询得到产品ID ，从订单表p-order表中取出相关的信息。查询命令如下：SELECT 订单 ID，订货日期FROM p-orderWHERE 产品 ID IN (SELECT 产品 IDFROM productWHERE 类别 ID=2 )3带有 NOTEXISTS引出的子查询- 可编辑使用 NOT EXISTS关键字的子查询时，相当于进行一次存在测试。子查询不返回任何实际数据，它只返回TRUE 或 FALSE 值。【例 4.26 】查询客户ID 为“ 1”的公司订购的所有订单的订单ID 和数量。SELECT 订单 ID，数量FROM p-orderWHERE EXISTS (SELECT *FROM customerWHERE 客户 ID=1)使用 EXISTS 关键词后，若内层查询结果为非空，则外层WHERE 子句返回真值，否则返回假。
