ORACLE DML语句

ORACLE DML语句

DML

全称是Data Manipulation Language数据操纵语言 (update insert delete)

1、插入数据

--补充
select userenv('language') from dual;
--如果显示SIMPLIFIED CHINESE_CHINA.ZHS16GBK，一个汉字占用两个字节;  varchar2(10) 存五个汉字
--如果显示SIMPLIFIED CHINESE_CHINA.AL32UTF8，一个汉字占用三个字节. varchar2(9) 存三个汉字

--插入数据
/*
字段顺序可以不与表中的字段顺序一致
插入的值要对应字段
主键约束的字段必须插入且不能重复
非空约束的字段一定要有数据否则会报错
唯一约束的字段值不能重复
有默认值的字段可以不插入
可以为空的字段可以不插入
*/
Insert into EMP2 (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (1000,'SMITH','CLERK',7902,to_date('2002-06-03','yyyy-MM-dd'),800,null,20);

--插入部分数据
Insert into EMP2 (EMPNO,ENAME,JOB,SAL) values(1001,'jack','MANAGER',7800) 

2、修改数据

--注意不需要写from 没有where条件 则会修改所有
-- 修改员工姓名 
update emp2  set ename = '特朗普'  

--修改员工工资 和 绩效 
update emp2 set sal=5000,comm=200

--修改员工号为7369的姓名   
update emp2 set ename = '时间管理大师' where empno = 7369

--没有绩效的 加百分之10工资 
update emp2 set sal = sal*1.1 where  comm is null

3、删除数据

--删除 需要from关键字 没有where条件 则会删除所有
delete from emp2
--删除 员工工资等于2450的
delete from emp2 where sal = 2450
--删除 职位为CLERk的
delete from emp2 where job = 'CLERK'
--删除 姓名中包含F的
delete from emp2 where ename like '%F%'
--删除 1982年入职的
delete from emp2 where EXTRACT(year from hiredate) = 1982

--补充 delete drop truncate 的区别
/*
首先delete 属于DML，当不commit时是不生效的
而truncate 和 drop  属于DDL 则是直接生效的，不能回滚。
truncate 和 delete 不删除表的结构，只是针对表中的内容删除 
drop可以删除表结构，但被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态.
用truncate命令或者delete命令，但从本质上，如果数据很多，truncate的速度很快，delete则会很慢。
truncate不能加where条件
*/

4、序列

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。

其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

序列是一种数据库对象,用来自动生成一组唯一的序号.

序列是一种共享式的对象,多个用户可以共同使用序列中的序号.

一般将序列应用于表的主键列,这样,当向表中插入数据时,主键列就使用了序列的序号,从而保证主键不会重复.

用序列来产生主键,可以获得可靠的主键值.

一句话: 序列就是序号生成器!给主键生成数据

语法

--创建序列
create sequence seq_userid  
increment by 1  --递增数
start with 1  --开始数
maxvalue 99999 --最大值
nocycle  --不循环  ，cycle 循环
cache 10;  --缓存10个  ，nocache不缓存

--生成序列值
seq_name.nextval 
--查看序列值
seq_name.currval

--删除序列
drop sequence seq_userid

--查看当前用户下的所有序列：
select * from user_sequences;

5、插入&修改

merge into的用法

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)

WHEN MATCHED THEN

[UPDATE sql]

WHEN NOT MATCHED THEN

[INSERT sql]

作用:判断Ｂ表和Ａ表是否满足ON中条件，如果满足则用B表去更新A表，如果不满足，则将B表数据插入A表但是有很多可选项，如下:

--1.正常模式
create table stu_a(
cid number,
cname varchar2(10)
);

create table stu_b(
cid number,
cname varchar2(10),
age number(2)
);

insert into stu_a values(1, 'a1111');
insert into stu_a values(2, 'a2222');
insert into stu_a values(5, 'a2225');

insert into stu_b values(1, 'b1111', 25);
insert into stu_b values(2, 'b1112', 26);
insert into stu_b values(3, 'b1113', 27);

select * from stu_a;
select * from stu_b;


--语法 
merge into 目标表 a  using   对比表 b on (条件)
when matched then --条件成立
sql
when not matched then --条件不成立
sql

--使用merge into用B_MERGE来更新&新增A_MERGE中的数据:

merge into stu_a a using (select cid,cname,age from stu_b) b on (a.cid=b.cid)
when matched then
  update set a.cname=b.cname
when not matched then
  	insert(a.cid,a.cname) values(b.cid,b.cname);

--2.只update或者只insert
	merge into stu_a a using (select cid,cname,age from stu_b) b on (a.cid=b.cid)
when matched then
  	update set a.cname=b.cname;

merge into stu_a a using (select cid,cname,age from stu_b) b on (a.cid=b.cid)
when not matched then
  	insert(a.cid,a.cname) values(b.cid,b.cname);
	commit;
	
--3.带条件的update或带条件的insert
insert into stu_b values(4, 'b1114', 27);
merge into stu_a a using (select cid,cname,age from stu_b) b on (a.cid=b.cid)
when matched then
  update set a.cname=b.cname where b.cid=2
when not matched then
  insert(a.cid,a.cname) values(b.cid,b.cname) where b.cid=3;

--4.全插入insert实现
	--有时我们需要将一张表中所有的数据插入到另外一张表，此时就可以添加常量过滤谓词来实现，让其只满足匹配和不匹配，这样就只有update或者只有insert。这里我们要无条件全插入，则只需将on中条件设置为永假
merge into stu_a a using (select cid,cname,age from stu_b) b on (1=2)
when not matched then
  insert(a.cid,a.cname) values(b.cid,b.cname);