[Python知识库]Sql 翻译 Pandas 或 Django-ORM

目前已有 Django-ORM, Pandas

SQL_To_Everything

image-20220227142548210

• 以下全部 sql 语句基于 mysql 的一个命令行插件mycli?编写 ,所以,关键字都是小写,结尾也没有分号

• 以下所有 Django-ORM均在django 第三方库?django-extensions?的shell_plus?环境下编写

• 以下所有Pandas 语句都在jupyter notebook界面中编写测试

数据集下载:

host:

https://gitee.com/fourforward/my-note-garden/blob/main/python/img/SQL_To_Everything

创建数据库文件:??/creat_table_sql.sql

插入具体数据文件:?/data.sql

django 的模型文件:?/models.py

供pandas 读取的excel 文件:?/dataframe_data.xlsx

Mysql 数据准备:

• 创建数据库

• mysql -u root -p 目标库名 < XXX.sql?将两个数据库文件写入

Django 数据准备

• 新建django项目,新建app应用,拷贝 models.py 文件

• 也可以根据数据库生成新的models.py文件,使用指令python3 manage.py inspectdb > models.py

  这时,在manage.py 同级目录下会看见一个models.py 文件,使用其覆盖?应用目录下的models.py文件

• 将settings.py中的DATABASES 指向已存在的MySQL数据库

• 最后迁移数据库python3 manage.py migrate

Pandas 数据准备:

import?pandas?as?pd
import?numpy?as?np
import?pymysql
from?sqlalchemy?import?create_engine

conn?=?create_engine(f'mysql+pymysql://root:密码@localhost:3306/库名',?echo=True)

customers?=?pd.read_sql('select?*?from?customers',conn)
orderitems?=?pd.read_sql('select?*?from?orderitems',?conn)
orders?=?pd.read_sql('select?*?from?orders',?conn)
productnotes?=?pd.read_sql('select?*?from?productnotes',?conn)
products?=?pd.read_sql('select?*?from?products',?conn)
vendors?=?pd.read_sql('select?*?from?vendors',?conn)

数据说明: 各表,各字段解释

"""
vendors:?存储销售产品的供应商的信息
????vend_id:?唯一的供应商ID?(主键)
????vend_name:?供应商名
????vend_address:?供应商地址
????vend_city:?供应商城市
????vend_state:?供应商州
????vend_zip:?供应商邮编
????vend_country:?供应商国家

products:?存储产品信息
????prod_id:?唯一产品ID??(主键)
????vend_id:?供应商ID??(关联到`vendors`中的vend_id)
????prod_name:?产品名称
????prod_price:?产品价格??
????prod_desc:?产品描述?

customers:?存储所有顾客的信息
????cust_id:?唯一的顾客ID??(主键)??
????cust_name:?顾客名
????cust_address:?顾客的地址
????cust_city:?顾客的城市
????cust_state:?顾客的州
????cust_zip:?顾客的邮编
????cust_country:?顾客的国家?
????cust_contact:?顾客的联系名
????cust_email:?顾客的邮件地址

orders:?存储客户订单
????order_num:?唯一订单号??(主键)
????order_date:?订单日期??
????cust_id:?订单顾客??(关联到`customers`中的cust_id)

orderitems:?存储每个订单中的实际物品
????order_num:?订单号??(关联到`orders`中的order_num)
????order_item:?订单物品号??(在某个订单中的顺序)(与order_num作复合主键)
????prod_id:?产品ID??(关联到`products`中的prod_id)
????quantity:?物品数量
????item_price:?物品价格?

productnotes:?存储与特定产品相关的注释
????note_id:?唯一注释ID??(主键)?
????prod_id:?产品ID??(关联到`products`中的prod_id)
????note_date:?增加注释的日期
????note_text:?注释文本
"""

4.检索数据

4.2 检索单列

#?sql
select?prod_name?from?products?ijlIL

#?Django-ORM
Products.objects.values(‘prod_name’)

#?pandas
products['prod_name']

```

### 4.3 检索多列

```
#?sql
select?prod_id,prod_price,prod_name?from?products

