SQL经典实例

第1章检索记录

1.1 检索所有行和列
- 知识点：使用SELECT *快速检索表中所有列；显式列出列名（如SELECT col1, col2）提高可读性和可控性，尤其在编程场景中更清晰。
1.2 筛选行
- 知识点：通过WHERE子句过滤符合条件的行，支持常用运算符（=、<、>等），是数据筛选的基础。
1.3 查找满足多个查询条件的行
- 知识点：使用AND、OR和括号组合多个条件，注意逻辑优先级，确保条件正确分组。
1.4 筛选列
- 知识点：在SELECT中指定具体列，避免返回无关数据，提升查询效率，尤其在网络传输数据时重要。
1.5 创建有意义的列名
- 知识点：通过AS关键字为列设置别名（如SELECT sal AS salary），增强结果可读性，别名可用于后续引用。
1.6 在WHERE子句中引用别名列
- 知识点：WHERE子句无法直接引用SELECT中的别名，需通过内嵌视图（子查询）包装查询，使外层查询可访问别名列。
1.7 串联多列的值
- 知识点：不同数据库使用不同串联方法，如DB2/Oracle/PostgreSQL用||，MySQL用CONCAT，SQL Server用+，实现多列合并。
1.8 在SELECT语句里使用条件逻辑
- 知识点：通过CASE表达式实现条件逻辑（如根据工资范围返回状态），替代传统编程语言的IF-ELSE，增强查询灵活性。
1.9 限定返回行数
- 知识点：不同数据库限制行数的语法不同，如MySQL/PostgreSQL用LIMIT，Oracle用ROWNUM，SQL Server用TOP，DB2用FETCH FIRST，用于分页或限制结果量。
1.10 随机返回若干行记录
- 知识点：结合ORDER BY与随机函数（如RAND()、DBMS_RANDOM.VALUE()）实现随机排序，再用行数限制语法获取随机行。
1.11 查找Null值
- 知识点：使用IS NULL判断Null值，不能用=或!=，是处理缺失数据的基础。
1.12 把Null值转换为实际值
- 知识点：通过COALESCE函数（如COALESCE(comm, 0)）将Null替换为指定值，或用CASE表达式实现类似逻辑，确保计算正确。
1.13 查找匹配项
- 知识点：使用LIKE配合通配符%（匹配任意字符）和_（匹配单个字符）进行模式匹配，筛选符合特定字符串模式的行。

第2章查询结果排序

2.1 以指定顺序返回查询结果
- 知识点：通过ORDER BY子句排序，默认升序（ASC），降序用DESC，可按列名或列位置序号排序。
2.2 多字段排序
- 知识点：在ORDER BY中用逗号分隔多个字段，按顺序依次排序，支持不同字段不同排序方向。
2.3 依据子串排序
- 知识点：使用字符串函数（如SUBSTR、SUBSTRING）提取子串，按子串内容排序，例如按职位最后两个字符排序。
2.4 对含有字母和数字的列排序
- 知识点：通过TRANSLATE和REPLACE函数清理非目标字符，保留字母或数字部分，再进行排序，处理混合数据列。
2.5 排序时对Null值的处理
- 知识点：Oracle支持NULLS FIRST/LAST直接控制Null值位置，其他数据库通过CASE表达式标记Null值，调整其在排序中的顺序。
2.6 依据条件逻辑动态调整排序项
- 知识点：在ORDER BY中使用CASE表达式，根据条件动态选择排序字段，例如按职位类型切换排序依据。

第3章多表查询

