SQL 分页查询、视图与序列

SQL 分页查询、视图与序列学习笔记

在 SQL 中，分页查询、视图和序列是常用的高级功能。

1. 分页查询

分页查询用于从大量数据中按页提取数据。Oracle 使用 ROWNUM 伪列实现分页查询。

（1）基本分页查询

-- 查询第 6 到第 10 行数据
SELECT *
FROM (SELECT e.*, ROWNUM rn FROM emp e)
WHERE rn >= 6 AND rn <= 10;

（2）分页查询公式

• 每页显示 pageSize 条数据。
• 查询第 pageNo 页的数据：

  SELECT *
  FROM (SELECT e.*, ROWNUM rn FROM emp e)
  WHERE rn >= (pageNo - 1) * pageSize + 1
    AND rn <= pageNo * pageSize;

（3）示例：每页显示 4 条数据

-- 查询第 2 页数据（第 5 到第 8 行）
SELECT *
FROM (SELECT e.*, ROWNUM rn FROM emp e)
WHERE rn >= 5 AND rn <= 8;

2. 视图（View）

视图是基于 SQL 查询的虚拟表，用于简化复杂查询或隐藏数据细节。

（1）创建视图

-- 创建视图：查询部门名称、部门编号和员工个数
CREATE OR REPLACE VIEW V_EMP_DEPT AS
SELECT d.dname, t.*
FROM (SELECT deptno, COUNT(*) AS emp_count FROM emp GROUP BY deptno) t
JOIN dept d ON t.deptno = d.deptno;

-- 查询视图
SELECT * FROM V_EMP_DEPT;

（2）修改视图

-- 修改视图
CREATE OR REPLACE VIEW V_EMP_DEPT AS
SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno;

（3）约束条件视图

-- 创建约束条件视图（只能查询部门编号为 10 的数据）
CREATE OR REPLACE VIEW V_EMP AS
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10 WITH CHECK OPTION;

-- 查询视图
SELECT * FROM V_EMP;

（4）只读视图

-- 创建只读视图
CREATE OR REPLACE VIEW V_EMP AS
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10 WITH READ ONLY;

-- 查询视图
SELECT * FROM V_EMP;

（5）内嵌视图（Inline View）

-- 查询工资大于所在部门平均工资的员工
SELECT e.ename, e.sal, e.deptno, t.avg_sal
FROM emp e
JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t
ON e.deptno = t.deptno
WHERE e.sal > t.avg_sal;

3. 序列（Sequence）

序列用于生成唯一的数字，通常用于主键列。

（1）创建序列

-- 创建序列
CREATE SEQUENCE myseq
    MINVALUE 1
    MAXVALUE 100
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

（2）使用序列

-- 获取当前序列值
SELECT myseq.CURRVAL FROM dual;

-- 获取下一个序列值
SELECT myseq.NEXTVAL FROM dual;

-- 插入数据时使用序列
CREATE TABLE tmp_del (
    curr_id INT,  -- 当前序列值
    next_id INT   -- 下一个序列值
);

INSERT INTO tmp_del VALUES (myseq.CURRVAL, NULL);
INSERT INTO tmp_del VALUES (NULL, myseq.NEXTVAL);

-- 查询数据
SELECT * FROM tmp_del;

4. 综合示例

（1）分页查询

-- 每页显示 4 条数据，查询第 2 页
SELECT *
FROM (SELECT e.*, ROWNUM rn FROM emp e)
WHERE rn >= 5 AND rn <= 8;

（2）创建视图

-- 创建视图：查询部门名称、部门编号和员工个数
CREATE OR REPLACE VIEW V_EMP_DEPT AS
SELECT d.dname, t.*
FROM (SELECT deptno, COUNT(*) AS emp_count FROM emp GROUP BY deptno) t
JOIN dept d ON t.deptno = d.deptno;

-- 查询视图
SELECT * FROM V_EMP_DEPT;

（3）使用序列

-- 创建序列
CREATE SEQUENCE myseq
    MINVALUE 1
    MAXVALUE 100
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

-- 插入数据时使用序列
INSERT INTO tmp_del VALUES (myseq.CURRVAL, NULL);
INSERT INTO tmp_del VALUES (NULL, myseq.NEXTVAL);

-- 查询数据
SELECT * FROM tmp_del;

5. 总结

1. 分页查询：使用 ROWNUM 实现分页查询。
2. 视图：创建、修改和使用视图简化查询。
3. 序列：创建和使用序列生成唯一数字。