Django 多表操作

增删改查

增加

# 增加
    # 一对一
    # au_obj = models.AuthorDetail.objects.get(id=4)

    models.Author.objects.create(
        name='海狗',
        age=59,
        # 两种方式
        au_id=4
        # au=au_obj
    )

    # 一对多
    # pub_obj = models.Publish.objects.get(id=3)
    #
    # models.Book.objects.create(
    #     title='xx2',
    #     price=13,
    #
    #     publishDate='2011-11-12',
    #     # publishs=pub_obj , #类属性作为关键字时,值为model对象
    #     publishs_id=3  # 如果关键字为数据库字段名称,那么值为关联数据的值
    # )

    # 多对多  -- 多对多关系表记录的增加
    # ziwen = models.Author.objects.get(id=3)
    # haigou = models.Author.objects.get(id=5)

    new_obj = models.Book.objects.create(
        title='海狗产后护理第二部',
        price=0.5,
        publishDate='2019-09-29',
        publishs_id=2,
    )

    new_obj.authors.add(3,5)  #  #*args  **kwargs
    new_obj.authors.add(*[3,5])  # 用的最多,
    new_obj.authors.add(ziwen, haigou)

删除

# 删除
    # 一对一
    # models.AuthorDetail.objects.filter(id=3).delete()
    # models.Author.objects.filter(id=3).delete()
    # 一对多
    # models.Publish.objects.filter(id=3).delete()
    # models.Book.objects.filter(id=4).delete()

# 多对多
    book_obj = models.Book.objects.get(id=2)
    # book_obj.authors.add()  # 添加
    # book_obj.authors.remove(1)  #删除  2  1
    
    # book_obj.authors.clear()  # 清除 
    # book_obj.authors.set(['1','5'])  # 先清除再添加,相当于修改

改

    # 改
    # ret = models.Publish.objects.get(id=2)
    # models.Book.objects.filter(id=5).update(
    #     # title='华丽丽',
    #     publishs=ret,
    #     # publishs_id=1,
    # )

基于对象的跨表查询

    # 查询
    # 一对一
    # 关系属性写在表1,关联到表2,那么通过表1的数据去找表2的数据,叫做正向查询,返过来就是反向查询
    # 查询一下王洋的电话号码

    # 正向查询  对象.属性
    # obj = models.Author.objects.filter(name='王洋').first()
    # ph = obj.au.telephone
    # print(ph)

    # 查一下电话号码为120的作者姓名
    # 反向查询  对象.小写的表名
    # obj = models.AuthorDetail.objects.filter(telephone=120).first()
    # ret = obj.author.name  #陈硕
    # print(ret)

    # 一对多
    # 查询一下 海狗的怂逼人生这本书是哪个出版社出版的  正向查询
    # obj = models.Book.objects.filter(title='海狗的怂逼人生').first()
    # ret = obj.publishs.name
    # print(ret)  #24期出版社
    #  查询一下 24期出版社出版过哪些书
    # obj = models.Publish.objects.filter(name='24期出版社').first()
    #
    # ret = obj.book_set.all() #<QuerySet [<Book: 母猪的产后护理>, <Book: 海狗的怂逼人生>]>
    # for i in ret:
    #     print(i.title)

    # 多对多
    # 海狗的怂逼人生 是哪些作者写的 -- 正向查询
    # obj = models.Book.objects.filter(title='海狗的怂逼人生').first()
    # ret = obj.authors.all()
    #
    # print(ret)  #<QuerySet [<Author: 王洋>, <Author: 海狗>]>
    # for i in ret:
    #     print(i.name)

    # 查询一下海狗写了哪些书 -- 反向查询
    # obj = models.Author.objects.filter(name='海狗').first()
    # ret = obj.book_set.all()
    # print(ret)
    # for i in ret:
    #     print(i.publishs.name)
    #     print(i.title)
    # return HttpResponse('ok')

admin添加用户

python manage.py createsuperuser
输入用户名:wuchao
邮箱不用输 直接回车
输入密码:必须超过8位,并且别太简单

