Oracle (2)

一、多表（关联）查询
多表查询也称之为关联查询、多表关联查询等，主要是指通过多个表的关联来获取数据的一种方式。
1.多表映射关系
例如：

2.笛卡尔集

笛卡尔集对于我们数据库的数据查询结果的影响：
1．数据冗余。—笛卡尔集并不是我们所需要的数据.
2.效率问题：查询大量数据的时候会导致你的查询结果时间非常非常非常长。
实际运行环境下，应避免使用全笛卡尔集。

笛卡尔集产生的条件：
1.省略连接条件
2.连接条件无效

3.多表连接的类型
Oracle的多表关联的类型分为：内连接、外连接、自连接。
内连接分为：等值内连接、不等值内连接
外连接分为：左外连接、右外连接、全外连接
自连接是一种特殊的关联，可以包含内连接和外连接的连接方式。

4.关于sql99 -了解
Oracle是关系型数据库，它遵的规范（sql规范）。

5.多表连接的基本语法
Sql99的语法：

Oracle的语法：


sql语句 优化：加上前缀：效率高！

6.内连接
a.等值内连接
等值内连接也称之为等值连接。
【示例】–需求：查询一下员工信息，并且显示其部门名称
SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno;–等值内连接，

b.不等值内连接
不等值内连接也称之为不等值连接。
【示例】需求:查询员工信息,要求显示员工的编号、姓名、月薪、工资级别。

SELECT * FROM emp t1,salgrade t2 WHERE t1.sal >=t2.losal AND t1.sal<=t2.hisal;

7.表的别名
为什么要使用表的别名？

• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。–SQL性能优化方案
• 在不同表中具有相同列名的列，可以用表的别名作为前缀来加以区分。
• 注意：一旦使用了表的别名，则不能再使用表的真名。
• 

8. 更多表的连接

7.外连接
分为左外连接，右外连接，全外连接（oracle特有 mysql没有）。
a.左外连接
需求：查询"所有"员工信息，要求显示员工号，姓名 ,和部门名称–要求使用左外连接。

SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;

b.右外连接

SELECT * FROM emp t1 RIGHT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;

右边表（dept）数据全部显示。

c.如何选择左外和右外
简单的说：左外连接就是左边的表的数据全部显示，右外就是右边的表的数据全部显示。

d.全外连接
左表和右表的数据全部都显示，而且不是笛卡尔集。
–全外连接

SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 on t1.deptno=t2.deptno
UNION
SELECT * FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno=t2.deptno;

8.自连接
自连接的查询的原理：就是将一张表当成两张表来使用.
案例：查询员工信息，要求同时显示员工和员工的领导的姓名。

SELECT *  FROM emp t1,emp t2 WHERE t1.mgr=t2.empno;

二、子查询
子查询也称之为嵌套子句查询。

案例：谁的工资比scott高？

SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='SCOTT');

2.子查询的分类

b.单行子查询
只返回一行。使用单行比较操作符。
<>也可以可以用!=代替，意思一样。

【示例】查询部门名称是SALES的员工信息

SELECT * FROM emp WHERE deptno=(SELECT deptno FROM DEPT WHERE dname ='SALES')

b.空值问题

c.非法使用单行子查询
【示例】需求:查找工作和’SMITH’ ‘ALLEN’ 这两个人的工作一样的员工信息

5.多行子查询

• 返回多行

• 使用多行比较操作符。
  -
  a .in操作符
  需求:查找工作和’SMITH’ ‘ALLEN’ 这两个人的工作一样的员工信息

    SELECT * FROM emp WHERE job IN(SELECT JOB FROM emp WHERE ename IN('SMITH','ALLEN'));
  

b.Any和all操作符
需求:查询工资比30号部门任意一个员工的工资高的员工信息。–面试题
任意一个：比最低的那个高就ok。

SELECT * FROM emp WHERE sal >(SELECT MIN(sal) FROM emp WHERE deptno=30);

