前言

最近复习DJango，然后记录一下数据库的基本操作，大家喜欢可以收藏一下，没事look look

数据库

这是DJango的models创建数据库例子

class Book(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=32)
    pub_date = models.DateField()
    price = models.DecimalField(max_digits=8, decimal_places=2)  # 999999.99
    publish = models.CharField(max_length=32)

    def __str__(self):
        return self.title

添加表记录

方式一：

 book_obj = Book(id=1, title="python", price=100, pub_date="2020-01-01", publish="人民出版社")
 book_obj.save()

方式二：

# 具有返回值：create 返回值就是当前生成对象的记录
book_obj = Book.objects.create(id=2, title="PHP", price=50, pub_date="2021-01-01", publish="中国出版社")
print(book_obj.title)

查询表记录API

# 1. all 方法：返回一个queryset对象
book_obj = Book.objects.all()
print(book_obj)
for i in book_obj:
    print(i.title, i.price)

# 2. first：第一个,last： 最后一个    调用者：queryset对象    返回值是：model对象，返回第一个
book_start = Book.objects.all().first()
book_end = Book.objects.all().last()
print(book_start)
print(book_end)
    

# 3. filter()   where语句
book_obj = Book.objects.filter(price=100)  # [obj1,obj2]
book_obj = Book.objects.filter(price=50,title="PHP")  # [obj1,obj2]
print(book_obj[0])

# 4. get() 有切只有一个查询结果的时候才有意义，返回值：model对象
book_obj = Book.objects.get(title="PHP")
print(book_obj)

# 5. exclude： 排除   queryset对象
ret = Book.objects.exclude(title="PHP")
print(ret[0].title)

# 6. order_by排序  加-为降序 ，调用者是queryset对象，返回值也是queryset对象
ret = Book.objects.all().order_by("-id")
ret = Book.objects.all().order_by("price")
# 当两个价格一样的话，以id排序
ret = Book.objects.all().order_by("price","id")
print(ret)

# 7.  count（）总数：调用者是queryset对象，返回值int类型
ret = Book.objects.all().count()
print(ret)

# 8. exist()
ret = Book.objects.all().exists()
print(ret)

# 9. values 和 values_list  查询字段
# <QuerySet [{'title': 'python 红宝书', 'id': 1}, {'title': 'PHP', 'id': 2}]>
book_list = Book.objects.all().values("title", "price")
print(book_list[0].get("title"))
# <QuerySet [('python 红宝书', Decimal('100.00')), ('PHP', Decimal('50.00'))]>
book_list = Book.objects.all().values_list("title", "price")
print(book_list[0])
print(book_list)

# 10. distinct 去重
ret = Book.objects.all().values("price").distinct()
print(ret)

模糊查询

# __gt 大于
# __lt 小于
ret=Book.objects.filter(price__gt=50,price__lt=200)
# 字段以开头
ret=Book.objects.filter(title__startswith="P")
# 内容包含  区分大小写
ret = Book.objects.filter(title__contains="h")
# 内容包含  不区分大小写
ret = Book.objects.filter(title__icontains="h")
# in
ret = Book.objects.filter(price__in=[50, 100])
# range 范围
ret=Book.objects.filter(price_range=[50,200])
# 日期 获取2020年所有数据
ret = Book.objects.filter(pub_date__year=2020, pub_date__month=1)
print(ret)

以下是多表操作

数据库

# 作者详情表
class AuthorDetail(models.Model):
    nid = models.AutoField(primary_key=True)
    birthday = models.DateField()
    telephone = models.BigIntegerField()
    addr = models.CharField(max_length=64)


# 出版社表
class Publish(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()

    def __str__(self):
        return self.name


class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    # 一对一
    author_detail = models.OneToOneField(to="AuthorDetail", to_field="nid", on_delete=models.CASCADE)
    def __str__(self):
        self.name


# 图书表
class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    # 一对多
    publish = models.ForeignKey(to="Publish", to_field="nid", on_delete=models.CASCADE)
    # 多对多
    authors = models.ManyToManyField(to="Author")

    def __str__(self):
        return self.title

添加：多表操作一对多绑定关系

    #方式一：
    # 为book表绑定关系：book   ---  publish
    book_obj = Book.objects.create(title="红楼梦", price=100, publishDate="2020-2-2", publish_id=1)
    print(book_obj.title)
     
    # 方式二
    publish_obj = Publish.objects.filter(nid=1).first()
    book_obj = Book.objects.create(title="西游记", price=100, publishDate="2020-2-2", publish=publish_obj)
    print(book_obj.title)  # 与这本书关联的出版社对象
    print(book_obj.publish)
    print(book_obj.publish.email)
    # 
    # 查询西游的出版社邮箱
    book_obj = Book.objects.filter(title="西游记").first()
    print(book_obj.publish.email)

添加：多对多绑定关系

    book_obj = Book.objects.create(title="三国演义", price=100, publishDate="2020-2-2", publish_id=1)
    luo = Author.objects.filter(nid=1).first()
    si = Author.objects.filter(nid=2).first()

    # 绑定多对多的API
    book_obj.authors.add(luo, si)
    book_obj.authors.add(1, 2, 3)
    book_obj.authors.add(*[1, 2, 3])

    # 接触多对多关系
    book_obj = Book.objects.filter(nid=8).first()
    book_obj.authors.remove(1)
    # # 删除全部
    book_obj.authors.clear()
    print(book_obj.authors.all())  # [obj1,obj2...]queryset 集合

    # 查询主键为8所有作者的名字
    ret = book_obj.authors.all().values("name")
    print(ret)

基于对象跨表查询：

def query(request):
    """
    跨表查询：
        1.基于对象查询
        2.基于双下划线查询
        3.聚合和分组查询
        4.F和Q 查询
    :param request:
    :return:
    """
    #  ----------------------基于对象跨表查询（子查询）------------------

    # 一对多查询的正向查询
    # 查询西游记的出版社名称
    # book_obj = Book.objects.filter(title="西游记").first()
    # print(book_obj.publish)  # 与书相关的出版社对象
    # print(book_obj.publish.name)

    # 一对多反向查询，查询出版社的书籍名称
    # publish_obj = Publish.objects.filter(name="人民出版社").first()
    # book_name=publish_obj.book_set.all().values("title").first().get("title")
    # print(book_name)

    # 多对多查询,查询西游记的作者名称
    # book_obj = Book.objects.filter(title="三国演义").first()
    # author_list = book_obj.authors.all()
    # for author in author_list:
    #     print(author.name)

    # 多对多反向查询：找施耐庵的书籍
    author_obj = Author.objects.filter(name="施耐庵").first()
    book_list = author_obj.book_set.all()
    for book in book_list:
        print(book.title)

    return HttpResponse("OK")