oracle SQL集锦

下面的语句都是本人整理出的，并且都在oracle XE上测试通过。下边的函数都没有做说明，相信大家都知道怎么用了，呵呵。

1普通的查询语句，例如：
SELECTlast_name,department_idFROMemployees;
2查询视图
SELECTemployee_id,last_name,job_title,department_name,country_name,
region_nameFROMemp_details_view;
3更换列名查询
SELECTemployee_id"EmployeeIDnumber",last_name"Employeelastname",
first_name"Employeefirstname"FROMemployees;
4联接查询
自然联接：要求两个表中的公共列必须有相同的名称和结构类型，否则报错。
SELECTemployee_id,last_name,first_name,department_id,
department_name,manager_idFROMemployeesNATURALJOINdepartments;
查询三张或以上表时用USING，前提是用来连接两张表的列必须名称相同。
SELECTe.employee_id,e.last_name,e.first_name,e.manager_id,department_id,
d.department_name,d.manager_idFROMemployeese
JOINdepartmentsdUSING(department_id);
联接时加上WHERE子句。
SELECTe.employee_id,e.last_name,e.first_name,e.department_id,
d.department_name,d.manager_id,d.location_id,l.country_idFROMemployeese
JOINdepartmentsdONe.department_id=d.department_id
JOINlocationslONd.location_id=l.location_id
WHEREl.location_id=1700;
自我联接：
SELECTe.employee_idemp_id,e.last_nameemp_lastname,m.employee_idmgr_id,
m.last_namemgr_lastname
FROMemployeese
JOINemployeesmONe.manager_id=m.employee_id;
LEFTOUTERJOIN：左表中所有的记录即使在右表中没有联接的都会被查询出来。
SELECTe.employee_id,e.last_name,e.department_id,d.department_name
FROMemployeeseLEFTOUTERJOINdepartmentsd
ON(e.department_id=d.department_id);
RIGHTOUTERJOIN：右表中所有的记录即使在左表中没有联接的都会被查询出来。
SELECTe.employee_id,e.last_name,e.department_id,d.department_name
FROMemployeeseRIGHTOUTERJOINdepartmentsd
ON(e.department_id=d.department_id);
FULLOUTERJOIN：左右表中的记录全部被查询出来。
SELECTe.employee_id,e.last_name,e.department_id,d.department_name
FROMemployeeseFULLOUTERJOINdepartmentsd
ON(e.department_id=d.department_id);

