亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

右外連接

顯示右表的全部記錄及左表符合連接條件的記錄

查詢所有商品的詳細信息（通過左外連接實現）

mysql>?SELECT?goods_id,?goods_name,?cate_name,?brand_name,?goods_price?FROM?tdb_goods?AS?g
????->?LEFT?JOIN?tdb_goods_cates?AS?c?ON?g.cate_id?=?c.cate_id
????->?LEFT?JOIN?tdb_goods_brands?AS?b?ON?g.brand_id?=?b.brand_id;
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+
|?goods_id?|?goods_name???????????????????????????????????????????????????????????????????????????|?cate_name???????????|?brand_name?|?goods_price?|
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+
|????????1?|?R510VC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?華碩?????|????3399.000?|
|????????2?|?Y400N?14.0英寸筆記本電腦??????????????????????????????????????????????????????|?筆記本???????????|?聯想?????|????4899.000?|
|????????3?|?G150TH?15.6英寸游戲本???????????????????????????????????????????????????????????|?游戲本???????????|?雷神?????|????8499.000?|
|????????4?|?X550CC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?華碩?????|????2799.000?|
|????????5?|?X240(20ALA0EYCD)?12.5英寸超極本?????????????????????????????????????????????????|?超級本???????????|?聯想?????|????4999.000?|
|????????6?|?U330P?13.3英寸超極本????????????????????????????????????????????????????????????|?超級本???????????|?聯想?????|????4299.000?|
|????????7?|?SVP13226SCB?13.3英寸觸控超極本????????????????????????????????????????????????|?超級本???????????|?索尼?????|????7999.000?|
|????????8?|?iPad?mini?MD531CH/A?7.9英寸平板電腦????????????????????????????????????????????|?平板電腦????????|?蘋果?????|????1998.000?|
|????????9?|?iPad?Air?MD788CH/A?9.7英寸平板電腦?（16G?WiFi版）???????????????????????????|?平板電腦????????|?蘋果?????|????3388.000?|
|???????10?|??iPad?mini?ME279CH/A?配備?Retina?顯示屏?7.9英寸平板電腦?（16G?WiFi版）?|?平板電腦????????|?蘋果?????|????2788.000?|
|???????11?|?IdeaCentre?C340?20英寸一體電腦?????????????????????????????????????????????????|?臺式機???????????|?聯想?????|????3499.000?|
|???????12?|?Vostro?3800-R1206?臺式電腦???????????????????????????????????????????????????????|?臺式機???????????|?戴爾?????|????2899.000?|
|???????13?|?iMac?ME086CH/A?21.5英寸一體電腦????????????????????????????????????????????????|?臺式機???????????|?蘋果?????|????9188.000?|
|???????14?|?AT7-7414LP?臺式電腦?（i5-3450四核?4G?500G?2G獨顯?DVD?鍵鼠?Linux?）???????|?臺式機???????????|?宏碁?????|????3699.000?|
|???????15?|?Z220SFF?F4F06PA工作站?????????????????????????????????????????????????????????????|?服務器/工作站?|?惠普?????|????4288.000?|
|???????16?|?PowerEdge?T110?II服務器???????????????????????????????????????????????????????????|?服務器/工作站?|?戴爾?????|????5388.000?|
|???????17?|?Mac?Pro?MD878CH/A?專業級臺式電腦??????????????????????????????????????????????|?服務器/工作站?|?蘋果?????|???28888.000?|
|???????18?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|????6999.000?|
|???????19?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|??????99.000?|
|???????20?|?X3250?M4機架式服務器?2583i14???????????????????????????????????????????????????|?服務器/工作站?|?IBM????????|????6888.000?|
|???????21?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|????6999.000?|
|???????22?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|??????99.000?|
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+

22 rows in set (0.00 sec)

查詢所有商品的詳細信息（通過右外連接實現）：

