练习内容:
ORM语句的练习:
查询操作:
1.查询平均成绩⼤于60分的同学的id和平均成绩;
2.查询所有同学的id、姓名、选课的数量、总成绩;
3.查询姓“李”的⽼师的个数;
4.查询没学过“李⽼师”课的同学的id、姓名;
5.查询学过课程id为1和2的所有同学的id、姓名;
6.查询所有课程成绩⼩于60分的同学的id和姓名;
7.查询没有学全所有课的同学的id、姓名;
8.查询所有学⽣的姓名、平均分,并且按照平均分从⾼到低排序;
9.查询各科成绩的最⾼和最低分,以如下形式显示:课程ID,课程名称,最⾼分,最低分;
10.统计总共有多少⼥⽣,多少男⽣;
11.将“⻩⽼师”的每⼀⻔课程都在原来的基础之上加5分;
12.查询两⻔以上不及格的同学的id、姓名、以及不及格课程数;
13.查询每⻔课的选课⼈数;
整体操作分析:
1.创建APP,添加models: 指令:python manage.py startapp course
2.关联APP,创建相关视图和urls
3.数据库迁移:生成迁移文件:python manage.py makemigrations;执行迁移:python manage.py migrate
4.进入查询的方式:通过terminal运行manage.py的方式,python manage.py shell 然后在其中输入相关的python指令执行相关的操作。
5.ORM查询和打印
models分析
models.py内容
from django.db import models
# Create your models here.
class Student(models.Model):
# 学生表
name = models.CharField(max_length=100)
gender = models.SmallIntegerField()
class Meta:
db_table = 'student'
class Course(models.Model):
# 课程
name = models.CharField(max_length=100)
teacher = models.ForeignKey("Teacher",on_delete=models.SET_NULL,null=True)
class Meta:
db_table = 'course'
class Score(models.Model):
# 分数
student = models.ForeignKey("Student",on_delete=models.CASCADE)
course = models.ForeignKey("Course",on_delete=models.CASCADE)
number = models.FloatField()
class Meta:
db_table = 'score'
class Teacher(models.Model):
# 老师
name = models.CharField(max_length=100)
class Meta:
db_table = 'teacher'
其中包含4个数据表,老师表,学生表,课程表,分数表
其中分数表的外键包括学生表和课程表,课程表的外键包括老师表
相关操作:
注:
from django.shortcuts import render
# Create your views here.
from school.models import Student,Teacher,Course,Score
from django.db.models import Avg,Count,Max,Min,Sum
from django.db.models import F
def school1(request):
context = Student.objects.filter(id=1)
# result = Score.objects.filter(course__name__contains="文学")
# course是score的外键,通过这个能够找的内容Score内,但是比对的内容确是其外键的字段
#result = Score.objects.aggregate(Count('student',distinct=True))
# 这里查询使用的是模版中定义的字,比如student是外键student,但是在数据库显示中其字段名称是student_id
# result = Score.objects.aggregate(max=Max('number'),min=Min('number'))
# result = Score.objects.filter(student__id=2).aggregate(Sum('number'))
# 1.使用values进行定义显示哪些字段,再对该分组进行显示平均成绩
result = Score.objects.values('student').annotate(avg=Avg('number')).filter(avg__gt=85)
# 2.查询所有同学的id、姓名、选课的数量、总成绩;
# result = Score.objects.values('student','student__name').annotate(count=Count('number'),sum=Sum('number'))
# result = Teacher.objects.annotate(count=Count('name')).filter(name__contains='李') # 先统计后过滤的话得到的是两个queryset,需要先过滤再统计
# 3.查询姓“李”的⽼师的个数;
# result = Teacher.objects.filter(name__contains='李').aggregate(count=Count('name'))
#
# 4.查询没学过“李⽼师”课的同学的id、姓名;(因为有2个李老师,有的人学过两个李老师的课程)
#result = Score.objects.values('student','student__name').filter(course__teacher__name__contains='李').annotate(count=Count('student'))
#5.查询学过课程id为1和2的所有同学的id、姓名;
# 涉及知识点:
#result = Score.objects.values('student','student__name').annotate(count=Count('student')).filter(course__id__exact=1)
result = Score.objects.values('student','student__name').filter(course__exact=1)
#6.查询所有课程成绩⼩于60分的同学的id和姓名;
#
result = Score.objects.values('student','student__name').filter(number__lt=60).annotate(counnt=Count('student'))
#7.与5类似,应该怎么做?
# 8.查询所有学⽣的姓名、平均分,并且按照平均分从⾼到低排序;
result = Score.objects.values('student__name').annotate(avg=Avg('number')).order_by('-avg')
#9.查询各科成绩的最⾼和最低分,以如下形式显示:课程ID,课程名称,最⾼分,最低分;
result = Score.objects.values('course__name').annotate(max=Max('number'),min=Min('number'))
# 10统计总共有多少⼥⽣,多少男⽣;
result = Student.objects.values('gender').annotate(count=Count('gender'))
# 11. 将“⻩⽼师”的每⼀⻔课程都在原来的基础之上加5分;
result = Score.objects.values('student__name','number').filter(course__teacher__name__contains='黄').update(number=F("number")+5)
# print(result)
# for res in result:
# print(res)
# 12查询两⻔以上不及格的同学的id、姓名、以及不及格课程数;
# 13查询每⻔课的选课⼈数;
result = Score.objects.values('course').annotate(count=Count('course'))
print(result)
for res in result:
print(res)
#
return render(request,'schoolTest/index.html',{'context':context})