架构视界
主页
企业架构
  • 方案总览
  • 业财一体
  • 企业中台
  • 组织与绩效
  • 流程治理
数据收集与分析
  • 转型总览
  • 信息化阶段
  • 数字化解决方案
  • 智数化解决方案
  • 智能化解决方案
  • 产品设计
  • 项目管理
全栈软件工程
AI 原生软件工程
方法论
博客
  • 资源导航
  • 效率工具
  • 展示专区
主页
企业架构
  • 方案总览
  • 业财一体
  • 企业中台
  • 组织与绩效
  • 流程治理
数据收集与分析
  • 转型总览
  • 信息化阶段
  • 数字化解决方案
  • 智数化解决方案
  • 智能化解决方案
  • 产品设计
  • 项目管理
全栈软件工程
AI 原生软件工程
方法论
博客
  • 资源导航
  • 效率工具
  • 展示专区
  • 标签索引
  • 2024年

    • 勇闯大A:美债与美元周期
    • 勇闯大A:交易纪律与自我管理
    • 勇闯大A:交易系统分层
    • 勇闯大A:买卖点与止损规则
    • 勇闯大A:债券与资金流向
    • 勇闯大A:微观、宏观与周期
    • Mac 开发环境:新机配置清单
    • 勇闯大A:内外盘与换手率
    • 会计管理:财报阅读与管理报表
    • 会计基础:记账原理与分录
    • 财务入门:财务体系全景
  • 2023年

    • 境外投资:泰国 BOI 申请实务
  • 2022年

    • 企业架构:故障管理与分级响应
    • 企业架构:技术债务的识别、度量与偿还
  • 2021年

    • Python 数据分析:员工分布可视化
    • Python 数据分析:IP 访问折线图
    • Python 数据分析:IP 词云图
    • Redis:RediSearch 全文检索
    • Rust 基础:第一个 CLI 程序
    • Rust 基础:所有权机制
    • Rust 基础:函数与控制流
    • Rust 基础:变量与数据类型
    • Rust 基础:入门与开发环境
    • 企业架构:分布式系统设计
    • 企业架构:编程式权限设计
    • JVM 调优:启动参数与 GC 日志
    • MyBatis 批量插入:三种写法对比
    • 测试:Spring Boot 分层 Mock
    • Redis:布隆过滤器判断元素存在
    • Jenkins:安装配置与应用部署
    • WebClient:非阻塞 HTTP 客户端
    • Drone:Docker 部署轻量 CI
    • Spring AOP:操作审计日志落库
    • Redisson:分布式锁与 RedLock
    • MyBatis 拦截器:雪花算法主键
    • Netty:线程模型与网络编程
    • Redis:SCAN 游标遍历大键空间
    • Jenkins:单机部署与升级脚本
    • Mac:Neo4j 与 Jupyter 安装
    • JavaFX:OpenJDK 11 环境配置
    • Mac:用 jenv 管理 JDK 版本
    • Redis:有序集合实现延时队列
    • Spring Boot 日志:MDC 串联链路
    • 定时任务:Spring 调度与 Cron
    • GoAccess:Nginx 日志分析
    • MyBatis 拦截器:慢 SQL 分析
    • Lucene:倒排索引与中文分词
    • 分布式锁:三种实现方案对比
    • 学习技巧:减少学习排斥心理
    • 动态数据源:多租户数据路由
    • Compose:Spring Boot 三件套
    • Java 阻塞队列:源码与用法
    • Redis:Docker Compose 哨兵
    • Redis:Docker Compose 主从
    • 网络通信:TCP/IP 与 HTTP 基础
  • 2020年

    • 延时队列:DelayQueue 与 Redis 实现
    • MySQL 基础:员工库导入实践
    • 雪花算法:分布式 ID 生成
    • Redis:地理位置查询
    • 抓包:Wireshark 与 Tcpdump
    • SPI:JDK 与 Spring 扩展机制
    • ZooKeeper:概念与集群搭建
    • Spring Boot:集成 JMH 基准测试
    • Wireshark:抓包工具入门
    • Compose:Redis 与 MySQL
    • CentOS 7:Docker 安装部署
    • Netty:源码阅读环境搭建
    • MySQL 基础:SQL 常见用法
    • CentOS 7:Selenium 运行环境
    • Nginx:HTTPS 证书配置
    • JMH:基准测试与 Spring Boot 集成
    • Spring Boot:指定 Tomcat 版本
    • Eureka:实例上下线告警
    • 钉钉告警:应用异常与实例上下线
    • JVM:内存布局、类加载与调优
    • Git:rebase 合并多次提交
    • CentOS 7:OpenResty 部署
  • 2019年

    • Redis CLI:macOS 安装与使用
    • Nginx:访问日志格式
    • 工程规范:可维护代码的写法
    • IntelliJ IDEA:常用插件
    • SkyWalking:源码阅读环境搭建
    • Spring Boot:Redis 超时配置
    • gRPC:ProtoBuf 定义与代码生成
    • Arthas:Java 线上问题诊断
    • Redis:缓存穿透与击穿防护
    • Docker 部署:Elasticsearch
    • Docker 部署:MySQL 5.7 单机
    • Spring Redis:字符串命令封装
    • Docker 部署:ZooKeeper 运行
    • Docker 部署:Redis 单机运行
    • Dubbo:Spring Boot 微服务集成
    • CentOS 7:CMake 源码编译安装
    • 性能度量:吞吐量与 QPS 指标
    • Java 递归:从笔试题到经典问题
    • CentOS 7:ELK 日志平台部署
    • CentOS 7:SonarQube 部署
    • Selenium:Chrome 浏览器自动化
    • JJWT:令牌生成与解析测试
    • Spring 源码:阅读环境搭建
    • JUnit 4:注解、断言与参数化
    • JWT:令牌结构与签名机制
    • FastDFS:架构与文件存取机制
    • XPath:XML 文档节点定位
    • Redis 基础:锁、队列与事务
    • Redis 基础:数据类型与命令
    • MyBatis:Slf4j 日志接入
    • MyBatis:H2 单元测试与 SQL 日志
    • 源码阅读:开源项目环境搭建
    • Git 配置:命令清单与作用域
    • Java 核心:面向对象与基础类型
    • Java 核心:关键字、异常与反射
    • Java 核心:多线程基础
    • Java 核心:同步关键字与线程池
    • Java 核心:锁与并发工具
    • Java 核心:语言基础与并发
    • Dubbo:远程调用与服务治理
    • Java 集合:框架总览与实现对比
    • Maven:生命周期与常用命令
    • MyBatis:执行流程与插件机制
    • Spring:IoC 容器与依赖注入
    • Spring:AOP 与自动装配
    • Spring:事务与启动流程
    • Spring:IoC、AOP 与事务
    • Spring MVC:请求分发与注解
    • MySQL:架构与索引
    • MySQL:事务、锁与 MVCC
    • MySQL:日志与 SQL 优化
    • MySQL:架构、事务与优化
    • Redis 基础:持久化与线程模型
  • 2018年

    • Java HashMap:扩容机制与哈希冲突
    • Java 集合:HashSet 去重原理
    • Java 基础:交换两个变量的值
    • Spring MVC:零 web.xml 改造
    • Mac 开发环境:新机配置清单
    • Log4j:日志分级与按业务拆分
    • Modbus:工业通信协议与 RTU 帧
    • MySQL 基础:子查询与连接查询
    • MySQL 基础:库与表操作
    • MySQL 基础:表操作与查询
    • MySQL 基础:约束、引擎与数据类型
    • MySQL 基础:库表、约束与类型
    • Java 集合:Vector 与 Stack
    • Java 集合:Vector 与 Stack
    • RabbitMQ:CentOS 7 单机部署
    • Redis:CentOS 7 单机部署
    • MongoDB:CentOS 7 单机部署
    • Linux:常用系统与运维命令
    • 注册中心:Eureka 与 ZooKeeper
    • MySQL:CentOS 7 单机部署
    • Git 分支:主分支与临时分支
    • CentOS 7:Java 开发环境部署
    • Java 集合:LinkedList 双向链表
    • Java 集合:ArrayList 动态数组
    • Spring AOP:注解方式实现切面
