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

1、过滤和排序数据 目标通过本章章学习，您将可可以:在查询中中过滤行行。在查询中中对行进进行排序序。在查询中中过滤行行返回在90好部门工工作的所所有员工工的信息息EMPLOYEES过滤使用WHERE子句，将将不满足足条件的的行过滤滤掉。WHERE子句紧随随FROM子句。SELECT*|DISTINCTcolumn|expressionalias,.FROMtableWHEREcondition(s);WHERE子句SELECTemployee_id, last_name,job_id,department_idFROMemployeesWHEREdepartment_id= 90 ;字符和日日期字

2、符和日日期要包包含在单单引号中中。字符大小小写敏感感，日期期格式敏敏感。默认的日日期格式式是DD-MON-RR。SELECTlast_name,job_id, department_idFROMemployeesWHERElast_name =Whalen;比较运算算操作符=含义等于大于 大于、等等于小于 小于、等等于不等于SELECTlast_name,salaryFROMemployeesWHEREsalary =10000ANDjob_id LIKE%MAN%;OROR要求或关关系为真真。SELECTemployee_id, last_name,job_id,salaryFROMempl

3、oyeesWHEREsalary = 10000ORjob_id LIKE%MAN%;SELECTlast_name,job_idFROMemployeesWHEREjob_idNOTIN(IT_PROG,ST_CLERK,SA_REP);NOT优先级可以使用用括号改改变优先先级顺序序优先级1算术运算算符2连接符3比较符4ISNOTNULL,LIKE,NOTIN5NOTBETWEEN6NOT7AND8ORSELECTlast_name,job_id, salaryFROMemployeesWHEREjob_id =SA_REPORjob_id =AD_PRESANDsalary 15000;优

4、先级SELECTlast_name,job_id, salaryFROMemployeesWHERE(job_id= SA_REPORjob_id =AD_PRES)ANDsalary 15000;优先级使用括号号控制执执行顺序序。SELECTlast_name, job_id,department_id,hire_dateFROMemployeesORDERBYhire_date;ORDERBY子句使用ORDERBY子句排序序ASC:升序DESC:降序ORDERBY子句在SELECT语句的结结尾。降序排序序SELECTlast_name, job_id,department_id,hire_

5、dateFROMemployeesORDERBYhire_dateDESC;按别名排排序SELECTemployee_id, last_name,salary*12 annsalFROMemployeesORDERBYannsal;按照ORDERBY列表的顺顺序排序序。可以使用用不在SELECT列表中的的列排序序。SELECTlast_name,department_id,salaryFROMemployeesORDERBYdepartment_id,salaryDESC;多个列排排序总结SELECT*|DISTINCTcolumn|expressionalias,.FROMtableWHEREcondition(s)ORDERBYcolumn, expr, alias ASC|DESC;通过本课课，您应应该可以以完成