SQL相关

SQL 系统学习与知识体系大纲

一、 引言与概述

• 1.1 SQL是什么？
  • 定义：结构化查询语言
  • 用途：用于管理关系数据库系统（RDBMS）
  • 标准：ANSI SQL 与各厂商扩展（T-SQL, PL/SQL等）
• 1.2 数据库基本概念
  • 数据库 (Database)
  • 表 (Table)
  • 行/记录 (Row/Record)
  • 列/字段 (Column/Field)
  • 主键 (Primary Key) 与外键 (Foreign Key)
  • 索引 (Index)

二、 环境搭建与初步实践

• 2.1 常见数据库管理系统 (DBMS) 简介
  • MySQL / MariaDB
  • PostgreSQL
  • SQLite
  • Microsoft SQL Server
  • Oracle Database
• 2.2 选择与安装一个DBMS (以MySQL/PostgreSQL为例)
• 2.3 使用图形化工具 (GUI)
  • DBeaver, DataGrip, Navicat, pgAdmin, MySQL Workbench
• 2.4 命令行客户端基础操作

三、 SQL 核心语法 (按功能分类)

• 3.1 数据定义语言 (DDL - Data Definition Language)
  • CREATE: 创建数据库、表、索引、视图
  • ALTER: 修改表结构（添加、删除、修改列）
  • DROP: 删除数据库、表、视图
  • TRUNCATE: 快速清空表数据
• 3.2 数据操作语言 (DML - Data Manipulation Language)
  • SELECT: 查询数据 (最核心)
  • INSERT: 插入新数据
  • UPDATE: 更新现有数据
  • DELETE: 删除数据
• 3.3 数据控制语言 (DCL - Data Control Language)
  • GRANT: 授予用户权限
  • REVOKE: 撤销用户权限
• 3.4 事务控制语言 (TCL - Transaction Control Language)
  • COMMIT: 提交事务
  • ROLLBACK: 回滚事务
  • SAVEPOINT: 设置事务保存点
  • BEGIN TRANSACTION: 开始事务 (部分数据库)

四、 SELECT 查询深度解析

• 4.1 基础查询结构
  • SELECT ... FROM ...
  • 使用 AS 设置别名
  • 去重：DISTINCT 关键字
• 4.2 过滤数据 (WHERE 子句)
  • 比较运算符 (=, !=, >, <, >=, <=)
  • 逻辑运算符 (AND, OR, NOT)
  • BETWEEN ... AND ...
  • IN (...)
  • LIKE 与通配符 (%, _)
  • IS NULL / IS NOT NULL
• 4.3 排序与限制 (ORDER BY & LIMIT)
  • ORDER BY 单列、多列排序 (ASC, DESC)
  • LIMIT 与 OFFSET (分页查询)
  • 注意：不同数据库的分页语法差异（如 SQL Server 用 TOP / FETCH， Oracle用 ROWNUM）
• 4.4 聚合函数与分组 (GROUP BY & HAVING)
  • 聚合函数：COUNT(), SUM(), AVG(), MAX(), MIN()
  • GROUP BY 子句的使用
  • HAVING 子句 vs WHERE 子句（过滤分组）
• 4.5 连接查询 (JOIN) - 重中之重
  • 关系模型复习：一对一，一对多，多对多
  • INNER JOIN: 内连接
  • LEFT (OUTER) JOIN: 左外连接
  • RIGHT (OUTER) JOIN: 右外连接
  • FULL (OUTER) JOIN: 全外连接
  • CROSS JOIN: 笛卡尔积
  • 自连接 (Self Join)
  • 多表连接
• 4.6 子查询 (Subquery)
  • 标量子查询 (返回单一值)
  • 行子查询 (返回单行)
  • 列子查询 (返回单列)
  • 表子查询 (返回结果集)
  • 相关子查询 vs 非相关子查询
  • 在 SELECT, FROM, WHERE, HAVING 中使用子查询
