MySQL操作程序二(修改数据库)

MySQL操作程序二(修改数据库)

main_type1的修改操作

表格main_type1的gname_id,maker_id改成INT(原是TEXT)
取得gname_id,maker_id
只保留数值,没有数值的全置零
程序执行前(main_type1_1.txt)
main_type1.pl
程序执行后(main_type1_2.txt)
改成INT后(main_type1_3.txt)
Main Marine Manufactureers管理画面(msc291.htm),输入NO INPUT(id=1),把1号改到419号(XXXX)=>AALBORG INDUSTRIES KK
Main Ship Equipments管理画面(msc281.htm),输入NO INPUT(id=1)
用程序修改数据gname_id,maker_id
调查main_type1生成新数据时如何操作gname_id,maker_id,需要的话修改操作程序

TEXT=>INT,手改 mysql> ALTER TABLE main_type1 CHANGE gname_id gname_id INT; Query OK, 4118 rows affected (1.24 sec) Records: 4118 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE main_type1 CHANGE maker_id maker_id INT; Query OK, 4118 rows affected (0.72 sec) Records: 4118 Duplicates: 0 Warnings: 0 mysql> show columns from main_type1; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | series | text | YES | | NULL | | | gname_id | int(11) | YES | | NULL | | | maker_id | int(11) | YES | | NULL | | | GR | text | YES | | NULL | | | DWG | text | YES | | NULL | | | memo | text | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 8 rows in set (0.02 sec) mysql> ALTER TABLE main_type1 CHANGE gname_id gname_id INT; ERROR 1265 (01000): Data truncated for column 'gname_id' at row 279 mysql> select * from main_type1 where id = 279\G *************************** 1. row *************************** id: 279 name: SJ 30G series: XXXSERIES gname_id: 603066-0.5.7=6031421=6031548=1140786 maker_id: 256 main_type1,有许多TYPE用maker_id=1 id,gname_id,maker_id 159===>211===>1 322===>22===>1 761===>331===>1 1258===>22===>1 1265===>300===>1 1390===>22===>1 1414===>571===>1 1808===>22===>1 2382===>40===>1 3620===>22===>1 3863===>1236===>1 3874===>22===>1 3933===>169===>1 294===>1===>45 463===>1===>103 1231===>1===>612 2185===>1===>92 2480===>1===>60 2582===>1===>30 2656===>1===>564 2658===>1===>113 2781===>1===>102 2863===>1===>612 2906===>1===>148 3166===>1===>103 3254===>1===>153 3880===>1===>66

不许OURREF重复的操作

$t{enq1_id} = $t{q}->param("enq1_id"); $t{our1_new} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_id}"); # 取得现有所有quo2的enq1id数据,如果有一样的不允许切换 # enq1和quo2必须是一对一关系 # 取出所有的OURREF $t{sth} = $self->dbh->prepare("SELECT enq1id FROM quo2"); $t{sth}->execute; while ( @rec = $t{sth}->fetchrow_array ) { $t{our1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $rec[0]"); push(@{ $t{our1s} },$t{our1}); } $t{sth}->finish; $t{our1_old} = join(' ',@{ $t{our1s} }); if ( $t{our1_old} !~ /$t{our1_new}/ ) { $t{sql} = 'UPDATE quo2 SET enq1id ="'; $t{sql} .= $t{enq1_id} . '" WHERE id = "'; $t{sql} .= $t{quo2_id} . '"'; $t{DO} = $self->dbh->do("$t{sql}"); }

删除表格内容的一些操作

显示表格hull_no的第309行到362行的内容 mysql> SELECT * from hull_no WHERE id >= 309 AND id <= 362; 删除表格hull_no的第309行到362行的HULL_NO mysql> UPDATE hull_no SET HULL_NO = "" WHERE id >= 309 AND id <= 362; Query OK, 54 rows affected (0.16 sec) Rows matched: 54 Changed: 54 Warnings: 0 删除表格hull_no的第309行到362行的name mysql> UPDATE hull_no SET name = "" WHERE id >= 309 AND id <= 362; Query OK, 54 rows affected (0.01 sec) Rows matched: 54 Changed: 54 Warnings: 0

表格删除一行操作

mysql> show columns from quo2; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | enq1id | int(11) | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | percent0 | int(11) | YES | | NULL | | | percent | text | YES | | NULL | | | price | text | YES | | NULL | | | total | int(11) | YES | | NULL | | | memo | text | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+ 12 rows in set (0.08 sec) mysql> ALTER TABLE quo2 DROP enq1id; Query OK, 6 rows affected (0.27 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> show columns from quo2; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | percent0 | int(11) | YES | | NULL | | | percent | text | YES | | NULL | | | price | text | YES | | NULL | | | total | int(11) | YES | | NULL | | | memo | text | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+ 11 rows in set (0.02 sec) mysql> show columns from order1; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | orderno | text | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | price | text | YES | | NULL | | | total | text | YES | | NULL | | | memo | text | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+ 10 rows in set (0.02 sec) mysql> ALTER TABLE order1 DROP price; Query OK, 10 rows affected (0.24 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE order1 DROP total; Query OK, 10 rows affected (0.17 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> show columns from order1; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | orderno | text | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | memo | text | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+ 8 rows in set (0.01 sec)

表格增加一行操作

mysql> show columns from enq2; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | enq1id | int(11) | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | makerid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | type1id | text | YES | | NULL | | | partsid | text | YES | | NULL | | | QTY | text | YES | | NULL | | | memo | text | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+ 12 rows in set (0.06 sec) mysql> ALTER TABLE enq2 ADD LANGUAGEid INT AFTER enq1id; Query OK, 1 row affected (0.45 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show columns from enq2; +------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | enq1id | int(11) | YES | | NULL | | | LANGUAGEid | int(11) | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | makerid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | type1id | text | YES | | NULL | | | partsid | text | YES | | NULL | | | QTY | text | YES | | NULL | | | memo | text | YES | | NULL | | +------------+---------+------+-----+---------+----------------+ 13 rows in set (0.00 sec) mysql> show columns from quo1; +----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | enq2id | int(11) | YES | | NULL | | | makerref | text | YES | | NULL | | | memo | text | YES | | NULL | | +----------+---------+------+-----+---------+----------------+ 5 rows in set (0.30 sec) mysql> ALTER TABLE quo1 ADD price TEXT AFTER makerref; Query OK, 2 rows affected (0.67 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> show columns from quo1; +----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | enq2id | int(11) | YES | | NULL | | | makerref | text | YES | | NULL | | | price | text | YES | | NULL | | | memo | text | YES | | NULL | | +----------+---------+------+-----+---------+----------------+ 6 rows in set (0.02 sec)

修改一个Column的操作(改名和改数据定义)

mysql> show columns from order1; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | quo2id | int(11) | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | price | text | YES | | NULL | | | total | text | YES | | NULL | | | memo | text | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+ 10 rows in set (0.16 sec) mysql> ALTER TABLE order1 CHANGE quo2id orderno TEXT; Query OK, 6 rows affected (0.56 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> show columns from order1; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | orderno | text | YES | | NULL | | | ORIGINid | int(11) | YES | | NULL | | | PRICEid | int(11) | YES | | NULL | | | PAYMENTid | int(11) | YES | | NULL | | | DELIVERY | text | YES | | NULL | | | price | text | YES | | NULL | | | total | text | YES | | NULL | | | memo | text | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+ 10 rows in set (0.02 sec)