sql 小笔记

substr()

区倒数N位，从第M位取到结尾 ，用负数就好

 

------------------------------------------

length/lengthb() 【b字节】

字符中含中文情况下

select length("你好")  from dual --2

select lengthB("你好")  from dual --4 

 

nls_database_parameters 看字符集的表

select * fromnls_database_parameters

字符集NLS_CHARACTERSET

 

------------------------------------------

chr()回车符；换行符，单引号

chr(13) 换行

10 回车

9Tab 建

39 单引号

 

------------------------------------------

q'<>'

select q '<adfadf' is 'ABC' >' from dual;   结果：adfadf' is 'ABC'

 

------------------------------------------

trunc()

取系统日期

select trunc(sysdate,'mm') from dual

select trunc(sysdate,'yyyy') from dual

select trunc(sysdate,'hh24') from dual

 

------------------------------------------

Like

select * from b1permit where b1_alt_id like '12CAP%'  只有它走索引

select * from test where al like '%/%%' escape '/'

select * from test where al like '%$%%' escape '$'

 

------------------------------------------

分析函数****用于统计报表

dense_rank()

rank()

cube

Sum

Max Min Lag Lead

Over

Grouping

Grouping  SETS

......

 

------------------------------------------

Null值处理

不为空的正确表达：isnull, is not null, = ''

Order by  null (nulls first/last)

e.g.

select * from qqcard b

orderbyb.cardidnullsfirst;

count(field) 不包括null的，与 count(*)不同

 

不要用notin,要用notexist，会出现bug

因为in里的数据有null时，not in查不出正确结果

 

SQL小技巧

------------------------------------------

逗号分割的字符村转成行

with staffnamea as (select 'sina,baidu,tudou,youku'||','staffname from dual),

staffnameb as (select regexp_substr(staffname,'[^,]+',1,rownum)staffname from staffnamea

           connect by rownum<=length(regexp_replace(staffname,'[^,]+')))

select * from staffnameb;

-------------------------------------------------------------------------------------

以日为单位生成2011年全年的日期

select to_date('2011-01','yyyy-mm') + rownum -1 from dualconnect by rownum <= 365;

-------------------------------------------------------------------------------------

多行分组，按顺序逗号分隔

with tmp_1 as

(select 1 deptno,'bb' ename,2 empno from dual

union all select 1,'aa',1 from dual

union all select 1,'cc',3 from dual

union all select 2,'ddd', 2 from dual

union all select 2, 'aaa', 1 from dual

)

select deptno, 

    ltrim(sys_connect_by_path(ename,','),',')emps 

  from ( select deptno, ename,   row_number()over (partition by deptno order by empno) rn, 

                                count(*) over (partition by deptno) cnt 

      from tmp_1 

  )  

  where level = cnt 

 start with rn = 1 

    connect byprior deptno = deptno and prior rn = rn-1 ;

每次取随机5条记录

select * 

  from ( 

    select b1_per_id3 

    from b1permit  

    order by dbms_random.value() 

  ) 

  where rownum <= 5;

------------------------------------------

常见逻辑错误

1、left join 主从表弄反

2、多个表left join时关联列的条件带全

3、inner join/join 的使用

4、字段 = （子查询），子查询不能有多行的情况

5、left join过滤条件在and后还是where后面

6、b1permit表多个cap type过滤

7、时间范围多了1秒

----------------------

常见性能问题

1、distinct是否需要->Group by 代替

2、union 还是union all

3、order by是否要？ Order by需要这么多列吗？

4、select * 在基本表使用？？？？？？

5、serv_prov_code

6、子查询时未先过滤条件

7、先过滤主表记录，再关联相关从表

8、Exists是上下结构 ，可以转换成左右结构(Leftjoin)，当子查询少时，变成左右结构更好

---

常见隐患

1、多表关联字段名未加前缀 ——逻辑隐患

2、SQL中where条件使用占位符（1=1）——性能隐患

3、Oracle版本不同，新函数 ——版本隐患

4、with语句嵌套层数太多，报表工具不支持

5、长时间运行SQL把数据库查“死” ——性能隐患

6、字段类型不符

7、未考虑当除数可能为0的情况

8、函数重名

9、SQL代码太多

10、In列表记录数量有限制（最大1000）

---

常见书写规范

1、with 语句tem_开头

2、括号对齐

3、多个表带前缀

4、不要有重复代码

5、嵌套子查询要有层次错位，便于阅读

6、驱动表全面

7、尽量不要right join

8、不用占位符

9、不要用select *

10、能关联原表就不要关联子查询