Copyright 2004, Oracle. All rights reserved. 天蝎机柜服务器技术规范2.5版 After completing this lesson, you should be able to do the following: Limit the rows that are retrieved by a query Sort the rows that are retrieved by a query Use ampersand substitution in iSQL*Plus to restrict and sort output at run time Objectives Limiting Rows Using a Selection “retrieve all employees in department 90” EMPLOYEES Restrict the rows that are returned by using the WHERE clause: The WHERE clause follows the FROM clause. Limiting the Rows That Are Selected SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s); SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ; Using the WHERE Clause SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen ; Character strings and date values are enclosed by single quotation marks. Character values are case-sensitive, and date values are format-sensitive. The default date format is DD-MON-RR. Character Strings and Dates Comparison Conditions OperatorMeaning =Equal to Greater than =Greater than or equal to = 10000 OR job_id LIKE %MAN% ; Using the OR Operator OR requires either condition to be true: SELECT last_name, job_id FROM employees WHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP) ; Using the NOT Operator Rules of Precedence You can use parentheses to override rules of precedence. OperatorMeaning 1Arithmetic operators 2Concatenation operator 3Comparison conditions 4IS NOT NULL, LIKE, NOT IN 5NOT BETWEEN 6Not equal to 7NOT logical condition 8 AND logical condition 9 OR logical condition SELECT last_name, job_id, salary FROM employees WHERE job_id = SA_REP OR job_id = AD_PRES AND salary 15000; Rules of Precedence SELECT last_name, job_id, salary FROM employees WHERE (job_id = SA_REP OR job_id = AD_PRES) AND salary 15000; 1 2 Sort retrieved rows with the ORDER BY clause: ASC: ascending order, default DESC: descending order The ORDER BY clause comes last in the SELECT statement: Using the ORDER BY Clause SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ; Sorting in descending order: Sorting by column alias: Sorting by multiple columns: Sorting SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ; 1 SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal ; 2 SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC; 3 Substitution Variables . salary = ? department_id = ? . last_name = ? . I want to query different values. Use iSQL*Plus substitution variables to: Temporarily store values with single-ampersand ( Use a variable prefixed with an ampersand ( Use single quotation marks for date and character values: Character and Date Values with Substitution Variables Specifying Column Names, Expressions, and Text SELECT employee_id, last_name, job_id, salary salary 15000 last_name SELECT employee_id, last_name, job_id, Use the double ampersand ( UNDEFINE employee_num old 3: WHERE employee_id = Use the VERIFY command to toggle the display of the substitution variable, both before and after iSQL*Plus replaces substitution variables with values: Using the VERIFY Command SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s) ORDER BY column, expr, alias ASC|DESC ; In this lesson, you should have learned how to: Use the WHERE clause to restrict rows of output: Use the comparison conditions Use the BETWEEN, IN, LIKE, and NULL conditions Apply the logical AND, OR, and NOT operators Use the ORDER BY clause to sort rows of output: Use ampersand substitution in iSQL*Plus to restrict and sort output at run time Summary This practice covers the following topics: Selecting data and changing the order of the rows that are displayed Restricting rows by using the WHERE clause Sorting rows by using the ORDER BY clause Using substitution variables to add flexibility to your SQL SELECT statements Practice 2: Overview 知识回顾知识回顾 Knowledge Knowledge ReviewReview