1、數(shù)據(jù)庫操作與管理語言O(shè)racle SQL 第08章：子查詢、更改表內(nèi)容本章目標子查詢子查詢的分類插入數(shù)據(jù)更新數(shù)據(jù)子查詢及分類子查詢是嵌入到另一個SELECT語句中的一個SELECT語句。通過使用子查詢，可以使用簡單的語句組成強大的語句。當需要從表中選擇行，而選擇條件卻取決于該表自身中的數(shù)據(jù)時，子查詢非常有用。子查詢主要分為以下兩種基本類型單行子查詢多行子查詢另外，還有以下3種類型多列子查詢關(guān)聯(lián)子查詢嵌套子查詢單行子查詢單行子查詢內(nèi)部SELECT語句給外部的SELECT語句返回0行或1行。可以在SELECT語句后面的WHERE、HAVING、FROM后面放置單行子查詢在WHERE子句中使用子查詢

2、-1可以將另外一個查詢作為WHERE子句的子查詢例如SELECT first_name, last_nameFROM customersWHERE customer_id = (SELECT customer_id FROM customers WHERE last_name = Brown);查詢效果如下圖所示：在WHERE子句中使用子查詢-2在單行子查詢還可以使用其他比較運算符，如、=例如，WHERE子句中使用，以及子查詢中使用AVG()聚集函數(shù)SELECT product_id, name, priceFROM productsWHERE price (SELECT AVG(price)

3、 FROM products);查詢結(jié)果如下圖所示：在HAVING子句中使用子查詢HAVING是在分組統(tǒng)計后用于過濾行，同樣在HAVING子句后面可以跟子查詢。單行子查詢將返回結(jié)果用于HAVING子句過濾分組統(tǒng)計的行例如SELECT product_type_id, AVG(price)FROM productsGROUP BY product_type_idHAVING AVG(price) (SELECT MAX(AVG(price) FROM products GROUP BY product_type_id)ORDER BY product_type_id;查詢結(jié)果如下圖所示：在FRO

4、M子句中的子查詢-1將子查詢用于FROM子句的數(shù)據(jù)源。將子查詢放置于FROM子句之后為其提供數(shù)據(jù)。例如SELECT product_idFROM (SELECT product_id FROM products WHERE product_id (SELECT AVG(price) FROM products)ORDER BY product_id DESC;查詢結(jié)果如下圖所示：多行子查詢多行子查詢內(nèi)部SELECT語句給外部的SELECT語句返回1行或多行。在父查詢中可以使用IN、ANY或ALL操作符例如SELECT product_id, nameFROM productsWHERE pro

5、duct_id IN (1, 2, 3);查詢結(jié)果如下所示：在多行子查詢中使用ININ是用來檢查一個的值是否在指定的列表中?？梢允褂米硬樵儊矸祷剡@個列表的值。同樣可以使用NOT IN檢查一個值是否不在指定的列表中。例如，查詢SELECT product_id, nameFROM productsWHERE product_id IN(SELECT product_id FROM products WHERE name LIKE %e%);查詢結(jié)果如下圖所示：在多行子查詢中使用ANY可以使用ANY操作符比較列表中的任意一個值。必須把=，=操作符放置在ANY之前例如，使用ANY查詢?nèi)我鉂M足工資低于

6、最低檔工資的員工信息SELECT employee_id, last_nameFROM employeesWHERE salary ANY (SELECT low_salary FROM salary_grades);查詢結(jié)果如下圖所示：在多行子查詢中使用ALLALL操作符要求比較列表中的所有值必須把=，=操作符放置在ALL之前例如，查詢滿足工資大于所有工資等級中最高工資的員工，這樣的員工顯然不存在SELECT employee_id, last_nameFROM employeesWHERE salary ALL (SELECT high_salary FROM salary_grades)

7、;查詢結(jié)果如下圖所示：多列子查詢子查詢還可以包含多列例如，通過多列子查詢查詢出每種商品的最低價，然后再查詢出滿足這些條件的商品信息SELECT product_id, product_type_id, name, priceFROM productsWHERE (product_type_id, price) IN (SELECT product_type_id, MIN(price) FROM products GROUP BY product_type_id);查詢結(jié)果如下圖所示：關(guān)聯(lián)子查詢子查詢引用與父查詢相關(guān)的一列或多列子查詢需要借助父查詢，而父查詢也需要借助子查詢子查詢需要為父查詢查

8、詢出的每一行運行一次例如，查詢出每類商品中，高出本類商品平均價格的商品SELECT product_id, product_type_id, name, priceFROM products outerWHERE price (SELECT AVG(price) FROM products inner WHERE duct_type_id = duct_type_id);查詢結(jié)果如下圖所示：關(guān)聯(lián)子查詢中使用EXISTS可以使用EXISTS操作符檢查是否存在由子查詢返回的行例如，查詢employees表中可以管理別的員工的員工SELECT employee_i

9、d, last_nameFROM employees outerWHERE EXISTS (SELECT employee_id FROM employees inner WHERE inner.manager_id = outer.employee_id);查詢結(jié)果如下圖所示：關(guān)聯(lián)子查詢中使用NOT EXISTS例如，查詢出未被售出過的商品信息注意子查詢中的1，由于只關(guān)心子查詢是否返回TRUE值，使用1可以提高查詢的效率；EXISTS子查詢效率高于IN子查詢SELECT product_id, nameFROM products outerWHERE NOT EXISTS (SELECT 1

10、 FROM purchases inner WHERE duct_id = duct_id);查詢結(jié)果如右圖所示：嵌套子查詢可以在子查詢中嵌套子查詢，最多可以嵌套255層例如SELECT product_type_id, AVG(price)FROM productsGROUP BY product_type_idHAVING AVG(price) 1) GROUP BY product_type_id)ORDER BY product_type_id;執(zhí)行效果如下圖所示：UPDATE和DELETE使用子查詢-1可以將子查詢用在UPDATE和DELETE語句

