只是简单读了读《oracle查询优化改写》，就让我获益匪浅，想写好sql，这一本书就够了！

柔光的暖阳◎ 2022-11-29 11:54 68阅读 0赞

**目录**

写在前面

基础知识

空值

返回前几行

获取随机数

排序

union

分页（6-10条）

表关联

复制表

日期

日期加减

trunc对于日期的用法

获取时间

判断是否是闰年（只需要判断二月底是哪一天）：

数字

聚集函数使用前先把null转化为0：coalesce(a,0)

ROUND（number\[,decimals\]）

trunc对数字的操作（取整）

其他取大小值

字符串

原样输出字符串Q-quote：  q'\[内容\]'

计算str中逗号出现的次数

字符串中字符替换

正则表达式

实例

相关函数

求余数

返回第一个不是空的值

Case具有两种格式。简单Case函数和Case搜索函数。

translate函数

lag与lead函数

书中还有很多知识点需要去挖掘！

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

# 写在前面 #

前些日子写sql语句，因为之前写sql仅仅局限于简单的增删改查，碰到复杂难写的sql就得冥思苦想，真正意义上的面向百度编程。

简单读了《oracle查询优化改写》，真的是让我豁然开朗，非常建议新人朋友、甚至想要提高自己的技术高人，都有时间来读一读。

**sql语法仅限oracle！！！**

# 基础知识 #

## 空值 ##

筛选条件只能是 is null 或者is not null。

null不支持加减乘除大小相等比较，否则只能为空。

order by 3 nulls first -----------空值排在前

order by 3 nulls lase ------------空值排在后

## 返回前几行 ##

返回前2行（如果要用=号，只能再加一层并且加上rownum进行筛选） ： where rownum<=2

rownum可以在sql语句后面直接where！！

## 获取随机数 ##

dbms\_random.value

dbms\_random.value可以在sql语句后面直接order by！！

## like ##

like '%e\_' ------%代表一个或多个字符，\_代表一个字符。

like '\\\_abc%'  escape'\\' -------转义字符\\就可以匹配出\_abc，escape定义转义字符。

like '\\\\abc%' ------就可以匹配出\\abc。

## 排序 ##

asc 、 desc   升序 降序

order by 3 ----按第三列排序，这种排序可以不用使用字段！！也不用再加一层

order by 3 nulls first -----------空值排在前

order by 3 nulls lase ------------空值排在后

order by 可以直接跟case when 函数，（相当于把这一列提取出来到order by里面了，而不用在列中显示）

## union ##

union是求两个（或者多个）集合的并集（不允许重复元素），得到的集合是\{1，2，3，4，5，6，7，8，9\}；

union all也是求两个（或者多个）集合的并集（允许重复元素），得到的集合是\{1，2，3，4，5，5，6，7，8，9\}；

而minus是求两个集合的差集，并且是前面一个集合去掉重复元素的部分，得到的集合是\{1，2，3，4\}；

## 分页（6-10条） ##

根据rownum，先获取前10条，再在最外层获取6-10条。

## 表关联 ##

1、select \* from A where id in (select id from B)--使用in

2、select \* from A where exists(select B.id from B where B.id=A.id)--使用exists

也可以完全不使用in和exists（这种相当于inner join！）：

3、select A.\* from A,B where A.id=B.id--不使用in和exists

还有not in、not exists表示相反的意思。

left join

right join

full join

inner join

## 复制表 ##

create table test2 as select \* from test;

只复制表的定义：

create table test2 as select \* from test where 1=2;

多表插入（当使用了ALL时，每一个WHEN子句都会进行判断；使用FIRST，FIRST则是按顺序判断WHEN子句，只执行第一个满足条件的WHEN子句，后面的WHEN子句则直接被跳过）：

INSERT ALL

WHEN sum\_orders < 10000 THEN INTO small\_customers

WHEN sum\_orders >= 10000 AND sum\_orders < 100000 THEN INTO medium\_customers

ELSE INTO lager\_customers

SELECT customer\_id, SUM(order\_total) sum\_orders

FROM oe.orders

GROUP BY customer\_id;

# 日期 #

## 日期加减 ##

