sql基础知识（笔记）（二）

**

sql基础知识（笔记）（二）

**

接上篇
sql基础知识（笔记）（一）

**

函数:

**

distinct:去除重复数据 distinct empno
trim:去掉字符串两头空格 trim(job)
ltrim:去掉字符串左边空格
rtrim:去掉字符串右边空格
ceil:向上取整 ceil(97.2)--98 ceil(-35.3)-- -35
floor:向下取整 floor(97.2)--97  floor(-35.3)-- -36
ABS:取绝对值 ABS(5)--5  ABS(-6)--6
last_day:获取给定时间月份的最后一天 last_day(sysdate)--20200229  last_day(to_date('20201001','YYYYMMDD'))--20201031
upper:小写转大写 upper('abc')--'ABC'
lower:大小转小写
length:返回参数字符长度 length('ABC')--3 length(列名)--返回列里每个字符串的长度
||:拼接符 'AB'||'CD'--'ABCD'
to_char:时间to_char to_char(sysdate,'YYYYMMDD')--'20200206'.也可以处理数据,自己搜索学习
to_char(数字,格式):便于阅读
to_date:字符串转化为时间格式 to_date('2020-02-05','YYYY-MM-DD')--时间格式 2020-02-05
months_between:2个参数,都是时间,返回的结果是参数1-参数2的月份数 months_between(时间1,时间2)--时间1-时间2对应的月份数
add_months:2个参数,增加月份 add_months(时间,数字) 给时间增加n个月.数字可正可负
replace:3个参数 repalce(参数1,参数2,参数3):将参数1中的参数2替换为参数3.repalce(' A B C  D',' ','');将字符串中的空格去掉
substr:
2个参数:substr(参数1,参数2):截取参数1,从参数2位开始到最后 参数2位正时,是从前往后截取,直到最后. 参数2位负数时,是从后往前的参数2位到最后 substr('ABCDE',2)--BCDE  substr('ABCDE',-2)--DE
3个参数:substr(参数1,参数2,参数3):截取参数1,从参数2位开始,截取参数3位. substr('ABCDE',3,2):--CD 截取ABCDE,从第三位开始截取,截取2位;
round:
1个参数:
round(12.56)--13
round(12.34)--12
2个参数:
round(12.56,1):小数点后保留1位,且有四舍五入 12.6
round(12.567,2):12.57
round(12.56,-1):小数点钱第一位置为0,且有四舍五入 10
round(sysdate,'D'):最近的周日
round(sysdate,'MM'):最近的月第一天 
round(sysdate,'Q'):最近的季度第一天
round(sysdate,'Y'):最近的年第一天
trunc:
1个参数:
trunc(12.56)--12保留整数且没有四舍五入
trunc(12.34)--12
trunc(sysdate):不带时分秒的时间
2个参数:
trunc(12.56,1):12.5 小数点后保留1位,无四舍五入
trunc(12.567,2):12.56
trunc(12.56,-1):10
trunc(12.56,-2):0
trunc(sysdate,'D'):当周第一天
trunc(sysdate,'MM'):当月第一天
trunc(sysdate,'Q'):当季度第一天
trunc(sysdate,'Y'):当年第一天

instr
2个参数:instr(参数1,参数2):返回位置,参数2在参数1中第一次出现的位置 select instr('ABCDEC','C') from dual;--3
4个参数:instr(参数1,参数2,参数3,参数4):从参数1中的参数3位开始找,参数2在参数1中出现参数4次的位置;select instr('AAAAAAA','A',3,2) from dual;--4
nvl 2个参数 nvl(参数1,参数2):当参数1为空时,返回参数2;--select nvl('',1) from dual; --1
nvl2 3个参数: nvl2(参数1,参数2,参数3):当参数1的值为空时返回参数3,否则返回参数2
select nvl2('',1,2) from dual;--2
select nvl2('A',1,2) from dual;--1
decode
decode(参数1,参数2,参数3,参数4,参数5,参数6):当参数1的值等于参数2时,返回参数3,当参数1的值等于参数4时返回参数5,其余时候返回参数6
select decode(3,1,'A',2,'B',3,'C','D') from dual;--C

**

关联查询

**
基本的关联查询语法:
基本语法:

