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

1、情境四公司管理数据库系统的查询模块1公司管理数据库系统的简单查询一、教学目标.准确理解TiansacrSQL充询语句的基本结构.能根据需要灵活使用查询语句的各r句二、工作任务公司所雇员的信息都存储在 com panyhfo数据库的empbyee表中，该表中包含了麻 员的雇员D、姓名、性别、出生年月、雇佣口期、特长、薪水7个字段和相应的记录，请利用TmnsacrSQL的SELEC T语句实现下列查询操作。.杳询所有雇员的信息，要求输出的结果格式如表4-1所示.衣4-1雇员的信息我姓名性别出生年月雇佣日期特长薪水2,查询所有男雇员的信息。.按照如我4N所示的格式，列出所有雇员的姓名和薪水.表4吃雇

2、员的姓名和薪水表雇员的姓名雇员的酬金.查询所11薪水超过 3000元的雇员的信息.5,查询所有薪水在2000元至3000元之间的雇员的姓名和雇佣I期，并按展例口期的先后排列。6,统计各种特长的雇员人数。.查询所Tf姓章的雇员的信息.三、相关实践知识（-）按照任务1的要求查询公司所有雇员的信息.操作步骤如下：.连接com panyhfe数据库，有以卜,两种方法：从“查询分析器”窗口的工具栏的下拉列表中选中公司管理数据库com panyhfo .在查询分析器窗口中输入如下命令，并运行.USE com panyhfo在查询分析器窗口中输入以下查询语句SELECT姓名，性别，出生年月，雇佣日期，特长，

3、薪水FROM em pbyeeGO2。执行查询语句方法。（二）按照任务2查询所有男雇员的信息。查询语句如下：SELECT *FROM em pbyeeW HERE性别=男（三）按照任务3规定的格式，列出所有雇员的姓名和薪水。为了使查询的结果更加友好，可以改变列的标题.即指定列的别名em pbyee表中有雇员的“姓幺”和“薪水”字段,何仟务要求示结构为“ 和“雇员的酎金”，可以用AS引导给出列的别名.查询语句如下：SELECT姓名AS雇员的姓名，薪水AS雇员的酬金FROM em pbyee（四）按照任务4列出所有薪水超过的 3000元的雇员的信息。查询语句如下：SELECT FROM em pb

4、yeeW HERE 薪水 =3000展佣H期和薪水,（五）按照任务5列出所有薪水在2000元至3000元之间的雇员的姓名、并按雇.佣日期的先后排列.查询语句如下：SELECT姓名，雇佣日期，薪水FRO M em pbyeeW HERE 薪水 between 2000 and 3000ORDER BY雇佣日期（六）按照任务6统计各种特长的雇员人数。查询语句如下：SELECT 特长，count） 人数FROM em pbyeeGROUP BY 特长执行结果如下：人数特长 TOC o 1-5 h z 唱歌2二胡2钢琴2古筝2会计1计算机8书法4跳舞2武术1演讲1音乐2游泳1（所影响的行数为12行）（

5、七）列出所有姓章的雇员的信息。查询语句如下：SELECT *FROM em pbyeeW HERE 姓名 LIKE 章四、相关知识使用数据库和数据表的主要目的是存储数据，以便在需要时进行检索、 统计或组织输出，通过TmnsactSQL的SELECT语句可以从表或视图中迅速、方便地检索数据。在众多的 TmnsacrSQ L语句中.SELECT语句是使用频率最高的一个。杳询的最基本方式是使用SELECT语句，按照用户给定的条件从 SQLSeIVer2000数据库中取出数据，并将数据通过一个或多个结果集返回给用户。（ ） SELECT语句结构SELECT语句的主要的子句可归纳如下：SELECT AL

