国产精品久久久久久妇女6080 ,亚洲精品成人无码一区二区三区

MySQL操作程序二(修改數(shù)據(jù)庫)

main_type1的修改操作

表格main_type1的gname_id,maker_id改成INT(原是TEXT)
取得gname_id,maker_id
只保留數(shù)值,沒有數(shù)值的全置零
程序執(zhí)行前(main_type1_1.txt)
main_type1.pl
程序執(zhí)行后(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)
用程序修改數(shù)據(jù)gname_id,maker_id
調(diào)查main_type1生成新數(shù)據(jù)時如何操作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重復(fù)的操作

$t{enq1_id} = $t{q}->param("enq1_id"); $t{our1_new} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_id}"); # 取得現(xiàn)有所有quo2的enq1id數(shù)據(jù),如果有一樣的不允許切換 # enq1和quo2必須是一對一關(guān)系 # 取出所有的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}"); }

刪除表格內(nèi)容的一些操作

顯示表格hull_no的第309行到362行的內(nèi)容 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的操作(改名和改數(shù)據(jù)定義)

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)

返回
廊坊市| 长兴县| 霍州市| 聂荣县| 东光县| 临潭县| 融水| 且末县| 商河县| 通海县| 天长市| 亚东县| 绥阳县| 屏边| 永丰县| 丰原市| 余江县| 天镇县| 光山县| 巧家县| 土默特左旗| 曲沃县| 仪征市| 阿鲁科尔沁旗| 正阳县| 平果县| 安岳县| 游戏| 沙洋县| 普兰县| 潮安县| 山东| 敖汉旗| 繁昌县| 牡丹江市| 临武县| 合江县| 潮安县| 平江县| 东方市| 易门县|