Shulou(Shulou.com)06/01 Report--

This article "SQL sub-query how to use" most people do not understand, so the editor summed up the following content, detailed, clear steps, with a certain reference value, I hope you can get something after reading this article, let's take a look at this "SQL sub-query how to use" article.

SQL subquery, or internal query or nested query, refers to embedding query statements in the WHERE clause of a SQLite query, and the query result of one SELECT statement can be used as the input value of another statement.

Subqueries in SELECT statements use the

Subqueries are usually used with SELECT statements. 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

Suppose the COMPANY table has the following records:

ID NAME AGE ADDRESS SALARY--1 Paul 32 California 20000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0

Now, let's examine the subquery usage in the SELECT statement:

Sqlite > SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000)

This will produce the following results:

Subqueries in ID NAME AGE ADDRESS SALARY--4 Mark 25 Rich-Mond 65000.05 David 27 Texas 85000.0INSERT statements use

Subqueries can also be used with INSERT statements. 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]

Example

Assume that the structure of the COMPANY_BKP is similar to the COMPANY table and can be created using the same CREATE TABLE, except that the table name is changed to COMPANY_BKP. Now copy the entire COMPANY table to COMPANY_BKP with the following syntax:

Sqlite > INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY); subqueries in the UPDATE statement use the

Subqueries can be used in conjunction with UPDATE statements. When subqueries are used through UPDATE statements, single or multiple columns in the table are updated.

The basic syntax is as follows:

UPDATE tableSET column_name = new_value [WHERE OPERATOR [VALUE] (SELECT COLUMN_NAME FROM TABLE_NAME) [WHERE)]

Example

Suppose we have the COMPANY_BKP table, which is a backup of the COMPANY table.

The following example updates the SALARY of all customers in the COMPANY table whose SALARY is greater than or equal to 27 to 0.50x:

Sqlite > UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > = 27)

This affects two rows, and the final record in the COMPANY table is as follows:

ID NAME AGE ADDRESS SALARY--1 Paul 32 California 10000.02 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 42500.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0DELETE statements use subqueries

Subqueries can be used in conjunction with DELETE statements, 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)]

Example

Suppose we have the COMPANY_BKP table, which is a backup of the COMPANY table.

The following example deletes all customer records with AGE greater than or equal to 27 in the COMPANY table:

Sqlite > DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27)

This affects two rows, and the final record in the COMPANY table is as follows:

ID NAME AGE ADDRESS SALARY--2 Allen 25 Texas 15000.03 Teddy 23 Norway 20000.04 Mark 25 Rich-Mond 65000.05 David 27 Texas 42500.06 Kim 22 South-Hall 45000.07 James 24 Houston 10000.0 is the content of this article on how to use SQL subqueries I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.