MySQL学习笔记(5)——JOIN联表查询，自连接查询，分页和排序，子查询与嵌套查询

最新推荐文章于 2022-09-04 19:33:02 发布

小蜉蝣星蔚

最新推荐文章于 2022-09-04 19:33:02 发布

阅读量708

点赞数 2

分类专栏： # 数据库文章标签：数据库 mysql sql 查询排序分页

本文链接：https://blog.csdn.net/qq_42391904/article/details/105746398

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

MySQL学习笔记(5)——JOIN联表查询，自连接查询，分页和排序，子查询与嵌套查询

文章目录

MySQL学习笔记(5)——JOIN联表查询，自连接查询，分页和排序，子查询与嵌套查询

0.准备

参考：https://www.bilibili.com/video/BV1NJ411J79W?p=18

数据表

以下SQL案例均已该建表语句为基础：

major专业表：majorid专业ID，majorname专业名称，number专业人数

student学生表：id学号，name姓名，major专业名称

-- ----------------------------
-- Table structure for `major`
-- ----------------------------
DROP TABLE IF EXISTS `major`;
CREATE TABLE `major` (
  `majorid` int(8) unsigned NOT NULL AUTO_INCREMENT ,
  `majorname` varchar(50) DEFAULT NULL,
  `number` int(4) unsigned DEFAULT '0',
  PRIMARY KEY (`majorid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(8) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `major` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

连接查询模型

demo

完整的select语法规则

SELECT [ALL | DISTINCT]
{ * | table.* | [table.fieal1[as alias1][,table.field2[as aliias2]][,...] ]}
FROM table_name [as table_alias]
   [left | right | inner JOIN table_name2]  -- 联合查询  
   [WHERE ...]  -- 指定where子句条件
   [GROUP BY ...] --指定结果按照那几个字段来分组
   [HAVING] -- 过滤分组的记录必须满足的次要条件
   [ORDER BY ...] -- 指定查询记录按照哟个或多个条件排序
   [LIMIT{[offset,]row_count | row_countOFFSET offset}]; -- 指定查询记录从那条到那条(起始位置)
   
   -- []代表可选  {}代表必选

顺序：JOIN , WHERE , GROUP BY , HAVING , ORDER BY , LIMIT.必须严格按照顺序，否则SQL执行会出错误。

可以看到联合查询使用需要放在WHERE之前。

1.简单多表查询

使用场景：需要查询的子段源自多个表⚡️

例如：查询学号为1的学生姓名和专业名称以及所属专业的人数

学生姓名和专业名称以及所属专业的人数属于不同的表，来自student和major这两张表。

以下针对该例子做sql的推导过程：

查询学号为1的学生姓名和专业名称以及所属专业的人数

首先明确要查询的字段和来自那些表：

学生姓名(student表的name字段)
专业名称(student表的majorname字段，major表的majorname字段)
所属专业的人数(major表的number字段)

1.1 笛卡尔积

首先得明确两表连接的结果为笛卡尔积：

**笛卡尔积-**百度百科

SELECT * FROM student,major;

简单多表查询笛卡尔积

1.2 从笛卡尔积种查询所需要数据

两表有联系得字段是专业名称，因此可加个WHERE条件。

SELECT *
FROM student,major
WHERE student.majorname = major.majorname;

由于两表字段名一样，MYSQL自动为后面那个majorname取别名为majorname1

简单多表查询推导2

此外还要求学号为1，只需要姓名，专业名，所属专业人数：

-- 查询学号为1的学生姓名和专业名称以及所属专业的人数
SELECT student.`name`,student.majorname,major.number 
FROM student,major
WHERE student.majorname = major.majorname AND id=1;

-- 使用AS为表取别名  AS可以省略
SELECT s.id,s.`name`,s.majorname,m.number 
FROM student AS s,major AS m
WHERE s.majorname = m.majorname AND id=1;

-- 使用AS取别名 省略AS
SELECT s.id,s.`name`,s.majorname,m.number 
FROM student  s,major  m
WHERE s.majorname = m.majorname AND id=1;

FROM student , major 也可以写成 FROM student INNER JOIN major 官方更推荐后者，具体差异可自行搜索。

SELECT s.id,s.`name`,s.majorname,m.number 
FROM student  s INNER JOIN major  m;
WHERE s.majorname = m.majorname AND id=1;

-- INNER JOIN可简写为JOIN

查询结果都是以下图片所示：
简单多表查询

注意：

其中name字段使用 ``符号引起来，是为了防止冲突，其实所有列名都可以用其引起来。
上述sql种其实只为majorname标注是哪个表的就行(s.majorname),其他的字段不加也能查询出结果，但是阿里巴巴Java开发规范中有如下建议：

**5.【强制】**对于数据库中表记录的查询和变更，只要涉及多个表，都需要加表名（或别名）进行限定。
说明：多表join后作为条件进行查询记录、更新记录、删除记录时，如果出现没有限定表名（或别名）的列名在多个表中均有存在，那么会抛出异常。
正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

**反例：**在某业务中，由于多表关联查询语句没有加表名（或别名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句全部出现出1052异常：Column ‘name’ in field list is ambiguous，导致票务交易下跌。

2.JOIN 连接查询（左中右）

三种基本JOIN对比 (图源百度)

三种基本JOIN

上图中连接结果分别如下：

两表左联接LEFTJOIN 结果为绿色部分 (左表内容除去与右表相同的部分)
两表内联接INEERJOIN 结果为红色部分 (左表与右表相同的部分)
两表右联接INEERJOIN 结果为蓝色部分 (右表内容除去与左表相同的部分)

操作	描述
INNER JOIN	如果表中至少有一个匹配，就返回行
LEFT JOIN	会从左表中返回所有的值，即使在右表中配有匹配
RIGHT JOIN	会从右表中返回所有的值，即使在左表中配有匹配

总之：以JOIN关键字前面的表为标准

-- 例如：查询学号为1的学生姓名和专业名称以及所属专业的人数
SELECT s.id, s.`name`, s.majorname, m.number 
FROM student s INNER JOIN major m 
ON s.majorname = m.majorname AND id=1;

SELECT s.id, s.`name`, s.majorname, m.number 
FROM student s LEFT JOIN major m 
ON s.majorname = m.majorname AND id=1;
 
SELECT s.id, s.`name`, s.majorname, m.number 
FROM student s RIGHT JOIN major m 
ON s.majorname = m.majorname AND id=1;

同样的例子，看看INNER JOIN，LEFT JOIN，RIGHT JOIN的区别：

INNER JOIN：

INNER JOIN

LEFT JOIN：(会从左表中返回所有的值，即使在右表中配有匹配)

LEFT JOIN

RIGHT JOIN ：会从右表中返回所有的值，即使在左表中配有匹配

RIGHT JOIN

只是在这个需要下使用内连接（INNER JOIN）是正确的，其他两个只是展示其连接效果。

建议刷题巩固：LeetCode-DataBase

on与where的区别

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

SQL中ON和WHERE的区别(转)

对于JOIN参与的表的关联操作，如果需要不满足连接条件的行也在我们的查询范围内的话，我们就必需把连接条件放在ON后面，而不能放在WHERE后面，如果我们把连接条件放在了WHERE后面，那幺所有的LEFT,RIGHT,等这些操作将不起任何作用，对于这种情况，它的效果就完全等同于INNER连接。对于那些不影响选择行的条件，放在ON或者WHERE后面就可以。
记住：所有的连接条件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT关联将作为摆设，而不起任何作用。

除了INNER JOIN，LEFT JOIN，RIGHT JOIN之外还有其他几种JOIN。

七种JOIN理论及其SQL (图源百度)：

7join

3.自连接查询(了解)

核心⚡️ :将一张表拆成两张一样的表即可

SQL自连接查询可以避免的吗？我发现很多自连接查询都可以拆分的，有什么场景是一定需要使用自连接才能解决的吗？

子查询一般的应用场景： 数据为树型结构,如子菜单查询父菜单，如省市区这样的，如多文件夹里的文件关系等，都在一张表里查询

实例:有以下文件结构file下有c,java,Python.然后下面又有各自的文件。

file

在数据表中有id，parent_id,name三个字段file的id规定为1，且在表中省略了file，建表语句和数据具体如下：

-- ----------------------------
-- Table structure for `file`
-- ----------------------------
DROP TABLE IF EXISTS `file`;
CREATE TABLE `file` (
  `id` int(8) NOT NULL,
  `parent_id` int(8) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of file
-- ----------------------------
INSERT INTO `file` VALUES ('2', '1', 'Java');
INSERT INTO `file` VALUES ('3', '1', 'Python');
INSERT INTO `file` VALUES ('4', '1', 'C');
INSERT INTO `file` VALUES ('5', '2', 'Spring');
INSERT INTO `file` VALUES ('6', '2', 'SpringMVC');
INSERT INTO `file` VALUES ('7', '3', 'Flask');
INSERT INTO `file` VALUES ('8', '3', 'TensorFlow');
INSERT INTO `file` VALUES ('9', '4', 'Tinyhttpd');
INSERT INTO `file` VALUES ('10', '4', 'CMockery');

filedata

这里使用子查询查询父文件夹和子文件夹的关系：即父文件子文件

SELECT f1.name,f2.name FROM file f1,file f2 WHERE f1.id=f2.parent_id;

查询结果如下：

自查询

4.分页和排序

更具完整的select语法规则可知顺序：JOIN , WHERE , GROUP BY , HAVING , ORDER BY , LIMIT.

分页 limit
排序 order by

4.1 分页

分页目的：缓解数据库压力(数据库)，提升用户体验(前端显示)

LIMIT 起始值,每页的大小

-- 分页  每页3个
SELECT majorname,number FROM major LIMIT 2;  -- 前两个条数据
SELECT majorname,number FROM major LIMIT 0,3;  -- 第一页  第一1到第三3条数据
SELECT majorname,number FROM major LIMIT 3,3;  -- 第二页  第一4到第一6条数据
SELECT majorname,number FROM major LIMIT 6,3;  -- 第三页  第一7到第一9条数据

c参数：n：当前页
pagesize：页面大小
(n-1)*pageSize：起始值
总页数=数据总数/页面大小

公式：SELECT 字段 FROM 表 ... LIMIT (n-1)*pageSize,pageSize

4.2 排序

升序 ORDER BY 字段 ASC
降序 ORDER BY 字段 DESC

-- 按照专业人数个数升序(降序)查找专业名称，专业人数


SELECT majorname,number FROM major ORDER BY number ASC; -- 升序 

SELECT majorname,number FROM major ORDER BY number DESC;-- 降序

4.3 分页排序组合应用

某一字段最大值：ORDER BY 字段 DESC LIMIT 1; 就是降序，页面大小为1

某一字段最小值：ORDER BY 字段 ASC LIMIT 1 ; 就是升序，页面大小为1

某一字段第二大的值 : ORDER BY 字段 DESC LIMIT 1,1; 就是降序分页，从1开始(默认是0)，页面大小为1

第二小同理。

5.子查询与嵌套查询

子查询本质：WHERE子句中嵌套一个子查询的语句

子查询就是括号中的查询

子查询与嵌套查询：虽不是一个概念，但是相关的概念

嵌套查询
是包括外层主查询和内层子查询的查询。
子查询还可以包括子查版询，可以是多层子查询，主查询也可以包括多个子查询。
子查询
就是括号中的查询。

看出来了吧：子查询是嵌套查询必不可少的组成部份。

例如下面的SQL例子中：括号内的SLELECT语句是子查询，包含子查询的整条SELLECT语句是嵌套查询。

-- 使用子查询
-- 例如：查询专业人数最多的专业下的学号从小到大排在前2的学号，姓名，专业名称
-- 拆成两步：1.查询专业人数最多的专业  2.该专业下的学号从小到大排在前2的学号，姓名，专业名称

-- 1.查询专业人数最多的专业  按照number降序 取一条数据 所以是number最大的那条数据 
SELECT majorname FROM major order by number DESC limit 1;

-- 2.该专业下的学号从小到大排在前2的学号，姓名，专业名称
-- 按照id升序  前两条就是从小到大前二学号的数据了，在选择学号，姓名，专业名称这三个字段即可
SELECT id,`name`,majorname 
FROM student
WHERE majorname=(SELECT majorname FROM major ORDER BY major.number DESC LIMIT 1)
ORDER BY id ASC LIMIT 2;

小蜉蝣星蔚

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记(5)——JOIN联表查询，自连接查询，分页和排序，子查询与嵌套查询

MySQL学习笔记(5)——JOIN联表查询，自连接查询，分页和排序，子查询与嵌套查询0.准备参考：https://www.bilibili.com/video/BV1NJ411J79W?p=18数据表以下SQL案例均已该建表语句为基础：major专业表：majorid专业ID，majorname专业名称，number专业人数student学生表：id学号，name姓名，major...
复制链接

扫一扫

专栏目录