mysql 子连接_MySQL-子查询及连接

最新推荐文章于 2022-10-10 17:03:50 发布
最新推荐文章于 2022-10-10 17:03:50 发布
阅读量194 收藏
点赞数
文章标签： mysql 子连接
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_35275781/article/details/114790410
版权

子查询

当一个查询是另一个查询的条件时，称之为子查询。

子查询必须用括号括起来。

由比较运算符引发的子查询：

先新建一个数据表

CREATE TABLE students (

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20) NOT NULL,

class_name VARCHAR(20) NOT NULL,

teacher_name VARCHAR(20) NOT NULL,

score SMALLINT UNSIGNED

);

-- 插入几条数据

INSERT INTO students VALUES

(NULL,'张山','网络18-1班','陈老师',78),

(NULL,'王五','微机18-1班','张老师',85),

(NULL,'赵六','设计18-1班','林老师',66),

(NULL,'陈七','网络18-1班','陈老师',90),

(NULL,'阿狗','电子18-1班','王老师',88),

(NULL,'狗腿','电子18-1班','王老师',95),

(NULL,'陈七','网络18-1班','陈老师',90),

(NULL,'小咪','设计18-1班','林老师',98),

(NULL,'富贵','微机18-1班','张老师',83),

(NULL,'王五','微机18-1班','张老师',85);

现在查出他们的平均成绩

SELECT ROUND(AVG(score),1) FROM students;

+---------------------+

| ROUND(AVG(score),1) |

+---------------------+

| 85.8 |

+---------------------+

查出比平均成绩高的学生

SELECT * FROM students WHERE score > (SELECT ROUND(AVG(score),1) FROM students);

+----+--------+---------------+--------------+-------+

| id | name | class_name | teacher_name | score |

+----+--------+---------------+--------------+-------+

| 4 | 陈七 | 网络18-1班 | 陈老师 | 90 |

| 5 | 阿狗 | 电子18-1班 | 王老师 | 88 |

| 6 | 狗腿 | 电子18-1班 | 王老师 | 95 |

| 7 | 陈七 | 网络18-1班 | 陈老师 | 90 |

| 8 | 小咪 | 设计18-1班 | 林老师 | 98 |

+----+--------+---------------+--------------+-------+

ANY SOME ALL关键字

对子查询中返回的多行结果进行处理

···

ANY

SOME

ALL

>、>=

最小值

最小值

最大值

最大值

最大值

最小值

=

任意值

任意值

<>、!=

最大值

使用： 查询

-- 先查询下林老师学生的分数

SELECT score FROM students WHERE teacher_name = '林老师';

+-------+

| score |

+-------+

| 66 |

| 98 |

+-------+

-- 找出比林老师学生分数高的学生

SELECT * FROM students

WHERE score > (SELECT score FROM students WHERE teacher_name = '林老师');

-- 不使用关键字时会报错

-- ERROR 1242 (21000): Subquery returns more than 1 row

-- 返回结果超过一行，不知道该跟那个做对比了

-- 使用关键字, 图标中可以看到，使用 ‘>’ ANY会跟返回结果中值最小的对比 最小的分数为66

SELECT * FROM students

WHERE score > ANY (SELECT score FROM students WHERE teacher_name = '林老师');

+----+--------+---------------+--------------+-------+

| id | name | class_name | teacher_name | score |

+----+--------+---------------+--------------+-------+

| 1 | 张山 | 网络18-1班 | 陈老师 | 78 |

| 2 | 王五 | 微机18-1班 | 张老师 | 85 |

| 4 | 陈七 | 网络18-1班 | 陈老师 | 90 |

| 5 | 阿狗 | 电子18-1班 | 王老师 | 88 |

| 6 | 狗腿 | 电子18-1班 | 王老师 | 95 |

| 7 | 陈七 | 网络18-1班 | 陈老师 | 90 |

| 8 | 小咪 | 设计18-1班 | 林老师 | 98 |

