過濾和排序數(shù)據(jù)課件.ppt

過濾和排序數(shù)據(jù),目標(biāo),通過本章學(xué)習(xí)，您將可以: 在查詢中過濾行。 在查詢中對行進(jìn)行排序。,在查詢中過濾行,返回在 90好部門工作的所有員工的信息,EMPLOYEES,過濾,使用WHERE 子句，將不滿足條件的行過濾掉。 WHERE 子句緊隨 FROM 子句。,SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s);,WHERE 子句,SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;,字符和日期,字符和日期要包含在單引號(hào)中。 字符大小寫敏感，日期格式敏感。 默認(rèn)的日期格式是 DD-MON-RR。,SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen;,比較運(yùn)算,操作符 = = ,含義 等于 大于 大于、等于 小于 小于、等于 不等于,SELECT last_name, salary FROM employees WHERE salary = 3000;,比較運(yùn)算,其它比較運(yùn)算,操作符 BETWEEN .AND. IN(set) LIKE IS NULL,含義 在兩個(gè)值之間 (包含邊界) 等于值列表中的一個(gè) 模糊查詢 空值,BETWEEN,使用 BETWEEN 運(yùn)算來顯示在一個(gè)區(qū)間內(nèi)的值。,SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;,Lower limit,Upper limit,SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);,IN,使用 IN運(yùn)算顯示列表中的值。,LIKE,使用 LIKE 運(yùn)算選擇類似的值 選擇條件可以包含字符或數(shù)字: % 代表一個(gè)或多個(gè)字符。 _ 代表一個(gè)字符。,SELECT first_name FROM employees WHERE first_name LIKE S%;,%和-可以同時(shí)使用。 可以使用 ESCAPE 標(biāo)識(shí)符 選擇%和 _ 符號(hào)。,LIKE,SELECT last_name FROM employees WHERE last_name LIKE _o%;,NULL,使用 NULL 判斷空值。,SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;,邏輯運(yùn)算,操作符 AND OR NOT,含義 邏輯并 邏輯或 邏輯否,AND,AND 要求和的關(guān)系為真。,SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary =10000 AND job_id LIKE %MAN%;,OR,OR 要求或關(guān)系為真。,SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary = 10000 OR job_id LIKE %MAN%;,SELECT last_name, job_id FROM employees WHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP);,NOT,優(yōu)先級,可以使用括號(hào)改變優(yōu)先級順序,優(yōu)先級 1 算術(shù)運(yùn)算符 2 連接符 3 比較符 4 IS NOT NULL, LIKE, NOT IN 5 NOT BETWEEN 6 NOT 7 AND 8 OR,SELECT last_name, job_id, salary FROM employees WHERE job_id = SA_REP OR job_id = AD_PRES AND salary 15000;,優(yōu)先級,SELECT last_name, job_id, salary FROM employees WHERE (job_id = SA_REP OR job_id = AD_PRES) AND salary 15000;,優(yōu)先級,使用括號(hào)控制執(zhí)行順序。,SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;,ORDER BY子句,使用 ORDER BY 子句排序 ASC: 升序 DESC: 降序 ORDER BY 子句在SELECT語句的結(jié)尾。,降序排序,SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC ;,按別名排序,SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal;,按照ORDER BY 列表的順序排序。 可以使用不在SELECT 列表中的列排序。,SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;,多個(gè)列排序,總結(jié),SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s) ORDER BY colum