← 返回博客

MySQL 基础:子查询与连接查询

2018年5月2日 #数据与存储 #MySQL

本章是整理知识内容,为强化知识长期更新。

MySql基础操作

子查询与连接

在MYSQL实际操作中,最大的操作就是查询,下面就关于子查询和链接做记录。

子查询

  • 子查询(SubQuery)是指出现在其它SQL语句中的SELECT子句。比如
SELECT * FROM table_1 AS t1 WHERE columns_1 = (SELECT columns_2 FROM table_2 AS t2);
# 这里的SELECT * FROM table_1 AS t1 称之为 Outer Query/Outer Statement;SELECT columns_2 FROM table_2 AS t2 成为 SubQuery。
  • 子查询严格以上是指嵌套在查询内部,且必须始终出现在圆括号内
  • 子查询可以包含多个关键字:DISTINCT、GROUP BY、ORDER BY、LIMIT等函数。
  • 子查询的外层可以是:INSERT、UPDATE、SELECT 、DELETE、SET、DO等操作。
  • 子查询可以返回结果,这个结果可以是标量、一行、一列,或者子查询。
  • 子查询的引发条件:这里需要特别注意,并不是显示的才是子查询。
    • 使用比较运算符的子查询:= , > , < , >= , <= , <> , != , <=>
    • 使用结构语法:operand comparison_operator subquery ,可以使用一下修饰符来配合比较运算符。
      • operand comparison_operator ANY(subquery)
      • operand comparison_operator SOME(subquery)
      • operand comparison_operator ALL(subquery)
