Sqlite 简明教程
SQLite - Subqueries
子查询或内部查询或嵌套查询是另一个 SQLite 查询中的查询，嵌入在 WHERE 子句中。
A Subquery or Inner query or Nested query is a query within another SQLite query and embedded within the WHERE clause.
一个子查询用于返回在主查询中的某个条件中将被用于进一步限制要检索的数据的数据。
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句以及 =、<、>、>=、⇐、IN、BETWEEN 等运算符一起使用。
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators such as =, <, >, >=, ⇐, IN, BETWEEN, etc.
有一些规则，子查询必须遵守 −
There are a few rules that subqueries must follow −
-
Subqueries must be enclosed within parentheses.
-
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
-
An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
-
Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
-
BETWEEN operator cannot be used with a subquery; however, BETWEEN can be used within the subquery.
Subqueries with SELECT Statement
子查询最常与SELECT语句一起使用。基本语法如下所示：
Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])Example
考虑 COMPANY 表和以下记录。
Consider COMPANY table with the following records.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0现在，让我们用 SELECT 语句检查一下以下子查询。
Now, let us check the following sub-query with SELECT statement.
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;这将产生以下结果。
This will produce the following result.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Subqueries with INSERT Statement
子查询也能与 INSERT 语句一起使用。INSERT 语句使用从子查询中返回的数据来插入另一个表。子查询中的选定数据可以用任何字符、日期或数字函数进行修改。
Subqueries can also be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.
以下是基本语法 −
Following is the basic syntax is as follows −
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]Example
考虑一个表 COMPANY_BKP 与 COMPANY 表具有相似的结构，并且可以使用相同的 CREATE TABLE 同时使用 COMPANY_BKP 作为表名来创建。为了将整个 COMPANY 表复制到 COMPANY_BKP 中，以下是该语法 −
Consider a table COMPANY_BKP with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. To copy the complete COMPANY table into COMPANY_BKP, following is the syntax −
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;Subqueries with UPDATE Statement
子查询可以与 UPDATE 语句联合使用。在使用 UPDATE 语句与子查询时，可以更新表中的单个或多个列。
The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.
以下是基本语法 −
Following is the basic syntax is as follows −
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]Example
假设我们有可用的 COMPANY_BKP 表，它是 COMPANY 表的备份。
Assuming, we have COMPANY_BKP table available which is a backup of COMPANY table.
以下示例对 COMPANY 表中所有年龄大于或等于 27 的客户更新了 SALARY，乘以 0.50。
Following example updates SALARY by 0.50 times in COMPANY table for all the customers, whose AGE is greater than or equal to 27.
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );这会影响两行，最后 COMPANY 表会有以下记录 −
This would impact two rows and finally COMPANY table would have the following records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 42500.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Subqueries with DELETE Statement
子查询可以与 DELETE 语句一起使用，就像与上面提到的任何其他语句一样。
Subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.
以下是基本语法 −
Following is the basic syntax is as follows −
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]Example
假设我们有可用的 COMPANY_BKP 表，它是 COMPANY 表的备份。
Assuming, we have COMPANY_BKP table available which is a backup of COMPANY table.
以下示例为所有年龄大于或等于 27 的客户从 COMPANY 表中删除记录。
Following example deletes records from COMPANY table for all the customers whose AGE is greater than or equal to 27.
sqlite> DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );这会影响两行，最后 COMPANY 表会有以下记录 −
This will impact two rows and finally COMPANY table will have the following records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 42500.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0