Oracle Database之数据表增删改

DML是Data Manipulation Language的缩写，DML语句主要用于数据库表、视图的操作。

1. 一般INSERT语句插入表数据

--语法：

INSERT INTO [schema.]table[@db_link][(column1[,column2]...)]]
[VALUES (expression1[,expression2]...)]|[subquery];

--参数说明：

TABLE: 要插入的表名；
DB_LINK: 数据库链接名；
Column1,Column2: 表的列名；
Expression: 表达式；
Subquery: 子查询语句，可以是任何合法的SELECT语句；
Values: 给出要插入的值列表；

示例1:

--创建表DWH_USER_INF
create table dwh_user_inf
(
	user_id number,user_num varchar2(20),user_name varchar2(30),nickname  varchar2(30),status  varchar2(10),gender  varchar2(1),birthday date,email varchar2(50),telephone varchar2(20),mobile varchar2(20),address varchar2(300),department_id number
);

--创建序列：(序列创建语法参考：Oracle Database之序列(Sequence))
create sequence DWH_USER_INF_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 99999999999999999999999999999
NOCYCLE
CACHE 50;

--用INSERT INTO....VALUES插入数据：

insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000001','david.tian','davide','Active','M',to_date('12-SEP-84','DD-MON-YY'),'david.tian@gmail.com','+86 510 8555 5555','13912345678','O Park,Xinhu District,Wuxi,Jiangsu,China',10);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000002','black.xie','black',to_date('12-AUG-85','black.xie@gmail.com','+86 510 8555 5554','13912345671','STK00000003','josen.zhang','josen',to_date('12-JUN-85','josen.zhang@gmail.com','+86 510 8555 5553','13912345672',11);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000004','kaishen.yang','kaishen',to_date('12-SEP-85','kaishen.yang@gmail.com','+86 510 8555 5552','13912345673',12);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000005','fab.yin','fab',to_date('12-SEP-88','fab.yin@gmail.com','+86 510 8555 5551','13912345674','STK00000006','klaus.he','klaus',to_date('12-SEP-86','klaus.he@gmail.com','+86 510 8555 5550','13912345675',14);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000007','alfred.li','alfred',to_date('11-SEP-84','alfred.li@gmail.com','+86 510 8555 5556','13912345676',13);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000008','tom.deng','tom',to_date('12-JAN-85','tom.deng@gmail.com','+86 510 8555 5557','13912345677','STK00000009','cindy.wang','cindy','F',to_date('02-FEB-86','cindy.wang@gmail.com','+86 510 8555 5558','13912345679','STK00000010','alvin.weng','alvin',to_date('12-MAR-87','alvin.weng@gmail.com','+86 510 8555 5559','13912345618',15);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000011','charles.du','charles',to_date('16-SEP-89','charles.du@gmail.com','+86 510 8555 5515','13912345628','STK00000012','chris.zhang','chris',to_date('12-NOV-88','chris.zhang@gmail.com','+86 510 8555 5525','13912345638',16);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000013','ben.liu','ben',to_date('22-SEP-85','ben.liu@gmail.com','+86 510 8555 5535','13912345648','STK00000014','simon.song','simon',to_date('12-APR-88','simon.song@gmail.com','+86 510 8555 5545','13912345658','STK00000015','light.chen','light',to_date('13-MAY-87','light.chen@gmail.com','+86 510 8555 5565','13912345668','STK00000016','katina.yang','katina','Inactive',to_date('20-JUN-97','katina.yang@gmail.com','+86 510 8555 5575','13912345688','STK00000017','luca.albricci','luca',to_date('11-AUG-83','luca.albricci@gmail.com','+86 510 8555 5585','13912345698','STK00000018','bluce.zheng','bluce',to_date('06-JUL-87','bluce.zheng@gmail.com','+86 510 8555 5595','13912345178',17);
insert into dwh_user_inf values (DWH_USER_INF_SEQ.NEXTVAL,'STK00000019','jackey.zhang','jackey',to_date('12-JUN-87','jackey.zhang@gmail.com','+86 510 8555 5155','13912342678','STK00000020','ekrn.dong','ekrn',to_date('16-APR-83','ekrn.dong@gmail.com','+86 510 8555 5255','13912343678','STK00000021','laura.pellegrini','laura',to_date('23-SEP-71','laura.pellegrini@gmail.com','+86 510 8555 5355','13910345678','STK00000022','ricky.he','ricky',to_date('12-JAN-82','ricky.he@gmail.com','+86 510 8555 5455','13982345678',18);

--创建表DWH_USER_INF_ACTIVE,结构与DWH_USER_INF一样：

--创建表DWH_USER_INF_ACTIVE，结构与DWH_USER_INF一样，不包含任何数据
CREATE TABLE DWH_USER_INF_ACTIVE
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;

--利用INSERT INTO....SUBQUERY方式插入数据：

INSERT INTO DWH_USER_INF_ACTIVE
SELECT *
FROM DWH_USER_INF
WHERE STATUS='Active';

2. 多表INSERT语句

INSERT [ALL] [condition_insert_clause]
[insert_into_clause values_clause] (subquery)
--其中，condition_insert_clause语法如下：
[ALL][FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]

--创建表DM_USER_INF_A,不包含数据，用于无条件插入
CREATE TABLE DM_USER_INF_A
AS
SELECT USER_ID,USER_NUM,USER_NAME,STATUS,EMAIL,DEPARTMENT_ID
FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_B,不包含数据，用于无条件插入
CREATE TABLE DM_USER_INF_B
AS
SELECT USER_ID,GENDER,TELEPHONE,ADDRESS
FROM DWH_USER_INF
WHERE 1=2;

--创建表DM_USER_INF_ACTIVE，不包含数据，用于有条件INSERT ALL
CREATE TABLE DM_USER_INF_ACTIVE
AS
SELECT USER_ID,DEPARTMENT_ID
FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_INACTIVE,不包含数据，用于有条件INSERT ALL
CREATE TABLE DM_USER_INF_INACTIVE
AS
SELECT USER_ID,ADDRESS
FROM DWH_USER_INF
WHERE 1=2;

--2.1 无条件INSERT ALL

INSERT ALL
INTO DM_USER_INF_A VALUES(USER_ID,DEPARTMENT_ID)
INTO DM_USER_INF_B VALUES(USER_ID,ADDRESS)
SELECT USER_ID,NICKNAME,BIRTHDAY,MOBILE,ADDRESS,DEPARTMENT_ID
FROM DWH_USER_INF;

--2.2 有条件的INSERT ALL

INSERT ALL
WHEN STATUS='Active' THEN
INTO DM_USER_INF_ACTIVE VALUES(USER_ID,DEPARTMENT_ID)
WHEN STATUS='Inactive' THEN
INTO DM_USER_INF_INACTIVE VALUES(USER_ID,DEPARTMENT_ID
FROM DWH_USER_INF ;

--2.3 有条件的FIRST INSERT

FIRST与ALL的区别在于：当遇到第一个求值为TRUE的语句之后停止对后面WHEN子句的求值，下面的例子中，如果第一个子句求值为TRUE，则其后的WHEN子句不会被执行，反之，则直到遇到第一个满足条件的子句为止：

--创建表DM_USER_INF_GENDER_M,不包含数据
create table DM_USER_INF_GENDER_M
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;

--创建表DM_USER_INF_GENDER_F,不包含数据
create table DM_USER_INF_GENDER_F
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_STATUS，不包含数据
create table DM_USER_INF_STATUS
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;
--创建表DM_USER_INF_OTHERS,不包含数据
create table DM_USER_INF_OTHERS
AS
SELECT * FROM DWH_USER_INF
WHERE 1=2;

--有条件的FIRST INSERT示例
INSERT FIRST
WHEN GENDER='M' THEN
          INTO DM_USER_INF_GENDER_M VALUES (USER_ID,DEPARTMENT_ID)
WHEN GENDER='F' THEN
         INTO DM_USER_INF_GENDER_F VALUES (USER_ID,DEPARTMENT_ID)
WHEN STATUS='Active' THEN
          INTO DM_USER_INF_STATUS VALUES (USER_ID,DEPARTMENT_ID)
ELSE
         INTO DM_USER_INF_OTHERS VALUES (USER_ID,DEPARTMENT_ID)
SELECT USER_ID,DEPARTMENT_ID
FROM DWH_USER_INF;

--数据验证：

SELECT 'DM_USER_INF_GENDER_M',x.*  FROM DM_USER_INF_GENDER_M x
UNION ALL
SELECT 'DM_USER_INF_GENDER_F',x.*  FROM DM_USER_INF_GENDER_F x
UNION ALL
SELECT 'DM_USER_INF_STATUS',x.*  FROM DM_USER_INF_STATUS x
UNION ALL
SELECT 'DM_USER_INF_OTHERS',x.*  FROM DM_USER_INF_OTHERS x

说明：

插入时是根据表中的每一行数据去匹配WHEN里面的条件，最先满足条件的执行插入操作，然后后面的条件不会再走，继续下一条判断与操作。

--2.4 UPDATE

给表DWH_USER_INF增加两列，salary和bonus:

alter table DWH_USER_INF  add (salary number(22,2),bonus number(22,2));

可以查看表DWH_USER_INF表，新增的两个字段值为空（NULL）：

下面通过随机数的方式为两个字段更新数据，有关生成随机数，参考教程Oracle DBMS_RANDOM包生成随机数：

--更新salary字段：
UPDATE DWH_USER_INF
SET 
salary=dbms_random.value(2500,15000)

--根据年龄更新bonus字段：
UPDATE DWH_USER_INF
SET 
bonus=(  CASE  
       WHEN FLOOR(MONTHS_BETWEEN(sysdate,birthday )/12)>=30 THEN
              salary*10
        WHEN FLOOR(MONTHS_BETWEEN(sysdate,birthday )/12)>=20 THEN
              salary*2
        ELSE
              salary*1
END) ;

--2.5 MERGE INTO

有关MERGE INTO的用法，请参考： ORACLE Database之MERGE INTO用法

--2.6 DELETE/TRUNCATE

--语法
--DELETE FROM [schema.]table_name [where_clause];

delete from DWH_USER_INF where status='Active'; --删除status='Active'的所有用户；
delete from DWH_USER_INF; --删除表中所有数据

删除表中所有数据的另一种更高效的方法是使用TRUNCATE:

TRUNCATE TABLE DWH_USER_INF; --清空表中所有数据；

引申： TRUNCATE和DELETE的区别：

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。 DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE,DELETE,DROP放在一起比较：
TRUNCATE TABLE：删除内容、释放空间但不删除定义。
DELETE TABLE:删除内容不删除定义，不释放空间。
DROP TABLE：删除内容和定义，释放空间。

--------------------------------------------------------------------------------------------------------

本文系原创，转载请表明出处！

如果您在尝试过程中遇到任何问题，请给予指正！

Oracle Database之数据表增删改

猜你在找的Oracle相关文章