运算

定义变量

使用DECLARE来声明，DEFAULT赋默认值，SET赋值

DECLARE counter INT DEFAULT 0;   
SET counter = counter+1;  

控制语句

条件语句

IF THEN、ELSEIF、ELSE、END IF

DECLARE type int;
DECLARE c varchar(50);
SET type=10;
IF type = 0 THEN 
    set c = 'param is 0';
ELSEIF type = 1 THEN 
    set c = 'param is 1';
ELSE
    set c = 'param is others, not 0 or 1';
END IF;

循环语句

WHILE DO、END WHILE

DECLARE n int DEFAULT 10;
DECLARE s int DEFAULT 0;
WHILE i <= n DO
    set s = s + i;
END WHILE;

LOOP、END LOOP

DECLARE n int DEFAULT 0;
DECLARE s int DEFAULT 0;
my_loop:loop
    SET s=s+1;
    SET n=n+1;
    if s=10 THEN 
        leave my_loop;
    end if; 
end loop my_loop;

REPEAT、UNTIL

DECLARE n int DEFAULT 0;
REPEAT
    set n=n+1;
UNTIL n=10 END REPEAT;

CASE语句

DECLARE c varchar(500);
DECLARE type int DEFAULT 0;
CASE type
WHEN 0 THEN
    set c = 'param is 0';
WHEN 1 THEN
    set c = 'param is 1';
ELSE
    set c = 'param is others, not 0 or 1';
END CASE;

存储过程/函数

官网文档

定义

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法； 存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

特性

1. 有输入输出参数，可以声明变量，有if/else,case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
2. 函数的普遍特性：模块化，封装，代码复用；
3. 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

创建存储过程

# ################# 不带返回值 #################
# 删除已有的存储过程
DROP PROCEDURE IF EXISTS `pr_add`;
DELIMITER ;;
# 创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_add`(a int,b int)
BEGIN
    #定义变量c
   declare c int;
   if a is null then set a = 0;
   end if;
   if b is null then set b = 0;
   end if;
   set c = a + b;
   select c as sum;
END
;;
DELIMITER ;

# ################# 不带返回值 #################
# 删除已有的存储过程
DROP PROCEDURE IF EXISTS `pr_add`;
DELIMITER ;;
# 创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_add`(IN a int,IN b int,OUT sum int)
BEGIN
    #定义变量c
   declare c int;
   if a is null then set a = 0;
   end if;
   if b is null then set b = 0;
   end if;
   # 输出值
   set sum = a + b;
END
;;
DELIMITER ;
# 调用
mysql> set @a=1;
mysql> call pr_add(1,@a,@s);
mysql> select @s as sum;

在终端创建存储过程：

/*
如果您使用mysql客户端程序来定义包含分号字符的存储程序，则会出现问题。 默认情况下， mysql本身将分号识别为语句分隔符，因此必须临时重新定义分隔符，才能将整个存储的程序定义传递到服务器。使用delimiter命令，重新定义mysql分隔符。 分隔符可以由单个字符或多个字符组成
*/
//在客户端中查看所有用户，使用
mysql> delimiter $$
mysql> CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
-> SQL SECURITY INVOKER
-> BEGIN
-> SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
-> END $$
mysql> delimiter ;

创建存储函数

先查看函数功能是否开启：show variables like ‘%func%'，未开启则创建函数会报错。

解决办法有两种，

第一种是在创建子程序(存储过程、函数、触发器)时，声明为 DETERMINISTIC | NO SQL | READS SQL DATA 等中的一个，具体可以看语法; 第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。 设置方法有三种: 1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1 开启,关闭则为0; 2.MySQL启动时，加上–log-bin-trust-function-creators选项，参数设置为1 3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1

eg:

DROP FUNCTION IF EXISTS `f1`;
DELIMITER ;;
# 创建存储函数
# return 数据类型时，声明返回类型是returns, return sql语句是，声明返回类型是return;
CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(a int,b int) RETURNS int(11) DETERMINISTIC
BEGIN
    #定义变量c
   declare c int;
   if a is null then set a = 0;
   end if;
   if b is null then set b = 0;
   end if;
   return a + b;
