MySQL
1. 视图
概述：
视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图相对于普通的表的优势主要包括以下几项。
- 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
- 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
视图简单使用：
-- 创建视图 -- create view 视图名称 as 视图内容 --例： CREATE view Pro_Goods as select * from products left JOIN category on products.category_id = category.cid --查询视图 --【注：这里面可根据各种条件来进行查询】 select * from Pro_Goods --查看视图 show CREATE VIEW Pro_Goods --删除视图 DROP VIEW Pro_Goods
2.存储过程和函数
2.1 基本操作
删除存储过程
drop PROCEDURE if EXISTS Pro_MyTest
创建存储过程
DELIMITER // CREATE PROCEDURE Pro_MyTest() BEGIN SELECT * FROM products; end // DELIMITER;
查看存储过程
call Pro_MyTest
2.2 存储过程--变量
drop PROCEDURE if EXISTS Pro_MyTest2
DELIMITER //
CREATE PROCEDURE Pro_MyTest2()
BEGIN
-- 声明一个变量
DECLARE num int;
set num=50;
select num+50;
end
// DELIMITER;
call Pro_MyTest2
2.3 存储过程--if
-- 存储过程--if
drop PROCEDURE if EXISTS Pro_MyTest3
DELIMITER //
CREATE PROCEDURE Pro_MyTest3()
BEGIN
-- 声明一个变量
DECLARE num int;
DECLARE msg VARCHAR(200);
set num=180;
if num >= 180 THEN set msg="很高";
ELSEIF num >= 170 THEN set msg="还行";
ELSE set msg="就那样";
END IF;
SELECT msg;
end
// DELIMITER;
call Pro_MyTest3
2.4 存储过程--传参
-- 存储过程--传参
drop PROCEDURE if EXISTS Pro_MyTest4
DELIMITER //
CREATE PROCEDURE Pro_MyTest4(in num int,out msg VARCHAR(200))
BEGIN
if num >= 180 THEN set msg="很高";
ELSEIF num >= 170 THEN set msg="还行";
ELSE set msg="就那样";
END IF;
end
// DELIMITER;
call Pro_MyTest4(180,@m);
SELECT @m【注：
@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 "@@" 符号, 叫做 系统变量
】
2.5 存储过程--case结构
-- 需求:
-- 给定一个月份, 然后计算出所在的季度
drop PROCEDURE if EXISTS Pro_MyTest5
DELIMITER //
CREATE PROCEDURE Pro_MyTest5(in num int,out msg VARCHAR(200))
BEGIN
case
WHEN num>=1 and num<=3 then set msg="第一季度";
WHEN num>=4 and num<=6 then set msg="第二季度";
WHEN num>=7 and num<=9 then set msg="第三季度";
WHEN num>=10 and num<=12 then set msg="第四季度";
WHEN num>=13 then set msg="输入有误";
end case;
end
// DELIMITER;
call Pro_MyTest5(15,@m);
SELECT @m改进，上述改为if语句执行
drop PROCEDURE if EXISTS Pro_MyTest6 DELIMITER // CREATE PROCEDURE Pro_MyTest6(in num int,out msg VARCHAR(200)) BEGIN if num>=1 and num <=3 then set msg="第一季度"; elseif num>=4 and num <=6 then set msg="第二季度"; elseif num>=7 and num <=9 then set msg="第三季度"; elseif num>=10 and num <=12 then set msg="第四季度"; else set msg="输入有误"; end if; end // DELIMITER; call Pro_MyTest6(1,@m); SELECT @m
2.6 存储过程-- while循环
-- 计算从1加到n的值
drop PROCEDURE if EXISTS Pro_MyTest7
DELIMITER //
CREATE PROCEDURE Pro_MyTest7(in num int)
BEGIN
DECLARE total int DEFAULT 0;
DECLARE n int DEFAULT 1;
while n<=num do
set total=total+n;
set n=n+1;
end while;
select total;
end
// DELIMITER;
call Pro_MyTest7(50);
2.7 存储过程--repeat语句
-- 计算从1加到n的值
-- [注：repeat语句下的until后面不跟分号]
drop PROCEDURE if EXISTS Pro_MyTest8
DELIMITER //
CREATE PROCEDURE Pro_MyTest8(in num int)
BEGIN
DECLARE total int DEFAULT 0;
repeat
set total=total+num;
set num=num-1;
until num=0
end repeat;
select total;
end
// DELIMITER;
call Pro_MyTest8(50);【注：repeat语句下的until后面不跟分号】
2.8 存储过程--loop语句 && leave
-- 计算从1加到n的值
drop PROCEDURE if EXISTS Pro_MyTest9
DELIMITER //
CREATE PROCEDURE Pro_MyTest9(in num int)
BEGIN
DECLARE total int DEFAULT 0;
ins:LOOP
IF num<=0 THEN
LEAVE ins;
END IF;
set total=total+num;
set num=num-1;
END LOOP ins;
select total;
end
// DELIMITER;
call Pro_MyTest9(5);【注：ins是随便写的主要是用于标识】
Loop语法：
标识: LOOP -- 结束循环条件 IF 条件 THEN LEAVE 标识; 这一行是循环体 直到满足循环条件时结束 END IF; END LOOP 标识;
2.9 存储过程--游标/光标
游标语法：
-- 存储过程--游标/光标 声明光标： DECLARE cursor_name CURSOR FOR select_statement ; 打开光标： OPEN cursor_name ; 捕获光标： FETCH cursor_name INTO var_name [, var_name] ... 关闭光标： CLOSE cursor_name ;
游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下：
drop PROCEDURE if EXISTS Pro_MyTest10 DELIMITER // CREATE PROCEDURE Pro_MyTest10() BEGIN declare eid int(11); declare ename VARCHAR(200); declare sex VARCHAR(200); declare salary int(255); declare time date; declare dept_name VARCHAR(200); -- 创建游标 declare emp_result cursor for select * from emp; -- 打开游标 open emp_result; -- 捕获游标 fetch emp_result into eid,ename,sex,salary,time,dept_name; select concat('id=',eid , ', name=',ename, ', sex=', sex, ', 薪资为: ',salary,'入职时间：',time,'部门名称：',dept_name); -- 关闭游标 close emp_result; end // DELIMITER; call Pro_MyTest10();
【注：捕获游标时，如果只写一次捕获游标语句，只会输出一条，所以，捕获一次输出一条】
【注：所以，需要加上循环捕获游标的方式】
循环捕获游标：
【注：那么在数据库中，其并不知道什么时候才算循环结束，所以应该有一个结束条件】：
DECLARE has_data int default 1; DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; if has_data=0 then leave xw; end if; -- 此处就是结束循环的条件
完整代码：
-- 使用循环捕获游标 drop PROCEDURE if EXISTS Pro_MyTest11 DELIMITER // CREATE PROCEDURE Pro_MyTest11() BEGIN declare eid int(11); declare ename VARCHAR(200); declare sex VARCHAR(200); declare salary int(255); declare time date; declare dept_name VARCHAR(200); DECLARE has_data int default 1; -- 必要的离开条件 declare emp_result cursor for select * from emp; DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; -- 必要的离开条件 open emp_result; -- 开始循环 xw:loop if has_data=0 then -- 必要的离开条件 has_data=0 LEAVE xw; end if; fetch emp_result into eid,ename,sex,salary,time,dept_name; select concat('id=',eid , ', name=',ename, ', sex=', sex, ', 薪资为: ',salary,'入职时间：',time,'部门名称：',dept_name); end loop xw; close emp_result; end // DELIMITER; call Pro_MyTest11();
3.0 Mybatis调取存储过程或函数
首先在数据库中创建一个存储过程：
//根据商品分类id查询每分类中有多少商品
-- 存储过程--使用Mybatis调用 drop PROCEDURE if EXISTS Pro_MyTest12 DELIMITER // CREATE PROCEDURE Pro_MyTest12(in num VARCHAR(200),out total int) BEGIN SELECT COUNT(*) into total from products where category_id=num; SELECT total; end // DELIMITER; call Pro_MyTest12('c003',@m); SELECT @m
1.首先在dao层处声明一个接口：
public interface GetTotalTestDao { Map GetTotal(Map map); }【注：返回值为Map】
【注：参数类型也是Map】
2.在Mapper映射层写相关SQL语句;
<mapper namespace="com.aaa.dao.GetTotalTestDao"> <select id="GetTotal" resultType="map" statementType="CALLABLE" parameterType="map"> {call Pro_MyTest12(#{num,jdbcType=VARCHAR,mode=IN},#{total,jdbcType=INTEGER,mode=OUT})} </select> </mapper>【注：在<select ></select>中 resultType="map" statementType="CALLABLE" parameterType="map"】这几个参数是必不可少的
且
3.测试输出：
@SpringBootTest class VueTestProjectApplicationTests { @Autowired private GetTotalTestDao getTotalTestDao; @Test void contextLoads() { HashMap<Object, Object> objectObjectHashMap = new HashMap<>(); objectObjectHashMap.put("num","c001"); objectObjectHashMap.put("total",null); getTotalTestDao.GetTotal(objectObjectHashMap); System.out.println(objectObjectHashMap); } }
4.0 触发器
4.1 触发器添加触发
触发器语法：
-- 触发器 create trigger 触发器名称 before/after insert/update/delete on 绑定被触发的表 for each row -- 行级触发器 begin 触发内容 end;
示例：
可以首先快速创建被绑定表的触发表
如：需要在添加商品时记录日志
快速创建商品日志表：
CREATE table products_log as select * from products where 1=2; select * from products_log;
创建触发器：
DELIMITER // CREATE TRIGGER Trigger_My1 AFTER INSERT on products for each row BEGIN insert into products_log(pid,pname,price) VALUES(new.pid,new.pname,new.price); end // DELIMITER;
创建成功后
添加条商品测试：
-- 添加数据 insert into products(pid,pname,price) VALUES(null,'英特尔笔记本',5600);
触发成功
删除触发器：
DROP TRIGGER 触发器名称
4.2 触发器更改触发
首先建一个被触发的表：
在表被更改时触发
DELIMITER // CREATE TRIGGER Trigger_My2 AFTER UPDATE on products for each row BEGIN insert into products_log_update(operateold,operatenew) VALUES( concat("原来的id:",old.pid,",原来的名字:",old.pname), concat("新的id:",new.pid,",新的名字:",new.pname) ); end // DELIMITER;
更改表数据：
update products set price=2000,pname='test' where pid=20
触发成功
4.3 进行条件更改触发
例：今天星期五不可以降商品价格，只能往上调
DELIMITER // CREATE TRIGGER Trigger_My2 AFTER UPDATE on products for each row BEGIN if WEEKDAY(now())=4 then -- 判断今天是周几，weekday是从0开始，0代表周一 if old.price>new.price then --判断旧的商品价格不能大于更改后新的商品价格 SIGNAL SQLSTATE 'HY001' set MESSAGE_TEXT="今天是星期五，你不能降商品价格"; else insert into products_log_update(operateold,operatenew) VALUES( concat("原来的id:",old.pid,",原来的名字:",old.pname), concat("新的id:",new.pid,",新的名字:",new.pname) ); end if; end if; end // DELIMITER;【注：创建此触发器可以先将之前的触发器先删除掉，以避免冲突】
DROP TRIGGER Trigger_My2现在进行更改操作：
update products set price=1000,pname='test' where pid=20 --注：此商品原来价格为2000
触发成功，新的价格只有大于旧的价格时才会进行触发更改操作
此次所需的数据库表数据
/* Date: 19/10/2023 20:42:07 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for products -- ---------------------------- DROP TABLE IF EXISTS `products`; CREATE TABLE `products` ( `pid` int(0) NOT NULL AUTO_INCREMENT COMMENT '商品编号', `pname` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '商品名称', `price` int(0) DEFAULT NULL COMMENT '商品价格', `state` int(0) DEFAULT NULL COMMENT '商品状态:0表示下架：1表示在售卖', `category_id` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '商品类别', `inven` int(0) DEFAULT NULL COMMENT '商品库存', `prodect_img` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '商品图片', PRIMARY KEY (`pid`) USING BTREE, INDEX `category_id`(`category_id`) USING BTREE, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;
以上便是MySQL中的部分内容，如有漏缺请在下方留言告知，我会及时补充
