# 连接sql server
# 从表单中取得user id 和 password
# 你可能要通过$dbh->quote()函数来确定输入中没有会破坏SQL语句的字符
my $insert_user = "INSERT INTO $user_table (user_id,password)
VALUES('$user_id',PASSWORD('$password')";
my $insert_sth = $dbh->prepare($insert_user);
$insert_sth->execute() or die "Error : $dbh->errstr";
# 检查用户是否输入了正确的字符
# 注意 : 又必须先运行dbh->quote()
my $check_sql = "SELECT * FROM $user_table WHERE
user_id = $user_id AND passwordfield = PASSWORD('$password_entered')";
14. 如何在mysql中创建表 ?
答：试下这个 ..
CREATE TABLE pictures( picture_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
category_id SMALLINT UNSIGNED NOT NULL,
location VARCHAR(40),
thumb VARCHAR(40),
title VARCHAR(80) NOT NULL,
description TINYTEXT,
last_modified DATE,
last_viwed DATE,
view_count INT UNSIGNED,
user_id VARCHAR(20) NOT NULL,
colour ENUM('true','false') NOT NULL DEFAULT 'true',
PRIMARY KEY (picture_id),
INDEX (title),
INDEX (user_id),
INDEX (category_id),
INDEX (colour) );
16. 如何获得表的字段信息?
答：
#!/usr/bin/perl
# connect to db
my $dbh = DBI->connect(bla..bla..bla);
my $sql_q = "SHOW COLUMNS FROM $table";
my $sth = $dbh->prepare($sql_q);
$sth->execute;
while (@row = $sth->fetchrow_array){
print"Field Type Null Key Default Extran";
print"---------------------------------------------------------------n";
print"$row[0] $row[1] $row[2] $row[3] $row[4] $row[5]n";
}
17. 如何添加一个超级用户 ?
答: 你可以用GRANT语句:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'something' WITH GRANT OPTION;
#!/usr/bin/perl
# 连接服务器 (见22)
my $sql_statement = "SELECT first_name,last_name FROM $table ORDER BY first_name";
my $sth = $dbh->prepare($sql_statement);
my ($first, $last);
# 结果保存在$sth中 $sth->execute() or die "无法执行SQL语句:
$dbh->errstr"; $sth->bind_columns(undef, $first, $last);
my $row; while ($row = $sth->fetchrow_arrayref) {
print "$first $lastn";
# 或者
print "$row->[0] $row[1]n";
}
#!/usr/bin/perl
# 连接数据库 ....
my $sql_statement = "INSERT INTO $table (field1,field2) VALUES($value1,$value2)";
my $sth = $dbh->prepare($sql_statement);
$sth->execute or die "无法添加数据 :
$dbh->errstr";
# 现在我们可以取回刚刚插入后生成的主键.
my $table_key = $sth->{insertid};
# 也可以用这种方法(标准的DBI方法)
my $table_key = $dbh->{'mysql_insertid'};
$sth->finish;
23. 执行SELECT查询以后,如何获得记录行数?
答: 有好几种方法可以做到。这是其中的一种:
# 文档中说这种方法不行,但对我来说却可以,你或许也行.
my $mysql_q = "SELECT field1,field2 FROM $table WHERE field1=$value1";
my $sth = $dbh->prepare($mysql_q);
my $found = $sth->execute or die "无法执行 :
$dbh->errstr";
$sth->finish;
# 这是一种较慢的方法,而且做SELECT查询时还不太可靠.
my $sql = q(select * from $table where field = ? );
my $sth = $dbh->prepare($sql);
$sth->execute('$value');
my $rows = $sth->rows;
$sth->finish;
# 这是一种较快的方法.
my $sql = q(select count(*) from $table where field = ? );
my $sth = $dbh->prepare($sql); $sth->execute('$value');
my $rows = $sth->fetchrow_arrayref->[0];
$sth->finish;
你真正需要的是: SELECT USER_ID FROM User ORDER BY USER_ID DESC LIMIT 1
25. WHERE语句中可否使用两个条件?
答: 可以
my $sql_statment = "SELECT * FROM $table WHERE $field1='$value1' AND $field2='$value2'";
26. 如何在多个字段中查找一个关键字?
答: 试下这个:
SELECT concat(last,' ',first,' ',suffix,' ',year,' ',phone,' ',email) AS COMPLEAT, last, first, suffix, year, dorm, phone, box, email
FROM Student HAVING COMPLEAT
LIKE '%value1%' AND COMPLEAT LIKE '%value2%' AND COMPLEAT LIKE '%value3%'
file: serve_gfx.cgi
-----------------------------------------------------
#!/usr/bin/perl
$|=1;
use DBI;
$database="speedy";
$table="archive";
$user="stephen";
$password="none";
$dsn="DBI:mysql:$database";
$dbh=DBI->connect($dsn, $user,$password);
$sth=$dbh->prepare("select * from $table where id=1");
$sth->execute();
$ref=$sth->fetchrow_hashref();
print "content-type: image/jpgnn";
print $ref->{'picture1'};
$numRows=$sth->rows;
$sth->finish();
$dbh->disconnect();
30. 如何插入N个记录?
答:
# 让我们插入10000个记录
my $rec_num = 10000;
my $PRODUCT_TB = "products";
my $dbh = DBI->connect($database,$db_user,$db_password) or die "无法连接数据库n";
my $sth = $dbh->prepare("INSERT INTO $PRODUCT_TB (name,price,description,pic_location) VALUES (?,?,?,?)");
for ($i = 1; $i <= $rec_num; $i++){
my $name = "Product $i";
my $price = rand 350;
my $desc = "Desccription of product $i";
my $pic = "images/product/product".$i.".jpg";
$sth->execute($name,$price,$desc,$pic);
}
$sth->finish();
print "完成插入$rec_num个记录到表$PRODUCT_TBn";
$dbh->disconnect;
exit;
31. 如何创建一个date字段,使其缺省值是新记录创建时的日期?
答:有很多种方法可以做到:
(1) 用TIMESTAMP
Create Table mytable( table_id INT NOT NULL AUTO_INCREMENT,
value VARCHAR(25),
date TIMESTAMP(14),
PRIMARY KEY (table_id) );
设为主页
收藏本站
相关文章: