MySQL学习笔记-子查询和连接

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

MySQL学习笔记-子查询和连接

 

使客户端进入gbk编码方式显示:

mysql> SET NAMES gbk;

 

1.子查询

子查询的定义:

子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。

例如:

 

 SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

 

其中SELECT * FROM t1 称为Outer Query / Outer Statement (外部查询)

SELECT col2 FROM t2 , 被称为SubQuery  (子查询)

 

子查询的条件:

子查询指嵌套在查询内部 ,且必须始终出现在圆括号内。

子查询可以包含多个关键字或条件,

                如:DISTINCT,GROUP BY,ORDER BY,LIMIT函数等。

子查询的外部查询可以是:SELECT , INSERT , UPDATE , SET 或 DO

 

子查询的返回值:

子查询可以返回标量、一行、一列或子查询

 

2.使用比较运算符的子查询

使用比较运算符的子查询:

=、>、<、>=、<=、<>、!=、 <=>

语法结构:

operand(操作数)、comparison_operator (比较运算符)、subquery(子查询)

用ANY  、SOME 或ALL修饰的比较运算符

operand comparison_operator ANY (subquery)

operand comparison_operator SOME (subquery)

operand comparison_operator ALL (subquery)

 

1.语法结构  操作数  比较运算符 any(子查询)

            操作数  比较运算符 some(子查询)

            操作数  比较运算符 all(子查询)

2.适合于子查询有多个结果

3.any 和some结果一致 all与any、some 相反

 

例,从tdb_goods表中 查询平均价格,小数点保留2位,:

mysql> SELECT ROUND(avg(goods_price),2) AS avg_price FROM tdb_goods;+-----------+| avg_price |+-----------+|   5391.30 |+-----------+

 

查询平均价格以上的商品:

mysql> SELECT * FROM tdb_goods WHERE goods_price > 5391.30;

 

mysql> SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

 

 

查询超极本的列表,按价格升序排列:

mysql> SELECT * FROM tdb_goods WHERE goods_cate = '超级本' ORDER BY goods_price ASC;

 

  • 查询比超极本的最低价格高的列表,按价格降序的方式排列:

mysql> SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;

 

  • 查询比超极本的最高价格高的列表,按价格降序的方式排列:

mysql> SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;

 

 

3.使用[NOT]IN/EXISTS引发的子查询

子查询形式:

1.使用IN的子查询

2.使用[NOT]IN的子查询

  语法结构:operand comparison_operator [NOT] IN (subquery)

  =ANY 运算符与IN 等效,!=ALL或<>ALL运算符与NOT IN等效

3.使用[NOT]EXISTS的子查询(用的相对较少)

  如果子查询返回任何行,EXISTS将返回TRUE,否则为FALSE

例,查找不是超极本的商品列表:

mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE goods_cate = '超级本');

 

 

 

4.使用INSERT...SELECT插入记录

例:在tdb_goods_cates表中插入tdb_goods表中的goods_cate分类;

mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
mysql> SELECT * FROM tdb_goods_cates;+---------+---------------------+| cate_id | cate_name           |+---------+---------------------+|       1 | 台式机              ||       2 | 平板电脑            ||       3 | 服务器/工作站       ||       4 | 游戏本              ||       5 | 笔记本              ||       6 | 笔记本配件          ||       7 | 超级本              |+---------+---------------------+

 

5.多表更新

多表更新:

UPDATE table_references SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]

 

其中,table_references即连接的语法结构

 

连接类型:

INNER JOIN,内连接

   在MySQL中,JOIN,CROSS JOIN和INNER JOIN是等价的

LEFT [OUTER] JOIN,左外连接

RIGHT [OUTER] JOIN,右外连接

 

连接—语法结构

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

 

例,将tdb_goods中的goods_cate更新为tdb_goods_cate表中的对应cate_id。 

mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

 

 

6.多表更新之一步到位

CREATE...SELECT

创建数据表同时将查询结果写入到数据表(合并了CREATE 和 INSERT...SELECT两个操作步骤)

CREATE TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)]select_statement

 

例:创建品牌分类数据表tdb_goods_brand,并将tdb_goods表中的brand_name写入

