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]
作用:判断B表和A表是否满足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);