数据库技术与应用（达梦）课件 任务3.5 维护电力调度管理系统基础数据

《数据库原理与应用》任务3.5维护电力调度管理系统基础数据项目3数据查询与管理主讲人：刘培林第11讲

你的数据你做主—数据操纵1）在任务3.4中，在某一个规定的时间自动读取了“10101”街道电表的用电情况，为了数据安全起见，请通过修改的方式将读取的数据进行加密，加密规则为将T_CONSUME表的CONSUME列的值先乘以2然后加3保存。2）进行用电数据分析，去掉一个用电最小值和一个最大值，然后统计用电平均值。3）在T_CONSUME表中建档所有用户，没有用电的用户将CONSUME列的值初始化为0。素质目标能力目标知识目标1）掌握修改数据表数据的语句；2）掌握从数据表删除数据的语句。1）能够修改数据表的数据；2）能够从数据表删除数据。培养数据安全意识，全面提升安全素质。3.5.1数据修改数据修改语句用于修改表中已存在的数据，有两种修改形式，值修改和基于查询的修改。1值修改值修改语法格式如下。UPDATE<表引用>{,<表引用>}SET<列名>=<<值表达式>|DEFAULT>{,<列名>=<<值表达式>|DEFAULT>}[WHERE<条件表达式>]1）<列名>是表或视图中被更新列的名称，如果<SET子句>中省略列的名称，列的值保持不变，<SET子句>中的<列名>不能重复出现。如果<列名>为被引用列，只有被引用列中未被引用列引用的数据才能被修改；如果<列名>为引用列，引用列的数据被修改后也必须满足引用完整性。系统能够自动检查引用完整性。2）<值表达式>指明赋予相应列的新值，可以为一个<集函数>、<函数>、<标量子查询>或<计算表达式>等。3）<条件表达式>指明限制被更新的行必须符合指定的条件，如果省略此子句，则修改表或视图中所有的行。【例3-70】修改T_USERINFO数据表，将USER_PASSWORD列的值修改为“123”。UPDATEEPDMS.T_USERINFOSETUSER_PASSWORD='123';【例3-71】修改T_USERINFO数据表，将COMMUNITY_ID列值为“10101”的用户的USER_PASSWORD列的值修改为“10101”。UPDATEEPDMS.T_USERINFOSETUSER_PASSWORD='10101'WHERECOMMUNITY_ID='10101';【例3-72】修改T_USERINFO数据表，将COMMUNITY_ID列值为“10101”的用户的USER_PASSWORD列的值修改为“10101”，REMARK列的值修改为“01”。UPDATEEPDMS.T_USERINFOSETUSER_PASSWORD='10101',REMARK='01'WHERECOMMUNITY_ID='10101';执行后仅修改COMMUNITY_ID列值为“10101”的4条记录。需要注意的是需要提交执行，也即按下查询窗口的

按钮，才能真正提交执行。可以编写查询语句查看修改的结果，查询代码如下。SELECT*FROMEPDMS.T_USERINFOWHERECOMMUNITY_ID='10101';【例3-73】修改T_USERINFO数据表，将AREA_ID列值为“101”的用户的USER_PASSWORD列的值修改为“10101”，REMARK列的值修改为“01”。第1步，用自然连接找到AREA_ID列值为“101”对应的COMMUNITY_ID列的值。SELECTCOMMUNITY_IDFROMEPDMS.T_COMMUNITYNATURALJOINEPDMS.T_AREAWHEREAREA_ID='101';第2步：在修改语句的WHERE子句中，将COMMUNITY_ID列值用IN运算符与子查询得到的表进行比较。UPDATEEPDMS.T_USERINFOSETUSER_PASSWORD='10101',REMARK='01'WHERECOMMUNITY_IDIN(SELECTCOMMUNITY_ID FROMEPDMS.T_COMMUNITY NATURALJOINEPDMS.T_AREA WHEREAREA_ID='101');语句执行的运行结果如图所示，影响了14条记录，也即修改了T_AREA表中AREA_ID列值为“101”的3条COMMUNITY_ID列值关联的14条用户记录。2参照完整性要求数据修改需要保证数据的参照完整性约束要求。【例3-74】修改T_USERINFO数据表，将COMMUNITY_ID列值为“10101”的用户的USER_ID列的值修改为“1000000999”。UPDATEEPDMS.T_USERINFOSETUSER_ID='1000000999'WHERECOMMUNITY_ID='10101';语句执行的运行结果如图所示，违反了USER_ID主键列所要求的唯一性约束要求。【例3-75】修改T_USERINFO数据表，将USER_ID列值为“1000000001”的用户的USER_ID列的值修改为“1000000999”。UPDATEEPDMS.T_USERINFOSETUSER_ID='1000000999'WHEREUSER_ID='1000000001';语句执行的运行结果如图所示，违反了参照完整性约束要求，因为T_USERINFO表的USER_ID列是T_CONSUME表的外键，在T_CONSUME表中有“1000000001”用户的记录。3查询修改查询修改一次可以修改多列或多行，语法格式如下。UPDATE<表引用>{,<表引用>}SET(<列名>{,<列名>})=<子查询表达式>[WHERE<条件表达式>]【例3-76】修改T_USERINFO数据表，将USER_PASSWORD列的值修改为USER_ID列的值。UPDATEEPDMS.T_USERINFOT1SETT1.USER_PASSWORD=(SELECTT2.USER_ID

FROMEPDMS.T_USERINFOT2WHERET2.USER_ID=T1.USER_ID);代码运行后用查询语句查询T_USERINFO表的值，运行结果如图所示，可以看到，USER_PASSWORD列的值全部修改为USER_ID列的值了。【例3-77】用查询修改实现例3-72，修改T_USERINFO数据表，将COMMUNITY_ID列值为“10101”的用户的USER_PASSWORD列的值修改为“10101”，REMARK列的值修改为“01”。UPDATEEPDMS.T_USERINFOSET(USER_PASSWORD,REMARK)=(SELECT'10101','01')WHERECOMMUNITY_ID='10101';3.5.2数据删除数据删除语句用于删除表中已存在的数据，语法格式如下。DELETE[FROM]<模式名>.]{<基表或视图名>|<子查询表达式>}[WHERE<条件表达式>][<LIMIT限定条件>];参数说明如下。1）<模式名>指明该表或视图所属的模式，缺省为当前模式。2）<基表名>指明被删除数据的基表的名称。3）<视图名>指明被删除数据的视图的名称，实际上DM将从视图的基表中删除数据。4）<条件表达式>指明基表或视图的基表中被删除的记录须满足的条件。5）<LIMIT限定条件>指明删除数据时的限定条件，同查询语句中的<LIMIT限定条件>。使用说明如下。1）如果不带<WHERE子句>，表示删除表中全部元组，但表的定义仍在字典中。DELETE语句删除的是表中的数据，并不删除表结构。2）DELETE语句一次只能对一个表进行删除，当两个表存在引用与被引用关系时，首先要删除引用表里的记录，只有引用表中无记录时，才能删被引用表中的记录。3）执行与表相关的DELETE语句将触发所有定义在表上的DELETE触发器。4）如果视图为不可更新视图，则不能从视图中删除记录。5）当<子查询表达式>作为DELETE的目标时，实际上是对查询表达式的基表进行操作，对于表子查询，查询表达式的查询项既可以来源于多个基表也可以是计算列，当查询项来自多个基表时，第一个满足条件的键值保存表即是最终删除数据的表；对于派生表子查询，查询表达式的查询项必须都来源于同一个基表且不能是计算列，查询项所属的基表即是查询表达式的基表。如果查询表达式是带有连接的查询，那么对于连接中视图基表以外的表，连接列上必须是主键或者带有UNIQUE约束。不支持PIVOT/UNPIVOT，不支持UNION/UNIONALL查询。【例3-78】删除T_USERINFO表的记录，分析运行结果。DELETEFROMEPDMS.T_USERINFO;运行后输出如图3-99所示的报错信息，显示违反了引用约束，因为T_CONSUME表引用了T_USERINFO表的USER_ID列，所以不能删除T_USERINFO表的全部数据，除非T_CONSUME表的数据已清空。【例3-79】修改例3-78，删除T_USERINFO表中USER_ID列的值为“1000000001”的记录，分析运行结果。DELETEFROMEPDMS.T_USERINFOWHEREUSER_ID='1000000001';运行后报错信息同例3-78，违反了引用约束。因为T_CONSUME表中包含有USER_ID列的值为“1000000001”的记录，如果想删除该用户，必须先删除T_CONSUME表中相关记录，也即先删除引用表记录，然后才能删除用户，也即删除被引用表相关记录，代码如下。DELETEFROMEPDMS.T_CONSUMEWHEREUSER_ID='1000000001';DELETEFROMEPDMS.T_USERINFOWHEREUSER_ID='1000000001';两个语句执行顺序正确，运行后输出正确删除提示信息。3.5.3MERGEINTO语句MERGEINTO语句合并UPDATE和INSERT语句，根据一张表（或视图）的连接条件对另外一张表（或视图）进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT，语法格式如下。MERGEINTO[<模式名>.]<基表或视图名>[<表别名>]USING<表引用>ON[(]<条件判断表达式>[)]WHENMATCHEDTHENUPDATESET子句<WHERE条件子句>[DELETE<WHERE条件子句>]WHENNOTMATCHEDTHENINSERT[<full_column_list>]VALUES<ins_value_list><where_clause_null>参数说明如下。1）<模式名>指明该表或视图所属的模式，缺省为当前用户的缺省模式。2）<基表名>指明被修改数据的基表的名称。3）<视图名>指明被修改数据的视图的名称，实际上DM对视图的基表更新数据。4）<条件表达式>指明限制被操作执行的行必须符合指定的条件，如果省略此子句，则对表或视图中所有的行进行操作。使用说明如下。1）INTO后为目标表，表示待更新、插入的表、可更新视图及可更新查询表达式。2）USING后为源表（普通表或可更新视图），表示用于和目标表匹配、更新或插入的数据源。3）ON(<条件判断表达式>)表示目标表和源表的连接条件，如果目标表有匹配连接条件的记录则执行更新该记录，如果没有匹配到则执行插入源表数据。4）MERGE_UPDATE_CLAUSE：当目标表和源表的JOIN条件为TRUE时，执行该语句。5）MERGE_INSERT_CLAUSE：当目标表和源表的JOIN条件为FALSE时，执行该语句。同时会触发目标表上的INSERT触发器，也会进行约束检查。可指定插入条件，插入条件只能在源表上设置。6）MERGE_UPDATE_CLAUSE和MERGE_INSERT_CLAUSE既可以同时指定，也可以只出现其中任何一个。7）需要有对源表的SELECT权限，对目标表的UPDATE/INSERT权限，如果<UPDATE子句>有DELETE，还需要有DELETE权限。8）<UPDATE子句>不能更新在ON连接条件中出现的列。【拓展例1】使用MERGEINTO语句将T_USERINFO表的数据合并到T_ADMININFO表中。MERGEINTOEPDMS.T_ADMININFOT1USINGEPDMS.T_USERINFOT2ON(T1.ADMIN_ID=T2.USER_ID)WHENMATCHEDTHENUPDATESETT1.ADMIN_NAME=T2.USER_NAMEWHENNOTMATCHEDTHENINSERT(ADMIN_ID,ADMIN_NAME,ADMIN_PASSWORD,ADMIN_TYPE)VALUES(USER_ID,USER_NAME,USER_PASSWORD,1);由于T_USERINFO表的ADMIN_ID列数据和T_ADMININFO表的USER_ID列数据没有相等的，执行后T_USERINFO表的数据将全部插入到T_ADMININFO表中。运行后输出如图所示信息，T_USERINFO表的47条记录全部插入到了T_ADMININFO表中。[任务实施]