运算符\关键字ANYSOMEALL
> 、>=最大值最小值最大
<、<=最大值最大值最小值
=任意值任意值
<>、!=任意值
  • 首先创建一个测试表,并准备测试数据。我们用最经典商城goods表来做测试。
# 创建测试表t_goods
[root@localhost][Demo]> CREATE TABLE t_goods(
    -> goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> goods_name VARCHAR(150) NOT NULL,
    -> godds_cate VARCHAR(40)  NOT NULL,
    -> goods_price DECIMAL(16,3) UNSIGNED NOT NULL DEFAULT 0,
    -> brand_name VARCHAR(40) NOT NULL,
    -> is_show BOOLEAN NOT NULL DEFAULT 1,
    -> is_saleoff BOOLEAN NOT NULL DEFAULT 0
    -> );
Query OK, 0 rows affected (0.13 sec)

# 哎哟写错了字段名字了。。。
[root@localhost][Demo]> ALTER TABLE t_goods CHANGE godds_cate goods_cate VARCHAR(40) NOT NULL;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

#检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM t_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field       | Type                   | Null | Key | Default | Extra          |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(150)           | NO   |     | NULL    |                |
| goods_cate  | varchar(40)            | NO   |     | NULL    |                |
| goods_price | decimal(16,3) unsigned | NO   |     | 0.000   |                |
| brand_name  | varchar(40)            | NO   |     | NULL    |                |
| is_show     | tinyint(1)             | NO   |     | 1       |                |
| is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
+-------------+------------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
  • 这里需要注意的是price价格,并没有使用浮点型,笔者之前公司用的是TINTINT就是存到分。这里用的是DECIMAL定点型,保证金额的准确。
# 添加测试数据
INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版)','平板电脑','苹果','3388',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版)','平板电脑','苹果','2788',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )','台式机','宏碁','3699',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);
 INSERT t_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

  • 我们检查下数据,这里贴图片。

  • 当然还另外一种显示方式 SELECT * FROM t_goods \G:
  • 另外goods_name名称中存在空格,我们使用 TRIM函数处理一下。
# 更新goods_name字段。
[root@localhost][Demo]> UPDATE t_goods SET goods_name = TRIM(goods_name);
Query OK, 4 rows affected (0.00 sec)
  • 这里使用一些聚合函数来做测试比如AVG、SUM、MIN、MAX。

使用 运算符 的子查询

