Welcome to LeaCoder's Blog!

持之以恒终有收获，自律自强方至成功

《MySQL必知必会》学习笔记目录

2019-06-16

leacoder

MySQL

MySQL

Read All

LeetCode-200--岛屿数量(Number-of-Islands)

2019-06-16

leacoder

Algorithm

Algorithm Leetcode

200. 岛屿数量
Python3 实现

200. 岛屿数量

200. 岛屿的个数
    给定一个由 '1'（陆地）和 '0'（水）组成的的二维网格，计算岛屿的数量。一个岛被水包围，并且它是通过水平方向或垂直方向上相邻的陆地连接而成的。你可以假设网格的四个边均被水包围。

    示例 1:

    输入:
    11110
    11010
    11000
    00000

    输出: 1
    示例 2:

    输入:
    11000
    11000
    00100
    00011

    输出: 3

Python3 实现

染色法 + DFS

# @author:leacoder
# @des:  染色法 + DFS 岛屿的个数


class Solution:
    # 便于 上下左右扩散
    dx = [-1, 1, 0, 0]
    dy = [ 0, 0,-1, 1]
    def numIslands(self, grid: List[List[str]]) -> int:
        if not grid or not grid[0]: return 0 # 参数判断
        self.max_x = len(grid);self.max_y = len(grid[0]);self.grid = grid
        self.visited = set() # 不修改原数据
        return sum([self.floodfill_DFS(i,j) for i in range(self.max_x) for j in range(self.max_y)])
    
    # 深度优先 染色
    def floodfill_DFS(self,x,y):
        if not self._is_valid(x,y): # 判断节点是否合法
            return 0
        self.visited.add((x,y)) # 表示节点已经访问过
        for k in range(4):
            self.floodfill_DFS( x + self.dx[k], y + self.dy[k]) # 上下左右扩散
      

染色法 + BFS

# @author:leacoder
# @des:  染色法 + BFS 岛屿的个数


class Solution:
    # 便于 上下左右扩散
    dx = [-1, 1, 0, 0]
    dy = [ 0, 0,-1, 1]
    def numIslands(self, grid: List[List[str]]) -> int:
        if not grid or not grid[0]: return 0 # 参数判断
        self.max_x = len(grid);self.max_y = len(grid[0]);self.grid = grid
        self.visited = set() # 不修改原数据
        return sum([self.floodfill_BFS(i,j) for i in range(self.max_x) for j in range(self.max_y)])
    
    # 广度优先 染色
    def floodfill_BFS(self,x,y):
        if not self._is_valid(x,y): # 判断节点是否合法
            return 0
        self.visited.add((x,y)) # 标记节点已经访问过
        queue = collections.deque() # 队列
        queue.append((x,y))
        while queue: # 不为空一直循环
            cur_x, cur_y = queue.popleft() # 从队列左端取数据
            
            for i in range(4):
                new_x, new_y = cur_x + self.dx[i], cur_y + self.dy[i] #上下左右扩散
                if self._is_valid(new_x, new_y): # 扩散后的数据是否合法
                    self.visited.add((new_x, new_y)) # 合法标记节点已经访问过
                    queue.append((new_x, new_y)) # 从右端加入队列
        return 1
    
    # 判断节点是否合法
    def _is_valid(self,x,y):
        # max_X max_y边界
        if x < 0 or x >= self.max_x or y < 0 or y >= self.max_y:
            return False
        # 是 '0' 水  或者 已经访问过（处理过）
        if self.grid[x][y] == '0' or ((x,y) in self.visited):
            return False
        return True

并查集

# @author:leacoder
# @des:  并查集 岛屿的个数

'''
1、初始化：将所有'1'（陆地）节点 root 指向自己
2、遍历：遍历所有节点，为1 相邻节点合并，为0 不管
3、遍历查询总共有多少root（可在2统计）
'''

class UnionFind(object):
    def __init__(self,grid):
        m, n = len(grid), len(grid[0])
        self.count = 0
        self.parent = [-1]*(m*n)
        self.rank = [0]*(m*n)
        for i in range(m):
            for j in range(n):
                if grid[i][j] == '1': # 为 1 时 count + 1
                    self.parent[i*n+j] = i*n+j  # 初始化：将所有'1'（陆地）节点 root 指向自己
                    self.count +=1
    
    def find(self,i):
        if self.parent[i] != i:
            self.parent[i] = self.find(self.parent[i])  # 如果parent不是自己，继续向上查找，找到属于哪个集合
        return self.parent[i] # 如果parent是自己,返回这个值
    
    def union(self,x,y):
        rootx = self.find(x) # 找 x 的 parent
        rooty = self.find(y) # 找 y 的 parent
        if rootx != rooty: # 不同 不在一个集合中
            ''' 如果不管rank 
            if self.rank[rootx] > self.rank[rooty]:
                self.parent[rooty] = rootx
            elif self.rank[rootx] < self.rank[rooty]:
                self.parent[rootx] = rooty
            else:
                self.parent[rooty] = rootx
                self.rank[rootx] += 1
            '''
            self.parent[rootx] = rooty # 将两个 合为 同一个 集合中
            self.count -=1  # 每次union  count-1
            

