MySQL Sub-query

Reprint: http://www.cnblogs.com/hellokitty1/p/4194841.html

--Product Category table--
CREATE TABLE Category (
cat_id INT PRIMARY KEY auto_increment, #类别编号
Cat_name VARCHAR (in) not null# category name
);

--Commodity table--
CREATE TABLE Goods (
goods_id INT PRIMARY KEY auto_increment, #商品编号
Goods_name VARCHAR (+) not NULL, #商品名称
Goods_price DOUBLE, #商品进价
Shop_price DOUBLE, #商品卖价
Market_price DOUBLE, #市场价
cat_id INT, #商品类别
Goods_number INT, #商品数量
FOREIGN KEY (cat_id) REFERENCES category (CAT_ID)
);

One, MySQL query five of the seed sentence

where (conditional query), having (filter), GROUP by (grouping), order by (sort), limit (limits the number of results)

1. Where commonly used operators:
Comparison operators
>, <, =,! = (< >), >=, <=
In (V1,v2. Vn
Between V1 and V2 between V1 to V2 (including V1,V2)
logical operators
Not (!) logical Non-
or (| |) Logical OR
and (&&) logic and

where price>=3000 and price <= the or price >=500 and the price <=1000
Take a value of 500-1000 or 3000-5000
where price not between and 5000
Values that are not between 3000 and 5000

Fuzzy query
Like
Wildcard characters:
% any character
_ Single Character
where Goods_name like ' Nokia% '
where Goods_name like ' Nokia n__ '

2. GROUP BY group
In general, it makes sense for a group to be used with statistical functions (aggregate functions).
such as: Select Goods_id,goods_name,cat_id,max (shop_price) from goods group by CAT_ID;
The goods_name in the results here is wrong! Because Shop_price uses the Max function, it is
And the group by group is used in the statement, then Goods_name does not use an aggregate function,
It is only the first product under CAT_ID, and will not change because of shop_price change
Five statistical functions in MySQL:
(1) Max: Ask for maximum value
Select Max (goods_price) from goods
This will take out the value of the maximum price, only the value
#查询每个栏目下价格最高的
Select Cat_id,max (goods_price) from goods group by CAT_ID;
#查出价格最高的商品编号
Select Goods_id,max (goods_price) from goods group by goods_id;


(2) Min: To find the minimum value
(3) Sum: Total and
#求商品库存总和
Select SUM (goods_number) from goods;
(4) Avg: averaging
#求每个栏目的商品平均价格
Select Cat_id,avg (goods_price) from goods group by CAT_ID;
(5) Count: Total number of rows
#求每个栏目下商品种类
Select Cat_id,count (*) from goods group by CAT_ID;

# # # #要把每个字段名当成变量来理解, it can do arithmetic # # #
Example: Query our store price of each commodity is lower than the market price;
Select Goods_id,goods_name,goods_price-market_price from goods;
Check the backlog of payment under each column
Select Cat_id,sum (goods_price*goods_number) from goods group by CAT_ID;

# # #可以用as来给计算结果取个别名 # #
Select Cat_id,sum (Goods_price * goods_number) as HK from goods group by cat_id
Not only the column name can be alias, the form can also take aliases

3. The similarities and differences between having and where

Having, like where, can filter the data, how to write the expression after the where, and how to write after having a
Where the columns in the table work, querying the data
Having a function on the columns in the query results, filtering the data
#查询本店商品价格比市场价低多少钱, output less than 200 yuan of goods
Select Goods_id,goods_name,market_price-shop_price as s from goods have s>200;
You cannot use where because S is the result of a query, where only the field names in the table are filtered
If you use where, it is:
Select Goods_id,goods_name from goods where Market_price-shop_price > 200;

#同时使用where与having
Select Cat_id,goods_name,market_price-shop_price as s from goods where cat_id = 3 have s > 200;
#查询积压货款超过2万元的栏目, as well as the backlog of payments in the column
Select Cat_id,sum (Shop_price * goods_number) as T from goods Group by CAT_ID have s > 20000
#查询两门及两门以上科目不及格的学生的平均分
Ideas:
#先计算所有学生的平均分
Select Name,avg (Score) as PJ from Stu Group by name;
#查出所有学生的挂科情况
Select name,score<60 from Stu;
#这里score <60 is a judgment statement, so the result is true or false, and MySQL really is 1 false for 0
#查出两门及两门以上不及格的学生
Select Name,sum (score<60) as GK from Stu Group by name have GK > 1;
#综合结果
Select Name,sum (score<60) as Gk,avg (score) as PJ from Stu Group by name have GK >1;
4. ORDER BY
(1) Order BY price//default ascending order
(2) Order BY price desc//Descending order
(3) Order BY price ASC//ascending order, as default
(4) Order by RAND ()//random arrangement, not high efficiency
#按栏目号升序排列, the prices of commodities under each column are listed in descending order
SELECT * from goods where cat_id!=2 order by cat_id,shop_price Desc;
5. Limit
Limit [offset,] N
Offset offsets, optional, no write is equivalent to limit 0,n
N Remove Entry

#取价格第4-6 High product
Select Good_id,goods_name,goods_price from goods order BY good_price DESC limit 3, 3;

# # #查询每个栏目下最贵的商品
Ideas:
#先对每个栏目下的商品价格排序
Select Cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price Desc;
#上面的查询结果中每个栏目的第一行的商品就是最贵的商品
#把上面的查询结果理解为一个临时表 [exists in memory] "subquery"
#再从临时表中选出每个栏目最贵的商品
SELECT * FROM (select Goods_id,goods_name,cat_id,shop_price to goods ORDER by cat_id,shop_price Desc) as-T GROUP by Cat_ Id
#这里使用group by CAT_ID is because the first item in each column of a temporary table is the most expensive item, and group by is not using an aggregate function, so the first row of data for each group is taken by default, and this is grouped by cat_id

A good understanding of the model:
1, where the expression, put the expression in each row, see if it is established
2, field (column), understood as a variable, you can perform operations (arithmetic and logical operations)
3, take out the result can be understood as a temporary table
Second, MySQL sub-query
1. Where sub-query
(The inner query result is treated as the comparison condition of the outer query)
#不用order by to find the latest products
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 type sub-query
(The inner query results for the outer layer again query)
#用子查询查出挂科两门及以上的同学的平均成绩
Ideas:
#先查出哪些同学挂科两门以上
Select Name,count (*) as GK from Stu where score < have GK >=2;
#以上查询结果, we just have to name it, so we take a name again.
Select name from (select Name,count (*) as GK from Stu have GK >=2) as T;
#找出这些同学了, then calculate their average score.
Select Name,avg (Score) from Stu where name in (select Name,count (*) as GK from Stu have GK >=2) as T) group by name;

