Oralce 函数表达式 的应用
来源:互联网 发布:图片尺寸修改软件 编辑:程序博客网 时间:2024/05/21 09:36
这几天 预习了一遍 Oracle , 此处把 Oralce 数据库 中 视图 的笔记 拿出来 分享 一下, 该文中 有的地方 或许存在 点错误, 希望 看到的朋友 帮我指正出来,谢谢。
Oracle 中提供了 大量的 内置函数, 已处理各种形式的运算。 这些函数涵盖了 字符串运算、数值运算、日期运算等方面, 同样 Oracle允许使用数值运算、逻辑运算等基表达式运算, 另外,提供了SQL标准所规定的特殊判式。Oracle 中的 字符串函数;Oracle 中的 数学函数;Oracle 中的 日期函数;Oracel 中的 聚合函数;Oracle 中的 运算表达式;Oracle 中的 特殊判式;Oracle 中的 高级函数—分析函数与窗口函数;-- lpad() 函数 : 用于 左补全 字符串。 在某些情况下, 预期的字符串为固定长度, 而且格式统一, 此时可以考虑使用 lpad() 函数。 例如, 深圳股票代码都是 以 0 开头, 并且都为 6为, 可以利用 lpad格式化股票代码, 以保证 股票代码的格式。select lpad('21',6,'0') stock_code from dual;--需要注意的是, 当原字符串的长度大于 预期长度时, 实际进行的是 截取字符串操作。select lpad('1234567889',6,'0') stock_code from dual;-- rpad() 函数 : 与 lpad() 函数 相反, rpad() 函数从右端补齐字符串。select rpad('abc',10,'*') from dual;--需要注意的是: -- lpad() 和 rpad() 都用于填充字符串, lpad() 从左端进行填充, 而 rpad() 从右端进行填充,-- 但是 二者在最终截取字符串时, 都是 从 左端开始截取 的。select rpad('abcdefg'5,'*') from dual; --这句代码执行后 ,截取出来的饿时 ‘abcde’-- lower() 函数: 返回小写字符串 。 lower() 函数用于返回字符串的小写形式。 lower() 函数在查询语句中经常 扮演重要角色。 例如, 对于用户名和密码的小眼来说, 用户名一般不区分大小写, 用户无论输入了大写还是小写的形式, 都被认为是合法用户。 因此, 在数据库查询时, 应该将数据库中用户名与用户输入的用户名进行统一。--select user_id,user_name from t_users where lower(user_name)=lower('Alex');SQL> select empno,ename from emp where lower(ename)=lower('joNes'); EMPNO ENAME---------- ---------- 7566 JONES--不使用 lower() 函数的形式 会出现如下结果:SQL> select empno,ename from emp where ename='joNes';未选定行SQL>-- upper() 函数: 返回大写字符串。 upper() 函数返回字符串的大写形式。 与 lower() 函数类似, upper() 函数也可以用在查询语句中, 以统一数据和 查询条件的一致性。--select user_id, user_name from t_users where upper(user_name)=upper('ALEX');SQL> select empno,ename from emp where upper(ename)=upper('jones'); EMPNO ENAME---------- ---------- 7566 JONESSQL>--不使用 upper() 函数的形式 会出现如下结果:SQL> select empno,ename from emp where ename='jones';未选定行SQL>--z注意与说明: upper() 函数 和 lower() 函数 只针对 英文字符其作用, 因为只有 英文字符才有大小写之分。-- initcap() 函数 : 单词 首字母大写。 select initcap('big') from dual; SQL> select initcap('big') from dual;INI---Big-- 需要注意的是, initcap() 函数 不能自动识别单词。 select initcap('bigbigtiger') from dual;SQL> select initcap('bigbigtiger') from dual;INITCAP(BI-----------Bigbigtiger-- initcap() 函数会将参数中的 非单词字符 作为 单词分隔符 SQL> select initcap('big_big_tiger') from dual; ----------- Big_Big_TigerSQL> select initcap('big/big/tiger') from dual; ----------- Big/Big/TigerSQL> select initcap('big big tiger') from dual; ----------- Big Big Tiger -- length() 函数 : 返回字符串 长度 。 select length('abcd ) from dual;-- 空字符串的长度不是 0 , 而是 null . 因为空字符串被 视作 null , 所以, length(null) 返回的仍然是 null.-- select length('') from dual;SQL> select length('') from dual; LENGTH('') ----------SQL>-- 对其他数据类型, 照样可以通过 length() 函数 来获得其 长度。 length() 函数 会首先将 参数转换为字符串, 然后计算其长度。-- select length(12.51) from dual;SQL> select length(12.51) from dual; LENGTH(12.51) ------------- 5SQL>-- substr() 函数: 截取字符串 。 substr() 函数用户截取字符串。 该函数可以指定 截取的起始位置, 截取长度, 可以实现灵活的截取操作, 因此, 称为字符串操作中最为常用的 函数之一。--例如: 对于 字符串“1234567890”, 现在欲截取 自 第5位开始的 4个字符。 select substr('1234567890',5,4) from dual;SQL> select substr('1234567890',5,4) from dual;SUBS----5678SQL>-- 需要注意的是 Oracle 中的 字符位置 是从 1 开始, 而不是向某些编程语言 (如Java)那样从 0 开始。-- 如果 不指定长度, 那么 substr() 函数 将获取 起始位置参数 字符串结尾处 的所有字符。-- select substr('1234567890',5) from dual;SQL> select substr('1234567890',5) from dual; SUBSTR ------ 567890SQL>-- instr() 函数 : 获得 字符串出现位置 。 instr() 函数用于获得字符串在父字符串中出现的位置--例如: select instr('big big tiger','big') from dual;SQL> select instr('big big tiger','big') from dual;INSTR('BIGBIGTIGER','BIG')-------------------------- 1 -- 可以指定额外的参数, 以命令该函数从 指定位置开始搜索。SQL> select instr('big big tiger','big',2) from dual;INSTR('BIGBIGTIGER','BIG',2)---------------------------- 5SQL>-- 还可以指定出现次数参数, 以指定是 第几次搜索到的子字符串。--例如: select instr('big big tiger','big',2,2) from dual;SQL> select instr('big big tiger','big',2,2) from dual; INSTR('BIGBIGTIGER','BIG',2,2) ------------------------------ 0 -- ltrim() 函数 : 删除字符串 首部空格。 ltrim() 中 l 代表 left 。 该函数用于删除字符串左端的空白符。-- select ltrim(' abc) from dual;SQL> select ltrim(' abc') from dual;LTR---abcSQL>-- 需要注意的是, 空白符 不仅仅包括了 空格符, 还包括 TAB键、 回车符 和 换行符。-- rtrim() 函数: 删除字符串 尾部空格 。 rtrim() 中 的 r 代表 right 。 该函数用于删除字符串右端空白符 。 删除字符串 首尾空白符可以结合 使用 ltrim() 和 rtrim() 函数。-- select rtrim(ltrim( ' abc ')) from dual;SQL> select rtrim(ltrim(' abc ')) from dual;RTR---abcSQL>-- trim() 函数 : 删除字符串首尾空格 。 trim() 函数可以用于删除首尾空格, 相当于 ltirm() 和 rtrim() 的组合。-- select trim(' abc ') from dual; SQL> select trim(' abc ') from dual;TRI---abcSQL> -- to_char() 函数 : 将其他类型转换为字符类型,这些类型主要包括数值型、 日期型。1. 将数值型转为 字符串--select to_char(120,'99999') result from dual;SQL> select to_char(120,'99999') result from dual;RESULT------ 120SQL>--select to_char(0.96,'9.99') result from dual;SQL> select to_char(0.96,'9.99') result from dual;RESUL----- .96SQL>--select to_char(0.96,'0.00') result from dual;SQL> select to_char(0.96,'0.00') result from dual;RESUL----- 0.96 --select to_char(5897.098,'999,999,999.000') result from dual;SQL> select to_char(5987.098,'999,999,999.000') result from dual;RESULT---------------- 5,987.098SQL>--select to_char(5987.098,'$999,999,999.000') result from dual;SQL> select to_char(587.098,'$999,999,999.000') result from dual;RESULT----------------- $587.098SQL>2. 将日期型转换为 字符串--select to_char(sysdate,'yyyy-mm-dd') result from dual;SQL> select to_char(sysdate,'yyyy-mm-dd') reuslt from dual;REUSLT----------2012-03-05SQL>--select to_char(sysdate,'YYYY-MON-DD') from dual;SQL> select to_char(sysdate,'YYYY-MON-DD') from dual;TO_CHAR(SYSDATE,----------------2012-3月 -05SQL>--创建 一个 名为 songyanjun 的表空间 create tablespace songyanjun datafile 'D:\oracle\product\10.2.0\oradata\orcl\songyanjun.dbf' size 100m autoextend on next 32m maxsize 2048m extent management local;-- 创建一个 名为 junjun 的用户 以及 用户密码 , 给其指定默认 songyanjun 的这个表空间 , 以及指定 临时 TEMP表空间create user junjun identified by junjun default tablespace songyanjun temporary tablespace TEMP;-- 给予 用户 连接 数据库的权限grant connect to junjun;-- 给予 用户 dba身份(超级管理员) 权限grant dba to junjun;--表空间之外的权限 无权操作。(无权利的地方,意思你无法执行操作,没有权限。)grant unlimited tablespace to junjun;--创建一个 测试表-- Create tablecreate table eusers( eid number, ename varchar2(30), eage number)tablespace SONGYANJUN storage ( initial 64K minextents 1 maxextents unlimited );-- Create/Recreate primary, unique and foreign key constraints alter table eusers add constraint PK_eid primary key (EID);-- 插入测试语句SQL> insert into eusers values(8,'syj'||chr(13)||chr(10)||'宋延军',26);已创建 1 行。-- 根据 刚才插入的eid 进行查询结果SQL> select * from eusers where eid=8; EID ENAME EAGE---------- ------------------------------ ---------- 8 syj 26 宋延军-- translat() 函数: 替换字符 。 translate() 函数用于替换字符串。 替换的规则类似于 翻译的过程。 --例如: select translate('56338','1234567890','avlihemoqr') result from dual;需要注意的是 , 当字符不能被成功“翻译”, 那么, Oracle 将使用 空字符串替换它。 利用此特性, 可以使用 translate() 函数删除一个 含有 数字和 英文字母的字符串中的 所有字母:--例如:select translate('112313jlkeuak77466asd888','#abcdefghijklmnopqrstuvwxyz',' ') result from dual;--SQL> select translate('112313jlkeuak77466asd888','#abcdefghijklmnopqrstuvwxyz',' ') result from dual;RESULT--------------11231377466888--=================================-- Oracle 中的数学函数 。 (Oracle 提供的数学函数可以处理日常使用大的大多数数学运算。)-- abs() 函数: 返回 数字的绝对值 。 abs() 函数的参数只能是数值型, 该参数用于返回参数的绝对值。--例如:select abs(-2.1) from dual;SQL> select abs(-2.1) from dual; ABS(-2.1)---------- 2.1-- round() 函数: 返回i数字的 “四舍五入”值。 round() 函数用于返回某个数字的四舍五入值。了使用该函数, 除了提供原始值之外, 还应提供精确到的位数。精确位数可以为正整数、0和负整数。--例如:select round(2745.173,2) result from dual;SQL> select round(2745.193,2) result from dual; RESULT---------- 2745.19-- 如果不实用第二个参数, 那么 , 相当于使用了参数 0 , 即精确到整数。--例如:select round(2746.193) result from dual;SQL> select round(2746.193) result from dual; RESULT---------- 2746--如果第二个参数为负数,那么, 相当于将数值精确到小数点之前的位数。--例如: select round(2746,-1) result from dual;SQL> select round(2746,-1) result from dual; RESULT---------- 2750 -- ceil() 函数: 向上去整 。 ceil() 函数只能有一个参数。 该函数将参数向上去整, 以获得大于等于该参数的最小整数。--例如: select ceil(21.897) result from dual;SQL> select ceil(21.897) result from dual; RESULT---------- 22--需要注意的是 该函数真对 负数的运算:--select ceil(-21.897) result from dual;SQL> select ceil(-21.897) result from dual; RESULT---------- -21--说明: 因为 ceil() 函数返回的是大于等译参数的最小整数, 所以, 该函数返回的并非-22, 而是-21 。-- floor() 函数: 向下去整。 与 ceil()函数 相反 , floor() 函数用于返回 小于等于某个数值的最大整数。--例如: select floor(21.897) result from dual;SQL> select floor(21.897) result from dual; RESULT---------- 21--例如: select floor(-21.897) result from dual;SQL> select floor(-21.897) result from dual; RESULT---------- -22 -- mod() 函数: 取模操作 。 mod() 函数有两个参数, 第一个参数为被除数, 第二个参数为除数。 mod() 函数的实际功能为获得两个数相除之后的余数。--例如: select mod(5,2) result from dual; SQL> select mod(5,2) result from dual; RESULT---------- 1 -- sign() 函数: 返回数字的正负形。 sign() 函数只有一个参数。 该函数就爱那个返回参数的正负性。 若返回值为1, 表示该参数大于0; 若返回值为-1, 表示该参数小于0;若返回值为0,表示该参数等于0.--例如: select sign(8) result from dual;SQL> select sign(8) from dual; SIGN(8)---------- 1--例如: select sign(-8) result from dual; SQL> select sign(-8) from dual; SIGN(-8)---------- -1--例如: select sign(0) result from dual;SQL> select sign(0) from dual; SIGN(0)---------- 0-- sing() 函数为 判断两个数值的大小关系提供了方便。因为在 ORACLE 中, 利用类似 if else 的结构来判断两个数值之间的大小关系,并不像 编程语言中那样方便, 而且极易造成代码复杂化。-- sqrt() 函数: 返回数字的平方根 。 sqrt() 函数也只有一个参数。 该函数用于返回参数的平方根。 可以利用 round() 函数 和 sqrt() 函数返回某个数值的近似平方根。--例如: select round(sqrt(2),3) result from dual;SQL> select round(sqrt(2),3) result from dual; RESULT---------- 1.414-- power() 函数: 乘平方运算 。 power() 函数有两个参数。 该函数用于实现数值的乘平方运算。--例如: select power(6,2) result from dual;SQL> select power(6,2) result from dual; RESULT---------- 36-- trunc() 函数: 截取数字 。 trunc() 函数用于截取部分数字。 其工作机制非常类似于 round()函数 。 与 round() 函数不同的是, 该函数不对数值做四舍五入处理, 而是直接截取。--例如: select trunc(2745.173,2) result from dual;SQL> select trunc(2745.173,2) result from dual; RESULT---------- 2745.17 --保留位数的值 可以为 0 , 当该参数的值为 0 时, 将保留到整数。--例如: select trunc(2745.173) result from dual;SQL> select trunc(2745.173) result from dual; RESULT---------- 2745--当保留位数 小于 0 时, 表示 保留到小数点之前的位数。--例如: select trunc(2745.173,-1) result from dual;SQL> select trunc(2745.173,-1) result from dual; RESULT---------- 2740-- vsize() 函数: 返回数据的存储空间 。 vsize() 函数根据数据库的存储格式, 来返回其所占用的存储空间字节数。-- 例如: select vsize('abc123') from dual;SQL> select vsize('abc123') from dual;VSIZE('ABC123')--------------- 6注意与说明: vsize() 函数在返回的是 Oracle 实际存储数据的字节数, 在实际开发中使用的几率也较小。读者可以不必了解 Oracle本身的存储机制。-- to_number() 函数: 将字符串转换为数值类型 。 to_number() 函数可以将字符串转换为数值型。--例如: select to_number('267.90') result from dual;SQL> select to_number('267.90') result from dual; RESULT---------- 267.9--需要注意的是, 被转换的字符串 必须符合数值类型格式。 如果被转换的字符串不符合数值类型格式, Oracle将会抛出错误提示。--例如:select to_number('a') result from dual;SQL> select to_number('a') result from dual;select to_number('a') result from dual *第 1 行出现错误:ORA-01722: 无效数字--=============================================-- Oracle 中的日期函数-- Oracle 提供了丰富的日期函数。利用日期函数可以灵活的对日期进行运算。-- to_date() 函数: 将字符串转为日期型 。 to_date() 函数用于将字符串转换为日期。 被转换的字符串必须符合特定的日期格式。--例如: select to_date('2012-03-06','yyyy-mm-dd') result from dual;SQL> select to_date('2012-03-06','yyyy-mm-dd') result from dual;RESULT--------------06-3月 -12-- add_months() 函数将为 日期添加特定月份, 并获得新的日期。--例如: select to_char(add_months(sysdate,2),'yyyy-mm-dd') result from dual;SQL> select to_char(add_months(sysdate,2),'yyyy-mm-dd') result from dual;RESULT----------2012-05-06-- last_day() 函数: 返回特定日期所在月 的最后一天。 last_day() 函数将接收一个日期参数。 该函数首先获得日期参数所在月的信息, 然后获得该月最后一天的日期。--例如: select to_char(last_day(sysdate),'yyyy-mm-dd') result from dual;SQL> select to_char(last_day(sysdate),'yyyy-mm-dd') result from dual;RESULT----------2012-03-31--可以综合利用 add_months() 函数来获得 若干月之后的月份的 最后一天。--例如:select to_char(last_day(add_months(sysdate,3)),'yyyy-mm-dd') result from dual;SQL> select to_char(last_day(add_months(sysdate,3)),'yyyy-mm-dd') result from dual;RESULT----------2012-06-30-- months_between() 函数: 返回连个日期所差的月数。 months_between() 函数用于获取两个日期所间隔的月数。 该函数的返回值 是一个实数。--例如: select months_between(sysdate,to_date('2012-03-06','yyyy-mm-dd')) result from dual;-- 返回 2011年1月1日 至 当天(2012年3月6日)的月数。SQL> select months_between(sysdate,to_date('2011-01-01','yyyy-mm-dd')) result from dual; RESULT----------14.1814512--注意: 当第一个日期早于第二个日期, 那么返回值将是 负值。--例如: select months_between(to_date('2013-01-01','yyyy-mm-dd'),to_date('2012-03-06','yyyy-mm-dd')) result from dual;SQL> select months_between(to_date('2013-01-01','yyyy-mm-dd'),to_date('2012-03-06','yyyy-mm-dd')) result from dual; RESULT----------9.83870968-- current_date() 函数: 返回当前会话时区的当前日期 。 current_date() 函数 用于返回当前会话时区的当前日期。--例如: select sessiontimezone,to_char(current_date,'yyyy-mm-dd hh:mi:ss') result from dual;SQL> select sessiontimezone,to_char(current_date,'yyyy-mm-dd hh:mi:ss') result from dual;SESSIONTIMEZONE RESULT--------------------------------------------------------------------------- -------------------+08:00 2012-03-06 03:13:08--注意与说明: -- current_date 等无参数函数座位 Oracle的关键字存在。 --在使用时, 不能为其添加小括号。 --即 select current_date() from dual; 是错误的SQL语句。-- current_timestamp() 函数: 返回当前会话时区的当前时间戳 。 current_timestamp() 函数 用于返回当前回话时的区时间戳。 可以结合 sessiontimezone 来查看器用法。--例如: select sessiontimezone,current_timestamp from dual;SQL> select sessiontimezone,current_timestamp from dual;SESSIONTIMEZONE---------------------------------------------------------------------------CURRENT_TIMESTAMP---------------------------------------------------------------------------+08:0006-3月 -12 03.20.33.349000 下午 +08:00-- extract() 函数: 返回日期的某个 域 。 日期有若干域组成, 例如年、 月、 日、小时等等。 extract() 函数可以返回这些域的具体值。 为了使用该函数,除了要指定原日期外, 还应该指定要返回的域名。--例如: selct extract(year from sysdate) result from dual;SQL> select extract(year from sysdate) result from dual; RESULT---------- 2012需要注意的是, year、month、day 域只能从日期(如 sysdate)中获得, 而 hour、 minute、second 只能从事件型(如 systimestamp)中获得。--======================================--Oracle中的聚合函数--Oracle 中的聚合函数: 所谓聚合函数是指 真对多条记录的函数。 Oracle最常用的聚合函数包括, max()、min()、 avg()、sum() 和 count() 函数。-- max() 函数: 求最大值 。 max() 函数用于 获得记录集在某列的最大值。 例如, 为了返回员工最高工资, 可以利用 max() 函数。--例如: select max(求最大值列) max_salary from 表;SQL> select max(eage) max_salary from eusers;MAX_SALARY---------- 26--select distnct e.employee_name, s.salary from t_employees e , t_salary s where e.employee_id=s.employee_id and s.salary=(select max(salary) from t_salary);--为了参照上面的这句验证测试一下, 在创建一个 测试表 ebooks 图书表-- Create tablecreate table EBOOKS( bookno NUMBER not null, eid NUMBER, bookname VARCHAR2(30))tablespace SONGYANJUN pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );-- Create/Recreate primary, unique and foreign key constraints alter table EBOOKS add constraint PK_BOOKNO primary key (BOOKNO) using index tablespace SONGYANJUN pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );--插入 ebooks 表中 数据--insert into ebooks values(1001,8,'javaBook');SQL> insert into ebooks values(1001,8,'javaBook');已创建 1 行。SQL> insert into ebooks values(1002,8,'javaBook');已创建 1 行。SQL> insert into ebooks values(1003,8,'javaBook');已创建 1 行。SQL> insert into ebooks values(1004,6,'c# Book');已创建 1 行。SQL> insert into ebooks values(1005,8,'c# Book');已创建 1 行。SQL> select e.bookname , u.ename from ebooks e, eusers u where e.eid=u.eid and u.eage=(select max(u.eage) from eusers);BOOKNAME ENAME------------------------------ ------------------------------javaBook syj 宋延军javaBook syj 宋延军javaBook syj 宋延军c# Book 军军 宋军BOOKNAME ENAME------------------------------ ------------------------------c# Book syj 宋延军-- min() 函数 : 求最小值 。 min() 函数可以用来获得记录集 在某列上的最小值, 其功能 与 max() 函数相反。--例如: select distinct e.employee_name,s.salary from t_employees e, t_salary s where e.employee_id=s.employee_id and s.salary=(select min(salary) from t_salary);SQL> select e.bookname, u.ename from ebooks e,eusers u where e.eid=u.eid and u.eage=(select min(u.eage) from eusers);BOOKNAME ENAME------------------------------ ------------------------------javaBook syj 宋延军javaBook syj 宋延军javaBook syj 宋延军c# Book 军军 宋军BOOKNAME ENAME------------------------------ ------------------------------c# Book syj 宋延军-- avg() 函数: 求平均值 。 avg() 函数 用于获得记录集 在某列上的平均值。--例如: select e.employee_name, avg(salary) from t_employees e, t_salary s where e.employee_id=s.employee_id group by e.employee_id,e.employee_name;SQL> select avg(eage) from eusers; AVG(EAGE)----------28.1666667SQL> select e.employee_name, avg(salary) from t_employees e, t_salary s where e.employee_id=s.employee_id group by e.employee_id,e.employee_name;EMPLOYEE_NAME AVG(SALARY)-------------------- -----------songyanjun 2000wangpeng 2000liSi 6500zhangSan 7000liangmingy 5000hanjipeng 2000已选择6行。-- sum() 函数 : 求和 。 sum() 函数用于获得 结果集上某列值的和。--例如: select e.employee_name, sum(salary) from t_employees e, t_salary s where e.employee_id=s.employee_id group by e.employee_id,e.employee_name; SQL> select sum(eid) from eusers; SUM(EID)---------- 6021SQL> select e.employee_name, sum(salary) from t_employees e, t_salary s where e.employee_id=s.employee_id group by e.employee_id,e.employee_name;EMPLOYEE_NAME SUM(SALARY)-------------------- -----------songyanjun 2000wangpeng 2000liSi 6500zhangSan 7000liangmingy 5000hanjipeng 2000已选择6行。-- count() 函数: 获得记录数 。 count() 函数 的作用对象 同样为记录集。 --与其他聚合函数不同的是, count() 函数可以有三种方式来进行计数:--count(*) 计算行数、 count(column) 计算某列 、 count(1) 累加1 。 例如:SQL> select * from ebooks; BOOKNO EID BOOKNAME---------- ---------- ------------------------------ 1001 8 javaBook 1002 8 javaBook 1003 8 javaBook 1004 6 c# Book 1005 8 c# BookSQL> select count(*) from ebooks; COUNT(*)---------- 6SQL> select count(eid) from ebooks;COUNT(EID)---------- 5SQL> select count(bookname) from ebooks;COUNT(BOOKNAME)--------------- 5SQL> select count(1) from ebooks; COUNT(1)---------- 6一般来说, 利用 count(1) 进行计数的 速度最快, 但是需要特别注意的是, 预期结果是针对 整行数据, 还是某列的数据。--================================-- Oracle 中的其他函数-- 除了数值函数 、 字符串函数、 日期函数 和 聚合函数 外、 Oracle 还提供了 其他功能性更强的函数。 下面介绍 decode() 、 nvl() 和 cast() 函数 。 decode() 函数 : 多值判断 。 decode() 函数用于 多值判断。 其 执行过程类似于 解码 操作。-- 该函数最常见的应用为, 实现类似 if else 的功能。 -- 例如: 可以利用 decode() 函数 为员工 工资添加 标识, 工资大于 6000 者为最高收入, 其余的为一般收入。-- SQL> select e.employee_id, e.employee_name, decode(sign(avg(s.salary)-6000),1,'高收入','一般收入') incomming from t_employees e, t_salary s where e.employee_id=s.employee_id group by e.employee_id,e.employee_name;EMPLOYEE_ID EMPLOYEE_NAME INCOMMIN----------- -------------------- -------- 103 songyanjun 一般收入 104 wangpeng 一般收入 102 liSi 高收入 101 zhangSan 高收入 106 liangmingy 一般收入 105 hanjipeng 一般收入已选择6行。--回顾一下 sign()函数:-- sign()函数只有一个参数。该函数将返回参数的正负性。若返回值为1,表示该参数大于0;若返回值为-1,表示该参数小于0;若返回值为0,表示该参数等于0。-- select sign(8) result from dual; -- select sign(-8) result from dual; -- select sign(0) result from dual; -- sign()函数为判断两个数值的大小关系提供了方便。因为在oracle中,利用类似if else的结构来判断两个数值之间的大小关系,并不像编程语言中那样方便,而且极易造成代码的复杂化。nvl() 函数 : 为空值重新赋值 。 nvl() 函数 用于处理某列的值。 该函数有两个参数, 第一个参数为要处理的列。 如果其值为空, 则返回第二个参数的值, 否则, 将返回列值。--例如: select employee_id , nvl(employee_name,'未知') employee_name from t_employees;--先插入一条带有空值 的数据到 员工表SQL> insert into t_employees values(107,null,null,null);已创建 1 行。SQL> select employee_id, nvl(employee_name,'未知') employee_name from t_employees;EMPLOYEE_ID EMPLOYEE_NAME----------- -------------------- 107 未知 101 zhangSan 102 liSi 103 songyanjun 104 wangpeng 105 hanjipeng 106 liangmingy已选择7行。-- nvl() 函数更常见的用途为判断 数值是否为空。 因为 sum() 等函数往往会返回 null , 例如: 表示汇率的列一旦为 null , 那么最终的货币结算额度也为 null , 所以, 必须对汇率 进行 nvl() 的处理。 在统计员工工资时,null 同样是不受欢迎的结果, 那么可以利用 nvl() 函数进行处理。--例如: select e.employee_id, nvl(e.employee_name, '未知') employee_name, nvl(sum(s.salary), 0) salary from t_employees e, t_salary s where e.employee_id = s.employee_id(+) group by e.employee_id, e.employee_name;SQL> select e.employee_id, nvl(e.employee_name, '未知') employee_name, nvl(sum(s.salary), 0) salary from t_employees e, t_salary s where e.employee_id = s.employee_id(+) group by e.employee_id, e.employee_name;EMPLOYEE_ID EMPLOYEE_NAME SALARY----------- -------------------- ---------- 103 songyanjun 2000 104 wangpeng 2000 102 liSi 6500 101 zhangSan 7000 106 liangmingy 5000 105 hanjipeng 2000 107 未知 0已选择7行。cast() 函数: 强制转换数据了性 。 cast() 函数 用于强制转换数据类型。 Oracle会根据操作符来自动进行数据类型的转换.-- 例如: select '123' + 200 result from dual;-- Oracle 会根据运算符“+”将 “123” 转换为 数值型 123.-- 例如:select '123' || 200 result from dual;-- Oracle 会根据运算符“||”将数字 200 转换为字符串‘200’。cast() 函数最常用的场景 是转换列的数据类型, 以创建新表。--例如: --create table tmp_salary as select cast(salary_id as varchar2(20)) salary_id, cast(employee_id as varchar2(20)) employee_id, cast(month as varchar2(20)) month, cast(salary as varchar2(20)) salary from t_salary; SQL> create table tmp_salary as select cast(salary_id as varchar2(20)) salary_id, cast(employee_id as varchar2(20)) employee_id, cast(month as varchar2(20)) month, cast(salary as varchar2(20)) salary from t_salary ;表已创建。SQL> set linesize 120;SQL> select * from tmp_salary;SALARY_ID EMPLOYEE_ID MONTH SALARY-------------------- -------------------- -------------------- --------------------1 101 3 70002 102 3 65003 103 3 20004 104 3 20005 105 3 20006 106 3 5000已选择6行。SQL>--==============================================-- Oracle 中的运算表达式-- Oracle 中的常用运算 包括: 数学运算、 逻辑元算 和 按位 运算。 数学运算: 数学运算 是最常用的运算方式, Oracle 中的数学运算包括: +、-、*、/, 分别代表了 加、减、乘、除 运算。 在使用数学运算是, Oracle会自动将其他数据类型转换为 数值型, 然后在参与运算。--例如:select 5+3 result from dual;select 5-3 result from dual;select 5*3 result from dual;select 5/3 result from dual;--需要注意的是, 任何一种运算符 与 null 的运算 结果 均为 null . --例如:select 5+null result from dual;select 5-null result from dual;select 5*null result from dual;select 5/null result from dual;--======================================================逻辑运算Oracle 中的逻辑运算包括:> :大于运算, 可用数值型、 日期型 和字符串类型;>= :大于等于运算, 可以用于 数值型、日期型 和字符串型;< :小于运算, 可用于数值型、日期型 和 字符串类型;<= :小于等于运算, 可用于数值型、日期型 和 字符串类型;= :等于, 可用于数值型、日期型 和 字符串类型;<> :不等于, 可用于数值型、日期型 和 字符串类型;!= :与<>用法相同;NOT:取反操作;AND:布尔值的操作;OR :布尔值的‘或’操作。--需要注意的是, Oracle中的逻辑运算符只能座位条件判断, 并不返回值。 为了查询 工资 在 5000-7000 之间的记录, 可以利用个逻辑运算符来组合查询条件。--例如: select * from t_salary where salary>=5000 and salary<=7000;SQL> select * from t_salary where salary>=5000 and salary<=7000; SALARY_ID EMPLOYEE_ID SALARY MONTH---------- ----------- ---------- ---------- 1 101 7000 3 2 102 6500 3 6 106 5000 3对于 null 值, 需要特别注意的是, 无论使用那种运算符, 结果都会返回 null。 当比较的结果为 null , 并作出条件出现时, Oracle都将其解释为 false 。select 1 result from dual where 1=null;select 1 result from dual where 1<>null;select 1 result from dual where null=null;select 1 result from dual where null<>null;--============================================ 位运算 从 Oracle8i开始, 系统已经提供了 位运算符。 最常用的莫过于 bitand 运算符。 --例如: select bitand(192,100) result from dual;SQL> select bitand(192,100) from dual;BITAND(192,100)--------------- 64--==========================================Oracle 中的特殊判式-- 除了逻辑运算之外, Oracle 提供了一些特殊判式。 这些判式可以用来生成更加复杂和灵活的查询条件。 between : 取值范围。in : 集合成员测试。like : 模式匹配。is null : 控制判断。all,some,any : 数量判断。exists : 存在性判断。-- between 判式: 测试范围 。 between 判式 , 用于判断某个值是否在两个至之间。 这些值可以为数值型、 字符串和日期型。 --例如: 使用 between 判式来获得ID号 在 101~105之间 的员工信息。 select * from t_employees where employee_id between 101 and 105;SQL> select * from t_employees where employee_id between 101 and 105;EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SEX EMPLOYEE_SALARY----------- -------------------- -------------------- --------------- 101 zhangSan 男 6000 102 liSi 男 5500 103 songyanjun 男 2000 104 wangpeng 男 2000 105 hanjipeng 男 2000between 判式同样可以应用与字符串和日期型。 字符串按照字母表的顺序进行比较, 而日期型是按照日期的先后顺序进行比较。--例如: --select * from t_employees where 'b' between 'b' and 'c';--select * from t_employees where 'b' between 'bc' and 'c';注意与说明: between 判式与 >= 、 <= 的组合是等价关系。 但是 效率上要 比 后者 差。-- in 判式: 集合成员测试 。 in 用于判断某个值 是否是一个集合的成员。--例如: select * from t_employees where statusSQL> select * from t_employees where status in('NEW','ACT');EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SEX EMPLOYEE_SALARY STATUS----------- -------------------- -------------------- --------------- ---------- 107 NEW 101 zhangSan 男 6000 ACT 104 wangpeng 男 2000 ACT 105 hanjipeng 男 2000 ACT 106 liangmingy 女 2000 NEW--值得注意的是: in 判式中的 集合的成员的数据类型可以 不一致。--例如, select * from t_employees were status in ('NEW','ACT',sysdate,1) 中的数据类型包含了字符串、日期类型和数值型。-- like 判式 : 模式匹配 。 like 判式的最大特点在于, 可以使用通配符。其 通常的应用场景为 处理模糊查询。--例如: select * from t_employees where employee_name like '%jun%'SQL> select * from t_employees where employee_name like '%n%';EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SEX EMPLOYEE_SALARY STATUS----------- -------------------- -------------------- --------------- ---------- 101 zhangSan 男 6000 ACT 103 songyanjun 男 2000 EN 104 wangpeng 男 2000 ACT 105 hanjipeng 男 2000 ACT 106 liangmingy 女 2000 NEWSQL> select * from t_employees where employee_name like '%jun%';EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SEX EMPLOYEE_SALARY STATUS----------- -------------------- -------------------- --------------- ---------- 103 songyanjun 男 2000 EN--如果要求字符串中含有原义字符“%”, 例如, 含有百分比的字符串。 那么 , like 判式应写作 'jun%\' escape '\' 。 Oracle会首先解释 escape 关键字, 并将其后的字符“\” 解释为转义字符。 那么在 “jun\%” 的 “%” 不再表示通配符, 而是表示原义字符 “%” 。“_”(下划线)可以用于 like 判式的另一个 通配符, 该通配符 表示一个任意的 字符。-- is null 判式: 空值判断 。 在逻辑判断中, 对于列值为空的判断, 不能使用 = 或者 <> 。oracle 对与 空值的判断提供了专门的判式 : is null 。--例如: 为了获取表 t_employees 中的员工信息不全的记录, 可以利用如下 查询语句。-- select * from employees where employee_id is null or employee_name is null or work_years is null or status is null;SQL> select * from t_employees where employee_id is null or employee_name is null or status is null;EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SEX EMPLOYEE_SALARY STATUS----------- -------------------- -------------------- --------------- ---------- 107 NEW-- exists 判式 : 存在性 判断 。 in 判式用于 判断表的列值是否 存在与 列表(集合)中。 而 exists 判式则可用于 判断查询结果集合 是否为空。 --例如: 为了查询出表 t_employees 所存储的员工信息中, 那些员工存在于工作表中 , 既可以 利用 exists 判式。--select * from t_employees e where exists(select * from t_salary where employee_id=e.employee_id);SQL> select * from t_employees e where exists(select * from t_salary where employee_id=e.employee_id);EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SEX EMPLOYEE_SALARY STATUS WORK_YEARS----------- --------------- --------------- --------------- ---------- ---------- 101 zhangSan 男 6000 ACT 3 102 liSi 男 5500 CNN 2 103 songyanjun 男 2000 EN 1 104 wangpeng 男 2000 ACT 1 105 hanjipeng 男 2000 ACT 1 106 liangmingy 女 2000 NEW 1已选择6行。-- all, some, any 判式:数量判断 。 all, some 和 any 判式的作用对象 为记录集合。 all 表示,记录中的所有记录;some 表示其中的一些记录;any 判式则表示其中的任意记录。 --例如: 在员工工资表 t_salary 中 , 为了查找 高于 id 为 104 和 105 的工资信息, 即可使用 all 判式。--select * from t_salary where employee_id = 104 or employee_id = 105;SQL> select * from t_salary where employee_id=104 or employee_id = 105; SALARY_ID EMPLOYEE_ID SALARY MONTH---------- ----------- ---------- ---------- 4 104 2000 3 5 105 2000 3--使用 all 判式:SQL> select * from t_salary where salary >all(select distinct salary from t_salary where employee_id=104 or employee_id=105); SALARY_ID EMPLOYEE_ID SALARY MONTH---------- ----------- ---------- ---------- 1 101 7000 3 2 102 6500 3 6 106 5000 3--distinct 说明: oracle distinct语句的用法 :创建视图时过滤数据 distinct的只显示一次重复出更的值。 不管这个值出现多少次只显示一次。 SQL> select distinct salary from t_salary; SALARY---------- 5000 6500 2000 7000-- 使用 some 判式:-- 例如:select * from t_salary where salary > some(select distinct salary from t_salary where employee_id=104 or employee_id=105)SQL> select * from t_salary where salary > some(select distinct salary from t_salary where employee_id=104 or employee_id=105); SALARY_ID EMPLOYEE_ID SALARY MONTH---------- ----------- ---------- ---------- 1 101 7000 3 2 102 6500 3 6 106 5000 3-- 此时的 some 判式实际相当于 逻辑运算中的 or 运算, 即 salary>6000 or salary>7000 。 此时, 使用 any 判式, 将返回同样的结果。--======================================================Oracle 高级函数 : 分析函数与窗口函数-- Oralce 中的分析函数具有非常强大的功能。 --分析函数:往往与 另一类函数(窗口函数)同时使用。--窗口函数:总是为查询过程中的当前记录提供一个相关的记录集, 而且随着当i安记录的推移, 相应的记录集也会随之改变, 这非常类似于“滑动窗”的概念。 分析函数的操作对象即为“滑动窗”所指定呃记录集合。排名分析函数中的 排名函数 可以真对 窗口中的记录 生成排序序号。 常用的 排名函数 有 rank() 、 dense_rank() 和 row_number() 。rank() 函数用于 返回当前记录在窗口函数所指定的记录集中的排名。 rank() 函数在排名过程中,具有跳跃的特点。--例如: select * from students;SQL> select * from t_employees;EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_SEX EMPLOYEE_SALARY STATUS WORK_YEARS----------- --------------- --------------- --------------- ---------- ---------- 107 NEW 1 101 zhangSan 男 6000 ACT 3 102 liSi 男 5500 CNN 2 103 songyanjun 男 2000 EN 1 104 wangpeng 男 2000 ACT 1 105 hanjipeng 男 2000 ACT 1 106 liangmingy 女 2000 NEW 1 108 wangErMaZi 男 123 jjj 3已选择8行。-- rank()--例如:select student_name, rank() over(order by student_age) position from students;SQL> select employee_id, employee_name, rank() over(order by work_years) position from t_employees;EMPLOYEE_ID EMPLOYEE_NAME POSITION----------- --------------- ---------- 107 1 103 songyanjun 1 106 liangmingy 1 105 hanjipeng 1 104 wangpeng 1 102 liSi 6 108 wangErMaZi 7 101 zhangSan 7已选择8行。-- dense_rank()--例如:select employee_id, employee_name, dense_rank() over(order by employee_salary) position from t_employees;SQL> select employee_id, employee_name, dense_rank() over(order by employee_salary) position from t_employees;EMPLOYEE_ID EMPLOYEE_NAME POSITION----------- --------------- ---------- 108 wangErMaZi 1 103 songyanjun 2 106 liangmingy 2 105 hanjipeng 2 104 wangpeng 2 102 liSi 3 101 zhangSan 4 107 5已选择8行。-- row_number()--例如: select student_name, row_number() over(order by student_age) position from students; SQL> select employee_id, employee_name, row_number() over(order by employee_id) position from t_employees;EMPLOYEE_ID EMPLOYEE_NAME POSITION----------- --------------- ---------- 101 zhangSan 1 102 liSi 2 103 songyanjun 3 104 wangpeng 4 105 hanjipeng 5 106 liangmingy 6 107 7 108 wangErMaZi 8已选择8行。--=======================================
Over!!!
- Oralce 函数表达式 的应用
- oralce的单行函数
- oralce 临时表的应用
- Oralce trunc()函数的使用
- Oralce常用的系统函数
- Oralce-函数
- Oralce 函数
- oralce与SQL的函数使用区别
- oralce的一些特殊函数语句
- oralce的to_date()和to_char()函数使用
- Oralce常用的110个函数
- Oracle:regexp_replace正则表达式函数的应用
- oralce 正则表达式
- Oralce 常用正表达式
- 【oracle资料整理】--【5】ORALCE的函数之单行函数
- oralce 10g 的表空间传输特性的应用
- 应用ZcxOracle实现Zope和Oralce数据库的连接
- oralce汇总函数备忘
- 屏蔽JS错误和右键
- 学习
- C++中将科学计数转换为其他类型
- 经典JavaScript特效
- Intent
- Oralce 函数表达式 的应用
- poj 1157 LITTLE SHOP OF FLOWERS
- WAMP转移至LNMP平台遇到的几个问题及解决方法
- Struts2+Spring集成中Action的管理
- login/getutline.c:25: error: storage class specified for parameter 'buffer'
- 搭建Android开发环境
- 求程序运行所用的时间
- 山洞
- strut2的配置文件修改默认路径问题