Oracle公司內(nèi)部數(shù)據(jù)庫(kù)培訓(xùn)資料02_過(guò)濾和排序數(shù)據(jù).ppt

過(guò)濾和排序數(shù)據(jù),目標(biāo),通過(guò)本章學(xué)習(xí)，您將可以:在查詢中過(guò)濾行。在查詢中對(duì)行進(jìn)行排序。,在查詢中過(guò)濾行,返回在90好部門(mén)工作的所有員工的信息,EMPLOYEES,過(guò)濾,使用WHERE子句，將不滿足條件的行過(guò)濾掉。WHERE子句緊隨FROM子句。,SELECT*|DISTINCTcolumn|expressionalias,.FROMtableWHEREcondition(s);,WHERE子句,SELECTemployee_id,last_name,job_id,department_idFROMemployeesWHEREdepartment_id=90;,字符和日期,字符和日期要包含在單引號(hào)中。字符大小寫(xiě)敏感，日期格式敏感。默認(rèn)的日期格式是DD-MON-RR。,SELECTlast_name,job_id,department_idFROMemployeesWHERElast_name=Whalen;,比較運(yùn)算,操作符=,含義等于大于大于、等于小于小于、等于不等于,SELECTlast_name,salaryFROMemployeesWHEREsalary=10000ANDjob_idLIKE%MAN%;,OR,OR要求或關(guān)系為真。,SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary=10000ORjob_idLIKE%MAN%;,SELECTlast_name,job_idFROMemployeesWHEREjob_idNOTIN(IT_PROG,ST_CLERK,SA_REP);,NOT,優(yōu)先級(jí),可以使用括號(hào)改變優(yōu)先級(jí)順序,優(yōu)先級(jí)1算術(shù)運(yùn)算符2連接符3比較符4ISNOTNULL,LIKE,NOTIN5NOTBETWEEN6NOT7AND8OR,SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=SA_REPORjob_id=AD_PRESANDsalary15000;,優(yōu)先級(jí),SELECTlast_name,job_id,salaryFROMemployeesWHERE(job_id=SA_REPORjob_id=AD_PRES)ANDsalary15000;,優(yōu)先級(jí),使用括號(hào)控制執(zhí)行順序。,SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_date;,ORDERBY子句,使用ORDERBY子句排序ASC:升序DESC:降序ORDERBY子句在SELECT語(yǔ)句的結(jié)尾。,降序排序,SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_dateDESC;,按別名排序,SELECTemployee_id,last_name,salary*12annsalFROMemployeesORDERBYannsal;,按照ORDERBY列表的順序排序?？梢允褂貌辉赟ELECT列表中的列排序。,SELECTlast_name,department_id,salaryFROMemployeesORDERBYdepartment_id,salaryDESC;,多個(gè)列排序,總結(jié),SELECT*|DISTINCTcolumn|expressionalias,.FROMtableWHEREcondition(s)ORDERBYcolumn,expr,aliasASC|DESC;,通過(guò)本課，您應(yīng)該可以