MySQL表关联关系
- 一对一 (OneToOne)
- 一对多 (OneToMany)
- 多对一 (ManyToOne)
- 多对多 (ManyToMany)
一对一
- MySQL 自动增长
- Oracle : 序列
- SQLServer :
建表方法一:
create table t_user(
id int primary key auto_increment ,
tel varchar(11) unique not null ,
password varchar(32) not null ,
email varchar(50) comment '邮箱',
last_login_time datetime comment '最后登录时间',
create_time datetime comment '注册时间',
status enum('0', '1', '2') comment '0禁用,1正常 2 拉黑'
);
create table t_user_info(
id int primary key auto_increment ,
realname varchar(50) comment '真实姓名',
nickname varchar(50) comment '昵称',
sex enum('f', 'm', 's') default 'm' comment '性别',
photo varchar(100) comment '用户头像',
-- 通过 定义外键、维护两张表的关联关系 ,外键必须指向另一表的主键
-- 1. 外键的类型 (一般和关联的表的主键类型保持一致)
-- 2. 清晰的了解 表与表之间的关系
user_id int unique not null,
constraint t_user_info_fk_user_id foreign key (user_id) references t_user(id)
);
建表方式二(共享主键):
create table t_user(
id int primary key auto_increment ,
tel varchar(11) unique not null ,
password varchar(32) not null ,
email varchar(50) comment '邮箱',
last_login_time datetime comment '最后登录时间',
create_time datetime comment '注册时间',
status enum('0', '1', '2') comment '0禁用,1正常,2 拉黑'
);
create table t_user_info(
id int primary key ,
realname varchar(50) comment '真实姓名',
nickname varchar(50) comment '昵称',
sex enum('f', 'm', 's') default 'm' comment '性别',
photo varchar(100) comment '用户头像',
constraint t_user_info_fk_id foreign key (id) references t_user(id)
);
联合主键
create table t_student(
id int primary key auto_increment ,
name varchar(100),
...
);
create table t_student(
id int auto_increment ,
name varchar(100),
...,
primary key(id)
);
-- 联合主键 : 表中多个字段共同作为一个主键
create table t_student2(
name varchar(100) ,
tel varchar(11),
primary key(name, tel)
);
一对多
create table t_resource(
id int primary key auto_increment ,
resource varchar(100) comment '资源存放路径',
resource_name varchar(100) comment '资源名称',
keywords varchar(200) comment '关键字',
score tinyint comment '资源积分',
resource_desc text comment '资源描述',
ext varchar(20) comment '资源后缀',
size int comment '资源大小', -- 存字节
create_time datetime comment '资源上传时间',
-- 维护关联关系的外键
user_id int ,
foreign key (user_id) references t_user(id)
);
多对多
create table t_role(
id int primary key auto_increment ,
role_name varchar(100) comment '角色名',
role_desc varchar(500) comment '角色描述'
);
-- 中间表 (方式一)
create table t_role_user(
id int primary key auto_increment ,
role_id int ,
user_id int ,
foreign key (role_id) references t_role(id),
foreign key(user_id) references t_user(id)
);
-- 中间表 (联合主键)
create table t_role_user(
role_id int ,
user_id int ,
foreign key (role_id) references t_role(id),
foreign key(user_id) references t_user(id),
primary key(user_id, role_id)
)
关联查询
- 左外连接 left [outer] join
- 右外连接 right [outer] join
- 内连接 [inner] join
-- 查询 手机号为 13123451234 用户对应的所有角色信息
select t.* from t_role t
left join t_role_user ru on t.id = ru.role_id
left join t_user u on u.id = ru.user_id
where u.tel = '13123451234'
-- 根据 管理员角色查询对应用户信息上传的所有资源
select r.* from t_resource r
-- inner join t_user u on r.user_id = u.id
inner join t_role_user t on t.user_id = r.user_id
inner join t_role m on m.id = t.role_id
where m.role_name = '管理员'