数据库技术与应用（达梦）课件 任务3.1 查询电力调度管理系统单表数据

《数据库原理与应用》任务3.1查询电力调度管理系统单表数据项目3数据查询与管理主讲人：刘培林第8讲

定制化查询你的数据（1）在电力调度管理系统中，用电情况分析是电力分析的核心业务之一，本任务全面分析T_CONSUME数据表的数据，通过以下查询获取用电消费信息，辅助电力管理决策。1）查询CONSUME_ID列、CONSUME_NAME列、CONSUME_NUM列和UESER_ID列的数据，对CONSUME_NUM列的值进行乘以3加2的加密操作，加密列重命名为CODE_NUM，按照加密结果降序排序。2）查询CONSUME_NUM列值最高的10条记录。3）查询CONSUME_NUM列值在150到200之间的5条记录。4）按用户分组统计用电情况（CONSUME_NUM列的值），显示用电总量最高的3个用户用电情况。5）按日期分组统计用电情况，统计出用电总量高于6800的日期。素质目标能力目标知识目标1）掌握单表查询各子句的含义与用法。2）掌握谓词与逻辑运算符的用法。3）理解集函数的概念与含义。4）了解集合查询的概念。1）能够准确选用查询子句查询用户需要的数据。2）能够准确选用谓词构造查询条件。3）能够准确理解分组查询中分组的含义与分组项的应用限制，正确构造分组查询语句准确获取用户数据。能够准确获取用户需求，正确理解查询要求，培养辩证思维能力。3.1.1简单查询仅从一个表/视图中检索数据称为单表查询，也即<FROM子句>仅涉及一个表/视图，单表查询是数据查询的基础。最简单的单表查询指定基表的所有元组，语法格式如下。SELECT[[<模式名>.]<基表名>|<视图名>.]*|<值表达式>[[AS]<列别名>],….FROM[<模式名>.]<基表名|视图名>【例3-1】查询T_USERINFO表的所有数据。SELECT*FROMEPDMS.T_USERINFO;可以用DISTINCT关键字修饰值表达式，表示去掉重复值记录。【例3-2】为例3-1的查询添加DISTINCT关键字，分析运行结果。SELECTDISTINCTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFO;值表达式中可以使用列名、常量、变量、运算符、子查询，以及函数等。【例3-3】查询T_USERINFO表，用平方根函数对BALANCE列进行加密，提高数据的安全性。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,SQRT(BALANCE),REMARKFROMEPDMS.T_USERINFO;可以对值表达式指定别名，使用AS关键字。【例3-4】完善例3-3，对BALANCE加密列指定别名CODE_BALANCE。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,SQRT(BALANCE)ASCODE_BALANCE,REMARKFROMEPDMS.T_USERINFO;AS关键字可以省略，直接省略掉即可。别名中包含空格时，需要用双引号将别名引起来。例如将加密的BALANCE列命名为别名CODEBALANCE时，需要使用双引号引起来，代码如下。SELECTDISTINCTUSER_ID,USER_NAMENAME,USER_PASSWORD,COMMUNITY_ID,SQRT(BALANCE)"CODEBALANCE",REMARKFROMEPDMS.T_USERINFO;1ORDERBY子句<ORDERBY子句>规定了当行由查询返回时应具有的顺序，必须跟在<查询表达式>之后，在查询计算得出的最终结果上进行操作，定义了要排序的项目清单和排序的顺序，递增顺序（ASC，默认）或是递减顺序（DESC），是可选项，语法格式如下。<ORDERBY子句>::=ORDERBY<order_by_list><order_by_list>::=<order_by_item>{,<order_by_item>}<order_by_item>::=<exp>[COLLATE<collation_name>][ASC|DESC][NULLSFIRST|LAST]<exp>::=<列说明>|<无符号整数>|<值表达式>|<布尔表达式>排序规则如下。1）当包含多个排序列时，按照列从左到右排列的顺序，先按左边列排序查询结果，当左边列值相等时，进一步按照右边排序列排序……以此类推，直到排序完成。【例3-5】查询T_USERINFO表，并将查询结果按照COMMUNITY_ID列升序排序。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYCOMMUNITY_ID;【例3-6】查询T_USERINFO表，并将查询结果按照COMMUNITY_ID和USER_NAME列升序排序。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYCOMMUNITY_ID,USER_NAME;【例3-7】查询T_USERINFO表，并将查询结果按照COMMUNITY_ID列和USER_NAME列降序排序。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYCOMMUNITY_IDDESC,

