MySQL-多表查询

已于 2023-03-29 12:44:33 修改
阅读量1.9k 收藏 3
点赞数 2
分类专栏： SQL 文章标签： 多表查询 内连接 外连接 子查询 自连接
于 2021-11-23 00:25:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45477086/article/details/121484052
版权

准备数据：

use db1;

-- 创建用户表
CREATE TABLE user(
  id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
  NAME VARCHAR(20),			        -- 用户姓名
  age INT                             -- 用户年龄
);
-- 添加数据
INSERT INTO user VALUES (NULL,'张三',23),(NULL,'李四',24),(NULL,'王五',25),(NULL,'赵六',26);

-- 订单表
CREATE TABLE orderList(
  id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
  number VARCHAR(30),					-- 订单编号
  uid INT,    -- 外键字段
  CONSTRAINT ou_fk FOREIGN KEY (uid) REFERENCES user(id)
);

-- 添加数据
INSERT INTO orderList VALUES (NULL,'test001',1),(NULL,'test002',1),(NULL,'test003',2),
(NULL,'test004',2),(NULL,'test005',3),(NULL,'test006',3),(NULL,'test007',NULL);


-- 商品分类表
CREATE TABLE category(
 id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
 NAME VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公'),(NULL,'烟酒茶糖'),(NULL,'鞋靴箱包');


-- 商品表
CREATE TABLE product(
  id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
  NAME VARCHAR(30),                    -- 商品名称
  cid INT, -- 外键字段
  CONSTRAINT cp_fk FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (NULL,'华为手机',1),(NULL,'小米手机',1),(NULL,'联想电脑',2),
(NULL,'苹果电脑',2),(NULL,'中华香烟',3),(NULL,'玉溪香烟',3),(NULL,'计生用品',NULL);


-- 中间表
CREATE TABLE us_pro(
  upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
  uid INT, -- 外键字段。需要和用户表的主键产生关联
  pid INT, -- 外键字段。需要和商品表的主键产生关联
  CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
  CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1),(NULL,1,2),(NULL,1,3),(NULL,1,4),
(NULL,1,5),(NULL,1,6),(NULL,1,7),(NULL,2,1),(NULL,2,2),(NULL,2,3),(NULL,2,4),
(NULL,2,5),(NULL,2,6),(NULL,2,7),(NULL,3,1),(NULL,3,2),(NULL,3,3),(NULL,3,4),
(NULL,3,5),(NULL,3,6),(NULL,3,7),(NULL,4,1),(NULL,4,2),(NULL,4,3),(NULL,4,4),
(NULL,4,5),(NULL,4,6), (NULL,4,7);

多表查询：

笛卡尔积：指A表和B表所有组合的情况，在查询数据时需消除笛卡尔积现象

-- 多表查询，笛卡尔积现象
select * from product , category;

-- 消除笛卡尔积现象
select * from product p, category c where p.cid = c.id;

内连接：

内连接查询的是两张表有交集的数据(有主外键关联的数据)

显式内连接

-- 标准语法  INNER可写可不写
SELECT 列名 FROM 表1 [INNER] JOIN 表2 ON 表1.公共字段 = 表2.公共字段;
-- 查询用户信息和对应的订单信息，起别名
SELECT u.name ,o.number FROM user u JOIN orderList o ON u.id = o.uid;

-- 查询用户姓名，年龄。和订单编号，标准查询格式
SELECT u.name,u.age,o.number FROM USER u INNER JOIN orderList o ON u.id = o.uid;

隐式内连接：

-- 标准语法
SELECT 列名 FROM 表1,表2 WHERE 条件 表1.公共字段 = 表2.公共字段;

-- 隐式内连接查询
-- 查询用户姓名和订单编号
SELECT u.name,o.number FROM USER u, orderList o WHERE u.id=o.uid;

问题：下面这两个语句执行结果是否一致

SELECT u.name ,o.number FROM user u JOIN orderList o ON u.id = o.uid;
SELECT u.name ,o.number FROM orderList o JOIN user u ON u.id = o.uid;

答:一致，因为内连接获取的是两个表的公共部分

外连接：

左外连接：

查询左表的全部数据，和左右两张表有交集部分的数据。

演示：

-- 标准语法   OUTER可写可不写
SELECT 列名 FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.公共字段 = 表2.公共字段;

-- 查询所有用户信息，以及用户对应的订单信息
SELECT u.*,o.number FROM USER u LEFT OUTER JOIN orderList o ON u.id = o.uid;

右外连接：

查询右表的全部数据，和左右两张表有交集部分的数据

-- 基本语法   OUTER可写可不写
SELECT 列名 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.公共字段 = 表2.公共字段;
select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段

-- 查询所有订单信息，以及订单所属的用户信息
SELECT u.*,o.* FROM USER u RIGHT OUTER JOIN orderList o ON u.id=o.uid;

右连接也可以变成左连接，所以开发使用左连接比较多

SELECT u.*, o.* FROM orderList o LEFT JOIN USER u ON u.id=o.uid;

自连接：

子连接查询，可以是内连接也可以是外连接查询，也就是自己连接自己。

-- 自连接语法：
select 字段列表 from 表1 别名1 JOIN 表1 别名2 ON 别名1.公共字段 = 别名2.公共字段;

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	mgr INT,
	salary DOUBLE
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);

-- 查询所有员工的姓名及其直接上级的姓名，
SELECT e1.name, e2.name FROM employee e1,employee e2 WHERE e1.mgr = e2.id;

-- 查询所有员工的姓名及其所属上级的姓名，没有上级的员工也需要查询
/*
分析：
	员工姓名 employee表        直接上级姓名 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
*/
SELECT e1.name,	e1.mgr, e2.id, e2.name FROM employee e1 LEFT JOIN employee e2 ON e1.mgr = e2.id;

联合查询：

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

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

-- 语法
-- SELECT 字段列表 FROM 表A UNION [ALL] SELECT 字段列表 FROM 表B;
-- 查询薪资大于5000，年龄大于50的员工
select * from user  where age > 20;
select * from user  where salary > 5000;

-- 把这两个语句连接起来就是联合查询
select * from user  where age > 20 union all select * from user  where salary > 5000;

-- 去重
select * from user  where age > 20 union select * from user  where salary > 5000;

子查询：

  • SQL语句中嵌套了SELECT语句，称为嵌套查询或子查询。

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

根据查询结果不同，子查询分为：

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

标量子查询：

子查询返回的结果是单个值（数字、字符串、日期等），这种查询称为标量子查询。

常用的操作符：=、<>、>、 >=、<、<=

演示：

-- 查询手机数码分类下的所有商品信息，先查询到分类id，再用分类id查询所有商品
select id from category where name = '手机数码';
select * from product where cid = 1;

-- 使用标量子查询实现
select * from product where cid = (select id from category where name = '手机数码');

列子查询：

子查询的返回结果是一列（可以是多行），这种子查询称为列子查询

常用的操作符：IN、NOT IN、ANY、SOME、ALL

操作符含义
IN在指定的集合范围之内，多选一
NOT IN不在指定的集合范围之内
ANY子查询返回列表中，有任意一个满足即可
SOME子ANY等同，使用SOME的地方都可以使用ANY
ALL子查询返回列表的所有值都必须满足

演示：

-- 查询"手机数码"分类下的所有商品价格，先查id
select id from category where name = '手机数码';

-- 再查分类下所有的商品价格
select price from product where cid = (select id from category where name = '手机数码');

-- 查询比"手机数码"全部商品价都要高的商品信息(总和)
select * from product where price > all (select price from product where cid = (select id from category where name = '手机数码'));

-- 查询比"手机数码"分类下任意一个价格高的商品信息，使用any和some一样
select * from product where price > any (select price from product where cid = (select id from category where name = '手机数码'));

行子查询：

子查询的返回结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：=、<>、IN、NOT IN

-- 查询"华为手机"的价格及所属分类下相同价格的商品信息，先查华为手机的所属分类
select cid from product where name ='华为手机';

-- 查询该分类下与华为手机价格相同的商品信息，对比查询结果即可
select * from product where (cid) = (1);

-- 转换成动态的查询结果
select * from product where (cid) = (select cid from product where name ='华为手机');

表子查询:

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

常用的操作符：IN

-- 查询和'华为手机'、'小米手机'的分类、价格相同的商品信息
-- 先查'华为手机'、'小米手机'的分类、价格
select name,price from product where name = '华为手机' or name = '小米手机';

select * from product where (name,price) in(select name,price from product where name = '华为手机' or name = '小米手机');

 -- 查询商品上架时间是'2023-01-01'之后的商品信息及所属分类信息，把结果作为一个表
 select * from product where putawayDate > '2023-01-01';

-- 查询这些商品及分类信息
select p.*,c.* from ( select * from product where putawayDate > '2023-01-01') p left join category c on c.id = p.cid;

多表查询练习：

1、查询用户的id、姓名、年龄、订单编号

/*
分析：
	user用户表：id、姓名、年龄  
	orderList订单表：订单编号
	条件：user.id = orderList.uid
*/

SELECT
	u.id,
	u.NAME,
	u.age,
	o.number 
FROM
	USER u,
	orderList o 
WHERE
	u.id = o.uid;

2、查询所有的用、用户的id、姓名、年龄、订单编号

/*
分析：
	user表：id、姓名、年龄  
	orderList表：订单编号
	条件：user.id = orderList.uid
	查询所有用户，使用左外连接
*/

SELECT
	u.id,
	u.NAME,
	u.age,
	o.number 
FROM
	USER u
	LEFT INNER JOIN orderList o ON u.id = o.uid;

3、查询所有的订单、用户的id、姓名、年龄、订单编号

/*
分析：
	user表：id、姓名、年龄 
	orderList表：订单编号
	条件：user.id = orderList.uid
	查询所有订单，使用右外连接
*/ 

SELECT
	u.id,
	u.NAME,
	u.age,
	o.number 
FROM
	USER u
	RIGHT OUTER JOIN orderList o ON u.id = o.uid;

4、查询用户年龄大于23岁的信息、显示用户的id、姓名、年龄、订单编号

/*
分析：
	user表：id、姓名、年龄 
	orderList表：订单编号
	条件：user.id = orderList.uid AND user.age > 23;
*/

SELECT
	u.id,
	u.NAME,
	u.age,
	o.number 
FROM
	USER u,
	orderList o 
WHERE
	u.id = o.uid 
	AND u.age > 23;

5、查询张三、李四用户的信息。显示用户的id、姓名、年龄。订单编号

/*
分析：
	user表：id、姓名、年龄 
	orderList表：订单编号
	条件：user.id = orderList.uid AND user.name IN ('张三','李四');
*/

SELECT
	u.id,
	u.NAME,
	u.age,
	o.number 
FROM
	USER u,
	orderList o 
WHERE
	u.id = o.uid 
	AND u.NAME IN ( '张三', '李' );

6、查询商品分类的id、分类名称、分类下的商品名称

/*
分析：
	category表：商品分类的编号、分类名称
	product表：分类下的商品名称 
	条件：category.id = product.cid
*/

SELECT
	c.id,
	c.NAME,
	p.cid 
FROM
	category c,
	product p 
WHERE
	c.id = p.cid;

7、查询所有的商品分类、商品分类的id、分类名称、分类下的商品名称

/*
分析：
	category表：商品分类的编号、分类名称 
	product：表分类下的商品名称 
	条件：category.id = product.cid
	查询所有的商品分类，使用左外连接
*/
SELECT
	c.id,
	c.NAME,
	p.NAME 
FROM
	category c
	LEFT OUTER JOIN product p ON c.id = p.cid;

8、查询所有的商品信息、商品分类的id、分类名称、分类下的商品名称

/*
分析：
	category表：商品分类的编号、分类名称
	product表：分类下的商品名称
	条件：category.id = product.cid
	查询所有的商品信息，使用右外连接
*/
SELECT
	c.id,
	c.NAME,
	p.NAME 
FROM
	category c
	RIGHT OUTER JOIN product p
	ON c.id = p.cid;

9、查询所有的用户和该用户能查看的所有的商品、显示用户的id、姓名、年龄、商品名称

/*
分析：
	user表：用户的id、姓名、年龄
	us_pro中间表：
	product表：商品名称
	条件：us_pro.uid = user.id AND us_pro.pid = product.id
*/
SELECT
	u.id,
	u.NAME,
	u.age,
	p.NAME 
FROM
	USER u,
	product p,
	us_pro up 
WHERE
	up.uid = u.id 
	AND up.pid = p.id;

10、查询张三和李四这两个用户可以看到的商品。显示用户的id、姓名、年龄、商品名称

/*
分析：
	user表：用户的id、姓名、年龄
	product表：商品名称
	us_pro中间表：
	条件：us_pro.uid = user.id AND us_pro.pid = product.id AND user.name IN ('张三','李四')
*/
SELECT
	u.id,
	u.NAME,
	u.age,
	p.NAME 
FROM
	USER u,
	product p,
	us_pro up 
WHERE
	up.uid = u.id 
	AND up.pid = p.id 
	AND u.NAME
	IN ( '张三', '李四' );
itzhuzhu.
关注
  • 2
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
数据库MySQL-多表查询
12-14
1.2 多表查询 1.2.1 内连接 规则：返回两个表的公共记录 语法： -- 语法一 select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 -- 语法二 select * from 表1，表2 where 表1.公共字段=表2.公共字段 ...
Mysql-单表查询 教学.pdf
最新发布
06-17
mysql -单表查询 教学.pdf
数据库之表查询，单表、多表，子查询
annie_zhou6158的博客
08-21 238
基本查询语句及方法 from where group by having distinct order by limit .... 连表 inner join left join right join union 子查询 书写顺序 select id,name from emp where id > 3 and id < 6;执行顺序 from # 确定到...
数据库_MySQL多表_查询_子查询
Jade-Tiger的博客
03-30 636
01.MySQL多表_建表原则： 1).要保证每个列只记录一个数据，如果一个列记录了多条数据，那么应该拆分为多列存储； 订单信息： id 订单日期 订单金额 商品信息 1 2017-10-01 1000 1,2,3,4 2).为每个表创建一个"主键"字段。 3).要保证每个表只记录一个事情，如果记录了多件事情，应该分表存储； 学员信息表： id 姓名 性别 年龄 科目(科目需要被分离出...
Linux进程管理详解：从基本操作到动态监控网络状态（ps、top、kill、killall、pstree、htop、systemctl、netstat、ss）
2402_83140078的博客
05-09 675
进程是计算机中正在运行的程序的实例。每个进程都有自己的内存空间、寄存器和文件描述符等资源。进程可以独立执行，与其他进程并发运行。
MySQL-04-笔记
qq_24410589的博客
05-27 205
多表查询 1 多表查询-数据准备 2 多表查询-笛卡尔积查询(了解) 3 多表查询-内连接查询 4 多表查询-外连接查询 5 多表查询-子查询 6 多表查询练习 7 多表查询-自关联查询
二、多表联查
panchang199266的博客
06-12 524
（一）案例介绍   有四张表：用户表（user）、商户表（business）、订单表(orders)、订单评论表(comment)，现在要根据“商户ID（businessId）”查询该商户的所有评论，怎么做？ （二）解决思路   1.最开始的思路   一开始，我想利用“商户ID（businessId）”查询出所有订单（orderList），在遍历所有订单，取出评论，组成评论集合。   问...
MySQL子查询练习题 【牛客-SQL必知必会】10 使用子查询
guliguliguliguli的博客
08-04 460
MySQL子查询练习题 ，牛客-SQL必知必会 10 使用子查询
MySQL多表查询详解上
09-08
主要介绍了MySQL多表查询详解上，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
MySQL 多表查询实现分析
09-11
MySQL 多表查询实现分析 MySQL 多表查询是指在一个数据库中，存在多个表，这些表都是相互关联的。我们可以使用多表查询来组合这些表，实现复杂的数据查询和分析。 在 MySQL 中，多表查询可以使用多种方式，包括 ...
MySQL多表查询详解下
09-08
MySQL的多表查询是数据库操作中的重要组成部分，它允许我们从多个相关表中获取数据，合并成一个单一的结果集。本篇文章将详细讲解MySQL的多表查询，特别是1999年的SQL标准语法。 首先，多表查询的核心是连接（JOIN...
Oracle
qq_43759694的博客
04-08 187
Oracle简单SQLSQL语句分类SQL函数 简单SQL 创建表 SQL> CREATE TABLE USERS( 2 ID NUMBER(8) PRIMARY KEY, 3 NAME VARCHAR(10), 4 SEX CHAR(2), 5 ADDR VARCHAR2(20), 6 BIRTHDAY DATE, 7 SALARY NUMBER(8,2) 8 ); 表已创建。 删除表 DROP TABLE xxx; 查询表结构（只能在命令行模式下）
MyBatis入门
qq_58081244的博客
03-18 82
注意：这里用的resultMap，如果bean类的参数名和数据库一致，用resultType就行，它的key等于bean类的全限定名即java包下的路径。注意：同时resultType改成resultMap，并且key等于上面resultMap标签的id值。注：也可以不写commit，在sqlsession创建时加入true参数。改：jdbc的连接路径，以及<mapper>标签的resource属性。注：返回值也可以写void，写int返回的是影响的行数。5.1接口和xml和main方法需要修改的。
MySQL｜基础操作+8大查询方式汇总
二哥为啥不像程序员？
10-24 6200
MySQL中可以创建多个数据库用于存储不同场景的表结构，学习MySQL之前，我们要先理清如下的关系：数据库 --> 数据表 --> 字段抛开数据库存储上限去考虑，每个数据库中可以包含无数个数据表，而每个数据表又可以包含无数个字段，因此我们的学习也应该从创建数据库开始。use 数据库名;
Java程序猿必学第二十七篇——MySQL视图与JDBC
程序猿的博客
11-18 2019
1. 视图 概念： 从一张或多张表中抽取的虚拟表，操作虚拟表的CRUD与原表完全一样 优点： 1.简单化，从多张表抽取后，只需要管理虚拟表即可 2.安全性，能看到的只是抽取到虚拟表的字段 3.逻辑独立性 缺点： 1.性能没有提升，简单表会变得复杂 2.修改不方便，有多种情况不能修改虚拟表的 创建视图： create view myView as select employee_id,first_name,last_name,salary from t_employees; select * f..
数据库查询所有顾客的订单编号、顾客编+号、顾客姓名、订单时间怎么写命令语...
weixin_42613360的博客
12-27 864
下面是一种可能的写法： SELECT orders.order_id, customers.customer_id, customers.name, orders.order_time FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id 这条查询会从 orders 表和 customers ...
MySql简单操作总结
zl15145428的博客
08-05 441
MySql简单操作总结
BigDecimal加减乘除计算
热门推荐
weixin_49562132的博客
09-13 6万+
BigDecimal的运算——加减乘除 首先是bigdecimal的初始化 这里对比了两种形式，第一种直接value写数字的值，第二种用string来表示 我们对其进行加减乘除绝对值的运算,其实就是Bigdecimal的类的一些调用 BigDecimal num1 = new BigDecimal(0.005); BigDecimal num2 = new BigDecimal(1000000); BigDecimal num3 = new BigDecimal(-1000000); //尽
BigDecimal的常用方法
delete_bug的博客
05-14 4146
Java在java.math包中提供的API类BigDecimal，用来对超过16位有效位的数进行精确的运算。双精度浮点型变量double可以处理16位有效数，但在实际应用中，可能需要对更大或者更小的数进行运算和处理。一般情况下，对于那些不需要准确计算精度的数字，我们可以直接使用Float和Double处理，但是Double.valueOf(String) 和Float.valueOf(String)会丢失精度。所以开发中，如果我们需要精确计算的结果，则必须使用BigDecimal类来操作。
mysql数据库-单表查询一
05-23
对于 MySQL 数据库的单表查询，我们可以使用 SELECT 语句来实现。下面是一个示例： ``` SELECT * FROM table_name; ``` 其中，`table_name` 表示要查询的表名，`*` 表示查询所有列，也可以指定要查询的列名。例如...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

itzhuzhu.

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值