SQL语句总结

创建客户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`));
1
2
3
4
5
6
7
8
9
10

创建供应商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`));
1
2
3
4
5
6
7
8
9
10

创建订单表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`));
1
2
3
4
5
6
7
8

列出所有订单详情（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`));
1
2
3
4
5
6
7
8

创建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`));
1
2
3
4
5
6
7
8
9

从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 OJOIN OrderItem I ON O.Id = I.OrderId   -- JOIN  即 INNER JOINJOIN Product P ON P.Id = I.ProductIdORDER BY    O.OrderNumber
1
2
3
4
5
6
7
8
9
10
11
12

列出第一个(左边的)表的所有记录对应的第二个(右边的)表信息

例如，列出所有人的订单信息

SELECT    c.FirstName,    c.LastName,    c.City,    c.Country,    o.OrderNumber,    o.TotalAmountFROM    Customer C  -- Customer表中的每一个人LEFT JOIN Orders O   -- Orders就是每一个人对应的订单信息ON O.CustomerId = C.Id  -- Customer 与  Orders的 联系ORDER BY   -- 排序    TotalAmount
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

LEFT JOIN和RIGHT JOIN的区别和联系

任何RIGHT JOIN都可以换成LEFT JOIN的写法，反之亦然。 
例如，下面两种写法的查询结果是等价的：

-- LEFT JOINSELECT    TotalAmount,    FirstName,    LastName,    City,    CountryFROM    Customer CLEFT JOIN orders O ON O.CustomerId = C.IdWHERE    TotalAmount IS NULL;-- RIGHT JOINSELECT    TotalAmount,    FirstName,    LastName,    City,    CountryFROM    orders ORIGHT JOIN Customer C ON O.CustomerId = C.IdWHERE    TotalAmount IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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;
1

总结：

如果需要从两个表中查询指定的字段信息，可以使用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.CountryUNION 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;