Android programmers must master the sqlite database connection Table query

Android programmers must master the sqlite database connection Table query

Basic Principles of SQL query: two cases are introduced. 1. Single Table query: filter the records in the table based on the WHERE condition to form an intermediate table (this intermediate table is invisible to users ); then SELECT the corresponding Column Based on the SELECT column to return the final result. 2. join queries for two tables: product (Cartesian Product) of the two tables and filter using the ON condition and connection type to form an intermediate table. Then, filter records of the intermediate table based ON the WHERE condition, return the query result based on the column specified by SELECT. 3. Multi-Table connection query: perform queries on the first and second tables based on the two-Table connection, and then perform connection queries using the query results and the third table, and so on, until all the tables are connected, an intermediate result table is formed. Then, records of the intermediate table are filtered Based on the WHERE condition, and query results are returned Based on the columns specified by SELECT. Understanding the SQL Query Process is the theoretical basis for SQL optimization.
One cross join statement 1: Implicit cross join, no cross join. Select tt. ID, TT. ORDER_NUMBER, TP. ID, TP. namefrom table tt, portable tp where tt. ID = 1; 4 rows of Data Statement 2: explicit cross join. Select tt. ID, TT. ORDER_NUMBER, TP. ID, TP. namefrom table tt, portable tp where tt. ID = 1; the results of Statement 1 and Statement 2 are the same, with 2*2 data.
Ii. inner join was originally built using sqlite, and now I am used to NaviCat. Inner join: There are two types of explicit and implicit data rows that meet the connection conditions and query conditions in the connection table. (The so-called chain table is the intermediate table formed by the database for query ). For example, the following statements 3 and 4 have the same results. Statement 3: Implicit inner join without inner join. The intermediate table is the Cartesian product of the two tables. Select o. ID, O. ORDER_NUMBER, C. ID, C. namefrom customers c, orders owhere c. ID = O. CUSTOMER_ID; Statement 4: The displayed inner join, which is generally called an inner join. The intermediate table is the Cartesian product of the two tables filtered by the ON condition. Select o. ID, O. ORDER_NUMBER, C. ID, C. NAMEFROM MERs c inner join orders o on c. ID = O. CUSTOMER_ID; Statement 5: select. name, B. qty from student a, (select id, count (*) qty from student group id) B where. id = B. id;

Statement 6: at work, you must write method 2 as method 1.
Method 1 saves a lot of time.
Inner join ...... ON syntax format:
 
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.bkjia.com
 
Syntax 2: large data volumes consume a lot of time.
 
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. let's add a small example. Here we use the nested select statement and query result as a table B to query select. product NO.,. initial quantity + B. numfrom Warehouse a, (select product number, sum (Quantity) as num from danju group by product number) as B, where. product NO. = B. product NO.

Summary
Connection query is the core of SQL query. The connection type of connection query depends on actual needs. Improper selection will not improve the query efficiency, but will lead to some logical errors or low performance. The following describes the basis for the Two-Table connection query selection method:

1. query the data with the same association columns in the two tables for internal join.
2. When Col_L is a child of Col_R, use outer right join.
3. Col_R is a child set of Col_L connected to the left outer.
4. Col_R and Col_L have an intersection with each other, but they are not a subset of each other.
5. Perform the Union Query when performing the difference operation.
When querying multiple tables, these different connection types can be written into one 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 join TAB4 ON (T2.C2 = T3.C3 );
WHERE T1.X> T3.Y;
The preceding SQL query is a demonstration of Multi-table join.