關系

多對多的關系，如何通過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)

)

為什么要分表：

數據分擔在多個表，提高了查詢的效率

總結

以上是生活随笔為你收集整理的mysql 关系_MySQL之关系的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。