下文主要給大家帶來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進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。