Oracle学习基础（三）总结

/*
  本节使用的Oracle自带的表：emp(员工表),dept(员工表)
  您可以使用SQL语句查看Oracle所有自带表：select * from tab;
  
  select * from emp;
  select * from dept;
*/

/*
  复习：
         多表查询
           等值内联接
           不等值内联接
           内联接
           ..inner join ... on 条件
          外连接
           左外连接：..left outer join .. on 条件
           右外连接：...right outer join ... on 条件
           Oracle特有的写法（+） 
*/  

--内联接的结果
select * from emp e1,emp t1 where e1.mgr = t1.empno;
--t1表里面的所有记录都会显示出来, 如果t1.empno 在e1.mgr中没有对应的记录,就加空值
select * from emp e1,emp t1 where e1.mgr(+) = t1.empno;
--查询不是领导的员工编号
select empno from emp where empno not in (select mgr from emp where mgr is not null);

--获取员工的名字和部门的名字
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
----使用子查询的方式来完成
select ename,deptno from emp;

select ename,deptno,deptno from emp;
--关联子查询, 子查询依赖外部查询的条件
select e.ename,e.deptno,(select d.dname from dept d where d.deptno = e.deptno ) aa from emp e;

---

       Oracle体系结构：
              数据库---数据库实例ORCL---表空间（用户里面创建表）----数据文件
              创建表空间：逻辑单位，一般先建立表空间在表空间里创建用户，再用用户创建表。
              语法:
                  create tablespace 表空间名字
                  datafile '文件的存储路径'
                  size  文件大小
                  autoextend on 是否自动增长
                  next  每次增长的大小
                              

--切换到system账号下创建
create tablespace hangdong
datafile 'D:/Oracle/hangdong'
size 10m
autoextend on 
next 1m

--删除表空间--只是删除逻辑关系，实体文件还需要自己手动删除
drop tablespace  hangdong

---

    创建用户：
        create user 用户名
        identified by 密码
        default tablespace 表空间的名称
 

--创建用户dakang
create user dakang
identified by dakang
default tablespace hangdong

---

授予权限
      gtant 角色|权限 to  用户
      

--授予connect权限
grant connect to dakang
--授予dba角色的权限
grant dba to dakang

---

创建表：
      create table 表名(
         列名  列的类型 [列的约束]
          列名  列的类型 [列的约束]
      );
      列的类型：
          varchar(长度) 现在Oracle支持，但是不代表以后支持
          varchar2(长度)  可变字符串   hello 占五个字符
          char(长度)  不可变字符  hello 占10个字符
          number(总长度,小数长度) 数字类型---小数长度不能大于等于总长度
          date  年月日时分秒  2017/09/01  11:24:27
          timestamp 时间戳  比date要更准确 13-APR-17 09.44.08.272000 AM +08:00
          LONG/CLOB : 存一本小说
          BLOB: 存放电影 java 存进去 再读取出来。
          
      使用子查询创建表(相当于表的复制)：
           create table 表名 as  查询语句;
                 情况：1.查询结果为空的情况下，只复制表结构
                       2.查询结构不为空的情况下，复制表结构和数据 
                       3.不复制各个列的约束，需要手动添加
 

create table hdsheng(
     name1 varchar2(10),
     name2 char(10),
     age number(3,0)
);

insert into hdsheng(name1,name2,age) values('hello','hello',22)
select * from hdsheng where name1 like 'hello';--可以查询出数据
select * from hdsheng where name2 like 'hello';--不可查询出数据（char是固定字符）

select current_date from dual;
select current_timestamp from dual;

select * from hdsheng;

--复制表
select * from scott.emp;--dakang用户右权限看下级表的数据

create table hdemp as select * from scott.emp;

select * from hdemp;

修改表
       添加列
       修改列
       删除列
       修改列名
      
       重命名表
       
SQL分类：
       DDL：数据定义语言，修改表结构 alter create drop truncate 
       DML:数据操作语言，修改表数据，insert update delete
       DCL:数据控制语言,赋予权限， grant
       DQL：数据查询语言，数据查询，select 
 

create table stu(
       stuid number,
       sname varchar2(10)
);
select * from stu;
--添加一列
alter table stu add phone varchar2(10);