–any(多行函数)

SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno=30);

【示例】需求:查询工资比30号部门所有员工的工资高的员工信息。

SELECT * FROM emp WHERE sal>(SELECT MAX (sal) FROM emp WHERE deptno=30);

–all(多个返回记录)–max(sal)

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

分析结果：

三、伪列
a.什么是伪列

• 伪列是在ORACLE中的一个虚拟的列。
• 列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
• 所有的伪列要得到值必须要显式的指定。
  最常用的两个伪列：rownum和rowid。

b.ROWNUM
ROWNUM（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号.每一次查询ROWNUM都会重新生成。
rownum永远按照默认的顺序生成。（不受orderby的影响）
ownum只能使用< <=，不能使用> >=符号，原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。

提示两点：

• ROWNUM是由数据库自己产生的。
• ROWNUM查询的时候自动产生的。

利用行号进行数据分页-重点
order by排序，不会影响到rownum的顺序。rownum永远按照默认的顺序生成。
Mysql使用limit的关键字可以实现分页，但Oracle没有该关键字，无法使用该方法进行分页。
–需求：根据行号查询出第四条到第六条的员工信息。

SELECT ROWNUM,t.* FROM emp t WHERE ROWNUM >=4 AND ROWNUM<=6;

rownum只能使用< <=，不能使用> >=符号.
原因是：Oracle是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。

3.ROWID
ROWID（记录编号）：是表的伪列，是用来标识表中唯一的一条记录，并且间接给出了表行的物理位置，定位表行最快的方式。

• 主键:标识唯一的一条业务数据的标识。主键是给业务给用户用的。不是给数据库用的。
• 记录编号rowid：标识唯一的一条数据的。主要是给数据库用的。类似UUID。
  
  ROWID的产生
  使用insert语句插入数据时，oracle会自动生成rowid并将其值与表数据一起存放到表行中。
  这与rownum有很大不同，rownum不是表中原本的数据，只是在查询的时候才生成的。
  
  ROWID的作用
• 去除重复数据。–面试题—了解
• 在plsql Developer中，加上rowid可以更改数据。
  关于主键和rowid的区别：
  相同点：为了标识唯一一条记录的。
  不同点：
  主键：针对业务数据，用来标识不同的一条业务数据。
  rowid：针对具体数据的，用来标识不同的唯一的一条数据，跟业务无关。

四、数据处理

a. Update
使用工具进行更新数据的操作。（通过rowid伪列）

通过工具修改数据

b. Insert

b.1.批量插入
单条插入语法

INSERT INTO table VALUES

批量插入语法（主要用于将一张表中的数据批量插入到另外一张表中)

INSERT INTO table  SELECT 查询语句

c. Delete
.Delete和truncate区别-面试题

• delete逐条删除，truncate先摧毁表，再重建 。
• 最根本的区别是：delete是DML（可以回滚,还能闪回），truncate是DDL（不可以回滚 ，后面的所事务会讲回滚）
• Delete不会释放空间，truncate会
• delete会产生碎片，truncate不会。

d. Hwm-高水位
高水位线英文全称为high water mark，简称HWM，那什么是高水位呢 ？
在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线（High-warter mark, HWM）。

在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HWM为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。

高水位对Oracle的应用有什么影响呢？
高水位对查询有巨大的影响。而且还浪费空间。
极端例子：数据库有10w条数据，删掉了前面的99999个，我select查询的时候，还是需要扫描10w次，虽然表中只有一条数据。效率还是非常的低！！！！！

如何解决高水位带来的查询效率问题呢？
1．将表数据备份出来，摧毁表再重建（truncate table），然后再将数据导回来。
2．收缩表，整理碎片，可使用变更表的语句：alter table 表名 move.
注意：
move最好是在空闲时做，记得move的是会产生锁的(如果你move的时候需要很长时间，那么别人是不能操作这张表的。排他锁)
move以后记得重建index。