Django入门到放弃之ORM多表操作
1 图书表：book,作者表：author，作者详情表：authordetail，出版社表：publish,(第三张中间表)
2 作者跟作者详情：是一对一，关联字段写在哪一方都可以
3 图书跟出版社：是一对多，一对多关系一旦确立，关联字段写在多的一方
4 图书和作者：是多对多，多对多的关系需要建立第三张表（可以自动生成）
5 models.py中把关系建立出来
from django.db import models
### django: 1.11.1 2.0.7
# Create your models here.
class Publish(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
addr = models.CharField(max_length=64)
phone = models.CharField(max_length=64)
email = models.EmailField()
class Book(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
price = models.DecimalField(max_digits=6, decimal_places=2)
publish_date = models.DateTimeField(auto_now_add=True)
# to='Publish'跟Publish表做关联（ForeignKey，一对多）
# to_field='id'跟哪个字段做关联
# publish=models.CharField(max_length=32)
# publish=models.ForeignKey(to='Publish',to_field='id')
# publish = models.ForeignKey(to='Publish') # 不写，默认跟主键做关联
publish = models.ForeignKey(to=Publish) # 不写，默认跟主键做关联
# 自动创建出第三张表(这句话会自动创建第三章表)
# authors在数据库中不存在该字段，没有to_field
# 默认情况：第三张表有id字段，当前Book表的id和Author表的id字段
authors=models.ManyToManyField(to='Author')
#db_constraint=False 如果使用两个表之间存在关联,首先db_constraint=False 把关联切断,但保留链表查询的功能,其次要设置null=True, blank=True,注意on_delete=models.SET_NULL 一定要置空,这样删了不会影响其他关联的表
class Author(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
age = models.SmallIntegerField()
# 一对一的本质是 ForeignKey+unique
author_detail=models.OneToOneField(to='AuthorDetail',to_field='id')
# author_detail=models.ForeignKey(to='AuthorDetail',to_field='id',unique=True)
class AuthorDetail(models.Model):
id = models.AutoField(primary_key=True)
sex = models.SmallIntegerField()
addr = models.CharField(max_length=64)
phone = models.BigIntegerField()
6 同步到mysql数据库
-配置文件
-pymysql.install_as_mysqldb()
-公司可以用过的mysqlclient
-两条命令
7 2.x版本的django
-外键字段必须加 参数：on_delete
-1.x版本不需要，默认就是级联删除
-假设，
删除出版社，该出版社出版的所有图书也都删除，on_delete=models.CASCADE
删除出版社，该出版社出版的图书不删除，设置为空on_delete=models.SET_NULL,null=True
删除出版社，该出版社出版的图书不删除，设置为默认on_delete=models.SET_DEFAULT,default=0
#1.1 一对一增加
# new_author_detail = models.AuthorDetail.objects.create(
# birthday='1979-08-08',
# telephone='138383838',
# addr='黑龙江哈尔滨'
# )
# obj = models.AuthorDetail.objects.filter(addr='山西临汾').first()
#方式1
# models.Author.objects.create(
# name='王涛',
# age='40',
# authorDetail=new_author_detail,
# )
# 方式2 常用
# models.Author.objects.create(
# name='王涛',
# age='40',
# authorDetail_id=obj.id,
# )
#方式1
# obj = models.Publish.objects.get(id=2)
# models.Book.objects.create(
# title = '李帅的床头故事',
# publishDate='2019-07-22',
# price=3,
# # publishs=models.Publish.objects.get(id=1),
# publishs=obj,
#
# )
# 方式2 常用
# models.Book.objects.create(
# title='李帅的床头故事2',
# publishDate='2019-07-21',
# price=3.5,
# # publishs=models.Publish.objects.get(id=1),
# publishs_id=obj.id
#
# )
# 总结：
1 email可以不传email，本质就是varchar（admin中会判断）
2 新增图书：
-publish=publish
-publish_id=publish.id
3 写在表模型中的publish字段，到数据库中会变成publish_id(ForeignKey)
4 查到book对象以后
-book.publish 对象
-book.publish_id id号，数字
1 自动创建的表，表模型就拿不到，book.authors代指表模型
# 多对多，作者和书
# 给西游记这本书新增两个作者lqz和egon
# 去到西游记这本书
# book=models.Book.objects.get(name='西游记')
# 代指中间表book.authors
# lqz=models.Author.objects.get(id=2)
# egon=models.Author.objects.get(id=3)
# book.authors.add(2,3) # 新增作者,通过id新增
# # book.authors.add(lqz,egon) # 新增作者,通过对象新增
# book.authors.add(2,egon) # 新增作者,通过对象新增
# 西游记删除一个作者
# book = models.Book.objects.get(name='西游记')
# book.authors.remove(2)
# egon = models.Author.objects.get(id=3)
# book.authors.remove(egon)
# clear 清空所有作者
book = models.Book.objects.get(name='西游记')
# book.authors.add(2, 3)
# book.authors.clear()
# set 先清空，再add,前提是不存在的作者
book.authors.set([4, ])
# add ,remove，set clear
# 一对一 表一外键关联到表二,表一删除,不影响表2,表2删除会影响表1
# models.AuthorDetail.objects.get(id=2).delete()
# models.Author.objects.get(id=3).delete()
# 一对多
# models.Publish.objects.get(id=1).delete()
# models.Book.objects.get(nid=1).delete()
# book_obj = models.Book.objects.get(nid=6)
# book_obj.authors.remove(6)
# book_obj.authors.remove(*[5,6])
# book_obj.authors.clear()
# book_obj.authors.add(*[1,])
# book_obj.authors.set('1')
# book_obj.authors.set(['5','6']) #删除然后更新
add ,remove，set clear
# 一对一
# models.Author.objects.filter(id=5).update(
# name='崔老师',
# age=16,
# # authorDetail=models.AuthorDetail.objects.get(id=5),
# authorDetail_id=4,
# )
# models.Book.objects.filter(pk=4).update(
# title='B哥的往事2',
# # publishs=models.Publish.objects.get(id=3),
# publishs_id=3,
# )
models.Publish.objects.filter(pk=2).update(
id=4, # 没有级联更新,报错!!
)
关系属性(字段)写在哪个类(表)里面,从当前类(表)的数据去查询它关联类(表)的数据叫做正向查询,反之叫做反向查询
''' 正向查询:Authorobj.authorDetail,对象.关联属性名称
Author----------------------------------->AuthorDetail
<-----------------------------------
反向查询:AuthorDetailobj.author ,对象.小写类名
'''
# 跨表查询有两种方式
-基于对象的跨表查询：子查询
-基于双下划线的跨表查询：关联查询，连表查询
# 基于对象的跨表查询
-查询主键为1的书籍的出版社所在的城市
# 基于对象的跨表查询（子查询）
# 一对多
# 查询主键为1的书籍的出版社所在的城市
# book=models.Book.objects.get(id=1) # 第一次查询
# # book=models.Book.objects.filter(id=1).first()
# publish=book.publish # 内部又执行了一次查询，根据publish_id查询publish
# print(publish.addr)
# 北京出版社出版的所有书籍
# publish=models.Publish.objects.get(name='北京出版社') # 第一次查询了出版社
# books=publish.book_set.all() # 表名小写_set # 第二次，根据出版社id，查询所有书
# print(books)
# 正向查询：book表内有publish字段 直接对象.字段名
# 反向查询：publish表内没有book字段，出版社对象.Book小写_set.all()
### 一对一
# 查询所有住址在山东的作者的姓名
# 反向查询：author_detail没有author字段，author_detail.表明小写
# author_detail=models.AuthorDetail.objects.filter(addr__contains='山东').first()
# # 反向
# print(author_detail.author.name)
# 查询egon作者的地址
# 正向
# author=models.Author.objects.get(name='egon')
# print(author.author_detail.addr)
# 多对多关系查询
#金x梅所有作者的名字以及手机号
# book=models.Book.objects.get(name='金x梅')
# # 正向
# authors=book.authors.all()
# for author in authors:
# print(author.name)
# print(author.author_detail.phone)
# 反向 查询egon出过的所有书籍的名字
# egon=models.Author.objects.get(name='egon')
# books=egon.book_set.all()
# for book in books:
# print(book.name)
# 连表查询
# 基于对象的跨表查询，先查对象，通过对象再去查另一个对象（正向：字段名，反向：表名小写/表名小写_set.all()）
# 地址为山东的作者写的所有书
# author_detail=models.AuthorDetail.objects.get(addr='山东')
# author=author_detail.author
# books=author.book_set.all()
# print(books[0].name)
# （作业）地址为山东的作者写的所有书的出版社名字
### 基于双下划线的跨表查之 一对多
# 正向：字段名
# 反向:表名小写
# filter，values,values_list(写 __ 跨表)
# 练习: 查询北京出版社出版过的所有书籍的名字与价格(一对多)
# SELECT `app01_book`.`name`, `app01_book`.`price` FROM `app01_publish` LEFT OUTER JOIN `app01_book` ON (`app01_publish`.`id` = `app01_book`.`publish_id`) WHERE `app01_publish`.`name` = '北京出版社' ;
# res=models.Publish.objects.filter(name='北京出版社').values('book__name','book__price')
# print(res)
#SELECT `app01_book`.`name`, `app01_book`.`price` FROM `app01_book` INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`) WHERE `app01_publish`.`name` = '北京出版社';
# res=models.Book.objects.filter(publish__name='北京出版社').values('name','price')
# print(res)
## 多对多
# 练习: 查询egon出过的所有书籍的名字,价格(多对多)
#反向
# res=models.Author.objects.filter(name='egon').values('book__name','book__price')
# print(res)
# 正向
# res=models.Book.objects.filter(authors__name='egon').values('name','price')
# print(res)
#查询egon的手机号
# res=models.Author.objects.filter(name='egon').values('author_detail__phone')
# print(res)
# res=models.AuthorDetail.objects.filter(author__name='egon').values('phone')
# print(res)
# 连续跨表
#查询北京出版社出版过的所有书籍的名字以及作者的姓名
# res=models.Publish.objects.filter(name='北京出版社').values('book__name','book__authors__name')
# print(res)
# res=models.Book.objects.filter(publish__name='北京出版社').values('name','authors__name')
# print(res)
# res=models.Author.objects.filter(book__publish__name='北京出版社').values('book__name','name')
# print(res)
# 手机号以189开头的作者出版过的所有 书籍名称 以及 出版社名称
# res=models.AuthorDetail.objects.filter(phone__startswith='189').values('author__book__name','author__book__publish__name')
# print(res)
# SELECT `app01_book`.`name`, `app01_publish`.`name` FROM `app01_author` INNER JOIN `app01_authordetail` ON (`app01_author`.`author_detail_id` = `app01_authordetail`.`id`) LEFT OUTER JOIN `app01_book_authors` ON (`app01_author`.`id` = `app01_book_authors`.`author_id`) LEFT OUTER JOIN `app01_book` ON (`app01_book_authors`.`book_id` = `app01_book`.`id`) LEFT OUTER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`) WHERE `app01_authordetail`.`phone` LIKE '189%' ;
res=models.Author.objects.filter(author_detail__phone__startswith='189').values('book__name','book__publish__name')
print(res)
###########1 聚合查询（聚合函数：最大，最小，和，平均，总个数）
from django.db.models import Avg,Max,Min,Count,Sum
#1 计算所有图书的平均价格
# aggregate结束，已经不是queryset对象了
# book=models.Book.objects.all().aggregate(Avg('price'))
# 起别名
# book=models.Book.objects.all().aggregate(avg=Avg('price'))
#2 计算总图书数
# book = models.Book.objects.all().aggregate(count=Count('id'))
# 3 计算最低价格的图书
# book = models.Book.objects.all().aggregate(min=Min('price'))
# 4 计算最大价格图书
# book = models.Book.objects.all().aggregate(max=Max('price'))
# print(book)
# annotate() 内写聚合函数
# values在前表示group by的字段
# values在后表示取某几个字段
# filter在前表示where
# filter在后表示having
####2 分组查询
'''
查询每一个部门名称以及对应的员工数
book:
id name price publish
1 金品 11.2 1
2 西游 14.2 2
3 东游 16.2 2
4 北邮 19.2 3
'''
# 示例一：查询每一个出版社id，以及出书平均价格
# select publish_id,avg(price) from app01_book group by publish_id;
# annotate
# from django.db.models import Avg, Count, Max, Min
# ret=models.Book.objects.values('publish_id').annotate(avg=Avg('price')).values('publish_id','avg')
# print(ret)
# 查询出版社id大于1的出版社id，以及出书平均价格
#select publish_id,avg(price) from app01_book where publish_id>1 group by publish_id;
# ret=models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).values('publish_id','avg')
# print(ret)
# 查询出版社id大于1的出版社id，以及出书平均价格大于30的
# select publish_id,avg(price)as aaa from app01_book where publish_id>1 group by publish_id HAVING aaa>30;
# ret = models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).filter(avg__gt=30).values(
# 'publish_id', 'avg')
# print(ret)
## 查询每一个出版社出版的书籍个数
# pk 代指主键
# ret=models.Book.objects.get(pk=1)
# print(ret.name)
# ret=models.Publish.objects.values('pk').annotate(count=Count('book__id')).values('name','count')
# print(ret)
# 如果没有指定group by的字段，默认就用基表（Publish）主键字段作为group by的字段
# ret=models.Publish.objects.annotate(count=Count('book__id')).values('name','count')
# print(ret)
# 另一种方式实现
# ret=models.Book.objects.values('publish').annotate(count=Count('id')).values('publish__name','count')
# print(ret)
#查询每个作者的名字，以及出版过书籍的最高价格(建议使用分组的表作为基表)
# 如果不用分组的表作为基表，数据不完整可能会出现问题
# ret=models.Author.objects.values('pk').annotate(max=Max('book__price')).values('name','max')
# ret = models.Author.objects.annotate(max=Max('book__price')).values('name', 'max')
# ret= models.Book.objects.values('authors__id').annotate(max=Max('price')).values('authors__name','max')
# print(ret)
#查询每一个书籍的名称，以及对应的作者个数
# ret=models.Book.objects.values('pk').annotate(count=Count('authors__id')).values('name','count')
# ret=models.Book.objects.annotate(count=Count('authors__id')).values('name','count')
# ret=models.Author.objects.values('book__id').annotate(count=Count('id')).values('book__name','count')
#
# print(ret)
#统计不止一个作者的图书
# ret=models.Book.objects.values('pk').annotate(count=Count('authors__id')).filter(count__gt=1).values('name','count')
# ret = models.Author.objects.values('book__id').annotate(count=Count('id')).filter(count__gt=1).values('book__name', 'count')
# print(ret)
# 统计价格数大于10元，作者的图书
ret = models.Book.objects.values('pk').filter(price__gt=10).annotate(count=Count('authors__id')).values('name',
'count')
print(ret)
#统计价格数大于10元，作者个数大于1的图书
ret = models.Book.objects.values('pk').filter(price__gt=10).annotate(count=Count('authors__id')).filter(count__gt=1).values('name', 'count')
print(ret)
# F查询：取出数据库的某个字段的值
# 把read_num都加1
from django.db.models import F
ret=models.Book.objects.all().update(read_num=F('read_num')+1)
print(ret)
#查询评论数大于阅读数的书籍
ret=models.Book.objects.all().filter(commit_num__gt=F('read_num'))
for i in ret:
print(i.name)
## 查询评论数大于阅读数2倍的书籍
ret=models.Book.objects.filter(commit_num__gt=F('read_num')*2)
print(ret)
# Q查询：制造 与或非的条件
Q() & | ~ 与或非
# Q查询：制造 与或非的条件
# 查询名字叫egon或者价格大于100的书
from django.db.models import Q
# ret=models.Book.objects.filter(Q(name='egon') | Q(price__gt=100))
# 查询名字叫egon并且价格大于100的书
# ret=models.Book.objects.filter(Q(name='egon') & Q(price__gt=100))
# ret=models.Book.objects.filter(name='egon',price__gt=100)
# 查询名字不为egon的书
# ret = models.Book.objects.filter(~Q(name='egon'))
# print(ret)
# Q可以嵌套
ret = models.Book.objects.filter((Q(name='egon') & Q(price__lt=100)) | Q(id__lt=3))
print(ret)
# 原生sql（有些sql用orm写不出来）
# 两种方案
# 第一种：用的比较少
# from django.db import connection
#
# cursor = connection.cursor()
#
# cursor.execute("""SELECT * from app01_book where id = %s""", [1])
#
# # row = cursor.fetchone()
# row = cursor.fetchall()
# print(row)
# 第二种，用的多
# books=models.Book.objects.raw('select * from app01_book where id >3')
# print(books)#RawQuerySet对象
# for book in books:
# print(book.name)
# books=models.Book.objects.raw('select * from app01_publish')
# for book in books:
# print(book.__dict__)
# print(book.name)
# print(book.addr)
# print(book.email)
# print(book.price)
# authors = models.Author.objects.raw('SELECT app01_author.id,app01_author. NAME,app01_authordetail.sex FROM app01_author JOIN app01_authordetail ON app01_author.author_detail_id = app01_authordetail.id WHERE app01_authordetail.sex = 1')
#
# for author in authors:
# print(author.name)
# print(author.__dict__)
# defer和only(查询优化相关)
# only保持是book对象，但是只能使用only指定的字段
# books = models.Book.objects.all().only('name')
# print(books[0].name)
# print(books[0].price) # 能出来，
# books = models.Book.objects.all().only('name')
#
# print(books[0].__dict__)
books = models.Book.objects.all().defer('name','price')
print(books[0].__dict__)
# 事物：ACID，事物的隔离级别（搜），锁， 行级锁，表级锁
# djanog orm中使用事物：原子性操作，要么都成功，要么都失败
# 新增一个作者详情，新增一个作者
# 事物的三个粒度
# 1 局部使用
from django.db import transaction
with transaction.atomic(): # 都在事物中，要么都成功，要么都失败
author_detail=models.AuthorDetail.objects.create(addr='xxx',phone='123',sex=1)
# raise Exception('抛了异常')
author=models.Author.objects.create(name='llqz',age=19,author_detail=author_detail)
# 2 视图函数装饰器,这一个视图函数都在一个事物中
# @transaction.atomic
# def index(request):
# return HttpResponse('ok')
# 3 整个http请求，在事物中，在setting.py中配置
'''
DATABASES = {
'default': {
...
'PORT': 3306,
'ATOMIC_REQUEST': True,
}
}
'ATOMIC_REQUEST': True,
设置为True统一个http请求对应的所有sql都放在一个事务中执行（要么所有都成功，要么所有都失败）。
'''
class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=8,decimal_places=2)
authors = models.ManyToManyField(to='Author') #与Author表自动创建多对多关联关系
class Author(models.Model):
name = models.CharField(max_length=32)
优点:可以使用ORM提供的快捷方法: add() clear() set() remove() all()
缺点:第三张表自动创建，无法扩展第三张表的字段
class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=8,decimal_places=2)
class Author(models.Model):
name = models.CharField(max_length=32)
class Book2Author(models.Model):
book = models.ForeignKey(to='Book')
author = models.ForeignKey(to='Author')
create_time = models.DateField(auto_now_add=True)
优点:可以自己扩展第三章关系表的字段
缺点:不能使用ORM提供的快捷方法(查询麻烦，需要跨三张表)
class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=8,decimal_places=2)
# 当前在哪个表中，元组中的第一个参数就是 表名_id或表对象
authors = models.ManyToManyField(to='Author',through='Book2Author',through_fields=('book','author'))
# through 告诉django orm 书籍表和作者表的多对多关系是通过Book2Author来记录的
# through_fields 告诉django orm记录关系时用过Book2Author表中的book字段和author字段来记录的
# through_fields 中的第一个字段必须为当前表的字段，参考Author表中ManyToManyField的写法，两个表只需要写一个
"""
多对多字段的
add
set
remove
clear不支持
"""
class Author(models.Model):
name = models.CharField(max_length=32)
# books = models.ManyToManyField(to='Book', through='Book2Author', through_fields=('author', 'book'))
#手动创建的第三张表
class Book2Author(models.Model):
book = models.ForeignKey(to='Book')
author = models.ForeignKey(to='Author')
create_time = models.DateField(auto_now_add=True)
# setting 中 指定扩写Book表
AUTH_USER_MODEL = 'blog.Book'
优点：
可以使用ORM提供的查询快捷方法，clear() all() 可以使用也可以使用
不用自己创建第三张表了，也可以任意扩展字段
缺点：多对多字段的add() remove() set() 无法使用
设置数据库持久连接
合理创建索引
提示:索引会占用磁盘空间,创建不必要的索引只会形成浪费。主键、外键、唯一键已经建立索引
1.频繁出现在where条件子句的字段 get() filter()
2.经常被用来分组(group by)或排序(order by)的字段
3.用于联接的列(主健/外健)上建立索引
4.在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定
减少SQL语句执行的次数(select_related prefetch_related)
仅获取需要的字段
使用批量创建、更新、删除，不随意对结果排序
"一劳永逸" 的话，有是有的，而 "一劳永逸" 的事却极少
