zlinks
文章
50
分类
6
评论
1

Oracle基本DML语句

| Oracle •
字数总计 23210 | 阅读时长 58分钟 | 阅读量 23

插入数据

语法：

INSERT INTO table_name --声明表名
    (column_1, column_2, column_3, ...column_n) --声明要插入数据的列名
VALUES (value_1, value_2, value_3, ..., value_n);  --要插入列对应的数据

如果要插入数据的值顺序跟列顺序一致，可以不用声明列明，可以这样用但是不建议

INSERT INTO table_name
VALUES (value_1, value_2, value_3, ..., value_n);

1、直接插入数据

实例：创建表students，往里面插入数据

create table students
(
    s_id       number       not null,
    s_birtnday date,
    s_grande   number(4, 1),
    s_name     varchar2(10) not null,
    primary key (s_id)
);
insert into students (s_id, s_birtnday, s_grande, s_name)
VALUES (20180101, date '2015-01-05', 75.8, '张三');
insert into students
VALUES (20180102, date '2014-01-05', 75.8, '李四');

2、从其他表提取数据插入

我们想将STUDENTS_INDEX表的基本信息和STUDENTS_GRANDE表的语文成绩插入到stundets表中，先看下数据

select *
from STUDENTS_INDEX;

select *
from STUDENTS_GRANDE;

insert into students
select t.S_ID, t.S_BRITHDAY, a.CHINESE, t.S_NAME
from STUDENTS_INDEX t,
     STUDENTS_GRANDE a
where t.S_ID = a.S_ID(+);

我们看下结果

select *
from students;

将多行数据插入到多表（Oracle INSERT ALL）

无条件插入

语法：

INSERT ALL
    INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

实例：

首先我们创建三张表

create table student_information--学生基本信息
(
    s_id       number       not null,
    s_name     varchar2(10) not null,
    s_birtnday date,
    s_grande   number(4, 1),
    primary key (s_id)
);
create table student_family--学生家庭信息
(
    s_id    number       not null,
    s_name  varchar2(10) not null,
    s_adder varchar2(20),
    primary key (s_id)
);
create table student_class--学生班级信息
(
    s_id    number       not null,
    s_name  varchar2(10) not null,
    s_calss varchar2(20),
    primary key (s_id)
);

接下来使用 INSERT ALL将多行数据插入这三张表

INSERT ALL
     INTO STUDENT_INFORMATION(S_ID,S_NAME,S_BIRTNDAY,S_GRANDE)values (20170201,'孙悟空',date '2015-02-01',85)
    INTO STUDENT_FAMILY(S_ID,S_NAME,S_ADDER) VALUES(20170201,'孙悟空', '花果山')
    INTO STUDENT_CLASS(S_ID,S_NAME,S_CALSS) VALUES(20170201,'孙悟空', '三年级二班')
select 1 from dual;
有条件插入

根据规定好的条件数据插入多个表中

INSERT [ ALL | FIRST ]
    WHEN condition1 THEN
        INTO table_1 (column_list ) VALUES (value_list)
    WHEN condition2 THEN 
        INTO table_2(column_list ) VALUES (value_list)
    ELSE
        INTO table_3(column_list ) VALUES (value_list)
Subquery

Oracle INSERT ALL示例

INSERT ALL
    WHEN S_ID > 20180101 THEN
    INTO STUDENT_INFORMATION(S_ID, S_NAME)
    WHEN S_ID = 20180101 THEN
    INTO STUDENT_FAMILY(S_ID, S_NAME)
    ELSE
    INTO STUDENT_CLASS(S_ID, S_NAME)
select S_ID, S_NAME
from STUDENTS_INDEX

Oracle INSERT FIRST示例

INSERT first
    WHEN S_ID > 20180101 THEN
    INTO STUDENT_INFORMATION(S_ID, S_NAME)
    WHEN S_ID = 20180101 THEN
    INTO STUDENT_FAMILY(S_ID, S_NAME)
    ELSE
    INTO STUDENT_CLASS(S_ID, S_NAME)
select S_ID, S_NAME
from STUDENTS_INDEX;
Oracle INSERT ALL限制

Oracle多表插入语句受以下主要限制：

  • 它可以用来将数据只插入到表中，而不是视图或物化视图。

  • 它不能用来将数据插入到远程表中。

  • 所有INSERT INTO子句中的列总和不得超过999。

  • 一个表集合表达式不能在一个多表插入语句中使用。

  • 多表插入语句的子查询不能使用序列。

删除数据

语法：

DELETE
FROM
    table_name
