mysql数据库（入门）五

关联关系之表设计

• 外键： 用来建立关系的字段称为外键
• 主键： 用来表示数据唯一性的字段称为主键

一对一

• 有AB两张表，A表中的一条数据对应B表中的一条数据同时B表一条对应A表一条，这种关系称为一对一
• 应用场景：商品表和商品详情表，
• 如何建立关系： 在从表中添加外键，外键的值指向主表的主键

练习：请设计表保存以下数据

• 用户名：wukong 密码：123456 昵称：齐天大圣 电话：13733666633 地址：花果山
• 用户名：bajie 密码：abcd 昵称：二师兄 电话：13833446622 地址：高老庄
• 用户名：libai 密码：aabbcc 昵称：李白 电话：13355668877 地址：语文书里

  create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
  create table userinfo(userid int,nick varchar(10),tel varchar(15),address varchar(20));
  insert into user values(null,'wukong','123456'),(null,'bajie','bacd'),(null,'libai','aabbcc');
  insert into userinfo values(1,'齐天大圣','13833446622','花果山'),(2,'二师兄','13833446622','高老庄'),(3,'李白','13833446622','语文书里');
  

• 完成以下查询：

• 查询李白的用户名和密码是什么

  select u.username,u.password
  from user u join userinfo ui
  on u.id=ui.userid
  where ui.nick='李白';
  

• 查询每一个用户的所有信息

  select *
  from user u join userinfo ui
  on u.id=ui.userid;
  

• 查询用户名bajie 的昵称是什么

  select ui.nick
  from user u join userinfo ui
  on u.id=ui.userid
  where u.username='bajie';
  

• 一对多

  • AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据，称为一对多
  • 应用场景： 员工-部门， 商品-分类
  • 如何建立关系： 在多的一端添加外键指向另外一张表的主键

  练习：创建表保存以下数据 t_emp t_dept

• 悟空 28岁 3000月薪 神仙部 花果山
• 刘备 34岁 8000月薪 三国部 蜀国
• 路飞 18岁 1000月薪 海贼部 日本
• 八戒 30岁 4000月薪 神仙部 花果山

    create table t_emp(empno int primary key auto_increment,ename varchar(10),age int,sal int,deptno int);
      create table t_dept(deptno int primary key auto<em>increment,dname varchar(10),loc varchar(10));
      insert into t_dept values(null,'神仙部','花果山'),(null,'三国部','蜀国'),(null,'海贼部','日本');
      insert into t_emp values(null,'悟空',28,3000,1),(null,'刘备',34,8000,2),(null,'路飞',18,1000,3),(null,'八戒',30,4000,1);
  

• 做题：
• 查询每个员工的姓名和部门名

  select e.ename,d.dname
  from t_emp e join t_dept d
  on e.deptno=d.deptno;
  

• 查询工作在花果山的员工姓名及工资

  select e.ename,e.sal
  from t_emp e join t_dept d
  on e.deptno=d.deptno
  where d.loc='花果山';
  

• 多对多

  • AB两张表，A表中一条数据对应B表中多条数据同时B表中一条数据对应A表中多条，称为多对多
  • 应用场景： 老师-学生 用户-角色
  • 如何建立关系：需要创建新的关系表，表中添加两个外键，指向两个主表的主键

  练习：创建表保存以下数据

• 唐僧的学生有：悟空，大哥
• 苍老师的学生有： 大哥，大姐

  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 teacher values(null,'唐僧'),(null,'老师');
  insert into student values(null,'悟空'),(null,'大哥'),(null,'大姐');
  insert into t_s values(1,1),(1,2),(2,2),(2,3);
  

• 查询苍老师的学生姓名

  select s.name
  from teacher t join t_s ts
  on t.id=ts.tid
  join student s
  on ts.sid=s.id
  where t.name='苍老师';
  

• 查询大哥的老师姓名

  select t.name
  from teacher t join t_s ts
  on t.id=ts.tid
  join student s
  on ts.sid=s.id
  where s.name='大哥'
  

• 表设计之权限管理案例

  • 创建三张主表user(id,name) role(id,name) module(id,name) 和两张关系表 u_r(uid,rid)（用户和角色） r_m(rid,mid)（角色和权限）

    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,'貂蝉');
  角色表：男游客，男管理员，女游客，女会员
  insert into role values(null,'男游客'),(null,'男管理员'),(null,'女游客'),(null,'女会员');
  权限表：男浏览，男发帖，男删帖，女浏览，女发帖
  insert into module values(null,'男浏览'),(null,'男发帖'),(null,'男删帖'),(null,'女浏览'),(null,'女发帖');
  关系：男游客->男浏览；男管理员->男浏览，男发帖，男删帖；女游客-》女浏览；女会员-》女浏览，女发帖
  刘德华-》男管理员和女游客
  貂蝉-》女会员和男游客
  insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(4,4),(4,5);
  insert into u_r values(1,2),(1,3),(2,4),(2,1);
  

• 练习：
• 查询每个用户对应的所有权限

  select u.name,m.name
  from user u join u_r ur
  on u.id=ur.uid
  join r_m rm 
  on ur.rid=rm.rid
  join module m
  on rm.mid=m.id;
  

• 查询刘德华的所有权限

  select m.name
  from user u join u_r ur
  on u.id=ur.uid
  join r_m rm 
  on ur.rid=rm.rid
  join module m
  on rm.mid=m.id 
  where u.name='刘德华';
  

• 查询拥有男浏览权限的用户都是谁

  select u.name
  from user u join u_r ur
  on u.id=ur.uid
  join r_m rm 
  on ur.rid=rm.rid
  join module m
  on rm.mid=m.id 
  where m.name='男浏览';