Oracle数据库的基本使用

本是古典何须时尚 2021-12-05 03:09 318阅读 0赞

# Oracle数据库的基本使用 #

### 1.创建表空间/用户/用户授权 ###

*  创建表空间
    
        create tablespace test--创建表空间
        datafile 'c:\itheima.dbf'--指定数据文件的位置
        size 100m--初始化大小
        autoextend on--自动增长
        next 10m;--每次增长的大小
 *  删除表空间
    
        drop tablespace test;
 *  创建用户
    
        create user user1--创建用户名
        identified by user1--指定密码
        default tablespace test;--指定默认的表空间
 *  给用用户授权
    
        --oracle数据库中常用的角色
        connect--连接角色,基本角色
            resource--开发者角色
        dba--超级管理员角色
        --给用户授权dba角色
        grant dba to user1

### 2.数据类型和表的创建以及操作 ###

*  数据类型
    
    1.  Varchar， varchar2 表示一个字符串
    2.  NUMBER NUMBER(n)表示一个整数，长度是n NUMBER(m,n):表示一个小数，总长度是 m，小 数是 n，整数是 m-n
    3.  DATA 表示日期类型
    4.  CLOB 大对象，表示大文本数据类型，可存 4G
    5.  BLOB 大对象，表示二进制数据，可存 4G
 *  创建表
    
        --创建一个Person表
        create table person(
        pid number(20),
        pname varchar2(10)
        );
 *  修改表结构
    
        --修改表结构
        --添加列
        alter table person add ( gender number(1));
        --修改列类  
        alter table person modify gender char(1);
        --修改列的名称
        alter table person rename column gender to sex;
        --删除一列
        alter table person drop column sex;
 *  三个删除
    
        --删除表中所有的记录
        delete from person;
        --删除表结构
        drop table person;
        --删除表,再次创建表;效果等同于删除表中的全部记录.
        --在数据量大的情况下,尤其在表中带有索引的情况下,该操作的效率高
        --索引可以提高查询效率,但会影响增删改的效率.
        truncate table person;

### 3.序列的使用 ###

--序列不真的属于任何一张表,但是可以逻辑和表做绑定
    --序列:默认从1开始,依次递增,主要用来给主键赋值使用.
    ----dual:虚表,只是为了补全语法,没有任何意义
    create sequence s_person;
    select s_person.nextval from dual;

### 4.scott用户介绍 ###

----scott用户,密码:tiger
    --解锁scott用户
    alter user scott account unlock;
    --解锁scott用户的密码
    alter user scott identified by tiger;
    --切换到scott用户下

### 5.单行函数 ###

--单行函数:作用于一行,返回一个值
    ----字符函数:
    select upper('yes') from dual;
    select lower('YES') from dual;
    ----数值函数
    select round(26.18,1) from dual;---四舍五入,后面的参表示保留小数的位数
    select trunc(26.18,1) from dual;---直接截取不再看后面位数的数字是否大于5
    select mod(10,3) from dual;---求余
    ----日期函数
    ----查询出emp表中所有员工入职距离现在几天.
    select sysdate-e.hiredate from emp e;
    ----算出明天此刻的时间
    select sysdate+1 from dual;
    ----查询出emp表中所有员工入职距离现在几月.
    select months_between(sysdate,e.hiredate) from emp e;
    ----查询出emp表中所有员工入职距离现在几年.
    select months_between(sysdate,e.hiredate)/12 from emp e;
    ----查询出emp表中所有员工入职距离现在几周.
    select round((sysdate-e.hiredate)/7,0) from emp e;
    ----转换函数
    ---日期转换字符串
    select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    ---字符串转日期
    select to_date('2018-6-7 18:23:43','fm yyyy-mm-dd hh24:mi:ss') from dual;
    ----通用函数
    ---算出emp表中所有员工的年薪
    ----奖金里面有null值,如果null只和任意数字做算术运算,结果都是null.
    select e.sal*12+nvl(e.comm,0) from emp e;--nvl处理空值

### 6.条件表达式 ###

