多表查询：内连接查询、外连接查询、子查询、自关联查询

最新推荐文章于 2022-12-04 16:58:37 发布
最新推荐文章于 2022-12-04 16:58:37 发布
阅读量364 收藏
点赞数 2
分类专栏： 基础 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/OVERLORDLL/article/details/118609672
版权

1. 多表查询数据准备

USE liulu;
-- 创建users表
CREATE TABLE users(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
	NAME VARCHAR(20),			        -- 用户姓名
	age INT                             -- 用户年龄
);
-- 添加数据
INSERT INTO users VALUES (1,'李白',23);
INSERT INTO users VALUES (2,'杜甫',24);
INSERT INTO users VALUES (3,'王维',25);
INSERT INTO users VALUES (4,'苏轼',26);

-- 创建订单表orderlist
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
	number VARCHAR(30),			-- 订单编号
	uid INT,    				-- 外键字段
	-- 一个用户可以有多个订单，一对多关系。在订单表中创建外键，关联用户表
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES users(id) 
);

-- 添加数据
INSERT INTO orderlist VALUES (1,'yulu001',1);
INSERT INTO orderlist VALUES (2,'yulu002',1);
INSERT INTO orderlist VALUES (3,'yulu003',2);
INSERT INTO orderlist VALUES (4,'yulu004',2);
INSERT INTO orderlist VALUES (5,'yulu005',3);
INSERT INTO orderlist VALUES (6,'yulu006',3);
INSERT INTO orderlist VALUES (7,'yulu007',NULL);



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

-- 创建商品表product
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	NAME VARCHAR(30),                    -- 商品名称
	cid INT, 			     -- 外键字段
	-- 一个商品分类有多个商品，一对多关系。在商品表中创建外键，关联分类表
	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);


-- 商品和用户为多对多的关系，创建一个中间表，将两者进行关联
CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
	uid INT, -- 外键字段。需要和用户表的主键产生关联
	pid INT, -- 外键字段。需要和商品表的主键产生关联
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES users(id),
	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);

-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);

2. 内连接查询

/*
	显示内连接
	标准语法：
		SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
-- 查询用户信息和对应的订单信息，起别名
SELECT * FROM USER u JOIN orderlist o ON u.id=o.uid;

-- 查询用户姓名，年龄。和订单编号
SELECT 
	u.name,
	u.age,
	o.number
FROM
	users u
JOIN
	orderlist o
ON
	u.id=o.uid;

/*
	隐式内连接
	标准语法：
		SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;
*/

-- 查询用户姓名，年龄。和订单编号
SELECT 
	u.name,
	u.age,
	o.number
FROM
	users u,
	orderlist o
WHERE
	o.uid=u.id;

3. 外连接查询

-- 左外连接：查询左表的全部数据，和左右两张表有交集部分的数据
/*
	左外连接
	标准语法：
		SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有用户信息，以及用户对应的订单信息
SELECT 
	u.*,
	o.number
FROM 
	users u
LEFT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;
	

-- 右外连接：查询右表的全部数据，和左右两张表有交集部分的数据
/*
	右外连接
	标准语法：
		SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有订单信息，以及订单所属的用户信息
SELECT 
	o.*,
	u.name
FROM
	users u
RIGHT OUTER JOIN
	orderlist o
ON
	u.id=o.uid;

4. 子查询

/*
	结果是单行单列的
	标准语法：
		SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);
*/
-- 查询年龄最高的用户姓名
SELECT MAX(age) FROM users;
SELECT NAME,age FROM users WHERE age=(SELECT MAX(age) FROM users);

/*
	结果是多行单列的
	标准语法：
		SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 
*/
-- 查询李白和杜甫的订单信息
SELECT id FROM users WHERE NAME IN ('李白','杜甫');

SELECT * FROM orderlist WHERE uid IN (SELECT id FROM users WHERE NAME IN ('李白','杜甫'));


/*
	结果是多行多列的
	标准语法：
		SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
*/
-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM orderlist WHERE id>4;

SELECT
	u.name,
	o.number
FROM
	users u,
	(SELECT * FROM orderlist WHERE id>4) o
WHERE
	u.id=o.uid;

5. 自关联查询

-- 创建员工表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 员工编号
	em_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);


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

7. 多表查询练习

-- 1.查询用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄  user表      订单编号 orderlist表
	条件：user.id=orderlist.uid
*/

SELECT 
	u.id,
	u.name,
	u.age,
	o.number
FROM
	users u,
	orderlist o
WHERE
	u.id=o.uid;
	
-- 2.查询所有的用户。用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄  user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的用户，左外连接
*/
SELECT 
	u.*,
	o.number
FROM
	users u
LEFT JOIN
	orderlist o
ON
	o.uid=u.id;
		

-- 3.查询所有的订单。用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid
	查询所有的订单，右外连接
*/
SELECT
	o.number,
	u.*
FROM
	orderlist o
LEFT JOIN
	users u
ON 
	u.id=o.uid;


-- 4.查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表    订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.age > 23
*/
-- 方法一：
SELECT * FROM users WHERE age>23;

