MySQL学习笔记-子查询和连接
使客户端进入gbk编码方式显示：
mysql> SET NAMES gbk;
1.子查询
子查询的定义：
子查询（Subquery)是指出现在其他SQL语句内的SELECT子句。
例如：
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中SELECT * FROM t1 称为Outer Query / Outer Statement （外部查询）
SELECT col2 FROM t2 ， 被称为SubQuery （子查询）
子查询的条件：
子查询指嵌套在查询内部 ，且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件，
如：DISTINCT，GROUP BY，ORDER BY，LIMIT函数等。
子查询的外部查询可以是：SELECT , INSERT , UPDATE , SET 或 DO
子查询的返回值：
子查询可以返回标量、一行、一列或子查询
2.使用比较运算符的子查询
使用比较运算符的子查询：
=、>、<、>=、<=、<>、！=、 <=>
语法结构：
operand（操作数）、comparison_operator （比较运算符）、subquery（子查询）
用ANY 、SOME 或ALL修饰的比较运算符
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
1.语法结构 操作数 比较运算符 any（子查询）
操作数 比较运算符 some（子查询）
操作数 比较运算符 all（子查询）
2.适合于子查询有多个结果
3.any 和some结果一致 all与any、some 相反
例，从tdb_goods表中 查询平均价格，小数点保留2位，：
mysql> SELECT ROUND(avg(goods_price),2) AS avg_price FROM tdb_goods;+-----------+| avg_price |+-----------+| 5391.30 |+-----------+
查询平均价格以上的商品：
mysql> SELECT * FROM tdb_goods WHERE goods_price > 5391.30;
mysql> SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
查询超极本的列表，按价格升序排列：
mysql> SELECT * FROM tdb_goods WHERE goods_cate = '超级本' ORDER BY goods_price ASC;
mysql> SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;
mysql> SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本') ORDER BY goods_price DESC;
3.使用[NOT]IN/EXISTS引发的子查询
子查询形式：
1.使用IN的子查询
2.使用[NOT]IN的子查询
语法结构：operand comparison_operator [NOT] IN (subquery)
=ANY 运算符与IN 等效，!=ALL或<>ALL运算符与NOT IN等效
3.使用[NOT]EXISTS的子查询(用的相对较少)
如果子查询返回任何行，EXISTS将返回TRUE，否则为FALSE
例，查找不是超极本的商品列表：
mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE goods_cate = '超级本');
4.使用INSERT...SELECT插入记录
例：在tdb_goods_cates表中插入tdb_goods表中的goods_cate分类；
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
mysql> SELECT * FROM tdb_goods_cates;+---------+---------------------+| cate_id | cate_name |+---------+---------------------+| 1 | 台式机 || 2 | 平板电脑 || 3 | 服务器/工作站 || 4 | 游戏本 || 5 | 笔记本 || 6 | 笔记本配件 || 7 | 超级本 |+---------+---------------------+
5.多表更新
多表更新：
UPDATE table_references SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]
其中，table_references即连接的语法结构
连接类型：
INNER JOIN，内连接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接
连接—语法结构
table_reference
{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}
table_referenceON conditional_expr
例，将tdb_goods中的goods_cate更新为tdb_goods_cate表中的对应cate_id。
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
6.多表更新之一步到位
CREATE...SELECT
创建数据表同时将查询结果写入到数据表（合并了CREATE 和 INSERT...SELECT两个操作步骤）
CREATE TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)]select_statement
例：创建品牌分类数据表tdb_goods_brand，并将tdb_goods表中的brand_name写入
mysql> CREATE TABLE tdb_goods_brand( -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> brand_name VARCHAR(40) NOT NULL
-> ) -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous
解决方法是给表起别名：
mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;
mysql> DESC tdb_goods;+-------------+------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+------------------------+------+-----+---------+----------------+| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || goods_name | varchar(150) | NO | | NULL | || goods_cate | varchar(40) | NO | | NULL | || brand_name | varchar(40) | NO | | NULL | || goods_price | decimal(15,3) unsigned | NO | | 0.000 | || is_show | tinyint(1) | NO | | 1 | || is_saleoff | tinyint(1) | NO | | 0 | |+-------------+------------------------+------+-----+---------+----------------+
goods_cate和brand_name任然是varchar，现在我们修改字段名goods_cate为cate_id，brand_name修改为brand_id, 为了节省空间，我们修改数据类型为smallant
mysql> ALTER TABLE tdb_goods； -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
mysql> DESC tdb_goods；
+-------------+------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+------------------------+------+-----+---------+----------------+| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || goods_name | varchar(150) | NO | | NULL | || cate_id | smallint(5) unsigned | NO | | NULL | || brand_id | smallint(5) unsigned | NO | | NULL | || goods_price | decimal(15,3) unsigned | NO | | 0.000 | || is_show | tinyint(1) | NO | | 1 | || is_saleoff | tinyint(1) | NO | | 0 | |+-------------+------------------------+------+-----+---------+----------------+
7.连接的语法结构
连接在MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
table_reference
{[INNER|CROSS] JOIN|{LEFT|RIGHT}[OUTER]JOIN}
table_referenceON conditional_expr
数据表参照
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name
或tbl_name alias_name 赋予别名
table_subquery可以作为子查询使用FROM子句中，
这样的子查询必须赋予别名。
8.内连接INNER JOIN
INNER JOIN，内连接
在MySQL中，JOIN，CROSS JOIN 和 INNER JOIN 是等价的
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接
连接条件：
使用ON关键字来设定连接条件，也可以使用WHERE来代替
通常使用ON关键字来设定连接条件
使用WHERE关键字进行结果集记录的过滤
内连接和外连接的区别
内连接 ，显示左表及右表符合连接条件的记录，即交集
例如 插入几条记录
-- 分别在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');
例，查看符合内连接的记录，表中只出现商品表和品牌表都有的记录，这就是内连接（这里商品表为左表，分类表为右表）：
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods-> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
9.外连接OUTER JOIN
左外连接
mysql> SELECT goods_id,cate_name,goods_price FROM tdb_goods
LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods
RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
10.多表连接
/**
多表的连接跟两张表的连接一样
表的连接实质就是外键的逆向约束
*/
例，查看符合内连接的记录，表中只出现商品表和品牌表都有的记录，这就是内连接的多表连接（这里商品表为左表，品牌表和分类表为右表）：
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id -> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id;
11.无限级分类表设计
自身连接
mysql> 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;+---------+-----------------+-----------------+| type_id | type_name | type_name |+---------+-----------------+-----------------+| 1 | 家用电器 | NULL || 2 | 电脑、办公 | NULL || 3 | 大家电 | 家用电器 || 4 | 生活电器 | 家用电器 || 5 | 平板电视 | 大家电 || 6 | 空调 | 大家电 || 7 | 电风扇 | 生活电器 || 8 | 饮水机 | 生活电器 || 9 | 电脑整机 | 电脑、办公 || 10 | 电脑配件 | 电脑、办公 || 11 | 笔记本 | 电脑整机 || 12 | 超级本 | 电脑整机 || 13 | 游戏本 | 电脑整机 || 14 | CPU | 电脑配件 || 15 | 主机 | 电脑配件 |+---------+-----------------+-----------------+
mysql> 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;
+---------+-----------------+--------------+| type_id | type_name | type_name |+---------+-----------------+--------------+| 1 | 家用电器 | 大家电 || 1 | 家用电器 | 生活电器 || 2 | 电脑、办公 | 电脑整机 || 2 | 电脑、办公 | 电脑配件 || 3 | 大家电 | 平板电视 || 3 | 大家电 | 空调 || 4 | 生活电器 | 电风扇 || 4 | 生活电器 | 饮水机 || 5 | 平板电视 | NULL || 6 | 空调 | NULL || 7 | 电风扇 | NULL || 8 | 饮水机 | NULL || 9 | 电脑整机 | 笔记本 || 9 | 电脑整机 | 超级本 || 9 | 电脑整机 | 游戏本 || 10 | 电脑配件 | CPU || 10 | 电脑配件 | 主机 || 11 | 笔记本 | NULL || 12 | 超级本 | NULL || 13 | 游戏本 | NULL || 14 | CPU | NULL || 15 | 主机 | NULL |+---------+-----------------+--------------+
mysql> SELECT p.type_id,p.type_name,COUNT(s.type_name) 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;+---------+-----------------+--------------------+| type_id | type_name | COUNT(s.type_name) |+---------+-----------------+--------------------+| 1 | 家用电器 | 2 || 2 | 电脑、办公 | 2 || 3 | 大家电 | 2 || 4 | 生活电器 | 2 || 5 | 平板电视 | 0 || 6 | 空调 | 0 || 7 | 电风扇 | 0 || 8 | 饮水机 | 0 || 9 | 电脑整机 | 3 || 10 | 电脑配件 | 2 || 11 | 笔记本 | 0 || 12 | 超级本 | 0 || 13 | 游戏本 | 0 || 14 | CPU | 0 || 15 | 主机 | 0 |+---------+-----------------+--------------------+
mysql> UPDATE tdb_goods_types AS t1 INNER JOIN
-> (SELECT p.type_id,p.type_name,count(s.type_name) AS child_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.child_count；
mysql> SELECT * FROM tdb_goods_types;+---------+-----------------+-----------+-------------+| type_id | type_name | parent_id | child_count |+---------+-----------------+-----------+-------------+| 1 | 家用电器 | 0 | 2 || 2 | 电脑、办公 | 0 | 2 || 3 | 大家电 | 1 | 2 || 4 | 生活电器 | 1 | 2 || 5 | 平板电视 | 3 | 0 || 6 | 空调 | 3 | 0 || 7 | 电风扇 | 4 | 0 || 8 | 饮水机 | 4 | 0 || 9 | 电脑整机 | 2 | 3 || 10 | 电脑配件 | 2 | 2 || 11 | 笔记本 | 9 | 0 || 12 | 超级本 | 9 | 0 || 13 | 游戏本 | 9 | 0 || 14 | CPU | 10 | 0 || 15 | 主机 | 10 | 0 |+---------+-----------------+-----------+-------------+
11.多表删除
DELETE tbl_name[.*][,tbl_name[.*]]...FROM table_references[WHERE where_condition]
例，查找重复记录：
mysql> 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> 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;
12.复制记录
复制编号为19,20的两条记录
mysql> SELECT goods_id,goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);+----------+------------------------------------+---------+----------+| goods_id | goods_name | cate_id | brand_id |+----------+------------------------------------+---------+----------+| 19 | 商务双肩背包 | 6 | 7 || 20 | X3250 M4机架式服务器 2583i14 | 3 | 1 |+----------+------------------------------------+---------+----------+
mysql> 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);
本文转自谢育政 51CTO博客，原文链接：http://blog.51cto.com/kurolz/1929134，如需转载请自行联系原作者
