1. 自定义主键字段的创建
AutoFiled(pirmary_key=True) # 一般不会自定义
- class Grade(models.Model):
- idd = models.AutoField(primary_key=True)
- name = models.CharField(max_length=20)
- num = models.CharField(max_length=20)
- View Code
- 2.order_by asc desc
小写排序默认是 asc, 倒叙的时候需要调用到 desc
- In [4]: from django.db.models.functions import Lower
- In [5]: res = Student.objects.order_by(Lower('name'))
- In [6]: print(res.query)
- SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`c_time` FROM `teacher_student` ORDER BY LOWER(`teacher_student`.`name`) ASC
- In [7]: res = Student.objects.order_by(Lower('name').desc())
- View Code
1. 表关系的创建
- - OneToOne
- student = models.OneToOneField('Student', on_delete=models.CASCADE)
学生和学生详情表通过外键关联
- class Studentdetail(models.Model):
- num = models.CharField(max_length=20,default=' ')
- collage = models.CharField(max_length=20,default=' ')
- student = models.OneToOneField('Student',on_delete=models.CASCADE)
- MySQL> show tables;
- +-----------------------+
- | Tables_in_crm |
- +-----------------------+
- | django_migrations |
- | teacher_grade |
- | teacher_student |
- | teacher_studentdetail |
- +-----------------------+
- 4 rows in set (0.00 sec)
- MySQL> desc teacher_studentdetail;
- +------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | num | varchar(20) | NO | | NULL | |
- | collage | varchar(20) | NO | | NULL | |
- | student_id | int(11) | NO | UNI | NULL | |
- +------------+-------------+------+-----+---------+----------------+
- 4 rows in set (0.00 sec)
- View Code
- - OneToMany
- grade = models.ForeignKey('Grade', on_delete=models.SET_NULL, null=True)
学生和班级是一对多的关系
- class Student(models.Model):
- name = models.CharField(max_length=20)
- age = models.SmallIntegerField(default=0)
- sex =models.SmallIntegerField(default=1)
- qq = models.CharField(max_length=20,default='')
- phone = models.CharField(max_length=20)
- c_time = models.DateTimeField(verbose_name='创建时间',auto_now_add=True)
- e_time = models.DateTimeField(verbose_name='修改时间', auto_now=True)
- grade = models.ForeignKey('Grade',on_delete=models.SET_NULL,null=True)
- class Grade(models.Model):
- idd = models.AutoField(primary_key=True)
- name = models.CharField(max_length=20)
- num = models.CharField(max_length=20)
- View Code
- - ManyToMany
多对多需要通过第三张表来实现
课程表和学生表是多对多关系
- class Course(models.Model):
- name = models.CharField('课程名称',max_length=20)
- student = models.ManyToManyField('Student')
- MySQL> desc teacher_course;
- +-------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(20) | NO | | NULL | |
- +-------+-------------+------+-----+---------+----------------+
- 2 rows in set (0.00 sec)
- View Code
第三张表:
- class Enroll(models.Model):
- student = models.ForeignKey('Student',on_delete=models.CASCADE)
- course = models.ForeignKey('Course',on_delete=models.CASCADE)
- pay = models.FloatField('缴费金额',default=0)
- c_time = models.DateTimeField('报名时间',auto_now_add=True)
- MySQL> desc teacher_enroll;
- +------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------+-------------+------+-----+---------+----------------+
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | pay | double | NO | | NULL | |
- | c_time | datetime(6) | NO | | NULL | |
- | course_id | int(11) | NO | MUL | NULL | |
- | student_id | int(11) | NO | MUL | NULL | |
- +------------+-------------+------+-----+---------+--------------
2. 关联表的数据操作
- OneToMany
- 正向 一个模型如果定义了一个外键字段, 通过这个模型操作外键
增
- In [3]: s = Student()
- In [5]: Student.objects.all()
- Out[5]: <QuerySet []>
- In [6]: s.name='xinlan'
- In [7]: Student.objects.all()
- Out[7]: <QuerySet []>
- In [9]: g1 = Grade.objects.first()
- In [10]: g1
- Out[10]: <Grade: 33qi-djangokuangjia>
- In [11]: s.grade=g1
- In [12]: s.save()
- In [13]: s2 = Student(name='weimingka')
- In [14]: g2 = Grade.objects.last()
- In [15]: s2.grade_id=g2.idd
- In [16]: s2.save()
- View Code
删
- In [15]: s.grade =None
- In [16]: s.save()
- In [17]: s= Student.objects.first()
- In [18]: s.grade
- View Code
改
- In [8]: g2=Grade.objects.last()
- In [9]: g2
- Out[9]: <Grade: 34qi-pachou>
- In [10]: s.grade= g2
- In [11]: s.save()
- In [14]: s.grade
- Out[14]: <Grade: 34qi-pachou>
- View Code
查
- In [37]: s2
- Out[37]: <Student: weimingkai-0>
- In [38]: s2.name
- Out[38]: 'weimingkai'
- In [39]: s2.grade.name
- Out[39]: 'pachou'
- In [40]: s2.grade.num
- Out[40]: '34qi'
- View Code
- 反向
增
- In [41]: g3 =Grade.objects.create(name='jinjie',num='40qi')
- In [42]: g3.student_set.create(name='lipenghao')
- Out[42]: <Student: lipenghao-0>
- In [43]: g3.student_set.add(s)
- In [44]: g3.student_set.all()
- Out[44]: <QuerySet [<Student: xinlan-0>, <Student: lipenghao-0>]>
- View Code
删
删除
- In [49]: g3.student_set.remove(s)
- In [50]: g3.student_set.all()
- Out[50]: <QuerySet [<Student: lipenghao-0>]>
清空
- In [51]: g3.student_set.clear()
- In [52]: g3.student_set.all()
- Out[52]: <QuerySet []>
- View Code
改
先执行 clear 清空, 在添加列表
- In [54]: g3.student_set.set([s,s2])
- In [55]: g3.student_set.all()
- Out[55]: <QuerySet [<Student: xinlan-0>, <Student: weimingkai-0>]>
- View Code
查
出现学生表里面的课程
- In [59]: res = Student.objects.filter(grade__name='djangokuangjia')
- In [60]: print(res.query)
- SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`grade_id`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` INNER JOIN `teacher_grade` ON (`teacher_student`.`grade_id` = `teacher_grade`.`idd`) WHERE `teacher_grade`.`name` = djangokuangjia
- View Code
- -Many-to-Many
*** 指定了中间表, add,remove,set 都不能用, 必须用中间表
- In [73]: Enroll.objects.create(student=s2,course=c3)
- Out[73]: <Enroll: weimingkai-0-english>
- In [75]: c1.student
- Out[75]: <django.db.models.fields.related_descriptors.create_forward_many_to_many_manager.<locals>.ManyRelatedManager at 0x7fa557bec5f8>
- -One-to-One
- In [80]: sd = Studentdetail.objects.create(num=2019020001,collage='jialidun',student=s2)
- In [81]: sd
- Out[81]: <Studentdetail: Studentdetail object (1)>
- In [82]: sd.student
- Out[82]: <Student: weimingkai-0>
- View Code
反向
3. 跨表查询
查询男生报了什么课程?
- In [88]: res = Course.objects.filter(student__sex=1)
- In [89]: print(res.query)
- SELECT `teacher_course`.`id`, `teacher_course`.`name` FROM `teacher_course` INNER JOIN `teacher_enroll` ON (`teacher_course`.`id` = `teacher_enroll`.`course_id`) INNER JOIN `teacher_student` ON (`teacher_enroll`.`student_id` = `teacher_student`.`id`) WHERE `teacher_student`.`sex` = 1
- View Code
查询所有报名 Python 课程的学员
- In [90]: res = Student.objects.filter(course__name__contains='python')
- In [91]: print(res.query)
- SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`grade_id`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) INNER JOIN `teacher_course` ON (`teacher_enroll`.`course_id` = `teacher_course`.`id`) WHERE `teacher_course`.`name` LIKE BINARY %python%
- View Code
查询所欲报名英语 33 期的学园
- In [93]: print(res.query)
- SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`grade_id`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) INNER JOIN `teacher_course` ON (`teacher_enroll`.`course_id` = `teacher_course`.`id`) INNER JOIN `teacher_grade` ON (`teacher_student`.`grade_id` = `teacher_grade`.`idd`) WHERE (`teacher_course`.`name` LIKE BINARY %english% AND `teacher_grade`.`num` LIKE BINARY %33%)
- View Code
缴费小于 3000 的学员
- In [95]: res = Student.objects.filter(enroll__pay__lt=3000)
- In [96]: print(res.query)
- SELECT `teacher_student`.`id`, `teacher_student`.`name`, `teacher_student`.`age`, `teacher_student`.`sex`, `teacher_student`.`qq`, `teacher_student`.`phone`, `teacher_student`.`grade_id`, `teacher_student`.`c_time`, `teacher_student`.`e_time` FROM `teacher_student` INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) WHERE `teacher_enroll`.`pay` < 3000.0
- View Code
报名 python 的课有哪些
- In [99]: res=Grade.objects.filter(student__course__name__contains='python')
- In [100]: print(res.query)
- SELECT `teacher_grade`.`idd`, `teacher_grade`.`name`, `teacher_grade`.`num` FROM `teacher_grade` INNER JOIN `teacher_student` ON (`teacher_grade`.`idd` = `teacher_student`.`grade_id`) INNER JOIN `teacher_enroll` ON (`teacher_student`.`id` = `teacher_enroll`.`student_id`) INNER JOIN `teacher_course` ON (`teacher_enroll`.`course_id` = `teacher_course`.`id`) WHERE `teacher_course`.`name` LIKE BINARY %python%
- View Code
来源: https://www.cnblogs.com/donghao1121/p/10447891.html