创建一个数据库
create database myssm;
使用当前数据库
use myssm;
复制下面代码,执行,创建各种表格
DROP TABLE IF EXISTS tbl_activity;
DROP TABLE IF EXISTS tbl_activity_remark;
DROP TABLE IF EXISTS tbl_clue;
DROP TABLE IF EXISTS tbl_clue_activity_relation;
DROP TABLE IF EXISTS tbl_clue_remark;
DROP TABLE IF EXISTS tbl_contacts;
DROP TABLE IF EXISTS tbl_contacts_activity_relation;
DROP TABLE IF EXISTS tbl_contacts_remark;
DROP TABLE IF EXISTS tbl_customer;
DROP TABLE IF EXISTS tbl_customer_remark;
DROP TABLE IF EXISTS tbl_dic_type;
DROP TABLE IF EXISTS tbl_dic_value;
DROP TABLE IF EXISTS tbl_tran;
DROP TABLE IF EXISTS tbl_tran_history;
DROP TABLE IF EXISTS tbl_tran_remark;
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_activity
(
activity_id CHAR ( 32 ) NOT NULL ,
activity_owner CHAR ( 32 ) ,
activity_name VARCHAR ( 255 ) ,
activity_start_date CHAR ( 10 ) ,
activity_end_date CHAR ( 10 ) ,
activity_cost VARCHAR ( 255 ) ,
activity_description VARCHAR ( 255 ) ,
activity_create_time CHAR ( 19 ) ,
activity_create_by VARCHAR ( 255 ) ,
activity_edit_time CHAR ( 19 ) ,
activity_edit_by VARCHAR ( 255 ) ,
PRIMARY KEY ( activity_id)
) ;
ALTER TABLE tbl_activity COMMENT '市场活动表' ;
CREATE TABLE tbl_activity_remark
(
acitvity_remark_id CHAR ( 32 ) NOT NULL ,
activity_id CHAR ( 32 ) ,
ar_note_content VARCHAR ( 255 ) ,
ar_create_time CHAR ( 19 ) ,
ar_create_by VARCHAR ( 255 ) ,
ar_edit_time CHAR ( 19 ) ,
ar_edit_by VARCHAR ( 255 ) ,
ar_edit_flag CHAR ( 1 ) ,
PRIMARY KEY ( acitvity_remark_id)
) ;
ALTER TABLE tbl_activity_remark COMMENT '市场活动备注表' ;
CREATE TABLE tbl_clue
(
clue_id CHAR ( 32 ) NOT NULL ,
clue_owner CHAR ( 32 ) ,
clue_remark_idcompany VARCHAR ( 255 ) ,
clue_appellation VARCHAR ( 255 ) ,
clue_fullname VARCHAR ( 255 ) ,
clue_job VARCHAR ( 255 ) ,
clue_email VARCHAR ( 255 ) ,
clue_mphone VARCHAR ( 255 ) ,
clue_website VARCHAR ( 255 ) ,
clue_phone VARCHAR ( 255 ) ,
clue_state VARCHAR ( 255 ) ,
clue_source VARCHAR ( 255 ) ,
clue_description VARCHAR ( 255 ) ,
clue_contact_summary VARCHAR ( 255 ) ,
clue_create_by VARCHAR ( 255 ) ,
clue_create_time CHAR ( 19 ) ,
clue_edit_by VARCHAR ( 255 ) ,
clue_edit_time CHAR ( 19 ) ,
clue_next_contact_time CHAR ( 10 ) ,
clue_address VARCHAR ( 255 ) ,
PRIMARY KEY ( clue_id)
) ;
CREATE TABLE tbl_clue_activity_relation
(
clue_activity_relation_id CHAR ( 32 ) NOT NULL ,
clue_id CHAR ( 32 ) ,
activity_id CHAR ( 32 ) ,
PRIMARY KEY ( clue_activity_relation_id)
) ;
CREATE TABLE tbl_clue_remark
(
clue_remark_id CHAR ( 32 ) NOT NULL ,
clue_id CHAR ( 32 ) ,
clue_remark_note_content VARCHAR ( 255 ) ,
clue_remark_create_by VARCHAR ( 255 ) ,
clue_remark_create_time CHAR ( 19 ) ,
clue_remark_edit_by VARCHAR ( 255 ) ,
clue_remark_edit_time CHAR ( 19 ) ,
clue_remark_edit_flag CHAR ( 1 ) ,
PRIMARY KEY ( clue_remark_id)
) ;
CREATE TABLE tbl_contacts
(
contacts_id CHAR ( 32 ) NOT NULL ,
customer_id CHAR ( 32 ) ,
contacts_owner CHAR ( 32 ) ,
contacts_source VARCHAR ( 255 ) ,
contacts_fullname VARCHAR ( 255 ) ,
contacts_appellation VARCHAR ( 255 ) ,
contacts_emaill VARCHAR ( 255 ) ,
contacts_mphone VARCHAR ( 255 ) ,
contacts_job VARCHAR ( 255 ) ,
contacts_create_by VARCHAR ( 255 ) ,
contacts_create_time CHAR ( 19 ) ,
contacts_edit_by VARCHAR ( 255 ) ,
contacts_edit_time CHAR ( 19 ) ,
contacts_description VARCHAR ( 255 ) ,
contacts_contact_summary VARCHAR ( 255 ) ,
contacts_next_contact_time CHAR ( 10 ) ,
contacts_address VARCHAR ( 255 ) ,
PRIMARY KEY ( contacts_id)
) ;
CREATE TABLE tbl_contacts_activity_relation
(
contacts_activity_relation_id CHAR ( 32 ) NOT NULL ,
contacts_id CHAR ( 32 ) ,
activity_id CHAR ( 32 ) ,
PRIMARY KEY ( contacts_activity_relation_id)
) ;
CREATE TABLE tbl_contacts_remark
(
contacts_remark_id CHAR ( 32 ) NOT NULL ,
contacts_id CHAR ( 32 ) ,
contacts_remark_note_content VARCHAR ( 255 ) ,
contacts_remark_create_by VARCHAR ( 255 ) ,
contacts_remark_create_time CHAR ( 19 ) ,
contacts_remark_edit_by VARCHAR ( 255 ) ,
contacts_remark_edit_time CHAR ( 19 ) ,
contacts_remark_edit_flag CHAR ( 1 ) ,
PRIMARY KEY ( contacts_remark_id)
) ;
CREATE TABLE tbl_customer
(
customer_id CHAR ( 32 ) NOT NULL ,
customer_owner CHAR ( 32 ) ,
customer_name VARCHAR ( 255 ) ,
customer_website VARCHAR ( 255 ) ,
customer_phone VARCHAR ( 255 ) ,
customer_description VARCHAR ( 255 ) ,
customer_contact_summary VARCHAR ( 255 ) ,
customer_next_contact_time CHAR ( 10 ) ,
customer_address VARCHAR ( 255 ) ,
customer_create_by VARCHAR ( 255 ) ,
customer_create_time CHAR ( 19 ) ,
customer_edit_by VARCHAR ( 255 ) ,
customer_edit_time CHAR ( 19 ) ,
PRIMARY KEY ( customer_id)
) ;
CREATE TABLE tbl_customer_remark
(
customer_remark_id CHAR ( 32 ) NOT NULL ,
customer_id CHAR ( 32 ) ,
customer_remark_note_content VARCHAR ( 255 ) ,
customer_remark_create_by VARCHAR ( 255 ) ,
customer_remark_create_time CHAR ( 19 ) ,
customer_remark_edit_by VARCHAR ( 255 ) ,
customer_remark_edit_time CHAR ( 19 ) ,
customer_remark_edit_flag CHAR ( 1 ) ,
PRIMARY KEY ( customer_remark_id)
) ;
CREATE TABLE tbl_dic_type
(
dic_code VARCHAR ( 255 ) NOT NULL ,
div_name VARCHAR ( 255 ) ,
dic_description VARCHAR ( 255 ) ,
PRIMARY KEY ( dic_code)
) ;
ALTER TABLE tbl_dic_type COMMENT '数据字典类型表' ;
CREATE TABLE tbl_dic_value
(
dic_id CHAR ( 32 ) NOT NULL ,
dic_code VARCHAR ( 255 ) ,
dic_value VARCHAR ( 255 ) NOT NULL ,
dic_text VARCHAR ( 255 ) ,
order_no VARCHAR ( 255 ) ,
PRIMARY KEY ( dic_id)
) ;
ALTER TABLE tbl_dic_value COMMENT '数据字典值' ;
CREATE TABLE tbl_tran
(
tran_id CHAR ( 32 ) NOT NULL ,
activity_id CHAR ( 32 ) ,
contacts_id CHAR ( 32 ) ,
customer_id CHAR ( 32 ) ,
tran_owner CHAR ( 32 ) ,
tran_money VARCHAR ( 255 ) ,
tran_name VARCHAR ( 255 ) ,
tran_expected_date CHAR ( 10 ) ,
tran_stage VARCHAR ( 255 ) ,
tran_type VARCHAR ( 255 ) ,
tran_source VARCHAR ( 255 ) ,
tran_description VARCHAR ( 255 ) ,
tran_contact_summary VARCHAR ( 255 ) ,
tran_next_contact_time CHAR ( 10 ) ,
tran_create_by VARCHAR ( 255 ) ,
tran_create_time CHAR ( 19 ) ,
tran_edit_by VARCHAR ( 255 ) ,
tran_edit_time CHAR ( 19 ) ,
PRIMARY KEY ( tran_id)
) ;
CREATE TABLE tbl_tran_history
(
tran_history_id CHAR ( 32 ) NOT NULL ,
tran_id CHAR ( 32 ) ,
tran_histroy_stage VARCHAR ( 255 ) ,
tran_histroy_money VARCHAR ( 255 ) ,
tran_histroy_expected_date CHAR ( 10 ) ,
tran_histroy_create_by VARCHAR ( 255 ) ,
tran_histroy_create_time CHAR ( 19 ) ,
PRIMARY KEY ( tran_history_id)
) ;
CREATE TABLE tbl_tran_remark
(
tran_remark_id CHAR ( 32 ) NOT NULL ,
tran_id CHAR ( 32 ) ,
tran_remark_note_content VARCHAR ( 255 ) ,
tran_remark_create_by VARCHAR ( 255 ) ,
tran_remark_create_time CHAR ( 19 ) ,
tran_remark_edit_time CHAR ( 19 ) ,
tran_remark_edit_by VARCHAR ( 255 ) ,
tran_remark_edit_flag CHAR ( 1 ) ,
PRIMARY KEY ( tran_remark_id)
) ;
CREATE TABLE tbl_user
(
user_id CHAR ( 32 ) NOT NULL ,
u_login_act VARCHAR ( 255 ) ,
u_login_pwd VARCHAR ( 255 ) ,
div_name VARCHAR ( 255 ) ,
u_email VARCHAR ( 255 ) ,
u_expire_time CHAR ( 19 ) ,
u_lock_state CHAR ( 1 ) ,
u_deptno CHAR ( 4 ) ,
u_allow_ips VARCHAR ( 255 ) ,
u_createTime CHAR ( 19 ) ,
u_create_by VARCHAR ( 255 ) ,
u_edit_time CHAR ( 19 ) ,
u_edit_by VARCHAR ( 255 ) ,
PRIMARY KEY ( user_id)
) ;
ALTER TABLE tbl_user COMMENT '用户表' ;
ALTER TABLE tbl_activity_remark ADD CONSTRAINT FK_Relationship_2 FOREIGN KEY ( activity_id)
REFERENCES tbl_activity ( activity_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
` tbl_user``tbl_tran_remark`
ALTER TABLE tbl_clue_activity_relation ADD CONSTRAINT FK_Relationship_3 FOREIGN KEY ( clue_id)
REFERENCES tbl_clue ( clue_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_clue_activity_relation ADD CONSTRAINT FK_Relationship_4 FOREIGN KEY ( activity_id)
REFERENCES tbl_activity ( activity_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_clue_remark ADD CONSTRAINT FK_Relationship_5 FOREIGN KEY ( clue_id)
REFERENCES tbl_clue ( clue_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_contacts ADD CONSTRAINT FK_Relationship_7 FOREIGN KEY ( customer_id)
REFERENCES tbl_customer ( customer_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_contacts_activity_relation ADD CONSTRAINT FK_Relationship_8 FOREIGN KEY ( activity_id)
REFERENCES tbl_activity ( activity_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_contacts_activity_relation ADD CONSTRAINT FK_Relationship_9 FOREIGN KEY ( contacts_id)
REFERENCES tbl_contacts ( contacts_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_contacts_remark ADD CONSTRAINT FK_Relationship_10 FOREIGN KEY ( contacts_id)
REFERENCES tbl_contacts ( contacts_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_customer_remark ADD CONSTRAINT FK_Relationship_6 FOREIGN KEY ( customer_id)
REFERENCES tbl_customer ( customer_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_dic_value ADD CONSTRAINT FK_Relationship_1 FOREIGN KEY ( dic_code)
REFERENCES tbl_dic_type ( dic_code) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_tran ADD CONSTRAINT FK_Relationship_11 FOREIGN KEY ( contacts_id)
REFERENCES tbl_contacts ( contacts_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_tran ADD CONSTRAINT FK_Relationship_12 FOREIGN KEY ( customer_id)
REFERENCES tbl_customer ( customer_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_tran ADD CONSTRAINT FK_Relationship_13 FOREIGN KEY ( activity_id)
REFERENCES tbl_activity ( activity_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_tran_history ADD CONSTRAINT FK_Relationship_14 FOREIGN KEY ( tran_id)
REFERENCES tbl_tran ( tran_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE tbl_tran_remark ADD CONSTRAINT FK_Relationship_15 FOREIGN KEY ( tran_id)
REFERENCES tbl_tran ( tran_id) ON DELETE RESTRICT ON UPDATE RESTRICT ;