子查询:是指出现在其他SQL语句内的SELECT语句。子查询指嵌套在查询内部,且必须始终出现在圆括号内。
子查询返回值:可以是标量、一行、一列或子查询。
一、使用比较运算符的子查询:=、>、>=......
如果子查询在返回多个结果的时候,可以用SOME,ANY,ALL来修饰;
mysql> select avg(price) from goods;
+--------------+
| avg(price) |
+--------------+
| 4440.1764706 |
+--------------+
1 row in set (0.01 sec)
mysql> select round(avg(price),2) from goods;
+---------------------+
| round(avg(price),2) |
+---------------------+
| 4440.18 |
+---------------------+
1 row in set (0.00 sec)
mysql> select id,name,price from goods where price>= 4440.18;
+----+----------+-----------+
| id | name | price |
+----+----------+-----------+
| 1 | L1 | 4999.000 |
| 7 | D1 | 5399.000 |
| 8 | D2 | 5599.000 |
| 9 | D3 | 4599.000 |
| 11 | D5 | 6599.000 |
| 12 | MackBook | 13999.000 |
| 13 | MackAir | 6999.000 |
| 14 | MackMINI | 5999.000 |
+----+----------+-----------+
8 rows in set (0.00 sec)
mysql> select id,name,price from goods where price>= (select round(avg(price),2) from goods);
+----+----------+-----------+
| id | name | price |
+----+----------+-----------+
| 1 | L1 | 4999.000 |
| 7 | D1 | 5399.000 |
| 8 | D2 | 5599.000 |
| 9 | D3 | 4599.000 |
| 11 | D5 | 6599.000 |
| 12 | MackBook | 13999.000 |
| 13 | MackAir | 6999.000 |
| 14 | MackMINI | 5999.000 |
+----+----------+-----------+
8 rows in set (0.01 sec)
mysql> select * from goods where price>any(select price from goods where brand_name='DELL');
+----+----------+-----------------+------------+-----------+---------+------------+
| id | name | cate | brand_name | price | is_show | is_saleoff |
+----+----------+-----------------+------------+-----------+---------+------------+
| 1 | L1 | 笔记本电脑 | Leveno | 4999.000 | 1 | 0 |
| 2 | L2 | 笔记本电脑 | Leveno | 3999.000 | 1 | 0 |
| 7 | D1 | 笔记本电脑 | DELL | 5399.000 | 1 | 0 |
| 8 | D2 | 笔记本电脑 | DELL | 5599.000 | 1 | 0 |
| 9 | D3 | 笔记本电脑 | DELL | 4599.000 | 1 | 0 |
| 11 | D5 | 笔记本电脑 | DELL | 6599.000 | 1 | 0 |
| 12 | MackBook | 笔记本电脑 | Apple | 13999.000 | 1 | 0 |
| 13 | MackAir | 笔记本电脑 | Apple | 6999.000 | 1 | 0 |
| 14 | MackMINI | 平板电脑 | Apple | 5999.000 | 1 | 0 |
| 16 | pad-pro | 平板电脑 | Apple | 3999.000 | 1 | 0 |
+----+----------+-----------------+------------+-----------+---------+------------+
10 rows in set (0.00 sec)
mysql> select * from goods where price>all(select price from goods where brand_name='DELL');
+----+----------+-----------------+------------+-----------+---------+------------+
| id | name | cate | brand_name | price | is_show | is_saleoff |
+----+----------+-----------------+------------+-----------+---------+------------+
| 12 | MackBook | 笔记本电脑 | Apple | 13999.000 | 1 | 0 |
| 13 | MackAir | 笔记本电脑 | Apple | 6999.000 | 1 | 0 |
+----+----------+-----------------+------------+-----------+---------+------------+
2 rows in set (0.00 sec)
mysql>
二、使用[NOT] IN 的子查询
operand comparison_operator [NOT] IN (subquery) ;
=ANY 运算符 与 IN 等效;
!=ALL 或 <> ALL 运算符 与 NOT IN 等效;
mysql> select * from goods where price in(select price from goods where brand_name='DELL');
+----+------+-----------------+------------+----------+---------+------------+
| id | name | cate | brand_name | price | is_show | is_saleoff |
+----+------+-----------------+------------+----------+---------+------------+
| 7 | D1 | 笔记本电脑 | DELL | 5399.000 | 1 | 0 |
| 8 | D2 | 笔记本电脑 | DELL | 5599.000 | 1 | 0 |
| 9 | D3 | 笔记本电脑 | DELL | 4599.000 | 1 | 0 |
| 10 | D4 | 笔记本电脑 | DELL | 3599.000 | 1 | 0 |
| 11 | D5 | 笔记本电脑 | DELL | 6599.000 | 1 | 0 |
+----+------+-----------------+------------+----------+---------+------------+
5 rows in set (0.00 sec)
mysql> select * from goods where price not in(select price from goods where brand_name='DELL');
+----+-----------+-----------------------+------------+-----------+---------+------------+
| id | name | cate | brand_name | price | is_show | is_saleoff |
+----+-----------+-----------------------+------------+-----------+---------+------------+
| 1 | L1 | 笔记本电脑 | Leveno | 4999.000 | 1 | 0 |
| 2 | L2 | 笔记本电脑 | Leveno | 3999.000 | 1 | 0 |
| 3 | L3 | 笔记本电脑 | Leveno | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 笔记本电脑配件 | Leveno | 399.000 | 1 | 0 |
| 5 | 键盘 | 笔记本电脑配件 | Leveno | 699.000 | 1 | 0 |
| 6 | 鼠标 | 笔记本电脑配件 | Leveno | 199.000 | 1 | 0 |
| 12 | MackBook | 笔记本电脑 | Apple | 13999.000 | 1 | 0 |
| 13 | MackAir | 笔记本电脑 | Apple | 6999.000 | 1 | 0 |
| 14 | MackMINI | 平板电脑 | Apple | 5999.000 | 1 | 0 |
| 15 | pad | 平板电脑 | Apple | 2999.000 | 1 | 0 |
| 16 | pad-pro | 平板电脑 | Apple | 3999.000 | 1 | 0 |
| 17 | pad-mini | 平板电脑 | Apple | 1999.000 | 0 | 1 |
+----+-----------+-----------------------+------------+-----------+---------+------------+
12 rows in set (0.00 sec)
mysql>
三、使用[NOT] EXISTS 的子查询;
insert [into] tb_name [(col_name,...)] select ...;将查询的结果插入到表中;
mysql> create table if not exists goods_cates(
-> id smallint unsigned primary key auto_increment,
-> cate_name varchar(40) not null);
Query OK, 0 rows affected (0.12 sec)
mysql> select * from goods_cates;
Empty set (0.01 sec)
mysql> insert goods_cates(cate_name) select cate from goods group by cate;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from goods_cates;
+----+-----------------------+
| id | cate_name |
+----+-----------------------+
| 1 | 笔记本电脑 |
| 2 | 笔记本电脑配件 |
| 3 | 平板电脑 |
+----+-----------------------+
3 rows in set (0.00 sec)
mysql>
四、多表更新;
table_references
{[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 ,右外连接;
mysql> update goods inner join goods_cates on cate = cate_name
-> set cate = goods_cates.id;
Query OK, 17 rows affected (0.07 sec)
Rows matched: 17 Changed: 17 Warnings: 0
mysql> select * from goods;
+----+-----------+------+------------+-----------+---------+------------+
| id | name | cate | brand_name | price | is_show | is_saleoff |
+----+-----------+------+------------+-----------+---------+------------+
| 1 | L1 | 1 | Leveno | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | Leveno | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | Leveno | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 2 | Leveno | 399.000 | 1 | 0 |
| 5 | 键盘 | 2 | Leveno | 699.000 | 1 | 0 |
| 6 | 鼠标 | 2 | Leveno | 199.000 | 1 | 0 |
| 7 | D1 | 1 | DELL | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | DELL | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | DELL | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | DELL | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | DELL | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | Apple | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | Apple | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | Apple | 5999.000 | 1 | 0 |
| 15 | pad | 3 | Apple | 2999.000 | 1 | 0 |
| 16 | pad-pro | 3 | Apple | 3999.000 | 1 | 0 |
| 17 | pad-mini | 3 | Apple | 1999.000 | 0 | 1 |
+----+-----------+------+------------+-----------+---------+------------+
17 rows in set (0.00 sec)
mysql>
创建数据表的同时将查询结果写入到数据表:
create table tb_name
[(create_definition,...)]
select_statement;
mysql> create table goods_brands(
-> brand_id smallint unsigned primary key auto_increment
-> ,
-> brand_name varchar(40) not null
-> )
-> select brand_name from goods group by brand_name;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> update goods as g inner join goods_brands as b on g.brand_name = b.brand_name
-> set g.brand_name = b.brand_id;
Query OK, 17 rows affected (0.08 sec)
Rows matched: 17 Changed: 17 Warnings: 0
mysql> alter table goods
-> change cate cate_id smallint unsigned not null,
-> change brand_name brand_id smallint unsigned not null;
Query OK, 17 rows affected (0.09 sec)
Records: 17 Duplicates: 0 Warnings: 0
mysql> select * from goods;
+----+-----------+---------+----------+-----------+---------+------------+
| id | name | cate_id | brand_id | price | is_show | is_saleoff |
+----+-----------+---------+----------+-----------+---------+------------+
| 1 | L1 | 1 | 1 | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | 1 | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | 1 | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 2 | 1 | 399.000 | 1 | 0 |
| 5 | 键盘 | 2 | 1 | 699.000 | 1 | 0 |
| 6 | 鼠标 | 2 | 1 | 199.000 | 1 | 0 |
| 7 | D1 | 1 | 2 | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | 2 | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | 2 | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | 2 | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | 3 | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | 3 | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | 3 | 5999.000 | 1 | 0 |
| 15 | pad | 3 | 3 | 2999.000 | 1 | 0 |
| 16 | pad-pro | 3 | 3 | 3999.000 | 1 | 0 |
| 17 | pad-mini | 3 | 3 | 1999.000 | 0 | 1 |
+----+-----------+---------+----------+-----------+---------+------------+
17 rows in set (0.00 sec)
mysql>
五、连接;MySQL在select语句、多表更新、多表删除语句中支持JOIN操作。
table_references
{[INNER | CROSS] JOIN} | {LEFT | RIGHT} [OUTER] JOIN }
table_reference
ON conditional_expr
数据表可以使用tb_name AS alias_name或 tb_name alias_name 赋予别名;(名字重复);
连接条件:
内连接:相当于A&B;
左外连接:相当于左表的全部以及符合条件的右表的记录;
右外连接:相当于右表的全部以及符合条件的左表的记录;
mysql> select name,cate_name,brand_name from goods as g
-> inner join goods_cates as c on g.cate_id = c.id
-> inner join goods_brands as b on g.brand_id = b.brand_id
-> ;
+-----------+-----------------------+------------+
| name | cate_name | brand_name |
+-----------+-----------------------+------------+
| L1 | 笔记本电脑 | Leveno |
| L2 | 笔记本电脑 | Leveno |
| L3 | 笔记本电脑 | Leveno |
| 摄像头 | 笔记本电脑配件 | Leveno |
| 键盘 | 笔记本电脑配件 | Leveno |
| 鼠标 | 笔记本电脑配件 | Leveno |
| D1 | 笔记本电脑 | DELL |
| D2 | 笔记本电脑 | DELL |
| D3 | 笔记本电脑 | DELL |
| D4 | 笔记本电脑 | DELL |
| D5 | 笔记本电脑 | DELL |
| MackBook | 笔记本电脑 | Apple |
| MackAir | 笔记本电脑 | Apple |
| MackMINI | 平板电脑 | Apple |
| pad | 平板电脑 | Apple |
| pad-pro | 平板电脑 | Apple |
| pad-mini | 平板电脑 | Apple |
+-----------+-----------------------+------------+
17 rows in set (0.00 sec)
mysql>
六、自身连接:数据表对自身的连接;
mysql> select * from goods_types;
+---------+-----------------+-----------+
| type_id | type_name | parent_id |
+---------+-----------------+-----------+
| 1 | 家用电器 | 0 |
| 2 | 电风扇 | 1 |
| 3 | 洗衣机 | 1 |
| 4 | 彩电 | 1 |
| 5 | 电脑 | 0 |
| 6 | 笔记本电脑 | 5 |
| 7 | 台式电脑 | 5 |
+---------+-----------------+-----------+
7 rows in set (0.00 sec)
mysql> select s.type_id,s.type_name,p.type_name from goods_types as s left join
-> goods_types as p
-> on s.parent_id = p.type_id;
+---------+-----------------+--------------+
| type_id | type_name | type_name |
+---------+-----------------+--------------+
| 1 | 家用电器 | NULL |
| 2 | 电风扇 | 家用电器 |
| 3 | 洗衣机 | 家用电器 |
| 4 | 彩电 | 家用电器 |
| 5 | 电脑 | NULL |
| 6 | 笔记本电脑 | 电脑 |
| 7 | 台式电脑 | 电脑 |
+---------+-----------------+--------------+
7 rows in set (0.00 sec)
mysql> select p.type_id,p.type_name,s.type_name from goods_types as p
-> left join goods_types as s
-> on s.parent_id = p.type_id;
+---------+-----------------+-----------------+
| type_id | type_name | type_name |
+---------+-----------------+-----------------+
| 1 | 家用电器 | 电风扇 |
| 1 | 家用电器 | 洗衣机 |
| 1 | 家用电器 | 彩电 |
| 5 | 电脑 | 笔记本电脑 |
| 5 | 电脑 | 台式电脑 |
| 2 | 电风扇 | NULL |
| 3 | 洗衣机 | NULL |
| 4 | 彩电 | NULL |
| 6 | 笔记本电脑 | NULL |
| 7 | 台式电脑 | NULL |
+---------+-----------------+-----------------+
10 rows in set (0.00 sec)
mysql> select p.type_id,p.type_name,s.type_name from goods_types as p
-> left join goods_types as s
-> on s.parent_id = p.type_id group by p.type_name;
+---------+-----------------+-----------------+
| type_id | type_name | type_name |
+---------+-----------------+-----------------+
| 1 | 家用电器 | 电风扇 |
| 5 | 电脑 | 笔记本电脑 |
| 2 | 电风扇 | NULL |
| 3 | 洗衣机 | NULL |
| 4 | 彩电 | NULL |
| 6 | 笔记本电脑 | NULL |
| 7 | 台式电脑 | NULL |
+---------+-----------------+-----------------+
7 rows in set (0.00 sec)
mysql>
七、多表删除:去重(去除id大的重复数据);
mysql> select * from goods;
+----+-----------+---------+----------+-----------+---------+------------+
| id | name | cate_id | brand_id | price | is_show | is_saleoff |
+----+-----------+---------+----------+-----------+---------+------------+
| 1 | L1 | 1 | 1 | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | 1 | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | 1 | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 2 | 1 | 399.000 | 1 | 0 |
| 5 | 键盘 | 2 | 1 | 699.000 | 1 | 0 |
| 6 | 鼠标 | 2 | 1 | 199.000 | 1 | 0 |
| 7 | D1 | 1 | 2 | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | 2 | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | 2 | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | 2 | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | 3 | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | 3 | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | 3 | 5999.000 | 1 | 0 |
| 15 | pad | 3 | 3 | 2999.000 | 1 | 0 |
| 16 | pad-pro | 3 | 3 | 3999.000 | 1 | 0 |
| 17 | pad-mini | 3 | 3 | 1999.000 | 0 | 1 |
| 25 | pad | 3 | 3 | 2999.000 | 1 | 0 |
| 26 | pad | 3 | 3 | 2999.000 | 1 | 0 |
| 27 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 28 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
+----+-----------+---------+----------+-----------+---------+------------+
21 rows in set (0.00 sec)
mysql> delete t1 from goods as t1 left join (select id,name from goods group by name having count(name)>= 2) as t2
-> on t1.name = t2.name where t1.id>t2.id;
Query OK, 4 rows affected (0.02 sec)
mysql> select * from goods;
+----+-----------+---------+----------+-----------+---------+------------+
| id | name | cate_id | brand_id | price | is_show | is_saleoff |
+----+-----------+---------+----------+-----------+---------+------------+
| 1 | L1 | 1 | 1 | 4999.000 | 1 | 0 |
| 2 | L2 | 1 | 1 | 3999.000 | 1 | 0 |
| 3 | L3 | 1 | 1 | 3399.000 | 1 | 0 |
| 4 | 摄像头 | 2 | 1 | 399.000 | 1 | 0 |
| 5 | 键盘 | 2 | 1 | 699.000 | 1 | 0 |
| 6 | 鼠标 | 2 | 1 | 199.000 | 1 | 0 |
| 7 | D1 | 1 | 2 | 5399.000 | 1 | 0 |
| 8 | D2 | 1 | 2 | 5599.000 | 1 | 0 |
| 9 | D3 | 1 | 2 | 4599.000 | 1 | 0 |
| 10 | D4 | 1 | 2 | 3599.000 | 1 | 0 |
| 11 | D5 | 1 | 2 | 6599.000 | 1 | 0 |
| 12 | MackBook | 1 | 3 | 13999.000 | 1 | 0 |
| 13 | MackAir | 1 | 3 | 6999.000 | 1 | 0 |
| 14 | MackMINI | 3 | 3 | 5999.000 | 1 | 0 |
| 15 | pad | 3 | 3 | 2999.000 | 1 | 0 |
| 16 | pad-pro | 3 | 3 | 3999.000 | 1 | 0 |
| 17 | pad-mini | 3 | 3 | 1999.000 | 0 | 1 |
+----+-----------+---------+----------+-----------+---------+------------+
17 rows in set (0.00 sec)
mysql>