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