ORACLE过滤和排序数据.ppt

过滤和排序数据 目标 通过本章学习 您将可以 在查询中过滤行 在查询中对行进行排序 在查询中过滤行 retrieveallemployeesindepartment90 EMPLOYEES 过滤 使用WHERE子句 将不满足条件的行过滤掉 WHERE子句紧随FROM子句 SELECT DISTINCT column expression alias FROMtable WHEREcondition s WHERE子句 SELECTemployee id last name job id department idFROMemployeesWHEREdepartment id 90 字符和日期 字符和日期要包含在单引号中 字符大小写敏感 日期格式敏感 默认的日期格式是DD MON RR SELECTlast name job id department idFROMemployeesWHERElast name Whalen 比较运算 Operator MeaningEqualtoGreaterthanGreaterthanorequaltoLessthanLessthanorequaltoNotequalto SELECTlast name salaryFROMemployeesWHEREsalary 3000 比较运算 其它比较运算 运算符BETWEEN AND IN set LIKEISNULL 含义在两值之间 包含 匹配列出的值匹配一个字符模式是空值 BETWEEN 使用BETWEEN运算来显示在一个区间内的值 SELECTlast name salaryFROMemployeesWHEREsalaryBETWEEN2500AND3500 Lowerlimit Upperlimit SELECTemployee id last name salary manager idFROMemployeesWHEREmanager idIN 100 101 201 IN 使用IN运算显示列表中的值 LIKE 使用LIKE运算选择类似的值选择条件可以包含字符或数字 代表一个或多个字符 代表一个字符 SELECTfirst nameFROMemployeesWHEREfirst nameLIKE S 和 可以同时使用 可以使用ESCAPE标识符选择 和 符号 LIKE SELECTlast nameFROMemployeesWHERElast nameLIKE o NULL 使用NULL判断空值 SELECTlast name manager idFROMemployeesWHEREmanager idISNULL 逻辑运算 OperatorANDORNOT MeaningReturnsTRUEifbothcomponentconditionsaretrueReturnsTRUEifeithercomponentconditionistrueReturnsTRUEifthefollowingconditionisfalse AND AND要求和的关系为真 SELECTemployee id last name job id 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 优先级 Overriderulesofprecedencebyusingparentheses OrderEvaluatedOperator1Arithmeticoperators2Concatenationoperator3Comparisonconditions4IS NOT NULL LIKE NOT IN5 NOT BETWEEN6NOTlogicalcondition7ANDlogicalcondition8ORlogicalcondition SELECTlast name job id salaryFROMemployeesWHEREjob id SA REP ORjob id AD PRES ANDsalary 15000 优先级 AND优先级大于OR SELECTlast name job id salaryFROMemployeesWHERE job id SA REP ORjob 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 dateFROMemployeesORDERBYhire dateDESC 按别名排序 SELECTemployee id last name salary 12annsalFROMemployeesORDERBYannsal 按照ORDERBY列表的顺序排序 可以使用不在SELECT列表中的列排序 SELECTlast name department id salaryFROMemployeesORDERBYdepartment id salaryDESC 多个列排序 总结 SELECT DISTINCT column expression alias FROMtable WHEREcond