使用 Perl 的 SQLite 查询 · ZetCode 中文系列教程

# 使用 Perl 的 SQLite 查询 > 原文： [http://zetcode.com/db/sqliteperltutorial/queries/](http://zetcode.com/db/sqliteperltutorial/queries/) 我们已经建立了到数据库的连接。现在我们要修改并从数据库中获取数据。使用`SELECT`语句从数据库中检索数据。在 Perl DBI 中，首先我们使用`prepare()`方法准备 SQL 语句。 SQL 字符串被发送到数据库引擎，该引擎检查语句的有效性，语法，并在某些数据库中检查执行某些查询的用户权限。如果一切正常，则对语句句柄的引用将返回到 Perl 脚本。下一步是对`execute()`方法的调用。该方法在数据库内执行查询。此时，结果保留在数据库中。 Perl 脚本尚不包含数据。对于非选择语句，`execute()`方法返回已知的受影响的行数。在最后一步中，从数据库中获取数据。数据被逐行提取并填充到 Perl 数据结构中。 Perl DBI 有几种从数据库表中获取数据的方法。 | 方法 | 描述 | | --- | --- | | `fetchrow_arrayref()` | 获取下一行数据并返回对数组的引用。 | | `fetchrow_array()` | 获取下一行数据，并将其作为列表返回。 | | `fetchrow_hashref()` | 获取下一行数据，并将其返回为对哈希的引用。 | | `fetchall_arrayref()` | 提取所有数据&返回对每行有一个引用的数组的引用。 | | `fetch()` | 该方法是`fetchrow_arrayref()`的别名。 | | `fetchrow()` | 该方法是`fetchrow_array()`的别名。 | 准备并执行完 SQL 语句后，我们调用可用的访存方法之一。 | 方法 | 描述 | | --- | --- | | `selectrow_arrayref()` | 将`prepare()`，`execute()`和`fetchrow_arrayref()`合并为一个调用 | | `selectrow_hashref()` | 将`prepare()`，`execute()`和`fetchrow_hashref()`合并为一个调用 | | `selectrow_array()` | 将`prepare()`，`execute()`和`fetchrow_array（）`合并为一个调用。 | | `selectall_arrayref()` | 将`prepare()`，`execute()`和`fetchall_arrayref()`合并为一个调用。 | | `selectall_hashref()` | 将`prepare()`，`execute()`和`fetchall_hashref()`合并为一个调用。 | | `selectcol_arrayref()` | 合并`prepare()`，`execute()`并从所有行中提取一个`col`到一个调用中。 | 在第二张表中，我们列出了工具方法，这些方法将三个方法组合为一个调用。它们是方便的方法。 ## 提取方法在第一个示例中，我们将演示`fetchrow_arrayref()`方法的用法。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5"); $sth->execute(); my $row; while ($row = $sth->fetchrow_arrayref()) { print "@$row[0] @$row[1] @$row[2]\n"; } $sth->finish(); $dbh->disconnect(); ``` 在示例中，我们从`Cars`表中选择 5 行。使用`fetchrow_arrayref()`方法检索数据。 ```perl my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5"); $sth->execute(); ``` 这是数据检索过程的前两个阶段。我们准备并执行`SELECT`语句。 ```perl my $row; while ($row = $sth->fetchrow_arrayref()) { print "@$row[0] @$row[1] @$row[2]\n"; } ``` 现在，我们正在获取数据。 `fetchrow_arrayref()`方法获取下一行数据，并返回对包含字段值的数组的引用。当没有更多的行了时，我们将方法放入终止的`while`循环中。 ```perl $ ./fetchrow_arrayref.pl 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 ``` 示例输出。在第二个示例中，我们将使用`fetchrow_array()`方法。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" ); $sth->execute(); my @row; while (@row = $sth->fetchrow_array()) { print "@row\n"; } $sth->finish(); $dbh->disconnect(); ``` 在此脚本中，我们连接到数据库，并使用`fetchrow_array()`方法一张一张地获取 5 行`Cars`表。 ```perl my @row; while (@row = $sth->fetchrow_array()) { print "@row\n"; } ``` `fetchrow_array()`方法获取下一行数据，并将其作为包含字段值的列表返回。我们使用`while`循环遍历所有 5 行。在下一个示例中，我们将按列名称获取数据。为此，我们将使用`fetchrow_hashref()`方法。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" ); $sth->execute(); my $row; while($row = $sth->fetchrow_hashref()) { print "$row->{Id} $row->{Name} $row->{Price}\n"; } $sth->finish(); $dbh->disconnect(); ``` 在示例中，数据以对 Perl 哈希的引用形式返回。 ```perl my $row; while($row = $sth->fetchrow_hashref()) { print "$row->{Id} $row->{Name} $row->{Price}\n"; } ``` `fetchrow_hashref()`方法获取下一行数据，并将其返回为对包含字段名称和字段值对的哈希的引用。使用此方法，我们可以按列名称检索值。在本节的最后一个示例中，我们一步一步从`SELECT`语句中获取所有数据。我们使用`fetchall_arrayref()`方法。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $sth = $dbh->prepare("SELECT * FROM Cars LIMIT 5"); $sth->execute(); my $all = $sth->fetchall_arrayref(); foreach my $row (@$all) { my ($id, $name, $price) = @$row; print "$id $name $price\n"; } $sth->finish(); $dbh->disconnect(); ``` 该示例从`Cars`表中选择并打印五行。 ```perl my $all = $sth->fetchall_arrayref(); ``` 我们通过一个方法调用获取所有数据。 `fetchall_arrayref()`方法返回对数组的引用，该数组每行包含一个引用。 ```perl foreach my $row (@$all) { my ($id, $name, $price) = @$row; print "$id $name $price\n"; } ``` 我们使用`foreach`循环遍历检索到的数据。 ## 转储数据 Perl DBI 有一个称为`dump_results()`的特殊方法。此方法被设计为用于原型设计和测试查询的便捷工具。它使用`neat_list()`方法格式化和编辑字符串以供人类阅读。不建议将其用于数据传输应用。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 } ) or die $DBI::errstr; my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" ); $sth->execute(); $sth->dump_results(); $sth->finish(); $dbh->disconnect(); ``` 在示例中，我们将转储结果集中的所有数据。 ```perl my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 5" ); $sth->execute(); ``` SQL 语句从`Cars`表中选择五行。和所有三列。 ```perl $sth->dump_results(); ``` `dump_results()`从语句句柄中选择所有行并进行打印。这是用于原型制作和测试的方法。 ```perl $ ./dump.pl 1, 'Audi', 52642 2, 'Mercedes', 57127 3, 'Skoda', 9000 4, 'Volvo', 29000 5, 'Bentley', 350000 5 rows ``` 这是示例的输出。 ## 便利方法我们将展示两个使用上述便利方法的示例。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $ary = $dbh->selectrow_arrayref("SELECT * FROM Cars WHERE Id = 5"); print join(" ", @$ary), "\n"; $dbh->disconnect(); ``` 在第一个代码示例中，我们将调用`selectrow_arrayref()`方法。我们从`Cars`表中选择第五行。 ```perl my $ary = $dbh->selectrow_arrayref("SELECT * FROM Cars WHERE Id = 5"); ``` `selectrow_arrayref()`方法将`prepare()`，`execute()`和`fetchrow_arrayref()`合并为一个调用。它返回对语句第一行数据的引用。请注意，我们不使用语句句柄。我们使用`$dbh`数据库句柄对象。 ```perl print join(" ", @$ary), "\n"; ``` 我们将行打印到控制台。下面的示例显示`selectall_arrayref()`方法。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $all = $dbh->selectall_arrayref("SELECT * FROM Cars LIMIT 5"); foreach my $row (@$all) { my ($id, $name, $price) = @$row; print "$id $name $price\n"; } $dbh->disconnect(); ``` 我们再次从`Cars`表中检索 5 行。 ```perl my $all = $dbh->selectall_arrayref("SELECT * FROM Cars LIMIT 5"); ``` `selectall_arrayref()`方法返回对数组的引用，其中包含对获取的每一行数据的数组的引用。提供的 SQL 语句从`Cars`表中选择 5 行。请注意，我们既没有调用`prepare()`也没有调用`execute()`方法。这是因为`selectall_arrayref()`方法将`prepare()`，`execute()`和`fetchall_arrayref()`组合到一个调用中。 ```perl foreach my $row (@$all) { my ($id, $name, $price) = @$row; print "$id $name $price\n"; } ``` 我们遍历提取的数组数组并将数据打印到终端。 ```perl $ ./retrieve.pl 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 ``` 这是示例的输出。 ## 参数化查询现在，我们将关注参数化查询。当使用参数化查询时，我们使用占位符，而不是直接将值写入语句。参数化查询可提高安全性和性能。当程序收到用户的输入时，程序员必须始终保持谨慎。稍后，我们将值绑定到预备语句，而不是从用户输入构建字符串。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $id = 3; my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id = ?" ); $sth->execute($id); my $ret = $sth->fetch(); foreach my $row (@$ret) { print "$row "; } print "\n"; $sth->finish(); $dbh->disconnect(); ``` 在代码示例中，我们从表中选择特定的行。 SQL 语句具有一个占位符，稍后将在代码中填充该占位符。 ```perl my $id = 3; ``` 这可能是用户的输入。 ```perl my $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id = ?" ); ``` 问号`?`是值的占位符。该值稍后添加。 ```perl $sth->execute($id); ``` `execute()`语句采用一个参数，该参数绑定到占位符。 ```perl $ ./parameterized.pl 3 Skoda 9000 ``` 我们已经使用参数化查询从`Cars`表中检索了一行。在第二个示例中，我们将使用便利选择方法之一使用参数化查询。 ```perl #!/usr/bin/perl use strict; use DBI; my $dbh = DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; my $id = 2; my @ary = $dbh->selectrow_array("SELECT * FROM Cars WHERE Id = ?", undef, $id); print join(" ", @ary), "\n"; $dbh->disconnect(); ``` 我们需要在`SELECT`查询中填充一个占位符。 ```perl my @ary = $dbh->selectrow_array("SELECT * FROM Cars WHERE Id = ?", undef, $id); ``` `selectrow_array()`方法的第三个参数采用占位符的值。在 SQLite Perl 教程的这一部分中，我们已经演示了如何使用各种 Perl DBI 方法从数据库中获取数据。