欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-24 12:35:01
null值的判断 $t{type1id} = $$pref{dbh}->selectrow_array("select type1id from enq1 where...
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