情境四-公司管理数据库系统的查询.doc

网络数据库管理与开发情境四 公司管理数据库系统的查询模块1 公司管理数据库系统的简单查询一、教学目标1准确理解Transact-SQL查询语句的基本结构2能根据需要灵活使用查询语句的各子句二、工作任务公司所有雇员的信息都存储在companyinfo数据库的employee表中，该表中包含了雇员的雇员ID、姓名、性别、出生年月、雇佣日期、特长、薪水7个字段和相应的记录，请利用Transact-SQL的SELECT语句实现下列查询操作。1 查询所有雇员的信息，要求输出的结果格式如表4-1所示。表4-1雇员的信息表姓名性别出生年月雇佣日期特长薪水2 查询所有男雇员的信息。3 按照如表4-2所示的格式，列出所有雇员的姓名和薪水。表4-2雇员的姓名和薪水表雇员的姓名雇员的酬金4 查询所有薪水超过3000元的雇员的信息。5 查询所有薪水在2000元至3000元之间的雇员的姓名和雇佣日期，并按雇佣日期的先后排列。6 统计各种特长的雇员人数。7 查询所有姓章的雇员的信息。三、相关实践知识（一）按照任务1的要求查询公司所有雇员的信息。操作步骤如下：1连接companyinfo数据库，有以下两种方法：l 从“查询分析器”窗口的工具栏的下拉列表中选中公司管理数据库companyinfo。l 在查询分析器窗口中输入如下命令，并运行。USE companyinfo在查询分析器窗口中输入以下查询语句SELECT 姓名，性别，出生年月，雇佣日期，特长，薪水FROM employeeGO2。执行查询语句方法。（二）按照任务2查询所有男雇员的信息。查询语句如下：SELECT *FROM employeeWHERE 性别=男（三）按照任务3规定的格式，列出所有雇员的姓名和薪水。 为了使查询的结果更加友好，可以改变列的标题，即指定列的别名。已知在employee表中有雇员的“姓名”和“薪水”字段，但任务要求显示结构为“雇员的姓名”和“雇员的酎金”，可以用AS引导给出列的别名。查询语句如下：SELECT 姓名 AS 雇员的姓名, 薪水 AS 雇员的酬金FROM employee（四）按照任务4列出所有薪水超过的3000元的雇员的信息。查询语句如下：SELECT * FROM employeeWHERE 薪水=3000（五）按照任务5列出所有薪水在2000元至3000元之间的雇员的姓名、雇佣日期和薪水，并按雇佣日期的先后排列。查询语句如下：SELECT 姓名, 雇佣日期, 薪水FROM employeeWHERE 薪水 between 2000 and 3000ORDER BY 雇佣日期（六）按照任务6统计各种特长的雇员人数。查询语句如下：SELECT 特长，count(*) 人数FROM employeeGROUP BY 特长 执行结果如下：特长 人数 - - 唱歌 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 2000数据库中取出数据，并将数据通过一个或多个结果集返回给用户。（一）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子句：指定用于限制返回的行的搜索条件。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 表名或视图名 其中参数的含义如下：ALL：指定在结果集中可以显示重复行。ALL 是默认设置。DISTINCT：指定在结果集中只能显示惟一行，即表示输出无重复的所有记。TOP N PERCENT：指定只从查询结果集中输出前N行。如果还指定了 PERCENT，则只从结果集中输出前百分之 N 行。1查询所有的列 在SELECT 子句中，通配符“*”表示输出指定的表或视图中所有的列。【例 4.1】从companyinfo数据库的客户表(customer)中检索所有客户的所有信息。USE companyinfoGOSELECT *FROM customerGO 2查询特定的列【例 4.2】从companyinfo数据库的客户表(customer)中检索所有客户的公司名称、联系人姓名、地址。USE companyinfoGOSELECT 公司名称，联系人姓名，地址FROM customerGO 【例 4.3】从companyinfo数据库的雇员表（employee）中检索所有的雇员的姓名和特长。USE companyinfoGOSELECT 姓名，特长FROM employeeGO 3指定特定列的列名【例 4.4】查询每个人的姓名和薪水降低30%后的信息。有三种方法：（1）采用符合ANSI规则的标准方法，在列表达式后面给出列名。相应的命令如下：USE companyinfoGOSELECT 姓名, 薪水 原薪水 , 薪水-薪水*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)中，检索已经被订购的产品的产品名。USE companyinfoGOSELECT DISTINCT 产品名FROM p_orderGO 5使用TOP关键字 SELECT TOP N | TOP N PERCENT 列名1,列名2,列名N FROM 表名其中参数如下；TOP N：表示返回最前面的N行，N表示返回的行数。TOP N PERCENT：表示返回的前面的N%行。【例 4.6】查询companyinfo数据库的订单表(p_order)中最前面的10行纪录。USE companyinfoGOSELECT top 10 *FROM p_orderGO 【例 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_employee的新表。USE companyinfoGOSELECT 姓名, 薪水 INTO new_employeeFROM employeeGO（三） WHERE子句使用WHERE子句的目的是为了从表格的数据集中过滤出符合条件的行。使用WHERE子句可以限制查询的范围，提高查询效率。语法格式如下：SELECT 列名1 , 列名2, 列名NFROM 表名WHERE 搜索条件 1使用算术表达式 【例 4.10】 查询companyinfo数据库的雇员表（employee）中，特长是计算机的雇员的信息。USE companyinfoGO SELECT *FROM employee WHERE 特长=计算机GO【例 4.11】 在companyinfo数据库的雇员表（employee）中，查询薪水超过3000元的雇员的姓名和薪水。USE companyinfoGO SELECT 姓名，薪水FROM employee WHERE 薪水=3000GO2使用逻辑表达式在Transact-SQL语句中，常用的逻辑运算符分别是：NOT：非运算，对表达式的否定。l AND：与运算，连接多个条件，所有的条件都成立时为真。l OR： 或运算，连接多个条件，只要有一个条件成立就为真。【例 4.12】在companyinfo数据库的雇员表（employee）中，查询特长为书法或钢琴的所有雇员的雇员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元至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 ,表达式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】查询雇员表（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 ,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子句中的关系按分组属性划分为若干组，同一组内所有的记录在分组属性上是相同的。其语法格式如下：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 compamyinfo 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 companyinfoGOSELECT 类别ID , avg(单价) 平均价格FROM ProductGROUP By l 类别IDHAVING avg(单价)10GO （七） COMPUTE子句在SELECT 语句中，使用COMPUTE 子句，表示既显示查看结果的明细行，又显示汇总行，即使用COMPUTE子句将产生额外的汇总行，可以计算分组的汇总值，也可以计算整个结果集的汇总值。语法格式：COMPUTE 聚合函数名 (列名) , N BY expression , N 其中，聚合函数见表4-5。【例 4.23】查找雇员表（empoyee）中各雇员的姓名、出生年月和特长，并产生一个雇员总人数行。USE companyinfoGOSELECT 姓名，出生年月，特长FROM employeeCOMPUTE count(雇员ID 模块2 公司管理数据库系统的多表查询一、教学目标使用联接查询和嵌套查询的方法实现companyinfo数据库系统的多表查询。二、工作任务公司将与产品订单有关的数据保存在订单表（p_order）中，此表中共包含了6个字段，分别是：订单ID、产品ID、数量、雇员ID、客户ID和订货日期；在产品表（product）中包含了产品ID、产品名、类别ID、单价、库存量5个字段；在顾客表（customer）中包含了客户ID、公司名称、联系人姓名、联系方式、地址和邮编6个字段；在类别表（category）中包含了类别ID、类别名和说明3个，且每个表中都包含了相应的记录。请利用Transact-SQL的SELECT语句实现下列查询操作。 1 查询已订购了产品的公司的公司名称，联系人姓名和所订产品的产品名称和数量。2 查询所有订购了鼠标产品的公司的公司名称和联系方式。3 查询客户名为通恒机械的公司所订购产品的产品名和数量。4 查询鼠标所属的类别名和相应的说明。三、相关实践知识点（一） 按照任务1查询已订购了产品的公司的公司名称，联系人姓名和所订产品的产品名称和数量。操作步骤与简单查询相同，首先连接服务器，然后连接companyinfo数据库，接着在查询分析器输入查询语句，最后执行语句。在此模块中，不再详细介绍。此任务的查询语句如下：SELECT 公司名称，联系人姓名，产品名，数量FROM customer INNER JOIN p_order ON customer.客户ID=p_order.客户ID通过客户ID将客户表customer与订单表p_order进行内连接，这样可以同时从两个表中获取信息。表联接条件经常使用“主键=外键”的形式。 （二）按照任务2查询所有订购了鼠标产品的公司的公司名称和联系方式。由于鼠标为产品的名称，顾客的名称与联系方式分别属于不同的表，因而需使用多张进行查询。查询语句如下：SELECT 公司名称,联系方式FROM customerWHERE 客户ID=(SELECT 客户ID FROM p_order WHERE 产品ID=(SELECT 产品ID FROM product WHERE 产品名=鼠标 ) )首先在产品表中查询鼠标的产品ID号，然后根据产品ID号，在订单表p_order表将订购鼠标的客户ID查出，最后根据客户ID在客户表customer中将公司的名称和联系方式查出。（三）任务3查询客户名为通恒机械的公司所订购产品的产品ID和数量。 查询语句如下：SELECT 公司名称，产品ID，数量FROM customer 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 产品名=鼠标)四、相关知识点（一）用联接进行多表查询通过联接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接将定义SQL Server 2000如何使用一个表中的记录来选择相关联数据表中的记录。联接条件通过以下方法定义两个表在查询中的关联方式： 指定每个表中要用于联接的字段。典型的联接条件在一个表中指定外键，在另一个表中指定与其关联的键。指定比较各字段的值时要使用逻辑运算符（=、 等）。联接查询包括内联接、外联接和交叉联接 。1 内联接内联接也叫自然联接，它是联接两个表的常用方法。内联接通过使用比较运算符，根据需要联接的数据表中公共的字段值来匹配两个表中的记录，将两个表中满足联接条件的记录组合起来作为结果。内联接有两种形式的语法结构：（1）SELECT FROM 表1 inner JOIN 表2ON 表1.列=表2.列 （2）SELECT FROM 表1,表2WHERE表1.列=表2.列2外联接 在自然联接中，只有在两个表中匹配的记录才能在结果集中出现。而在外联接中可以只限制一个表，而对另外一个表不加限制（即所有的行都出现在结果集中）。外联接分为左外联接、右外联接和全外联接。左外联接是对联接条件中左边的表不加限制；右外联接是对联接条件中右边的表不加限制；全外联接对两个表都不加限制，所有两个表中的记录都会包括在结果集中。（1）左外联接的语法为：SELECT FROM 表1 LEFT OUTER 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包括所有联接表中的所有记录，不论它们是否匹配。（二）用嵌套查询的方式实现多表查询在实际应用中，经常要用到多层查询。在SQL Server 2000中，将一条SELECT语句作为另一条SELECT语句的一部分称为嵌套查询。外层的SELECT语句被称为外部查询或父查询，内层的SELECT语句成为内部查询或子查询。Transact-SQL语言允许多层嵌套，但是子查询语句中不允许出现ORDER BY子句，ORDER BY 子句永远只能对最终查询结果排序。1 带有比较运算符的子查询在该方式下，通过子查询返回一个单一的数据，该数据可以参加相关表达式的运算。当子查询返回的是单值时，可以使用，=，=，!=或等比较运算符。【例 4.24】查询东南实业所订购的产品的订单号和订购数量。分析：查询东南实业的订单号和