MySQL查询数据

基本SELECT语法

SELECT语句用于从MySQL数据库表中查询数据。

简单查询

-- Select all columns from a table
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with column aliases
SELECT name AS user_name, email AS user_email FROM users;

-- Select with expressions
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Select with calculations
SELECT product_name, price, price * 0.9 AS discounted_price FROM products;

WHERE子句过滤

-- Equality condition
SELECT * FROM users WHERE status = 'active';

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM orders WHERE quantity >= 10;
SELECT * FROM users WHERE age < 30;

-- IN operator
SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');

-- BETWEEN operator
SELECT * FROM products WHERE price BETWEEN 50 AND 200;

-- LIKE operator for pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE name LIKE 'iPhone%';

-- IS NULL
SELECT * FROM users WHERE phone IS NULL;

-- Combining conditions
SELECT * FROM users WHERE status = 'active' AND age >= 18;
SELECT * FROM products WHERE (category = 'Electronics' OR category = 'Computers') AND price < 1000;

排序

-- Ascending order (default)
SELECT * FROM users ORDER BY name;

-- Explicit ascending
SELECT * FROM users ORDER BY name ASC;

-- Descending order
SELECT * FROM products ORDER BY price DESC;

-- Multiple column sorting
SELECT * FROM users ORDER BY country ASC, name ASC;

-- Sorting by position
SELECT name, email, age FROM users ORDER BY 3 DESC;

-- Sorting with expressions
SELECT name, price, price * 0.9 AS sale_price FROM products ORDER BY sale_price DESC;

-- NULL handling in sorting
SELECT * FROM users ORDER BY phone IS NULL, phone;

限制和偏移

-- Limit number of results
SELECT * FROM users LIMIT 10;

-- Skip first 10 results
SELECT * FROM users LIMIT 10 OFFSET 10;

-- Alternative syntax
SELECT * FROM users LIMIT 10, 10;

-- Pagination example (page 3 with 10 items per page)
SELECT * FROM products LIMIT 10 OFFSET 20;

-- Get latest records
SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;

-- Random sampling
SELECT * FROM users ORDER BY RAND() LIMIT 10;

去重

-- Get unique values
SELECT DISTINCT country FROM users;

-- Multiple columns
SELECT DISTINCT country, city FROM users;

-- With WHERE clause
SELECT DISTINCT category FROM products WHERE price > 100;

-- Counting distinct values
SELECT COUNT(DISTINCT country) AS unique_countries FROM users;

聚合函数

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count specific column (non-NULL values)
SELECT COUNT(phone) FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT country) FROM users;

-- Sum values
SELECT SUM(amount) FROM orders;

-- Average value
SELECT AVG(price) FROM products;

-- Minimum value
SELECT MIN(price) FROM products;

-- Maximum value
SELECT MAX(price) FROM products;

-- Multiple aggregates
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order,
    MIN(amount) AS min_order,
    MAX(amount) AS max_order
FROM orders;

分组

-- Group by single column
SELECT country, COUNT(*) AS user_count 
FROM users 
GROUP BY country;

-- Group by multiple columns
SELECT country, city, COUNT(*) AS user_count 
FROM users 
GROUP BY country, city;

-- With HAVING clause
SELECT category, AVG(price) AS avg_price 
FROM products 
GROUP BY category 
HAVING AVG(price) > 100;

-- With WHERE and ORDER BY
SELECT 
    status, 
    COUNT(*) AS count,
    AVG(amount) AS avg_amount
FROM orders 
WHERE created_at >= '2024-01-01'
GROUP BY status 
HAVING COUNT(*) > 10
ORDER BY avg_amount DESC;

子查询

-- Simple subquery
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- Correlated subquery
SELECT p1.name, p1.price 
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price) 
    FROM products p2 
    WHERE p2.category = p1.category
);

-- EXISTS subquery
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id
);

-- FROM subquery
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_avg
WHERE avg_price > 100;

连接操作

-- INNER JOIN
SELECT u.name, o.order_number, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.order_number
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.name, o.order_number
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Self JOIN
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Multiple JOINs
SELECT 
    u.name,
    o.order_number,
    p.product_name,
    oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

联合

