perl 学习资料整理篇第1/4页

null值的判断
$t{type1id} = $$pref{dbh}->selectrow_array("select type1id from enq1 where id =
3");
if ( $t{type1id} == 0 ) {
print "type1id is null\n";
}
==>不是数值项的话,这个语句有问题。数值项专用。
if ( length($t{type1id}) == 0 ) {
print "type1id is null\n";
}
==>如果null的话,这个语句有问题
如果@rec含有null的话,下面的操作要出错误信息
$t{line1} = join(' ',@rec);

($t{old1},$t{new1p},$t{new1q}) = $self->dbh->selectrow_array("select
type1id,partsid,qty from enq1 where id = $t{enq1_id}");
91==> if ( $t{old1} == 0 ) {
--------------------------------------------------
[error] [client 127.0.0.1] use of uninitialized value in numeric eq (==) at
./pro/mscenq1.pl line 91, <config> line 11.,
--------------------------------------------------
如何判断一个项目的值是否是null(未解决)
解决!第一次insert时,放一个常数(比如"b")
起源==>
637==> $t{nu1} = $self->dbh->selectrow_array("select parts_unit from parts_nu
where id = $t{nuid1}");
--------------------------------------------------
[wed may 14 17:27:51 2008] [error] [client 127.0.0.1] dbd::mysql::db
selectrow_array failed: you have an error in your sql syntax; check the manual
that corresponds to your mysql server version for the right syntax to use near
'' at line 1 at ./pro/mscenq1.pl line 637, <config> line 11., referer:
--------------------------------------------------
要考虑$t{nuid1}不存在的情况

考虑id=c的情况
591==>
@{ $t{p1} } = $self->dbh->selectrow_array("select * from $t{ptable}
where id = $t{pid1}");
--------------------------------------------------
[error] [client 127.0.0.1] dbd::mysql::db selectrow_array failed: unknown
column 'c' in 'where clause' at ./pro/mscenq1.pl line 591, <config> line 11.,
referer:
--------------------------------------------------
要考虑$t{pid1}='c'的情况
if ( $#{ $t{pid_list} } == 0 && $t{pid_list}[0] eq 'c' ) {
next;
}
copy一个项目的subroutine
use strict;
use dbi;
# 连接数据库
my(%t,$n,@fld,@rec,$pref);
print "this is test3.pl.\n";
# 连接数据库
$$pref{dsn} = "dbi:mysql:host=localhost;database=cookbook";
$$pref{dbh} = dbi->connect($$pref{dsn}, "cbuser", "cbpass") or die "cannot
connect to server\n";
$$pref{dbh}->do("set names utf8");
if(!$$pref{dbh}){
print "sql read error!\n";
exit;
}
$$pref{table} = 'enq2';
$$pref{oldid} = 4;
($pref) = copy_one($pref);
# 关闭数据库
$$pref{dbh}->disconnect;
# copy一个项目
sub copy_one {
my($pref) = @_;
my(%t,@rec,$n);

# 取出columns
$t{sth} = $$pref{dbh}->prepare("show columns from $$pref{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns} },$rec[0]);
}
$t{sth}->finish;
# 取出数据(同时记住不是null的项目)
@{ $t{one} } = $$pref{dbh}->selectrow_array("select * from $$pref{table}
where id = $$pref{oldid}");

for $n ( 1 .. $#{ $t{one} } ) {
$t{name} = $t{columns}[$n];
$t{value} = $t{one}[$n];
if ( $t{value} ) {
$t{value} = '"' . $t{value} . '"';
push(@{ $t{names} },$t{name});
push(@{ $t{values} },$t{value});
}
}
$t{name1} = join(',',@{ $t{names} });
$t{value1} = join(',',@{ $t{values} });

# 插入新项目
$t{sql} = 'insert into ' . $$pref{table} . '(';
$t{sql} .= $t{name1} . ') values(';
$t{sql} .= $t{value1} . ')';

$t{do} = $$pref{dbh}->do($t{sql});

# print "do=$t{do}\n";
return($pref);
}
# 可能mysql存在很简单的命令执行上面的操作。已经做过的程序就放在这儿了。

--------------------------------------------------------------------------------
mysql操作程序二
返回
--------------------------------------------------------------------------------
不许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) 

1