django数据库模型类和查询

from django.db import models

# Create your models here.
class Department(models.Model):
    name = models.CharField(max_length=20)
    create_date = models.DateField(auto_now_add=True, null=True)
    is_delete = models.BooleanField(default=False)

    def __str__(self):
        return self.name
    class Meta:
        db_table = "department"

class Employee(models.Model):
    CHOICES_GENDER = (
        (0,"男"),
        (1,"女"),
        (2,"未知"),
    )
    name = models.CharField(max_length=20)
    age = models.IntegerField(default=1)
    gender = models.IntegerField(choices=CHOICES_GENDER)
    # max_digits:浮点数最大位数(长度),不算小数点
    salary = models.DecimalField(decimal_places=2, max_digits=8)
    comment = models.CharField(max_length=200, null=True)
    hire_date = models.DateField()
    create_time = models.DateTimeField(auto_now_add=True, null=True)

    # 关联属性(生成外键)，一对多，在多的表建
    # CASCADE: 级联删除，默认值
    # PROTECT: 如果有关联的对象，不能删除该部门
    # department = models.ForeignKey(Department, on_delete=models.CASCADE)
    # department = models.ForeignKey(Department, on_delete=models.PROTECT)
    department = models.ForeignKey(Department, on_delete=models.SET_NULL, null=True)    #如果部门被删除，则将该字段设置为null

    class Meta:
        db_table = "employee"

普通语句

# 新增一条数据
Department.objects.create(name="研发部3")
# 查询所有数据
Department.objects.all()
# 查询一条数据，如果查询0条或者多条，报错。执行该语句后立刻向数据库进行查询
Department.objects.get(id=1)
# 根据查询条件的数据，执行语句后不会立刻对数据库进行查询，需要使用的时候才查。惰性查询
Department.objects.filter(name="研发部", is_delete=True)

# 新增一条数据
e = Employee()
e.name = "王五"
e.age = 12
e.gender = 1
e.salary = 22.22
e.hire_date = "2020-8-15"
e.department = Department.objects.get(id=1)
e.save()

# 修改
# e = Employee.objects.filter(name="王五")	# 这个也可以
e = Employee.objects.get(name="王五")
e.age=100
e.save()

查询语句

#  等同于：select * from employee where id=1;
# 类属性__exact判断相等
Employee.objects.filter(id__exact=1)
Employee.objects.filter(id=1)

# 模糊查询： contains / endswith / startswith
# select * from employee where name like '%马%';
Employee.objects.filter(name__contains='马')

# 例：查询名字以'军'结尾的员工
# select * from employee where name like '%军';
Employee.objects.filter(name__endswith='军')

# 空查询： isnull
 # select * from employee where comment is not null ;
 Employee.objects.filter(comment__isnull=False)

# 范围查询: in
# select * from employee where id in (1, 3, 5); 
Employee.objects.filter(id__in=[1,3,5])

# 比较查询: gt(greater than)、lt(less than)、gte、lte
 # elect * from employee where age >= 30; 
 Employee.objects.filter(age__gte=30)

# 日期查询： year、month、day、week_day、hour、minute、second
#select * from employee where year(hire_date) = 2015; 
 Employee.objects.filter(hire_date__year=2015)

 # 例：查询2014年1月1日后入职的员工
 #select * from employee where hire_date >= '2014-1-1';
 Employee.objects.filter(hire_date__gte='2014-1-1')


# exclude方法
# 返回不满足条件的数据，用法： 模型类.objects.exclude(条件)
# select * from employee where id != 3; 
 Employee.objects.exclude(id=3)