mysql>?SELECT?goods_id,?goods_name,?cate_name,?brand_name,?goods_price?FROM?tdb_goods?AS?g
????->?RIGHT?JOIN?tdb_goods_cates?AS?c?ON?g.cate_id?=?c.cate_id
????->?RIGHT?JOIN?tdb_goods_brands?AS?b?ON?g.brand_id?=?b.brand_id;
+----------+--------------------------------------------------------------------------------------+---------------------+--------------+-------------+
|?goods_id?|?goods_name???????????????????????????????????????????????????????????????????????????|?cate_name???????????|?brand_name???|?goods_price?|
+----------+--------------------------------------------------------------------------------------+---------------------+--------------+-------------+
|????????1?|?R510VC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?華碩???????|????3399.000?|
|????????4?|?X550CC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?華碩???????|????2799.000?|
|????????2?|?Y400N?14.0英寸筆記本電腦??????????????????????????????????????????????????????|?筆記本???????????|?聯想???????|????4899.000?|
|????????5?|?X240(20ALA0EYCD)?12.5英寸超極本?????????????????????????????????????????????????|?超級本???????????|?聯想???????|????4999.000?|
|????????6?|?U330P?13.3英寸超極本????????????????????????????????????????????????????????????|?超級本???????????|?聯想???????|????4299.000?|
|???????11?|?IdeaCentre?C340?20英寸一體電腦?????????????????????????????????????????????????|?臺式機???????????|?聯想???????|????3499.000?|
|????????3?|?G150TH?15.6英寸游戲本???????????????????????????????????????????????????????????|?游戲本???????????|?雷神???????|????8499.000?|
|????????7?|?SVP13226SCB?13.3英寸觸控超極本????????????????????????????????????????????????|?超級本???????????|?索尼???????|????7999.000?|
|???????18?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?索尼???????|????6999.000?|
|???????19?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?索尼???????|??????99.000?|
|???????21?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?索尼???????|????6999.000?|
|???????22?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?索尼???????|??????99.000?|
|????????8?|?iPad?mini?MD531CH/A?7.9英寸平板電腦????????????????????????????????????????????|?平板電腦????????|?蘋果???????|????1998.000?|
|????????9?|?iPad?Air?MD788CH/A?9.7英寸平板電腦?（16G?WiFi版）???????????????????????????|?平板電腦????????|?蘋果???????|????3388.000?|
|???????10?|??iPad?mini?ME279CH/A?配備?Retina?顯示屏?7.9英寸平板電腦?（16G?WiFi版）?|?平板電腦????????|?蘋果???????|????2788.000?|
|???????13?|?iMac?ME086CH/A?21.5英寸一體電腦????????????????????????????????????????????????|?臺式機???????????|?蘋果???????|????9188.000?|
|???????17?|?Mac?Pro?MD878CH/A?專業級臺式電腦??????????????????????????????????????????????|?服務器/工作站?|?蘋果???????|???28888.000?|
|???????12?|?Vostro?3800-R1206?臺式電腦???????????????????????????????????????????????????????|?臺式機???????????|?戴爾???????|????2899.000?|
|???????16?|?PowerEdge?T110?II服務器???????????????????????????????????????????????????????????|?服務器/工作站?|?戴爾???????|????5388.000?|
|???????14?|?AT7-7414LP?臺式電腦?（i5-3450四核?4G?500G?2G獨顯?DVD?鍵鼠?Linux?）???????|?臺式機???????????|?宏碁???????|????3699.000?|
|???????15?|?Z220SFF?F4F06PA工作站?????????????????????????????????????????????????????????????|?服務器/工作站?|?惠普???????|????4288.000?|
|???????20?|?X3250?M4機架式服務器?2583i14???????????????????????????????????????????????????|?服務器/工作站?|?IBM??????????|????6888.000?|
|?????NULL?|?NULL?????????????????????????????????????????????????????????????????????????????????|?NULL????????????????|?海爾???????|????????NULL?|
|?????NULL?|?NULL?????????????????????????????????????????????????????????????????????????????????|?NULL????????????????|?清華同方?|????????NULL?|
|?????NULL?|?NULL?????????????????????????????????????????????????????????????????????????????????|?NULL????????????????|?神舟???????|????????NULL?|
|?????NULL?|?NULL?????????????????????????????????????????????????????????????????????????????????|?NULL????????????????|?海爾???????|????????NULL?|
|?????NULL?|?NULL?????????????????????????????????????????????????????????????????????????????????|?NULL????????????????|?清華同方?|????????NULL?|
|?????NULL?|?NULL?????????????????????????????????????????????????????????????????????????????????|?NULL????????????????|?神舟???????|????????NULL?|
+----------+--------------------------------------------------------------------------------------+---------------------+--------------+-------------+
28?rows?in?set?(0.00?sec)

