单表的增删改查
前期准备
1.在Django项目中建表，数据的迁移
class User(models.Model):
username = models.CharField(max_length=64,verbose_name='用户名')
password = models.CharField(max_length=64,verbose_name='密码')
age = models.IntegerField()
reg_time = models.DateField()
ps:
1.verbose_name = '...' 对字段的解释
2.DateField(auto_now=False，auto_now_add=False)
auto_now=True：当往表里面更新一条记录的时候，这个字段的值会自动把当前时间每次都更新，不用自己写了
auto_now_add=True: 当你往表里面新插入一条记录的时候，这个字段的值会自动把当前时间写进入，你不用自己写了
2.使用本地测试数据库---sqlite3
打开方式：sqlite3---pycharm双击打开、navicat
3.测试环境大搭建
1.在tests.py中搭建
2.将入口文件manage.py中:os.environ.setdefault("DJANGO_SETTINGS_MODULE", "django54.settings")CV过去；再添加一些代码
eg：
from django.test import TestCase
# Create your tests here.
if __name__ == "__main__":
import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "django54.settings")
import django
django.setup()
# 以后再写ORM相关的操作的时候，就写在这个下面
....
知识回顾
1.filter():过滤(带有过滤条件的查询)----where条件
2.first():取第一个数据对象(拿queryset里面第一个元素)
3.create():新增数据
4.update():更新数据
5.delete():删除数据
6.all():获取全部数据对象(查询所有数据)
新增数据
1.方式一：
res =models.User.objects.create(username='nana1',password='123',age = 18)
# res--->当前插入成功的这条数据对象--->res.username
models.User.objects.create(username='nana2',password='123',age = 9)
models.User.objects.create(username='nana3',password='123',age = 39)
models.User.objects.create(username='nana4',password='123',age = 25)
models.User.objects.create(username='nana5',password='123',age = 50)
2.方式二：
res = models.User(username='nana6',password='123',age=25)
print(res) # User object
res.save()
改数据
1.方式一：
res =models.User.objects.filter(pk = 1).update(username = 'cx',password = '123')
print(res) # 1
2.方式二：
user_obj = models.User.objects.filter(pk = 2).first()
print(user_obj) # User object
user_obj.username = 'xiao'
user_obj.save()
删数据
1.方式一：
res = models.User.objects.filter(pk = 6).delete()
print(res) # (1, {'app01.User': 1})
2.方式二：
user_obj = models.User.objects.filter(pk = 5).first()
res = user_obj.delete()
print(res) # (1, {'app01.User': 1})
查数据
1.models.User.objects -->objects
res = models.User.objects
print(res) # app01.User.objects
2.models.User.objects.all() -->QuerySet对象
res1 = models.User.objects.all()
print(res1) # <QuerySet [<User: User object>, <User: User object>, <User: User object>, <User: User object>, <User: User object>]>
3.models.User.objects.filter(pk = 1) -->QuerySet对象
res2 = models.User.objects.filter(pk = 1)
print(res2) # <QuerySet [<User: User object>]>
4.models.User.objects.filter(pk = 1).first() -->object
res3 = models.User.objects.filter(pk = 1).first()
print(res3) # User object
print(res3.username) # cx
print(res3.password) # 123
5.QuerySet对象，可以连续调用方法，还可以for循环，其提供了好多的方法
res4 = models.User.objects.filter(age = 25).filter(pk= 7)
print(res4) # <QuerySet [<User: User object>]>
7. .first()---->数据不存在时不报错；
objects.get() ----->数据不存在时报错---异常捕获
res = models.User.objects.filter(pk=20).first()
print(res) # None
res1 = models.User.objects.get(pk = 20)
print(res1) # 报错
常见的十几种查询方法
知识回顾
1.all()----查询所有数据
2.filter()----带有过滤条件的查询
3.get()----直接拿数据对象 但是条件不存在直接报错
4.first()----拿queryset里面第一个元素
last()
获取最后一个对象
res = models.User.objects.all()
print(res) # <QuerySet [<User: User object>, <User: User object>, <User: User object>, <User: User object>, <User: User object>]>
print(res.last()) # User object
print(res.last().username) # nana5
values()、valuelist()
1.values():指定查询的字段，返回的是列表套字典
res = models.User.objects.values('username','password')
print(res) # <QuerySet [{'username': 'cx', 'password': '123'}, {'username': 'xiao', 'password': '123'}, {'username': 'nana3', 'password': '123'}, {'username': 'nana4', 'password': '123'}, {'username': 'nana5', 'password': '123'}]>
类似于：select username,password from user
2.value_list():指定查询的字段，返回的是列表套元组
res1 = models.User.objects.values_list('username', 'password')
print(res1) # <QuerySet [('cx', '123'), ('xiao', '123'), ('nana3', '123'), ('nana4', '123'), ('nana5', '123')]>
补充:查看原生sql
1.QuerySet.query:查看sql语
res1 = models.User.objects.values_list('username', 'password')
print(res1) # <QuerySet [('cx', '123'), ('xiao', '123'), ('nana3', '123'), ('nana4', '123'), ('nana5', '123')]>
print(res1.query) # SELECT "app01_user"."username", "app01_user"."password" FROM "app01_user"
ps:返回的结果只有是QuerySet对象的时候，才有query属性，才能看sql语句
2.将LOGGING字典复制粘贴至settings.py
LOGGING = {
'version': 1,
'disable_existing_loggers': False,
'handlers': {
'console': {
'level': 'DEBUG',
'class': 'logging.StreamHandler',
},
},
'loggers': {
'django.db.backends': {
'handlers': ['console'],
'propagate': True,
'level': 'DEBUG',
},
}
}
distinct
1.distinct():去重，每一条数据都要完全不一样，如果说带主键，一定不会重复
res = models.User.objects.distinct()
print(res) # <QuerySet [<User: cx>, <User: xiao>, <User: nana3>, <User: nana4>, <User: nana5>]>
2.values().distinct():
res = models.User.objects.values('age','password').distinct()
print(res) # <QuerySet [{'age': 18, 'password': '123'}, {'age': 9, 'password': '123'}, {'age': 39, 'password': '123'}, {'age': 25, 'password': '123'}]>
order_by()
1.order_by():排序，默认是升序排列，还可以多字段(降序：'-字段名')
res = models.User.objects.order_by('age')
print(res) # <QuerySet [<User: xiao>, <User: cx>, <User: nana4>, <User: nana5>, <User: nana3>]>
res1 = models.User.objects.order_by('-age')
print(res1) # <QuerySet [<User: nana3>, <User: nana4>, <User: nana5>, <User: cx>, <User: xiao>]>
res2 = models.User.objects.order_by('age','id')
print(res2) # <QuerySet [<User: xiao>, <User: cx>, <User: nana4>, <User: nana5>, <User: nana3>]>
reverse()
1.reverse():翻转---->order_by().reverse()-----先排序再翻转
res = models.User.objects.order_by('age').reverse()
print(res) # <QuerySet [<User: nana3>, <User: nana4>, <User: nana5>, <User: cx>, <User: xiao>]>
res1 = models.User.objects.order_by('-age').reverse()
print(res1) # <QuerySet [<User: xiao>, <User: cx>, <User: nana4>, <User: nana5>, <User: nana3>]>
res2 = models.User.objects.order_by('age','id').reverse()
print(res2) # <QuerySet [<User: nana3>, <User: nana5>, <User: nana4>, <User: cx>, <User: xiao>]>
count()
1.count():查数据条数
res = models.User.objects.count()
print(res) # 5
等同于：select count(*) from user
exclude()
1.exclude():排除
res=models.User.objects.all().exclude(age = 25)
print(res) # <QuerySet [<User: cx>, <User: xiao>, <User: nana3>]>
exist()
1.exists():判断数据存不存在
res = models.User.objects.filter(pk=20).exists()
print(res) # False
基于双下划线查询
1.年龄大于35岁的数据
res = models.User.objects.filter(age__gt = 35).all()
print(res) # <QuerySet [<User: nana3>]>
2.年龄小于10岁的数据
res = models.User.objects.filter(age__lt = 10).all()
print(res) # <QuerySet [<User: xiao>]>
3.年龄大于等于10岁的数据
res = models.User.objects.filter(age__gte=10).all()
print(res) # <QuerySet [<User: cx>, <User: nana3>, <User: nana4>, <User: nana5>]>
4.年龄是9 或者 18 或者 39的数据
res = models.User.objects.filter(age__in=[9,18,39]).all()
print(res) # <QuerySet [<User: cx>, <User: xiao>, <User: nana3>]>
5.年龄在18到40岁之间的数据
res =models.User.objects.filter(age__range=[18,40]).all()
print(res) # <QuerySet [<User: cx>, <User: nana3>, <User: nana4>, <User: nana5>]>
6.查询出名字里面含有s的数据
res = models.User.objects.filter(username__contains='s').all()
print(res) # <QuerySet [<User: nanas3>, <User: snana>]>
7.用户名以s开头的
res = models.User.objects.filter(username__startswith='s').all()
print(res) # <QuerySet [<User: snana>]>
8.查询出注册时间是 2023 5月
res = models.User.objects.filter(reg_time__year=2023,reg_time__month=5)
print(res) # <QuerySet [<User: nanas3>]>
多表操作
多表查询前期准备
1.用图书表，出版社表，作者表，作者详情表为例
class Book(models.Model):
title = models.CharField(max_length=32)
price = models.DecimalField(max_digits=8,decimal_places=2)
publish_date = models.DateField(auto_now_add=True)
# 一对多
publish = models.ForeignKey(to='Publish')
# 多对多
authors = models.ManyToManyField(to='Author')
class Publish(models.Model):
name = models.CharField(max_length=32)
addr = models.CharField(max_length=64)
# varchar(254) 该字段类型不是给models看的 而是给后面我们会学到的校验性组件看的
def __str__(self):
return self.name
class Author(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
# 一对一
author_detail = models.OneToOneField(to='AuthorDetail')
class AuthorDetail(models.Model):
phone = models.BigIntegerField() # 电话号码用BigIntegerField或者直接用CharField
addr = models.CharField(max_length=64)
一对多的外键增删改查数据
需求：如何增添一本新书
1.插入数据--->publish字段--->增添出版社的数据
2.手动插入出版社的数据
3.添加图书数据
3.1:方式一：
models.Book.objects.create(title='自传1',price=666,publish_id=1)
models.Book.objects.create(title='自传2', price=55, publish_id=2)
models.Book.objects.create(title='自传3', price=777, publish_id=3)
models.Book.objects.create(title='自传4', price=22222, publish_id=2)
3.2：方式二：查出版社对象---update（publish=出版社对象）
publish_obj = models.Publish.objects.filter(pk = 1).first()
models.Book.objects.create(title='自传5',price=9999,publish=publish_obj)
需求：修改图书数据
1.方式一：
res = models.Book.objects.filter(pk = 2).update(publish_id = 3)
print(res) # 1
2.方式二：查出版社对象---update（publish=出版社对象）
publish_obj = models.Publish.objects.filter(pk=3).first()
res = models.Book.objects.filter(pk = 2).update(publish = publish_obj)
print(res) # 1
需求：删除图书数据
models.Book.objects.filter(pk=2).delete()
多对多外键增删改查
多对多的外键增删改查，本质上就是操作第三张表
需求：给书籍添加作者---->手动添加authordetail 、author数据
需求：给书籍主键为1的图书添加一个作者
1.对象.外键字段(多对多)--->到达第三张表 --->.add
# 1.找到书籍对象
book_obj = models.Book.objects.filter(pk = 1).first()
# 2.到第三张表
print(book_obj.authors) # app01.Author.None --->就相当于已经到了第三张表
# 3.添加作者
book_obj.authors.add(1)
2.一本书可以有多个作者
book_obj.authors.add(2,3)
需求：给书籍主键为1的图书删除作者
book_obj.authors.remove(1)
book_obj.authors.remove(2,3)
需求：给书籍主键为1的图书修改作者---类似于将该图书对象对应作者关系全删，再设置新的关系
book_obj = models.Book.objects.filter(pk = 1).first()
book_obj.authors.add(1,2,3)
book_obj.authors.set([1,2]) # 里面参数是一个容器类型
需求：查询图书主键为1的所有作者
book_obj = models.Book.objects.filter(pk=1).first()
print(book_obj) # Book object
print(book_obj.authors) # app01.Author.None
res = book_obj.authors.all()
print(res) # <QuerySet [<Author: Author object>, <Author: Author object>]>
print(res[0]) # Author object
print(res[0].name) # nana
补充：clear()--->清空数据：book_obj.authors.clear()
正反向概念
1.正向：外键在我手上，我查你，就是正向查询
eg：
book------外键字段在book表中------>publish------>正向查
2.反向：外键在我手上，你查我，就是反向查询
eg：
publish------外键字段在book表中------>book------>反向查询
3.判断出正反向之后，如何查询
1.正向查询按字段(外键字段)
2.反向查询按表名小写或者表名小写_set
多表查询
子查询
1.查询书籍主键为1的出版社
# 1.得出书籍对象
book_obj= models.Book.objects.filter(pk=1).first()
# 2.找出关系：书籍--->出版社--外键：书籍-->正向-->外键字段查-->对象
print(book_obj.publish) # 清华出版社-->它就相当于是出版社对象了
publish_obj = book_obj.publish
print(publish_obj.name) # 清华出版社
print(publish_obj.addr) # 北京
2.查询书籍主键为2的作者
# 1.得出书籍对象
book_obj = models.Book.objects.filter(pk = 2).first()
# 2.找出关系：书籍--->作者--外键：书籍--->正向--->按外键字段
print(book_obj.authors) # app01.Author.None--到第三张表
print(book_obj.authors.all()) # <QuerySet [<Author: Author object>, <Author: Author object>]>
query_list = book_obj.authors.all()
for author_obj in query_list:
print(author_obj.name) # nana cx
3.查询作者nana的电话号码
# 1.得出作者对象
author_obj = models.Author.objects.filter(name = 'nana').first()
# 2.找关系：作者--->详情--主键：作者--按主键字段
print(author_obj.author_detail) # AuthorDetail object
author_detail_obj = author_obj.author_detail
print(author_detail_obj.phone) # 123
4.查询出版社是清华出版社出版的书
# 1.得出出版社对象
publish_obj = models.Publish.objects.filter(name = '清华出版社').first()
# 2.找关系：出版社--->书籍---外键：书籍---反向--->按表名小写、表名小写_set
print(publish_obj.book_set) # app01.Book.None --->到了book表
print(publish_obj.book_set.all()) # <QuerySet [<Book: Book object>, <Book: Book object>]>
query_list = publish_obj.book_set.all()
for book_obj in query_list:
print(book_obj.title) # 自传1 自传5
5.查询作者是nana写过的书
# 1.找出作者对象
author_obj = models.Author.objects.filter(name='nana').first()
# 2.找关系：作者--->书籍--外键：书籍---反向---按小写表名、小写表名_set
print(author_obj.book_set) # app01.Book.None
print(author_obj.book_set.all()) # <QuerySet [<Book: Book object>, <Book: Book object>]>
query_list = author_obj.book_set.all()
for book_obj in query_list:
print(book_obj.title) # 自传1 # 自传2
6.查询手机号是123的作者姓名
# 1.找作者详情表的对象
author_detail_obj = models.AuthorDetail.objects.filter(phone = 123).first()
# 2.找关系：作者详情表--->作者表---外键：作者---反向--按小写表名、小写表名_set
print(author_detail_obj.author) # Author object
author_obj = author_detail_obj.author
print(author_obj.name) # nana
连表查询
inner join 内连接，查询两张表的共有数据
left join 左连接，查询左表的所有数据，右边的数据Null填充
right join 右连接，查询右表的所有数据，左边的数据Null填充
连表查询是基于双下划线的跨表查询
1.查询nana的手机号和作者姓名
1.方式一：
# 1.得出作者对象
author_obj = models.Author.objects.filter(name= 'nana').first()
# 2.找关系：作者-->作者详情---外键：作者---正向--按外键字段
print(author_obj.author_detail) # AuthorDetail object
author_detail_obj = author_obj.author_detail
print(author_detail_obj.phone) # 123
print(author_detail_obj.addr) # 北京
2.方式二：
# 找关系：作者-->作者详情---外键：作者---正向--按外键字段
author_obj = models.Author.objects.filter(name = 'nana').values('name','author_detail__phone')
print(author_obj) # <QuerySet [{'name': 'nana', 'author_detail__phone': 123}]>
# 反向
author_detail_obj = models.AuthorDetail.objects.filter(author__name='nana').values('phone','author__name')
print(author_detail_obj) # <QuerySet [{'phone': 123, 'author__name': 'nana'}]>
2.查询书籍主键为1的出版社名称和书的名称
# 书籍--->出版社---外键：书---正向---->按外键字段
book_obj = models.Book.objects.filter(pk = 1).values('title','publish__name')
print(book_obj) # <QuerySet [{'title': '自传1', 'publish__name': '清华出版社'}]>
# 反向
publish_obj = models.Publish.objects.filter(book__id = 1).values('name','book__title')
3.查询书籍主键为1的作者姓名
# 书籍--->作者---外键：书---正向----按外键字段
book_obj = models.Book.objects.filter(pk = 1).values('authors__name')
print(book_obj) # <QuerySet [{'authors__name': 'nana'}, {'authors__name': 'cx'}]>
# 反向
author_obj = models.Author.objects.filter(book__pk=1).values('name')
print(author_obj) # <QuerySet [{'name': 'nana'}, {'name': 'cx'}]>
4.查询书籍主键是1的作者的手机号
# 书籍--作者---作者详情 -->1.外键书籍---正向--按外键字段
book_obj = models.Book.objects.filter(pk=1).values('authors__author_detail__phone')
print(book_obj) # <QuerySet [{'authors__author_detail__phone': 123}, {'authors__author_detail__phone': 456}]>
# 反向
author_detail_obj = models.AuthorDetail.objects.values('phone').filter(author__book__pk = 1)
print(author_detail_obj) # <QuerySet [{'phone': 123}, {'phone': 456}]>
