目錄

一.變量

1.系統變量? global session

2.用戶自定義變量

3.局部變量

二.觸發器

1.pysx函數(銷售)拼音縮寫觸發器

2.goods維護(存儲過程)

3.xkglxt

4.訂單延期

---

一.變量

1.系統變量? global session

2.用戶自定義變量

1.不用提前聲明，使用時直接''? @變量名? ? ? ''

2.set @name='lisi';

set 字段名 into @name from 表名

3.局部變量

1.需要用declare聲明

2.declare 變量名 類型?

3.set 變量名=值

二.觸發器

1.pysx函數(銷售)拼音縮寫觸發器

DELIMITER $$USE `shujuku`$$DROP FUNCTION IF EXISTS `PysxCx`$$CREATE DEFINER=`root`@`localhost` FUNCTION `PysxCx`(zw VARCHAR(20)) RETURNS VARCHAR(20) CHARSET utf8mb4READS SQL DATA
BEGINSET @pysx='';SET @l=CHAR_LENGTH(zw);SET @i=1;WHILE (@i<=@l) DOSELECT 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 ;

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?觸發器維護pysx縮寫2

DELIMITER $$USE `shujuku`$$DROP TRIGGER `Update_mcsx_before_insert_goods`$$CREATETRIGGER `Update_mcsx_before_insert_goods` BEFORE INSERT ON `goods`?FOR EACH ROW BEGIN ??SET new.abbreviations=pysxcx(new.gname); ? ?
END;
$$DELIMITER ;

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?觸發器維護pysx縮寫2

DELIMITER $$USE `shujuku`$$DROP TRIGGER `Update_mcsx_before_update_goods_gname`$$CREATETRIGGER `Update_mcsx_before_update_goods_gname` BEFORE UPDATE ON `goods`?FOR EACH ROW BEGIN ? ?IF new.gname<>old.gname THEN ? ? ?SET new.abbreviations=pysxcx(new.gname); ? ??END IF; ? ?
END;
$$DELIMITER ;

2.goods維護(存儲過程)

goodswh

DELIMITER $$USE `shujuku`$$DROP PROCEDURE IF EXISTS `goodswh`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `goodswh`(gid INT,gname VARCHAR(20),unit VARCHAR(20),barcode VARCHAR(20),retail_Price DECIMAL(10,2),promotional_Price DECIMAL(10,2),STATUS TINYINT)
BEGINIF gid=0 THENINSERT INTO goods(gname,unit,barcode,retail_Price,promotional_Price,STATUS)?VALUES (gname,unit,barcode,retail_Price,promotional_Price,STATUS);ELSEIF gid>0 THENUPDATE ?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;ELSEDELETE FROM goods WHERE ?goods.gid=-gid;END IF;END$$DELIMITER ;

goodscx

DELIMITER $$USE `shujuku`$$DROP PROCEDURE IF EXISTS `goodscx`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `goodscx`(gid INT)
BEGINIF gid=0 THENSELECT * FROM goods;ELSESELECT * FROM goods  WHERE goods.gid=gid;END IF;
END$$DELIMITER ;

?刪除觸發器

DELIMITER $$USE `shujuku`$$DROP TRIGGER /*!50032 IF EXISTS */ `delete_from_stock_after_delete_goods`$$CREATE/*!50017 DEFINER = 'root'@'localhost' */TRIGGER `delete_from_stock_after_delete_goods` BEFORE DELETE ON `goods` FOR EACH ROW BEGIN	DELETE FROM stock WHERE gid=old.gid;   
END;
$$DELIMITER ;

?11.銷售業務存儲過程