左外連接

顯示左表的全部記錄及右表符合連接條件的記錄

內連接

顯示左表及右表符合連接條件的記錄

mysql>?DESC?tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
|?Field???????|?Type???????????????????|?Null?|?Key?|?Default?|?Extra??????????|
+-------------+------------------------+------+-----+---------+----------------+
|?goods_id????|?smallint?unsigned??????|?NO???|?PRI?|?NULL????|?auto_increment?|
|?goods_name??|?varchar(150)???????????|?NO???|?????|?NULL????|????????????????|
|?cate_id?????|?smallint?unsigned??????|?NO???|?????|?NULL????|????????????????|
|?brand_id????|?smallint?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?rows?in?set?(0.00?sec)

mysql>?DESC?tdb_goods_cates;
+-----------+-------------------+------+-----+---------+----------------+
|?Field?????|?Type??????????????|?Null?|?Key?|?Default?|?Extra??????????|
+-----------+-------------------+------+-----+---------+----------------+
|?cate_id???|?smallint?unsigned?|?NO???|?PRI?|?NULL????|?auto_increment?|
|?cate_name?|?varchar(40)???????|?YES??|?????|?NULL????|????????????????|
+-----------+-------------------+------+-----+---------+----------------+
2?rows?in?set?(0.01?sec)

mysql>?SELECT?goods_id,?goods_name,?cate_name,?brand_name,?goods_price?FROM?tdb_goods?AS?g
????->?INNER?JOIN?tdb_goods_cates?AS?c?ON?g.cate_id?=?c.cate_id
????->?INNER?JOIN?tdb_goods_brands?AS?b?ON?g.brand_id?=?b.brand_id;
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+
|?goods_id?|?goods_name???????????????????????????????????????????????????????????????????????????|?cate_name???????????|?brand_name?|?goods_price?|
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+
|????????1?|?R510VC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?華碩?????|????3399.000?|
|????????2?|?Y400N?14.0英寸筆記本電腦??????????????????????????????????????????????????????|?筆記本???????????|?聯想?????|????4899.000?|
|????????3?|?G150TH?15.6英寸游戲本???????????????????????????????????????????????????????????|?游戲本???????????|?雷神?????|????8499.000?|
|????????4?|?X550CC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?華碩?????|????2799.000?|
|????????5?|?X240(20ALA0EYCD)?12.5英寸超極本?????????????????????????????????????????????????|?超級本???????????|?聯想?????|????4999.000?|
|????????6?|?U330P?13.3英寸超極本????????????????????????????????????????????????????????????|?超級本???????????|?聯想?????|????4299.000?|
|????????7?|?SVP13226SCB?13.3英寸觸控超極本????????????????????????????????????????????????|?超級本???????????|?索尼?????|????7999.000?|
|????????8?|?iPad?mini?MD531CH/A?7.9英寸平板電腦????????????????????????????????????????????|?平板電腦????????|?蘋果?????|????1998.000?|
|????????9?|?iPad?Air?MD788CH/A?9.7英寸平板電腦?（16G?WiFi版）???????????????????????????|?平板電腦????????|?蘋果?????|????3388.000?|
|???????10?|??iPad?mini?ME279CH/A?配備?Retina?顯示屏?7.9英寸平板電腦?（16G?WiFi版）?|?平板電腦????????|?蘋果?????|????2788.000?|
|???????11?|?IdeaCentre?C340?20英寸一體電腦?????????????????????????????????????????????????|?臺式機???????????|?聯想?????|????3499.000?|
|???????12?|?Vostro?3800-R1206?臺式電腦???????????????????????????????????????????????????????|?臺式機???????????|?戴爾?????|????2899.000?|
|???????13?|?iMac?ME086CH/A?21.5英寸一體電腦????????????????????????????????????????????????|?臺式機???????????|?蘋果?????|????9188.000?|
|???????14?|?AT7-7414LP?臺式電腦?（i5-3450四核?4G?500G?2G獨顯?DVD?鍵鼠?Linux?）???????|?臺式機???????????|?宏碁?????|????3699.000?|
|???????15?|?Z220SFF?F4F06PA工作站?????????????????????????????????????????????????????????????|?服務器/工作站?|?惠普?????|????4288.000?|
|???????16?|?PowerEdge?T110?II服務器???????????????????????????????????????????????????????????|?服務器/工作站?|?戴爾?????|????5388.000?|
|???????17?|?Mac?Pro?MD878CH/A?專業級臺式電腦??????????????????????????????????????????????|?服務器/工作站?|?蘋果?????|???28888.000?|
|???????18?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|????6999.000?|
|???????19?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|??????99.000?|
|???????20?|?X3250?M4機架式服務器?2583i14???????????????????????????????????????????????????|?服務器/工作站?|?IBM????????|????6888.000?|
|???????21?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|????6999.000?|
|???????22?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?索尼?????|??????99.000?|
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+
22?rows?in?set?(0.01?sec)

