MySQL多表查询

最新推荐文章于 2023-07-27 11:30:00 发布
最新推荐文章于 2023-07-27 11:30:00 发布
阅读量259 收藏
点赞数
分类专栏： java菜鸟
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Jackson__Yi/article/details/100746845
版权

多表查询

1、分类

    -| 合并结果集（了解）

    -| 连接查询

    -| 子查询

合并结果集

  -| 要求被合并的表中，列的类型和列数相同

  -| union 去除重复行

  -| UNION ALL， 不去除重复行

 

select * from cd

union ALL

SELECT * FROM ab;

 

连接查询

1.分类

  -| 内连接

  -| 外连接

    -| 左外连接

    -| 右外连接

    -| 全外连接(MySQL不支持)

自然连接(属于一种简化)

 

2.内连接

  -| 方言：select * from 表1 别名1， 表2， 别名2 where 别名1.xx = 别名2.xx

  -| 标准：select * from 表1 别名1 inner join 表2 别名2 on 别名1.xx = 别名2.xx

  -| 自然： select * from 表1 别名1 natural join 表2 别名2

内连接查询出的所有记录都满足条件。

 

3. 外连接

    左外：select * from 表1 别名1 left outer join 表2 别名2 on 别名1.xx = 别名2.xx

      >左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，其右表部分为NULL

    左外自然：select * from 表1 别名1 NATURAL LEET OUTER JOIN 表2 别名2 ON 别名1.xx == 别名2.xx

    右外：select * from 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx = 别名2.xx

      >右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表中不满足条件的记录，其左表部分为NULL

      左外自然：select * from 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx == 别名2.xx

    全链接：可以使用UNION来完成全链接

子查询：

      查询中有查询（查看select关键字的个数！）

1.出现的位置

   where后作为条件存在

   from后作为表存在（多行多列） 

  2.条件

   单行单列：SELECT * FROM 表1 别名1 WHERE 列1 [=、 >、 <、 >=、 <=、 != ]（SELECT 列 FROM 表2 别名2 WHERE 条件）

    多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN、 ALL、 ANY ]（SELECT 列 FROM 表2 别名2 WHERE 条件）

    单行多列：SELECT * FROM 表1 别名1 WHERE 列1 （列1， 列2）IN（SELECT 列 FROM 表2 别名2 WHERE 条件）

    多行多列：SELECT * FROM 表1 别名1 （SELECT ......）别名2 WHERE 条件

-- ---------------------mysql内连接查询（方言）----------------------
-- 查询的笛卡尔积
-- {a, b, c} {1, 2}
-- {a1, a2, b1, b2, c1, c2}
-- 在查询中会产生大量的垃圾数据
SELECT *
FROM emp, dept;   -- 会产生笛卡尔积

-- ----------加入查询条件-----------
SELECT * 
FROM emp, dept
WHERE emp.deptno = dept.deptno;

/*
	打印：所有员工的姓名、工资、以及部门名称（去笛卡尔积）
*/
SELECT e.ename as '姓名', e.sal as '工资', d.dname as '部门名称' 
FROM emp e, dept d  -- 为表加入别名
WHERE e.deptno = d.deptno;
-- ---------------------mysql内连接查询（标准）----------------------
-- 标准内连接查询（在开发中建议使用标准查询语句，保证兼容性，防止数据库变更时重写SQL语句）

SELECT e.ename as '姓名', e.sal as '工资', d.dname as '部门名称' 
FROM emp e INNER JOIN dept d   -- 查询的内连接
ON e.deptno = d.deptno;					-- 此处可以使用where关键字 但是作为标准使用方式要使用on

-- ---------------------mysql内连接查询（自然）----------------------
-- 自然查询  NATURAL（此方法可读性较低，不建议使用）
SELECT e.ename as '姓名', e.sal as '工资', d.dname as '部门名称' 
FROM emp e NATURAL JOIN dept d;   -- 使用natural关键字 在查询中系统会自动都去关联表中查找相同的列进行匹配

--  ---------------SQL查询外连接-------------------
/* 外连接有一住一次，左外（左表）为主
										即：emp e为主，主表中所有的记录无论是否满足，都打印出来
																			 当不满足条件时，右表部分使用null补位
 */
SELECT e.ename, e.sal, d.dname  -- 可以使用ifnull函数，将null变为给定信息
  -- SELECT e.ename, e.sal, IFNULL(d.dname,'无部门')
