Oracle的使用笔记

左手的ㄟ右手 2022-01-15 11:47 205阅读 0赞

[为什么80%的码农都做不了架构师？>>> ][80_] ![hot3.png][]

**1. 虚表dual**

虚表是Oracle提供的用于操作函数的方式，属于sys用户，共享给所有用户使用。虚表dual是单行单列的表，表中存放一个常量数据X。

虚表的意义：更方便的操作函数或者查询常量。

**2. 函数**

**单行函数**：表中的一列作为函数的参数，对于每一条记录，函数都有一个返回值。例如：upper lower nvl

**多行函数**：表中的一列作为函数的参数，将那个记录分组，对于每组数据，函数返回一个值。例如：avg

**3. 单行函数根据处理参数的数据类型分为：**

**3.1 字符函数**： upper, lower

**3.2 数值函数**：

**　　round** **四舍五入**

round(12.345,2)=12.35
    round(12.345,0)=round(12.345)=12(保留小数点前一位，即个位，是舍是收应该看小数点后一位)
    round(12.345,-1)=10（保留小数点前二位，即十位）

**trunc** **截取**

trunc(12.345,2)=12.34
    trunc(12.345,0)= trunc(12.345)=12
    trunc(12.345,-1)=10

**3.3 日期和日期函数**

select sysdate from dual;

日期类型数据用**7个字节来依次保存世纪、年、月、日、时、分、秒，格式敏感**

**3.3.1. 设置会话的日期格式：**

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

之后再查询：

select sysdate from dual;

就会发现显示的时间格式已经改变

重新连接该用户之后，又会以默认的日期格式来显示日期。

to\_date的返回类型是date，把字符串转换成了date类型，所以，参数的数据类型是**【字符】**类型

’01-1月-10’系统进行了隐式转换。调用了to\_date函数

插入数据后，可以这样查看详细日期：

select to_char(c1,’yyyy-mm-dd hh24:mi:ss’) from test;

‘2008-08-08’，用户做显式数据类型转换，自己调用to\_date(‘2008-01-01’,’yyyy-mm-dd’),第二个参数是对第一个参数的格式说明

**a.** **会话级**：

alter session set nls_date_format

**b.** **语句级**：

select to_char(c1……)

**to\_char**的返回类型是字符类型，把date类型转换成字符串类型，所以参数的数据类型是date。to\_char函数可以获得日期的任何一部分信息，比如年。

**fm****表示去掉前导0或去掉两边的空格**。比如字符串0000234字符串234。

**3.3.2. 日期的运算**

日期可以加减一个数字，单位为天。例如：查看今天、明天、后天的日期：

select sysdate-1,sysdate,sysdate+1 from dual;

计算十分钟后：

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+1/144,'yyyy-mm-dd hh24:mi:ss') from dual;

其中的1/144就是十分钟，因为按天来作为单位1的。

两个日期相减

**add\_months** **按月加 返回类型是date**

select add_months(sysdate,6) from dual;
    select add_months(sysdate,-6) from dual;

**months\_between()**两个日期之间相差多少个月，**返回类型是number**

例如：计算员工的雇佣日期

select months_between(sysdate,hiredate) as hiremonths from emp;

**last\_day(sysdate)** **本月的最后一天**

**3.3.3. 转换函数小结**

to\_date()转换成日期

to\_char()转换成字符串

to\_number()转换成数字

**3.4 其他函数**

**a. coalesce****参数可以很多个，取第一个非空的值作为函数值。类似nvl函数。它还是oracle专有的函数。**

coalesce(bonus,salary\*0.1,100)如果bonus为空，则看salary\*0.1是否为空，如果第二个也为空，则取第三个值作为结果。如果第二个不为空，则取第二个的值。

**b. case when** **条件表达式 then 结果**

**when** **条件表达式 then 结果**

**。。。。。。**

**else** **结果**

**end**

如果没有else，当条件又不匹配的时候，此表达式返回null。

**c. decode：****简略版的case when**

select ename, decode(deptno, 10, salary*1.1, 20, salary*1.2, salary) from emp;

同样，若没有最后一个参数，函数返回null。

**4. 多行函数（组函数）**

**avg ()**平均值 返回number 参数只能是number

**　　sum()**求和 返回number 参数只能是number

**　　count()**计数 返回number 参数可以是number date和字符

**　　count(\*)**统计记录数

**　　max()**最大值 是什么就返回什么 参数可以是number date和字符

**　　min()**最小值 是什么就返回什么 参数可以是number date和字符

**　　avg(bonus)****所有有奖金的员工的平均值**

**　　avg(nvl(bonus,0))****所有员工奖金的平均值**

**　　count(bonus)****有奖金的员工的个数**

当所有的值都是null，count函数返回0，其他组函数返回null

**5. Group by****子句**

若有group by 子句，select后面跟组标识和组函数

各子句执行的顺序：**【from---w****here---****group by---****select---****order by****】**

若没有group by 子句，select后面只要有一个是组函数，其余的都得是组函数。

**6. Having****子句**

select deptno,round(avg(salary)) from emp where deptno in (20,30) group by deptno having round(avg(salary))>5000

执行顺序：**【from****---****where****---****group by****---****having****---****select****---****order by****】**

**7. where****和having的比较**

共同点：都在执行select之前，都有过滤的功能

不同点：where执行在having之前

where过滤的是**【记录】**，任意列名都可以出现在where子句，单行函数可以用在where子句。组函数不能出现在where子句里

having过滤的是**【组】**，组标识可以出现在having子句，其他列名不行。组函数用于having子句，但行函数不可以

**8. 关于null的讨论**

*  case when 在没有else和decode少一个参数时，返回null
 *  order by bonus,asc升序时null值在最后，desc降序时null在最前（把null看作无穷大即可）。
 *  组函数和null值的关系：1.组函数的缺省处理方式是处理所有的【**非空值】**2.当所有的值都是null，count函数返回0，其他函数返回null
 *  若group by的列有null值，所有的null值分在一起

转载于:https://my.oschina.net/itblog/blog/204166

[80_]: https://my.oschina.net/u/2663968/blog/3051541
[hot3.png]: /images/20220115/a70e2f01a693473395c7a9b56e2b30ba.png