WHERE
    condition;

单表删除

我们还是看studnets表

删除出生于2014年12月1日之前的数据

delete
from students
where S_BRITHDAY <= to_date('2014-12-01', 'yyyy-mm-dd');

看下结果

如果不带where条件，则是删除表中所有数据。

多表删除

这里就需要借助外键来完成，首先创建一个students1表，使用students的s_id作为外键，

CREATE TABLE STUDENTS1
(
    s_id    varchar2(20),
    s_calss varchar2(10),
    s_adder varchar2(20),
    CONSTRAINT FK_STUDENTS1
        FOREIGN KEY (s_id)
            REFERENCES STUDENTS (s_id)
                ON DELETE CASCADE
);

向表中插入数据

接下来我们删除students表中s_adder为”银河星系三里屯“的人员

delete
from students
where S_ADDER = '银河星系三里屯';

再看下两张表中的数据

通过外键，当students表中的数据被删除时，对应students1表对应s_id的数据也会被删除

合并数据

Oracle MERGE语句从一个或多个源表中选择数据并更新或将其插入到目标表中。 MERGE语句可指定一个条件来确定是更新数据还是将数据插入到目标表中。

语法

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        [DELETE WHERE <delete_condition>]
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

解析：

  • 首先，指定要在INTO子句中更新或插入的目标表(target_table)。

  • 其次，指定要更新或插入USING子句中的数据源(source_table)。

  • 第三，指定合并操作在ON子句中更新或插入的搜索条件。

对于目标表中的每一行，Oracle都会评估搜索条件：

  • 如果结果为true，则Oracle使用源表(source_table)中的相应数据更新该行。

  • 如果任何行的结果为false，则Oracle将源表(source_table)中相应的行插入到目标表(target_table)中。

示例：

创建表student_information和STUDENTS_INDEX


  CREATE TABLE STUDENTS_INDEX
(
    "S_ID"       VARCHAR2(10) primary key,
    "S_NAME"     VARCHAR2(10),
    "S_ID_NO"    VARCHAR2(20),
    "S_ADDER"    VARCHAR2(20),
    "S_SEX"      NUMBER(2, 0),
    "S_BRITHDAY" DATE
);
create table STUDENTS_INFORMATION as
select *
from STUDENTS_INDEX
where 1 = 2;

插入数据

