Mysql 实战练习

建表

建表switch_line_port并通过csv文件向该表导入数据

CREATE TABLE `switch_line_port` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '索引ID',
  `switch_name` varchar(80) NOT NULL DEFAULT '' COMMENT '交换机名称',
  `line` varchar(80) NOT NULL DEFAULT '' COMMENT '专线名称',
  `port_name` varchar(80) NOT NULL DEFAULT '' COMMENT '网口名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `switch_line_port` (`switch_name`,`line`,`port_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT 'pop点交换机下的专线，专线下的网口';

LOAD DATA INFILE 'D:\\job\\sws_line_port.csv' 
INTO TABLE switch_line_port
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

建表device并通过csv文件向该表导入数据

CREATE TABLE `device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `address` varchar(16) DEFAULT NULL,
  `description` varchar(64) DEFAULT NULL,
  `vendor` varchar(64) DEFAULT NULL,
  `flowVersion` varchar(32) DEFAULT NULL,
  `sample` bigint(10) DEFAULT NULL,
  `snmpAddress` varchar(16) DEFAULT NULL,
  `snmpVersion` varchar(8) DEFAULT NULL,
  `snmpCommunity` varchar(32) DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `createtime` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8；

LOAD DATA INFILE 'D:\\job\\device.csv' 
INTO TABLE device
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

建表interface并通过csv文件向该表导入数据

CREATE TABLE `interface` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `port` int(11) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `ip` varchar(16) DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `direction` tinyint(1) DEFAULT NULL,
  `monitor` tinyint(1) DEFAULT NULL,
  `bandwith` bigint(40) DEFAULT '0',
  `device_id` bigint(20) DEFAULT NULL,
  `alias` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_int` (`port`,`device_id`),
  KEY `FK_ID` (`device_id`),
  CONSTRAINT `FK_ID` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=348713348 DEFAULT CHARSET=utf8

联表查询：

select port from interface, device where device.id=interface.device_id and device.name='dev_name' 
and interface.name in (select port_name from switch_line_port where switch_name='dev_name');
等价于
select port from interface, device, switch_line_port where switch_line_port.port_name = interface.name and device.id=interface.device_id and device.name = 'dev_name' # switch_line_port中也有switch_name，但是这里必须要用device.name，否则联表条件device.id=interface.device_id 无效

其中

select port from interface, device where device.id=interface.device_id and device.name='dev_name' # 表示查出的网口要在名称为dev_name的设备上

表示查出interface表中 device_name 为 dev_name 的所有网口, 由于 interface表中没有device_name 字段，所以要联合device表查询，用条件 where device.id=interface.device_id 把 dev_name转换成 interface表中的device_id

其中 条件

 interface.name in (select port_name from switch_line_port where switch_name='dev_name')

表示 在 switch_line_port表中switch_name为 dev_name的交换机中的所有网口（某个线路下）