项目四公司数据库查询系统.docx

项目四企业管理数据库系统的数据查询一、教学目的终极目的：能根据需要灵活、迅速地查询企业管理系统数据库的数据促成目的：1 .会在一种数据表中实现数据的简朴查询2 .会同步在多种数据表中实现数据的兔合查询3 .会实现分组查询4 .会对查询成果集排序5 .会运用库函数进行数据记录二、工作任务根据需要灵活迅速地查询企业管理数据库系统的数据模块1企业管理数据库系统的简朴查询一、教学目的1 .精确理解Transact-SQL查询语句的基本构造2 .能根据需要灵活使用查询语句的各子句二、工作任务企业所有雇员的信息都存储在companyinfo数据库的employee表中，该表中包括了雇员的雇员ID、姓名、性别、出生年月、雇佣日期、专长、薪水7个字段和对应的记录，请运用Transact-SQL的SELECT语句实现下列查询操作。1 .查询所有雇员的I信息，规定输出的成果格式如表4T所示。表47雇员的信息表姓名性别出生年月雇佣日期专长薪水2 .查询所有男'雇员的信息。3 .按照如表4-2所示的格式，列出所有雇员的姓名和薪水。表4-2雇员的姓名和薪水表雇员的姓名雇员的酬金4 .查询所有薪水超过3000元的雇员的信息。5 .查询所有薪水在2023元至3000元之间的雇员的姓名和雇佣日期，并按雇佣日期的先后排列。6 .记录多种专长的雇员人数。7 .查询所有姓章'的雇员的信息。三、有关实践知识(一)按照任务1的规定查询企业所有雇员的信息。操作环节如下：1 .连接COmPanyinfo数据库，有如下两种措施：从“查询分析器”窗口的工具栏的下拉列表中选中企业管理数据库CompanyinfOo在查询分析器窗口中输入如下命令，并运行。USEcompanyinfo在查询分析器窗口中输入如下查询语句SELECT姓名，性别，出生年月，雇佣日期，专长，薪水FROMemployeeGO2。执行查询语句措施。（二）按照任务2查询所有'男'雇员的信息。查询语句如下：SELECT*FROUemployeeWHERE性别='男'（三）按照任务3规定的格式，列出所有雇员的姓名和薪水。为了使查询的成果愈加友好，可以变化列的标题，即指定列的别名。己知在employee表中有雇员的“姓名”和“薪水”字段，但任务规定显示构造为“雇员的姓名”和“雇员H勺酎金”，可以用AS引导给出列的I别名。查询语句如下：SELECT姓名AS雇员的姓名，薪水AS雇员的酬金FROMemployee（四）按照任务4列出所有薪水超过H勺3000元H勺雇员的信息。查询语句如下：SELECT*FROMemployeeWHERE薪水=3000（五）按照任务5列出所有薪水在2023元至3000元之间的雇员的姓名、雇佣日期和薪水，并按雇佣日期的先后排列。查询语句如下：SELECT姓名，雇佣日期，薪水FROMemployeeWHERE薪水between2023and3000ORDERBY雇佣日期（六）按照任务6记录多种专长的雇员人数。查询语句如下：SELECT专长，count（*）人数FROMemployeeGROUPBY专长执行成果如下：专长人数唱歌2二胡2钢琴2古筝2会计1计算机8书法4跳舞2武术1演讲1音乐2游泳1（所影响口勺行数为12行）（七）列出所有姓章'的雇员的信息。查询语句如下：SELECT*FROMemployeeWHERE姓名LIKE'章%'四、有关知识使用数据库和数据表的重要目的是存储数据，以便在需要时进行检索、记录或组织输出，通过TranSaCt-SQL的SELECT语句可以从表或视图中迅速、以便地检索数据。在众多的TranSaCt-SQL语句中，SELECT语句是使用频率最高的一种。查询的最基本方式是使用SELECT语句,按照顾客给定H勺条件从SQLServer2023数据库中取出数据，并将数据通过一种或多种成果集返回给顾客。（一）SELECT语句构造SELECT语句的重要Fl勺子句可归纳如下：SELECTALLDISTINCT目的体现式L目的体现式INTO新表名FROM表名或视图名,表名或视图名WHERE条件体现式GROUPBY列名1HAVING体现式ORDERBY列名2ASCDESC其中,包括子句SELECT,INTO,FROM,WHERE,GROUPBY,HAVING,ORDERBY等，每个子句均有各自的使用措施和功能。 SELEeT子句：指定由查询返回的列。 INTO子句：将检索成果存储到新表或视图中。 FROM子句：用于指定引用的列所在附表和视图。 WHERE子句：指定用于限制返回的行的搜索条件。 GROUPBY子句：指定用来放置输出行的组，并且假如SELECT子句SELECTLIST中包括聚合函数，则计算每组的汇总值。 HAVlNG子句：指定组或聚合的搜索条件。HAVING一般与GROUPBY子句一起使用。假如不使用GROUPBY子句，HAVING的行为与WHERE子句同样。 ORDERBY子句：指定成果集的排序。SELECT子句的功能，用于返回指定列的数据集。SELECT语句的常规使用格式：SELECTALLDISTINCTTOPNPERCENT列名IL列名2,列名NFROM表名或视图名其中参数的含义如下： ALL：指定在成果集中可以显示反复行。ALL是默认设置。 DISTINCT：指定在成果集中只能显示惟一行，即体现输出无反复的所有记。 TOPNPERCENT：指定只从查询成果集中输出前N行。假如还指定了PERCENT,则只从成果集中输出前百分之N行。1 .查询所有的列在SELECT子句中，通配符体现输出指定的表或视图中所有的列。【例4.1从COmPanyinfO数据库的客户表(CUStOmer)中检索所有客户的所有信息。USEcompanyinfoGOSELECT*FROMcustomerGO2 .查询特定的列【例4.2从ComPanyinfo数据库B客户表(CUStonler)中检索所有客户0¾企业名称、联络人姓名、地址。USEcompanyinfoGOSELECT企业名称，联络人姓名，地址FROMcustomerGO【例4.3从COmPanyinfo数据库B¾雇员表(employee)中检索所有的雇员的姓名和专长。USEcompanyinfoGOSELECT姓名，专长FROMemployeeGO3 .指定特定列的列名【例4.4查询每个人的姓名和薪水减少30%后的信息。有三种措施：(1)采用符合ANSl规则的原则措施，在列体现式背面给出列名。对应的命令如下:USEcompanyinfoGOSELECT姓名，薪水原薪水，薪水-薪水*0.3'现薪水'FROMemployeeGO(2)用“=”来连接列体现式。对应的命令如下:USEcompanyinfoGOSELECT姓名，原薪水'=薪水，现薪水'二薪水-薪水*0.3FROMemployeeGO(3)用AS关键字来连接列体现式和指定的列名。对应的命令如下：USEcompanyinfoGOSELECT姓名，薪水as'原薪水，薪水-薪水*0.3as'现薪水'FROMemployeeGO4 .删除反复的列【例4.5从COlnPanyinfo数据库0¾订单表(p_order)中，检索已经被订购的产品0¾产品名。USEcompanyinfoGOSELECTDISTINCT产品名FROMp_orderGO5 .使用TOP关键字SELECTTOPNITOPNPERCENT列名IL列名2,列名NFROM表名其中参数如下； TOPN：体现返回最前面0N行，N体现返回的行数。 TOPNPERCENT：体现返回的前面的N%行。【例4.6查询COmPanyinfo数据库的订单表(p_order)中最前面B¾10行纪录。USEcompanyinfoGOSELECTtop10*FROMporderGO【例4.7查询COlDPanyinfo数据库的订单表(p_order)中的前面的10%记录。USEcompanyinfoGOSELECTtop10percent*FROMporder6.使用计算列【例4.81显示每种产品的价格减少3O%0¾产品信息。USEcompany!nfoGOSELECT产品名，库存量，单价，单价-单价*0.3EROMproductGO(三)INTo子句INTO子句用于创立新表并将查询的成果插入新表中，其语法如下：INTO新表名【例4.9使用INTo子句创立一种包括employee表中姓名和薪水字段，且名为newemployee!勺新表。USEcompanyinfoGOSELECT姓名，薪水INTOnew_empIoyeeFROMemployeeGO(四)WHERE子句使用WHERE子句的目的是为了从表格的数据集中过滤出符合条件的J行。使用WHERE子句可以限制查询的范围，提高查询效率。语法格式如下：SELECT列名1,列名2,列名NFROM表名WHERE搜索条件1 .使用算术体现式【例4.10查询COnIPanyinfo数据库的雇员表(employee)中，专长是计算机的雇员的信息。USEcompanyinfoGOSELECT*FROMemployeeWHERE专长='计算机GO【例4.11在ComPanyinfO数据库的雇员表(employee)中，查询薪水超过3000元的雇员的姓名和薪水。USEcompanyinfoGOSELECT姓名，薪水FROMemployeeWHERE薪水>=3000GO2 .使用逻辑体现式在TranSaCt-SQL语句中，常用的逻辑运算符分别是: NOT：非运算，对体现式H勺否认。 AND：与运算，连接多种条件，所有的I条件都成立时为真。 OR：或运算，连接多种条件，只要有一种条件成立就为真。【例4.12在companyinfo数据库的雇员表(employee)中，查询专长为书法'或'钢琴'的所有雇员的雇员ID、姓名和专长。USEcompanyinfoGOSELECT雇员ID,姓名，专长FROMemployeeWHERE专长='书法'or专长='钢琴GO查询成果如下：雇员ID姓名专长2李立三书法10姜玲娜书法12金林皎书法15刘启芬钢琴22钱其娜书法26欧阳天民钢琴3 .使用范围体现式使用BETWEEN关键字可以更以便地体现查询数据的范围。语法格式为：体现式NOTBETWEEN体现式1AND体现式2【例4.13在COinPanyinfO数据库的雇员表(employee),查询薪水在3000元至4000元的雇员的姓名和薪水。SELECT姓名，薪水FROMemployeeWHERE薪水between3000and4000【例4.14查询库存量不不大于200或不不不大于100的产品的产品名、库存量和单价。SELECT产品名，库存量，单价FROMproductWHERE库存量notbetween200and1004 .使用IN关键字同BETWEEN关键字同样，IN的引入也是为了更以便地限制检索数据的范围，灵活使用IN关键字，可以用简洁的语句实现构造复杂的查询。语法格式为：体现式NOTIN(体现式1,体现式2,体现式N)【例4.15】在雇员表（employee）中，查询所有专长为'计算机、钢琴、'书法的雇员的雇员ID、姓名、专长。USEcompanyinfoGOSELECT雇员ID,姓名，专长FROMemployeeWHERE专长in（'计算机'，'钢琴'，'书法'）GO.通配符的使用1.IKE子句在大多数状况下会与通配符配合使用。通配符的含义见教材表4-4o【例4.16在雇员表（employee）中，查询所有姓名中具有“利”字的雇员的雇员ID和姓名。SELECT雇员TD,姓名FROMemployeeWHERE姓名LIKE飞利为'GO【例4.17】查询雇员表（employee）中所有雇员ID满足第2个字符为“1”的雇员的姓名和出生年月。SELECT雇员ID,姓名，出生年月FROMemployeeWHERE雇员IDLIKE'%'GO5 .使用NULL关键字在WHERE子句中不能使用比较运算符对空值进行判断，只能使用空值体现式来判断某个体现式与否为空值。如下所示：体现式ISNULL或体现式ISNOTNULL(五)ORDERBY子句应用程序中常常需要对检索得到的数据集进行排序。可以运用ORDERBY子句实现。语法格式为：ORDERBY体现式1ASCDESC,体现式2ASCDESC,-N【例4.18】在COmPanyinfO数据库的I订单表(p_order)中，查询产品名，数量和订货日期，并按时订货日期H勺降序显示。USEcompanyinfoGOSELECT产品名，数量，订货日期FROMporderORDERBY订货日期/DESCGO阐明：（1）假如在SELEeT中同步指定了ToP,则ORDERBY无效。（2）空值被视为最低的值。（六）GROUPBY子句使用GROUPBY子句可以对数据按照某列进行分组。GROUPBY子句的作用是把FROM子句中的关系按分组属性划分为若干组，同一组内所有的记录在分组属性上是相似的。其语法格式如下：GROUPBY分组体现式【例4.19记录订单表（prder）中所有产品的已订购总额。USEcompanyinfoGOSELECTsum（数量）FROMporder【例4.20在订单表（p_order）中，查询每类产品（按产品ID分类）的订购总和。USECompamyinfoSELECT产品ID,产品名，SUn1（数量）as总数量FROMporderGROUPBY产品ID【例4.21在订单表（prder）中，按“产品ID”分类，求出各类产品的价格总和、平均价格及各类产品的数量。USECompamyinfoGOSELECT产品ID,SUn（单价）价格总和，avg（单价）平均单价，count（*）FROMporderGROUPBY产品IDGO（七）HAVING子句HAVING一般与GROUPBY子句一起使用，用于指定组或聚合的!搜索条件。语法格式如下：HAVING搜索条件当HAVING与GROUPBYALL一起使用时，HAVING子句替代ALLo在HAVING子句中不能使用TEXT、IMAGE和NTEXT数据类型。【例4.22查询平均价格超过10元於J产品的种类。USEcompanyinfoGOSELECT类别ID,avg（单价）平均价格FROMProductGROUPBy1类别IDHAVINGavg（单价）10GO（八）COMPUTE子句在SELECT语句中，使用COMPUTE子句，体现既显示查当作果的明细行，又显示汇总行，虽然用COMPUTE子句将产生额外的汇总行，可以计算分组的!汇总值，也可以计算整个成果集的汇总值。语法格式：COMPUTE聚合函数名（列名）,NBYexpression,N其中，聚合函数见表4-5。【例4.23】查找雇员表（empoyee）中各雇员的姓名、出生年月和专长，并产生一种雇员总人数行。USEcompanyinfoGOSELECT姓名，出生年月，专长FROMemployeeCOMPUTECoUnt（雇员ID模块2企业管理数据库系统的多表查询一、教学目的使用联接查询和嵌套查询的措施实现COmpanyinfO数据库系统的多表查询。二、工作任务企业将与产品订单有关的数据保留在订单表（prder）中，此表中共包括了6个字段，分别是：订单ID、产品ID、数量、雇员ID、客户ID和订货日期；在产品表（PrOdUCt）中包括了产品ID、产品名、类别ID、单价、库存量5个字段；在顾客表(customer)中包括了客户ID、企业名称、联络人姓名、联络方式、地址和6个字段；在类别表(category)中包括了类别ID、类别名和阐明3个，且每个表中都包括了对应的记录。请运用Transact-SQL口勺SELECT语句实现下列查询操作。1 .查询已订购了产品的企业的企业名称，联络人姓名和所订产品的产品名称和数量。2 .查询所有订购了鼠标'产品的企业的企业名称和联络方式。3 .查询客户名为通恒机械'的企业所订购产品的产品名和数量。4 .查询鼠标'所属的类别名和对应的阐明。三、有关实践知识点(一)按照任务1查询已订购了产品的企业的企业名称，联络人姓名和所订产品的产品名称和数量。操作环节与简朴查询相似，首先连接服务器，然后连接ConIPanyinf。数据库，接着在查询分析器输入查询语句，最终执行语句。在此模块中，不再详细简介。此任务的查询语句如下：SELECT企业名称，联络人姓名，产品名，数量FROMcustomerINNERJOINporderONcustomer.客户ID=Porder.客户ID通过客户ID将客户表CUStOlnCr与订单表p_order进行内连接，这样可以同步从两个表中获取信息。表联接条件常常使用“主键=外键”/J形式。(二)按照任务2查询所有订购了鼠标'产品的企业的企业名称和联络方式。由于鼠标'为产品的名称，顾客的名称与联络方式分别属于不同样的表，因而需使用多张进行查询。查询语句如下：SELECT企业名称,联络方式FROMcustomerWHERE客户ID=（SELECT客户IDFROMporderWHERE产品TD=（SELECT产品IDFROMproductWHERE产品名='鼠标）首先在产品表中查询鼠标的产品ID号,然后根据产品ID号,在订单表p_ordcr表将订购鼠标'的客户ID查出，最终根据客户ID在客户表customer中将企业的名称和联络方式查出。（三）任务3查询客户名为通恒机械'的企业所订购产品的产品ID和数量。查询语句如下：SELECT企业名称，产品ID,数量FROMcustomerasAINNERJOINporderasBONA.客户ID=B.客户IDWHEREA.企业名称='通恒机械'为了便于操作,将客户表(customer)定义别名便订单表(p-order)定义别名B,因此在ON子句和WHERE子句以便的书写为A.客户ID和B.客户ID。(四)任务4查询鼠标'所属的类别名和对应的阐明。查询语句如下：SELECT类别名，阐明FROMcategoryWHERE类别ID=(SELECT类别IDFROMproductWHERE产品名='鼠标')四、有关知识点(一)用联接进行多表查询通过联接，可以根据各个表之间日勺逻辑关系从两个或多种表中检索数据。联接将定义SQLServer2023怎样使用一种表中H勺记录来选择有关联数据表中的记录。联接条件通过如下措施定义两个表在查询中的关联方式：指定每个表中要用于联接的字段。经典的联接条件在一种表中指定外键，在另一种表中指定与其关联的键。指定比较各字段的值时要使用逻辑运算符(=、等)。联接查询包括内联接、外联接和交叉联接。1 .内联接内联接也叫自然联接，它是联接两个表的常用措施。内联接通过使用比较运算符，根据需要联接的数据表中公共的字段值来匹配两个表中的记录，将两个表中满足联接条件的记录组合起来作为成果。内联接有两种形式的语法构造：(1) SELECT(选择列表FROM表1innerJoIN表2ON表1.列二表2.列(2) SELECT选择列名FROM表1,表2WHERE表1.列二表2.列2 .外联接在自然联接中，只有在两个表中匹配的记录才能在成果集中出现。而在外联接中可以只限制一种表，而对此外一种表不加限制(即所有的行都出目前成果集中)。外联接分为左外联接、右外联接和全外联接。左外联接是对联接条件中左边的表不加限制；右外联接是对联接条件中右边的表不加限制；全外联接对两个表都不加限制，所有两个表中的I记录都会包括在成果集中。(1)左外联接的语法为：SELECT选择列表FROM表1LEFTOUTERJOIN表2ON表1.歹IJ1二表2.歹IJ2包括第一种命名表(“左”表，出目前JOIN子句时最左边)中的所有行。不包括右表中的不匹配行。(2)右外联接的语法为:SELECT选择列表FROM表1RIGHTOUTERJOIN表2ON表1.歹Ul=表2.歹IJ2包括第二个命名表（“右”表，出目前JOIN子句的最右边）中的所有行。不包括左表中的不匹配行。（3）全外联接的语法为：SELECT选择列表>FROM表1FULLOUTERJOIN表2ON表1.歹IJl二表2.歹IJ2包括所有联接表中的所有记录，不管它们与否匹配。（二）用嵌套查询的方式实现多表查询在实际应用中，常常要用到多层查询。在SQLSerVer2023中，将一条SELECT语句作为另一条SELECT语句的一部分称为嵌套查询。外层的SELECT语句被称为外部查询或父查询，内层的SELECT语句成为内部查询或子查询。TranSaCt-SQL语言容许多层嵌套，不过子查询语句中不容许出现ORDERBY子句，ORDERBY子句永远只能对最终查询成果排序。1 .带有比较运算符的子查询在该方式下，通过子查询返回一种单一的数据，该数据可以参与有关体现式的运算。当子查询返回的是单值时，可以使用>,<,=,<=,>=,!=或等比较运算符。【例4.24查询东南实业'所订购的产品H勺订单号和订购数量。分析：查询'东南实业'的订单号和订购数量，可以首先由企业名称='东南实业'在CUStOmer表中查出该企业对应的客户ID,然后通过客户ID在p_order表中，查询所有该客户ID下的订单ID和数量。因此可以分为两步执行。第一步：确定东南实业'的客户IDSELECT客户IDFROMcustomerWHERE企业名称='东南实业'执行成果：客户ID2第二步：查询客户ID为“2”定购的订单号和产品名SELECT订单ID,数量FROMp_orderWHERE客户ID=2分布执行较麻烦。可以使用嵌套查询来一步实现，将第一步查询嵌套在第二步查询中，以构成第二步的查询条件。SELECT订单ID,数量FROMporderWHERE客户ID=(SELECT客户TDFROMCostomerWHERE企业名称='东南实业')阐明：在这种措施下通过子查询获得的数据必须是惟一的I,不能返回多值，否则运行将出现错误。2 .带有IN关键词的子查询使用IN关键词联接父查询和子查询的关系，通过IN关键词判断查询的某项属性值与否在子查询成果中。此时一般子查询的返回成果往往是一种集合。【例4.25查询订购了类别ID为“2”的所有订单的订单ID和订货日期。分析：产品的类别ID放在产品表ProdUCt中，订单有关的信息寄存在P-Order表中。因此需要通过产品ID将产品表product和订单表p-order联接起来；而一种类别的产品也许不止一件产品，因此通过WHERE子句从产品表PrOdUCt中所有的产品ID。在通过子查询得到产品ID,从订单表p-order表中取出有关的信息。查询命令如下：SELECT订单ID,订货日期FROMp-orderWHERE产品IDIN(SELECT产品IDFROMproductWHERE类别ID=2)3 .带有NOTEXISTS引出的子查询使用NOTEXISTS关键字的子查询时，相称于进行一次存在测试。子查询不返回任何实际数据，它只返回TRUE或FALSE值。【例4.26查询客户ID为“1”的企业订购的所有订单的订单ID和数量。SELECT订单ID,数量EROMp-orderWHEREEXISTS(SELECT*FROMcustomerWHERE客户ID=I)使用EXISTS关键词后，若内层查询成果为非空，则外层WHERE子句返回真值,否则返回假值