MySQL-----多表查询(一)

这篇具有很好参考价值的文章主要介绍了MySQL-----多表查询(一)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

一.多表关系:

1.1 一对多(多对一):

1.2 多对多:

1.3 一对一:

二.多表查询概述:

三.连接查询:

3.1内连接:

3.2外连接:

3.3自连接查询:

3.4联合查询:


一.多表关系:

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:

  • 一对多(多对一)
  • 多对多
  • 一对一

1.1 一对多(多对一):

  • 案例: 部门 与 员工的关系
  • 关系: 一个部门对应多个员工,一个员工对应一个部门
  • 实现: 在多的一方建立外键,指向一的一方的主键

大致关系图(展示部分):

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

对应SQL脚本:

create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办');
 
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
 
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20,
'项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开
发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程
序员鼓励师',6600, '2004-10-12', 2,1);
-- 添加外键约束
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);

1.2 多对多:

  • 案例: 学生 与 课程的关系
  • 关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
  • 实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

大致关系图:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

对应SQL脚本:

-- 建立学生表
reate table student(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';

insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊',
'2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');
-- 建立课程表
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,
studentid int not null comment '学生ID',
courseid int not null comment '课程ID',
constraint fk_courseid foreign key (courseid) references course (id), -- 加入外键约束
constraint fk_studentid foreign key (studentid) 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)

1.3 一对一:

  • 案例: 用户 与 用户详情的关系
  • 关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另 一张表中,以提升操作效率
  • 实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一(UNIQUE)

大致关系图:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

对应SQL脚本:

