Book
id title price publish
1 php 100 人民出版社
2 python 200 老男孩出版社
3 go 100 人民出版社
4 java 300 人民出版社
为了存储出版社的邮箱,地址,在第一个表后面加字段
Book
id title price publish email addr
1 php 100 人民出版社 111 北京
2 python 200 老男孩出版社 222 上海
3 go 100 人民出版社 111 北京
4 java 300 人民出版社 111 北京
这样会有大量重复的数据,浪费空间
####################################################################################
一对多:一个出版社对应多本书(关联信息建在多的一方,也就是book表中)
Book
id title price publish_id
1 php 100 1
2 python 200 1
3 go 100 2
4 java 300 1
Publish
id name email addr
1 人民出版社 111 北京
2 沙河出版社 222 沙河
总结:一旦确定表关系是一对多:在多对应的表中创建关联字段(在多的表里创建关联字段) ,publish_id
查询python这本书的出版社的邮箱(子查询)
select publish_id from Book where title=“python”
select email from Publish where id=1
####################################################################################
多对多:一本书有多个作者,一个作者出多本书
Book
id title price publish_id
1 php 100 1
2 python 200 1
3 go 100 2
4 java 300 1
Author
id name age addr
1 alex 34 beijing
2 egon 55 nanjing
Book2Author
id book_id author_id
1 2 1
2 2 2
3 3 2
总结:一旦确定表关系是多对多:创建第三张关系表(创建中间表,中间表就三个字段,自己的id,书籍id和作者id) :
id book_id author_id
# lqz出版过的书籍名称(子查询)
select id from Author where name='lqz'
select book_id from Book2Author where author_id=1
select title from Book where id =book_id
####################################################################################
一对一:对作者详细信息的扩展(作者表和作者详情表)
Author
id name age ad_id(UNIQUE)
1 lqz 34 1
2 egon 55 2
AuthorDetail
id addr gender tel gf_name author_id(UNIQUE)
1 beijing male 110 小花 1
2 nanjing male 911 杠娘 2
总结: 一旦确定是一对一的关系:在两张表中的任意一张表中建立关联字段+Unique
====================================
Publish
Book
Author
AuthorDetail
Book2Author
CREATE TABLE publish(
id INT PRIMARY KEY auto_increment ,
name VARCHAR (20)
);
CREATE TABLE book(
id INT PRIMARY KEY auto_increment ,
title VARCHAR (20),
price DECIMAL (8,2),
pub_date DATE ,
publish_id INT ,
FOREIGN KEY (publish_id) REFERENCES publish(id)
);
CREATE TABLE authordetail(
id INT PRIMARY KEY auto_increment ,
tel VARCHAR (20)
);
CREATE TABLE author(
id INT PRIMARY KEY auto_increment ,
name VARCHAR (20),
age INT,
authordetail_id INT UNIQUE ,
FOREIGN KEY (authordetail_id) REFERENCES authordetail(id)
);
CREATE TABLE book2author(
id INT PRIMARY KEY auto_increment ,
book_id INT ,
author_id INT ,
FOREIGN KEY (book_id) REFERENCES book(id),
FOREIGN KEY (author_id) REFERENCES author(id)
)
注意:关联字段与外键约束没有必然的联系(建管理字段是为了进行查询,建约束是为了不出现脏数据)
在Models创建如下模型
class Book(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
price = models.DecimalField(max_digits=5, decimal_places=2)
publish_date = models.DateField()
# 阅读数
# reat_num=models.IntegerField(default=0)
# 评论数
# commit_num=models.IntegerField(default=0)
publish = models.ForeignKey(to='Publish',to_field='nid',on_delete=models.CASCADE)
authors=models.ManyToManyField(to='Author')
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='AuthorDatail',to_field='nid',unique=True,on_delete=models.CASCADE)
class AuthorDatail(models.Model):
nid = models.AutoField(primary_key=True)
telephone = models.BigIntegerField()
birthday = models.DateField()
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()