有关SQL

1.工作中常用sql排行

①group by(用来聚合也时常用来去重注意用groupby select 后要跟聚合字段)
②where(SQL计算之前进行过滤,对性能是极大的提高,代码测试阶段where条件下尽量跟分区)
③having(SQL计算之后进行过滤,它的使用会极大提升的代码的简洁性,同样它的功能十分强大)
④时间函数(时间与我们息息相关时间函数同样也是)
⑤nvl函数(去空变0)
⑥union以及union all(像双面胶一样只要数据结构一样就可以直接拼接 union去重拼接,union all不去重)
⑦row_number,rank_number,dence_rank rank函数(给函数标上rank序号)
⑧窗口函数 over(partition by t1 order by t2)(常与rank函数一同使用)
(⑦⑧使用详细可看博客https://blog.csdn.net/shaiguchun9503/article/details/82349050)

2.工作中常用sql小技巧

①运行代码前进行校检代码逻辑性是否正确使用explain sql语句(运行SQL前检验可以提前发现语法错误,减少资源浪费,提高开发效率,强烈建议每次运行前使用explain进行检验)
②查看异常数据null
③查看表分区下具体数据量(show rowcount extended table)
④代码冗余且可复用可创建临时表
(CREATE TABLE if not exists tem_table select * from table)
注:中间表切记从小维度往大维度走尽量减少直接创建大维度表.
例:
需求:创建一个月维度基于base表的中间表
一、先创建日维度表
二、再用日维度表生成月维度表
这样做的好处,增加中间表的复用性,减少大表join带来的资源浪费.

3.SQL进阶(这是重点)

1.关于null值

null值常见问题

• null 值不是值也不是变量 连接null值常用is null 或者 is not null 来判断是否为空值

    错误:把null当成值
    case col_1
    when 1 then 'o'
    when null then 'x'
    end
    
    正确语法
    
    case when col_1 =1 then 'O'
    when col_1 is null then 'x'
    end
  

• 2.null值下not in 不等价于 not exists
  ''not in 不等价于 not exists" ,虽然in 和 exists 是可以互换但是在有null情况下不可使用not in 来代替 not exists,not in 在有null情况下默认生成结果为空值,原理涉及到sql的三值原理(分别为true ,false ,以及因为null存在的unknow)

    	CREATE TABLE Class_A
    	(name VARCHAR(16) PRIMARY KEY,
    	age  INTEGER,
    	city VARCHAR(16) NOT NULL );
    	
    	CREATE TABLE Class_B
    	(name VARCHAR(16) PRIMARY KEY,
    	age  INTEGER,
    	city VARCHAR(16) NOT NULL );
    	
    	INSERT INTO Class_A VALUES('布朗', 22, '东京');
    	INSERT INTO Class_A VALUES('拉里',   19, '埼玉');
    	INSERT INTO Class_A VALUES('伯杰',   21, '千叶');
    	
    	INSERT INTO Class_B VALUES('齐藤',  22,   '东京');
    	INSERT INTO Class_B VALUES('田尻',  23,   '东京');
    	INSERT INTO Class_B VALUES('山田',  NULL, '东京');
    	INSERT INTO Class_B VALUES('和泉',  18,   '千叶');
    	INSERT INTO Class_B VALUES('武田',  20,   '千叶');
    	INSERT INTO Class_B VALUES('石川',  19,   '神奈川');
  
    	CREATE TABLE Students
    	(name VARCHAR(16) PRIMARY KEY,
    	 age  INTEGER );
    	
    	INSERT INTO Students VALUES('布朗', 22);
    	INSERT INTO Students VALUES('拉里',   19);
    	INSERT INTO Students VALUES('约翰',   NULL);
    	INSERT INTO Students VALUES('伯杰', 21);
  

• 需求: 查询与B班住在东京的学生年龄不同的A班学生的SQL语句？

    /*错误sql语句,结果为空值*/
    SELECT *
      FROM Class_A
     WHERE age NOT IN ( SELECT age
                          FROM Class_B
                         WHERE city = '东京' );
    /* 正确的SQL语句：拉里和伯杰将被查询到 */
    SELECT *
      FROM Class_A A
     WHERE NOT EXISTS ( SELECT *
                          FROM Class_B B
                         WHERE A.age = B.age
                           AND B.city = '东京' );
  

• 在null下使用限定谓词all也会爆空值

    /* 查询比B班住在东京的所有学生年龄都小的A班学生 */
    SELECT *
    FROM Class_A
    WHERE age < ALL ( SELECT age
                  FROM Class_B
                 WHERE city = '东京' );
  

null值克星(重点):

1.极值函数(max,min) 忽略null值
2.聚合函数:count(具体列名)忽略null值 注:count(*)包含null
avg,sum均忽略null值
3.where条件下过滤null值又或者是使用nvl等函数转换null值