SELECT 
	u.id,
	u.name,
	u.age,
	o.number
FROM
	(SELECT * FROM users WHERE age>23) u,
	orderlist o
WHERE 
	u.id=o.uid;
	
-- 方法二：
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	users u,
	orderlist o
WHERE
	u.id=o.uid
	AND
	u.age > 23;	


-- 5.查询李白和杜甫用户的信息。显示用户的编号、姓名、年龄。订单编号
/*
分析
	用户的编号、姓名、年龄 user表   订单编号 orderlist表
	条件：user.id=orderlist.uid AND user.name IN ('张三','李四')
*/
-- 方法一：
SELECT * FROM users WHERE NAME IN ('李白','杜甫');
SELECT 
	u.id,
	u.name,
	u.age,
	o.number
FROM
	(SELECT * FROM users WHERE NAME IN ('李白','杜甫')) u,
	orderlist o
WHERE
	u.id=o.uid;

-- 方法二：
SELECT
	u.id,
	u.name,
	u.age,
	o.number
FROM
	users u,
	orderlist o
WHERE
	u.id=o.uid
	AND
	u.name IN ('李白','杜甫');

-- 6.查询商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
*/
SELECT 
	c.id,
	c.name,
	p.name
FROM
	category c,
	product p
WHERE
	c.id=p.cid;

-- 7.查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称 category表    商品名称 product表
	条件：category.id=product.cid
	查询所有的商品分类，左外连接
*/
SELECT 
	c.id,
	c.name,
	p.name
FROM
	category c
LEFT JOIN
	product p
ON
	c.id=p.cid;	
	
-- 8.查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
/*
分析
	商品分类的编号、分类名称  category表   商品名称 product表
	条件：category.id=product.cid
	查询所有的商品信息，右外连接
*/
SELECT 
	c.id,
	c.name,
	p.name
FROM
	product p
LEFT JOIN
	category c
ON
	c.id=p.cid;
	
	
-- 9.查询所有的用户和该用户能查看的所有的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析
	用户的编号、姓名、年龄 user表   商品名称 product表    中间表 us_pro
	条件：us_pro.uid=user.id AND us_pro.pid=product.id
*/
SELECT 
	u.id,
	u.name,
	u.age,
	p.name
FROM
	users u,
	us_pro up,
	product p
WHERE
	up.pid=p.id
	AND
	up.uid=u.id;
	
