# 添加数据
# models.表对象.objects.方法
models.classes.objects.create(classname='周末一期')
li = [
models.classes(classname='周末二期'),
models.classes(classname='周末三期'),
models.classes(classname='周末四期'),
]
models.classes.objects.bulk_create(li)
# 查询数据
# 获取所有的数据=====> select * from classses;
res = models.classes.objects.all()
<QuerySet [
<classes: classes object (1)>,
<classes: classes object (2)>,
<classes: classes object (3)>,
<classes: classes object (4)>
]>
for item in res:
print(item.id , item.classname)
# 获取classname ====> select classname from classses;
<QuerySet [
{'classname': '周末一期'},
{'classname': '周末二期'},
{'classname': '周末三期'},
{'classname': '周末四期'}]>
res = models.classes.objects.values('classname').all()
print(res)
for item in res:
print(item['classname'])
# value_list:
# <QuerySet [('周末一期',), ('周末二期',), ('周末三期',), ('周末四期',)]>
res = models.classes.objects.values_list('classname').all()
print(res)
# 取第一条 ==》 select * from classes limit 1;
res = models.classes.objects.first()
print(res.classname)
# 根据条件查询数据
res = models.classes.objects.filter(id=4)
# 删除 ===》 delete from classes where id=1;
models.classes.objects.filter(id=4).delete()
# 更新
models.classes.objects.filter(id=3).update(classname='周末3期')
# 一对多添加
models.students.objects.create(student_name='王刚蛋', classes_id=1)
models.students.objects.create(student_name='王铁锤', classes_id=2)
models.students.objects.create(student_name='二狗', classes_id=3)
models.students.objects.create(student_name='刘dd', classes_id=1)
models.students.objects.create(student_name='狗剩子', classes_id=2)
models.students.objects.create(student_name='铁蛋', classes_id=3)
models.students.objects.create(student_name='xxxx', classes_id=3)
# 正向查询
# 外键(FK) classes_id 代之===> classes表里面的一行记录
# select * from classes left join students on claess.id =stdents.classid;
# SELECT `app01_students`.`id`, `app01_students`.`student_name`, `app01_students`.`classes_id` FROM `app01_students`
res = models.students.objects.all()
for item in res:
print(item.id, item.student_name, item.classes.classname)
# 周末一期有多少学生?
# obj.表名_set.all()
# 反向查询
# select * from classes where classname='周末一期'
# id classname
res = models.classes.objects.filter(classname='周末一期').first()
select * from students where classes_id = res['id']
info = res.students_set.all()
print(info)
for item in info:
print(item.student_name)
# __ 神奇的双下划线
res = models.students.objects.filter(classes__classname='周末一期').all()
# SELECT `app01_students`.`id`,
# `app01_students`.`student_name`,
# `app01_students`.`classes_id`
# FROM `app01_students` INNER JOIN `app01_classes` ON (`app01_students`.`classes_id` = `app01_classes`.`id`) WHERE `app01_classes`.`classname` = 周末一期
print(res.query)
for item in res:
print(item.student_name)
# 获取个数 ===> count() ==> select count(*) from students
cnt = models.classes.objects.count()
print(cnt)
# 大于,小于
# greater then
res = models.classes.objects.filter(id__gt=1)
res = models.classes.objects.filter(id__gte=1)
res = models.classes.objects.filter(id__lt=1)
res = models.classes.objects.filter(id__lte=1)
# select * from classes where id < 10 and id > 1
res = models.classes.objects.filter(id__lt=10, id__gt=1) # 获取id大于1 且 小于10的值
# in
# exclude : 除去xxx ,还剩xxxx
res = models.classes.objects.filter(id__in=[11, 22, 33]) # 获取id等于11、22、33的数据
res = models.classes.objects.exclude(id__in=[11, 22, 33]) # not in
#isnull
res = models.classes.objects.filter(id__isnull=True)
# select * from stundets where student_name like '%二%';
# contains
# startswith == > like 'ven%',istartswith, endswith ===> like '%ven', iendswith,
res = models.classes.objects.filter(classname__contains="ven")
res = models.classes.objects.filter(classname__icontains="ven") # icontains大小写不敏感
models.classes.objects.exclude(classname__icontains="ven")
# range
res = models.classes.objects.filter(id__range=[1, 2]) # 范围bettwen and
#order by
#
models.classes.objects.filter(name='seven').order_by('id') # asc 升序
models.Tb1.objects.filter(name='seven').order_by('-id') # desc 降序
# group by
from django.db.models import Count, Min, Max, Sum
res = models.students.objects.values('classes_id').annotate(c=Count('id'))
# SELECT `app01_students`.`classes_id`,
# COUNT(`app01_students`.`id`) AS `c` FROM `app01_students` GROUP BY `app01_students`.`classes_id` ORDER BY NULL
# limit 、offset
res = models.Tb1.objects.all()[10:20]
print(res)
print(res.query)
# F
from django.db.models import F
models.test.objects.filter(id=1).update(age=F('age') + 1)
from django.db.models import F, Q
# Q
#
# 方式一:
Q(nid=8) | Q(nid__gt=10)
res = models.students.objects.filter(Q(id=8) | Q(id__gt=10))
# SELECT `app01_students`.`id`, `app01_students`.`student_name`, `app01_students`.`classes_id` FROM `app01_students` WHERE (`app01_students`.`id` = 8 OR `app01_students`.`id` > 10)
Q(Q(nid=8) | Q(nid__gt=10)) & Q(caption='root')
res = models.students.objects.filter(Q( Q(id=8) | Q(id__gt=10)) & Q(student_name='root'))
# SELECT `app01_students`.`id`, `app01_students`.`student_name`, `app01_students`.`classes_id` FROM `app01_students` WHERE ((`app01_students`.`id` = 8 OR `app01_students`.`id` > 10) AND `app01_students`.`student_name` = root)
print(res.query)
# 执行原生SQL
from django.db import connection, connections
cursor = connection.cursor()
cursor = connections['xxx'].cursor()
# cursor.execute("""SELECT * from auth_user where id = %s""", [1])
row = cursor.fetchone()
# 伪代码
# 伪代码
# 1. 反向查询
res = models.teacher.objects.filter(tname='eagon').first()
info = res.teacher2class_set().all()
for item in info:
item.classes.classname
# 2. 查询
res = models.teacher2class.objects.filter(teacher__tname='eagon')
for item in res:
item.classes.classname