MySQL多表联查的实现思路

目录
  • 多表联查场景
    • 一对一
    • 多对一
    • 多对多
    • 内连接查询
    • 外连接
    • 自连接
    • 联合查询
    • 子查询

多表联查场景

一对一

用户与用户信息表:当用户的信息数据过多时,我们可以将其分成两个表分别对应用户基本信息和用户的详情信息。

create table user(
    `id` int auto_increment primary key comment'用户id',
    `name` varchar(10),
    `age` varchar(10),
    `gender` char,
    `tel` varchar(30),
    `school` varchar(20),
    `addr` varchar(20),
    `degree` varchar(10),
    `university` varchar(10)
);

当用户信息过多时,就可以使用外键进行关联。在任意一方加入外键,关联另一方主键,并且设置外键为唯一的UNIQUE如下实现:

create table user(
    `id`  	 int auto_increment primary key,
    `name`   varchar(10),
    `age`    varchar(10),
    `gender` char
);
create table user_info(
    `id`         int auto_increment primary key ,
    `tel`        varchar(30),
    `school`     varchar(20),
    `addr`       varchar(20),
    `degree`     varchar(10),
    `university` varchar(10),
    `user_id` int unique,
    constraint fk_user_info foreign key (user_id) references user(id)
);

多对一

部门与员工:一个员工对应一个部门,一个部门对应多个员工

员工指向多的一方,部门指向一的一方。此时应该在员工表中创建外键,指向部门表中的主键

# 员工表
create table emp
(
  `emp_id`      int auto_increment primary key,
  `emp_name`    varchar(20) not null,
  `emp_gender`  char,
  `emp_tel`     varchar(30),
  `emp_dept_id` int,
  constraint fk_emp_dept foreign key (emp_dept_id) references dept (dept_id)
);
# 部门表
create table dept
(
  `dept_id` int auto_increment primary key,
  `dept_name` varchar(20) not null
);

查询方法:

-- 正常单表查
select * from emp;
-- 全查 笛卡尔积
select * from emp,dept;
-- 联查 消除无效的笛卡尔积
select * from emp,dept where emp_dept_id = dept.dept_id;

多对多

学生与课程:一个学生可以选修多门课程,一门课程可以被多个学生选择

此时我们应该在学生表与课程表之间建立中间表。中间表包含两个外键,分别对应学生表和课程表的主键

首先我们准备好数据,学生表+课程表+中间信息表

# 学生表
CREATE TABLE student(
	id INT auto_increment PRIMARY KEY COMMENT '主键ID',
	name VARCHAR(10) COMMENT '姓名',
	no VARCHAR(10) COMMENT '学号'
)COMMENT '学生表';
INSERT INTO student VALUES(NULL,'小瘪三','2001'),(NULL,'小瘪四','2002'),(NULL,'小瘪五','2003'),(NULL,'小瘪六','2004');
# 课程表
CREATE TABLE course(
	id INT auto_increment PRIMARY KEY COMMENT'主键ID',
	name VARCHAR(10) COMMENT '课程名称'
)COMMENT '课程表';
INSERT INTO course VALUES(NULL,'java'),(NULL,'PHP'),(NULL,'MySQL'),(NULL,'Hadoop');

