Oracle语句深入了解Day02

一、单行函数

1. 字符函数
1.1 求字符串长度 length()
select length('123') from dual;
1.2 求字符串的子串 substr(字符串,起始位置,数据数量)
select substr('abcd中efg',1,2) from dual;
1.3 求子串在字符串中的位置  instr(字符串,字串) => 位置
SELECT INSTR('abcd中efg', '中') FROM DUAL;
1.4 substr 和 instr 结合使用 获取指定字符串中 指定字符 后的数据
已知字符串 6666@qq.com 获取邮箱类型 ==>qq.com
select substr('6666@qq.com',INSTR('6666@qq.com', '@') + 1,10) from dual;
1.5 字符串拼接 concat(字符串1,字符串2)
select concat('1','2') from dual;
1.6 多个字符串拼接 ||
select 'a'||'b'||'c' from dual;

二、数值函数

2. 数值函数
2.1 四舍五入函数 round
select round(1.64) from dual;
2.2 指定四舍五入小数位数
select round(1.65,1) from dual;
2.3 截取函数 trunc(数值,小数位数)
select trunc(1.66,1) from dual;
2.4 取模 mod(数值,被除数)
with t1 as (
select mod(10,2) ret from dual)
select * from t1 where ret=0;
2.5 创建表my_test02 创建语句如下
create table my_test02(
    num number
);

insert into my_test02 values(1);
insert into my_test02 values(2);
insert into my_test02 values(3);
insert into my_test02 values(4);
insert into my_test02 values(5);
insert into my_test02 values(6);
insert into my_test02 values(7);
-- 获取表my_test02中所有的偶数
with t1 as (
select num,mod(num,2) ret from my_test02)
select num from t1 where ret=0;

 三、时间函数

3. 时间函数
3.1 获取系统时间 sysdate
select sysdate from DUAL;
3.2 加月函数 add_months(时间类型,3)
select add_months(sysdate,1) from DUAL;
3.3 加减时间通用方法 select sysdate + interval '1' minute from dual;
select sysdate - interval '1' month from dual;

3.4 时间相关函数汇总
Orcale中时间类型date是 必须为 年月日时分秒的样式
和mysql中不一样 没有单独的year时间类型

3.4.1 to_date是把字符串转化为时间类型:
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL;

SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL;

SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL;

SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL;

SELECT TO_DATE('2006', 'YYYY') FROM DUAL;

3.4.2 trunc函数截取后的结果依然为时间类型
select sysdate S1,

trunc(sysdate) S2,

trunc(sysdate,'year') YEAR,

trunc(sysdate,'month') MONTH ,

trunc(sysdate,'day') DAY

from dual;
获取当前日期 和 日期2024-04-10 的差值

3.4.3 to_char函数的结果为字符串类型
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;

select to_char(sysdate,'yyyy') as nowYear from dual;

select to_char(sysdate,'mm') as nowMonth from dual;

select to_char(sysdate,'dd') as nowDay from dual;

select to_char(sysdate,'hh24') as nowHour from dual;

select to_char(sysdate,'mi') as nowMinute from dual;

select to_char(sysdate,'ss') as nowSecond from dual;

3.5 字符串转数字 TO_NUMBER
select to_number('123') from dual;

四、其他函数

4. 其他函数
空值处理函数 NVL
NVL (检测的值 ，如果为 null 的值)
select nvl(null,1) from dual;
NVL2 (检测的值，如果不为 null 的值，如果为 null 的值)
select nvl(null,10,20) from dual;

五、数据行转列

1. 按月份统计 2012 年各个地区的水费 T_AREA t_account
区域 一月 二月 三月
海淀 xxx xxx xxx
西城 xxx xxx xxx
select
       (select name from T_AREA where T_AREA.ID=T_ACCOUNT.AREAID) 区域,
       sum(case when MONTH='01' then MONEY end ) as 一月,
       sum(case when MONTH='02' then MONEY end) as 二月,
       sum(case when MONTH='03' then MONEY end) as 三月
from T_ACCOUNT
where YEAR = '2012'
group by AREAID;
-- 解析
with t1 as (
select AREAID from T_ACCOUNT where YEAR = '2012'
group by AREAID)
select NAME from T_AREA,t1 where T_AREA.ID=t1.AREAID;

 六、集合运算

1、并集

-- 并集运算
1. UNION ALL 不去掉重复记录 t_owners
select * from T_OWNERS
union all
select * from T_OWNERS;
2. UNION  去掉重复记录 t_owners
select * from T_OWNERS
union
select * from T_OWNERS;

2、交集 

交集运算
3. intersect 返回属于两个集合的部分
select id,name from T_OWNERS where id<=7
intersect
select id,name from T_OWNERS where ID>=4;
inner join方式
select T_OWNERS.id,T_OWNERS.name from T_OWNERS
inner join
(select id,name from T_OWNERS where ID>=4) t2 on T_OWNERS.id=t2.ID;
t1,t2 where方式
select * from
(select id,name from T_OWNERS where id<=7) t1,(select id,name from T_OWNERS where ID>=4) t2 where t1.id=t2.ID;

 3、差集

-- 差集运算
4. minus 返回属于第一个集合 但不属于第二个集合的部分
select id,name from T_OWNERS where id<=7
minus
select id,name from T_OWNERS where ID>=4;
5. minus 运算符来实现分页
select id,name from T_OWNERS where id<=4
minus
select id,name from T_OWNERS where ID<=2;

 七、窗口函数

over(
      partition by 分组字段
      order by 排序字段
      rows between current row and 1 following
      range between 1 preceding and 1 following
)
current row 当前行
1 following 后1行
1 preceding 前一行

t_owners表
按照区域统计id的和 显示id,name,addressid
如果是sum和count 那么 在order by的时候 那是一个累计统计
如果加order by后 max min 也是组内累计逐行统计
select id,name,addressid,
       sum(id) over (partition by ADDRESSID order by id
           range between current row and 1 following ) v1
from T_OWNERS;

今天的语法会比较难，知识量也比较大，需要学习的同学，要多多复习，巩固已经所学知识

加油。🩷🩷🩷