SQL相关笔记整理

1、去重并分组统计；

select
count(1)
from 
 (
  select bb.certid,
  count(distinct flag) num
  from  bb
  group by certid
  having count(distinct flag)>1

 )a

2、执行顺序

from>where>group（含聚合）>having>order>select。

3、关于where、group by和having顺序；

查询大于等于41号部门,并且平均薪资高于1000的部门编号 更推荐第一种: 
SELECT dept_id,AVG(salary) 
FROM s_emp 
WHERE dept_id>=41 
GROUP BY dept_id HAVING AVG(salary)>1000; 


或者 
SELECT dept_id,AVG(salary) 
FROM s_emp 
GROUP BY dept_id  
HAVING dept_id>=41 AND AVG(salary)>1000

4、concat或||是mysql和oracle的字符串连接操作，如果对列进行该函数操作，那么也开会忽略索引的使用。

-- 忽律索引
select ... from .. where first_name || '' || last_name = 'bill gates' ;
-- 使用索引
select ... from .. where first_name = 'bill' and last_name = 'bill gates' ;

5、like：通配符出现在首位，无法使用索引，反之可以。

-- 无法使用索引
select .. from .. where name like '%t%' ;
-- 可以使用索引
select .. from .. where name like 't%' ;

6、order by子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低。

7、not运算无法使用索引，可以改成其他能够使用索引的操作。

-- 索引无效
select .. from .. where sal != 3000 ;
-- 索引生效
select .. from .. where sal < 3000  or sal > 3000;

8、where与having

select .. from .. on .. where .. group by .. having .. order by .. limit ..，以上是sql语句的语法结构，其中on、where和having是有过滤行为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因此如果在having中的过滤行为能够在where中完成，则应该优先考虑where来实现。

9、exists替代in

not in是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或not exists。

-- 正确
SELECT  *
FROM EMP 
WHERE  
	EMPNO > 0 
	AND  EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

-- 错误
SELECT  * 
FROM  EMP 
WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

10、要在索引列上使用函数，这样会停止使用索引，进行全表扫描

-- 错误
SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 
-- 正确
SELECT … FROM  DEPT  WHERE SAL > 25000/12; 

11、union代替or

在索引列上，可以使用union替换or操作。索引列上的or操作会造成全表扫描。

-- 高效: 
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 
UNION 
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = 'MELBOURNE' 

-- 低效: 
SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID=10 OR REGION ='MELBOURNE'

12、

-- 低效: (索引失效) 
SELECT .. FROM  DEPARTMENT  WHERE  DEPT_CODE IS NOT NULL; 
-- 高效: (索引有效) 
SELECT .. FROM  DEPARTMENT  WHERE  DEPT_CODE >=0; 

is null & is not null

如果列可空，避免使用索引。对于多个列使用的索引，起码保证至少有个列不为空。对于多列索引，只有访问了第一个列才会启用索引，如果访问后面的列则使用的是全表扫描。

 

13、NVL

1、函数说明：NVL函数是一个空值转化函数，其表达式为NVL(表达式1，表达式2)，如果表达式1为空值的话，NVL就把表达式2的值返回。否则就返回表达式1的值。

2、函数格式：表达式1和2的数据类型必须是同一种类型。可以是数字型，字符型和日期型。例如：
数字型：NVL(comm,1)
字符型：NVL(TO_CHAT(comm),‘No Commission’)
日期型：NVL(hiredate,‘31-DEC-99’)

例如： NVL(comm,1)
如果comm为空，就返回1。如果 comm 不为空(null),就返回表达式 comm的值。

NVL2
1、函数说明：NVL2的表达式多了一个，其表达式为NVL2(表达式1，表达式2，表达式3)，如果表达式1为空值的话，返回表达式3的值，如果表达式1不为空值的话，则返回表达式2的值。

例如: NVL2(comm,‘sal+comm’,sal)
如果comm为空，就返回sal 的值。如果 comm 不为空(null),就返回表达式 sal+comm的值。

14、union和union all区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

15、STR_TO_DATE(str,format)和DATE_FORMAT(date,format)

STR_TO_DATE(str,format)：
函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值。
SELECT STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') AS result;
----2017-01-06 10:20:30


DATE_FORMAT(date,format)：
函数则是把数据库的日期转换为对应的字符串格式：
SELECT STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d') AS result2;
------2017-01-06

16、HIVE SQL中将字符串类型日期数据，转换为日期型数据

udf.format_datetext(data,'YYYY-MM-DD')