当前位置：首页 > news >正文

MySQL之关系

news 来源：原创 2024/9/29 6:05:13

关系
- 多对多的关系，如何通过mysql来表示
- 一对一关系

关系

多对多的关系，如何通过mysql来表示

站在老师的角度

一个老师可以教多个学生，

一个老师也可以教一个学生。

站在学生的角度

一个学生可以被一个老师教

一个学生也可以被多个老师教

结论：如果站在两边看都是一对多的情况，那么这个关系就是多对多的。

问题：

如果表示a老师教过x学生和y学生，x学生和y学生同时也被b老师教

解决方法

多对多关系，无论是把外键放在哪一张表都不合适，因为可能有多个值

解决方案：建立一个中间的关系表

create table student(
    id int primary key auto_increment,
    name char(10)
) charset utf8;

create table teacher(
    id int primary key auto_increment,
    name char(10)
) charset utf8;

create table t_s_r(
    id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id)
) charset utf8 ;

insert into teacher values(null,"bgon"),(null,"nike");


insert into student values(null,"老王"),(null,"老李");

# 老王被bgon教过
insert into t_s_r values(null,1,1);

# nike教过老李
insert into t_s_r values(null,2,2);

# nike教过老王
insert into t_s_r values(null,2,1);


# 现在已知老师名称为bgon,请找出他教过的那些学生
mysql> select id from teacher where name="bgon";
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select s_id from t_s_r where t_id=1;
+------+
| s_id |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select name from student where id=1;
+--------+
| name   |
+--------+
| 老王   |
+--------+
# 子查询
mysql> select name from student where id=(select s_id from t_s_r where t_id=(select id from teacher where name="bgon"));
+--------+
| name   |
+--------+
| 老王   |
+--------+
1 row in set (0.00 sec)




# 已知学生名为老李，请查询出哪些老师教过他.
mysql> select id from student where name = "老李";
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> select t_id from t_s_r where s_id=2;
+------+
| t_id |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select name from teacher where id=2;
+------+
| name |
+------+
| nike |
+------+
1 row in set (0.00 sec)

# 子查询 
mysql> select name from teacher where id=(select t_id from t_s_r where s_id=(select id from student where name = "老李"));
+------+
| name |
+------+
| nike |
+------+
1 row in set (0.00 sec)

总结：

如何确认多对多的关系？

站在两个表的角度去想
处理方式，通过在两个表中间建立一个外键表，该外键表分别都关联两表的字段。

联合唯一约束

对于上面的t_s_r表进行改进

# 原表
create table t_s_r(
    id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
) charset utf8 ;

# 改进方法一(表已创建的情况下)：unique key
alter table t_s_r add unique key(t_id,s_id);

# 改进方法二（表没创建的情况下）:unique key
create table t_s_r(
    id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    unique key(t_id,s_id)
) charset utf8 ;



# 联合主键
create table t_s_r(
    id int primary key auto_increment,
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    primary key(t_id,s_id)
) charset utf8 ;

对于上面的t_s_r表进行改进------最终版

# 创建学生表
create table student(
    id int primary key auto_increment,
    name char(10)
) charset utf8;

# 创建教师表
create table teacher(
    id int primary key auto_increment,
    name char(10)
) charset utf8;

# 创建学生和老师关系表
create table t_s_r(
    t_id int,
    s_id int,
    foreign key(t_id) references teacher(id),
    foreign key(s_id) references student(id),
    primary key(t_id,s_id)
) charset utf8 ;

insert into teacher values(null,"bgon"),(null,"nike");

insert into student values(null,"老王"),(null,"老李");

# 老王被bgon教过
insert into t_s_r values(null,1,1);

# nike教过老李
insert into t_s_r values(null,2,2);

# nike教过老王
insert into t_s_r values(null,2,1);


# 问题：现在已知老师名称为bgon,请找出他教过的那些学生
mysql> select id from teacher where name="bgon";
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select s_id from t_s_r where t_id=1;
+------+
| s_id |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select name from student where id=1;
+--------+
| name   |
+--------+
| 老王   |
+--------+
# 整合查询语句：子查询
mysql> select name from student where id=(select s_id from t_s_r where t_id=(select id from teacher where name="bgon"));
+--------+
| name   |
+--------+
| 老王   |
+--------+
1 row in set (0.00 sec)




# 问题：已知学生名为老李，请查询出哪些老师教过他.
mysql> select id from student where name = "老李";
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> select t_id from t_s_r where s_id=2;
+------+
| t_id |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select name from teacher where id=2;
+------+
| name |
+------+
| nike |
+------+
1 row in set (0.00 sec)

# 整合查询语句：子查询 
mysql> select name from teacher where id=(select t_id from t_s_r where s_id=(select id from student where name = "老李"));
+------+
| name |
+------+
| nike |
+------+
1 row in set (0.00 sec)

一对一关系

例如：每一个人都有一个身份证。一个身份证只对应一个人

分表：

垂直分表，例如：人物的详细信息，就可以垂直分表

# 全表
create table person(
 id int primary key auto_increment,
 name char(10),
    age int,
 height float,
    weigth float
)

# 垂直分表：person
create table person(
    id int primary key auto_increment,
 name char(10),
    age int
) charset utf8;


# 垂直分表：person_info
create table person_info(
 id int primary key,
    height float,
    weigth float,
 foreign key(id) references person(id)
) charset utf8;

水平分表

数据量很大，使用一个表，查询效率低，使用两个表来存取这些数据

处理一对一关系处理方式：

先确定先后顺序
将先存在的数据作为主表
后存在的作为从表
使两个表的id保持一一对应

方法1：从表的id即是主键又是外键

方法2：从表的id设置为外键，并保证唯一

人物关系表，从客户演变为学生：

# 创建客户表
create table kehu_t(
    id int primary key auto_increment,
    name char(10),
    phone char(11)
)

# 创建学生表
create table student_t(
    id int primary key auto_increment,
    card_id char(18)
)