文档章节

Oracle学习笔记(二)

weslie
 weslie
发布于 2015/06/29 13:57
字数 1715
阅读 29
收藏 0

SQL函数

     SQL函数分单行函数和多行函数俩种

一、单行函数

      单行函数是只操作数据库表中的一条记录并返回一条结果,这样的函数称为单行函数。若操作多条记录,则每条记录都会返回一个结果。

     单行函数分5大类:字符函数、数字函数、日期函数、转换函数、通用函数。

      dual是Oracle中的一个“伪表”

      1、大小写控制函数(字符函数)

                 lower      全部转为小写
                 upper     全部转为大写
                 initcap    每个单词的首字母转为大写

 

select lower('DO WHATEVER YOU WANT'),upper('do whaterver you want'),initcap('do whaterver you want')
from dual;
--输出结果:
--do whatever you want      DO WHATERVER YOU WANT      Do Whaterver You Want

select * from employees
where lower(last_name) = 'king'
--lower(last_name)是先把last_name转为小写,再与king比较

 

      2、字符控制函数(字符函数)             concat     连接字符串
            substr     截取字符串
            length     获取字符串长度

 

 

 

select concat('hello','world'),substr('helloworld',2,4),length('helloworld')
from dual;
--输出结果:
--helloworld     ello     10
--上面substr的意思是从第二个字符开始,截取后面的4个字符,如果不写会截取后面的全部
--注意:Oracle里下标都是从1开始的

            instr        获取字符或字符串在某字符串中首次出现的下标位置
            lpad        左边补齐,即右对齐
            rpad        右边补齐,即左对齐
            trim         去除首尾字符,中间出现的则不能去除                          
            replace   替换字符

 

 

select instr('helloworld','l') from dual;
--输出结果:3
--如果查找的字符或字符串不存在则返回0

select last_name,lpad(salary,10,'0'),rpad(salary,10,'*')
from employees
--lpad是将工资右对齐,工资输出长度为10,不足10位的用*左边补齐
--rpad是将工资左对齐,工资输出长度为10,不足10位的用*右边补齐

select trim('h' from 'hhhehllohhworldhh') from dual;
--仅去除首尾的
--输出结果:ehllohhworld

select replace('abcdab','b','m') from dual;
--讲字符串中的b替换成m,替换的是字符串中所有存在的
--输出结果:amcdam      

 

       3、数字函数

             round    四舍五入

             trunc      截断,不管后面是什么都舍掉

             mod       求余,如 mod(12, 5) 结果为2

 

select round(10532.92,2),round(10532.92),round(10532.92, -2),round(10532.92, -3) from dual;
--round(10532.92,2):保留两位小数
--round(10532.92):取整
--round(10532.92, -2):即十位上进行四舍五入
--round(10532.92, -3):即百位上进行四舍五入
--输出结果:10532.92        10533        10500        11000

select trunc(10532.92,2), trunc(10532.92, 0),trunc(10532.92, -1) from dual;
--输出结果:10532.92        10532        10530

select mod(12,5) from dual;
--输出结果:2

      4、日期函数:

 

           Oracle中的日期型数据实际含有俩个值:日期和时间。默认的日期格式是DD-MON-RR(天月年)

          (1)日期的数学运算

                  * 日期与数字:在日期上加上或减去一个数字结果仍为日期

                  * 俩个日期相减返回日期之间相差的天数,但两个日期不能相加,日期可以和数字加减

                  * 可以用数字除24来向日期中加上或减去小时。

 

select sysdate-1,sysdate, sysdate+1,sysdate-1/24,sysdate-1/24/60 from dual;
--sysdate-1  昨天
--sysdate    今天
--sysdate+1  明天
--sysdate-1/24  当前时间减去一小时
--sysdate-1/24/60  当前时间减去一分钟   

select empName, sysdate-hireDate from employees;
--hireDate是员工入职时间,
--sysdate-hireDate,两个日期相见计算天数

          (2)日期函数

 

                   months_between:俩个日期相差的月数

                   add_months:向指定日期上加上或减去N个月

                   next_day:获取指定时间的下个星期N的日期时间

                   last_day:本月的最后一天

                   round:日期四舍五入

 

                   trunc:日期截断                  

 

select months_between(sysdate,hireDate) from employees
--会将sysdate减去hireDate,该函数返回的结果可以是正、负、0

select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期六') from dual;
--next_day下个星期六的日期时间

select last_day(sysdate) from dual;
--查询本月的最后一天

select empName,hireDate from employees
where hireDate = last_day(hire-date)-1;
--查询是每个月倒数第二天入职的员工

select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh')
from dual;

alter session set NLS_DATE_FORMAT='yyyy-mm-dd'; 
-- 修改当前session的日期格式

 

      5、转换函数:

 

 

         (1)隐式数据类型转换,Oracle自动完成varchar2或char与date或数字之间的转换

                    

         (2)显式数据类型转换

                  * to_char:日期或数字转换 成字符串

                  *to_date:字符串转换成日期

                  *to_number:字符串转换成数字

                  下面是to_char和to_number函数经常使用的几种格式:

                     

 

select hireDate from employees
where to_char(hireDate,'yyyy-mm-dd') = '1994-7-6'--日期转换成字符串
or to_date('1994-7-7','yyyy-mm-dd') = hireDate--字符串转换成日期

select to_char(123456.89,'999,999,999.99'),to_char(123456.89,'L999,999,999.99'),to_number('$123,456.89','$999,999,999.99')
from dual;
--数字与字符串的转换
--L999,999,999.99:这个是数字转换的格式,可以用9或0

      6、通用函数

          这些函数适用于任何数据类型,同时也适用于空值:

          NVL(expr1, expr2):滤空函数,不为Null返回本身的值,expr1为Null时返回expr2

          NVL(expr1, expr2, expr3):不为Null返回expr2,expr1为Null时返回expr3

          NULLIF(expr1, expr2):expr1等于expr2返回Null,不等返回expr1

          COALESCE(expr1, expr2, ... exprn):若expr1为Null,则返回expr2,若expr2也为Null则返回exprn

 

select empName,salary*12*(1+nvl(commission,0))
from employees;
--若commission(奖金)为Null返回0,不为Null就是本身的值

select empName,nvl2(commission,commission*0.015,0.01)
from employees;
--若奖金不为Null返回commission*0.015,为Null返回0.01

select firstName,lastName,nullif(length(firstName),length(lastName))
from employees;
--如果firstName与lastName的长度相等返回Null,不等返回firstName的长度

select empName,coalesce(commission,salary,10)
from employees;
--如果commission为Null,返回salary,若salary也为Null则返回10


      7、条件表达式

 

           (1) CASE ... WHEN ... THEN ...ELSE ...END

 

select empName,deptId,
case deptId 
     when 10 then salary * 1.1
     when 20 then salary * 1.2
     else salary * 1.3
end new_salary
from employees
where deptId in(10,20,30);
--查询部门号为10,20,30的员工信息,若部门号为10,则打印其工资的1.1倍
--若部门号20,则打印其工资的1.2倍,若部门号30,则打印其工资的1.3倍
--PS:new_salary 是别名

          (2)DECODE

 

select empName,deptId,
decode(deptId,
       10,salary * 1.1,
       20,salary * 1.2,
       salary
) new_salary
from employees
where deptId in(10,20,30);
--deptId为10时工资为salary * 1.1,20号部门工资为salary * 1.2,30号部门为salary

 

二、多行函数

 

       多行函数是操作数据库表中多条记录返回一条结果。不能在WHERE子句中使用组函数,可以在HAVING子句中使用组函数

      1、组函数

         (1)AVG(平均值)

         (2)COUNT(总数)

                  COUNT(expr):返回expr不为空的记录总数

                  COUNT(DISTINCT expr):返回expr非空且不重复的记录总数

         (3)MAX(最大值)

         (4)MIN(最小值)

         (5)SUM(总和)

       组函数忽略空值,可以使用NVL函数,使组函数无法忽略空值

         max和min里的参数类型可以是number、varchar、date

         avg和sum里的参数类型只能是number

         组函数计算的都是非空的

© 著作权归作者所有

weslie
粉丝 1
博文 51
码字总数 80088
作品 0
虹口
程序员
私信 提问
Oracle笔记 目录索引

Oracle笔记 一、oracle的安装、sqlplus的使用 Oracle笔记 二、常用dba命令行 Oracle笔记 三、function 、selectOracle笔记 四、增删改、事务 Oracle笔记 五、创建表、约束、视图、索引、序列...

ibm_hoojo
2011/05/03
0
0
Oracle SQL的执行(一)

一、SGA--共享池 共享池是oracle缓存程序数据的地方.执行过的每一条sql语句在共享池中都存有解析后的内容.这个部分称作库高速缓存.在oracle解析每条sql之前,先检查库高速缓存,如果存在,就使用...

记忆的美好
2012/09/24
105
0
java 获取指定目录下的所有文件

java ,如何获取指定目录下的所有文件呢? 看代码: 说明:上述方法采用了递归,所以包含子目录下的子目录中的文件。。。 测试代码: 输出结果: d:Tempaadividedmergedoracle学习笔记.doc ...

zh119893
2014/05/10
325
0
怎样成长为一个真正的Oracle DBA (转载)

Oracle分两大块,一块是开发,一块是管理。 开发主要是写写存储过程、触发器什么的,还有就是用Oracle的Develop工具做form。有点类似于程序员,需要有较强的逻辑思维和创造能力,个人觉得会比...

y862318
2014/05/14
0
0
ORACLE学习笔记(二)​

DB Administration Tools Oracle Universal Installer(OUI) :OUI是用来安装、升级和删除Oracle软件。 Oracle Database Configuration Assistance(DBCA) :DBCA是一个图形界面的程序,可...

coolio
2014/10/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

CentOS 7系统增加swap

转载请注明文章出处:CentOS 7系统增加swap swap是位于磁盘上的特殊文件(或分区),属于“虚拟内存”的一部分。通俗点就是内存的备胎,内存充足的情况下,基本上没swap什么事(和设置有关)...

tlanyan
21分钟前
2
0
基于Prometheus和Grafana的监控平台 - 环境搭建

相关概念 微服务中的监控分根据作用领域分为三大类,Logging,Tracing,Metrics。 Logging - 用于记录离散的事件。例如,应用程序的调试信息或错误信息。它是我们诊断问题的依据。比如我们说...

JAVA日知录
今天
5
0
PHP运行时全局构造体

struct _php_core_globals { zend_bool magic_quotes_gpc; // 是否对输入的GET/POST/Cookie数据使用自动字符串转义。 zend_bool magic_quotes_runtime; //是否对运行时从外部资源产生的数据使...

冻结not
今天
4
0
webpack插件html-webpack-plugin

本文转载于:专业的前端网站→webpack插件html-webpack-plugin 1、插件安装 npm install html-webpack-plugin --save-dev 2、插件使用 webpack.config.js配置文件为: var htmlWebpackPlugin=...

前端老手
今天
6
0
数据挖掘

zhengchen1996
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部