Mysql子查询，连接，多表更新

男娘i 2023-06-03 12:56 11阅读 0赞

1.子查询是指在另一个查询语句中的SELECT子句。

例句：

SELECT \* FROM t1 WHERE column1 = (SELECT column1 FROM t2);

其中，SELECT \* FROM t1 ...称为Outer Query\[外查询\](或者Outer Statement),

SELECT column1 FROM t2 称为Sub Query\[子查询\]。

所以，我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询。

子查询必须出现在圆括号之间。

行级子查询

SELECT \* FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

SELECT \* FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);  
  
行级子查询的返回结果最多为一行。

优化子查询

\-- 创建数据表

CREATE TABLE IF NOT EXISTS tdb\_goods(  
goods\_id SMALLINT UNSIGNED PRIMARY KEY AUTO\_INCREMENT,  
goods\_name VARCHAR(150) NOT NULL,  
goods\_cate VARCHAR(40) NOT NULL,  
brand\_name VARCHAR(40) NOT NULL,  
goods\_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,  
is\_show BOOLEAN NOT NULL DEFAULT 1,  
is\_saleoff BOOLEAN NOT NULL DEFAULT 0  
);

\-- 写入记录

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);

\-- 求所有电脑产品的平均价格,并且保留两位小数，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 等价于 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;

\-- 创建“商品分类”表

CREATE TABLE IF NOT EXISTS tdb\_goods\_cates(

cate\_id SMALLINT UNSIGNED PRIMARY KEY AUTO\_INCREMENT,  
  
cate\_name VARCHAR(40)

);

\-- 查询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 tdb\_goods INNER JOIN tdb\_goods\_cates ON goods\_cate = cate\_name

SET goods\_cate = cate\_id ;

\-- 通过CREATE...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 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;

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

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');

\-- 查询所有商品的详细信息(通过内连接实现)

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\\G;

\-- 查询所有商品的详细信息(通过左外连接实现)

SELECT goods\_id,goods\_name,cate\_name,brand\_name,goods\_price FROM tdb\_goods AS g

LEFT JOIN tdb\_goods\_cates AS c ON g.cate\_id = c.cate\_id

LEFT JOIN tdb\_goods\_brands AS b ON g.brand\_id = b.brand\_id\\G;

\-- 查询所有商品的详细信息(通过右外连接实现)

SELECT goods\_id,goods\_name,cate\_name,brand\_name,goods\_price FROM tdb\_goods AS g

RIGHT JOIN tdb\_goods\_cates AS c ON g.cate\_id = c.cate\_id

RIGHT JOIN tdb\_goods\_brands AS b ON g.brand\_id = b.brand\_id\\G;

\-- 无限分类的数据表设计

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  
);

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;  
  
\-- 查找所有分类及其子类

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;

\-- 查找所有分类及其子类的数目

SELECT p.type\_id,p.type\_name,count(s.type\_name) AS children\_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;

\-- 为tdb\_goods\_types添加child\_count字段

ALTER TABLE tdb\_goods\_types ADD child\_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;

\-- 将刚才查询到的子类数量更新到tdb\_goods\_types数据表

UPDATE tdb\_goods\_types AS t1 INNER JOIN ( SELECT p.type\_id,p.type\_name,count(s.type\_name) AS children\_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.children\_count;

\-- 复制编号为12,20的两条记录

SELECT \* FROM tdb\_goods WHERE goods\_id IN (19,20);

\-- INSERT ... SELECT实现复制

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);

\-- 查找重复记录

SELECT goods\_id,goods\_name FROM tdb\_goods GROUP BY goods\_name HAVING count(goods\_name) >= 2;

\-- 删除重复记录

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;

转载于:https://www.cnblogs.com/luchongjian/p/4625879.html