SQL语法基础，进阶，高级sql语句学习

一、基础语法示例

1. 多列查询

   SELECT username, email FROM users;  -- 查询用户名和邮箱（基础查询）
   

2. 条件筛选（范围）

   SELECT * FROM orders WHERE amount BETWEEN 100 AND 500;  -- 金额在100到500之间的订单
   

3. 模糊匹配

   SELECT * FROM products WHERE name LIKE '%手机%';  -- 名称包含“手机”的商品
   

4. 插入多行数据

   INSERT INTO departments (name) VALUES ('销售部'), ('技术部');  -- 批量插入部门
   

5. 更新多列

   UPDATE employees SET salary = salary * 1.1, position = '高级工程师' WHERE department = '研发部';  -- 调薪并更新职位
   

6. 删除符合条件的记录

   DELETE FROM logs WHERE log_level = 'ERROR' AND created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);  -- 删除3个月前的错误日志
   

7. 排序与分页

   SELECT * FROM customers ORDER BY total_spent DESC LIMIT 10;  -- 查询消费最高的前10名客户
   

8. 去重统计

   SELECT DISTINCT product_category FROM inventory;  -- 获取所有不重复的商品分类
   

9. 简单聚合函数

   SELECT MAX(salary) AS highest_salary FROM employees;  -- 查询最高薪资
   

10. 别名与表达式

    SELECT price * 0.8 AS discounted_price FROM products;  -- 计算8折后价格
    

---

二、进阶语法示例

1. 右连接示例

   SELECT o.order_id, p.name 
   FROM products p 
   RIGHT JOIN order_items o ON p.id = o.product_id;  -- 保留所有商品，即使未被购买
   

2. 多表全外连接

   SELECT u.name, d.name 
   FROM users u 
   FULL JOIN departments d ON u.department_id = d.id;  -- 合并用户与部门表，保留双方数据
   

3. 多行子查询

   SELECT * FROM employees 
   WHERE department_id IN (SELECT id FROM departments WHERE location = '上海');  -- 查询上海分部的员工
   

4. 带条件的分组

   SELECT department, AVG(salary) 
   FROM employees 
   GROUP BY department 
   HAVING AVG(salary) > 20000;  -- 平均薪资超2万的部门
   

5. UNION合并结果集

   SELECT name FROM customers 
   UNION 
   SELECT name FROM suppliers;  -- 合并客户与供应商名称（去重）
   

6. 窗口函数计算累计值

   SELECT order_date, SUM(amount) OVER (ORDER BY order_date) AS cumulative_sales 
   FROM orders;  -- 按时间计算累计销售额
   

7. 动态条件分支（CASE）

   SELECT 
       order_id, 
       CASE 
           WHEN amount > 1000 THEN '大额订单' 
           ELSE '小额订单' 
       END AS order_type 
   FROM orders;  -- 根据金额分类订单
   

8. 自连接查询上下级

   SELECT e.name AS employee, m.name AS manager 
   FROM employees e 
   LEFT JOIN employees m ON e经理ID = m.id;  -- 查询员工及其上级
   

9. 派生表优化查询

   SELECT * FROM (
       SELECT product_id, AVG(amount) AS avg_monthly 
       FROM orders 
       GROUP BY product_id
   ) AS derived 
   WHERE avg_monthly > 500;  -- 子查询筛选月均销量超500的商品
   

10. EXISTS子查询

    SELECT name FROM departments 
    WHERE EXISTS (
        SELECT 1 FROM employees 
        WHERE department_id = departments.id 
        AND hire_date < '2020-01-01'
    );  -- 查询有员工在2020年前入职的部门
    

---

三、高级语法示例

1. 窗口函数分组排名

   SELECT name, salary, 
          RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_rank 
   FROM employees;  -- 按部门内薪资排名
   

2. 公共表表达式（CTE）递归查询

   WITH RECURSIVE category_tree AS (
       SELECT id, name, parent_id 
       FROM categories 
       WHERE parent_id IS NULL
       UNION ALL
       SELECT c.id, c.name, c.parent_id 
       FROM categories c 
       JOIN category_tree ct ON c.parent_id = ct.id
   )
   SELECT * FROM category_tree;  -- 查询分类层级结构
   

3. 事务与回滚

   BEGIN TRANSACTION;
   INSERT INTO transactions (user_id, amount) VALUES (1, -100);
   UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
   IF @@ERROR <> 0 
       ROLLBACK;
   ELSE 
       COMMIT;  -- 事务处理转账操作
   

4. 动态SQL（存储过程）

   DELIMITER $$
   CREATE PROCEDURE SearchProducts(IN keyword VARCHAR(50))
   BEGIN
       SET @sql = CONCAT('SELECT * FROM products WHERE name LIKE "%', keyword, '%)');
       PREPARE stmt FROM @sql;
       EXECUTE stmt;
   END $$
   

5. 全文索引搜索

   SELECT * FROM articles 
   WHERE MATCH(content) AGAINST('MySQL 性能优化' IN NATURAL LANGUAGE MODE);  -- 全文内容搜索
   

6. 存储过程循环

   DELIMITER $$
   CREATE PROCEDURE GenerateMonthlyReport()
   BEGIN
       DECLARE i INT DEFAULT 1;
       WHILE i <= 12 DO
           INSERT INTO monthly_sales 
           SELECT MONTH(order_date) AS month, SUM(amount) 
           FROM orders 
           WHERE MONTH(order_date) = i 
           GROUP BY month;
           SET i = i + 1;
       END WHILE;
   END $$
   

7. 触发器自动更新

   CREATE TRIGGER update InventoryAfterOrder 
   AFTER INSERT ON orders 
   FOR EACH ROW 
   BEGIN
       UPDATE products 
       SET stock = stock - NEW.quantity 
       WHERE id = NEW.product_id;
   END;  -- 订单插入后自动扣减库存
   

8. 高级子查询嵌套

   SELECT * FROM employees 
   WHERE salary > (
       SELECT AVG(salary) 
       FROM employees 
       WHERE department_id = (
           SELECT department_id FROM employees WHERE name = '张三'
       )
   );  -- 查询薪资高于其所在部门平均的员工
   

9. 合并插入与更新（MERGE）

   MERGE INTO customer balances cb 
   USING (SELECT user_id, total_spent FROM temp_data) td 
   ON cb.user_id = td.user_id 
   WHEN MATCHED THEN UPDATE SET cb.current_balance = cb.current_balance + td.total_spent 
   WHEN NOT MATCHED THEN INSERT (user_id, current_balance) VALUES (td.user_id, td.total_spent);  -- 合并更新客户余额
   

10. 复杂视图创建

    CREATE VIEW department_stats AS 
    SELECT d.name AS department, 
           COUNT(e.id) AS employee_count, 
           AVG(e.salary) AS avg_salary 
    FROM departments d 
    LEFT JOIN employees e ON d.id = e.department_id 
    GROUP BY d.id;  -- 创建部门统计视图
    

---

总结

以上示例覆盖了SQL的核心操作、复杂查询及高级功能，可帮助巩固不同场景下的应用。如需进一步优化或扩展，建议结合具体业务需求调整语法细节。