Oracle培训笔记20060408

比较运算符补充
对空值进行比较NULL = <> > <
select * from emp where comm < NULL;
这种比较不能选出列来,要想比较空值需要使用下面的比较运算符
IS NULL
IS NOT NULL
select * from emp where comm is null;
is null 或 is not null只是一个运算符.
oracle不支持布尔型的数据
如 不能使用
select * from emp where true;
而需要使用
select * from emp where 1=1; 

(3)函数 function

 比如取余运算等等,SQL语句并不能解决
 而需要借助外部语言,问题是有时外部语言也不能够解决

只能借助于函数.
select * from emp
  where MOD(sal,10)=2;
函数分为两类
一是预定义函数,内建函数
二是自定义函数(PL/SQL,JAVA)

单行函数,输入一行,得到一个结果
多行函数,输入多行,得到一个结果

字符串函数
lower,upper,initcap
lower(s1):字母变成小写
upper(s1):字母变成大写
initcap(s1):首字母变成大写
 用于转化大小写
 用于忽略大小写
select  upper(ename),lower(ename),initcap(ename) from emp;
如果表中又两列人的 姓名又大小写的区别,但是是同名的,例如
SMITH和smith,smiTh和smITH
select * from emp where upper(ename)='SMITH';

substr(s1,ni,n2):返回s1中从n1位开始长度为n2的子串
其中n1可以为负数,表示倒数ABS(n1)位
s2有默认值,表示从s1开始到字符串结束
练习:查询每个人名字的第5,6,7三个字符
  select substr(ename,5,3) from emp;
练习:查询每个人名字的后三个字符
 select substr(ename,-3,3) from emp;
 select substr(ename,-3) from emp;
练习:查询每个人名字后面数的第三位和第二位
 select substr(ename,-3,2) from emp;
instr(s1,s2,n1,n2):返回在s1中从n1位之后第n2次出现子串s2的位置,其中n1,n2默认是1
练习:查询每个人名字中第一次出现字母A的位置
 select ename,instr(ename,'A',1,1) from emp;
练习:查询每个人名字中的二次出现L的位置
 select ename,instr(ename,'L',1,2) from emp;
 不可以写成select ename,instr(ename,'L',2) from emp;

length(s1):返回s1的字符个数.
length('你好'); 2
多字节构成的也算是一个字符.
length('smith你好');7
但是不能够查询字节数
可以使用
lengthd(s1):返回字符串的字节数.
下面的两个函数也是指字节.
substrb()
instrb()

 lpad(s1,n1,s2):将s1以n1位表示,不够的用s2填充
select lpad(ename,10,'*') from emp;
 rpad(s1,n1,s2):从右侧
练习:将每个人的 名字,用字符串'-+/' 从右侧填充到十位长度
 select rpad(ename,10,'-+/') from emp;
如果使用字符串填充,那么采用截取法
比如smith填充成10位用'-+/',则显示为smith-+/-=,其余的一位不填充

数字函数

round(n1,n2):返回n1被四舍五入到n2为的数字
 n2>0:表示小数点后的n2位
 n2=0:表示个位,默认值
 n2<0:表示个位前的ABS(N2)位

trunc(n1,[n2]):返回n1被截断n2位的值,默认是个位.
MOD(n1,n2):返回n1被n2除所得的余数

ABS(n1):取绝对值
sign(n1):返回n1的符号
n1>0:返回1
n1<0:返回-1
n1=0:返回0
特殊的表:DUAL表,只返回一行
比如:select 1+1 from dual;
目的用于不需要引用任何表任何列的值时使用.
日期函数
(1)日期表达式
 d1-d2:返回两者相差的天数
 d1+n1:日期d1经过n1天之后的日期
sysdate:返回当前服务器的时间,没有参数,在ORACLE中不需要写这个函数的空括弧
  select sysdate + 100 from dual;
d1-d2:返回两个日期之间相差的天数

练习一:查询每个职工工作至今累计多少天
 select sysdate-hiredate from emp;
返回的时间:9243.62373,因为工作的时间与现在的时间不时同一个小时,分,秒,即分,秒,小时都要换算成为小数

世纪-年-月-日-小时-分-秒之间的进制很复杂(60,60,24,28,29,30,31,12,100)
interval '123-2' year(3) to month
interval '123' year(3)
interval '300' month
year(3)默认为2
经过10年5个月后的时间:
select sysdate+interval '10-5' year to month
interval '10-5' year to month:是一个值,不是表达式也不是函数

