完整教程：SQL 进阶：视图、流程控制、权限管理

文章目录

• 一、视图
• • 1.1 视图的核心特性与作用
  • 1.2 视图的优点
  • 1.3 视图的创建
  • • 1.3.1 基础语法
    • 1.3.2 实战示例
  • 1.4 视图的典型应用场景
• 二、流程控制
• • 2.1 条件判断：IF 与 CASE
  • • 2.1.1 IF 语句（多条件分支）
    • 2.1.2 CASE 语句（等值分支）
  • 2.2 循环控制：WHILE、LOOP、REPEAT
  • • 2.2.1 WHILE 循环（先判断后执行）
    • 2.2.2 LOOP 循环（无限循环）
    • 2.2.3 REPEAT 循环（先执行后判断）
  • 2.3 循环辅助：LEAVE 与 ITERATE
  • • 2.3.1 LEAVE（退出循环 / 程序块）
    • 2.3.2 ITERATE（跳过当前循环）
• 三、权限管理
• • 3.1 创建用户
  • 3.2 授权用户（核心操作）
  • 3.3 对视图授权
  • 3.4 刷新权限

一、视图

视图是 SQL 中用于简化查询、控制权限的虚拟表，基于基表（创建视图的原始表）的查询结果构建，它本身不存储数据，仅保存查询逻辑，所有结果都来自它依赖的基表

1.1 视图的核心特性与作用

操作限制：常用于 SELECT 查询，UPDATE、DELETE、INSERT操作限制极多（如视图包含聚合函数、关联查询时无法直接修改），实际中很少使用。

核心用途：

1. 权限管理：可精准控制用户访问的数据范围（如仅让用户查看学生表的 “学号”“姓名”，隐藏 “性别”“班级” 等敏感字段），而直接对表授权无法实现列级权限控制。

2. 数据重构适配：当基表结构变更（如拆分、合并表）时，可通过修改视图逻辑屏蔽变更影响，无需修改应用程序中的 SQL 语句。

1.2 视图的优点

1. 简单易用：用户无需关心视图背后的基表结构、关联条件或筛选规则，只需像查询普通表一样使用视图，即可获取预先过滤好的结果集。

2. 安全可靠：仅向用户开放视图可见的数据，避免用户直接访问基表，防止敏感信息泄露（如财务表仅向财务人员开放 “部门营收” 视图，不开放完整财务表）。

3. 数据独立性：基表结构变化（如新增列、修改列名）时，只要视图所需字段不变，可通过修改视图映射关系适配变化，应用程序无需改动。

1.3 视图的创建

1.3.1 基础语法

CREATE VIEW <视图名> AS <SELECT语句>; -- SELECT语句定义视图的数据源和筛选逻辑

1.3.2 实战示例

假设存在两个基表：

• student（学生表）：student_id（学号，主键）、name（姓名）
• score（成绩表）：student_id（学号，外键）、course_id（课程 ID）、score（成绩）

需求：创建视图，仅显示 “成绩≥80 分” 的学生学号、姓名及对应成绩，简化高频查询操作。

-- 1. 创建视图：关联学生表和成绩表，筛选高分记录
CREATE VIEW view_high_score AS
SELECT
s.student_id,  -- 从学生表取学号
s.name,        -- 从学生表取姓名
sc.score       -- 从成绩表取分数
FROM
student s  -- 学生表别名s
JOIN
score sc ON s.student_id = sc.student_id  -- 按学号关联两表
WHERE
sc.score >= 80;  -- 筛选条件：成绩≥80分
-- 2. 使用视图查询：无需重复写关联和筛选逻辑，直接查视图
SELECT * FROM view_high_score; -- 结果即所有高分学生的目标信息

1.4 视图的典型应用场景

以 “基表拆重构” 为例：

如果因业务需求，需要将原 user 表拆分为 usera（存储 “姓名”“年龄”）和 userb（存储 “姓名”“性别”），此时应用程序中原有 SELECT * FROM user 语句会报错。

解决方案：创建名为user的视图，映射拆分后的表结构，无需修改应用程序：

-- 创建视图，模拟原user表结构
CREATE VIEW user AS
SELECT
a.name,  -- 从usera取姓名
a.age,   -- 从usera取年龄
b.sex    -- 从userb取性别
FROM
usera a
JOIN
userb b ON a.name = b.name; -- 按姓名关联两表
-- 应用程序仍可执行原语句，视图自动关联拆分后的表
SELECT * FROM user;

---

二、流程控制

SQL 的流程控制用于实现 “条件判断”“循环执行” 等逻辑，主要用于存储过程、函数中，常用关键字包括IF、CASE、WHILE、LOOP等，需配合DELIMITER修改语句分隔符（默认;会提前终止存储过程定义）。

2.1 条件判断：IF 与 CASE

2.1.1 IF 语句（多条件分支）

用于根据多个条件执行不同逻辑，语法类似编程语言的if-else if-else：

IF condition THEN  -- 条件1：成立则执行下方逻辑
  执行语句1;
ELSEIF condition THEN  -- 条件2：条件1不成立时判断，成立则执行
  执行语句2;
ELSE  -- 所有条件均不成立时执行
  执行语句3;
END IF;  -- 结束IF判断（必须写）

2.1.2 CASE 语句（等值分支）

用于 “判断变量是否等于指定值”，类似编程语言的switch-case，适合固定值匹配场景：

CASE value  -- 待判断的变量或表达式
WHEN value1 THEN 执行语句1;  -- 变量=value1时执行
WHEN value2 THEN 执行语句2;  -- 变量=value2时执行
ELSE 执行语句3;  -- 变量不等于任何指定值时执行
END CASE;  -- 结束CASE判断（必须写）