1技术分析1）加密T_CONSUME表的数据是修改数据，使用数据修改语句。2）去掉T_CONSUME表中CONSUME列值最大值和最小值最简单，使用删除语句直接删除掉对应记录，然后统计CONSUME列的平均值。3）建档仅需对T_CONSUME表中没有的T_USERINFO表中记录操作，因此，使用MERGEINTO语句。[任务实施]

2实现步骤1）使用UPDATE语句修改T_CONSUME表的记录，代码如下。UPDATEEPDMS.T_CONSUMESETCONSUME_NUM=CONSUME_NUM*2+3;2）删除CONSUME列值最大值和最小值的记录，代码如下。DELETEFROMEPDMS.T_CONSUMEWHERECONSUME_NUM=(SELECTMAX(CONSUME_NUM)FROMEPDMS.T_CONSUME);DELETEFROMEPDMS.T_CONSUMEWHERECONSUME_NUM=(SELECTMIN(CONSUME_NUM)

FROMEPDMS.T_CONSUME);[任务实施]

3）统计CONSUME列的平均值，代码如下。SELECTAVG(CONSUME_NUM)FROMEPDMS.T_CONSUME;4）将T_USERINFO表的数据合并到T_CONSUME表中，代码如下。--SYS_GUID()生成全球唯一标识符--GETDATE()返回系统当前时间--LEFT(char,10)返回字符串char最左边的10个字符组成的字符串MERGEINTOEPDMS.T_CONSUMET1USINGEPDMS.T_USERINFOT2ON(T1.USER_ID=T2.USER_ID)WHENNOTMATCHEDTHENINSERT(CONSUME_ID,T1.CONSUME_DATE,USER_ID,CONSUME_NUM)

VALUES(SYS_GUID(),LEFT(GETDATE(0),10),USER_ID,0);[任务测试]1）本任务是数据的维护，需要比较维护前后的数据变化，所以测试代码都需要执行两次，一次在数据维护前执行，查询数据的原始值，一次在维护后执行，查询维护的结果。以查询T_CONSUME表数据加密是否成功为例，在加密前用一条指定记录（例如，某用户某天的记录）编写查询语句，查询T_CONSUME表的数据，代码如下。SELECT*FROMEPDMS.T_CONSUMEWHEREUSER_ID='1000000001'ANDCONSUME_DATE='2024-06-25';[任务测试]然后执行数据修改语句，运行结果如下图所示，表示全部数据已修改成功。再次执行数据查询语句，运行结果如下图所示，并与上一次查询结果进行比较，检查数据维护的结果。[任务测试]2）删除CONSUME列值最大值和最小值。以删除CONSUME列值最大值为例，首先查询CONSUME列的最大值，代码如下。SELECTMAX(CONSUME_NUM)FROMEPDMS.T_CONSUM