[MySQL
目录
1 标量子查询
2 关联子查询
3 普通子查询
导入数据
分别在MySQL和Python中导入数据。
import pandas as pd
milk_tea = pd.read_excel('SQLData.xlsx','milk_tea')
prod_info = pd.read_excel('SQLData.xlsx','prod_info')
supplier_info = pd.read_excel('SQLData.xlsx','supplier_info')
cust_info = pd.read_excel('SQLData.xlsx','cust_info')
order_info = pd.read_excel('SQLData.xlsx','order_info')1 标量子查询
子查询返回的是一个常数。
例1：找出prod_info表中，平均商品销售价格大于milk_tea表中奶茶价格的行。
筛选功能对比：
MySQL用【WHERE+条件】语句实现筛选，筛选出哪个变量用SELECT控制
Python中用【df[ 判断语句 ]】实现筛选，筛选出什么变量用df[判断语句][‘变量’]控制
groupby对比：
MySQL用【GROUP BY 变量】实现分组，用【SELECT 聚合键 聚合函数】实现分组求XX
Python用【df.groupby(‘变量’)】实现分组，用【df.groupby(‘变量’).mean()】实现分组求XX
--[MySQL] 例1：找出prod_info表中，平均商品销售价格大于milk_tea表中奶茶价格的行。
SELECT p.class, AVG(p.sale_price)
FROM prod_info AS p
GROUP BY p.class
HAVING AVG(p.sale_price) > (SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name = '奶茶');# [Python] 例1：找出prod_info表中，平均商品销售价格大于milk_tea表中奶茶价格的商品种类。
milk_tea[ milk_tea['prod_name'].isin(['奶茶']) ] #milk_tea表中奶茶行，适用于多值筛选
milk_tea[ milk_tea['prod_name']=="奶茶" ] #milk_tea表中奶茶行，适用于判断筛选#milk_tea表中奶茶的价格，15.
a = milk_tea[milk_tea['prod_name'].isin(['奶茶'])]['sale_price']#prod_info中各类产品的均值
b = prod_info.groupby(['class']).mean()Out[1]: prod_id cost sale_price
class
日用品 10003.0 25.298000 34.960000
零食 20004.0 9.467143 14.542857
饮料 30002.5 2.565000 3.750000#最终结果
b[ b['sale_price']>a[0] ].index补充学习：
Python进行数据筛选：使用python对数据进行筛选_ch206265的博客-CSDN博客_python 筛选
Python中df.groupby([X])：python中groupby函数详解（非常容易懂） - The-Chosen-One - 博客园
例2： 找出prod_info表中，日用品价格大于其均价的商品行
--[MySQL] 例2：找出prod_info表中，日用品价格大于其均价的商品行
SELECT p.*
FROM prod_info AS p
WHERE p.class = '日用品' AND p.sale_price > (SELECT AVG(p2.sale_price) --只要一个数，可以不用聚合键FROM prod_info AS p2WHERE p2.class = '日用品'GROUP BY p2.class);#[Python] 例2：找出prod_info表中，日用品价格大于其均价的商品行
prod_info.groupby('class').mean()#日用品均价
a = prod_info.groupby('class').mean().iloc[0,2]#最终结果
prod_info.loc[ (prod_info['class']=='日用品') & (prod_info['sale_price']>a[0]) ]Out[3]: prod_id prod_name brand type class cost sale_price supplier_id
0 10001 抽纸 洁柔 120抽*8 日用品 20.11 28.8 NJ0001
1 10002 抽纸 维达 120抽*8 日用品 18.02 25.5 NJ0002
2 10003 抽纸 洁云 120抽*8 日用品 19.11 26.8 SH0001
3 10004 洗衣液 蓝月亮 2.5kg 日用品 33.43 43.8 SH0002
4 10005 洗衣液 奥妙 2.5kg 日用品 35.82 49.9 SZ0001
上面的第一句输出结果如下：
prod_info.groupby('class').mean()
Out[2]: prod_id cost sale_price
class
日用品 10003.0 25.298000 34.960000
零食 20004.0 9.467143 14.542857
饮料 30002.5 2.565000 3.750000
2 关联子查询
嵌套在其他查询中的查询。适用于组内比较。
子查询返回一列数据：子查询的结果，与主查询的目标列存在一定关联。
--[MySQL] 例3：找出prod_info表中，每一个商品价格都都大于其均价的商品行
SELECT p.*
FROM prod_info AS p
WHERE p.sale_price > (SELECT AVG(p2.sale_price) --只要一个数，可以不用聚合键FROM prod_info AS p2WHERE p2.class = p.class --精髓GROUP BY p2.class);
# [Python] 例3：找出prod_info表中，每一个商品价格都都大于其均价的商品行
#prod_info中每一商品类别的均价
a = prod_info.groupby(['class']).mean()['sale_price'] #type(a)为numpyOut[4]:
class
日用品 34.960000
零食 14.542857
饮料 3.750000# 在a表只有3行，在a表用for循环比在prod_info表用for循环的循环次数少
df = pd.DataFrame()
for i in range(0,len(a)):df_i = prod_info[(prod_info['class']==a.index[i]) & (prod_info['sale_price']>a[i])]df = pd.concat([df,df_i])Out[5]: prod_id prod_name brand type class cost sale_price supplier_id
3 10004 洗衣液 蓝月亮 2.5kg 日用品 33.43 43.8 SH0002
4 10005 洗衣液 奥妙 2.5kg 日用品 35.82 49.9 SZ0001
5 20001 原味薯片 乐事 80g 零食 11.11 18.3 SZ0002
6 20002 黄瓜味薯片 乐事 80g 零食 12.31 18.8 NJ0001
7 20003 番茄味薯片 乐事 80g 零食 12.31 18.8 NJ0002
11 20007 趣多多 卡夫卡 280g 零食 13.54 18.6 SZ0002
14 30003 营养快线 娃哈哈 350ml 饮料 3.56 4.8 SH00013 普通子查询
形式 1：子查询返回一列数据：姜子查询的结果列，作为主查询的取值范围。
形式2：子查询返回二维表：将查询结果的二维表作为新的目标表。
平时Python中做的一直就是MySQL中的普通子查询。
--例4：[子查询返回一列]筛选出milk_tea表中销售价格等于15的商品对于的行
--正常如下即可
SELECT m.* FROM milk_tea AS m WHERE m.sale_price = 15; --为了示范，强调先选出sale_price=15的商品（一列数据），再从商品名字提取行
SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price =15;
--合并
SELECT m2.* FROM milk_tea AS m2
WHERE m2.prod_name IN (SELECT m1.prod_name FROM milk_tea AS m1 WHERE m1.sale_price =15 );--例5：[子查询返回一张表] 用筛选出的表子表，进一步做筛选
SELECT p.prod_name,p.type,p.sale_price FROM prod_info AS p WHERE p.prod_name = '抽纸';SELECT * FROM () AS b WHERE b.sale_price > 26;--合并
SELECT b.type
FROM (SELECT p.prod_name,p.type,p.sale_price FROM prod_info AS p WHERE p.prod_name = '抽纸') AS b
WHERE b.sale_price > 26;
[MySQL
目录
1 标量子查询
2 关联子查询
3 普通子查询
导入数据
分别在MySQL和Python中导入数据。
import pandas as pd
milk_tea = pd.read_excel('SQLData.xlsx','milk_tea')
prod_info = pd.read_excel('SQLData.xlsx','prod_info')
supplier_info = pd.read_excel('SQLData.xlsx','supplier_info')
cust_info = pd.read_excel('SQLData.xlsx','cust_info')
order_info = pd.read_excel('SQLData.xlsx','order_info')1 标量子查询
子查询返回的是一个常数。
例1：找出prod_info表中，平均商品销售价格大于milk_tea表中奶茶价格的行。
筛选功能对比：
MySQL用【WHERE+条件】语句实现筛选，筛选出哪个变量用SELECT控制
Python中用【df[ 判断语句 ]】实现筛选，筛选出什么变量用df[判断语句][‘变量’]控制
groupby对比：
MySQL用【GROUP BY 变量】实现分组，用【SELECT 聚合键 聚合函数】实现分组求XX
Python用【df.groupby(‘变量’)】实现分组，用【df.groupby(‘变量’).mean()】实现分组求XX
--[MySQL] 例1：找出prod_info表中，平均商品销售价格大于milk_tea表中奶茶价格的行。
SELECT p.class, AVG(p.sale_price)
FROM prod_info AS p
GROUP BY p.class
HAVING AVG(p.sale_price) > (SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name = '奶茶');# [Python] 例1：找出prod_info表中，平均商品销售价格大于milk_tea表中奶茶价格的商品种类。
milk_tea[ milk_tea['prod_name'].isin(['奶茶']) ] #milk_tea表中奶茶行，适用于多值筛选
milk_tea[ milk_tea['prod_name']=="奶茶" ] #milk_tea表中奶茶行，适用于判断筛选#milk_tea表中奶茶的价格，15.
a = milk_tea[milk_tea['prod_name'].isin(['奶茶'])]['sale_price']#prod_info中各类产品的均值
b = prod_info.groupby(['class']).mean()Out[1]: prod_id cost sale_price
class
日用品 10003.0 25.298000 34.960000
零食 20004.0 9.467143 14.542857
饮料 30002.5 2.565000 3.750000#最终结果
b[ b['sale_price']>a[0] ].index补充学习：
Python进行数据筛选：使用python对数据进行筛选_ch206265的博客-CSDN博客_python 筛选
Python中df.groupby([X])：python中groupby函数详解（非常容易懂） - The-Chosen-One - 博客园
例2： 找出prod_info表中，日用品价格大于其均价的商品行
--[MySQL] 例2：找出prod_info表中，日用品价格大于其均价的商品行
SELECT p.*
FROM prod_info AS p
WHERE p.class = '日用品' AND p.sale_price > (SELECT AVG(p2.sale_price) --只要一个数，可以不用聚合键FROM prod_info AS p2WHERE p2.class = '日用品'GROUP BY p2.class);#[Python] 例2：找出prod_info表中，日用品价格大于其均价的商品行
prod_info.groupby('class').mean()#日用品均价
a = prod_info.groupby('class').mean().iloc[0,2]#最终结果
prod_info.loc[ (prod_info['class']=='日用品') & (prod_info['sale_price']>a[0]) ]Out[3]: prod_id prod_name brand type class cost sale_price supplier_id
0 10001 抽纸 洁柔 120抽*8 日用品 20.11 28.8 NJ0001
1 10002 抽纸 维达 120抽*8 日用品 18.02 25.5 NJ0002
2 10003 抽纸 洁云 120抽*8 日用品 19.11 26.8 SH0001
3 10004 洗衣液 蓝月亮 2.5kg 日用品 33.43 43.8 SH0002
4 10005 洗衣液 奥妙 2.5kg 日用品 35.82 49.9 SZ0001
上面的第一句输出结果如下：
prod_info.groupby('class').mean()
Out[2]: prod_id cost sale_price
class
日用品 10003.0 25.298000 34.960000
零食 20004.0 9.467143 14.542857
饮料 30002.5 2.565000 3.750000
2 关联子查询
嵌套在其他查询中的查询。适用于组内比较。
子查询返回一列数据：子查询的结果，与主查询的目标列存在一定关联。
--[MySQL] 例3：找出prod_info表中，每一个商品价格都都大于其均价的商品行
SELECT p.*
FROM prod_info AS p
WHERE p.sale_price > (SELECT AVG(p2.sale_price) --只要一个数，可以不用聚合键FROM prod_info AS p2WHERE p2.class = p.class --精髓GROUP BY p2.class);
# [Python] 例3：找出prod_info表中，每一个商品价格都都大于其均价的商品行
#prod_info中每一商品类别的均价
a = prod_info.groupby(['class']).mean()['sale_price'] #type(a)为numpyOut[4]:
class
日用品 34.960000
零食 14.542857
饮料 3.750000# 在a表只有3行，在a表用for循环比在prod_info表用for循环的循环次数少
df = pd.DataFrame()
for i in range(0,len(a)):df_i = prod_info[(prod_info['class']==a.index[i]) & (prod_info['sale_price']>a[i])]df = pd.concat([df,df_i])Out[5]: prod_id prod_name brand type class cost sale_price supplier_id
3 10004 洗衣液 蓝月亮 2.5kg 日用品 33.43 43.8 SH0002
4 10005 洗衣液 奥妙 2.5kg 日用品 35.82 49.9 SZ0001
5 20001 原味薯片 乐事 80g 零食 11.11 18.3 SZ0002
6 20002 黄瓜味薯片 乐事 80g 零食 12.31 18.8 NJ0001
7 20003 番茄味薯片 乐事 80g 零食 12.31 18.8 NJ0002
11 20007 趣多多 卡夫卡 280g 零食 13.54 18.6 SZ0002
14 30003 营养快线 娃哈哈 350ml 饮料 3.56 4.8 SH00013 普通子查询
形式 1：子查询返回一列数据：姜子查询的结果列，作为主查询的取值范围。
形式2：子查询返回二维表：将查询结果的二维表作为新的目标表。
平时Python中做的一直就是MySQL中的普通子查询。
--例4：[子查询返回一列]筛选出milk_tea表中销售价格等于15的商品对于的行
--正常如下即可
SELECT m.* FROM milk_tea AS m WHERE m.sale_price = 15; --为了示范，强调先选出sale_price=15的商品（一列数据），再从商品名字提取行
SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price =15;
--合并
SELECT m2.* FROM milk_tea AS m2
WHERE m2.prod_name IN (SELECT m1.prod_name FROM milk_tea AS m1 WHERE m1.sale_price =15 );--例5：[子查询返回一张表] 用筛选出的表子表，进一步做筛选
SELECT p.prod_name,p.type,p.sale_price FROM prod_info AS p WHERE p.prod_name = '抽纸';SELECT * FROM () AS b WHERE b.sale_price > 26;--合并
SELECT b.type
FROM (SELECT p.prod_name,p.type,p.sale_price FROM prod_info AS p WHERE p.prod_name = '抽纸') AS b
WHERE b.sale_price > 26;