7.常用查询以及表关系的实现

常用的模型字段类型
https://docs.djangoproject.com/en/2.1/ref/models/fields/#field-types

AutoFiled(pirmary_key=True) 自定义主键字段的创建一般不会自定义
IntegerField : 整型，映射到数据库中的int类型。
SmallIntegerField : 小整形
CharField: 字符类型，映射到数据库中的varchar类型，必须通过max_length指定最大长度。
TextField: 文本类型，映射到数据库中的text类型。
BooleanField: 布尔类型，映射到数据库中的tinyint类型，在使用的时候，传递True/False进去。如果要可以为空，则用NullBooleanField。
DateField: 日期类型，没有时间。映射到数据库中是date类型，在使用的时候，可以设置- - DateField.auto_now每次保存对象时，自动设置该字段为当前时间。设置DateField.auto_now_add当对象第一次被创建时自动设置当前时间。
DateTimeField: 日期时间类型。映射到数据库中的是datetime类型，在使用的时候，传递datetime.datetime()进去

字段的常用参数
官方文档：https://docs.djangoproject.com/en/2.1/ref/models/fields/#field-options

primary_key: 指定是否为主键。
unique: 指定是否唯一。
null: 指定是否为空，默认为False。
blank: 等于True时form表单验证时可以为空，默认为False。
default: 设置默认值。
DateTimeField（DateField）.auto_now: 每次修改都会将当前时间更新进去，只有调用，QuerySet.update方法将不会调用。这个参数只是Date和DateTime以及TimModel.save()方法才会调用e类才有的。
DateTimeField（DateField）.auto_now_add: 第一次添加进去，都会将当前时间设置进去。以后修改，不会修改这个值。

常用查询
通过模型类上的管理器来构造QuerySet。

模型类上的管理器是啥？
模型类.objects

>>> from index.models import StudentDetail
>>> stu = StudentDetail.objects
>>> stu
<django.db.models.manager.Manager object at 0x7f1e776ac080>

QuerySet 表示数据库中对象的集合。
等同于select 语句。惰性的
first() 获取第一条返回的是对象

>>> stu = StudentDetail.objects.first()
>>> stu
<StudentDetail: liuwei-1>

last() 获取最后一条返回的也是一个对象
思考：排序规则？默认通过主键。通过_meta 设置
get(**kwargs) 根据给定的条件，获取一个对象，如果有多个对象符合，保存

>>> stu = StudentDetail.objects.get(name='liuwei')
>>> stu
<StudentDetail: liuwei-1>

all() 获取所有记录返回的是queryset