mysql> CREATE TABLE tdb_goods_brand(    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -> brand_name VARCHAR(40) NOT NULL
    -> )    -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;

 

  • 例,将tdb_goods中的brand_name更新为tdb_goods_brand表中的对应brand_id。错误写法(程序分别不出哪个brand_name属于哪个表):

mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

 

解决方法是给表起别名:

mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;

 

  • 查询tdb_goods的数据表结构

mysql>  DESC tdb_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    |                || brand_name  | varchar(40)            | NO   |     | NULL    |                || goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                || is_show     | tinyint(1)             | NO   |     | 1       |                || is_saleoff  | tinyint(1)             | NO   |     | 0       |                |+-------------+------------------------+------+-----+---------+----------------+

 

goods_cate和brand_name任然是varchar,现在我们修改字段名goods_cate为cate_id,brand_name修改为brand_id,  为了节省空间,我们修改数据类型为smallant

mysql> ALTER TABLE tdb_goods;    -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,    -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
mysql> DESC tdb_goods;                                                                                    
+-------------+------------------------+------+-----+---------+----------------+| Field       | Type                   | Null | Key | Default | Extra          |+-------------+------------------------+------+-----+---------+----------------+| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment || goods_name  | varchar(150)           | NO   |     | NULL    |                || cate_id     | smallint(5) unsigned   | NO   |     | NULL    |                || brand_id    | smallint(5) unsigned   | NO   |     | NULL    |                || goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                || is_show     | tinyint(1)             | NO   |     | 1       |                || is_saleoff  | tinyint(1)             | NO   |     | 0       |                |+-------------+------------------------+------+-----+---------+----------------+

 

7.连接的语法结构

连接在MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

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

 

数据表参照

table_reference

tbl_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用tbl_name AS alias_name

或tbl_name alias_name 赋予别名

table_subquery可以作为子查询使用FROM子句中,

这样的子查询必须赋予别名。

 

8.内连接INNER JOIN

INNER JOIN,内连接

在MySQL中,JOIN,CROSS JOIN 和 INNER JOIN 是等价的

LEFT [OUTER] JOIN,左外连接

RIGHT [OUTER] JOIN,右外连接

连接条件:

使用ON关键字来设定连接条件,也可以使用WHERE来代替

通常使用ON关键字来设定连接条件

使用WHERE关键字进行结果集记录的过滤

 

内连接和外连接的区别

内连接 ,显示左表及右表符合连接条件的记录,即交集

例如 插入几条记录

-- 分别在tdb_goods_cates和tdb_goods_brands表插入记录

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

 

-- 在tdb_goods数据表写入任意记

INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

 

例,查看符合内连接的记录,表中只出现商品表和品牌表都有的记录,这就是内连接(这里商品表为左表,分类表为右表):

mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods-> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

 

 

9.外连接OUTER JOIN

左外连接

  • 显示左表的全部记录及右表符合连接条件的记录

  • 例,查看符合左外连接的记录,表中出现商品表的全部和2表表都有的记录,这就是左外连接(这里商品表为左表,分类表为右表)

mysql> SELECT goods_id,cate_name,goods_price FROM tdb_goods 
LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

 

 

  • 右外连接

 

  • 显示右表的全部记录及左表符合连接条件的记录

  • 例,查看符合右外连接的记录,表中出现品牌表的全部和2表表都有的记录,这就是右外连接(这里商品表为左表,分类表为右表)

mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods 
RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

 

 

10.多表连接

/**  

  多表的连接跟两张表的连接一样

  表的连接实质就是外键的逆向约束

*/

例,查看符合内连接的记录,表中只出现商品表和品牌表都有的记录,这就是内连接的多表连接(这里商品表为左表,品牌表和分类表为右表):

mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods    -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id    -> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id;

 

 

11.无限级分类表设计

自身连接

 

  • 例,查找所有分类及其父类:

