- DML语句
- 目录
- DML语句定义
DML语句:数据操纵语句,针对的是数据,经过DML语句的操作,能够对数据源中的数据内容进行调整,包括数据的增加、删除、更新。
争议:SELECT是否属于DML语句?
按照DML语句的定义,执行过DML操作后,数据源中的数据会有所变化,而SELECT语句仅仅是在查询结果中临时对数据有一定程度的变更,但数据源中的原始数据未发生任何变动,因此SELECT并不算作DML语句。
- 手动DML(FOR UPDATE)
- 语法
SELECT * FROM TB_NAME FOR UPDATE; --全局修改
SELECT * FROM TB_NAME WHERE CONDITION FOR UPDATE; --部分修改
语句执行后,目标表的状态即由不可解锁转为可解锁状态,同时【提交】和【回滚】按钮开放,此时可以通过查询结果上方的小锁对表解锁,解锁后可直接在查询结果中进行修改,修改结束后点击绿色对勾表示修改完成,再点击小锁使表回到锁定状态,最后点击【提交】按钮使修改生效或点击【回滚】按钮取消刚才的修改。
- 应用场景
- 测试时为了验证代码的准确性,小范围临时修改数据,一般验证结束后需要再改回;
- 生产环境非常小的修改,不值得重新跑批程序;(一般很少)
- 客户通过EXCEL表格提供少量数据,要求导入到数据库中,可通过该方法直接复制粘贴到表中。(演示)
- 总结:范围小、数据少、临时(非常规)
- 优缺点
1)优点:1.操作简单;
2.可以同时满足增、删、改三方面的需求。
2)缺点:1.所做操作不能留下痕迹,不方便后续回顾;
2.同时对大量数据操作时会有崩溃的危险;
3.必须依赖特定的数据库操作工具。
- 自动DML
- 不基于原表
- INSERT
- 语法:
1.单条数据的插入-录入确定内容(语法1)
INSERT INTO TB_NAME (COL_LIST)
VALUES (DATA_LIST);
2.多条数据的插入-插入查询结果(语法2)
INSERT INTO TB_NAME (COL_LIST)
SELECT …;
SELECT A.DEPTNO, DNAME, COUNT(1) EMP_CT, SUM(SAL) SAL_NUM
FROM EMP A --来源表
LEFT JOIN DEPT B --来源表
ON A.DEPTNO = B.DEPTNO
GROUP BY (A.DEPTNO, DNAME);
--建表
CREATE TABLE DEPT_EMP(DEPTNO NUMBER,DNAME VARCHAR2(15),EMP_CT NUMBER,SAL_NUM NUMBER);
SELECT * FROM DEPT_EMP;
--插入数据(多条数据的插入)
INSERT INTO DEPT_EMP --目标表
SELECT *
FROM (SELECT A.DEPTNO, DNAME, COUNT(1) EMP_CT, SUM(SAL) SAL_NUM
FROM EMP A
LEFT JOIN DEPT B
ON A.DEPTNO = B.DEPTNO
GROUP BY (A.DEPTNO, DNAME));
--将【来源表】中的数据经过加工处理插入到【目标表】中
1.语法1注意点
1.该语法每次仅能插入一条数据;
2.指定插入的数据可与表结构在数量或顺序上不一致,此时字段列表必须与数据列表在列数、顺序、属性、内容上完全一致,否则会报错;
3.若插入的数据在列数、顺序、属性、内容上与表结构完全一致,可在语句中省略字段列表;
4.相较于手动插入数据,语法1能够同时承受的数据插入会更多,但不会多出太多,且还是会有崩溃的危险。若数据量较多建议改到命令窗口执行且中间多穿插’【COMMIT; 】命令。
2.语法2注意点
- 该语法能够支持同时插入多条数据,数据量的多少取决于后边的查询部分;
- 插入数据的列数、顺序、属性、内容完全取决于查询部分;
- 指定插入的数据可与表结构在列数或顺序上不一致,此时字段列表必须与数据列表在列数、顺序、属性、内容上完全一致,否则会报错;
- 若插入的数据在列数、顺序、属性、内容上与表结构完全一致,可在语句中省略字段列表;
- 查询部分可以是完全独立的一部分,所有语法规则完全遵循查询的语法,也因为如此,该语法非常灵活,可通过改变查询部分来改变最终插入的数据。
- 查看表结构
- 通过右键菜单DESCRIBE描述来查看
- SELECT * 直接查询该表
- 通过右键菜单VIEW查看和EDIT编辑来查看表结构
- 在COMMAND WINDOW中 DESC TB_NAME 回车
- 在CMD窗口中连接SQLPLUS DESC TB_NAME 回车
- 应用场景
- 语法1应用场景:1.少量确定数据的临时插入;
2.程序中少量固定数据的频繁插入。
- 语法2应用场景:
工作中所有的固定化程序中,无论数据量的大小。
注:语法2是所有数据插入中效率最快的方法,即便数据量非常大时也会出现效率低的情况,但是相比起来,前两种方法效率会更低。
- DELETE
- 语法
DELETE FROM TB_NAME; --全部数据的删除
DELETE FROM TB_NAME WHERE CONDITION; --有条件的数据删除
注意:1.手动执行删除时,删除语句最好由查询语句变更得到,提交前一定要先确认好删除结果;2.WHERE子句与查询中的WHERE子句用法完全一致。
2.数据的插入思想
数据的插入思想:先删后插-防止数据重复插入
举例:
建一张表(包含姓名、部门名称、岗位、薪资、工作地点),并多次插入对应数据。
建一张表(包括部门名称、人数、薪资合计、平均薪资),并多次插入对应数据。
数据的加工流程:
一次性(或分批次)删除结果表中的本批数据及中间表的所有数据
查询源表插入到中间表1
查询中间表1插入到中间表2
…
查询中间表N插入到结果表
--建表
CREATE TABLE DEPT_EMP(DEPTNO NUMBER,DNAME VARCHAR2(15),EMP_CT NUMBER,SAL_NUM NUMBER);
SELECT * FROM DEPT_EMP;
--插入数据(多条数据的插入)
DELETE FROM DEPT_EMP; --先删除再插入(先删再插,避免重复数据的插入)
INSERT INTO DEPT_EMP --目标表
SELECT *
FROM (SELECT A.DEPTNO, DNAME, COUNT(1) EMP_CT, SUM(SAL) SAL_NUM
FROM EMP A
LEFT JOIN DEPT B
ON A.DEPTNO = B.DEPTNO
GROUP BY (A.DEPTNO, DNAME));
--将【来源表】中的数据经过加工处理插入到【目标表】中
- UPDATE
- 语法
UPDATE TB_NAME SET = EXPR2,....] [WHERE CONDITION]; COL1 = EXPR1 [,COL2
--更新员工信息表中数据,根据每个部门给每个员工调薪,10号部门上调10%,20号部门上调20%,30号部门下调5%
SELECT * FROM EMP;
UPDATE EMP SET SAL=1.1*SAL WHERE DEPTNO=10;
UPDATE EMP SET SAL=1.2*SAL WHERE DEPTNO=20;
UPDATE EMP SET SAL=0.95*SAL WHERE DEPTNO=30;
UPDATE EMP SET SAL=CASE WHEN DEPTNO=10 THEN 1.1*SAL WHEN DEPTNO=20 THEN 1.2*SAL WHEN DEPTNO=30 THEN 0.95*SAL END;
注意:
1.更新后的结果可以是某个固定内容,或是函数处理后的结果,或是运算,或是子查询(单行单列);
2.更新时等号左边是待更新的字段,右边是更新后的结果,顺序不要写反了。
- 疑问
- 若是将两个字段对调,是写一个语句同时更新,还是写两个语句分别更新?
ENAME和JOB对调
SELECT * FROM EMP;
--一个语句同时更新
UPDATE EMP SET ENAME=JOB,JOB=ENAME; --回滚日志
--两个语句分别更新
UPDATE EMP SET ENAME=JOB; --ENAME旧
UPDATE EMP SET JOB=ENAME; --会将JOB和ENAME更新成一样的数据(不能引用到另一条SQL的日志)
- 能否根据不同的条件更新为不同的结果?
--更新员工信息表中数据,根据每个部门给每个员工调薪,10号部门上调10%,20号部门上调20%,30号部门下调5%
--1
UPDATE EMP SET SAL=1.1*SAL WHERE DEPTNO=10;
UPDATE EMP SET SAL=1.2*SAL WHERE DEPTNO=20;
UPDATE EMP SET SAL=0.95*SAL WHERE DEPTNO=30;
--2
UPDATE EMP SET SAL=CASE WHEN DEPTNO=10 THEN 1.1*SAL WHEN DEPTNO=20 THEN 1.2*SAL WHEN DEPTNO=30 THEN 0.95*SAL END;
- 使用UPDATE将所有列都更新为空值,是否等同于删除该行数据?
否,所有列更新为空,是里面的值为空,但这条数据还存在;而删除该行数据,这条数据则不存在了。
- 基于原表的DML操作--MERGE
- 语法
MERGE INTO TB_NAME A --目标表
USING DATA_SOURCE B --数据源 --表、视图、子查询
ON (CONDITION) --关联条件:
WHEN MATCHED THEN --如果能匹配上
UPDATE --对目标表中的旧数据进行更新
SET A.COL1 = B.COL1,A.COL2 = B.COL2 ...
WHERE CONDITION –更新条件-以关联条件成立为前提
DELETE --对目标表中的旧数据进行删除
WHERE CONDITION --删除条件-以更新条件成立为前提
WHEN NOT MATCHED THEN
INSERT (A.COL1,A.COL2....)
VALUES (B.COL1,B.COL2....) --将数据源中的新数据插入到目标表
WHERE CONDITION; --插入条件-以关联条件不成立为前提
- 测试表
- 表名
SELECT * FROM GOODS_CURRENT; --当前商品信息
SELECT * FROM GOODS_CHANGE; --商品变动信息
2.建表并插入数据
CREATE TABLE GOODS_CURRENT
(GOODS_ID varchar2(20),
GOODS_NAME varchar2(50),
PRICE number,
COST number,
GOODS_TYPE varchar2(50)
);
DELETE FROM GOODS_CURRENT;
insert into GOODS_CURRENT (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE)
values ('G01', '笔记本', 10, 5, '办公用品');
insert into GOODS_CURRENT (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE)
values ('G02', '中性笔', 2, 1, '办公用品');
insert into GOODS_CURRENT (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE)
values ('G03', '卫龙辣条', 3, 1, '食品');
insert into GOODS_CURRENT (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE)
values ('G04', '薯片', 10, 4, '食品');
insert into GOODS_CURRENT (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE)
values ('G05', '数据库设计手册', 40, 20, '书籍');
insert into GOODS_CURRENT (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE)
values ('G06', '高等数学', 30, 15, '书籍');
SELECT * FROM GOODS_CURRENT;
SELECT * FROM GOODS_CHANGE;
CREATE TABLE GOODS_CHANGE
(GOODS_ID varchar2(20),
GOODS_NAME varchar2(50),
PRICE number,
COST number,
GOODS_TYPE varchar2(50),
DEL_FLAG varchar2(20),
UPDATE_DT varchar2(50)
);
insert into GOODS_CHANGE (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE, DEL_FLAG, UPDATE_DT)
values ('G08', '百事可乐', 5, 2, '食品', null, '2022-06-20');
insert into GOODS_CHANGE (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE, DEL_FLAG, UPDATE_DT)
values ('G01', '笔记本', 12, 5, '办公用品', null, '2022-06-20');
insert into GOODS_CHANGE (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE, DEL_FLAG, UPDATE_DT)
values ('G07', '大宝SOD', 30, 20, '护肤品', null, '2022-07-10');
insert into GOODS_CHANGE (GOODS_ID, GOODS_NAME, PRICE, COST, GOODS_TYPE, DEL_FLAG, UPDATE_DT)
values ('G03', '卫龙辣条', 4, 1, '食品', 'Y', '2022-07-10');
SELECT * FROM GOODS_CHANGE;
3.测试内容
- 同时进行了更新和插入的操作(最常用)
已有商品更新价格,新商品上架
MERGE INTO GOODS_CURRENT A
USING GOODS_CHANGE B
ON(A.GOODS_ID=B.GOODS_ID)
WHEN MATCHED THEN
UPDATE SET A.PRICE=B.PRICE
WHEN NOT MATCHED THEN
INSERT (A.GOODS_ID,A.GOODS_NAME,A.PRICE,A.COST,A.GOODS_TYPE)
VALUES(B.GOODS_ID,B.GOODS_NAME,B.PRICE,B.COST,B.GOODS_TYPE);
2.仅更新
已有商品更新价格
SELECT * FROM GOODS_CURRENT;
SELECT * FROM GOODS_CHANGE;
MERGE INTO GOODS_CURRENT A
USING GOODS_CHANGE B
ON(A.GOODS_ID=B.GOODS_ID)
WHEN MATCHED THEN
UPDATE
SET A.PRICE=B.PRICE;
3.仅插入
新商品上架
SELECT * FROM GOODS_CURRENT;
SELECT * FROM GOODS_CHANGE;
MERGE INTO GOODS_CURRENT A
USING GOODS_CHANGE B
ON(A.GOODS_ID=B.GOODS_ID)
WHEN NOT MATCHED THEN
INSERT(A.GOODS_ID,A.GOODS_NAME,A.PRICE,A.COST,A.GOODS_TYPE)
VALUES(B.GOODS_ID,B.GOODS_NAME,B.PRICE,B.COST,B.GOODS_TYPE);
4.在做插入或更新时可以加入条件(彼此互不影响)
已达到更新日期的旧商品变更价格,新商品上架,未达到更新日期的不做任何变动
SELECT * FROM GOODS_CURRENT;
SELECT * FROM GOODS_CHANGE;
MERGE INTO GOODS_CURRENT A
USING GOODS_CHANGE B
ON(A.GOODS_ID=B.GOODS_ID)
WHEN MATCHED THEN
UPDATE
SET A.PRICE=B.PRICE
WHERE UPDATE_DT<=TO_CHAR(SYSDATE,'YYYY-MM-DD')
WHEN NOT MATCHED THEN
INSERT(A.GOODS_ID,A.GOODS_NAME,A.PRICE,A.COST,A.GOODS_TYPE)
VALUES(B.GOODS_ID,B.GOODS_NAME,B.PRICE,B.COST,B.GOODS_TYPE)
WHERE UPDATE_DT<=TO_CHAR(SYSDATE,'YYYY-MM-DD');
5.在MERGE中使用删除的操作
用变动表更新商品表,更新时要根据日期来决定是否更新,另外删除标识为Y的商品下架
SELECT * FROM GOODS_CURRENT;
SELECT * FROM GOODS_CHANGE;
MERGE INTO GOODS_CURRENT A
USING GOODS_CHANGE B
ON(A.GOODS_ID=B.GOODS_ID)
WHEN MATCHED THEN
UPDATE
SET A.PRICE=B.PRICE
WHERE UPDATE_DT<=TO_CHAR(SYSDATE,'YYYY-MM-DD')
DELETE --会受到UPDATE SET中WHERE条件的影响
WHERE B.DEL_FLAG='Y'
WHEN NOT MATCHED THEN
INSERT(A.GOODS_ID,A.GOODS_NAME,A.PRICE,A.COST,A.GOODS_TYPE)
VALUES(B.GOODS_ID,B.GOODS_NAME,B.PRICE,B.COST,B.GOODS_TYPE)
WHERE UPDATE_DT<=TO_CHAR(SYSDATE,'YYYY-MM-DD');
MERGE 语法的优劣
1.优势
1.可以同时满足增、删、改的要求,而不必分开书写多个SQL
2.可以依据目标表原有数据的情况作出改动
2.劣势
1.要求的合并以及需要顾及原表内容,会导致运行负担的增加
2.暂时无法单独满足删除的要求
DML语句的注意点
1.对数据源的任何修改,都需要经过提交才能最终生效,提交前若是对所做的修改不满意,
可通过回滚来取消修改。若未提交,则做出的修改仅能在当前窗口临时生效,其他会话窗口查不到当前的修改。
2.无论什么类型的何种DML操作,在操作过程中都会产生回滚日志,
以用于操作的回滚或数据的恢复。日志的生成可通过代码指令或参数设置来取消,
但该操作非常危险,一旦数据库运行出现错误,若是缺少相应的日志,数据就有可能会丢失。