表设计之关联关系
⼀对⼀
什么是⼀对⼀：有A和B两张表，A表中的⼀条数据对应B表中的⼀条数据，同时B表的⼀条数据也对应A表的⼀条，称为⼀对⼀的关系。 应⽤场景： ⽤户表和⽤户信息扩展表 ，商品表和商品详情表 如何建⽴⼀对⼀的关系：在从表中添加外键指向主表的主键建⽴关系
练习：创建⽤户表和⽤户详情表并保存以下信息
user:id,username,password userinfo:user_id,nick,qq
创建表
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(user_id int,nick varchar(10),qq varchar(15));
⽤户名 密码 昵称 qq
libai admin ⼩⽩⽩ 112233
liubei 123456 刘皇叔 667788
diaochan 112233 媳妇⼉ 998877
-插⼊数据：
insert into user (username,password) values(‘libai’,‘admin’),(‘liubei’,‘123456’),(‘diaochan’,‘112233’);
insert into userinfo values(1,‘⼩⽩⽩’,‘112233’),(2,‘刘皇叔’,‘667788’),(3,‘媳妇⼉’,‘998877’);
24. 查询每个⽤户的⽤户名，昵称和qq
select u.username,ui.nick,ui.qq
from user u join userinfo ui
on u.id=ui.user_id;
25. 查询⼩⽩⽩的⽤户名和密码
select u.username,u.password
from user u join userinfo ui
on u.id=ui.user_id where ui.nick=‘⼩⽩⽩’;
26. 查询貂蝉的所有信息
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);
27. 查询每个部门对应的员⼯姓名
select ,
from emp e join dept d
on e.dept_id=;
28. 查询⼋戒的部门名称
select
from emp e join dept d
on e.dept_id= where e.name=‘⼋戒’;
29. 查询妖怪部有谁
select
from emp e join dept d
on e.dept_id= where d.name=‘妖怪’;tod分动器
多对多
什么是多对多：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);自动打蜡机
30. 查询每个⽼师姓名和对应的学⽣姓名
select ,
from teacher t join t_s ts
on t.id=ts.tid
join student s
on s.id=ts.sid;
31. 查询苍⽼师的学⽣姓名
select
from teacher t join t_s ts
on t.id=ts.tid
join student s
on s.id=ts.sid where t.name=‘苍⽼师’;
32. 查询⼩明的⽼师都有谁
select
from teacher t join t_s ts
on t.id=ts.tid
join student s
on s.id=ts.sid where s.name=‘⼩明’;
表设计案例：权限管理
三张主表： ⽤户表，⾓⾊表，权限表
关系表： ⽤户⾓⾊关系表 ⾓⾊权限关系表
创建表：
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);
33. 查询每个⽤户名称和拥有的权限名称
select ,
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on rm.rid=ur.rid
join module m
on ;
34. 查询苍⽼师的权限有哪些
select ,
from user u join u_r ur
on u.id=ur.uid
join r_m rm
on rm.rid=ur.rid
join module m
on where u.name=‘苍⽼师’;
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议