[亲测]Oracle查询--单行函数--PL/SQL,分析函数,集合运算(三)

ゝ一世哀愁。 2022-04-10 14:42 314阅读 0赞

# ORACLE查询（单行函数–PL/SQL,分析函数,集合运算） #

### 一、单行函数–PL/SQL ###

#### （一）字符函数 ####

##### 1.字符串拼接 concat 或 || #####

--方式一
    select concat('Hello','World') from dual;
    --方式二
    select 'Hello'||'world' from dual;

结果：  
![在这里插入图片描述][20190111151905298.png]

##### 2.将首字母大写，其他全部小写 initcap #####

select initcap('hElLOwoRLD') from dual;

结果：  
![在这里插入图片描述][20190111151918384.png]

##### 3.找出字符的位置 instr(从1开始计数) #####

select instr('Helloworld','o') from dual;

结果：  
![在这里插入图片描述][20190111151929322.png]

##### 4.计算字符长度 length #####

select length('HelloWorld') from dual;

结果：  
![在这里插入图片描述][20190111152008631.png]

##### 5.替换 replace #####

select replace('HelloWorld','rld','od') from dual;

结果：  
![在这里插入图片描述][20190111152020915.png]

##### 6.截取字符串 substr(第一个参数:要截取的字符串,第二个参数:从几开始,第三个参数:截取几个) #####

select substr('HelloWorld',3,3) from dual;

结果：  
![在这里插入图片描述][20190111152035192.png]

##### 7.去除首尾空格 trim #####

select trim(' a ') from dual;

结果：  
![在这里插入图片描述][20190111152148515.png]

##### 8.全转小写 lower #####

select lower('HeLloWorLD') from dual;

结果：  
![在这里插入图片描述][20190111152202293.png]

#### 9.全转大写 upper ####

select upper('HeLloWorLD') from dual;

结果：  
![在这里插入图片描述][20190111152212693.png]

#### （二）数值函数 ####

##### 1.求绝对值 abs #####

select abs(-18) from dual;

结果：  
![在这里插入图片描述][20190111152224387.png]

##### 2.进一法 ( 有小数就进一 ) ceil #####

select ceil(10.1) from dual;
    select ceil(10.9) from dual;

结果：  
![在这里插入图片描述][2019011115244260.png]

##### 3.去尾法 ( 有小数就去掉 ) floor #####

select floor(10.1) from dual;
    select floor(10.9) from dual;

结果：  
![在这里插入图片描述][20190111152538159.png]

##### 4.取模（ 余数 ） mod #####

select mod(17 ,3) from dual;

结果：  
![在这里插入图片描述][20190111152556244.png]

##### 5.幂次方 power #####

select power(3,9) from dual;

结果：  
![在这里插入图片描述][20190111152607527.png]

##### 6.开平方 sqrt #####

select sqrt(25) from dual;

结果：  
![在这里插入图片描述][20190111152617999.png]

##### 7.四舍五入 第二个参数保留几位小数 round #####

**第一个参数：要截取数字；第二个参数：截取个数**

select round(9.4901) from dual;
    select round(9.5322,2) from dual;

结果：  
![在这里插入图片描述][20190111152727266.png]

##### 8.数值截断 trunc #####

**第一个参数：要截取数字；第二个参数：截取个数**

select trunc(9.3232,3) from dual;

结果：  
![在这里插入图片描述][20190111152851611.png]

#### （三）日期函数 ####

##### 1.获取当前系统时间 sysdate #####

select sysdate from dual;

结果：  
![在这里插入图片描述][20190111152920681.png]

##### 2.指定时间的天数 + - sysdate+\* / sysdate-\* #####

###### 当前系统时间加十天 ######

select sysdate+10 from dual;

结果:  
![在这里插入图片描述][20190111152933493.png]

###### 当前系统时间减十天 ######

select sysdate-10 from dual;

结果：  
![在这里插入图片描述][20190111152942958.png]

##### 3.构造时间 to\_date #####

select to_date('20190110102323','yyyymmdd hh24miss') from dual;

结果：  
![在这里插入图片描述][20190111153005143.png]

##### 4.指定时间加月 add\_months #####

select add_months(sysdate,3) from dual;

结果：  
![在这里插入图片描述][20190111153020180.png]

##### 5,指定时间月份的最后一天 last\_day #####

select last_day(sysdate) from dual;

结果：  
![在这里插入图片描述][20190111153032229.png]

##### 6.计算月差 months\_between #####

select months_between(sysdate,to_date('20180101','yyyymmdd')) from dual;

结果：  
![在这里插入图片描述][20190111153044972.png]

##### 7.指定时间的下个星期 \* next\_day #####

select next_day(sysdate ,'星期五') from dual;

结果：  
![在这里插入图片描述][20190111153058790.png]

##### 8.对日期四舍五入 round （day ， month ， year ） #####

