Django-ORM练习题 https://www.cnblogs.com/Michael--chen/protected/articles/10926556.html
django-orm
一. 表结构
请创建如下表，并创建相关约束。
models文件里，模型类代码：
复制代码
class Classes(models.Model):
cid = models.AutoField(primary_key=True)
caption = models.CharField(max_length=32)
grade = models.ForeignKey("ClassGrade", on_delete=models.CASCADE)
class ClassGrade(models.Model):
gid = models.AutoField(primary_key=True)
gname = models.CharField(max_length=32)
class Student(models.Model):
sid = models.AutoField(primary_key=True)
sname = models.CharField(max_length=32)
gender_choice = ((0, "女"), (1, "男"))
gender = models.SmallIntegerField(choices=gender_choice, default=1)
classes = models.ForeignKey("Classes", on_delete=models.CASCADE)
class Teacher(models.Model):
tid = models.AutoField(primary_key=True)
tname = models.CharField(max_length=32)
classes = models.ManyToManyField("Classes")
class Course(models.Model):
cid = models.AutoField(primary_key=True)
cname = models.CharField(max_length=32)
teacher = models.ForeignKey("Teacher", on_delete=models.CASCADE)
class Score(models.Model):
sid = models.AutoField(primary_key=True)
student = models.ForeignKey("Student", on_delete=models.CASCADE)
course = models.ForeignKey('Course', on_delete=models.CASCADE)
score = models.IntegerField()
复制代码
二. 插入测试数据
1. 年级表（ClassGrade）
INSERT INTO app01_classgrade (gid, gname) VALUES (1, '一年级');
INSERT INTO app01_classgrade (gid, gname) VALUES (2, '二年级');
INSERT INTO app01_classgrade (gid, gname) VALUES (3, '三年级');
INSERT INTO app01_classgrade (gid, gname) VALUES (4, '四年级');
2. 班级表（Classes）
复制代码
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (1, '一年1班', 1);
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (2, '一年2班', 1);
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (3, '二年1班', 2);
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (4, '二年2班', 2);
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (5, '三年1班', 3);
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (6, '三年2班', 3);
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (7, '三年3班', 3);
INSERT INTO app01_classes (cid, caption, grade_id) VALUES (8, '四年1班', 4);
复制代码
3. 老师表（Teacher）
INSERT INTO app01_teacher (tid, tname) VALUES (1, '仓老师');
INSERT INTO app01_teacher (tid, tname) VALUES (2, '任盈盈');
INSERT INTO app01_teacher (tid, tname) VALUES (3, '任我行');
INSERT INTO app01_teacher (tid, tname) VALUES (4, '岳不群');
INSERT INTO app01_teacher (tid, tname) VALUES (5, '唐僧');
4. 课程表（Course）
INSERT INTO app01_course (cid, cname, teacher_id) VALUES (1, '物理', 3);
INSERT INTO app01_course (cid, cname, teacher_id) VALUES (2, '生物', 1);
INSERT INTO app01_course (cid, cname, teacher_id) VALUES (3, '语文', 5);
INSERT INTO app01_course (cid, cname, teacher_id) VALUES (4, '数学', 4);
INSERT INTO app01_course (cid, cname, teacher_id) VALUES (5, '日语', 1);
INSERT INTO app01_course (cid, cname, teacher_id) VALUES (6, '体育', 2);
5. 学生表（Student）
复制代码
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (1, '张无忌', 2, 1);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (2, '赵敏', 1, 0);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (3, '令狐冲', 3, 1);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (4, '任盈盈', 4, 0);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (5, '方世玉', 5, 1);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (6, '张飞', 6, 1);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (7, '武松', 8, 1);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (8, '鲁智深', 7, 1);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (9, '金莲', 6, 0);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (10, '西门庆', 8, 1);
INSERT INTO app01_student (sid, sname, classes_id, gender) VALUES (11, '林冲', 7, 1);
复制代码
6. 班级任职表（TeacherClasses关系表）
复制代码
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (1, 1, 8);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (2, 1, 7);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (3, 1, 6);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (4, 2, 5);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (5, 3, 4);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (6, 3, 5);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (7, 4, 4);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (8, 5, 3);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (9, 2, 2);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (10, 3, 1);
INSERT INTO app01_teacher_classes (id, teacher_id, classes_id) VALUES (11, 4, 1);
复制代码
7. 成绩表（Score）
复制代码
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (1, 80, 1, 2);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (2, 60, 1, 3);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (3, 72, 2, 1);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (4, 58, 3, 1);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (5, 91, 4, 5);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (6, 84, 5, 5);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (7, 78, 6, 8);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (8, 82, 4, 7);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (9, 87, 4, 6);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (10, 57, 2, 3);
INSERT INTO app01_score (sid, score, course_id, student_id) VALUES (11, 68, 3, 2);
insert into app01_score (sid, score, course_id, student_id) values (12, 73, 2, 2);
复制代码
三. 查询操作
复制代码
# 1、自行创建测试数据；
# 2、查询学生总人数；
res = models.Student.objects.count()
res = models.Student.objects.aggregate(c=Count('sid'))
# 3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
# 思路： 先过滤出生物或者物理成绩大于60的学生，然后按照学生分组，统计大于1的，即为都及格的学生
res = models.Score.objects.filter(course__cname__in=['生物', '物理'], score__gt=60).values("student__sid").annotate(c=Count('student__sid')).filter(c__gt=1).values('student__sid', 'student__sname')
# 4、查询每个年级的班级数，取出班级数最多的前三个年级；
# 思路：按年级分组，统计每个年级的班级数进行计数，然后降序取前三个值即可
res = models.Classes.objects.values('grade_id').annotate(c=Count('cid')).order_by('-c')[0:3]
# 5、查询平均成绩最高的学生的id和姓名以及平均成绩；
# 思路：按学生进行分组，得到每个学生的平均成绩，然后根据成绩排序，取出第一个即可
res = models.Score.objects.values('student_id', 'student__sname').annotate(a=Avg('score')).order_by('-a')[0]
# 6、查询每个年级的学生人数；
# 思路：按年级分组，跨表统计学生人数
res = models.Classes.objects.values('grade_id').annotate(c=Count('student__sid'))
# 7、查询每位学生的学号，姓名, 平均成绩；
# 思路：按学生分组，统计出平均成绩
res = models.Score.objects.values('student_id', 'student__sname').annotate(a=Avg('score'))
# 8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名及分数；
# 思路：先按pk值过滤出该学生，然后，按成绩排序取出最高值的学科即可
res = models.Score.objects.filter(student__sid=2).values('student__sname', 'course__cname', 'score').order_by('-score')[0]
res = models.Score.objects.filter(student__sid=2).values('student__sname', 'course__cname').annotate(m=Max('score'))[0]
# 9、查询姓“任”的老师所带班级数；
# 思路：先过滤出姓‘任’的老师,根据老师任职表，统计出班级数
res = models.Teacher.objects.filter(tname__startswith="任").values('tname').annotate(c=Count('classes'))
# 10、查询班级数小于3的年级id和年级名；
# 思路：先按年级进行分组，得到每个年级的班级数，再按条件进行过滤即可
res = models.Classes.objects.values('grade_id', 'grade__gname').annotate(c=Count('cid')).filter(c__lt=5)
# 11、查询教过课程超过2门的老师的id和姓名；
# 思路：按照老师分组，统计出所教的课程数，进行过滤即可
res = models.Course.objects.values('teacher_id', 'teacher__tname').annotate(c=Count("cname")).filter(c__gte=2)
# 12、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
# 思路：先过滤出学过1或者2课程的同学，然后按照学生分组，得到大于1的既是都学过的学生。
res = models.Score.objects.filter(course_id__in=[1, 2]).values('student_id', 'student__sname').annotate(c=Count('student_id')).filter(c__gt=1)
# 13、查询所带班级数最多的老师id和姓名；
# 思路：先按老师进行分组，统计出每个老师的所带的班级数，按照所带班级数进行排序取第一个即可。
res = models.Teacher.objects.annotate(c=Count('classes')).values('tid', 'tname').order_by('-c')[0]
# 14、查询有课程成绩小于60分的同学的学号、姓名；
# 思路：在成绩表中先过滤出成绩小于60的同学，然后去重即可
res = models.Score.objects.filter(score__lt=60).values('student_id', 'student__sname').distinct()
# 15、查询男生、女生的人数，按倒序排列；
# 思路：先按性别分组，进行计数，最后降序排序即可
res = models.Student.objects.values('gender').annotate(c=Count('sid')).order_by('-c')
# 16、 查询各个课程及相应的选修人数；
# 思路：根据成绩表，按课程分组，统计选修人数即可。
res = models.Score.objects.values('course__cname').annotate(c=Count('student_id'))
# 17、 查询同时选修了物理课和生物课的学生id和姓名；
# 思路：同12
res = models.Score.objects.filter(course__cname__in=['物理', '生物']).values('student_id', 'student__sname').annotate(c=Count('student_id')).filter(c__gt=1)
# 18、 检索“3”课程分数小于60，按分数降序排列的同学学号；
# 思路：先按条件过滤出同学，然后按分数降序即可
res = models.Score.objects.filter(student_id=3, score__lt=60).order_by('-score').values('student_id')
# 19、 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
# 思路：按课程分组，求出平均成绩，然后排序即可。
res = models.Score.objects.values('course__cname').annotate(a=Avg('score')).order_by('a', '-course_id')
# 20、 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
# 思路：在成绩表按课程分组，计算出分数最高分和最低分，完成显示即可。
res = models.Score.objects.values('course__cname').annotate(max_num=Max('score'), Min_num=Min('score'))
复制代码