插入记录
插入单行记录
如:
INSERT INTO emp
(empno, ename, job, mgr,
hiredate, sal, comm, deptno
)
VALUES (7890, '刘七', '副理', 7566,
TO_DATE ('2001-08-15', 'YYYY-MM-DD'), 8000, 300, 20
);
INSERT INTO emp
VALUES (7891, '刘七', '副理', 7566, TO_DATE ('2001-08-15', 'YYYY-MM-DD'),
8000, 300, 20);
插入默认值和NULL值
默认值是在定义数据表时指定的值,如果未指定默认值DEFAULT选项,并且其NOT NULL没有被指定为True,那么将默认为NULL值。
可以使用隐式的默认值插入方法,即通过在INSERT语句中省略掉部分列值,自动使用默认值进行插入。
也可以显式地指定,如:
INSERT INTO emp
VALUES(7894,'霍十','',DEFAULT,'',NULL,NULL,20);
使用子查询插入多行数据
如:
INSERT INTO emp_copy
SELECT *
FROM emp
WHERE deptno = 20;
INSERT INTO emp_copy
(empno, ename, job, mgr, deptno)
SELECT empno, ename, job, mgr, deptno
FROM emp
WHERE deptno = 30;
使用INSERT-WHEN插入多表数据
如:
--此处的FIRST表示匹配到第一个WHEN以后跳过后面的WHEN
--如果是换成ALL,表示匹配每一个WHEN
INSERT FIRST
WHEN deptno = 10 --如果部门编号为10
THEN
INTO emp_dept_10 --则插入到emp_dept_10表
WHEN deptno = 20 --如果部门编号为20
THEN
INTO emp_dept_20 --则插入到emp_dept_20表
WHEN deptno = 30 --如果部门编号为30
THEN
INTO emp_dept_30 --则插入到emp_dept_30表
ELSE --如果deptno不为10、20或者是30
INTO emp_copy --则插入到emp_copy 表
SELECT *
FROM emp; --查询emp表中的所有数据,插入到目标表
还可以使用VALUES子句来为每一个表指定要插入的列,如:
INSERT FIRST
WHEN deptno = 10 --如果部门编号为10
THEN
INTO emp_dept_10 --插入到emp_dept_10,使用VALUES指定字段
(empno, ename, sal, deptno
)
VALUES (empno, ename, sal, deptno
)
WHEN deptno = 20 --如果部门编号为20
THEN
INTO emp_dept_20 --插入到emp_dept_20,使用VALUES指定字段
(empno, ename
)
VALUES (empno, ename
)
WHEN deptno = 30 --如果部门编号为30
THEN
INTO emp_dept_30 --插入到emp_dept_30,使用VALUES指定字段
(empno, ename, hiredate
)
VALUES (empno, ename, hiredate
)
ELSE --如果部门编号即不为10、20或30
INTO emp_copy --插入到emp_copy,使用VALUES指定字段
(empno, ename, deptno
)
VALUES (empno, ename, deptno
)
SELECT *
FROM emp; --指定插入子查询
更新记录
直接更新记录
如:
UPDATE emp SET sal=3000 WHERE empno=7369;
UPDATE emp SET sal=3000,comm=200,mgr=7566 WHERE empno=7369;
使用子查询更新记录
如:
UPDATE emp x
SET x.sal = (SELECT AVG (y.sal)
FROM emp y
WHERE y.deptno = x.deptno)
WHERE x.empno = 7369;
也可以使用非相关子查询:
UPDATE emp
SET sal = (SELECT sal
FROM emp
WHERE empno = 7782)
WHERE empno = 7369;
也可以同时更新多个列:
UPDATE emp x
SET (x.sal, x.comm) = (SELECT AVG (y.sal), MAX (y.comm)
FROM emp y
WHERE y.deptno = x.deptno)
WHERE x.empno = 7369;
使用MERGE合并表行
MERGE命令组合了INSERT和UPDATE命令,在对数据进行插入时,如果行存在,则执行UPDATE,如果是一个新的行,则执行INSERT。
如:
MERGE INTO emp_copy c --目标表
USING emp e --源表,可以是表、视图或子查询
ON (c.empno = e.empno)
WHEN MATCHED THEN --当匹配时,进行UPDATE操作
UPDATE
SET c.ename = e.ename, c.job = e.job, c.mgr = e.mgr,
c.hiredate = e.hiredate, c.sal = e.sal, c.comm = e.comm,
c.deptno = e.deptno
WHEN NOT MATCHED THEN --当不匹配时,进行INSERT操作
INSERT
VALUES (e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,
e.deptno);
删除记录
直接删除记录
如:
DELETE FROM emp;
DELETE FROM emp WHERE empno=7903;
使用子查询删除记录
如:
DELETE FROM emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE dname = '销售部');
DELETE FROM emp x
WHERE EXISTS (SELECT 1
FROM emp_copy
WHERE empno = x.empno);
DELETE FROM emp x
WHERE empno IN (SELECT empno
FROM emp_copy
WHERE empno = x.empno);
使用TRUNCATE清除记录
除了可以使用DELETE删除表数据以外,还可以使用TRUNCATE,该语句会删除表中所有行,并且释放该表所使用的存储空间。
如:
TRUNCATE TABLE dept;
TRUNCATE实际上不是DML语句,而是DDL语句,与CREATE TABLE等语句一样,它不具有撤销功能,一经调用,表中数据便彻底清除。
与DELETE相比,TRUNCATE速度要快一些,这是因为:
- TRUNCATE语句不会激活表的删除触发器。
- TRUNCATE语句属于DDL语句,不会产生撤销信息。