Mariadb的单表查询方法

# MariaDB的单表查询方法

## 目录
1. [引言](#引言)
2. [基础查询语法](#基础查询语法)
3. [SELECT语句详解](#select语句详解)
4. [WHERE子句与条件过滤](#where子句与条件过滤)
5. [排序与分组](#排序与分组)
6. [聚合函数应用](#聚合函数应用)
7. [高级查询技巧](#高级查询技巧)
8. [性能优化建议](#性能优化建议)
9. [实战案例解析](#实战案例解析)
10. [总结](#总结)

---

## 引言
MariaDB作为MySQL的重要分支，在单表查询方面提供了丰富而强大的功能...(约1500字)

---

## 基础查询语法
### 1.1 最简单的SELECT查询
```sql
SELECT * FROM employees;

1.2 选择特定列

SELECT first_name, last_name FROM employees;

1.3 使用列别名

SELECT product_id AS "产品ID", 
       product_name AS "产品名称" 
FROM products;

…(约2000字)

---

SELECT语句详解

2.1 DISTINCT去重

SELECT DISTINCT department_id FROM employees;

2.2 表达式计算

SELECT product_name, 
       unit_price * 0.9 AS discounted_price 
FROM products;

2.3 字符串函数

SELECT CONCAT(first_name, ' ', last_name) AS full_name 
FROM employees;

…(约2500字)

---

WHERE子句与条件过滤

3.1 比较运算符

SELECT * FROM orders 
WHERE order_date > '2023-01-01';

3.2 逻辑运算符

SELECT * FROM products 
WHERE category_id = 5 AND price > 100;

3.3 IN和BETWEEN

SELECT * FROM employees 
WHERE department_id IN (3,5,7);

SELECT * FROM sales 
WHERE amount BETWEEN 1000 AND 5000;

…(约3000字)

---

排序与分组

4.1 ORDER BY排序

SELECT * FROM customers 
ORDER BY last_name DESC, first_name ASC;

4.2 GROUP BY分组

SELECT department_id, COUNT(*) AS emp_count 
FROM employees 
GROUP BY department_id;

4.3 HAVING筛选

SELECT product_id, SUM(quantity) AS total_sold 
FROM order_details 
GROUP BY product_id 
HAVING total_sold > 100;

…(约2500字)

---

聚合函数应用

5.1 常用聚合函数

SELECT 
  AVG(salary) AS avg_salary,
  MAX(hire_date) AS newest_hire,
  COUNT(*) AS total_employees
FROM employees;

5.2 分组聚合

SELECT 
  product_category,
  SUM(sales_amount) AS category_sales
FROM sales_data
GROUP BY product_category;

…(约2000字)

---

高级查询技巧

6.1 子查询

SELECT * FROM products 
WHERE category_id IN (
  SELECT category_id FROM categories 
  WHERE category_name LIKE '%电子%'
);

6.2 正则表达式

SELECT * FROM contacts 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail.com$';

…(约1500字)

---

性能优化建议

7.1 索引使用

-- 创建索引
CREATE INDEX idx_employee_name ON employees(last_name, first_name);

-- 查看执行计划
EXPLN SELECT * FROM employees WHERE last_name = 'Smith';

7.2 查询重构

-- 优化前
SELECT * FROM large_table WHERE YEAR(create_time) = 2023;

-- 优化后
SELECT * FROM large_table 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

…(约2000字)

---

实战案例解析

8.1 电商产品查询

-- 查询热销商品
SELECT p.product_id, p.product_name, 
       SUM(od.quantity) AS total_sold
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_sold DESC
LIMIT 10;

8.2 员工数据分析

-- 各部门薪资统计
SELECT 
  d.department_name,
  COUNT(e.employee_id) AS employee_count,
  ROUND(AVG(e.salary),2) AS avg_salary,
  MAX(e.salary) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

…(约2500字)

---

总结

本文全面介绍了MariaDB单表查询的各类方法…(约1000字) “`

注：实际内容需扩展补充技术细节、示例说明、性能分析等内容以达到完整篇幅。建议： 1. 每个语法点配3-5个变体示例 2. 添加注意事项和常见错误 3. 包含性能对比测试数据 4. 增加可视化执行计划解析 5. 补充安全查询的最佳实践