11、中可以在UPDATE語句中，使用單行子查詢返回的結(jié)果給列賦值例如，將employee_id為4的員工工資進行更新UPDATE employeesSET salary = (SELECT AVG(high_salary) FROM salary_grades)WHERE employee_id = 4;執(zhí)行結(jié)果如下圖所示：UPDATE和DELETE使用子查詢-2可以利用子查詢返回的結(jié)果給DELETE語句的WHERE條件進行DELETE操作例如，刪除工資高于平均工資的員工DELETE FROM employeesWHERE salary (SELECT AVG(high_salary) FROM

12、salary_grades);執(zhí)行結(jié)果如下圖所示：小結(jié)子查詢主要包含以下幾種單行子查詢多行子查詢多列子查詢關(guān)聯(lián)子查詢嵌套子查詢INSERT語句-1可以使用INSERT語句將數(shù)據(jù)插入到表中，需要指定以下信息插入到哪張表指定插入哪些列指定列清單對應(yīng)的一系列列值當插入數(shù)據(jù)時，需要給主鍵以及非空約束的列指定值?？梢詾镹ULL值的列可以不指定值。例如，可以使用DESCRIBE命令查看列的情況DESCRIBE customers查詢效果如下圖所示：INSERT語句-2例如，使用INSERT語句往customers表中插入數(shù)據(jù)，指定相關(guān)列和值INSERT INTO customers ( customer_

13、id, first_name, last_name, dob, phone) VALUES ( 6, Fred, Brown, 01-1月-1970, 800-555-1215);查詢customers表，查看結(jié)果集省略列清單如果為表所有列都指定值，那么可以忽略列清單例如，為customers表插入一條記錄INSERT INTO customersVALUES (7, Jane, Green, 01-1月-1970, 800-555-1216);執(zhí)行結(jié)果如下圖所示：說明如果省略列清單的話，那么值的指定順序必須與DESCRIBE命令看到的列清單的順序保持一致。為列指定NULL值可以使用NULL為

14、某些列指定空值例如，往customers表中插入記錄，為dob和phone指定NULL值INSERT INTO customersVALUES (8, Sophie, White, NULL, NULL);再將customer_id為8的用戶查詢出來SELECT *FROM customersWHERE customer_id = 8;執(zhí)行效果如下圖所示：值包含單引號和雙引號列值中可以包含單引號或雙引號例如，往customers表中插入last_name包含單引號的OMalleyINSERT INTO customersVALUES (9, Kyle, OMalley, NULL, NULL)

15、;插入產(chǎn)品名包含雙引號的：The Great GatsbyINSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 13, 1, The Great Gatsby, NULL, 12.99);執(zhí)行效果如右圖所示：將行從一個表復制到另一個表可以使用INSERT語句將從一個表查詢出來的行插入到另外一張表。源表的列的數(shù)量和列類型必須和目標表必須相符。 例如，將customers表中的1號用戶查詢出來，然后插入到customers表中，并為其指定customer_id為10INSERT

16、INTO customers (customer_id, first_name, last_name)SELECT 10, first_name, last_nameFROM customersWHERE customer_id = 1;執(zhí)行效果如下圖所示：使用UPDATE修改表-1可以使用UPDATE修改表中的行。當使用UPDATE時，需要指定以下信息表名需要修改的列需要用WHERE子句指定條件列名的清單及新值例如更新customers表中customer_id為2的客戶last_nameUPDATE customersSET last_name = OrangeWHERE customer_id = 2;查詢結(jié)果如下圖所示：使用UPDATE修改表-2在一個UPDATE語句中可以修改多列例如，將products表中商品價格大于等于20的提高20%，并將