在oracle中，date类型直接相减得相差天数，加减数字就是加减天数，加减月份要用add_months函数：
    hiredate - 5 -------减五天
    hiredate + 5 -------加五天
    add_months(hiredate,-5) ------减五个月
    add_months(hiredate,5) ------加五个月
    add_months(hiredate,-5*12) ------减五年
    add_months(hiredate,5*12) ------加五年
    hiredate - 5/24/60/60 -----减五秒
    hiredate + 5/24/60/60 -----加五秒
    hiredate - 5/24/60 -----减五分钟
    hiredate + 5/24/60 -----加五分钟
    hiredate - 5/24 -----减五小时
    hiredate + 5/24 -----加五小时
    
    date1 - date2 ----------------间隔天数
    (date1 -date2)*24 --------------间隔小时数
    (date1 -date2)*24*60 --------------间隔分钟数
    (date1 -date2)*24*60*60 --------------间隔秒数
    months_between(date1,date2)------间隔月
    months_between(date1,date2)/12---------间隔年

## trunc对于日期的用法 ##

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
           to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss')
      from dual t;--sysdate和trunc(sysdate)是不一样的 sysdate在当天的零时零分零秒等于trunc(sysdate)
    select trunc(sysdate, 'dd'), trunc(sysdate) from dual t; --今天 20140703 零时零分零秒
    select to_char(trunc(sysdate, 'dd'), 'yyyy/mm/dd hh24:mi:ss'),
           to_char(trunc(sysdate), 'yyyy/mm/dd hh24:mi:ss')
      from dual;
    
    select trunc(sysdate-to_date('20140709 23:12:12','yyyy/mm/dd hh24:mi:ss')) from dual t;--两个日期相差的天数
    select trunc(sysdate-1) from dual t;--昨天
    select trunc(sysdate+1) from dual t;--明天
    select trunc(sysdate, 'yy') from dual;--当年第一天
    select trunc(sysdate, 'year') from dual;--当年第一天
    select trunc(sysdate, 'yyyy') from dual;--当年第一天
    select trunc(sysdate, 'q') from dual;--当前时间所在的季度的第一天
    select trunc(sysdate, 'mm') from dual;--当月第一天
    select trunc(sysdate, 'month') from dual;--当月第一天
    select trunc(sysdate, 'd') from dual;--返回本周的第一天（周日为第一天）
    select trunc(sysdate,'day') from dual;--返回本周的第一天（周日为第一天）
    select trunc(sysdate, 'iw') from dual;--本周第二天（周日为第一天）
    select trunc(sysdate, 'hh') from dual; --当前时间，精确到小时
    select trunc(sysdate, 'hh24') from dual;--当前时间。精确到小时
    select trunc(sysdate, 'mi') from dual;--当前时间。精确到分钟 没有精确到秒的精度

## 获取时间 ##

to_number(to_char(date1,'hh24'))-----时
    to_number(to_char(date1,'mi'))-----分 
    to_number(to_char(date1,'ss'))-----秒
    to_number(to_char(date1,'dd'))-----日
    to_number(to_char(date1,'mm'))-----月
    to_number(to_char(date1,'yyyy'))-----年
    to_number(to_char(date1,'ddd'))-----年内第几天
    trunc(date1,'dd')----一天之始
    trunc(date1,'day')----周初
    trunc(date1,'mm')----月初
    last_day(date1)-----月末
    add_months(trunc(date1,'mm'),1)---下月初
    trunc(date1,'yy')-----年初
    to_char(date1,'day')----周几
    to_char(date1,'month')----月份
    next_day(date1,1) ---- 下个周日
    
    获取时间（to_char获取的是字符串，extract获取的是数字类型）：
    extract(YEARfrom date1)--------年
    extract(MONTH from date1)--------月
    extract(DAY from date1)--------日
    extract(HOUR from date1)--------小时
    extract(MINUTE from date1)--------分钟
    extract(SECOND from date1)--------秒

## 判断是否是闰年（只需要判断二月底是哪一天）： ##

last\_day(add\_months(trunc(hiredate,'y'),1)) as 二月底

# 数字 #

## 聚集函数使用前先把null转化为0：coalesce(a,0) ##

avg(a) ----平均值

min(a) -------最小值

max(a) ------ 最大值

sum(a) ------总数

count(\*) -----总行数

sum(a) over(order by b) ----从第一行到当前行的总和，（先根据b排序之后）

## ROUND（number\[,decimals\]） ##

四舍五入，decimals为可选值，表示小数点后的位数。可为负数！

## trunc对数字的操作（取整） ##

/*
    TRUNC（number,num_digits）
    Number 须要截尾取整的数字。
    Num_digits 用于指定取整精度的数字。
    Num_digits 的默认值为 0。
    正数向后截取，负数向前截取
    TRUNC()函数截取时不进行四舍五入
    */
    select trunc(123.458) from dual; --123
    select trunc(123.458, 0) from dual; --123
    select trunc(123.458, 1) from dual; --123.4
    select trunc(123.458, -1) from dual; --120
    select trunc(123.458, -4) from dual; --0
    select trunc(123.458, 4) from dual; --123.458
    select trunc(123) from dual; --123
    select trunc(123, 1) from dual; --123
    select trunc(123, -1) from dual; --120