連接條件

使用 ON 關鍵字來設定連接條件，也可以使用 WHERE 來代替。

通常使用 ON 關鍵字來設定連接條件，

使用 WHERE 關鍵字進行結果集記錄的過濾。

連接類型

INNER JOIN，內連接

• 在 MySQL 中，JOIN，CROSS JOIN 和 INNER JOIN 是等價的。

LEFT [OUTER] JOIN，左外連接

RIGHT [OUTER] JOIN，右外連接

數據表參照

table_reference
tbl_name?[[AS]?alias]?|?table_subquery?[AS]?alias

數據表可以使用 tbl_name AS alias_name 或 tbl_name alias_name? 賦予別名。

table_subquery 可以作為子查詢使用在 FROM 子句中，這樣的子查詢必須為其賦予別名。

?

語法結構

table_reference
{[INNER?|?CROSS]?JOIN?|?{LEFT|RIGHT}?[OUTER]?JOIN}
table_reference
ON?conditional_expr

連接

MySQL 在 SELECT 語句、多表更新、多表刪除語句中支持 JOIN 操作。

?

連接

MySQL 在 SELECT 語句、多表更新、多表刪除語句中支持 JOIN 操作。

分別在 tdb_goods_cates 和 tdb_goods_brands 表插入記錄：

mysql>?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?*?FROM?tdb_goods_cates;
+---------+-----------+
|?cate_id?|?cate_name?|
+---------+-----------+
|???????1?|???????????|
|???????2?|???????????|
|???????3?|???????????|
|???????4?|???????????|
|???????5?|???????????|
|???????6?|????/??????|
|???????7?|???????????|
|???????8?|???????????|
|???????9?|???????????|
|??????10?|???????????|
+---------+-----------+
10?rows?in?set?(0.00?sec)

查看 tdb_goods 的數據表結構：

mysql>?DESC?tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
|?Field???????|?Type???????????????????|?Null?|?Key?|?Default?|?Extra??????????|
+-------------+------------------------+------+-----+---------+----------------+
|?goods_id????|?smallint?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???????|????????????????|
+-------------+------------------------+------+-----+---------+----------------+
7?rows?in?set?(0.00?sec)

通過 ALTER TABLE 語句修改數據表結構：

