Mysql基础-多表查询

Mysql基础-多表查询

文章目录

  • Mysql基础-多表查询
    • 1 多表关系
      • 1.1 一对多
      • 1.2 多对多
      • 1.3 一对一
    • 2 多表查询概述
      • 2.1 多表查询分类
    • 3 内连接
    • 4 外连接
    • 5 自连接
    • 6 联合查询-union union all
    • 7 子查询
      • 7.1 标量子查询
      • 7.2 列子查询
      • 7.3 行子查询
      • 7.4 表子查询

1 多表关系

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

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

1.1 一对多

  • 案例: 部门 与 员工的关系

  • 关系: 一个部门对应多个员工,一个员工对应一个部门

  • 实现: 在多的一方建立外键,指向一的一方的主键

    在这里插入图片描述

1.2 多对多

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

在这里插入图片描述

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, '黛绮丝', '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)

在这里插入图片描述

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',	--注意这里用了unique
	constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';

2 多表查询概述

  • 概述:指从多张表中查询数据

  • 笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积

  • 基本语法:

    select * from 表1,表2,... 但是这样会产生笛卡尔积 可以加入条件消除笛卡尔积

    select * from 表1,表2 where ... 消除笛卡尔积

2.1 多表查询分类

  • 连接查询

    内连接:相当于查询A、B交集部分数据

    外连接:

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

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

  • 子查询

    在这里插入图片描述

3 内连接

内连接查询语法:

  • 隐式内连接

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

  • 显示内连接

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

内连接查询的是两张表的交集部分

-- 内连接演示
-- 查询每一个员工的姓名以及关联的部门的名称 分别使用隐式、显示内连接
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;

SELECT emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

4 外连接

外连接查询语法:

  • 左外连接

    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

    相当于查询表1(左表)的所有数据 包含表1和表2交集部分的数据

  • 右外连接

    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

    相当于查询表2(右表)的所有数据 包含表1和表2交集部分的数据

-- 外连接演示
-- 查询emp表的所有数据 和对应的部门信息(左外连接)
SELECT emp.*,dept.name from emp LEFT outer JOIn dept ON emp.dept_id = dept.id;
-- 查询dept表的所有数据 和对应的员工信息(右外连接)
SELECT dept.*,emp.* from emp RIGHT outer JOIn dept ON emp.dept_id = dept.id;

5 自连接

自连接查询语法:

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

自连接查询,可以使内连接查询 也可以是外连接查询。

其实就是看成两张表就完事,两张表能做的,它也能做。不过别忘记给两张表都起别名,在FROM处起别名

6 联合查询-union union all

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

SELECT 字段列表 FROM 表A ...

UNION [ALL]

SELECT 字段列表 FROM 表B ...;

-- 1. 将薪资低于5000的员工 和年龄大于 50岁的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;

UNION ALL 是直接将所有查询结果合并,UNION是将所有结果合并后,再将重复结果去除。

联合查询属于纵向合并,需要多个查询结果列数以及字段类型是相同的

7 子查询

  • 概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询

    SELECT * FROM t1 WHERE column1 = (SELECT coulumn1 FROM t2);

    子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

  • 根据子查询结果不同,分为:

    • 标量子查询(子查询结果为单个值)
    • 列子查询(子查询结果为一列)
    • 行子查询(子查询结果为一行)
    • 表子查询(子查询结果为多行多列)

7.1 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为 标量子查询

常用的操作符:= <> > >= < <=

-- 标量子查询
-- 1. 查询销售部的所有员工信息
select id from dept where name = "销售部";
select * from emp where dept_id = (select id from dept where name = "销售部");

-- 2. 查询在“房东白”入职之后的员工信息
select entrydate from emp where name = '房东白';
select * from emp where entrydate > (select entrydate from emp where name = '房东白') ;

7.2 列子查询

子查询返回的结果是一列(可以是多行),这种子查询称为 列子查询

常用操作符:IN、NOT IN 、ANY 、SOME、 ALL

在这里插入图片描述

-- 查询 销售部 和 市场部 的所有员工信息
select id from dept where name = '销售部' or name = '市场部';
select * from emp where id in (select id from dept where name = '销售部' or name = '市场部');

-- 查询比财务部所有人工资都高的员工信息
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
select * from emp where salary > ALL(select salary from emp where dept_id = (select id from dept where name = '财务部'));


-- 查询比研发部其中任意一人工资高的员工信息
select salary from emp where dept_id = (select id from dept where name = '研发部');
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));

7.3 行子查询

子查询返回的结果是一行(可以使多列),这种子查询称为行子查询

