sql总结（二）

一.有关 数据去重

数据去重也是工作中会用到的sql之一

关于数据去重的大致思路都是以 分组为提取基础，以rowid作为重复数据的唯一标识

首先抛出三个问题：

1.假设表中有重复数据，查到哪些数据是重复的

2.去重-用查询语句去重

3.去重-如何删除重复数据，将每份数据都只保留一个

注意三个问题的出发点不同

1是查重复的数据

2是要求用select查出所有的数据且不重复

3.是要求用删除语句删掉所有多余的数据

代码如下：

--典型的重复数据问题
1.假设表中有重复数据，查到哪些数据是重复的
SELECT *
  FROM EMP
 WHERE EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(*) > 1);
--即可查到重复的数据

2.去重-用查询语句去重
法一： 
SELECT *
  FROM EMP
 WHERE ROWID IN
       (SELECT MAX(ROWID)
          FROM EMP E
         WHERE EMPNO IN (SELECT EMPNO FROM EMP M WHERE E.EMPNO = M.EMPNO)
         GROUP BY EMPNO);

法二：
SELECT EMPNO, ENAME, MGR, HIREDATE, SAL, COMM, DEPTNO, GRO
  FROM (SELECT EMPNO,
               ENAME,
               MGR,
               HIREDATE,
               SAL,
               COMM,
               DEPTNO,
               ROW_NUMBER() OVER(PARTITION BY EMPNO ORDER BY EMPNO) GRO
          FROM EMP)
 WHERE GRO = 1;
--怎么做到只取每个区间的的第一个？
--怎么做到只取一个组的一个？：取该组最大或最小的rowid
3.去重-如何删除重复数据，将每份数据都只保留一个

DELETE FROM EMP
 WHERE EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(*) > 1)
   AND ROWID NOT IN
       (SELECT MAX(ROWID) FROM EMP GROUP BY EMPNO);

开始验证.首先将EMP表的主键消除

ALTER TABLE EMP DROP CONSTRAINT PK_EMP;
--若不知道EMP的主键名称，可以使用数据字典来查询约束名称

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME ='EMP';

数据字典：

可以看到主键被删除了 

 

消除后添加重复数据即可

INSERT INTO EMP(EMPNO) VALUES(7369);
INSERT INTO EMP(EMPNO) VALUES(7369);
INSERT INTO EMP(EMPNO) VALUES(7369);

SELECT * FROM EMP;

 我的emp表原本只有16条，现在添加了三个重复数据，重复empno=7369；

开始验证问题一：

问题二：

法一：

 

 法二：

 这里需要注意的是，如果用开窗函数则需要额外添加表的字段

问题三：

 

 执行后显示 '三行删除，耗时0.002秒'

查看emp表

说明语句没问题. 

二.浅谈索引失效

索引失效只有一个原因：数据扫描方式变成了全盘扫描
所以需要追溯变成全盘扫描的原因

目前我学到的索引失效大致以下几类：
1.使用模糊查询时 出现 '%A'的情况，即%在前面
此类型失效的原因要涉及到索引结构与索引引擎搜索方式
索引是在对应数据列的大概位置开始进行搜索
以叶子节点储存的地址或者数据为匹配路径
以遍历树来匹配数据
所以需要匹配的出发点，否则索引失效

2.对索引使用函数
索引提升sql效率的方式即是 将指定字段的数据和rowid存入叶子节点
以加快查询效率
注意存入的数据是原数据
使用函数后返回的值已经不是索引中原本的值 
索引失效

3.隐式转换
当索引字段是字符串而表达式是数值时会导致索引失效
当索引字段是数值而表达式是字符串时不会导致索引失效
 当比较一个字符型和数值型的值时，ORACLE会把字符型的值隐式转换为数值型。
这种隐式转换总是将字符串转为数值类型
当字段被隐式转换时相当于使用函数
索引失效

以上是大多数失效原因，还有一些小众的原因:
如果使用or逻辑运算符 对条件筛选时，所有字段都需要是被索引标记的字段
如果有任一字段没有被标记。那么索引就会失效

因为如果条件列不是索引列，满足or的要求，ORACLE会使用全盘扫描

三.