#?Django-ORM
Products.objects.values(‘prod_id’,‘prod_price’,‘prod_name’)

#?pandas
products[['prod_id','prod_price','prod_name']]

```

### 4.4 检索所有列

```
#?sql
select?*?from?products

#?Django-ORM
Products.objects.values()

#?pandas
products

```

### 4.5 检索不同的行(去重)

```
#?sql
select?distinct?vend_id?from?products

#?Django-ORM
Products.objects.values(‘vend_id’).distinct()

#?pandas
products['vend_id'].drop_duplicates(keep='last')????#?对Series?去重

products.drop_duplicates(subset=['vend_id'],?keep='last')['vend_id']????#?对DataFrame?去重
"""
参数解释:
????subset:?需要去重的列
????keep：?可选参数有三个：first、?last、?False，?默认值?first
????????（1）first?表示：?保留第一次出现的重复行，删除后面的重复行。
????????（2）last?表示：?删除重复项，保留最后一次出现。
????????（3）False?表示：?删除所有重复项。
????inplace：默认为?False?，删除重复项后返回副本。True，直接在原数据上删除重复项。
????ignore_index:?默认为?False?,保留原有的index,?True?则生成新的index
"""

```

### 4.6 限制结果

```
#?sql
#?原生sql?是?limit
select?prod_name?from?products?limit?5

#?Django-ORM
#?Django-ORM是?切片
Products.objects.values(‘prod_name’)[:5]

#?pandas
products['prod_name'][:5]

```

5.排序数据
------

### 5.1 排序数据

```
#?sql
select?prod_name?from?products?order?by?prod_name

#?Django-ORM
Products.objects.values(‘prod_name’).order_by(‘prod_name’)

#?pandas
products['prod_name'].sort_values()????#?对Series?按值排序

products.sort_values(['prod_name'])['prod_name']????#?对?DataFram?按值排序

```

### 5.2 按多个列排序

```
#?sql
select?prod_id,prod_price,prod_name?from?products?order?by?prod_price,prod_name

#?Django-ORM
Products.objects.values(‘prod_id’,‘prod_price’,‘prod_name’).order_by(‘prod_price’,‘prod_name’)

#?pandas
products.sort_values(['prod_price','prod_name'],?ignore_index=True)[['prod_id','prod_price','prod_name']]
#?ignore_index=True?重设索引?默认是不重设

```

### 5.3 指定排序方向

```
#?sql
select?prod_id,prod_price,prod_name?from?products?order?by?prod_price?desc
#?原生sql?是?默认是升序?`desc`?是降序?

#?Django-ORM
Products.objects.values(‘prod_id’,‘prod_price’,‘prod_name’).order_by(’-prod_price’)
#?Django-ORM是?列名前加?-?负号代表降序

#?pandas
products.sort_values(['prod_price'],?ascending=False,?ignore_index=True)[['prod_id','prod_price','prod_name']]
#?ascending:?默认为True?升序

```

6.过滤数据
------

### 6.1 使用where子句

```
#?sql
select?prod_name,prod_price?from?products?where?prod_price=2.50

#?Django-ORM
Products.objects.filter(prod_price=2.50).values(‘prod_price’,‘prod_name’)

#?pandas
products.loc[products['prod_price']==2.50,['prod_name','prod_price']]

```

### 6.2.2 不匹配查询

```
#?sql
#?原生sql?是?not
select?vend_id,prod_name?from?products?where?vend_id?!=?1003

#?Django-ORM
#?Django-ORM是?exclude
Products.objects.exclude(vend_id=1003).values(‘vend_id’,‘prod_name’)

#?pandas
products.loc[products['vend_id']!=1003,['vend_id','prod_name']]

```

### 6.2.3 范围值查询

```
#?sql
#?原生sql?是?between
select?prod_name,prod_price?from?products?where?prod_price?between?5?and?10

#?Django-ORM
#?Django-ORM是?__range
Products.objects.filter(prod_price__range=(5,10)).values(‘prod_price’,‘prod_name’)

