【SQL开源代码栏目提醒】:网学会员SQL开源代码为您提供crm_createtable.sql参考,解决您在crm_createtable.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);