目录
视图
存储过程
mybatis 调用存储过程
函数
触发器
MySQL架构
MySQL引擎
innoDB
myisam
视图
视图就是将某个查询语句存储在数据中,并为其命名,视图中并不存储数据,数据还是在基本中存储。
定义视图
CREATE VIEW 视图名
AS SELECT 列 1,列 2... FROM 表(查询语句);
使用视图
SELECT * FROM 视图名
删除视图
drop view 视图
视图与基本表的区别:
- 视图是已经编译好的sql语句。而表不是
- 视图没有实际的物理记录。而表有。
- 表是内容,视图是窗口
- 表只用物理空间而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时的对它进行修改,但视图只能有创建的语句来修改
- 表是内模式,视图是外模式
- 视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全的角度说,视图可以不给用户接触数据表,从而不知道表结构。
- 表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表。
- 视图的建立和删除只影响视图本身,不影响对应的基本表
存储过程
存储过程就把一段处理逻辑存入到数据库中,使用时,由jdbc调用既可。减少应用程序和数据库交互次数,在数据库内部执行,执行效率高。
存储事先需要定义, 有3种类型参数: in 入参 out 返回 inout 接收/输出
创建存储过程
create procedure 存储过程名([in 变量名 类型,out 参数 2,…])
begin
[declare 变量名 类型 [DEFAULT 值];]
存储过程语句块;
end;
定义第一个存储过程
-- 开始位置
DELIMITER$$
CREATE PROCEDURE test()
BEGIN
-- 声明变量
DECLARE v_name VARCHAR(20) DEFAULT 'jim';
SET v_name = 'tom'; -- 变量赋值
SELECT v_name; -- 测试输出语句
END$$ -- 结束位置
-- 调用存储过程
CALL test1(
定义一个有参数的存储过程
DELIMITER$$
CREATE PROCEDURE findUserCount(IN p_type INT,OUT p_count INT)
BEGIN
-- 把 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)
循环
DELIMITER$$
CREATE PROCEDURE test7()
BEGIN
DECLARE v_num INT DEFAULT 0; -- 循环开始
addnum:LOOP
SET v_num = v_num+1; -- 循环语句
-- 循环结束条件
IF v_num = 10 THEN
LEAVE addnum;
END IF;
END LOOP; -- 循环结束
SELECT v_num;
END$$
CALL test7()
使用存储过程插入信息
DELIMITER$$
CREATE PROCEDURE saveUser(IN p_account VARCHAR(20),IN p_sex CHAR(1),OUT res_mark
INT)
BEGIN
DECLARE v_count INT DEFAULT 0;
SELECT COUNT(*) INTO v_count FROM t_user WHERE account = p_account;
IF v_count = 0 THEN
INSERT INTO t_user(account,sex)VALUES(p_account,p_sex);
SET res_mark = 0;
ELSE
SET res_mark = 1;
END IF;
END$$
mybatis 调用存储过程
<parameterMap type="map" id=“usermap">
<parameter property="addend1" jdbcType="VARCHAR" mode="IN"/>
<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>
jdbcType 必须指定
<insert id="saveUserDemo" parameterMap="usermap" statementType="CALLABLE">
{call saveuser(?, ?)}
</insert>
Map<String, Object> parms = new HashMap<String, Object>();
parms.put("addend1", 3);
userDao.saveUserDemo(parms);
parms.get(“result”);//获得输出参数
函数
函数也是一个特殊的存储过程。存储过程有输入参数,还有输出的参数,没有返回值,里面可以完成逻辑处理.。不能在普通的sql中调用, call 存储过程。
函数一般用于查询场景,里面只有入参,没有输出参数,必须有返回值,可以在普通的sql中调用使用。
语法
create function 函数名([参数列表]) returns 数据类型
begin
DECLARE 变量;
sql 语句;
return 值;
end;
删除函数
DROP FUNCTION 函数名;
不带参数
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;
带参数
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
触发器
什么是触发器?
可以理解为特殊的存储过程,不需要调用,在满足某种条件时自动执行,对表进行增、删、改操作之前或者之后自动执行,类似于事件。作为事务的一部分。
create trigger 触发器名称 触发时机(after或before)触发类型(insert、update、delete)
on 表名
for each row //行级触发
begin
//sql语句
end
案例:
删除用户时,自动触发删除用户菜单关系
DELIMITER $$
CREATE TRIGGER delete_user_menu BEFORE DELETE
ON t_user
FOR EACH ROW
BEGIN
DELETE FROM t_user_menu WHERE user_id = old.id;
END$$;
新增用户时,自动向其他表插入数据
DELIMITER $$
CREATE TRIGGER save_user_log AFTER INSERT
ON user
FOR EACH ROW
BEGIN
INSERT INTO test(id,NAME)VALUES(new.id,new.account);
END$$;
//执行
INSERT INTO user(account)VALUES('jim')
MySQL架构
概述(四层)
- 连接层:负责接收客户端程序的连接,负责授权,认证。
- 服务层:可以接收sql,负责调用存储过程、触发器、函数。对sql执行顺序排序,优化。如果是查询操作,还可以从缓存中先查询数据,提高性能。
- 引擎层:引擎是真正具体执行者,MySQL提供不同的执行引擎,不同的引擎特点不同,可以根据需要选择即可。
- 物理文件存储层:在硬盘上存储系统文件,表数据,各种日志文件。
MySQL引擎
什么是引擎?
引擎就是实际存储数据的机制,不同的引擎机制不同(例如:索引,锁(表锁,行锁等))。根据不同的需要,选择不同的引擎,使得效率最佳。
存储引擎主要有:
- MyIsam
- InnoDB(mysql默认)
- Memory
- Blackhole
- CSV
- Performance_Schema
- Archive
- Federated
MySQL常用存储引擎
innoDB
MySQL5不支持全文索引,MySQL8支持全文索引。
默认的存储引擎,支持事务,支持行锁(适合高并发操作),支持外键,支持缓存,不存储表的总行数。支持全文索引。
myisam
不支持事务,不支持行锁,只支持表锁。不支持外键,不支持缓存,存储表的总行数。