#?pandas
#?Series.between()
products.loc[products['prod_price'].between(5,10),['prod_name','prod_price']]
"""
left?:?scalar?or?list-like
????????????Left?boundary.
right?:?scalar?or?list-like
????????????Right?boundary.
inclusive?:?{"both",?"neither",?"left",?"right"}????默认为?"both"?左闭右闭
????????????Include?boundaries.?Whether?to?set?each?bound?as?closed?or?open.
????????????..?versionchanged::?1.3.0
"""

```

7.数据过滤
------

### 7.1.1 AND 操作符

```
#?sql
select?prod_id,prod_price,prod_name?from?products?where?vend_id=1003?and?prod_price<=10

#?Django-ORM
Products.objects.filter(vend_id=1003,prod_price__lte=10).values(‘prod_id’,‘prod_price’,‘prod_name’)

#?pandas
products.loc[(products['vend_id']==1003)?&?(products['prod_price']<=10),['prod_id','prod_price','prod_name']]

```

### 7.1.2 OR操作符

```
#?sql
select?prod_name,prod_price?from?products?where?vend_id=1002?or?vend_id=1003

#?Django-ORM
Products.objects.filter(Q(vend_id=1002)|Q(vend_id=1003)).values(‘prod_price’,‘prod_name’)

#?pandas
products.loc[(products['vend_id']==1002)?|?(products['vend_id']==1003),['prod_price','prod_name']]

```

### 7.2 IN操作符

```
#?sql
select?prod_name,prod_price?from?products?where?vend_id?in?(1002,1003)?order?by?prod_name

#?Django-ORM
Products.objects.filter(vend_id__in=(1002,1003)).values(‘prod_price’,‘prod_name’).order_by(‘prod_name’)

#?pandas
products.loc[products['vend_id'].isin([1002,1003]),['prod_price','prod_name']].sort_values(['prod_name'])

```

### 7.3 NOT 操作符

```
#?sql
#?not?in
select?prod_name,prod_price?from?products?where?vend_id?not?in?(1002,1003)?order?by?prod_name

#?Django-ORM
#?Products.objects.exclude
Products.objects.exclude(vend_id__in=(1002,1003)).values(‘prod_price’,‘prod_name’).order_by(‘prod_name’)

#?pandas
#?pandas?没有not?in?的语法糖,?但是将?isin?取反之后就是not?in
products.loc[products['vend_id'].isin([1002,1003])==False,['prod_price','prod_name']].sort_values(['prod_name'])

#?或者在条件前加上?~?,代表取反
products.loc[~products['vend_id'].isin([1002,?1003]),?['prod_price',?'prod_name']].sort_values(['prod_name'])

```

8.用通配符进行过滤
----------

### 8.1 LIKE 操作符

```
#?sql
select?prod_id,prod_name?from?products?where?prod_name?like?'jet%'

#?Django-ORM
Products.objects.filter(prod_name__icontains=‘jet’).values(‘prod_id’,‘prod_name’)

#?并不是完全等同
select?prod_id,prod_name?from?products?where?prod_name?like?‘%jet%’

#?pandas
#?可以使用正则表达式
products.loc[products['prod_name'].str.contains('(?i)jet')][['prod_id','prod_name']]

```

9.使用正则表达式进行搜索
-------------

### 9.2.1 基本字符匹配

```
#?sql
select?prod_name?from?products?where?prod_name?regexp?'1000'

#?Django-ORM
#?Django-ORM?建议使用?iregex?不区分大小写
Products.objects.filter(prod_name__iregex=r’1000’).values(‘prod_name’)

#?pandas
products.loc[products['prod_name'].str.extract('(1000)',expand=False).notnull(),['prod_name']]
#?只有一个捕获组的情况下,expand?取?False

```

### 9.2.2 进行OR匹配

```
#?sql
select?prod_name?from?products?where?prod_name?regexp?'1000|2000'?order?by?prod_name

#?Django-ORM
Products.objects.filter(prod_name__iregex=r’1000|2000’).values(‘prod_name’).order_by(‘prod_name’)

