Oracle Database之数据表增删改

前端之家收集整理的这篇文章主要介绍了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:删除内容和定义,释放空间。

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

本文系原创,转载请表明出处!

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

原文链接:https://www.f2er.com/oracle/211407.html

猜你在找的Oracle相关文章