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

一、使用DDL语句管理表

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

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

（2）、创建用户  user

（3）、赋权限

（4）、创建表

（5）、操作数据库

2、创建表空间

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

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

3、创建用户

--创建用户  usercreate 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男  sextele 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.empcreate table dept as select * from scott.deptselect * 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 ordersinsert 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 persontruncate --删除全部数据  效果=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）、示例

--封装复杂sqlcreate 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 empselect e.*,d.dname from view_emp e,dept d where e.deptno=d.deptno;---只读视图    with read onlycreate 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 personinsert into orders(oid) values(seq_person.nextval);

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

-- 创建序列create sequence seq_testminvalue  1     --默认最小值1increment by 1  --默认是1，每次增长的长度start with 4    --默认是1，起始值maxvalue  15    --默认值是 18个9  999999999999999999cycle           --循环  默认  nocyclecache 6         --缓存  默认会缓存205 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))beginfor i in 1..5000000  loop    insert into t_test values(i,'测试数据'||i);  end loop;end;select * from t_test where tname='测试数据4000000'   --0.734screate 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');