1.join语句
Sql join语句用来合并两个或多个表中的记录。ANSI标准SQL语句中有四种JOIN:INNER,OUTER,LEFTER,RIGHT,一个表或视图也可以可以和它自身做JOIN操作。下面举例说明。下面所举的例子使用的表有Employee和Department,在这两个表中Department.DepartmentID是主键,Employee.DepartmentID是外键。
图1—Employee表
LastName |
DepartmentID |
Country |
EmployeeID |
John |
NULL |
Germany |
306 |
Jones |
33 |
Australia |
124 |
Rafferty |
31 |
Australia |
123 |
Robinson |
34 |
United States |
201 |
Smith |
34 |
Germany |
305 |
图2—Department表
DepartmentID |
DepartmentName |
31 |
Sales |
33 |
Engineering |
34 |
Clerical |
35 |
Marketing |
下面是建表和数据语句:
USE [AppressFinancial]
GO
/****** Object: Table [dbo].[department] Script Date: 08/11/2010 15:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
[DepartmentID] [int] NULL,
[DepartmentName] [varchar](25) NULL,
UNIQUE NONCLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO USE [AppressFinancial]
GO
/****** Object: Table [dbo].[department] Script Date: 08/11/2010 15:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
[DepartmentID] [int] NULL,
[DepartmentName] [varchar](25) NULL,
UNIQUE NONCLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO