mysql学习笔记之连接查询与子查询_subquery uses ungrouped column &quot；miv

2401_84103344

于 2024-04-26 22:47:46 发布

阅读量921

点赞数 12

分类专栏：程序员文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/2401_84103344/article/details/138232844

版权

程序员专栏收录该内容

232 篇文章 0 订阅

订阅专栏

 SELECT \* FROM tdb\_goods\G;

显示乱码 - 因为存入数据的时候用的是UTF-8的编码

如果要正确显示出来 ，则需要用 gbk的编码方式显示。

set names gbk:表示只是设置客户端的显示编码，不会影响数据编码

– 写入记录

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);

 INSERT tdb\_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 tdb\_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 tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);

 INSERT tdb\_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 tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb\_goods (goods\_name,goods\_cate,brand\_name,goods\_price,is\_show,is\_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

子查询注意点：

（1）子查询指嵌套在查询内部，且必须始终出现在圆括号内。
（2）子查询可以包含多个关键字或条件，如：
DISTINCT / GROUP BY / ORDER BY / LIMIT / 函数等。
（3）子查询的外层查询可以是：SELECT, INSERT, UPDATE , SET 或DO

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

SELECT \* FROM table\_name1 WHERE column\_name = (SELECT column\_name2 FROM table\_name2);

由于比较引起的子查询

– 求所有电脑产品的平均价格,并且保留两位小数，AVG,MAX,MIN、COUNT、SUM为聚合函数

 SELECT ROUND(AVG(goods\_price),2) AS avg\_price FROM tdb\_goods;

– 查询所有价格大于平均价格的商品，并且按价格降序排序

 SELECT goods\_id,goods\_name,goods\_price FROM tdb\_goods WHERE goods\_price > 5845.10 ORDER BY goods\_price DESC;

– 使用子查询来实现

 SELECT goods\_id,goods\_name,goods\_price FROM tdb\_goods 
WHERE goods\_price > (SELECT ROUND(AVG(goods\_price),2) AS avg\_price FROM tdb\_goods) ORDER BY goods\_price DESC;

– 查询类型为“超记本”的商品价格

 SELECT goods\_price FROM tdb\_goods WHERE goods\_cate = '超级本';

– 查询价格大于或等于”超级本”价格的商品，并且按价格降序排列

  SELECT goods\_id,goods\_name,goods\_price FROM tdb\_goods 

 WHERE goods\_price = ANY(SELECT goods\_price FROM tdb\_goods WHERE goods\_cate = '超级本')

 ORDER BY goods\_price DESC;

在子查询时的关键字ANY / SOME / ALL(由于比较产生的子查询，ANY和SOME一样，可以只记ANY和ALL)

– = ANY 或 = SOME 等价于 IN（在之内）；！=ALL NOT IN(不在之内)

  SELECT goods\_id,goods\_name,goods\_price FROM tdb\_goods 

 WHERE goods\_price IN (SELECT goods\_price FROM tdb\_goods WHERE goods\_cate = '超级本')

 ORDER BY goods\_price DESC;

1. 查询价格大于（平均价格）的笔记本的一些信息。

select goods\_id,goods\_name,goods\_price from tdb\_goods where goods\_price > (select round(avg(goods\_price),2) as avg\_price from tdb\_goods);

2. 查询价格大于超极本价格的笔记本的一些信息。

select goods\_id,goods\_name,goods\_price from tdb\_goods where goods\_price > ALL(SELECT goods\_price FROM tdb\_goods WHERE goods\_cate = '超级本');

因为表一 tdb_goods表有很多重复的内容，比如商品类别是一样的，存储了很多一样的东西，占用很多内存，量大了，查找起来不方便，可以重新构造一张表，通过外键约束来减少表的存储；

– 创建“商品分类”表

 CREATE TABLE IF NOT EXISTS tdb\_goods\_cates(

 cate\_id SMALLINT UNSIGNED PRIMARY KEY AUTO\_INCREMENT,

 cate\_name VARCHAR(40)

 );

INSERT … SELECT …可以使用子查询

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

INSERT INTO table\_name [(column\_name)] SELECT column\_name2 FROM table\_name2 GROUP BY column\_name3;

如何剥离出商品类别？ 用 groupBy

insert into tdb\_goods\_cates (cate\_name) select goods\_cate from tdb\_goods group by goods\_cate;

– 查询tdb_goods表的所有记录，并且按”类别”分组

  SELECT goods\_cate FROM tdb\_goods GROUP BY goods\_cate;

– 将分组结果写入到tdb_goods_cates数据表

  INSERT tdb\_goods\_cates (cate\_name) SELECT goods\_cate FROM tdb\_goods GROUP BY goods\_cate;

???如何将商品分类表的信息更新到商品表呢？===》多表更新

– 通过tdb_goods_cates数据表来更新tdb_goods表

多表更新：更新语句的语法。

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

INNER JOIN,内连接

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

LEFT [OUTER] JOIN ,左外连接
RIGHT [OUTER] JOIN,右外连接

1.左连接：

update tdb\_goods inner join tdb\_goods\_cates on goods\_cate=cate\_name set goods\_cate=cate\_id;

tdb_goods：想要更改的表名
inner join：内连接
tdb_goods_cates：关联的附表
goods_cate=cate_name 两个表对应列的关系

goods_cate=cate_id; 设置值

UPDATE tdb\_goods INNER JOIN tdb\_goods\_cates ON goods\_cate = cate\_name 

 SET goods\_cate = cate\_id ;

– 通过CREATE…SELECT来创建数据表并且同时写入记录

create….select 创建商标表

创建数据表的同时将查询结果写入到数据表
create table 表名（字段1，字段2）直接跟select语句

– SELECT brand_name FROM tdb_goods GROUP BY brand_name;

 CREATE TABLE tdb\_goods\_brands (

 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_brands数据表来更新tdb_goods数据表(错误)

UPDATE tdb\_goods INNER JOIN tdb\_goods\_brands ON brand\_name = brand\_name

 SET brand\_name = brand\_id;

– Column ‘brand_name’ in field list is ambigous(语意不明)

– 正确

update tdb\_goods inner join tdb\_goods\_brands on tdb\_goods.brand\_name = tdb\_goods\_brands.brand\_name
set tdb\_goods.brand\_name = tdb\_goods\_brands.brand\_id;

–或者给两个表起别名；这样语音就清楚了；

  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;

– 查看tdb_goods的数据表结构

  DESC tdb_goods;

发现和原来的数据结构是一样的，没有减少存储的量，要改变其数据结构；

    goods_cate  VARCHAR(40)  NOT NULL,
    brand_name  VARCHAR(40)  NOT NULL,

– 通过ALTER TABLE语句修改数据表结构

使用 MODIFY COLUMN 不用输入2次列名。
更改表的数据结构~

alter table tdb\_goods
modify goods\_cate smallint unsigned not null,
modify brand\_name smallint unsigned not null;

使用 CHANGE COLUMN 来修改列的数据类型，还必须设置列名。

  ALTER TABLE tdb\_goods 

 CHANGE goods\_cate cate\_id SMALLINT UNSIGNED NOT NULL,

 CHANGE brand\_name brand\_id SMALLINT UNSIGNED NOT NULL;

– 分别在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');

在上述几张表中，插入了数据，这都没有问题，但是在tdb_goods表中，插入的cate_id，brand_id分别是’12’,’4’，要呈现给用户看的不是’12’,’4’，而是其对应的
商品分类和商品品牌；

如何让多表进行协作呢？~~~通过连接来实现；

连接

1、MySQL这SELECT语句，多表更新，多表删除语句中都支持JOIN操作
2、连接有三种：INNER JOIN 内连接，LEFT JOIN左外连接，RIGHT JOIN右外连接
3、语法结构：

table reference A
{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference B
ON condition_expr;

ON后面是连接条件,
数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名(两个表有相同字段是很有用)。
table_subquery可以作为子查询使用在FROM子句中，这样的from子查询必须为其赋予别名。

1、内连接：在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的
2、外连接：LEFT JOIN左外连接；RIGHT JOIN右外连接
3、连接条件：使用ON设定连接条件，也可以用WHERE代替
· ON：设定连接条件
· WHERE：进行结果集记录的过滤

内连接图片

只显示连接条件的记录，是多张表的交集；

左外链接显示左表的全部记录右表如果未查到显示为null

右外链接显示右表的全部记录左表如果未查到显示为null

SELECT goods\_id,goods\_name,cate\_name FROM tdb\_goods INNER JOIN tdb\_goods\_cate ON tdb\_goods.cate\_id = tdb\_goods\_cate.cate\_id;

显示的结果中，并没有上次插入的几条记录，tdb_goods_cates(‘路由器’),(‘交换机’),(‘网卡’)以及 tdb_goods黑白激光打印机，因为这些只在单张表中存在；

左连接：

这里写图片描述

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;

新增一条记录为tdb_goods黑白激光打印机。左边有goods_id，goods_name 的cate_id = 12黑白激光打印机，而右表tdb_goods_cates中没有cate_id = 12的数据，显示为null

总结

这份面试题几乎包含了他在一年内遇到的所有面试题以及答案，甚至包括面试中的细节对话以及语录，可谓是细节到极致，甚至简历优化和怎么投简历更容易得到面试机会也包括在内！也包括教你怎么去获得一些大厂，比如阿里，腾讯的内推名额！

某位名人说过成功是靠99%的汗水和1%的机遇得到的，而你想获得那1%的机遇你首先就得付出99%的汗水！你只有朝着你的目标一步一步坚持不懈的走下去你才能有机会获得成功！

成功只会留给那些有准备的人！

db_goods.cate_id = tdb_goods_cates.cate_id;


新增一条记录为`tdb_goods`黑白激光打印机。左边有`goods_id`，`goods_name` 的`cate_id` = 12黑白激光打印机，而右表`tdb_goods_cates`中没有`cate_id` = 12的数据，显示为null




## 总结

> 这份面试题几乎包含了他在一年内遇到的所有面试题以及答案，甚至包括面试中的细节对话以及语录，可谓是细节到极致，甚至简历优化和怎么投简历更容易得到面试机会也包括在内！也包括教你怎么去获得一些大厂，比如阿里，腾讯的内推名额！


某位名人说过成功是靠99%的汗水和1%的机遇得到的，而你想获得那1%的机遇你首先就得付出99%的汗水！你只有朝着你的目标一步一步坚持不懈的走下去你才能有机会获得成功！

成功只会留给那些有准备的人！

[外链图片转存中...(img-QZGKyoOp-1714142855612)]

> **本文已被[CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】](https://bbs.csdn.net/topics/618154847)收录**

2401_84103344

关注

12
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
mysql学习笔记之连接查询与子查询_subquery uses ungrouped column &quot；miv

这份面试题几乎包含了他在一年内遇到的所有面试题以及答案，甚至包括面试中的细节对话以及语录，可谓是细节到极致，甚至简历优化和怎么投简历更容易得到面试机会也包括在内！也包括教你怎么去获得一些大厂，比如阿里，腾讯的内推名额！某位名人说过成功是靠99%的汗水和1%的机遇得到的，而你想获得那1%的机遇你首先就得付出99%的汗水！你只有朝着你的目标一步一步坚持不懈的走下去你才能有机会获得成功！成功只会留给那些有准备的人！新增一条记录为`tdb_goods`黑白激光打印机。
复制链接

扫一扫