Oracle9i培訓(xùn)膠片中文版-12.基本的DDL和DML介紹-caith.ppt

基本的DML和DDL介紹,1.0,目標(biāo),通過(guò)這部分課程的學(xué)習(xí),您將了解以下內(nèi)容 : Select 語(yǔ)句的功能 執(zhí)行基本的Select語(yǔ)句 描述 DML 語(yǔ)句 在表中插入數(shù)據(jù)行 在表中修改數(shù)據(jù)行 從表中刪除數(shù)據(jù)行 合并表中的數(shù)據(jù)行 控制事務(wù)處理,SELECT語(yǔ)句的功能,Selection,Projection,Table 1,Table 2,Table 1,Table 1,Join,基礎(chǔ)SELECT 語(yǔ)句語(yǔ)法,SELECT *|DISTINCT column|expression alias,. FROM table;,SELECT * FROM departments;,選擇所有的列,選擇指定的列,SELECT department_id, location_id FROM departments;,如何寫SQL語(yǔ)句,SQL 語(yǔ)句不區(qū)分大小寫 關(guān)鍵字不能分行隔開 條件通常被分行放置 縮進(jìn)被用來(lái)提高語(yǔ)句的可讀性,算術(shù)表達(dá)式,通過(guò)使用算術(shù)操作符來(lái)表達(dá)數(shù)值型和日期型數(shù)據(jù).,Operator + - * /,描述 加 Add 減 Subtract 乘 Multiply 除 Divide,使用算術(shù)操作符,SELECT last_name, salary, salary + 300 FROM employees;,算術(shù)操作符優(yōu)先級(jí),乘除的優(yōu)先級(jí)高于加減. 相同優(yōu)先級(jí)從左到右依次處理. 括號(hào)被要求優(yōu)先執(zhí)行.,操作優(yōu)先級(jí),SELECT last_name, salary, 12*salary+100 FROM employees;,使用圓括號(hào),SELECT last_name, salary, 12*(salary+100) FROM employees;,定義空值,空值是一種不可用、不被指派、未知、不確定的值 空值不同于零或一個(gè)空格,SELECT last_name, job_id, salary, commission_pct FROM employees;,SELECT last_name, 12*salary*commission_pct FROM employees;,在算術(shù)操作中的空值,算術(shù)操作中如有空值，運(yùn)算結(jié)果返回為空.,使用列的別名,SELECT last_name “Name“, salary*12 “Annual Salary“ FROM employees;,SELECT last_name AS name, commission_pct comm FROM employees;,使用連接符,SELECT last_name|job_id AS “Employees“ FROM employees;,使用字符串,SELECT last_name | is a |job_id AS “Employee Details“ FROM employees;,重復(fù)的行,查詢結(jié)果缺省顯示所有的行，包括重復(fù)的行.,SELECT department_id FROM employees;,排除重復(fù)的行,SELECT DISTINCT department_id FROM employees;,使用SQL語(yǔ)句限制顯示的行,“retrieve all employees in department 90”,EMPLOYEES,限制挑選出來(lái)的行,SELECT *|DISTINCT column|expression alias,. FROM table WHERE condition(s);,使用WHERE語(yǔ)句限制返回的行 WHERE語(yǔ)句應(yīng)該寫在FROM語(yǔ)句之后,使用 WHERE 條件,SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;,字符串和日期值,都使用單引號(hào) 字符串值區(qū)分大小寫，日期值需注意格式,SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen;,比較條件,操作符 = = ,含義 等于 大于 大于或等于 小于 小于或等于 不等于,SELECT last_name, salary FROM employees WHERE salary = 3000;,使用比較條件,其它的比較條件,操作符 BETWEEN .AND. IN(set) LIKE IS NULL,含義 兩個(gè)值之間 匹配值列表中值 匹配一個(gè)字符樣式 空值,使用 BETWEEN 條件,可以使用該條件顯示某范圍內(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 條件,滿足成員列表中的條件.,使用 LIKE 條件,搜索條件能包括兩種模式匹配類型: % 表示許多字符 _ 表示一個(gè)字符,SELECT first_name FROM employees WHERE first_name LIKE S%;,可混合兩種模式匹配類型,使用 LIKE 條件,SELECT last_name FROM employees WHERE last_name LIKE _o%;,使用 NULL 條件,測(cè)驗(yàn)空值使用 IS NULL 條件.,SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;,邏輯條件,Operator AND OR NOT,含義 兩部分條件都返回真值則結(jié)果返回真值 兩部分條件中有任意一部分返回真值則結(jié)果返回真值 如果條件返回假則結(jié)果返回真值,使用 AND 操作符,要求條件都為真,SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary =10000 AND job_id LIKE %MAN%;,使用 OR 操作符,SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary = 10000 OR job_id LIKE %MAN%;,滿足任意一個(gè)條件即可返回真值,SELECT last_name, job_id FROM employees WHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP);,使用 NOT 操作符,優(yōu)先規(guī)則,使用括號(hào)可不遵照上述優(yōu)先規(guī)則.,優(yōu)先級(jí)別 操作符 1 算術(shù)操作符 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)先規(guī)則,SELECT last_name, job_id, salary FROM employees WHERE (job_id = SA_REP OR job_id = AD_PRES) AND salary 15000;,優(yōu)先規(guī)則,使用括號(hào),SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;,ORDER BY 條件,帶 ORDER BY 條件的排序 ASC: 升序，缺省 DESC: 降序 ORDER BY 條件在Select語(yǔ)句最后,降序排列,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 last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;,多列排序,數(shù)據(jù)操作語(yǔ)句,一條 DML 語(yǔ)句在下列情況下被執(zhí)行: 向表中增加新的數(shù)據(jù)行 修改表中存在的數(shù)據(jù) 從表中刪除數(shù)據(jù) 一個(gè)事務(wù)由DML語(yǔ)句集合形成的邏輯工作單元構(gòu)成.,向表中增加新的數(shù)據(jù)行,DEPARTMENTS,New row,INSERT 語(yǔ)句語(yǔ)法,.,INSERT INTO table (column , column.) VALUES (value , value.);,向表中插入新的數(shù)據(jù)行,插入的新行應(yīng)包括所有的列值. 插入的值應(yīng)按照表中列缺省的順序排列. 如插入所有的列, INSERT 條件中可隨意選擇是否列出所有列.,INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, Public Relations, 100, 1700); 1 row created.,INSERT INTO departments VALUES (100, Finance, NULL, NULL); 1 row created.,INSERT INTO departments (department_id, department_name ) VALUES (30, Purchasing); 1 row created.,插入帶空值的數(shù)據(jù)行,隱性的方法: 省略為空的列名.,顯性的方法: 使用關(guān)鍵字NULL來(lái)指定為空的列.,INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (113, Louis, Popp, LPOPP, 515.124.4567, SYSDATE, AC_ACCOUNT, 6900, NULL, 205, 100); 1 row created.,插入特殊的值,SYSDATE 函數(shù)記錄當(dāng)前的日期和時(shí)間.,INSERT INTO employees VALUES (114, Den, Raphealy, DRAPHEAL, 515.127.4561, TO_DATE(FEB 3, 1999, MON DD, YYYY), AC_ACCOUNT, 11000, NULL, 100, 30); 1 row created.,插入特殊的日期型值,增加一行數(shù)據(jù)： 驗(yàn)證：,帶子查詢的INSERT 語(yǔ)句 字段的數(shù)據(jù)類型必須匹配,INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE %REP%; 4 rows created.,從另外一個(gè)表中復(fù)制行,修改表中數(shù)據(jù),EMPLOYEES,修改 EMPLOYEES 表中的某些行.,UPDATE 語(yǔ)句語(yǔ)法,可一次性修改多行,UPDATE table SET column = value , column = value, . WHERE condition;,UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated.,在WHERE條件中指定需要修改的行. 修改表中所有的行.,修改表中數(shù)據(jù),UPDATE copy_emp SET department_id = 110; 22 rows updated.,UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; 1 row updated.,使用子查詢修改兩列的值,UPDATE copy_emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200); 1 row updated.,基于另外表SELECT得到的值修改數(shù)據(jù),使用子查詢實(shí)現(xiàn)：,UPDATE employees * ERROR at line 1: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found,UPDATE employees SET department_id = 55 WHERE department_id = 110;,修改行時(shí): 完整性約束錯(cuò)誤,Department number 55 does not exist,從 DEPARTMENTS 表中刪除一行.,從表中刪除一行,DEPARTMENTS,DELETE 語(yǔ)句語(yǔ)法,DELETE FROM table WHERE condition;,通過(guò)WHERE條件指定刪除的行. 省略 WHERE 條件，所有的行都將被刪除.,從表中刪除多行,DELETE FROM departments WHERE department_name = Finance; 1 row deleted.,DELETE FROM copy_emp; 22 rows deleted.,DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE %Public%); 1 row deleted.,基于另外表SELECT得到的值刪除數(shù)據(jù),通過(guò)子查詢語(yǔ)句從另外表中得到值作為刪除條件.,刪除行時(shí): 完整性約束錯(cuò)誤,DELETE FROM departments WHERE department_id = 60;,DELETE FROM departments * ERROR at line 1: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found,不能刪除被另外一個(gè)表作為外鍵的主鍵對(duì)應(yīng)的行.,在INSERT 語(yǔ)句中使用子查詢,INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50) VALUES (99999, Taylor, DTAYLOR, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK, 5000, 50); 1 row created.,在INSERT 語(yǔ)句中使用的子查詢,驗(yàn)證結(jié)果,SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50;,在DML語(yǔ)句中使用 WITH CHECK OPTION 關(guān)鍵字,WITH CHECK OPTION 關(guān)鍵字禁止改變不在子查詢結(jié)果中的值.,INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION) VALUES (99998, Smith, JSMITH, TO_DATE(07-JUN-99, DD-MON-RR), ST_CLERK, 5000); INSERT INTO * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation,使用顯性的缺省值,INSERT INTO departments (department_id, department_name, manager_id) VALUES (300, Engineering, DEFAULT);,UPDATE departments SET manager_id = DEFAULT WHERE department_id = 10;,INSERT中使用缺省值: UPDATE中使用缺省值:,數(shù)據(jù)庫(kù)事務(wù),一個(gè)數(shù)據(jù)庫(kù)完整事務(wù)包含以下情況: 構(gòu)成對(duì)數(shù)據(jù)的一次一致性改變的DML語(yǔ)句（一條或多條） 一條DDL 語(yǔ)句,數(shù)據(jù)庫(kù)事務(wù),當(dāng)?shù)谝粭l DML SQL 語(yǔ)句執(zhí)行時(shí)開始 當(dāng)下列任意事件發(fā)生時(shí)結(jié)束: 提交或回滾語(yǔ)句執(zhí)行 一條DDL 執(zhí)行(自動(dòng)提交) 系統(tǒng)崩潰,COMMIT 和 ROLLBACK 語(yǔ)句的優(yōu)勢(shì),保證數(shù)據(jù)一致性 在改變永久生效之前預(yù)覽改變 可以將一些操作按邏輯相關(guān)性分組,控制事務(wù),ROLLBACK to SAVEPOINT B,ROLLBACK to SAVEPOINT A,ROLLBACK,UPDATE. SAVEPOINT update_done; Savepoint created. INSERT. ROLLBACK TO update_done; Rollback complete.,回滾改變到一個(gè)標(biāo)記,在當(dāng)前的事務(wù)中可通過(guò) SAVEPOINT 語(yǔ)句創(chuàng)建標(biāo)記. ROLLBACK TO SAVEPOINT 語(yǔ)句可回滾到標(biāo)記.,自動(dòng)的 commit 發(fā)生在 : DDL 語(yǔ)句執(zhí)行,隱性的事務(wù)處理,COMMIT 或 ROLLBACK前數(shù)據(jù)狀態(tài),數(shù)據(jù)可恢復(fù)到原先狀態(tài) 當(dāng)前用戶通過(guò)SELECT 看到DML操作后數(shù)據(jù)的結(jié)果 其它用戶不能SELECT 看到當(dāng)前用戶DML操作后數(shù)據(jù)的結(jié)果. 受影響的行可能被鎖，其它用戶不能改變受影響的行.,COMMIT后數(shù)據(jù)狀態(tài),被改變的數(shù)據(jù)在數(shù)據(jù)庫(kù)中永久改變. 前面的數(shù)據(jù)狀態(tài)信息被永久丟失. 所有用戶將看到同樣的數(shù)據(jù)結(jié)果. 受影響的行鎖被釋放; 這些行能被其它用戶使用.,COMMIT; Commit compl