数据库总结（四）：表设计之关联关系

最新推荐文章于 2025-05-15 03:45:00 发布

原创最新推荐文章于 2025-05-15 03:45:00 发布 · 4.8k 阅读

CC 4.0 BY-SA版权

文章标签：

9 篇文章

订阅专栏

本文详细介绍了数据库设计中的关联关系，包括一对一、一对多和多对多三种关系，并通过实例解析了如何建立这些关系。此外，还给出了权限管理的表设计案例，展示了如何创建用户、角色和权限之间的复杂关联。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询每个用户的用户名，昵称和qq
select u.username,ui.nick,ui.qq
from user u join userinfo ui
on u.id=ui.user_id;
查询小白白的用户名和密码
select u.username,u.password
from user u join userinfo ui
on u.id=ui.user_id where ui.nick=‘小白白’;
查询貂蝉的所有信息
select *
from user u join userinfo ui
on u.id=ui.user_id where u.username=‘diaochan’;

什么是一对多：有AB两张表：A表中的一条数据对应B表的多条数据，同时B表的一条数据对应A表的一条
应用场景：商品表和分类表员工表和部门表
如何建立关系：在两张表中多的表中添加外键指向另外一张表的主键
练习：创建数据库db5并使用，创建员工表和部门表并保存以下数据
emp:id name dept_id dept:id name
创建表：
create database db5;
use db5;
create table emp(id int primary key auto_increment,name varchar(10),dept_id int);
create table dept(id int primary key auto_increment,name varchar(10));

神仙部的员工悟空和八戒
妖怪不的员工蜘蛛精和白骨精
插入数据：
insert into dept values(null,‘神仙’),(null,‘妖怪’);
insert into emp values(null,‘悟空’,1),(null,‘八戒’,1),(null,‘蜘蛛精’,2),(null,‘白骨精’,2);

查询每个部门对应的员工姓名
select d.name,e.name
from emp e join dept d
on e.dept_id=d.id;
查询八戒的部门名称
select d.name
from emp e join dept d
on e.dept_id=d.id where e.name=‘八戒’;
查询妖怪部有谁
select e.name
from emp e join dept d
on e.dept_id=d.id where d.name=‘妖怪’;

什么是多对多：AB两张表，A表中一条数据对应B表多条，同时B表中一条数据对应A表多条称为多对多
应用场景：老师表和学生表
如何建立关系：创建一张关系表，在关系表中添加两个外键指向另外两张表的主键
练习：创建老师和学生表保存以下信息
创建表：teacher：id name student：id name t_s:tid,sid
create table teacher(id int primary key auto_increment,name varchar(10));
create table student(id int primary key auto_increment,name varchar(10));
create table t_s(tid int,sid int);
保存以下数据：
苍老师：小明小红小绿小黄
王老师：小明小红
insert into student values(null,‘小明’),(null,‘小红’),(null,‘小绿’),(null,‘小黄’);
insert into teacher values(null,‘苍老师’),(null,‘王老师’);
insert into t_s values(1,1),(1,2),(1,3),(1,4),(2,1),(2,2);

三张主表：用户表，角色表，权限表
关系表：用户角色关系表角色权限关系表
创建表：
create table user(id int primary key auto_increment, name varchar(10));
create table role(id int primary key auto_increment, name varchar(10));
create table module(id int primary key auto_increment, name varchar(10));
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
保存以下数据：用户(苍老师，小明，克晶老师) 角色（男游客，男管理员，女会员，女管理员）权限（男浏览，男发帖，男删帖，女浏览，女发帖，女删帖）
insert into user values(null,‘苍老师’),(null,‘小明’),(null,‘克晶老师’);
insert into role values(null,‘男游客’),(null,‘男管理员’),(null,‘女会员’),(null,‘女管理员’);
insert into module values(null,‘男浏览’),(null,‘男发帖’),(null,‘男删帖’),(null,‘女浏览’),(null,‘女发帖’),(null,‘女删帖’);
用户和角色关系：苍老师（男管理员，女管理员）小明（女会员）克晶老师（女管理员，男游客）
insert into u_r values(1,2),(1,4),(2,3),(3,1),(3,4);
角色和权限关系：男游客（男浏览）男管理员（男浏览，男发帖，男删帖）女会员（女浏览，女发帖），女管理员（女浏览，女发帖，女删帖）
insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(3,5),(4,4),(4,5),(4,6);