I. Basic Queries: Retrieving Data from Tables
1.1 Syntax Format
SELECT ColumnName FROM TableName;1.2 Practical Examples
Assuming a table named `student` exists, containing fields `id`, `name`, `sex`, `age`, and `class_num`, a basic query is demonstrated:
# Query all fields (* represents all fields)
SELECT * FROM student;
# Query specific fields (name, sex)
SELECT name, sex FROM student;
# Query specific fields (name, sex, age)
SELECT name, sex, age FROM student;
Note: MySQL is case-insensitive; keywords such as `SELECT` and `From` can be in either uppercase or lowercase, but it is recommended to maintain a consistent style (e.g., uppercase keywords, lowercase table and field names).
II. Conditional Query: Filtering data that meets certain conditions
2.1 Syntax Format
SELECT 字段列表 FROM 表名 WHERE 条件表达式;2.2 Commonly Used Conditional Operators
| Operators | meaning | Example |
|---|---|---|
= | equal | name = 'John Smith' |
> | Greater than | age > 20 |
< | Less than | age < 20 |
>= | Greater than or equal to | age >= 20 |
<= | Less than or equal to | age <= 20 |
<> or != | Not equal to | age <> 20 or age != 20 |
BETWEEN...AND... | Interval (closed interval) | age BETWEEN 20 AND 22 |
IN(value list) | Matching multiple values | id IN (1,2,3,4) or name IN ('John Smith','Emily Johnson') |
IS NULL | Field is empty | class_num IS NULL |
IS NOT NULL | Field is not empty | class_num IS NOT NULL |
AND | Logical AND (multiple conditions must be met simultaneously) | class_num = '20201001' AND sex = 'boy' |
OR | Logical OR (one of multiple conditions must be met) | sex = 'girl' OR class_num = '20201001' |
Display brief information
2.3 Practical Examples
# Query student information with the name "John Smith"
SELECT * FROM student WHERE name = 'John Smith';
# Query student information with the age greater than 20
SELECT * FROM student WHERE age > 20;
# Query student information with the age not equal to 20
SELECT * FROM student WHERE age <> 20; # or age != 20;
# Query student information with the age between 20 and 22
SELECT * FROM student WHERE age BETWEEN 20 AND 22;
# Query student information with id 1, 2, 3, 4
SELECT * FROM student WHERE id IN (1,2,3,4);
# Query student information with the names "John Smith", "Emily Johnson", and "Michael Brown"
SELECT * FROM student WHERE name IN ('John Smith','Emily Johnson','Michael Brown');
# Query student information with a null class_num field
SELECT * FROM student WHERE class_num IS NULL
; Query information for male students in class 20201001:
`SELECT * FROM student WHERE class_num = '20201001' AND sex = 'boy';
` # Query information for male students in class 20201001 or female students in other classes:
`SELECT * FROM student WHERE (sex = 'girl' AND class_num != '20201001') OR (class_num = '20201001' AND sex = 'boy');`
III. Fuzzy Search: Search by Keyword
3.1 Syntax Format
SELECT columnName FROM tableName WHERE field LIKE 'Wildcard expression';3.2 Wildcard Explanation
– `%`: Matches any number of characters (including zero);
– `_`: Matches a single character.
3.3 Practical Examples
# Query students whose names contain "Smith" (e.g., "John Smith", "Smith Brown")
SELECT * FROM student WHERE name LIKE '%Smith%';
# Query students whose last name is "Smith" (e.g., "Smith Brown")
SELECT * FROM student WHERE name LIKE 'Smith %';
# Query students whose second name is "Michael" (e.g., "John Michael")
SELECT * FROM student WHERE name LIKE '_Michael%';
IV. Aggregate Queries: Performing statistical analysis on data.
4.1 Commonly Used Aggregate Functions
| function | meaning | Example |
|---|---|---|
SUM(field) | Summation | SUM(score)(Assuming a score field exists) |
AVG( | Find the average | AVG(age) |
MAX( | Find the maximum value | MAX(age) |
MIN( | Find the minimum value | MIN(age) |
COUNT( or COUNT(*) | Statistical record count | COUNT(*)(Count all records) COUNT(class_num)(Count records where class_num is not NULL) |
4.2 Practical Examples
# Query the maximum age of students
: SELECT MAX(age) FROM student;
# Query the average age of students
: SELECT AVG(age) FROM student;
# Query the total number of records in the student table (including NULL fields):
SELECT COUNT(*) FROM student;
# Query the number of records where the class_num field is not NULL:
SELECT COUNT(class_num) FROM student;
V. Grouped Queries: Categorizing and Statistically Based on Fields
5.1 Syntax Format
SELECT 'Aggregate field'/'Group field' FROM tableName GROUP BY 'group field';5.2 Practical Examples
# Query the average age of each class
SELECT AVG(age) AS avg_age, class_num
FROM student
GROUP BY class_num;
# Query the number of students in each class and by gender
SELECT COUNT(*) AS sex_count, sex, class_num
FROM student
GROUP BY class_num, sex;
VI. Sorting and Querying: Controlling the order in which data is displayed.
6.1 Syntax Format
SELECT columnName FROM tableName ORDER BY 'sort field' [ASC|DESC];– `ASC`: Ascending order (default, can be omitted);
– `DESC`: Descending order.
6.2 Practical Examples
# Sort by age in ascending order:
SELECT * FROM student ORDER BY age;
# Sort by age in descending order:
SELECT * FROM student ORDER BY age DESC;
# Sort by multiple fields: first by class in ascending order, then by age in descending order:
SELECT * FROM student ORDER BY class_num ASC, age DESC;
7. Pagination query: Limit the number of query results
7.1 Syntax Format
SELECT columnName FROM tableName LIMIT 'starting index', 'number of items per page';– The starting index starts from `0`;
– If only one parameter is specified, representing “number of items per page”, the default starting index is `0`.
7.2 Practical Example (Assuming 5 data entries are displayed per page)
# First page (index 0, retrieve 5 records)
SELECT * FROM student LIMIT 0,5;
# Second page (index 5, retrieve 5 records)
SELECT * FROM student LIMIT 5,5;
# Third page (index 10, retrieve 5 records)
SELECT * FROM student LIMIT 10,5;
# Simplified version (same effect)
SELECT * FROM student LIMIT 5 OFFSET 0; # First page
SELECT * FROM student LIMIT 5 OFFSET 5; # Second page
SELECT * FROM student LIMIT 5 OFFSET 10; # Third page
Front-end integration logic: If the front-end passes `pageIndex` (page number, starting from 1) and `pageSize` (number of items per page), then the SQL can be written as:
SELECT * FROM student LIMIT pageSize OFFSET (pageIndex-1)*pageSize;8. Multi-table join query: Joining data from multiple tables
8.1 Cartesian Product Phenomenon
If multiple tables are joined without a join condition, the result will be the product of “number of records in Table 1 × number of records in Table 2”, which is called a Cartesian product. This must be avoided by using a join condition.
8.2 Connection Method
(1) Inner join (`INNER JOIN` or `JOIN`)
– Only query the data in the intersection of the two tables.
– Syntax: `SELECT field FROM table1 INNER JOIN table2 ON join condition;`
(2) Left outer join (`LEFT JOIN` or `LEFT OUTER JOIN`)
– Query all records in the left table; fields in the right table that cannot be matched will be NULL.
– Syntax: `SELECT field FROM table1 LEFT JOIN table2 ON join condition;`
(3) Right outer join (`RIGHT JOIN` or `RIGHT OUTER JOIN`)
– Query all records in the right table; fields not matched in the left table will be returned as NULL.
– Syntax: `SELECT field FROM table1 RIGHT JOIN table2 ON join condition;`
8.3 Practical Example (Assume there are `student` and `class` tables, and `student.class_num` is associated with `class.class_num`)
# Implicit Join (Not recommended, prone to Cartesian product)
SELECT s.name, c.class_name
FROM student s, class c
WHERE s.class_num = c.class_num AND s.name = 'John Smith';
# Explicit Inner Join (Recommended)
SELECT s.name, c.class_name
FROM student s
INNER JOIN class c
ON s.class_num = c.class_num
WHERE s.name = 'John Smith';
# Left Outer Join: Query all students (including those without classes) and their corresponding class names
SELECT s.*, c.class_name
FROM student s
LEFT JOIN class c
ON s.class_num = c.class_num;
# Right Outer Join: Query all classes (including classes without students) and their corresponding students
SELECT c.*, s.name
FROM student s
RIGHT JOIN class c
ON s.class_num = c.class_num;
IX. Summary: Core Knowledge Points of SQL Queries
1. Basic query: `SELECT fields FROM table;`, where `*` represents all fields;
2. Conditional Queries: `WHERE` is followed by a conditional expression, supporting operators such as `=`, `>`, `BETWEEN…AND…`, `IN`, `IS NULL`, as well as logical combinations of `AND` and `OR`;
3. Fuzzy search: `LIKE` combined with `%` (any number of characters) and `_` (single character) enables keyword search;
4. Aggregate queries: `SUM`, `AVG`, `MAX`, `MIN`, `COUNT` are used to perform statistical analysis on the data;
5. Grouped queries: `GROUP BY` categorizes data by field and combines it with aggregate functions to achieve grouped statistics;
6. Sorting query: `ORDER BY` sorts by field in ascending order (`ASC`) or descending order (`DESC`);
7. Pagination query: `LIMIT` controls the starting position and number of query results;
8. Multi-table joins: Data from multiple tables can be joined using `JOIN` (inner join, left outer join, right outer join). `ON` must be used to avoid Cartesian products.
Mastering these SQL query techniques will enable you to handle the vast majority of data query needs in a MySQL database. Further exploration of subqueries, views, stored procedures, and other advanced topics will allow you to further enhance your database operation capabilities.