SELECT Query

SELECT is the most commonly used and important statement in SQL, used to retrieve data from databases. This chapter详细介绍 will introduce various uses of the SELECT statement, allowing you to flexibly query the data you need from databases.

Basic Syntax

SELECT Statement Structure

SELECT column1, column2, ...
FROM table_name;

Query All Columns

Use the * wildcard to query all columns in a table:

SELECT * FROM users;

Note: In actual applications, you should explicitly specify the columns you need rather than using * to improve performance.

Query Specific Columns

Query only the columns you need:

SELECT name, age, city FROM users;

Query with Sorting

SELECT name, age
FROM users
ORDER BY age;

Query Single Table

Prepare Data

Suppose we have the following users table:

id	name	age	city	email
1	Zhang San	25	Beijing	zhang@example.com
2	Li Si	30	Shanghai	li@example.com
3	Wang Wu	28	Guangzhou	wang@example.com
4	Zhao Liu	35	Beijing	zhao@example.com
5	Qian Qi	22	Shenzhen	qian@example.com

Query All Data

SELECT * FROM users;

Result:

id	name	age	city	email
1	Zhang San	25	Beijing	zhang@example.com
2	Li Si	30	Shanghai	li@example.com
3	Wang Wu	28	Guangzhou	wang@example.com
4	Zhao Liu	35	Beijing	zhao@example.com
5	Qian Qi	22	Shenzhen	qian@example.com

Query Specific Columns

SELECT name, age FROM users;

Result:

name	age
Zhang San	25
Li Si	30
Wang Wu	28
Zhao Liu	35
Qian Qi	22

Using Aliases (AS)

Specify aliases for columns to make results more readable:

SELECT
    name AS 姓名,
    age AS 年龄,
    city AS 城市
FROM users;

Result:

姓名	年龄	城市
Zhang San	25	Beijing
Li Si	30	Shanghai
Wang Wu	28	Guangzhou
Zhao Liu	35	Beijing
Qian Qi	22	Shenzhen

Note: The AS keyword is optional and can be omitted:

SELECT name 姓名, age 年龄, city 城市
FROM users;

Distinct Query

DISTINCT Keyword

Use DISTINCT to eliminate duplicate rows:

SELECT DISTINCT city FROM users;

Result:

city
Beijing
Shanghai
Guangzhou
Shenzhen

Multi-column Distinct

Distinct on combination of multiple columns:

SELECT DISTINCT city, age FROM users;

Computed Columns

Basic Calculation

Calculate columns in a query:

SELECT
    name,
    age,
    age * 2 AS 双倍年龄
FROM users;

Result:

name	age	双倍年龄
Zhang San	25	50
Li Si	30	60
Wang Wu	28	56
Zhao Liu	35	70
Qian Qi	22	44

String Concatenation

-- MySQL
SELECT
    name,
    CONCAT(name, ' (', city, ')') AS 用户信息
FROM users;

-- PostgreSQL
SELECT
    name,
    name || ' (' || city || ')' AS 用户信息
FROM users;

Conditional Expression

Use CASE WHEN for conditional judgment:

SELECT
    name,
    age,
    CASE
        WHEN age < 25 THEN 'Young'
        WHEN age < 35 THEN 'Middle-aged'
        ELSE 'Senior'
    END AS 年龄分组
FROM users;

Result:

name	age	年龄分组
Zhang San	25	Middle-aged
Li Si	30	Middle-aged
Wang Wu	28	Middle-aged
Zhao Liu	35	Senior
Qian Qi	22	Young

Limit Result Count

LIMIT Clause

Limit the number of rows returned:

-- Return only the first 3 records
SELECT * FROM users LIMIT 3;

Result:

id	name	age	city	email
1	Zhang San	25	Beijing	zhang@example.com
2	Li Si	30	Shanghai	li@example.com
3	Wang Wu	28	Guangzhou	wang@example.com

LIMIT and OFFSET Combined

Pagination query:

-- Skip first 2, return next 3
SELECT * FROM users LIMIT 3 OFFSET 2;

MySQL Pagination Syntax

-- Return page 1 (2 per page)
SELECT * FROM users LIMIT 0, 2;

-- Return page 2 (2 per page)
SELECT * FROM users LIMIT 2, 2;

SQL Server Pagination Syntax

-- Use OFFSET-FETCH
SELECT * FROM users
ORDER BY id
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY;

Sort Results

ORDER BY Clause

Use ORDER BY to sort results:

-- Sort by age ascending
SELECT name, age FROM users ORDER BY age;

Result:

