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;
完整的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
此外还要求学号为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对比 (图源百度)
上图中连接结果分别如下:
- 两表
左联接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:
LEFT JOIN:(会从左表中返回所有的值,即使在右表中配有匹配)
RIGHT JOIN :会从右表中返回所有的值,即使在左表中配有匹配
只是在这个需要下使用内连接(INNER JOIN)是正确的,其他两个只是展示其连接效果。
建议刷题巩固:LeetCode-DataBase
on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉对于JOIN参与的表的关联操作,如果需要不满足连接条件的行也在我们的查询范围内的话,我们就必需把连接条件放在ON后面,而不能放在WHERE后面,如果我们把连接条件放在了WHERE后面,那幺所有的LEFT,RIGHT,等这些操作将不起任何作用,对于这种情况,它的效果就完全等同于INNER连接。对于那些不影响选择行的条件,放在ON或者WHERE后面就可以。
记住:所有的连接条件都必需要放在ON后面,不然前面的所有LEFT,和RIGHT关联将作为摆设,而不起任何作用。
除了INNER JOIN,LEFT JOIN,RIGHT JOIN之外还有其他几种JOIN。
七种JOIN理论及其SQL (图源百度):
3.自连接查询(了解)
核心⚡️ :将一张表拆成两张一样的表即可
SQL自连接查询可以避免的吗?我发现很多自连接查询都可以拆分的,有什么场景是一定需要使用自连接才能解决的吗?
子查询一般的应用场景: 数据为树型结构,如子菜单查询父菜单, 如省市区这样的,如多文件夹里的文件关系等,都在一张表里查询
实例:有以下文件结构file下有c,java,Python.然后下面又有各自的文件。
在数据表中有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');
这里使用子查询 查询父文件夹和子文件夹的关系:即 父文件 子文件
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;