mysql 高级（进阶学习）

视图

视图就是将某个查询语句存储在数据中，并为其命名，视图中并不存储数据，数据还是在基本表中存储。

定义视图

create view 视图名 as 查询语句（select a,b from 表）

使用视图

select * from 视图名

删除视图

drop view 视图名

存储过程

存储过程就是把一段处理逻辑存入到数据库中，使用是就由 JDBC 调用即可。

调用存储过程可以减少应用程序和数据库交互次数，在数据库内部执行，执行效率高。

存储事先需要定义，有三种参数类型：

• in 入参（接收调用者传入的数据）

• out 返回（向调用者返回数据）

• inout （既可以接收调用者传入的数据，也可以向调用者返回数据）

函数

函数是一个特殊的存储过程。

存储过程不仅有输入参数，还有输出参数，但是没有返回值，里面可以完成逻辑处理，不能在普通的 sql 中调用，call 存储过程。

函数一般用于查询场景，里面只有入参，没有输出参数，但是必须有返回值，可以在普通 sql 中调用。

函数语法

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

注意：

1. 参数列表包含两部分：参数名 参数类型
2. 函数体：肯定会有 return 语句，如果没有就会报错
3. 函数体中仅有一句话，则可以省略掉 begin end
4. 使用 delimter 语句设置结束标记

设置函数可以没有参数

SET GLOBAL log_bin_trust_function_creators=TRUE;

删除函数

drop function 函数名;

带参数

DELIMITER$$
CREATE FUNCTION findDeptNameById(p_id INT) RETURNS VARCHAR(10)
BEGIN
	DECLARE v_name VARCHAR(10);
	SELECT NAME INTO v_name FROM dept WHERE id = p_id;
	RETURN v_name;
END$$
SELECT account,findDeptNameById(dept_id) FROM user


DELIMITER$$
CREATE FUNCTION checkUserType(p_type INT) RETURNS VARCHAR(4)
BEGIN
	IF p_type = 0 THEN
		RETURN '管理员';
	ELSE
		RETURN '业务用户';
	END IF;
END$$
SELECT tu.account,checkUserType(tu.type)utype FROM user tu
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

不带参数

DELIMITER$$
CREATE FUNCTION test() RETURNS INT
BEGIN
	DECLARE v_num INT;
	SELECT COUNT(*) INTO v_num FROM t_user;
	RETURN v_num;
END$$
测试
select test() from dual;
1
2
3
4
5
6
7
8
9

触发器

触发器可以理解为特殊的存储过程，不需要调用，在满足某种条件是自动执行。

特点：
对表进行新增，修改，删除操作之前或之后自动执行，类似于事件。

作为事务的一部分。

DELIMITER $$
CREATE TRIGGER save_user_log 
AFTER 
INSERT 
ON USER
FOR EACH ROW -- 行级触发
BEGIN
   INSERT INTO save_user_log(account,oper_time)VALUES(new.account,NOW());
  END$$
DELIMITER ;

INSERT INTO USER(account,PASSWORD)VALUES('aaa','1111')



DELIMITER $$
CREATE TRIGGER delete_user_role 
BEFORE 
DELETE 
ON USER
FOR EACH ROW -- 行级触发
BEGIN
    DELETE FROM user_role WHERE userid = old.id;
  END$$
DELIMITER ;

DELETE FROM USER WHERE id = 14;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

MySql 架构

MySql 的完整架构图：

mysql架构分四层：

1. 连接层
   负责接收客户端程序连接，授权，认证
2. 服务层
   接收 sql，负责调用函数，存储过程，触发器，对 sql 执行顺序排序，优化
   如果是查询操作，还可以从缓存中先查询数据，提高性能
3. 引擎层
   引擎是真正具体的执行者，mysql 提供不同的执行引擎，不同引擎特点不同，根据需要选择即可
4. 物理文件存储层
   在硬盘上存储系统文件，表数据，各种日志文件。

MySql引擎

引擎就是实际存储数据的机制，不同的引擎机制不同（例如索引，锁…）
根据不同的需要，选择不同的引擎，可以使效率最佳。

mysql 常用的存储引擎有：

• innodb： 默认的存储引擎 支持事务，支持行锁，支持外键，支持缓存，不存储表的总行数，支持全文索引。

• myisam： 不支持事务，不支持行锁，只支持表锁，不支持外键，不支持缓存，存储表的总行数。

查看支持的引擎

SHOW ENGINES;

查看表引擎

SHOW TABLE STATUS LIKE ‘表名’

修改引擎

方式 1:将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.
方式 2:建表时指定 CREATE TABLE 表名(…)ENGINE=MYISAM;
方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB;