Oracle基础加强

DDL语句：

/*
Oracle体系结构：
    1、一台计算机只有一个Oracle的数据库
    2、一个数据库可以有多个实例： orcl
    3、一个实例下可以有多个表空间 ： 存储数据的逻辑单位
                  1）一个表空间可以由 多个 数据文件【dbf\ora】组成
                  2）一个表空间中可以 有  多个用户
    4、一个用户只能从属于一个表空间

创建表空间：
    create tablespace        表空间名称
    datafile 'c:\test.dbf'   数据文件的位置
    size 50m                初始化大小
    autoextend on            开启自动扩展
    next 30m ;              下一次扩展的大小
*/
--创建表空间需要dba的权限：切换到  system  管理员
create tablespace itheima87
datafile 'c:\itheima87.dbf'
size 500m
autoextend on 
next 300m;

--===============================创建用户
--           用户名                 密码           所存放的表空间
create user yunbing identified by yun123 default tablespace dyb;

--创建了用户 还登录不了，必须授权
/*
Orale中：默认有三种角色
        connect: 链接权限
        resource: 开发者权限
        dba: 管理员权限

授权使用dba角色去授权：
grant connect,resource to yunbing;

撤销：
revoke connect,resource from yunbing;
*/
grant dba to yunbing;


--==========================Oracle数据类型
-- ======字符串
char      : 定长     name  char(10)   :  johnny      占用十个字符，6个字符+4个空
varchar2  : 变长     name varchar(10) :  johnny      占用六个
long      : 存储2G字符串

-- ======数字
number(v1,p1): v1 : 是整个数字的总长度； p1:小数位数，省略p1,就是整数
--小数
number(3,2)   : 最大值：9.99
--整数
number(3)     : 最大值：999

-- ======日期
date : 精确到时分秒，类似mysql ：datatime
timestamp: 精确到秒的后9位 ，用于秒杀场景

-- ======大数据类型
blob : 存储4G的二进制文件
clob : 存储4G的字符串


--================================创建表与修改表
create table person (
       id number(10),
       name varchar2(200)
);

-- 加一列
alter table person add address01 varchar2(300);

-- 改变列
alter table person modify address01 varchar2(500);

-- 改变列名
alter table person rename column address01 to address;

-- 删除列
alter table person drop column address;


--=========================================约束：
--=================主键约束：包含非空和唯一约束
drop table person;
create table person(
       id number(10) primary key,
       name varchar2(200),
       gender number(1) default 1
);
--插入数据
insert into person(id,name,gender) values (1,'纯情小飞飞',1);
commit;

--下面的数据插入不了
insert into person (id,name,gender) values (1,'纯情小鸭鸭',1);
insert into person (id,name,gender) values (null,'纯情小鸭鸭',1);
commit;


--========================非空和唯一约束
drop table person;
create table person(
       id number(10) primary key,
       name varchar2(200) not null unique,
       gender number(1) default 1
);

--插入数据
insert into person values (1,'纯情小狼狗',1);
commit;

--下面两条数据插入不了
insert into person values(2,'纯情小狼狗',2);
insert into person values(2,null,2);
commit;


--================检查约束
drop table person;
create table person(
       id number(10) primary key,
       name varchar2(200) not null unique,
       gender number(1) default 1 check(gender in(1,2))
);

--插入值
insert into person values (1,'纯情小空空',1);
commit;

--下面的值插入不了，性别只能是 1 和  2
insert into person values (2,'纯情小塔塔',3);
commit;


select * from person;


--========================外键约束
/*
订单：    主表
订单项：  从表    外键在这里，外键的值来源于主表的主键，如果没有定义非空约束，可以插入空值
*/
--主表
create table orders(
       order_id number(10) primary key,
       price number(10,2),
       create_date date
);

--创建从表
create table orders_item(
       item_id number(10) primary key,
       item_name varchar2(200),
       itme_order_id number(10),
       constraint fk_order_id foreign key(itme_order_id) references orders(order_id)
);

--插入数据：一般先有主表的数据，再插入从表的数据
--插入主表数据
insert into orders(order_id,price,create_date) values (1,9999.85,sysdate);
commit;
--插入从表数据
insert into orders_item(item_id,item_name,itme_order_id) values (1,'VIVO:油光双摄照亮你的美',1);
commit;
insert into orders_item(item_id,item_name,itme_order_id) values (2,'OPPO:开发5分钟，调bug两小时',null);
commit;

--下面的数据插入不了  ： 外键  2 在主表中没有记录
insert into orders_item(item_id,item_name,itme_order_id) values (3,'小米:为发烧而生',2);
commit;


--删除值：先删除从表数据，再删除主表数据
delete from orders_item where item_id = 1;
delete from orders where order_id = 1;

--删除表：先删除从表，再删除主表
drop table orders_item;
drop table orders;

--级联删除表：我们可以直接删除主表，去除外键的约束
drop table orders cascade constraint;



--========================级联删除表中的数据：【慎用】删除主表中的数据，级联删除从表的数据
--主表
create table orders(
       order_id number(10) primary key,
       price number(10,2),
       create_date date
);

--创建从表
drop table orders_item;
create table orders_item(
       item_id number(10) primary key,
       item_name varchar2(200),
       itme_order_id number(10),
       constraint fk_order_id foreign key(itme_order_id) references orders(order_id) on delete cascade
);


--插入主表数据
insert into orders(order_id,price,create_date) values (1,9999.85,sysdate);
commit;
--插入从表数据
insert into orders_item(item_id,item_name,itme_order_id) values (1,'VIVO:油光双摄照亮你的美',1);
commit;
insert into orders_item(item_id,item_name,itme_order_id) values (2,'OPPO:开发5分钟，调bug两小时',1);
commit;

--级联删除主表数据【慎用】
delete from orders where order_id = 1;
commit;


select * from orders;
select * from orders_item;
select * from person;

DML语句：

DML语句： insert 、update 、delete
--插入
insert into person (id,name,gender) values (2,'贝吉塔',1);
commit;

--更新
update person set name ='特兰克斯',gender=2 where id = 2;
commit;

--删除
delete from person where id=2;
commit;

--删除全表数据
delete from person; -- 删除所有数据，这种方式会产生数据碎片，好处：可以回滚
truncate table person ; -- 先摧毁表结构，再重新创建表：效率高，不会产生碎片，弊端：不能回滚

select * from person;

事物：

/*
事务：逻辑上的一组操作，要么全部成功，要么全不成功【失败】
事务的四大特性： ACID  【原子性、一致性、隔离性、持久性】
    如果不考虑隔离性会出现读的问题：
         脏读：
         不可重复读：
         幻读（虚读）：

在Oracle中：支持三种事务：
         Oracle 支持的 3 种事务隔离级别： READ COMMITED, SERIALIZABLE， READ ONLY
         Oracle 默认的事务隔离级别为: READ COMMITED

Oracle中保存点： savepoint
  设置了保存点，当回滚到保存点的时候，保存点之前的数据正常提交，保存点之后的数据回滚
*/
insert into person (id,name,gender) values (1,'樱木',1);
savepoint s1;
insert into person (id,name,gender) values (2,'三井寿',2);
rollback to s1;
commit;

select * from person;

试图：

/*
视图:一般用于查询的，可以封装一些复杂的sql、屏蔽掉一些敏感数据
细节：
    1)视图它本身不存储数据，真正的数据都在表中
    2)创建视图需要dba的权限
创建视图语法：
create [or replace] view 视图的名称 as 查询语句;
*/

--用scott来创建视图，他没有创建的权限，先授权
grant dba to scott;

--创建视图：
create view v_emp as select * from emp;

--查询
select * from v_emp;
--更新视图中的数据: 更新的是表中的数据，视图自己不存储数据
update v_emp set ename='smith' where empno = 7369;
commit;

--查询emp表:
select * from emp;

--=====作用：
--1）封装复杂的sql  ： 查询部门的平均工资  
create view v_dept_avg_sal as
select deptno,round(avg(sal),2) as davg
from emp
group by deptno;

--查询视图  : 如果是复杂的sql视图不能更新操作：update、delete
select * from v_dept_avg_sal;

--2）屏蔽一些敏感数据
create view v_emp2 as 
select empno,ename,mgr,job,hiredate,deptno from emp;

--查询视图：
select * from v_emp2;

序列：

