MySQL-多表操作

11- 多表操作

11.1 多表关系设计

11.1.1 一对一

A表和B表：A表中一条记录对应B表中一条记录。如：身份证 - 护照

一对一的建表原则	说明
外键唯一	将从表的外键添加唯一约束，变成了一对一的关系。其实是一个特殊的一对多的关系。
外键是主键	外键唯一 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

架构图

​

案例语句

-- 创建数据库
CREATE DATABASE test2;

# 使用数据库
use db_mysql;

# 一对一的关系

-- 主表
CREATE TABLE stu1(
	id INT PRIMARY KEY, -- 主键
	NAME VARCHAR(20)
);

-- 从表
CREATE TABLE info(
	id INT PRIMARY key, -- 主键
	address VARCHAR(20),
	use_name VARCHAR(10),
	weight double,
	-- 创建外键,外键又是主键
	FOREIGN KEY(id) REFERENCES stu1(id)
);

11.1.2 一对多关系

表与表之间的关系	记录之间的对应关系
一对多	A表一方，B表多方： A表中一条记录对应了B表中多条记录。如：部门 - 员工 B表中一条记录对应了A表中一条记录
一对多建表原则	在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键。 在多方建立外键，指向一方的主键。

实现一个"线路分类"中有多个"旅游线路"的一对多的关系。(category 分类, route 线路)

架构图

​

案例语句

# 一对多,一个分类对应多条线路

/* 
  创建旅游线路分类表category
*/
create table category(
	cid int primary key auto_increment, -- cid旅游线路分类主键，自动增长
	cname varchar(100) not null unique -- cname旅游线路分类名称非空，唯一，字符串100
);

drop table category;
drop table route;

/*
 创建旅游线路表route
*/
create table route(
	rid int primary key auto_increment, -- rid旅游线路主键，自动增长
	rname varchar(100) not null unique, -- 旅游路线名称
	price double, -- 路线价格
	rdate date, -- 日期时间
	cid int, -- 添加外键约束
	constraint fk_cid foreign key(cid)references category(cid)
);

-- 删除外键约束
alter table route drop foreign key fk_cid;

11.1.3 多对多关系

表与表之间的关系	记录之间的对应关系
多对多	A表中一条记录对应了B表中多条记录 B表中一条记录对应了A表中多条记录

多对多关系建表原则

• 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

图示

​

sql语句

一个用户收藏多个线路，一个线路被多个用户收藏，建表体现线路与用户之间的关系。用户与线路之间是多对多的关系。

/* 
  创建旅游线路分类表category
*/
create table category(
  cid int primary key auto_increment, -- cid旅游线路分类主键，自动增长
  cname varchar(100) not null unique -- cname旅游线路分类名称非空，唯一，字符串100
);

/*
 创建旅游线路表route
*/
create table route(
  rid int primary key auto_increment, -- rid旅游线路主键，自动增长
  rname varchar(100) not null unique, -- 旅游路线名称
  price double, -- 路线价格
  rdate date, -- 日期时间
  cid int, -- 添加外键约束
  constraint fk_cid foreign key(cid)references category(cid)
);

/*
	用户表user
*/
create table USER(
     uid int primary key auto_increment, -- uid用户主键,自增长
     username varchar(100) unique not null,	-- 用户名长度100，唯一，非空
     password varchar(30) not null,	-- 密码长度30,为空....
     name varchar(100),	-- 姓名长度
     birthday date, -- 生日
     sex CHAR(1),	-- sex性别,定长字符串1
     telephone varchar(11), -- 手机号,字符串11
     email varchar(100) -- email邮箱,字符串长度100
);

/*
	收藏表favorite
*/
create table favorite(
     date datetime, -- 收藏时间
     rid int, -- 路线id的外键
     constraint fk_rid foreign key(rid)references route(rid), -- 关联了线路的主键
     uid int, -- 用户id的外键
     constraint fk_uid foreign key(uid)references USER(uid)	-- 关联了用户的主键
);

架构图

​

11.2 内连接

内连接的特点:

• 通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示。
• 比如通过: 从表的外键 = 主表的主键 方式去匹配。

11.2.1 隐式内联接

• from子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接.
• 使用where条件过滤无用的数据

语法：

select 列名 from 左表,右表 where 从表.外键=主表.主键

11.2.2 显示内联接

使用 inner join …on 这种方式, 就是显式内连接。

语法：

-- 显示内连接, on后面就是表连接的条件
select 列名 from 左表 inner