oracle语句中得到info,oracle学习笔记（二）------函数

函数：单行函数，多行函数

单行函数：字符函数,Number函数,日期函数，转换函数，系统函数

(oracle中所有字母都是以大写字母存储的)

字符函数：转换函数，字符操纵函数

转换函数:lower('str')　将字符串中所有字母转换为小写

select lower(ename) from emp;

upper('str')　将字符串中所有字母转换为大写

select * from emp where ename='king';

initcap('SStr aaa')　将字符串首字母转换为大写,其它都转换为小写(Sstr Aaa)

字符操纵函数: concat('str1','str2')　用于将两个字符串连接(str1str2)

sumstr('str',1,3)　取子串函数(str)

length('str')　取字符串长度(3) (注意:每个汉字的长度为1,等同一个字母)

instr('str','t')　取某字符在某个字符串中首次出现的位置(2)

lpad()　将字符串按某种模式显示

lpad(sal,10,'*')  结果:******5000

比如sal是5000它按10位输出，多出的位用'*'代替

(字符串函数中的字符串下标是从1开始的，而不是0)

-------------------------

Number函数:

round 返回四舍五入值

round(45.926,2)  45.93

round(45.926,0)  46

round(45.926,-1)  50

trunc 截取小数

trunc(45.926,2)  45.92

trunc(45.926)  45

trunc(45.926,-1) 40

mod 求余运算

mod(1600,300)  100

---------------------------

日期型函数：

日期型函数格式：century,year,month,day,hours,minutes,seconds

默认日期格式:DD-MON-YY

SYSDATE:返回当前数据库服务器的系统日期和时间

select sysdate from dual;

(dual是系统的名义表，是为使用某些函数使用的表，oracle中不允许查询语句中

没有from关键字)

MONTHS_BETWEEN 返回两个日期之间月份的差值

MONTHS_BETWEEN('01-SEP-95','11-JAN-94')   19.6774194

select trunc(months_between(sysdate,'11-JAN-94')/12) from emp

求出表emp中的员工工作的年数

ADD_MONTHS 在日期上加上月份数

ADD_MONTHS('11-JAN-94',6)    11-JUL-94

NEXT_DAY 指定日期的后一天

('01-SEP-95','FRIDAY')  得到的是下一个周五是几号

LAST_DAY 月份中最后一天

LAST_DAY('01-SEP-95') 30-SEP-95

ROUND 四舍五入日期

round('25_7_95','month')对月进行四舍五入　01-8-95

round('25_7_95','year')对年进行四舍五入   01-1-96

TRUNC 截断日期

trunc('25_7_95','month')  01-7-95

trunc('25_7_95','year')   01-1-95

----------------------------------

转换函数

数据类型转换：隐式数据类型转换(隐式转换函数可以不用转换函数,oracle可以自动转换)，显示数据类型转换

oracle自动转换

字符型(数字的字符)　加　数字型 自动转换成数字类型

字符型　和　日期型 相互运算 时 自动转换成日期型

数值型　和　日期型　也可以自动转换成字符型

显示数据类型转换:number 转换为 character 用to_char

character转换为 number 用to_number

character转换为date用to_date

date转换为character用to_char

select ename,to_char(hiredate,'YYYY-MM-DD') from emp;

显示员工受雇日期，转换为字符输出

YYYY 代表完整的年份

YEAR 年份

MM　两位数月份

MONTH 月份的完整名称

DY 每星期中某天的三个字符缩写

DAY　天的完整名称

select name, to_char(hiredate,'YYYY-MM-DD') from emp;

显示员工表，员工的姓名，将员工的受雇日期转换成字符型显示(hiredate 员工的雇佣日期)

select to_char(sal) from emp;

将员工工资转换成字符型

select 'this is '||name||' '||to_char(sal) from emp;(加上连接运算符执行上面的操作)

insert into emp(no,name,hiredate) values(9005,'Tss','1990-11-1');

这里的'1990-11-1'必须符合alter session set nls_date_format='YYYY-MM-DD'定义的日期模式

如果session中定义的日期模式和输入的不一样，可以用下面的日期转换函数去做

insert into emp(no,name,hiredate) values(9006,'Ppss',to_date('1990-11-1','YYYY-MM-DD'));

-----------------------------------------------------------------------------------------------

NVL: 将NULL值转换成一个实际的值(date,character,number型可以使用)

null值任何数据运算时都得到一个null值,这里就可以用NVL

select name, sal, comm, (sal*12)+NVL(comm,0) as 年薪 from emp;

comm为员工的提成工资，因为提成可能有，也可能没有，用NUL这个函数，如果没提成它的默认值为0

NVL(hiredate,'01-JAN-97') 如果雇佣日期为空，它的默认值为

NVL(job,'no job yet')　如果工作职位为空，默认值为'no job yet'

NVL(comm,0) 提成工资为空，默认值为0

-------------------------------------------------------------------------------------------

Decode功能类似CASE or IF-THEN-ELSE语句

DECODE(col/expression, search1,result1

[,search2,result2,...,]

[default])

col/expression 值或表达式

search1 条件１　result1 结果１

default 没有符合条件时的默认值

select job,sal,

DECODE(job, 'ANALYSY', sal*1.1,

'CLERK', sal*1.15,

'MANAGER', sal*1.20,

sal) 实发工资 from emp;

(如果job为'ANALYSY',工资为实际工资再加上10%

如果job为'CLERK',工资为实际工资再加上15%

如果job为'MANAGER',工资为实际工资再加上20%

如果job都不符合，工资就为默认值，实际工资sal

)

-----------------------------------------------------------------

函数嵌套

单行函数可以多重嵌套

select name NVL(to_char(mgr),'No Manager') from emp where mgr is null;

(mgr是上司，如果没有上司，就返回'No Manager')

------------------------------------------------------------------

Sql plus命令

save:将刚执行的sql语句，保存到文件中

save 'd:/empsql.txt';

clear screen 清屏

get:读取硬盘中的脚本文件,在plus Sql中显示其中的内容，并加载到缓存中，并没有执行,然后运行run才能执行

get 'd:/empsql.txt';

@ 'd:/empsql.txt'; 这条语句不仅是把脚本文件读出来，而且加到缓存中，并执行

connect system/oracle 连接到本机的system账号，密码是oracle

connect system/oracle@xxx 最后的xxx是oracle的服务器

访问另一个用户的表(比如当前登入的用户是teacher)

grant select on student　将当前数据库的select语句功能，授权给student

用student登入的另一个sqlPlus

select * from teacher.emp

student用户访问teacher用户的emp表

如果命令前几个写母和其它命令没有重复的，可以简写如：connect 可简写成conn，run-->r

exit: 可直接退出sql plus

sqlPlus 中选‘选项’－－‘环境变量’

里面有个autocommit默认是关，通过命令提交事务;如果选择开，每次的sql语句就都自动提交了。