oracle基础笔记

1. 表空间
    -- 创建普通表空间
    create tablespace my_tbsp
    datafile 'E:/app/Administrator/product/11.2.0/dbhome_1/oradata/sample/my_tbsp.dbf'
    size 20M
    autoextend on  
    next 50m maxsize 20480m  
    extent management local;

    -- 创建临时表空间
    create temporary tablespace my_temp
    tempfile 'E:/app/Administrator/product/11.2.0/dbhome_1/oradata/sample/my_temp.dbf'
    size 20M
    autoextend on  
    next 50m maxsize 20480m  
    extent management local;

    -- 查看所有的表空间
    select * from user_tablespaces;

    -- 删除表空间
    drop tablespace my_tbsp;
    drop tablespace my_temp;

2. 用户
   -- 创建用户
   create user zhangsan
   identified by abc123
   default tablespace my_tbsp
   temporary tablespace my_temp;
   -- 修改用户密码
   alter user lisi identified by abcdefg;
   -- 删除用户
   drop user lisi;
   -- 删除用户(级联删除该用户的数据)
   drop user lisi cascade;

   -- 查看用户
   select * from user_users;

4. 权限和角色
   -- 授予权限
   grant create session to zhangsan;
   grant create user to zhangsan;
   grant create table to zhangsan;
   -- 撤销权限
   revoke create user from zhangsan;

   -- 角色: connect,resource,dba
   grant connect,resource to lisi;
   -- 撤销角色
   revoke resource from lisi;

5. 数据库语言
   DDL: 数据库模式定义语言(Data Definition Language)
        create,alter,drop,truncate
   DML: 数据操纵语言(Data Manipulation Language)
        insert,update,delete
   DQL: 数据查询语言(Data QueryLanguage)
        select
   TCL: 事务控制语言(Transaction Control Language)
        commit,rollback,savepoint
   DCL: 数据控制语言(Data Control Language)
        grant,revoke

6. 数据库类型
   6.1 字符型
       char: 定长字符,如果添加的值不足则会补齐空格,最多2000个字节
       varchar2: 可变长字符,最多4000个字节
       long: 可变长字符,最多2G
   6.2 数值型
       number(8,2) 8: 最多8位,2:接受最多两位小数(如果有小数,整数范围就要减去小数位)
                   整数位只能最多6位,小数位最多2位
   6.4 日期时间
       date: 存储日期和时间部分，精确到整个的秒
       timestamp: 存储日期、时间和时区信息，秒值精确到小数点后6位

   6.5 RAW
       raw: 存储2进制数据,最多2000字节
       long raw: 存储2进制数据,最多2G

   6.6 LOB
       BLOB: 2进制大字段,最多4G
       CLOB: 字符字符大字段,最多4G
       NCLOB: Unicode字符大字段,最多4G
       BFILE: 外部文件大字段

   6.7 伪列
       rowid: 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的一行
       rownum 是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数


7. 表
   7.1 创建表
       create table t_user (
            id varchar2(36) primary key,id_card char(18) not null unique,name varchar2(20) default 'hehehe',price number(4,2) default 3.1415,birthday date default sysdate
       );
       create table t_department(
              id varchar2(36) primary key,name varchar2(40)
       );

       alter table t_user add depart_id varchar2(36);

       primary key 主键(唯一,不为空)
       not null 不能为空
       unique 唯一
       default 设置默认值
       sysdate 表示系统当前时间
       ** oracle中没有自增长(auto increment),oracle中需要依赖sequence来实现自增长
   7.2 删除表
       drop table t_user;
   7.3 修改表结构
       -- 添加列
       alter table t_user add gender varchar2(6) default '男';
       -- 删除列
       alter table t_user drop column price;
       -- 修改列
       alter table t_user modify gender char(6) default 'female';


   7.4 外键约束

       -- 创建外键约束
       alter table t_user 
       add constraint fk_user_department
       foreign key(depart_id)
       references t_department(id);
       -- 删除外键约束
       alter table t_user drop constraint fk_user_department; 


----------
1. DDL清空表
   truncate table bonus; 
2. DML
   2.1. 添加数据
   insert into
        insert into dept(deptno,dname) 
        values(60,'财务部');
        insert into dept(deptno,dname) values(50,'技术部');
        commit;
        -- 新增所有列数据
        insert into dept values(70,'财务部','财务');
   2.2. 修改数据
   update
        update dept
        set
               loc = '深圳'
        where
               deptno = 50;
   2.3. 删除数据
   delete
        delete from dept where deptno = 50 or deptno = 60;
      -- 删除所有
        delete from dept;
     ** delete 和 truncate 清空表时有什么区别?
        delete删除的数据会保存到回滚段,可以被回滚,效率低.
        truncate直接删除数据不可以回滚,效率高.
        清空表通常使用truncate.