常用操作符:= 、<>、IN 、 NOT IN

-- 1 查询与“张无忌” 的薪资及直属领导相同的员工信息;
select salary ,managerid from emp where name = '张无忌';
select * from emp where (salary ,managerid) = (select salary ,managerid from emp where name = '张无忌');

7.4 表子查询

子查询返回的结果是多行多列,这种子查询称为表子查询

常用的操作符:IN

-- 1 查询与“鹿杖客” , “送元钱”的职位和薪资相同跟的员工信息
select job,salary from emp where name = "鹿杖客"  or name = "松原桥";
select * from emp where (job,salary) in (select job,salary from emp where name = "鹿杖客"  or name = "松原桥");

-- 2 查询入职日期是“2006-01-01”之后的员工信息,及其部门信息
select * from emp where entrydate > '2006-01-01';
select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/697325.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Jenkins构建打包部署前端Vue项目至Nginx

一. 安装jenkins 基于DockerJenkins实现自动部署SpringBootMaven项目-CSDN博客 二. 安装NodeJs插件并配置 显示上面两行则表示安装成功, 然后回到首页, 点击’系统管理’->‘全局工具配置’: 配置node.js 三. 创建jenkins项目 1、创建项目 2、配置gitee 3、配置源码 4、…

网络学了点socket,写个聊天室,还得改进

目录 第一版: common 服务端: 客户端 第一版问题总结: 第二版 服务端: 客户端: 改进: Windows客户端 一些小问题 还可以进行的改进 这篇文章我就先不讲网络基础的东西了,我讲讲在我进行制作我这个拉跨聊天室中遇到的问题,并写了三版代码. 第一版: common #pragm…

MyBatis映射器:实现动态SQL语句

大家好&#xff0c;我是王有志&#xff0c;一个分享硬核 Java 技术的金融摸鱼侠&#xff0c;欢迎大家加入 Java 人自己的交流群“共同富裕的 Java 人”。 上一篇文章中&#xff0c;我们已经学习了如何在 MyBatis 的映射器中通过简单的 SQL 语句实现增删改查&#xff0c;今天我…

关闭windows11磁盘地址栏上的历史记录

关闭windows11的磁盘地址栏上的历史记录 windows11打开磁盘后访问某一个磁盘路径后会记录这个磁盘路径&#xff0c;而且有时候会卡住这个地址栏&#xff08;关都关不掉&#xff09;&#xff0c;非常麻烦。 如下图所示&#xff1a; 关闭地址栏历史记录 按下windows键打开开…

C++面试八股文:static和const的关键字有哪些用法?

100编程书屋_孔夫子旧书网 某日二师兄参加XXX科技公司的C工程师开发岗位第7面&#xff1a; 面试官&#xff1a;C中&#xff0c;static和const的关键字有哪些用法? 二师兄&#xff1a;satic关键字主要用在以下三个方面&#xff1a;1.用在全局作用域&#xff0c;修饰的变量或者…

Adobe Premiere Pro 2024下载安装(视频剪辑软件Pr2024)

百度网盘下载地址&#xff08;含PR教学课程&#xff08;PR从入门到精通108节课程&#xff09;&#xff09;https://pan.baidu.com/s/1WKYZENoMzTcKhbgMgbEPGQ?pwdSIMS 一、Pr简介 Pr全称Premiere&#xff0c;是Adobe公司开发的一款功能强大的视频剪辑软件&#xff0c;目前被…

Java实现物候相机和植被分析导出相对指数成果图

一、基础概念 植被分析是利用地理信息系统&#xff08;GIS&#xff09;、遥感技术、生态学、环境科学等多学科交叉手段&#xff0c;对植被的分布、类型、结构、组成、动态变化、生产力、生态功能进行量化评估的过程。植被分析对于生态保护、生物多样性研究、资源管理、环境监测…

Mysql基础 - 事务

Mysql基础 - 事务 文章目录 Mysql基础 - 事务1 事务简介2 事务操作2.1 控制事务一2.2 控制事务二 3 事务四大特性4 并发事务问题5 事务隔离级别 1 事务简介 事务是一组操作的集合&#xff0c;他是一个不可分割的工作单位&#xff0c;事务会把所有操作作为一个整体一起向系统提…

[chisel]马上要火的硬件语言,快来了解一下优缺点

Chisel是什么&#xff1f; Chisel的全称为Constructing Hardware In a Scala Embedded Language&#xff0c;是一个基于Scala的DSL&#xff08;Domain Specific Language&#xff0c;特定领域专用语言&#xff09;。2012年&#xff0c;加州大学伯克利分校&#xff08;UC Berkel…

