[TimLinux] myblog 数据表格显示

1. 设计

2. 数据

 创建数据库用户:

CREATE USER IF NOT EXISTS 'user1'@'MyBlogPwd123';
GRANT ALL ON d1.* TO 'user1'@'%';

创建数据库d1:

CREATE DATABASE IF NOT EXISTS d1 DEFAULT CHARSET = utf8;

创建数据表:

USE d1;

CREATE TABLE IF NOT EXISTS score (
    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(32) NOT NULL,
    KEY score_name_key (`name`),
    UNIQUE (`name`)
);

CREATE TABLE IF NOT EXISTS student (
    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `stuid` INT(8) ZEROFILL NOT NULL DEFAULT '00000000',
    `name` VARCHAR(32) NOT NULL,
    `sex` BOOLEAN NOT NULL,
    KEY student_name_key (`name`),
    UNIQUE (`stuid`)
);

CREATE TABLE IF NOT EXISTS student_score (
    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `student_id` INT(11) NOT NULL,
    `score_id` INT(11) NOT NULL,
    `pass_flag` BOOLEAN,
    KEY student_score_student_key (`student_id`),
    KEY student_score_score_key (`score_id`),
    CONSTRAINT student_score_student_key FOREIGN KEY (`student_id`) REFERENCES `student` (`id`),
    CONSTRAINT student_score_score_key FOREIGN KEY (`score_id`) REFERENCES `score` (`id`)
);

导入数据: 

INSERT INTO score VALUES 
    (1, '高等数学'),
    (2, '军事理论'),
    (3, '大学英语'),
    (4, '离散数学'),
    (5, '数据库导论');

INSERT INTO student VALUES
    (1, '20040001', '赵敏', 0),
    (2, '20040002', '周芷若', 0),
    (3, '20040003', '小昭', 0),
    (4, '20040004', '殷离', 1),
    (5, '20040005', '张翠山', 1),
    (6, '20040006', '谢逊', 1),
    (7, '20040007', '灭绝师太', 0),
    (8, '20040008', '张无忌', 1),
    (9, '20040009', '殷素素', 0),
    (10, '20040010', '陈友谅', 1);

INSERT INTO student_score (student_id, score_id, pass_flag) VALUES
    (1, 1, 1), (1, 2, 0), (1, 3, 1), (1, 4, 1), (1, 5, 0),
    (2, 1, 1), (2, 2, 1), (2, 3, 1), (2, 4, 1), (2, 5, 1),
    (3, 1, 0), (3, 2, 1), (3, 3, 1), (3, 4, 1), (3, 5, 0),
    (4, 1, 1), (4, 2, 0), (4, 3, 1), (4, 4, 1), (4, 5, 0),
    (5, 1, 1), (5, 2, 0), (5, 3, 1), (5, 4, 1), (5, 5, 0),
    (6, 1, 1), (6, 2, 0), (6, 3, 1), (6, 4, 1), (6, 5, 0),
    (7, 1, 1), (7, 2, 0), (7, 3, 1), (7, 4, 1), (7, 5, 0),
    (8, 1, 1), (8, 2, 0), (8, 3, 1), (8, 4, 1), (8, 5, 0),
    (9, 1, 1), (9, 2, 0), (9, 3, 1), (9, 4, 1), (9, 5, 0),
    (10, 1, 1), (10, 2, 0), (10, 3, 1), (10, 4, 1), (10, 5, 0);

 

3. 结构

ttt

4. 实现

polls/models/__init__.py:

from .student import Score, Student, StudentScore

polls/models/student.py

from django.db import models


class Score(models.Model):
    name = models.CharField(max_length=32, null=False, blank=False, unique=True)

    def __str__(self):
        return self.name

    class Meta:
        db_table = 'score'


class Student(models.Model):
    stu_id = models.IntegerField(null=False, blank=False, default='00000000')
    name = models.CharField(max_length=32, null=False, blank=False, unique=True)
    sex = models.BooleanField()  # True: 男, False: 女
    score = models.ManyToManyField(Score, through="StudentScore")

    def __str__(self):
        return self.name

    class Meta:
        db_table = "student"


class StudentScore(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    score = models.ForeignKey(Score, on_delete=models.CASCADE)
    pass_flag = models.BooleanField()  # True: pass, False: fail

    def __str__(self):
        return "%s %s" % (self.student.name, self.score.name)

    class Meta:
        db_table = "student_score"

开始迁移数据：

D:\pycharm\myblog>python manage.py makemigrations
Migrations for 'polls':
  polls\migrations\0001_initial.py
    - Create model Score
    - Create model Student
    - Create model StudentScore
    - Add field score to student

D:\pycharm\myblog>python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, polls, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying polls.0001_initial... OK
  Applying sessions.0001_initial... OK

进入MySQL导入默认数据：python manange.py dbshell，执行上面提到的INSERT INTO 语句

更新polls/views.py:

ttt

 

5. 效果

ttt