操作数据库一个零件表的程序/复写整个数据库/检查TYPE序列表格
返回
- 没有a004311,需要生成
- 把所有的tables写入tables.txt
<![CDATA[
复写整个数据库===把所有的tables写入tables.txt
c:\database\sql>mysql -u cbuser -p cookbook > tables.txt
Enter password: ******
show tables;
quit
把msc数据库装入msc.sql文件中(注意使用mysqldump)
>mysqldump -u mscuser -p msc > msc.sql
把msc数据库装入XP机器中
-------------------------------------
C:\database\txt>mysql cookbook -u root -p < msc.sql
Enter password: ****
msc.sql部分内容
---------------------------------------
-- MySQL dump 10.11
--
-- Host: localhost Database: msc
-- ------------------------------------------------------
-- Server version 5.0.45-community-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `a000001`
--
DROP TABLE IF EXISTS `a000001`;
CREATE TABLE `a000001` (
`id` int(11) NOT NULL auto_increment,
`name` text,
`code` text,
`dwg_id` int(11) default NULL,
`Nuid` int(11) default NULL,
`weight` int(11) default NULL,
`price1` text,
`price2` text,
`memo` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `a000001`
--
LOCK TABLES `a000001` WRITE;
/*!40000 ALTER TABLE `a000001` DISABLE KEYS */;
INSERT INTO `a000001` VALUES (1,'INSULATION RESISTANCE METER (IRM)==FOR 110V FEEDER PANEL 0-5 MEGA OHM==MKR: TOYO KEIKI,NO-NK 22204==115mm x 95mm','VIEW-OV11',2,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(2,'BRUSH HOLDER','5',3,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(3,'BRUSH','6',3,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(4,'PART OF ELECTRIC CONDUCTING','9',3,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(5,'VISCOSENSE SENSOR ASS\'Y==D/N:0820-1223-4','0279-0088',4,2,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(6,'Tools & parts Cabinets==L 1500 x W 750 X H 720 mm, ref. impa code: 613854','X1',1,2,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',''),(7,'AC AMMETER, LS-80NAA, 50/5A,0-50-150A','X2',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(8,'AC AMMETER, LS-80NAA, 200/5A,0-200-600A','X3',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(9,'AC AMMETER, LS-80NAA, 300/5A,0-300-900A','X4',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(10,'AC AMMETER, LS-80NAA, 150/5A,0-150-450A','X5',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(11,'DC AMMETER, LS-80NRI, 4-20mA/ 0-30MPa','X6',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(12,'DC AMMETER, LS-80NRI, 4-20mA/ 0-400A','X7',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(13,'DC INDICATOR, FM-215SN, 4-20mA/0-3.0rpm','X8',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(14,'DC INDICATOR, FM-215SN, 4-20mA/0-100m','X9',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(15,'DETECTOR HEAD(IONIZATION)100x73, NEW TYPE FDS-512(EXPOSED)','FDS-511',1,1,1,'26000=100=1=2008-06-05=8060603=206=139==26000=100=1=2008-06-05=8091816=206=1451','30500=125=1=2008-06-06=3',''),(16,'DETECTOR HEAD(THERMAL)105x45','FDL-511',1,1,1,'8000=100=1=2008-06-05=8060603=206=139','10000=125=1=2008-06-06=3',NULL),(17,'SEAT RING','150A',5,1,1,'14300=100=1=2008-06-17=8061701=239=240','17160=120=1=2008-06-17=1',NULL),(18,'SEAT RING','125A',5,1,1,'13200=100=1=2008-06-17=8061701=239=240','15840=120=1=2008-06-17=1',NULL),(19,'TIMER RELAY 24-48VAC','H3CR-A',1,1,1,'2850=100=1=2008-07-16=8071528=20=577','3420=120=1=2008-07-16=3',NULL),(20,'TIMER RELAY 100-240VAC','H3CR-A8E-315',1,1,1,'3950=100=1=2008-07-16=8071528=20=577','4740=120=1=2008-07-16=3',NULL),(21,'MAGNETIC CONTACTOR 440V','SC-N5',1,1,1,'10600=100=1=2008-07-16=8071528=20=577','12720=120=1=2008-07-16=3',''),(22,'MAGNETIC CONTACTOR 440V','SC-N7',1,1,1,'23500=100=1=2008-07-16=8071528=20=577','28200=120=1=2008-07-16=3',''),(23,'\'MAGNETIC SWITCH FOR AIR COND.==TYPE: CKL 65HT HP 11\'','220V 65A',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(24,'\'MAGNETIC CONTACTOR FOR REFRIGERATOR\'','SA-12',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(25,'\'RELAY\'','8831',1,1,1,'0=100=1=0000-00-00=0000000=1=1','0=115=1=0000-00-00=1',NULL),(26,'\'MAGNETIC CONTACTOR, S-2xN125\'','AC440V',1,1,1,'38800=100=1=2008-08-18=8081801=20=1048','46560=120=1=2008-08-18=11',NULL),(27,'\'ANODE TYPE 150A WITH 12 FIXING STUDS,GASKET,ISOLATING HEAD & O-RINGS==WITH EPOXY FILLER (BI-COMPONENT)50ltr','P-C-2320-1',1,2,1,'4794=100=3=2010-03-02=9080117=223=5502','5752.80=120=3=2009-08-04=1',''),(28,'\'ISOLATING SLEEVE\'','P-C-2320-5',1,1,1,'56=100=3=2010-03-02=9080117=223=5502','67.20=120=3=2009-08-04=1',NULL),(29,'\'ISOLATING HEAD\'','P-C-2320-16',1,1,1,'0=100=3=2010-03-02=9080117=223=5502','0.00=120=3=2009-08-04=1',NULL),(30,'\'O-RING\'','P-C-2320-17',1,1,1,'11.2=100=3=2010-03-02=9080117=223=5502','13.44=120=3=2009-08-04=1',NULL),(31,'\'REFERENCE CELL\'','P-C-2442-3',1,1,1,'615=100=3=2010-03-02=9080117=223=5502','738.00=120=3=2009-08-04=1',NULL),(32,'\'HEAD \'','P-C-2442-4',1,1,1,'0=100=3=2010-03-02=9080117=223=5502','0.00=120=3=2009-08-04=1',NULL),(33,'\'O-RING\'','P-C-2442-16',1,1,1,'11.2=100=3=2010-03-02=9080117=223=5502','13.44=120=3=2009-08-04=1',NULL),(34,'\'BRUSH\'','P-C-8725-3',1,1,1,'324=100=3=2010-03-02=9080117=223=5502','388.80=120=3=2009-08-04=1',NULL),(35,'\'CU ANODE DIA 120mm(DIA:120mm,L:540mm)\'','CU',8,1,1,'2295=100=3=2009-08-04=9080118=223=5503','2754.00=120=3=2009-08-04=1',''),(36,'\'AI ANODE DIA 120mm(DIA:120mm,L:540mm)\'','AI',8,1,1,'771=100=3=2009-08-04=9080118=223=5503','925.20=120=3=2009-08-04=1',''),(37,'\'CU ANODE DIA 80mm(DIA:80mm,L:610mm)\'','CU',9,1,1,'1531=100=3=2009-08-04=9080118=223=5503','1837.20=120=3=2009-08-04=1',''),(38,'\'AI ANODE DIA 80mm(DIA:80mm,L:610mm)\'','AI',9,1,1,'448=100=3=2009-08-04=9080118=223=5503','537.60=120=3=2009-08-04=1',''),(39,'COPPER ANODE DIA 80mm x L610mm ','02',10,1,1,'1410=100=3=2012-03-27=C032304=865=22167','1692.00=120=3=2012-03-27=434',NULL),(40,'ALUMINIUM ANODE DIA 80mm x L610mm ','02',10,1,1,'415=100=3=2012-03-27=C032304=865=22167','498.00=120=3=2012-03-27=434',NULL),(41,'1x6mm2 CABLE==2.5 FOR EACH ANODE','10',9,9,1,'8=100=3=2012-03-27=C032304=865=22167','9.60=120=3=2012-03-27=434',NULL);
/*!40000 ALTER TABLE `a000001` ENABLE KEYS */;
UNLOCK TABLES;
]]>
make_ptable1.pl
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# 输入主机序号,形成零件表名
print "Please input parts table name(Enginee.NO)=";
chop($t{input}=<STDIN>);
$t{inputf} = sprintf("%06d",$t{input});
$t{table1} = 'a' . $t{inputf};
# 连接数据库
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 删除一个零件表
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{table1} . ';';
$t{dbh}->do($t{sql});
# 创建一个零件表
$t{sql} = 'CREATE TABLE ' . $t{table1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code TEXT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{dbh}->do($t{sql});
$t{sth} = $t{dbh}->prepare ("SHOW columns FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array )
{
print "@rec\n";
}
$t{sth}->finish;
# ----------注意以下部分删除
# 输入enq1序号
print "Please input ID of enq1=";
chop($t{enqid}=<STDIN>);
$t{sql} = 'UPDATE enq1 SET partsid = "C" WHERE id = "';
$t{sql} .= $t{enqid} . '"';
$t{dbh}->do($t{sql});
$t{sql} = 'UPDATE enq1 SET QTY = "C" WHERE id = "';
$t{sql} .= $t{enqid} . '"';
$t{dbh}->do($t{sql});
$t{dbh}->disconnect;
__END__;
]]>
返回