How to execute sql query statement in mysql

As a developer, using MySQL database for data storage and query is one of the common tasks. Not only do you need to learn SQL queries, but you also need to understand how to execute these queries in MySQL. In this article, we’ll take a closer look at how MySQL executes SQL queries.

Execute SQL queries using the MySQL shell

MySQL provides a command line interface, called the MySQL shell, which allows developers to interact with the database. We can use MySQL shell to execute SQL queries.

First, we need to enter the MySQL shell through the command prompt or terminal. Enter the following command:

mysql -u root -p

-u specifies the username and -p specifies the password. If you do not set a password, you do not need to use the -p flag.

After successful login, you will see the MySQL shell prompt as shown below:

mysql>

Now, we can execute the SQL query. For example, if you want to query all columns in a table, you can use the following command:

SELECT * FROM table_name;

This command will return all columns in the table. Make sure to replace "table_name" with the actual table name you are querying.

Use MySQL Workbench to execute SQL queries

MySQL Workbench is a visual data modeling and database management tool officially provided by MySQL and is suitable for multiple platforms. Using Workbench, you can execute SQL queries and visualize the results.

First, you need to start MySQL Workbench and connect to your database. Once connected, select "Query Manager" from the menu on the left.

In Query Manager, you can write and execute SQL queries. For example, the following query will return all rows in a table named "users":

SELECT * FROM users;

You can also use Workbench to visualize the results of SQL queries, making it easier to view and analyze the data. When a query returns results, you can access the Result Set panel, which displays the query results.

Execute SQL queries using programming languages

As developers, we usually use programming languages ​​to interact with databases. Different programming languages ​​use different APIs to interact with MySQL.

The following are examples of executing SQL queries in common programming languages:

Using Python

In Python, we can use the MySQL Connector module to connect and query the MySQL database. The following is a sample code to query all rows in a table named "users":

import mysql.connector

# 建立连接
mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
)

# 创建游标对象
mycursor = mydb.cursor()

# 执行查询
mycursor.execute("SELECT * FROM users")

# 获取查询结果
myresult = mycursor.fetchall()

# 打印结果
for x in myresult:
    print(x)

Using PHP

In PHP, we can use specific modules such as mysqli or PDO with MySQL Interactively, the following is a sample code that uses mysqli to query all rows in a table named "users":

$servername = "localhost";
$username = "yourusername";
$password = "yourpassword";
$dbname = "mydatabase";

// 建立连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接是否成功
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// 执行查询
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

// 打印结果
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

// 关闭连接
mysqli_close($conn);

This is some basic methods and examples of MySQL queries and executing SQL queries. I hope this article can help developers use MySQL better. If you need to learn more, you can read the official MySQL documentation or related books.

The above is the detailed content of How to execute sql query statement in mysql. For more information, please follow other related articles on the PHP Chinese website!