第五章 子查询与连接

数据准备

回顾

记录操作：
写操作：INSERT,UPDATE,DELETE
读取操作：SELECT

这章主要学习：
子查询
连接
多表删除
多表更新

数据准备：
简单的商城数据库

Paste_Image.png

tdb_good表结构：

Paste_Image.png

插入数据：略（见下载文件中的”子查询.txt“）

注意编码方式，插入的时候是以utf8的形式插入的，显示会乱码，此时使用SET NAMES gbk;设置客户端的编码方式（不会影响服务器）

子查询简介

子查询：
子查询（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,DELETE,SET或DO。

子查询中的外层查询是指SQL语句的统称，而不仅仅是SELECT（SQL：结构化查询语言）

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

拿到结果后就可以在INSERT,UPDATE,SELECT,DELETE等其他的SQL语句中使用

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

子查询分类：
使用比较运算符的子查询
使用比较运算符的子查询（=、>、<、>=、<=、<>、!=、<=>）
语法结构：operand comparison_operator subquery

查找平均价格
SELECT AVG(goods_price) FROM tdb_goods;
avg()聚合函数，和i有一个返回值，类似的函数还有sum(),count(),max(),min()

SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;#ROUND(AVG(goods_price),2)指的是对平均值进行四舍五入，最后保留l两位小数

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5636.36;#选择价格大于平均价格（5636.36）的商品

将上两条查询合并：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);#使用了比较运算符，而且使用了小括号

查询超极本类型的价格：
SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本';

查询价格大于超极本价格的商品：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本';#错误，因为子查询中返回的不是一个数据而是三条记录，WHERE中应该告诉系统大于哪个数据

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

  • operand comparison_operator ANY (subquery)
  • operand comparison_operator SOM(subquery)
  • operand comparison_operator ALL(subquery)

ANY、SOME是等价的，只要符合其中的一个就行，ALL是要符合全部

ANY、SOME、ALL关键字：


Paste_Image.png

ANY演示：
`SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price> ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

ALL演示：
`SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price> ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

等于ALL演示：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');#选择的其实就是子查询里查询的结果

由[NOT] IN/EXISTS引发的子查询

使用[NOT] IN的子查询
语法结构：
operand comparison_operator [NOT] IN (subquery)=ANY 运算符与IN等效。
!=ALL或<>ALL运算符与NOT IN等效

演示：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price <> ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');
等价于
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

使用[NOT] EXISTS的子查询
如果子查询返回任何行，EXISTS将返回TRUE；否则返回FALSE。

用的较少，子查询返回了结果EXISTS返回TRUE，否则返回FALSE

使用INSERT...SELECT插入记录

之前讲过INSERT 和 INSERT SET的区别是INSERT SET 可以使用子查询（SET 可以使XX=XX引发子查询）

tdb_goods表中有很多弊，存在这很多重复的信息，如品牌有很多索尼，分类中有很多笔记本配件，字符串比数字占的字节数多，如果记录越来越多，数据表就会越来越庞大，查找时速度就会变慢，最好的方法是使用外键，需要两张数据表。

创建分类表：

CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL
);

不用一条一条分类的插入，应该使用子查询
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;#一共有7类，需要写入分类表中
使用INSERT...SELECT将查询写入数据表：

INSERT...SELECT
将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

实现：
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;#不能省略插入表的列名，不用写VALUES
使用SELECT * FROM tdb_goods_cates;查看已经插入成功

但是还是没有使用外键，应该参照分类表来更新商品表

多表更新

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

CREATE...SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [ID NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

FROM子句中的子查询
语法结构
SELECT...FROM(subquery) [AS] name...
说明：
名称为必选项，且子查询的列名称必须唯一。

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

表的连接条件，第一张表+连接类型+第二张表+连接条件
语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

表的参照可以给表赋予别名也可以不赋予别名

连接类型
INNER JOIN,内连接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;#tdb_goods表中的goods_cate已经被更新

多表更新之一步到位

以上更新操作参照别的表更新了本表，一共使用了三步：
1.创建表
2.通过使用INSERT...SELECT把记录写入新表
3.多表更新

把三步合并为一步：
可以使用CREATE...SELECT实现：
CREATE...SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

将表中的品牌也独立出一张表，创建表的同时将查询的数据写入：

CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(49) NOT NULL
)
SELECT brand name FROM tdb_goods GROUP BY brand_name;

查看tdb_goods_brands表可以看到数据写入成功
还有一步应该参照品牌表更新商品表中的品牌：
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name SET brand_name = brand_id;#会报错，提示brand_name含义不明确，因为两张表中都有brand_name，系统不知道那两个brand_name是哪张表中的

要解决这个问题，只能给表起别名或者在字段前边加上表名
通常是给表起别名：
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;#更新成功
使用SELECT * FROM tdb_goods\G查看表中的记录，看到表中的brand_name已经被更新

此时，使用SHOW COLUMNS FROM tdb_goods;查看表结构发现表中的goods_cate和brand_name仍然是varchar类型，表中的数字代表的是字符而不是tdb_goods_cate和tdb_goods_brands中的id（数字型）

修改标结构：

ALTER TABEL tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

再使用SHOW COLUMNS FROM tdb_goods;查看表结构看到表结构已经修改成功goods_cate和brand_name修改成了数字类型

关于使用外键：不一定要使用FORIGN KEY物理外键，可以用这种外键，称为事实外键，通常较多使用事实外键，物理外键用的不多

在分类表和品牌表中插入一些记录：

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

插入三个不同的分类和三个不同的品牌