alter table stu add sex varchar(4);
--修改列名 sex---gender
alter table stu rename column sex to gender;

--修改表名
rename stu to student;

--删除表
drop table student;

---

列的五大约束
    列的约束：规范表中的数据
    主键约束：  primary key  非空唯一
    非空约束： 非空 not null
    唯一约束： 唯一 unique
    检查约束： check,在MySQL中可以使用，但是默认不使用
    外键约束：表A中的数据必须存在于表B中。    
 

create table student(
       stuid number primary key,
       sname varchar(10) unique,
       age varchar(10) not null,
       gender varchar2(4) check( gender in('男','女','人妖'))
);

--主键约束违反
insert into student values(1,'张三','31','男');
insert into student values(1,'李四','31','男');
--唯一约束违反
insert into student values(2,'徐立','31','男');
insert into student values(2,'徐立','31','男');
--非空约束
insert into student values(3,'徐立',null,'男');
--检查约束
insert into student values(4,'徐立','31','男');

insert into student values(4,'徐立','31','妖');

select * from student;

   外键约束：
     商品分类,商品表
     

--商品分类表
create table category(
       cid number primary key,
       cname varchar2(20)
);

--创建一个商品表
create table product(
       pid number primary key,
       pname varchar2(20),
       cno number
);
insert into category values(1,'手机数码');

select * from product;
select * from category;
--添加外键约束:category为主表
alter table product add foreign key(cno) references category(cid);

--主表中先存在2号，从表再插入数据。
insert into category values(2,'电脑办公');
insert into product values(11,'外星人',2);

--表中数据被外键关联无法删除
drop table category;

--方法1：强制删除:先删除从表的外键约束，然后再删除自己。先删除product的外键约束，再删除category.
drop table category cascade constraint;

--方法2：级联删除
----Step1:添加外键约束时，使用级联约束，再删除的时候，使用级联删除
alter table product add foreign key(cno) references category(cid) on delete cascade; 

----Step2:使用级联删除:先查找从表中的关联数据并删除，再删除主表中的数据
delete from category where cid=2;---此处删除主表category的cid为2的数据时，其从表的级联数据也会删除。

select * from category;
select * from product;


--执行速度，一般来说: drop> truncate > delete。
drop table product;--删除整个表（表结构和表数据）
truncate table product;--删除表数据（整个表数据）
delete * from product where pid=? --删除的是行数据

---

     插入数据:
         insert into 表名 values(所有列的值都要对应写上)
         insert into 表名(列1,列2) values(值1,值2);
         
     使用子查询插入数据
         insert into 表名 查询语句
 

select * from emp1;

select * from scott.emp;
create table emp1 as select * from scott.emp;
--将emp中10号部门的员工信息,插入到emp1中
insert into emp1 select * from scott.emp where deptno=10;

     更新数据
       update 表名 set 列名 = 列的值  [where 条件]
 

update emp1 set ename='HUAAN' where ename = 'KING';
select * from emp1;

删除数据
       delete from 表名 where [条件]
      delete  与 truncate 区别：
      
      delete:        truncate:
        DML           DDL
        逐条删除      删除表再创建表
        支持事务操作  不支持事务操作
                     执行效率高
          

delete from emp1 where empno=7839;

---

  事务：就是一系列操作，要么成功，要么失败
      事务的四大特性：原子性，隔离性，持久性，一致性。
      
      如果不考虑隔离级别：脏读，虚读，不可重复性。
           MYSQL隔离级别: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLE
           ORACLE隔离级别: READ COMMITTED ，SERIALIZABLE ,READ ONLY    默认隔离级别: READ COMMITTED
           
      事务的提交：commit
      事务的保存点/回滚点：savepoint 保存点的名称
      回滚：rollback
      

create table lou(
   lou number primary key    
);

select * from lou;

insert into lou values(1);
insert into lou values(2);
insert into lou values(3);
insert into lou values(4);
insert into lou values(5);
savepoint huigun
insert into lou values(5);
insert into lou values(6);
rollback to hungun
commit;

---

