目录
?导入数据
分别在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 p2
WHERE 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 p2
WHERE p2.class = p.class --精髓
GROUP BY p2.class);
# [Python] 例3：找出prod_info表中，每一个商品价格都都大于其均价的商品行
#prod_info中每一商品类别的均价
a = prod_info.groupby(['class']).mean()['sale_price'] #type(a)为numpy
Out[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;