Oracle学习基础（三）总结

超、凢脫俗 2022-02-26 15:35 294阅读 0赞

/*
      本节使用的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;