第一章单表查询：

1.3 查找空值 和 null对于不同的函数起不同作用

1
2
replace('strng','a','b')	--在string查找a,然后用b替换
greatest('a','b') --返回最大值，其中字符串和数值比较各有不同，当数和字符串比较会提示错误

1.4 将空值转换为实际值

1
2
3
coalesce 和 nvl ，获取第一个不为空的值，其中coalesce支持多个参数，比nvl更好
coalesce('','','','') 	--同时多个参数
nvl(nvl('',''),'')		--多层嵌套，每次只能比较两个数值

1.7.为列名取有意义的名称

1
2
3
select  A as B from table; 
select A B from table; 
上述两个意思一样，将A列名改为B

1.8 在where 子句中引用取别名的列,需要嵌套一层select

1
select *  from (select A as B from table) where B = "b"

1.9 拼接列 || 双竖杆为拼接符号

1
2
3
4
5
6
7
8
9
10
select A||'的父亲是'||B   as C  from table；
结果：
C
---------------------
A的父亲是B
用来拼接SQL,用于直接用基础数据或者数据字典来批量生产，进行修改
select ‘truncate table ’ || ' owner ' || '.' || table_name || ';' as 清空表
from all_tables 
where owner = 'scott';
--将会对用户为scott拥有的表进行删除操作

1.11 限制返回的行数 rownum

1
select * from tanle where rownum <= 2 ; 最多返回两行

1.12 从表中随机返回n条记录 dbms_random.value 获取随机数

1
2
3
4
5
select * from (select A from table order by dbms_random.value )
where rownum <= 3 ;
注意：先随机排序、再取返回行数
错误写法如下：先取数据再随机排序 
select A from table where rownum <= 3  order by dbms_random.value ;

1.13 模糊查询

1
2
3
4
5
6
7
通配符：%(替代一个或多个字符) _ (替代一个或多个字符)
常用方法：%%
select * from table where A like '%a%';
转义字符 \
select * from table where A like '%\_%' esacpe '\';
转义\ 为 \\ 双斜杆 代表一个 \ 且作为通配符处理
select * from table where A like '%\\_%' esacpe '\';

第二章：给查询结果排序

2.1指定的次序返回查询额几个

1
2
3
select A,B,C from table order by A asc;	asc 按照小到大排序 desc 按照大到小排序
select A,B,C from table order by 1 asc;	排序结果以第一列【排序
注意：数据替代列位置，只能用于order by

2.3 按字符排序

1
2
select A,B,C,substr(A,-4) as 尾号 from table  where rownum <= 5 order by 4 asc;	
通过截取出来的第四列尾号进行排序

2.4 translate 是replace的超集

1
2
3
4
语法：translate(expr , from_string , to_string)
from_string 和 to_string 以字符串为单位，对应字符一一替换
1、to_string 为空，返回空值
2、to_string 对应的位置没有字符，删除from_string 中列出来的字符将会被消掉。！！

2.5 按数字和字母混合字符串中的字母排序(数字排序) 使用translate 函数过滤不需要字符

1
2
3
4
5
6
7
8
9
10
select A|| ' ' || B  as date from table ;  //
其中A 是数据 B 是字母
查询结果为
1234  kell
123	 cat
现在排序查询：
select  date , translate(date,'- 0123456789','-') as date2 
from table 
order by date2 ;
把数字和空格转为空，利用2.4的第二点内容

2.6 处理排序空值

1
2
3
4
用 nvl(A, -1 ) 这种方式为空值赋值，需要对列的类型和保存数据有所了解，如果保存数据变化是，该语句需要维护，不太友好
关键字 nulls first 空值在前 和 null last 空值在后
select A,B,C from table order by 3 nulls first ;
select A,B,C from table order by 3 nulls last;

2.7根据条件取不同的列中的值来排序处理

1
2
3
4
5
6
7
8
9
10
通过新生一列，用多排序方式处理
select A,B,case where A >=0 and A < 5 then a else 2 end as C,D 
from table
where D = '销售部'
order by 3,4 ;
不显示多一行，将case where 放到order by 后面
select A,B,D 
from table
where D = '销售部'
order by case where A >=0 and A < 5 then a else 2 end ,3 ;

第三章：操作多张表

3.1 union all 与空字符串

1
2
3
4
select A,B,C from table 
union all
select A,B,'' as C from table_a
其中第二个 ‘’ as c 相当于 NULL空字符串

3.2 union 与 or

1
2
3
4
//创建索引
create index idx_empno on emp(empo)
create index idx_ename on emp(ename)
union 和 union all 其中union 去掉重复数据

3.4 in exists 和 inner join

1
2
3
4
5
6
inner join  没有重复列时候可以直接使用
select * from table where A in ('','')
select * from table where (A,B) in (select A,B from table2) 
select A,B,C from table a  where exists(select A,B,C from table2 b where a.A= b.A )
select A,B,C from  table inner join table2 on (a.A = b.A)
select A from table a,table2 b where a.A= b.A

3.5 inner join 内关联 、left join 左关联 、right join 右关联 和 full join 外关联(全关联)

3.6 自关联

3.7 not in 、 not exists 和 left join

3.8 外连接的条件不能乱放，否则就内部运行，不按照对应的方式查询

3.9 检测两个表中的数据及对应数据的条数相同 例子p45

3.10 聚集与内连接 p46

3.11 聚集与外连接 p50

3.12 从多个表中返回丢失的数据 p50

1
通过full jin 和 union all 方式显示，关联缺少数据

3.13 多表查询时的空值处理

1
2
注意：与空值比较之后的值依旧为空值，需要转化之后才能比较 
coalesce(a.comn,0)  如果为空则为0

第四章 插入更新与修改

4.1 插入新纪录 p55

1
2
3
4
5
6
7
8
9
create table table_a(
a varchar2(10) default '默认值a',
b varchar2(10) default '默认值b',
c varchar2(10) default '默认值c',
d data default sysdate,
e decimal default 0
);
default 默认值
insert into table_a (a,b,c) values ('a',null,'c');

4.2 阻止对某列的插入 p56

1
创建一个view然后插入值，不过不能再使用default的默认属性了

4.3 复制表的定义及数据 p57

1
2
3
4
a.复制表的定义及数据
create table table_b as select * from table_a;
b.复制标的定义不包括数据,然后再自己新增数据
create table table_b as select * from table_a wherer 1=2;

4.4 用 with check option 限制数据的录入 p57

1
2
3
4
5
6
7
8
9
a.简单的直接添加到表结构内下 constraints  添加约束条件
ps: alter table_a add constraints ch_e check (e > 0); a必须大于0 才能插入
b.复杂或特殊的约束条件是不能直接放到表内的如日期等
ps: alter table_a add contraints ch_d checck ( d >= sysdate );
错误提示：ora-02436:日期或者系统变量在check 约束条件中指定错误
c.with check option 使用
insert into table_a 
(select a,b,c,d from table_a where d <= sysdate with check option ) values ('a','b','c',sysdate+1);
错误提示：ora-01402 视图with check option where 子句违规

4.5 多表插入语句 p58

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
四种情况：
a. 无条件 insert
b. 有条件 insert all
c. 转置 insert
d. 有条件 insert first 

a. 无条件的insert
    创建两个测试表
    create or replace table_b as select  a,b,c  from table_a where 1=2
    create or replace table_c as select  a,b,e  from table_a where 1=2
    insert all
    into table_b (a,b,c) values (a,b,c)
    into table_c (a,b,e) values (a,b,e)
    select a,b,c,e from table_a where a in ('a_1','a_2')
    没有条件，所以table_b和table_c的条数一样