2.2 循环控制：WHILE、LOOP、REPEAT

2.2.1 WHILE 循环（先判断后执行）

满足条件时循环执行逻辑，语法：

WHILE condition DO  -- 条件成立则进入循环
循环执行的语句;
END WHILE;  -- 结束循环

2.2.2 LOOP 循环（无限循环）

无默认终止条件，需配合LEAVE（类似break）手动退出，语法：

LOOP  -- 开启无限循环
循环执行的语句;
IF condition THEN  -- 满足终止条件时
LEAVE label;  -- 退出循环（label为循环标签，需提前定义）
END IF;
END LOOP;  -- 结束循环

示例：用 LOOP 计算 1-100 的和

-- 1. 修改分隔符为//（避免存储过程中的;提前终止定义）
DELIMITER //
-- 2. 创建存储过程：接收输出参数sum，返回1-100的和
CREATE PROCEDURE example_loop(OUT sum INT)
BEGIN
DECLARE i INT DEFAULT 1;  -- 循环变量，初始值1
DECLARE s INT DEFAULT 0;  -- 累加变量，初始值0
loop_label:LOOP  -- 定义循环标签loop_label
SET s = s + i;  -- 累加：当前和 + 循环变量
SET i = i + 1;  -- 循环变量自增1
IF i > 100 THEN  -- 终止条件：i超过100
LEAVE loop_label;  -- 退出循环
END IF;
END LOOP;  -- 结束循环
SET sum = s;  -- 将累加结果赋值给输出参数
END
//
-- 3. 恢复分隔符为默认;
DELIMITER ;
-- 4. 调用存储过程：传入输出参数@sum
CALL example_loop(@sum);
-- 5. 查询结果：输出1-100的和（5050）
SELECT @sum;

2.2.3 REPEAT 循环（先执行后判断）

先执行一次循环体，再判断条件，满足条件则退出，类似do-while，语法：

REPEAT
循环执行的语句;
UNTIL condition  -- 条件成立则退出循环（无需写;）
END REPEAT;  -- 结束循环

示例：用 REPEAT 计算 1-100 的和

DELIMITER //
CREATE PROCEDURE example_repeat(OUT sum INT)
BEGIN
DECLARE i INT DEFAULT 1;  -- 循环变量
DECLARE s INT DEFAULT 0;  -- 累加变量
REPEAT
SET s = s + i;  -- 先累加（至少执行1次）
SET i = i + 1;  -- 变量自增
UNTIL i > 100  -- 条件：i>100时退出（无需;）
END REPEAT;
SET sum = s;
END
//
DELIMITER ;
-- 调用与查询
CALL example_repeat(@sum);
SELECT @sum; -- 结果5050

2.3 循环辅助：LEAVE 与 ITERATE

2.3.1 LEAVE（退出循环 / 程序块）

类似break，用于退出BEGIN…END、LOOP、WHILE、REPEAT定义的代码块，需配合 “标签” 使用。

2.3.2 ITERATE（跳过当前循环）

类似continue，用于跳过当前循环的剩余语句，直接进入下一次循环，仅支持LOOP、WHILE、REPEAT循环，语法：

ITERATE label;  -- label为循环标签，跳转到该标签对应的循环开头

---

三、权限管理

权限管理用于 “控制用户对数据库的操作范围”，包括创建用户、授权、刷新权限等，确保不同用户仅能访问自己权限内的数据，保障数据库安全。

3.1 创建用户

创建数据库用户，指定用户可登录的主机（如本地、任意远程主机）和登录密码，语法：

CREATE USER username@host IDENTIFIED BY password;

• username：用户名（自定义，如user1）。
• host：允许登录的主机，localhost表示仅本地登录，%表示任意远程主机登录（如user1@%）。
• password：登录密码（如123456，建议复杂密码）。

示例：创建用户student，允许从任意远程主机登录，密码stu123：

CREATE USER student@% IDENTIFIED BY 'stu123';

3.2 授权用户（核心操作）

为用户分配指定权限（如SELECT、INSERT），控制其可操作的数据库和表，语法：

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

• privileges：权限类型，多个权限用逗号分隔，ALL表示所有权限（常用权限：SELECT查询、INSERT插入、UPDATE更新、DELETE删除）。
• databasename.tablename：授权范围，*.*表示所有数据库的所有表，db1.t1表示db1库的t1表。
• WITH GRANT OPTION：可选，允许该用户将自己的权限授权给其他用户（若无此选项，用户无法授权他人）。

示例 1：给student用户授予school库中student表的SELECT权限（仅允许查询学生信息）：

GRANT SELECT ON school.student TO 'student'@'%';

示例 2：给admin用户授予所有数据库所有表的所有权限，并允许其授权他人：

GRANT ALL ON *.* TO 'admin'@localhost WITH GRANT OPTION;

3.3 对视图授权

视图的授权需额外指定SHOW VIEW权限（查看视图结构），若需查询视图数据，还需授予SELECT权限，语法：

GRANT select, SHOW VIEW ON `databasename`.`viewname` TO 'username'@'host';

示例：给student用户授予school库中view_high_score视图的查询和查看权限：

GRANT select, SHOW VIEW ON `school`.`view_high_score` TO 'student'@'%';

3.4 刷新权限

修改用户权限（如授权、回收权限）后，需执行刷新命令使权限生效，语法：

FLUSH PRIVILEGES;

示例：给student用户新增INSERT权限后，刷新权限：

GRANT INSERT ON school.student TO 'student'@'%';FLUSH PRIVILEGES; -- 权限立即生效