class Solution:
    def numIslands(self, grid: List[List[str]]) -> int:
        if not grid or not grid[0]:
            return 0

        uf = UnionFind(grid)
        directions = [(0,1),(0,-1),(-1,0),(1,0)]
        m, n = len(grid), len(grid[0])
        
        for i in range(m):
            for j in range(n):  # 遍历：遍历所有节点，为1 相邻节点合并，为0 不管
                if grid[i][j] == '0':
                    continue
                for d in directions: # 上下左右 扩散
                    nr,nc = i + d[0],j+d[1]                
                    if nr >= 0 and nc >= 0 and nr < m and nc < n and grid[nr][nc] == '1': # 坐标合法 并且 为 1
                        uf.union(i*n+j,nr*n+nc) # 为1 相邻节点合并到同一集合
        # 遍历查询总共有多少root（可在2统计）
        return uf.count # 由于 union 中 被合并为同一集合 count -1 ，最后就剩 唯一的了 

GitHub链接： https://github.com/lichangke/LeetCode

知乎个人首页： https://www.zhihu.com/people/lichangke/

简书个人首页： https://www.jianshu.com/u/3e95c7555dc7

个人Blog: https://lichangke.github.io/

欢迎大家来一起交流学习

Read All

《MySQL必知必会》学习笔记（24-29）

2019-06-15

leacoder

MySQL

MySQL
- 第二十四章使用游标
  - 24.1 游标
  - 24.2 使用游标
    - 24.2.1 创建游标
《MySQL必知必会》学习笔记目录

《MySQL必知必会》学习笔记目录

第二十四章使用游标

讲授什么是游标以及如何使用游标

24.1 游标

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

24.2 使用游标

使用游标涉及几个明确的步骤
- 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
- 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
- 对于填有数据的游标，根据需要取出（检索）各行。
- 在结束游标使用时，必须关闭游标。
24.2.1 创建游标

游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。
```
CREATE PROCEDURE processorders()
  BEGIN
    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
  END;*
注意使用 DELIMITER // 切换改变输入结束符为//，不然 ； 会被解析为结束符导致命令失败

DELIMITER //
CREATE PROCEDURE processorders()
  BEGIN
    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
  END//
DELIMITER ;
```
定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
Read All
《MySQL必知必会》学习笔记（19-23）

2019-06-11

leacoder

MySQL

MySQL
《MySQL必知必会》学习笔记目录

《MySQL必知必会》学习笔记目录

第十九章插入数据

如何利用SQL的INSERT语句将数据插入表中。

19.1 数据插入

INSERT是用来插入（或添加）行到数据库表的。插入可以用几种方式使用：
- 插入完整的行；
- 插入行的一部分；
- 插入多行；
- 插入某些查询的结果。
19.2 插入完整的行

把数据插入表中的最简单的方法是使用基本的INSERT语法，它要求指定表名和被插入到新行中的值。
```
INSERT INTO customers 
 VALUES ( NULL, 'Pep E. Lapew' , '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
```
没有输出 INSERT语句一般不会产生输出。

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。

编写INSERT语句的更安全（不过更烦琐）的方法如下：
```
INSERT INTO customers(cust_name, cust_address , cust_city , cust_state, cust_zip, cust_country,  cust_contact, cust_email)
 VALUES (  'Pep E. Lapew' , '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
```
此例子完成与前一个INSERT语句完全相同的工作，但在表名后的括号里明确地给出了列名。在插入行时， MySQL将用VALUES列表中的相应值填入列表中的对应项。 VALUES中的第一个值对应于第一个指定的列名。第二个值对应于第二个列名，如此等等。

下面的INSERT语句填充所有列（与前面的一样），但以一种不同的次序填充。因为给出了列名，所以插入结果仍然正确：
```
INSERT INTO customers(cust_name,cust_contact, cust_email ,cust_address , cust_city , cust_state, cust_zip, cust_country  )
 VALUES (  'Pep E. Lapew' , NULL, NULL, '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');
```
总是使用列的列表 一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化。

