数据库笔记2(oracle DML、事务、视图、索引、序列)
创建保存点
SQL> savepoint p16_00;
保存点已创建。
通过查询创建表
create table my_emp
as
select id,last_name,start_date
from s_emp
where dept_id=41;
插入数据
insert into my_emp(id,last_name,start_date)
values(10,'aaa',to_date('2000-01-01','yyyy-mm-dd'));
更新数据
update my_emp set
last_name='bbb',
start_date=to_date('1988-08-08','yyyy-mm-dd')
where id=10;
把s_emp中的数据插入到my_emp
insert into my_emp(id,last_name,start_date)
select id,last_name,start_date
from s_emp;
事务的特点：原子性(一组sql操作)、一致性()、隔离性()、持久性()
创建序列
create sequence my_seq
increment by 1
start with 10
maxvalue 1000
nocycle
nocache;
desc user_sequences;
查询序列的最大值
select max_value
from user_sequences
where sequence_name='MY_SEQ';
查询序列的下一个值
select my_seq.NEXTVAL from dual;
查询序列的当前值
select my_seq.currval from dual;
insert into s_dept(id,name)
values(my_seq.nextval,'&zhang'||my_seq.nextval);
创建视图
create view myview
as
select id,last_name,start_date
from s_emp
where dept_id=45;
注：对简单视图的DML操作会影响到此视图所指向的表
复杂视图无法作DML操作
create view myview2
as
select e.last_name ename,d.name dname
from s_emp e,s_dept d
where e.dept_id=d.id;
--drop view myview2;
delete from myview2 where dname='&dname';
--将表s_ord重命名为s_order
rename s_ord to s_order
--删除表的数据，同时降低水位线，不能回滚
truncate table s_item
--给表添加注释
comment on table s_emp is 'Employee Information';
--创建序列
create sequence s_dept_id
increment by 1
start with 51
maxvalue 9999999
nocache
nocycle
--查询序列信息
select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences;
select s_dept_id.nextval
from sys.dual;
--利用序列向表中插入值
insert into s_dept(id,name,region_id)
values(s_dept_id.nextval,'Finance',2);
--修改序列
alter sequence sequence
[increment by n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}]
--删除序列
drop sequence s_dept_id;
--创建视图
create [or replace][force | noforce] view view_name [(alias[,alias]...)]
as subquery
[with check option[constraint constraint]]
[with read only]
--创建索引
create index index_name
on table(column[,column]...);
--创建用户
create user scott identified by tiger;
--给用记授权
grant create table,create sequence,create view to scott;
--修改密码
alter user scott identified by
--授权
grant object_priv[(columns)]
on object
to {user|role|public}
[with grant option];
grant select
on s_emp
to sue,rich;
grant update(name,region_id)
on s_dept
to scott,manager;
grant select
on s_emp
to scott
with grant option;
grant select
on s_ord_id
to public ;
revoke select ,insert
on s_dept
from scott;
--创建同义词
create synonym s_dept
for alice.s_dept;
create synonym d_sum
for dept_sum_vu;
--删除同义词
drop synonym s_dept;
--导入导出
sqlplus 'as sysdba'
create user scott identifed by tiger;
grant resource,connect to scott;
imp useid=briup/briup full=y file=briup.dmp
exp userid=briup/briup full=y file=inner_notify.dmp