存储过程procedure

存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由
于他在创建时，就已经对SQL语句进行了编译，所以执行效率高，而且可以重复调用，类似于我们Java中的方法。

语法：

DELIMITER $$
CREATE
    PROCEDURE `performance_schema`.`myTestPro`()
    BEGIN

    END$$
DELIMITER ;
注意：创建存储过程中需要管理员分配权限
补充：delimiter是mysql定义结束标记的，在mysql客户端中结束标记默认是分号(;)，如果一次输入的语句比
较多，并且语句中间有分号，这时需要指定一个特殊的结束符，delimiter$$表示mysql语句结束，过程结束后
肯定会有一句的delimiter;表示恢复成默认的。

参数：

in:输入参数
out:输出参数
inout:输入输出参数

控制循环的两个关键字：
leave相当于Java中的break
iterator相当于Java中的continue

例1：in输入参数
DELIMITER $$
CREATE
	PROCEDURE `myPro1`(IN num INT)
	BEGIN
    DELETE FROM emp WHERE num =empno;
    END$$
DELIMITER ;

调用存储过程：
CALL myPro1()

例2：out输出参数
DELIMITER $$
	PROCEDURE `myPro2`(IN num INT,OUT SUM INT)
	BEGIN
    DELETE FROM emp WHERE empno=num;
    SELECT COUNT(*) FROM emp INTO SUM; 
    END$$
DELIMITER ;

调用存储过程：
-- 定义一个会话变量
SET @sum=0;
CALL myPro2(7499,@sum);
-- 查询会话变量的值
SELECT @sum;

例3：inout参数
DELIMITER $$
	PROCEDURE `myPro3`(INOUT num INT)
	BEGIN
    DELETE FROM emp WHERE empno=num;
    SELECT COUNT(*) INTO num FROM emp;      
    END$$
DELIMITER ;

调用存储过程：
SET @sum=7521;
CALL myPro3(@sum);
SELECT @sum;

调用存储过程：

语法：call 表名(9527,@rr)
查询结果： select @rr

删除存储过程：

语法： drop procedure 表名;

查看存储过程show：

show procedure status\G;  -- 查看所有的存储过程状态
show create procedure 存储过程名字\G; -- 查看创建存储过程的语句

带有if逻辑的存储过程：if then elseif else

DELIMITER $$
PROCEDURE `myPro4`(IN num INT,OUT str VARCHAR(10))
BEGIN
    IF num=1 THEN
       SET str='星期一';    
    ELSEIF num=2 THEN                -- 注意：elseif连在一起，不能分开
       SET str='星期二';
    ELSEIF num=3 THEN
       SET str='星期三';
    ELSEIF num=4 THEN
       SET str='星期四';              -- 注意也要用分号结束
    ELSEIF num=5 THEN
       SET str='星期五';
    ELSEIF num=6 THEN
       SET str='星期六';
    ELSEIF num=7 THEN
       SET str='星期天';
    ELSE 
       SET str='输入错误'; 
    END IF;                           -- 注意要结束if 后面有分号
    END$$

DELIMITER ;

带有循环的存储过程：while do

DELIMITER $$
PROCEDURE `myPro5`(IN num INT,OUT SUM INT)
BEGIN
    -- 定义一个局部变量
    DECLARE i INT DEFAULT 1;
    DECLARE result INT DEFAULT 0;
    
    WHILE i<=num DO
	SET result=result+i;
	SET i=i+1;
    END WHILE;   -- 要记得结束循环
    SET SUM=result;    
    END$$

DELIMITER ;

REPEAT循环和loop循环：

REPEAT循环:

