2019.03.22 聚合函数的使用

以后就用原生的数据表吧唯一区别就是修改引擎而已

链接数据库就两步，创建迁移表，执行迁移表。

原生数据表要添加，测试，应用，ok

聚合函数

MAX() MIN() COUNT() SUM() AVG()


from student.models import *

def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']
    
#求最高成绩

from django.db.models import Max
Stu.objects.aggregate(Max('score'))
{'score__max': 99}

showsql()
SELECT MAX(`t_stu`.`score`) AS `score__max` FROM `t_stu`


#求最低成绩

from django.db.models import Min
Stu.objects.aggregate(m=Min('score'))
{'m': 66}

showsql()
SELECT MIN(`t_stu`.`score`) AS `m` FROM `t_stu`


#学生总人数

from django.db.models import Count
Stu.objects.aggregate(c=Count('sno'))
{'c': 5}
showsql()
SELECT COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu`


#求总成绩

from django.db.models import Sum
Stu.objects.aggregate(s=Sum('score'))
{'s': 432}
showsql()
SELECT SUM(`t_stu`.`score`) AS `s` FROM `t_stu`


#求平均成绩

from django.db.models import Avg
Stu.objects.aggregate(a=Avg('score'))
{'a': 86.4}
showsql()
SELECT AVG(`t_stu`.`score`) AS `a` FROM `t_stu`


分组聚合函数，这个呢基于 

Group By 这个进行的，以前有学过，这是过滤呀。这是

Group By 分组

聚合函数[ max() min() count() sum() avg() ]

准备工作

from student.models import *
from django.db.models import *  

def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']



Stu.objects.annotate(c=Count('sno'))
<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>

showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id`, COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu` GROUP BY `t_stu`.`sno` ORDER BY NULL LIMIT 21

Stu.objects.annotate(a=Avg('clazz'))
<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>

showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id`, AVG(`t_stu`.`clazz_id`) AS `a` FROM `t_stu` GROUP BY `t_stu`.`sno` ORDER BY NULL LIMIT 21

#求每个班学生的总成绩

Stu.objects.values('clazz').annotate(s=Sum('score'))
<QuerySet [{'s': 154, 'clazz': 1L}, {'s': 90, 'clazz': 2L}, {'s': 188, 'clazz': 3L}]>
showsql()
SELECT `t_stu`.`clazz_id`, SUM(`t_stu`.`score`) AS `s` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21




# 求每个班级的学生人数

Stu.objects.values('clazz').annotate(c=Count('sno'))
<QuerySet [{'c': 2, 'clazz': 1L}, {'c': 1, 'clazz': 2L}, {'c': 2, 'clazz': 3L}]>

showsql()
SELECT `t_stu`.`clazz_id`, COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21





# 求每个班级的学生平均成绩

Stu.objects.values('clazz').annotate(a=Avg('score'))
<QuerySet [{'a': 77.0, 'clazz': 1L}, {'a': 90.0, 'clazz': 2L}, {'a': 94.0, 'clazz': 3L}]>

showsql()
SELECT `t_stu`.`clazz_id`, AVG(`t_stu`.`score`) AS `a` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21

子查询


Stu.objects.values('clazz').annotate(s=Sum('score')).aggregate(m=Max('s'))
{'m': 188}

showsql()
SELECT MAX(`s`) FROM (SELECT `t_stu`.`clazz_id` AS Col1, SUM(`t_stu`.`score`) AS `s` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL) subquery

关联查询


Stu.objects.values('clazz__cname')
<QuerySet [{'clazz__cname': u'HTML5'}, {'clazz__cname': u'HTML5'}, {'clazz__cname': u'MySQL'}, {'clazz__cname': u'Python'}, {'clazz__cname': u'Python'}]>

showsql()
SELECT `t_cls`.`cname` FROM `t_stu` INNER JOIN `t_cls` ON (`t_stu`.`clazz_id` = `t_cls`.`cno`) LIMIT 21



原生查询  怎么说，就是利用游标来查询的，raw必须包含主键
就是在模型类中有的方法是可以查询的，但当有比较特别的查询时，可能不满足，所以可以考虑用到原生的

方式1

包含主键

for d in Stu.objects.raw('select * from t_stu'):
    print d
    
Stu:zhangsan,88
Stu:lisi,90
Stu:wangwu,99
Stu:zhangjie,66
Stu:xiena,89

不包含主键


for d in Stu.objects.raw('select sname,score from t_stu'):
    print d
    
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Python27\lib\site-packages\django\db\models\query.py", line 1245, in __iter__
    raise InvalidQuery('Raw query must include the primary key')
