从实践中学习Oracle SQL笔记



Oracle SQL-何明
  sql
    Data Manipulation Language：DML
      select
      insert
        insert into worker (empno,ename,sal) values(66,'张三‘,4600);
        insert into worker(empno,ename,sal) select empno,ename,sal from emp where job like 'sal%";
          在使用子查询向某表插入数据时不能使用values关键字
          insert子句中的列数和数据类型必须与子查询中的列数和数据类型一致
        insert的替代变量
          insert into emp_DML(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(&id,'&name','&job',7689,to_date('&hiredate','yyyy mm dd'),666,77,66);
      update
        update emp set sal=sal*1.5 where ename='smith';
        基于另外的表修改记录：update emp set sal=(select losal from salgrade where grade=1) where sal<(select losal from salgrade where grade=1);
        多列子查询修改记录:update emp set (job,sal)=(select job,sal from emp where empno=7369) where jon='保安';
      delete
        delete from emp where (job='manage' and sal>2500) or (job='salesman' and sal>1300);
        基于另外的表删除:delete from emp where deptno=(select deptno from dept  where dname=upper('美容’);
    Data  Definition Language：DDL
      create
        table
          create table product (p_code number(6), p_name varchar2(30));
            varchar2(size)变长字符型数据，默认值和最小值都是1，最大值2000
            char(size):定长字符型数据，最大值2000
            date：日期型数据
            number(P,S)数字型数据，1<p<38,-84<s<124
            LOB：LARGE OBJECT
              clob
              blob
              bfile
              raw
          create table worker as  select empno,ename,job from emp;
        user
          CREATE USER 用户名 IDENTIFIED BY口令；
      alter
        table
          alter table worker add (hiredate date);
          alter table worker modify (hireddate default sysdate);
          alter table worker drop column hiredate;
          alter table worker set unsed(income);
          alter table worker drop unused columns;
        user
          ALTER USER CAT DEFAULT TABLESPACE USERS QUOTA 20M ON USERS;
      rename
        rename 对象原来名字 对象现在的名字
      truncate
        truncate table worker；清空worker数据，保留表结构
      drop
        drop table worker；删除表
    Transaction Control
      commit
        set autocommit on
      rollback
    Data Control Language：DCL
      grant
      revoke
  运算符
    连接运算符 ||
    distinct运算符
      作用于多列时，每一种组合显示一行
      oracle通过排序完成此功能，效率较低
    比较运算符
      >
      >=
      <
      <=
      =
      <>
      !=
      between...and... not between...and...
        包含上下限
        可用于数字，字符（需用单引号），日期型（需用单引号）
      in、not in
      like
        %:0或者多个字符
        _：一个且只能是一个字符
      转义符\
    集合操作符
      分类
        union
        union all
        minus
        intersect:返回两查询结果相同的
      人造列
        select d.location_id,department_name "部门", to_char(null) "仓库地址" from departments d union  select l.location_id,to_char(null) "部门", l.state_province from locations l
  子句
    where子句
      字符串区分大小写
      不能用分组函数
    order by子句
      排序
        ASC 升序（默认）
        DESC 降序
      若使用order by子句，则其一定是最后一个子句
      可使用别名、列名、列号、表达式进行排序
      多列排序顺序为从左至右为先后
    group by子句
      在使用分组函数后，则在任何不在分组函数中的列或表达式必须在group by子句中
      进行分组后，结果更丰富，但是系统效率冲击较大
    having子句
      处理顺序
        首先对记录进行分组
        把所得到的分组应用于分组函数
        最后显示满足having子句所指定的条件的结果
  sqlplus 命令
    desc emp
    show all查看所有环境变量，使用set 进行设置
    set linesize 80:每行显示宽度80个字符
    set pagesize 50：报告的显示长度为25行
    set heading off
    set echo on
    L和n text
    n（设置当前行）A【ppend】附加
    del
    c【hange】
    /：run命令
    col[umn][{列名|别名}[可选项】】
      col deptno for 999999:dept字段占6位数字
      col loc
    spool命令
      保存命令后所有显示
      spool d:\sql\output
      spool off
    脚本文件
      生成：save d:\sql\sample
      将脚本文件装入缓冲区：get  d:\sql\sample.sql
      编辑：ed d:\sql\sample
      运行：start或者@d:\sql\sample.sql
  函数
    单行函数
    null处理
      null值
        is null
        is not null
      升序，null最后
      逻辑表达式
        and优先级F-NULL-T
        or的优先级T-NULL-F
        not
      函数
        NVL
          对数字型：NVL（COMM，0）
            select avg(nvl(comm,0)) from emp;
            select sum(comm),count(*),count(comm),avg(comm),avg(nvl(comm,0)) from emp ;
          对字符型：NVL（to_char(comm),'no commission')
          对日期型：NVL（hiredate，‘31-DEC-99’）
        NVL2
        nullif
        decode
          select ename,job, decode(job, 'SALESMAN',sal*1.15 'CLERK',SAL*1.2, SAL*1.4) "NEW SALARY" FROM EMP;
        coalesce
    分组函数，只能嵌套两层
      count
        count({*[distinct|all] 表达式})
        返回非空行数目
        可以通过distinct返回去重复数目
      avg
      sum
      max
      min
      stddev
      variance
  多表连接
    连接类型
      equi join相等连接（内连接）
        笛卡尔连接，通过where子句输出
      self join自连接
        自表连接：select w.empno,w.ename,w.job,w.mgr from emp w,emp m where w.mgr=m.empno
      non—equijoin不等连接
        select...where e.sal between s.losal and s.hisal...;
      outer join外连接
        运算符：（+）
        +表示补充，即哪个表有加号，这个表就是匹配表。若加号写在右表，左表就是全部显示，故是左连接
        外连接类型
          left outer join左外连接，左表为基础表，右表为匹配表
          right outer join右外连接
          full outer join全外连接
        select empno,ename,sal,emp.deptno,dept.deptno,loc from emp,dept where dept.deptno=emp.deptno(+);
        select empno,ename,sal,emp.deptno,dept.deptno,loc from dept left outer join emp on( dept.deptno=emp.deptno);
    子句
      using子句
        select e.empno,e.ename,e.sal,deptno,d.loc  from emp e  join dept d  using (deptno) ;
      on子句
        select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e join dept d on (e.deptno=d.deptno);
  子查询
    单行子查询
    多行子查询
      操作符
        in
          select empno,ename,job,sal from emp where sal in (select max(sal) from emp group by job);
        any
          select empno,ename,job,sal from emp where sal>any (select avg(sal) from emp group by job);
          <any小于最大的
          >any大于最小的
            select a.ename,a.sal,b.ename,b.sal from emp a left outer join ( select empno,ename,job,sal from emp where sal<any (select avg(sal) from emp group by job) ) b on a.empno=b.empno;
          =any相当于in
        all
          select empno,ename,job,sal from emp where sal>all (select avg(sal) from emp group by job);
          >all 大于最大的
          <all小与最小的
          =all，子查询返回列表中所有值不符合逻辑
    多列子查询
      分类
        成对比较多列子查询
          select empno,ename,job,sal from emp where (sal,job) in (select max(sal),job from emp group by job);
        非成对比较多列子查询
          select empno,ename,job,sal from emp where sal in (select max(sal)  from emp group by job) and job in  (select distinct job from emp);
  数据字典
    分类
      USER_*:有关用户所拥有的对象信息
      ALL_*:有关用户可以访问的对象的信息
      DBA_*:有关整个数据库中对象的信息
    常用语句
      select * from cat[user_catalog]:表名和类型
      select name,created,log_mod  from v$database
      select instance_name,host_name,version,achiver from v$instance;
      select username,created from dba_users; 创建用户及创建时间
  替代变量
    数字：select empno,ename,sal from emp where sal>&salary;
    字符和日期：select empno,ename,sal from emp where sal>'&salary';
    define v_col=job
    accept
      hide
    undefine
    select ename,job,&&v_col from emp order by &v_col;&&绑定变量后需要undefine v_col，否则变量一直在内存中不会改变
  索引和约束
    索引
      create index emp_ename_idx on emp(ename);
      create index emp_sal_idx on scott.emp(sal-2000);基于函数索引的权限问题
      select * from user_indexes;
      检查是否使用索引，运行utlxplan脚本，创建plan_table,在语句前加explan plan for，再查看plan_table
      drop index scott.emp_ename_idx;
    约束constraints
      约束类型
        非空not null
        唯一unique:U
        主键primarykey:P
        外键foreign key:R
          在进行插入操作时，只有操作在子表或从表一端才会产生违反引用完整性的问题，主表不会
          在进行删除操作时，只有操作主表或父表一端。。。
          alter table empcon add constraint empcon_deptno_fk forergn key(deptno) references deptcon(deptno);
        条件check:C
          create table person( id varchar2(1o), name varchar2(20), gender char(2), age number, constraint person_gender_ck  check(gender='F'), constraint person_age_ck check(age between 18 and 30));
      select * from user_constraints;
      select * from user_cons_columns;
  视图
    CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名 [别名[,别名]...] AS 子查询语句 [WITH CHECK OPTION[CONSTRAINT 约束名]] [WITH READ ONLY]
      别名的个数要与列的个数一致
    drop view 视图名
    视图的DML操作限制
    对视图的DML操作都可以转化为对视图所引用表的DML操作
  序列号和同义词
    序列号
      CREATE SEQUENCE [START WITH n] [INCREMENT BY n] ...
        nextval
        currval
      select * from user_sequences
      alter sequence ...
      drop sequence
    同义词
      CREATE  SYNONYM S FOR SUPPIER;
      drop synonym s;
  用户管理
    角色（role）
      select * from role_sys_privs;
        create role animal;
        grant select any table,create table to animal;
        grant animal to dog,cat,pig;
      select * from dba_role_privs;
    权限
      系统权限
        SELECT * FROM SESSION_PRIVS;
        revoke select any table,create table from dog;
      对象权限
        分类
          EXECUTE
            PROCEDURE
          ALTER
            SEQUENCE
            TABLE
          SELECT
            SEQUENCE
            VIEW
            TABLE
          INDEX
            TABLE
          REFERENCES
            TABLE
          INSERT
            VIEW
            TABLE
          UPDATE
            VIEW
            TABLE
          DELETE
            VIEW
            TABLE
        GRANT 对象的权限|ALL[列名【，列名。。。】 on 对象名 TO [用户名|角色名|PUBLIC] [WITH GRANT OPTION]
          grant update(phone,fax) on supplier to cat;
        select * from user_tab_privs_made;

Oracle SQL-何明.mmap
43.9 KB