📄 1831.html
字号:
# 或者<br>
print "$row->[0] $row[1]n";<br>
}<br>
<br>
以上的程序将列出结果中的每一行，打印出first name和last name。 这是最快的<br>
提取数据的方法之一。<br>
<br>
21. 如何从服务器随机地提取记录?<br>
答: 用mysql的LIMIT函数.<br>
$Query = "SELECT * FROM Table";<br>
$sth = $dbh->prepare($Query);<br>
$numrows = $sth->execute;<br>
$randomrow = int(rand($numrows));<br>
$sth = $dbh->prepare("$Query LIMIT $randomrow,1");<br>
$sth->execute;<br>
@arr = $sth->fetchrow;<br>
<br>
<br>
22. 插入记录后,如何获得自动增加的主键值?<br>
答: insertid方法是mysql特有的,也许不能在其它SQL server上工作<br>
<br>
#!/usr/bin/perl<br>
# 连接数据库 ....<br>
my $sql_statement = "INSERT INTO $table (field1,field2) VALUES($value1,$value2)";<br>
my $sth = $dbh->prepare($sql_statement);<br>
$sth->execute or die "无法添加数据 :<br>
$dbh->errstr";<br>
<br>
# 现在我们可以取回刚刚插入后生成的主键.<br>
my $table_key = $sth->{insertid};<br>
# 也可以用这种方法(标准的DBI方法)<br>
my $table_key = $dbh->{'mysql_insertid'};<br>
$sth->finish;<br>
<br>
23. 执行SELECT查询以后,如何获得记录行数?<br>
答: 有好几种方法可以做到。这是其中的一种:<br>
<br>
# 文档中说这种方法不行,但对我来说却可以,你或许也行.<br>
my $mysql_q = "SELECT field1,field2 FROM $table WHERE field1=$value1";<br>
my $sth = $dbh->prepare($mysql_q);<br>
my $found = $sth->execute or die "无法执行 :<br>
$dbh->errstr";<br>
$sth->finish;<br>
<br>
# 这是一种较慢的方法,而且做SELECT查询时还不太可靠.<br>
my $sql = q(select * from $table where field = ? );<br>
my $sth = $dbh->prepare($sql);<br>
$sth->execute('$value');<br>
my $rows = $sth->rows;<br>
$sth->finish;<br>
<br>
# 这是一种较快的方法.<br>
my $sql = q(select count(*) from $table where field = ? );<br>
my $sth = $dbh->prepare($sql); $sth->execute('$value');<br>
my $rows = $sth->fetchrow_arrayref->[0];<br>
$sth->finish;<br>
<br>
<br>
24. 为什么SELECT LAST_INSERT_ID(USER_ID) FROM User返回的是所有的user id而不是最后一个?<br>
答: 摘自手册："在服务器上最后创建的ID是根据每个连接来单独管理的。也就是说，<br>
它不能被另外一个客户端改变. 甚至你用一个非空和非零的值来更新另外一个<br>
AUTO_INCREMENT字段,它也不会改变. 如果算式做为一个参量赋给UPDATE语句中的<br>
LAST_INSERT_ID(),则参量会返回LAST_INSERT_ID()的值."<br>
<br>
你真正需要的是: SELECT USER_ID FROM User ORDER BY USER_ID DESC LIMIT 1<br>
<br>
25. WHERE语句中可否使用两个条件?<br>
答: 可以<br>
my $sql_statment = "SELECT * FROM $table WHERE $field1='$value1' AND $field2='$value2'";<br>
<br>
26. 如何在多个字段中查找一个关键字?<br>
答: 试下这个:<br>
SELECT concat(last,' ',first,' ',suffix,' ',year,' ',phone,' ',email) AS COMPLEAT, last, first, suffix, year, dorm, phone, box, email<br>
FROM Student HAVING COMPLEAT<br>
LIKE '%value1%' AND COMPLEAT LIKE '%value2%' AND COMPLEAT LIKE '%value3%'<br>
<br>
27.如何找到一个星期前创建的记录?<br>
答: 我们需要用DATE函数来做sql查询:<br>
DATE_ADD(date,INTERVAL expr type)<br>
DATE_SUB(date,INTERVAL expr type)<br>
ADDDATE(date,INTERVAL expr type)<br>
SUBDATE(date,INTERVAL expr type)<br>
<br>
例如 : # 这个查询语句返回所有"年龄"小于或等于7天的记录<br>
my $sql_q = "SELECT * FROM $database WHERE DATE_ADD(create_date,INTERVAL 7 DAY) >= NOW() ORDER BY create_date DESC";<br>
<br>
28.如何取回所有字段的数据并用"column_name" => value来放入一个相关的数组中?<br>
答:用$sth->fetchrow_hashref 方法.<br>
$SQL = "SELECT * FROM members";<br>
my $sth = $dbh->prepare($SQL);<br>
$sth->execute or die "sql语句错误 ".<br>
$dbh->errstr;<br>
my $record_hash;<br>
while ($record_hash = $sth->fetchrow_hashref){<br>
print "$record_hash->{first_name} $record_hash->{last_name}n";<br>
}<br>
$sth->finish;<br>
<br>
29.如何保存一个图像文件(JPG和GIF)到数据库中?<br>
答:<br>
file: test_insert_jpg.pl<br>
-------------------------<br>
#! /usr/bin/perl<br>
use DBI;<br>
open(IN,"/imgdir/bird.jpg");<br>
$gfx_file=join('',);<br>
close(IN);<br>
<br>
$database="speedy";<br>
$table="archive";<br>
$user="stephen";<br>
$password="none";<br>
$dsn="DBI:mysql:$database";<br>
$dbh=DBI->connect($dsn, $user, $password);<br>
$sql_statement=<<"__EOS__";<br>
insert into $table (id, date, category, caption, content, picture1, picture2,<br>
picture3, picture4, picture5, source, _show) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )<br>
<br>
__EOS__<br>
<br>
# uncomment to debug sql statement<br>
# --------------------------------<br>
#open(SQLLOG,">>sql_log_file");<br>
#print SQLLOG scalar(localtime)."t$sql_statementn";<br>
#close(SQLLOG);<br>
$sth=$dbh->prepare($sql_statement);<br>
$sth->execute(NULL,NULL,"car|sports","Porsche Boxster S","German excellence",$gfx_file,NULL,NULL,NULL,NULL,"European Car","Y");<br>
$sth->finish(); $sth=$dbh->prepare("SELECT * FROM $table");<br>
$sth->execute();<br>
<br>
while($ref=$sth->fetchrow_hashref()){<br>
print "id = $ref->{'id'}tcategory = $ref->{'category'}tcaption = $ref->{'caption'}n";<br>
}<br>
<br>
$numRows=$sth->rows;<br>
$sth->finish();<br>
$dbh->disconnect();<br>
<br>
file: serve_gfx.cgi<br>
-----------------------------------------------------<br>
#!/usr/bin/perl<br>
$|=1;<br>
use DBI;<br>
$database="speedy";<br>
$table="archive";<br>
$user="stephen";<br>
$password="none";<br>
$dsn="DBI:mysql:$database";<br>
$dbh=DBI->connect($dsn, $user,$password);<br>
$sth=$dbh->prepare("select * from $table where id=1");<br>
$sth->execute();<br>
$ref=$sth->fetchrow_hashref();<br>
print "content-type: image/jpgnn";<br>
print $ref->{'picture1'};<br>
$numRows=$sth->rows;<br>
$sth->finish();<br>
$dbh->disconnect();<br>
<br>
30. 如何插入N个记录?<br>
答:<br>
# 让我们插入10000个记录<br>
my $rec_num = 10000;<br>
my $PRODUCT_TB = "products";<br>
my $dbh = DBI->connect($database,$db_user,$db_password) or die "无法连接数据库n";<br>
my $sth = $dbh->prepare("INSERT INTO $PRODUCT_TB (name,price,description,pic_location) VALUES (?,?,?,?)");<br>
<br>
for ($i = 1; $i <= $rec_num; $i++){<br>
my $name = "Product $i";<br>
my $price = rand 350;<br>
my $desc = "Desccription of product $i";<br>
my $pic = "images/product/product".$i.".jpg";<br>
$sth->execute($name,$price,$desc,$pic);<br>
}<br>
$sth->finish();<br>
print "完成插入$rec_num个记录到表$PRODUCT_TBn";<br>
$dbh->disconnect;<br>
exit;<br>
<br>
31. 如何创建一个date字段,使其缺省值是新记录创建时的日期?<br>
答:有很多种方法可以做到:<br>
<br>
(1) 用TIMESTAMP<br>
Create Table mytable( table_id INT NOT NULL AUTO_INCREMENT,<br>
value VARCHAR(25),<br>
date TIMESTAMP(14),<br>
PRIMARY KEY (table_id) );<br>
<br>
当插入或更新记录时，TIMESTAMP字段将自动地设置成当前日期。 如果你不想更新<br>
时改变日期，可在用UPDATE语句时，把日期字段设置成原来的(插入日期)。<br>
<br>
(2) 用NOW()函数.<br>
<br>
Create Table mytable( table_id INT NOT NULL AUTO_INCREMENT,<br>
value VARCHAR(25),<br>
date DATE,<br>
PRIMARY KEY (table_id) );<br>
<br>
在insert语句中设置date=NOW().<br>
</FONT><br>
</TD>
</TR>
<TR>
<TD colSpan=2><FONT
class=middlefont></FONT><BR>
<FONT
class=normalfont>全文结束</FONT> </TD>
</TR>
<TR>
<TD background="images/dot.gif" tppabs="http://www.linuxhero.com/docs/images/dot.gif" colSpan=2
height=10></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE></DIV></TD>
<TD vAlign=top width="20%"
background="images/line.gif" tppabs="http://www.linuxhero.com/docs/images/line.gif" rowSpan=2>
<DIV align=center>
<table class=tableoutline cellspacing=1 cellpadding=4
width="100%" align=center border=0>
<tr class=firstalt>
<td noWrap background="images/bgline.gif" tppabs="http://www.linuxhero.com/docs/images/bgline.gif" colspan=2 height=21>
<font class=normalfont><b>所有分类</b></font></td>
</tr>
<tr class=secondalt> <td noWrap width=27%> <font class=normalfont>1:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type1.html" tppabs="http://www.linuxhero.com/docs/type1.html">非技术类</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>2:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type2.html" tppabs="http://www.linuxhero.com/docs/type2.html">基础知识</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>3:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type3.html" tppabs="http://www.linuxhero.com/docs/type3.html">指令大全</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>4:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type4.html" tppabs="http://www.linuxhero.com/docs/type4.html">shell</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>5:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type5.html" tppabs="http://www.linuxhero.com/docs/type5.html">安装启动</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>6:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type6.html" tppabs="http://www.linuxhero.com/docs/type6.html">xwindow</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>7:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type7.html" tppabs="http://www.linuxhero.com/docs/type7.html">kde</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>8:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type8.html" tppabs="http://www.linuxhero.com/docs/type8.html">gnome</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>9:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type9.html" tppabs="http://www.linuxhero.com/docs/type9.html">输入法类</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>10:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type10.html" tppabs="http://www.linuxhero.com/docs/type10.html">美化汉化</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>11:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type11.html" tppabs="http://www.linuxhero.com/docs/type11.html">网络配置</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>12:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type12.html" tppabs="http://www.linuxhero.com/docs/type12.html">存储备份</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>13:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type13.html" tppabs="http://www.linuxhero.com/docs/type13.html">杂项工具</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>14:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type14.html" tppabs="http://www.linuxhero.com/docs/type14.html">编程技术</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>15:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type15.html" tppabs="http://www.linuxhero.com/docs/type15.html">网络安全</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>16:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type16.html" tppabs="http://www.linuxhero.com/docs/type16.html">内核技术</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>17:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type17.html" tppabs="http://www.linuxhero.com/docs/type17.html">速度优化</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>18:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type18.html" tppabs="http://www.linuxhero.com/docs/type18.html">apache</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>19:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type19.html" tppabs="http://www.linuxhero.com/docs/type19.html">email</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>20:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type20.html" tppabs="http://www.linuxhero.com/docs/type20.html">ftp服务</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>21:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type21.html" tppabs="http://www.linuxhero.com/docs/type21.html">cvs服务</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>22:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type22.html" tppabs="http://www.linuxhero.com/docs/type22.html">代理服务</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>23:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type23.html" tppabs="http://www.linuxhero.com/docs/type23.html">samba</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>24:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type24.html" tppabs="http://www.linuxhero.com/docs/type24.html">域名服务</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>25:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type25.html" tppabs="http://www.linuxhero.com/docs/type25.html">网络过滤</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>26:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type26.html" tppabs="http://www.linuxhero.com/docs/type26.html">其他服务</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>27:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type27.html" tppabs="http://www.linuxhero.com/docs/type27.html">nfs</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>28:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type28.html" tppabs="http://www.linuxhero.com/docs/type28.html">oracle</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>29:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type29.html" tppabs="http://www.linuxhero.com/docs/type29.html">dhcp</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>30:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type30.html" tppabs="http://www.linuxhero.com/docs/type30.html">mysql</a></font></td> </tr> </table></td></tr><tr class=secondalt> <td noWrap width=27%> <font class=normalfont>31:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type31.html" tppabs="http://www.linuxhero.com/docs/type31.html">php</a></font></td> </tr> </table></td></tr><tr class=firstalt> <td noWrap width=27%> <font class=normalfont>32:</font> </td><td noWrap width=73%> <table width=100% border=0> <tr> <td><font class=normalfont><a href="type32.html" tppabs="http://www.linuxhero.com/docs/type32.html">ldap</a></font></td> </tr> </table></td></tr> </table>
</DIV></TD></TR>
<TR vAlign=top>
<TD width="80%">
<DIV align=center><BR>
</DIV>
</TD></TR></TBODY></TABLE></TD></TR>
</TABLE></TD></TR>
</TABLE>
<TABLE cellSpacing=0 cellPadding=4 width="100%" bgColor=#eeeeee
border=0><TBODY>
<TR>
<TD width="50%">
<P><FONT class=middlefont>版权所有 © 2004 <A
href="mailto:bjchenxu@sina.com">linux知识宝库</A><BR>
违者必究. </FONT></P>
</TD>
<TD width="50%">
<DIV align=right><FONT class=middlefont>Powered by: <A
href="mailto:bjchenxu@sina.com">Linux知识宝库</A> Version 0.9.0 </FONT></DIV>
</TD></TR></TBODY></TABLE>
<CENTER></CENTER></TD></TR>
</TABLE></CENTER></BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -