数据库总结(四):表设计之关联关系

数据库 专栏收录该内容
9 篇文章 0 订阅

数据库总结(一):基本SQL

数据库总结(二):基本查询

数据库总结(三):分组,联结

数据库总结(四):表设计之关联关系

数据库总结(五):视图,约束,索引

表设计之关联关系

一对一

  • 什么是一对一:有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’);
  1. 查询每个用户的用户名,昵称和qq
    select u.username,ui.nick,ui.qq
    from user u join userinfo ui
    on u.id=ui.user_id;
  2. 查询小白白的用户名和密码
    select u.username,u.password
    from user u join userinfo ui
    on u.id=ui.user_id where ui.nick=‘小白白’;
  3. 查询貂蝉的所有信息
    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);

  1. 查询每个部门对应的员工姓名
    select d.name,e.name
    from emp e join dept d
    on e.dept_id=d.id;
  2. 查询八戒的部门名称
    select d.name
    from emp e join dept d
    on e.dept_id=d.id where e.name=‘八戒’;
  3. 查询妖怪部有谁
    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);
  1. 查询每个老师姓名和对应的学生姓名
    select t.name,s.name
    from teacher t join t_s ts
    on t.id=ts.tid
    join student s
    on s.id=ts.sid;
  2. 查询苍老师的学生姓名
    select s.name
    from teacher t join t_s ts
    on t.id=ts.tid
    join student s
    on s.id=ts.sid where t.name=‘苍老师’;
  3. 查询小明的老师都有谁
    select t.name
    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);
  1. 查询每个用户名称和拥有的权限名称
    select u.name,m.name
    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 rm.mid=m.id;
  2. 查询苍老师的权限有哪些
    select u.name,m.name
    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 rm.mid=m.id where u.name=‘苍老师’;
  • 1
    点赞
  • 0
    评论
  • 9
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 程序猿惹谁了 设计师:白松林 返回首页

打赏作者

AlexChenlc

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

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

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

打赏作者

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

抵扣说明:

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

余额充值