加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle Database之数据表增删改

发布时间：2020-12-12 15:36:43 所属栏目：百科 来源：网络整理
导读：DML是Data Manipulation Language的缩写，DML语句主要用于数据库表、视图的操作。 1. 一般INSERT语句插入表数据 --语法： INSERT INTO [schema.]table[@db_link][(column1[,column2]...)]][VALUES (expression1[,expression2]...)]|[subquery]; --参数说明：

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：删除内容和定义，释放空间。
    •  

 

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

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

如果您在尝试过程中遇到任何问题，请给予指正！
                        
（编辑：李大同）


【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

                    		
                  

                

            

            
    
            
    
            
    
            	
    相关内容
    

      

            
      
        
      
    
      
    
      
    	
      
        
      推荐文章
      
            
        
        
        
    	
        
        
        站长推荐
        
            
        
        
        
        
        
    	
        
        
        热点阅读
        
            
          
        
          
        
          
        
          
	
          

          

          

          

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。
          

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果
          

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同