MySQL多表查询之连接查询

MySQL多表查询之连接查询

多表连接

sale表

 CREATE TABLE `sale` (
  `SaleID` int(10) NOT NULL AUTO_INCREMENT,
  `MerchID` varchar(11) NOT NULL,
  `SaleDate` datetime NOT NULL,
  `SaleNum` int(11) NOT NULL,
  `SalePrice` double NOT NULL,
  `SaleLID` varchar(10) NOT NULL,
  PRIMARY KEY (`SaleID`),
  KEY `saleIndex` (`SaleID`,`SaleDate`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

merchinfo表

CREATE TABLE `merchinfo` (
  `MerchID` varchar(11) NOT NULL,
  `MerchName` varchar(20) NOT NULL,
  `MerchPrice` double NOT NULL,
  `MerchNum` int(11) NOT NULL,
  `CautionNum` int(11) NOT NULL,
  `PlanNum` int(11) DEFAULT NULL,
  `BarCode` varchar(50) NOT NULL,
  `SalesProDateS` datetime DEFAULT NULL,
  `SalesProDateE` datetime DEFAULT NULL,
  `AllowAbate` tinyint(4) NOT NULL,
  `AllowSale` tinyint(4) DEFAULT NULL,
  `FactoryID` varchar(10) NOT NULL,
  `ProvideID` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`MerchID`),
  KEY `merchinfoIndex` (`MerchID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

插入数据

INSERT INTO supermarket.merchinfo 
```sql
(MerchID, 
MerchName, 
MerchPrice, 
MerchNum, 
CautionNum, 
PlanNum, 
BarCode, 
AllowAbate, 
AllowSale, 
FactoryID, 
ProvideID
)
VALUES
('0000001', '营养快线', '5', '100', '20', '100', '151845515545', '0', '1', 'F000001', 'P000001'),
('0000002', '小浣熊', '5', '100', '20', '100', '151845515544', '0', '1', 'F000002', 'P000001'),
('0000003', '魔芋爽', '5', '100', '20', '100', '151845515543', '0', '1', 'F000003', 'P000001'),
('0000004', '可口可乐', '5', '100', '20', '100', '151845515542', '0', '1', 'F000004', 'P000001'),
('0000005', '老干妈', '5', '100', '20', '100', '151845515541', '0', '1', 'F000005', 'P000001'),
('0000006', '匡师傅红烧牛肉面', '5', '100', '20', '100', '151845515540', '0', '1', 'F000006', 'P000001');
```INSERT INTO supermarket.sale 
```sql
(
MerchID, 
SaleDate, 
SaleNum, 
SalePrice, 
SaleLID
)
VALUES
('0000001', NOW(), '4', '20','S0000001'),
('0000002', NOW(), '4', '20','S0000001'),
('0000003', NOW(), '4', '20','S0000001'),
('0000004', NOW(), '4', '20','S0000001');

内连接

inner join

#内连接
SELECT Sale.*,merchinfo.FactoryID FROM Sale INNER JOIN merchinfo ON  SaleLID='S0000001'AND Sale.MerchID=merchinfo.MerchID ;

左连接

left join 左边为主，右边是匹配项没有则为空

#左连接
SELECT Sale.*,merchinfo.FactoryID FROM Sale LEFT JOIN merchinfo ON  SaleLID='S0000001'AND Sale.MerchID=merchinfo.MerchID ;

右连接

right join 右边为主门左边是匹配项，没有则为空

#右连接
SELECT Sale.*,merchinfo.FactoryID FROM Sale RIGHT JOIN merchinfo ON  SaleLID='S0000001'AND Sale.MerchID=merchinfo.MerchID ;