下文主要给大家带来MySQL子查询和连接有何区别,希望这些内容能够带给大家实际用处,这也是我编辑MySQL子查询和连接有何区别这篇文章的主要目的。好了,废话不多说,大家直接看下文吧。
mysql> SET NAMES gbk;
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
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;
mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE 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 | 超级本 |+---------+---------------------+
UPDATE table_references SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]
table_reference {[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN} table_referenceON conditional_expr
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
CREATE TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)]select_statement
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 | |+-------------+------------------------+------+-----+---------+----------------+
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 | |+-------------+------------------------+------+-----+---------+----------------+
table_reference {[INNER|CROSS] JOIN|{LEFT|RIGHT}[OUTER]JOIN} table_referenceON conditional_expr
INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
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;
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;
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;
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 |+---------+-----------------+-----------+-------------+
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;
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子查询和连接有何区别,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。