子查询、更改表的内容课件

1、数据库操作与管理语言Oracle SQL 第08章：子查询、更改表内容本章目标子查询子查询的分类插入数据更新数据子查询及分类子查询是嵌入到另一个SELECT语句中的一个SELECT语句。通过使用子查询，可以使用简单的语句组成强大的语句。当需要从表中选择行，而选择条件却取决于该表自身中的数据时，子查询非常有用。子查询主要分为以下两种基本类型单行子查询多行子查询另外，还有以下3种类型多列子查询关联子查询嵌套子查询单行子查询单行子查询内部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()聚集函数SELECT product_id, name, priceFROM productsWHERE price (SELECT AVG(price)

3、 FROM products);查询结果如下图所示：在HAVING子句中使用子查询HAVING是在分组统计后用于过滤行，同样在HAVING子句后面可以跟子查询。单行子查询将返回结果用于HAVING子句过滤分组统计的行例如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;查询结果如下图所示：在FRO

4、M子句中的子查询-1将子查询用于FROM子句的数据源。将子查询放置于FROM子句之后为其提供数据。例如SELECT product_idFROM (SELECT product_id FROM products WHERE product_id (SELECT AVG(price) FROM products)ORDER BY product_id DESC;查询结果如下图所示：多行子查询多行子查询内部SELECT语句给外部的SELECT语句返回1行或多行。在父查询中可以使用IN、ANY或ALL操作符例如SELECT product_id, nameFROM productsWHERE pro

5、duct_id IN (1, 2, 3);查询结果如下所示：在多行子查询中使用ININ是用来检查一个的值是否在指定的列表中。可以使用子查询来返回这个列表的值。同样可以使用NOT IN检查一个值是否不在指定的列表中。例如，查询SELECT product_id, nameFROM productsWHERE product_id IN(SELECT product_id FROM products WHERE name LIKE %e%);查询结果如下图所示：在多行子查询中使用ANY可以使用ANY操作符比较列表中的任意一个值。必须把=，=操作符放置在ANY之前例如，使用ANY查询任意满足工资低于

6、最低档工资的员工信息SELECT employee_id, last_nameFROM employeesWHERE salary ANY (SELECT low_salary FROM salary_grades);查询结果如下图所示：在多行子查询中使用ALLALL操作符要求比较列表中的所有值必须把=，=操作符放置在ALL之前例如，查询满足工资大于所有工资等级中最高工资的员工，这样的员工显然不存在SELECT employee_id, last_nameFROM employeesWHERE salary ALL (SELECT high_salary FROM salary_grades)

7、;查询结果如下图所示：多列子查询子查询还可以包含多列例如，通过多列子查询查询出每种商品的最低价，然后再查询出满足这些条件的商品信息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);查询结果如下图所示：关联子查询子查询引用与父查询相关的一列或多列子查询需要借助父查询，而父查询也需要借助子查询子查询需要为父查询查

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);查询结果如下图所示：关联子查询中使用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);查询结果如下图所示：关联子查询中使用NOT EXISTS例如，查询出未被售出过的商品信息注意子查询中的1，由于只关心子查询是否返回TRUE值，使用1可以提高查询的效率；EXISTS子查询效率高于IN子查询SELECT product_id, nameFROM products outerWHERE NOT EXISTS (SELECT 1

10、 FROM purchases inner WHERE duct_id = duct_id);查询结果如右图所示：嵌套子查询可以在子查询中嵌套子查询，最多可以嵌套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;执行效果如下图所示：UPDATE和DELETE使用子查询-1可以将子查询用在UPDATE和DELETE语句

11、中可以在UPDATE语句中，使用单行子查询返回的结果给列赋值例如，将employee_id为4的员工工资进行更新UPDATE employeesSET salary = (SELECT AVG(high_salary) FROM salary_grades)WHERE employee_id = 4;执行结果如下图所示：UPDATE和DELETE使用子查询-2可以利用子查询返回的结果给DELETE语句的WHERE条件进行DELETE操作例如，删除工资高于平均工资的员工DELETE FROM employeesWHERE salary (SELECT AVG(high_salary) FROM

12、salary_grades);执行结果如下图所示：小结子查询主要包含以下几种单行子查询多行子查询多列子查询关联子查询嵌套子查询INSERT语句-1可以使用INSERT语句将数据插入到表中，需要指定以下信息插入到哪张表指定插入哪些列指定列清单对应的一系列列值当插入数据时，需要给主键以及非空约束的列指定值。可以为NULL值的列可以不指定值。例如，可以使用DESCRIBE命令查看列的情况DESCRIBE customers查询效果如下图所示：INSERT语句-2例如，使用INSERT语句往customers表中插入数据，指定相关列和值INSERT INTO customers ( customer_

13、id, first_name, last_name, dob, phone) VALUES ( 6, Fred, Brown, 01-1月-1970, 800-555-1215);查询customers表，查看结果集省略列清单如果为表所有列都指定值，那么可以忽略列清单例如，为customers表插入一条记录INSERT INTO customersVALUES (7, Jane, Green, 01-1月-1970, 800-555-1216);执行结果如下图所示：说明如果省略列清单的话，那么值的指定顺序必须与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;执行效果如下图所示：值包含单引号和双引号列值中可以包含单引号或双引号例如，往customers表中插入last_name包含单引号的OMalleyINSERT INTO customersVALUES (9, Kyle, OMalley, NULL, NULL)

15、;插入产品名包含双引号的：The Great GatsbyINSERT INTO products ( product_id, product_type_id, name, description, price) VALUES ( 13, 1, The Great Gatsby, NULL, 12.99);执行效果如右图所示：将行从一个表复制到另一个表可以使用INSERT语句将从一个表查询出来的行插入到另外一张表。源表的列的数量和列类型必须和目标表必须相符。 例如，将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;执行效果如下图所示：使用UPDATE修改表-1可以使用UPDATE修改表中的行。当使用UPDATE时，需要指定以下信息表名需要修改的列需要用WHERE子句指定条件列名的清单及新值例如更新customers表中customer_id为2的客户last_nameUPDATE customersSET last_name = OrangeWHERE customer_id = 2;查询结果如下图所示：使用UPDATE修改表-2在一个UPDATE语句中可以修改多列例如，将products表中商品价格大于等于20的提高20%，并将