USER_NAMEDESC;用列序号代替列名，本例代码可以书写如下。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBY4,2DESC;2）当排序列为列计算表达式或子查询时，按照表达式计算结果或子查询结果进行排序。特殊情况，不包含列名的纯粹值表达式不参与排序，但是，纯粹的布尔表达式按照表达式结果进行排序。【例3-8】查询T_USERINFO表，并将查询结果按照BALANCE列的值加1排序。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYBALANCE+1;但是，以下代码不会排序。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBY1+1;布尔表达式按照表达式结果进行排序。【例3-9】查询T_USERINFO表，并将USER_NAME列值为“赵元”的行排在最后。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYUSER_NAME='赵元';【例3-10】查询T_USERINFO表，并将USER_NAME列值为“赵元”的行排在第一行。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYUSER_NAME!='赵元';也可以为例3-9<ORDERBY子句>增加DESC关键字实现，代码如下。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYUSER_NAME='赵元'DESC;3）当排序列为NULL时，不进行排序。当排序列值包含NULL时，根据“NULLSFIRST|LAST”规则排序，缺省值为NULLSFIRST；4）对于包含<GROUPBY子句>的分组查询，必须在<GROUPBY子句>分组中包含所有的排序列。5）使用集函数的<ORDERBY子句>必须使用<GROUPBY子句>分组，且查询的所有列都必须包含在<GROUPBY子句>分组中。6）<ORDERBY子句>至多可包含255个排序列。2TOP子句<TOP子句>筛选查询结果，语法格式如下。<TOP子句>::=TOP<n>|<n1>,<n2>|<n>PERCENT|<n>WITHTIES|<n>PERCENTWITHTIES【例3-11】为例3-10添加TOP子句，返回USER_NAME列值为“赵元”的行。SELECTTOP1USER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOORDERBYUSER_NAME!='赵元';2）TOP<n1>,<n2>选择第n1条记录之后的n2条记录；【例3-12】查询EPDMS.T_USERINFO表中第2条记录之后的4条记录。SELECTTOP2，4*EPDMS.T_USERINFO;3）TOP<n>PERCENT选择结果的前n%条记录；【例3-13】查询T_USERINFO表中前50%条的记录。SELECTTOP50PERCENT*FROMEPDMS.T_USERINFO;4）TOP<n>PERCENTWITHTIES选择结果的前n%条记录，且可以返回额外的行，也即与最后一行以相同的排序键排序的所有行。WITHTIES必须与ORDERBY子句同时出现，没有ORDERBY子句时忽略WITHTIES。【例3-14】为例3-13查询添加WITHTIES关键字和ORDERBY子句，分析运行结果。SELECTTOP50PERCENTWITHTIES*FROMEPDMS.T_USERINFOORDERBYCOMMUNITY_ID;3LIMIT与ROW_LIMIT子句<LIMIT子句>按顺序选取结果集中某条记录开始的N条记录。语法格式如下。<LIMIT子句1>::=LIMIT<记录数>|<偏移量>,<记录数>|<记录数>OFFSET<偏移量><LIMIT子句2>::=OFFSET<偏移量>LIMIT<记录数>1）LIMITN：选择前N条记录；【拓展例1】实现与例3-11同样功能，查询T_USERINFO表，返回USER_NAME列值为“赵元”的行。SELECTUSER_ID,USER_NAME,USER_PASSWORD,COMMUNITY_ID,BALANCE,REMARKFROMEPDMS.T_USERINFOLIMIT1ORDERBYUSER_NAME!='赵元';2）LIMITM,N：选择第M条记录之后的N条记录；【拓展例2】实现与例3-12同样功能，查询T_USERINFO表中第2条记录之后的4条记录。SELECT*FROMEPDMS.T_USERINFOLIMIT2，4;3）LIMITMOFFSETN：选择第N条记录之后的M条记录；【拓展例3】实现与拓展例2同样功能，查询T_USERINFO表中第2条记录之后的4条记录。SELECT*FROMEPDMS.T_USERINFOLIMIT4OFFSET2;4）OFFSETNLIMITM：选择第N条记录之后的M条记录。【拓展例4】实现与拓展例2同样功能，查询T_USERINFO表中第2条记录之后的4条记录。SELECT*FROMEPDMS.T_USERINFOOFFSET2LIMIT4;<TOP子句>和<LIMIT子句>不能同时出现，<LIMIT子句>一般用在UPDATE或DELETE操作之后。<ROW_LIMIT子句>指定查询结果中偏移的行数，或从指定偏移开始返回的行数或总行数的百分比，以便更为灵活地获取查询结果。语法格式如下。<ROW_LIMIT子句>::=[OFFSET<offset><ROW|ROWS>][<FETCH说明>]<FETCH说明>::=FETCH<FIRST|NEXT>[<大小>|<大小>PERCENT]<ROW|ROWS><ONLY|WITHTIES>1）<offset>指定查询返回行的起始偏移。必须为数字。offset为负数时视为0，使用时需要在负数外加上括号()；为NULL或大于等于所返回的行数时，返回0行；为小数时，小数部分四舍五入；2）<FIRST|NEXT>FIRST为从偏移为0的位置开始。NEXT为从指定的偏移的下一行开始获取结果。只做注释说明的作用，没有实际的限定作用；3）[<大小>|<大小>PERCENT]指定返回行的行数或者百分比。其中<大小>只能为数字。PERCENT指定为负数时，视为0%；为NULL时返回0行；如果没有指定PERCENT而指定了<大小>，返回从偏移开始，<大小>指定的行数；如果省略返回行数和PERCENT，默认返回偏移位置的下一行数据；4）<ONLY|WITHTIES>指定结果集是否返回额外的行。额外的行是指与最后一行以相同的排序键排序的所有行。ONLY为只返回指定的行数。WITHTIES必须与ORDERBY子句同时出现，如果没有ORDERBY子句，则忽略WITHTIES。【拓展例5】查询T_USERINFO表中BALANCE列值最低的50%用户情况。SELECT*FROMEPDMS.T_USERINFOORDERBYBALANCEFETCHFIRST50PERCENTROWSONLY;【拓展例6】查询T_USERINFO表中BALANCE列值第3低的3条记录。SELECT*FROMEPDMS.T_USERINFOORDERBYBALANCEOFFSET2ROWSFETCHFIRST3ROWSONLY;3.1.2分组查询1集函数SQL语言提供了多种系统集函数增强查询能力，方便用户的使用。集函数对行集作统计操作并返回单一统计值。函数名说明函数名说明MAX求最大值CORR求相关系数MIN求最小值FIRST_VALUE返回查询项的首行记录AVG求平均值ANY_VALUE返回查询项的任意一行记录SUM求和AREA_MAX求区间范围内的最大值COUNT统计个数MEDIAN求中位数VARIANCE求方差PERCENTILE_CONT求百分比STDDEV求标准差BIT_AND按位与STDDEV_SAMP求样本标准差BIT_OR按位或COVAR_POP求总体协方差BIT_XOR按位异或COVAR_SAMP求样本协方差

