from django.db import models


class Student(models.Model):
    name = models.CharField(max_length=10, unique=True)
    age = models.IntegerField(default=18)
    gender = models.BooleanField(default=1)

    # auto_now_add: 创建数据时，默认create_time字段为当前时间
    create_time = models.DateTimeField(auto_now_add=True, null=True)
    # auto_now:修改时间，每次update学生信息时，修改该字段的时间为当前时间
    operate_time = models.DateTimeField(auto_now=True, null=True)
    yuwen = models.DecimalField(max_digits=3, decimal_places=1, null=True)
    shuxue = models.DecimalField(max_digits=3, decimal_places=1, null=True)

    class Meta:
        # 指定student模型映射到数据库中对应的表名
        db_table = 'student'

class StuInfo(models.Model):
    phone = models.CharField(max_length=11)
    address = models.CharField(max_length=100)
    # OneToOneField 等价于 ForeignKey,且约束unique=True
    stu = models.OneToOneField(Student)

    class Meta:
        db_table = 'stu_info'

from django.db.models import Avg, Count, Sum, Max, Min, Q, F
from django.shortcuts import render
from django.http import HttpResponse
# 引入学生student模型
from app.models import Student, StuInfo


def add_stu(request):
    # 像数据库中的student表中插入数据
    # 第一种方式进行创建：save（）
    # stu = Student()
    # stu.name = '小明'
    # 向数据库中插入一条数据
    # stu.save()

    # 第二种方式
    Student.objects.create(name='莫幺儿', age=19)
    return HttpResponse('创建成功')


def del_stu(request):
    # 实现删除
    # sql: del from student where name='大锤'
    # 1.查询name=‘大锤’的信息
    # Student.objects.filter(name='大锤').delete()
    stus = Student.objects.filter(name='大锤')
    stus.delete()
    return HttpResponse('删除成功')


def up_stu(request):
    # 实现更新
    # 第一种方式
    # stus= Student.objects.filter(name='莫幺儿')
    # 获取莫幺儿对象
    # stu = stus[0]
    # stu = Student.objects.filter(name='小明').first()
    # stu.name = '狗屎'
    # stu.save()

    # 第二种方式
    Student.objects.filter(name='胡歌').update(name='莫宇', age=25)
    return HttpResponse('更新成功')


def sel_stu(request):
    # 查询
    # 查询年龄等于18的学生信息
    # first(): 获取结果中的第一个
    # last(): 获取结果中的最后一个
    stus = Student.objects.filter(age=18)

    # 查询所有学生
    stus = Student.objects.all()

    # 查询name=xxx 的学生信息
    # git(): 获取唯一的满足条件对象，且查询的条件必须存在
    # 如果查询条件不存在，则报错
    # 如果查询的结果大于一个，则报错
    stu = Student.objects.get(name='德邦')

    # 获取不叫小明的学生信息
    stus = Student.objects.exclude(name='小明')

    # 查询年龄十八且性别为女的学生信息
    stus = Student.objects.filter(age=18).filter(gender=0)
    stus = Student.objects.filter(age=18, gender=0)

    # 排序order_by
    # 降序 -id 在sql中是id desc
    # 升序 id，在sql中 id asc
    stus = Student.objects.all().order_by('-id')

    # 获取对象的值
    stus_value = Student.objects.all().values()

    # 判断name= 妲己 的学生存不存在,存在返回True, 不存在返回False

    stu = Student.objects.filter(name='妲己').exists()

    # 查询姓名中包含‘小’的学生信息，contains
    # contains : 大小写敏感
    # icontains: 大小写不敏感
    # 类似于like '%小%'
    stu = Student.objects.filter(name__contains='小')

    # 类似于like '%小'
    stus = Student.objects.filter(name__endswith='小')

    # 类似于like '小 %'
    stus = Student.objects.filter(name__startswith='小')

    # sql: select * from xxx where id in （1,2,3）
    stus = Student.objects.filter(id__in=[1, 2, 3])

    # pk代表主键
    stus = Student.objects.filter(pk__in=[1, 2, 3])

    # 大于: __gt__、大于等于: __gte__
    # 小于: __lt__、小于等于：__lte__
    stus = Student.objects.filter(age__lt=19)

    # 查询平均年龄
    stus = Student.objects.all()
    ages = 0
    for stu in stus:
        ages += stu.age
    avg_age=ages / len(stus)
    print(avg_age)

    # 聚合aggregate
    avg_age = Student.objects.all().aggregate(Avg('age'))
    print(avg_age)

    # sql:select count(age), sum(age), max(age), min(age) from student;
    sum_age = Student.objects.all().aggregate(Sum('age'))
    count_age = Student.objects.all().aggregate(Count('age'))
    max_age = Student.objects.all().aggregate(Max('age'))
    min_age = Student.objects.all().aggregate(Min('age'))
    print(sum_age, count_age, max_age, min_age)

    # 或者条件、非条件、并且条件
    # 查询age=18 并且 gender=1的学生信息
    # Q()
    # 导入包：from django.db.models import Q
    stus = Student.objects.filter(Q(age=18), Q(gender=1))

    stus = Student.objects.filter(Q(age=18) & Q(gender=1))
    # 查询age=18 或者 gender=1的学生信息
    stus = Student.objects.filter(Q(age=18) | Q(gender=1))
    # 查询满足age！=18 或者gender=1的条件
    stus = Student.objects.filter(~Q(age=18) | Q(gender=1))

    # 查询语文成绩比数学成绩好的学生信息
    # F()
    stus = Student.objects.filter(yuwen__gt=F('shuxue'))

    # 查询语文成绩比数学成绩高十分的学生信息
    stus = Student.objects.filter(yuwen__gt=F('shuxue') + 10)


    # 获取学生的姓名
    stu_names = [stu.name for stu in stus]

    return HttpResponse(stu_names)


