Oracle学习笔记 -- day06 DDL、DML、视图索引序列、数据导入导出、数据恢复

一、使用DDL语句管理表

1、新用户操作数据库的步骤

（1）、创建表空间，同时把数据文件也创建

（2）、创建用户  user

（3）、赋权限

（4）、创建表

（5）、操作数据库

2、创建表空间

注意：创建表空间需要用管理员身份登录

--创建表空间，同时把数据文件也创建
create tablespace heima_50_space   --指定表空间的名称heima_50_space
datafile 'C:\\heima_50.dbf'        --创建数据文件
size 100M                          --给数据文件指定大小
autoextend on                      --给数据文件设置自动增长
next 10M                           --自动增长大小

3、创建用户

--创建用户  user
create user heima_50               --创建用户  heima_50 用户名
identified by heima_50             --给用户指定密码
default  tablespace heima_50_space --用户默认操作的表空间

4、给用户赋权限

（1）、查看当前用户权限

select * from session_privs

（2）、Oracle数据库提供的3中权限角色

dba

resource

connect

（3）、给用户赋予角色

grant dba to heima_50 --把dba的角色赋给heima_50

5、Oracle中的数据类型

（1）、字符型

特点：长度没有默认值

char         固定长度   最大长度255          --char

varchar2     不固定长度 最大长度3999        --varchar

（2）、数值型

特点： 默认长度是8

number   number(3) 999  number(3,2) 999.99错！  9.99  --总长度不包括小数点和负号

（3）、日期型

特点： 带有时分秒，mysql中的date  只有年月日

date  --相当于mysql中datetime  有时分秒

timestamp--精度高  最多可以保留到秒后9位小数

（4）、大数据类型 --（了解）

long --支持2G  相当于mysql中longtext

clob --支持4G 

blob --支持4G 

6、约束

primary key：主键约束

not null ：非空约束

unique ：唯一约束

check ：检查约束

7、创建表

（1）、普通建表语句

create table person(
pid number(5)  ,       --唯一 并且不为空
pname varchar2(30) not null,
gender number(1) ,      -- 0女   1男  sex
tele varchar2(11),
constraint pk_pid primary key(pid),
constraint check_gender check (gender in(0,1)),
constraint unique_key unique(tele)
)

（2）、快速建表语句

create table emp as select * from scott.emp
create table dept as select * from scott.dept
select * from emp;
select * from dept;

（3）、创建表，指定导入某一时间点上的数据

create table dept_bak 
    as 
  select * from dept 
    as of 
    TIMESTAMP to_timestamp('20081126 103435','yyyymmdd hh24miss');

8、外键

create table orderdetail( --订单项表
odid number primary key,
price number(8,2),
name varchar2(30),
oid number(5),
constraint fk_orderdetail_orders foreign key(oid) references  orders(oid)
)

insert into orders values(1,900);
insert into orders values(2,1000);
commit;
select * from orders

insert into orderdetail(odid,oid) values (1,1)
insert into orderdetail(odid,oid) values (2,2)

9、修改表

--给person表添加address列
alter table person add(address varchar2(30))
alter table person modify(address varchar2(300))

二、使用DML语句处理数据

1、删除表

        如果是create创建出来的 都是用drop删除

drop table person

2、删除数据

delete from orders where oid=1;

delete from person   --删除全部数据
select * from person
truncate --删除全部数据  效果=delete from person 
truncate table person

注意：delete 和 truncate 区别

（1）、delete只是删除数据而truncate是摧毁表后重新建表

（2）、delete删除的数据可以回滚，truncate不可回滚

（3）、delete会产生磁盘碎片,truncate不会产生磁盘碎片

3、修改数据

        update  表名 set 字段1=值, 字段2=值  where 条件

--给NEW YORK的员工涨100元工资
1、查询是NEW YORK地区的员工

查询在NEW YORK的部门，哪些员工是NEW YORK地区部门的
select * from emp where deptno 
in(select deptno from dept where loc='NEW YORK')
2、执行更新操作
update  emp set sal=sal+100  where  deptno 
in(select deptno from dept where loc='NEW YORK')

4、oracle中的事务

 insert  update  delete  --涉及事务

savepoint  事务保存点

 insert into person values(1,'TOM',1,'1389273',null);
 savepoint a;
 update person set pname='JRREY' where pid=1;
 savepoint b;
 delete from person where pid=1;
 rollback to b;