admin注册

from django.contrib import admin

# Register your models here.

from app01 import models

admin.site.register(models.Author)
admin.site.register(models.AuthorDetail)
admin.site.register(models.Publish)
admin.site.register(models.Book)

基于双下划线的跨表查询（连表查询 join）

	select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name='技术';
	select emp.name from dep inner join emp on emp.dep_id = dep.id where dep.name='技术';

示例:

# 一对一
# 查询一下王洋的电话号码

# ret = models.Author.objects.filter(name='王洋').values('au__telephone')

# ret = models.AuthorDetail.objects.filter(author__name='王洋').values('telephone')
# print(ret) #<QuerySet [{'au__telephone': '110'}]> #<QuerySet [{'telephone': '110'}]>
#

# 一对多
# 海狗的怂逼人生这本书是哪个出版社出版的
# ret = models.Book.objects.filter(title='海狗的怂逼人生').values('publishs__name')
# print(ret) #<QuerySet [{'publishs__name': '24期出版社'}]>
# ret = models.Publish.objects.filter(book__title='海狗的怂逼人生').values('name')
# print(ret) #<QuerySet [{'name': '24期出版社'}]>


#查询一下24期出版社出版了哪些书
# ret = models.Publish.objects.filter(name='24期出版社').values('book__title')
# print(ret) #<QuerySet [{'book__title': '华丽的产后护理'}, {'book__title': '海狗的怂逼人生'}]>

# ret = models.Book.objects.filter(publishs__name='24期出版社').values('title')
# print(ret) #<QuerySet [{'title': '华丽的产后护理'}, {'title': '海狗的怂逼人生'}]>

# 多对多
#海狗的怂逼人生 是哪些作者写的
# ret = models.Book.objects.filter(title='海狗的怂逼人生').values('authors__name')
# print(ret)

# ret = models.Author.objects.filter(book__title='海狗的怂逼人生').values('name')
# print(ret) #<QuerySet [{'name': '王洋'}, {'name': '海狗'}]>
# return render(request,'index.txt',{'x':123,'y':456})


# related_name
# 查询一下24期出版社出版了哪些书
ret = models.Publish.objects.filter(name='24期出版社').values('xxx__title') #xxx代替反向查询的小写表名
print(ret)

聚合查询

ret = models.Book.objects.all().aggregate(a=Avg('price'),m=Max('price'))
print(ret) 
#{'price__avg': 45.1, 'price__max': Decimal('200.00')} python字典格式,也就是说,聚合查询是orm语句的结束

分组查询

    # 每个出版社出版的书的平均价格
    # 用的是publish表的id字段进行分组
    # ret = models.Book.objects.values('publishs__id').annotate(a=Avg('price'))
    # 用的book表的publishs_id字段进行分组
    # ret = models.Book.objects.values('publishs_id').annotate(a=Avg('price'))
    # print(ret)
    # ret = models.Publish.objects.annotate(a=Avg('book__price')).values('a')
    # print(ret) #<QuerySet [{'a': None}, {'a': 71.166667}, {'a': 6.0}]>

F查询

from django.db.models import Avg, Sum, Max, Min, Count,F
查询一下评论数大于点赞数的书
# ret = models.Book.objects.filter(comment__gt=F('good'))
# print(ret)

将所有书的价格上调100块
# models.Book.objects.all().update(
#     price=F('price')+100
# )

Q查询

与 & 或 | 非 ~

    from django.db.models import Avg, Sum, Max, Min, Count, F,Q
    ret = models.Book.objects.filter(Q(id=2)&Q(Q(price__gt=112)|~Q(comment__lte=200)))
    print(ret)

settings

#配置数据库
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'library02',
        'USER':'root',
        'PASSWORD':'123',
        'HOST':'127.0.0.1',
        'PORT':3306,
    }
}

#将orm转换为sql语句

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}

#设置静态文件及别名
STATIC_URL = '/static/'

