手机联系人系统及数据库生成脚本
1. 下载并安装.Net Framework 2.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=0856EACB-4362-4B0D-8EDD-AAB15C5E04F5&displaylang=zh-cn
2. 生成数据库
--1)创建数据库
USE [master]
GO
/****** 创建数据库前, 检查是否已存在相同名字的数据库,若存在, 则先删除它 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ContactDB')
DROP DATABASE ContactDB
CREATE DATABASE ContactDB
USE ContactDB
/****** 删除已有的数据库 ******/
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Group_ContactGroup_fk]')
AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ContactGroup] DROP CONSTRAINT [Group_ContactGroup_fk]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Group_ContactGroup_fk')
AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ContactGroup] DROP CONSTRAINT Group_ContactGroup_fk
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[ContactGroup]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [ContactGroup]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Contact]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Contact]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Group]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Group]
GO
--2)创建联系人表
CREATE TABLE Contact/*记录每个联系人的详细信息*/
(
ContactID int IDENTITY(1,1) NOT NULL, /*1-150*/
[Name] nvarchar(20) NOT NULL,
Mobile varchar(20) ,
Telephone varchar(20),
OfficeTel varchar(20),
Fax varchar(20),
Email varchar(20)/*如[email protected]*/,
Category Char(1) CONSTRAINT Check_Category CHECK (Category IN('A','B','C')),/*A办公类，B个人类，C商务类*/
CONSTRAINT Contact_pk PRIMARY KEY (ContactID)
)
GO
--3)创建组群表
CREATE TABLE [Group]/*记录组群的详细信息*/
(
GroupID int PRIMARY KEY IDENTITY(1,1) Not NULL,
Title nvarchar(20) NOT NULL UNIQUE
)
GO
--4)创建联系人组群表
CREATE TABLE ContactGroup
(
ContactID int,
GroupID int,
CONSTRAINT ContactGroup_pk PRIMARY KEY (ContactID,GroupID),
CONSTRAINT Contact_ContactGroup_fk FOREIGN KEY (ContactID) REFERENCES Contact(contactID),
CONSTRAINT Group_ContactGroup_fk FOREIGN KEY (GroupID) REFERENCES [Group](GroupID)
)
GO
CREATE UNIQUE INDEX ContactID ON Contact(ContactID)
CREATE UNIQUE INDEX Name_Mobile ON Contact([Name], Mobile)
CREATE UNIQUE INDEX GroupId ON [Group](GroupID)
CREATE INDEX Title ON [Group](Title)
GO
--5)插入数据 147条记录到Contact中
INSERT INTO Contact VALUES('许泽波', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波1', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋2', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨3', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑4', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言9', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾51', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建61', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽71', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平81', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言19', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波a', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋b', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨c', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑d', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾e', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建f', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽g', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平h', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言i', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良j', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆k', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波1l', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋2m', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨3n', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑4o', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5p', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6q', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7r', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8s', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言9t', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10u', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11v', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良1011', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w1', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x1', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y1', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z1', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5111', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6111', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7111', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8111', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言1911', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w11', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x11', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y11', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z11', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾51111', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建61111', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽71111', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平81111', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言19111aas', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆111111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w112', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x112', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y112', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z112', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511112', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611112', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711112', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811112', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191112slko', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101112', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111112', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w113', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x113', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y113', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z113', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511113', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611113', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711113', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811113', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191113dd', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101113', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111113', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w114', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x114', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y114', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z114', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511114', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611141', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7111441', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811114', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191114wojg', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101114', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111114', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w115', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x115', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y115', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z115', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511151', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611115', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711151', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811151', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191511asd', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良105111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111151', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w1156', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x1156', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y1156', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z1165', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5111651', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6116115', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7116151', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8111651', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言1916511ldop', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良1065111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11116151', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('吴江礽71168151', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平81911651', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言19186511pwj', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10865111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆111169151', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
GO
INSERT INTO [Group] VALUES('朋友')
INSERT INTO [Group] VALUES('同事')
INSERT INTO [Group] VALUES('重要人物')
INSERT INTO [Group] VALUES('家人')
GO
INSERT INTO ContactGroup VALUES('1','1')
INSERT INTO ContactGroup VALUES('2','2')
INSERT INTO ContactGroup VALUES('3','2')
INSERT INTO ContactGroup VALUES('4','3')
INSERT INTO ContactGroup VALUES('5','1')
INSERT INTO ContactGroup VALUES('6','1')
INSERT INTO ContactGroup VALUES('7','4')
INSERT INTO ContactGroup VALUES('8','2')
INSERT INTO ContactGroup VALUES('9','3')
INSERT INTO ContactGroup VALUES('10','2')
INSERT INTO ContactGroup VALUES('11','1')
USE [master]
GO
/****** 创建数据库前, 检查是否已存在相同名字的数据库,若存在, 则先删除它 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'ContactDB')
DROP DATABASE ContactDB
CREATE DATABASE ContactDB
USE ContactDB
/****** 删除已有的数据库 ******/
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Group_ContactGroup_fk]')
AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ContactGroup] DROP CONSTRAINT [Group_ContactGroup_fk]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].Group_ContactGroup_fk')
AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ContactGroup] DROP CONSTRAINT Group_ContactGroup_fk
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[ContactGroup]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [ContactGroup]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Contact]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Contact]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Group]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Group]
GO
--2)创建联系人表
CREATE TABLE Contact/*记录每个联系人的详细信息*/
(
ContactID int IDENTITY(1,1) NOT NULL, /*1-150*/
[Name] nvarchar(20) NOT NULL,
Mobile varchar(20) ,
Telephone varchar(20),
OfficeTel varchar(20),
Fax varchar(20),
Email varchar(20)/*如[email protected]*/,
Category Char(1) CONSTRAINT Check_Category CHECK (Category IN('A','B','C')),/*A办公类，B个人类，C商务类*/
CONSTRAINT Contact_pk PRIMARY KEY (ContactID)
)
GO
--3)创建组群表
CREATE TABLE [Group]/*记录组群的详细信息*/
(
GroupID int PRIMARY KEY IDENTITY(1,1) Not NULL,
Title nvarchar(20) NOT NULL UNIQUE
)
GO
--4)创建联系人组群表
CREATE TABLE ContactGroup
(
ContactID int,
GroupID int,
CONSTRAINT ContactGroup_pk PRIMARY KEY (ContactID,GroupID),
CONSTRAINT Contact_ContactGroup_fk FOREIGN KEY (ContactID) REFERENCES Contact(contactID),
CONSTRAINT Group_ContactGroup_fk FOREIGN KEY (GroupID) REFERENCES [Group](GroupID)
)
GO
CREATE UNIQUE INDEX ContactID ON Contact(ContactID)
CREATE UNIQUE INDEX Name_Mobile ON Contact([Name], Mobile)
CREATE UNIQUE INDEX GroupId ON [Group](GroupID)
CREATE INDEX Title ON [Group](Title)
GO
--5)插入数据 147条记录到Contact中
INSERT INTO Contact VALUES('许泽波', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波1', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋2', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨3', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑4', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言9', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾51', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建61', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽71', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平81', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言19', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波a', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋b', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨c', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑d', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾e', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建f', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽g', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平h', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言i', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良j', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆k', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波1l', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋2m', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨3n', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑4o', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5p', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6q', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7r', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8s', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言9t', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10u', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11v', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良1011', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w1', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x1', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y1', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z1', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5111', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6111', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7111', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8111', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言1911', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w11', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x11', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y11', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z11', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾51111', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建61111', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽71111', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平81111', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言19111aas', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆111111', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w112', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x112', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y112', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z112', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511112', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611112', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711112', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811112', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191112slko', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101112', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111112', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w113', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x113', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y113', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z113', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511113', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611113', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711113', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811113', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191113dd', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101113', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111113', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w114', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x114', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y114', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z114', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511114', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611141', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7111441', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811114', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191114wojg', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良101114', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111114', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w115', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x115', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y115', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z115', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾511151', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建611115', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽711151', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平811151', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言191511asd', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良105111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆1111151', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('许泽波11w1156', '13751870781', '39321590', '39342260', '39342260', '[email protected]', 'c')
INSERT INTO Contact VALUES('袁锋21x1156', '13760792010', '39322819', '39345620', '39345621', '[email protected]', 'b')
INSERT INTO Contact VALUES('王滨31y1156', '13836520265', '39322849', '39344464', '39344465', '[email protected]', 'a')
INSERT INTO Contact VALUES('叶剑41z1165', '13820363512', '39321593', '39349587', '39349588', '[email protected]', 'c')
INSERT INTO Contact VALUES('欧蕾5111651', '13756933658', '39346320', '39363269', '39363269', '[email protected]', 'a')
INSERT INTO Contact VALUES('欧建6116115', '13666923654', '39356325', '39382398', '39382398', '[email protected]', 'b')
INSERT INTO Contact VALUES('吴江礽7116151', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平8111651', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言1916511ldop', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良1065111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆11116151', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
INSERT INTO Contact VALUES('吴江礽71168151', '13889654464', '39321360', '39376954', '39376954', '[email protected]', 'b')
INSERT INTO Contact VALUES('黄生平81911651', '13669872541', '39323695', '39323630', '39323630', '[email protected]', 'a')
INSERT INTO Contact VALUES('陈言19186511pwj', '13778963582', '39348521', '39363130', '39363130', '[email protected]', 'c')
INSERT INTO Contact VALUES('彭克良10865111', '13636521687', '39323691', '39366338', '39366338', '[email protected]', 'b')
INSERT INTO Contact VALUES('土豆111169151', '13658921453', '39348536', '39341296', '39341296', '[email protected]', 'c')
GO
INSERT INTO [Group] VALUES('朋友')
INSERT INTO [Group] VALUES('同事')
INSERT INTO [Group] VALUES('重要人物')
INSERT INTO [Group] VALUES('家人')
GO
INSERT INTO ContactGroup VALUES('1','1')
INSERT INTO ContactGroup VALUES('2','2')
INSERT INTO ContactGroup VALUES('3','2')
INSERT INTO ContactGroup VALUES('4','3')
INSERT INTO ContactGroup VALUES('5','1')
INSERT INTO ContactGroup VALUES('6','1')
INSERT INTO ContactGroup VALUES('7','4')
INSERT INTO ContactGroup VALUES('8','2')
INSERT INTO ContactGroup VALUES('9','3')
INSERT INTO ContactGroup VALUES('10','2')
INSERT INTO ContactGroup VALUES('11','1')
3.下载 https://files.cnblogs.com/fengmk2/PhoneBook1.0.rar 运行PhoneBook.exe
浙公网安备 33010602011771号