-- 创建用户表
create table tb_user(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '1: 男 , 2: 女',
phone char(11) comment '手机号'
) comment '用户基本信息表';
-- 创建用户受教育信息表
create table tb_user_edu(
id int auto_increment primary key comment '主键ID',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaryschool varchar(50) comment '小学',
middleschool varchar(50) comment '中学',
university varchar(50) comment '大学',
userid int unique comment '用户ID',
constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';
-- 向表中插入数据
insert into tb_user(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

二.多表查询概述:

我们之前在讲解SQL语句的时候,讲解了DQL语句,也就是数据查询语句,但是之前讲解的查询都是单表查询,而本章节我们要学习的则是多表查询操作,多表查询就是指从多张表中查询数据。其中,多表查询又分为连接查询与子查询

在查询之前,我们先导入数据(建立表以及表之间的关系):

-- 创建部门表
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');
-- 创建员工表
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
-- 设置外键约束
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
-- 插入数据
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

原来查询单表数据,执行的SQL形式为:select * from emp;

在多表查询中,我们就只需要使用逗号分隔多张表即可,如: select * from emp , dept

查询结果(结果很长,展示部分):

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

为什么会出现这种结果呢?这里要引入笛卡尔积的概念了:

🦉🦉🦉笛卡尔积: 笛卡尔乘积是指在数学中,两个集合A集合 和 B集合的所有组合情况

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

但是在实际的查询当中,我们是要消除笛卡尔积的,具体怎么消除呢?和单表查询类似,加上条件就行了,例如(查询所有员工及其部门对应的信息):

select * from emp , dept where emp.dept_id = dept.id;

全部查询结果: 

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

三.连接查询:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

  • 内连接:相当于查询A、B交集部分数据
  • 外连接:
  • 左外连接:查询左表所有数据,以及两张表交集部分数据
  • 右外连接:查询右表所有数据,以及两张表交集部分数据
  • 自连接:当前表与自身的连接查询,自连接必须使用表别名!!!

3.1内连接:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

内连接的语法分为两种: 隐式内连接、显式内连接,接下来我们看一下语法规则:

-- 隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

-- 显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

案例:

#内连接
-- A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 全部书写,如果表名字过长可能导致书写错误
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
-- 简写,方便书写,推荐
select e.name,d.name from emp e,dept d where e.dept_id = d.id;

-- B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
-- 简写
select e.name,d.name from emp e join dept d on e.dept_id = d.id;

 查询结果(两个查询结果一致,这里展示其中一个):

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

3.2外连接:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

外连接分为 左外连接,右外连接

  • 左外连接语法:
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据

  •  右外连接语法:
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据,当也然包含表1和表2交集部分的数据

 案例演示:

# 外连接
-- A. 查询emp表的所有数据, 和对应的部门信息(左外连接)

select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*,d.name from emp e left join dept d on e.dept_id = d.id;

-- B. 查询dept表的所有数据, 和对应的员工信息(右外连接)

select d.*,e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*,e.* from dept d left outer join emp e on e.dept_id = d.id;

 查询结果:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

3.3自连接查询:

 自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

案例:

-- 1.自连接查询
-- A查询员工 及其 所属领导的名字

select a.name,b.name from emp a,emp b where a.managerid = b.id;

-- B 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;

 查询结果:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

3.4联合查询:

对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集,语法如下:

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

注意:

  • 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
  • union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重

案例:

-- 2.联合查询
-- A 将薪资低于 5000 的员工, 和 年龄大于 50 岁的员工全部查询出来.
select * from emp where salary < 5000
union all
select * from emp where age > 50;
-- 由结果可以看出,联合查询只是两表的简单拼接,还有重复部分
-- union all查询出来的结果,仅仅进行简单的合并,并未去重

-- B对数据进行去重
select * from emp where salary < 5000
union 
select * from emp where age > 50;

查询结果:

MySQL-----多表查询(一),MySQL数据库,mysql,数据库

 

 结语: 写博客不仅仅是为了分享学习经历,同时这也有利于我巩固知识点,总结该知识点,由于作者水平有限,对文章有任何问题的还请指出,接受大家的批评,让我改进。同时也希望读者们不吝啬你们的点赞+收藏+关注,你们的鼓励是我创作的最大动力!

MySQL-----多表查询(一),MySQL数据库,mysql,数据库文章来源地址https://www.toymoban.com/news/detail-859935.html

到了这里,关于MySQL-----多表查询(一)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【JaveWeb教程】(22) MySQL数据库开发之多表查询:内连接、外连接、子查询 详细代码示例讲解(最全面)

    1.1.1 数据准备 SQL脚本: 1.1.2 介绍 多表查询:查询时从多张表中获取所需数据 单表查询的SQL语句:select 字段列表 from 表名; 那么要执行多表查询,只需要使用逗号分隔多张表即可,如: select 字段列表 from 表1, 表2; 查询用户表和部门表中的数据: 此时,我们看到查询结果中包

    2024年01月22日
    浏览(55)
  • MySQL数据库——多表操作

    在日常的数据库使用过程中,我们处理的数据不只是单张表,而是需要我们同时处理多个表的数据,那么今天我将为大家分享关于MySQL的多表操作。 在学习多表操作之前,我们需要知道有哪些多表关系:一对一关系、一对多关系/多对一关系、多对多关系。 一对一关系是指一

    2024年02月14日
    浏览(46)
  • MySQL数据库——MySQL WHERE:条件查询数据

    在 MySQL 中,如果需要有条件的从数据表中查询数据,可以使用 WHERE 来指定查询条件。 使用 WHERE 的语法格式如下: WHERE 查询条件 查询条件可以是: 带比较运算符和逻辑运算符的查询条件 带 BETWEEN AND 的查询条件 带 IS NULL 的查询条件 带 IN

    2024年02月03日
    浏览(58)
  • MySQL数据库实验三 MySQL查询

    一、实验项目: MySQL查询。 二、实验目的 掌握MySQL的查询操作。 三、实验内容 (一): 1、查询lineitem表中商品编号(productid)和单价(unitprice),要求消除重复行。 2、计算lineitem表中每条记录的商品金额。 3、显示orders表单笔高于200元的客户号(userid)、成交金额(total

    2024年02月11日
    浏览(53)
  • 数据库多表查询

    1、创建表格 2、插入数据 3.查询student表的所有记录 4.查询student表的第2条到4条记录 5.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息 6.从student表中查询计算机系和英语系的学生的信息 7.从student表中查询年龄18~22岁的学生信息 由于表中没有年龄1

    2024年01月16日
    浏览(50)
  • MySQL数据库基础之MySQL查询基础

    从今天开始,本系列内容就带各位小伙伴学习数据库技术 。数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深, 全面讲解数据库体系。 非常适合零基础的小伙伴来学习。 全文大约【 1212】 字,不说废话,只讲可以让你学到技术、

    2024年02月06日
    浏览(52)
  • MySQL数据库——MySQL SELECT:数据表查询语句

    在 MySQL 中,可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求,使用不同的查询方式来获取不同的数据,是使用频率最高、最重要的操作。 SELECT 的语法格式如下: 其中,各条子句的含义如下: {*|字段列名} 包含星号通配符的字段列表,表示所要查询字段的

    2024年02月05日
    浏览(70)
  • 数据库多表查询作业

    创建数据库 插入数据 1.查询student表的所有记录 2.查询student表的第2条到4条记录 3.从student表查询所有学生的学号(id)、姓名 (name)和院系(department)的信息 4.从student表中查询计算机系和英语系的学生的信息 5.从student表中查询年龄18~22岁的学生信息 6.从student表中查询每个院

    2024年02月16日
    浏览(45)
  • 数据库基础——7.多表查询

    这篇文章来讲一下数据库中的多表查询 目录 1. 一个案例引发的多表连接 1.1 案例说明 1.2 笛卡尔积(或交叉连接)的理解 1.3 案例分析与问题解决 2. 多表查询分类讲解 2.1 等值连接 vs 非等值连接 2.1.1等值连接 2.1.2 非等值连接 2.2 自连接 vs 非自连接  2.3 内连接 vs 外连接  3.实

    2024年02月06日
    浏览(46)
  • MySQL数据库,联合查询

    目录 1. 联合查询 1.1 内查询 1.2 外查询 1.3 自连接 1.4 子查询 1.5 合并查询 联合查询,简单的来讲就是多个表联合起来进行查询。这样的查询在我们实际的开发中会用到很多,因此会用 笛卡尔积 的概念。 啥是笛卡尔积?两张表经过笛卡尔积后得到的新表的列数是两表列数之和

    2023年04月23日
    浏览(72)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包