【例3-16】查询T_USERINFO表的记录数。SELECTCOUNT(*)FROMEPDMS.T_USERINFO;【例3-17】查询T_USERINFO表的BALANCE列的最大值。SELECTMAX(BALANCE)fromEPDMS.T_USERINFO;【例3-18】查询T_USERINFO表的BALANCE列值的和。SELECTSUM(BALANCE)fromEPDMS.T_USERINFO;2GROUPBY和HAVING子句<GROUPBY子句>用于分类汇总，一般与集函数一起使用。<HAVING子句>用于为组设置检索条件（注意与带条件查询子句的应用场景区别）。当存在分组项时，若查询项包含列，则只能是分组项中的列，或者是作用于当前分组的集函数。使用<GROUPBY子句>注意事项如下。1）分组列不能为集函数表达式和<SELECT子句>中定义的别名；2）当分组列值包含空值时，空值作为一个独立组；3）当分组列包含多个列名时，按照<GROUPBY子句>中列出现的顺序进行分组；4）<GROUPBY子句>中至多可包含512个分组项；【例3-19】按COMMUNITY_ID列值分组统计T_USERINFO表中各区的用户数。SELECTCOMMUNITY_ID,COUNT(*)FROMEPDMS.T_USERINFOGROUPBYCOMMUNITY_ID;【例3-20】按COMMUNITY_ID列值分组统计T_USERINFO表中各街道的BALANCE列的平均值，且仅显示BALANCE列的平均值大于150的情况。SELECTCOMMUNITY_ID,AVG(BALANCE)FROMEPDMS.T_USERINFOGROUPBYCOMMUNITY_IDHAVINGAVG(BALANCE)>150ORDERBYAVG(BALANCE);3.1.3带条件查询为查询添加<WHERE子句>，能够指定查询的条件，限定查询范围为满足条件的元组。<WHERE子句>常用的查询条件由谓词和逻辑运算符组成。谓词包括比较谓词（=、>、<、>=、<=、<>），BETWEEN谓词、IN谓词、LIKE谓词、NULL谓词、EXISTS谓词。谓词的运算结果为布尔值“真、假或未知”。逻辑运算符有AND，OR，NOT、&&、!。其中AND和&&表示逻辑与运算、OR表示逻辑或运算，NOT和!表示逻辑非运算。1比较谓词比较谓词比较两个值的大小，数值数据基于代数值的大小进行比较，字符串数据按序对同一顺序位置的字符逐一进行比较，两字符串长度不同时，短的字符串先在末尾增加空格，然后再进行比较。【例3-21】查询T_USERINFO表的BALANCE列的值在大于157.08的用户。SELECT*FROMEPDMS.T_USERINFOWHEREBALANCE>157.08;2BETWEEN谓词BETWEEN谓词用于限定查询的范围，用下限值和上限值进行限定。【例3-22】查询TUSEBLNFO表中BALANCE列的值在157.08和250.00之间的记录。SELECTFROMEPDMS.TUSERINFCWHEREBALANCEBETWEEN157.08AND250.00;3IN/NOTIN谓词IN谓词限定查询的取值范围，用集合进行限定。NOTIN谓词是IN谓词的取反，限定查询的取值范围为IN谓词限定的范围之外。【例3-23】查询T_USERINFO表中COMMUNITY_ID列的值为'10101'和'10401'的记录。SELECT*FROMEPDMS.T_USERINFOWHERECOMMUNITY_IDIN('10101','10401');【例3-24】查询T_USERINFO表中COMMUNITY_ID列的值不为10101或10401的记录。SELECTFROMEPDMS.TUSERINFCWHERECOMMUNITY_IDNOTIN('10101','10401');运行后返回COMMUJNITY_ID列值不为10101或10401的37条记录,与例3-23的7条记录合起来,正好是T_USERINFO表的全部30条记录。IN谓词支持多列查询。【例3-25】查询T_USERINFO表中10101街道中名字叫“张玮”的用户。SELECT*FROMEPDMS.T_USERINFOWHERE（COMMUNITY_ID,USERNAME）

