对于（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);

--另一种方法 rownum（oralce中特有的）

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;