b.有条件的insert all
    delete table_b;
    delect table_c;
    清空表table_b,table_c的数据
    insert all
    when c in ('c_1','c-2') then
    into table_b (a,b,c) values (a,b,c)
    when e > 20 then
    into table_c (a,b,e) values (a,b,e)
    select a,b,c,e from table_a where a in ('a_1','a_2')
d. 有条件的 insert frist 
    insert first
    when c in ('c_1','c-2') then
    into table_b (a,b,c) values (a,b,c)
    when e > 20 then
    into table_c (a,b,e) values (a,b,e)
    select a,b,c,e from table_a where a in ('a_1','a_2')
    当符合第一个条件之后插入table_b，即使满足第二个条件也不会插入table_c内，这是和	
    insert all 和 insert first 区别所在

e. 转置 insert (以其一个分类，不然说是insert all 的一个用法)
    将不同列的数据插入到同一张表的不同行内
    drop table t1;
    drop table t2;
    create table t2 (d varchar2(10), des varchar2(50));
    create table t1 as 
    select '熊1,精神不佳' as d1,
    '熊2,精神不佳' as d2,
    '熊3,精神不佳' as d3,
    '熊4,精神不佳' as d4,
    '熊5,精神不佳' as d5
    from dual;
    /*转置*/
    insert all
    into t2(d,des) values ('周1',d1)
    into t2(d,des) values ('周2',d2)
    into t2(d,des) values ('周3',d3)
    into t2(d,des) values ('周4',d4)
    into t2(d,des) values ('周5',d5)
    select d1,d2,d3,d4,d5 form t1;
    /*结果*/
    select * from t2
    -----------------------------------------------
    周1 熊1，精神不佳
    周2 熊2，精神不佳
    周3 熊3，精神不佳
    周4 熊4，精神不佳
    周5 熊5，精神不佳
    等价于sql语句
    insert t2 (d,des)
    select '周1',d1 from t1 union all
    select '周2',d2 from t1 union all
    select '周3',d3 from t1 union all
    select '周4',d4 from t1 union all
    select '周5',d5 from t1 ;

4.6 用其他表中的值更新 p63

1
2
3
4
5
6
7
8
9
10
alter table_a add aname varchar2(50) default 'noname';
    添加上限制，否则全部更新，不符合更新条件的数据将会被设值为NULL
    添加where exists() 获取需要更新的语句
alter table_a add contraints pk_a primary key (a) ; //添加主键
    方式三种：直接关联、view 、merge(只关联一个数据库)
merge into emp
    using( select dname,deptno from dept where depy.name in ('a','b') ) dept
    on (dept.deptno = emp.deptno )
    when matched then
    update set emp.dname = dept.dname;

4.7 合并记录 p67 *** merge into 重点方式和详细使用，可同时操作增删改进行

1
2
3
4
5
6
7
8
merge into table_a  a
    using (select a,b from table_b where a = 'good') b on (b,a = a.a)
    when matchtd then //满足条件
    update set a.a= a.a + b.b;
    delect where ()
    when not matched then //不满足条件的
    insert (a.a) values (b.b)
    where b.c < =800 ;

4.8 删除违反参照完整性的记录 p71

1
2
3
alter table table_a add contraints pk_a primary key (a) //主键
alter table table_b add contraints fk_b_a foreign key (a) references table_a(a) //外检
create index idx_a,b on table_a(a,b) //索引

4.9 删除名称重复的记录 p72

1
2
3
4
5
1.通过字段判断（建立索引加快效率）
    create index idx_a,b on table_a(a,b) //索引
2.通过rowid判断
3.通过分组序号判断
    row_number() over (partition by name order by id) as sep 通过名称，ID排序获取行号

第五章、使用字符串

5.1 遍历字符串

1
2
3
4
5
6
7
8
9
10
11
select level form dual connect by level <= 4 
connect by 是一种树形查询的一个子句返回的值是伪列，表示树形中的级别层次
例子：
create or replace view v as 
select "天天向上" as 汉字,'TTXS' as 拼音 from dual;
select v.拼音,
v.汉字,
level,
substr(v.汉字,level,1) as 拆分汉字,
substr(v.拼音,level,1) as 拆分拼音
from v connect by level <= length(v.汉字);

5.2 字符串文字包含引号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
oracle 10g 新增了 q_quote 特性 定界符 q'[132]' 
界定符可以是‘’,[],()<> ，而且必须成对存在
开头是Q或者q，后面加定位符,中间放内容
 5.3 计算字符在字符串中出现的次数
1.计算子字符串个数，分隔符字符串是一个
    a. oracle 11g 提供函数 regexp_count 计算出现次数
        select regexp_count(str,',') + 1  from dual;
        select regexp_count('qwe,qeqew,qeqe',',') + 1  from dual; //获取字符串个数
    b. 没有regexp_count 函数则用regexp_replace 迂回求值即可
        select length(regexp_replace(str,'[^,]')) + 1  from dual; 
        select length(regexp_replace('字,符,串','[^,]')) + 1  from dual;  //返回三
    c. 还可以使用translate()
        select length(translate(str,','||str,',') +1 from dual;
        select length(translate('字,符,串',','||'字,符,串',',')) +1 from dual;
2.分隔符号是多个时候,如分隔字符为‘S#’
    //需要考虑分隔符长度问题
    select length(translate(str,'S#' || str ,'S#')) / length('S#') +1 as cnt from dual;
    select regexp_count(str,'S#') + 1  from dual; //regexp_count 不需要考虑长度问题

5.4 从字符串中删除不需要的字符串

1
2
3
4
5
例子：将名字中的元音字符 aeiou 去掉
1. select replace(translate(str,'aeiou','aaaaa'),'a','') from dual; 将元音转换为a之后在替换为‘’
2. select translate(str,'1aeiou','1')  from dual;
3.正则表达式替换 regexp_replace,相当于执行多个replace方式
    select regexp_replace(str,'[aeiou]') from dual;

5.5 将字符和数字数据分隔 regexp_replace

1
2
3
4
5
6
使用正则表达式 ^否意思 如果不放在方括号内且开头，如 ^hell 默认字符串开始位置
    select regexp_replace(str,'[0-9]','') , regexp_replace(str,'[^0-9]','') from dual ;
ps：select regexp_replace('1asd123','[0-9]','') ,regexp_replace('1asd123','[^0-9]','') from dual ;   
结果 asd 1123
下面translate 达到相同的效果
ps: select translate('1asd123','a0123456789','a') as 非数字,  translate('1asd123','0123456789'||'1asd123' ,'0123456789') as 数字 from dual;

5.6 查询只包含字母或数字型的数据

1
2
3
4
5
6
7
8
9
10
11
正则表达式一些小知识
regexp_count 统计数量 regexp_replace 替换 regexp_like 模糊查询
[^] ^ 代表否定 ^str  代表字符串的开始位置
    regexp_like(str,'a') like ''%a% 
    regexp_like(str,'^a') like 'a%'  没有前模糊查询
    regexp_like(str,'a$') like '%a'  没有后模糊查询
    regexp_like(str,’^a$') like 'a'  精确查询	
+ 和 * ‘+’ 表示匹配前面的表达式一次或多次：‘*’ 表示匹配前面的表达式零或多次
regexp_like(str,'16+') like '16%'
regexp_like(str,'16*') like '1%'
注意：NULL 是不能用like来比较的

5.7 提取姓名的大写首字母缩写

1
2
3
4
5
6
7
8
9
正则表达式实现
    select regexp_replace(str,'([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') from dual;
    select regexp_replace('Hello World','([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') from dual;
    select regexp_replace('Hello World Gong','([[:upper:]])(.*)([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3.\5') from dual;
()()()()将字符串分隔四部分，\1.\3 分别取13部分，中间加‘.’符号
translate 方式替换更加简洁
    select translate(str,' '|| lower(str),'.') from dual;
    select translate('Hello World Gong',' '|| lower('Hello World Gong'),'.') from dual;
先将字符串转小写，然后空格替换为’.‘ 结果保留大写字母

5.8 按字符串中的数值排序

1
2
3
4
5
6
可以通过正则表达式和translate 方式新增一列清空非数字列，排序
    select str,to_number(regexp_replace(str,'[^0-9]','') ) as str2 from dual order by str2;
    select str,to_number(translate(str,'0123456789'|| str,'0123456789') ) as str2 from dual order by str2;
ps:例子
    select 'asdfasdf12',to_number(regexp_replace('asdfasdf12','[^0-9]','') ) as str2 from dual order by str2;
    select  'asdfasdf12',to_number(translate( 'asdfasdf12','0123456789'||  'asdfasdf12','0123456789') ) as str2 from dual order by str2;

5.9 根据表中的行创建一个分隔列表

1
2
3
4
wmsys.wm_concat 函数 具有不确定性 返回varcahr,但是在oracl 11.2 返回的是clob类型
oracl 11.2 开始有分析函数 listagg 
    select  deptno,sun(sal) as total_sal,
    listagg(ename,',') with group (order by ename ) as tomal_name

from emp order by deptno;

5.10 提取第N个分隔字符串

1
2
3
正则表达式 regexp_substr 
    select regexp_substr(str,'[^,]+',1,2) as 子字符串 from dual;
    select regexp_substr('aa,b,ac,ad,','[^,]+',1,4) as 子字符串 from dual;  //最后一个参数是返回第四个字符串

5.11 分隔ip地址

1
2
3
4
5
6
7
8
9
10
select regexp_substr(str,'[^.]+',1,1) as ip1, 
    regexp_substr(str,'[^.]+',1,2) as ip2, 
    regexp_substr(str,'[^.]+',1,3) as ip3, 
    regexp_substr(str,'[^.]+',1,4) as ip4
    from dual;
select regexp_substr('192.168.1.1','[^.]+',1,1) as ip1, 
    regexp_substr('192.168.1.1','[^.]+',1,2) as ip2, 
    regexp_substr('192.168.1.1','[^.]+',1,3) as ip3, 
    regexp_substr('192.168.1.1','[^.]+',1,4) as ip4
    from dual;

5.12 将分隔数据转换为多值in列表

1
2
3
regexp_substr 和 translate 结合 转换为 in 条件入参
    select * from emp where ename in ( select regexp_substr(str, '[^,]+', 1, level) as ename  from   dual connect by level <= (length(translate(str,',' || str ,',')) + 1));
    select * from emp where ename in ( select regexp_substr(str, '[^,]+', 1, level) as ename from   dual connect by level <= (regexp_count(str,',') + 1));

5.13 按照字母顺序排序字符串

1
2
3
4
5
6
7
8
9
通过拆分字符串+ listagg 合并显示结果
1.拆分
    select str,substr(str,level,1) from dual connect by level <= lenght(str) ;
    ps: select 'fdavs',substr( 'fdavs',level,1) from dual connect by level <= length( 'fdavs') ;
2.listagg 合并
    select  str,listagg(substr(str,level,1)) within group(order by substr(str,level,1) ) as str2 from dual connect by level <= length(str) order by str2;
    ps : select  'fdavs',listagg(substr('fdavs',level,1)) within group(order by substr('fdavs',level,1) ) as str2 from dual connect by level <= length('fdavs') order by str2;
3去重复
    select  listagg(MIN(substr('fdaaavs',level,1))) within group(order by MIN(substr('fdaaavs',level,1))) as str2 from dual connect by level <= length('fdaaavs')  group by  (substr('fdaaavs',level,1))

5.14 判别可作为数值的字符串

1
2
3
rpad 填充
    select substr('AAA-BBB',1,instr('AAA-BBB','-',-1)-1) 值 from dual; 结果 AAA
    select substr('AAA-BBB',instr('AAA-BBB','-',-1)+1) 值 from dual; 结果 BBB