SQL高级教程（六）

SQL 视图（Views）

1 SQL CREATE VIEW 语句

在 SQL 中，视图是基于 SQL 语句的额结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

你可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

SQL CREATE VIEW 语法

CREATE VIEW view_name AS
SELECT column1,column2,...
FROM table_name
WHERE condition;

参数说明：

• CREATE VIEW：声明你要创建一个视图。
• view_name：指定视图的名称
• AS：指定关键字，表示视图的定义开始。
• SELECT column1,column2,...：指定视图中包含的列，可以是表中的列或计算列。
• FROM table_name：指定视图从哪个表中获取数据。
• WHERE condition：可选部分，用于指定筛选条件，限制视图中的行。

2 SQL CREATE VIEW 实例

假设你有一个包含员工信息的表 employees，包括以下列：employee_id、first_name、last_name、satary 和 department_id，现在，我们将创建一个视图，显示工资高于某个阈值的员工信息。

实例如下：

-- 创建包含高工资员工信息的视图
CREATE VIEW hign_salary_employees AS
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary > 50000;

在这个例子中，我们创建了一个名为 hign_salary_employees 的视图，该视图包含了那些工资高于 50000 的员工的信息。

现在，你可以像查询普通表一样使用这个视图：

-- 查询高工资员工视图
SELECT *
FROM high_salary_employees;

这将返回所有工资高于 50000 的员工的详细信息，而不需要每次都填写相同的筛选条件。

值得注意的是，视图本质上是一个虚拟的表，它并不存储数据，而是基于基础表的查询结果生成。因此，如果基础表的数据发生变化，视图的内容也会相应地更新。

3 SQL 更新视图

在 SQL 中，你不能直接使用 UPDATE 语句来更新视图，因为视图是基于查询结果生成的虚拟表，而不是实际存储数据的表。

更新视图的实质是通过更新视图所基于的表中的数据，然后视图会反映这些变化。

UPDATE table_name
SET column1 = value1,column2 = value2, ...
WHERE condition;

其中 table_name 是基础表的名称，column1,column2,...是要更新的列，value1,value2,... 是新的值，condition 是更新的条件。

现在，我们希望向 Current Product List 视图添加 Category 列。我们将通过下列 SQL 更新视图：

举例来说，如果你有一个名为 high_salary_employees 的视图，显示工资高于 50000 的员工信息，而这个视图基于 employees 表的查询结果，你可以通过以下步骤来更新数据：

-- 步骤 1：更新 employees 表中的数据
UPDATE employees
SET salary = 60000
WHERE employee_id = 1001;

-- 步骤 2：查询更新后的高工资员工视图
SELECT *
FROM high_salary_employees;

这样，你更新了 employees 表中的数据，而视图 high_salary_employees 将反映出这些变化。

4 SQL 撤销视图

在 SQL 中，撤销（或删除）视图是通过使用 DROP VIEW 语句来实现。

DROP VIEW 语句用于从数据库中删除一个已存在的视图。语法如下：

DROP VIEW [IF EXISTS] view_name;

参数说明：

• DROP VIEW：表示你要删除一个视图。
• IF EXISTS：可选部分，用于检查视图是否存在。如果存在，则执行删除操作；如果不存在，不会发生错误。在某些数据库系统中，这是可选的。
• view_name：指定要删除的视图的名称。

在执行以下语句后，视图 high_salary_employees 将被从数据库中删除。

-- 删除名为 high_salary_employees 的视图
DROP VIEW IF EXISTS high_salary_employees;

请注意，这并不影响基础表中的数据，只是删除了视图的定义。

如果你需要撤销或删除某个表中的数据，应该使用 DROP TABLE 语句。

在使用 DROP VIEW 语句时，请确保你真的想要删除该视图，因为一旦删除，将无法恢复视图的定义。

SQL Date 函数

1 MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

2 SQL Server Date 函数

下面的表格列出了 SQL Server 中最重要的内建日期函数：

3 SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
• YEAR - 格式：YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：唯一的数字

4 SQL 日期处理

