dset Display Device | Laptop accessory |
| 23 | Business Backpage | Laptop accessory |
| 24 | LaserJet Pro P1606dn Black&White blazer printer | NULL |
+----------+--------------------------------------------------------------------------------------------+---------------------+
24 rows in set (0.00 sec)
注意最后一个记录的类名是null
RIGHT JOIN是这样的
mysql> SELECT GOODS_ID,GOODS_NAME,CATE_NAME FROM GOODS RIGHT JOIN CATES ON GOODS.CATE_ID=CATES.CATE_ID;
+----------+--------------------------------------------------------------------------------------------+---------------------+
| GOODS_ID | GOODS_NAME | CATE_NAME |
+----------+--------------------------------------------------------------------------------------------+---------------------+
| 1 | R510VC 15.6Inches Laptop | Laptop |
| 2 | Y400N 14.0Inches Laptop | Laptop |
| 3 | G150TH 15.6Inches Gamebook | Gamebook |
| 4 | X550CC 15.6Inches Laptop | Laptop |
| 5 | X240(20ALA0EYCD) 12.5InchesUltrabook | Ultrabook |
| 6 | U330P 13.3Inches Ultrabook | Ultrabook |
| 7 | SVP13226SCB 13.3Inches Touch Ultrabook | Ultrabook |
| 8 | iPad mini MD531CH/A 7.9Inches Tablet | Tablet |
| 9 | iPad Air MD788CH/A 9.7Inches Tablet ?16G WiFi Vision? | Tablet |
| 10 | iPad mini ME279CH/A with Retina screen 7.9InchesTablet ?16G WiFi Vision? | Tablet |
| 11 | IdeaCentre C340 20Inches All-in-one | Desktop |
| 12 | Vostro 3800-R1206 desktop | Desktop |
| 13 | iMac ME086CH/A 21.5Inches All-in-one | Desktop |
| 14 | AT7-7414LP Desktop ?i5-3450 4G 500G 2GIndependent graphics card DVD Keyboard&Mouse Linux ? | Desktop |
| 15 | Z220SFF F4F06PAWork station | server/Work station |
| 16 | PowerEdge T110 IIserver | server/Work station |
| 17 | Mac Pro MD878CH/A Professional Desktop | server/Work station |
| 18 | HMZ-T3W Headset Display Device | Laptop accessory |
| 19 | Business Backpage | Laptop accessory |
| 20 | X3250 M4 server 2583i14 | server/Work station |
| 21 | Xuanlong Laptop Radiator | Laptop accessory |
| 22 | HMZ-T3W Headset Display Device | Laptop accessory |
| 23 | Business Backpage | Laptop accessory |
| NULL | NULL | Adaptor |
| NULL | NULL | Exchanger |
| NULL | NULL | Netcard |
+----------+--------------------------------------------------------------------------------------------+---------------------+
26 rows in set (0.00 sec)
----多表连接
mysql> SELECT GOODS_ID,GOODS_NAME,CATE_NAME,BRAND_NAME,GOODS_PRICE FROM GOODS AS G
-> INNER JOIN CATES AS C ON G.CATE_ID=C.CATE_ID
-> INNER JOIN BRANDS AS B ON G.BRAND_ID=B.BRAND_ID;
每连接一个表就需要一个JOIN，注意起别名给数据表！
此时的显示虽然跟一开始一样，但实现不同，意义大不一样
----无限级分类表设计
一般实际中会出现无限级数据表，不断往下分
举个例子
CREATE TABLE goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT goods_types(type_name,parent_id) VALUES('Home Electronics',DEFAULT);
INSERT goods_types(type_name,parent_id) VALUES('PC、Office',DEFAULT);
INSERT goods_types(type_name,parent_id) VALUES('Appliances',1);
INSERT goods_types(type_name,parent_id) VALUES('Life Appliances',1);
INSERT goods_types(type_name,parent_id) VALUES('Tablet TV',3);
INSERT goods_types(type_name,parent_id) VALUES('Air Conditioner',3);
INSERT goods_types(type_name,parent_id) VALUES('Electrical Fan',4);
INSERT goods_types(type_name,parent_id) VALUES('Water Dispenser',4);
INSERT goods_types(type_name,parent_id) VALUES('Complete Machine',2);
INSERT goods_types(type_n