mysql> SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS  p ON s.parent_id = p.type_id;+---------+-----------------+-----------------+| type_id | type_name       | type_name       |+---------+-----------------+-----------------+|       1 | 家用电器        | NULL            ||       2 | 电脑、办公      | NULL            ||       3 | 大家电          | 家用电器        ||       4 | 生活电器        | 家用电器        ||       5 | 平板电视        | 大家电          ||       6 | 空调            | 大家电          ||       7 | 电风扇          | 生活电器        ||       8 | 饮水机          | 生活电器        ||       9 | 电脑整机        | 电脑、办公      ||      10 | 电脑配件        | 电脑、办公      ||      11 | 笔记本          | 电脑整机        ||      12 | 超级本          | 电脑整机        ||      13 | 游戏本          | 电脑整机        ||      14 | CPU             | 电脑配件        ||      15 | 主机            | 电脑配件        |+---------+-----------------+-----------------+

 

  • 例,查找所有分类及其子类:

mysql> SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id; 
+---------+-----------------+--------------+| type_id | type_name       | type_name    |+---------+-----------------+--------------+|       1 | 家用电器        | 大家电       ||       1 | 家用电器        | 生活电器     ||       2 | 电脑、办公      | 电脑整机     ||       2 | 电脑、办公      | 电脑配件     ||       3 | 大家电          | 平板电视     ||       3 | 大家电          | 空调         ||       4 | 生活电器        | 电风扇       ||       4 | 生活电器        | 饮水机       ||       5 | 平板电视        | NULL         ||       6 | 空调            | NULL         ||       7 | 电风扇          | NULL         ||       8 | 饮水机          | NULL         ||       9 | 电脑整机        | 笔记本       ||       9 | 电脑整机        | 超级本       ||       9 | 电脑整机        | 游戏本       ||      10 | 电脑配件        | CPU          ||      10 | 电脑配件        | 主机         ||      11 | 笔记本          | NULL         ||      12 | 超级本          | NULL         ||      13 | 游戏本          | NULL         ||      14 | CPU             | NULL         ||      15 | 主机            | NULL         |+---------+-----------------+--------------+

 

  • 例,查找所有分类及其子类的数目

mysql> SELECT p.type_id,p.type_name,COUNT(s.type_name) FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;+---------+-----------------+--------------------+| type_id | type_name       | COUNT(s.type_name) |+---------+-----------------+--------------------+|       1 | 家用电器        |                  2 ||       2 | 电脑、办公      |                  2 ||       3 | 大家电          |                  2 ||       4 | 生活电器        |                  2 ||       5 | 平板电视        |                  0 ||       6 | 空调            |                  0 ||       7 | 电风扇          |                  0 ||       8 | 饮水机          |                  0 ||       9 | 电脑整机        |                  3 ||      10 | 电脑配件        |                  2 ||      11 | 笔记本          |                  0 ||      12 | 超级本          |                  0 ||      13 | 游戏本          |                  0 ||      14 | CPU             |                  0 ||      15 | 主机            |                  0 |+---------+-----------------+--------------------+

 

  • 为tdb_goods_types添加child_count字段

mysql> UPDATE tdb_goods_types AS t1 INNER JOIN
    -> (SELECT p.type_id,p.type_name,count(s.type_name) AS child_count FROM tdb_goods_types AS p    -> LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id    -> GROUP BY p.type_name    -> ORDER BY p.type_id)AS t2    -> ON t1.type_id = t2.type_id    ->  SET t1.child_count = t2.child_count;
    
mysql> SELECT * FROM tdb_goods_types;+---------+-----------------+-----------+-------------+| type_id | type_name       | parent_id | child_count |+---------+-----------------+-----------+-------------+|       1 | 家用电器        |         0 |           2 ||       2 | 电脑、办公      |         0 |           2 ||       3 | 大家电          |         1 |           2 ||       4 | 生活电器        |         1 |           2 ||       5 | 平板电视        |         3 |           0 ||       6 | 空调            |         3 |           0 ||       7 | 电风扇          |         4 |           0 ||       8 | 饮水机          |         4 |           0 ||       9 | 电脑整机        |         2 |           3 ||      10 | 电脑配件        |         2 |           2 ||      11 | 笔记本          |         9 |           0 ||      12 | 超级本          |         9 |           0 ||      13 | 游戏本          |         9 |           0 ||      14 | CPU             |        10 |           0 ||      15 | 主机            |        10 |           0 |+---------+-----------------+-----------+-------------+

 

11.多表删除

DELETE tbl_name[.*][,tbl_name[.*]]...FROM table_references[WHERE where_condition]

 

例,查找重复记录:

mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name) >= 2;                
+----------+-----------------------------+| goods_id | goods_name                  |+----------+-----------------------------+|       18 |  HMZ-T3W 头戴显示设备       ||       19 | 商务双肩背包                |+----------+-----------------------------+

 

删除重复记录

mysql> DELETE t1 FROM tdb_goods AS t1    -> LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name) >= 2) AS t2    -> ON t1.goods_name = t2.goods_name    -> WHERE t1.goods_id > t2.goods_id;

 

12.复制记录

复制编号为19,20的两条记录

mysql>  SELECT goods_id,goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);+----------+------------------------------------+---------+----------+| goods_id | goods_name                         | cate_id | brand_id |+----------+------------------------------------+---------+----------+|       19 | 商务双肩背包                       |       6 |        7 ||       20 | X3250 M4机架式服务器 2583i14       |       3 |        1 |+----------+------------------------------------+---------+----------+

 

mysql> INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN(19,20);

 








      本文转自谢育政 51CTO博客,原文链接:http://blog.51cto.com/kurolz/1929134,如需转载请自行联系原作者


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
SQL 缓存 Java
实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-connector-sqlserver-cdc 这两个CDC连接器只能选择其中一个使用吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
23 0
|
1天前
|
关系型数据库 MySQL 数据安全/隐私保护
关于Navicat连接MySQL 报 Authentication plugin ‘caching_sha2_password‘ cannot be loaded
关于Navicat连接MySQL 报 Authentication plugin ‘caching_sha2_password‘ cannot be loaded
|
2天前
|
关系型数据库 MySQL 数据库
mysql 设置环境变量与未设置环境变量连接数据库的区别
设置与未设置MySQL环境变量在连接数据库时主要区别在于命令输入方式和系统便捷性。设置环境变量后,可直接使用`mysql -u 用户名 -p`命令连接,而无需指定完整路径,提升便利性和灵活性。未设置时,需输入完整路径如`C:\Program Files\MySQL\...`,操作繁琐且易错。为提高效率和减少错误,推荐安装后设置环境变量。[查看视频讲解](https://www.bilibili.com/video/BV1vH4y137HC/)。
24 3
mysql 设置环境变量与未设置环境变量连接数据库的区别
|
2天前
|
关系型数据库 MySQL 数据库
navicat过期了,直接用idea连接mysql
navicat过期了,直接用idea连接mysql
10 0
|
2天前
|
SQL Java 关系型数据库
【JAVA基础篇教学】第十六篇:Java连接和操作MySQL数据库
【JAVA基础篇教学】第十六篇:Java连接和操作MySQL数据库
|
2天前
|
存储 算法 关系型数据库
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
|
2天前
|
SQL 数据库
mysql-相关子查询
该任务是找出未选修任何课程的学生学号和姓名。通过在教学数据库中使用SQL子查询,先获取选修了课程的学生学号集合,然后在外层查询中找出不在这个集合中的学生信息。代码实现为:`select sno, sname from student where sno not in (select distinct sno from score);`
19 0
|
2天前
|
数据库
mysql-相关子查询
任务是找出选修了课程004的学生姓名和所在系。在teachingdb数据库中,使用子查询和EXISTS关键字来实现。代码示例:`select sname, sdept from student a where exists (select * from score b where a.sno=b.sno and b.cno=004);`
11 0
|
2天前
|
关系型数据库 MySQL 数据库
MySQL -相关子查询
该任务是数据库查询,目标是找出所有选修了&quot;刘东明&quot;所选全部课程的学生学号。需运用子查询和NOT EXISTS运算符。代码首先选择学生表中名字不是&#39;刘东明&#39;的学生,然后检查他们是否存在与&#39;刘东明&#39;相同的所有课程记录。
17 0
|
2天前
|
SQL 数据库
mysql-相关子查询
在教学数据库teachingdb中,使用子查询和NOT EXISTS运算符找出选修了所有课程的学生学号。代码如下: ```sql SELECT sno FROM student a WHERE NOT EXISTS (SELECT cno FROM course b WHERE NOT EXISTS (SELECT * FROM score WHERE sno=a.sno AND cno=b.cno)); ``` 这段SQL语句查找的是没有匹配未选修课程记录的学生学号,即这些学生选修了所有课程。
12 0

推荐镜像

更多