3.1 叠加两个行集
- 知识点：使用UNION ALL合并多个表的行（包含重复），UNION去重，要求列数和数据类型匹配。
3.2 合并相关行
- 知识点：通过内连接（INNER JOIN或隐式连接）基于共同列合并表，返回匹配行，是多表数据关联的基础。
3.3 查找两个表中相同的行
- 知识点：使用INTERSECT（DB2/PostgreSQL/Oracle）或连接查询匹配多列，获取两表交集数据。
3.4 查找只存在于一个表中的数据
- 知识点：差集运算（DB2/PostgreSQL用EXCEPT，Oracle用MINUS）或NOT IN、NOT EXISTS子查询，找出单表独有数据。
3.5 从一个表检索与另一个表不相关的行
- 知识点：外连接（左/右/全连接）保留主表不匹配行，过滤Null值后获取无关联数据，如查找无员工的部门。
3.6 新增连接查询而不影响其他连接查询
- 知识点：使用外连接（如LEFT JOIN）避免丢失主表数据，或标量子查询添加额外信息，确保原有结果集完整。
3.7 确定两个表是否有相同的数据
- 知识点：通过差集运算结合UNION ALL比较两表差异，或先比较行数再逐行对比，确保数据一致性。
3.8 识别并消除笛卡儿积
- 知识点：笛卡儿积由缺少连接条件导致，通过n-1个连接条件（WHERE或JOIN）避免，确保结果正确。
3.9 组合使用连接查询与聚合函数
- 知识点：聚合前注意连接可能产生的重复行，用DISTINCT去重或先聚合再连接，确保统计结果准确。
3.10 组合使用外连接查询与聚合函数
- 知识点：外连接保留主表数据，聚合时处理Null值（如COALESCE），正确计算包含缺失关联数据的分组统计。
3.11 从多个表中返回缺失值
- 知识点：全外连接（FULL OUTER JOIN）结合条件过滤，同时保留两表不匹配行，如同时显示无员工的部门和无部门的员工。
3.12 在运算和比较中使用Null
- 知识点：COALESCE将Null转换为有效值参与运算，避免Null导致的逻辑错误，确保比较和计算正确。

第4章插入、更新和删除

4.1 插入新记录
- 知识点：使用INSERT INTO ... VALUES插入单行或多行，省略列名时需按顺序提供所有列值，支持批量插入。
4.2 插入默认值
- 知识点：通过DEFAULT关键字显式插入列默认值，或省略列名利用表定义的默认值，简化插入操作。
4.3 使用Null覆盖默认值
- 知识点：在VALUES中显式指定NULL，即使列有默认值也强制插入Null，控制数据输入。
4.4 复制数据到另一个表
- 知识点：INSERT INTO ... SELECT将查询结果插入目标表，支持过滤条件，快速迁移数据。
4.5 复制表定义
- 知识点：DB2用CREATE TABLE ... LIKE，其他数据库用SELECT * FROM ... WHERE 1=0创建空表，复制表结构。
4.6 多表插入
- 知识点：Oracle用INSERT ALL根据条件插入多表，DB2通过UNION ALL结合表约束实现，其他数据库需分步插入。
4.7 禁止插入特定列
- 知识点：创建仅暴露允许插入列的视图，限制用户操作，通过视图权限控制数据插入范围。
4.8 更新记录
- 知识点：UPDATE ... SET结合WHERE更新指定行，支持表达式计算（如加薪10%），预览结果后执行。
4.9 当相关行存在时更新记录
- 知识点：子查询IN或EXISTS判断关联表存在性，针对性更新，如根据奖金表更新工资。
4.10 使用另一个表的数据更新记录
- 知识点：连接两表后更新（如UPDATE ... FROM ... JOIN），或子查询提供新值，实现跨表数据同步。
4.11 合并记录
- 知识点：Oracle的MERGE语句根据匹配条件自动插入或更新，简化数据同步逻辑，其他数据库需分步操作。
4.12 删除全表记录
- 知识点：DELETE FROM不带WHERE删除所有行，注意事务和性能影响，大表慎用。
4.13 删除指定记录
- 知识点：DELETE ... WHERE结合条件过滤，确保精确删除，避免误删数据。
4.14 删除单行记录
- 知识点：基于主键或唯一键精确删除，确保WHERE条件唯一，避免删除多行。
4.15 删除违反参照完整性的记录
- 知识点：NOT EXISTS或NOT IN子查询找出无关联的孤立记录，如删除无对应部门的员工。
4.16 删除重复记录
- 知识点：按重复列分组，保留最小/最大标识的行，删除其他重复行，确保数据唯一性。
4.17 删除被其他表参照的记录
- 知识点：先处理子表依赖数据（如级联删除），或直接删除主表记录（需数据库支持外键级联），处理外键约束。

第5章元数据查询

5.1 列举模式中的表
- 知识点：查询系统表或视图（如DB2的SYSCAT.TABLES，Oracle的ALL_TABLES，信息模式INFORMATION_SCHEMA.TABLES）获取表列表。
5.2 列举字段
- 知识点：通过系统视图获取列信息（如数据类型、位置），如DB2的SYSCAT.COLUMNS，Oracle的ALL_TAB_COLUMNS。
5.3 列举索引列
- 知识点：查询索引相关系统表（如DB2的SYSCAT.INDEXES，Oracle的ALL_IND_COLUMNS），了解表的索引结构。
5.4 列举约束
- 知识点：通过系统视图获取约束信息（主键、外键、检查约束等），如SYSCAT.TABCONST（DB2）、ALL_CONSTRAINTS（Oracle）。
5.5 列举非索引外键
- 知识点：结合索引和外键系统表，筛选未建立索引的外键列，优化数据库性能。
5.6 用SQL生成SQL
- 知识点：通过字符串拼接动态生成SQL脚本（如统计行数、禁用约束），实现自动化维护任务。
5.7 描述Oracle数据字典视图
- 知识点：利用Oracle的DICTIONARY和DICT_COLUMNS视图，快速了解数据字典视图的结构和用途。

