[MySQL | Python] MySQL子查询及其对于的Python操作

目录

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 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      SH0001

3 普通子查询

形式 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;