5绑定变量查询：Oracle将已解析、已编译的SQL连同其他内容存储在共享池中，这是SGA中一个非常重要的存储结构（内存结构主要分为SGA和PGA）。而绑定变量查询在Oracle里执行时只编译一次，随后就会把这个查询计划存储在一个共享池中以便重用，即所谓的软解析。具体的使用方法如下：
SELECT*FROMemployeesWHEREemployee_id=:employee_id
6查询虚拟列：所谓的虚拟列在Oracle里类似表格的列，但并非存储在表中。查询虚拟列时将返回一个值，因此它又类似与函数。Oracle里的虚拟列有：ROWNUM,SYSDATE,andUSER。eg：
SELECTSYSDATE"NOW"FROMDUAL;
SELECTUSERFROMDUAL;
SELECTemployee_id,hire_date,SYSDATEFROMemployeesWHEREROWNUM<10;
7带函数查询：
UsingNumericFunctions：
SELECTemployee_id,ROUND(salary/30,2)"Salaryperday"FROMemployees;
SELECTemployee_id,TRUNC(salary/30,0)"Salaryperday"FROMemployees;
SELECTemployee_id,MOD(employee_id,2)FROMemployees;
UsingCharacterFunctions：
SELECTemployee_id,UPPER(last_name),LOWER(first_name)FROMemployees;
SELECTemployee_id,INITCAP(first_name),INITCAP(last_name)FROMemployees;
SELECTemployee_id,RTRIM(first_name)||''||LTRIM(last_name)FROMemployees;
SELECTemployee_id,TRIM(last_name)||','||TRIM(first_name)FROMemployees;
SELECTemployee_id,RPAD(last_name,30,''),first_nameFROMemployees;
SELECTemployee_id,SUBSTR(last_name,1,10)FROMemployees;
SELECTLENGTH(last_name)FROMemployees;
SELECTemployee_id,REPLACE(job_id,'SH','SHIPPING')FROMemployees
WHERESUBSTR(job_id,1,2)='SH';
UsingDateFunctions：
SELECTemployee_id,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE))"MonthsEmployed"
FROMemployees;
SELECTemployee_id,EXTRACT(YEARFROMhire_date)"YearHired"FROMemployees;
SELECTEXTRACT(YEARFROMSYSDATE)||EXTRACT(MONTHFROMSYSDATE)||
EXTRACT(DAYFROMSYSDATE)"CurrentDate"FROMDUAL;
SELECTemployee_id,hire_date,ADD_MONTHS(hire_date,3)FROMemployees;
SELECTemployee_id,hire_date,LAST_DAY(hire_date)"Lastdayofmonth"
FROMemployees;
SELECTSYSTIMESTAMPFROMDUAL;
UsingConversionFunctions：
SELECTTO_CHAR(SYSDATE,'DD-MON-YYYYAD')"Today"FROMDUAL;
SELECTTO_CHAR(SYSDATE,'FMMonthDDYYYY')"Today"FROMDUAL;
SELECTTO_CHAR(SYSDATE,'MM-DD-YYYYHH24:MI:SS')"Now"FROMDUAL;
SELECThire_date,TO_CHAR(hire_date,'DS')"ShortDate"FROMemployees;
SELECThire_date,TO_CHAR(hire_date,'DL')"LongDate"FROMemployees;
SELECTTO_CHAR(EXTRACT(YEARFROMSYSDATE))||
TO_CHAR(EXTRACT(MONTHFROMSYSDATE),'FM09')||
TO_CHAR(EXTRACT(DAYFROMSYSDATE),'FM09')"CurrentDate"FROMDUAL;
SELECTTO_CHAR(CURRENT_DATE,'DD-MON-YYYYHH24:MI:SS')"CurrentDate"FROMDUAL;
SELECTTO_CHAR(salary,'$99,999.99')salaryFROMemployees;
SELECTTO_NUMBER('1234.99')+500FROMDUAL;
SELECTTO_NUMBER('11,200.34','99G999D99')+1000FROMDUAL;
SELECTTO_DATE('27-OCT-98','DD-MON-RR')FROMDUAL;
SELECTTO_DATE('28-Nov-0514:10:10','DD-Mon-YYHH24:MI:SS')FROMDUAL;
SELECTTO_DATE('January15,2006,12:00A.M.','Monthdd,YYYY,HH:MIA.M.')
FROMDUAL;
SELECTTO_TIMESTAMP('10-Sep-0514:10:10.123000','DD-Mon-RRHH24:MI:SS.FF')
FROMDUAL;
UsingAggregateFunctions：
SELECTCOUNT(*)"EmployeeCount"FROMemployeesWHEREmanager_id=122;
SELECTCOUNT(*)"EmployeeCount",manager_idFROMemployees
GROUPBYmanager_idORDERBYmanager_id
SELECTCOUNT(commission_pct)FROMemployees;
SELECTCOUNT(DISTINCTdepartment_id)FROMemployees;
SELECTMIN(salary),MAX(salary),AVG(salary),job_idFROMemployees
GROUPBYjob_idORDERBYjob_id;
SELECTRANK(2600)WITHINGROUP
(ORDERBYsalaryDESC)"Rankof$2,600amongclerks"
FROMemployeesWHEREjob_idLIKE'%CLERK';
SELECTjob_id,employee_id,last_name,salary,DENSE_RANK()OVER
(PARTITIONBYjob_idORDERBYsalaryDESC)"SalaryRank(Dense)"
FROMemployeesWHEREjob_id='SH_CLERK';
SELECTemployee_id,salary,hire_date,STDDEV(salary)
OVER(ORDERBYhire_date)"StdDeviationofSalary"
FROMemployeesWHEREjob_id='ST_CLERK';
UsingNULLValueFunctions：
SELECTcommission_pct,NVL(commission_pct,0)FROMemployees;
SELECTphone_number,NVL(phone_number,'MISSING')FROMemployees;
SELECTemployee_id,last_name,commission_pct,salary,
NVL2(commission_pct,salary+(salary*commission_pct),salary)income
FROMemployees;
UsingConditionalFunctions：
SELECTemployee_id,hire_date,salary,
CASEWHENhire_date<TO_DATE('01-JAN-90')THENsalary*1.20
WHENhire_date<TO_DATE('01-JAN-92')THENsalary*1.15
WHENhire_date<TO_DATE('01-JAN-94')THENsalary*1.10
ELSEsalary*1.05END"RevisedSalary"
FROMemployees;
SELECTemployee_id,job_id,salary,
DECODE(job_id,'PU_CLERK',salary*1.05,
'SH_CLERK',salary*1.10,
'ST_CLERK',salary*1.15,
salary)"RevisedSalary"
FROMemployees;