Oracle笔记之DML

DML（Data Manipulation Language 数据操控语言）用于
操作数据库对象中包含的数据，也就是说操作的单位是
记录。

DML 的主要语句（操作）

语句	作用
Insert	向数据表张插入一条记录
Delete	删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作对象仍是记录
Update	用于修改已存在表中的记录的内容

使用场景：

insert	注册
update	修改密码
delete	退出、删除、剔除会员
select	登录

1. 序列

使用工具|程序管理流水号，序列在创建时 没有与表关联
,在操作数据时与表关联

1.1. 创建

create sequence 序列名 start with 起始值 increment by 步进;
create sequence seq_tb_user start with 2 increment by 2;

1.2. 使用

在操作数据 添加 更新 -->主键
1)、currval :当前值
2)、nextval:下个值
select seq_tb_user.nextval from dual;
select seq_tb_user.currval from dual

1.3. 删除

drop sequence 序列名;
drop sequence seq_tb_user;

2. insert

insert into 表名 [(字段列表)] values(值列表);
添加记录

2.1. 添加记录时需要满足一下条件

• 类型 长度 兼容: 字段 兼容值
• 值满足约束 :主键 (唯一+非空) 非空(必填) 唯一(不重复) 默认(没有填写使用默认值) 检查(满足条件) 外键(参考主表主键列的值)
• 个数必须相同: 指定列，个数顺序与列相同；没有指定，个数与表结构的列个数和顺序相同 (null也得占位，没有默认值)

2.2. 添加数据

insert into 表名 values(和表结构顺序和个数和类型一致的数据，可以手写也可以从别的表中获取的);
insert into 表名(指定列名) values(和指定的列个数、顺序、类型一致的列数据)

eg:

insert into 表(指定列) select 查询列 from 源表 where 过滤数据; 
insert into 表(指定列) values(值列表);
insert into 表名 select 查询列 from 源表 where 过滤数据; 
insert into 表名 values(值列表 );

2.3. 操作实例

创建序列： 一般为一张表准备一个序列
create sequence seq_user_id increment by 1
start with 1 ; create sequence seq_txt_id
increment by 1 start with 1 ;
准备表：

1. 添加所有列

--没有列|所有列 所有字段,值个数必须为7个，没有默认值，非空可以使用null占位
insert into tb_user values 
(seq_user_id.nextval, 'test', 'test123',null, '女', null, sysdate);

2. 添加指定列(推荐: 方便阅读操作、更改顺序、非空列
   和默认值列 可以选填)

--指定所有列
insert into tb_user
(username, userid, userpwd, gender, age,email, regtime)
values
('shsxt',seq_user_id.nextval,'verygood','男',27,'bjsxt@qq.com.cn',sysdate);
--指定部分列(非空列和默认值列 可以选填, 必填项|主键列
必须指定 ,没有指定列 如果存在默认值，使用默认值填充，否则null填充)
insert into tb_user
(username, userid, userpwd)
values
('shsh', seq_user_id.nextval, 'shanghai');

commit;

insert into tb_user
(username, userid, userpwd, age)
values
('穷屌丝男', seq_user_id.nextval, '极品女士',20);

commit;

3. 添加外键

--添加同时查询 :使用一条sql 查询外键 ,直接添加 (少用，外键只有一个值，多个值运行错误)一条sql搞定
insert into tb_txt
(txtid, title, userid)
values
(seq_txt_id.nextval,'iphone6来了',(select userid from tb_user where username= '穷屌丝男'));
--先查询后添加 :
--查询值
select userid from tb_user where username='穷屌丝男';
--添加
insert into tb_txt(txtid,title,userid)
values(seq_txt_id.nextval,'您的肾值钱吗?',10);
commit;

4. 添加时间

--添加时间
insert into tb_user
(username, userid, userpwd, age, regtime)
values
('java程序猿',seq_user_id.nextval,'bjsxt',20,to_date('2014-10-31', 'yyyy-mm-dd'));

commit;

2.4. 查看数据

select * from tb_user;
select * from tb_txt;

从已有表中添加数据

select * from emp_his;
insert into emp_his(empno, ename, job) 
select empno, ename, job from emp where sal > 2000;

rollback;

select * from emp_his;
insert into emp_his
	select empno, ename, job, mgr, hiredate,sal, comm, deptno 
	from emp 
	where deptno = 20;

3. update

update 表名 set 字段=值 [,....] where 过滤行记录;

要求：

1. 记录存在
2. 类型 长度 兼容: 字段 兼容值
3. 个数相同

3.1. 更改数据

从已有表中查询数据更改字段值：

update 表名
set (字段列表) = 
(select 字段列表 from 源表 where 过滤源表记录)
where 更新记录的条件手动更改字段值：

3.2. 操作实例

eg:手动更改字段值

select * from tb_user;
--重置所有人员的密码 8888
update tb_user set userpwd=8888 where 1=1;
--修改 shsxt 的密码 和年龄
update tb_user set userpwd='good',age=29 
where username='shsxt' and userpwd='verygood';

commit;

eg:从表中获取

--了解 将用户名与密码对换
update tb_user set (username, userpwd) =
	(select userpwd, username from tb_user where userid = 6)
where userid = 6;
--select 只能返回一行数据:
update tb_user set (username, userpwd) =
	(select userpwd, username from tb_user)
where userid = 6;

4. delete

delete [from] 表名 where 过滤行记录
说明：

1. delete 可以删除指定部分记录，删除全部记录
2. 记录上存在主外键关联时， 删除存在关联的主表的
   记录时,注意 参考外键约束， 约束强制不让删除先删
   除从表 再删除主表

--删除全部数据
delete from tb_user where 1=1;
--删除指定数据
delete from tb_user where userid<10;
--主外键关联时，注意 参考约束， 约束强制不让删除
--默认先删除从表 再删除主表
delete from tb_txt where 1=1;
delete from tb_user where 1=1;
commit;
--删除主表数据,并设置从表数据为null
--删除主表数据,级联删除从表数据

5. 截断数据

truncate: 截断所有的数据 ,如果截断的是主表，结构不能存在外键关联，截断数据同时从结构上检查

create table emp_his as select * from emp where 1=1;
select * from emp_his;
--截断所有的数据
truncate table emp_his;
--不能截断: truncate table dept;

截断数据与删除数据区别 truncate 与delete 区别

1、truncate -->ddl ，不涉及事务，就不能回滚
delete -->dml ,涉及事务，可以回滚
2、truncate 截断所有的数据 delete 可以删除全部 或者部分记录
3、truncate从结构上检查是否存在主外键，如果存在，不让删除
delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。

测试：

truncate table tb_user;
delete from tb_user;