---条件表达式
    ---条件表达式的通用写法, mysql和oracle通用
    ---给emp表中的员工起中文名
    select e.ename,
          case e.ename
            when 'SMITH' then '张三'
              when 'ALLEN' then '李四' 
                when 'WARD' then '王五'
                  --else '无名'
                    end
    from emp e;
    ---判断emp表中员工的工资,如果高于3000显示高收入,如果高于1500低于3000显示中等收入,其余显示低收入
    select e.sal,
          case
            when e.sal>3000 then '高收入'
              when e.sal>1500 then '中等收入' 
                  else '低收入'
                    end
    from emp e;
    ----oracle中除了起别名,都用单引号.
    ----oracle专用条件表达式
    select e.ename,
          decode(e.ename,
             'SMITH' , '张三',
               'ALLEN' , '李四' ,
                 'WARD' , '王五',
                   '无名') 中文名 
    from emp e;

### 7.多行函数 ###

--多行函数(聚合函数):作用于多行,返回一个值
    select count(1) from emp;---查询总数量
    select sum(sal) from emp;---工资总和
    select max(sal) from emp;---最大工资
    select min(sal) from emp;---最低工资
    select avg(sal) from emp;---平均工资

### 8.分组查询 ###

---查询出每个部门的平均工资
    select e.deptno,avg(e.sal)
    from emp e
    group by e.deptno
    ---查询出平均工资高于2000的部门信息
    select e.deptno,avg(e.sal)
    from emp e
    group by e.deptno
    having avg(e.sal)>2000;
    ---所有条件都不能使用别名来判断.
    
    ---查询出每个部门工资高于800的员工的平均工资
    select e.deptno,avg(e.sal)
    from emp e
    where e.sal>800
    group by e.deptno
    ----where是过滤分组前的数据,having是过滤分组后的数据.
    ---表现形式:where必须在group by之前,having是在group by之后.
    ---查询出每个部门工资高于800的员工的平均工资,然后再查询出平均工资高于2000的部门
    select e.deptno,avg(e.sal)
    from emp e
    where e.sal>800
    group by e.deptno
    having avg(e.sal)>2000

### 9.自连接概念 ###

---自连接概念
    ---自连接:自连接其实就是站在不同的角度把一张表看成多张表.
    select e1.ename,e2.ename
    from emp e1,emp e2
    where e1.mgr = e2.empno
    ----查询员工姓名,员工部门名称,员工领导姓名,员工领导部门名称
    select e1.ename, d1.dname,e2.ename,d2.dname
    from emp e1,emp e2,dept d1,dept d2
    where e1.mgr = e2.empno
    and e1.deptno = d1.deptno
    and e2.deptno = d2.deptno

### 10.子查询 ###

---子查询返回一个值
    ---查询出工资和scott一样的员工信息
    select * from emp where sal in 
    (select sal from emp where ename = 'SCOTT')
    ---子查询返回一个集合
    ---查询出工资和10号部门任意员工一样的员工信息
    select * from emp where sal in
    (select sal from emp where deptno = 10)
    ---子查询返回一张表
    ---查询出每个部门的最低工资,和最低工资员工的姓名,和该员工所在部门的名称
    ---1.先查出每个部门的最低工资
    select deptno,min(sal) msal
    from emp
    group by deptno;
    ---2.三表联查,得到最终的结果
    select t.deptno,t.msal,e.ename,d.dname
    from (select deptno,min(sal) msal
          from emp
          group by deptno) t,emp e,dept d
    where t.deptno = e.deptno
    and t.msal = e.sal
    and e.deptno = d.deptno;

### 11.分页查询 ###

----oracle中的分页
    ---rownum行号:行号从1开始,一次递增,不能调走
    ----排序操作会影响rownum的顺序
    select rownum,e.* from emp e order by e.sal desc;
    
    select rownum,t.* from
    (select rownum,e.* from emp e order by e.sal desc) t
    
    ----emp表工资倒叙排列后,每页5条记录,查询第二页
    ----rownum行号不能写上大于一个正数
    select * from
    (select rownum rn,e.* from
            (select * from emp order by sal desc) e
    where rownum<11) tt where rn>5

