MySQL知识梳理（三）

MySQL知识梳理（三）

八.视图

1.定义：视图是存储在数据库中的查询的sql 语句，是一个虚拟表，其内容由查询定义

2.作用：临时表，用来筛选

3.优点：

     1)简单化，数据所见即所得
     2)安全性，用户只能查询或修改他们所能见到得到的数据
     3)逻辑独立性，可以屏蔽真实表结构变化带来的影响

4.缺点：性能差，修改不方便

5.创建语法：

     CREATE 
     VIEW '视图1'
     AS 查询语句

 #在学生表和年级表形成视图，展示学生id，学生姓名，年纪id，年级名称
      CREATE VIEW v2
      AS
      SELECT studentno,studentname,g.gradeid,gradename
      FROM student s
      JOIN grade g
      ON s.gradeid=g.gradeid

6.查看视图

     SELECT *FROM '视图1' 

7.删除视图

     DROP VIEW IF EXISTS v2   

九.存储过程(Stored PROCEDURE)

1.定义：一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

2.作用：为了完成特定功能的sql语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数来调用执行

3.优点：

     (1)存储过程可封装，并隐藏复杂的商业逻辑。
     (2)存储过程可以回传值，并可以接受参数。
     (3)存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
     (4)存储过程可以用在数据检验，强制实行商业逻辑等

4.缺点：

     (1)存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
     (2)存储过程的性能调校与撰写，受限于各种数据库系统。

5.创建存储过程

       DELIMITER @@
       CREATE PROCEDURE test(IN p_in INT)
       BEGIN
       
       END @@
       DELIMITER ;
       CALL test(参数);

 (1)DELIMITER @@:自定义语句结束符
 (2)CREATE PROCEDURE test(IN p_in INT):声明存储过程
 (3)BEGIN  END @@:存储过程开始和结束符号
 (4)SET @p_in=1:变量赋值
 (5)DECLARE l_int INT UNSIGNED DEFAULT 40000:变量定义
 (6)CALL test(参数)：调用存储过程

6.存储过程的参数：

 CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

 (1)IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
 (2)OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
 (3)INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

7.变量

DECLARE variable_name [,variable_name...] datatype [DEFAULT VALUE];

  (1)datatype为MySQL的数据类型：int，float，date，varchar

 DECLARE l_int INT UNSIGNED DEFAULT 4000000;

  (2)变量赋值

 SET 变量名 = 表达式值 [,variable_name = expression ...]

8.查询存储过程

      (1) SELECT `name` FROM mysql.proc WHERE db='数据库名';
      (2)SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
      (3)SHOW PROCEDURE STATUS WHERE db='数据库名';
      (4)SHOW CREATE PROCEDURE 数据库.存储过程名;

9.修改存储过程

 ALTER PROCEDURE

10.删除存储过程

   DROP PROCEDURE

11.MySQL存储过程中的控制语句

     (1) IF-THEN-ELSE 语句
      (2) CASE 语句
      (3) WHILE 条件 DO 循环体 END WHILE
      (4) REPEAT 循环体 UNTIL 循环条件 END REPEAT
      (5) LOOP LEAVE END LOOP    
      (6) lables 标号 ：标号可以用在 BEGIN REPEAT WHILE 或者 LOOP 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。 
      (7)ITERATE 迭代
       

具体使用：参考https://www.runoob.com/w3cnote/mysql-stored-procedure.html

十.udf自定义函数

     CREATE FUNCTION 函数名([参数列表]) RETURNS 数据类型
     BEGIN
     sql语句;
     RETURN 值;
     END;

   eg：
     DELIMITER //
     CREATE FUNCTION testFail() RETURNS VARCHAR(10)
     BEGIN
     DECLARE num,`sum`INT(11) DEFAULT 0;
     WHILE num<=100
     DO
     SET num=num+1;
     SET `sum`=`sum`+num;
     END WHILE;
     RETURN `sum`;
     END//
     DELIMITER ;
     SELECT testFail();

十一.外键

1.外键：建立约束

2.创建外键：

  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段)REFERENCES 关联表名（关联字段）

 (1)建表同时加外键:

         CREATE TABLE SUBJECT(
         CONSTRAINT grade_sub_id FOREIGN KEY (GradeID)
         REFERENCES grade(GradeID)
         );

 (2)建表后添加外键:

         ALTER TABLE student
         ADD CONSTRAINT FK_gradeid FOREIGN KEY(gradeid) REFERENCES grade(gradeid);

3.删除外键

        ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
        ALTER TABLE student DROP INDEX FK_gradeid;

  注：删除具有主外键关系的表时，要先删子表，再删主表

十二.MySQL PLUS

#定时任务
#游标
#loop，while，repeat循环
#其他优化