6、L DBTNCT目标表达式 目标表达式Into from（表名或视图名 w HERE条件表达式group by列名 1Having 1ORDER BY ASC |DESC 其中，包含子句SELECT , NT0 , FROM . W HERE . GROUP BY t HAV3XG , ORDER BY 等，每个子句都有各自的用法和功能。SELECT子句：指定由查询返回的列INTO子句：将检索结果存储到新袤或视图中。FROM子句：用于指定引用的列所在的表和视图。W HERE子句：指定用于限制返回的行的搜索条件。GROUP BY子句：指定用来放置输出行的组，并且如果 SELECT子句SELECT

7、 LST）中 包含聚合函数，则计算每组的汇总值。HAV3XG子句：指定组或聚合的搜索条件。HAVIXG通常与GROUPBY子句一起使用。如果 不使用GROUP BY子句，HAVKG的行为与胃HERE子句一样。ORDER BY子句：指定结果集的排序。（-）SELECT 子句SELECT子句的功能，用干诋回指定列的数据集。SELECT语句的常须使用格式：SELECT ALL |DETKCT TOP N PERCENT 列名 列名 2,列名 NFROM袤名或视图名其中参数的含义如下：ALL ：指定在结果集中可以显示重复行。ALL是默认设苣。DBT1XCT ：指定在结果集中只能显示惟一行，即表示输出无

8、重复的所有记。TOPN BERCEXT：指定只从查询结果集中输出前N行。如果还指定了 PERCENT ,则只从结果集中输出前百分之 N行。.查询所有的列在SELECT子句中，通配符“ *”表示输出指定的表或视图中所有的列。【例4.1】从com Danvhft）数据库的客户表fcustom er）中检索所有客户的所有信息。USE com panyhfcGOSELECT *FROM custom erGO.查询特定的列【例4.2从com panyiifi）数据库的客户表tustom er）中检索所有客户的公司名称、联系人姓名、地址。USE com pan5rhfcGOSELECT公司名称，联系人姓

9、名，地址FROM custom erGO【例4.3 从com panyiifi）数据库的雇员表（empbyee ）中检索所有的雁员的姓名和特 长.USE com panyhfoGOSELECT姓名，特长FRO M em pbyeeGO.指定特定列的列名【例4.4 查询每个人的姓名和薪水降低30%后的信息。有三种方法：（1）采用符合ASI规则的标准方法，在列表达式后而给出列幺。相应的命令如下：USE com panyhfe GOSELECT姓名，薪水原薪水，薪水-薪水种.3现薪水FROM em pbyee GO（2）用“=”来连接列表达式.相应的命令如下：USE com panyhfe GOSE

10、LEC T姓名，原薪水=薪水，现薪水,=薪水-薪水枪.3FROM em pbyeeGO（3）用AS关键字来连接列表达式和指定的列名。相应的命令如下：USE com panyhfeGOSELECT姓名，薪水as原薪水，薪水-薪水杓.3 as 现薪水FROM em pbyee GO.删除重复的列【例4.5从com panyiifi）数据库的订单表 Voider）中，检索己经被订购的产品的产品名.USE com pan5rhfe GOFROM p-oiderGO.使用TOP关键字S ELECT TOP N |TOP N PERCENT列名 11列名 2,列名 NFROM 去名 其中参数如下；TOP

11、X ：表示返回最前面的N行， 表示返回的行数。TOP N PERCENT ：表示返回的前面的X%行。【例1.6】查询com panyhfe 数据库的订单表 Voider）中最前面的10行纪录oUSE com panyhfcGOSELECT top 10 *FRO M p.oiderGO【例4.7】查询com panyhft）数据库的订单表 crder）中的前面的10%记录。USE com panyhfeGOSELECT top 10 percent*FROM p.oider.使用计算列【例4.8 显示每种产品的价格降低30%的产品信息。USE com panyiifi)GOSELECT产品名，