# 学生课程关系表(中间表)
CREATE TABLE student_course(
	id INT auto_increment COMMENT '主键' PRIMARY KEY,
	student_id INT NOT NULL COMMENT '学生ID',
	course_id INT NOT NULL COMMENT '课程ID',
	CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES course (id),
	CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student (id)
)COMMENT '学生——课程关系中间表';
INSERT INTO student_course VALUES(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

我们切换IDEA打开可以清晰的看到三个表之间的关系,中间信息表中的两个外键:student_id与course_id将学生表和课程表关联了起来

内连接查询

查询A集合与B集合的交集

-- 方式一
select [字段列表] from 表1,表2 where 条件...;
-- 方式二
select [字段列表] from 表1 inner join 表2 on 条件...;

示例:查询所有员工与其所属部门

-- 方式一
select emp_name,dept_name from emp inner join dept d on emp.emp_dept_id = d.dept_id;
-- 方式二
select emp_name,dept_name from emp,dept where emp.emp_dept_id = dept.dept_id;

外连接

右外连接:查询右表所有数据以及两表交集部分数据

select 字段列表 from 表1 right outer join 表2 on 条件...;

左外连接:查询左表所有数据以及两表交集部分数据

select 字段列表 from 表1 left outer join 表2 on 条件...;

示例:

-- 2. 查询员工全部信息及其对应的部门信息 左外连接
select  e.*,dept_name from emp e left join dept d on d.dept_id = e.emp_dept_id;
-- 3. 查询全部部门和其对应的所有员工信息 右外连接
select d.*,e.emp_name from emp e right join dept d on e.emp_dept_id = d.dept_id;

自连接

当前表与自身的连接查询,自连接必须使用别名

格式:

select 字段列表 from 表A 别名 join 表A 别名 on 条件...;

在员工表中,所有的普通员工、管理者都是员工。查询每个员工归属哪个管理者管理就需要用到自连接

select a.name,b.name from emp a join emp b on a.emp_id = b.manager_id;

联合查询

关键字 union [all] 将两条sql语句查询的结果拼接起来

-- 查询年龄大于50的员工  和薪资小于10000的员工
select * from emp where emp.emp_age > 50
union all
select * from emp where emp.emp_salary < 10000;

加上all表示不会去重,不加all表示去重复(即同时满足两条sql语句的只出现一次即可)

多张表的列数必须保持一致,字段类型也需要一致

子查询

子查询是指在SQL语句中嵌套select语句进行嵌套查询

select * from t1 where column1 =(select column1 from t2);

子查询的外部语句可以是insert、uodate、delete、select的任何一个

标量子查询示例:查询‘ 开发部 ’的全部员工信息

首先你可以利用正常的两条sql去查询:

-- 查询“开发部”的所有员工
-- 1. 两条语句查询
select dept_id from dept where dept_name = '开发部';
select emp_name from emp where emp_dept_id = 1;

或者你可以使用内连接方式进行联查:

-- 内连接
select e.emp_name,d.dept_name from emp e inner join dept d on e.emp_dept_id = d.dept_id where dept_name = '开发部';

或者使用子查询:

-- 使用子查询
select * from emp where emp_dept_id = (select dept_id from dept where dept_name = '开发部');

到此这篇关于MySQL多表联查的实现思路的文章就介绍到这了,更多相关MySQL多表联查内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL详细讲解多表关联查询

    目录 数据库设计范式 外键 内连接 外连接 结语 数据库设计范式 目前数据库设计有五种范式 , 一般我们数据库只需要满足前三项即可 第一范式 : 确保每列保持原子性 什么是原子性? 意思就是不可再分的,例如下 联系方式有 QQ,微信 , 电话等等 , 显然此列不满足原子性, 如果是单独的QQ或者电话等,则只有一个, 满足第一范式 第二范式 : 要有主键,要求其他字段都依赖于主键 为什么主键这么重要? 我们可以这样理解, 如果把表当作一个队伍, 那么主键就是这个队伍的队旗 • 没有主键就没有唯一性

  • 浅谈mysql中多表不关联查询的实现方法

    大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据

  • MySQL多表联查的实现思路

    目录 多表联查场景 一对一 多对一 多对多 内连接查询 外连接 自连接 联合查询 子查询 多表联查场景 一对一 用户与用户信息表:当用户的信息数据过多时,我们可以将其分成两个表分别对应用户基本信息和用户的详情信息. create table user( `id` int auto_increment primary key comment'用户id', `name` varchar(10), `age` varchar(10), `gender` char, `tel` varchar(30),

  • MySQL单表千万级数据处理的思路分享

    项目背景 在处理过程中,今天上午需要更新A字段,下午爬虫组完成了规格书或图片的爬取又需要更新图片和规格书字段,由于单表千万级深度翻页会导致处理速度越来越慢. select a,b,c from db.tb limit 10000 offset 9000000 但是时间是有限的,是否有更好的方法去解决这种问题呢? 改进思路 是否有可以不需要深度翻页也可以进行数据更新的凭据? 是的,利用自增id列 观察数据特征 此单表有自增id列且为主键,根据索引列查询数据和更新数据是最理想的途径. select

  • MySQL多表查询实例详解【链接查询、子查询等】

    本文实例讲述了MySQL多表查询.分享给大家供大家参考,具体如下: 准备工作:准备两张表,部门表(department).员工表(employee) create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'ma

  • MySql分表、分库、分片和分区知识点介绍

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

  • MySql分表、分库、分片和分区知识深入详解

    一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

  • MySQL多表查询机制

    目录 1.前言 2.等值连接和非等值连接 2.1等值连接 2.2非等值连接 3.自连接和非自连接 3.1自连接 3.2非自连接 4.内连接和外连接 4.1测试数据 4.2左外连接 4.3右外连接 4.4全外连接 附录:测试数据SQL脚本 1. 前言 在SQL开发当中,多表联查是绝对绕不开的一种技能.同样的查询结果不同的写法其运行效率也是千差万别. 在实际开发当中,我见过(好像还写过~)不少又长又臭的查询SQL,数据量一上来查个十几分钟那是家常便饭. 因此,深入理解SQL的多表查询机制,少写一些慢

  • MySQL派生表联表查询实战过程

    目录 前情提要: 查询过程: 总结: 前情提要: 公司运营的一个商城系统,忽然发现订单提现功能有问题,有大量的商户体现金额和订单金额不一致.于是产生了需求,需要把提现表和供应商表作为一个结果集,连接上订单表中的订单金额,通过计算订单表的金额和体现表商户提现的金额进行比对,查看商户是多提现了还是少提现了. 下面记录我的查询过程. 查询过程: 刚开始,第一步我以提现表为主表,查询出来相关结果.MySQL语句如下 SELECT count(ysw.supply_id) AS '提现次数',ysw.us

  • MyBatis-Plus多表联查的方法(动态查询和静态查询)

    目录 建库建表 依赖 配置 代码 测试 1.静态查询 2.动态查询 1.不传条件 2.传条件 建库建表 DROP DATABASE IF EXISTS mp; CREATE DATABASE mp DEFAULT CHARACTER SET utf8; USE mp; DROP TABLE IF EXISTS `t_user`; DROP TABLE IF EXISTS `t_blog`; SET NAMES utf8mb4; CREATE TABLE `t_user` ( `id` BIGIN

  • MyBatis-Plus多表联查的实现方法(动态查询和静态查询)

    目录 建库建表 依赖 配置 代码 测试 1.静态查询 2.动态查询 1.不传条件 2.传条件 建库建表 DROP DATABASE IF EXISTS mp; CREATE DATABASE mp DEFAULT CHARACTER SET utf8; USE mp; DROP TABLE IF EXISTS `t_user`; DROP TABLE IF EXISTS `t_blog`; SET NAMES utf8mb4; CREATE TABLE `t_user` ( `id` BIGIN

  • MySQL数据库表被锁、解锁以及删除事务详解

    目录 背景 故障追踪 解决方案 第一步:查看表使用 第二步:查看进程 第三步:查看当前运行的所有事务 第四步:查看当前出现的锁 第五步:查询锁等待的对应关系 第六步:kill掉事务 MySQL的锁 MySQL锁表场景 Waiting for table metadata lock 场景一:长事务运行,阻塞DDL,继而阻塞所有同表的后续操作. 场景二:为提交事务,阻塞DDL,继而阻塞所有同表的后续操作. 场景三:显式事务失败操作获得锁,未释放 小结 总结 背景 在程序员的职业生涯中,总会遇到数据库

随机推荐