19.3 插入多个行

可以使用多条INSERT语句，甚至一次提交它们，每条语句用一个分号结束
```
INSERT INTO customers(cust_name, cust_address , cust_city , cust_state, cust_zip, cust_country)
 VALUES (  'Pep E. Lapew' , '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');
INSERT INTO customers(cust_name, cust_address , cust_city , cust_state, cust_zip, cust_country)
 VALUES (  'M. Martian' , '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
```
或者，只要每条INSERT语句中的列名（和次序）相同，可以如下组合各语句：
```
INSERT INTO customers(cust_name, cust_address , cust_city , cust_state, cust_zip, cust_country)
 VALUES (  'Pep E. Lapew' , '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'),
(  'M. Martian' , '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
```
19.4 插入检索出的数据

INSERT一般用来给表插入一个指定列值的行。但是， INSERT还存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT，顾名思义，它是由一条INSERT语句和一条SELECT语句组成的
```
INSERT INTO customers(cust_id ,cust_contact, cust_email ,cust_name ,cust_address , cust_city , cust_state, cust_zip, cust_country  )
SELECT cust_id ,cust_contact, cust_email ,cust_name ,cust_address , cust_city , cust_state, cust_zip, cust_country FROM custnew;
```
这个例子使用INSERT SELECT从custnew中将所有数据导入customers。

这个例子导入了cust_id（假设你能够确保cust_id的值不重复）。也可以简单地省略这列（从INSERT和SELECT中），这样MySQL就会生成新值。

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

第二十章更新和删除数据

如何利用UPDATE和DELETE语句进一步操纵表数据。

20.1 更新数据

为了更新（修改）表中的数据，可使用UPDATE语句。可采用两种方式使用UPDATE：
- 更新表中特定行
- 更新表中所有行
不要省略WHERE子句 在使用UPDATE时一定要注意细心。因为稍不注意，就会更新表中所有行。

UPDATE语句由3部分组成，分别是：
- 要更新的表；
- 列名和它们的新值；
- 确定要更新行的过滤条件。
客户10005现在有了电子邮件地址，因此他的记录需要更新，语句如下：
```
UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
```
更新多个列的语法稍有不同：
```
UPDATE customers SET cust_name = 'The Fudds' , cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
```
为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。
```
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;
```
20.2 删除数据

为了从一个表中删除（去掉）数据，使用DELETE语句。可以两种方式使用DELETE
- 从表中删除特定的行；
- 从表中删除所有行。
下面的语句从customers表中删除一行：
```
DELETE FROM customers WHERE cust_id = 10006;
```
在这个例子中，只删除客户10006。如果省略WHERE子句，它将删除表中每个客户。

DELETE不需要列名或通配符。 DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。

如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。换句话说，如果执行UPDATE而不带WHERE子句，则表中每个行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。

第二十一章更新和删除数据

表的创建、更改和删除的基本知识。

21.1 创建表

为了用程序创建表，可使用SQL的CREATE TABLE语句。

21.1.1 表创建基础

为利用CREATE TABLE创建表，必须给出下列信息：
- 新表的名字，在关键字CREATE TABLE之后给出；
- 表列的名字和定义，用逗号分隔。
```
CREATE TABLE customers
(
  cust_id                 int                NOT NULL AUTO_INCREMENT,
  cust_name          char(50)       NOT NULL,
  cust_address      char(50)       NULL,
  cust_city             char(50)       NULL,
  cust_state           char(50)       NULL,
  cust_zip              char(50)       NULL,
  cust_country       char(50)       NULL,
  cust_contact        char(50)       NULL,
  cust_email           char(255)     NULL,
  PRIMARY KEY (cust_id)
) ENGINE = InnoDB;
```
表的主键可以在创建表时用PRIMARY KEY关键字指定。

处理现有的表 在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表， SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。

21.1.2 使用NULL值

允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。

每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。
```
CREATE TABLE orders
(
  order_num  int NOT NULL AUTO_INCREMENT,
  order_date  datetime  NOT NULL ,
  cust_id    int    NOT NULL,
  PRIMARY KEY (order_num)
) ENGINE = InnoDB;
```
理解NULL 不要把NULL值与空串相混淆。 NULL值是没有值，它不是空串。如果指定’‘（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。 NULL值用关键字NULL而不是空串指定

21.1.3 主键再介绍

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名。

主键和NULL值 主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。

