（课堂笔记）SQL 临时表、视图、正则表达式

本文总结了SQL中临时表(CTE)、视图和正则表达式的核心知识点。

---

临时表部分重点讲解WITH子句的编写规范、使用优势（逻辑拆分、提高可读性）和连续登录天数计算案例，强调引用顺序和必须包含最终SELECT的注意事项。

---

视图部分对比普通视图与物化视图的存储特性，说明视图在封装逻辑和安全性方面的作用。

---

正则表达式部分详解Oracle四大核心函数(REGEXP_LIKE/SUBSTR/REPLACE/INSTR)的用法，包含字符串匹配、截取、替换和位置查找的典型示例，特别强调正则模式符号的应用技巧。

---

复习建议聚焦连续登录问题分析、视图封装价值和正则匹配/截取功能的重点练习。

以下是根据你的课堂笔记整理的结构化复习文档，适合学生快速理解和复习重点知识点。

📘 课堂笔记：临时表、视图与正则表达式

一、临时表（WITH 子句 / CTE）

✅ 基本写法

sql

WITH 临时表名1 AS ( SELECT 查询 ), 临时表名2 AS ( SELECT 查询 ) SELECT 任意字段 FROM 任意临时表;

✅ 好处

• 将复杂逻辑拆分成多个简单步骤

• 提高代码可读性和可维护性

✅ 示例：最大连续登录天数 & 最近连续登录天数

sql

WITH T1 AS ( SELECT USER_ID, LOGIN_DATE, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY LOGIN_DATE) AS RN FROM T_LOGIN ), T2 AS ( SELECT USER_ID, LOGIN_DATE, RN, LOGIN_DATE - RN AS RR FROM T1 ), T3 AS ( SELECT USER_ID, RR, COUNT(1) AS CNT FROM T2 GROUP BY USER_ID, RR ) SELECT DISTINCT USER_ID, MAX(CNT) OVER (PARTITION BY USER_ID) AS 最大连续, FIRST_VALUE(CNT) OVER (PARTITION BY USER_ID ORDER BY RR DESC) AS 最近连续 FROM T3;

⚠️ 注意点

1. 只能从上往下引用临时表

2. 临时表仅在当前 SQL 中生效

3. 必须写最终的SELECT语句

❌ 错误示例1：顺序引用错误

sql

WITH T1 AS (...), T3 AS (SELECT * FROM T2), -- T2 还未定义 T2 AS (...) SELECT * FROM T3;

❌ 错误示例2：没有最终 SELECT

sql

WITH T1 AS (...), T2 AS (...) -- 缺少 SELECT

二、视图（VIEW）

✅ 作用

• 封装一段SELECT语句

• 避免重复复杂查询

• 隐藏底层表结构，增强安全性

✅ 创建语法

sql

CREATE OR REPLACE VIEW 视图名 AS SELECT 语句;

✅ 示例

sql

CREATE OR REPLACE VIEW EMP_V AS SELECT ENAME, SAL, E.DEPTNO, DNAME, LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

✅ 使用方式（和表一样）

sql

SELECT * FROM EMP_V; SELECT LOC, COUNT(1) FROM EMP_V GROUP BY LOC;

🧠 区别对比

三、正则表达式（Oracle）

✅ 四个核心函数

✅ 常用正则匹配项

1️⃣ REGEXP_LIKE（匹配）

sql

-- 姓名必须是 5 位大写字母 SELECT * FROM EMP WHERE REGEXP_LIKE(ENAME, '^[A-Z]{5}$'); -- 手机号校验 SELECT 1 FROM DUAL WHERE REGEXP_LIKE('13112345678', '^1[3456789][0-9]{9}$');

2️⃣ REGEXP_SUBSTR（截取）

sql

-- 按 - 分割，取第 4 段 SELECT REGEXP_SUBSTR('中国-浙江省-杭州市-西湖区', '[^-]+', 1, 4) FROM DUAL; -- 提取第 4 组连续字母 SELECT REGEXP_SUBSTR('DS333DAS5D5AS5DA6', '[A-Z]+', 1, 4) FROM DUAL;

3️⃣ REGEXP_REPLACE（替换）

sql

-- 统计数字 1 的个数 SELECT LENGTH(REGEXP_REPLACE('10101001010', '[^1]', '')) FROM DUAL; -- 姓名脱敏：首字母 + *** + 尾字母 SELECT ENAME, SUBSTR(ENAME,1,1) || REGEXP_REPLACE(SUBSTR(ENAME,3), '[A-Z]', '*') || SUBSTR(ENAME,-1) AS NEW_ENAME FROM EMP;

4️⃣ REGEXP_INSTR（位置）

sql

-- 查找第 3 组数字的起始位置 SELECT REGEXP_INSTR('JBJ34J33J45J', '[0-9]+', 1, 3) FROM DUAL; -- 查找第 2 组非字母的起始位置 SELECT REGEXP_INSTR('8932BJ3B2BJ', '[^A-Z]+', 1, 2) FROM DUAL;

✅ 复习建议

• 临时表：重点掌握连续登录问题的拆分思路

• 视图：理解封装逻辑和安全控制

• 正则：重点练习REGEXP_LIKE和REGEXP_SUBSTR