视图：是对查询结果的封装
      视图中的数据来自于查询的表中的数据，视图本身不存储数据。
      1.能够封装复杂的查询结果
      2.屏蔽一些细节
    语法：or replace --指：如果存在该视图，那么覆盖掉
        create [or replace] view 视图名称 as 查询语句 [with  read only]
    注意：
        通常不要通过视图去修改，视图创建的时候，通常要加上with read only.

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

--创建一个视图
create or replace view view_test1 as select empno,ename,job from emp;
select * from view_test1;

--通过视图修改数据
update view_test1 set ename='SMITH2' where ename='SMI%TH';--视图中的数据修改其实是修改原表中的数据。

--创建一个只读视图
create view view_test1 as  select empno,ename,sal from emp with read only;--视图已存在，必须加or replace
create or replace view view_test1 as  select empno,ename,sal from emp with read only;

--同义词的概念：复制一个类似的表；
create synonym syno_test1 for view_test1;
select * from syno_test1;

   序列：生成类似于 aotu_increment 这种ID自动增长 1,2,3,4,5.....
         aotu_increment 这个是MySQL
         
         语法：
              create sequence 序列名
              start with 从几开始
              increment by 每次增长多少
              maxvalue 最大值| nomaxvalue
              minvalue 最小值|nominvalue
              cycle | nocycle 是否循环  1,2,3,1,2,3
              cache 缓存数量 3 |nocache  1,2,3,4,5,6 
         如何从序列获取值
              currval:当前值
              nextval:下一个值
              
                    注意：currval 需要在调用nextval之后才能使用
                    永不回头，一直往下取值，无论发生异常，回滚。

--创建一个 1,3,5,7,9...30的序列
create sequence sequ_test1
start with 1
increment by 2
maxvalue 30
minvalue 0
cycle 
cache 3;

select sequ_test1.nextval from dual;
select sequ_test1.currval from dual;

  索引：相当一本书的目录，可以提高我们的查询效率
       如果某一列数据量大且经常查询，那么有必要建立索引，提高查询效率。
       
      语法：
          create index 索引名称 on 表名（列名） 
       注意：主键约束自带主键索引，唯一约束自带唯一索引。
       索引原理：btree  balance Tree 平衡二叉树
                注意点：1.如果列作为查询条件的时候，可以提高查询效率，但是修改的时候，会变慢。
                        2.索引创建好之后，一般过一段时间，DBA都会重构索引。
                        
       SQL调优：
              1.查看执行计划F5
              2.分析里面的cost(CPU调用次数)  和 影响行数 ，想办法降低。
              
平衡二叉树：
（1）非叶子节点最多拥有两个子节点；
（2）非叶子节值大于左边子节点、小于右边子节点；
（3）树的左右两边的层级数相差不会大于1;
（4）没有值相等重复的节点;
优缺点：
  1.二叉排序树是一种比较有用的折衷方案。  
  2.数组的搜索比较方便，可以直接用下标，但删除或者插入某些元素就比较麻烦。  
  3.链表与之相反，删除和插入元素很快，但查找很慢。  
  4.二叉排序树就既有链表的好处，也有数组的好处。  
  5.在处理大批量的动态的数据是比较有用。

--五百万数据测试
create table wubaiwan(
      name varchar2(30),
      address varchar2(20) 
);

--插入500000万条数据
declare

begin
     for i in 1..5000000 loop
       insert into wubaiwan values('姓名'||i,'地址'||i);
     end loop;
     commit;  
end;--133。049秒

--在没有添加索引的情况下,去查询  name='姓名3000000'  --2.985
select * from wubaiwan where name='姓名3000000';

--创建索引 name 再去查询 name='姓名3000000'
create index in_wubaiwan on wubaiwan(name);--52.683
select * from wubaiwan where name='姓名3000000';  --0.016

--在没有添加复合索引的情况下,再去查询 name='姓名3000000' and '地址3000000'
select * from wubaiwan where name='姓名3000000' and address='地址3000000'; --0.032

--创建复合索引的情况下, 再去查询
create index ind_wubaiwan2 on wubaiwan(name,address);
select * from wubaiwan where name='姓名3000000' and address='地址3000000'; --0.015

---

