SQL必知必会学习笔记(四)
学极客时间的 SQL必知必会专栏,做记录(视图、存储过程)
视图
视图,也就是我们今天要讲的虚拟表,本身是不具有数据的,它是 SQL 中的一个重要概念。它一方面可以帮我们使用表的一部分而不是所有的表,另一方面也可以针对不同的用户制定不同的查询视图。
视图作为一张虚拟表,帮我们封装了底层与数据表的接口。它相当于是一张表或多张表的数据结果集。
创建视图:CREATE VIEW
CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition
-- 使用视图
SELECT * FROM view_name
实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW,这样就会基于 SQL 语句的结果集形成一张虚拟表。其中 view_name 为视图名称,column1、column2 代表列名,condition 代表查询过滤条件。当视图创建之后,它就相当于一个虚拟表,可以直接使用。
嵌套视图
当我们创建好一张视图之后,还可以在它的基础上继续创建视图
CREATE VIEW view_name1 AS SELECT column1 FROM view_name
修改视图:ALTER VIEW
ALTER VIEW view_name AS
SELECT column1, column2, column3
FROM table
WHERE condition
删除视图:DROP VIEW
DROP VIEW view_name
需要说明的是,SQLite 不支持视图的修改,仅支持只读视图,也就是说你只能使用 CREATE VIEW 和 DROP VIEW,如果想要修改视图,就需要先 DROP 然后再 CREATE。
如何使用视图简化 SQL 操作
- 利用视图完成复杂的连接
- 利用视图对数据进行格式化
- 使用视图与计算字段
正确使用视图可以简化复杂的 SQL 查询,让 SQL 更加清爽易用。不过有一点需要注意,视图是虚拟表,它只是封装了底层的数据表查询接口,因此有些 RDBMS 不支持对视图创建索引(有些 RDBMS 则支持,比如新版本的 SQL Server)。
我们也需要了解到视图是虚拟表,本身不存储数据,如果想要通过视图对底层数据表的数据进行修改也会受到很多限制,通常我们是把视图用于查询,也就是对 SQL 查询的一种封装。那么它和临时表又有什么区别呢?在实际工作中,我们可能会见到各种临时数据。比如你可能会问,如果我在做一个电商的系统,中间会有个购物车的功能,需要临时统计购物车中的商品和金额,那该怎么办呢?这里就需要用到临时表了,临时表是真实存在的数据表,不过它不用于长期存放数据,只为当前连接存在,关闭连接后,临时表就会自动释放。
存储过程
SQL 的存储过程和视图一样,都是对 SQL 代码的封装,可以反复利用。它和视图有着同样的优点,清晰、安全,还可以减少网络传输量。不过它和视图不同,视图是虚拟表,通常不对底层数据表直接操作,而存储过程是程序化的 SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。存储过程可以说是由 SQL 语句和流控制语句构成的语句集合,它和我们之前学到的函数一样,可以接收输入参数,也可以返回输出参数给调用者,返回计算结果。
存储过程的英文是 Stored Procedure。它的思想很简单,就是 SQL 语句的封装。一旦存储过程被创建出来,使用它就像使用函数一样简单,我们直接通过调用存储过程名即可。存储过程实际上由 SQL 语句和流控制语句共同组成。
-- 定义一个存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
需要执行的语句
END
和视图一样,删除已经创建的存储过程,使用的是 DROP PROCEDURE ,如果要更新存储过程,我们需要使用 ALTER PROCEDURE 。
参数类型
参数类型 | 是否返回 | 作用 |
---|---|---|
IN | 否 | 向存储过程传入参数,存储过程中修改该参数的值,不能被返回。 |
OUT | 是 | 把存储过程计算的结果放到该参数中,调用者可以得到返回值。 |
INOUT | 是 | IN和OUT的结合,既用于存储过程的传入参数,同时又可以把计算结果放到参数中,调用者可以得到返回值。 |
流控制语句
- BEGIN…END:BEGIN…END 中间包含了多个语句,每个语句都以(;)号为结束符。
- DECLARE:DECLARE 用来声明变量,使用的位置在于 BEGIN…END 语句中间,而且需要在其他语句使用之前进行变量的声明。
- SET:赋值语句,用于对变量进行赋值。
- SELECT…INTO:把从数据表中查询的结果存放到变量中,也就是为变量赋值。
- IF…THEN…ENDIF:条件判断语句,我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。
- CASE:CASE 语句用于多条件的分支判断。
- LOOP、LEAVE 和 ITERATE:LOOP 是循环语句,使用 LEAVE 可以跳出循环,使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验,你可以把 LEAVE 理解为 BREAK,把 ITERATE 理解为 CONTINUE。
- REPEAT…UNTIL…END REPEAT:这是一个循环语句,首先会执行一次循环,然后在 UNTIL 中进行表达式的判断,如果满足条件就退出,即 END REPEAT;如果条件不满足,则会就继续执行循环,直到满足退出条件为止。
- WHILE…DO…END WHILE:这也是循环语句,和 REPEAT 循环不同的是,这个语句需要先进行条件判断,如果满足条件就进行循环,如果不满足条件就退出循环。
CASE
WHEN expression1 THEN ...
WHEN expression2 THEN ...
...
ELSE
--ELSE语句可以加,也可以不加。加的话代表的所有条件都不满足时采用的方式。
END
关于存储过程使用的争议
优点:首先存储过程可以一次编译多次使用。存储过程只在创造时进行编译,之后的使用都不需要重新编译,这就提升了 SQL 的执行效率。其次它可以减少开发工作量。将代码封装成模块,实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以重复使用,在减少开发工作量的同时,还能保证代码的结构清晰。还有一点,存储过程的安全性强,我们在设定存储过程的时候可以设置对用户的使用权限,这样就和视图一样具有较强的安全性。最后它可以减少网络传输量,因为代码封装到存储过程中,每次使用只需要调用存储过程即可,这样就减少了网络传输量。同时在进行相对复杂的数据库操作时,原本需要使用一条一条的 SQL 语句,可能要连接多次数据库才能完成的操作,现在变成了一次存储过程,只需要连接一次即可。
缺点:
- 可移植性差,存储过程不能跨数据库移植,比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程,在换成其他数据库时都需要重新编写。
- 调试困难,只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说,开发和维护都不容易。
- 版本管理困难,比如数据表索引发生变化了,可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理,但是存储过程本身没有版本控制,版本迭代更新的时候很麻烦。
- 不适合高并发的场景,高并发的场景需要减少数据库的压力,有时数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了。