存储过程procedure、触发器Trigger、视图View、数据库表设计

一、创建一张表，表的字段和数据来自一条查询语句

1.语法：

* 创建一张表会把另一张表中的字段和对应的数据全部复制过去
create table 表名 as select * from 另一张表 where 1=1
* 只要字段不要数据
create table 表名 as select * from 另一张表 where 1=2;
* 只要部分字段
create table 表名 as select 表名.字段名,表名.字段名 from 另一张表 where 1=2;
* 创建的一张表也可以来自一个子查询
create table 表名 as 子查询

2.注意：

当where条件里面的语句为true时，会将字段和数据全部复制过去；
为false时，只复制字段

二、存储过程procedure

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

DELIMITER $$ 

CREATE
   
    PROCEDURE `mylx`.`myTestPro`()
   
    BEGIN

    END$$

DELIMITER ;

• 注意：

创建存储过程是需要管理员分配权限的
DELIMITER $$    用$$表示mysql语句的结束
DELIMITER ;   表示过程结束，恢复成默认的

• 参数

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

• 调用存储过程

call 存储过程名;

• 删除存储过程

drop procedure 存储过程名

• 查看存储过程

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

• 带有IF逻辑的存储过程

DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
	IF num=1 THEN
		SET str='星期一';
	ELSEIF num=2 THEN              --注意elseif  连在一块
		SET str='星期二';
	ELSEIF num=3 THEN
		SET str='星期三';             -- 注意要用分号结束
	ELSE
		SET str='输入错误';
	END IF;                        -注意要结束if  后面有分号
END $$

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

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

• REPEAT循环的存储过程

DELIMITER $
 CREATE PROCEDURE proRepeat(OUT outnum INT)
     BEGIN
     DECLARE i INT DEFAULT 0;
     REPEAT
         SET i=i+1;
         UNTIL i>=5  -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
     END REPEAT; -- 记着结束
     SET outnum=i;
  END $
  CALL proRepeat(@num);
  SELECT @num;

• loop循环的存储过程

 DELIMITER $
 CREATE PROCEDURE proLoop(OUT outnum INT)
  BEGIN
     DECLARE i INT DEFAULT 0;
      myloop:LOOP    -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
         SET i=i+1;
        IF i>=5 THEN
            LEAVE myloop;  -- 根据我的循环标号中断循环 leave 是中断循环的意思
         END IF;
    END LOOP;
     SET outnum=i;
  END $
  
  CALL proLoop(@num);
  SELECT @num;

1.控制循环的两个关键字

leave相当于java中的break
iterate相当于java中的continue

2.变量

1. 全局变量

全局变量：可以在多个会话中去访问他
——查看所有的全局变量show variables
——查看某个全局变量select @@变量名
——修改全局变量set 变量名=新值

2. 会话变量

会话变量：只存在当前客户端与数据库服务器端的一次连接中。如果连接断开，该会话变量全部丢失
——定义会话变量set @变量名=值
——查看会话变量select @变量

3. 局部变量

在存储过程中使用的变量就是局部变量。只要存储过程执行完毕，局部变量就丢失
——定义局部变量DECLARE i int DEFAULT 1;
——给变量设置值set i=10;

三、触发器Trigger

• 概念
  触发器是数据库中的一个对象，相当于JS中的监听器，触发器可以监听 增删改 三个动作
• 语法

DELIMITER $$

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

    END$$

DELIMITER ;

BEFORE行为发生之前就触发
AFTER行为发生之后触发
FOR EACH ROW行级触发，每操作一行就触发

• old和new
  old. 字段，可以获取到被监听的表中的字段的旧值
  new. 字段，可以获取到被监听表中更新后的字段的新值

四、视图View

• 概念
  视图：有结构（有行有列）,但没有结果（结构中不真实存储数据）的虚拟的表。虚拟表的结构来源不是自己定义，而是从对应的基表中产生。
• 创建视图的语法

create view 视图名 as select * from 表名;

注意：创建多表视图，不要查询两张表中的同名字段，不然会报错
create view 视图名 as select a.字段名,b.字段名 from a,b where a.id=b.id;

• 查看视图（其实视图就是一张虚拟表，关于查询表的语句，对视图都是可用的）

——查看所有的视图show views;
——查看视图结构desc 视图名
——查看创建视图的语句show create view 视图名;

• 视图一旦创建，系统就会在视图对应的数据库文件夹下，创建一个对应的结构文件 frm文件
• 视图的使用：视图的使用，只是为了查询，可以把视图当做表一样去使用
  视图的执行：本质就是执行封装的select语句
• 删除视图

drop view 视图名;

• 修改视图：视图本身不可以修改，但是视图的来源是可以修改的（其实就是修改select语句）

alter view 视图名 as 新的select语句

• 视图的意义：

（1）视图可以节省SQL语句，将一条复杂的查询语句，使用视图保存，以后可以直接对视图进行操作。
（2）数据安全，视图操作主要针对查询语句的，如果对视图结构进行处理（比如删除），不会影响基表的数据，相对来说数据比较安全。
（3）视图往往是在大项目中去使用，而且是多系统中去使用，可以对外提供一些有用的数据，隐藏一些关键的数据。
（4）视图对外可以提供友好的数据；不同的视图提供不同的数据。
（5）视图可以更好的进行权限控制，比如对外隐藏一些基表的名称

• 视图数据的操作：视图是可以进行数据操作的，但是有很多限制

1. 视图插入数据

（1）多表视图不能插入数据
（2）单表视图可以插入数据
（3）视图是可以向基表中插入数据的（视图的操作是影响基表的）

2. 视图删除数据：

多表视图不能删除数据
单表视图可以删除数据，也会影响基表

3. 视图更新数据

（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; 就会报错 不允许改 因为做了限制

五、函数

函数包括内置函数、和自定义函数

• 自定义函数的语法

DELIMITER $$
  
  CREATE
     
      FUNCTION `mytestdb`.`myFun`(num INT)
      RETURNS INT
     
      BEGIN
  	DECLARE i INT DEFAULT 100;
  	SET i=i+num;
      RETURN i;
      END$$
  
  DELIMITER ;

• 函数的调用 select 函数名();
• 函数和存储过程的区别：
  存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out实现返回值这个作用
  存储过程有in out inout这几个参数类型，函数的参数全是用来收实参

六、数据库的权限（DCL）

七、数据库表设计