-- Combine results from multiple queries
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

-- UNION ALL (includes duplicates)
SELECT product_name FROM products WHERE category = 'Electronics'
UNION ALL
SELECT product_name FROM products WHERE price < 100;

-- With ORDER BY
SELECT name, 'customer' AS type FROM customers
UNION
SELECT name, 'supplier' AS type FROM suppliers
ORDER BY name;

条件表达式

-- CASE expression
SELECT 
    name,
    price,
    CASE 
        WHEN price < 50 THEN 'Cheap'
        WHEN price < 200 THEN 'Moderate'
        ELSE 'Expensive'
    END AS price_category
FROM products;

-- Simple CASE
SELECT 
    name,
    status,
    CASE status
        WHEN 'active' THEN '活跃'
        WHEN 'inactive' THEN '不活跃'
        WHEN 'pending' THEN '待定'
        ELSE '未知'
    END AS status_cn
FROM users;

-- IF function
SELECT name, IF(age >= 18, 'Adult', 'Minor') AS age_group FROM users;

-- IFNULL
SELECT name, IFNULL(phone, 'No phone') AS contact FROM users;

-- COALESCE
SELECT name, COALESCE(phone, email, 'No contact') AS contact FROM users;

日期和时间函数

-- Current date/time
SELECT NOW(), CURDATE(), CURTIME();

-- Date extraction
SELECT 
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    DAY(created_at) AS day
FROM orders;

-- Date arithmetic
SELECT 
    created_at,
    DATE_ADD(created_at, INTERVAL 7 DAY) AS week_later,
    DATE_SUB(created_at, INTERVAL 1 MONTH) AS month_ago
FROM orders;

-- Date difference
SELECT 
    name,
    DATEDIFF(NOW(), created_at) AS days_since_registration
FROM users;

字符串函数

-- String manipulation
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(email) AS email_upper,
    LOWER(name) AS name_lower,
    LENGTH(description) AS desc_length,
    SUBSTRING(phone, 1, 3) AS area_code,
    REPLACE(email, '@gmail.com', '@example.com') AS new_email,
    TRIM(name) AS trimmed_name
FROM users;

模式匹配

-- LIKE patterns
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE name LIKE 'iPhone%';
SELECT * FROM users WHERE name LIKE '_ohn%';

-- REGEXP (regular expressions)
SELECT * FROM users WHERE email REGEXP '^[a-z]+@[a-z]+\\.com$';
SELECT * FROM products WHERE sku REGEXP '^[A-Z]{3}-[0-9]{4}$';

WITH（公用表表达式）

-- Non-recursive CTE
WITH high_value_customers AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 1000
)
SELECT c.name, hvc.total_spent
FROM customers c
INNER JOIN high_value_customers hvc ON c.id = hvc.customer_id;

-- Multiple CTEs
WITH 
    monthly_sales AS (
        SELECT 
            DATE_FORMAT(order_date, '%Y-%m') AS month,
            SUM(amount) AS total
        FROM orders
        GROUP BY month
    ),
    avg_monthly AS (
        SELECT AVG(total) AS avg_total
        FROM monthly_sales
    )
SELECT ms.month, ms.total, am.avg_total
FROM monthly_sales ms
CROSS JOIN avg_monthly am
WHERE ms.total > am.avg_total;

限制变化

-- Top N by value

-- Top N with offset (pagination)

-- Random sample

-- First and last

-- With GROUP BY

综合示例

    -- Column selection with aliases
    
    -- Aggregates
    
    -- Conditional expressions
    
    -- Date functions
    
    -- String functions

小结

SELECT语句是MySQL中数据检索的基础：

基本查询：使用带特定列和条件的SELECT 过滤：使用WHERE子句进行精确数据选择 排序：使用ORDER BY对结果排序 限制：使用LIMIT和OFFSET进行分页 聚合：COUNT、SUM、AVG、MIN、MAX 分组：带HAVING的GROUP BY 连接：INNER、LEFT、RIGHT、SELF连接 子查询：嵌套查询实现复杂逻辑 表达式：CASE、IF、COALESCE实现条件逻辑 CTE：使用WITH子句实现可重用子查询

---

上一个：插入数据

下一个：WHERE子句