#单表：
    #查询每一个部门的id以及对应员工的平均薪水
    ret = models.Emp.objects.values('dep_id').annotate(s=Avg('salary'))
    #查询每个部门的id以及对对应的员工的最大年龄
    ret = models.Emp.objects.values('dep_id').annotate(a=Max('age'))
    #Emp表示表，values中的字段表示按照哪个字段group by，annotate里面是显示分组统计的是什么

#连表：
    # 查询每个部门的名称以及对应的员工个数和员工最大年龄
    ret = models.Emp.objects.values('dep__name').annotate(a=Count('id'),b=Max('age')) #注意，正向与反向的结果可能不同，如果反向查的时候，有的部门还没有员工，那么他的数据也会被统计出来，只不过值为0，但是正向查的话只能统计出来有员工的部门的相关数据，因为通过你是员工找部门，而不是通过部门找员工，结果集里面的数据个数不同，但是你想要的统计结果是一样的
    #<QuerySet [{'a': 1, 'dep__name': '销售部', 'b': 12}, {'a': 3, 'dep__name': '人事部', 'b': 22}]>
    #使用双下划线进行连表，然后按照部门名称进行分组，然后统计员工个数和最大年龄，最后结果里面显示的是部门名称、个数、最大年龄。
#注意：如果values里面有多个字段的情况：ret = models.Emp.objects.values('dep__name','age').annotate(a=Count('id'),b=Max('age')) #是按照values里面的两个字段进行分组，两个字段同时相同才算是一组，看下面的sql语句'''     SELECT `app01_dep`.`name`, `app01_emp`.`age`, COUNT(`app01_emp`.`id`) AS `a`, MAX(`app01_emp`.`age`) AS `b` FROM `app01_emp` INNER JOIN `app01_dep` ON (`app01_emp`.`dep_id` = `app01_dep`.`id`) GROUP BY `app01_dep`.`name`, `app01_emp`.`age`;'''
 

　　下面是书籍表和出版社表的一个连表分组的sql语句写法：

查询练习

　　(1) 练习：统计每一个出版社的最便宜的书

publishList=Publish.objects.annotate(MinPrice=Min("book__price")) #如果没有使用objects后面values或者values_list，得到的结果是queryset类型，里面是Publish的model对象，并且是对所有记
录进行的统计，统计的Minprice也成了这些model对象里面的一个属性，这种连表分组统计的写法最常用，思路也比较清晰
for publish_obj in publishList:
    print(publish_obj.name,publish_obj.MinPrice)

　　annotate的返回值是querySet，如果不想遍历对象，可以用上valuelist：

queryResult= Publish.objects
　　　　　　　　　　　　.annotate(MinPrice=Min("book__price"))
　　　　　　　　　　　　.values_list("name","MinPrice")
print(queryResult)

'''


SELECT "app01_publish"."name", MIN("app01_book"."price")  AS "MinPrice" FROM "app01_publish" 
LEFT  JOIN "app01_book" ON ("app01_publish"."nid" = "app01_book"."publish_id") 
GROUP BY "app01_publish"."nid", "app01_publish"."name", "app01_publish"."city", "app01_publish"."email" 

'''

　　(2) 练习：统计每一本书的作者个数

ret=Book.objects.annotate(authorsNum=Count('authors__name'))
ret=models.Book.objects.annotate(authorsNum=Count('authors__name')).values('title','authorsNum') #注意写法，values里面写的个数的别名ret=models.Book.objects.annotate(a=Count('author__name')).filter(a__gt=2).values('title','a') #还有这种写法，看看你能不能明白这是在做什么

　　(3) 统计每一本以py开头的书籍的作者个数：

 queryResult=Book.objects
　　　　　　　　　　 .filter(title__startswith="Py")
　　　　　　　　　 　.annotate(num_authors=Count('authors')) #连接第三张表再连接author表，where title regexp '^Py' 然后按照连表后的大表中的book表的title字段进行分组，并且统计对应作者的个数

　　(4) 统计不止一个作者的图书：

queryResult=Book.objects
　　　　　　　　　　.annotate(num_authors=Count('authors'))
　　　　　　　　　　.filter(num_authors__gt=1) #filter也是也可以是querset来调用