【Rd-03E】使用CH340给Rd03_E雷达模块烧录固件

Rd03_E 指导手册 安信可新品雷达模组Rd-03搭配STM32制作简易人体感应雷达灯教程 http://t.csdnimg.cn/mqhkE 测距指导手册网址&#xff1a; https://docs.ai-thinker.com/_media/rd-03e%E7%B2%BE%E5%87%86%E6%B5%8B%E8%B7%9D%E7%94%A8%E6%88%B7%E6%89%8B%E5%86%8C%E4%B8%AD%…

02-JAVA面向对象编程

一、面向对象编程 1、面向过程编程思想&#xff08;Process Oritented Programming&#xff09; 将实现一个功能的一组指令组合在一起&#xff0c;成为一个函数。这个函数就能实现这一个功能&#xff0c;是对功能实现的一种抽象。通过这种抽象方式&#xff0c;将代码实现复用。…

软件游戏steam_api.dll丢失的解决方法,总结5种有效的方法

在玩电脑游戏时&#xff0c;我们经常会遇到一些错误提示&#xff0c;其中之一就是“游戏缺少steam_api.dll”。这个问题可能让很多玩家感到困惑和烦恼。那么&#xff0c;究竟是什么原因导致游戏缺少steam_api.dll呢&#xff1f;又该如何解决这个问题呢&#xff1f;本文将为大家…

pyqt opengl 小黑块

目录 OpenGLWidget_g初始化函数&#xff1a; 解决方法&#xff1a;把初始化函数的parent去掉 pyqt opengl 小黑块 原因&#xff1a; 创建OpenGLWidget_g的时候把main_window作为父类&#xff08;self&#xff09;传进去了&#xff0c; self.opengl_widget OpenGLWidget_g(…

SpringBoot+Vue网上超市系统(前后端分离)

技术栈 JavaSpringBootMavenMySQLMyBatisVueShiroElement-UI 系统角色对应功能 用户管理员 系统功能截图

【云岚到家】-day02-2-客户管理-认证授权

【云岚到家】-day02-2-客户管理-认证授权 第二章 客户管理1 认证模块1.1 需求分析1.2 小程序认证1.2.1 测试小程序认证1.2.1.1 参考官方流程1.2.1.2 申请小程序账号1.2.1.3 创建jzo2o-customer1.2.1.4 部署前端1.2.1.5 编译运行1.2.1.6 真机调试 2 阅读代码2.1 小程序认证流程2…

定个小目标之刷LeetCode热题(15)

这道题直接就采用两数相加的规则&#xff0c;维护一个进阶值&#xff08;n&#xff09;即可&#xff0c;代码如下 class Solution {public ListNode addTwoNumbers(ListNode l1, ListNode l2) {// 新建一个值为0的头结点ListNode newHead new ListNode(0);// 创建几个指针用于…

大数据解决方案案例:电商平台日志分析

个人名片 &#x1f393;作者简介&#xff1a;java领域优质创作者 &#x1f310;个人主页&#xff1a;码农阿豪 &#x1f4de;工作室&#xff1a;新空间代码工作室&#xff08;提供各种软件服务&#xff09; &#x1f48c;个人邮箱&#xff1a;[2435024119qq.com] &#x1f4f1…

PythonX.X、pipX的关系

PythonX.X、pipX的关系 Python2.x 与 3.x Python 的 3.0 版本&#xff0c;相对于 Python 的早期版本&#xff0c;是一个大的升级。许多针对早期 Python2.x 版本设计的程序都无法在 Python 3.x 上正常执行。为了照顾大量的历史遗留项目&#xff0c;Python 2.6 作为一个过渡版本…

Chat-TTS:windows本地部署实践【有手就行】

最近Chat-TTS模型很火&#xff0c;生成的语音以假乱真&#xff0c;几乎听不出AI的味道。我自己在本地部署玩了一下&#xff0c;记录一下其中遇到的问题。 环境&#xff1a; 系统&#xff1a;windows 11 GPU&#xff1a; Nvidia 4060 Cuda&#xff1a;12.1&#xff08;建议安…

数据结构与算法题目集(中文)6-2顺序表操作集

题目地址 https://pintia.cn/problem-sets/15/exam/problems/type/6?problemSetProblemId725&page0 注意审题&#xff0c;返回false的时候不要返回ERROR&#xff0c;否则答案错误&#xff0c;机器规则是死的。 位置一般指数组下标&#xff0c;位序一般指数组下标1。但是思…