#?pandas
products.loc[products['prod_name'].str.extract('(1000|2000)',expand=False).notnull(),['prod_name']].sort_values('prod_name')

```

9.X.X 略

10.创建计算字段
---------

### 10.2 拼接字段

```
#?sql
select?concat(vend_name,'?(',vend_country,')')?as?vend_title?from?vendors?order?by?vend_name

#?Django-ORM

from?django.db.models.functions?import?Concat
from?django.db.models?import?Value

Vendors.objects.values(vend_title=Concat(‘vend_name’,Value(’?(’),‘vend_country’,Value(’)’))).order_by(‘vend_name’)

#?pandas
vendors['vend_name'].sort_values().str.cat('?('?+?vendors['vend_country']?+?')')

#?因为要排序,所以写成这样,全部使用?+?拼接的话无法排序,就算先排好序再使用?+?拼接,顺序也会被还原,所以,只能再排好序的Series上使用?.str.cat(),如此才能保证排序

```

### 10.3 执行算数运算

检索订单号为20005中所有的物品,并汇总物品价格

```
#?sql
select?prod_id,quantity,item_price,quantity*item_price?as?expanded_price?from?orderitems?where?order_num=20005

#?Django-ORM
Orderitems.objects.filter(order_num=20005).values(‘prod_id’,‘quantity’,‘item_price’,expanded_price=F(‘quantity’)*F(‘item_price’))

#?pandas
df?=?orderitems.loc[orderitems['order_num']==20005,['prod_id','quantity','item_price']]
df['expanded_price']?=?df['quantity']?*?df['item_price']
df

```

11.使用数据处理函数
-----------

Django-ORM的数据处理都在?`from django.db.models.functions import XXX`

### 11.2.1 将文本转换为大写

```
#?sql
select?vend_name,upper(vend_name)?as?vend_name_upcase?from?vendors?order?by?vend_name

#?Django-ORM
Vendors.objects.values(‘vend_name’,vend_name_upcase=Upper(‘vend_name’)).order_by(‘vend_name’)

#?pandas
df?=?vendors.loc[:,?['vend_name']]
df['vend_name_upcase']?=?df['vend_name'].str.upper()
df

```

### 11.2.2 时间函数

检索出2005年9月下的所有订单

```
#?sql
select?cust_id,order_num?from?orders?where?year(order_date)=2005?and?month(order_date)=9

#?Django-ORM
Orders.objects.filter(order_date__year=2005,order_date__month=9).values(‘cust_id’,‘order_num’)

#?pandas
#?网上的攻略都是将日期列转换为dataframe的index再操作,不知为何
s?=?pd.to_datetime(orders['order_date'])
orders.loc[(s.dt.year==2005)&(s.dt.month==9),['cust_id','order_num']]

```

12.聚集函数
-------

### 12.1.1 AVG()函数

查询prodcts表中所有产品的平均价格

```
#?sql
select?AVG(prod_price)?from?products

#?Django-ORM
Products.objects.aggregate(Avg(‘prod_price’))

#?pandas
products['prod_price'].mean()

#?或者
products['prod_price'].agg({'prod_price':'mean'})

```

### 12.1.2 COUNT()函数

查询customers中客户的总数

```
#?sql
select?count(*)?as?num_cust?from?customers

#?Django-ORM
Customers.objects.aggregate(num_cust=Count(‘cust_id’))

#?pandas
customers.shape[0]

#?或者
customers['cust_id'].count()

```

12.1.3 MAX()函数 (略)

12.1.4 MIN()函数 (略)

12.1.5 SUM()函数 (略)

### 12.2 聚集不同的值

查询特定供应商提供的产品的平均价格,只考虑各个不同的价格

```
#?sql
select?avg(distinct?prod_price)?as?avg_price?from?products?where?vend_id=1003

#?Django-ORM
#?给聚集函数指定?distinct=True?的参数
Products.objects.filter(vend_id=1003).aggregate(avg_price=Avg(‘prod_price’,distinct=True))