InvalidQuery: Raw query must include the primary key

方式2

包含主键


from student.models import *
from django.db import connection

cursor = connection .cursor()
cursor.execute('select * from t_stu')
5L
datas = cursor.fetchall()

print datas
((1L, u'zhangsan', 88L, datetime.date(2018, 4, 7), 1L), (2L, u'lisi', 90L, datetime.date(2018, 4, 7), 2L), (3L, u'wangwu', 99L, datetime.date(2018, 4, 7), 3L), (4L, u'zhangjie', 66L, datetime.date(2018, 4, 7), 1L), (5L, u'xiena', 89L, datetime.date(2018, 4, 7), 3L))
for d in datas:
    print d
(1L, u'zhangsan', 88L, datetime.date(2018, 4, 7), 1L)
(2L, u'lisi', 90L, datetime.date(2018, 4, 7), 2L)
(3L, u'wangwu', 99L, datetime.date(2018, 4, 7), 3L)
(4L, u'zhangjie', 66L, datetime.date(2018, 4, 7), 1L)
(5L, u'xiena', 89L, datetime.date(2018, 4, 7), 3L)

cursor.close()

不包含主键


from student.models import *
from django.db import connection
cursor= connection.cursor()
cursor.execute('select sname,score from t_stu')
5L
ds = cursor.fetchall()
print ds
((u'zhangsan', 88L), (u'lisi', 90L), (u'wangwu', 99L), (u'zhangjie', 66L), (u'xiena', 89L))
cursor.close()

获取一条记录


from student.models import *
from django.db import connection
cursor= connection.cursor()
cursor.execute('select * from t_stu where sno=1')
1L
cursor.fetchone()
(1L, u'zhangsan', 88L, datetime.date(2018, 4, 7), 1L)

cursor.close()

封装函数实现原生查询



def test(sql):
    with connection.cursor() as c:
        c.execute(sql)
        print [d for d in c.fetchall()]

MySQL外连接

SQL92：select * from ltable,rtable where ltable.column=rtable.column(+)
SQL99: select * from ltable left join rtable on (ltable.column=rtable.column)

Q查询

查看底层SQL Q查询就是与或非，f就是追加呗

def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']


#条件与操作

from student.models import *
from django.db.models import Q,F
Stu.objects.filter(Q(sno=1)&Q(sname='zhangsan'))
<QuerySet [<Stu: Stu:zhangsan,88>]>

    
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE (`t_stu`.`sno` = 1 AND `t_stu`.`sname` = 'zhangsan') LIMIT 21





Stu.objects.get(Q(sno=1)&Q(sname='zhangsan'))
<Stu: Stu:zhangsan,88>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE (`t_stu`.`sno` = 1 AND `t_stu`.`sname` = 'zhangsan')


#条件或操作
Stu.objects.filter(Q(created__gte='2017-12-12')|Q(sno=1))
<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE (`t_stu`.`created` >= '2017-12-12' OR `t_stu`.`sno` = 1) LIMIT 21


#条件非操作
Stu.objects.filter(~Q(sname='zhangsan'))
<QuerySet [<Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE NOT (`t_stu`.`sname` = 'zhangsan') LIMIT 21



Stu.objects.filter(~Q(sname='zhangsan',sno=1))
<QuerySet [<Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>
showsql()
SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id` FROM `t_stu` WHERE NOT (`t_stu`.`sno` = 1 AND `t_stu`.`sname` = 'zhangsan') LIMIT 21

F查询


#学号大于3的学生成绩加10分
Stu.objects.filter(Q(sno__gt=3)).update(score=F('score')+10)
2L
showsql()
UPDATE `t_stu` SET `score` = (`t_stu`.`score` + 10) WHERE `t_stu`.`sno` > 3



#学号大于3的学生入学日期提前2天

import datetime
Stu.objects.filter(Q(sno__gt=3)).update(created=F('created')+datetime.timedelta(days=-2))
2L
showsql()
UPDATE `t_stu` SET `created` = (`t_stu`.`created` + INTERVAL '172800.000000' SECOND_MICROSECOND) WHERE `t_stu`.`sno` > 3

posted @ 2019-03-22 21:14 AJking 阅读(118) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

2019.03.22 聚合函数的使用

以后就用原生的数据表吧 唯一区别就是修改引擎而已

聚合函数

Group By 分组

准备工作

子查询

关联查询

方式1

包含主键

不包含主键

方式2

包含主键

不包含主键

获取一条记录

封装函数实现原生查询

MySQL外连接

Q查询

查看底层SQL Q查询就是与或非，f就是追加呗

F查询

公告

以后就用原生的数据表吧唯一区别就是修改引擎而已