一些Oracle相关知识思考总结
一、函数
1、trunc截取函数
trunc:类似截取函数,按指定的格式截取输入的数据,一般用在日期和数值的截取上:
- 日期 :trunc( date [, fmt] ) --date:日期值;fmt:日期格式;
/*这里只列出比较常用的*/
--默认是返回当天日期
select trunc(sysdate) from dual;
--返回当年的第一天
select trunc(sysdate,'yy') from dual; --2022/1/1
--返回当月第一天
select trunc(sysdate,'mm') from dual; --2022/9/1
--返回当天日期
select trunc(sysdate,'dd') from dual; --2022/9/9
--返回当前星期的第一天,即星期天
select trunc(sysdate,'d') from dual; --2022/9/4
- 数值 :trunc( number [ , decimals ]);
注意:数值的截取不采用四舍五入的方式
--截取小数点后两位
select trunc(123.567,2) from dual; --123.56
--从右往左数,小数点左边第二位开始,补0
select trunc(123.567,-2) from dual; --100
2、日期函数
- 日期直接 + 或 - N
注意:date’2022-09-02’ 的含义:‘2022-09-02 00:00:00’
(这里补充一下:注意 a <= date’2022-08-31’,这样写是取不到08-31这一天的,可以理解吧)
/*这里只列出比较常用的*/
--日期加一天
select (date'2022-09-02' + 1) from dual; --2022/9/3
--日期减一天
select (date'2022-09-02' - 1) from dual; --2022/9/1
--加一个小时。若加N个小时,就是N/24
select (date'2022-09-02' + 1/24) from dual; --2022/9/2 1:00:00
--减一个小时
select (date'2022-09-02' - 1/24) from dual; --2022/9/1 23:00:00
--加一分钟。若加N分钟,就是N/(24 * 60)
select (date'2022-09-02' + 1/(24*60)) from dual; --2022/9/2 0:01:00
--减一分钟
select (date'2022-09-02' - 1/(24*60)) from dual; --2022/9/1 23:59:00
--加一秒。若加N秒,就是N/(24 * 60 *60)
select (date'2022-09-02' + 1/(24 * 60 *60)) from dual; --2022/9/2 0:01:00
--减一秒
select (date'2022-09-02' - 1/(24 * 60 *60)) from dual; --2022/9/1 23:59:00
- 两个日期的差值
- 两个日期相差的天数:date1 - date2
--相差的天数
select (date'2022-09-02' - date'2022-09-01') from dual; --1
--相差的小时数
select (date'2022-09-02' - date'2022-09-01') * 24 from dual; --24
--相差的分钟数
select (date'2022-09-02' - date'2022-09-01') * 24 * 60 from dual; --1140
--相差的秒数
select (date'2022-09-02' - date'2022-09-01') * 24 * 60 * 60 from dual; --86400
- 两个日期相差的月份数:MONTHS_BETWEEN( date1, date2) : date1 - date2
不建议使用:这个有bug,得出的结果是按照每个月31天计算的
--相差的月份数
--不同年的同一个月份得出正确值
select months_between(date'2022-09-09',date'2020-09-09') from dual; --24
--看看2022-02的,这个月份只有28天,但是天数不等于结果直接乘28天
select months_between(date'2022-02-09',date'2022-02-01') from dual; --0.258064516129032
select months_between(date'2022-02-09',date'2022-02-01') * 28 from dual; --7.2258064516129
select months_between(date'2022-02-09',date'2022-02-01') * 31 from dual; --8
3、字符串相关函数
loacate( substr, str) /position(substr in str) :返回子串substr在str中第一次出现的位置,若无返回0
left( str, length) :从左边开始截取str,截取length长度
right( str, length) :从右边开始截取str,截取length长度
substring_index( str, substr, n):返回substr在str中第n次出现之前的字符串
substr( str, n, m):返回字符串str从第n个字符开始,共截取m个
substr( str, pos) :返回从pos位置开始到最后的所有str字符串
replace( str, n, m):将str字符串中‘n’字符用‘m’字符替换
length( str ) :统计字符串长度
4、条件函数
name | id |
---|---|
王五 | 001 |
李四 | 002 |
我有这么个需求,当name为李四的,我要将他的状态展示为’社牛‘。
select name, id ,decode(name,'李四','是','否') as '是否社牛' from table2
结果如下:
name | id | 是否社牛 |
---|---|---|
王五 | 001 | 否 |
李四 | 002 | 是 |
decode(expression, search, result [, default]) :if ( expression = search) then result [else defualt]
eg:decode(name, ‘李四’, ’是‘, ’否‘)
if( name= ‘李四’, ’是‘, ’否‘)
case when name = ‘李四’ then ’是‘ else ’否‘ end case
以上三个达成的效果是一样的,都是当name为’李四‘的时候赋值’是‘,否则赋值’否‘,其中decode是oracle独有的
5、合并函数 wmsys.wm_concat()
当字段a相同,将其字段b的值合并为一行,并用,隔开,结合group by使用
eg:select a,to_char(wmsys.wm_concat(b))
from table1
group by table1.a
此函数要用group by 对其中相同的字段进行分组,比如下面例子中的name。
需求:将表table3中name相同的,购买的所有产品合并在一个单元格,并用‘,’隔开,例如:“张三 花,巧克力” 的形式
name | product |
---|---|
张三 | 花 |
王五 | 水 |
王五 | 面包 |
张三 | 巧克力 |
select name, tochar(wmsys.wm_concat(trim(product))) newpro
from table3 group by table3.name;
name | newpro |
---|---|
张三 | 花,巧克力 |
王五 | 水,面包 |
6、取整函数
- ceil(x)函数
天花板函数,顾名思义返回大于或等于x的最小整数
select ceil(3.46) from dual; -- 4
select ceil(-3.46) from dual; -- -3
- floor函数
地板函数,顾名思义,返回小于或等于x的最大整数
select floor(3.46) from dual; -- 3
select floor(-3.46) from dual; -- -4
- roud()函数
四舍五入取大约值
select round(3.46) from dual; -- 3
select round(-3.56) from dual; -- -4
7、NVL()函数
NVL(string1, replace_with) :若string1为空,返回replace_with;若string1不为空,返回string1本身的值
8、最值函数 Greatest、Least
greatest():返回N个参数中最大值
least():返回N个参数中的最小值
规则:
1.若参数中含有NULL,则直接返回NULL,不作比较
2. 若参数由数字和字符串组成,则函数将他们作为数字比较
select greatest(10,20,30) from dual; --30
select least(10,20,30) from dual; --10
--参数中含NULL
select greatest(10,null,30) from dual; --null
select least(10,null,30) from dual; --null
那就有人问了,如果我硬要比较,null的情况我就当是0,要怎么做?那么你可以事先判断一下
nvl()函数 、 if 、case when等判断函数都可以实现
比如,我现在有Q1 Q2 Q3我要获取最大值,但是我并不知道哪个是空的
select greatest(nvl(Q1,0), nvl(Q2,0), nvl(Q3,0)) from dual;
9、开窗函数
聚合函数+over()分析函数:
over()括号里面就是定义窗口的内容,partition by 根据某个字段分组
注意:在使用over等开窗函数时,over里头的分组及排序的执行晚于“where,group by,order by”的执行
eg:sum(score) over(partition by name):先根据name分组,后将每个组(每个窗口)的里的字段score进行求和
看到这里是不是觉得,这不就是聚合函数+group by函数吗?有啥优势呢?
举个栗子~
需求:我目前有一个工资表,我要获取员工所在部门、当月工资和部门总工资。
name | dept | salary |
---|---|---|
张三 | A | 2000 |
王五 | B | 3000 |
李四 | A | 3000 |
- 直接使用group by 函数
create table ryy_test_a(
name varchar2(10),
dept varchar2(10),
salary number
);
select dept,sum(salary) total_salary
from ryy_test_a
group by dept
dept | total_salary |
---|---|
A | 5000 |
B | 3000 |
而要实现需求中显示员工所在的部门和当月工资,还需要进行多个嵌套查询
select name,dept,salary,(select total_salary
from (select dept,sum(salary) total_salary
from ryy_test_a
group by dept) tmp1
where tmp1.dept = tmp2.dept) total_salary
from ryy_test_a tmp2
- 使用开窗函数
select name,dept,salary,sum(salary)over(partition by dept) total_salary
from ryy_test_a
上述的两个结果都是下表,但是从简洁程度上看,开窗函数还是比较可观的。
name | dept | salary | total_salary |
---|---|---|---|
张三 | A | 2000 | 5000 |
王五 | B | 3000 | 3000 |
李四 | A | 3000 | 5000 |
分组排序函数 row_number()over(partition by … order by…)
先根据dept分组,后每个组内部进行降序排序
select name,dept,salary,row_number()over(partition by dept order by salary desc) rank
from ryy_test_a
name | dept | salary | rank |
---|---|---|---|
李四 | A | 3000 | 1 |
张三 | A | 2000 | 2 |
王五 | B | 3000 | 1 |
那现在给你个需求:把每个部门中最高工资的员工信息取出来,是不是一下子就能得到答案?
--desc是降序排序,而asc是升序排序,可以根据需求写升降序排序,从而获取所需数据
select * from(
select name,dept,salary,row_number()over(partition by dept order by salary desc) rank
from ryy_test_a
)
where rank < 2
name | dept | salary | rank |
---|---|---|---|
李四 | A | 3000 | 1 |
王五 | B | 3000 | 1 |
10、字符串连接函数
- ||连接运算符:string1 || string2 -->string1string2
- concat() :concat(‘A’,‘B’) -->‘AB’
二、定义或关键字相关
1、truncate、delete、drop比较
- truncate、drop为DDL语句;delete为DML
- truncate 只用于表;delete、drop可用于表、视图
- truncate 会清空表的所有行,但表结构及其约束、索引等不变;drop会删表结构、约束、索引;delete删除表数据,但是会保留表结构
- truncate不会激活触发器,delete会激活
2、DDL和DML区别
DDL:对数据库中对象(比如database、table)–create drop alter
DML:对数据库中数据 --select delete insert update
(1)DDL:建库、表,设置约束 create alter drop
- 创建数据库 create database…
--创建一个名为 A 的数据库
create database if not exists A character set utf-8;
- 创建表格 create table …
--在数据库A中,创建一个名为 table1 的表
use A create table is not exists table1 (
id int,
name varchar2(30)
);
- 更改表结构 alter table table1 drop/alter/modify column column_name …
--查看表结构
desc table1;
--删除列名为name的列
alter table table1 drop column name;
--增加列名为name的列
alter table table1 add column name varchar2(30);
--将列名为name的修改为列名user_name
alter table table1 rename column name to user_name;
--将列名为name的字段大小改为50
alter table table1 modify name varchar2(50);
--更改列名的默认值
alter table table1 modify name dedault A;
- 删除表、数据库 drop table/database
--删除名为table1的表
drop table table1;
--删除名为A的数据库
drop database A;
(2)DML 增删改查 select delete insert update
- 查询
--一般查询都直接select 列名,因为select * 的效率很低
select * from table1;
select id, name from table1;
--去重查询
select distinct id,name from ryy_test_a;
- 删除
delete from table1 ;
delete from table1 where name = '李四';
- 插入
create table ryy_test_a(
id number,
name varchar2(20)
)
create table ryy_test_b(
id number,
name varchar2(20),
age number
)
select * from ryy_test_a;
select * from ryy_test_b;
--插入一行
insert into ryy_test_a(id,name) values(1,'张三');
insert into ryy_test_a values(2,'李四');
insert into ryy_test_a(name) values('王五');
--插入多行
insert all
into ryy_test_a values(4,'小明')
into ryy_test_a values(5,'大黄')
select 1 from dual;
insert all
into ryy_test_b values(6,'修勾',2)
into ryy_test_b values(7,'修喵',3)
select 1 from dual;
--从一个表中select出来的值insert到
insert into ryy_test_a(
id,
name
)select
id,
name
from ryy_test_b;
- 更新
--普通定值更新
update ryy_test_b
set age = 10,
name = '大白'
where name = '修勾'
--用一个表的结果更新另一个表
update ryy_test_b
set (name,age) = (
select name, id
from ryy_test_a
where name = '大黄'
)
where name = '修喵'
--多个条件更新值
update A
SET A.aa = (
case when A.bb = a then '1'
when A.bb = b then '2'
else '0'
end
)
3、join
现在有student表,如下:
id | name | teacher_id |
---|---|---|
001 | 张三 | t001 |
003 | 王五 | t002 |
teacher表如下:
teacher_id | teacher_name | course_id |
---|---|---|
t001 | A老师 | c01 |
t001 | A老师 | c02 |
t001 | A老师 | c03 |
t003 | C老师 | c05 |
select * from
- student a left join teacher b on a.teacher_id = b.teacher_id
id | name | teacher_id | teacher_id | teacher_name | course_id |
---|---|---|---|---|---|
001 | 张三 | t001 | t001 | A老师 | c01 |
001 | 张三 | t001 | t001 | A老师 | c02 |
001 | 张三 | t001 | t001 | A老师 | c03 |
003 | 王五 | t002 |
这里显然看得到,left join会导致左表增加一定的数据量。怎么就增加了数据量呢?你可以尝试
select a.* from student a left join teacher b on a.teacher_id = b.teacher_id ,此时你会发现a.*的数据变成了四条,看完这里是不是觉得:啊?所以呢?有啥影响啊?
那假设我的student表多了个money呢?student表变成下表
id | name | teacher_id | money |
---|---|---|---|
001 | 张三 | t001 | 100 |
003 | 王五 | t002 | 150 |
left join 的结果表就变成了
id | name | teacher_id | money | teacher_id | teacher_name | course_id |
---|---|---|---|---|---|---|
001 | 张三 | t001 | 100 | t001 | A老师 | c01 |
001 | 张三 | t001 | 100 | t001 | A老师 | c02 |
001 | 张三 | t001 | 100 | t001 | A老师 | c03 |
这个时候你要求每个学生有多少钱,本来张三的money实际只有100,但是此时你sum(money),一下子变成了300了,是不是就数据翻几倍了。那怎么解决这个问题呢?你可以先预处理student 表,简单来说就是先在student表sum(money),后面再进行左关联,关联后的表就不再需要进行sum了,也就不会出现money数据翻倍的情况。
- student a right join teacher b on a.teacher_id = b.teacher_id
id | name | teacher_id | teacher_id | teacher_name | course_id |
---|---|---|---|---|---|
001 | 张三 | t001 | t001 | A老师 | c01 |
001 | 张三 | t001 | t001 | A老师 | c02 |
001 | 张三 | t001 | t001 | A老师 | c03 |
t003 | C老师 | c05 |
- student a inner join teacher b on a.teacher_id = b.teacher_id
id | name | teacher_id | teacher_id | teacher_name | course_id |
---|---|---|---|---|---|
001 | 张三 | t001 | t001 | A老师 | c01 |
001 | 张三 | t001 | t001 | A老师 | c02 |
001 | 张三 | t001 | t001 | A老师 | c03 |
- student a full join teacher b on a.teacher_id = b.teacher_id
id | name | teacher_id | teacher_id | teacher_name | course_id |
---|---|---|---|---|---|
001 | 张三 | t001 | t001 | A老师 | c01 |
001 | 张三 | t001 | t001 | A老师 | c02 |
001 | 张三 | t001 | t001 | A老师 | c03 |
003 | 王五 | t002 | |||
t003 | C老师 | c05 |
我以前有个错误的认知,以为left join 的结果一定是左表的行数啊,其实不是的,根据前面的left join 表结果来看就可以明白了。而right join 也同理。因此join的结果不一定等于主表。
4、union & union all
- union : 相当于or,自带去重
select * from t1 union select * from t2
查询的结果:t1和t2的全部集合,但是去除重复部分
- union all:全部集合,相当于并集
select * from t1 union all select * from t2
查询的结果:t1和t2的全部集合,不去重