子查询
常用的比较运算符： =、>、<、>=、<=、<>、!=、<=>
修饰运算符
| 运算符 | 关键字 | 关键字 | 关键字 |
|---|---|---|---|
| ANY | SOME | ALL | |
、>= | 最小值 | 最小值 | 最大值 |
| <、<= | 最大值 | 最大值 | 最小值 |
| = | 任意值 | 任意值 | |
| <>、!= | 任意值 |
// 查找所有价格大于平均值的行
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods
-> WHERE goods_price > (SELECT ROUND(AVG(goods_price)) FROM tdb_goods)
-> ORDER BY goods_price;运算符=ANY 和 =SOME 相当于 IN
=ANY =SOME --IN
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY (SELECT goods_price FROM tdb_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 |
+----------+---------------------------------+-------------+> ANY 表示大于最小值
// > ANY
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_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 |
| 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 |
+----------+----------------------------------+-------------+> ALL表示大于最大值
// > ALL
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');
+----------+----------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+----------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
+----------+----------------------------------+-------------+IN 和NOT IN
NOT IN等效于！=ALL 或 <>ALL
// NOT IN 与 ！= ALL等效 ，以下查询不是超极本的电脑，两次查询的结果相同
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price != ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');
+----------+------------------------------------------------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+------------------------------------------------------------------------+-------------+
| 1 | R510VC 15.6英寸笔记本 | 3399.000 |
| 2 | Y400N 14.0英寸笔记本电脑 | 4899.000 |
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 4 | X550CC 15.6英寸笔记本 | 2799.000 |
| 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 1998.000 |
| 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 3388.000 |
| 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 2788.000 |
| 11 | IdeaCentre C340 20英寸一体电脑 | 3499.000 |
| 12 | Vostro 3800-R1206 台式电脑 | 2899.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 3699.000 |
| 15 | Z220SFF F4F06PA工作站 | 4288.000 |
| 16 | PowerEdge T110 II服务器 | 5388.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 19 | 商务双肩背包 | 99.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 21 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 22 | 商务双肩背包 | 99.000 |
+----------+------------------------------------------------------------------------+-------------+EXISTS
如果子查询返回任何行，EXISTS将返回TRUE ，否则返回FALSE
select * from t1 where exists (select * from t1 where id = 2);INSERT INTO tbl_name [(col_name,…)] SELECT …
使用查询插入数据表
创建分类表，用以上语句更新分类表
mysql> INSERT INTO tdb_goods_cate (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;多表更新
UPDATE tbl_name1 [LEFT|RIGHT]JOIN tbl_name2 ON condition SET name=value… WHERE condition..
// 按照分类表，将商品表中的数据更新
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cate
ON tdb_goods.goods_cate=tdb_goods_cate.cate_name
SET tdb_goods.goods_cate = tdb_goods_cate.id;创建表的同时插入数据
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_defition,…)] select_statement…
// 创建表的同时插入表信息
mysql> CREATE TABLE tdb_goods_brand(
-> id INT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40)
-> )
-> SELECT brand_name FROM tdb_goods GROUP BY brand_name;连接类型
- INNER JOIN ： 内链接。 显示左表和右表都存在的记录（显示交集）
- LEFT [OUTER] JOIN ： 左外连接 ，显示左表的所有记录和右表中满足条件的记录
RIGHT [OUUTER] JOIN ： 右外连接，显示右表的所有记录和左表中满足条件的记录
在MySQL中，JOIN,CROSS JOIN 和INNER JOIN 是等价的
- 用ON 关键字类设定连接条件，也可以使用WHERE来代替。通常使用ON关键字类设定连接条件。使用WHERE关键字今昔你个结果集记录的过滤
多表连接
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price,is_show ,is_saleoff
-> FROM tdb_goods g
-> JOIN tdb_goods_cate c on c.id = g.goods_cate_id
-> JOIN tdb_goods_brand b on b.id = g.brand_id;
// 多表查询
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price,is_show ,is_saleoff
FROM tdb_goods
INNER JOIN tdb_goods_cate ON tdb_goods.goods_cate_id = tdb_goods_cate.id
INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.id;
// 使用AS未每个表重命名
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price,is_show ,is_saleoff
-> FROM tdb_goods g
-> JOIN tdb_goods_cate c on c.id = g.goods_cate_id
-> JOIN tdb_goods_brand b on b.id = g.brand_id;
// 结果相同
+----------+------------------------------------------------------------------------+---------------+------------+-------------+---------+------------+
| goods_id | goods_name | cate_name | brand_name | goods_price | is_show | is_saleoff |
+----------+------------------------------------------------------------------------+---------------+------------+-------------+---------+------------+
| 1 | R510VC 15.6英寸笔记本 | 笔记本 | 华硕 | 3399.000 | 1 | 0 |
| 2 | Y400N 14.0英寸笔记本电脑 | 笔记本 | 联想 | 4899.000 | 1 | 0 |
| 3 | G150TH 15.6英寸游戏本 | 游戏本 | 雷神 | 8499.000 | 1 | 0 |
| 4 | X550CC 15.6英寸笔记本 | 笔记本 | 华硕 | 2799.000 | 1 | 0 |
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 超级本 | 联想 | 4999.000 | 1 | 0 |
| 6 | U330P 13.3英寸超极本 | 超级本 | 联想 | 4299.000 | 1 | 0 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 超级本 | 索尼 | 7999.000 | 1 | 0 |
| 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 平板电脑 | 苹果 | 1998.000 | 1 | 0 |
| 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 平板电脑 | 苹果 | 3388.000 | 1 | 0 |
| 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 平板电脑 | 苹果 | 2788.000 | 1 | 0 |
| 11 | IdeaCentre C340 20英寸一体电脑 | 台式机 | 联想 | 3499.000 | 1 | 0 |
| 12 | Vostro 3800-R1206 台式电脑 | 台式机 | 戴尔 | 2899.000 | 1 | 0 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 台式机 | 苹果 | 9188.000 | 1 | 0 |
| 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 台式机 | 宏碁 | 3699.000 | 1 | 0 |
| 15 | Z220SFF F4F06PA工作站 | 服务器/工作站 | 惠普 | 4288.000 | 1 | 0 |
| 16 | PowerEdge T110 II服务器 | 服务器/工作站 | 戴尔 | 5388.000 | 1 | 0 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 服务器/工作站 | 苹果 | 28888.000 | 1 | 0 |
| 18 | HMZ-T3W 头戴显示设备 | 笔记本配件 | 索尼 | 6999.000 | 1 | 0 |
| 19 | 商务双肩背包 | 笔记本配件 | 索尼 | 99.000 | 1 | 0 |
| 20 | X3250 M4机架式服务器 2583i14 | 服务器/工作站 | IBM | 6888.000 | 1 | 0 |
| 21 | HMZ-T3W 头戴显示设备 | 笔记本配件 | 索尼 | 6999.000 | 1 | 0 |
| 22 | 商务双肩背包 | 笔记本配件 | 索尼 | 99.000 | 1 | 0 |
+----------+------------------------------------------------------------------------+---------------+------------+-------------+---------+------------+无限分组：
// 使用左连接查询每个子类的父类
mysql> SELECT s.type_id AS son_id, s.type_name AS son_name,p.type_name AS parent_name
-> FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p
-> ON s.parent_id = p.type_id;
+--------+------------+-------------+
| son_id | son_name | parent_name |
+--------+------------+-------------+
| 1 | 家用电器 | NULL |
| 2 | 电脑、办公 | NULL |
| 3 | 大家电 | 家用电器 |
| 4 | 生活电器 | 家用电器 |
| 5 | 平板电视 | 大家电 |
| 6 | 空调 | 大家电 |
| 7 | 电风扇 | 生活电器 |
| 8 | 饮水机 | 生活电器 |
| 9 | 电脑整机 | 电脑、办公 |
| 10 | 电脑配件 | 电脑、办公 |
| 11 | 笔记本 | 电脑整机 |
| 12 | 超级本 | 电脑整机 |
| 13 | 游戏本 | 电脑整机 |
| 14 | CPU | 电脑配件 |
| 15 | 主机 | 电脑配件 |
+--------+------------+-------------+
使用左查询查询所有父类的子类
mysql> SELECT p.type_id parent_id, p.type_name parent_name, s.type_name son_name
-> FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s
-> ON p.type_id = s.parent_id
-> ORDER BY parent_id;
+-----------+-------------+----------+
| parent_id | parent_name | son_name |
+-----------+-------------+----------+
| 1 | 家用电器 | 大家电 |
| 1 | 家用电器 | 生活电器 |
| 2 | 电脑、办公 | 电脑整机 |
| 2 | 电脑、办公 | 电脑配件 |
| 3 | 大家电 | 平板电视 |
| 3 | 大家电 | 空调 |
| 4 | 生活电器 | 电风扇 |
| 4 | 生活电器 | 饮水机 |
| 5 | 平板电视 | NULL |
| 6 | 空调 | NULL |
| 7 | 电风扇 | NULL |
| 8 | 饮水机 | NULL |
| 9 | 电脑整机 | 笔记本 |
| 9 | 电脑整机 | 游戏本 |
| 9 | 电脑整机 | 超级本 |
| 10 | 电脑配件 | 主机 |
| 10 | 电脑配件 | CPU |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 13 | 游戏本 | NULL |
| 14 | CPU | NULL |
| 15 | 主机 | NULL |
+-----------+-------------+----------+
// 查看父类的子类个数
mysql> SELECT p.type_id AS parent_id, p.type_name AS parent_name, count(p.parent_id) AS son_count
-> FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s
-> ON p.type_id = s.parent_id
-> GROUP BY parent_name ORDER BY parent_id;
+-----------+-------------+-----------+
| parent_id | parent_name | son_count |
+-----------+-------------+-----------+
| 1 | 家用电器 | 2 |
| 2 | 电脑、办公 | 2 |
| 3 | 大家电 | 2 |
| 4 | 生活电器 | 2 |
| 5 | 平板电视 | 1 |
| 6 | 空调 | 1 |
| 7 | 电风扇 | 1 |
| 8 | 饮水机 | 1 |
| 9 | 电脑整机 | 3 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 1 |
| 12 | 超级本 | 1 |
| 13 | 游戏本 | 1 |
| 14 | CPU | 1 |
| 15 | 主机 | 1 |
+-----------+-------------+-----------+多表删除
DELETE tbl_name1,tbl_name2.. FROM table_references WHERE condidtions
同时删除多个表的记录
DELETE t1,t2 FROM t1,t2 WHERE t1.name = t2.name; 使用多表删除删除表中的重复数据
// 先用group by删选出名称相同的数量大于2的分组，筛选出的分组既是需要删除的记录
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> SELECT * FROM tdb_goods AS t1
-> INNER 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 ;
+----------+-----------------------+---------------+----------+-------------+---------+------------+----------+-----------------------+
| goods_id | goods_name | goods_cate_id | brand_id | goods_price | is_show | is_saleoff | goods_id | goods_name |
+----------+-----------------------+---------------+----------+-------------+---------+------------+----------+-----------------------+
| 18 | HMZ-T3W 头戴显示设备 | 37 | 6 | 6999.000 | 1 | 0 | 18 | HMZ-T3W 头戴显示设备 |
| 19 | 商务双肩背包 | 37 | 6 | 99.000 | 1 | 0 | 19 | 商务双肩背包 |
| 21 | HMZ-T3W 头戴显示设备 | 37 | 6 | 6999.000 | 1 | 0 | 18 | HMZ-T3W 头戴显示设备 |
| 22 | 商务双肩背包 | 37 | 6 | 99.000 | 1 | 0 | 19 | 商务双肩背包 |
+----------+-----------------------+---------------+----------+-------------+---------+------------+----------+-----------------------+
// 最后删除重复的记录
DELETE t1 FROM tdb_goods AS t1
INNER 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;
扫一扫
504
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
