测试数据集

CREATE TABLE `kehu` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lianxiren` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `kehubianhao` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

INSERT INTO `20241109dbtest`.`kehu` (`lianxiren`, `kehubianhao`) VALUES ('qin2112', 'C7');
INSERT INTO `20241109dbtest`.`kehu` (`lianxiren`, `kehubianhao`) VALUES ('qin2112', 'C7');

CREATE TABLE `kehu02` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lianxiren` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `kehubianhao` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES ('11111', 'C8');
INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES ('2222', 'C8');
INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES ('3333', 'C8');
INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES ('5', 'C5');

CREATE TABLE `shangpin` (
  `shangpinbianma` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pinming` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `yanse` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `huaxing` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `guige` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `kucunshuliang` int DEFAULT NULL,
  `zuigaokucun` int DEFAULT NULL,
  `zuidikucun` int DEFAULT NULL,
  `cankaojiage` decimal(12,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `20241109dbtest`.`shangpin` (`shangpinbianma`, `pinming`, `yanse`, `huaxing`, `guige`, `kucunshuliang`, `zuigaokucun`, `zuidikucun`, `cankaojiage`) VALUES ('P1', '色布', '米白', '无', '245', 2400, 3000, 1000, 12.00);
INSERT INTO `20241109dbtest`.`shangpin` (`shangpinbianma`, `pinming`, `yanse`, `huaxing`, `guige`, `kucunshuliang`, `zuigaokucun`, `zuidikucun`, `cankaojiage`) VALUES ('P2', '花布', '漂白', '太阳花', '230', 1500, 2500, 800, 15.00);
INSERT INTO `20241109dbtest`.`shangpin` (`shangpinbianma`, `pinming`, `yanse`, `huaxing`, `guige`, `kucunshuliang`, `zuigaokucun`, `zuidikucun`, `cankaojiage`) VALUES ('P3', '花布', '钻红', '玫瑰', '245', 2500, 2000, 500, 13.50);
INSERT INTO `20241109dbtest`.`shangpin` (`shangpinbianma`, `pinming`, `yanse`, `huaxing`, `guige`, `kucunshuliang`, `zuigaokucun`, `zuidikucun`, `cankaojiage`) VALUES ('P4', '花布', '浅粉', '水仙花', '260', 3000, 2500, 1000, 15.60);
INSERT INTO `20241109dbtest`.`shangpin` (`shangpinbianma`, `pinming`, `yanse`, `huaxing`, `guige`, `kucunshuliang`, `zuigaokucun`, `zuidikucun`, `cankaojiage`) VALUES ('P5', '色布', '浅灰', '无', '245', 560, 2500, 500, 12.50);
INSERT INTO `20241109dbtest`.`shangpin` (`shangpinbianma`, `pinming`, `yanse`, `huaxing`, `guige`, `kucunshuliang`, `zuigaokucun`, `zuidikucun`, `cankaojiage`) VALUES ('P6', '花布', '米白', '百合', '245', 600, 2000, 1000, 16.50);

CREATE TABLE `zhanghu` (
  `zhanghao` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `xingming` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `yue` decimal(12,5) DEFAULT NULL,
  PRIMARY KEY (`zhanghao`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

INSERT INTO `20241109dbtest`.`zhanghu` (`zhanghao`, `xingming`, `yue`) VALUES ('A', 'liming', 900.00000);
INSERT INTO `20241109dbtest`.`zhanghu` (`zhanghao`, `xingming`, `yue`) VALUES ('B', 'wanghong', 500.00000);
INSERT INTO `20241109dbtest`.`zhanghu` (`zhanghao`, `xingming`, `yue`) VALUES ('C', 'lisi', 5000.00000);
INSERT INTO `20241109dbtest`.`zhanghu` (`zhanghao`, `xingming`, `yue`) VALUES ('E', 'changjian', 110.00000);
INSERT INTO `20241109dbtest`.`zhanghu` (`zhanghao`, `xingming`, `yue`) VALUES ('F', 'huanghe', 222.00000);
INSERT INTO `20241109dbtest`.`zhanghu` (`zhanghao`, `xingming`, `yue`) VALUES ('G', 'taishan11', 100.00000);
INSERT INTO `20241109dbtest`.`zhanghu` (`zhanghao`, `xingming`, `yue`) VALUES ('H', 'taishan22', 1100.00000);

CREATE TABLE `zhigong` (
  `zhigonghao` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `xingming` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `zhiwu` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `gongzi` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `20241109dbtest`.`zhigong` (`zhigonghao`, `xingming`, `zhiwu`, `gongzi`) VALUES ('1', 'name1', 'zhiwu1', '10000');
INSERT INTO `20241109dbtest`.`zhigong` (`zhigonghao`, `xingming`, `zhiwu`, `gongzi`) VALUES ('2', 'name2', 'zhiwu2', '20000');
INSERT INTO `20241109dbtest`.`zhigong` (`zhigonghao`, `xingming`, `zhiwu`, `gongzi`) VALUES ('3', 'name3', 'zhiwu3', '30000');

存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS PROB;
CREATE PROCEDURE PROB(IN num CHAR(5))
BEGIN
DECLARE name CHAR(8);
DECLARE zg_salary,tax DECIMAL(8,2);
SELECT xingming,gongzi INTO name,zg_salary
FROM zhigong
WHERE zhigonghao LIKE num;
SET tax=(zg_salary-3600)*0.03;
SELECT name,zg_salary,tax;
END $$
DELIMITER;

mysql> CALL PROB('2');

调用动态传变量对象

mysql> SELECT @NUM:=1;
+---------+
| @NUM:=1 |
+---------+
|       1 |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> CALL PROB(@NUM);
+-------+-----------+--------+
| name  | zg_salary | tax    |
+-------+-----------+--------+
| name1 |  10000.00 | 192.00 |
+-------+-----------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

--

SELECT字段赋值变量输出

mysql> SELECT xingming,gongzi INTO @name,@zg_salary FROM zhigong WHERE zhigonghao LIKE '1';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @name;
+-------+
| @name |
+-------+
| name1 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT @zg_salary;
+------------+
| @zg_salary |
+------------+
| 10000      |
+------------+
1 row in set (0.00 sec)

存储过程+IF+OUT对象值

DELIMITER $$
DROP PROCEDURE IF EXISTS PROM;
CREATE PROCEDURE PROM(in Accno char(5),out Level char(10))
BEGIN
DECLARE amount DEC(12,5);
SELECT yue INTO amount FROM zhanghu WHERE zhanghao=Accno;
IF amount>=50 THEN SET Level='钻石卡';
ELSEIF(amount < 500 AND amount > 100) THEN
SET Level='金卡';
ELSE SET Level='普通卡';
END IF;
END $$ 

mysql> CALL PROM('C',@p);
Query OK, 1 row affected (0.00 sec)

mysql> select @p;
+-----------+
| @p        |
+-----------+
| 钻石卡    |
+-----------+
1 row in set (0.00 sec)

循环-WHILE

创建计算T=1+2+3+...+n的存储过程，while

DELIMITER $$
DROP PROCEDURE IF EXISTS PROE1;
CREATE PROCEDURE PROE1(IN n INT)
BEGIN
DECLARE i,T INT;
SET i=0,T=0;
WHILE i<n DO
SET i=i+1;
SET T=T+i;
END WHILE;
SELECT T;
END $$
DELIMITER;

mysql> CALL PROE1(100);
+------+
| T    |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

循环-REPEAT

创建计算T=1+2+3+...+n的存储过程，repeat

DELIMITER $$
DROP PROCEDURE IF EXISTS PROE2;
CREATE PROCEDURE PROE2(IN n INT)
BEGIN
	DECLARE i,T int(10);
	SET i=0,T=0;
	REPEAT
	SET i=i+1;
	SET T=T+i;
	UNTIL i=n
	END REPEAT;
	SELECT T;
END $$
DELIMITER;

mysql> CALL PROE2(100);
+------+
| T    |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

循环-LOOP

创建计算T=1+2+3+...+n的存储过程，loop

DELIMITER $$
DROP PROCEDURE IF EXISTS PROE3;
CREATE PROCEDURE PROE3(IN n INT)
BEGIN
	DECLARE i,T int(10);
	SET i=0,T=0;
	Add_num: LOOP
	SET i=i+1;
	SET T=T+i;
	IF i=100 THEN LEAVE Add_num;
	END IF;
	END LOOP;
	SELECT T;
END $$
DELIMITER;


mysql> CALL PROE3(100);
+------+
| T    |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

函数FUNCTION

DELIMITER $$
DROP FUNCTION IF EXISTS P_count01;
CREATE FUNCTION P_count01()
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE n INT;
SELECT COUNT(*) INTO n FROM zhanghu;
RETURN n;
END$$
DELIMITER;

mysql> SELECT P_count01();
+-------------+
| P_count01() |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

游标

DELIMITER $$
DROP PROCEDURE IF EXISTS PROF;
CREATE PROCEDURE PROF(IN sp_num CHAR(10))
BEGIN
	IF EXISTS(SELECT shangpinbianma,pinming,kucunshuliang,zuigaokucun FROM shangpin WHERE kucunshuliang>=zuigaokucun AND shangpinbianma LIKE sp_num) THEN
		BEGIN
		SELECT shangpinbianma,pinming,kucunshuliang,zuigaokucun,kucunshuliang-zuigaokucun AS 积压 FROM shangpin WHERE shangpinbianma LIKE sp_num;
		END;
	ELSEIF EXISTS(SELECT shangpinbianma,pinming,kucunshuliang,zuidikucun FROM shangpin WHERE kucunshuliang<=zuidikucun AND shangpinbianma LIKE sp_num) THEN
		BEGIN
		SELECT shangpinbianma,pinming,kucunshuliang,zuidikucun,kucunshuliang-zuidikucun AS 缺货 FROM shangpin WHERE shangpinbianma LIKE sp_num;
		END;
	END IF;
END $$;
DELIMITER;


mysql> SET @num:='P3';
Query OK, 0 rows affected (0.00 sec)

mysql> CALL PROF(@num);
+----------------+---------+---------------+-------------+--------+
| shangpinbianma | pinming | kucunshuliang | zuigaokucun | 积压   |
+----------------+---------+---------------+-------------+--------+
| P3             | 花布    |          2500 |        2000 |    500 |
+----------------+---------+---------------+-------------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

--PROG()基于PROF()

DELIMITER $$
DROP PROCEDURE IF EXISTS PROG;
CREATE PROCEDURE PROG()
BEGIN
	DECLARE SP_num VARCHAR(10);
	DECLARE no,SP_count INT;
	DECLARE SP_cur CURSOR FOR SELECT shangpinbianma FROM shangpin;
	SELECT COUNT(*) INTO SP_count FROM shangpin;
	SET no=1;
	OPEN SP_cur;
	WHILE no<=SP_count DO
	FETCH SP_cur INTO SP_num;
	CALL PROF(SP_num);
	SET no=no+1;
	END WHILE;
	CLOSE SP_cur;
END $$


mysql> CALL PROG();
+----------------+---------+---------------+-------------+--------+
| shangpinbianma | pinming | kucunshuliang | zuigaokucun | 积压   |
+----------------+---------+---------------+-------------+--------+
| P3             | 花布    |          2500 |        2000 |    500 |
+----------------+---------+---------------+-------------+--------+
1 row in set (0.00 sec)

+----------------+---------+---------------+-------------+--------+
| shangpinbianma | pinming | kucunshuliang | zuigaokucun | 积压   |
+----------------+---------+---------------+-------------+--------+
| P4             | 花布    |          3000 |        2500 |    500 |
+----------------+---------+---------------+-------------+--------+
1 row in set (0.01 sec)

+----------------+---------+---------------+------------+--------+
| shangpinbianma | pinming | kucunshuliang | zuidikucun | 缺货   |
+----------------+---------+---------------+------------+--------+
| P6             | 花布    |           600 |       1000 |   -400 |
+----------------+---------+---------------+------------+--------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

触发器TRIGGER

DELIMITER;
DROP TRIGGER IF EXISTS AAA;
DELIMITER $$
CREATE TRIGGER AAA
AFTER INSERT ON kehu
FOR EACH ROW
BEGIN
	INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES ('5', 'C5');
	INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES (NEW.lianxiren, NEW.kehubianhao);
END $$
DELIMITER;

DELIMITER;
DROP TRIGGER IF EXISTS AAA;
DELIMITER $$
CREATE TRIGGER AAA
AFTER INSERT ON kehu
FOR EACH ROW
BEGIN
	INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES ('5', 'C5');
	INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES (NEW.lianxiren, NEW.kehubianhao);
END $$
DELIMITER;

--

DROP TRIGGER IF EXISTS BBB;
CREATE TRIGGER BBB
BEFORE DELETE ON kehu
FOR EACH ROW
BEGIN
DELETE FROM kehu02 WHERE id=old.id;
DELETE FROM kehu02 WHERE id=6;
END $$

--触发后的DML结果

CREATE TABLE `kehu` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lianxiren` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `kehubianhao` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

CREATE DEFINER=`root`@`localhost` TRIGGER `AAA` AFTER INSERT ON `kehu` FOR EACH ROW BEGIN
	INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES ('5', 'C5');
	INSERT INTO `20241109dbtest`.`kehu02` (`lianxiren`, `kehubianhao`) VALUES (NEW.lianxiren, NEW.kehubianhao);
END;

CREATE DEFINER=`root`@`localhost` TRIGGER `BBB` BEFORE DELETE ON `kehu` FOR EACH ROW BEGIN
DELETE FROM kehu02 WHERE id=old.id;
DELETE FROM kehu02 WHERE id=6;
END;

--