mysql>?ALTER?TABLE?tdb_goods
????->?CHANGE?goods_cate?cate_id?SMALLINT?UNSIGNED?NOT?NULL,
????->?CHANGE?brand_name?brand_id?SMALLINT?UNSIGNED?NOT?NULL;
Query?OK,?22?rows?affected?(0.14?sec)
Records:?22??Duplicates:?0??Warnings:?0

mysql>?DESC?tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
|?Field???????|?Type???????????????????|?Null?|?Key?|?Default?|?Extra??????????|
+-------------+------------------------+------+-----+---------+----------------+
|?goods_id????|?smallint?unsigned??????|?NO???|?PRI?|?NULL????|?auto_increment?|
|?goods_name??|?varchar(150)???????????|?NO???|?????|?NULL????|????????????????|
|?cate_id?????|?smallint?unsigned??????|?NO???|?????|?NULL????|????????????????|
|?brand_id????|?smallint?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?rows?in?set?(0.00?sec)

通過 tdb_goods_brands 數據表來更新 tdb_goods 數據表（錯誤）

UPDATE?tdb_goods?INNER?JOIN?tdb_goods_brands?ON?brand_name?=?brand_name
SET?brand_name?=?brand_id;
--?Column?'brand_name'?in?field?list?is?ambigous

正確：

mysql>?UPDATE?tdb_goods?AS?g?INNER?JOIN?tdb_goods_brands?AS?b?ON?g.brand_name?=?b.brand_name?
????->?SET?g.brand_name?=?b.brand_id;
Query?OK,?22?rows?affected?(0.00?sec)
Rows?matched:?22??Changed:?22??Warnings:?0

mysql>?SELECT?*?FROM?tdb_goods;
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+---------+------------+
|?goods_id?|?goods_name???????????????????????????????????????????????????????????????????????????|?goods_cate??????????|?brand_name?|?goods_price?|?is_show?|?is_saleoff?|
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+---------+------------+
|????????1?|?R510VC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?1??????????|????3399.000?|???????1?|??????????0?|
|????????2?|?Y400N?14.0英寸筆記本電腦??????????????????????????????????????????????????????|?筆記本???????????|?2??????????|????4899.000?|???????1?|??????????0?|
|????????3?|?G150TH?15.6英寸游戲本???????????????????????????????????????????????????????????|?游戲本???????????|?3??????????|????8499.000?|???????1?|??????????0?|
|????????4?|?X550CC?15.6英寸筆記本???????????????????????????????????????????????????????????|?筆記本???????????|?1??????????|????2799.000?|???????1?|??????????0?|
|????????5?|?X240(20ALA0EYCD)?12.5英寸超極本?????????????????????????????????????????????????|?超級本???????????|?2??????????|????4999.000?|???????1?|??????????0?|
|????????6?|?U330P?13.3英寸超極本????????????????????????????????????????????????????????????|?超級本???????????|?2??????????|????4299.000?|???????1?|??????????0?|
|????????7?|?SVP13226SCB?13.3英寸觸控超極本????????????????????????????????????????????????|?超級本???????????|?4??????????|????7999.000?|???????1?|??????????0?|
|????????8?|?iPad?mini?MD531CH/A?7.9英寸平板電腦????????????????????????????????????????????|?平板電腦????????|?5??????????|????1998.000?|???????1?|??????????0?|
|????????9?|?iPad?Air?MD788CH/A?9.7英寸平板電腦?（16G?WiFi版）???????????????????????????|?平板電腦????????|?5??????????|????3388.000?|???????1?|??????????0?|
|???????10?|??iPad?mini?ME279CH/A?配備?Retina?顯示屏?7.9英寸平板電腦?（16G?WiFi版）?|?平板電腦????????|?5??????????|????2788.000?|???????1?|??????????0?|
|???????11?|?IdeaCentre?C340?20英寸一體電腦?????????????????????????????????????????????????|?臺式機???????????|?2??????????|????3499.000?|???????1?|??????????0?|
|???????12?|?Vostro?3800-R1206?臺式電腦???????????????????????????????????????????????????????|?臺式機???????????|?6??????????|????2899.000?|???????1?|??????????0?|
|???????13?|?iMac?ME086CH/A?21.5英寸一體電腦????????????????????????????????????????????????|?臺式機???????????|?5??????????|????9188.000?|???????1?|??????????0?|
|???????14?|?AT7-7414LP?臺式電腦?（i5-3450四核?4G?500G?2G獨顯?DVD?鍵鼠?Linux?）???????|?臺式機???????????|?7??????????|????3699.000?|???????1?|??????????0?|
|???????15?|?Z220SFF?F4F06PA工作站?????????????????????????????????????????????????????????????|?服務器/工作站?|?8??????????|????4288.000?|???????1?|??????????0?|
|???????16?|?PowerEdge?T110?II服務器???????????????????????????????????????????????????????????|?服務器/工作站?|?6??????????|????5388.000?|???????1?|??????????0?|
|???????17?|?Mac?Pro?MD878CH/A?專業級臺式電腦??????????????????????????????????????????????|?服務器/工作站?|?5??????????|???28888.000?|???????1?|??????????0?|
|???????18?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?4??????????|????6999.000?|???????1?|??????????0?|
|???????19?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?4??????????|??????99.000?|???????1?|??????????0?|
|???????20?|?X3250?M4機架式服務器?2583i14???????????????????????????????????????????????????|?服務器/工作站?|?9??????????|????6888.000?|???????1?|??????????0?|
|???????21?|??HMZ-T3W?頭戴顯示設備??????????????????????????????????????????????????????????|?筆記本配件?????|?4??????????|????6999.000?|???????1?|??????????0?|
|???????22?|?商務雙肩背包???????????????????????????????????????????????????????????????????|?筆記本配件?????|?4??????????|??????99.000?|???????1?|??????????0?|
+----------+--------------------------------------------------------------------------------------+---------------------+------------+-------------+---------+------------+
22?rows?in?set?(0.00?sec)