### 12.视图 ###

---视图的概念:视图就是提供一个查询的窗口,所有数据来自原表.
    ---使用查询语句创建表
    create table emp as select * from scott.emp;
    select * from emp;
    ---创建视图【必须有DBA权限】
    create view v_emp as select ename,job from emp;
    ---查询视图
    select * from v_emp;
    ---修改视图[不推荐]
    update v_emp set job='CLERK' where ename='ALLEN';
    commit;
    ---创建自读视图
    create view v_emp1 as select ename,job from emp with read only;
    ---视图的作用
    ---第一:视图可以屏蔽掉一些敏感字段.
    ---第二:保证总部和分部数据及时统一.

### 13.索引 ###

--索引的概念:索引就是在表的列上构建一个二叉树
    ----达到大幅度提高查询效率,但是索引回影响增删改的效率.
    ---单列索引
    ---创建一个单列索引
    create index idx_ename on emp(ename);
    ---单列索引触发规则:条件必须是索引列中的原始值.
    ---单行函数,模糊查询,都会影响索引的触发.
    select * from emp where ename='SCOTT';
    ---复合索引
    ---创建复合索引
    create index idx_enamejob on emp(ename,job);
    ---复合索引的第一列为优先检索列;
    ---复合索引的触发规则:如果要触发复合索引,必须有优先检索列的原始值.
    select * from emp where ename='SCOTT' and job='xx'---触发复合索引
    select * from emp where ename='SCOTT' or job='xx'---不触发索引
    select * from emp where ename='SCOTT';--如果既有单列索引和复合索引,此时只触发单列索引

### [14.pl/sql编程语言][14.pl_sql] ###

---pl/sql编程语言
    ---pl/sql编程语言是对sql语言的扩展,使得sql语言具有过程化编程的特性.
    ---pl/sql编程语言比一般的编程语言,更加灵活高效.
    ---pl/sql编程语言主要用来编写存储过程和存储函数等.