3. 事务
    事务指一组针对数据库操作的命令集合,事务是一个原子操作,不可拆分,必须同时成功或者是同时失败.
   四大特性
   3.1 原子性: 事务时一个原子才做,事务中的操作同时成功或者同时失败
   3.2 一致性: 事务操作之前和之后的数据要保持一致
   3.3 隔离性: 事务之间互不影响,互相并不知道其他事务的存在
   3.4 持久性: 事务操作之后,数据应当永久保存

4. create as(根据查询结果创建表,并将查询到的数据添加到表中)
   create table t_employee
   as
   select ename,job,sal from emp where deptno = 10;

5. insert [as](将全部查询到的数据添加到指定表中,不要as关键字)
   insert into bonus select * from t_temp;

   如何快速去除表中的重复数据
   -- copy一个临时表
   create table t_temp as select distinct * from bonus;
   -- 清空原表
   truncate table bonus;
   -- 将临时数据导入原表
   insert into bonus select * from t_temp;
   commit;
   drop table t_temp;

6. 简单查询 select
   6.1 单表查询
        select * from emp where sal >= (select max(sal) from emp)
       -- * 查询所有列
       select * from emp;
       -- 查询指定列
       select ename,sal from emp;
       -- 去重复
       select distinct  job,mgr from emp;
       -- 起别名
       select 
          e.ename 姓名,e.sal 工资 
       from 
          emp e
       -- 单条件
       select * from emp where sal < 3000
       select * from emp where comm is not null
       select * from emp where comm is null
       select * from emp where ename = 'SCOTT'
       ** 
          =:  等于
          <> != ^=:  不等于(通常用<>)
          in: 多值匹配(匹配in中的列表值) select * from emp where empno in (7369,7788,7934)
          not in:
          between and: select * from emp where sal between 1100 and 2000 (相当于 sal >= 1100 and sal <= 2000)
          not between:
          is null:
          is not null:
          like: 模式匹配,只能用于字符串(%表示任意个任意字符,_表示一个任意字符)
                select * from emp where ename like 'MAR%'
                select * from emp where ename like '%S'
                select * from emp where ename like '%A%'
                select * from emp where ename like 'A____'
           not like:

       -- 多条件(and)
       select 
          * 
       from 
          emp 
       where 
          comm is not null 
          and 
          comm > 0
       -- 排序
       select * from emp order by sal asc  -- 升序
       select * from emp order by sal desc -- 降序
       -- 多列组合排序
       select * 
       from emp 
       order by 
             sal desc,comm desc

       -- 分组查询
          select
             job,sum(sal),max(sal),min(sal),avg(sal),count(empno),count(comm)
          from
             emp
          group by
             job
          having
             avg(sal) > 1500
       -- 聚合函数
          sum()   求和(对当前组的所有值进行求和)
          max()   取最大值
          min()   取最小值
          avg()   取平均值
          count() 求有效值数,通常用于求记录数
      -- 在oracle中用count查询记录数时,通常使用一个常量值,如:1 而不是用*或者是列值,效率高
        select job,count(1)
        from emp
        group by job

7. 日期时间处理
        create table t_user (
               id varchar2(50) primary key,name varchar2(20),birthday date
        )
       to_date(将字符串转日期)
       insert into t_user values('10001','zhangsan',sysdate);
       insert into t_user values('10002',to_date('1992-09-15','YYYY-MM-DD'));
       insert into t_user values('10003',to_date('1992-09-15 18:30:20','YYYY-MM-DD HH24:MI:SS'));

       to_char(将日期转字符串)
       select id,name,to_char(birthday,'YYYY/MM/DD HH24:MI:SS') from t_user
8. 虚表dual
   select sysdate from dual;
   select 20+30 from dual;


----------
1. 集合操作
  1.1 union（合并查询结果，并集，会去重复）
    select ename,sal,deptno from emp where sal > 2000
    union
    select ename,deptno from emp where deptno = 20

  1.2 union all（并集，不会去重复）
    select ename,deptno from emp where sal > 2000
    union all
    select ename,deptno from emp where deptno = 20

  1.3 intersect(求交集)
    select ename,deptno from emp where sal > 2000
    intersect
    select ename,deptno from emp where deptno = 20

  1.3 minus(返回从第一个查询结果中排除第二个查询中出现的行。)
    select ename,deptno from emp where sal > 2000
    minus
    selec ename,deptno from emp where deptno = 20