# 获取goods_price的平均值。
[root@localhost][Demo]> SELECT AVG(goods_price) FROM t_goods;
+------------------+
| AVG(goods_price) |
+------------------+
|     5636.3636364 |
+------------------+
1 row in set (0.00 sec)

# 查询价格大于平均值的商品。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= 5636;
+----------+-----------------------------------------+-------------+
| goods_id | goods_name                              | goods_price |
+----------+-----------------------------------------+-------------+
|        3 | G150TH 15.6英寸游戏本                   |    8499.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本          |    7999.000 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑         |    9188.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑        |   28888.000 |
|       18 | HMZ-T3W 头戴显示设备                    |    6999.000 |
|       20 | X3250 M4机架式服务器 2583i14            |    6888.000 |
|       21 | HMZ-T3W 头戴显示设备                    |    6999.000 |
+----------+-----------------------------------------+-------------+
7 rows in set (0.00 sec)

#那么问题来了,我们想一条SQL语句就完成该怎么处理。使用子查询就可以满足。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= (SELECT AVG(goods_price) FROM t_goods );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name                              | goods_price |
+----------+-----------------------------------------+-------------+
|        3 | G150TH 15.6英寸游戏本                   |    8499.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本          |    7999.000 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑         |    9188.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑        |   28888.000 |
|       18 | HMZ-T3W 头戴显示设备                    |    6999.000 |
|       20 | X3250 M4机架式服务器 2583i14            |    6888.000 |
|       21 | HMZ-T3W 头戴显示设备                    |    6999.000 |
+----------+-----------------------------------------+-------------+
7 rows in set (0.00 sec)

# 在来演示下查询 goods_cate 是超级本的商品信息。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price , goods_cate FROM t_goods WHERE goods_cate = '超级本';
+----------+---------------------------------------+-------------+------------+
| goods_id | goods_name                            | goods_price | goods_cate |
+----------+---------------------------------------+-------------+------------+
|        5 | X240(20ALA0EYCD) 12.5英寸超极本       |    4999.000 | 超级本     |
|        6 | U330P 13.3英寸超极本                  |    4299.000 | 超级本     |
|        7 | SVP13226SCB 13.3英寸触控超极本        |    7999.000 | 超级本     |
+----------+---------------------------------------+-------------+------------+
3 rows in set (0.00 sec)

# 实际上在子查询中,经常会出现返回的值不是一条的问题。如下演示,我们想要查询goods_cate是超极本的商品价格。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= ( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
ERROR 1242 (21000): Subquery returns more than 1 row
# 这里会抛出异常,指明返回的结果集超过了一行。

#上面的列子中,使用了比较运算符,所以可以使用子查询。那配合修饰关键字试试。
# 使用大于等于 >= 添加ANY关键字的时候,获取最小的。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= ANY( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name                              | goods_price |
+----------+-----------------------------------------+-------------+
|        2 | Y400N 14.0英寸笔记本电脑                |    4899.000 |
|        3 | G150TH 15.6英寸游戏本                   |    8499.000 |
|        5 | X240(20ALA0EYCD) 12.5英寸超极本         |    4999.000 |
|        6 | U330P 13.3英寸超极本                    |    4299.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本          |    7999.000 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑         |    9188.000 |
|       16 | PowerEdge T110 II服务器                 |    5388.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑        |   28888.000 |
|       18 | HMZ-T3W 头戴显示设备                    |    6999.000 |
|       20 | X3250 M4机架式服务器 2583i14            |    6888.000 |
|       21 | HMZ-T3W 头戴显示设备                    |    6999.000 |
+----------+-----------------------------------------+-------------+
11 rows in set (0.00 sec)

# 使用大于等于 >= 添加SOME关键字 依然是最小。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= SOME( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name                              | goods_price |
+----------+-----------------------------------------+-------------+
|        2 | Y400N 14.0英寸笔记本电脑                |    4899.000 |
|        3 | G150TH 15.6英寸游戏本                   |    8499.000 |
|        5 | X240(20ALA0EYCD) 12.5英寸超极本         |    4999.000 |
|        6 | U330P 13.3英寸超极本                    |    4299.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本          |    7999.000 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑         |    9188.000 |
|       16 | PowerEdge T110 II服务器                 |    5388.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑        |   28888.000 |
|       18 | HMZ-T3W 头戴显示设备                    |    6999.000 |
|       20 | X3250 M4机架式服务器 2583i14            |    6888.000 |
|       21 | HMZ-T3W 头戴显示设备                    |    6999.000 |
+----------+-----------------------------------------+-------------+
11 rows in set (0.00 sec)

