子查询
子查询是另一个 SQL 查询中的查询。子查询也称为内部查询或内部选择,包含子查询的语句称为外部查询或外部选择。
注意
- 子查询必须括在括号内,
- ORDER BY 不能在子查询中使用。
- 子查询中不允许使用 BLOB,数组,文本数据类型等图像类型。
子查询可以与 where,from,select 子句以及 IN,比较运算符等一起使用 select,insert,update 和 delete 语句。
我们有一个名为 ITCompanyInNepal 的表,我们将在其上执行查询以显示子查询示例:
示例: 使用 Select 语句的子查询
使用 In 运算符和 where 子句:
SELECT *
FROM ITCompanyInNepal
WHERE Headquarter IN (SELECT Headquarter
FROM ITCompanyInNepal
WHERE Headquarter = 'USA');
with comparison 运算符和 where 子句
SELECT *
FROM ITCompanyInNepal
WHERE NumberOfEmployee < (SELECT AVG(NumberOfEmployee)
FROM ITCompanyInNepal
)
用 select 子句
SELECT CompanyName,
CompanyAddress,
Headquarter,
(Select SUM(NumberOfEmployee)
FROM ITCompanyInNepal
Where Headquarter = 'USA') AS TotalEmployeeHiredByUSAInKathmandu
FROM ITCompanyInNepal
WHERE CompanyAddress = 'Kathmandu' AND Headquarter = 'USA'
带有 insert 语句的子查询
我们必须将 IndianCompany 表中的数据插入 ITCompanyInNepal。IndianCompany 的表格如下所示:
INSERT INTO ITCompanyInNepal
SELECT *
FROM IndianCompany
带有 update 语句的子查询
假设由于美国公司的政策发生了一些变化,所有总部位于美国的公司决定从尼泊尔的所有美国公司解雇 50 名员工。
UPDATE ITCompanyInNepal
SET NumberOfEmployee = NumberOfEmployee - 50
WHERE Headquarter IN (SELECT Headquarter
FROM ITCompanyInNepal
WHERE Headquarter = 'USA')
带有 Delete 语句的子查询
假设总部位于丹麦的所有公司都决定将其公司从尼泊尔关闭。
DELETE FROM ITCompanyInNepal
WHERE Headquarter IN (SELECT Headquarter
FROM ITCompanyInNepal
WHERE Headquarter = 'Denmark')