--插入表STUDENTS_INDEX 
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180101', '赵一', '111111201507011021', '银河星系三里屯', 1, TIMESTAMP '2015-07-01 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180102', '钱二', null, '银河星系火箭村', 0, TIMESTAMP '2015-12-18 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180103', '孙三', null, '银河星系逍遥镇', 1, TIMESTAMP '2015-07-04 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180104', '李四', null, '银河星系八达里', 0, TIMESTAMP '2014-10-07 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180105', '王五', null, '银河星系三里屯', 1, TIMESTAMP '2015-01-02 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180106', '周六', null, '银河星系火箭村', 1, TIMESTAMP '2016-11-03 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180107', '田七', null, '银河星系三里屯', 0, TIMESTAMP '2015-11-04 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20190101', '鲁智深', '111111201507011021', '银河星系八大街', 1, TIMESTAMP '2014-07-04 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20190102', '武松', null, '银河星系景阳冈', 0, TIMESTAMP '2014-09-18 20:04:36');
INSERT INTO STUDENTS_INDEX (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20190103', '吴用', null, '银河星系梁山泊', 1, TIMESTAMP '2014-12-04 20:04:36');
--插入表STUDENTS_INFORMATION 
INSERT INTO STUDENTS_INFORMATION (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180101', '赵一', '111111201507011021', '银河星系三里屯', 1, TIMESTAMP '2015-07-01 20:04:36');
INSERT INTO STUDENTS_INFORMATION (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180102', '钱二', null, '银河星系火箭村', 0, TIMESTAMP '2015-12-18 20:04:36');
INSERT INTO STUDENTS_INFORMATION (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180103', '狗剩', null, '银河星系逍遥镇', 1, TIMESTAMP '2015-07-04 20:04:36');
INSERT INTO STUDENTS_INFORMATION (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180104', '李四', null, '银河星系八达里', 0, TIMESTAMP '2014-10-07 20:04:36');
INSERT INTO STUDENTS_INFORMATION (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180105', '王麻子', null, '银河星系三里屯', 1, TIMESTAMP '2015-01-02 20:04:36');
INSERT INTO STUDENTS_INFORMATION (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180106', '周六', null, '银河星系火箭村', 1, TIMESTAMP '2016-11-03 20:04:36');
INSERT INTO STUDENTS_INFORMATION (S_ID, S_NAME, S_ID_NO, S_ADDER, S_SEX, S_BRITHDAY) VALUES ('20180107', '田七', null, '银河星系三里屯', 0, TIMESTAMP '2015-11-04 20:04:36');

需要将表student_information中的记录插入或者更新到STUDENTS_INDEX，如果存在相同s_id，进行更新，没有则进行插入

merge into STUDENTS_INFORMATION t1
using (select t.S_ID, t.S_NAME, t.S_ID_NO, t.S_ADDER, t.S_SEX, t.S_BRITHDAY
       from STUDENTS_INDEX t) t2
on (t1.S_ID = t2.S_ID)
when matched then
    update
    set t1.S_NAME=t2.S_NAME,
        t1.S_ID_NO=t2.S_ID_NO,
        t1.S_ADDER=t2.S_ADDER,
        t1.S_SEX=t2.S_SEX,
        t1.S_BRITHDAY=t2.S_BRITHDAY
    where t1.S_NAME <> t2.S_NAME
      and t1.S_SEX <> t2.S_SEX
      and t1.S_BRITHDAY <> t2.S_BRITHDAY
when not matched then
    insert (t1.S_ID, t1.S_NAME, t1.S_ID_NO, t1.S_ADDER, t1.S_SEX, t1.S_BRITHDAY)
    values (t2.S_ID, t2.S_NAME, t2.S_ID_NO, t2.S_ADDER, t2.S_SEX, t2.S_BRITHDAY)

我们看下STUDENTS_INFORMATION 的数据

查询数据

select语句

SELECT
  column_1, 
  column_2, 
  ...
FROM
  table_name;

排序

SELECT
    column_1,
    column_2,
    column_3,
    ...
FROM
    table_name
ORDER BY
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],

其中asc表示升序，desc表示降序，查询默认asc升序

按照单列排序

select * from STUDENTS_INDEX order by S_ID;

按照多列排序

select * from STUDENTS_INDEX order by S_NAME,S_BRITHDAY;

按照列位置排序

select s_id, s_name, s_id_no, s_adder, s_sex, s_brithday
from STUDENTS_INDEX
order by 2 asc, 6 desc

使用函数或者表达式排序

select s_id, s_name, s_id_no, s_adder, s_sex, s_brithday
from STUDENTS_INDEX
order by length(S_NAME);

使用s_name的列长度排序

条件查询

SELECT
    column_1,
    column_2,
    ...
FROM
    table_name
WHERE
    search_condition
ORDER BY
    column_1,
    column_2;

运算符

运算符

描述

=

等于

!=、<>

不等于

>

大于

<

小于

>=

大小等于

<=

小于等于

in

等于in后面的任何值

not in

不等于in后面的任何值

between n and m

在n和m之间

not exists

如果子查询返回至少一行，则返回true

is null

为空

is not null

不为空

any/some/all

将值与列表或子查询进行比较。它必须以另一个运算符(例如：=，>，<)作为前缀。

and

AND运算符是一个逻辑运算符，它组合了布尔表达式，如果两个表达式都为真，则返回true。 如果其中一个表达式为假，则AND运算符返回false。

必须满足and连接的任何条件才为true，否则为false

or

只要满足or连接的多个多个条件中的任意一个条件就为true，如果where字句中条件较多，建议将or条件括号起来，以免造成歧义

in

in跟or的理解一致，但是写法上面更加简洁

expression [NOT] IN (v1,v2,...)

between and

一般用于number或者date类型

expression [ NOT ] BETWEEN low AND high

like

通配符%

匹配零个或多个字符的任何字符串。

--查询姓李的信息
select s_id, s_name, s_id_no, s_adder, s_sex, s_brithday
from STUDENTS_INDEX
where S_NAME like '李%'

通配符_

匹配任何单个字符。

select s_id, s_name, s_id_no, s_adder, s_sex, s_brithday
from STUDENTS_INDEX
where s_id like '201_0_02';

ESCAPE字符转义符

如果条件的列中包含通配符，则可以使用ESCAPE

源数据

select s_id, s_name, s_id_no, s_adder, s_sex, s_brithday
from STUDENTS_INDEX
where S_NAME like '李%/%%' escape '/';

ps：第一个和最后一个%为通配符，第二个%为escape 转义的普通字符串

正则表达式

文章作者： zlinks
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ZFS的成长之路！
Oracle Oracle基本语法
喜欢就支持一下吧