#?pandas
products.loc[products['vend_id']==1003,['prod_price']].drop_duplicates(subset=['prod_price'],?keep='last').mean()

```

### 12.3 组合聚集函数

```
#?sql
select?count(*)?as?num_items,min(prod_price)?as?price_min,max(prod_price)?as?price_max,avg(prod_price)?as?price_avg?from?products

#?Django-ORM
Products.objects.aggregate(num_items=Count(‘prod_id’),price_min=Min(‘prod_price’),price_max=Max(‘prod_price’),price_avg=Avg(‘prod_price’))

#?pandas
products.agg(num_items=('prod_id',?'count'),?price_min=('prod_price',?'min'),?price_max=('prod_price',?'max'),?price_mean=('prod_price',?'mean')).T.fillna(method='bfill')[:1]

#?或者
pd.DataFrame([[products.shape[0],products['prod_price'].min(),products['prod_price'].max(),products['prod_price'].mean()]],columns=['num_items','price_min','price_max','price_avg'])

```

13.分组数据
-------

### 13.2创建分组

查询每个供应商的产品数量

```
#?sql
select?vend_id,count(*)?as?num_prods?from?products?group?by?vend_id

#?Django-ORM
Products.objects.values(‘vend_id’).annotate(num_prods=Count(‘vend_id’))

#?pandas
products.groupby(['vend_id'])['prod_id'].count()

```

### 13.3 过滤分组

```
#?sql
select?cust_id,count(*)?as?orders?from?orders?group?by?cust_id?having?count(*)?>=2

#?Django-ORM
Orders.objects.values(‘cust_id’).annotate(orders=Count(‘cust_id’)).filter(orders__gte=2)

#?pandas
s?=?orders.groupby(['cust_id'])['order_num'].count()
s.loc[s>=2]

```

### 13.3 同时使用 where 和 having

查询具有2(含)个以上,价格为10(含)以上的产品的供应商

```
#?sql
select?vend_id,count(*)?as?num_prods?from?products?where?prod_price>=10?group?by?vend_id?having?count(*)>=2

#?Django-ORM
Products.objects.filter(prod_price__gte=10).values(‘vend_id’).annotate(orders=Count(‘vend_id’)).filter(orders__gte=2)

#?pandas
s?=?products.loc[products['prod_price']>=10].groupby(['vend_id']).count()['prod_id']
s.loc[s>=2]

```

### 13.4 分组和排序

检索总计订单价格>=50的订单,的订单号和总价,并按总计价格排序

```
#?sql
select?order_num,sum(quantity*item_price)?as?ordertotal?from?orderitems?group?by?order_num?having?sum(quantity*item_price)>=50?order?by?ordertotal

#?Django-ORM
Orderitems.objects.values(‘order_num’).annotate(ordertotal=Sum(F(‘quantity’)*F(‘item_price’))).filter(ordertotal__gte=50).order_by(‘ordertotal’)

#?pandas
df?=?orderitems.loc[:,['order_num']]
df['ordertotal']?=?orderitems['quantity']?*?orderitems['item_price']
df2?=?df.groupby(['order_num']).sum()
df2.loc[df2['ordertotal']>=50].sort_values(['ordertotal'])

#?还有一种对分组后,聚合前的数据进行过滤的方式
#?问题关键在于使用这种过滤方式后(df.groupby().filter())?返回的是一个df,而不是分组数据...不能直接对其聚合,就很迷,所以暂时采用上面那种方式来实现
#?错误示例如下:
df.groupby(['order_num']).filter(lambda?x:?x['ordertotal'].sum()?>=?50)

```

14.使用子查询
--------

### 14.2 利用子查询进行过滤

查询订购物品 TNT2 的所有客户

```
#?sql
select?cust_name,cust_contact?from?customers?where?cust_id?in?(select?cust_id?from?orders?where?order_num?in?(select?order_num?from?orderitems?where?prod_id?=?'tnt2'))

#?Django-ORM
Customers.objects.filter(cust_id__in=Orders.objects.filter(order_num__in=Orderitems.objects.filter(prod_id=‘tnt2’).values(‘order_num’)).values(‘cust_id’)).values(‘cust_name’,‘cust_contact’)

