MySQL: Where sub-query & From sub-query & Exists sub-query

wuliws

　　1. A good comprehension model:
　　1) Where expression: Fit this experssion into every row and get the true/false result.

#Where expression comprehension model
select * from stu where stu_name = 'zhangsan';
　　2) Row: Regard as variable which enables basic math operation.

#Row comprehension model
#We can regard the second 'stu_name' as a variable whose value depends on the row cursor now pointing to
select stu_name from stu where stu_name = 'zhangsan'
　　3) Result set: Regard as a temporary table which enables further query.

#We regard the sub-query result set as a temp table which enables further query
#We want to find the stu_name whose max score equals or larger than 80
select * from (select stu_name, max(stu_score) as stu_max from stu group by stu_name) as temp where stu_max >= 80;
　　
　　2. Four Types of Sub-query
　　1) Where sub-query

# Find the highest score and the corresponding stu_name, stu_course
# Solution 1: Where sub-query
# Regard the result of where sub-query as a value
select stu_name, stu_score, stu_course from stu where stu_score = (select max(stu_score) from stu);
# Solution 2: Order & Limit
select stu_name, stu_score, stu_course from stu group by stu_score limit 1;
　　

#Find the highest score and the corresponding student in each course
#Using where sub-query
#But this kind of approach is not that precise.
#Just use the stu_score as the signal, there may be some other stu_score in other courses that equals the max(score).
select * from stu where stu_score in (select max(stu_score) from stu group by stu_course);
#There is an overlap in Literature course because the stu_score in Literature 50 equals the highest score that in Geograph 50
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | Math       |        90 |
| zhangsan | Literature |        50 |
| lisi     | Literature |        55 |
| zhaoliu  | Geograph   |        50 |
| zhaoliu  | Politic    |        99 |
+----------+------------+-----------+
　　
　　2) In sub-query

#The core thought lies behind From sub-query is regarding the result of inner query as a table based on which we can start a new outter query.
select * from (select max(stu_score) from stu group by stu_course) as temp;
　　

#Find the avg score for students who has more than one invalid course.
#Using Where sub-query
#Most inner query find the stu_name who has more than one invalid course.
#Secondary inner query filter other useless columns for where sub-query
#Outter query find the average score for each students whose name lists in inner query.
select stu_name, avg(stu_score) from stu where stu_name in (select stu_name from (select stu_name, stu_score, sum(stu_score < 60) as invalid_count from stu group by stu_name having invalid_count >= 2) as temp) group by stu_name;
#Another way to find out the student name whose invalid course in more than one
select stu_name, count(*) as invalid_count from stu where stu_score < 60 group by stu_name having invalid_count >= 2;
　　
　　3) Exists sub-query

#Core thoughts lies on Exists sub-query is that 'Put the result of out query into inner query to judge the inner query true or false'
select * from category;
#First Level Category
####Secondary Category
#######Real Goods
#######Real Goods
####Secondary Category
#######Third Level Category
###########Real Goods
####Secondary Category
# All the categories are stored in category table,
# But some categories contains no goods but secondary category
# What if we want to figure out the category that directly contains goods?
select cat_id, cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);