sql连接查询

1.内连接：一般都是默认的连接类型。内连接基于连接谓语，将两张表（假如有A表和B表）的列组合在一起，产生新的结果表。 内连接查询会将A表的每一行和B表的每一行进行比较，并找出满足连接谓语词的组合。当连接谓语词被满足，A表和B表中匹配的行会按列组合（并排组合）成结果集中的一行。

2.

***：注意(简化代码)--->Customers.CustomerID = c.CustomerID）

（1）外联接

--外联接
select * from Customers
select * from Customers CROSS JOIN Orders

（2）内联接

--内联接
select * from Customers
select Customers.*, Orders.* from Customers INNER JOIN Orders
ON (Customers.CustomerID  =  Orders.CustomerID)

（3）查询所有内容

--查询所有订单，显示客人姓名、订单编号、快递地址、订单金额、下单时间
select c.CustomerName as 客户姓名, o.OrdersID as 订单编号, o.Amount as 订单金额, o.Address as 收货地址， o.OrdersTime as 下单时间 form Customers c INNER JOIN Orders o
ON(c.CustomerID = o.CustomerID)

（4）外联接：左外联接、右外联接

--查询所有客户信息以及客户的订单信息，包括：客户编号、客户姓名、电话号码、地址、订单金额、下单时间
--外联接：左外联接、右外联接      

--1）左外联接：“左表”为“核心”，“右表”为子表；右边的信息左边没有的，不能输出--
select * from Customers
select c.CustomerId as 客户编号, c.CustomerName as 客户姓名, c.Phone as 电话号码, c.Address1 as 地址, 
o.Amount as 订单金额, o.OrderTime as 下单时间
from Customers c left outer join Orders o
--或：from join Orders o right outer join Customers c 
-ON(c.CustomerId=o.CustomerId)

--2）右外联接：“右表”为“核心”，“左表”为子表；左边的信息右边没有的，不能输出--
select * from Customers
select c.CustomerId as 客户编号, c.CustomerName as 客户姓名, c.Phone as 电话号码, c.Address1 as 地址, 
o.Amount as 订单金额, o.OrderTime as 下单时间
from Customers c right outer join Orders o
--或：from  join Orders o left outer join  Customers c
ON(c.CustomerId=o.CustomerId)

（5）多表查询

--多表查询
--查询所有订单，显示客户姓名、电话号码、订单编号、订单金额、快递地址、下单时间
select c.CustomerName as 客户姓名, c.Phone as 电话号码,
o.OrderId as 订单编号, o.Amount as 订单金额,o.Address as 快递地址, o.OrderTime as 下单时间
from Customers c,Orders o where o.CustomerId=c.CustomerId   --等价于内联接

（6）查询订单

--查询张三的订单，显示客户姓名、电话号码、订单编号、订单金额、快递地址、下单时间
select c.CustomerName as 客户姓名, c.Phone as 电话号码,
o.OrderId as 订单编号, o.Amount as 订单金额,o.Address as 快递地址, o.OrderTime as 下单时间
from Customers c, Orders o 
where o.CustomerId=c.CustomerId AND c.CustomerName='张三'

（7）统计订单信息

--统计每个客户的订单信息：客户名称、订单总额、订单量;以订单总额进行降序排序
select c.CustomerName as 客户名称, ISNULL(sum(o.Amount),0) as 总额, count(o.OrderId) as 订单量
from Customers c left JOIN Orders o 
 ON(c.CustomerId = o.CustomerId) group by c.CustomerName order by 总额 desc