| 9 | 富贵 | 微机18-1班 | 张老师 | 83 |

| 10 | 王五 | 微机18-1班 | 张老师 | 85 |

+----+--------+---------------+--------------+-------+

-- 使用 ‘

SELECT * FROM students

WHERE score < ANY (SELECT score FROM students WHERE teacher_name = '林老师');

+----+--------+---------------+--------------+-------+

| id | name | class_name | teacher_name | score |

+----+--------+---------------+--------------+-------+

| 1 | 张山 | 网络18-1班 | 陈老师 | 78 |

| 2 | 王五 | 微机18-1班 | 张老师 | 85 |

| 3 | 赵六 | 设计18-1班 | 林老师 | 66 |

| 4 | 陈七 | 网络18-1班 | 陈老师 | 90 |

| 5 | 阿狗 | 电子18-1班 | 王老师 | 88 |

| 6 | 狗腿 | 电子18-1班 | 王老师 | 95 |

| 7 | 陈七 | 网络18-1班 | 陈老师 | 90 |

| 9 | 富贵 | 微机18-1班 | 张老师 | 83 |

| 10 | 王五 | 微机18-1班 | 张老师 | 85 |

+----+--------+---------------+--------------+-------+

多表更新

-- 新建班级表

CREATE TABLE classes(

class_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

class_name VARCHAR(20) NOT NULL,

teacher_name VARCHAR(20) NOT NULL

);

/

-- 将学生表的班级及老师写入班级表

INSERT classes (class_name,teacher_name)

SELECT class_name,teacher_name

FROM students GROUP BY class_name;

-- 查询结果

SELECT * FROM classes;

+----------+---------------+--------------+

| class_id | class_name | teacher_name |

+----------+---------------+--------------+

| 1 | 微机18-1班 | 张老师 |

| 2 | 电子18-1班 | 王老师 |

| 3 | 网络18-1班 | 陈老师 |

| 4 | 设计18-1班 | 林老师 |

+----------+---------------+--------------+

-- 正文

-- 将学生表班级名更改成班级id

UPDATE students INNER JOIN (SELECT class_id,class_name FROM classes ) AS cl

ON students.class_name = cl.class_name SET students.class_name = cl.class_id;

-- 查看结果

+----+--------+------------+--------------+-------+

| id | name | class_name | teacher_name | score |

+----+--------+------------+--------------+-------+

| 1 | 张山 | 1 | 陈老师 | 78 |

| 2 | 王五 | 2 | 张老师 | 85 |

| 3 | 赵六 | 3 | 林老师 | 66 |

| 4 | 陈七 | 1 | 陈老师 | 90 |

| 5 | 阿狗 | 4 | 王老师 | 88 |

| 6 | 狗腿 | 4 | 王老师 | 95 |

| 7 | 陈七 | 1 | 陈老师 | 90 |

| 8 | 小咪 | 3 | 林老师 | 98 |

| 9 | 富贵 | 2 | 张老师 | 83 |

| 10 | 王五 | 2 | 张老师 | 85 |

+----+--------+------------+--------------+-------+

-- 另一种方法

-- 建表时直接写入

CREATE TABLE teachers (

teacher_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

teacher_name VARCHAR(20) NOT NULL

)

SELECT teacher_name FROM students GROUP BY teacher_name;

-- 然后更新

UPDATE students INNER JOIN (SELECT * FROM teachers)

AS t ON students.teacher_name = t.teacher_name

SET students.teacher_name = t.teacher_id;

-- 最后更改下学生表字段名

ALTER TABLE students CHANGE class_name class_id SMALLINT

UNSIGNED NOT NULL;

ALTER TABLE students CHANGE teacher_name teacher_id SMALLINT UNSIGNED NOT NULL;

连接

内连接的连接查询结果集中仅包含满足条件的行，外连接(左、右)的连接查询结果集中既包含那些满足条件的行，还包含其中某个表的全部行

1、内连接

-- 写入两条数据

INSERT INTO students VALUES (NULL,'张伟','10','12',100),

