I. What is MySQL?¶
MySQL is an open-source relational database management system, often likened to a “digital warehouse” for storing and managing large volumes of structured data. Examples include e-commerce user information, school student records, and corporate order data—all of which can be stored and retrieved using MySQL. For programming or data analysis, mastering data extraction from MySQL is a fundamental skill.
II. Prerequisite: Connecting to the Database¶
Before running queries, ensure MySQL is installed and running. For beginners, we recommend using GUI tools (e.g., Navicat, SQLyog) or the command-line client (MySQL Command Line Client) to connect. Once connected, you’ll see a prompt like mysql>, where you can start entering query statements.
III. Understanding “Tables” and “Columns”¶
In MySQL, data is stored in “tables,” which consist of multiple “columns.” Each column represents a specific type of information (e.g., name, age). For example, consider a student table with the following columns:
- id: Student ID (numeric, unique identifier)
- name: Name (text)
- age: Age (numeric)
- class: Class (text)
IV. Basic Query: Retrieve All Data¶
Syntax: SELECT * FROM table_name;
- * denotes “all columns,” and FROM specifies the table name.
Example: Retrieve all student information:
SELECT * FROM student;
Executing this will display all rows and columns of data in the student table.
V. Query Specific Columns: View Only What You Need¶
To avoid viewing all columns (e.g., only name and age), specify the column names directly.
Syntax: SELECT column1, column2, ... FROM table_name;
Example: Retrieve student names and ages:
SELECT name, age FROM student;
The result will only show the name and age columns.
VI. Aliasing Columns: Clarify Result Labels¶
If column names are not intuitive (e.g., name should display as “Student Name”), use AS to alias columns:
Syntax: SELECT column_name AS alias FROM table_name;
Example: Alias name and age for clarity:
SELECT name AS 学生姓名, age AS 年龄 FROM student;
The result columns will now display as “学生姓名” (Student Name) and “年龄” (Age).
VII. Conditional Queries: Filter Data by Criteria¶
In practice, we rarely query all data; instead, we filter using conditions with the WHERE clause.
1. Comparison Operators: Filter Rows by Conditions¶
>(greater than),<(less than),=(equal),>=(greater than or equal),<=(less than or equal),!=or<>(not equal)
Example 1: Retrieve students older than 18:
SELECT * FROM student WHERE age > 18;
Example 2: Retrieve students in Class “一班” (note: strings require single quotes):
SELECT * FROM student WHERE class = '一班';
2. Logical Operators: Combine Multiple Conditions¶
AND(AND),OR(OR),NOT(NOT)
Example 1: Students older than 18 and in Class “一班”:
SELECT * FROM student WHERE age > 18 AND class = '一班';
Example 2: Students younger than 15 or in Class “二班”:
SELECT * FROM student WHERE age < 15 OR class = '二班';
3. Fuzzy Matching: Partial Text Matches¶
Use LIKE with wildcards: % (any number of characters) or _ (single character).
Example 1: Students with names starting with “张”:
SELECT * FROM student WHERE name LIKE '张%';
Example 2: Students with “明” in their names (any position):
SELECT * FROM student WHERE name LIKE '%明%';
VIII. Sorting: Order Results¶
Use ORDER BY to sort results. Default is ascending (ASC); append DESC for descending order.
Syntax: SELECT ... FROM table_name ORDER BY column_name [ASC/DESC];
Example: Sort students by age in descending order (older students first):
SELECT * FROM student ORDER BY age DESC;
IX. Limiting Results: Return Top N Rows¶
Use LIMIT to restrict the number of returned rows (e.g., for pagination or quick previews).
Syntax: SELECT ... FROM table_name LIMIT number; or LIMIT offset, number; (offset starts at 0).
Example 1: Retrieve the first 3 students:
SELECT * FROM student LIMIT 3;
Example 2: Retrieve 3 students starting from the 2nd row (offset = 1):
SELECT * FROM student LIMIT 1, 3;
X. Removing Duplicates: Exclude Repeated Rows¶
Use DISTINCT to eliminate duplicate rows in the result set.
Syntax: SELECT DISTINCT column_name FROM table_name;
Example: Retrieve all unique classes:
SELECT DISTINCT class FROM student;
XI. Comprehensive Example: Combine Multiple Operations¶
Requirement:
- Class = “一班”
- Age > 18
- Name starts with “李”
- Display only name and age
- Sort by age (descending)
- Limit to top 2 results
SQL Statement:
SELECT name AS 学生姓名, age AS 年龄
FROM student
WHERE class = '一班'
AND age > 18
AND name LIKE '李%'
ORDER BY age DESC
LIMIT 2;
XII. Practice Suggestions¶
- Create a simple table (e.g., a student table) and insert test data.
- Modify the examples: adjust conditions, change column names, or adjust sorting.
- Practice combining conditions (AND/OR/NOT) and fuzzy matching (
%and_).
By repeatedly experimenting, you’ll become familiar with MySQL query logic—from writing simple statements to flexibly extracting data.
Key Takeaway: The core of MySQL queries is “define requirements → select table → specify columns → add conditions → sort/limit”. With practice, you’ll master this efficiently!