# --------------------------------------------------------------------------------------------------------
# 三张表 出版社表 书籍表 作者表
#
# # 书籍表
# class Book(models.Model):
# name = models.CharField(max_length=20)
# price = models.IntegerField()
# pub_date = models.DateField()
# publish = models.ForeignKey("Publish")
# #authors = models.ManyToManyField("Author") #一般创建多对多关系 自己也可以创建一个多对多表关系 推荐单建立一个多表
# # authors 对应Author的id == authors_id authors.列名 能取到Author的数据
# def __str__(self):
# return self.name
#
# class Meta:
# db_table = "Book"
# # 出版社表
# class Publish(models.Model):
# name = models.CharField(max_length=32)
# city = models.CharField(max_length=32)
#
# def __str__(self):
# return self.name
#
# class Meta:
# db_table = "Publish"
# # 推荐自己创建一个多表 这种比较灵活
# # class Book_Author(models.Model):
# # book=models.ForeignKey("Book")
# # author=models.ForeignKey("Author")
# # 作者表
# class Author(models.Model):
# name = models.CharField(max_length=32)
# age = models.IntegerField(default=20)
#
# def __str__(self):
# return self.name
#
# class Meta:
# db_table = "Author"
# -------------------------------------------------------------------------------------------------------- # --------------------------------------------------------------------------------------------------------
# 多表关系 一对多操作
# 添加数据
# Book.objects.create(name="python", price=77, pub_date="2017-12-12", publish_id=2)
#
# publish_obj=Publish.objects.filter(name="人民出版社")[0]
# Book.objects.create(name="GO",price=23,pub_date="2017-05-12",publish=publish_obj) # book_obj=Book.objects.get(name="python") # 是一个对象
# print(type(book_obj))
# print(book_obj.name)
# print(book_obj.pub_date)
#
# #一对多：book_obj.publish--------一定是一个对象
# print(book_obj.publish.name)
# print(book_obj.publish.city)
# print(type(book_obj.publish))
#
# 查询人民出版社出过的所有书籍名字和价格
# select name,price from Book where publish_id in (select id from Publish where name="人民出版社");
# 方式一：
# pub_obj = Publish.objects.filter(name="人民出版社")[0]
# ret = Book.objects.filter(publish=pub_obj).values("name", "price")
# print(ret) # 方式二
# pub_obj = Publish.objects.filter(name="人民出版社")[0]
# print(pub_obj.book_set.all().values("name","price"))
# print(type(pub_obj.book_set.all())) # 方式三 推荐用这个 正向查询 神奇的双下划线 __
# 通过 filter values (双下换线)进行多对多的关联查询（形式和一对多）
#
# 查询人民出版社出的书名 和 书的价格
# SELECT `Book`.`name`, `Book`.`price` FROM `Book` INNER JOIN `Publish` ON (`Book`.`publish_id` = `Publish`.`id`) WHERE `Publish`.`name` = '人民出版社'
# ret=Book.objects.filter(publish__name="人民出版社").values("name","price")
# print(ret)
#
# python这本书出版社的名字 注意book要小写
# select name from Publish where id in (select publish_id from Book where name="python");
# SELECT `Publish`.`name` FROM `Publish` INNER JOIN `Book` ON (`Publish`.`id` = `Book`.`publish_id`) WHERE `Book`.`name` = 'python'
# ret2 = Publish.objects.filter(book__name="python").values("name")
# print(ret2)
# ret3 = Book.objects.filter(name="python").values("publish__name")
# print(ret3)
#
# 北京城市出的所有书
# select name from Book where publish_id in (select id from Publish where city="bj")
# SELECT `Book`.`name` FROM `Book` INNER JOIN `Publish` ON (`Book`.`publish_id` = `Publish`.`id`) WHERE `Publish`.`city` = 'bj'
# ret4 = Book.objects.filter(publish__city="bj").values("name")
# print(ret4) # 查询一段时间内出过书的出版社
# SELECT `Publish`.`name` FROM `Book` INNER JOIN `Publish` ON (`Book`.`publish_id` = `Publish`.`id`) WHERE (`Book`.`pub_date` < '2017-07-01' AND `Book`.`pub_date` > '2017-01-01')
# ret5=Book.objects.filter(pub_date__lt="2017-07-01",pub_date__gt="2017-01-01").values("publish__name")
# print(ret5)
# -------------------------------------------------------------------------------------------------------- # --------------------------------------------------------------------------------------------------------
# 多多关系 对对多操作
# 通过对象的方式绑定关系
# book_obj=Book.objects.get(id=3)
# print(book_obj.authors.all())
# print(type(book_obj.authors.all())) #查询id为2的作者出的书
# author_obj=Author.objects.get(id=2)
# print(author_obj.book_set.all()) # 多表操作 增加数据
# book_obj=Book.objects.get(id=37)
# author_objs=Author.objects.all()
# book_obj.authors.add(*author_objs) # 多表操作 删除数据
# # book_obj.authors.remove(*author_objs)
# book_obj.authors.remove(4) # 创建第三张表
# Book_Author.objects.create(book_id=38,author_id=1)
# Book_Author.objects.create(book_id=35,author_id=1)
# Book_Author.objects.create(book_id=37,author_id=1) # 查找book id是36的作者
# select name from Author where id in (select author_id from Book_Author where book_id = 35);
# ret = Author.objects.filter(book_author__book__id="35")
# obj_id = Book.objects.filter()
# print(ret) # 外键找外键关联的表 直接用fk找
# 没外键找和它有关联表用 外键的类_set找 注意 外键类小写 或者在外键上加上 related_name='自己的类名' 注意小写
ret = Book.objects.get(id=36).authors.all().values("name")
print(ret) # zhangsan出过的书籍名称及价格
# select name,price from Book where id in (select book_id from Book_Author where author_id in(select id from Author where name="zhangsan")));
# ret2=Book.objects.filter(book_author__author__name="zhangsan").values("name","price")
# ret2=Book.objects.filter(authors__name="zhangsan").values("name","price")
# print(ret2)
# ret1 = Author.objects.get(name="zhangsan").book_set.values("name","price")
ret1 = Author.objects.get(name="zhangsan").book_set.all()
print(ret1) # 找出 linux运维 这本书的作者是谁
#SELECT `Author`.`name` FROM `Author` INNER JOIN `Book_Author` ON (`Author`.`id` = `Book_Author`.`author_id`) INNER JOIN `Book` ON (`Book_Author`.`book_id` = `Book`.`id`) WHERE `Book`.`name` = 'linux运维';
#select name from Author where id in (select author_id from Book_Author where book_id in (select id from Book where name = "linux运维"));;
# ret = Author.objects.filter(book_author__book__name="linux运维").values("name")
ret2 = Book.objects.get(name="linux运维").authors.all().values("name")
print(ret2) # 聚合函数 aggregate
# ret=Book.objects.all().aggregate(Avg("price"))
# ret=Book.objects.all().aggregate(Sum("price"))
# ret=Book.objects.filter(authors__name="alex").aggregate(alex_money=Sum("price"))
# ret=Book.objects.filter(authors__name="alex").aggregate(Count("price"))
# print(ret) # ret=Book.objects.values("authors__name").annotate(Sum("price"))
# print(ret) # ret=Publish.objects.values("name").annotate(abc=Min("book__price"))
# print(ret) # b=Book.objects.get(name="GO",price=77)
# print(b) # Book.objects.all().update(price=F("price")+10) # ret=Book.objects.filter(Q(name__contains="G"))
# print(ret) # ret=Book.objects.filter(Q(name="GO"),price=87)
# print(ret) # ret=Book.objects.filter(price=200) # for i in ret:
# print(i.price)
#
# Book.objects.all().update(price=200)
# ret = Book.objects.filter(price=100)
# for i in ret:
# print(i.price) # if ret.exists():
# print("ok") # ret=ret.iterator()
# print(ret)
#
# for i in ret:
# print(i.name)
#
# for i in ret:
# print(i.name)