(NULL,'刘六','12','10',100);

INSERT INTO classes VALUE (NULL,'机电18-1班','潘老师');

-- 连接查询

-- 显示班级

SELECT id,name,cl.class_name FROM students

INNER JOIN classes AS cl ON students.class_id = cl.class_id;

+----+--------+---------------+

| id | name | class_name |

+----+--------+---------------+

| 1 | 张山 | 网络18-1班 |

| 2 | 王五 | 微机18-1班 |

| 3 | 赵六 | 设计18-1班 |

| 4 | 陈七 | 网络18-1班 |

| 5 | 阿狗 | 电子18-1班 |

| 6 | 狗腿 | 电子18-1班 |

| 7 | 陈七 | 网络18-1班 |

| 8 | 小咪 | 设计18-1班 |

| 9 | 富贵 | 微机18-1班 |

| 10 | 王五 | 微机18-1班 |

+----+--------+---------------+

-- 里面没有张伟跟刘六，是因为他们不符合条件

-- 张伟和刘六的class_id 为10，12

+----------+---------------+--------------+

| class_id | class_name | teacher_name |

+----------+---------------+--------------+

| 1 | 微机18-1班 | 张老师 |

| 2 | 电子18-1班 | 王老师 |

| 3 | 网络18-1班 | 陈老师 |

| 4 | 设计18-1班 | 林老师 |

| 5 | 机电18-1班 | 潘老师 |

+----------+---------------+--------------+

-- 表里是没有这两个class_id的，所以不会显示

-- 同理 没有学生的class_id为5，所以也不符合条件

2、左连接

-- 还是查询班级

SELECT id,name,cl.class_name FROM students

LEFT JOIN classes AS cl ON students.class_id = cl.class_id;

+----+--------+---------------+

| id | name | class_name |

+----+--------+---------------+

| 2 | 王五 | 微机18-1班 |

| 9 | 富贵 | 微机18-1班 |

| 10 | 王五 | 微机18-1班 |

| 5 | 阿狗 | 电子18-1班 |

| 6 | 狗腿 | 电子18-1班 |

| 1 | 张山 | 网络18-1班 |

| 4 | 陈七 | 网络18-1班 |

| 7 | 陈七 | 网络18-1班 |

| 3 | 赵六 | 设计18-1班 |

| 8 | 小咪 | 设计18-1班 |

| 11 | 张伟 | NULL |

| 12 | 刘六 | NULL |

+----+--------+---------------+

-- 左连接说明JOIN左侧的所有记录均会被显示，无论其在右侧是否得到匹配

-- 刘六 张伟 没有班级，会显示为NULL

3、右连接

SELECT id,name,cl.class_name FROM students

RIGHT JOIN classes AS cl ON students.class_id = cl.class_id;

+------+--------+---------------+

| id | name | class_name |

+------+--------+---------------+

| 1 | 张山 | 网络18-1班 |

| 2 | 王五 | 微机18-1班 |

| 3 | 赵六 | 设计18-1班 |

| 4 | 陈七 | 网络18-1班 |

| 5 | 阿狗 | 电子18-1班 |

| 6 | 狗腿 | 电子18-1班 |

| 7 | 陈七 | 网络18-1班 |

| 8 | 小咪 | 设计18-1班 |

| 9 | 富贵 | 微机18-1班 |

| 10 | 王五 | 微机18-1班 |

| NULL | NULL | 机电18-1班 |

+------+--------+---------------+

-- 右连接说明JOIN右侧的所有记录均会被显示，无论其在左侧是否得到匹配

-- 机电18-1班没有学生，会显示为NULL

无限级分类

-- 新建省份城市表

CREATE TABLE areas (

id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

area_name VARCHAR(30) NOT NULL,

parent_id SMALLINT UNSIGNED NOT NULL

);

-- 写入一些数据

INSERT INTO areas VALUES

(NULL,'广东省',0),

(NULL,'江苏省',0),

(NULL,'山东省',0),

