Mysql视图,存储过程,触发器,函数以及Mysql架构

一,视图

视图是基于查询的一个虚拟表 , 也就是将sql语句封装起来, 要用的时候直接调用视图即可, select语句查询的表称为基表, 查询的结果集称为虚拟表, 基本表数据发生了改变, 那么视图也会发生改变, 使用视图就是为了简化查询语句.

1.CREATE VIEW view_admin AS  SELECT * FROM  admin;  //创建视图
2.    
3. SELECT * FROM  view_admin  //使用视图
4.   
5. DROP VIEW view_admin  //删除视图

二.存储过程

1.概述: 我们可以根据用户一些的复杂的要求, 将一些逻辑业务的sql语句集存储在数据库中, 由jdbc来调用这组sql语句, 这种把编写在数据库中的语句的过程称为存储过程.

调用存储过程可以简化开发人员的工作, 减少数据库与应用程序之间的传输, 可以提高数据处理的效率.

2.语法: 存储过程需要先创建好(包括业务逻辑), 可以向其中传入参数, 参数有三种类型:

in(输入参数,该参数的值必须由调用程序指定)

out(输出参数,该参数的值经存储过程计算后,返回给应用程序)

inout(既可输出又可输入)

declare : 用来声明变量

以下是无参的存储过程:

1.DELIMITER $$  
2. CREATE PROCEDURE `newsdb`.`a`()  //newsdb为数据库名   a为定义的存储过程名
3.    BEGIN  
4.     DECLARE var_num INT DEFAULT  0;  //声明一个变量,默认为0
5.          SET var_num = 10;  
6.          SELECT var_num;  
7.    END$$  
8.DELIMITER;  

以下是有参的存储过程:

DELIMITER$$
CREATE PROCEDURE findUserCount(IN p_type INT,OUT p_count INT)
BEGIN
-- into 把 sql 中查询的结果赋给 变量
SELECT COUNT(*) INTO p_count FROM USER WHERE TYPE = p_type;
SELECT p_count;
END$$
测试
CALL findUserCount(1,@p_count); -- @p_count 测试输出参数

以下是流程控制语句:

DELIMITER$$
CREATE PROCEDURE test(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$$
测试
CALL test(2)

以下是流程控制语句的case when

DELIMITER$$
CREATE PROCEDURE test(IN p_day INT)
BEGIN
CASE WHEN p_day = 0 THEN
SELECT "星期天";
ELSE
SELECT "星期一";
END CASE;
END$$
CALL test(2);

三.函数

函数的参数列表有: 参数名, 参数类型

函数体中肯定有return语句, 否则会报错.

设置函数可以没有参数

SET GLOBAL log_bin_trust_function_creators=TRUE;

以下是无参的函数:

#无参的函数
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(); 

以下是有参的函数:

#有参的函数
DELIMITER$$
CREATE FUNCTION findnews(p_id INT) RETURNS VARCHAR(20)
BEGIN
DECLARE v_account VARCHAR(20) DEFAULT '';
SELECT account INTO v_account FROM admin WHERE id=p_id;
RETURN v_account;
END$$
#测试
SELECT m.*,findnews(2) FROM newsmessage m

四.触发器

触发器是一种特殊的存储过程, 它并不需要用户对它进行调用, 而是在对表进行增,删,改之前或者之后进行执行的存储过程.

触发器是自动执行, 不可撤销的, 它与存储过程不同, 不能直接调用,不能传递或者接收参数.

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

#触发器
#删除案例,在删除管理员之前删除管理员与角色关联表
DELETE FROM admin WHERE id=4DELIMITER $$
CREATE TRIGGER delete_admin_role BEFORE DELETE
ON admin
FOR EACH ROW
BEGIN
DELETE FROM admin_role WHERE adminid = old.id;//  old表示原来的数据
END$$;

#新增管理员之前想一个表增加一条信息
DELIMITER $$
CREATE TRIGGER saveadmin AFTER INSERT
ON admin
FOR EACH ROW
BEGIN
INSERT INTO LOGS(account,PASSWORD,oper_time)VALUES(new.account,new.password,NOW());
END$$;   //new表示新增加的数据INSERT INTO admin(account,PASSWORD)VALUES('jim',111)

五.Mysql架构

1.连接层: 最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。也就是负责客户端的连接请求, 可以进行验证(验证账号密码)

2.服务层: 第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化,如确定查询表的顺序，是否利用素引等，最后生成相应的执行操作。如果是 select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。也就是接收sql, 语言解析,优化,缓存.

3.引擎层: 存储引擎层，存储引擎真正的负责了 MysQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。也就是真正落地实现的具体方式, 不同的存储引擎特点不同

4.物理文件存储层: 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

六.Mysql存储引擎

概述: MySQL 中的数据用各种不同的技术存储在文件中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平、并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作表类型)。MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在 MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内多数需要处理大量数据的应用程序的要求.

存储引擎种类: 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6.

Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

我们常用的有两种:

1.InnoDB

它是默认的,事务型存储引擎, 有行级锁和外键约束, 支持全文检索(全文索引),它的设计目标是处理大容量数据库系统,MySQL 运行时 Innodb 会在内存中建立缓冲池，用于缓冲数据和索引；支持主键自增.不存储表的总行数

2.Myisam

MyISAM 也是 MySQL 的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些;支持全文检索;存储表的总行数., 主要用于增删少, 查询多的场景