# STATICFILES_DIRS = [
#     os.path.join(BASE_DIR,"jingtaiwenjian")
# ]

urls

from django.conf.urls import url
from django.contrib import admin
from app01 import views

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^index/', views.index),
]

init

import pymysql
pymysql.install_as_MySQLdb()

admin

from django.contrib import admin
from app01 import models
# Register your models here.

admin.site.register(models.Author)
admin.site.register(models.AuthorDetail)
admin.site.register(models.Book)
admin.site.register(models.Publish)

models

from django.db import models

# Create your models here.

class Author(models.Model):
    """
    作者表
    """
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    sex = models.CharField(max_length=20, default='male')
    # authorDetail=models.OneToOneField(to="AuthorDetail",to_field="nid",on_delete=models.CASCADE)
    au = models.OneToOneField("AuthorDetail",on_delete=models.CASCADE)

    def __str__(self):
        return self.name

class AuthorDetail(models.Model):
    """
    作者详细信息表
    """
    birthday = models.DateField()
    telephone = models.CharField(max_length=11)
    addr = models.CharField(max_length=64)
    # class Meta:
        # db_table='authordetail' #指定表名
        # ordering = ['-id',]
    def __str__(self):
        return self.telephone + self.addr


class Publish(models.Model):
    """
    出版社表
    """
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    def __str__(self):
        return self.name

class Book(models.Model):
    """
    书籍表
    """
    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5,decimal_places=2)
    comment = models.FloatField(default=100)  # 评论数
    good = models.FloatField(default=100)  # 点赞数
    # publishs=models.ForeignKey(to="Publish",on_delete=models.CASCADE 级联,related_name='xxx' 设置别名)
    publishes = models.ForeignKey(to="Publish",on_delete=models.CASCADE,)
    authors = models.ManyToManyField(to='Author',)

    def __str__(self):
        return self.title

连接数据库

TOOLS -- run manage.py -- task
makemigrations
migrate

admin创表

createsuperuser  输入用户名和密码

views

from django.shortcuts import render,HttpResponse,redirect
from app01 import models
from django.db.models import Avg,Sum,Max,Min,Count,F,Q
# Create your views here.

