最近几年来，地理信息系统无论是在理论上还是应用上都处在一个飞速发展的阶段。 GIS被应用于多个领域的建模和决策支持，如城市管理、区划、环境整治等等，地理信息成为信息时代重要的组成部分之一； “数字地球”概念的提出，更进一步推动了作为其技术支撑的GIS的发展。 与此同时，一些学者致力于相关的理论研究，如空间感知、空间数据误差、空间关系的形式化等等。 这恰好说明了地理信息系统作为应用技术和学科的两个方面，并且这两个方面构成了相互促进的发展过程。
Subqueries, or internal queries, or nested queries, refer to the SQLite In the query WHERE The query statement is embedded in the clause.
One SELECT The query result of a statement can be used as an input value for another statement.
A subquery can be associated with the SELECT 、 INSERT 、 UPDATE And DELETE Statements can be accompanied by operators such as =, <, >, > =, < =, IN, BETWEEN, and so on.
Here are a few rules that subqueries must follow:
Subqueries must be enclosed in parentheses.
The subquery is in the
SELECTThere can be only one column in the clause, unless there are multiple columns in the main query, compared to the selected column of the subquery.ORDER BYCannot be used in subqueries, although the main query can use theORDER BY. Can be used in subqueriesGROUP BY, features vs.ORDER BYThe same.The subquery returns more than one row and can only be used with multivalued operators, such as the IN operator.
BETWEENOperators cannot be used with subqueries, butBETWEENcan be used within subqueries.
1.40.1. SELECT Subqueries in statements use the ¶
Subqueries are usually associated with SELECT Statement is used together. 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]) 1.40.2. Example ¶
Hypothetical COMPANY The table has 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 Now, let’s check SELECT Statement uses:
sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ; This will produce the following results:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 1.40.3. INSERT Subqueries in statements use the ¶
Subqueries can also be associated with INSERT Statement is used together. The INSERT statement inserts the data returned by the subquery into another table. The data selected in the subquery can be modified with any character, date, or number function.
The basic syntax is as follows:
INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ] 1.40.4. Example ¶
Hypothetical COMPANY_BKP The structure of the COMPANY Tables are similar, and you can use the same CREATE TABLE To create, but the table name is changed to COMPANY_BKP . Now put the whole COMPANY The table is copied to COMPANY_BKP The syntax is as follows:
sqlite> INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ; 1.40.5. Subqueries in UPDATE statements use the ¶
A subquery can be associated with the UPDATE Statement is used in combination. When passed UPDATE Statement uses a subquery, one or more columns in the table are updated.
The basic syntax is as follows:
UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] 1.40.6. Example ¶
Hypothetically, we have COMPANY_BKP Table, yes COMPANY A backup of the table.
The following example sets the COMPANY All in the table AGE For customers greater than or equal to 27 SALARY Updated to 0.50 times the original:
sqlite> UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 ); This will affect two lines, and finally COMPANY The records in the table are as follows:
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.0 1.40.7. DELETE Subqueries in statements use the ¶
A subquery can be associated with the DELETE Statement, just like the other statements mentioned above.
The basic syntax is as follows:
DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ] 1.40.8. Example ¶
Hypothetically, we have COMPANY_BKP Table, yes COMPANY A backup of the table.
The following example is deleted COMPANY All in the table AGE Customer records greater than or equal to 27:
sqlite> DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 ); This will affect two lines, and finally COMPANY The records in the table are as follows:
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