　　(5) 根据一本图书作者数量的多少对查询集 QuerySet进行排序:

 	
Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

　　(6) 查询各个作者出的书的总价格:

#   按author表的所有字段 group by
    queryResult=Author.objects　　　　　　　　　　　　　　.annotate(SumPrice=Sum("book__price"))　　　　　　　　　　　　　　.values_list("name","SumPrice")
    print(queryResult)

F查询与Q查询

F查询

　　在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？我们在book表里面加上两个字段：评论数：commentNum，收藏数：KeepNum

　　Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。

# 查询评论数大于收藏数的书籍
 
   from django.db.models import F
   Book.objects.filter(commentNum__lt=F('keepNum'))

　　Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。

# 查询评论数大于收藏数2倍的书籍
    Book.objects.filter(commentNum__lt=F('keepNum')*2)

　　修改操作也可以使用F函数,比如将每一本书的价格提高30元：

Book.objects.all().update(price=F("price")+30)　

Q查询

　　filter() 等方法中的关键字参数查询都是一起进行“AND” 的。 如果你需要执行更复杂的查询（例如OR 语句），你可以使用Q 对象。

from django.db.models import Q
Q(title__startswith='Py')

　　Q 对象可以使用&(与) 、|（或）、~（非） 操作符组合起来。当一个操作符在两个Q 对象上使用时，它产生一个新的Q 对象。

bookList=Book.objects.filter(Q(authors__name="yuan")|Q(authors__name="egon"))

　　等同于下面的SQL WHERE 子句：

WHERE name ="yuan" OR name ="egon"

　　你可以组合& 和| 操作符以及使用括号进行分组来编写任意复杂的Q 对象。同时，Q 对象可以使用~ 操作符取反，这允许组合正常的查询和取反(NOT) 查询：

bookList=Book.objects.filter(Q(authors__name="yuan") & ~Q(publishDate__year=2017)).values_list("title")
bookList=Book.objects.filter(Q(Q(authors__name="yuan") & ~Q(publishDate__year=2017))&Q(id__gt=6)).values_list("title") #可以进行Q嵌套，多层Q嵌套等，其实工作中比较常用

　　查询函数可以混合使用Q 对象和关键字参数。所有提供给查询函数的参数（关键字参数或Q 对象）都将"AND”在一起。但是，如果出现Q 对象，它必须位于所有关键字参数的前面。例如：

bookList``=``Book.objects.``filter``(Q(publishDate__year``=``2016``) | Q(publishDate__year``=``2017``),
                              ``title__icontains``=``"python"  #也是and的关系，但是Q必须写在前面
                             ``)

综合查询练习题

#1 查询每个作者的姓名以及出版的书的最高价格
    ret = models.Author.objects.values('name').annotate(max_price=Max('book__price'))
    print(ret) #注意：values写在annotate前面是作为分组依据用的，并且返回给你的值就是这个values里面的字段（name）和分组统计的结果字段数据(max_price)
    # ret = models.Author.objects.annotate(max_price=Max('book__price')).values('name','max_price')#这种写法是按照Author表的id字段进行分组，返回给你的是这个表的所有model对象，这个对象里面包含着max_price这个属性，后面写values方法是获取的这些对象的属性的值，当然，可以加双下划线来连表获取其他关联表的数据，但是获取的其他关联表数据是你的这些model对象对应的数据，而关联获取的数据可能不是你想要的最大值对应的那些数据
# 2 查询作者id大于2作者的姓名以及出版的书的最高价格
    ret = models.Author.objects.filter(id__gt=2).annotate(max_price=Max('book__price')).values('name','max_price')#记着，这个values取得是前面调用这个方法的表的所有字段值以及max_pirce的值，这也是为什么我们取关联数据的时候要加双划线的原因
    print(ret)

#3 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
    # ret = models.Author.objects.filter(Q(id__gt=2)|Q(age__gte=20),sex='female').annotate(max_price=Max('book__price')).values('name','max_price')