# 使用大于等于 >= 添加ALL关键字 则是最大。
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price >= ALL( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+-----------------------------------------+-------------+
| goods_id | goods_name                              | goods_price |
+----------+-----------------------------------------+-------------+
|        3 | G150TH 15.6英寸游戏本                   |    8499.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本          |    7999.000 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑         |    9188.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑        |   28888.000 |
+----------+-----------------------------------------+-------------+
4 rows in set (0.00 sec)
  • 通过 >= 配合 ANY、SOME 、ALL关键做了测试,那么其它几种运算符就不演示了。

使用 [ NOT ] IN 的子查询

# 这个操作相对比较好理解 IN 包含 NOT IN 不包含
[root@localhost][Demo]> SELECT goods_id , goods_name , goods_price FROM t_goods WHERE goods_price IN  ( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' );
+----------+---------------------------------------+-------------+
| goods_id | goods_name                            | goods_price |
+----------+---------------------------------------+-------------+
|        5 | X240(20ALA0EYCD) 12.5英寸超极本       |    4999.000 |
|        6 | U330P 13.3英寸超极本                  |    4299.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本        |    7999.000 |
+----------+---------------------------------------+-------------+
3 rows in set (0.00 sec)

# 使用NOT IN 就和上面的结果截然相反
[root@localhost][Demo]> SELECT goods_id, goods_price FROM t_goods WHERE goods_price NOT  IN  ( SELECT goods_price FROM t_goods WHERE goods_cate = '超级本' ); 
+----------+-------------+
| goods_id | goods_price |
+----------+-------------+
|        1 |    3399.000 |
|        2 |    4899.000 |
|        3 |    8499.000 |
|        4 |    2799.000 |
|        8 |    1998.000 |
|        9 |    3388.000 |
|       10 |    2788.000 |
|       11 |    3499.000 |
|       12 |    2899.000 |
|       13 |    9188.000 |
|       14 |    3699.000 |
|       15 |    4288.000 |
|       16 |    5388.000 |
|       17 |   28888.000 |
|       18 |    6999.000 |
|       19 |      99.000 |
|       20 |    6888.000 |
|       21 |    6999.000 |
|       22 |      99.000 |
+----------+-------------+
19 rows in set (0.00 sec)

使用[NOT] EXISTS 子查询

  • 为了演示该子查询,需要在添加一张表与其配合。
# 创建一张t_goods_cate表存储商品分类信息。
[root@localhost][Demo]> CREATE TABLE IF NOT EXISTS t_goods_cates(
    -> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT ,
    -> cate_name VARCHAR(40) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

[root@localhost][Demo]> SHOW COLUMNS FROM  t_goods_cates;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| cate_id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| cate_name | varchar(40)          | NO   |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

# 现在这张表的数据是空的,需要将t_goods 中 goods_cate信息写入进来。
# 先看t_goods 有哪些分类;这里使用GROUP BY 来做分组查询。
[root@localhost][Demo]> SELECT goods_cate FROM t_goods GROUP BY goods_cate;
+---------------------+
| goods_cate          |
+---------------------+
| 台式机              |
| 平板电脑            |
| 服务器/工作站       |
| 游戏本              |
| 笔记本              |
| 笔记本配件          |
| 超级本              |
+---------------------+
7 rows in set (0.00 sec)

# 使用INSERT...SEKECT 方式来插入数据到t_goods_cates。
[root@localhost][Demo]> INSERT INTO t_goods_cates(cate_name) SELECT goods_cate FROM t_goods GROUP BY goods_cate;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

# OK检查下数据是否录入成功。
[root@localhost][Demo]> SELECT * FROM t_goods_cates;
+---------+---------------------+
| cate_id | cate_name           |
+---------+---------------------+
|       1 | 台式机              |
|       2 | 平板电脑            |
|       3 | 服务器/工作站       |
|       4 | 游戏本              |
|       5 | 笔记本              |
|       6 | 笔记本配件          |
|       7 | 超级本              |
+---------+---------------------+
7 rows in set (0.00 sec)

  • 这个时候打算把操作反过来,让t_goods 表中的 goods_cate 存储 t_goods_cates 中的主键信息。而不在存储内容。

连接查询

  • 将多张表(至少大于等于2张表)按照某张指定的条件成立进行查询。

table_reference { [INNER | CROSS] JOIN | { LEFT | RIGHT } JOIN} table_reference ON conditional_expr

  • 连接类型
    • INNER JOIN内连接
    • 在Mysql中 JOIN , CROSS JION 和 INNER JOIN 是等价的。
    • LEFT [OUTER] JOIN 左外连接。
    • RIGHT [OUTER] JOIN 右外连接。
  • 连接条件
    • 可以使用ON来设置连接条件,也可以使用WHERE来代替。
    • 但是通常使用ON关键字来设置条件,WHERE关键进行结果集的过滤。
# 使用INNER JOIN 内连接来完成t_goods表中 goods_cate字段记录的更新。
[root@localhost][Demo]> UPDATE t_goods INNER JOIN t_goods_cates ON t_goods.goods_cate = t_goods_cates.cate_name SET t_goods.goods_cate = t_goods_cates.cate_id;
Query OK, 22 rows affected (0.01 sec)
Rows matched: 22  Changed: 22  Warnings: 0

# 那我们在检查一下
[root@localhost][Demo]> SELECT goods_id  , goods_cate FROM t_goods;
+----------+------------+
| goods_id | goods_cate |
+----------+------------+
|        1 | 5          |
|        2 | 5          |
|        3 | 4          |
|        4 | 5          |
|        5 | 7          |
|        6 | 7          |
|        7 | 7          |
|        8 | 2          |
|        9 | 2          |
|       10 | 2          |
|       11 | 1          |
|       12 | 1          |
|       13 | 1          |
|       14 | 1          |
|       15 | 3          |
|       16 | 3          |
|       17 | 3          |
|       18 | 6          |
|       19 | 6          |
|       20 | 3          |
|       21 | 6          |
|       22 | 6          |
+----------+------------+
22 rows in set (0.00 sec)

# 这里最好将t_goods 里面 goods_cate 的字段类型修改一下,毕竟已经不是存储字符串了,而是存储关联表主键。
# 将t_goods 表中 goods_cate 修改成 cate_id 类型为 SMALLINT UNSIGNED;

[root@localhost][Demo]> ALTER TABLE t_goods MODIFY cate_id SMALLINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.22 sec)
Records: 22  Duplicates: 0  Warnings: 0

# t_goods表中有一个字段 brand_name 在此对表进行改造。这里创建一张品牌表 t_goods_brand。
[root@localhost][Demo]> CREATE TABLE t_goods_brand(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL
    -> )
    -> ;
Query OK, 0 rows affected (0.11 sec)

# 检查表结构
[root@localhost][Demo]> SHOW COLUMNS FROM t_goods_brand;
+------------+----------------------+------+-----+---------+----------------+
| Field      | Type                 | Null | Key | Default | Extra          |
+------------+----------------------+------+-----+---------+----------------+
| id         | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| brand_name | varchar(40)          | NO   |     | NULL    |                |
+------------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)


# 将t_goods表中的brand_name 去重后添加到t_goods_brand中。
[root@localhost][Demo]> INSERT t_goods_brand(brand_name) SELECT brand_name FROM t_goods GROUP BY brand_name;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

# 查询结果
[root@localhost][Demo]> SELECT * FROM t_goods_brand;
+----+------------+
| id | brand_name |
+----+------------+
|  1 | IBM        |
|  2 | 华硕       |
|  3 | 宏碁       |
|  4 | 惠普       |
|  5 | 戴尔       |
|  6 | 索尼       |
|  7 | 联想       |
|  8 | 苹果       |
|  9 | 雷神       |
+----+------------+
9 rows in set (0.00 sec)

# 更新t_goods表中brand_name 字段时期存储t_goods_brand主键信息。
[root@localhost][Demo]> UPDATE t_goods INNER JOIN t_goods_brand ON t_goods.brand_name = t_goods_brand.brand_name SET t_goods.braand_name = t_goods_brand.id;
Query OK, 22 rows affected (0.01 sec)
Rows matched: 22  Changed: 22  Warnings: 0

# 检查下效果
[root@localhost][Demo]> SELECT goods_id , brand_name FROM t_goods;
+----------+------------+
| goods_id | brand_name |
+----------+------------+
|        1 | 2          |
|        2 | 7          |
|        3 | 9          |
|        4 | 2          |
|        5 | 7          |
|        6 | 7          |
|        7 | 6          |
|        8 | 8          |
|        9 | 8          |
|       10 | 8          |
|       11 | 7          |
|       12 | 5          |
|       13 | 8          |
|       14 | 3          |
|       15 | 4          |
|       16 | 5          |
|       17 | 8          |
|       18 | 6          |
|       19 | 6          |
|       20 | 1          |
|       21 | 6          |
|       22 | 6          |
+----------+------------+
22 rows in set (0.00 sec)

# OK 再把t_goods 中 brand_name 字段进行修改。
[root@localhost][Demo]> ALTER TABLE t_goods CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
Query OK, 22 rows affected (0.22 sec)
Records: 22  Duplicates: 0  Warnings: 0
  • 为什么一定要修改字段的属性,这样做主要是为了做适当调整结构。数字和字符串占用的大小要小的多。
  • 这里t_goods 表中的 cate_id 和brand_id 由于不是物理外键,所以约束机会没有。也就是说哪怕插入t_goods表的时候,cate_id 和 brand_id 在t_goods_cates 和 t_goods_brand表中都不存在,依然是可以插入。但是数据不完整。

内连接

  • 内连接查询的是相交部分的数据

左外连接

# 这里为了演示其效果,我们在t_goods表中添加一个模拟数据,这条数据的cates_id 是不存在与t_goods_cates表中的。
INSERT t_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
Query OK, 1 row affected (0.00 sec)

  • 将INNER JOIN 改成 LEFT JOIN 看看结果

  • 这里可以看低23条记录的cate_name是null。

右外连接

  • 将LEFT JOIN 改成 RIGHT JOIN 试试。
# 在 t_goods_cates 表中添加记录
[root@localhost][Demo]> INSERT t_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

  • 这里可以看到优先显示t_goods_cates表中的数据。和左外连接刚刚好相反。

多表连接

现在以及存在t_goods 、 t_goods_cates 、 t_goods_brand 表,这次同时查询一个商品表结果集合。

# 内链接三张表查询结果集合。
[root@localhost][Demo]> SELECT g.goods_id , g.goods_name , g.goods_price , gc.cate_name , gb.brand_name FROM t_goods AS g INNER JOIN t_goods_cates AS gc ON g.cate_id = gc.cate_id INNER JOIN t_goods_brand AS gb ON g.brand_id = gb.id ;

  • 这里只会吧符合内连接条件的数据展示出来。

注意事项

  • t_goods LEFT JOIN t_goods_cates join_condition
    • t_goods_cates 表中的结果集依赖 t_goods
    • 如果使用内连接查找记录在连接数据表中不存在,并且在WHERE 语句中尝试定义了: columns_name IS NULL 时(就是这个字段可以为NULL的时候),如果columns_name 被定义为NOT NULL(这个字段不能为NULL),Mysql将在找到符合条件的记录后停止搜索更多的行。因为这个操作是冲突的。

自连接

很多情况下,可能会出现一张表自关联,特别是在商城项目的商品类型这种。所以这里在举个例子。

# 创建一张商品类型表t_goods_types
[root@localhost][Demo]> CREATE TABLE t_goods_types(
    -> type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> type_name VARCHAR(20) NOT NULL,
    -> parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
    -> );
Query OK, 0 rows affected (0.13 sec)

  INSERT t_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
  INSERT t_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
  INSERT t_goods_types(type_name,parent_id) VALUES('大家电',1);
  INSERT t_goods_types(type_name,parent_id) VALUES('生活电器',1);
  INSERT t_goods_types(type_name,parent_id) VALUES('平板电视',3);
  INSERT t_goods_types(type_name,parent_id) VALUES('空调',3);
  INSERT t_goods_types(type_name,parent_id) VALUES('电风扇',4);
  INSERT t_goods_types(type_name,parent_id) VALUES('饮水机',4);
  INSERT t_goods_types(type_name,parent_id) VALUES('电脑整机',2);
  INSERT t_goods_types(type_name,parent_id) VALUES('电脑配件',2);
  INSERT t_goods_types(type_name,parent_id) VALUES('笔记本',9);
  INSERT t_goods_types(type_name,parent_id) VALUES('超级本',9);
  INSERT t_goods_types(type_name,parent_id) VALUES('游戏本',9);
  INSERT t_goods_types(type_name,parent_id) VALUES('CPU',10);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

# 检查插入数据结果。
[root@localhost][Demo]> SELECT * FROM t_goods_types;
+---------+-----------------+-----------+
| type_id | type_name       | parent_id |
+---------+-----------------+-----------+
|       1 | 家用电器        |         0 |
|       2 | 电脑、办公      |         0 |
|       3 | 大家电          |         1 |
|       4 | 生活电器        |         1 |
|       5 | 平板电视        |         3 |
|       6 | 空调            |         3 |
|       7 | 电风扇          |         4 |
|       8 | 饮水机          |         4 |
|       9 | 电脑整机        |         2 |
|      10 | 电脑配件        |         2 |
|      11 | 笔记本          |         9 |
|      12 | 超级本          |         9 |
|      13 | 游戏本          |         9 |
|      14 | CPU             |        10 |
+---------+-----------------+-----------+
14 rows in set (0.00 sec)

  • 这里可以看到t_goods_typs中存在一个字段parent_id,如果不是0即表示有上级类别,而上级列表是指type_id。
# 尝试查询一次参考子类的信息。
[root@localhost][Demo]> SELECT t.type_id , t.type_name , p.type_name AS parent_name FROM t_goods_types AS t LEFT JOIN t_goods_types AS p ON t.paarent_id = p.type_id;
+---------+-----------------+-----------------+
| type_id | type_name       | parent_name     |
+---------+-----------------+-----------------+
|       1 | 家用电器        | NULL            |
|       2 | 电脑、办公      | NULL            |
|       3 | 大家电          | 家用电器        |
|       4 | 生活电器        | 家用电器        |
|       5 | 平板电视        | 大家电          |
|       6 | 空调            | 大家电          |
|       7 | 电风扇          | 生活电器        |
|       8 | 饮水机          | 生活电器        |
|       9 | 电脑整机        | 电脑、办公      |
|      10 | 电脑配件        | 电脑、办公      |
|      11 | 笔记本          | 电脑整机        |
|      12 | 超级本          | 电脑整机        |
|      13 | 游戏本          | 电脑整机        |
|      14 | CPU             | 电脑配件        |
+---------+-----------------+-----------------+
14 rows in set (0.00 sec)

连接查询小结

JOIN作为经常使用的关键字之一,使用的是否正确直接影响到查询结果和查询效率。

SQL标准的JOIN类型

  • 内连接INNER
  • 全外连接FULL OUTER 在mysql中并不能直接使用。
  • 左外连接LEFT OUTER
  • 右外连接RIGHT OUTER
  • 交叉连接CROSS

小结

用商品测试表串起子查询与连接查询:子查询返回多行时比较运算符报 1242 错误,需改用 ANY 或 ALL;LEFT JOIN 保留未匹配记录,但关联字段不自动建外键。

最近更新: 2026/11/20 22:16
Contributors: 庆峰
Prev
Modbus:工业通信协议与 RTU 帧
Next
MySQL 基础:库与表操作