## 其他取大小值 ##

min(sal) over(partition by deptno) 部门最低工资,   
    max(sal) over(partition by deptno) 部门最高工资
    
    lead(sal, 1, 0) over(partition by deptno order by sal)  比自己工资高的部门前一个,    
    lag(sal, 1, 0) over(partition by deptno order by sal)  比自己工资低的部门后一个,    
    nvl(lead(sal) over(partition by deptno order by sal) - sal, 0)  比自己工资高的部门前一个差额,     
    nvl(sal - lag(sal) over(partition by deptno order by sal), 0)   比自己工资高的部门后一个差额
    
    取最大值最小值也可以用：
    first_value(a) over(partition by deptno)
    last_value(a) over(partition by deptno)

# 字符串 #

substr(a,1,2)--------截取a字符串的从第1个开始数2个字符。

lower('A') --------字符串替换成小写

listagg(a,',') within group(order by b)-----用在group by中，多个列用逗号间隔显示

## 原样输出字符串Q-quote：  q'\[内容\]' ##

SELECT  Q'[I'm a boy,my name is 'david']'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'(I'm a boy,my name is 'david')'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'{I'm a boy,my name is 'david'}'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'|I'm a boy,my name is 'david'|'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'aI'm a boy,my name is 'david'a'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'2I'm a boy,my name is 'david'2'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'@I'm a boy,my name is 'david'@'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'*I'm a boy,my name is 'david'*'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'#I'm a boy,my name is 'david'#'  FROM  DUAL;  --结果：I'm a boy,my name is 'david'
    SELECT  Q'[[@#$%^&*~?/" ']]' FROM  DUAL;   --结果：[@#$%^&*~?/" ']

## 计算str中逗号出现的次数 ##

regexp\_count(str,',')-------计算str中逗号出现的次数

## 字符串中字符替换 ##

replace(str\_source,str1,str2)  把 str\_source 中 str1 字符串替换为 str2 字符串，当 str2 为 null 或'' 时，把str\_source 中的 str1 字符串剔除

replace(str\_source,str1)         把str\_source 中的 str1 字符串剔除

regexp\_replace(str\_source,pattern\_str,rep\_str) 支持正则表达式，用法类似于 replace，但功能更强大

regexp\_replace(str\_source,pattern\_str)   把 str\_source 中的 pattern\_str 字符串剔除

translate(str\_source,chr1,chr2) 以字符为单位，把 str\_source 中的 chr1 字符对应替换为 chr2。如果 chr1 比chr2 长，那么在 chr1 中而不在 chr2 中的字符将被剔除，因为没有对应的替换字符。需注意 chr2 不能为 null 或''，否则返回值也为空 （translate(str,'1abc','1')---去除abc）

比方：

select REGEXP\_SUBSTR(‘虹桥/北京’,’\[^/\]+’,1,1) from dual -----返回的是虹桥

select REGEXP\_SUBSTR(‘虹桥/北京’,’\[^/\]+’,1,2) from dual----------返回的是北京

第一个參数是要解析的字符串，第二个是正在表达式，第三个表示字符串解析时的起始位置。比方：

select REGEXP\_SUBSTR(‘虹桥/北京’,’\[^/\]+’,2,1) from dual------返回的是桥

第四个參数表示取拆分后数组里的第几个值。

## 正则表达式 ##

ORACLE中的支持正则表达式的函数主要有下面四个：

1，REGEXP\_LIKE ：与LIKE的功能相似

2，REGEXP\_INSTR ：与INSTR的功能相似

3，REGEXP\_SUBSTR ：与SUBSTR的功能相似

4，REGEXP\_REPLACE ：与REPLACE的功能相似

5，REGEXP\_COUNT  ： regexp\_count(str,',')-------计算str中逗号出现的次数

它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，

但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（\_）字符。

POSIX 正则表达式由标准的元字符（metacharacters）所构成：

'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹

配 '/n' 或 '/r'。

'.' 匹配除换行符之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'+' 匹配前面的子表达式一次或多次。

'\*' 匹配前面的子表达式零次或多次。

'|' 指明两项之间的一个选择。例子'^(\[a-z\]+|\[0-9\]+)$'表示所有小写字母或数字组合成的

字符串。

'( )' 标记一个子表达式的开始和结束位置。

'\[\]' 标记一个中括号表达式。

