mysql学习教程，从入门到精通，SQL 创建视图(CREATE VIEW 语句)（34）

1、SQL 创建视图(CREATE VIEW 语句)

在 SQL 中，CREATE VIEW 语句用于创建一个视图（View），视图是一种虚拟表，它基于 SQL 查询的结果集。视图并不存储实际数据，而是存储定义视图的查询。当您查询视图时，数据库会动态执行视图定义中的查询，并返回结果。
以下是 CREATE VIEW 语句的基本语法：

CREATE VIEW 视图名称 AS
SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

或者，如果视图是基于多个表的连接或子查询，语法可能更复杂：

CREATE VIEW 视图名称 AS
SELECT 列1, 列2, ..., 聚合函数(...)
FROM 表1
JOIN 表2 ON 表1.列 = 表2.列
WHERE 条件
GROUP BY 列
HAVING 条件
ORDER BY 列;

在上面的语法中：

• CREATE VIEW 是创建视图的关键字。
• 视图名称 是您要创建的视图的名称。视图名称在数据库中必须是唯一的。
• AS 关键字后面跟着的是定义视图的 SQL 查询。
• SELECT 子句指定了视图中的列。这些列可以是来自一个或多个表的列，也可以是计算列（例如，使用表达式或函数计算的列）。
• FROM 子句指定了视图基于的表。
• WHERE 子句（可选）用于过滤结果集中的行。
• JOIN 子句（可选）用于结合来自两个或多个表的数据。
• GROUP BY 子句（可选）用于对结果集中的行进行分组。
• HAVING 子句（可选）用于过滤分组后的结果集。
• ORDER BY 子句（可选）用于对结果集中的行进行排序。
  请注意，视图中的 SELECT 查询不能包含 ORDER BY 子句（除非在视图定义中使用了 TOP 或 OFFSET-FETCH 子句，这在某些数据库系统中是允许的，但通常不推荐，因为 ORDER BY 在视图中的行为可能是不可预测的），因为视图通常是作为更复杂查询的一部分来使用的，而最终的排序应该在外部查询中指定。
  另外，还可以使用 WITH CHECK OPTION 子句来限制对视图中数据的更新操作，以确保更新后的数据仍然满足视图定义中的条件。例如：

CREATE VIEW 视图名称 AS
SELECT 列1, 列2, ...
FROM 表名
WHERE 条件
WITH CHECK OPTION;

使用 WITH CHECK OPTION 后，如果尝试通过视图插入或更新数据，而这些数据不满足视图定义中的 WHERE 条件，则数据库将拒绝这些操作。
最后，请注意，不同的数据库系统（如 MySQL、PostgreSQL、SQL Server、Oracle 等）在视图的功能和语法上可能有所不同。因此，在创建视图时，最好参考您所使用的数据库系统的官方文档。
当然！以下是一个使用 SQL CREATE VIEW 语句的示例。假设我们有一个名为 employees 的表，其结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

我们还假设有一个名为 departments 的表，其结构如下：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

现在，我们希望创建一个视图，该视图包含员工的姓名、部门名称和薪水。这个视图将结合 employees 和 departments 两个表的信息。

以下是创建该视图的 SQL 语句：

CREATE VIEW employee_department_salary AS
SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM 
    employees e
JOIN 
    departments d
ON 
    e.department_id = d.department_id;

在这个示例中：

1. CREATE VIEW employee_department_salary AS：创建一个名为 employee_department_salary 的视图。
2. SELECT e.first_name, e.last_name, d.department_name, e.salary：选择视图中的列，包括员工的名字、姓氏、部门名称和薪水。
3. FROM employees e JOIN departments d ON e.department_id = d.department_id：从 employees 表和 departments 表中获取数据，并通过 department_id 列将它们连接起来。
   创建视图后，您可以像查询表一样查询视图：

SELECT * FROM employee_department_salary;

这将返回包含员工姓名、部门名称和薪水的结果集。视图在逻辑上是一个表，但它不存储数据，而是基于查询动态生成结果。
当然可以，以下是一些使用 CREATE VIEW 语句创建视图的例子，这些例子将帮助您更深入地理解视图的概念和用法。

例子 1：创建单行列子集视图

假设我们有一个名为 students 的表，其中包含学生的学号（sno）、姓名（sname）、年龄（sage）和系别（sdept）。我们可以创建一个只包含信息系（IS）学生的视图。

CREATE VIEW IS_Student AS
SELECT sno, sname, sage
FROM students
WHERE sdept = 'IS';

这个视图 IS_Student 是一个行列子集视图，因为它从单个基本表 students 中导出了数据，并且只是去掉了不属于信息系的学生行。

例子 2：创建多表连接视图

现在，假设我们还有一个名为 courses 的表，记录课程信息，以及一个名为 enrollments 的表，记录学生的选课情况。我们可以创建一个视图，显示学生选修的课程名称、课程号和成绩。

CREATE VIEW Student_Courses AS
SELECT 
    s.sno,
    s.sname,
    c.cname,
    e.grade
FROM 
    students s
JOIN 
    enrollments e ON s.sno = e.sno
JOIN 
    courses c ON e.cno = c.cno;

这个视图 Student_Courses 通过连接 students、enrollments 和 courses 三个表，提供了学生选课情况的全面视图。

例子 3：创建带表达式的视图

假设我们想在 students 表中创建一个视图，显示学生的出生年份。我们可以使用当前年份减去学生的年龄来计算出生年份。

CREATE VIEW Student_Birth_Year AS
SELECT 
    sno,
    sname,
    2024 - sage AS birth_year  -- 假设当前年份是2024年
FROM 
    students;

在这个例子中，Student_Birth_Year 视图包含学生的学号、姓名和计算出的出生年份。

例子 4：创建分组视图

假设我们想在 enrollments 表中创建一个视图，显示每个学生的平均成绩。

CREATE VIEW Student_Avg_Grade AS
SELECT 
    sno,
    AVG(grade) AS avg_grade
FROM 
    enrollments
GROUP BY 
    sno;

这个视图 Student_Avg_Grade 通过对学生成绩进行分组和平均计算，提供了每个学生的平均成绩。

例子 5：创建带 WITH CHECK OPTION 的视图

回到例子 1 中的 IS_Student 视图，如果我们希望在修改和插入操作时仍保证该视图只包含信息系的学生，我们可以使用 WITH CHECK OPTION 子句。

CREATE VIEW IS_Student AS
SELECT sno, sname, sage
FROM students
WHERE sdept = 'IS'
WITH CHECK OPTION;

现在，如果尝试向 IS_Student 视图中插入一个不属于信息系的学生记录，或者修改现有记录使其系别变为非信息系，数据库将拒绝这些操作。

查询视图

创建视图后，您可以像查询表一样查询视图。例如，查询 IS_Student 视图中年龄小于20岁的学生：

SELECT sno, sname, sage
FROM IS_Student
WHERE sage < 20;

或者查询 Student_Avg_Grade 视图中平均成绩高于80分的学生：

SELECT sno, avg_grade
FROM Student_Avg_Grade
WHERE avg_grade > 80;

这些例子展示了如何使用 CREATE VIEW 语句创建不同类型的视图，以及如何查询这些视图。通过创建视图，您可以简化复杂查询、提高数据安全性、抽象基础表结构，并增强数据的可读性和可维护性。