下文主要给大家带来MySQL子查询和连接有何区别,希望这些内容能够带给大家实际用处,这也是我编辑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;
例,将tdb_goods中的brand_name更新为tdb_goods_brand表中的对应brand_id。错误写法(程序分别不出哪个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;
查询tdb_goods的数据表结构
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
左外连接
显示左表的全部记录及右表符合连接条件的记录
例,查看符合左外连接的记录,表中出现商品表的全部和2表表都有的记录,这就是左外连接(这里商品表为左表,分类表为右表)
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;
右外连接
显示右表的全部记录及左表符合连接条件的记录
例,查看符合右外连接的记录,表中出现品牌表的全部和2表表都有的记录,这就是右外连接(这里商品表为左表,分类表为右表)
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 |+---------+-----------------+--------------------+
为tdb_goods_types添加child_count字段
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);
对于以上关于MySQL子查询和连接有何区别,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。