在本章中，我们将学习如何从表中选择数据.
SELECT语句检索选定的行.它们可以包括UNION语句，排序子句，LIMIT子句，WHERE子句，GROUP BY ... HAVING子句和子查询.
查看以下一般语法 :
SELECT field, field2,... FROM table_name, table_name2,... WHERE...
SELECT语句提供了多个用于指定所用表和减号的选项;
database_name.table_name
table_name.column_name
database_name.table_name.column_name
所有选择语句必须包含一个或多个选择表达式.选择表达式包含以下选项之一 :
列名.
使用运算符和函数的表达式.
规范"table_name.*"选择给定表中的所有列.
字符"*"，用于选择FROM子句中指定的所有表中的所有列.
执行select语句时可以使用命令提示符或PHP脚本.
在命令提示符下，按以下顺序执行语句;
root@host# mysql -u root -p password; Enter password:******* mysql> use PRODUCTS; Database changed mysql> SELECT * from products_tbl +-------------+---------------+ | ID_number | Nomenclature | +-------------+---------------+ | 12345 | Orbitron 4000 | +-------------+---------------+
在PHP函数中使用相同的SELECT语句来执行操作.您将再次使用 mysql_query()函数.查看下面给出的示例 :
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT product_id, product_name,product_manufacturer, ship_date FROM products_tbl';
mysql_select_db('PRODUCTS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Product ID :{$row['product_id']} <br> ".
"Name: {$row['product_name']} <br> ".
"Manufacturer: {$row['product_manufacturer']} <br> ".
"Ship Date : {$row['ship_date']} <br>".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>成功检索数据后，您将看到以下输出 :
Product ID: 12345 Nomenclature: Orbitron 4000 Manufacturer: XYZ Corp Ship Date: 01/01/17 ---------------------------------------------- Product ID: 12346 Nomenclature: Orbitron 3000 Manufacturer: XYZ Corp Ship Date: 01/02/17 ---------------------------------------------- mysql> Fetched data successfully
最佳做法建议在每个SELECT语句后释放游标内存.为此，PHP提供了 mysql_free_result()函数.查看其用法如下所示 :
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl';
mysql_select_db('PRODUCTS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
echo "Product ID :{$row[0]} <br> ".
"Name: {$row[1]} <br> ".
"Manufacturer: {$row[2]} <br> ".
"Ship Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully\n";
mysql_close($conn);
?>