#?pandas
customers.loc[customers['cust_id'].isin(orders.loc[orders['order_num'].isin(orderitems.loc[orderitems['prod_id']?==?'TNT2',?['order_num']]['order_num']),?['cust_id']]['cust_id']),?['cust_name',?'cust_contact']]

```

### 14.3 作为计算字段使用子查询

对每个客户的订单进行计数

```
#?sql
select?cust_name,cust_state,(select?count(*)?from?orders?where?orders.cust_id=customers.cust_id)?as?orders?from?customers?order?by?cust_name

#?Django-ORM
#?写不出来…

#?pandas
#?实际也是使用连接来做的,并非子查询的思想

df2?=?orders.groupby('cust_id')['order_num'].count().reset_index(name='count')
#?聚合后,cust_id?变成了索引,将它还原成一列
df3?=?pd.merge(left=customers,right=df2,on=['cust_id'],how='left')[['cust_name','cust_state','count']].sort_values(['cust_name']).fillna(0)
#?合并数据,并将缺失值改为0
df3['count']?=?df3['count'].astype('int')
#?因为缺失值的存在,合并数据时,该字段变成了float类型,将它改回来
df3

```

15.联结表
------

### 15.2 创建联结

所要查询的列不在一个表中

```
#?sql?使用等值联结
select?vend_name,prod_name,prod_price?from?vendors,products?where?vendors.vend_id=products.vend_id?order?by?vend_name,prod_name

#?Django-ORM
Products.objects.all().values(‘prod_name’,‘prod_price’,vend_name=F(‘vend_id__vend_name’)).order_by(‘vend_name’,‘prod_name’)

#?该语句只是结果达到与sql相同,但是使用?connection.queries[-1][‘sql’]?查看原生sql?发现,使用的是?FROM?products?INNER?JOIN?vendors?ON?…?它使用的是内联结

#?内联结和等值联结在sql层面只是写法不同,内部是相同的

#?pandas
pd.merge(left=vendors,right=products,on='vend_id',how='inner')[['vend_name','prod_name','prod_price']].sort_values(['vend_name','prod_name'])

```

### 15.2.2 内部联结

```
#?sql?内联结
select?vend_name,prod_name,prod_price?from?vendors?inner?join?products?on?vendors.vend_id=products.vend_id

#?Django-ORM
Products.objects.all().values(‘prod_name’,‘prod_price’,vend_name=F(‘vend_id__vend_name’))

#?pandas
pd.merge(left=vendors,right=products,on='vend_id',how='inner')[['vend_name','prod_name','prod_price']]

```

### 15.2.3 联结多个表

查询编号为20005的订单中的物品

```
#?sql
select?prod_name,vend_name,prod_price,quantity?from?orderitems,products,vendors?where?products.vend_id=vendors.vend_id?and?orderitems.prod_id=products.prod_id?and?order_num=20005

#?Django-ORM
Orderitems.objects.filter(order_num=20005).values(‘quantity’,prod_name=F(‘prod_id__prod_name’),prod_price=F(‘prod_id__prod_price’),vend_name=F(‘prod_id__vend_id__vend_name’))

#?该语句只是结果达到与sql相同,但是使用?connection.queries[-1][‘sql’]?查看原生sql?发现?
#?‘SELECT?orderitems.quantity,?products.prod_name?AS?prod_name,?products.prod_price?AS?prod_price,?vendors.vend_name?AS?vend_name?FROM?orderitems?INNER?JOIN?products?ON?(orderitems.prod_id?=?products.prod_id)?INNER?JOIN?vendors?ON?(products.vend_id?=?vendors.vend_id)?WHERE?orderitems.order_num?=?20005?LIMIT?21’

#?说明:?要想采用?INNER?JOIN?的方式进行查询,Django-ORM必须是从子表向主表查询,也就是正向查询

#?pandas
df?=?pd.merge(left=pd.merge(left=vendors,?right=products,?on='vend_id',?how='inner'),?right=orderitems,?on=['prod_id'],?how='inner')
df.loc[df['order_num']==20005,['prod_name',?'vend_name',?'prod_price',?'quantity']]

```

### 15.2.3 第二示例

查询订购物品 TNT2 的所有客户

```
#?sql?第14章的例子?将子查询变更为联结查询
select?cust_name,cust_contact?from?customers,orders,orderitems?where?customers.cust_id=orders.cust_id?and?orderitems.order_num=orders.order_num?and?prod_id='tnt2'

#?Django-ORM
Orderitems.objects.filter(prod_id=‘tnt2’).values(cust_name=F(‘order_num__cust_id__cust_name’),cust_contact=F(‘order_num__cust_id__cust_contact’))

#?pandas
df?=?pd.merge(left=pd.merge(left=customers,?right=orders,?on=['cust_id'],?how='inner'),?right=orderitems,?on=['order_num'],?how='inner')
df.loc[df['prod_id']?==?'TNT2',?['cust_name',?'cust_contact']]

```

16.创建高级联结
---------

### 16.1 使用表别名

```
#?sql
select?cust_name,cust_contact?from?customers?as?c,orders?as?o,orderitems?as?oi?where?c.cust_id=o.cust_id?and?oi.order_num=o.order_num?and?prod_id='tnt2'

#?Django-ORM
#?Django-Django-ORM?无需使用表别名

#?pandas
#?pandas?无需使用表别名

```

### 16.2.1 自联结

假如你发现某物品(其ID为DTNTR)存在问题,因此想知道生产该物品的供应商旗下的其它物品是否也存在这些问题

*   子查询
    

```
#?sql?子查询
select?prod_id,prod_name?from?products?where?vend_id=(select?vend_id?from?products?where?prod_id='dtntr')

#?Django-Django-ORM子查询
Products.objects.filter(vend_id=Products.objects.get(prod_id=‘dtntr’).vend_id).values(‘prod_id’,‘prod_name’)

#?pandas
products.loc[products['vend_id']?==?products.loc[products['prod_id']?==?'DTNTR']['vend_id'].values[0],?['prod_id',?'prod_name']]

```

*   自联结
    

```
#?sql?自联结
select?p1.prod_name,p1.prod_id?from?products?as?p1,products?as?p2?where?p1.vend_id=p2.vend_id?and?p2.prod_id='DTNTR'

#?Django-ORM
#?写不出来…

#?pandas
pd.merge(left=products,right=products.loc[products['prod_id']=='DTNTR',['vend_id']],on=['vend_id'])[['prod_id',?'prod_name']]

```

### 16.2.3 外部联结

检索客户和他们的订单,包括没有订单的客户

```
#?sql
select?customers.cust_id,order_num?from?customers?left?outer?join?orders?on?customers.cust_id=orders.cust_id

#?Django-ORM
Customers.objects.all().values(‘cust_id’,order_num=F(‘orders__order_num’))

#?Django-Django-ORM的反向查询就是外部联结

#?pandas
#?作为合并条件的列,合并之后只会留下一列,取列时不必加后缀
pd.merge(left=customers,?right=orders,?on=['cust_id'],?how='left')[['cust_id',?'order_num']]

```

### 16.3 使用带聚集函数的联结

检索所有客户及每个客户所下的订单数

```
#?sql
select?customers.cust_name,customers.cust_id,count(orders.order_num)?as?num_ord?from?customers?inner?join?orders?on?customers.cust_id=orders.cust_id?group?by?customers.cust_id

#?Django-ORM
Orders.objects.all().values(‘cust_id’).annotate(num_ord=Count(‘order_num’)).values(‘num_ord’,‘cust_id’,cust_name=F(‘cust_id__cust_name’))

#?pandas
pd.merge(left=customers,?right=orders,?on=['cust_id'],?how='inner')[['cust_id',?'order_num',?'cust_name']].groupby(['cust_id',?'cust_name'],?as_index=False).agg({'order_num':?'count'}).rename(columns={'order_num':'count'})