*  pl/sql编程语言定义变量
    
        ---声明方法
        ---赋值操作可以使用:=也可以使用into查询语句赋值
            declare
              i number(2) := 10;--定义变量(数值类型)
              s varchar2(10) := '小明';--定义变量(字符串类型)
              ena emp.ename%type;--引用表字段类型(引用型变量)
              emprow emp%rowtype;---记录型变量
          begin
                dbms_output.put_line(i);
                dbms_output.put_line(s);
                select ename into ena from emp where empno = 7788;
                dbms_output.put_line(ena);
                select * into emprow from emp where empno = 7788;
                dbms_output.put_line(emprow.ename || '的工作为' || emprow.job);
            end;
 *  pl/sql中的if判断
    
        ---pl/sql中的if判断
        ---输入小于18的数字,输出未成年
        ---输入大于18小于40的数字,输出中年人
        ---输入大于40的数字,输出老年人
        declare
        i number(3) :=&ii;--手动输入一个值
        begin
        if i<18 then
          dbms_output.put_line('未成年');
        elsif i<40 then
          dbms_output.put_line('中年人');
        else
          dbms_output.put_line('老年人');
        end if;
        end;
 *  pl/sql中的循环
    
        ---pl/sql中的loop循环
        ---用三种方式输出1到10中的10个数字
        ---while循环
        declare
          i number(2) := 1;
        begin
          while i<11 loop
            dbms_output.put_line(i);
            i := i+1;
          end loop;
        end;
        ---exit循环
        declare
          i number(2) := 1;
        begin
          loop
            exit when i>10;
            dbms_output.put_line(i);
            i := i+1;
          end loop;
        end;
        ---for循环
        declare
        
        begin
          for i in 1..10 loop
            dbms_output.put_line(i);
          end loop;
        end;
 *  pl/sql中的游标
    
        ---游标:,可以存放多个对象,多行记录
        ---输出emp表中所有员工的姓名
        declare
          cursor c1 is select * from emp;
          emprow emp%rowtype;
        begin
          open c1;
            loop
              fetch c1 into emprow;
              exit when c1%notfound;
              dbms_output.put_line(emprow.ename);
            end loop;
          close c1;
        end;
        
        ----给指定部门的员工涨工资
        declare
          cursor c2(eno emp.deptno%type)
          is select empno from emp where deptno = eno;
          en emp.empno%type;
        begin
          open c2(10);
            loop
              fetch c2 into en;
              exit when c2%notfound;
              update emp set sal=sal+100 where empno=en;
            end loop;
          close c2;
        end;
        
        ---查询10号部门所有的员工
        select * from emp where deptno = 10;
 *  pl/sql的存储过程
    
        ---存储过程
        --存储过程:存储过程就是提亲已经编译好的一段pl/sql语言,放置在数据库端
        ----------可以直接被调用.这一段pl/sql一般都是固定步骤的业务.
        ----给指定的员工涨100元
        create or replace procedure p1(eno emp.empno%type)
        is
        
        begin
          update emp set sal = sal+100 where empno = eno;
          commit;
        end;
        
        select * from emp where empno = 7788;
        ---测试p1
        declare
        
        begin
          p1(7788);
        end;
 *  pl/sql的存储函数
    
        ---通过存储函数实现计算指定员工的年薪
        ---存储过程和存储函数的参数都不能带长度
        ---存储函数的返回值类型不能带长度
        create or replace function f_yearsal(eno emp.empno%type) return number
        is
          s number(10);
        begin
          select sal*12 +nvl(comm,0) into s from emp where empno=eno;
          return s;
        end;
        
        ----测试f_yearsal
        ----存储函数在调用的时候返回值需要接收.
        declare
          s number(10);
        begin
          s := f_yearsal(7788);
          dbms_output.put_line(s);
        end;
 *  pl/sql语言out类型参数的使用
    
        ---out类型的参数如何使用
        ---使用存储过程来计算年薪
        create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number)
        is
          s number(10);
          c emp.comm%type;
        begin
          select sal*12,nvl(comm,0) into s,c from emp where empno = eno;
          yearsal := s+c;
        end;
        
        ---测试p_yearsal
        declare
          yearsal number(10);
        begin
          p_yearsal(7788,yearsal);
          dbms_output.put_line(yearsal);
        end;
        
        ---in和out类型的参数区别:凡是涉及into查询语句赋值或者:=赋值操作的参数,都必须使用out来修饰
 *  触发器的使用  
    —触发器,就是制定一个规则,在我们做增删改操作的时候,  
    \----只要满足该规则,自动触发,无需调用.  
    —语句级触发器:不包含for each row的触发器  
    —行级触发器:包含有for each row的就是行级触发器.  
    \----加for each row是为了使用:old或者new对象.
    
     *  语句级触发器
        
            ---插入一条记录,输出一个新员工入职
            create or replace trigger t1
            after
            insert
            on person
            declare
            
            begin
              dbms_output.put_line('一个新员工入职');
            end;
            
            --触发t1
            insert into person values(1,'小红');
            commit;
            select * from person;
     *  行级触发器
        
            ---行级触发器
            ---不能给员工降薪
            ---raise_application_error(-20001~-20999之间,'错误的提示信息');
            create or replace trigger t2
            before
            update
            on emp
            for each row
            declare
            
            begin
              if :old.sal>:new.sal then
                raise_application_error(-20001,'不能给员工降薪');
              end if;
            end;
            
            --触发t2
            update emp set sal = sal-1 where empno=7788;
            commit;
            select * from emp where empno=7788;
     *  触发器实现行级自增
        
            ---触发器实现行级自增,[行级触发器]
            ---分析:在用户做插入操作的之前,拿到即将插入的数据,
            ----给该数据中的主键列赋值.
            create or replace trigger auid
            before
            insert
            on person
            for each row
            declare
            
            begin
              select s_person.nextval into :new.pid from dual;
            end;
            
            select * from person;
            ---使用auid实现主键自增
            insert into person (pname) values('a');
            commit;

[14.pl_sql]: http://14.pl/sql%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80