FROM emp e LEFT OUTER JOIN dept d     -- 左外连接
ON e.deptno = d.deptno;

-- 右链接
SELECT e.ename, e.sal, d.dname  -- 查询效果与左连接相反（表中财务部没有员工，只显示部门名称，其他列用null补位，张三没有与主表相连，所以无法查询到）
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

-- 全外链接（左表和右表没有出现的信息都要进行补位）
SELECT e.ename, e.sal, d.dname
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;

-- 注意：mysql中不支持全外链接查询，可以使用结果合并集模拟次操作
SELECT e.ename, e.sal, d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno   -- 注意此处不能出现分号（分号表示一条查询语句的结束）
UNION				-- 用结果集弥补全外查询
SELECT e.ename, e.sal, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
-- ----------------------子查询------------------------------
/*
  查询本公司工资最高的员工的详细信息
  子查询可以出现的位置：
		FROM
		WHERE
*/

SELECT MAX(sal) FROM emp;   -- 查询最高工资

SELECT *			-- 查询指定工资（最高工资）的员工的所有信息
FROM emp e
WHERE sal=(SELECT MAX(sal) FROM emp);  -- 在查询条件中加入查询条件（子查询）

-- 出现位置在from中
SELECT e.empno, e.ename
FROM(SELECT * FROM emp WHERE deptno=30) e  -- 以部门为30的员工为基础进行二次查询,注意要取一个别名
WHERE e.empno = 1002;

-- 子查询的结果集分为：
	-- 单行单列， 多行单列、单行单列、 多行多列

/*单行单列：打印高于平均工资的所有员工*/
SELECT * 
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);

/*多行单列：打印高于30部门所有员工工资的员工*/
SELECT * 
FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE deptno=20);

/*多行单列：打印高于任意经理工资的员工*/
SELECT * 
FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE job='经理');

/*单行多列：打印工作、部门和工资都与殷天正相同的员工  */
SELECT * 
FROM emp
WHERE(job, deptno, sal) IN (SELECT job, deptno, sal FROM emp WHERE ename='殷天正'); -- 可以理解为比较两个对象是否相等

/*多行多列：查询30部门中所以员工的编号和姓名*/
SELECT e.empno, e.ename
FROM(SELECT * FROM emp WHERE deptno=30) e

mysql数据源：

/*
Navicat MySQL Data Transfer

Source Server         : erp
Source Server Version : 50022
Source Host           : localhost:3306
Source Database       : exam

Target Server Type    : MYSQL
Target Server Version : 50022
File Encoding         : 65001

Date: 2019-09-12 16:43:52
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL,
  `dname` varchar(50) default NULL,
  `loc` varchar(50) default NULL,
  PRIMARY KEY  (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', '教研部', '北京');
INSERT INTO `dept` VALUES ('20', '学工部', '上海');
INSERT INTO `dept` VALUES ('30', '销售部', '广州');
INSERT INTO `dept` VALUES ('40', '财务部', '武汉');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(11) NOT NULL,
  `ename` varchar(50) default NULL,
  `job` varchar(50) default NULL,
  `mgr` int(11) default NULL,
  `hiredate` date default NULL,
  `sal` decimal(7,2) default NULL,
  `COMM` decimal(7,2) default NULL,
  `deptno` int(11) default NULL,
  PRIMARY KEY  (`empno`),
  KEY `fk_emp` (`mgr`),
  CONSTRAINT `fk_emp` FOREIGN KEY (`mgr`) REFERENCES `emp` (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1001', '甘宁', '文员', '1013', '2000-12-17', '8000.00', null, '20');
INSERT INTO `emp` VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30');
INSERT INTO `emp` VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30');
INSERT INTO `emp` VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00', null, '20');
INSERT INTO `emp` VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30');
INSERT INTO `emp` VALUES ('1006', '关羽', '经理', '1009', '2001-05-01', '28500.00', null, '30');
INSERT INTO `emp` VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10');
INSERT INTO `emp` VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-09-01', '30000.00', null, '20');
INSERT INTO `emp` VALUES ('1009', '曾阿牛', '董事长', null, '2001-11-17', '50000.00', null, '10');
INSERT INTO `emp` VALUES ('1010', '韦一笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30');
INSERT INTO `emp` VALUES ('1011', '周泰', '文员', '1008', '2007-05-28', '11000.00', null, '20');
INSERT INTO `emp` VALUES ('1012', '程普', '文员', '1006', '2001-12-03', '9500.00', null, '30');
INSERT INTO `emp` VALUES ('1013', '庞统', '分析师', '1004', '2001-12-09', '30000.00', null, '20');
INSERT INTO `emp` VALUES ('1014', '黄盖', '文员', '1007', '2002-01-23', '13000.00', null, '10');
INSERT INTO `emp` VALUES ('1015', '张三', '保洁员', '1009', '1999-09-09', '80000.00', '90000.00', '50');

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) NOT NULL,
  `losal` int(11) default NULL,
  `hisal` int(11) default NULL,
  PRIMARY KEY  (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '7000', '12000');
INSERT INTO `salgrade` VALUES ('2', '12010', '14000');
INSERT INTO `salgrade` VALUES ('3', '14010', '20000');
INSERT INTO `salgrade` VALUES ('4', '20010', '30000');
INSERT INTO `salgrade` VALUES ('5', '30010', '99990');

-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
  `sid` int(11) NOT NULL,
  `sname` varchar(50) default NULL,
  `age` int(11) default NULL,
  `gender` varchar(10) default NULL,
  `province` varchar(50) default NULL,
  `tuition` int(11) default NULL,
  PRIMARY KEY  (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '山东', '3500');
INSERT INTO `stu` VALUES ('4', '宋永合', '18', '男', '成都', '4500');
INSERT INTO `stu` VALUES ('5', '宋永合', '20', '女', '湖北', '1000');

 

Jackson__Yi
关注
专栏目录
MySQL学习笔记第六章（多表查询）
mabaizi的博客
05-01 1169
假设有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。**总结：**连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件。除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。角度1 ：针对连接条件：等值连接 vs 非等值连接。角度2：自连接 vs 非自连接。
mysql多表联合查询 去重_mysql中的多表联合并且去重排序
weixin_32267731的博客
01-26 3757
同一数据库中au_log_开头的表，表中ipsrc和ipdst、portsrc和portdst字段将这些4元组去重后显示出来，并按照某个字段排序$result = $db->query("(SELECT ipsrc,ipdst,portsrc,portdst FROM au_log_baseline where node_name = '".$deviceName."' group by i...
【关于mysql：SQL中的左外连接，右表中缺少数据的左表列也显示出来】
qq_45442178的博客
05-15 2052
左表右表当你需要查询letter_data中branch_id中的数量并通过branch表连接查询，查询语句这种left join on后的where条件会使原本在on之后查询左表的字段筛选掉，从而即使左表的字段所count的值为null也不显示出来。
mysql左表有右表没有_查询左表存在而右表不存在的记录
mimosa2008的博客
06-09 2497
mysql左表有右表没有_查询左表存在而右表不存在的记录
两种极端情况的案例：N+1次查询和笛卡尔积
Laurence的技术博客
05-09 6035
前一篇文章两种极端：频繁的查询和巨大的结果集讲到了Hibernate加载数据时可能会出现的两种极端情况：频繁的查询和一次查出巨大的结果集。其中：N+1次查询是前一种情况的一个典型案例，笛卡尔积则是后一种情况的典型案例。下面分别简单地再总结一下这两种极端案例出现的原因以及调优方法。 一.N+1次查询    如果一个集合是lazy loading的,那么在第一次访问到这个集合时，hib
mysql多表联合查询练习题
Jackson__Yi的博客
10-03 7415
1、查询至少有一个员工的部门，显示部门编号，部门名称，部门位置，部门人数 /* 1、查询至少有一个员工的部门，显示部门编号，部门名称，部门位置，部门人数 列：d.deptno, d.dname, d.loc, 部门人数 (要查询部门人数需要使用分组) 表：dept d, emp e 条件：e.deptno = d.deptno */ SELECT * -- 查询部...
mysql多表查询的分类
Frank---7的博客
07-15 1万+
内连接 等值连接 非等值连接 自连接 外连接 inner join 等值连接 非等值连接 自连接 外连接
MySQL多表查询（联合查询、连接查询、子查询）
最新发布
m0_49864110的博客
07-27 2万+
EXCEPT 将两张表的某些字段的公共记录减去，然后返回一张表的剩余记录（差集）（MySQL不存在，使用NOT IN字段实现）3、作为联合查询的多表之间的列数、以及列数的类型必须相同（例如：表1查询哪些列，表2就查询哪些列）1、通过联合查询，可以得到两张表中记录的集合或者公共记录的集合，或者其中某张表中的记录的集合。将两张表的全部记录交叉组合，在添加了其他表字段的同时，所得的记录数是两张表行数的乘积。#查询张三入职之后的所有员工信息（先查询张三的入职信息，再查询此时间之后的员工信息。
mysql 多表 结构相同 查询
qq_27127363的博客
03-14 3930
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。 expression1, expression2, ... expression_n: 要检索的列。 tables:要检索的数据表。 WHERE conditions:可选， 检索条件。 DISTINCT:可选，删除结果集中重复的数据。默认情况下...
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
mysql 多表查询效率优化_详解Mysql多表联合查询效率分析及优化_MySQL - join
weixin_29827279的博客
01-26 1956
...。MySQL数据库支持如下的联接查询：CROSS JOIN(交叉联接)INNER JOIN(内联接)OUTER JOIN(外联接)其它在进行各种联接操作时，一定要回忆一下在《SQL逻辑查询语句执...代码如下:SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.columnb. RIG...
MySQL多表查询大全（超精确）
qq_53518630的博客
05-02 7303
目录 1、等值查询 2、内连接 3、外连接 3.1、左外连接 3.2、右外连接 3.3、完全外连接 4、交叉连接 5、自连接 6、联合查询 如果我们要查询的数据分布在不同的表时，那么需要连接多张表进行多表查询。 --而这些表当中，必须存在主外键关系（直接关联） --如果不存在直接关系，则必定存在间接关系，否则业务上是没有意义的 1、等值查询 select 字段集合 from 表1,表2,...,表n where 条件 (连接条件，一般使用主外键) #注意： 条件..
Mysql 多表查询详解
热门推荐
Jintao_Ma的博客
04-27 12万+
纸上得来终觉浅 1.前言：上篇讲到Mysql中关键字执行的顺序，只涉及了一张表；实际应用大部分情况下，查询语句都会涉及到多张表格 : 1)多表连接有哪些分类； 2)针对这些分类有哪些连接方法？ 3)这些连接方法分别作用于哪些应用场景？ 这篇针对这三个点通过实例来讲述，目的是穷尽所有的场景和所有的方法，并且对每个方法的使用做实例。 2.假设有两张表格A和B，把表格当作一个集合，那么表格中
java 联合查询,联合查询 使用方法
weixin_39644377的博客
03-20 522
联合查询Ø Union :集合的并，不包括重复行select field1, field2, . field_n from tables UNION select field1, field2, . field_n from tables;Ø union all：集合的并，包括重复行select field1, field2, .field_n from tables UNION ALL s...
idea点击网页预览jsp界面：Please, configure Web Facet first!错误问题
Jackson__Yi的博客
11-26 7939
在调整css和js文件加载的时候，把IDEA中web加载地址弄错了，导致在启动网页预览时， 出现：Please, configure Web Facet first!错误 在Project Structure中查看facets选项： 查看资源路径是否正确，原来出现的错误是，路径报红，无法找到此路径，删除后重新添加： 注意：添加的时候，默认到子项目名称路径，根路径需要自己手动添加（...
关系模型中的3中关系（一对一，一对多，多对多）
Jackson__Yi的博客
09-11 6575
数据库中，一对一关系：从表的主键既是外键。 CREATE TABLE hasbend( -- 创建主表 hid INT PRIMARY KEY auto_increment, -- 指定字段 hname VARCHAR(50) ); ALTER TABLE hasbend -- 修改表名 REMOVE TO hasbend; DROP TABLE hasbend; ...
IDEA中如何解决,去除JSP、HTML、XML页面中黄色背景问题
Jackson__Yi的博客
11-22 4806
去除JSP、HTML、XML页面中黄色背景
Eclipse中运行Coverrage，代码字体背景变红/变黄/变绿，原因和取消方法
Jackson__Yi的博客
08-31 4455
EclEmma-Java Code Coverage for Eclipse 是eclipse中的一个插件，简单概述： EclEmma是Eclipse的免费Java代码覆盖工具 ，可在Eclipse Public License下 获得。它将代码覆盖率分析直接引入Eclipse工作台： 快速开发/测试周期：可以直接分析JUnit测试运行中的工作台内的启动以获取代码覆盖率。 特征： Ecl...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值