2. 连接操作
   在oracle中使用||来连接两个字符串
   select ename || '(' || sal || ')' es from emp;
   操作符优先级: 
           ()
            大于
          运算符(+-*/) 
            大于
          连接符(||) 
            大于
          比较运算(=,!=,<>,^=,>,<,>=,<=,is null,like,is not null,not like,between .. and,not between .. and)
            大于
           not 
            大于
           and
            大于
            or
3. 常用函数
   3.1 日期函数
       add_months
       last_day
       months_between
       next_day
       round
       trunc: 从指定位置处截断 select trunc(sysdate,'yyyy') from dual
       extract

   3.2 字符函数
       3.2.1 TRIM
           select ltrim('abababababbbbbbbabababab','ab') from dual;
           select rtrim('ababababccccababab','ab') from dual;
           select rtrim(ltrim( 'abababcccccababab','ab'),'ab') from dual;

       3.2.2 translate & replace
           -- 字符一对一替换
           select translate('hehehehehehehhe','h','lol') from dual;
           -- 整串替换
           select replace('hehehhehehehehhehehe','he','lol') from dual;
       3.2.3 instr: 相当于indexOf,从1开始
       3.2.4 substr
       3.2.5 Concat 
       3.2.6 upper lower
       3.2.7 initcap 首字母大写
       3.2.8 length: select length('heheh') from dual;
       3.2.9 decode: 条件匹配函数,相当于switch
             select
                     ename,decode(job,'ANALYST','分析师','CLERK','职员','SALESMAN','销售员',job
                            )
             from emp

   3.3 数字函数
      ...
   3.4 转换函数
       to_char
       to_date
       to_number
   3.5 其他函数
       nvl: 如果值为null则显示为指定值
          select ename,nvl(comm,0) from emp
       nvl2: 如果值不为空则取第二个参数,如果为空则取第三个参数
          select ename,comm,nvl2(comm,100,0) from emp
       nullif: 如果两个值相同,则为null,否则取第一个参数
          select nullif('aa','aa') from dual

   3.6 分析函数
       row_number: 连续排位
           select 
                  ename,row_number() over(order by sal desc,comm asc)
           from emp

      rank: 等值同名,会跳跃
         select 
                ename,rank() over(order by sal desc)
         from emp

     dense_rank: 等值同名,不会跳跃
         select 
                ename,dense_rank() over(order by sal desc)
         from emp

4. 子查询
    -- 条件时使用子查询
    select * from emp where deptno in (select deptno from dept where dname in ('SALES','RESEARCH'))
    -- 子查询为临时表
    select 
          * 
    from 
         (select * from emp where sal > 2000) t_temp
    where
         t_temp.deptno = 20

    -- 查询列上使用子查询
    select
       ename,(select dname from dept d where d.deptno = e.deptno) dname
    from 
       emp e

5. 连接查询
    *****select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+); 左连接
    select e1.ename,emp e2,dept d where e1.mgr = e2.empno(+) and e1.DEPTNO = d.DEPTNO and e1.ename='SCOTT';
    *****select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;
   5.1 左外连接,以左边表为基准,左边表所有数据都需要被显示
     select
         e.ename,d.dname
     from
         emp e left join dept d
         on
         e.deptno = d.deptno
   5.2 右外连接,以右边表为基准,右边表所有数据都要被显示
    select
         e.ename,d.dname
     from
         emp e right join dept d
         on
         e.deptno = d.deptno
   5.3 全连接,两边表的数据都要显示
     select
         e.ename,d.dname
     from
         emp e full join dept d
         on
         e.deptno = d.deptno
   5.4 内连接,只显示两边对应的数据
     内连接1:
     select
         e.ename,d.dname
     from
         emp e inner join dept d
         on
         e.deptno = d.deptno
     内连接2(多表联查):
     select
         e.ename,d.dname
     from
         emp e,dept d
     where
         e.deptno = d.deptno

   5.5 自关联
        select
           e.ename name,m.ename mgr
        from
               emp e,emp m
        where
               m.empno = e.mgr

    select
               e.ename name,m.ename mgr
        from
               emp e left outer join emp m
               on m.empno = e.mgr

