Study database notes 5. Database notes

Study database notes 5. Database notes

I

Where subquery

The inner query result acts as the outer query condition.

1. query the latest product

Step a) select max (goods_id) from goods; this is a dynamic statement, and how to add it will be up-to-date. It finds the latest goods_id column.

Step B) query the full line information of the latest product. Use the preceding statement as a condition.

Select goods_id, goods_name from goods where goods_id = (select max (goods_id) from goods );

Step c) the result is "goods_id", which is the record of the latest row.

* If a problem occurs, the problem can be solved step by step.

2. query the latest products under each topic.

Step a) select max (goods_id) from goods group by cat_id; find the latest item ID in each column

Step B) Check the id as the condition and then

Select * from goods where goods_idIn(Select max (goods_id) from goods group by cat_id); use In because there are more than one condition

From subquery

The queried result set can be viewed as a table.

In normal times, the from clause is followed by a table. In this case, the from clause can treat the entire query as a table.

(Generally, the result we query is only a result set in the memory, rather than the content on the hard disk. We can use having to query it)

A query statement, enclosed in parentheses, uses an alias and becomes a "new" temporary table, as shown in figure

(Select * from goods order by cat_id asc, goods_id desc) as temp converts the query result to a new temp table.This technique is useful!

When using this temporary table,

Select goods_id, cat_id, goods_name from (select * from goods order by cat_id asc, goods_id desc) as temp group by cat_id;

A temporary table only needs to add parentheses to your select statement to write an as and table name. It is very useful to remember the preceding method.