#4 查询每个作者出版的书的最高价格 的平均值
    # ret = models.Author.objects.values('id').annotate(max_price=Max('book__price')).aggregate(Avg('max_price')) #{'max_price__avg': 555.0} 注意，aggregate是queryset的终止句，得到的是字典
    # ret = models.Author.objects.annotate(max_price=Max('book__price')).aggregate(Avg('max_price')) #{'max_price__avg': 555.0} 注意，aggregate是queryset的终止句，得到的是字典

#5 每个作者出版的所有书的最高价格以及最高价格的那本书的名称（通过orm玩起来就是个死题，需要用原生sql）
    '''
    select title,price from (select app01_author.id,app01_book.title,app01_book.price from app01_author INNER JOIN app01_book_authors on app01_author.id=
app01_book_authors.author_id INNER JOIN app01_book on app01_book.id=
app01_book_authors.book_id ORDER BY app01_book.price desc) as b  GROUP BY id
'''

    print(ret)

六 ORM执行原生sql语句(了解)

　　在模型查询API不够用的情况下，我们还可以使用原始的SQL语句进行查询。

　　Django 提供两种方法使用原始SQL进行查询：一种是使用raw()方法，进行原始SQL查询并返回模型实例；另一种是完全避开模型层，直接执行自定义的SQL语句。

执行原生查询

　　　　raw()管理器方法用于原始的SQL查询，并返回模型的实例：

　　　　注意：raw()语法查询必须包含主键。

　　　　这个方法执行原始的SQL查询，并返回一个django.db.models.query.RawQuerySet 实例。 这个RawQuerySet 实例可以像一般的QuerySet那样，通过迭代来提供对象实例。

　　　　举个例子：

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

　　可以像下面这样执行原生SQL语句

>>> for p in Person.objects.raw('SELECT * FROM myapp_person'):
...     print(p)

　　raw()查询可以查询其他表的数据。

　　　　举个例子：

ret = models.Student.objects.raw('select id, tname as hehe from app02_teacher')
    for i in ret:
        print(i.id, i.hehe)

　　raw()方法自动将查询字段映射到模型字段。还可以通过translations参数指定一个把查询的字段名和ORM对象实例的字段名互相对应的字典

d = {'tname': 'haha'}
    ret = models.Student.objects.raw('select * from app02_teacher', translations=d)
    for i in ret:
        print(i.id, i.sname, i.haha)

　　原生SQL还可以使用参数，注意不要自己使用字符串格式化拼接SQL语句，防止SQL注入！

d = {'tname': 'haha'}
    ret = models.Student.objects.raw('select * from app02_teacher where id > %s', translations=d, params=[1,])
    for i in ret:
        print(i.id, i.sname, i.haha)

直接执行自定义SQL

　　　　有时候raw()方法并不十分好用，很多情况下我们不需要将查询结果映射成模型，或者我们需要执行DELETE、 INSERT以及UPDATE操作。在这些情况下，我们可以直接访问数据库，完全避开模型层。

　　　　我们可以直接从django提供的接口中获取数据库连接，然后像使用pymysql模块一样操作数据库。

from django.db import connection, connections
cursor = connection.cursor()  # cursor = connections['default'].cursor()
cursor.execute("""SELECT * from auth_user where id = %s""", [1])
ret = cursor.fetchone()　

七 Python脚本中调用Django环境

(django外部脚本使用models)

如果你想通过自己创建的python文件在django项目中使用django的models，那么就需要调用django的环境：

import os

if __name__ == '__main__':
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings")
    import django
    django.setup()

    from app01 import models  #引入也要写在上面三句之后

    books = models.Book.objects.all()
    print(books)

八 补充多个app配置models

　　app01的models文件内容

from django.db import models

# Create your models here.

class UserInfo(models.Model):

    name = models.CharField(max_length=12)

　　app02的models文件内容

from django.db import models

# Create your models here.


class Class(models.Model):

    title = models.CharField(max_length=32)
    user = models.ForeignKey('app01.Userinfo')  #如果需要两个app之间的models进行关联，直接这样写就可以，或者直接将那个被关联的表，通过import的方法引入进行进行关联。

　　不需要进行其他的配置了，直接执行数据库同步指令就可以了。

　　关于多个app多个数据库，并且数据有关联时的一些玩法，等后面我再补充吧