ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle学习笔记(2)

oracle学习笔记(2)

原创 Linux操作系统 作者:Deadmonth 时间:2011-07-13 17:35:47 0 删除 编辑

对于(1)的补充 子查询(subquery

以下例题是以oracle中自带的hr用户中的表

SQL>conn  hr/passwd

SQL>select  *  from  tab;

单行子查询

--eg 最低工资的员工信息

select min(salary) from employees;

select last_name,salary

from employees

where salary = (select min(salary) from employees);

 

--另一种方法 rownumoralce中特有的)

select last_name,salary

from employees

where rownum <= 1

order by salary;

 

多行子查询

-- in

select max(salary) from employees group by department_id;

 

select last_name,salary

from employees

where salary in

(select max(salary) from employees group by department_id);

 

-- any  all

 

select employee_id,last_name,job_id,salary

from employees

where salary < any (select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';

select employee_id,last_name,job_id,salary

from employees

where salary < all (select salary from employees where job_id = 'IT_PROG') and job_id <> 'IT_PROG';

 SQL函数(常用函数介绍,关键在于灵活运用)

字符函数  lower,upper,initcap,length,substr,trim,pad,replace

--lower , initcap

select last_name,job_id,salary

from employees

where last_name = 'lee';

--where lower(last_name) = 'lee';

--where last_name = initcap('lee');

 

--substr,upper

select last_name,job_id,salary

from employees

where substr(job_id,1,2) = upper('ad');

--说明substr中的1表示位置,2为长度;且位置是从1开始。

 

--pad  lpad  rpad

select lpad(first_name,10,'*') l,rpad(last_name,10,'-') r

from employees

where job_id = 'IT_PROG';

 

select lpad('12345678',6,'0') from dual;

select rpad('12345678',6,'0') from dual;

-- 都输出123456

注意:当实际的长度大于预期的长度时,进行截取操作,lpad,rpad都是从左端开始截取n位数。

 

.数字函数abs,round,trunk,mod

Select abs(-12.3) from dual;

------mod取模  

select last_name, mod(salary,2000) from employees;

------round 四舍五入

select   round(16.167,0),round(16.167,2),round(16.167,-1),

round(16.167,-2) from dual;

------trunc 截取数字

select   trunc(16.167,0),trunc(16.167,2),trunc(16.167,-1),

trunc(16.167,-2) from dual;

.日期函数 sysdate,months_between,add_months,extract

select sysdate from dual;

select months_between('24-8-11' ,'02-10-89') from dual;

select add_months('24-8-11',-6) from dual;

---extract 返回日期的某个域(如年,月,日,小时等)

select extract(year from sysdate) from dual;

--year 可以换成month,day

select extract(hour from sysdate) from dual;Error

Correct: select extract(hour from timestamp '2012-10-9 8:7:6') from dual;

.转换函数

--to_char  让数字或日期转换成字符串

select last_name,to_char(hire_date,'yyyy-mm-dd hh24:mi:ss')

from employees

where department_id = 90;

 

select to_char(sysdate, 'hh24:mi:ss') from dual;

 --数字货币转换

select to_char(1099.22,'$9,999,999.99') from dual;

select to_char(1099.22,'$0,000,000.00') from dual;

select to_char(1099,'$0,000.00') from dual;

select to_char(1099,'L9,999.99') from dual; --L 表示当地的货币符

--格式位数应大于实际数值的位数,否则无法显示

select to_char(1099,'L999.99') from dual;  

--不够大时输出 ###############

select to_char(11234.1253,'$9,999,999.99') money from dual;

 -- to_date 将字符串转换成日期

select to_date('10-6-11') from dual;

--error ORA-01861: 文字与格式字符串不匹配

select to_date('2012-10-10') from dual;           

select to_date('2012-10-10','yyyy-mm-dd') from dual;--correct

select to_date('2012-10-10','yyyy/mm/dd') from dual;--correct

select to_date('2012-10-10','mm/yyyy/dd') from dual;

--error  ORA-01843: 无效的月份

 

--to_number

select to_number('123') from dual;  --correct

select to_number('123') from dual; --error ORA-01722: 无效数字

select to_number('123','L999.00') from dual; --correct

select to_number('1,234.56','L9999.00') from dual; --correct

select to_number('1,234.56','L9999') from dual; --error

select to_number('1234.56','L9,999.00') from dual;--error!

.其他函数  nvl(), decode()

 

-- nvl

desc employees;

select last_name,salary,commission_pct,

(salary * (1 + nvl(commission_pct,0)))*12  total

from employees;

select last_name,salary,commission_pct,

nvl(to_char(commission_pct),'without')

from employees;

--nvl中的数值或字段类型一定要相同,如果不同,要像上例一样,用to_char这类函数转换成相同类型

 

--decode() 多值判断

--(1) 对于用户hr 表中employees 为例,如果job_id AD_VP ,工资为salary*1.1;

如果job_id HR_REP ,工资为salary*1.2;其他的都是salary;

 

select last_name,job_id,salary,

decode(job_id,'AD_VP',salary*1.1,'HR_REP',salary*1.2,salary)

from employees;

 

 

 

  

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25436591/viewspace-702118/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-02

  • 博文量
    18
  • 访问量
    45206