def index(request):

    #1.增加 一对一
    # models.AuthorDetail.objects.create(
    #     birthday="2000-11-11",
    #     telephone="555",
    #     addr="美国",
    # )

    # au_obj = models.AuthorDetail.objects.get(id=5)

    # models.Author.objects.create(
    #     name="海王",
    #     age=66,
    #     au_id=5,
    #     #或者 au="au_obj"
    # )

    # 2. 一对多 书籍和出版社
    # pub_obj = models.Publish.objects.get(id=3)
    #
    # models.Book.objects.create(
    #     title="xx2",
    #     price=33,
    #     publishDate="2011-11-12",
    #     comment=324,
    #     good=444,
    #     publishes=pub_obj, #类属性作为关键字时,值为model对象
    #     # publishes_id=3, # 如果关键字为数据库字段名称,那么值为关联数据的值
    # )

    # 3. 多对多 书籍和作者
    # sese = models.Author.objects.get(id=1)
    # xingxing = models.Author.objects.get(id=2)
    #
    # new_obj = models.Book.objects.create(
    #     title="海贼王",
    #     price="45",
    #     publishDate="2009-11-14",
    #     comment=399,
    #     good=666,
    #     publishes_id=2,
    # )

    # 三种方法
    # new_obj.authors.add(*[1,2]) #用的最多
    # new_obj.authors.add(1,2)
    # new_obj.authors.add(sese,xingxing)

    # 4. 删除 一对一
    # models.AuthorDetail.objects.filter(id=5).delete()
    # models.Author.objects.filter(id=4).delete()

    # 5.  删除 一对多
    # models.Publish.objects.filter(id=4).delete()
    # models.Book.objects.filter(id=4).delete()

    # 6.删除 多对多
    # book_obj = models.Book.objects.get(id=8)
    # # book_obj.authors.add() #添加
    # book_obj.authors.remove(1)  # 删除作者1，书籍8

    # book_obj.authors.clear()  #清除关系
    # book_obj.authors.set(["3","4"])  #先清除再添加

    # 7. 改 修改书籍名和出版社
    # ret = models.Publish.objects.get(id=2)
    # models.Book.objects.filter(id=7).update(
    #     title="ooo",
    #     publishes=ret,
    #     # publishes_id=2
    # )

    # 8. 基于对象的跨表查询 一对一
    # 关系属性写在表1,关联到表2,那么通过表1的数据去找表2的数据,叫做正向查询,返过来就是反向查询
    # 正向查询用对象.属性，反向查询用对象.小写的表名

    # 1.查询韩星的电话号 str原因
    # obj = models.Author.objects.filter(name="韩星").first()
    # print(type(obj))
    # ph = obj.au.telephone
    # print(ph)

    # 反向查询已知电话444，查作者
    # obj = models.AuthorDetail.objects.filter(telephone=444).first()
    # pa = obj.author.name
    # print(pa)

    # 9. 基于对象的跨表查询 一对多
    # 已知书籍名，查询出版社，正向
    # obj = models.Book.objects.filter(title="大主宰").first()
    # ph = obj.publishes.name
    # print(ph)

    # 已知出版社，查询所有书籍,反向,对象.表名_set.all()
    # obj = models.Publish.objects.filter(name="24期出版社").first()
    # ret = obj.book_set.all() # <QuerySet [<Book: 风云>, <Book: 莽荒纪>, <Book: ooo>, <Book: 海贼王>]>
    # print(ret)
    # for i in ret:
    #     print(i.title)

    # 10.多对多
    # 已知书籍名，查询作者,正向查询
    # obj = models.Book.objects.filter(title="万剑道尊").first()
    # ret = obj.authors.all() #<QuerySet [<Author: 邸宗超>, <Author: 韩星>, <Author: 李文宝>]>
    # for i in ret:
    #     print(i.name)
    #     print(i.au.telephone)  #连环查询，有对象，正向查询

    # 已知作者名，查看书籍
    # obj = models.Author.objects.filter(name="韩星").first()
    # ret = obj.book_set.all()
    # print(ret)  #<QuerySet [<Book: 朝花夕拾>, <Book: 浮云>, <Book: 大主宰>, <Book: 万剑道尊>]>
    # for i in ret:
    #     print(i.title)

    # 11. 基于双下划线的跨表查询
    # 一对一 正向
    # 已知作者名，查询电话号
    # ret1 = models.Author.objects.filter(name="韩星").values("au__telephone")
    # print(ret1)  #<QuerySet [{'au__telephone': '222'}]>
    # ret2 = models.AuthorDetail.objects.filter(author__name="韩星").values("telephone")
    # print(ret2)  #<QuerySet [{'telephone': '222'}]>
    # for i in ret1:
    #     print(i["au__telephone"])
    # for j in ret2:
    #     print(j["telephone"])

    # 一对多
    # 已知书名查询出版社
    # ret1 = models.Book.objects.filter(title="大主宰").values("publishes__name")
    # print(ret1)  # <QuerySet [{'publishes__name': '老男人出版社'}]>
    # ret2 = models.Publish.objects.filter(book__title="大主宰").values("name")
    # print(ret2)  # <QuerySet [{'name': '老男人出版社'}]>
    # for i in ret1:
    #     print(i["publishes__name"])
    # for j in ret2:
    #     print(j["name"])

    # 已知出版社，查询书籍名
    # ret1 = models.Publish.objects.filter(name="24期出版社").values("book__title")
    # print(ret1)
    # ret2 = models.Book.objects.filter(publishes__name="24期出版社").values("title")
    # print(ret2)
    # for i in ret1:
    #     print(i["book__title"])
    # for j in ret2:
    #     print(j["title"])

    # 多对多
    # 已知书籍名，查询作者
    # ret1 = models.Book.objects.filter(title="万剑道尊").values("authors__name")
    # print(ret1)
    # ret2 = models.Author.objects.filter(book__title="万剑道尊").values("name")
    # print(ret2)
    # for i in ret1:
    #     print(i["authors__name"])

    # 12. 聚合查询
    # 统计所有书籍的平均价格,别名可以不写
    # ret1 = models.Book.objects.all().aggregate(a=Avg("price"),m=Max("price"))
    # print(ret1)

    # 每个出版社出版书的平均价格
    # 1.根据publish表的id字段进行分组，别名可以不写
    # ret1 = models.Book.objects.values("publishes__id").annotate(a=Avg("price"))
    # print(ret1)
    # 2. 根据book的publishs_id字段进行分组，等同于publishes
    # ret2 = models.Book.objects.values("publishes_id").annotate(a=Avg("price"))
    # print(ret2)
    # ret3 = models.Book.objects.values("publishes","id").annotate(a=Avg("price"))
    # print(ret3) #多条件分组

    # 此方法连表查询（book__price),查询的值是a，默认用publish的id分组
    # ret4 = models.Publish.objects.annotate(a=Avg("book__price")).values("a")
    # print(ret4)

    # F查询
    #查询一下评论数大于点赞数的书
    # ret = models.Book.objects.all()
    # l1 = []
    # for i in ret:
    #     if i.comment > i.good:
    #         l1.append(i)
    # for j in l1:
    #     print(j.title)

    # 双下方法
    # ret = models.Book.objects.filter(comment__gt=F("good"))
    # print(ret)
    # for i in ret:
    #     print(i.title)

    #将所有的书籍价格上调100块
    # ret = models.Book.objects.all()
    # for i in ret:
    #     i.price += 100
    #     i.save()

    # models.Book.objects.all().update(
    #     price=F("price") - 100
    # )

    # Q查询 或 |   与 &   非~
    # 查询书籍价格大于80并且评论数大于等于200
    # ret = models.Book.objects.filter(price__gt=80,comment__gte=200) #和，
    # print(ret)

    # 查询书籍价格大于80或者评论数大于等于600
    # ret = models.Book.objects.filter(Q(price__gt=80)|Q(comment__gte=600))
    # print(ret)

    #查询id=2，书籍价格大于80或者评论数大于等于600

    # ret = models.Book.objects.filter(Q(Q(price__gt=80)|Q(comment__gte=600))&Q(id=2))
    # print(ret)

    # 1 查询每个作者的姓名以及出版的书的最高价格
    # ret1 = models.Author.objects.values("name").annotate(m=Max("book__price"))
    # print(ret1)

    # ret2 = models.Book.objects.values("authors__name").annotate(Max("price"))
    # print(ret2) #有点小错误

    # 2 查询作者id大于2作者的姓名以及出版的书的最高价格

    # ret1 = models.Author.objects.filter(id__gt=2).annotate(m=Max("book__price")).values("name","m")
    # print(ret1)

    # 3 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
     # 不对，等于不用Q，直接逗号    # ret1 = models.Author.objects.filter(Q(id__gt=2)|Q(age__gte=20)&Q(sex="female")).annotate(m=Max("book__price")).values("sex","m")
    # ret1 = models.Author.objects.filter(Q(id__gt=2)|Q(age__gte=20),sex="female").annotate(m=Max("book__price")).values("sex","m")
    # print(ret1)

    # 4 查询每个作者出版的书的最高价格的平均值
    # ret1 = models.Author.objects.values("name").annotate(m=Max("book__price")).aggregate(a=Avg('m'))
    # # ret1 = models.Author.objects.annotate(m=Max("book__price")).aggregate(a=Avg('m')) # 默认id分组
    # print(ret1)

    # 5 每个作者出版的所有书的最高价格以及最高价格的那本书的名称
    ret1 = models.Author.objects.annotate(m=Max("book__price")).values("name","m")
    print(ret1)
    return HttpResponse("OK")