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 ;