mysql-day05

表设计之关联关系

一对一:

1.Definition:在A表中有一条数据和B表中的一条数据有关联关系,这种关系称为一对一 

2.应用场景:用户表和用户信息扩展表,商品表和商品信息扩展表

3.如何建立关系:

User和UserInfo两张表中User是主表,此时 需要在从表中添加一个字段记录主表的主键值,此字段称之为外键;指向另外一张表.

id,username,密码,

年龄,qq,email,电话,地址,性别

-练习:

创建User(id,username,密码,)和UserInfo(id,nick,qq,phone)表,然后分别往两张表中插入有关联关系的3条数据

一对多

1.Definition:AB两张表中A表中的一条数据可以对应B表中的一条或多条数据时,两张表的关系成为一对多

2.应用场景:商品和分类,员工和部门等

3,如何建立关系:在一对多,多的一端的表中需要有一个字段记录另外一张表中数据的主键

多对多

1.Definition:AB两张表中A表中一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的多条数据,称为多对多

2.应用场景:学生和老师,用户和角色

3,如何建立关系:多对多必须创建一张关系表,在关系表中保存两张表的主键

primary key auto_increment

------------------------不需要自己来定id,可以自赠

primary key auto_increment

primary key ,auto_increment

create table teacher(id int,name varchar(10)) engine=myisam charset=utf8;

create table student(id int,name varchar(10)) engine=myisam charset=utf8;

create table t_s(tid int,sid int) engine=myisam charset=utf8;

insert into teacher values(1,'唐僧');

insert into teacher values(2,'刘备');

insert into teacher values(3,'苍老师');

insert into student values(1,'悟空');

insert into student values(2,'关羽');

insert into student values(3,'小猪威威');

insert into  t_s values(1,1),(1,2),(2,2),(2,3),(3,3),(3,1);

select s.*,t.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where t.id=3;    

select s.name,t.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where s.id=3; 

使用关联查询

select s.name,t.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid; 

使用子查询

自关联---一张表拆成两张表

-什么是子关联:在一张表中有某一个字段指向当前表的主键,这种称为自关联.

-什么时候使用自关联:当数据和数据之间存在层级关系,而且不确定有多少层的时候使用自关联,如:员工表的上级领导,分类的上级分类,部门的直属部门

连接方式和关联关系的区别

连接方式:包括内连接和外连接(左外和右外),是指通过sql语句查询两张表数据时使用的查询方式.

关联关系:一对一,一对多,多对多,是指两张表之间存在的逻辑关系

案例:权限管理的表设计--------五表联查!

create table user(id int primary key auto_increment,name varchar(10))charset=utf8;

create table role(id int primary key auto_increment,name varchar(10))charset=utf8;

create table module(id int primary key auto_increment,name varchar(10))charset=utf8;

create table u_r(uid int,rid int)charset=utf8;

create table r_m(rid int,mid int)charset=utf8;

insert into user values(null,'刘德华');

insert into user values(null,'王妃');

insert into role values(null,'男游客');

insert into role values(null,'女vip');

insert into module values(null,'男浏览');

insert into module values(null,'男发帖');

insert into module values(null,'女浏览');

insert into module values(null,'女发帖');

insert into module values(null,'女删帖');

insert into u_r values(1,1),(2,1),(2,2);

insert into r_m values(1,1),(2,1),(2,4);

show create table user;

select * from user;

****************************************************************************************************************************************************************************************************************************************************************************************************************************

create database db9 character set utf8;

防止出现后期需要给每个表格确定字符集,首先就给数据库给定好 

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,'女vip');




insert into module values(null,'男发帖'),(null,'男浏览'),(null,'女浏览'),(null,'女发帖'),(null,'女删帖');




insert into u_r values(1,1),(2,1),(2,2);




insert into r_m values(1,1),(2,1),(2,4);

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

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