select round(to_date('20190107','yyyymmdd'),'day')from dual;  -- 指定天，即是四舍五入 周 最终指向周日

结果:  
![在这里插入图片描述][20190111153156739.png]

select round(to_date('20190625','yyyymmdd'),'month')from dual; -- 指定月，即是四舍五入 月 最终指向月初

结果:  
![在这里插入图片描述][20190111153207132.png]

select round(to_date('20190625','yyyymmdd'),'year') from dual; --指定年，即是四舍五入 年 最终指向元旦

结果:  
![在这里插入图片描述][20190111153216866.png]

##### 9.获取指定年的元旦 / 月初 / 周日 trunc （ day ， month ， year ） #####

select trunc(sysdate,'year') from dual;
    select trunc(sysdate,'month') from dual;
    select trunc(sysdate,'day') from dual;

结果:  
![在这里插入图片描述][20190111153321150.png]

#### （四）转换函数 ####

##### 1.将 日期格式 / 数字 转换成字符串 to\_char #####

select to_char(sysdate,'yyyy-mm-dd') from dual;

结果:  
![在这里插入图片描述][20190111153338788.png]

##### 2.将 字符串 转换成时间 to\_date #####

select to_date('20190110102323','yyyymmdd hh24miss') from dual;

结果:  
![在这里插入图片描述][20190111153400179.png]

##### 3.将字符串转换成数字 to\_number #####

select to_number('18181') from dual;

结果:  
![在这里插入图片描述][20190111153413237.png]

#### ( 五）其它函数 ####

**原始表**  
![在这里插入图片描述][20190111153539971.png]

##### 1.空值处理函数 ( 列，如果为 null 的值） NVL #####

select price,nvl(maxnum,1)  usenum from t_pricetable;

结果:  
![在这里插入图片描述][20190111153607469.png]

##### 2.值处理函数 ( 列，如果不为 null 的值，如果为 null 的值 ) NVL2 #####

select price ,nvl2(maxnum , maxnum, 2) usenum  from t_pricetable;

结果:  
![在这里插入图片描述][20190111153619802.png]

##### 3.条件处理 decode #####

select id,name,decode(ownertypeid ,'1','家用电','2','事业用电','3','商业用电') from t_owners;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70]

### 二、分析函数 ###

##### 1.相同的值，排名跳跃 rank #####

select rank() over(order by usenum desc)  ,usenum from t_account;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 1]

##### 2.相同的值，排名连续 dense\_rank #####

select dense_rank() over(order by usenum desc) ,usenum from t_account;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 2]

##### 3.不同值，排名连续 row\_number #####

select row_number() over(order by usenum desc) ,usenum from t_account;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 3]

### 三、集合运算 ###

##### 1.并集运算 #####

###### 不去重 union all ######

select * from t_owners where id >= 3
    union all
    select * from t_owners where id <= 7;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 4]

###### 去重 union ######

select * from t_owners where id >= 3
    union 
    select * from t_owners where id <= 7;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 5]

##### 2.交集运算 intersect #####

select * from t_owners where id >= 3
    intersect
    select * from t_owners where id <= 7;

结果：  
![在这里插入图片描述][20190111154107743.png]

##### 3.差集运算 minus #####

select * from t_owners where id >= 3
    minus
    select * from t_owners where id <= 7;

结果：  
![在这里插入图片描述][20190111154120304.png]

**以下推荐个人文章**  
[\[亲测\]Oracle数据库安装与配置  
][Oracle_]

[\[亲测\]Oracle数据库操作  
][Oracle_ 1]

[\[亲测\]Oracle表的创建、修改与删除  
][Oracle_ 2]

[\[亲测\]Oracle数据库约束][Oracle]

[\[亲测\]Oracle数据增删改][Oracle 1]

[\[亲测\]Oracle查询–单表查询,连接查询(一)  
][Oracle_ 3]

[\[亲测\]Oracle查询–子查询,分页查询(二)  
][Oracle_ 4]

[\[亲测\]Oracle查询–单行函数–PL/SQL,分析函数,集合运算(三)  
][Oracle_PL_SQL_]

[Oracle执行计划Explain Plan 如何使用][Oracle_Explain Plan]

[\[亲测\]数据库优化][Link 1]