在商品表中插入记录：
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');#此时插入数据成功，但是有一个小错误，goods_cate写的是12，但是tdb_goods_cate表中并没有id为12的分类
把表中的记录查询出来呈现出来，存储时cate_id和brand_id存储的是其他表中的id，显示的时候就不能这样直接显示了，应该显示商品品牌和分类而不是id，这时就需要使用到连接了

连接的语法结构

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

三种连接：内连接，左外连接，右外连接
A表+连接类型+B表+连接条件
语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

数据表参照：
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

内连接INNER JOIN

连接类型：
INNER JOIN,内连接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接

连接条件:
使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件，
使用WHERE关键字进行结果集记录的过滤。

内链接：显示左表及右表符合连接条件的记录

内连接

实例：
SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;
可以看到22条记录，并没有刚刚添加的第23条记录，因为第23条记录不符合连接条件，刚才添加的cate_id是12，在tdb_goods_cate表中并不存在id为12的记录，而且刚刚新添加的几个分类也没有显示出来，这就是内连接（两张表都会有的才会显示出来），仅显示符合连接条件的记录

外连接OUTER JOIN

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

左外连接

演示：SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;#OUTER可以写，也可以不写
得到23条记录，但是第23条记录的cate_name为空,左外连接指的是左表中的全部和右表中符合条件的，如果右表中没有符合条件的会显示为NULL

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

右外连接

演示：SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;#OUTER可以写，也可以不写
得到25条记录，没有了那个cate_id为12的记录，又多了三条分类的记录，右外连接指的是显示右表中的全部和左表中符合连接条件的记录

这三种连接中内连接用的想对较多

多表连接

商品表中存在商品分类和品牌
实现三张表的连接：
演示：

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g 
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id 
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;

可以看到这是有恢复了我们最初原始的结果，不一样的是这次是通过多张表的连接实现的，以前是通过一张表查询出来的

其实表的连接就像是外键的逆向操作，外键把表分开存储，连接把多张表连接起来查询

关于连接的几点说明

A LEFT JOIN B join_condition

  • 数据表B的结果集依赖数据表A。
  • 数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。
  • 左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。
  • 如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。

查找到的结果为NULL但是含有约束NOT NULL的情况：
如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_namd IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接执行条件的记录后停止搜索更多的行。

无限极分类表设计

查看tdb_goods_cates表的记录，这些分类远远达不到现实中分类的要求，很多网站中，这些分类有很多级分类，一级分类、二级分类、三级分类……这种分类就是无限分类，数据表应该怎样设计，可以设计很多张表，随着分类的增多，表的数目也会逐渐增多，查找起来就不方便了，所以，一般都采用在表中增加父分类的id字段实现：

实例：

CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);#parent_id为父分类的id，为0表示没有父分类，为一级分类

然后插入数据：

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

那么问题来了：如何查询这张表

可以通过自身连接查询：
自身连接：同一个数据表对其自身进行连接

示例演示：
一张表做自身连接必须要起别名，要不就分不清这两个相同名称的字段从哪来的了
想象一下有两张相同的表，左边是父表，右边是子表
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;#就可以查到子类的id，子类的名字以及父类的名字

查找子类，父类以及父类下的子类：
左边是子表，右边是父表
SELECT p.type_id,p.type_name,s.typename FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;
含有重复的父类，使用GROUP BY分组：
SELECT p.type_id,p.type_name,s.typename 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 ;
可以看到只有15个分类了，按照id排序：

SELECT p.type_id,p.type_name,s.typename 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;

不要子类的名字，需要子类的数目：

SELECT p.type_id,p.type_name,count(s.typename) 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;

就可以看到子类的数量了

多表删除

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

使用SELECT * FROM tdb_goods;查看表中的记录，看到第18、19和第21、22条记录是重复的，这是，想要把重复的记录删除，保留id较小的记录。
可以通过多表删除实现，也就是采用一张表模拟两张表实现
演示：
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name;#从23条记录中得到了21个商品，因为有些记录是相同的

我们只想要相同商品名称超过两个以上的记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name) > 1;#得到重复商品的记录，这就是我们将要删除或者要保留的记录
可以通过这张表来删除原表中的数据：
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;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 158,736评论 4赞 362
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,167评论 1赞 291
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 108,442评论 0赞 243
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 43,902评论 0赞 204
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,302评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,573评论 1赞 216
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,847评论 2赞 312
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,562评论 0赞 197
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,260评论 1赞 241
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,531评论 2赞 245
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,021评论 1赞 258
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,367评论 2赞 253
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,016评论 3赞 235
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,068评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,827评论 0赞 194
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,610评论 2赞 274
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,514评论 2赞 269

推荐阅读更多精彩内容

  • mysql的查询、子查询及连接查询
    一、mysql查询的五种子句 where(条件查询)、having（筛选）、group by（分组）、order ...
    化城阅读 1,530评论 0赞 9
  • MySQL
    第1章 初涉MySQL 1.1 MySQL文件 （1）MySQL目录结构 （2）MySQL配置向导文件（安装后配置...
    凛0_0阅读 740评论 1赞 0
  • mysql入门篇
    mysql常用命令mysql> SELECT VERSION(); 查看当前MYSQL版本mysql> SELE...
    GeekAmI阅读 835评论 0赞 20
  • （六）子查询与连接
    1、创建练习使用的数据表 goods_id —— 商品编号goods_name —— 商品名称goods_cate...
    黒猫阅读 923评论 0赞 4
  • 哲学思维小工具（一）
    哲学离我们并不遥远，哲学源自生活，人人都可以像哲学家一样思考。 我们可以把哲学能力想象成一种工具，一种帮助我们思考...
    鱼生指教阅读 785评论 6赞 5