oracle学习手记（三）
2014年09月16日
⁄ 综合
⁄ 共 3139字 ⁄ 字号
小 中 大
1：字符函数分为转换函数和字符操作函数
转换函数有：LOWER,UPPER,INITCAP(首字母大写)
字符操作函数：CONCAT,SUBSTR,LENGTH,INSTR(某个字符串在此字符串中的位置),
IPAD（字符串按某种格式显示）;
例如：
select initcap(ename) from emp; //返回所有所有的人名，并且将名字首字母大写。
select concat(ename,job) from emp; //返回一列，此列是由ename和job组成的。
select initcap(substr(ename,1,3)) from emp; //返回一列，此列是某列的字串。
Select length(‘我爱你’) from dual; //返回3，字母和汉字都是按两个字节来存储的。
select lpad(ename,10,'*') from emp; //返回名字，如果不足10个，用*补全。
2：在oracle内部存储都是以大写存储的。
例如：
select * from emp where ename='king'; //查找不出结果
select * from emp where ename=upper('king'); //能查找出符合条件的结果。
3：oracle 中的dual表
Oracle中的Dual表比较特殊，是一个系统表，只有一个Dummy Varchar2(1)字段，而且Oracle会尽量保证它只返回一条记录。在查询Oracle中的sysdate或sequence.currval等系统值时需要在Select 语句中写Dual。如：select sysdate from dual.用Dual表来查询一些没有具体用户表的数据。
其实在每个表中都有一个隐藏的rowid，rownum（除了dual，其他表都有） 。
dual不仅可以插入还可以删除（最好不要删除该表，可能会引起数据库无法启动。如果误删也有解决办法，将参数replication_dependency_tracking 设置成 FALSE就可以了）。
dual它应该是系统内存中的一个虚拟的表，而系统中的dual表只是为了维护数据字典和系统对dual的操作权限。在看看下面的实验，向Dual表中随便插入几条记录然后查询：
SQL> select * from dual;
D
-
X
SQL> select rowid ,dummy from dual;
ROWID D
------------------ -
AAAM1CAABAAAAgiAAA X
SQL> select rowid ,dummy from dual order by dummy;
ROWID D
------------------ -
AAAM1CAABAAAAgiAAA X
AAAM1CAABAAAAgiAAB Y
AAAM1CAABAAAAgiAAC Y
AAAM1CAABAAAAgiAAD Z
4：数值（number）函数
Round（x，m） //返回四舍五入值。原值为x，精度为m。m如果为负值或0
0表示小数点原位数，如果为负数表示小数点位数左移相应位数。
TRUNC（x，m） //截取小数
MOD（x，y） //取模运算。
例如：
select round(45.946,2) ,round(45.946,0),round(45.946,-1) from dual;
结果：
ROUND(45.946,2) ROUND(45.946,0) ROUND(45.946,-1)
--------------- --------------- ----------------
45.95 46 50
例子：
select trunc(45.946,2),trunc(45.34),trunc(45.946,-1) from dual;
结果:
TRUNC(45.946,2) TRUNC(45.34) TRUNC(45.946,-1)
--------------- ------------ ----------------
45.94 45 40
5：使用日期型函数
ORACLE内部的存储格式是：世纪，年，月，日，时，分，秒。
默认的日期格式:日，月，年。DD-MON-YYYY.
Sysdate能够返回当前服务器的系统时间。
例子：
select ename,(sysdate-hiredate)/7 weeks from emp; //返回如公司多少周了。
用desc 表名 //返回表的结构。
对于round（date）用于日期的时候，用于上下月的统计。
select months_between(sysdate,hiredate) from emp; //返回员工入公司多少个月了，
如果想再求年，可以除12.并加上trunc截取。
6：转换函数（不同的数据类型之间的转换）
日期格式中的模式字符
YYYY=======代表完整的年份
YEAR=======年份
MM=========两位数月份
MONTH======月份的完整表示
DY==========每个星期中天的三位表示
DAY==========天的完整表示
例子：
select ename,to_char(hiredate,'yyyy-mm-dd') from emp;
结果：
ENAME TO_CHAR(HI
---------- ----------
SMITH 1980-12-17
ALLEN 1981-02-20
将字符串转换为日期型通常用于插入型。
insert into emp(empno,ename,hiredate) values(9005,'wang',to_date('07-08-07','yy-mm-dd'));
对于字符型和数值型的运算，最好将两者转换为相同的类型。
7：NVL函数：将一个控制转换为一个实际的值。（日期，字符，数值）。
NVL(COMM,0) //如果comm为空，就用0来代替。
NVL（hiredate，‘01-jan-97） //如果hiredate为空，则用后面的值代替。
用此可以解决一个问题：对于一个函数表达式，如果其中有一个为null，则结果为空。
但是如果用了nvl函数，可以解决这个问题。
select sal + nvl(comm,0) *2 from emp;//这样每一行都有数据
而select sal + comm * 2 salary from emp;//会有很多行为空
8: DECODE函数：类似于switch的函数。
9：sqlplus常见的命令
Save：在使用了某语句后，需要保存此命令在某个地方可以使用
Save ‘F:ABC.TXT
get 'd:abc.txt'; //加载保存在盘中的文件，并不执行。
Clear screen 清屏
@ ‘d:abc.txt’ //不仅加载文件内容，并且执行其。
Conn 帐号名/密码 //切换帐户
Run，r ,/ 都表示执行。
Exit/quit //推出sqlplus环境。
Conn 帐户名/密码 @sid；//链接到远程服务器上。使用前必须配置NET CONFIG ASSISTANCE.
其他用户访问其他人帐户，必须获得授权。否则看不到数据。