SQL小练

创建事件 

#创建事件，x秒后，用库存更新昨日库存
DELIMITER $$
CREATE    EVENT xxx.xxx
ON SCHEDULE
     AT CURRENT_TIMESTAMP   + INTERVAL 10  SECOND  
DO
    BEGIN
         UPDATE `stock` SET `yesterday_quantity`=`quantity`;
    END $$
DELIMITER ;

DELIMITER $$
CREATE    DEFINER='root'@'localhost' EVENT `jxc`.`direct_happen2`
ON SCHEDULE
     AT '2024-06-16 08:56:45'  
DO
    BEGIN
         UPDATE `stock` SET `month_quantity`=`quantity`;
    END $$
DELIMITER ;


#创建一个从下个月最后一天24点开始到2025年12月30日结束、每个月执行一次的事件，每次用当前库存更新月初库存
#interval表示时间间隔
DELIMITER $$
CREATE EVENT update_inventory_monthly
ON SCHEDULE EVERY 1 MONTH
STARTS LAST_DAY(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))+INTERVAL '24:00:00' HOUR_SECOND
DO UPDATE stock SET month_quantity=quantity;
END $$
DELIMITER ;

存储过程

DELIMITER$$
CREATE PROCEDURE wh
(gid INT,gname VARCHAR(20),unit VARCHAR(20),barcode VARCHAR(20),
retail_Price DECIMAL(10,2),promotional_Price DECIMAL(10,2),STATUS TINYINT)
BEGIN
	IF gid=0 THEN
	INSERT INTO goods (gname,unit,barcode,retail_Price,promotional_Price,STATUS) 
		VALUES (gname,unit,barcode,retail_Price,promotional_Price,STATUS);
	ELSEIF gid>0 THEN UPDATE goods SET goods.gname=gname, goods.unit=unit, 
		 goods.barcode=barcode, goods.retail_Price=retail_Price, goods.promotional_Price=promotional_Price, 
                 goods.STATUS=STATUS 
		WHERE  goods.gid=gid;
	ELSE DELETE FROM goods WHERE goods.`gid`=gid;
	END IF;
END$$
DELIMITER;


DELIMITER$$
CREATE PROCEDURE goodscx (gid INT)
BEGIN 
	IF gid=0 THEN 
		SELECT * FROM goods;
	ELSEIF gid>0 THEN
		SELECT * FROM goods WHERE goods.`gid`=gid;
	END IF;
END$$
DELIMITER;

#名称缩写的维护
DELIMITER$$
CREATE DEFINER='root'@'localhost'
FUNCTION PysxCx(zw VARCHAR(50)) RETURNS VARCHAR(10)
READS SQL DATA
BEGIN
	SET @pysx='';
	SET @l=CHAR_LENGTH(zw);
	SET @i=1;
	WHILE (@i<=@l)DO
		SELECT jp INTO @jp FROM hzpyb WHERE hz=SUBSTR(zw,@i,1);
		SET @pysx=CONCAT(@pysx,@jp);
		SET @i=@i+1;
	END WHILE;
	RETURN @pysx;
END$$
DELIMITER;


DELIMITER $$
DROP PROCEDURE IF EXISTS `xxx`$$
CREATE DEFINER = `root`@`localhost` PROCEDURE `xxx`(cxlb INT,cxcs INT)
BEGIN
  IF cxlb=0 THEN
   SELECT `cashaccountid`,`cashierid`, `cashier_time`,`uid`,
   `amount_money`,`actual_money`,`discount_money`,
	CASE `payment`
                     WHEN 0 THEN 'xxx' 
                     WHEN 1 THEN 'xxx' 
                     WHEN 2 THEN 'xxx' 
                     WHEN 3 THEN 'xxx'
        END  AS xxx
   FROM `cashaccount` WHERE `cashierid`= cxcs AND `cashier_time`>=CURDATE() ORDER BY `cashaccountid` DESC;
  ELSE  
    SELECT * FROM v_xsjlmcb WHERE v_xsjlmcb.cashaccountid=cxcs;
  END IF;
END$$
DELIMITER ;

触发器

#在插入货品信息时更改货品名称拼音缩写
DELIMITER$$
CREATE TRIGGER Update_mcsx_before_insert_goods
BEFORE INSERT ON goods FOR EACH ROW
BEGIN 
	SET new.abbreviations=pysxcx(new.gname);
END$$
DELIMITER;


#在更新商品信息时更改货品名称拼音缩写
DELIMITER$$
CREATE TRIGGER Update_mcsx_before_update_goods_gname
BEFORE INSERT ON goods FOR EACH ROW
BEGIN

	IF new.gname<>gname THEN
	SET new.abbreviations=pysxcx(new.gname);
	END IF;
	
END$$
DELIMITER;


#自动在库存中添加一条对应数据
DELIMITER$$
CREATE TRIGGER Insert_into_stock_after_insert_name
AFTER INSERT ON goods FOR EACH ROW
BEGIN
INSERT INTO stock(gid) VALUES (new.gid);
END$$
DELIMITER;


#自动在库存中删除对应库存数据
DELIMITER$$
CREATE TRIGGER delete_from_stock_after_delete_goods 
AFTER DELETE ON goods FOR EACH ROW
BEGIN
	DELETE FROM stock WHERE stock.gid=old.gid;
END$$
DELIMITER;

杂

#CAST( 字符串 AS SIGNED);  将字符串类型的数字转化为数字类型的数字
#MID()函数用于返回一个字符串中从指定位置开始的指定长度的子字符串。其语法为：
MID(str, START, LENGTH)
#start为要开始提取子字符串的位置（位置从1开始）。
#LOCATE()函数用于在一个字符串中查找子字符串，并返回子字符串第一次出现的位置。其语法为：
LOCATE(SUBSTRING, STRING, START)