DELIMITER $$USE `shujuku`$$DROP PROCEDURE IF EXISTS `Xsjlcp_new`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `Xsjlcp_new`(cashierid INT, uid INT, payment TINYINT, xsmx VARCHAR(8000))
BEGINDECLARE amount_money DECIMAL(18,3);DECLARE actual_money DECIMAL(18,3);DECLARE discount_money DECIMAL(18,3);-- 如果存在名為 lsb 的表，則刪除它DROP TEMPORARY TABLE IF EXISTS lsb;-- 創建臨時表 lsb，包含 gid 和 xssl 兩個字段CREATE TEMPORARY TABLE lsb (gid INT, xssl NUMERIC(18,3));-- 使用 WHILE 循環解析 xsmx 中的數據并插入到 lsb 表中WHILE (xsmx > '') DOSET @k = POSITION(',' IN xsmx);SET @gid = LEFT(xsmx, @k - 1);SET xsmx = SUBSTR(xsmx, @k + 1);SET @k = POSITION(',' IN xsmx);SET @xssl = LEFT(xsmx, @k - 1);INSERT INTO lsb(gid, xssl) VALUES(@gid, @xssl);SET xsmx = SUBSTR(xsmx, @k + 1);END WHILE;-- 計算總金額和實際金額SELECT SUM(xssl * retail_Price) INTO amount_money FROM lsb, goods WHERE lsb.gid = goods.gid;SELECT SUM(xssl * promotional_Price) INTO actual_money FROM lsb, goods WHERE lsb.gid = goods.gid;SET discount_money = amount_money - actual_money;-- 插入收銀記錄到 cashaccount 表INSERT INTO cashaccount(cashierid, uid, payment, amount_money, actual_money, discount_money)VALUES (cashierid, uid, payment, amount_money, actual_money, discount_money);-- 插入銷售明細到 cashaccount_detail 表INSERT INTO cashaccount_detail(cashaccountid, gid, salesquantity, retailprice, promotionalprice)SELECT LAST_INSERT_ID(), lsb.gid, xssl, retail_Price, promotional_Price FROM lsb, goods WHERE lsb.gid = goods.gid;-- 更新庫存UPDATE stock s JOIN lsb ON s.gid = lsb.gid SET s.quantity = s.quantity - lsb.xssl;-- 如果 uid 大于等于 10000，則更新用戶的卡余額和積分IF uid >= 10000 THENUPDATE `user` SET card_balance = card_balance - actual_money, points = points + FLOOR(actual_money)WHERE uid = uid;END IF;-- 刪除臨時表 lsbDROP TEMPORARY TABLE IF EXISTS lsb;END$$DELIMITER ;

3.xkglxt

1.實現學生表的插入，需要判斷當前學號學生是否存在

DELIMITER $$USE `myspj`$$DROP PROCEDURE IF EXISTS `insert_Student`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_Student`(IN Studentid INT ,IN Studentname VARCHAR(10))
BEGINDECLARE StudentExists INT ;SELECT COUNT(*) INTO StudentExistsFROM Student WHERE id=Studentid;IF StudentExists > 0 THEN?SELECT '學號存在，學生信息插入失敗' ;ELSEINSERT INTO Student(id,`name`) VALUES (Studentid,Studentname);SELECT '學生信息插入成功';END IF;END$$DELIMITER ;

2.實現選課表的插入，需要判斷當前課程id和學生id是否存在

DELIMITER $$USE `myspj`$$DROP PROCEDURE IF EXISTS `insert_xuanke`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_xuanke`(IN _xid INT, IN _id INT, IN _kid INT, IN _chengji INT)
BEGIN ? ?DECLARE studentExists INT DEFAULT 0; ? ?DECLARE courseExists INT DEFAULT 0; ? ?SELECT COUNT(*) INTO studentExists ??FROM Student WHERE id = _id; ? ?IF studentExists = 0 THEN ? ?SELECT '學生id不存在' AS result; ?END IF; ?SELECT COUNT(*) INTO courseExists ??FROM kecheng WHERE kid = _kid; ?IF courseExists = 0 THEN ? ?SELECT '課程id不存在' AS result; ?ELSE ? ?-- 檢查學生是否已經選了這門課程 ?IF NOT EXISTS (SELECT 1 FROM xuanke WHERE kid = _kid AND id = _id) THEN ? ?INSERT INTO xuanke (xid, id, kid, chengji) VALUES (_xid, _id, _kid, _chengji); ? ?SELECT '插入成功' AS result; ?ELSE ? ?SELECT '學生已選該課程'; ? ?END IF; ? ?END IF; ?
END$$DELIMITER ;

4.訂單延期

將顧客表上余額不足1000元的，將訂單日期延后10天

UPDATE orders
SET DATE = DATE_ADD(DATE, INTERVAL 10 DAY)
WHERE orders.gid IN (SELECT gidFROM customersWHERE balance < 1000
);

將商品表中沒有顧客訂購的商品信息刪除

DELETE FROM goods
WHERE goods.`sid` NOT IN (
SELECT DISTINCT orders.`sid` FROM orders
)