3, Exists type sub-query
(Take the outer query results to the inner layer, see if the inner layer of the query is established)
#查询哪些栏目下有商品, column list category, Commodity table goods
Select Cat_id,cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id);
Iii. Use of Union
(combine two or more query results, ask for the same number of columns in the query, the corresponding column type of the recommended query is consistent, you can query multiple tables, multiple query statements if the column name is not the same, then take the first column name!) If the values of each column of the row taken in a different statement are the same, then the result will automatically repeat, and if you do not want to repeat it, add all to declare, that is, union ALL)
# # Existing Table A 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
Two tables with the same ID and
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 change the value of B in TB to 10 to query the result of B is 10, because B in TA is also 10, so the union will be filtered out of a duplicate result, then you need to use UNION ALL
Select Id,sum (num) from (SELECT * FROM-TA UNION All-select * from-TB) as TMP GROUP by ID;

#取第4, 5 columns of goods, according to the column in ascending order, each column of commodity prices in descending order, with union to complete
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 that needs to be wrapped up with (), it is recommended to use order by at the end, which is to sort the results after the final merge"
#取第3, 4 columns, the top 3 items with the highest price in each column, and the results are sorted in descending order of price
(select Goods_id,goods_name,cat_id,shop_price from goods where cat_id=3 order by shop_price DESC limit 3) union (select Go Ods_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;

Four, left join, right connection, inner connection

The existing table A has 10 data, table B has 8 data, so what is the Cartesian product of table A and table B?
SELECT * from TA,TB//output result is 8*10=80 bar

1, left JOIN connect
To left table, the right table to find data, if there is no matching data, then NULL to fill the vacancy, so the output number >= left table the original data number

Syntax: Select N1,n2,n3 from TA left join TB on ta.n1= ta.n2 [here on the following expression, not necessarily =, can also >,< arithmetic, logical operator] "After the connection is completed, you can see as a new table, Use the Where and other queries "
#取出价格最高的五个商品, and displays the category name of the item
Select Goods_id,goods_name,goods.cat_id,cat_name,shop_price from goods left join category on goods.cat_id = Category.cat _id ORDER BY shop_price desc LIMIT 5;
2. Right connection
A LEFT join B is equivalent to B right join a
We recommend using a left connection instead of a right connection
Syntax: Select N1,n2,n3 from TA right join TB on ta.n1= ta.n2
3. Internal connection
The result of the query is the intersection of the left and right joins, "that is, the result of the left-right connection removes the set of the null entry (duplicates removed)"
MySQL currently does not support external connections (i.e., the set of left and right connection results, without removing null entries)
Syntax: Select N1,n2,n3 from TA inner join TB on ta.n1= ta.n2
#########
Example: Existing Table A
Name Hot
A 12
b 10
C 15
Table B:
Name Hot
D 12
E 10
F 10
G 8
Table A LEFT JOIN table B, query hot the same data
Select a.*,b.* from a LEFT join B on a.hot = B.hot
Query Result:
Name Hot name Hot
A 12 D
B E 10
b 10 F
C NULL NULL
As you can see from the above, the columns of the query result table A are present, and table B's data shows only those items that match the criteria.
And then table B left JOIN table A, query hot the same data
Select a.*,b.* from B left join a on a.hot = B.hot
The query results are:
Name Hot name Hot
D 12 A
E 10 B
F Ten B 10
G 8 NULL NULL
And then table a right join table B, query hot the same data
Select a.*,b.* from a right join B on a.hot = B.hot
The query results are the same as the B left join a above
# # #练习, check the name of the product, category, Brand
Select Goods_id,goods_name,goods.cat_id,goods.brand_id,category.cat_name,brand.brand_name from goods left join Category on goods.cat_id = category.cat_id left join brand on goods.brand_id = brand.brand_id limit 5;
Understanding: The results after each connection can be viewed as a new table

MySQL Sub-query