MySQL Learning Note (ii)-Query

One, multi-table connection query

New two sheets t_user, T_order.

1. Internal connection

Returns all records that meet the criteria.

(1) Explicit in-connection

Use the inner JOIN keyword to set the join condition in the ON clause.

Selectu.id,u.name,u.address,o.ordernofromt_user uinner JOIN t_order o on u.id = O.userid;

Results:

(2) Implicit in-connection

Does not contain the inner join and on keywords, setting the join condition in the WHERE clause.

Selectu.id,u.name,u.address,o.ordernofromt_user U,t_order owhereu.id = O.userid;

Results:

2. External connection

(1) Left outer connection

A: Returns all records that satisfy the join condition, and also returns other records remaining in the left table.

B: Using the left OUTER JOIN keyword, set the join condition in the ON clause, which can be added to the WHERE clause.

Selectu.id,u.name,u.address,o.ordernofromt_user uleft OUTER JOIN t_order o on u.id = O.userid;

Results:

(2) Left outer connection

A: Returns all records that satisfy the join condition, and also returns other records remaining in the right table.

B: Using the left OUTER JOIN keyword, set the join condition in the ON clause, which can be added to the WHERE clause.

Selectu.id,u.name,u.address,o.ordernofromt_user uright OUTER JOIN t_order o on u.id = O.userid;

Results:

Fundamentals of 3.SQL queries

(1) Single-table query

Filters the records in the table according to where conditions, forming an intermediate table (this intermediate table is invisible to the user). The final result is returned by selecting the appropriate column based on the Select column of the SELECT.

(2) Two-table connection query

The two-table quadrature (Cartesian product) is filtered by the on condition and the connection type to form the 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.

(3) Multi-table connection query

The first and second tables are queried by two table joins, then the query results and the third table are used for the connection query, and so on until all the tables are connected, eventually forming an intermediate result table, then filtering the records of the intermediate table based on the where condition, and returning the query results based on the column specified by select.
The process of understanding SQL query is the theoretical basis for SQL optimization.

(4) difference between on condition and where condition

On condition: is to filter two link table Cartesian product form the constraint condition of the intermediate table.
WHERE Condition: in a SELECT statement with an on condition, the constraint that filters the intermediate table. In a single-table query that does not have on, it is a constraint that restricts the return of records to physical tables or intermediate query results. In a two-table or multiple-table connection, a constraint that restricts the return result of the connection to form the final intermediate table.
As you can see from here, it is inappropriate to move the where condition into the back. The recommended practice is to connect only, where only the records of the intermediate table are filtered.

Second, sub-query

A subquery is also called a nested query, which is embedded in a SELECT clause or in a WHERE clause and a SELECT query statement.

1.where Sub-query

The inner query result is treated as the comparison condition of the outer query.

#不用order by to find the latest commodity select goods_id,goods_name from goods where goods_id = (select Max (goods_id) from goods);                #取出每个栏目下最新的产品 (goods_id only) Select Cat_id,goods_id,goods_name from goods where goods_id in (select Max (goods_id) from goods Group by cat_id);

2.from Sub-query

The query results for the inner layer are queried again for the outer layers.

# # #用子查询查出挂科两门及以上的同学的平均成绩 # # #先查出哪些同学挂科两门以上select Name,count (*) as GK from Stu where score < have GK >=2; #以上查询结果,  We just have to name it, so we take the name again. Select name from (select Name,count (*) as GK from Stu have GK >=2) as T; #找出这些同学了, then calculate their average sub-select Name,avg (Score) from Stu, where name in (select name from (select Name,count (*) as GK from Stu have GK >=2) as T) Gro up by name;

MySQL Learning Note (ii)-Query