[20190111151905298.png]: /images/20220328/7df76048445e49e8b2d3b6933dc2a9d3.png
[20190111151918384.png]: /images/20220328/0b3c948874584158a23057d778b51034.png
[20190111151929322.png]: /images/20220328/c34fd365370e4fef8e4c18f9e1e36fa0.png
[20190111152008631.png]: /images/20220328/5ddf638fe3bd49d3ab8d5572891b0c41.png
[20190111152020915.png]: /images/20220328/699eed39bca6476380a5fbae1b6b442f.png
[20190111152035192.png]: /images/20220328/10ff2677bb7a41e9b5238975f2e0a5d3.png
[20190111152148515.png]: /images/20220328/ce810a9f29fd45edbcb3671dd36bcbec.png
[20190111152202293.png]: /images/20220328/7bc5dc9e6d0e4c9ebbf9c404bee49a29.png
[20190111152212693.png]: /images/20220328/51649c2729284dcdaa9c69157c5ad0d5.png
[20190111152224387.png]: /images/20220328/a99109143a42492f82fa9aa577f62fb4.png
[2019011115244260.png]: /images/20220328/d893afc6a21046e5938eebf3d7775eb2.png
[20190111152538159.png]: /images/20220328/39df0f79605c4c30888df290291a19e8.png
[20190111152556244.png]: /images/20220328/32ebf52cfd40441894f6534e31f08d5d.png
[20190111152607527.png]: /images/20220328/3fba2a88d14d4bcfb82ff6944126243a.png
[20190111152617999.png]: /images/20220328/bcb6767350f64915a0bee2550a6881ed.png
[20190111152727266.png]: /images/20220328/e47288265e134e7b9c33ecb5585dc795.png
[20190111152851611.png]: /images/20220328/0b2af4f31c614b4b8e9aea97a178e7f7.png
[20190111152920681.png]: /images/20220328/9543de5a9b934cbea760bc340f7a0754.png
[20190111152933493.png]: /images/20220328/937b361ea0c844f5a2f900cd9188125c.png
[20190111152942958.png]: /images/20220328/5b7ffef9604b4b3abf19202cf6d6cbb7.png
[20190111153005143.png]: /images/20220328/e3adddf4676e4442946bc073e4988777.png
[20190111153020180.png]: /images/20220328/59f786cd9691463cad2f081e3d8be9a3.png
[20190111153032229.png]: /images/20220328/1742d2f07cad48e792afdd2eeadf47fe.png
[20190111153044972.png]: /images/20220328/248941ed207146dd9eb511e5d270f49b.png
[20190111153058790.png]: /images/20220328/b4cc0a854759402786b623dcc5924222.png
[20190111153156739.png]: /images/20220328/4db2914837244159adab5322a08da43d.png
[20190111153207132.png]: /images/20220328/c143497260d74b30b11f64859ac10180.png
[20190111153216866.png]: /images/20220328/d933e5c711fe47dc9b455a9ec251752b.png
[20190111153321150.png]: /images/20220328/4015ed6608ec44b980f0cacc8f1d020e.png
[20190111153338788.png]: /images/20220328/4f815dc3d42d44e29f2b6eb3b6256354.png
[20190111153400179.png]: /images/20220328/9e820e97297d457aa6e02b0e99987242.png
[20190111153413237.png]: /images/20220328/9a4e2e199124417dbc2496d1bd3921dc.png
[20190111153539971.png]: /images/20220328/fa5ce3b52d3e4e91b3bb5c44b7968fa0.png
[20190111153607469.png]: /images/20220328/2c81e401c2bd4b2f81120db52f5de1e5.png
[20190111153619802.png]: /images/20220328/c07b8847f8e043a386233c4d611191d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70]: /images/20220328/6c6b1da29f394839afb3cb2b391f3e19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 1]: /images/20220328/348c7d19961e4d7f9515f0b5ef048c20.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 2]: /images/20220328/d3e2f181ba944a5c87a54f605305d1fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 3]: /images/20220328/86c3214d8f654f5a9693f2d46cffab01.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 4]: /images/20220328/a56fa2f8b91c41e1bbcfbe4744c6f14d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjUyNTA5_size_16_color_FFFFFF_t_70 5]: /images/20220328/e20408ba44f949d7babe8961ef99380a.png
[20190111154107743.png]: /images/20220328/3ced8247c366463bb4c27d5e671570a5.png
[20190111154120304.png]: /images/20220328/3f4a0f90599246a48e1126519f4efbe1.png
[Oracle_]: https://blog.csdn.net/qq_43652509/article/details/86096360
[Oracle_ 1]: https://blog.csdn.net/qq_43652509/article/details/86096706
[Oracle_ 2]: https://blog.csdn.net/qq_43652509/article/details/86096661
[Oracle]: https://blog.csdn.net/qq_43652509/article/details/86096587
[Oracle 1]: https://blog.csdn.net/qq_43652509/article/details/86096497
[Oracle_ 3]: https://blog.csdn.net/qq_43652509/article/details/86292696
[Oracle_ 4]: https://blog.csdn.net/qq_43652509/article/details/86301674
[Oracle_PL_SQL_]: https://blog.csdn.net/qq_43652509/article/details/86303911
[Oracle_Explain Plan]: https://blog.csdn.net/qq_43652509/article/details/86319169
[Link 1]: https://blog.csdn.net/qq_43652509/article/details/86319322