Shulou(Shulou.com)05/31 Report--

This article introduces how to achieve query and sub-query in mysql, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

I. five clauses of the query

Where (conditional query), having (filtering), group by (grouping), order by (sorting),

Limit (limit the number of results)

1. Where common operators:

Comparison operator

>

), > =, = 3000 and price = 500 and price 200

/ / where cannot be used here because s is the result of the query, and where can only filter the field names in the table

If you use where, it is:

Select goods_id,goods_name from goods where market_price-shop_price > 200

# using both where and having

Select cat_id,goods_name,market_price-shop_price as s from goods where cat_id = 3 having s > 200

# query the column with a backlog of more than 20,000 yuan, and the backlog of payments in this column

Select cat_id,sum (shop_price * goods_number) as t from goods group by cat_id having s > 20000

# query the average scores of students who fail in two or more subjects

Train of thought:

# calculate the average score of all students first

Select name,avg (score) as pj from stu group by name

# find out the failing status of all the students

Select name,score=2) as t

# find out these students, then calculate their average score

Select name,avg (score) from stu where name in

(select name from (select name,count (*) as gk from stu having gk > = 2) as t) group by name

3. Subquery of subquery of's type

(take the outer query result to the inner layer to see if the inner query is valid.)

# query which columns have goods, column table category, merchandise table goods

Select cat_id,cat_name from category where exists

(select * from goods where goods.cat_id = category.cat_id); www.2cto.com

III. The usage of union

(merge the results of two or more queries, requiring the same number of columns

It is recommended that the corresponding column types of the query are the same. You can query multiple tables if the column names are different when querying statements multiple times.

Take the column name for the first time! If each column of a row fetched from a different statement has the same value, the result will be repeated automatically

If you don't want to repeat it, add all to declare it, that is, union all)

# # Table an is as follows

Id num

A 5

B 10

C 15

D 10

Table b is as follows

Id num

B 5

C 10

D 20

E 99

Find the sum of the same id in two tables

Select id,sum (num) from (select * from ta union select * from tb)

As tmp group by id

/ / the above query results do output correctly in this example, but if you put the value of b in tb

Change to 10 so that the value of b of the query result is 10, because the b in ta is also 10, so union will filter out a duplicate result.

At this point, use union all.

Select id,sum (num) from (select * from ta union all select * from tb)

As tmp group by id

# take the commodities in columns 4 and 5, arrange them in ascending order, and arrange the commodity prices in descending order in each column, complete with union

Select goods_id,goods_name,cat_id,shop_price from goods

Where cat_id=4 union select goods_id,goods_name,cat_id,shop_price from goods

Where cat_id=5 order by cat_id,shop_price desc

[if there is an order by in the clause, it needs to be wrapped with (), but it is recommended to use order by at the end.

That is, to sort the results after the final merger]

# take the 3rd and 4th columns, the top 3 commodities with the highest price in each column, and the results are arranged in descending order

(select goods_id,goods_name,cat_id,shop_price from

Goods where cat_id=3 order by shop_price desc limit 3) union (select goods_id,goods_name,cat_id,shop_price from goods where cat_id=4 order by

Shop_price desc limit 3) order by shop_price desc

Left connection, right connection, inner connection www.2cto.com

There are 10 pieces of data in table an and 8 pieces of data in table b. What is the Cartesian product of table an and table b?

Select * from ta,tb / / output result is 8100.80

1. Left connection

According to the left table, go to the right table to find the data. If there is no matching data, use null to fill the gap.

So the number of output results > = the number of original data in the left table

Grammar: select N1, n2, from ta left join tb on ta.n1= ta.n2 n3

[the expression after on here is not necessarily =, but can also be >.

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.