```

包含没有下订单的客户

```
#?sql
select?customers.cust_name,customers.cust_id,count(orders.order_num)as?num_ord?from?customers?left?outer?join?orders?on?customers.cust_id=orders.cust_id?group?by?customers.cust_id

#?Django-ORM
Customers.objects.values(‘cust_id’).annotate(num_ord=Count(‘orders__order_num’)).values(‘cust_id’,‘cust_name’,‘num_ord’)

#?pandas
pd.merge(left=customers,?right=orders,?on=['cust_id'],?how='left')[['cust_id',?'order_num',?'cust_name']].groupby(['cust_id',?'cust_name'],?as_index=False).agg({'order_num':?'count'}).rename(columns={'order_num':'count'})

```

17.组合查询
-------

### 17.2.1 使用 UNION

查询价格小于等于5的所有商品,并且包括供应商1001,1002的所有商品(不考虑价格)

```
#?sql
select?vend_id,prod_id,prod_price?from?products?where?prod_price<=5?union?select?vend_id,prod_id,prod_price?from?products?where?vend_id?in?(1001,1002)

#?Django-ORM
Products.objects.filter(prod_price__lte=5).values(‘vend_id’,‘prod_id’,‘prod_price’).union(Products.objects.filter(vend_id__in=(1001,1002)).values(‘vend_id’,‘prod_id’,‘prod_price’))

#?也可以省略第二句的values()
Products.objects.filter(prod_price__lte=5).values(‘vend_id’,‘prod_id’,‘prod_price’).union(Products.objects.filter(vend_id__in=(1001,1002)))

#?pandas
pd.concat((products.loc[products['prod_price']?<=?5,?['vend_id',?'prod_id',?'prod_price']],?products.loc[products['vend_id'].isin((1001,?1002)),?['vend_id',?'prod_id',?'prod_price']]),?join='inner').drop_duplicates(subset=['prod_id'],?ignore_index=True)

```

### 17.2.3 包含重复的行

```
#?sql
select?vend_id,prod_id,prod_price?from?products?where?prod_price<=5?union?all?select?vend_id,prod_id,prod_price?from?products?where?vend_id?in?(1001,1002)

#?Django-Django-ORM指定参数?union(…,?all=True)
Products.objects.filter(prod_price__lte=5).values(‘vend_id’,‘prod_id’,‘prod_price’).union(Products.objects.filter(vend_id__in=(1001,1002)),all=True)

pd.concat((products.loc[products['prod_price']?<=?5,?['vend_id',?'prod_id',?'prod_price']],?products.loc[products['vend_id'].isin((1001,?1002)),?['vend_id',?'prod_id',?'prod_price']]),?join='inner',?ignore_index=True)

```

### 17.2.4 对组合查询结果排序

在使用UNION组合查询时,只能使用一条ORDER BY 子句,它必须出现在最后一条SELECT语句之后,对于结果集,不存在用一种方式排序一部分,用另一种方式排序另一部分的情况

```
#?sql
select?vend_id,prod_id,prod_price?from?products?where?prod_price<=5?union?select?vend_id,prod_id,prod_price?from?products?where?vend_id?in?(1001,1002)?order?by?vend_id,prod_price

#?Django-ORM
Products.objects.filter(prod_price__lte=5).values(‘vend_id’,‘prod_id’,‘prod_price’).union(Products.objects.filter(vend_id__in=(1001,1002))).order_by(‘vend_id’,‘prod_price’)

#?pandas
pd.concat((products.loc[products['prod_price']?<=?5,?['vend_id',?'prod_id',?'prod_price']],?products.loc[products['vend_id'].isin((1001,?1002)),?['vend_id',?'prod_id',?'prod_price']]),?join='inner').drop_duplicates(subset=['prod_id']).sort_values(['vend_id',?'prod_price'],?ignore_index=True)

```

18.全文本搜索

19.插入数据

20.更新和删除数据

21.创建和操纵表

22.使用视图

23.使用存储过程

![](https://img-blog.csdnimg.cn/img_convert/be2eb2864e0541ac1c554d6afac1bbeb.png)  

  

---