createdb.sql

dms_demo.zip 好东西 大家一起分享

--建立库脚本：该脚本比较笨，请参考存储过程版本
--createdb.sql

--删除数据库
drop database  if exists netctoss;

--创建数据库
create database if not exists netctoss default character set gb2312;

--切换数据库
use netctoss;

--创建用户表
create table users  ( 
	user_id int(10),  -- 用户id
	user_type int(10),  -- 用户类型
	user_name varchar(32),  -- 用户名
	login_name varchar(20) unique,  -- 登陆名
	login_password varchar(32),  -- 登陆密码
	primary key(user_id)
);

--初始化用户表
insert into users values(1,1,'admin','admin','admin');
insert into users values(2,2,'tarena','tarena','tarena');

--创建采集原始记录表
create table details_1 (
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logindate	timestamp,	-- 登录时间
	logoutdate	timestamp,	-- 退出时间	
	labip		varchar(24),	-- 实验室ip
	duration	long		-- 登录时长
);


create table details_2 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_3 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_4 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_5 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_6 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_7 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_8(
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_9 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_10 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_11 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_12 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);


create table details_13 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_14 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_15 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_16 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_17 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_18(
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_19(
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_20 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_21 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_22 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_23(
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);


create table details_24 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_25 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_26 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_27 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);

create table details_28 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_29 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_30(
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);
create table details_31 (
	loginname	varchar(20) ,	
	loginip		varchar(24),	 
	logindate	timestamp,		 
	logoutdate	timestamp,		
	labip		varchar(24),	
	duration	long	
);


--创建采集整合表[天表]
create table detaildays_1(
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logouttime	timestamp,	-- 登出时间
	labip		varchar(24),	-- 实验室ip
	duration	long,		-- 登录时长
	primary key (loginname, labip, logouttime,loginip)
);

create table detaildays_2(
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logouttime	timestamp,	-- 登出时间
	labip		varchar(24),	-- 实验室ip
	duration	long,		-- 登录时长
	primary key (loginname, labip, logouttime)
);
create table detaildays_3(
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logouttime	timestamp,	-- 登出时间
	labip		varchar(24),	-- 实验室ip
	duration	long,		-- 登录时长
	primary key (loginname, labip, logouttime)
);
create table detaildays_4(
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logouttime	timestamp,	-- 登出时间
	labip		varchar(24),	-- 实验室ip
	duration	long,		-- 登录时长
	primary key (loginname, labip, logouttime)
);
create table detaildays_5(
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logouttime	timestamp,	-- 登出时间
	labip		varchar(24),	-- 实验室ip
	duration	long,		-- 登录时长
	primary key (loginname, labip, logouttime)
);
create table detaildays_6(
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logouttime	timestamp,	-- 登出时间
	labip		varchar(24),	-- 实验室ip
	duration	long,		-- 登录时长
	primary key (loginname, labip, logouttime)
);
create table detaildays_7(
	loginname	varchar(20) ,	-- 登录名
	loginip		varchar(24),	-- 登录ip
	logouttime	timestamp,	-- 登出时间
	labip		varchar(24),	-- 实验室ip
	duration	long,		-- 登录时长
	primary key (loginname, labip, logouttime)
);
create table detaildays_8(