sql总结(二)"/>
sql总结(二)
一.有关 数据去重
数据去重也是工作中会用到的sql之一
关于数据去重的大致思路都是以 分组为提取基础,以rowid作为重复数据的唯一标识
首先抛出三个问题:
1.假设表中有重复数据,查到哪些数据是重复的2.去重-用查询语句去重3.去重-如何删除重复数据,将每份数据都只保留一个
注意三个问题的出发点不同
1是查重复的数据
2是要求用select查出所有的数据且不重复
3.是要求用删除语句删掉所有多余的数据
代码如下:
--典型的重复数据问题
1.假设表中有重复数据,查到哪些数据是重复的
SELECT *FROM EMPWHERE EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(*) > 1);
--即可查到重复的数据2.去重-用查询语句去重
法一:
SELECT *FROM EMPWHERE ROWID IN(SELECT MAX(ROWID)FROM EMP EWHERE EMPNO IN (SELECT EMPNO FROM EMP M WHERE E.EMPNO = M.EMPNO)GROUP BY EMPNO);法二:
SELECT EMPNO, ENAME, MGR, HIREDATE, SAL, COMM, DEPTNO, GROFROM (SELECT EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO,ROW_NUMBER() OVER(PARTITION BY EMPNO ORDER BY EMPNO) GROFROM EMP)WHERE GRO = 1;
--怎么做到只取每个区间的的第一个?
--怎么做到只取一个组的一个?:取该组最大或最小的rowid
3.去重-如何删除重复数据,将每份数据都只保留一个DELETE FROM EMPWHERE 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会使用全盘扫描
三.
更多推荐
sql总结(二)
发布评论