2011-11-14，联系人，数据表SQL_通讯录基本表sql-优快云博客

本文介绍了一个基于 SQL Server 的联系人管理系统的设计方案，包括联系人表与联系人组表的创建语句，并展示了如何通过视图将两个表进行关联查询。

1、联系人表

CREATE TABLE [dbo].[contactList](
	[contactId] [int] IDENTITY(1,1) NOT NULL,
	[contactName] [nvarchar](10) NOT NULL,
	[contactMP] [varchar](15) NULL,
	[contactSecPhone] [varchar](15) NULL,
	[contactThirPhone] [varchar](15) NULL,
	[contactAddress] [nvarchar](50) NULL,
	[contactEmail] [nvarchar](40) NULL,
 CONSTRAINT [PK_customPersonList] PRIMARY KEY CLUSTERED

(
 [contactId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2、联系人组表

CREATE TABLE [dbo].[contactGroup](
	[groupId] [nvarchar](10) NOT NULL,
	[contactId] [int] NOT NULL,
	[defaultId] [bit] NULL,
	[groupType] [nvarchar](10) NULL,
 CONSTRAINT [PK_groupContact] PRIMARY KEY CLUSTERED 
(
	[groupId] ASC,
	[contactId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

3、视图

SELECT     dbo.contactGroup.groupId, dbo.contactGroup.contactId, dbo.contactGroup.defaultId, dbo.contactGroup.groupType, dbo.contactList.contactName, 
                      dbo.contactList.contactMP, dbo.contactList.contactSecPhone, dbo.contactList.contactThirPhone, dbo.contactList.contactAddress, 
                      dbo.contactList.contactEmail
FROM         dbo.contactGroup INNER JOIN
                      dbo.contactList ON dbo.contactGroup.contactId = dbo.contactList.contactId