/*
mysql中有auto_increment主键自增长策略，但是oracle中是没有

序列：oracle中生成主键的时候，可以采取此种策略来生成主键：可以是连续或者不连续

语法:
       create sequence 序列名称;

使用:
       currval ： 查看序列的当前值，刚创建完的序列，不能马上执行查询当前值，必须要先执行一次nextval
       nextval :  查询序列的下一个值，每执行一次都会加一，从1开始  
*/
create sequence seq_test;

--马上查询当前值会报错
select seq_test.currval from dual;


--查询下一个值
select seq_test.nextval from dual;

--使用序列：与插入语句一起用
insert into emp (empno,ename) values (seq_test.nextval,'aaaaa');
commit;

select * from emp;


/*
了解一下完整的语法
       create sequence 序列的名称
       start with 3      --开始值
       increment by 3    --步长
       maxvalue 20       --最大值 ： 默认的最大值： 999999999999999999999999999
       cycle             --循环
       cache 5;   --默认值缓存20
*/
create sequence seq_test2
       start with 3      --开始值
       increment by 3    --步长
       maxvalue 20       --最大值
       cycle             --循环
       cache 5;   --默认值缓存20

--循环回去都是从1开始
select seq_test2.nextval from dual;

索引：

/*
索引： 为了提高查询效率存在的

语法：
    单列索引：create index 索引名称 on 表名(列名);
    复合索引：create index 索引名称 on 表名(列名1,列名2......);

索引的使用规则：
    1、在数据量大的表中使用索引，百万级
    2、在查询比较多的表中使用索引
    3、在增删改比较多的表中，慎用索引，原因：插入或者删除数据的时候，要重构索引树
    4、复合索引的使用有优先索引列：  create index 索引名称 on 表名(列名1,列名2);

     select * from 表名 where 列名1=?  and 列名2=?    --触发索引
     select * from 表名 where 列名1=?  or 列名2=?    --不会触发索引
     select * from 表名 where 列名2=?  and 列名1=?   --不会触发索引
     select * from 表名 where 列名2=?  or 列名1=?   --不会触发索引

*/

--演示有索引和没有索引的查询时间: 使用 yunbing 用户插入一百万条数据


--删除person
drop table person;
create table person(
       id number(10) primary key,
       name varchar2(200),
       gender number(1) default 1
);

--插入一百万条数据： 用到明天的知识：plsql语法
--创建序列
create sequence seq_person_id;

--使用oracle的uuid的函数来生成名称
select sys_guid() from dual;


--plsql语法
declare
       pname varchar2(200);
begin
       for i in 1..1000000 loop
          select sys_guid() into pname from dual;
          insert into person (id,name,gender) values (seq_person_id.nextval,pname,1);
       end loop;
       commit;
end;

select count(*) from person;

--没有索引的查询时间   -- 0.406秒
select * from person where name = 'F8894BC158E145CD9B90E519499D8499';

--创建索引
create index index_person_name on person(name);

--有索引的查询时间     -- 0.031
select * from person where name = '373AB00B541842BC950C49E84E1B0FC8';

同义词：

/*
  同义词： 在另外一个用户下去使用其他用户的表，跨用户使用表，一般不使用

  语法：
       create synonym 同义词名称 for 其他用户名称.表名

  权限:
       需要dba权限
*/
create synonym semp for scott.emp;

select * from semp;

导入和导出数据：

--1、在服务器端【oracle服务所在的机器】操作： 在命令提示符中去操作,最后不能有分号，效率最高，推荐用这种
    导出数据：

    导出所有数据：
         exp scott/123456 file='c:\scott.dmp'
    导出具体的表的数据：
         exp scott/123456 file='c:\scott_emp_dept.dmp' tables=emp,dept


    导入所有数据：
         imp yunbing/yun123 file='c:\scott.dmp' full=y
         imp yunbing/yun123 file='c:\scott.dmp' fromuser=scott touser=feifei
    导入具体的表的数据：
         imp yunbing/yun123 file='c:\scott_emp_dept.dmp' fromuser=scott touser=yunbing tables=emp,dept

--2、在客户端上操作：导出sql就可以
       导出数据：
                Tools - Export tables
       导入数据:
                Tools - Import tables