Oracle数据库的点点滴滴

Oracle简介

1. 创始人 : 拉里·埃里森（Larry Ellison）,中国称呼其公司为甲骨文.
   
2. 数据库 :
   Oracle数据库是数据的物理存储,这就包括(数据文件ORA或者DBF、控制文件、联机日志、参数文件). 其实Oracle数据库的概念和其它数据库不一样,这里的数据库是一个操作系统只有一个库,可以看做是Oracle就只有一个大数据库.
3. 实例 :
   一个Oracle实例(Oracle Instance)有一系列的后台进程和内存结构组成,可以数据可以有多个实例
4. 用户
   用户是在实例下建立的,不同实例可以建相同名字的用户.

Oracle数据库基本操作

– 创建表空间
create tablespace junyang
datafile ‘f:\ora\junyang.dbf’
size 10m
autoextend on
next 5m;
– 删除表空间
drop tablespace junyang;
– 创建用户
create user C##junyang
identified by 123456
default tablespace junyang;
– 给用户授权
– oracle数据库中常用角色
connect --连接角色,基本角色
resource – 开发者角色
dba – 超级管理员角色
grant dba to C##junyang;

Oracle常用数据类型及DDL相关操作

1. 常用数据类型
   1. varchar2 : 表示一个字符串
   2. number :
      1. number(n)表示一个整数,长度是n.
      2. number(m,n)表示一个小数,总长度是m,小时位数是n,整数是m-n
   3. data : 表示日期类型
   4. clob : 大对象,表示大文本数据类型,可存4G
   5. blob : 大对象,表示二进制数据,可存4G.
2. 建表语句
   – 创建一张person表

   create table person(
          pid number(20),
          name varchar2(10)
   );
   

3. 添加一列
   – 添加一列
   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;

Oracle数据库DML操作

1. 添加记录 :
   insert into person values (‘1’,‘小明’);
2. 修改记录
   update person set name = ‘小黑’ where pid = 1;
3. 三种删除
   1. delete from person; 删除表中全部记录
   2. drop table person ; 删除表结构
   3. truncate table person ; 先删除表,再创建表,最后的结果和delete from person 一样,此种方法适合数据量比较大的时候的删除数据操作.
4. 序列
   1. 序列 : 默认从1开始,一次递增,主要用来给主键赋值使用
   2. 序列不是真的属于任何一张表,但是可以逻辑和表绑定
   3. duai : 虚表,只是为了补全语法,没有任何意义.
   4. 语法 : create sequence s_person;
   5. 使用 : insert into person values(s_person.nextval , ‘小白’);
5. 注意对于DML操作,需要使用commit进行提交.

Oracle数据库DQL操作

1. 解锁用户
   alter user scott account unlock;
2. 解锁用户的密码(重置密码)
   alter user scott identitied by tiger;
3. 单行函数
   1. 字符函数
      1. select upper(‘yes’) from dual; – 把子母转换为大写
      2. select lower(‘YES’) from dual; --把子母转换为小写.
   2. 数值函数
      1. select round(56.16,n) from dual; – 四舍五入,后面的参数表示保留的位数.
      2. select trunce(56.16,m) from dual ; – 直接截取,不再看后面位数的数字
      3. select mod(10,3) from dual ; – 取余
   3. 日期函数
      1. select sysdate -e.hiredate from emp e; – 查询出emp表中所有员工入职天数
      2. select sysdate +1 from dual; – 预测出明天的日期
      3. select months_between (sysdate,e.hiredate) from emp e; – 计算emp表中的入职有多少月.
      4. select to_char (sysdate,’ fm yyyy-mm-dd hh24-mi-ss’) from dual; – 把当前日期转换成指定格式.
      5. select to_date (‘2020-6-25 16:40:48’ , ’ fm yyyy-mm-dd hh24-mi-ss’) from dual; – 把字符串转换为日期
   4. 通用函数 : select e.sal*12+nvl(e.comm,0) from emp e; – 计算出emp表中员工的年薪.
   5. 条件表达式 (Oracle中除了起别名都用单引号)
      需求 : 给emp表中员工起中文名
      select e.ename,
      case e.ename
      when ‘SMITH’ then ‘小密’
      when ‘ALLEN’ 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;