USE `mydemo`$$
PROCEDURE `myPro6`(OUT outnum INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    REPEAT
        SET i=i+1;
        UNTIL i>=5  -- 注意这里不要加分号，不然会报语法错误  until结束条件  
    END REPEAT;  -- 记着结束
    SET outnum=i;
    END$$
DELIMITER ;

loop循环:

DELIMITER $$
PROCEDURE `myPro7`(OUT outnum INT)
BEGIN
    DECLARE i INT DEFAULT 0;
       myloop:LOOP -- 这里的myloop是我给循环起的一个标号或者是别名，这是语法要求，不起就会报错
         SET i=i+1;
        IF i>=5 THEN 
	    LEAVE myloop; -- 根据我的循环标号中断循环  leave是中断循环的意思, 相当于Java中的break   
        END IF;
    END LOOP;
      SET outnum=i;
    END$$
DELIMITER ;

变量Variables

MySQL中的变量分为三种：
全局变量：也叫内置变量，在任何会话中都能够访问到
会话变量：在同一个会话中可以访问到，会话结束，变量也就消失了
局部变量：定义在函数中或者存储过程中的变量，函数调用完毕后，变量也就消失了。

1.查看所有的全局变量(内置变量)
SHOW VARIABLES;
2.查看某个全局变量的值，全局变量前面有两个@@
SELECT @@version;
3.设置全局变量的值：
SELECT @@character_set_client; -- 查看数据库编码
SELECT @@character_set_result; -- mysql服务器输出数据的代码
4.修改全局变量的值：set 变量名=新值
character_set_client: mysql服务器的接收数据的编码
character_set_results：mysql服务器输出数据的编码
SET character_set_client=gbk   –设置数据库编码
set character_set_results=gbk

SET character_set_client=utf8;   –设置数据库编码
set character_set_results=utf8;

5.定义局部变量的语法:DECLARE i INT DEFAULT 1;
给变量设置值：set i=100;

触发器Trigger

触发器：数据库中的一个对象，相当于JS中的监听器，触发可以监听 增 删 改 三个动作。
例如：我想监听一张表中的数据，只要我增删改了这张表中的数据，我就可以触发这个监听器，去另一个表中记
录一下日志
DELIMITER $$

CREATE
    TRIGGER `mytestdb`.`myTriger` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `mytestdb`.`<Table Name>`
    FOR EACH ROW 
    BEGIN
     
    END$$

DELIMITER ;
BEFORE 行为发生之前就触发
AFTER 行为发生之后触发
INSERT 插入，数据增加操作
UPDATE 更新，数据修改操作
DELETE 删除，数据删除操作
FOR EACH ROW 行级触发，每操作一行就触发
DELIMITER $$
CREATE
    TRIGGER `myTri1` AFTER INSERT ON `test` 
    FOR EACH ROW 
    BEGIN
     INSERT INTO logger VALUES(NULL,'你增加了一条数据',NULL);
    END$$；
DELIMITER ;
old.字段：可以获取到被监听的表中的字段的旧值
new.字段：可以获取到被监听的表中的字段的新值，比如说插入新值或者修改旧值

-- 例如：我往一张表t1中添加一条数据，另一张表t2也要添加一条同样的数据
DELIMITER $$
CREATE
    TRIGGER `myt1` AFTER INSERT ON `t1` 
    FOR EACH ROW 
    BEGIN
    INSERT INTO t2 VALUES(new.id,new.name,new.age);
    END$$;
DELIMITER ;

-- 例如：我往一张表t1中修改一条数据，另一张表t2也要修改一条同样的数据
DELIMITER $$
CREATE
    TRIGGER `myTri2` AFTER UPDATE ON `t1` 
    FOR EACH ROW 
    BEGIN
    UPDATE t2 SET id=new.id,NAME=new.name,age=new.age WHERE id=old.id;
    END$$;
DELIMITER ;

视图View

1.视图
视图：有结构(有行有列)，但没有结果(结构中不真是存储数据)的虚拟的表。
	虚拟表的结构来源不是自己定义，而是从对应的基表中产生(视图数据的来源).
	视图的出现是为了简化我们数据的查询，视图本身并不会存储数据，它的数据来自于查询的基表中的数据。
2.创建视图语法
 create view 视图名称  as select语句(这个语句可以是一张或多张表的普通查询，或多表查询)
 例如：创建单表视图
 CREATE VIEW my_view AS SELECT *FROM emp;
 例如：创建多表视图
 CREATE VIEW my_view3 AS SELECT user.*,orders.`id` AS oid,orders.`totalprice`,
 orders.`uer_id` FROM USER,orders WHERE user.`id`=orders.`uer_id`;
3.查看视图，跟查询表的语句是一样的。
SELECT *FROM my_view;

SELECT ename,sal FROM emp;

CREATE VIEW my_view2 AS SELECT ename,sal FROM emp;

SELECT *FROM my_view2;
4.视图一旦创建，系统会在视图对应的数据库文件夹下，创建一个对应的结构文件：frm文件。
5.视图的使用：视图的使用，就是为了简化查询，我们可以把视图当作一个表一样去使用，
例如：SELECT *FROM my_view;视图的执行，其实本质上就是执行封装的select语句。
6.删除视图：drop view 视图名称
7.修改视图：视图本身并不能修改，但是视图的来源是可以修改的(其实就是修改select语句)
alter view 视图名 as 新的select语句
8.视图的意义：
(1)视图可以节省SQL语句，将一条复杂的查询语句，使用视图进行保存，以后可以直接对视图进行操作。
(2)数据安全，视图操作注意是针对查询语句的，如果对视图结构进行处理(比如删除)，不会影响基表的数据，
所以相对来说数据比较安全。
(3)视图往往是在大项目中去使用，而且是多系统中去使用，我可以对外提供一些有用的资源，
隐藏一些关键的数据。
(4)视图对外可以提供友好的数据：不同的视图提供不同的数据，对外提供的数据好像是经过专门设计的一样。
(5)视图可以更好的进行权限控制，比如对外隐藏我的一些基表的名称。
9.视图数据的操作:视图是可以进行数据操作的(比如 增,删,改,视图中的数据),但是有很多限制
  视图插入数据:
  (1)多表视图不能插入数据
  (2)单表视图中可以插入数据(如果视图中字段没有基表中不能为空的字段且没有默认值的字段,是插入不成功的)
  (3)视图是可以向基表中插入数据的 (视图的操作是影响基表的)
  视图删除数据
  (1):多表视图不能删除数据
  (2):单表视图可以删除数据,也会影响到基表
  视图更新数据
  (1):单表视图,多表视图都可以更新数据
  	  更新限制:with check option
例如:create view my_v1 as select * from student where age>30 with check option;表示视图数据的
来源都是年龄大于30的,with check option 决定通过视图更新的时候,不能将已得到数据age>30的学生
 改成age<30 的.那么:update  my_v1 set age=20 where id=1; 就会报错 不允许改 因为做了限制