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