1
create
database
eTable
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
go
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
use
eTable
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
go
8![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
--
表格管理系统
10
--
11
--
功能:可以设置生成各种类型的表格
12
13
--
一、系统功能模板
14
--
1、用户注册
15
--
用户使用邮箱进行注册,注册时需要用户登录邮箱进行确认。注册后的用户才可以创建表格以及参与在线讨论。当用户忘记密码时可通过邮箱找回。
16
create
table
userInfo
17
(
18
[
user_id
]
int
identity
primary
key
,
19
[
user_name
]
varchar
(
200
)
not
null
,
--
用户名称,也是用户的注册邮箱
20
user_pass
varchar
(
32
)
not
null
,
--
用户的登录密码
21
user_date
datetime
default
getdate
(),
--
用户注册的时间
22
true_name
varchar
(
200
)
not
null
,
--
真实姓名
23
log_date
datetime
--
最后一次登录时间
24
--
user_email varchar(200) --备用邮箱
25
)
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
--
2、密码找回
28
--
当用户忘记密码时,系统会生成一个找回密码的链接,此时可重新设置密码,此链接的有效时间为24小时,超过此时间需要重新进行设置。当用户初次生成链接时,添加一条数据,当用户多次生成链接时,后面的数据总是更新前面的数据,也就是同一用户只能存一条数据。24小时该链接失效,用户需要重新申请,24小时内用户只能申请一次,申请时只是把数据进行重发,为同一链接。当用户打开链接时,验证用户的合法性后,该条数据失效,用户可以在此页面中重新设置密码。
29
create
table
getPass
30
(
31
get_id
int
identity
primary
key
,
32
[
user_name
]
varchar
(
200
)
not
null
,
--
用户邮箱
33
get_code
varchar
(
200
)
not
null
,
--
随机生成验证码,并进行加密。
34
get_date
datetime
default
getdate
()
--
获取时间
35
)
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
--
用户登录
38
39
--
3、日志管理 log
40
--
记录整个系统的操作日志,包括系统用户和系统管理员
41
create
table
operateLog
42
(
43
log_id
int
identity
primary
key
,
44
[
user_id
]
int
default
0
,
--
用户编号,当编号为0时为不能确认该用户的编号,如密码找回时。
45
log_message
varchar
(
1000
)
not
null
,
--
发生的事件信息
46
log_date
datetime
default
getdate
(),
--
事件发生的时间
47
log_ip
varchar
(
15
)
not
null
--
用户的IP地址
48
)
49![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
--
4、邮箱配置
51
--
系统设置的邮箱,用于向用户发送邮件,需要配置SMTP服务器,邮箱名称和邮箱登录服务器
52
create
table
email
53
(
54
email_id
int
identity
primary
key
,
55
email_name
varchar
(
200
)
not
null
,
--
邮箱名称
56
email_pass
varchar
(
200
)
not
null
,
--
邮箱密码,采用对称加密
57
email_SMTP
varchar
(
200
)
not
null
,
--
SMTP服务器
58
email_count
int
default
0
--
已经发送的邮件数
59
)
60![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
--
5、系统权限设定
62
--
5.1、系统管理员 admin
63
create
table
adminManage
64
(
65
admin_id
int
identity
primary
key
,
66
admin_name
varchar
(
200
)
not
null
,
--
用户名
67
admin_pass
varchar
(
32
)
not
null
,
--
用户密码
68
69
)
70![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
--
6、讨论区
72
--
讨论区用于用户之间的交流
73
--
6.1、讨论区栏目 bbsClass
74
create
table
bbsClass
75
(
76
class_id
int
identity
primary
key
,
77
class_title
varchar
(
200
)
not
null
,
--
栏目名称
78
class_parent
int
default
0
,
--
父栏目编号
79
[
user_id
]
int
null
--
管理者编号,同用户表userInfo对应,管理者可回复、屏蔽、删除信息,并且可设置排序
80
)
81
--
6.2、讨论区内容
82
create
table
bbs
83
(
84
bbs_id
int
identity
primary
key
,
85
bbs_title
varchar
(
200
)
not
null
,
--
标题
86
bbs_content
text
not
null
,
--
内容
87
class_id
int
not
null
,
--
栏目编号
88
bbs_date
datetime
default
getdate
(),
--
留言时间
89
[
user_id
]
int
not
null
,
--
留言者编号,同用户表userInfo对应
90
bbs_ip
varchar
(
15
)
not
null
,
--
留言者IP地址
91
order_id
int
not
null
--
排序编号,同bbsOrder表相对应
92
)
93
--
6.3、留言排序依据
94
create
table
bbsOrder
95
(
96
order_id
int
identity
primary
key
,
--
97
order_title
varchar
(
200
)
not
null
,
--
类别名称,如精华、推荐、普通、重点等,可设置格式。
98
order_num
int
not
null
,
--
顺序排列
99
order_image
varchar
(
200
)
--
类别图标
100
)
101![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
--
7、内容管理
103
--
内容管理可发布新闻,可发布通知公告等信息
104
--
7.1、内容管理栏目
105
create
table
newClass
106
(
107
class_id
int
identity
primary
key
,
108
class_name
varchar
(
200
)
not
null
,
--
栏目名称
109
class_parent
int
not
null
--
父栏目编号
110
)
111
--
7.2、内容
112
create
table
news
113
(
114
new_id
int
identity
primary
key
,
115
new_title
varchar
(
200
)
not
null
,
--
标题
116
new_content
text
not
null
,
--
内容
117
redirect_url
varchar
(
500
),
--
重定向页面
118
class_id
int
not
null
,
--
类别编号
119
new_date
datetime
default
getdate
(),
--
添加时间
120
new_click
int
default
0
,
--
点击数
121
admin_id
int
not
null
--
添加管理员编号
122
)
123![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
124
--
8、数据统计
125
--
8.1、记录网站的数据访问量
126
create
table
visitLog
127
(
128
log_id
int
identity
primary
key
,
129
log_IP
varchar
(
15
)
not
null
,
--
访问者IP
130
log_Browser
varchar
(
20
)
not
null
,
--
浏览器类型
131
log_time
datetime
default
getdate
(),
--
访问时间
132
log_OS
varchar
(
20
)
not
null
,
--
操作系统
133
log_url
varchar
(
200
)
not
null
,
--
所访问的页面
134
log_from
varchar
(
200
)
--
上一页面
135
)
136![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
137
--
二、表格功能的实现
138
--
1、表格管理 tableName
139
create
table
tableName
140
(
141
table_id
int
identity
primary
key
,
142
table_name
varchar
(
200
)
not
null
,
--
表格名
143
table_text
text
,
--
表格说明
144
[
user_id
]
int
not
null
,
--
同userInfo表相对应,创建者编号
145
table_stat
datetime
not
null
,
--
表格使用的起始时间
146
table_end
datetime
not
null
--
表格使用的结束时间
147
)
148![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
149
--
2、字段类型 fieldType
150
create
table
fieldType
151
(
152
type_id
int
identity
primary
key
,
153
type_name
varchar
(
200
)
not
null
,
--
类型名称
154
type_reg
varchar
(
200
)
not
null
,
--
验证正则表达式
155
type_explain
varchar
(
200
)
--
举例说明格式
156
)
157![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
158
--
3、表格中的各个要素 tableBasic
159
create
table
tableBasic
160
(
161
basic_id
int
identity
primary
key
,
162
table_id
int
not
null
,
--
表格编号,同tableName表相对应
163
basic_name
varchar
(
200
)
not
null
,
--
要素的名称
164
type_id
int
not
null
,
--
数据类型,同fieldType表相对应
165
basic_order
int
default
0
,
--
排序,从高到低,默认为1,为最末
166
basic_explain
varchar
(
200
)
--
要素说明
167
)
168![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
169
--
4、提交数据用户表 theUse
170
create
table
theUse
171
(
172
the_id
int
identity
primary
key
,
--
用户编号
173
table_id
int
not
null
,
--
表格编号
174
the_date
datetime
default
getdate
(),
--
添加时间
175
the_IP
varchar
(
15
)
not
null
--
提交者IP
176
)
177![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
178
--
5、用户提交的数据管理 tableData
179
create
table
tableData
180
(
181
data_id
int
identity
primary
key
,
182
the_id
int
not
null
,
--
提交数据的用户编号
183
basic_id
int
not
null
,
--
要素编号
184
data_value
varchar
(
2000
)
--
该要素的值
185
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
130
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
131
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
132
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
133
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
134
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
135
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
136
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
137
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
138
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
139
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
140
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
141
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
142
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
143
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
144
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
145
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
146
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
147
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
148
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
149
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
150
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
151
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
152
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
153
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
154
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
155
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
156
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
157
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
158
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
159
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
160
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
161
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
162
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
163
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
164
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
165
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
166
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
167
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
168
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
169
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
170
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
171
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
172
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
173
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
174
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
175
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
176
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
177
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
178
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
179
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
180
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
181
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
182
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
183
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
184
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
185
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)