'\{m,n\}' 一个精确地出现次数范围，m=<出现次数<=n，'\{m\}'表示出现m次，'\{m,\}'表示至少

出现m次。

/num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。

字符簇：

\[\[:alpha:\]\] 任何字母。

\[\[:digit:\]\] 任何数字。

\[\[:alnum:\]\] 任何字母和数字。

\[\[:space:\]\] 任何白字符。

\[\[:upper:\]\] 任何大写字母。

\[\[:lower:\]\] 任何小写字母。

\[\[:punct:\]\] 任何标点符号。

\[\[:xdigit:\]\] 任何16进制的数字，相当于\[0-9a-fA-F\]。

各种操作符的运算优先级

/转义符

(), (?:), (?=), \[\] 圆括号和方括号

\*, +, ?, \{n\}, \{n,\}, \{n,m\} 限定符

^, $, anymetacharacter 位置和顺序

\*/

## 实例 ##

\--创建表

create table fzq

(

id varchar(4),

value varchar(10)

);

\--数据插入

insert into fzq values

('1','1234560');

insert into fzq values

('2','1234560');

insert into fzq values

('3','1b3b560');

insert into fzq values

('4','abc');

insert into fzq values

('5','abcde');

insert into fzq values

('6','ADREasx');

insert into fzq values

('7','123 45');

insert into fzq values

('8','adc de');

insert into fzq values

('9','adc,.de');

insert into fzq values

('10','1B');

insert into fzq values

('10','abcbvbnb');

insert into fzq values

('11','11114560');

insert into fzq values

('11','11124560');

\--regexp\_like

\--查询value中以1开头60结束的记录并且长度是7位

select \* from fzq where value like '1\_\_\_\_60';

select \* from fzq where regexp\_like(value,'1....60');

\--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。

\--使用like就不是很好实现了。

select \* from fzq where regexp\_like(value,'1\[0-9\]\{4\}60');

\-- 也可以这样实现，使用字符集。

select \* from fzq where regexp\_like(value,'1\[\[:digit:\]\]\{4\}60');

\-- 查询value中不是纯数字的记录

select \* from fzq where not regexp\_like(value,'^\[\[:digit:\]\]+$');

\-- 查询value中不包含任何数字的记录。

select \* from fzq where regexp\_like(value,'^\[^\[:digit:\]\]+$');

\--查询以12或者1b开头的记录.不区分大小写。

select \* from fzq where regexp\_like(value,'^1\[2b\]','i');

\--查询以12或者1b开头的记录.区分大小写。

select \* from fzq where regexp\_like(value,'^1\[2B\]');

\-- 查询数据中包含空白的记录。

select \* from fzq where regexp\_like(value,'\[\[:space:\]\]');

\--查询所有包含小写字母或者数字的记录。

select \* from fzq where regexp\_like(value,'^(\[a-z\]+|\[0-9\]+)$');

\--查询任何包含标点符号的记录。

select \* from fzq where regexp\_like(value,'\[\[:punct:\]\]');

# 相关函数 #

## 求余数 ##

mod(a,2)---求余数

## 返回第一个不是空的值 ##

只能写两个参数： nvl(a,b)

可以写多个参数 ： coalesce(a,b,c,d)

## Case具有两种格式。简单Case函数和Case搜索函数。 ##

\--简单Case函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

\--Case搜索函数

CASE WHEN sex = '1' THEN '男'

WHEN sex = '2' THEN '女'

ELSE '其他' END

## translate函数 ##

translate(a,b,c)------把a中b的内容依次替代为c的内容

translate('ab你好bcadefg','abcdefg','1234567')  ---------12你好2314567

translate(a,b,'') ----------结果为null

translate('ab你好bcadefg','1abcdefg','1')-------结果为'你好'

## lag与lead函数 ##

lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

over()表示 lag()与lead()操作的数据都在over()的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b表示以a字段进行分组，再 以b字段进行排序，对数据进行查询。

例如：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

# 书中还有很多知识点需要去挖掘！ #

只是简单读了读《oracle查询优化改写》，就让我获益匪浅，想写好sql，这一本书就够了！

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Maven入门，读完这篇就够了

相关 Java 创建线程读这一篇就够了

相关学习SQL Server这一篇就够了

相关只是简单读了读《oracle查询优化改写》，就让我获益匪浅，想写好sql，这一本书就够了！

相关学习Oracle这一篇就够了

相关想理解MySQL主从原理？看这本书就够了

相关 Go 入门这一本就够了

相关 Maven入门，读这篇文章就够了

相关学习 Node.js 一本书就够了【送书】

相关 Maven入门，读这篇文章就够了