name	age
Qian Qi	22
Zhang San	25
Wang Wu	28
Li Si	30
Zhao Liu	35

Descending Sort

Use DESC keyword:

-- Sort by age descending
SELECT name, age FROM users ORDER BY age DESC;

Result:

name	age
Zhao Liu	35
Li Si	30
Wang Wu	28
Zhang San	25
Qian Qi	22

Multi-column Sort

-- Sort by city first, then by age
SELECT name, city, age
FROM users
ORDER BY city, age;

Sort by Column Alias

SELECT
    name,
    age * 2 AS 双倍年龄
FROM users
ORDER BY 双倍年龄 DESC;

Null Value Handling

COALESCE Function

Returns the first non-NULL value:

-- Suppose email column may have NULL values
SELECT
    name,
    COALESCE(email, 'Email not filled') AS 邮箱
FROM users;

NULLIF Function

Returns NULL if two parameters are equal:

SELECT
    name,
    age,
    NULLIF(age, 0) AS 检查年龄
FROM users;

String Functions

CONCAT Function

-- MySQL
SELECT CONCAT(name, ' - ', city) FROM users;

UPPER and LOWER

SELECT UPPER(name) FROM users;  -- Convert to uppercase
SELECT LOWER(name) FROM users;  -- Convert to lowercase

SUBSTRING

-- Substring
SELECT SUBSTRING(name, 1, 2) FROM users;  -- Take first 2 characters

LENGTH

-- Get string length
SELECT LENGTH(name) FROM users;

Date Functions

CURRENT_DATE / CURRENT_TIME

SELECT CURRENT_DATE;  -- Current date
SELECT CURRENT_TIME;  -- Current time

DATE_FORMAT (MySQL)

SELECT
    name,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS 注册日期
FROM users;

DATE_PART (PostgreSQL)

SELECT
    name,
    DATE_PART('year', created_at) AS 年份
FROM users;

Practical Examples

Example 1: Query Active Users

SELECT
    username,
    email,
    last_login
FROM users
WHERE last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY last_login DESC;

Example 2: Count Users by City

SELECT
    city AS 城市,
    COUNT(*) AS 用户数
FROM users
GROUP BY city
ORDER BY 用户数 DESC;

Example 3: Query High-Value Customers

SELECT
    u.username,
    u.email,
    SUM(o.total_amount) AS 总消费,
    COUNT(o.id) AS 订单数
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING 总消费 > 1000
ORDER BY 总消费 DESC;

Performance Optimization Tips

1. Avoid Using SELECT *

-- Not recommended: Query all columns
SELECT * FROM users;

-- Recommended: Query only needed columns
SELECT id, name, email FROM users;

2. Use WHERE Instead of HAVING

-- Not recommended: Filter in HAVING
SELECT city, COUNT(*)
FROM users
GROUP BY city
HAVING city = 'Beijing';

-- Recommended: Filter in WHERE
SELECT city, COUNT(*)
FROM users
WHERE city = 'Beijing'
GROUP BY city;

3. Use LIMIT Appropriately

-- Pagination query
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

4. Create Indexes for Common Queries

-- Create index for frequently queried columns
CREATE INDEX idx_user_name ON users(name);

Common Errors

Error 1: Column Name Spelling Error

-- Error: Column does not exist
SELECT nam FROM users;

-- Correct
SELECT name FROM users;

Error 2: Forgetting FROM Clause

-- Error: Missing FROM
SELECT name, age;

-- Correct
SELECT name, age FROM users;

Error 3: Improper Use of Column Alias

-- Error: In some databases, aliases cannot be used in WHERE
SELECT name, age * 2 AS 双倍年龄
FROM users
WHERE 双倍年龄 > 50;

-- Correct: Use original expression in WHERE
SELECT name, age * 2 AS 双倍年龄
FROM users
WHERE age * 2 > 50;

Summary

This chapter introduced basic usage of SELECT queries:

• Basic Syntax: SELECT column FROM table
• Query All Columns: Use * wildcard
• Query Specific Columns: Explicitly list needed columns
• Column Aliases: Use AS to specify column aliases
• Distinct Query: Use DISTINCT
• Computed Columns: Calculate in queries
• Limit Results: Use LIMIT and OFFSET
• Sort Results: Use ORDER BY
• String Functions: CONCAT, UPPER, LOWER, etc.
• Date Functions: CURRENT_DATE, DATE_FORMAT, etc.

SELECT is the most important statement in SQL. Mastering its various uses is the foundation for learning SQL. In the next chapter, we will learn how to use the WHERE clause to filter data.

Next Step: Learn WHERE Conditions to learn how to filter query results.