Oracle的使用笔记

Wesley13
• 阅读 622

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值分在一起

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Easter79 Easter79
3年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
Stella981 Stella981
3年前
JS 对象数组Array 根据对象object key的值排序sort,很风骚哦
有个js对象数组varary\{id:1,name:"b"},{id:2,name:"b"}\需求是根据name或者id的值来排序,这里有个风骚的函数函数定义:function keysrt(key,desc) {  return function(a,b){    return desc ? ~~(ak
Stella981 Stella981
3年前
HIVE 时间操作函数
日期函数UNIX时间戳转日期函数: from\_unixtime语法:   from\_unixtime(bigint unixtime\, string format\)返回值: string说明: 转化UNIX时间戳(从19700101 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式举例:hive   selec
Stella981 Stella981
3年前
PostgreSQL Oracle 兼容性之
Oracle使用sys\_guid()用来产生UUID值。 在PostgreSQL中有类似的函数,需要安装uuidossp插件。 如果用户不想修改代码,还是需要使用sys\_guid()函数的话,可以自己写一个。 如下:1.postgres\createextension"uuidossp";2.CREATE
Wesley13 Wesley13
3年前
oracle查询表数据并重新插入到本表
oracle查询表数据并重新插入到本表CreateTime2018年5月17日10:30:10Author:Marydon1.情景描述查询表中数据SELECTFROMat_aut
Wesley13 Wesley13
3年前
mysql——GROUP BY和HAVING
GROUPBY语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。select子句中的列名必须为分组列或列函数,列函数对于groupby子句定义的每个组返回一个结果。某个员工信息表结构和数据如下:  id  name  dept  salary  edlevel     hiredate   1  张
Wesley13 Wesley13
3年前
PHP中的NOW()函数
是否有一个PHP函数以与MySQL函数NOW()相同的格式返回日期和时间?我知道如何使用date()做到这一点,但是我问是否有一个仅用于此的函数。例如,返回:2009120100:00:001楼使用此功能:functiongetDatetimeNow(){
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究