Mysqli 简明教程

MySQLi - Select Query

SQL SELECT 命令用于从 MySQL 数据库中获取数据。您可以在 mysql> 提示符以及任何像 PHP 这样的脚本中使用此命令。

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.

Syntax

以下是用于从 MySQL 表格中获取数据的 SELECT 命令的通用 SQL 语法 -

Here is generic SQL syntax of SELECT command to fetch data from the MySQL table −

SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

  1. 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.

  2. You can fetch one or more fields in a single SELECT command.

  3. You can specify star (*) in place of fields. In this case, SELECT will return all the fields.

  4. You can specify any condition using the WHERE clause.

  5. You can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.

  6. You can limit the number of returns using the LIMIT attribute.

Fetching Data from a Command Prompt

这将使用 SQL SELECT 命令从 MySQL 表 tutorials_tbl 中获取数据。

This will use SQL SELECT command to fetch data from the MySQL table tutorials_tbl.

Example

以下示例将返回 tutorials_tbl 表中的所有记录 -

The following example will return all the records from the tutorials_tbl table −

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>

Fetching Data Using a PHP Script

PHP 使用 mysqli query() 或 mysql_query() 函数来从 MySQL 表中选择记录。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。

PHP uses mysqli query() or mysql_query() function to select records from a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)

Sr.No.

Parameter & Description

1

$sql Required - SQL query to select records from a MySQL table.

2

$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

尝试使用以下示例从表中选择记录-

Try the following example to select a record from a table −

将以下示例复制粘贴为 mysql_example.php：

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');

         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";

         $result = $mysqli->query($sql);

         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
                  $row["tutorial_id"],
                  $row["tutorial_title"],
                  $row["tutorial_author"],
                  $row["submission_date"]);
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

访问在 apache web 服务器上部署的 mysql_example.php，并验证输出。在运行选择脚本之前，我们在此表中输入了多条记录。

Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Java Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021