练习:经过100个月后的日期
  select sysdate+interval '100' month(3) from dual;
练习:经过15年10个月后的日期
  select sysdate+interval '15-10' year to month from dual;
练习:经过100年零15天后的日期
  select sysdate+interval '100' year(3)+interval '15' day from dual;
注意:year,month,day默认的长度都是2位,如果要加的时间大于2位,则应使用括弧

select months_between(d1,d2):返回d1和d2之间相差的月数.
 d1是当前时间,d2是以前的时间
练习:查询每个职工工作至今累计工作了多少整年?
 select trunc(MOD(months_between(hiredate,sysdate),12)) from emp;
给列起别名:
 SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)
    FROM EMP;

add_months(d1,n1):返回d1加n1个月后的日期.
 与interval的区别:interval是直接量,而函数是变量
next_day(d1,'fmt'):返回d1之后,下一个星期几(fmt)的日期
 fmt根据客户端环境改变(monday,星期一,....)
练习:查询下一星期六的日期
select next_day(sysdate,'星期六') from dual;

last_day(d1):返回d1所在月的最后的一天

round(d1[,'fmt'])
'cc','yy','mm','dd','hh24','mi','ss'
其中DD是默认的.
select sysdate,round(sysdate,'DD') from dual;
进位的制度:.....

trunc(d1,'fmt'):只舍不入

 练习:查询那些帐户是前天创建的
all_users上有系统内所有帐户的名字(username),创建时间(created)
select username from all_users where trunc(created,'DD')=trunc(sysdate+interval '-2' day,'DD');

 练习:查询每个职工工作至今累计多少整年零多少整月零多少日?
SELECT HIREDATE,SYSDATE,
TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) 整年,
MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12) 整月,
SYSDATE -
  ADD_MONTHS(HIREDATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE))//工作日+相隔的整月) 零日
 FROM EMP;

类型转化函数
数字,字符串和日期

(1)自动转换
 数字与字符串之间的自动转换
 数字-->字符串:总是可以自动转换
select 123 || 'ABC' from dual;
123ABC
select substr(1234567,3,2) from dual;
34
 字符串--->数字:字符串是十进制数字字符构成的字符序列.(可以有一个小数点)
select '123'+'234' from dual;
357

 字符串和日期之间的自动转换
 日期--->字符串:总是可以自由转换,转换结果根据NLS_DATE_FORMAT参数而定.
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
 字符串-->日期:字符串必须符合当前会话的当前NLS_DATE_FORMAT参数指定的格式.
select last_day('08-4月-06') from dual;

查询1949年10月1日 距今多少个月.
alter session set nls_date_format='YYYY-MM-DD';
select trunc(months_between(sysdate,'1949-10-01')) from dual;

(2)强制转换
 数字与字符串之间
 数字--->字符串:在自动转换的基础上,添加数字的位等等.
TO_CHAR(n1,'fmt'):
 fmt可以是:9 0 , . $ , L
9不添加,0添加,L指的是本地系统的货币符号
 字符串到数字:必须是十进制构构成的字符序列
TO_NUMBER(s1,'fmt'):
 fmt可以是S,等
select to_number('$123','$999')+10 from dual;
 字符串与日期之间的函数转换:
 日期转成字符串:
to_char(d1,'fmt'):fmt与数字中的不一样,'cc' 'bc' 'yyyy' 'yy' 'year' 'mm' 'mon' 'month' 'month' 'DD' 'DDD' 'D' 'DAY' 'HH' 'HH24' 'MI' 'SS'  'AM' 'PM' '-' '/' ':' 'space'如果是英文单词或者汉字等,需要加双引号"string"
 练习:查询当前时间要求用yyyy年mm月dd日hh24的格式显示
 select to_char(sysdate,'YYYY"年"-MM"月"-DD"日" HH24"时"') from dual;
 练习:查询当前时间是星期几.
select to_char(sysdate,'day') from dual;
 练习:查询当前时间是今年的第几天?
select to_char(sysdate,'DDD') from dual;
 
 字符串转换成日期:
TO_DATE(s1,'fmt')
 练习:将19990102转换成日期;
 select to_date('19990102','YYYY-MM-DD') from dual;
 练习:查询今年国庆节是星期几?
 select to_char(to_date('20061001','YYYY-MM-DD'),'DAY') from dual;
 练习:查询今年有多少天?
select to_char(to_date('2006-12-31','YYYY-MM-DD'),'DDD') from dual;