(NULL,'浙江省',0),

(NULL,'河南省',0),

(NULL,'广州市',1),

(NULL,'深圳市',1),

(NULL,'南京市',2),

(NULL,'济南市',3),

(NULL,'青岛市',3),

(NULL,'杭州市',4),

(NULL,'宁波市',4),

(NULL,'郑州市',5);

+----+-----------+-----------+

| id | area_name | parent_id |

+----+-----------+-----------+

| 1 | 广东省 | 0 |

| 2 | 江苏省 | 0 |

| 3 | 山东省 | 0 |

| 4 | 浙江省 | 0 |

| 5 | 河南省 | 0 |

| 6 | 广州市 | 1 |

| 7 | 深圳市 | 1 |

| 8 | 南京市 | 2 |

| 9 | 济南市 | 3 |

| 10 | 青岛市 | 3 |

| 11 | 杭州市 | 4 |

| 12 | 宁波市 | 4 |

| 13 | 郑州市 | 5 |

+----+-----------+-----------+

-- 查询城市所属省

SELECT a.id,a.area_name,p.area_name AS city

FROM areas AS a LEFT JOIN (SELECT * FROM areas ) AS p ON a.id = p.parent_id;

+----+-----------+-----------+

| id | area_name | city |

+----+-----------+-----------+

| 1 | 广东省 | 广州市 |

| 1 | 广东省 | 深圳市 |

| 2 | 江苏省 | 南京市 |

| 3 | 山东省 | 济南市 |

| 3 | 山东省 | 青岛市 |

| 4 | 浙江省 | 杭州市 |

| 4 | 浙江省 | 宁波市 |

| 5 | 河南省 | 郑州市 |

| 6 | 广州市 | NULL |

| 7 | 深圳市 | NULL |

| 8 | 南京市 | NULL |

| 9 | 济南市 | NULL |

| 10 | 青岛市 | NULL |

| 11 | 杭州市 | NULL |

| 12 | 宁波市 | NULL |

| 13 | 郑州市 | NULL |

+----+-----------+-----------+

-- 内连接

SELECT a.id,a.area_name,p.area_name AS city

FROM areas AS a INNER JOIN (SELECT * FROM areas ) AS p ON a.id = p.parent_id;

+----+-----------+-----------+

| id | area_name | city |

+----+-----------+-----------+

| 1 | 广东省 | 广州市 |

| 1 | 广东省 | 深圳市 |

| 2 | 江苏省 | 南京市 |

| 3 | 山东省 | 济南市 |

| 3 | 山东省 | 青岛市 |

| 4 | 浙江省 | 杭州市 |

| 4 | 浙江省 | 宁波市 |

| 5 | 河南省 | 郑州市 |

+----+-----------+-----------+

多表删除

-- 由于只有一张表，就用这一张演示，多表同理

-- 查看students

+----+--------+----------+------------+-------+

| id | name | class_id | teacher_id | score |

+----+--------+----------+------------+-------+

| 1 | 张山 | 3 | 4 | 78 |

| 2 | 王五 | 1 | 1 | 85 |

| 3 | 赵六 | 4 | 2 | 66 |

| 4 | 陈七 | 3 | 4 | 90 |

| 5 | 阿狗 | 2 | 3 | 88 |

| 6 | 狗腿 | 2 | 3 | 95 |

| 7 | 陈七 | 3 | 4 | 90 |

| 8 | 小咪 | 4 | 2 | 98 |

| 9 | 富贵 | 1 | 1 | 83 |

| 10 | 王五 | 1 | 1 | 85 |

| 11 | 张伟 | 10 | 12 | 100 |

| 12 | 刘六 | 12 | 10 | 100 |

+----+--------+----------+------------+-------+

-- 会发现有几个重复的

-- 查看重复的用户

SELECT * FROM students GROUP BY name HAVING COUNT(name) > 1;

+----+--------+----------+------------+-------+

| id | name | class_id | teacher_id | score |