12、库存量,单价，单价 -单价3FRO M productGO(-)KT0子句NT0子句用于创建新表并将查询的结果插入新表中，其语法如下：KT0 新表名【例4.9使用NT0子句创建一个包含em pbyee表中姓名和薪水字段，且名为new_em pbyee 的新表。USE com panyhfi) GOINTO new_em pbyeeFROM em pbyeeGO(三)w HERE子句使用W HERE子句的目的是为了从表格的数据集中过渡出符合条件的行。使用W HERE子句可以限制查询的范围，提高查询效率。语法格式如下：SELECT列名列名2,列名节FROM 表名W HERE 搜索条件1 .使用算术

13、表达式【例4.10】查询com panyhfi)数据库的雇员表(em pbyee )中，特长是计算机的雇员的信息。USE com panyhfc GOSELECT *FROM em pbyeew HERE特长=计算机 GO【例4,11 在com panyhfo 数据库的雇员表(em pbyee )中,查询薪水超过 3000元 的雇员的姓名和薪水。USE com panyhfcGOSELECT姓名，薪水FROM em pbyeeV HERE 薪水 =3000 GO2.使用逻辑表达式在TmnsactSQL语句中，常用的逻辑运算符分别是：NOT:非运算，对表达式的否定。AXD ：与运算，连接多个条件

14、，所有的条件都成立时为真。OR ：或运算，连接多个条件，只要有一个条件成立就为真。【例4.12 在 com panyhfo 数据库的雇员表(em pbyee )中,查询特长为书法或钢琴的所有雇员的雇员 D、姓名和特长。USE com panyhfeGOSELECT雇员D ,姓名，特长FROM em pbyeew HERE特长=书法or 特长=钢琴GO查询结果如下：雇员D姓名特长2李立三书法10姜玲娜书法12金林皎书法15刘启芬钢琴22钱其娜书法26欧阳天民钢客.使用范围表达式使用BETW EEN关键字可以更方便地表示查询数据的范围。语法格式为：表达式NOTlBETW EEN 表达式1 AND

15、表达式2【例4.13】在companyhfi)数据库的雇员表(em pbyee )中，查询薪水在 3000元至 4000元的雇员的姓名和薪水。SELECT姓名，薪水FRO M em pbyeeW HERE 薪水 between 3000 and 4000【例4.14】查询库存量大于200或小于100的产品的产品名、库存量和单价。SELECT 产品名，库存量，单价FROM productW HERE 库存量 notbeween 200 and 100.使用N关键字同BETW EEN关键字一样，IN的引入也是为了更方便地限制检索数据的范围，灵活使用 K关键字，可以用简洁的语句实现结构复杂的查询。语