IN(('10101','张玮'));【例3-26】查询TUSERINFO表中10101街道中名字叫“张玮”或“张芬”的用户。SELECT*FROMEPDMS.T_USERINFOWHERE(COMMUNITY_ID,USER_NAME)

IN(('10101','张玮'),('10101','张芬'));4LIKE谓词LIKE谓词使用匹配字符串常数进行字符串匹配，匹配字符串常数可以是完整的字符串，也可以包含特殊字符百分号（%）和下划线（_），“%”和“_”称通配符。“%”代表任意字符串（包括空串），“_”代表任意一个字符。使用LIKE谓词的查询称为模糊查询或匹配查询。【例3-27】查询T_USERINFO表中“李”姓用户。SELECT*FROMEPDMS.T_USERINFOWHEREUSER_NAMELIKE'李%';【例3-28】查询T_USERINFO表中名字中包含“元”字的用户。SELECT*FROMEPDMS.T_USERINFOWHEREUSER_NAMELIKE'%元%';

【例3-29】查询T_USERINFO表中COMMUNITY_ID列以“03”结尾的用户。SELECT*FROMEPDMS.T_USERINFOWHERECOMMUNITY_IDLIKE'%03';针对作为普通字符使用的“%”和“_”，SQL语句提供了转义说明，语法格式如下。<列名>LIKE'<匹配字符串常数>'[ESCAPE<转义字符>]其中，<转义字符>指定了一个字符，当该字符出现在<匹配字符串常数>中时，用以指明紧跟其后的“%”或“_”不是通配符，作一般字符使用。【例3-30】查询T_USERINFO表中COMMUNITY_ID列以“_03”结尾的用户。SELECT*FROMEPDMS.T_USERINFOWHERECOMMUNITY_IDLIKE'%*_03'ESCAPE'*';LIKE谓词针对列进行计算时，使用ROW保留字，LIKE谓词还可以对表或视图进行LIKE计算。计算时，查询依次对表或视图中所有字符类型的列进行LIKE计算，只要有符合条件的列，记录即被返回。语法格式如下。<表名>.ROWLIKE<匹配字符串>[ESCAPE<转义字符>]这种查询在实际中非常有用，例如查询有关某信息的评价，即可使用这种查询。【例3-31】查询T_USERINFO表中包含字符串“05”的记录。SELECT*FROMEPDMS.T_USERINFOWHEREEPDMS.T_USERINFO.ROW

