create table url(
ID int not null primary key auto_increment,
url varchar(64),
urlname varchar(50),
urlsn varchar(255));
insert into url(url,urlname,urlsn) values("http://www.zhengmao.org","周易在線","在線預測留言本");

create table bbs(
ID int not null primary key auto_increment,
SubjectId int not null default 0,
Name varchar(128),
Time datetime not null,
Subject varchar(255),
Content text,
Pic varchar(128),
click int(5) not null default 0,
IP varchar(20));

create table type(
ID int not null primary key auto_increment,
type varchar(64) not null);

create table bumen(ID int not null primary key auto_increment,type varchar(64) not null);
insert into bumen(type) values("辦公室");
insert into bumen(type) values("市場部");
insert into bumen(type) values("技術部");
insert into bumen(type) values("生產部");
insert into bumen(type) values("設備部");

create table calendar(
ID int not null primary key auto_increment,
title varchar(62),
time datetime not null,
remindtime datetime not null,
content text,
state varchar(1) not null default '0',
userid int not null default 0);

create table card(
ID int not null primary key auto_increment,
cname varchar(16),
ename varchar(32),
company varchar(128),
comaddress varchar(128),
area varchar(16),
trade varchar(32),
postcode varchar(6),
duty varchar(16),
phone varchar(32),
fax varchar(32),
handset varchar(11),
email varchar(32),
flag varchar(16),
remark varchar(255),
userid int not null default 0);

create table chat(
ID int not null primary key auto_increment,
send int not null defalut 0,
receive int not null defalut 0,
message text,
zt varchar(1) not null default '0',
who varchar(32),
time varchar(16),
endtime datetime);

create table help(
ID int not null primary key auto_increment,
type varchar(16),
title varchar(16),
content text,
time varchar(16));

//change to upfile
create table learn(
ID int not null primary key auto_increment,
type tinyint not null default 0,
tname varchar(16),
part varchar(32),
title varchar(32),
content text,
body mediumblob,
html varchar(128),
filesize int not null default 0,
time datetime,
count int not null default 0,
IP varchar(20),
filename varchar(32),
contenttype varchar(32));

create table upfile(
ID int not null primary key auto_increment,
type tinyint not null default 0,
tname varchar(16),
part varchar(32),
title varchar(32),
ttext varchar(255),
body mediumblob,
html varchar(128),
filesize int not null default 0,
time datetime,
IP varchar(20),
count int not null default 0,
filename varchar(32),
contenttype varchar(32) not null default "");
type //以后使用表示分類
type=1 表示上報的文件

create table jhtdata(
ID int not null primary key auto_increment,
tname varchar(16),
part varchar(32),
type tinyint not null default 0,
title varchar(32),
ttext varchar(255),
html varchar(128),
filesize int not null default 0,
time datetime,
IP varchar(20),
fjname varchar(64),
inid int not null default 0,
outid int not null default 0);
INSERT INTO `jhtdata` ( `tname` , `part` , `type` , `title` , `ttext` , `html` , `time` , `IP` , `fjname` ) VALUES ('', '正茂', '正茂軟件', '1', 'ok', '正茂的上傳文件', 'download/', '2003-06-09 20:32:13', '127.0.0.1', NULL );

create table soft(
ID int not null primary key auto_increment,
name varchar(32),
upname int not null default 0,
content text,
url varchar(64),
size varchar(32) not null default "0",
time varchar(16),
best varchar(1) not null default "0");

create table tel(
ID int not null primary key auto_increment,
telname varchar(16),
telephone varchar(24));

create table type(
ID int not null primary key auto_increment,
urlname varchar(64),
url varchar(64),
urlsn varchar(32));

create table myuser(
ID int not null primary key auto_increment,
username varchar(16) not null,
pwd varchar(16) not null,
part varchar(32) not null,
rule varchar(1) not null default 'd',
email varchar(64) not null,
wenti varchar(32) not null,
daang varchar(32) not null,
shenhe varchar(1) not null default '0',
time datetime not null,
IP varchar(20) not null,
state tinyint  not null default 0,
phone varchar(24)  not null,
name varchar(16) not null,
logIP varchar(20) not null,
Utime datetime not null,
times int not null default 0,
mobile varchar(11) not null,
pagesize tinyint not null default 10,
);

create table userinfo(
ID int not null primary key auto_increment,
userid int not null default 0,
Uname varchar(16),
sex varchar(6) not null default "男性",
duty varchar(16),
grade varchar(16),
birthday varchar(32),
polity varchar(16),
health varchar(16),
Nplace varchar(32),
weight varchar(16),
idcard varchar(15),
marriage varchar(8) not null default "未婚",
Fschool varchar(64),
member varchar(16),
speciality varchar(16),
length varchar(8),
study varchar(8),
foreign1 varchar(16),
Elevel varchar(8),
Clevel varchar(8),
Hplace varchar(64),
QQ varchar(10),
call varchar(16),
place varchar(64),
love varchar(64),
award varchar(63),
experience varchar(64),
family varchar(64),
contact varchar(64),
remark text,
nation varchar(16),
height varchar(8),
Ltime varchar(16),
photo varchar(32) not null default "image/jpeg",
photo1 blob,
phone varchar(24),
mobile varchar(11),
havephoto tinyint not null default 0,
check tinyint not null default 0);

//郵政編碼
create table post(
ID int not null primary key auto_increment,
dq varchar(20),
dm varchar(20),
yb varchar(6),
qh varchar(4));
load data infile "c:/mysql/data/post.txt" into table post;

//手機位置
create table shouji(
start int,
end int,
city varchar(16),
city1 varchar(16),
detail varchar(32),
ID int not null primary key auto_increment);

load data infile "c:/mysql/data/shouji.txt" into table shouji;

//IP地址
create table ipaddr(
start varchar(20),
end varchar(20),
country varchar(64),
local varchar(64),
detail varchar(64),
ID int not null primary key auto_increment);
load data infile "c:/mysql/data/ip.txt" into table ipaddr;


create table allmail(
ID int not null primary key auto_increment,
time datetime,
towho int not null default 0,
stowho varchar(32),
userid int not null default 0,
username varchar(32),
ssb varchar(32),
sb int not null default 0,
title varchar(128),
status tinyint not null default 0,
message text,
url varchar(64),
isread tinyint not null default 0);

status = 0 收件箱
status = 1 草稿箱
status = 2 垃圾箱
status = 3 公共郵件
isread = 0 沒有閱讀 1 已經閱讀
url 附件的下載地址
userid 總是自己/
towho 總是別人/