-- 10.查询李白和杜甫这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
/*
分析
	用户的编号、姓名、年龄 user表   商品名称 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
	users u,
	us_pro up,
	product p
WHERE
	up.pid=p.id
	AND
	up.uid=u.id
	AND
	u.name IN ('李白','杜甫');

如有错误欢迎留言评论，2021年7月10日  羽露风 

羽露风
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
mysql子查询外部查什么 内部查什么,MySQL子查询
weixin_26643379的博客
03-23 852
MySQL子查询MySQL中的子查询是一个查询，它嵌套在另一个SQL查询中，并与各种运算符一起嵌入SELECT，INSERT，UPDATE或DELETE语句。我们还可以将子查询与另一个子查询嵌套在一起。子查询称为内部查询，而包含子查询的查询称为外部查询。首先执行内部查询，将结果提供给外部查询，然后执行主/外部查询。 MySQL允许我们在任何地方使用子查询，但是必须在括号内将其关闭。 MySQL也将...
antdPro - ProTable 实现两个选择框联动效果
名人卷耶
06-08 3231
antdPro实现选择框联动效果
mysql子查询
qq_53318060的博客
03-27 754
外键约束 create table dept( -- 主表 deptno varchar(20) primary key, -- 部门编号 name varchar(20) -- 部门名字 ); create table emp( -- 从表 eid varchar(20) primary key, ename varchar(20), age int, dept_id varchar(20), -- 员工所属部门 constrai
MySQL 多表查询（内连接，左外连接，右外连接）
weixin_59131972的博客
12-04 1731
合并具有相同列而且，表中的数据至少要两行以上，结果集中不包含，一个表与另一个表不匹配的行 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或者右表中不匹配的行；左外连接：俩个表中在连接过程中，除了返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接称为左外连接；从sql优化的角度有，建议多表查询时，每个字段前面都指明其所在的表。
MySQL中多表查询、表连接（内连接和外连接）
心皿月的博客
08-28 4845
表与表之间的关系：一对一、一对多、多对多；笛卡尔积；避免全笛卡尔积、消除笛卡尔积；表与表之间的连接：内连接：where关键字和inner join in两种；外连接：左连接left outer join、右连接right join。子查询：指的是在查询语句里面嵌套查询语句，子查询语句放在where条件中，子查询的select语句总是使用圆括号括起来。 .........
MySQL查询优化:用子查询代替非主键连接查询实例介绍
09-10
对多的两张表，一般是一张表的外键关联到另一个表的主键，接下来为大家介绍下用子查询代替非主键连接查询，感兴趣的朋友可以参考下哈，希望对你有所帮助
MYSQL基础：多表关联查询.docx
最新发布
08-08
在MySQL数据库中，多表关联查询是一种强大的技术，可以通过连接多个表来检索和组合数据，从而获得更...本文将介绍四种常见的多表关联查询类型：交叉连接查询、内连接查询、外连接查询和子查询，并提供相关的SQL代码示例
mysql连接查询、联合查询、子查询原理与用法实例详解
12-14
本文实例讲述了mysql连接查询、联合查询、子查询原理与用法。分享给大家供大家参考，具体如下： ... 外连接：与内连接不同的是不管匹配符不符合都保留，根据外连接连接方式来决定保留哪张表，比如保留左表的
【数据库】作业7——SQL练习4 – SELECT（连接查询、嵌套查询）
12-14
4.多表连接 嵌套查询 1.带有IN谓语的子查询 3.带有比较运算符的子查询 3.带有ANY（SOME）或ALL谓语的子查询 总结： 连接查询   若一个查询同时涉及两个以上的表，则称之为链接查询。 链接查询是关系数据库中最主
（四）MySQL学习笔记——多表设计、多表查询、多表查询练习题
mbytes的博客
03-12 972
一、多表设计 多表设计即上一章的外键约束，通过外键约束将表之间建立联系，分为一对一、一对多、多对多等三种。 1.一对一设计 例如用户表person和身份证表card，用户表中的任意一条数据只能对应身份证表中的一条数据。 实现：在其中一张表建立外键，关联另一张表的主键。 CREATE DATABASE db5; USE db5; -- 创建person表 CREATE TABLE person( id INT PRIMARY KEY AUTO_INCREMENT, -- 用户id 唯一且非空
使用mysql内连接查询年龄_Mysql的连表查询
weixin_35645813的博客
01-27 657
若一个查询同时涉及到两个以上的表，称为连表查询准备表create table department(id int auto_increment PRIMARY KEY,name varchar(20));departmentcreate table employee(id int primary key auto_increment,name varchar(20),sex enum('male'...
MySQL基础练习03
weixin_43842051的博客
12-08 201
CREATE DATABASE db3; USE db3; #准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部'); # 创建员工表 CREATE TABLE ...
sql的子查询和外部查询以及关联相关问题！
qq_43401942的博客
07-18 1336
sql，关联，数据库
MySQL数据库（详解）
辰伏的博客
01-10 1741
MySQL 1. 基础 1.1 数据库概念 学习数据库是为了更方便的对开发中的数据进行管理 用于存储和管理数据的仓库 英文单词为DataBase，简称DB 它的存储空间很大，可以存放百万条、千万条、上亿条数据 使用一种统一的方式操作数据库——SQL 1.2 MySQL介绍 MySQL是一个最流行的关系型数据库管理系统之一，由瑞典MySQL AB公司开发，后被Oracle公司收购 关系型数据库是将数据保存在不同的数据表中，而不是将所有数据放在一个大仓库内，而且表与表之间还可以有关联关系，这样就提高了访问
antdesignpro ProTable 搜索模式自定义搜索字段
热门推荐
qq_23064501的博客
11-12 1万+
const [params, setParams] = useState({}) const handleChange = (value) => { setParams({ "device_id": value }) console.log(value); // { value: "lucy", key: "lucy", label: "Lucy (101)" } } { title: '设备', dataIndex: 'devi...
MySQL多表查询（内连接和外连接）
CjhLoveAndroid的博客
12-18 817
新建两张表a_table和b_table，如果把表格当作一个集合，那么表格中的记录就是集合中的一个元素。 表a： 表b： 1.内连接（只有一种场景） select a.,b. from a_table a inner join b_table b on a.a_id=b.b_id; 或 select a.,b. from a_table a join b_table b on a.a_id=b...
mysql 04（多表链接查询、自关联查询、子查询、多表操作、防止sql注入）
这里的分享，都是干货
11-26 191
1、内链接 inner join、inner join…on…（两个表的交集） select * from students inner join classes; # 查询students表里对应的班级 # inner join..on..取的是两个表的交集 select * from students inner join classes on students.cls_id=classes....
多表查询，内连接（隐式、显示），外连接（左外、右外、基础表）
lx__angel的博客
01-12 9437
from子句进行多表查询 例如：查询分数信息，显示玩家昵称、游戏名称和分数 select user_name as '昵称', gname as '游戏名称', score as '分数' from users,games,scores where users.user_qq=scores.user_qq and games.gno=scores.gno
MySQL 多表查询 内连接和外连接
weixin_46441425的博客
02-02 235
一、笛卡尔积 1、有两个集合A和B，取这两个集合的所有组成情况。 2、要完成多表查询，需要消除无用的数据。 二、分类 1、内连接查询： 1、隐式内连接：使用where条件消除无用的数据 2、显示内连接 2、外连接查询： 3、子查询： ...
数据库多表查询子连接和左连接
07-15
子连接和左连接都是多表查询中常用的连接方式，用于将多个表的数据进行关联查询。 子连接（Subquery）是在一个查询中嵌套另一个查询，将内部查询的结果作为外部查询的条件之一。子连接可以用于在主查询中根据内部...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

羽露风

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

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

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

打赏作者

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

抵扣说明：

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

余额充值