>>> stu = StudentDetail.objects.all()
>>> stu
<QuerySet [<StudentDetail: liuwei-1>, <StudentDetail: xinlan-2>, <StudentDetail: dabao-3>, <StudentDetail: jiajia-4>, <StudentDetail: huihui-5>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail`

filter(**kwargs) 根据给定的条件，获取一个过滤后的queryset，多个条件使用and连接。

>>> stu = StudentDetail.objects.filter(name='liuwei')
>>> stu
<QuerySet [<StudentDetail: liuwei-1>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` WHERE `index_studentdetail`.`name` = liuwei

exclude(**kwargs) 跟filter使用方法一致，作用想反，它是排除。

>>> stu = StudentDetail.objects.exclude(sex=1)
>>> stu
<QuerySet [<StudentDetail: jiajia-4>, <StudentDetail: huihui-5>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` WHERE NOT (`index_studentdetail`.`sex` = 1)

多条件的OR连接用到Q对象，django.db.models.Q

>>> from django.db.models import Q
>>> stu = StudentDetail.objects.filter(Q(name='liuwei')|Q(name='jiajia'))
>>> stu
<QuerySet [<StudentDetail: liuwei-1>, <StudentDetail: jiajia-4>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` WHERE (`index_studentdetail`.`name` = liuwei OR `index_studentdetail`.`name` = jiajia)

values(*fields) 返回一个queryset，返回一个字典列表，而不是数据对象。

>>> stu = StudentDetail.objects.values('name')
>>> stu
<QuerySet [{'name': 'liuwei'}, {'name': 'xinlan'}, {'name': 'dabao'}, {'name': 'jiajia'}, {'name': 'huihui'}]>
>>> print(stu.query)
SELECT `index_studentdetail`.`name` FROM `index_studentdetail`

only(*fiels) 返回querySet ，对象列表，注意only一定包含主键字段

>>> stu = StudentDetail.objects.only('name')
>>> stu
<QuerySet [<StudentDetail: liuwei-1>, <StudentDetail: xinlan-2>, <StudentDetail: dabao-3>, <StudentDetail: jiajia-4>, <StudentDetail: huihui-5>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name` FROM `index_studentdetail`

defer(*fields) 返回一个QuerySet，作用和only相反

>>> stu = StudentDetail.objects.defer('c_time', 'e_time')
>>> stu
<QuerySet [<StudentDetail: liuwei-1>, <StudentDetail: xinlan-2>, <StudentDetail: dabao-3>, <StudentDetail: jiajia-4>, <StudentDetail: huihui-5>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex` FROM `index_studentdetail`

order_by(*fields) 根据给定的字段来排序默认是顺序，字段名前加上 ‘-’代表反序

>>> stu = StudentDetail.objects.only('name').order_by('-c_time')
>>> stu
<QuerySet [<StudentDetail: huihui-5>, <StudentDetail: jiajia-4>, <StudentDetail: dabao-3>, <StudentDetail: xinlan-2>, <StudentDetail: liuwei-1>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name` FROM `index_studentdetail` ORDER BY `index_studentdetail`.`c_time` DESC

切片和python的列表切片用法相似，不支持负索引，数据量大时不用步长
*** 切片过后，不再支持，附加过滤条件与排序

>>> stu = StudentDetail.objects.all()[:4]
>>> stu
<QuerySet [<StudentDetail: liuwei-1>, <StudentDetail: xinlan-2>, <StudentDetail: dabao-3>, <StudentDetail: jiajia-4>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail`  LIMIT 4

常用查询条件(多用于filter，exclude， get）

exact 等于

>>> stu = StudentDetail.objects.filter(name__exact='jiajia')
>>> stu
<QuerySet [<StudentDetail: jiajia-4>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`,         `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM         `index_studentdetail` WHERE `index_studentdetail`.`name` = jiajia

iexact 等于（不区分大小写）

contains

>>> stu = StudentDetail.objects.filter(name__contains='jiajia')
>>> stu
<QuerySet [<StudentDetail: jiajia-4>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` WHERE `index_studentdetail`.`name` LIKE BINARY %jiajia%

icontains
in
```
Student.objects.filter(id__in=[1,2,7])
```

range

Student.objects.filter(id__range=(18,20))

gt 大于

>>> stu = StudentDetail.objects.filter(id__gt=2)
>>> stu
<QuerySet [<StudentDetail: dabao-3>, <StudentDetail: jiajia-4>, <StudentDetail: huihui-5>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` WHERE `index_studentdetail`.`id` > 2

gte
lt 小于
lte
startswith
istartswith
endswith
iendswith

isnull
True False 对应 IS NULL IS NOT NULL

>>> stu = StudentDetail.objects.filter(name__isnull=False)
>>> stu
<QuerySet [<StudentDetail: liuwei-1>, <StudentDetail: xinlan-2>, <StudentDetail: dabao-3>,         <StudentDetail: jiajia-4>, <StudentDetail: huihui-5>]>
>>> print(stu.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` WHERE `index_studentdetail`.`name` IS NOT NULL

举例按姓名小写进行排序 asc() desc()

>>> from index.models import StudentDetail
>>> from django.db.models.functions import Lower
>>> res = StudentDetail.objects.order_by(Lower('name'))
>>> res
<QuerySet [<StudentDetail: dabao-3>, <StudentDetail: huihui-5>, <StudentDetail: jiajia-4>, <StudentDetail: liuwei-1>, <StudentDetail: xinlan-2>]>
>>> print(res.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` ORDER BY LOWER(`index_studentdetail`.`name`) ASC
# -----------------------------------------倒叙-----------------------------------------------#
>>> res = StudentDetail.objects.order_by(Lower('name').desc())
>>> res
<QuerySet [<StudentDetail: xinlan-2>, <StudentDetail: liuwei-1>, <StudentDetail: jiajia-4>, <StudentDetail: huihui-5>, <StudentDetail: dabao-3>]>
>>> print(res.query)
SELECT `index_studentdetail`.`id`, `index_studentdetail`.`name`, `index_studentdetail`.`age`, `index_studentdetail`.`sex`, `index_studentdetail`.`c_time`, `index_studentdetail`.`e_time` FROM `index_studentdetail` ORDER BY LOWER(`index_studentdetail`.`name`) DESC

聚合
from django.db.models import Count, Avg, Max, Min, Sum

count
```
Student.objects.filter().count()
```

Avg, Max, Min, Sum
通过queryset的aggregate方法
Student.objects.aggregate(age_avg=Avg('age')) # 计算平均年龄

>>> from django.db.models import Count, Avg, Max, Min, Sum
>>> stu = StudentDetail.objects.filter(name='liuwei').count()
>>> stu
1
>>> stu = StudentDetail.objects.aggregate(age_avg = Avg('age'))
>>> stu
{'age_avg': 16.8}
>>> stu = StudentDetail.objects.aggregate(age_avg = Max('age'))
>>> stu
{'age_avg': 18}
>>> stu = StudentDetail.objects.aggregate(age_avg = Min('age'))
>>> stu
{'age_avg': 16}
>>> stu = StudentDetail.objects.aggregate(age_avg = Sum('age'))
>>> stu
{'age_avg': 84}

分组(一定要聚合)

结合 Values,annotate 和聚合方法一起实现

>>> stu = StudentDetail.objects.values('sex').annotate(num=Count('sex'))
>>> stu
<QuerySet [{'sex': 1, 'num': 3}, {'sex': 0, 'num': 2}]>
>>> stu = StudentDetail.objects.values('age').annotate(num=Count('sex'))
>>> stu
<QuerySet [{'age': 18, 'num': 2}, {'age': 16, 'num': 3}]>

类似查询男生有几个，女生有几个

表关系的创建

OneToOne 其实也就是添加附表，增加额外信息
student = models.OneToOneField('Student', on_delete=models.CASCADE)

from django.db import models


class Student(models.Model):
    name = models.CharField(max_length=20)
    age = models.SmallIntegerField(default=0)
    sex = models.SmallIntegerField(default=0)
    c_time = models.DateTimeField(auto_now_add=True)
    e_time = models.DateTimeField(auto_now=True)

    def __str__(self):
        return '{}-{}'.format(self.id, self.name)

class StudentDetail(models.Model):
    qq = models.CharField(max_length=15,default='')
    phone = models.CharField(max_length=15,default='')
    college = models.CharField(max_length=20,default='')
    # models.OneToOneField('Student', on_delete=models.CASCADE)
    # 第一个参数 相关联的主表(字符串格式)
    # 第二个参数 级联操作 on_delete=models.CASCADE 主表删除，子表也对应删除
    student = models.OneToOneField('Student', on_delete=models.CASCADE)

    def __str__(self):
        return '{}号学生详情'.format(self.id)

mysql> desc index_student;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | NO   |     | NULL    |                |
| age    | smallint(6) | NO   |     | NULL    |                |
| sex    | smallint(6) | NO   |     | NULL    |                |
| c_time | datetime(6) | NO   |     | NULL    |                |
| e_time | datetime(6) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> desc index_studentdetail;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| qq         | varchar(15) | NO   |     | NULL    |                |
| phone      | varchar(15) | NO   |     | NULL    |                |
| college    | varchar(20) | NO   |     | NULL    |                |
| student_id | int(11)     | NO   | UNI | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

OneToMany
area = models.ForeignKey('Area', on_delete=models.SET_NULL, null=True)

from django.db import models


class Student(models.Model):
    name = models.CharField(max_length=20)
    age = models.SmallIntegerField(default=0)
    sex = models.SmallIntegerField(default=0)
    c_time = models.DateTimeField(auto_now_add=True)
    e_time = models.DateTimeField(auto_now=True)
    # 第一个参数 'Area' 字符串格式，
    # 不需要删除学生就删除地区，只需设置为空就好 on_delete=models.SET_NULL, null=True
    area = models.ForeignKey('Area', on_delete=models.SET_NULL, null=True)
    def __str__(self):
        return '{}-{}'.format(self.id, self.name)

# 地区表
# 一个学生只能在一个地区，一个地区可以有多个学生，须在学生字段添加外键（多的一方添加外键）
class Area(models.Model):
    address = models.CharField(max_length=20)
    
    def __str__(self):
        return '{}区域'.format(self.address)

mysql> desc index_area;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| address  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc index_student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| age     | smallint(6) | NO   |     | NULL    |                |
| sex     | smallint(6) | NO   |     | NULL    |                |
| c_time  | datetime(6) | NO   |     | NULL    |                |
| e_time  | datetime(6) | NO   |     | NULL    |                |
| area_id | int(11)     | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

ManyToMany
course = models.CharField(verbose_name='课程名称', max_length=20)

from django.db import models


class Student(models.Model):
    name = models.CharField(max_length=20)
    age = models.SmallIntegerField(default=0)
    sex = models.SmallIntegerField(default=0)
    c_time = models.DateTimeField(auto_now_add=True)
    e_time = models.DateTimeField(auto_now=True)
    # 第一个参数 'Area' 字符串格式，
    # 不需要删除学生就删除地区，只需设置为空就好 on_delete=models.SET_NULL, null=True
    area = models.ForeignKey('Area', on_delete=models.SET_NULL, null=True)
    def __str__(self):
        return '{}-{}'.format(self.id, self.name)

# 课程表
class Course(models.Model):
    course = models.CharField(verbose_name='课程名称', max_length=20)
    # 多对多
    # 第一个参数 'Student' 字符串格式
    student = models.ManyToManyField('Student')

# 会自动创建第三张表

mysql> desc index_student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| age     | smallint(6) | NO   |     | NULL    |                |
| sex     | smallint(6) | NO   |     | NULL    |                |
| c_time  | datetime(6) | NO   |     | NULL    |                |
| e_time  | datetime(6) | NO   |     | NULL    |                |
| area_id | int(11)     | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> desc index_course;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| course | varchar(20) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc index_course_student;
+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| course_id  | int(11) | NO   | MUL | NULL    |                |
| student_id | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

若需对第三张表有字段需求

class Student(models.Model):
    name = models.CharField(max_length=20)
    age = models.SmallIntegerField(default=0)
    sex = models.SmallIntegerField(default=0)
    c_time = models.DateTimeField(auto_now_add=True)
    e_time = models.DateTimeField(auto_now=True)
    # 第一个参数 'Area' 字符串格式，
    # 不需要删除学生就删除地区，只需设置为空就好 on_delete=models.SET_NULL, null=True
    area = models.ForeignKey('Area', on_delete=models.SET_NULL, null=True)
    def __str__(self):
        return '{}-{}'.format(self.id, self.name)

# 课程表
class Course(models.Model):
    course = models.CharField(verbose_name='课程名称', max_length=20)
    # 多对多
    # 第一个参数 'Student' 字符串格式,
    # 第二个参数指定第三张表
    student = models.ManyToManyField('Student', through='Enroll')

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 index_student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| age     | smallint(6) | NO   |     | NULL    |                |
| sex     | smallint(6) | NO   |     | NULL    |                |
| c_time  | datetime(6) | NO   |     | NULL    |                |
| e_time  | datetime(6) | NO   |     | NULL    |                |
| area_id | int(11)     | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> desc index_course;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| course | varchar(20) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc index_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    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

关联表的数据操作

OneToMany
正向一个模型如果定义了一个外键字段，通过这个模型操作外键(通过学生操作区域)
增删改查

>>> from index.models import Student, StudentDetail, Area, Course, Enroll
# --------------必须先地区表（一）增加数据----------------------#
>>> g1 = Area.objects.create(addr='华北')
>>> g1
<Area: 华北区域>
>>> g2 = Area.objects.create(addr='华中')
>>> g2
<Area: 华中区域>
>>> g3 = Area.objects.create(addr='华南')
>>> g3
<Area: 华南区域>
>>> Area.objects.all()
<QuerySet [<Area: 华北区域>, <Area: 华中区域>, <Area: 华南区域>]>
# --------------学生表（多）增加数据 方式一：赋值地区对象----------------------#
>>> s = Student()
>>> s.name = '心蓝'
>>> a1 = Area.objects.first()
>>> a1
<Area: 华北区域>
>>> s.area = a1
>>> s.save()

mysql> select * from index_student;
+----+--------+-----+-----+----------------------------+----------------------------+---------+
| id | name   | age | sex | c_time                     | e_time                     | area_id |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
|  1 | 心蓝   |   0 |   0 | 2019-02-27 12:00:05.888228 | 2019-02-27 12:00:05.888296 |       1 |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
1 row in set (0.00 sec)

# --------------学生表（多）增加数据 方式二：赋值地区id----------------------#
>>> s2 = Student(name='塔卡')
>>> a2 = Area.objects.last()
>>> a2
<Area: 华南区域>
>>> s2.area_id=a2.id
>>> s2.save()

mysql> select * from index_student;
+----+--------+-----+-----+----------------------------+----------------------------+---------+
| id | name   | age | sex | c_time                     | e_time                     | area_id |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
|  1 | 心蓝   |   0 |   0 | 2019-02-27 12:00:05.888228 | 2019-02-27 12:00:05.888296 |       1 |
|  2 | 塔卡   |   0 |   0 | 2019-02-27 12:04:42.273799 | 2019-02-27 12:04:42.273839 |       3 |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
2 rows in set (0.00 sec)

#------------------------------------修改数据-----------------------------------------#
>>> s.area = a2
>>> s.save()

mysql> select * from index_student;
+----+--------+-----+-----+----------------------------+----------------------------+---------+
| id | name   | age | sex | c_time                     | e_time                     | area_id |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
|  1 | 心蓝   |   0 |   0 | 2019-02-27 12:00:05.888228 | 2019-02-27 12:08:24.641443 |       3 |
|  2 | 塔卡   |   0 |   0 | 2019-02-27 12:04:42.273799 | 2019-02-27 12:04:42.273839 |       3 |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
2 rows in set (0.00 sec)

#------------------------------------删除数据-----------------------------------------#
>>> s.area = None
>>> s.save()

mysql> select * from index_student;
+----+--------+-----+-----+----------------------------+----------------------------+---------+
| id | name   | age | sex | c_time                     | e_time                     | area_id |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
|  1 | 心蓝   |   0 |   0 | 2019-02-27 12:00:05.888228 | 2019-02-27 12:11:01.305708 |    NULL |
|  2 | 塔卡   |   0 |   0 | 2019-02-27 12:04:42.273799 | 2019-02-27 12:04:42.273839 |       3 |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
2 rows in set (0.00 sec)

#------------------------------------查询数据-----------------------------------------#
>>> s1 = Student.objects.last()
>>> s1.name
'塔卡'
>>> s1.area.address
'华南'

反向（通过地区操作学生）
增删改查

>>> a1 = Area.objects.last()
>>> a1
<Area: 华南区域>
# a1.student_set 为一个反向管理器， 对象.表名小写_set
>>> a1.student_set
<django.db.models.fields.related_descriptors.create_reverse_many_to_one_manager.<locals>.RelatedManager object at 0x7f765764d4a8>
#-----------------------------------反向增加数据方式一----------------------------------#
>>> a1.student_set.create(name='小泼')
<Student: 3-小泼>

mysql> select * from index_student;
+----+--------+-----+-----+----------------------------+----------------------------+---------+
| id | name   | age | sex | c_time                     | e_time                     | area_id |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
|  1 | 心蓝   |   0 |   0 | 2019-02-27 12:00:05.888228 | 2019-02-27 12:11:01.305708 |    NULL |
|  2 | 塔卡   |   0 |   0 | 2019-02-27 12:04:42.273799 | 2019-02-27 12:04:42.273839 |       3 |
|  3 | 小泼   |   0 |   0 | 2019-02-27 12:31:07.214964 | 2019-02-27 12:31:07.215024 |       3 |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
3 rows in set (0.01 sec)
#-----------------------------------反向增加数据方式二----------------------------------#
>>> s = Student.objects.create(name='空山')
# add可以包括很多对象add(s1,s3,s5)
>>> a1.student_set.add(s)

mysql> select * from index_student;
+----+--------+-----+-----+----------------------------+----------------------------+---------+
| id | name   | age | sex | c_time                     | e_time                     | area_id |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
|  1 | 心蓝   |   0 |   0 | 2019-02-27 12:00:05.888228 | 2019-02-27 12:11:01.305708 |    NULL |
|  2 | 塔卡   |   0 |   0 | 2019-02-27 12:04:42.273799 | 2019-02-27 12:04:42.273839 |       3 |
|  3 | 小泼   |   0 |   0 | 2019-02-27 12:31:07.214964 | 2019-02-27 12:31:07.215024 |       3 |
|  4 | 空山   |   0 |   0 | 2019-02-27 12:34:50.317970 | 2019-02-27 12:34:50.318010 |       3 |
+----+--------+-----+-----+----------------------------+----------------------------+---------+
4 rows in set (0.00 sec)

#-----------------------------------反向查询数据方式----------------------------------#
# student_set 相当于objects
>>> a1.student_set.all()
<QuerySet [<Student: 2-塔卡>, <Student: 3-小泼>, <Student: 4-空山>]>
>>> a1.student_set.filter(name='塔卡')
<QuerySet [<Student: 2-塔卡>]>
>>> a1.student_set.filter(name='塔卡').count()
1

#-----------------------------------反向删除数据方式一----------------------------------#
>>> a1.student_set.all()
<QuerySet [<Student: 2-塔卡>, <Student: 3-小泼>, <Student: 4-空山>]>
>>> s
<Student: 4-空山>
# remove(s1,s2,s3)
>>> a1.student_set.remove(s)
>>> a1.student_set.all()
<QuerySet [<Student: 2-塔卡>, <Student: 3-小泼>]>

#-----------------------------------反向删除数据方式二----------------------------------#
>>> a1.student_set.clear()
>>> a1.student_set.all();
<QuerySet []>

#-----------------------------------反向修改数据----------------------------------#
>>> s1 = Student.objects.first()
>>> s2 = Student.objects.last()
# 此方法会先执行clear，在添加
>>> a1.student_set.set([s1,s2]);
>>> a1.student_set.all();
<QuerySet [<Student: 1-心蓝>, <Student: 4-空山>]>

>>> a1.student_set.set([s1]);
>>> a1.student_set.all();
<QuerySet [<Student: 1-心蓝>]>

举例：查询该地区的所有学生

>>> Student.objects.filter(area__address='华南')
<QuerySet [<Student: 1-心蓝>, <Student: 4-空山>]>

>>> print(Student.objects.filter(area__address='华南').query)
SELECT `index_student`.`id`, `index_student`.`name`, `index_student`.`age`, `index_student`.`sex`, `index_student`.`c_time`, `index_student`.`e_time`, `index_student`.`area_id` FROM `index_student` INNER JOIN `index_area` ON (`index_student`.`area_id` = `index_area`.`id`) WHERE `index_area`.`address` = 华南

Many-to-Many
*** 指定了中间表Enroll，add，remove，set 都不能用，必须用中间表

>>> from index.models import Student, StudentDetail, Area, Course, Enroll
>>> s1 = Student.objects.create(name='心蓝')
>>> s2 = Student.objects.create(name='塔卡')
>>> c1 = Course.objects.create(course='python')
>>> c2 = Course.objects.create(course='java')
#---------------------增加数据方式一--------------------#
>>> e = Enroll()
>>> e.student = s1
>>> e.course = c1
>>> e.save()

#---------------------增加数据方式二--------------------#
>>> e = Enroll()
>>> e.student_id=s2.id
>>> e.course_id=c2.id
>>> e.save()

#---------------------增加数据方式三--------------------#
>>> Enroll.objects.create(student=s1,course=c2)
<Enroll: 1-心蓝学生报名了java课程课程>
>>> Enroll.objects.create(student=s2,course=c1)
<Enroll: 2-塔卡学生报名了python课程课程>

#---------------------查询数据--------------------#
# 正向 该课程多少人报名 因为manytomany外键字段在course中，所以通过course表查询为正向
>>> c1.student.all()
<QuerySet [<Student: 2-塔卡>, <Student: 2-塔卡>]>

# 反向 该学生报名了多少门课
>>> s1.course_set.all()
<QuerySet [<Course: java课程>]>

One-to-One

# 与一对多类似，也有正向反向之别
#---------------------------正向----------------------#
>>> s1
<Student: 1-心蓝>
>>> sd = StudentDetail.objects.create(student=s1)
>>> sd
<StudentDetail: 1号学生详情>
>>> sd.student.name
'心蓝'

#---------------------------反向----------------------#

>>> s1.studentdetail       # 这是一个对象，并非管理器，与上面不一样
<StudentDetail: 1号学生详情>
>>> s1.studentdetail.college
''

跨表查询（关联字段名__相对应字段名）

# 查询男生都报名了什么课程
>>> Course.objects.filter(student__sex=0)
<QuerySet [<Course: java课程>, <Course: python课程>, <Course: python课程>]>
# 心蓝老师报名了什么课程
>>> Course.objects.filter(student__name='心蓝')
<QuerySet [<Course: java课程>]>
# 模糊查询报了python课程的男学员
>>> Student.objects.filter(course__course__contains='java', sex=0)
<QuerySet [<Student: 1-心蓝>]>
# 小于3000的课程
>>> Student.objects.filter(enroll__pay__lt=3000)
<QuerySet [<Student: 2-塔卡>, <Student: 1-心蓝>, <Student: 2-塔卡>]>
# 报名python课程的学员的地区
>>> Area.objects.filter(student__course__course__contains='python')
<QuerySet []>

7.常用查询以及表关系的实现

推荐阅读更多精彩内容