END
;;
DELIMITER ;

存储的函数不能递归,默认情况下允许递归存储过程，但禁用。

要启用递归，请将max_sp_recursion_depth服务器系统变量设置为大于零的值。 存储过程递归增加了对线程堆栈空间的需求。 如果增加max_sp_recursion_depth的值，可能需要通过在服务器启动时增加thread_stack的值来增加线程堆栈大小

存储过程或函数与特定数据库相关联。 这有几个含义：

1.当调用例程时，执行隐式USE db_name （当程序终止时，它将被撤销）。 存储例程中的USE语句不允许。 2.可以使用数据库名称限定常规名称。这可以用于引用不在当前数据库中的例程。例如，要调用与test数据库相关联的存储过程p或函数f ，可以说CALL test.p()或test.f() 。 3.当数据库被删除时，与它相关联的所有存储例程也被删除。

查看存储过程和函数|存储函数|触发器

1）查询数据库中的存储过程/函数

# 方法一：
select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE|FUNCTION|TRIGGER';
# 方法二：
SHOW {PROCEDURE | FUNCTION} status

2）查看存储过程|函数|触发器的创建代码

//查看存储过程
SHOW CREATE {PROCEDURE | FUNCTION | TRIGGER} sp_name;

删除存储过程|函数|触发器

DROP {PROCEDURE | FUNCTION | TRIGGER } [IF EXISTS] sp_name;

存储过程和存储函数优缺点和区别

优缺点：

区别：

触发器

特点

触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行；

作用

保证数据的完整性，起到约束的作用；

弊端

增加程序的复杂度，有些业务逻辑在代码中处理，有些业务逻辑用触发器处理，会使后期维护变得困难；

语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

创建触发器

DROP TRIGGER IF EXISTS `t1`;
DELIMITER ;;
CREATE TRIGGER `t1` AFTER UPDATE ON `user` FOR EACH ROW begin
    # 用户更新历史记录
    INSERT INTO user_history(user_id,operatetype, operatetime) VALUES (new.id, 'update a user', now());
end
;;
DELIMITER ;

删除查看

视图

视图总结原地址

定义

视图是从一个或多个表（或视图）导出的表。视图是数据库的用户使用数据库的观点。可以根据他们的不同需求，在物理的数据库上定义他们对数据库所要求的数据结构，这种根据用户观点所定义的数据结构就是视图。

与表区别

视图与表（有时为与视图区别，也称表为基本表——Base Table）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

优点

1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。 2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。 3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。 4）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。 5）可以重新组织数据以便输出到其他应用程序中

注意

1）在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name 2）视图的命名必须遵循标志符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。 3）不能把规则、默认值或触发器与视图相关联。 4）不能在视图上建立任何索引，包括全文索引。

语法

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
    
     
/* 
# column_list：
要想为视图的列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的列名。column_list中的 名称数目必须等于SELECT语句检索的列数。若使用与源表或视图中相同的列名时可以省略column_list。

# or replace：
给定了OR REPLACE子句，语句能够替换已有的同名视图。

# algorithm子句：
 可选的ALGORITHM子句是对标准SQL的MySQL扩展，规定了MySQL的算法，算法会影响MySQL处理视图的方 式。ALGORITHM可取3个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是 UNDEFINED（未定义的）。指定了MERGE选项，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。 MERGE算法要求视图中的行和基表中的行具有一对一的关系，如果不具有该关系，必须使用临时表取而代之。指定了TEMPTABLE选项，视图的结果将被 置于临时表中，然后使用它执行语句。
 
 # select_statement：
用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图。但对SELECT语句有以下的限制：
1）定义视图的用户必须对所参照的表或视图有查询（即可执行SELECT语句）权限；
2）不能包含FROM子句中的子查询；
3）不能引用系统或用户变量；
4）不能引用预处理语句参数；
5）在定义中引用的表或视图必须存在；
6）若引用不是当前数据库的表或视图时，要在表或视图前加上数据库的名称；
7）在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，则视图定义中的ORDER BY将被忽略。
8）对于SELECT语句中的其他选项或子句，若视图中也包含了这些选项，则效果未定义。例如，如果在视图定义中包含LIMIT子句，而SELECT语句使用了自己的LIMIT子句，MySQL对使用哪个LIMIT未做定义。
 
# WITH CHECK OPTION：
指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修 改的数据。当视图是根据另一个视图定义的时，WITH CHECK OPTION给出两个参数：LOCAL和CASCADED。它们决定了检查测试的范围。Local关键字使CHECK OPTION只对定义的视图进行检查，cascaded则会对所有视图进行检查。如果未给定任一关键字，默认值为CASCADED。
 
 */

