A list of SQLite database queries that Android programmers must master

Fundamentals of SQL queries: two scenarios. First, single-table query: Filter the records in the table according to where conditions, form an intermediate table (this intermediate table is not visible to the user), and then select the corresponding column according to the Select column of select to return the final result. Second, two-table connection query: The two-table quadrature (Cartesian product) with on conditions and connection type filtering to form an intermediate table; then the records of the intermediate table are filtered based on the Where condition and the query results are returned based on the column specified by select. Third, multi-table connection query: First and second table according to two table connection query, and then use the query results and the third table to make a connection query, and so on, until all the tables are connected, and eventually form an intermediate result table, and then filter the records of the intermediate table according to where conditions, and returns the result of the query based on the column specified by select. The process of understanding SQL query is the theoretical basis for SQL optimization.
Cross JOIN Statement 1: Implicit cross-joins, no crosses joins. SELECT tt.id, TT. Order_number, Tp.id, TP. Namefrom TABLE TT, portable TP WHERE tt.id=1; get four rows of data statement 2: Explicit cross joins, using the Intersect join. SELECT Tt.id,tt. ORDER_NUMBER,TP.ID,TP. Namefrom TABLE TT, portable TP WHERE tt.id=1; The result of statement 1 and statement 2 is the same, 2*2 data.
Second, the internal connection (INNER join) was originally built with SQLite, now accustomed to use Navicat. INNER JOIN (INNER join): There are two types, explicit and implicit, that return data rows in the join table that meet the join criteria and query criteria. (The so-called link table is the database in the form of queries formed in the intermediate table). For example: The result of the following statement 3 and statement 4 is the same. Statement 3: Implicit inner join, without inner join, forms a Cartesian product of two tables. SELECT o.id,o.order_number,c.id,c.namefrom CUSTOMERS c,orders owhere c.id=o.customer_id; Statement 4: Internal connections shown, generally called Inner joins, inner JOIN, the resulting intermediate table is a Cartesian product with two tables filtered by on condition. Select O.id,o.order_number,c.id,c.namefrom CUSTOMERS C INNER JOIN ORDERS O on c.id=o.customer_id; Statement 5: Look directly at example select A.name, B.qty from Student A, (select Id,count (*) Qty from student Group ID) b where a.id =b.id;

Statement 6: At work, be sure to put Method 2, write Method 1
Method one saves a lot of time.
INNER JOIN ... The syntax format for on is:

From ((Table 1 INNER join table 2 on table 1. Field number = Table 2. Field number) INNER JOIN table 3 on table 1. Field number = Table 3. Field number)
INNER join table 4 on Member. Field number = Table 4. Field number) INNER join table X on Member. Field number = Table X. Field number www.2cto.com

Syntax Two: Large amounts of data can be time consuming.

From table 1

INNER JOIN table 2 on table 1. Field number = Table 2. Field number

INNER JOIN table 3 on table 1. Field number = Table 3. Field number

INNER JOIN table 4 on Member. Field number = Table 4. Field number

INNER JOIN table X on Member. Field number = table x. Field number

Statement 7. Add a small example here the nested SELECT statement, query results, as a table B, to query    Select a. Product number, A. Initial quantity + B.numfrom  Warehouse A, (select product number, SUM (quantity) as num from Danju  GROUP by product number As b,where A. Product number =b. Product number

Summarize
The connection query is the core of the SQL query, and the connection type of the connection query is selected according to actual requirements. If you choose improperly, not only can not improve query efficiency, but will bring some logic errors or poor performance. Here is a summary of the two-table connection query selection method based on:

1, check two tables related columns equal data with the internal connection.
2, col_l is a subset of the Col_r when using the right outer connection.
3, Col_r is a subset of col_l with left outer connection.
4, Col_r and col_l each other has the intersection but each other is not a subset of time with the whole outside.
5, the differential operation when using a joint query.
When querying multiple tables, these different connection types can be written to a piece. For example:
SELECT T1. C1,t2. Cx,t3.cy
From TAB1 T1
INNER JOIN TAB2 T2 on (T1. C1=t2. C2)
INNER JOIN TAB3 T3 on (T1. C1=t2. C3)
Left OUTER joins TAB4 on (T2. C2=t3. C3);
WHERE T1. X >t3. Y
The above SQL query is a demonstration of multi-table joins.

A list of SQLite database queries that Android programmers must master