这几天 预习了一遍 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 JONES
SQL>
--不使用 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_Tiger
SQL> select initcap('big/big/tiger') from dual;
-----------
Big/Big/Tiger
SQL> 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)
-------------
5
SQL>
-- substr() 函数： 截取字符串 。 substr() 函数用户截取字符串。 该函数可以指定 截取的起始位置， 截取长度， 可以实现灵活的截取操作， 因此， 称为字符串操作中最为常用的 函数之一。
--例如： 对于 字符串“1234567890”， 现在欲截取 自 第5位开始的 4个字符。 select substr('1234567890',5,4) from dual;
SQL> select substr('1234567890',5,4) from dual;
SUBS
----
5678
SQL>
-- 需要注意的是 Oracle 中的 字符位置 是从 1 开始， 而不是向某些编程语言 （如Java）那样从 0 开始。
-- 如果 不指定长度， 那么 substr() 函数 将获取 起始位置参数 字符串结尾处 的所有字符。
-- select substr('1234567890',5) from dual;
SQL> select substr('1234567890',5) from dual;
SUBSTR
------
567890
SQL>
-- 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)
----------------------------
5
SQL>
-- 还可以指定出现次数参数， 以指定是 第几次搜索到的子字符串。
--例如： 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
---
abc
SQL>
-- 需要注意的是， 空白符 不仅仅包括了 空格符， 还包括 TAB键、 回车符 和 换行符。
-- rtrim() 函数： 删除字符串 尾部空格 。 rtrim() 中 的 r 代表 right 。 该函数用于删除字符串右端空白符 。 删除字符串 首尾空白符可以结合 使用 ltrim() 和 rtrim() 函数。
-- select rtrim(ltrim( ' abc ')) from dual;
SQL> select rtrim(ltrim(' abc ')) from dual;
RTR
---
abc
SQL>
-- trim() 函数 ： 删除字符串首尾空格 。 trim() 函数可以用于删除首尾空格， 相当于 ltirm() 和 rtrim() 的组合。
-- select trim(' abc ') from dual;
SQL> select trim(' abc ') from dual;
TRI
---
abc
SQL>
-- to_char() 函数 ： 将其他类型转换为字符类型，这些类型主要包括数值型、 日期型。
1. 将数值型转为 字符串
--select to_char(120,'99999') result from dual;
SQL> select to_char(120,'99999') result from dual;
RESULT
------
120
SQL>
--select to_char(0.96,'9.99') result from dual;
SQL> select to_char(0.96,'9.99') result from dual;
RESUL
-----
.96
SQL>
--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.098
SQL>
--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.098
SQL>
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-05
SQL>
--select to_char(sysdate,'YYYY-MON-DD') from dual;
SQL> select to_char(sysdate,'YYYY-MON-DD') from dual;
TO_CHAR(SYSDATE,
----------------
2012-3月 -05
SQL>
--创建 一个 名为 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 table
create 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:00
06-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 table
create 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.1666667
SQL> 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 2000
wangpeng 2000
liSi 6500
zhangSan 7000
liangmingy 5000
hanjipeng 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)
----------
6021
SQL> 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 2000
wangpeng 2000
liSi 6500
zhangSan 7000
liangmingy 5000
hanjipeng 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# Book
SQL> select count(*) from ebooks;
COUNT(*)
----------
6
SQL> select count(eid) from ebooks;
COUNT(EID)
----------
5
SQL> select count(bookname) from ebooks;
COUNT(BOOKNAME)
---------------
5
SQL> 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 7000
2 102 3 6500
3 103 3 2000
4 104 3 2000
5 105 3 2000
6 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 男 2000
between 判式同样可以应用与字符串和日期型。 字符串按照字母表的顺序进行比较， 而日期型是按照日期的先后顺序进行比较。
--例如：
--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 status
SQL> 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 NEW
SQL> 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！！！
568
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
