视图、存储过程、函数、触发器
> > > MySQL基础
> > > MySQL的多表操作查询
> > > MySQL多表操作练习
> > > MySQL视图、存储过程、函数、触发器
👇👇👇创建表结构和添加数据的SQL在底部👇👇👇
视图
- 视图:是一种虚拟的数据表,在数据库中并不实际存在该表。
- 作用:将复杂的查询结果封装到虚拟表中,后期查询直接查询该试图即可。
– 创建视图:CREATE VIEW 视图名 (字段名) AS 查询语句;
CREATE VIEW 视图名(CITY_ID, CITY_NAME, COUNTRY_NAME) AS
SELECT 字段1,
字段2,
…
FROM 表1, 表2
WHERE 查询条件;
/*
注意:(字段名) 这里的字段是视图的字段,不需要和表中的字段保持一致,
但是字段个数要和查询的字段个数保持一致
** 修改视图的数据后,原表中的数据也会被修改 **
*/
CREATE VIEW CITY_COUNTRY (CITY_ID, CITY_NAME, COUNTRY_NAME) AS
SELECT CI.ID,
CI.NAME,
CO.NAME
FROM CITY CI, COUTRY CO
WHERE CI.CID = CO.ID;
视图的查询
视图查询:SELECT * FROM 视图名;
SELECT * FROM CITY_COUNTRY;
存储过程
– 创建存储过程
DELIMITER$ – 修改结束符号(分隔符)
CREATE PROCEDURE 过程名([IN|OUT|INOUT]参数 数据类型) – 创建过程参数可为空
– IN:入参
– OUT:出参,该参数作为返回值
– INOUT:可为入参也可为出参
BEGIN
SQL语句;
END$
DELIMITER; – 修改结束符号(分隔符)– 过程的调用
CALL 过程名(参数);
过程定义
DELIMITER$
CREATE PROCEDURE pro_test()
BEGIN
DECLARE total INT;
DECLARE info VARCHAR(10);
SELECT SUM(SCORE) INTO total FROM student;
IF total > 380 THEN
SET info = '学习优秀';
ELSEIF total >= 320 AND total <= 380 THEN
SET info = '学习不错';
ELSE
SET info = '成绩一般';
END IF;
SELECT total, info;
END$
DELIMITER;
存储过程定义变量
DECLARE 变量名 数据类型 [DEFAULT 默认值];
DECLARE num INT DEFAULT 10;
给变量赋值
SET 变量名 = 变量值;
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
SELECT NAME INTO P_NM FROM USER;
IF语句语法
IF 条件 THEN
SQL语句;
ELSEIF 条件 THEN
SQL语句;
…
ELSE
SQL语句;
END IF;
WHILE 循环
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
-- 1~100之间的偶数相加
DELIMITER$
CREATE PROCEDURE pro_event()
BEGIN
DECLARE result INT DEFAULT 0;
DECLARE num INT DEFAULT 1;
WHILE num <= 100 DO
IF num % 2 = 0 THEN
SET result = result + num;
END IF;
SET num = num + 1;
END WHILE;
SELECT result;
END$
DELIMITER;
函数
– 函数 函数必须要有返回值
DELIMITER$
CREATE FUNCTION(参数列表)
RETURNS 返回值类型;
BEGIN
DECLARE s_count INT;
SQL语句;
RETURN 结果;
END$
DELIMITER;– 函数调用
SELECT 函数名(参数);
无传参创建函数
DELIMITER$
CREATE FUNCTION()
RETURNS INT -- 返回值类型
BEGIN
DECLARE s_count INT;
SELECT COUNT(*) INTO s_count FROM USER;
RETURN s_count;
END$
DELIMITER;
有参创建函数
DELIMITER$
CREATE FUNCTION(P_USER_CDE VARCHAR(50))
RETURNS VARCHAR -- 返回值类型
BEGIN
DECLARE V_RETURN VARCHAR(50); -- 声明变量
-- 根据 P_USER_CDE入参 查询数据,并把结果赋值给 V_RETURN
SELECT name INTO V_RETURN FROM user WHERE usercde = P_USER_CDE;
-- 判断查询结果是否为空'' 为空则组织返回语句
IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
SET V_RETURN = CONCAT_WS('"','E0000,未查询到usercde为',P_USER_CDE,'的用户名');
END IF;
RETURN V_RETURN; -- 返回查询结果
END$
DELIMITER;
MySQL触发器
触发器类型 OLD NEW
INSERT型 (无,插入前无数据) 表示将要或已新增数据
UPDATE型 OLD表示修改之前的数据 表示将要或已新增数据
DELETE型 OLD表示将要或已删数据 无(删除后无数据)
– 创建触发器
DELIMITER$
CREATE TRIGGER 触发器名称
BEFORE | AFTER INSERT | UPDATE | DELETE
ON 表名 – 给哪个表创建触发器
FOR EACH ROW
BEGIN
触发器要执行的功能;
END$
DELIMITER;
创建INSERT触发器
-- 创建测试触发器(INSERT)
DELIMITER $
CREATE TRIGGER account_insert
AFTER INSERT ON account FOR EACH ROW
BEGIN
INSERT INTO account_log VALUES (null,'INSERT',now(),NEW.id,
CONCAT('{id=',NEW.ID,',name=',NEW.NAME,',money=',NEW.MONEY,'}'),'',
CONCAT('插入的值{id=',NEW.ID,',name=',NEW.NAME,',money=',NEW.MONEY,'}'),'新增');
END$
DELIMITER;
创建UPDATE触发器
-- 创建测试触发器(UPDATE)
DELIMITER $
CREATE TRIGGER account_update
AFTER UPDATE ON account FOR EACH ROW
BEGIN
INSERT INTO account_log VALUES (null,'UPDATE',now(),NEW.id,
CONCAT('{id=',NEW.ID,',name=',NEW.NAME,',money=',NEW.MONEY,'}'),
CONCAT('修改前的值{id=',OLD.ID,',name=',OLD.NAME,',money=',OLD.MONEY,'}'),
CONCAT('修改后的值{id=',NEW.ID,',name=',NEW.NAME,',money=',NEW.MONEY,'}'),'更新');
END$
DELIMITER;
创建DELETE触发器
-- 创建测试触发器(UPDATE)
DELIMITER $
CREATE TRIGGER account_delete
AFTER DELETE ON account FOR EACH ROW
BEGIN
INSERT INTO account_log VALUES (null,'DELETE',now(),OLD.id,
CONCAT('{id=',OLD.ID,',name=',OLD.NAME,',money=',OLD.MONEY,'}'),
CONCAT('删除前的值{id=',OLD.ID,',name=',OLD.NAME,',money=',OLD.MONEY,'}'),
'删除后的值{}','删除');
END$
DELIMITER;
👇👇👇创建表结构和添加数据👇👇👇
-- 创建TB_TEST数据库
CREATE DATABASE TB_TEST;
USE TB_TEST;
-- 创建orderlist表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for orderlist
-- ----------------------------
DROP TABLE IF EXISTS `orderlist`;
CREATE TABLE `orderlist` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单主键id',
`number` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '订单编号',
`uid` int(11) NULL DEFAULT NULL COMMENT '用户id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_orderlist_tb_user_1`(`uid`) USING BTREE,
CONSTRAINT `fk_orderlist_tb_user_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of orderlist
-- ----------------------------
INSERT INTO `orderlist` VALUES (1, ' BM0001', 1);
INSERT INTO `orderlist` VALUES (2, 'BM0002', 1);
INSERT INTO `orderlist` VALUES (3, 'BM0003', 2);
INSERT INTO `orderlist` VALUES (4, 'BM0004', 2);
INSERT INTO `orderlist` VALUES (5, 'BM0005', 3);
INSERT INTO `orderlist` VALUES (6, 'BM0006', 3);
INSERT INTO `orderlist` VALUES (7, 'BM0007', 4);
INSERT INTO `orderlist` VALUES (8, 'BM0008', NULL);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建user表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '用户年龄',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 23);
INSERT INTO `user` VALUES (2, '李四', 24);
INSERT INTO `user` VALUES (3, '王五', 25);
INSERT INTO `user` VALUES (4, '赵六', 26);
INSERT INTO `user` VALUES (5, '邹七', 27);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建product表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`cid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_product_category_1`(`cid`) USING BTREE,
CONSTRAINT `fk_product_category_1` FOREIGN KEY (`cid`) REFERENCES `category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, '华为手机', 1);
INSERT INTO `product` VALUES (2, '小米手机', 1);
INSERT INTO `product` VALUES (3, '联想电脑', 2);
INSERT INTO `product` VALUES (4, '苹果电脑', 2);
INSERT INTO `product` VALUES (5, '中华香烟', 3);
INSERT INTO `product` VALUES (6, '玉溪香烟', 3);
INSERT INTO `product` VALUES (7, '计生用品', NULL);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建us_pro中间表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for us_pro
-- ----------------------------
DROP TABLE IF EXISTS `us_pro`;
CREATE TABLE `us_pro` (
`upid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NULL DEFAULT NULL COMMENT '用户id',
`pid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`upid`) USING BTREE,
INDEX `fk_us_pro_tb_user_1`(`uid`) USING BTREE,
INDEX `fk_us_pro_product_1`(`pid`) USING BTREE,
CONSTRAINT `fk_us_pro_product_1` FOREIGN KEY (`pid`) REFERENCES `product` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_us_pro_tb_user_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of us_pro
-- ----------------------------
INSERT INTO `us_pro` VALUES (1, 1, 2);
INSERT INTO `us_pro` VALUES (2, 2, 1);
INSERT INTO `us_pro` VALUES (3, 3, 1);
INSERT INTO `us_pro` VALUES (4, 3, 3);
INSERT INTO `us_pro` VALUES (5, 2, 3);
INSERT INTO `us_pro` VALUES (6, 4, 2);
INSERT INTO `us_pro` VALUES (7, 3, 2);
INSERT INTO `us_pro` VALUES (8, 4, 1);
INSERT INTO `us_pro` VALUES (9, 5, 2);
INSERT INTO `us_pro` VALUES (10, 5, 5);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建account表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' 账号id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '姓名',
`money` double NULL DEFAULT NULL COMMENT '余额',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES (1, '张三', 1000);
INSERT INTO `account` VALUES (2, '李四', 1000);
INSERT INTO `account` VALUES (3, '王五', 5000);
-- 创建account_log
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for account_log
-- ----------------------------
DROP TABLE IF EXISTS `account_log`;
CREATE TABLE `account_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志id',
`operation` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作类型(INSERT,UPDATE,DELETE)',
`operation_time` timestamp NULL DEFAULT NULL COMMENT '操作时间',
`operation_id` int(11) NULL DEFAULT NULL COMMENT '操作表的id',
`operation_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作参数',
`bf_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作前的参数',
`af_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作后的参数',
`type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作类型名(新增,更新,删除)',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of account_log
-- ----------------------------
INSERT INTO `account_log` VALUES (1, 'INSERT', '2021-12-18 15:27:58', 3, '{id=3,name=王五,money=3000}', '', '插入的值{id=3,name=王五,money=3000}', '新增');
INSERT INTO `account_log` VALUES (2, 'UPDATE', '2021-12-18 15:40:50', 3, '{id=3,name=王五,money=5000}', '修改前的值{id=3,name=王五,money=3000}', '修改后的值{id=3,name=王五,money=5000}', '更新');
SET FOREIGN_KEY_CHECKS = 1;
> > > MySQL基础
MySQL基础:https://blog.csdn.net/qq_51076413/article/details/122023535
> > > MySQL的多表操作查询
多表操作查询:https://blog.csdn.net/qq_51076413/article/details/122024324
> > > MySQL多表操作练习
MySQL多表操作练习:https://blog.csdn.net/qq_51076413/article/details/122024838
> > > MySQL视图、存储过程、函数、触发器
MySQL视图、存储过程、函数、触发器:https://blog.csdn.net/qq_51076413/article/details/122025461