可更新视图

要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

1）聚合函数； 2）DISTINCT关键字； 3）GROUP BY子句； 4）ORDER BY子句； 5）HAVING子句； 6）UNION运算符； 7）位于选择列表中的子查询； 8）FROM子句中包含多个表； 9）SELECT语句中引用了不可更新视图； 10）WHERE子句中的子查询，引用FROM子句中的表； 11）ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。

插入数据

使用INSERT语句通过视图向基本表插入数据 例: 创建视图CS_XS，视图中包含计算机专业的学生信息，并向CS_XS视图中插入一条记录：（‘081255’，‘李牧’，‘计算机’，1，‘1990-10-21’，50，NULL，NULL）。 首先创建视图CS_XS：(以下的删除、修改都是用该表)

CREATEOR REPLACE VIEW CS_XS
      AS  SELECT* FROM XS
      WHERE 专业 = '计算机'  WITH CHECK OPTION;

注意: 在创建视图的时候加上WITH CHECK OPTION子句，是因为WITH CHECK OPTION子句会在更新数据的时候检查新数据是否符合视图定义中WHERE子句的条件。WITH CHECKOPTION子句只能和可更新视图一起使用。

接下来插入记录：

INSERT INTO CS_XS
    VALUES('081255', '李牧', '计算机', 1, '1990-10-14',50, NULL, NULL);

注意：这里插入记录时专业名只能为“计算机”。 这时，使用SELECT语句查询CS_XS视图和基本表XS，就可发现XS表中该记录已经被添加。

**当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。**例如，不能向视图CS_KC插入数据，因为CS_KC依赖两个基本表：XS和XS_KC。 **对INSERT语句还有一个限制：SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。**例如，若CS_XS视图定义的时候不加上“姓名”字段，则插入数据的时候会出错。

修改数据

使用UPDATE语句可以通过视图修改基本表的数据 例: 将CS_XS视图中所有学生的总学分增加8。

UPDATE CS_XS SET 总学分 = 总学分+8;

该语句实际上是将CS_XS视图所依赖的基本表XS中所有记录的总学分字段值在原来基础上增加8。 若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。

例: 将CS_KC视图中学号为081101的学生的101课程成绩改为90。

UPDATE CS_KC SET 成绩=90
    WHERE 学号='081101' AND 课程号='101';

本例中，视图CS_KC依赖于两个基本表：XS和XS_KC，对CS_KC视图的一次修改只能改变学号（源于XS表）或者课程号和成绩（源于XS_KC表）。

删除数据

使用DELETE语句可以通过视图删除基本表的数据

例: 删除CS_XS中女同学的记录。

DELETEFROM CS_XS  WHERE 性别 = 0;

**注意：对依赖于多个基本表的视图，不能使用DELETE语句。**例如，不能通过对CS_KC视图执行DELETE语句而删除与之相关的基本表XS及XS_KC表的数据。

修改已有视图的定义

语法格式：

ALTER[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]  AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

ALTERVIEW语句的语法和CREATE VIEW类似

例: 将CS_XS视图修改为只包含计算机专业学生的学号、姓名和总学分。

USEXSCJ;
ALTERVIEW CS_XS
AS  SELECT 学号,姓名,总学分  FROM XS
      WHERE 专业名 = '计算机';

查看视图

查看视图数据

select * from view_name;

查看视图状态

show tables status like view_name;

删除视图

drop VIEW [IF EXISTS] view_name1 [,view_name2]...
    [RESTRICT | CASCADE]