+----+--------+----------+------------+-------+

| 2 | 王五 | 1 | 1 | 85 |

| 4 | 陈七 | 3 | 4 | 90 |

+----+--------+----------+------------+-------+

-- 接下来把存入时间晚的重复学生删除

DELETE t1 FROM students AS t1

LEFT JOIN (SELECT * FROM students GROUP BY name HAVING COUNT(name)>1) AS t2

ON t1.name = t2.name WHERE t1.id > t2.id;

+----+--------+----------+------------+-------+

| id | name | class_id | teacher_id | score |

+----+--------+----------+------------+-------+

| 1 | 张山 | 3 | 4 | 78 |

| 2 | 王五 | 1 | 1 | 85 |

| 3 | 赵六 | 4 | 2 | 66 |

| 4 | 陈七 | 3 | 4 | 90 |

| 5 | 阿狗 | 2 | 3 | 88 |

| 6 | 狗腿 | 2 | 3 | 95 |

| 8 | 小咪 | 4 | 2 | 98 |

| 9 | 富贵 | 1 | 1 | 83 |

| 11 | 张伟 | 10 | 12 | 100 |

| 12 | 刘六 | 12 | 10 | 100 |

+----+--------+----------+------------+-------+

-- 🆗

为自己学习记录，错误之处请帮助指出，共同学习，谢谢