假设我们有如下 Orders 表：

现在，我们希望从上表中选取 OrderDate 为 2008-11-11 的记录。

我们使用下面的 SELECT 语句：

SELECT * FROM Orders WHERE OrderDate='2008-11-11';

结果集如下所示：

现在，假设 Orders 表如下所示（请注意 OrderDate 列中的时间部分）：

如果我们使用和上面一样的 SELECT 语句：

SELECT * FROM Orders WHERE OrderDate='2008-11-11';
或
SELECT * FROM Orders WHERE OrderDate='2008-11-11 00:00:00';

那么我们将得不到结果！因为表中没有 2008-11-11 00:00:00 日期。如果没有时间部分，默认时间为 00:00:00。

SQL NULL 值

1 SQL NULL 值

如果表中的某个列是可选的，那么我们可以在不向该列添加值得情况下插入新纪录或更新已有得记录。这意味着该字段将以 NULL 值保存。

NULL 值得处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

2 SQL 的 NULL 值处理

请看下面的 Persons 表：

假如 Persons 表中的 Address 列是可选的。这意味着如果在 Address 列插入一条不带值得记录，"Address" 列会使用 NULL 值保存。

那么我们如何测试 NULL 值呢？

无法使用比较运算符来测试 NULL 值，比如=、< 或 <>

我们必须使用 IS NULL 和 IS NOT NULL 操作符。

3 SQL IS NULL

我们如何仅仅选取在 Address 列中带有 NULL 值的记录呢？

我们必须使用 IS NULL 操作符：

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

结果集如下所示：

4 SQL IS NOT NULL

我们如何仅仅选取在 Address 列中不带有 NULL 值得记录呢？

我们必须使用 IS NOT NULL 操作符：

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL;

结果集如下所示：

SQL NULL 函数

SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

请看下面得 Products 表：

假如 UnitsOnOrder 是可选的，而且可以包含 NULL 值。

我们使用下面的 SELECT 语句：

SELECT ProductName,UnitPrice*(UnitsInstock+UnitsOnOrder)
FROM Products;

在上面的实例中，如果有 UnitsOnOrder 值是 NULL，那么结果是 NULL。

微软的 ISNULL() 函数用于规定如何处理 NULL 值。

NVL()、IFNUL() 和 COALESCE() 函数也可以达到相同的结果。

在这里，我们希望 NULL 值为 0 。

下面，如果 UnitsOnOrder 是 NULL，则不会影响计算，因为如果值是 NULL 则 ISNULL() 返回 0 。

① SQL Server / MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products;

② Oracle

Oracle 没有 ISNULL() 函数。不过，我们可以使用 NVL() 函数达到相同的结果：

SELECT ProductName,UnitPrice*(UnitsInstock+NVL(UnitsOnOrder,0))
FROM Products;

③ MySQL

MySQL 也拥有类似 ISNULL() 函数。不过，他们的工作方式与微软的 ISNULL() 函数有点不同。

在 MySQL 中，我们可以使用 IFNULL() 函数，如下所示：

SELECT PriductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products;

或者我们可以使用 COALESCES() 函数，如下所示：

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products;

SQL 通用数据类型

1 SQL 通用数据类型

数据库表中的每个列都要求有名称和数据类型。

Each column in a database table is required to have a name and a data type.

SQL 开发人员必须在创建 SQL 表时决定表中每个列将要存储的数据的类型。数据类型是一个标签，是便于 SQL 了解每个列期望存储什么类型的数据的指南，它也标识了 SQL 如何与存储的数据进行交互。

下面的表格列出了 SQL 中通用的数据类型：

2 SQL 数据类型快速参考手册

然而，不同的数据库对数据类型定义提供不同的选择。

下面的表格显示了各种不同数据库平台上一些数据类型的通用名称：

SQL 用于各种数据库的数据类型

1 Microsoft Access 数据类型

2 MySQL 数据类型

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date / Time（日期/时间）类型。

int（9）显示结果为000000010
int（3）显示结果为010

3 SQL Server 数据类型