eTable
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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)