4. 多行函数 : 作用于多行,返回一个值
   select count(1) from emp; – 查询总数量
   select max(sal) from emp; – 查询工资最大值
   select min(sal) from emp; – 查询工资最小值
   select avg(sal) from emp; – 查询平均工资
   select sum(sal) from emp; – 查询工资总数
5. 分组查询
   1. 需求 : 查询出每个部门的平均工资
      注意 : 分组查询中,出现在group by后面的原始列,才能出现在select后面.没有出现在group by后面的列,想在select后面出现,必须加上聚合函数.
      聚合函数 : 可以把多行记录变成一个值.
      select
      e.depno,avg(e.sal),
      from
      emp e
      group by
      e.depno;
   2. 需求 : 查询平均工资大于2000的部门信息
      select e.depno ,avg(sal)
      from emp e
      group by e.depno
      having avg(sal)>2000;
      注意 :
      1. 所有条件判断都不能是别名
      2. where 是过滤分组前的数据,having是过滤分组后的数据
      3. where必须在group by之前,having实在group by之后.
   3. 需求 : 查询出每个部门工资高于800的员工的平均工资
      select e.depno ,avg(sal)
      from emp e
      group by e.depno
      having avg(sal)>800;
6. 多表查询
   1. 隐式内连接(等值连接)
      select * from emp e ,dept d where e.depno = d.deptno;
   2. 显式内连接
      select * from emp e inner join dept d on e.depno = d.deptno;
   3. 外连接(左/右)
      select * from emp e left(right) join dept d on e.depno = d.deptno;
   4. 自连接 : 就是站在不同的角度,把一张表看成多张表
      1. 需求 : 查询出员工姓名,员工领导姓名
         select e1.ename,e2.ename
         from emp e1 , emp e2
         where e1.mgr = e2.empno;
      2. 需求 : 查询出员工姓名,员工部门名称,员工领导姓名,员工领导部门名称.
         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.depno = d1.deptno and e2.depno = d2.deptno;
   5. 子查询
      1. 需求 : 查询出工资和scott一样的员工信息 .
         select * from emp where sal in (select sal from emp where ename =‘WARD’);
      2. 需求 : 查询出工资和10号部门任意员工一样的员工信息
         select * from emp where sal in (select sal from emp where depno = 10);
      3. 需求 : 查询出每个部门最低工资,和最低工资员工姓名,和该员工所在部门名称
         1. 先查询出每个部门最低工资
            select depno , min(sal) msal
            from emp
            group by depno;
         2. 添加条件 完成查询
            select t.depno , t.msal , e.ename , d.dname
            from (select depno , min(sal) msal
            from emp
            group by depno) t ,emp e , dept d
            where t.msal = e.sal and t.depno = e.depno and t.depno = d.deptno ;
   6. 分页查询
      1. rownum行号 : 当我们做select操作的时候, 每查询出一行记录,就会在该行上加上一个行号,此行号从1开始,一次递增,不能跳着走.
         select * from (
         select rownum rn ,e.* from (
         select * from emp order by sal desc
         ) e where rownum<11
         ) where rn>5;

Oracle数据库视图和索引

1. 视图 :
   1. 视图的作用 :
      1. 视图可以屏蔽掉一些例如工资等敏感字段
      2. 保证总部和分部数据及时统一
   2. 视图的概念 :
      视图就是提供一个查询的窗口,所有数据来自于原表
   3. 创建视图 (dba权限)
      1. 创建普通视图
         create view v_emp as select ename,job from emp;
      2. 修改视图
         update v_emp set job = ‘CLERK’ where ename= ‘ALLEN’;
         commit;
      3. 创建只读视图
         create view v_emp as select ename,job from emp with read onlu;
2. 索引 :
   1. 索引的概念 : 就是在表的列上构建一个二叉树
   2. 优点 : 大幅度提高查询效率.
   3. 缺点 : 影响增删改的效率.
   4. 单列索引
      1. 创建 : create index idx ename on emp(ename);
      2. 触发 : 条件必须是索引列中的原始值 ,但是单行函数,模糊查询,都会影响索引的触发
   5. 复合索引
      1. 创建 : create index idx ename on emp(ename,job);
      2. 触发 : 复合索引中第一列为优先索引列 ,如果要触发复合索引,必须包含有优先索引列中的原始值.