MySQL---子查询、表的连接

来源：互联网发布：java sftp 上传文件编辑：程序博客网时间：2024/05/21 07:12

子查询

常用的比较运算符： =、>、<、>=、<=、<>、!=、<=>

修饰运算符

运算符关键字关键字关键字 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 --INmysql> 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;

阅读全文

0 0