复习：
   DDL表空间操作：
      创建表空间
      创建用户
      授权（DCL）
      
      创建表
              子查询创建表
         修改表 : 添加列,删除列,修改列,修改列名, 修改表名
         
         约束:
             主键约束,唯一约束,非空约束,检查约束,外键约束
             
             外键约束:
               强制删除
               级联删除
             
     DML表中数据:
         插入数据
             子查询插入数据
         更新数据
         删除数据: delete 和 truncate
         
         事务操作:
               savepoint 保存点
               rollback to 保存点
          ORACLE事务隔离级别  : READ COMMITTED 
          
     视图: 就像窗户一样, 封装查询结果 , 通常视图创建只读视图
     序列: 主要是用来实现ID自增长 
     索引: 相当于是书的目录,能够提高查询效率, 原理 平衡二叉树, 每隔一段时间DBA都需要去重建索引
     同义词: create synonym 名称 for 对象的名称    

---

---

PLSQL编程：procedure Language 过程语言 Oracle对sql的一个扩展
      让我们能够想java一样写if  else  elseif 条件，还可以编写循环逻辑 for ,while
      
      declare
         --变量声明
         变量名称  变量类型;
         变量名称  变量类型:=初始值;
         eg: vsal emp.sal%type ;--定义引用变量类型 vsal
             vrow emp%rowtype ; --声明记录型变量 vrow
      begin
         --业务逻辑
      end;
      
      注意：dbms_output.put_line()相当于java中syso
      

declare 
     i varchar2(10):='张三';
begin
    dbms_output.put_line(i);
end;

---查询7369的工资，并打印出来(引用型变量的使用)
declare
    vsal emp.sal%type;
begin
  select sal into vsal from emp where empno=7369;
  dbms_output.put_line(vsal);
end; 

--打印出7369的员工信息（记录型变量的使用 ）
declare
   vrow emp%rowtype;
begin
   select * into vrow from emp where empno=7369;
   dbms_output.put_line('姓名:'||vrow.ename||'工资：'||vrow.sal);
end;

  PL条件判断
     
     if  then
        
     elsif then 
     
     else
       
     end if;

declare
    age number:=&a;  --&变量名 表示控制台自己输入某个值
begin
    if age<18 then
      dbms_output.put_line('小屁孩');
    elsif age<30  then
      dbms_output.put_line('年轻人');
    elsif age<50  then
      dbms_output.put_line('中年人');
    else
      dbms_output.put_line('老年人');
    end if;
end;

循环条件：
    while 循环：
       while 条件 loop
         
       end loop;
    for循环：
       for 变量  in [reverse] 起始值..结束值 loop
         
       end loop;
    
    loop循环:
        loop
          exit when 条件;
        end  loop;
        

--while循环输出1~10
declare
    i number:=1;
begin
    while i<=10 loop
      dbms_output.put_line(i);
      i:=i+1;
    end loop;
end;

--for循环输出11~1
declare
    i number:=1;
begin
    for i in reverse 1..11 loop
        dbms_output.put_line(i);
    end loop;
end;

--loop循环输出1~12
declare
   i number:=1;
begin
  loop
     exit when i>12;
             dbms_output.put_line(i);
             i:=i+1;
   end loop;
end;

---

输出菱形：

   *
  ***
 *****
  ***
   *   
输出 m  
   x : [-m,m]
   y : [-m,m]
   
   输出所有满足条件的 : abs(y)+abs(x) <=m
   
   m取值
   
   输出各种菱形的方法：abs(x)+abs(y)<=m   abs()是Oracle自带的函数

 

--使用PLSQL输出菱形
declare
   m number:=10;
begin
  for x in -m..m loop
    for y in -m..m loop
       if abs(x)+abs(y)<=m then
          dbms_output.put('*');
       else
          dbms_output.put(' ');
       end if;
    end loop;
     dbms_output.new_line();
  end loop;
end;

--使用PLSQL输出三角形,只要是三个角
declare
   m number := 10;
begin
   for x in reverse -m..m loop
     for y in -m..m loop
       if abs(y) + abs(x) <= m and x>=0 then
         dbms_output.put('*');
       else
         dbms_output.put(' ');
       end if;      
     end loop;
     dbms_output.new_line();
   end loop;  
end;