mysql>?DESC?tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
|?Field???????|?Type???????????????????|?Null?|?Key?|?Default?|?Extra??????????|
+-------------+------------------------+------+-----+---------+----------------+
|?goods_id????|?smallint?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???????|????????????????|
+-------------+------------------------+------+-----+---------+----------------+
7?rows?in?set?(0.00?sec)

通過 CREATE...SELECT 來創建數據表并且同時寫入記錄

mysql>?CREATE?TABLE?tdb_goods_brands?(
????->???brand_id?SMALLINT?UNSIGNED?PRIMARY?KEY?AUTO_INCREMENT,
????->???brand_name?VARCHAR(40)?NOT?NULL
????->?)?SELECT?brand_name?FROM?tdb_goods?GROUP?BY?brand_name;
Query?OK,?9?rows?affected?(0.06?sec)
Records:?9??Duplicates:?0??Warnings:?0

mysql>?SHOW?TABLES;
+------------------+
|?Tables_in_imooc??|
+------------------+
|?tdb_goods????????|
|?tdb_goods_brands?|
|?tdb_goods_cates??|
+------------------+
3?rows?in?set?(0.01?sec)

mysql>?SELECT?*?FROM?tdb_goods_brands;
+----------+------------+
|?brand_id?|?brand_name?|
+----------+------------+
|????????1?|?華碩???????|
|????????2?|?聯想???????|
|????????3?|?雷神???????|
|????????4?|?索尼???????|
|????????5?|?蘋果???????|
|????????6?|?戴爾???????|
|????????7?|?宏碁???????|
|????????8?|?惠普???????|
|????????9?|?IBM????????|
+----------+------------+
9?rows?in?set?(0.00?sec)

CREATE...SELECT

創建數據表同時將查詢結果寫入到數據表

CREATE?TABLE?[IF?NOT?EXISTS]?tbl_name
[(create_definition,...)]
select_statement