数据库开发及ADO.NET(23)——表连接Join

1、有客户表（T_Customers）和订单表（T_Orders）两个表，客户表字段为：Id、Name、Age，订单表字段为：Id、BillNo、CustomerId，订单表通过CustomerId关联客户表。测试数据如下：（在做表与表的连接查询时，大表在前，小表在后）

CREATE TABLE [T_Customers](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[Age] [int] NULL
);

INSERT [T_Customers] ([Id], [Name], [Age]) VALUES (1, N'tom', 10);
INSERT [T_Customers] ([Id], [Name], [Age]) VALUES (2, N'jerry', 15);
INSERT [T_Customers] ([Id], [Name], [Age]) VALUES (3, N'john', 22);
INSERT [T_Customers] ([Id], [Name], [Age]) VALUES (4, N'lily', 18);
INSERT [T_Customers] ([Id], [Name], [Age]) VALUES (5, N'lucy', 18);

CREATE TABLE [T_Orders](
	[Id] [int] NOT NULL,
	[BillNo] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[CustomerId] [int] NULL);

INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (1, N'001', 1);
INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (2, N'002', 1);
INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (3, N'003', 3);
INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (4, N'004', 2);
INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (5, N'005', 2);
INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (6, N'006', 5);
INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (7, N'007', 4);
INSERT [T_Orders] ([Id], [BillNo], [CustomerId]) VALUES (8, N'008', 5);

 

（1）SELECT o.BillNo,c.Name,c.Age from T_Orders as o JOIN T_Customers as c on o.CustomerId=c.Id

（2）join是和哪个表连接，on后是连接的关系是什么。(多表) 。

 

（3）要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄。

select o.BillNo,c.Name
from T_Orders as o
join T_Customers as c
on o.CustomerId=c.Id
where c.Age>15

 

（4）要求显示年龄大于平均年龄的顾客购买的订单。

select o.BillNo,c.Name,c.Age
from T_Orders as o
join T_Customers as c on o.CustomerId=c.Id
where c.Age>(select AVG(Age) from T_Customers)

 

（5）Inner Join、Left Join、Right Join