三 、视图索引序列

1、视图（重点）

（1）、什么是视图：

        一个虚表，是数据库表中数据的引用。

        如果对视图进行修改，对应的表中的数据也将会被修改

        创建视图的时候，加上只读，即可避免该问题

（2）、视图的作用

        可以封装复杂的sql查询语句

        可以隐藏敏感列

（3）、视图的创建语法

create view 视图名 as sql查询语句

（4）、示例

--封装复杂sql
create view view_total as 
(select sum(t.counts) "Total" ,
 sum(decode(years,'1980',counts)) "1980",
 sum(decode(years,'1981',counts)) "1981",
 sum(decode(years,'1982',counts)) "1982",
 sum(decode(years,'1987',counts)) "1987"
  from (select to_char(hiredate,'yyyy') years,count(*) counts
 from emp group by to_char(hiredate,'yyyy')) t)

select * from view_total;

-- 隐藏敏感数据
create view view_emp as select empno,ename,job,mgr,hiredate,deptno from emp

select e.*,d.dname from view_emp e,dept d where e.deptno=d.deptno;


---只读视图    with read only
create or replace view view_emp as 
select empno,ename,job,mgr,hiredate,deptno from emp 
 with read only

2、序列

（1）、序列的作用

        主要是用来做主键自增的，他是独立于表之外的对象

（2）、序列的简单语法以及用法

--创建简单语法：
create sequence 序列名称

create sequence seq_person; -- 创建序列
select seq_person.nextval from dual -- 查看下一个序列值
select seq_person.currval from dual -- 查看当前序列值

insert into person(pid,pname) values(seq_person.nextval,'TOM'); -- 根据序列插入数据
select * from person
insert into orders(oid) values(seq_person.nextval);

（3）、序列的复杂语法及用法 -- 了解内容

-- 创建序列
create sequence seq_test
minvalue  1     --默认最小值1
increment by 1  --默认是1，每次增长的长度
start with 4    --默认是1，起始值
maxvalue  15    --默认值是 18个9  999999999999999999
cycle           --循环  默认  nocycle
cache 6         --缓存  默认会缓存20
5 7 9 11 13 15 3 5 7 9 11 13 15 3 5 7 9 11 13 15 
insert into orders(oid) values(seq_test.nextval);
commit;
select seq_test.nextval from dual 

3、索引

（1）、什么是索引

        索引相当于一本书的目录，是独立的对象

（2）、索引的作用

        提高查询效率

（3）、使用索引的规则

        如果表中的数据经常被修改，不合适创建索引

        数据量小时不用建索引

        某些字段不会被当成条件做查询时，没有必要建索引

（4）、索引的语法

语法：
create index 索引名称 on 表名(字段名)；

（5）、示例：从5000000条数据中查询

create table t_test(
tid number,
tname varchar2(30)
)

begin
for i in 1..5000000
  loop
    insert into t_test values(i,'测试数据'||i);
  end loop;
end;

select * from t_test where tname='测试数据4000000'   --0.734s

create index index_test on t_test(tname)

select * from t_test where tname='测试数据4560000'   --0.062s

（6）、如何提高sql的查询效率

        创建索引

        使用子查询 

        经常出现的列尽量做到同一个表中

        使用分区

（7）、复合索引（了解）

create index index_emp_ename_job on emp(ename ,job)
select * from emp where job='' and ename=XX

四、数据的导入导出

1、按照用户导入导出

（1）、将scott用户下的所有数据导出

exp scott/tiger@192.168.204.10:1521/orcl file=c:\scott.dmp

（2）、将scott用户导出的数据导入自定义的heima_50用户下

imp heima_50/heima_50@192.168.204.10:1521/orcl file=c:\scott.dmp full=y

注意：使用 exp 和 imp 命令的前提是本机必须安装oracle软件，并且是在doc窗口中输入命令

2、按照表的导入导出

（1）、导出

exp heima_50/heima_50 file=heima_50.dmp tables=t_demp,t_emp; 

（2）、导入

imp heima_50/heima_50 file=heima_50.dmp tables=t_demp,t_emp; 

五、恢复数据库中表数据

1、恢复数据到某一时间点

1.启动行移动功能
alter table T_STANDARD enable row movement;
2.进行闪回操作
flashback table T_STANDARD to timestamp to_timestamp('2017-09-05 08:45:00','yyyy-mm-dd hh24:mi:ss');