Some query operations in the model:

(1) Aggregate queries: Aggregate () is a termination clause of QuerySet that returns a dictionary of key and value pairs

from movie.models import User
from django.db.models import Count, Avg, Max, Min, Sum

def test_info(request) :
    # aggregate query
    The default data format returned is a dictionary, where the key name is age__avg and the value is the average of the specified field.
    rs = User.objects.all().aggregate(Avg("age"))
    You can specify the key name of the dictionary by specifying the following.
    rs = User.objects.all().aggregate(age=Avg("age"))
    # average, maximum, minimum, sum
    rs = User.objects.all().aggregate(Avg("age"),Max("age"),Min("age"),Sum("age"))
Copy the code

By default (average age in User) :Specify the key name (average age in User) :

(2) Q queries: If you need to perform more complex queries (such as OR statements), you can use Q objects. Q object can use & (and), | (or) operator; Use the ~ (not) operator to indicate negation before a Q object

Select * from User where name = xiaoming or xiared
rs = User.objects.filter(Q(name='Ming')|Q(name='little red'))
Select * from Student where name = xiaoming and age = 18
rs = User.objects.filter(Q(name='Ming')&~Q(age=18))
Copy the code

(3) F query: (query is a whole column of data! Comparison of the values of two fields

The college ID is smaller than the student id
rs = Student.objects.filter(department_id__gt=F(Rs = user.objects.all ().update(age=F(')age') + 1)Copy the code

(4) Group query: generate an independent statistical value for each object in the QuerySet called

① One-to-many relationship:

import Student,Course
from django.db.models import Count

# Take out the fields in the student table that you need to group
rs = Student.objects.values()     Display all information about all queried data in dictionary form
"" output" example: < QuerySet [{' s_id: 1, 's_name' : 'Ming', 'department_id: 1}, {' s_id: 2,' s_name ': 'little red', 'department_id, 2}, {' s_id: 3,' s_name ':' wang, department_id ': 1}] > "" "
rs = Student.objects.values('department')  Display the department field of all queried data in dictionary form
"" output" example: < QuerySet [{' department ': 1}, {' department' : 2}, {" department ": 1}] >", ""
rs = Student.objects.values('s_name'.'department')  Display the s_NAME and department fields of all queried data in dictionary form
"" output" example: < QuerySet [{' s_name ':' xiao Ming ', 'department' : 1}, {' s_name ':' red ', 'department' : 2}, {' s_name ': "> < span style =" max-width: 100%;


Group the department field
rs = Student.objects.values('department').annotate(count=Count('department'))
"" output" example: < QuerySet [{' department ': 1, "count" : 2}, {' department' : 2, "count" : 1}] > ", ""
Output the grouped content according to the required fields
rs = Student.objects.values('department').annotate(count=Count('department')).values('department'.'count')
"" output" example: < QuerySet [{' department ': 1, "count" : 2}, {' department' : 2, "count" : 1}] > ", ""
Copy the code

① Many-to-many relationship:

# Many-to-many relationships
# Look up the number of students in each course and get the data of all courses
rs = Course.objects.all(a)# In the relation table, the number of student id in the same course is the number of students who have signed up for this course
rs = Course.objects.all().annotate(count=Count('students'))
rs = Course.objects.all().annotate(count=Count('students')).values('c_name'.'count')

# Check the number of courses each student has signed up for and get the data of all students
rs = Student.objects.all(a)In the relational table, the number of courses for the same student is the number of courses that the student has signed up for
rs = Student.objects.all().annotate(count=Count('course'))
rs = Student.objects.all().annotate(count=Count('course')).value('s_name'.'count')

Copy the code