6. exists: 当子查询能查询到数据时,外层SQL的当前数据将被查询出来
    select * from emp e where sal > 1000 and exists(select 1 from dept d where d.deptno = e.deptno and d.dname='ACCOUNTING' )

   not exists:
    select * from emp e where sal > 1000 and not exists(select 1 from dept d where d.deptno = e.deptno and d.dname='ACCOUNTING' )

7. case ... when ... then ... end; 
   用法一:
   select
        ename,(case job
          when 'CTO' then '首席技术官'
          when 'MANAGER' then '网管'
          when 'SBO' then '呵呵'
          else '其他'
        end) job
   from
        emp

   用法二:
   select
        ename,(case 
            when (sal > 5000 and comm is not null) then '土豪'
            when sal > 3000 then '高富帅'
            when sal > 2000 then '中产阶级'
            when sal > 1000 then '贫下中农'
            else '低调'
        end) pt
   from
        emp

  行变列
  select * from t_score
  select
         name,sum((case course when '语文' then score else 0 end )) 语文,sum((case course when '数学' then score else 0 end )) 数学,sum((case course when '英语' then score else 0 end )) 英语
   from t_score
   group by name


----------
1. 同义词
   为现有对象创建一个别名（简化SQL，隐藏对象原有名称）
   私有
   create synonym sy_xxx for emp;
   需要权限
   grant create synonym to scott;
   其他用户使用需要权限
   grant select on emp to zhangsan;

   公有
   create public synonym sy_xxx for emp;

   删除
   drop synonym sy_xxx;
   drop public synonym emp_syn; 
    一般resource角色中没有创建同义词的权限，需要重新赋权限。

2. 序列
   drop sequence seq_test;
   -- 创建序列
   create sequence seq_test
   start with 10
   increment by 100000000
   --maxvalue 99   
   nomaxvalue
   -- minvalue 0    
   nominvalue
   -- cycle         
   nocycle
   cache 20     -- nocache
   ;

   -- seq_test.nextval(取出并生成下一个值)
   select seq_test.nextval from dual;
   -- seq_test.currval（取出当前值，不生成下一个值）
   select seq_test.currval from dual;

   create table t_employee(
          id number(8) primary key,name varchar2(20)
   );

   -- 添加数据时使用
   insert into t_employee(id,name) values(seq_test.nextval,'小明');
   select * from t_employee;
   -- 修改序列
   alter sequence seq_test 
   increment by 1 
   maxvalue 200000000000000000 
   nominvalue 
   nocycle 
   nocache;
   -- 删除序列
   drop sequence seq_test;

3. 视图(存储查询语句并起别名,将复杂sql简化)
   create or replace force view v_e as
    select
        e1.empno,e1.ename,e2.ename mgr,d.dname
      from
         emp e1,dept d
      where
         e2.empno = e1.mgr
         and
         d.deptno = e1.deptno;

    -- 查询使用视图
    select * from v_e t;

4. 索引
   在逻辑和物理上都独立于表一种映射结构.
   提高SQL执行效率,减少磁盘I/O
   4.1 一般索引(当查询条件中出现索引列时,会使用该索引)
       create index idx_ename on emp(ename);

   4.2 组合索引(如果条件少于索引列数,将不会被使用)
       create index idx_name_job on emp(ename,job);
       select * from emp where ename = 'SCOTT' and job = 'SALESMAN'

   4.3 函数索引(在列上使用函数时将使用该索引)
       create index indx_lower_ename on emp(lower(ename));
       select * from emp where lower(ename) = 'scott';

   4.4 唯一索引(要求被索引列必须值唯一,主键自带唯一索引)
       create unique index idx_unique_dname on dept(dname);
       insert into dept(deptno,dname,loc) values(50,'xiaoshou','沙坪坝');
       select * from dept where dname = 'xiaoshou'

   4.5 位图索引(适合用于低基数列,不直接索引rowid,索引列值到rowid的映射)
       create bitmap index idx_bmjob on emp(job);
       select * from emp where job = 'CLERK'

   4.6 反向键索引(反转索引列的每个字节,适合用在连续增长列上)
       create index idx_reverse_mgr on emp(mgr) reverse;
       select * from emp where mgr = '7788';

   4.7 索引组织表 
       create table t_user (
              id varchar2(50) primary key,name varchar2(20)
       ) organization index;

   合理创建索引才能给查询带来好处,如果索引不合理反而会使效率降低
   在索引列上使用like,不等于(<>),is null/is not null,使用函数(如果该列创建了对应的函数索引除外),比较类型不匹配都不会使用到索引.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!