关联关系之表设计
- 外键: 用来建立关系的字段称为外键
- 主键: 用来表示数据唯一性的字段称为主键
一对一
- 有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='男浏览';