def add_stu_info(request):

    # StuInfo.objects.create(phone='13681927364', address='四川省成都市', stu_id=6)

    s_info = StuInfo()
    s_info.phone = '15451685412'
    s_info.address = '天府新区'
    # s_info.stu_id = 1
    s_info.stu = Student.objects.get(pk=5)
    s_info.save()

    return HttpResponse('创建拓展成功')


def sel_info_by_stu(request):
    # 通过学生信息查拓展表信息
    # 查询小花的电话号码
    # sql: select stu_info.phone from student,stu_info
    # where student.id=stu_info.stu_id

    # sql2
    # select phone from stu_info,(select id from student) as  a
    # where stu_info.stu_id=a.id

    stu = Student.objects.filter(name='小花').first()
    # 第一种方法
    # stu_info = StuInfo.objects.filter(stu_id=stu.id).first()
    stu_info = StuInfo.objects.filter(stu=stu).first()
    phone = stu_info.phone

    # 第二种反向查询
    stu_info = stu.stuinfo
    phone = stu_info.phone
    return HttpResponse(phone)


def sel_stu_by_info(request):
    # 查询phone=13681927364的学生信息
    stu_info = StuInfo.objects.get(phone='13681927364')
    stu = stu_info.stu
    return HttpResponse('通过拓展表查询学生信息')

    一对一：OneToOneField
    一对多：Foreignkey
    多对多：ManyToManyFiled
    
    class Student：
        # OneToOneField定义在任何一方都可以
        stu_info = modles.OneToOneField(Student)
    
        # Foreignkey定义在多的一方
        g = modles.Foreignkey(Grade)
        
        # ManyToManyFiled定义在任何一方都可以
        c = modles.ManyToManyFiled(Course)
    
    class StuInfo:
        id = AutoField()
    class Grade:
        id = AutoField()
    class Course:
        id = AutoField()

已知学生对象stu: 
  - 查询班级：stu.g
  - 查询拓展表：stu.stu_info
  - 查询课程：stu.c

已知拓展表stu_info对象：
  - 查询学生：stu_info.student
  
已知班级对象grade:
  - 查询学生：grade.student_set
  
已知课程对象course:
  - 查询学生：course.student_set
  
一对一：知道一，查询一，对象点属性字段/对象点查询对象的小写
一对多：知道多，查询一，对象点查询对象的小写_set
多对多：分解成一对多，知道多，查询一

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>
        {% block title %}
        {% endblock %}
    </title>
</head>
<body>
    {% block content}
    {% endblock %}
</body>
</html>

{% extends 'base.html' %}


{% block title %}
{% endblock %}


{% block content %}
{% endblock %}

{% extends 'base.main.html' %}


{% block title %}
    登录
{% endblock %}


{% block content %}
    <form action="" method="post">
        <p>账号：<input type="text" name="username"></p>
        <p>密码：<input type="password" name="password"></p>
        <p><input type="submit" value="提交"></p>
    </form>
{% endblock %}