数据小人
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL子查询与连接
Mr_fightingboy的博客
11-02 420
一、修改默认数据及数据表默认编码（utf8为例子） SHOW CREATE DATABASE db_name;//查看数据库编码格式 ALTER DATABASE db_name CHARACTER SET = utf8;//修改数据库编码格式 ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;//修改表的编码格式 SET NAMES u
mysql 子连接_MySQL 子连接示例
weixin_36016076的博客
01-19 122
Summary:in this tutorial, you will learn how to useMySQL self jointhat joins a table to itself using join statement.In the previous tutorial, you have learned how to join a table to the other tables u...
MySQL的子连接
m0_60271677的博客
10-10 585
mysal遇到的一些收获
mysql子连接是什么意思_MySQL子查询与连接
weixin_28826521的博客
01-20 696
——MySQL子查询与连接1、set names gbk: 设置客户端的显示形式为gbk(如果之前设置是utf-8编码则显示出来文字不能识别)2、子查询指嵌套在查询内部，必须在圆括号里使用，返回值是一个标量、一行也开始是一个数据表3、子查询是指在另一个查询里面的select子句4、在进行数据表的写入时，默认是不允许写入汉字的，因此需要修改一下编码格式：show full columns from ...
mysql子查询连接
SYJ_1835_LPM的博客
06-01 847
mysql子查询连接 数据库1 数据库2 含义：出现在其他语句中的select语句，称为子查询或者内查询外部的查询语句，称为：主查询或者外查询 分类： 1、按子查询出现的位置： select后面： 仅仅支持标量子查询 from后面： 支持表子查询 where或having后面： 标量子查询 列子查询 行子查询 exists后面：（相关子查询） 表子...
mysql连接查询、联合查询、子查询原理与用法实例详解
09-08
MySQL中的查询操作是数据库操作的核心，它包括连接查询、联合查询和子查询，这些方法都是在处理多表数据时非常重要的技术。 连接查询是将两个或更多表的数据结合在一起进行查询，主要有四种类型：内连接（INNER ...
详解MySQL数据库--多表查询--内连接，外连接，子查询，相关子查询
09-09
本文将深入探讨MySQL数据库中的多表查询，包括内连接、外连接和子查询，这些都是数据库操作中的重要概念。 首先，多表查询允许我们从多个相关联的表格中获取信息。例如，假设我们有两个表格，一个是`customers`，...
MySQL的子查询及相关优化学习教程
09-10
主要介绍了MySQL的子查询及相关优化学习教程,使用子查询时需要注意其对数据库性能的影响,需要的朋友可以参考下
MySQL优化之使用连接(join)代替子查询
09-09
有些时候，子查询可以被更有效的连接替代，方法仅供参考，需要的朋友可以了解下。
Mysql自连接查询实例详解
09-09
例如，查询所有分类及其父类时，我们将表`tdb_cates`称为子表（`s`）和父表（`p`），然后通过`LEFT JOIN`进行连接，连接条件为子表的`parent_id`等于父表的`id`。这样，我们就可以得到每个类别的ID、名称以及它的...
数据库连接查询、子查询
04-14
数据库连接查询、子查询、嵌套查询、汇总数据
MySQL 连接查询和子查询
sshi9的博客
02-15 1310
连接查询 连接查询：也可以叫跨表查询，需要关联多个表进行查询 连接查询分类 根据语法出现的年代来划分的话，包括： SQL92 SQL99 根据表的连接方式来划分，包括： 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接（左连接） 右外连接（右连接） 全连接 笛卡尔积现象：当两张表进行连...
mysql之子查询与连接
jiangmoting的博客
11-22 228
1.子查询-------出现在其他ＳＱＬ语句内的ＳＥＬＥＣＴ语句，嵌套在查询内部，始终出现在圆括号内 SET NAMES GBK;在客户端显示数据，但并不影响数据表中的记录（数据表中记录编码方式为utf8） 子查询可以包含多个关键字或条件如DISTINCT,GROUP BY,ORDER BY,LIMIT等 外层可以是SELECT,UPDATE,INSERT等 子查询返回值可以是标量，一行或
MySQL的子查询和连接知识
YangJack-前端开发
02-14 1276
MySQL子查询与连接 子查询： 1.子查询是指出现在【其他SQL语句内】的SELECT子句 eg： SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); 其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement) SELECT column1 FROM t2
Mysql连接,多表,子连接查询,子查询
qq_41942241的博客
08-23 1508
1. 连接查询                           内     连接     select ... from 表1 inner join 表2 on 连接条件 WHERE ... group by ... HAVING ... ORDER BY ... limit ... (重点)                                左    外       select...
(5)Mysql子查询与连接
不积跬步，无以千里
08-03 462
简单回顾，上一节主要学习了mysql对记录的操作，这些主要集中在增删改查四个方面，其中增删改我们把它作为写操作：insert（增）update（改）delete（删） 读取操作：select（查）。而在今天的学习内容当中，我们会主要学习子查询和连接，我们还会学习多表的删除以及多表的更新。在查询记录之前，如果出现中文乱码，可以通过SET NAMES gbk;设置客户端数据显示的编码。SET NAMES
Mysql数据库常用语句——隐式内连接/显式外连接/左外连接/右外连接/子连接
yopky的专栏
06-15 2047
Mysql数据库常用语句——多表查询，包括隐式内连接，显式外连接，左外连接，右外连接，子连接
数据库内/外连接和子连接
不积跬步，无以至千里
10-10 1346
前言： 多表进行查询数据，主要通过excel模拟人工操作，再写SQL语句，这样可以直观的理解逻辑。 –交叉连接 笛卡尔乘积：没有任何限制条件的连接 结果得到的表是表的列数相乘，有点类似于测试要因的全覆盖。 select * from 表1, 表2; select * from 表1 cross join 表2; 内连接 两张表中同时符合某种条件的数据记录的组合（相互没有对应关系的则会隐藏） s...
mysql学习笔记之连接查询与子查询
小平果的欢乐谷
07-11 4972
mysql连接查询与子查询1.子查询是指在另一个查询语句中的SELECT子句。例句： SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),SELECT column1 FROM t2 称为Sub Query[子查询
mysql做关联表_mysql---多表关联
最新发布
06-08
多表关联在 MySQL 中非常常见，可以使用 JOIN 或者子查询来实现。 使用 JOIN 可以将多个表连接起来，常见的 JOIN 类型有 INNER JOIN（内连接）、LEFT JOIN（左连接）、RIGHT JOIN（右连接）和 FULL OUTER JOIN（全...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值