MySQL外键关联(一对多)&MySQL连接查询

MySQL外键关联(一对多)

外键说明  
  
什么是外键?

1)表A中有字段(如身份证号)做了主键,表B中也有这个字段(身份证号),这里的身份证号一定来自表A同样字段里的内容,但再B表中身份证号对应id可以重复
2)那么表B的这个字段就叫表A字段的外键,也就是两个表以身份证号这个字段建立了联系

外键作用

1)为了一张表记录的数据不要太过冗余
2)保持数据的一致性、完整性

  • 一致性: 外键的作用就是可以让身份证号保证是来自表A中,也就是保证了数据的一致性
  • 完整性: 如果要删除A表中的某个身份证号,那么首先要删除B表中同样的身份证号,这保证了数据的完整性

创建学生表(student), 和学生每天上课记录表(student_record)

student和student_record表创建语法

#1、student表
create table student(
id int auto_increment,
name char(32) not null,
age int not null,
register_data date not null,
primary key (id)) 
engine=InnoDB  
;

#2、student_record表
create table study_record (
  id int(11) auto_increment,
  day int NOT NULL,
  status char(32) NOT NULL,
  stu_id int(11) NOT NULL,
  primary key (id),
  CONSTRAINT fk_student_key FOREIGN KEY (stu_id) REFERENCES student (id)
)
engine=InnoDB  
;

在student表中创建两条记录

mysql>   insert into student(name,age,register_data) values("zhangsan",100,"2016-06-20");
mysql>   insert into student(name,age,register_data) values("lisi",101,"2016-06-21");

在student_record表中创建与student表的关联记录(day,status,stu_id)

mysql> insert into study_record (day,status,stu_id) values(1,"yes",1);        # student表id=1第一天到了
mysql> insert into study_record (day,status,stu_id) values(1,"yes",2);        # student表id=2第一天到了
mysql> insert into study_record (day,status,stu_id) values(1,"yes",3);        # 会报错,因为student没id=3

如果有student表中有student_record表关联的数据,你是不能删除student表中的记录(报错)

mysql> delete from student where name='lisi';

查看刚刚创建study_record表结构创建记录

mysql> show create table study_record;

django在model中添加一对多字段后migrate报错,手动解决冲突

  • 添加普通字段(django model中添加 max_times 字段)
alter table notify_notifybytagrelation add column max_times int not null;
  • 创建外键关联的表(django model添加了notify_tagnotifygroup表)

notify_tagnotifygroup

create table notify_tagnotifygroup(
id int auto_increment,
name char(255) not null,
notify_interval int not null,
max_times int not null,
primary key (id));
  • 添加外键(django model中已有表的group_notify字段关联了2中的表,一对多)

1)添加字段(这个字段作为本表外键)
alter table notify_notifybytagrelation add column group_notify_id int;
2)创建外键关系(将上面创建的 group_notify_id 外键添加外键关系)        
说明:notify_notifybytagrelation 表中的group_notify_id作为外键关联notify_tagnotifygroup表的主键id
alter table notify_notifybytagrelation add foreign key(group_notify_id) references notify_tagnotifygroup(id);

  • mysql手动创建和删除外键约束

创建student和student_record表,但不直接创建外键关联

#1、student表
create table student(
id int auto_increment,
name char(32) not null,
age int not null,
primary key (id)) 
engine=InnoDB  
;

#2、student_record表
create table study_record (
  id int(11) auto_increment,
  day int NOT NULL,
  status char(32) NOT NULL,
  primary key (id))
engine=InnoDB  
;

手动创建外键关联的字段和外键约束

alter table study_record add column stu_id int;            # 创建stu_id作为外键关联字段
# 说明:创建外键约束study_record 表中的 stu_id 字段 一对多外键关联 student 表的 id 字段
alter table study_record add foreign key(stu_id) references student(id);

查看数据库表创建的sql语句

mysql> show create table study_record;
| study_record | CREATE TABLE `study_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` int(11) NOT NULL,
  `status` char(32) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stu_id` (`stu_id`),
  CONSTRAINT `study_record_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

解除外键约束

alter table study_record drop foreign key study_record_ibfk_1;

show create table study_record;

| study_record | CREATE TABLE `study_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` int(11) NOT NULL,
  `status` char(32) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stu_id` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

删除外键

mysql> alter table study_record drop stu_id;

show create table study_record;

| study_record | CREATE TABLE `study_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` int(11) NOT NULL,
  `status` char(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

手动创建django表关联关系

class NewFlowUserRoleActionConf(models.Model):
    flowconf = models.ForeignKey(FlowConf, verbose_name='流程审批名称')
    approvetype = models.CharField(max_length=32, verbose_name='审批类型')
    sequence = models.IntegerField(verbose_name='审批序号')
    approvetogroupuser = models.BooleanField(default=True, verbose_name='是否允许指派组内执行')
    approvetorole = models.BooleanField(default=False, verbose_name='是否角色组审批')

create table workflow_newflowuserroleactionconf (
  id int(11) auto_increment,
  flowconf_id int NOT NULL,
  approvetype char(64) NOT NULL,
  sequence int NOT NULL,
  approvetogroupuser int NOT NULL,
  approvetorole int NOT NULL,
  primary key (id),
  CONSTRAINT fk_workflow_flowconf_key FOREIGN KEY (flowconf_id) REFERENCES workflow_newflowuserroleactionconf (id)
)
engine=InnoDB  
;

MySQL连接查询:两个表之间外键关联

left join (左连接:两个表的差集)

1、左连接where只影向右表,所以左表(student)中数据全部显示,右表study_record表中不符合where条件的数据不会显示
2、select * from student left join study_record on student.id=study_record.stu_id;
在这里插入图片描述

right join (右连接:两个表的差集)

1、右连接where只影向左表,所以左表(student)中不符合where条件的数据不会显示,右表study_record表内容全部显示
2、select * from student right join study_record on student.id=study_record.stu_id;
在这里插入图片描述

inner join (内连接:两个表的交集)

inner join:理解为“有效连接”,两张表中都有的数据才会显示left join
select * from student inner join study_record on student.id=study_record.stu_id; # 等价于面这条语句
select * from student,study_record where study_record.stu_id = student.id;
在这里插入图片描述

Full join(两个表的并集)

select * from a FULL JOIN b on a.a = b.b; # MySQL不支持这个命令(可以使用下面语句代替,两行是一个语句)
select * from student left join study_record on student.id=study_record.stu_id UNION
select * from student right join study_record on student.id=study_record.stu_id;
在这里插入图片描述

  • 0
    点赞
  • 3
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:像素格子 设计师:CSDN官方博客 返回首页
评论

打赏作者

相思寄清风

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值