三.Oracle常用数据类型及单行函数总结
1.课外扩展知识
UTF-8 一个中文占3个字节,GBK 一个中文占2个字节
ctrl+E:可以找到sql查询语句的历史记录(在sql-wiwdow操作)
在doc里输desc 表名可以查出该表所有列的类型.(例:desc emp)
也可以在pl/sql工具里点表名点edit也可以查出该表所有列的类型.
2.Oracle常用数据类型:
在Oracle里,数字建议用number,字符用varchar2,,时间用Date;
字符串类型:用单引号引起来的字符序列
CHAR(length)：固定长度字符串，不足自动以空格补齐长度，最多2000个字节。
例：CHAR(10) 代表只能输10个字节,5个中文(一个中文占2个字节),如果输入的字符只占8个字节,它会自动补空格补齐到10个字节
查出来的length长度永远是10.
VARCHAR2(length)：可变长度字符串，最多4000个字节。
例：VARCHAR2(10) 代表只能输10个字节,5个中文(一个中文占2个字节),输入字符的字节占多少字节,它的length长度就是多少
数值类型：
NUMBER[(precision, scale)]：数值型，可以存储整数、浮点数。最高精度38位。如果没有指定最大位数和精度，就存储38位精度的数字。
NUMBER(24) 最多24位,而且不能有小数;
NUMBER(24,4) 最多24位,其中小数最多4位,整数最多20位(24-4)
日期类型
DATE：存储日期和时间，精确到秒.
默认存放格式：“DD-MON-YYYY”
默认显示格式：“DD-MON-YY"
TIMESTAMP[(seconds_precision)]：存储日期、时间和时区信息，带小数位的秒。时间戳
如：TIMESTAMP(3) 秒后面小数点为3位。（最多可9位）
java时间格式:yyyy-MM-dd hh:mm:ss
oracle时间格式:yyyy-mm-dd hh24:mi:ss
sql语句:
select length(列头) from emp;代表查询该列头下每列占多少字节.
3.伪列:
Oracle为数据中的表都提供有伪列。伪列就像表的一个列，但是它并没有存储在表中。
伪列可以从表中查询，但不能插入、更新和删除它们的值.
常用的伪列有ROWID和ROWNUM
ROWID 是表中每一条记录的唯一标识符，数据库内部使用它来存储行的物理地址。
该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的某一行。
ROWNUM 是SQL查询返回的结果集中每一行的行号.
可以用它来限制查询返回的行数。
ROWNUM是先查到结果集之后再加上去的一个列.
ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。
它取得第一条记录则ROWNUM值为1，第二条为2，依次类推。
rownum>1永远不成立(当满足条件了才加1)
因为从缓冲区或数据文件中得到的第一条记录的ROWNUM为1，1>1不符合WHERE条件，则被删除；接着取下条，但它的ROWNUM还是1，又被删除，依次类推，便没有了数据
4.单行函数:
1.函数概念;
1.1:具有某种功能的程序块，通常包括输入端和输出端;
2.函数分类;
2.1：单行函数：接受一个数据，输出一个数据;
2.2：多行函数：聚合函数，分组函数。接收一组数据，输出一个数据;
3.常见字符函数;
3.1:大小写相关 lower(将字符转换为小写),upper(将字符转换为大写),initcap(将字符的首字母单词大写);
4.常见数值函数;
4.1：ROUND:(四舍五入);select round(10.55) from dual;
4.2：TRUNC:(去尾);select trunc(10.000) from dual;
4.3：MOD:(去余);select mod(10,3) from dual
单行函数对于从表中查询到的每一行返回一个值(一行一行的执行,每行返回一个值,每一行都会被过滤)
单行函数可以大致划分为：
字符函数：接收字符串输入并返回字符串或数值(常用方法参见课件day4)
例:nvl函数跟nvl2函数
select nvl('bcd','abc')from dual;表示第一个参数不为null取第一个,否则第二个
select nvl2('adgg','bcd','abc')from dual;表示第一个参数不为null取第二个,否则第三个
数字函数：接收数值输入并返回数值(常用方法参见课件day4)
注意round函数四舍五入就可以了
日期函数：对日期进行操作(常用方法参见课件day4)
例:取当前时间+10天(默认加天)
select sysdate+10 from dual;
转换函数：从一种数据类型转换成另一种数据类型(常用方法参见课件day4)
重要:需重点操作
例:日期转字符串
select to_char(sysdate,'yyyy-mm-dd')from dual;
字符转number(9代表要转换成的格式)
select to_number('$12.3477','$99.9999')from dual;
number转字符串
select to_char(123666,'999,999')from dual;
DECODE函数(oracle特有,在Oracle9i以上版本建议使用CASE表达式来替代):
例:
select decode('x','a','b','c','d','e','f',6)from dual;
表示:如果有个参数跟第一个参数匹配,就取这个参数后面的参数,
如果没有一个参数跟第一个参数匹配,就取最后落单的那个参数
用case when then 替代:
select case 'b'
when 'a' then 'b'
when 'b' then 'c'
else '6'
end from dual;
case when then 里还可以接条件表达式:
select sal,case
when sal between 1 and 1000 then '底薪'
when sal between 1001 and 3000 then '工薪'
else '高薪' and
from emp;
正则表达式函数(常用方法参见课件day4)
regexp_like()函数
^开头,$结尾
5.单行函数练习：
--别名 t.*代表t表中所有的列 select t.ename from emp t --所有列查出并查出它的伪列 行号 select t.*,t.rowid,rownum from emp t --查出表中前5名员工的信息 select * from emp where rownum<=5 --查出表中10后面的员工 (select t.*,rownum rn from emp t)先算出总行数 select * from(select t.*,rownum rn from emp t)where rn>10 --(字符函数) --返回B的ascii码表相当于B在ascii码表中用什么数字表示（int char 互换） dual(哑表)当操作为常量时可以用 --ascii(x) select ascii('B') from dual --chr(x) select chr(100) from dual --initcap(驼峰法就是每个单词首字母大写) select initcap('user')||initcap('name') from dual --lower(转换小写) select lower(ename) from emp --upper(转换大写) select upper('feng') from dual --replace()(替换) select replace(job,'SALESMAN','经理') from emp --instr() --返回A在job职位中是第几个坐标，从1开始。找不到A返回0 select job, instr(job,'A') from emp --反向找 select job, instr(job,'A'，-1) from emp --substr(截取字符串)从第三个位置开始(包括第三个) select substr(ename,3)from emp --concat(x,y)连接字符串只能连接两个 select concat('a','b') from dual --trim()去空白 select trim(' aa bb ') from dual --去左空格 select ltrim(' aa bb ')from dual --去右空格 select rtrim(' aa bb ')from dual --nvl(x,value)如果x为null,返回value,否则返回x select nvl('','bb') from dual select nvl('aa','bb') from dual select nvl(comm,0)from emp --nvl2(x,value1,value2)(如果x不为null,执行value1,否则执行value2,相当于if,else) select nvl2(comm,comm,0) from emp -- 查询出所有员工的姓名，首字母大写，其它字母小写。 select initcap(ename) from emp -- 查询出所有员工的姓名，如果姓名中有”S”，全部替换成“8”。 select replace(ename,'S','8') from emp -- 查询出姓名中有两个“L”的员工信息。<>0表示不等于0 select * from emp where ename like '%L%L%' select * from emp where instr(ename,'L',1,2)<>0 -- 查询出所有员工的姓名和职位的前5个字符。 select substr(ename,1,5),substr(job,1,5)from emp -- 查询出姓名字符数超过5个的员工信息。 select * from emp where length(ename)>5 --instr(从指定位置找字符出现的坐标,如果指定位置没有返回0) select instr('ABCCBA','C',1,2)from dual -- 查询出所有员工的姓名和年总收入（(月薪+奖金)*12）。 select ename as 姓名,(sal+nvl2(comm,comm,0))*12 as 总收入 from emp --日期 --返回当前数据库时间 select sysdate from dual --返回时间精确到微秒(用得少) select systimestamp from dual --对日期进行加减默认是加减天 select sysdate+1 from dual --add_months(对月份进行加减) select add_months(sysdate,12)from dual --months_between(d1,d2)(返回两个日期相差的月数) select months_between(sysdate,hiredate)from emp --last_day(d)(返回指定日期当月的最后一天) select last_day(sysdate)from dual select last_day(add_months(sysdate,1))from dual --round(d,)(对指定日期时间取整)(如果参数为年，返回当前年，如果参数为月，取半舍入（一个月31天，16日才能进月）) --如果参数是天，返回是当日这个星期的第一天（如果是周四进一周） select round(sysdate,'YEAR')from dual --next_day(d,day)(返回从d开始的下一个时间值) select next_day(sysdate,'星期五')from dual --trunc(d,)(截止日期时间数据，默认为截断当天的开始时间)(截断) --如果参数是day返回的是当前星期的第一天 select trunc(sysdate,'MONTH')from dual --查询出每个员工的姓名，以及到今天他共工作了几天。 select ename,sysdate-hiredate from emp --查询出在当月最后一天入职的员工姓名和入职日期。 select ename,hiredate from emp where last_day(hiredate)=hiredate; --查询出在当月第一天入职的员工姓名和入职日期。 select ename,hiredate from emp where trunc(hiredate,'MONTH')=hiredate --round四舍五入 --保留两位小数 select round(13223.6767,2) from dual --整数 select round(13223.67)from dual --trunc截断 --保留两位小数 select trunc(13223.6767,2) from dual --整数 select trunc(13223.67)from dual --mod取余 select mod(5,2)from dual --floor(向下取整) select floor(5.3)from dual --ceil(向上取整) select ceil(5.3)from dual --select ceil(123.456) from dual; --select floor(123.456) from dual; --select round(123.456) from dual; --select round(456.123, -2) from dual; --select trunc(123.456) from dual; --select trunc(456.123, -2) from dual; --类型转换 --to_char转成字符串 select to_char(234,'L99,9') from dual --日期转 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual select * from emp where to_char(hiredate,'mm')='0' select --字符转数字to_char select to_number('$12,3','$999')from dual --数字格式转格式 select to_date('2012-12-31','yyyy-mm-dd')from dual --decode(如果匹配就执行条件匹配后面的，都不匹配就执行落单的，没有落单就返回空) select decode('a','a',1,'b',2,'c',3,'d',4,'ff') from dual select ename,decode(job,'PRESIDENT','总裁','SALESMAN','销售员','MANAGER','管理员','ANALYST','分析员',job)from emp select ename,job,sal, case when job='PRESIDENT' then '总裁' when job='SALESMAN' then '销售员' else job end 职位 , case when sal<=2000 then '薪水' else '高薪' end 薪资水平 from emp --匹配email地址里有没有@. select t.*,t.rowid from EMP t where regexp_like(email,'.+@.+\..+') --匹配email地址里以数字开头(^)以数字结尾（$）的邮箱 select t.*,t.rowid from EMP t where regexp_like(email,'^[0-9]+$') --instr email邮箱里第一次出现数字的坐标 count(数字在email里出现的次数) select email,regexp_instr(email,'\d'),regexp_count(email,'\d')from emp t select * from emp where regexp_like(ename, '^.*M.*$'); select regexp_instr('abc123def', '\d') from dual; select regexp_replace(ename, '\w{5,}(\w)', '\1') as n from emp; select regexp_substr(ename, 'N.*') from emp;
