1、安装mysql
这一系列,请参考我之前的博文
2、创建表的语法
实际上创建的方式有很多种,但是我想采用脚本创建的方式去常创建,这样的灵活性更高,而不用重复的操作
2.0 数据库表命名规则
根据之前设计的表分别对表进行命名:
- experimen_info 中文:实验信息表
- exp_id (samllint, 0-65535)
- exp_manager (char ,4bytes)
- start_time (datatime,now(6)产生微秒数据,)
- end_time(datatime,now(6)产生微秒数据,)
- exp_desciption (varchar, 变长字符串)
- experimen_info 中文:实验信息表
- v_info_register 中文:变量信息注册表
- v_id (smallint, 2bytes, 0-655535)
- subsys_id(smallint, 2bytes, 0-655535)
- register_id(smallint,2bytes,0-65535)
- v_name(char, 实际上,最好是给定一个合适结构,后面添加的变量都要按照对应格式进行)
- ip_port(ip_port, char,例如:192.168.127.5:5556)
- created_time(datetime, 8 bytes)
- created_manager(char, 创建管理人,人名,char, 4bytes)
- v_type(float,int,或者什么样,防止对数据库造成迅速变大)
- v_descrption (varchar)
- v_status(是否在线,这个变量是否正在监控之中)
- v_info_register 中文:变量信息注册表
- v_list_formonitoring 中文:变量监控启动表
- v_id (smallint, 2bytes, 0-655535)
- subsys_id(smallint, 2bytes, 0-655535)
- register_id(smallint,2bytes,0-65535)
- ip_port(ip_port, char,例如:192.168.127.5:5556)
- v_register(实际寄存器是多少)
- v_list_formonitoring 中文:变量监控启动表
- v_data_monitor 中文:变量信息存储表
- exp_id (samllint, 0-65535)
- subsys_id(smallint, 2bytes, 0-655535)
- register_id(smallint,2bytes,0-65535)
- v_data(float,4bytes)
- v_data_time(datetime,length 6/8 now(6) )
- v_data_monitor 中文:变量信息存储表
- subsys_info 中文:子系统信息表
- subsys_id(samllint, 0-65535)
- subsys_name(子系统名称)
- subsys_ip(ip, char,例如:192.168.127.5:5556)
- subsys_status( 子系统是否连接在线(tcp ip))
- description(简述本系统的内容)
- subsys_info 中文:子系统信息表
2.1 创建表
创建表前, 应当创建一个数据库,用来存表:
CREATE DATABASE 数据库名;
2.2.1 实验信息表
- experimen_info
- exp_id (samllint, 0-65535)
- exp_managername (char ,4bytes)
- start_time (datatime,now(6)产生微秒数据,)
- end_time(datatime,now(6)产生微秒数据,)
- exp_desciption (varchar, 变长字符串)
CREATE TABLE IF NOT EXISTS `experiment_info`(
`exp_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`exp_magagername` VARCHAR(12) NOT NULL,
`start_time` DATETIME(6) NOT NULL,
`end_time` DATETIME(6),
`exp_description` VARCHAR(100),
PRIMARY KEY ( `exp_id` )
);
建立结果验证:
2.2.2 变量信息注册表
- v_info_register 中文:变量信息注册表
- #v_id (smallint, 2bytes, 0-655535)
- subsys_id(smallint, 2bytes, 0-655535)
- register_id(smallint,2bytes,0-65535)
- v_name(char, 实际上,最好是给定一个合适结构,后面添加的变量都要按照对应格式进行)
- ip_port(ip_port, char,例如:192.168.127.5:5556)
- created_time(datetime, 8 bytes)
- created_manager(char, 创建管理人,人名,char, 4bytes)
- v_type(float,int,或者什么样,防止对数据库造成迅速变大)
- v_descrption (varchar)
- v_status(是否在线,这个变量是否正在监控之中)
CREATE TABLE IF NOT EXISTS `v_info_register`(
# `v_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`subsys_id` SMALLINT UNSIGNED,
`register_id` SMALLINT UNSIGNED,
`v_name` CHAR(100) NOT NULL,
`ip_port` CHAR(100) NOT NULL ,
`created_time` DATETIME(6) NOT NULL,
`created_manager` CHAR(16) NOT NULL,
`v_type` CHAR(20) NOT NULL,
`v_description` VARCHAR(300) NOT NULL,
`v_status` CHAR(10) NOT NULL,
PRIMARY KEY (subsys_id,register_id)
);
建立验证结果:
2.2.3 变量监控启动表
- v_list_formonitoring 中文:变量监控启动表
- subsys_id(smallint, 2bytes, 0-655535)
- register_id(smallint,2bytes,0-65535)
- ip_port(ip_port, char,例如:192.168.127.5:5556)
- v_register(实际寄存器是多少)
CREATE TABLE IF NOT EXISTS `v_formonitoring`(
# `v_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`subsys_id` SMALLINT UNSIGNED,
`register_id` SMALLINT UNSIGNED,
`ip_port` CHAR(100) NOT NULL ,
`v_register` SMALLINT UNSIGNED,
PRIMARY KEY ( subsys_id,register_id)
);
实验结果验证:
2.2.4 变量信息存储表
- v_data_monitor 中文:变量信息存储表
- subsys_id(smallint, 2bytes, 0-655535)
- register_id(smallint,2bytes,0-65535)
- exp_id (samllint, 0-65535)
- v_data(float,4bytes)
- v_data_time(datetime,length 6/8 now(6) )
CREATE TABLE IF NOT EXISTS `v_data_monitor`(
`subsys_id` SMALLINT UNSIGNED,
`register_id` SMALLINT UNSIGNED,
`exp_id` SMALLINT UNSIGNED NOT NULL,
`v_data` FLOAT NOT NULL,
`v_data_time` DATETIME(6) NOT NULL,
PRIMARY KEY ( subsys_id,register_id,exp_id, v_data_time)
);
实验结果验证:
2.2.5 子系统信息表
- subsys_info 中文:子系统信息表
- description(简述本系统的内容)
- subsys_status( 子系统是否连接在线(tcp ip))
- subsys_ip(ip, char,例如:192.168.127.5:5556)
- subsys_name(子系统名称)
- subsys_id(samllint, 0-65535)
CREATE TABLE IF NOT EXISTS `subsys_info`(
`subsys_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`ip` VARCHAR(100) NOT NULL,
`status` CHAR(10) NOT NULL,
`description` VARCHAR(300) NOT NULL,
PRIMARY KEY ( `subsys_id` )
);
结果验证:
代码汇总:
CREATE TABLE IF NOT EXISTS `experiment_info`(
`exp_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`exp_magagername` VARCHAR(12) NOT NULL,
`start_time` DATETIME(6) NOT NULL,
`end_time` DATETIME(6),
`exp_description` VARCHAR(100),
PRIMARY KEY ( `exp_id` )
);
CREATE TABLE IF NOT EXISTS `v_info_register`(
# `v_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`subsys_id` SMALLINT UNSIGNED,
`register_id` SMALLINT UNSIGNED,
`v_name` CHAR(100) NOT NULL,
`ip_port` CHAR(100) NOT NULL ,
`created_time` DATETIME(6) NOT NULL,
`created_manager` CHAR(16) NOT NULL,
`v_type` CHAR(20) NOT NULL,
`v_description` VARCHAR(300) NOT NULL,
`v_status` CHAR(10) NOT NULL,
PRIMARY KEY (subsys_id,register_id)
);
CREATE TABLE IF NOT EXISTS `v_formonitoring`(
# `v_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`subsys_id` SMALLINT UNSIGNED,
`register_id` SMALLINT UNSIGNED,
`ip_port` CHAR(100) NOT NULL ,
`v_register` SMALLINT UNSIGNED,
PRIMARY KEY ( subsys_id,register_id)
);
CREATE TABLE IF NOT EXISTS `v_data_monitor`(
`subsys_id` SMALLINT UNSIGNED,
`register_id` SMALLINT UNSIGNED,
`exp_id` SMALLINT UNSIGNED NOT NULL,
`v_data` FLOAT NOT NULL,
`v_data_time` DATETIME(6) NOT NULL,
PRIMARY KEY ( subsys_id,register_id,exp_id, v_data_time)
);
CREATE TABLE IF NOT EXISTS `subsys_info`(
`subsys_id` SMALLINT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`ip` VARCHAR(100) NOT NULL,
`status` CHAR(10) NOT NULL,
`description` VARCHAR(300) NOT NULL,
PRIMARY KEY ( `subsys_id` )
);