第6章字符串处理

6.1 遍历字符串
- 知识点：通过笛卡儿积生成多行，用SUBSTR逐字符提取，模拟循环处理字符串，是字符串解析的基础。
6.2 嵌入引号
- 知识点：在字符串中用两个连续引号表示单个引号（如''），处理包含引号的数据。
6.3 统计字符出现的次数
- 知识点：通过LENGTH和REPLACE计算原字符串与替换后字符串的长度差，除以目标字符长度，统计出现次数。
6.4 删除不想要的字符
- 知识点：TRANSLATE替换目标字符为统一符号，再用REPLACE删除，或多次REPLACE逐个删除指定字符。
6.5 分离数字和字符数据
- 知识点：TRANSLATE将数字或字符转换为统一符号，REPLACE删除非目标符号，分离混合数据列。
6.6 判断含有字母和数字的字符串
- 知识点：TRANSLATE将字母数字转换为单一字符，比较转换后字符串是否全由该字符组成，或用正则表达式（如MySQL的REGEXP）筛选。
6.7 提取姓名的首字母
- 知识点：通过TRANSLATE、REPLACE处理非字母字符，提取首字母并拼接，处理不同格式的姓名。
6.8 按照子字符串排序
- 知识点：用SUBSTR提取子串，在ORDER BY中按子串排序，如按姓名最后两个字符排序。
6.9 根据字符串里的数字排序
- 知识点：清理非数字字符（如TRANSLATE替换为数字），转换为数值类型后排序，处理混合数字的字符串。
6.10 创建分隔列表
- 知识点：不同数据库用不同方法，如MySQL的GROUP_CONCAT，Oracle的SYS_CONNECT_BY_PATH，拼接多行数据为逗号分隔字符串。
6.11 分隔数据转换为多值IN列表
- 知识点：拆分分隔字符串为多行，转换为数值后用于IN子句，处理输入的列表数据。
6.12 按字母表顺序排列字符
- 知识点：遍历字符并排序，用聚合函数拼接，实现字符串内字符的排序。
6.13 识别字符串里的数字字符
- 知识点：TRANSLATE标记数字字符，筛选包含数字的行，或提取纯数字部分，处理混合数据。
6.14 提取第n个分隔子字符串
- 知识点：利用SUBSTRING_INDEX（MySQL）、SPLIT_PART（PostgreSQL）或INSTR结合SUBSTR，按分隔符提取指定位置子串。
6.15 解析IP地址
- 知识点：按.分隔符拆分IP地址为四部分，用字符串函数提取各段数值，处理网络地址数据。

第7章数值处理

7.1 计算平均值
- 知识点：AVG函数忽略Null值，分组计算（GROUP BY）各部门平均值，处理聚合统计。
7.2 查找最小值和最大值
- 知识点：MIN和MAX函数获取列极值，支持分组统计，忽略Null值，是基本聚合函数。
7.3 求和
- 知识点：SUM函数累加数值列，支持分组（GROUP BY），忽略Null值，处理数据汇总。
7.4 计算行数
- 知识点：COUNT(*)统计所有行，COUNT(col)统计非Null值行数，分组统计各部门人数。
7.5 计算非Null值的个数
- 知识点：COUNT(col)自动忽略Null，直接获取有效数据行数，如统计有奖金的员工数。
7.6 累计求和
- 知识点：窗口函数SUM OVER（DB2/Oracle）或标量子查询（其他数据库），按顺序累加值，生成运行总计。
7.7 计算累计乘积
- 知识点：利用对数转换和指数运算（LN+EXP）实现累计乘积，或Oracle的MODEL子句，处理数值连乘。

后续章节（8-14章及附录）

第8-14章：涵盖日期运算、日期处理、区间查询、高级查询、报表生成、层次查询等，涉及窗口函数、递归查询、数据透视等高级技术，针对不同数据库的特性提供解决方案。
附录A：窗口函数简介，解释分组、聚合、分区等概念，是理解高级查询的基础。
附录B：重温经典SQL问题，结合新特性（如窗口函数）提供优化方案，提升查询效率。