• 4.7 集合运算 (Set Operations)
  • UNION / UNION ALL: 并集
  • INTERSECT: 交集
  • EXCEPT / MINUS: 差集

五、 函数

• 5.1 标量函数 (处理单值，返回单值)
  • 字符串函数：CONCAT(), SUBSTRING(), UPPER(), LOWER(), LENGTH()/LEN(), TRIM()
  • 数值函数：ROUND(), CEIL()/CEILING(), FLOOR(), ABS(), MOD()
  • 日期时间函数：NOW()/GETDATE(), CURDATE(), DATE_ADD()/DATEADD(), DATEDIFF(), EXTRACT()/DATEPART()
  • 转换函数：CAST(), CONVERT(), FORMAT()
• 5.2 窗口函数 (Window Functions) - 高级主题
  • OVER() 子句：PARTITION BY 和 ORDER BY
  • 排名函数：ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
  • 聚合函数作为窗口函数：SUM() OVER(...)
  • 取值函数：LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

六、 数据完整性、约束与高级表设计

• 6.1 约束 (Constraints)
  • PRIMARY KEY: 主键约束
  • FOREIGN KEY: 外键约束与参照完整性
  • UNIQUE: 唯一约束
  • NOT NULL: 非空约束
  • CHECK: 检查约束
  • DEFAULT: 默认值约束
• 6.2 规范化 (Normalization) - 数据库设计理论
  • 第一范式 (1NF)
  • 第二范式 (2NF)
  • 第三范式 (3NF)
  • （简介）巴斯-科德范式 (BCNF)
• 6.3 索引优化
  • 索引的作用与原理（B树，哈希）
  • 创建索引：CREATE INDEX
  • 索引类型：唯一索引、复合索引、全文索引
  • 索引的优点与代价（何时使用，何时避免）

七、 视图、存储过程、触发器

• 7.1 视图 (VIEW)
  • 创建视图：CREATE VIEW
  • 可更新视图的条件
  • 视图的优点（简化查询、安全性）
• 7.2 存储过程 (Stored Procedure)
  • 概念：预编译的SQL语句集合
  • 创建与调用
  • 输入参数与输出参数
  • 流程控制语句 (IF...ELSE, WHILE, CASE)
• 7.3 触发器 (TRIGGER)
  • 触发时机：BEFORE / AFTER (INSTEAD OF)
  • 触发事件：INSERT, UPDATE, DELETE
  • 访问新旧数据：OLD 和 NEW 伪记录

八、 高级主题与性能

• 8.1 事务与并发控制
  • ACID 属性
  • 事务隔离级别 (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)
  • 并发问题：脏读、不可重复读、幻读
• 8.2 查询性能优化
  • 使用 EXPLAIN 分析查询执行计划
  • 避免使用 SELECT *
  • 优化 JOIN 和子查询
  • 合理使用索引
• 8.3 常用数据库特有功能简介
  • MySQL: 存储引擎 (InnoDB, MyISAM)
  • PostgreSQL: 高级数据类型 (数组、JSON、GIS)，CTE
  • SQL Server: CTE, 分页 (OFFSET-FETCH)

九、 实践与资源

• 9.1 学习路径建议
  • 阶段1：掌握 DDL, DML, 基础 SELECT
  • 阶段2：深入 JOIN, 子查询, 聚合
  • 阶段3：学习索引、设计、事务
  • 阶段4：接触窗口函数、性能优化
• 9.2 在线练习平台
  • LeetCode (数据库题库)
  • HackerRank (SQL模块)
  • SQLZoo
  • Codecademy, DataCamp
• 9.3 推荐书籍与文档
  • 《SQL必知必会》
  • 《高性能MySQL》
  • 官方文档是最好的参考
• 9.4 项目实践想法
  • 设计一个博客系统的数据库
  • 设计一个电商平台的数据库 (用户、商品、订单、购物车)
  • 使用公开数据集进行数据分析练习