创建客户customer表
CREATE TABLE `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`LastName` char(50) DEFAULT NULL,
`FirstName` char(50) DEFAULT NULL,
`address` char(50) DEFAULT NULL,
`city` char(50) DEFAULT NULL,
`county` char(50) DEFAULT NULL,
`phone` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
);
创建供应商Supplier表
CREATE TABLE `Supplier` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CompanyName` char(50) DEFAULT NULL,
`ContactName` char(50) DEFAULT NULL,
`city` char(50) DEFAULT NULL,
`county` char(50) DEFAULT NULL,
`Phone` char(20) DEFAULT NULL,
`Fax` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
);
创建订单表orders
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`OrderDate` datetime DEFAULT NULL,
`OrderNumber` char(30) NOT NULL,
`CustomerId` int(11) NOT NULL,
`TotalAmount` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
列出所有订单详情(OrderNumber, TotalAmount, FirstName, LastName, City, Country)
创建订单项表 orderitem
CREATE TABLE `orderitem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`OrderId` int(11) NOT NULL,
`ProductId` int(11) NOT NULL,
`UnitPrice` float NOT NULL,
`Quantity` int(11) NOT NULL,
PRIMARY KEY (`id`)
);
创建Product表
CREATE TABLE `product2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ProductName` char(100) NOT NULL,
`SupplierId` int(11) NOT NULL,
`UnitPrice` float NOT NULL,
`Package` int(11) NOT NULL,
`IsDiscontinued` tinyint(4) NOT NULL COMMENT '是否下架',
PRIMARY KEY (`id`)
);
从orders、orderitem、Product三个表中查询OrderNumber、ProductName、UnitPrice、Quantity
SELECT
O.OrderNumber as 订单号,
O.OrderDate AS 订购日期,
P.ProductName AS 物品名称,
I.Quantity as 购买数量,
I.UnitPrice as 单价
FROM
ORDERS O
JOIN OrderItem I ON O.Id = I.OrderId -- JOIN 即 INNER JOIN
JOIN Product P ON P.Id = I.ProductId
ORDER BY
O.OrderNumber
列出第一个(左边的)表的所有记录对应的第二个(右边的)表信息
例如,列出所有人的订单信息
SELECT
c.FirstName,
c.LastName,
c.City,
c.Country,
o.OrderNumber,
o.TotalAmount
FROM
Customer C -- Customer表中的每一个人
LEFT JOIN Orders O -- Orders就是每一个人对应的订单信息
ON O.CustomerId = C.Id -- Customer 与 Orders的 联系
ORDER BY -- 排序
TotalAmount
LEFT JOIN和RIGHT JOIN的区别和联系
任何RIGHT JOIN都可以换成LEFT JOIN的写法,反之亦然。
例如,下面两种写法的查询结果是等价的:
-- LEFT JOIN
SELECT
TotalAmount,
FirstName,
LastName,
City,
Country
FROM
Customer C
LEFT JOIN orders O ON O.CustomerId = C.Id
WHERE
TotalAmount IS NULL;
-- RIGHT JOIN
SELECT
TotalAmount,
FirstName,
LastName,
City,
Country
FROM
orders O
RIGHT JOIN Customer C ON O.CustomerId = C.Id
WHERE
TotalAmount IS NULL;
FULL JOIN
例如,如何查询各个国家的所有客户和供应商?
分析:
1. 查询各个国家,需要按国家排序
2. 查询客户和供应商,需要从Customer表和Supplier表中查询;
3. 查询所有客户和所有供应商,需要使用FULL JOIN;
SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry, S.Country AS SupplierCountry, S.CompanyName FROM Customer C FULL JOIN Supplier S ON C.Country = S.Country ORDER BY C.Country, S.Country;
总结:
如果需要从两个表中查询指定的字段信息,可以使用JOIN;
如果同时查询所有客户对应的供应商信息,可以用FROM Customer LEFT JOIN Supplier;
如果同时查询所有供应商对应的客户信息,可以用FROM Customer RIGHT JOIN Supplier;
如果同时查询所有客户和所有供应商的信息,可以用FROM Customer FULL JOIN SupplierSupplier;
有些数据库不支持FULL JOIN,例如MySQL,不过在MySQL中可以使用LEFT JOIN + UNION + RIGHT JOIN 实现类似FULL JOIN的功能,例如上述例子如果在 MySQL中可以这样写:
SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry, S.Country AS SupplierCountry, S.CompanyName FROM Customer C LEFT JOIN Supplier S ON C.Country = S.Country
UNION
SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry, S.Country AS SupplierCountry, S.CompanyName FROM Customer C RIGHT JOIN Supplier S ON C.Country = S.Country ORDER BY SupplierCountry,CustomerCountry;