-- Learning not just technology, but dreams!
Home HTML JAVASCRIPT CSS VUE REACT PYTHON3 JAVA C C++ C# AI GO SQL LINUX VS CODE BOOTSTRAP GIT Local Bookmarks
MySQL Tutorial
MySQL Tutorial
MySQL Installation
MySQL Management
MySQL Connection
MySQL Create Database
MySQL Drop Database
MySQL Select Database
MySQL Data Types
MySQL Create Table
MySQL Drop Table
MySQL Insert Data
MySQL Query Data
MySQL WHERE Clause
MySQL UPDATE
MySQL DELETE
MySQL LIKE Clause
MySQL UNION
MySQL ORDER BY
MySQL GROUP BY
MySQL Join
MySQL NULL Values
MySQL Regular Expressions
MySQL Transactions
MySQL ALTER Command
MySQL Indexes
MySQL Temporary Tables
MySQL Copy Tables
MySQL Synonyms
MySQL Sequences
MySQL Handling Duplicates
MySQL and SQL Injection
MySQL Export Data
MySQL Import Data
MySQL Functions
MySQL Operators
MySQL Quick Reference
MySQL Quiz
MySQL Programming Languages
MySQL Node.js
MySQL PHP
MySQL Python
MySQL Java
MySQL Insert Data
MySQL WHERE Clause
Deep Dive
Scripting Languages
Web Design & Development
Educational Resources
Distance Learning
Machine Learning & AI
Programming Languages
Education
Software
Social Sciences
Internet & Telecom
MySQL Query Data
MySQL database uses the SELECT statement to query data.
You can query data in the database through the mysql> command prompt window or through PHP scripts.
Syntax
The following is the general SELECT syntax for querying data in a MySQL database:
SELECT column1, column2, ...
FROM table_name
[ORDER BY column_name [ASC | DESC]]
;
Parameter Description:
column1, column2, ... are the names of the columns you want to select. Use * to select all columns.
table_name is the name of the table from which you want to query data.
WHERE condition is an optional clause used to specify filter conditions, returning only rows that meet the criteria.
ORDER BY column_name [ASC | DESC] is an optional clause used to specify the sort order of the result set. The default is ascending (ASC).
LIMIT number is an optional clause used to limit the number of rows returned.
Simple Application Examples of MySQL SELECT Statement:
Example
-- Select all columns and all rows
SELECT * FROM users;
-- Select all rows of specific columns
SELECT username, email FROM users;
-- Add WHERE clause to select rows that meet the condition
SELECT * FROM users WHERE is_active = TRUE;
-- Add ORDER BY clause to sort by a column in ascending order
SELECT * FROM users ORDER BY birthdate;
-- Add ORDER BY clause to sort by a column in descending order
SELECT * FROM users ORDER BY birthdate DESC;
-- Add LIMIT clause to limit the number of rows returned
SELECT * FROM users LIMIT 10;
The SELECT statement is flexible; we can combine and use these clauses according to actual needs, such as using both WHERE and ORDER BY clauses simultaneously, or using LIMIT to control the number of rows returned.
In the WHERE clause, you can use various conditional operators (like =, , =, !=), logical operators (like AND, OR, NOT), and wildcards (like %), etc.
Here are some advanced SELECT statement examples:
Example
-- Use AND operator and wildcard
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;
-- Use OR operator
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';
-- Use IN clause
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');
Fetching Data via Command Prompt
In the following example, we will use the SQL SELECT command to fetch data from the MySQL data table tutorial_tbl:
Example
The following example will return all records from the data table tutorial_tbl:
Read the data table:
select * from tutorial_tbl;
Output result:
Using PHP Scripts to Fetch Data
Use the PHP function mysqli_query() and the SQL SELECT command to fetch data.
This function is used to execute SQL commands, and then use the PHP function mysqli_fetch_array() to use or output all queried data.
The mysqli_fetch_array() function fetches one row from the result set as an associative array, a numeric array, or both. It returns an array generated from the row fetched from the result set, or false if there are no more rows.
The following example reads all records from the data table tutorial_tbl.
Example
Try the following example to display all records from the data table tutorial_tbl.
Fetch data using mysqli_fetch_array with MYSQLI_ASSOC parameter:
<?php
$dbhost = 'localhost'; // MySQL server host address
$dbuser = 'root'; // MySQL username
$dbpass = '123456'; // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled text
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorial_tbl';
mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Cannot read data: ' . mysqli_error($conn));
}
echo '
mysqli_fetch_array Test
';
echo '
| Tutorial ID | Title | Author | Submission Date |
';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
echo "| {$row['tutorial_id']} | ".
"{$row['tutorial_title']} | ".
"{$row['tutorial_author']} | ".
"{$row['submission_date']} | ".
"
";
}
echo '
';
mysqli_close($conn);
?>
The output result is as follows:
In the above example, each fetched row is assigned to the variable $row, and then each value is printed.
Note: Remember, if you need to use variables in a string, place the variables inside curly braces.
In the example above, the second parameter of the PHP mysqli_fetch_array() function is MYSQLI_ASSOC. Setting this parameter makes the query result return an associative array, allowing you to use field names as array indices.
PHP provides another function, mysqli_fetch_assoc(), which fetches one row from the result set as an associative array. It returns an associative array generated from the row fetched from the result set, or false if there are no more rows.
Example
Try the following example, which uses the mysqli_fetch_assoc() function to output all records from the data table tutorial_tbl:
Fetch data using mysqli_fetch_assoc:
<?php
$dbhost = 'localhost:3306'; // MySQL server host address
$dbuser = 'root'; // MySQL username
$dbpass = '123456'; // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled text
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorial_tbl';
mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Cannot read data: ' . mysqli_error($conn));
}
echo '
mysqli_fetch_assoc Test
';
echo '
| Tutorial ID | Title | Author | Submission Date |
';
while($row = mysqli_fetch_assoc($retval))
{
echo "| {$row['tutorial_id']} | ".
"{$row['tutorial_title']} | ".
"{$row['tutorial_author']} | ".
"{$row['submission_date']} | ".
"
";
}
echo '
';
mysqli_close($conn);
?>
The output result is as follows:
You can also use the constant MYSQLI_NUM as the second parameter of the PHP mysqli_fetch_array() function to return a numeric array.
Example
The following example uses the MYSQLI_NUM parameter to display all records from the data table tutorial_tbl:
Fetch data using mysqli_fetch_array with MYSQLI_NUM parameter:
<?php
$dbhost = 'localhost:3306'; // MySQL server host address
$dbuser = 'root'; // MySQL username
$dbpass = '123456'; // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled text
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorial_tbl';
mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Cannot read data: ' . mysqli_error($conn));
}
echo '
mysqli_fetch_array Test
';
echo '
| Tutorial ID | Title | Author | Submission Date |
';
while($row = mysqli_fetch_array($retval, MYSQLI_NUM))
{
echo "| {$row} | ".
"{$row} | ".
"{$row} | ".
"{$row} | ".
"
";
}
echo '
';
mysqli_close($conn);
?>
The output result is as follows:
The output results of the above three examples are all the same.
Memory Release
After executing the SELECT statement, it is a good practice to release the cursor memory.
This can be achieved through the PHP function mysqli_free_result().
The following example demonstrates the usage of this function.
Example
Try the following example:
Release memory using mysqli_free_result:
<?php
$dbhost = 'localhost:3306'; // MySQL server host address
$dbuser = 'root'; // MySQL username
$dbpass = '123456'; // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled text
mysqli_query($conn , "set names utf8");
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorial_tbl';
mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('Cannot read data: ' . mysqli_error($conn));
}
echo '
mysqli_fetch_array Test
';
echo '
| Tutorial ID | Title | Author | Submission Date |
';
while($row = mysqli_fetch_array($retval, MYSQLI_NUM))
{
echo "| {$row} | ".
"{$row} | ".
"{$row} | ".
"{$row} | ".
"
";
}
echo '
';
// Release memory
mysqli_free_result($retval);
mysqli_close($conn);
?>
The output result is as follows:
MySQL Insert Data
MySQL WHERE Clause
ByteArk Coding Plan
Supports mainstream large models like Doubao, GLM, DeepSeek, Kimi, MiniMax, officially supplied, stable and reliable.
Configuration Guide
¥9.9
/ month
Subscribe Now
iFlytek Spark Coding Plan
Includes free model call quotas, DeepSeek, GLM, Kimi, MiniMax, one-stop experience and deployment platform.
Configuration Guide
¥3.9
/ month
Subscribe Now
7 Notes
Write Note
Category Navigation
Python / Data Science
AI / Intelligent Development
Frontend Development
Backend Development
Database
Mobile Development
DevOps / Engineering
Programming Languages
Computer Fundamentals
XML / Web Service
.NET
Website Building
Advertisement
MySQL Tutorial
MySQL Tutorial
MySQL Installation
MySQL Management
MySQL Connection
MySQL Create Database
MySQL Drop Database
MySQL Select Database
MySQL Data Types
MySQL Create Table
MySQL Drop Table
MySQL Insert Data
MySQL Query Data
MySQL WHERE Clause
MySQL UPDATE
MySQL DELETE
MySQL LIKE Clause
MySQL UNION
MySQL ORDER BY
MySQL GROUP BY
MySQL Join
MySQL NULL Values
MySQL Regular Expressions
MySQL Transactions
MySQL ALTER Command
MySQL Indexes
MySQL Temporary Tables
MySQL Copy Tables
MySQL Synonyms
MySQL Sequences
MySQL Handling Duplicates
MySQL and SQL Injection
MySQL Export Data
MySQL Import Data
MySQL Functions
MySQL Operators
MySQL Quick Reference
MySQL Quiz
MySQL Programming Languages
MySQL Node.js
MySQL PHP
MySQL Python
MySQL Java
Online Examples
·HTML Examples
·CSS Examples
·JavaScript Examples
·Ajax Examples
·jQuery Examples
·XML Examples
·Java Examples
Character Sets & Tools
· HTML Character Set Settings
· HTML ASCII Character Set
· JS Obfuscation/Encryption
· PNG/JPEG Image Compression
· HTML Color Picker
· JSON Formatter
· Random Number Generator
Latest Updates
· AI Ethics & Safety
· How AI Works
· AI Tools Panorama
· Prompt Engineering
· Introduction to AI
· AI Beginner Tutorial
· Large Model Multimodal (M...
Site Information
· Feedback
· Disclaimer
· About Us
· Article Archive
Follow WeChat
My Bookmarks
Mark Article
Browsing History
Clear All
No records yet
YouTip