MySql进阶篇

视图

什么是视图

        视图是基于查询的虚拟表。就是一条select语句执行后返回的结果集。

        select 语句查询的表称为视图的基表，查询的结果集沉稳给虚拟表，驶入本身并不储存具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会跟着改变。

为什么使用视图

        使用视图是为了方便复杂的查询语句。基本思路是将复杂的语句定义在视图内部，然后对视图进行查询，从而简化复杂的查询语句。

-- 定义视图
CREATE VIEW 视图名 AS SELECT 列1，列2... FROM 表(查询语句)
-- 使用视图
SELECT * FROM 视图名
-- 删除视图
drop view 视图名

存储过程

        将一些逻辑处理的代码可以事先存储在数据库中，然后使用时直接调用即可。可以减少应用程序与数据库之间交互的次数

语法

        存储过程事先需要创建(包含逻辑)，可以向存储过程中传入参数

        参数分为3种:   in(输入参数)，   out(输出参数)，  inout(既可以出入，也可以输出)

存储过程的定义

-- 创建存储过程的语法格式
create procedure 存储过程名 (in 变量名 类型，out 参数2 类型，...)
    begin
        [declare 变量名 类型 [default 值];]
        存储过程语句块
    end;

注:存储过程种的语句必须包含在begin和end之间        

     declare中用来声明变量，变量默认赋值使用default,语句块中改变变量值，使用set 变量名= 值;

存储过程使用

        1、定义一个有参数的存储过程

DELIMITER $$
-- 定义一个有参数的存储过程
CREATE
    PROCEDURE `newsdb`.`test1`(IN p_type INT,OUT p_count INT)
   	BEGIN
	  -- 把sql中查询的结果赋给 变量
	  SELECT COUNT(*) INTO p_count FROM admin WHERE TYPE =P_type;
	  -- 输出
	  SELECT p_count;

	END$$

DELIMITER ;

-- 测试
CALL test1(1,@p_count);

        2、流程控制语句

DELIMITER $$
-- 流程控制语句(if else)
CREATE
    PROCEDURE `newsdb`.`test2`(IN p_day INT)

	BEGIN
	  IF p_day =0 THEN
		SELECT "星期天";
	  ELSEIF p_day=1 THEN
		SELECT "星期一";
	  ELSEIF p_day=2 THEN
		SELECT "星期二";
	  ELSE 
		SELECT "无效日期";
	  END IF;
	END$$

DELIMITER ;
-- 测试
CALL test2(1);

        3、case when

DELIMITER $$
-- case when
CREATE
    PROCEDURE `newsdb`.`test3`(IN p_day INT)
	BEGIN
	  CASE WHEN p_day = 0 THEN
		SELECT "星期天";
	  ELSE 
		SELECT "无效日期";
	  END CASE;
	END$$

DELIMITER ;
-- 测试
CALL test3(0);

         4、循环

DELIMITER $$
-- 循环
CREATE

    PROCEDURE `newsdb`.`test4`()
	BEGIN
	DECLARE var_num INT DEFAULT 0;
	-- 循环开始
	addnum:LOOP
		SET var_num = var_num + 1; -- 循环语句
	-- 循环结束条件
	IF var_num = 10 THEN
		LEAVE addnum;
	END IF;
	END LOOP;
		SELECT var_num;
	END$$

DELIMITER ;

-- 测试
CALL test4();

        5、使用存储过程插入信息

DELIMITER $$

CREATE
    PROCEDURE `newsdb`.`saveAdmin`(IN p_account VARCHAR(20),INT p_password VARCHAR(20),OUT p_res INT);

	BEGIN
		SELECT COUNT(*) INTO p_res FROM admin WHERE account = p_account;
		IF p_res = 0 THEN 
			INSERT INTO admin(admin,PASSWORD) VALUE (p_account,p_password);
		ELSE
			SELECT p_res;
		END IF;

	END$$

DELIMITER ;

-- 测试
CALL saveAdmin("admin1","111",@p_res);

函数

函数的定义

create function 函数名([参数列表]) returns 数据类型
    begin
            DECLARE 变量；
            sql 语句;
        return 值;
    end;

注:

        1.参数列表包含两部分：参数名 参数类型

        2.函数体：肯定会有 return 语句，如果没有会报错

        3.函数体中仅有一句话，则可以省略 begin end

        4.使用 delimter 语句设置结束标记

设置函数可以没有参数

        SET GLOBAL log_bin_trust_function_creators=TRUE;

删除函数

        DROP FUNCTION 函数名;

函数的使用

        1、不带参数

-- 无参
DELIMITER$$
CREATE FUNCTION test() RETURNS INT
BEGIN
	DECLARE v_num INT DEFAULT 0;
	SELECT COUNT(*) INTO v_num FROM admin;
	RETURN v_num;
END$$
-- 无参测试
SELECT test() FROM DUAL;

2、带参数

-- 有参
DELIMITER$$
CREATE FUNCTION findAdminById(p_id INT) RETURNS VARCHAR(20)
BEGIN
	DECLARE v_account VARCHAR(20);
	SELECT account INTO v_account FROM admin WHERE id =p_id;
	RETURN v_account;

END$$

-- 有参测试
SELECT a.*,findAdminById(a.id) FROM admin a

DELIMITER$$
CREATE FUNCTION turnType(p_type INT) RETURNS VARCHAR(20)
BEGIN
	IF p_type=0 THEN
		RETURN "超级管理员";
	ELSE 
		RETURN "普通用户";
	END IF;
END$$


SELECT a.account,turnType(a.type)atype FROM admin a

触发器

触发器是一种特殊的存储过程，其特殊性在于它并不需要用户直接调用，而是在对表添加，修改，删除之前或者之后自动执行的存储过程。

特点       

        1、与表相关联

               触发器定义在特定的表上，这个表称为触发表

        2、自动激活触发器

               当对表种的数据执行INSERT、UPDATE或DELETE操作时，如果对表上的这个特定操作           定义了触发器，改触发器自动执行，这是不可撤销的。

        3、不能直接调用             

                与存储过程不同，触发器不能被直接调用，也不能传递或接受参数

        4、作为事务的一部分

                触发器与激活触发器的语句一起作为一个单一的事务来对待，可以从触发器中的任何位            置回滚

定义触发器的语法规则

CREATE TRIGGER 触发器名称 触发时机 触发事件
    ON 表名称
    FOR EACH ROW -- 行级触发
        BEGIN
            语句
        END;
-- 触发时机分为 before 和  after

MySql架构

1、连接层：负责接收客户端的连接请求，可以进行认证(验证账号密码)

2、服务层：接受sql，语法解析，优化，缓存

3、引擎层：引擎层时真正落地实现的具体方式，不同的存储引擎特点不同

4、物理文件存储层：使用各种文件用来存储数据，以及各种日志文件

MySql引擎

        存储引擎是具体的操作数据的方式

        innodb：支持事务，支持行级锁(一个事务对某行数据操作时，只会锁定某一行数据，不锁定其他行，效率高)，支持外键约束，支持缓存，全文索引，不会存储表中的总行数

        MyISAM：不支持事务，不支持主外键，不支持行级锁，支持表锁(进行dml操作时会锁定整张表)，主要用于查询多，增删改比较少的场景。支持全文索引，存储表的总行数。