MySQL-选择查询
MySQL-选择查询 (MySQL - Select Query)
The SQL SELECT command is used to fetch data from the MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.
SQL SELECT命令用于从MySQL数据库获取数据。 您可以在mysql>提示符以及任何脚本(如PHP)中使用此命令。
句法 (Syntax)
Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −
这是SELECT命令的通用SQL语法，用于从MySQL表中获取数据-
SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]
You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
您可以使用WHERE子句来使用一个或多个用逗号分隔的表来包含各种条件，但是WHERE子句是SELECT命令的可选部分。
You can fetch one or more fields in a single SELECT command.
您可以在单个SELECT命令中获取一个或多个字段。
You can specify star (*) in place of fields. In this case, SELECT will return all the fields.
您可以指定星号(*)代替字段。 在这种情况下，SELECT将返回所有字段。
You can specify any condition using the WHERE clause.
您可以使用WHERE子句指定任何条件。
You can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.
您可以使用OFFSET指定偏移量，SELECT将从该偏移量开始返回记录。 默认情况下，偏移量从零开始。
You can limit the number of returns using the LIMIT attribute.
您可以使用LIMIT属性限制退货数量。
从命令提示符中获取数据 (Fetching Data from a Command Prompt)
This will use SQL SELECT command to fetch data from the MySQL table tutorials_tbl.
这将使用SQL SELECT命令从MySQL表tutorials_tbl中获取数据。
例 (Example)
The following example will return all the records from the tutorials_tbl table −
以下示例将返回tutorials_tbl表中的所有记录-
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2007-05-21 |
| 2 | Learn MySQL | Abdul S | 2007-05-21 |
| 3 | JAVA Tutorial | Sanjay | 2007-05-21 |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)
mysql>
使用PHP脚本获取数据 (Fetching Data Using a PHP Script)
You can use the same SQL SELECT command into a PHP function mysql_query(). This function is used to execute the SQL command and then later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns the row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.
您可以在PHP函数mysql_query()中使用相同SQL SELECT命令。 该函数用于执行SQL命令，然后可以使用另一个PHP函数mysql_fetch_array()来获取所有选定数据。 此函数以关联数组，数字数组或两者兼有的形式返回该行。 如果没有更多行，此函数将返回FALSE。
The following program is a simple example which will show how to fetch / display records from the tutorials_tbl table.
下面的程序是一个简单的示例，它将显示如何从tutorials_tbl表中获取/显示记录。
例 (Example)
The following code block will display all the records from the tutorials_tbl table.
下面的代码块将显示tutorials_tbl表中的所有记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
The content of the rows is assigned to the variable $row and the values in that row are then printed.
将行的内容分配给变量$ row，然后打印该行中的值。
NOTE − Always remember to put curly brackets when you want to insert an array value directly into a string.
注意 -当您想将数组值直接插入字符串时，请始终记住使用大括号。
In the above example, the constant MYSQL_ASSOC is used as the second argument to the PHP function mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.
在上面的示例中，常量MYSQL_ASSOC用作PHP函数mysql_fetch_array()的第二个参数，因此它将行作为关联数组返回。 使用关联数组，您可以使用字段名称而不是使用索引来访问该字段。
PHP provides another function called mysql_fetch_assoc(), which also returns the row as an associative array.
PHP提供了另一个名为mysql_fetch_assoc()的函数，该函数还以关联数组的形式返回该行。
例 (Example)
The following example to display all the records from the tutorial_tbl table using mysql_fetch_assoc() function.
下面的示例使用mysql_fetch_assoc()函数显示tutorial_tbl表中的所有记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval)) {
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
You can also use the constant MYSQL_NUM as the second argument to the PHP function mysql_fetch_array(). This will cause the function to return an array with the numeric index.
您还可以将常量MYSQL_NUM用作PHP函数mysql_fetch_array()的第二个参数。 这将导致函数返回带有数字索引的数组。
例 (Example)
Try out the following example to display all the records from tutorials_tbl table using the MYSQL_NUM argument.
尝试以下示例使用MYSQL_NUM参数显示tutorials_tbl表中的所有记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
All the above three examples will produce the same result.
以上三个示例都将产生相同的结果。
释放内存 (Releasing Memory)
It is a good practice to release cursor memory at the end of each SELECT statement. This can be done by using the PHP function mysql_free_result(). The following program is the example to show how it should be used.
在每个SELECT语句的末尾释放游标内存是一个好习惯。 这可以通过使用PHP函数mysql_free_result()来完成。 下面的程序是显示应如何使用的示例。
例 (Example)
Try out the following example −
尝试以下示例-
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully\n";
mysql_close($conn);
?>
While fetching data, you can write as complex a code as you like, but the procedure will remain the same as mentioned above.
在获取数据时，您可以根据需要编写复杂的代码，但是该过程将保持与上述相同。
翻译自: https://www.tutorialspoint.com/mysql/mysql-select-query.htm