crm_createtable.sql

数据库客户信息管理系统,C++,SQL下完成.进入系统前需要身份验证、用户名、密码

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'area_info_tab')
   DROP TABLE area_info_tab
--创建地方信息表
CREATE TABLE area_info_tab(
	area_id INT PRIMARY KEY,
	area_name   VARCHAR(24) UNIQUE NOT NULL
);
       
--创建结识途径信息表
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'approach_info_tab')
   DROP TABLE approach_info_tab
CREATE TABLE approach_info_tab(
	approach_id INT PRIMARY KEY,
	approach_name   VARCHAR(24) UNIQUE NOT NULL
);

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'profession_info_tab')
   DROP TABLE profession_info_tab

--创建职位信息表
CREATE TABLE profession_info_tab(
	profession_id INT PRIMARY KEY,
	profession_name   VARCHAR(24) UNIQUE NOT NULL
); 

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'vocation_info_tab')
   DROP TABLE vocation_info_tab
             
--创建行业信息表
CREATE TABLE vocation_info_tab(
	vocation_id INT PRIMARY KEY,
	vocation_name   VARCHAR(24) UNIQUE NOT NULL
);

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'customer_info_tab')
   DROP TABLE customer_info_tab
--创建客户信息表
CREATE TABLE customer_info_tab(
	id  INT IDENTITY(1,1) PRIMARY KEY,
	name  VARCHAR(24) NOT NULL,
	area  VARCHAR(24) NULL,
	profession  VARCHAR(24) NULL,
	vocation  VARCHAR(24) NULL,
	company  VARCHAR(24) NULL,
	approach  VARCHAR(24) NULL,
	email  VARCHAR(24) NULL,
	phone  VARCHAR(24) NULL,
	mobile  VARCHAR(24) NULL,
	interest  VARCHAR(60) NULL,
	memo  VARCHAR(1000) NULL	
);

--添加所在地area的索引                            
CREATE INDEX customer_info_tab_areaindex 
    ON customer_info_tab(area); 
--添加profession的索引                            
CREATE INDEX customer_info_tab_professionindex 
    ON customer_info_tab(profession); 
--添加所在地vocation的索引                            
CREATE INDEX customer_info_tab_vocationindex 
    ON customer_info_tab(vocation); 
--添加所在地approach的索引                            
CREATE INDEX customer_info_tab_approachindex 
    ON customer_info_tab(approach);