文本教程分析les

1、Restricting and Sorting Data ObjectivesAfter completing this lesson, you should be able to do the following:Limit the rows that are retrieved by a querySort the rows that are retrieved by a queryUse ampersand substitution to restrict and sort output at run timeLesson AgendaLimiting rows with:The WHE

2、RE clauseThe comparison conditions using =, =, BETWEEN, IN, LIKE, and NULL conditionsLogical conditions using AND, OR, and NOT operatorsRules of precedence for operators in an expressionSorting rows using the ORDER BY clauseSubstitution variablesDEFINE and VERIFY commandsLimiting Rows Using a Select

3、ion“retrieve allemployees in department 90”EMPLOYEESLimiting the Rows That Are SelectedRestrict the rows that are returned by using the WHERE clause:The WHERE clause follows the FROM clause.SELECT *|DISTINCT column|expression alias,.FROM tableWHERE condition(s);SELECT employee_id, last_name, job_id,

4、 department_idFROM employeesWHERE department_id = 90 ;Using the WHERE ClauseSELECT last_name, job_id, department_idFROM employeesWHERE last_name = Whalen ;Character Strings and DatesCharacter strings and date values are enclosed with single quotation marks.Character values are case-sensitive and dat

5、e values are format-sensitive.The default date display format is DD-MON-RR.SELECT last_name FROM employeesWHERE hire_date = 17-FEB-96 ;注：题53Comparison OperatorsNot equal toBetween two values (inclusive)BETWEEN.AND.Match any of a list of values IN(set)Match a character pattern LIKEIs a null value IS

6、NULLLess thanLess than or equal to=Greater thanEqual to=MeaningOperatorSELECT last_name, salaryFROM employeesWHERE salary = 10000AND job_id LIKE %MAN% ;Using the AND OperatorAND requires both the component conditions to be true:SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary

7、= 10000OR job_id LIKE %MAN% ;Using the OR OperatorOR requires either component condition to be true:SELECT last_name, job_idFROM employeesWHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP) ;Using the NOT OperatorLesson AgendaLimiting rows with:The WHERE clauseThe comparison conditions using =, 15000;Ru

8、les of PrecedenceSELECT last_name, job_id, salaryFROM employeesWHERE (job_id = SA_REPOR job_id = AD_PRES)AND salary 15000;12注：题56、57、60、61Lesson AgendaLimiting rows with:The WHERE clauseThe comparison conditions using =, =, BETWEEN, IN, LIKE, and NULL operatorsLogical conditions using AND, OR, and N

9、OT operatorsRules of precedence for operators in an expressionSorting rows using the ORDER BY clauseSubstitution variablesDEFINE and VERIFY commandsUsing the ORDER BY ClauseSort the retrieved rows with the ORDER BY clause:ASC: Ascending order, defaultDESC: Descending orderThe ORDER BY clause comes l

10、ast in the SELECT statement:SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;SortingSorting in descending order:Sorting by column alias:SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;1SELECT employee_id, last_name, salary*1

11、2 annsalFROM employeesORDER BY annsal ;2SortingSorting by using the columns numeric position:Sorting by multiple columns:SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY 3;3SELECT last_name, department_id, salaryFROM employeesORDER BY department_id, salary DESC;4注：题63、64、65、

12、66、67、68、69Lesson AgendaLimiting rows with:The WHERE clauseThe comparison conditions using =, =, BETWEEN, IN, LIKE, and NULL operatorsLogical conditions using AND, OR, and NOT operatorsRules of precedence for operators in an expressionSorting rows using the ORDER BY clauseSubstitution variablesDEFIN

13、E and VERIFY commandsSubstitution Variables. salary = ? department_id = ? . last_name = ? .I want to query different values.Substitution VariablesUse substitution variables to:Temporarily store values with single-ampersand (&) and double-ampersand (&) substitutionUse substitution variables to supple

14、ment the following:WHERE conditionsORDER BY clausesColumn expressionsTable namesEntire SELECT statementsSELECT employee_id, last_name, salary, department_idFROM employeesWHERE employee_id = &employee_num ;Using the Single-Ampersand Substitution VariableUse a variable prefixed with an ampersand (&) t

15、o prompt the user for a value:Using the Single-Ampersand Substitution VariableSELECT last_name, department_id, salary*12FROM employeesWHERE job_id = &job_title ;Character and Date Values with Substitution VariablesUse single quotation marks for date and character values:Specifying Column Names, Expr

16、essions, and TextSELECT employee_id, last_name, job_id,&column_nameFROM employeesWHERE &conditionORDER BY &order_column ;SELECT employee_id, last_name, job_id, &column_nameFROM employeesORDER BY &column_name ;Using the Double-Ampersand Substitution VariableUse double ampersand (&) if you want to reu

17、se the variable value without prompting the user each time:Lesson AgendaLimiting rows with:The WHERE clauseThe comparison conditions using =, =IS NULL!=IS LIKEIN BETWEENIn this lesson, you should have learned how to:Use the WHERE clause to restrict rows of output:Use the comparison conditionsUse the

18、 BETWEEN, IN, LIKE, and NULL operatorsApply the logical AND, OR, and NOT operatorsUse the ORDER BY clause to sort rows of output:Use ampersand substitution to restrict and sort output at run timeSELECT *|DISTINCT column|expression alias,.FROM tableWHERE condition(s)ORDER BY column, expr, alias ASC|DESC ;SummaryPract