Oracle第五课

一、DQL(data query language)数据查询语言

• 单表查询

  1. 分页

     -- 伪列（物理不存在的列，Oracle对结果进行计算得到的临时列）

     -- 分页用到了伪列rownum,因为这个列是不稳定的，变化的，所以要把这个结果当作下一次查询的数据(select e.*,rownum rn from emp e)，这样在下一个查询中它就成了一个真实存在的列，就可以进行条件的限定

     -- 把伪列转换为实列(用到了子查询，把一个查询作为另一个查询的一部分就是子查询，也叫嵌套查询)
     -- 简单分页，不带排序
     SELECT t.* FROM (SELECT e.*,ROWNUM rn FROM emp e) t WHERE t.rn>(n-1)*SIZE AND t.rn<=n*SIZE;
     -------------------------------
     SELECT t.* FROM (SELECT e.*,ROWNUM rn FROM emp e) t WHERE t.rn>(&n-1)*&SIZE AND t.rn<=&n*&SIZE;
     ------------------------------
     -- 带排序的分页，三层嵌套,&代表变量，在运行时可以赋值
     SELECT * FROM (SELECT t.*,ROWNUM rn FROM (SELECT e.* FROM emp e ORDER BY sal) t) o WHERE o.rn>(&n-1)*&SIZE AND o.rn<=&n*&SIZE;
     ​

  2. 分组（group by）

     -- 列表中的列一定要在group by后面的字段中
     -- 按部门和工作进行分组
     SELECT deptno,job FROM emp GROUP BY deptno,job;
     -- 按工作进行分组
     SELECT job FROM emp GROUP BY job;

• 多表查询

  1. 内连接

     -- 标准写法
     SELECT e.empno,e.ename,e.deptno,d.dname FROM emp e INNER JOIN dept d ON e.deptno= d.deptno;
     -- 常用写法
     SELECT empno,ename,d.deptno,dname FROM emp e,dept d WHERE e.deptno=d.deptno;

     -- 因为两张表中可能存在相同名称的列，此时在sql语句中一定要表明该列是哪一个表，否则会报错，如果列名可以明显区分，则不需要标明是哪个表

     SELECT empno,ename,e.deptno,dname FROM emp e INNER JOIN dept d ON e.deptno= d.deptno;

     -- 上述sql中的deptno在两张表中都有，所以一定要标明表

     -- 内连接的结果是一一对应，第一张表中的数据一定和第二张表中的数据有关联。

  2. 左外连接

     -- 查询的结果中会显示左表和右表一一对应的记录，并且还显示左表中不对应的数据，重点是左表，就是要显示左表中所有记录

     SELECT empno,ename,d.deptno,dname FROM emp LEFT OUTER JOIN dept d ON emp.deptno = d.deptno;

  3. 右外连接

     SELECT e.empno,e.ename,d.deptno,dname FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

  4. 全外连接

     SELECT e.empno,e.ename,d.deptno,dname FROM emp e FULL JOIN dept d ON e.deptno=d.deptno;

二、TCL（transaction contol language)事务控制语言

-- 设置标志
savepoint p1;
-- 插入数据
insert into emp(empno) values(5);
-- 回滚到标志处
rollback to p1;
-- 提交
commit

三、DCL(data control language)数据控制语言

1. 创建角色

   create role 角色名;

2. 分配权限

   grant select on class to 角色名;

四、索引

• 字典中有索引，书中有目录

• 快速查询数据

• 优点：DQL快速查询，为了保证查询速度，可以牺牲一定的空间和DML的操作时间

• 缺点：占空间（在保存正常业务数据的同时，还要额外的存储索引信息),在维护正常业务数据的同时，还要维护索引的数据（占时间）

• 什么情况下适合建立索引

  • 一个条件经常出现在where中，就要考虑是否要建立索引

  • 对于列中的数据区分度比较高的列也可以建立索引（反例：性别不适合）

  • 如果同时又多个查询条件，where c1=a and c2 = b,建立联合索引(c1,c2),索引中的列的顺序和where条件中的顺序尽量一致

    • 索引(c1,c2...)条件是where c1 = a and c2=b 这个是好的，条件是where c2=b这个不是特别理想，或者where c2=b and c1 = a这个也不好

• 索引的分类

  • 聚集索引：会改变记录的物理位置

  • 非聚集索引：数据的顺序和它插入时的顺序一致，在索引中保存的是记录的rowid，找到索引，然后找到rowid，然后找到记录

  • name1	row1
    name2	row3
    name3	row4
    name4	row2

    -- 上面的表格为非聚集索引表格，里面存储了rowid

    -- 姓名列在插入时的数据是name1,name4,name2,name3，建立索引后不会改变插入顺序

    -- 在索引中的顺序是name1,name2,name3,name4

    -- 要找name3，根据算法在索引表里找到name3对应的物理地址(row4),很快定位到name3这条记录