MariaDB Select查询
MariaDB Select查询
在本章中,我们将学习如何从表中选择数据。
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
所有select语句必须包含一个或多个select表达式。select表达式由以下选项之一组成:
至少一个列名。
使用运算符和函数的表达式。
指定"table_name.*”选择表中的所有列。
字符“*”用于从FROM子句中指定的所有表中选择所有列。
命令提示符或PHP脚本均可用于执行select语句。
命令提示符
在命令提示符下,执行以下语句:
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() 函数,在函数中使用SELECT语句。查看下面给出的一个例子:
<?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); ?>