select * from 表1,表2,表3 where 表1.列名=表2.列名 and 表2.列名=表3.列名;

关联列选取:关系传递时,选取业务含义相同的列做关联.–注释
左连接:
–最多 显示两张表全部的列,左表在前,右表在后.显示左表全部数据,右表能关联上到部分,显示右表数据,否则右表部分显示为空;
基本语法:

select * from 表1 left join 表2 on 表1.列名=表2.列名;
select * from 表1,表2 where 表1.列名=表2.列名(+);

右连接:
–最多 显示两张表全部的列,左表在前,右表在后,显示右表全部的数据,左表能关联上的部分,显示左表数据,否则左表部分显示为空;
基本语法:

select * from 表1 right join 表2 on 表1.列名=表2.列名;
select * from 表1,表2 where 表1.列名(+)=表2.列名;

内连接:
在功能上等同于基本的关联查询
基本语法: select * from 表1 inner join 表2 on 表1.列名=表2.列名;
外连接:–分为左外连接和右外连接,功能上等同于左连接和右连接
基本语法:

select * from 表1 left outer join 表2 on 表1.列名=表2.列名;
select * from 表1 right outer join 表2 on 表1.列名=表2.列名;

全连接:
功能上是左连接和右连接的集合,展示左表全部数据和右表全部数据,关联不上的部分各自补空;
基本语法:

select * from 表1 full join 表2 on 表1.列名=表2.列名;

自然连接:
功能上类似基本关联查询,会自动找两张表中名字相同的列做关联;
基本语法:

select * from 表1 natural join 表2;

date_created和date_updated

子查询
子查询的概念:筛选查询时,我们的过滤条件是列等于某个值,这个值是直接给出的.如果这个值不是直接给出,而是给出计算逻辑和参数,那就需要用一个sql来获取,获取的的这个sql就是子查询
可以用在什么地方:
1.作为select的内容;
2.作为where条件的一部分;
3.作为having的一部分
4.作为结果集(子查询作为一张表);

**

集合

**
合集:
union和union all, union有去重和排序的作用(升序排列),SQL优化时,如果允许,可以把unoin换成union all
交集:
intersect 取两个结果集交叉的部分
差集:
minus, A minus B 显示A中去掉AB交集的部分
B minus A 显示B中去掉AB交集的部分

增删改:
insert:
1.全列插入数据:

 insert into 表名(列名1,列名2,列名3) values(值1,值2,值3);

其中还表名后的括号部分可以省略 值和列名是一一对应的
2.部分列插入数据:

insert into 表名(列1,列3) values(值1,值3);

3.表插表 insert into 表名 select * from 表名;
如果要插入部分列,则可以写为:

insert into 表名(列1,列2,列3) select 列1,列2,列3 from 表名;

delete:

delete from 表名 where 条件;

–删除符合where条件的数据,where条件部分可以省略,即全表删除;
truncate:删除全表数据 语法:

truncate table 表名;

delete与truncate的区别:
1.delete是DML语句,需要提交;truncate是DDL语句,不需要提交;
2.delete可以删除全表数据,也可以删除部分数据;truncate只针对全表数据;
3.针对大量数据时,delete效率低(写大量日志,用于数据恢复),truncate效率很高(日志量很少,很难恢复数据);

update:
基本语法:

update 表名 set 列=值 where 条件;

–将表中符合where条件数据的这列的值改为给出的值; 如果不写where条件,即整列更新;

联表更新:

update 表名 set 列=值 where 条件
exists(子查询);

exists:如果查询有值,则返回true,无则返回false.

update 表名 set 列=值 where 条件
and 列 in(子查询);

merge into 表名 a
using 表名或者查询语句 b
on(关联条件)
when matched then
update set
a.列名1=b.列名1,
a.列名2=b.列名2
when not matched then
insert values(a.列1,a.列2);

1.using之后可以是一个表名,也可以是一个筛选查询语句;
2.when macthed 和 when not matched可以只有一个,不须要全部都有;
3.insert时可以值插入部分列;–查找笔记
4.更新时可以加条件更新(只更新部分数据);–查找笔记
5.涉及到分区表时,merge中分区键不能更新;

–DML部分结束
ddl 下篇再讲