Oracle公司内部数据库培训资料02_过滤和排序数据.ppt

过滤和排序数据,目标,通过本章学习，您将可以:在查询中过滤行。在查询中对行进行排序。,在查询中过滤行,返回在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;,通过本课，您应该可以