21.1.4 使用AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时， MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

21.1.5 指定默认值

如果在插入行时没有给出值， MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。
```
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
```
quantity列包含订单中每项物品的数量。在此例子中，给该列的描述添加文本DEFAULT 1指示MySQL，在未给出数量的情况下使用数量1。

21.1.6 引擎类型

MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

以下是几个需要知道的引擎：
- InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
- MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
- MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
外键不能跨引擎 混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

21.2 更新表

为更新表定义，可使用ALTER TABLE语句。

为了使用ALTER TABLE更改表结构，必须给出下面的信息：
- 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
- 所做更改的列表。
下面的例子给表添加一个列：
```
ALTER TABLE vendors ADD vend_phone CHAR(20);
```
删除刚刚添加的列，可以这样做：
```
ALTER TABLE vendors DROP COLUMN vend_phone;
```
ALTER TABLE的一种常见用途是定义外键。

21.3 删除表

删除表（删除整个表而不是其内容）非常简单，使用DROP TABLE语句即可：
```
DROP TABLE customers3;
```
21.4 重命名表

使用RENAME TABLE语句可以重命名一个表：
```
RENAME TABLE customers2 TO customers3;
```
第二十二章使用视图

介绍视图究竟是什么，它们怎样工作，何时使用它们。我们还将看到如何利用视图简化前面章节中执行的某些SQL操作。

22.1 视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

理解视图的最好方法是看一个例子。下面的SELECT语句从3个表中检索数据：
```
SELECT cust_name, cust_contact FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2';
```
此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构，并且知道如何创建查询和对表进行联结。为了检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE子句。现在，假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据： SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = ‘TNT2’; productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询（与上面用以正确联结表的相同的查询）。

22.1.1 为什么使用视图
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

22.1.2 视图的规则和限制

关于视图创建和使用的一些最常见的规则和限制。
- 与表一样，视图必须唯一命名
- 对于可以创建的视图数目没有限制。
- 为了创建视图，必须具有足够的访问权限。
- 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
- 视图不能索引，也不能有关联的触发器或默认值
- 视图可以和表一起使用。
22.2 使用视图

视图的创建
- 视图用CREATE VIEW语句来创建
- 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
- 用DROP删除视图，其语法为DROP VIEW viewname;。
- 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。
22.2.1 利用视图简化复杂的联结

视图的最常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结。
```
CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id 
FROM customers, orders, orderitems 
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;
```
这条语句创建一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。为检索订购了产品TNT2的客户，可如下进行
```
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
```
22.2.2 用视图重新格式化检索出的数据

视图的另一常见用途是重新格式化检索出的数据。
```
SELECT Concat( RTrim(vend_name), '(', RTrim(vend_country), ')' ) AS vend_title 
FROM vendors ORDER BY vend_name;
```
假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。
```
CREATE VIEW vendorlocations AS SELECT Concat( RTrim(vend_name), '(', RTrim(vend_country), ')' ) AS vend_title
FROM vendors ORDER BY vend_name;
```
```
SELECT * FROM vendorlocations;
```
22.2.3 用视图过滤不想要的数据

视图对于应用普通的 WHERE 子句也很有用。
```
CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email 
FROM customers WHERE cust_email IS NOT NULL;
```
这里的WHERE子句过滤了cust_email列中具有NULL值的那些行，使他们不被检索出来。
```
SELECT * FROM customeremaillist;
```
22.2.4 使用视图与计算字段

视图对于简化计算字段的使用特别有用。
```
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems WHERE order_num = 20005;
```
将其转换为一个视图
```
CREATE VIEW orderitemsexpanded AS 
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems;
```
为检索订单20005的详细内容
```
SELECT * FROM orderitemsexpanded  WHERE order_num = 20005;
```
22.2.5 更新视图

迄今为止的所有视图都是和SELECT语句使用的。然而，视图的数据能否更新？答案视情况而定。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：
- 分组（使用GROUP BY和HAVING）；
- 联结；
- 子查询；
- 并；
- 聚集函数（Min()、 Count()、 Sum()等）；
- DISTINCT；
- 导出（计算）列。
第二十三章使用存储过程

介绍什么是存储过程，为什么要使用存储过程以及如何使用存储过程，并且介绍创建和使用存储过程的基本语法。

23.1 存储过程

迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

23.2 为什么要使用存储过程

23.3 使用存储过程

23.3.1 执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。 CALL接受存储过程的名字以及需要传递给它的任意参数。
```
CALL productpricing ( @pricelow, @pricehigh, @priceaverage);
```
执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

