Master MySQL Queries with Navicat: Create, Design, and Query Databases

Introduction

Hello, I am the IT sharing host nicknamed PiPi. Today I will talk about query operations in MySQL.

Preface

Previously we learned basic commands such as creating databases and tables. In practice, we often use visual tools like Navicat for MySQL to simplify these operations.

Navicat can conveniently help us create databases, create tables, and modify tables.

Navicat for MySQL Simple Example

Create Database

After connecting to a MySQL service with Navicat: Right‑click → New Database Select the new database information:

Character set: utf8mb4 – UTF‑8 Unicode (supports Emoji)

Collation:

utf8mb4_general_ci

Create Table

Right‑click Table → New Table

Define the table structure (see screenshot).

Save the table ( Ctrl+S).

Design Table

In real projects we often need to modify table structures, i.e., design tables. Right‑click Table → Design Table Modify the table and save.

Query

While visual tools are convenient for creating and modifying databases, they cannot perform queries; native SQL is required and offers the most flexibility.

Requirement Analysis

Assume we need a student management system with a student table (name, age, gender, etc.) and a class table . Each student belongs to a class.

Table Structure Diagram

Table Structure Code

school_test.sql

SELECT Queries

Query All

-- Syntax
SELECT * FROM <table_name>;
-- Example: query the student table
SELECT * FROM student;

WHERE Condition Query

-- Syntax
SELECT * FROM <table_name> WHERE <condition>;
-- Example: query id = 1
SELECT * FROM student WHERE id = 1;

WHERE Multiple Conditions (AND)

-- Syntax
SELECT * FROM student WHERE <condition1> AND <condition2> ...;
-- Example: query male student named "琪琪"
SELECT * FROM student WHERE name = "琪琪" AND gender = "男";

Select Specific Columns

SELECT * FROM <table_name>

selects all columns. To select particular columns, list them after SELECT, e.g., SELECT id, name, age FROM student. Columns can also be given aliases:

SELECT id, name AS "姓名", age FROM student;

Summary

This tutorial demonstrated how to use Navicat for MySQL to create databases, create tables, and design tables. It then introduced a simple student‑class schema and showed how to perform native SQL SELECT queries, including retrieving all rows, filtering with WHERE, combining conditions with AND, and selecting specific columns or using column aliases.