LIKE'%05%';5NULL谓词NULL标识还未确定值或值未知的列，数值0和空字符串是确定的值，不是NULL值。【例3-32】查询T_USERINFO表中REMARK列的值为NULL的记录。SELECT*FROMEPDMS.T_USERINFOWHEREREMARKISNULL;在NULL谓词前，加NOT表示不为空。【例3-33】查询T_USERINFO表中REMARK列的值不为NULL的记录。SELECT*FROMEPDMS.T_USERINFOWHEREREMARKISNOTNULL;6组合逻辑谓词与逻辑运算符组合可以生成复杂的条件查询。【例3-34】实现例3-22同样功能，查询T_USERINFO表的BALANCE列的值大于或等于157.08、小于或等于250.00的记录。SELECT*FROMEPDMS.T_USERINFOWHEREBALANCE>=157.08ANDBALANCE<=250.00;【例3-35】查询T_USERINFO表中各街道的BLANCE列值，仅显示BLANCE列的平均值大于210或小于120的用户情况。SELECTCOMMUNITY_ID,AVG(BALANCE)FROMEPDMS.T_USERINFOGROUPBYCOMMUNITY_IDHAVINGAVG(BALANCE)>210ORAVG(BALANCE)<120ORDERBYAVG(BALANCE);3.1.4集合查询集合查询是指使用了集合运算符的查询。集合运算符包括UNION、EXCEPT、MINUS和INTERSECT。语法格式如下。<select_clause><集合运算符>[ALL|DISTINCT|UNIQUE]<select_clause>1）<集合运算符>有4种，其中，UNION运算符返回两个结果集的并集；EXCEPT和MINUS运算符功能完全一样，返回两个结果集的差集；INTERSECT运算符返回两个结果集的交集，INTERSECT运算符会自动删除重复行，如果需要保留重复行，需要使用INTERSECTALL；2）两个查询块<select_clause>的查询列数目必须相同，数据类型必须兼容；3）ALL|DISTINCT|UNIQUE。ALL表示集合查询结果中保持所有重复；DISTINCT表示删除所有重复。DISTINCT与UNIQUE等价。默认值为DISTINCT。【例3-36】查询T_CONSUME表和T_USERINFO表的USER_ID列值，将两者连接，并去掉重复行。SELECTUSER_IDFROMEPDMS.T_CONSUMEUNIONSELECTUSER_IDFROMEPDMS.T_USERINFO;【例3-37】查询T_CONSUME表和T_USERINFO表的USER_ID列值，将两者连接，保留重复行。SELECTUSER_IDFROMEPDMS.T_CONSUMEUNIONALLSELECTUSER_IDFROMEPDMS.T_USERINFO;【例3-38】查询T_ADMININFO表和T_USERINFO表的ID列值和NAME列值，返回两者的差集，并去掉重复行。SELECTADMIN_ID,ADMIN_NAMEFROMEPDMS.T_ADMININFO

EXCEPT

SELECTUSER_ID,USER_NAMEFROMEPDMS.T_USERINFO;也可以是以下代码。SELECTUSER_ID,USER_NAMEFROMEPDMS.T_USERINFOEXCEPTSELECTADMIN_ID,ADMIN_NAMEFROMEPDMS.T_ADMININFO;【例3-39】查询T_CONSUME表和T_USERINFO表的USER_ID列值，返回两者的交集，并去掉重复行。SELECTUSER_IDFROMEPDMS.T_CONSUMEINTERSECTSELECTUSER_IDFROMEPDMS.T_USERINFO;也可以是以下代码，运行结果不变。SELECTUSER_IDFROMEPDMS.T_USERINFOINTERSECT

SELECTUSER_IDFROMEPDMS.T_CONSUME;[任务实施]

1技术分析1）本任务是仅涉及T_CONSUME数据表的单表数据查询。2）查询中涉及排序、分组和查询条件，仔细分析任务要求和单表查询各子句的应用场景，准确使用查询子句完成查询。[任务实施]

2实现步骤1）查询CONSUME_ID列、CONSUME_NAME列、CONSUME_NUM列和UESER_ID列的数据，对CONSUME_NUM列的值进行乘以3加2的加密操作，加密列重命名为CODE_NUM，按照加密结果降序排序。简单查询，参考例3-3对CONSUME_NUM列的值运算即可，代码如下。SELECTCONSUME_ID,CONSUME_NUM*3+2ASCODE_NUM,USER_IDFROMEPDMS.T_CONSUMEORDERBYCODE_NUMDESC;[任务实施]

2）查询C