23.3.2 创建存储过程

一个例子——一个返回产品平均价格的存储过程
```
CREATE PROCEDURE productpricing() 
BEGIN
    SELECT Avg(prod_price) AS priceaverage  FROM products;
END;
```
直接这样会报错此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句.

mysql命令行客户机的分隔符 默认的MySQL语句分隔符为;mysql命令行实用程序也使用;作为语句分隔符。如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示：
```
DELIMITER //
CREATE PROCEDURE productpricing() 
BEGIN
    SELECT Avg(prod_price) AS priceaverage  FROM products;
END //
DELIMITER ;
```
其中， DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END//而不是END;。这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER ;

使用这个存储过程
```
CALL productpricing() ;
```
23.3.3 删除存储过程

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。

为删除刚创建的存储过程，可使用以下语句：
```
DROP PROCEDURE productpricing;
```
23.3.4 使用参数

以下是productpricing的修改版本
```
CREATE PROCEDURE productpricing(
  OUT pl DECIMAL(8,2),  
  OUT ph DECIMAL(8,2),  
  OUT pa DECIMAL(8,2)
) 
BEGIN
    SELECT Min(prod_price) 
    INTO pl
    FROM products;
     SELECT MAX(prod_price) 
    INTO ph
    FROM products;
     SELECT Avg(prod_price) 
    INTO pa
    FROM products;
END;
```
同样可能需要DELIMITER 改变为如下
```
DELIMITER //
CREATE PROCEDURE productpricing(
  OUT pl DECIMAL(8,2),  
  OUT ph DECIMAL(8,2),  
  OUT pa DECIMAL(8,2)
) 
BEGIN
    SELECT Min(prod_price) 
    INTO pl
    FROM products;
     SELECT MAX(prod_price) 
    INTO ph
    FROM products;
     SELECT Avg(prod_price) 
    INTO pa
    FROM products;
END//

DELIMITER ;
```
```
CALL productpricing ( @pricelow, @pricehigh, @priceaverage);
```
这条CALL语句给出3个参数。它们是存储过程将保存结果的3个变量的名字。

变量名所有MySQL变量都必须以@开始。
```
SELECT @priceaverage;
```
```
SELECT @pricelow, @pricehigh, @priceaverage;
```
23.3.5 建立智能存储过程
```
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
  DECLARE total DECIMAL(8,2);
  DECLARE taxrate INT  DEFAULT 6;
  SELECT Sum(item_price*quantity)
  FROM orderitems WHERE order_num = onumber 
  INTO total;
  IF taxable THEN
      SELECT total+(total/100*taxrate) INTO total;
  END IF;
  SELECT total INTO ototal;
END;
```
注意使用 DELIMITER // 转
```
DELIMITER //
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
  DECLARE total DECIMAL(8,2);
  DECLARE taxrate INT  DEFAULT 6;
  SELECT Sum(item_price*quantity)
  FROM orderitems WHERE order_num = onumber 
  INTO total;
  IF taxable THEN
      SELECT total+(total/100*taxrate) INTO total;
  END IF;
  SELECT total INTO ototal;
END//
DELIMITER ;
```
注意 DELIMITER 与 ;之间的空格

添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。在存储过程体中，用DECLARE语句定义了两个局部变量。 DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默认被设置为6%）。 SELECT语句已经改变，因此其结果存储到total（局部变量）而不是ototal。 IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。

COMMENT关键字 本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。
```
CALL ordertotal(20005, 0 , @total);
SELECT @total;
```
```
CALL ordertotal(20005, 1 , @total);
SELECT @total;
```
IF语句 这个例子给出了MySQL的IF语句的基本用法。 IF语句还支持ELSEIF和ELSE子句

23.3.6 检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATEPROCEDURE语句：
```
SHOW CREATE PROCEDURE ordertotal;
```
为了获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS。

限制过程状态结果 SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式，例如：
```
SHOW PROCEDURE STATUS LIKE 'ordertotal';
```
GitHub链接： https://github.com/lichangke/LeetCode

知乎个人首页： https://www.zhihu.com/people/lichangke/

简书个人首页： https://www.jianshu.com/u/3e95c7555dc7

个人Blog: https://lichangke.github.io/

欢迎大家来一起交流学习
Read All
《MySQL必知必会》学习笔记（15-18）

2019-06-03

leacoder

MySQL

MySQL

Read All
《MySQL必知必会》学习笔记（9-14）

2019-05-24

leacoder

MySQL

MySQL

Read All