16、法格式为：表达式N0TN( 表达式1,表达式2表达式ND【例4.15】在雇员表（empbyee ）中，查询所有特长为计算机、钢琴、书法的雇员的雇员D、姓名、特长。USE com panyhfeGOSELECT雇员3D ,姓名，特长FROM em pbyeew HERE特长力（ 计算机，钢琴，书法）GO.通配符的使用 TOC o 1-5 h z LKE子句在大多数情况下会与通配符配合使用。通配符的含义见教材表4-4 o【例4.16】在薜员表（em pbyee ）中，杳询所有姓名中含有“禾十字的雇员的雇员D和姓名。SELECT雇员D ,姓名FROM em pbyeeW HERE 姓名 LIKE %

17、利GO【例4.17 查询雇员表（em pbyee ）中所有雇员 D满足第2个字符为“ 1”的雇员的 姓名和出生年月。SELECT雇员D ,姓名，出生年月FROM em pbyeeW HERE 雇员 D LIKE GO.使用NULL关键字在W HERE子句中不能使用比较运算符对空值进行判断，只能使用空值表达式来判断某个表达式是否为空值。如下所示：表达式E NULL或表达式E NOT NULL（四）ORDER BY子句应用程序中经常需要对检索得到的数据集进行排序。可以利用ORDERBY子句实现。 语ORDER BY 表达式 1 ASC |DESC，去达式 2 ASC |DESC 7【例4.18】在

18、companyii加数据库的订单表g_order）中，查询产品名，数量和订货日期， 并按的订货日期的降序显示。USE com panyhfeGOSELECT产品名，数量，订货日期FROM phonierORDER BY订货日期由ESCGO说明：（1）如果在SELECT中同时指定/ TOP,则ORDERBY无效。（2）空值被视为最低的值.（五）GROUP BY子句使用GROUP BY夕句可以对数据按照某列进行分组。 GROUP BY子句的作用是把 FROM f 句中峋关系按分组属性划分为若干组.同一组内所有的记录在分组属性上是相同的.其语法格式如下：&ROUPBY 分组表达式1【例4.19】统计

19、订单表 G_ordeB中所有产品的已订购总额。 USE com panytiB GOSELECT sum （数量）FROM p_order【例4.20 在订单式 g_order） 中，杳询每类产品（按产品 。分类）的订购总和。 USE com pam yiift） SELECT产品ID,产品名，sum （数量）as总数量FROM p_oider GROUP BY 产品 【例4.21】在订单表3_ordei） 中，按“产品 D ”分类，求出各类产品的价格总和、 平均价格及各类产品的数量.USE com pam yhfe GOSELEC T产品JD , sum （单价）,价格总和avg （单价）平

20、均单价count的FROM p.oiderGROUP BY 产品 D GO（六）HAVING子句HAVKG通常与GROUP BY子句一起使用，用于指定组或聚合的搜索条件。语法格式如T：HAVING搜索条件当HAVRG与GROUP BY ALL 一起使用时，HAVING子句替代ALL在HAVNG子句中不 能使用TEXT、WAGE和TEXT数据类型。【例4.22 1查询平均价格超过10元的产品的种类。USE com pan3FhfeGOSELECT 类别D ,avg（单价），平均价格FROM ProductGROUP By 1 类别 DHAVKG avg（单价）ioGO（七）COMPUTE子句在S

21、ELECT语句中，使用COMPUTE子句，表示既显示查看结果的明细行，又显示汇总行， 即使用COMPUTE子句将产生额外的汇总行，可以计算分组的汇总值，也可以计算整个结果集的汇 总值。语法格式：Compute 保合函数名（列名）-n JIby expressbn -n其中，聚合函数见表 4-0 o【例4.23】查找雇员表（empoyee ）中各雇员的姓名、出生年月和特长，并产生一个雇 员总人数行。USE com panyhfeGOSELECT姓名,出生年月.特长FROM em pbyeeCOMPUTE count罐员 D模块2公司管理数据库系统的多表查询一、教学目标使用联接查询和嵌套查询的方法

22、实现com panyhfc数据座系统的多表查询。二、工作任务公司将与产品订单有关的数据保存在订单衣（p.order ）中，此表中共包含了 6个字段，分别是：订单。、产品1D、数量、雇员。、客户D和订货日期；在产品表（product ）中包含了产 品1D、产品名、类别1D、单价、库存量5个字段；在原客表（custom er ）中包含了客户ID、公司名 称、联系人姓名、联系方式、地址和邮编6个字段；在类别表（categoiy ）中包含了类别D、类别名和说明3个，且每个表中都包含了相应的记录。 请利用TransactSQL 的SELECT语句实现下列查询操作。.查询已订购了产品的公司的公司名称，联系

23、人姓名和所订产品的产品名称和数量。.查询所ff订购了 鼠标产品的公司的公司名称和联系方式.查询客户名为通恒机械的公司所订购产品的产品名和数量。.查询鼠标所属的类别名和相应的说明。三、相关实践知识点（-） 按照任务1兖询已订购了产品的公司的公司名称，联系人姓名和所订产品的产品名称和数量.操作步骤与前单直询相同， 首先连接服务器, 然后连接com panyhfe数据库，接着在查 询分析霜输入查询语句.最后执行语句.在此模块中,不再详细介绍.此任务的查询语句如下：SELECT公司名称，联系人姓名，产品名，数量FROM custom er INNER JO JX p_oiderON custom er

24、 客户 D =p_oidei: 客户 D通过客户I）将客户表cusiomer与订单次p_onier进行内连接，这样可以同时从两个代 中获取信息.表联接条件经常使用“主犍=外键”的形式。（二）按照任务2查询所有订购了 鼠标产品的公司的公司名称和联系方式。由于鼠标为产品的幺称,顾客的名称与联系方式分别属于不同的表，因而需使用多张进行杳询.查询语句如下：SELECT公司名称，联系方式FROM custom erW HERE 客户 D = （SELECT 客户 IDFRO M p_oiderW HERE产品 D = 6ELECT 产品 IDFROM productW HERE产品名=鼠标）首先在产品表

25、中查询鼠标的产品D号，然后根据产品 D号，在订单.表p_oider表将订购鼠标的客户1D直出，最后根据客户3D在客户表cussm er中将公司的名称和联系方式套出.（三）任务3查询客户名为通恒机械的公司所订购产品的产品TD和数量。查询语句如下：SELECT公司名称，产品D ,数量FROM custom eras A KNER JON p_oideras BON A.客户D=B.客户Bw HERE A公.司名称=通恒机械为了便于操作，将客户表（custom er ）定义别名A,订单表（phonier ）定义别名B,所以在0广句和W HERE f句方便的书写为A.客户D和B.客户0 。（四）任务4

26、杳询鼠标所属的类别名和相应的说明。查询语句如下：SELECT类别名，说明FROM categoryW HERE 类别 D = （SELECT 类别 IDFRO M productw HERE产品名=鼠标*）四、相关知识点-）用联接进行多表查询通过联接，可以根据各个女之间的逻辑关系从两个或多个衣中检索数据.联接将定义 SQLS eiver2000如何使用一个表中的记录来选择相关联数据表中的记录.联接条件通过以下方法定义两个表在查彻中的关联方式：指定每个表中要用于联接的字段。典型的联接条件在一个表中指定外健，在另一个式中指定与其关联的键。指定比较各字段的值时要使用逻辑运算符（=、 等）。联接查询包

27、括内联接、外联接和交叉联接.内联接内联接也叫自然联接，它是联接两个表的常用方法。内联接通过使用比较运算符，根据 需要联接的数据表中公共的字段值来匹配两个表中的记录，将两个表中满足联接条件的记象组合起来作为结果.内联接有两种形式的语法结构：SELECT FROM 表 1 1nerOjON 表 2。表1.歹上表2.列SELECT FROM 1 LEFT ）UTERJOK 表 2 表ON表1.列1 =表2.列2包括第个命名表（“左”表,出现在 JOIN子句的最左边）中的所有行。不包拈右 表中的不匹配行.2）右外联接的语法为：SELECT选择列表FROM 表 1 REHT g）UTERjON 表 2O

28、N & 1.列 1=衣2.列 2包括第二个命名衣（“右”衣，出现在 JON 子句的最右边）中的所有行.不包括左表 中的不匹配行.3）全外联接的语法为:SELECT选择列表FROM & 1 FULL 0UTERJON 表 2ON表1.歹ij 1=表2.列2包括所有联接表中的所仃记录，不论它们是否匹配。（二）用嵌套住询的方式实现多次查询在实际应用中,经常婴用到多层查询.在SQL Seiver2000中.将条SELECT语句作为另- 条SELECT语句的部分称为嵌套查询。外层的SELECT语句被称为外部查询或父查询，内层的 SELECT语句成为内部查询或夕查询。TmnsactSQL语言允许多层嵌套,但是广查询语句中不允许 出现ORDER BY /句，ORDER BY /句永远只能对最终杳询结果排序。.带有比较运算符的f查询在该方式下，通过/查询返回个单的数据，该数据可以参加相关表达式的运算。当子查询返回的是单值时，可以使用，=, =.匕或。等比较运算符。【例4.24 查询东南实业所订购的产品的订单号和订购数量。分析：查询东南实业的订单号和订购数量，可以首先由公司名称=东南实业在custm er衣