数据库练习一-CSDN博客

本文链接：https://blog.csdn.net/m0_65258833/article/details/130301173

CREATE DATABASE Jxcxt;

CREATE TABLE 商品表(
   商品id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   商品名称 CHAR(20) NOT NULL,
   单位 CHAR(20),
   单价 DOUBLE,
   商品状态 CHAR(20)
)CHARSET utf8;

CREATE TABLE 库存表(
   库存id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   商品id INT NOT NULL,
   库存数量 INT,
   FOREIGN KEY(商品id) REFERENCES 商品表(商品id)
)CHARSET utf8;

DROP TRIGGER IF EXISTS delete_after_goods;
DELIMITER $$
CREATE TRIGGER delete_BEFORE_goods BEFORE DELETE ON 商品表
FOR EACH ROW
BEGIN
DELETE FROM 库存表 WHERE 商品id=old.商品id;
END $$
DELIMITER ;

DELETE FROM 商品表 WHERE 商品id=1;

CREATE DATABASE Xkglxt;
CREATE TABLE 学生表(
   学生id INT PRIMARY KEY,
   学号 VARCHAR(20),
   姓名 CHAR(20),
   专业 CHAR(20),
   年级 CHAR(20),
   姓名拼音缩写 CHAR(20)
)CHARSET utf8;

CREATE TABLE 课程表(
   课程id INT NOT NULL PRIMARY KEY,
   课程名称 VARCHAR(20),
   学分 INT
)CHARSET utf8;

CREATE TABLE 选课表(
   选课id INT NOT NULL PRIMARY KEY,
   学生id INT,
   课程id INT,
   成绩 DOUBLE(10,2),
   FOREIGN KEY (学生id) REFERENCES 学生表(学生id),
   FOREIGN KEY (课程id) REFERENCES 课程表(课程id)
)CHARSET utf8;

CREATE VIEW v1 AS SELECT 选课id,学号,姓名,课程名称,成绩 FROM `课程表`,`选课表`,`学生表`
WHERE `课程表`.`课程id`=`选课表`.`课程id`AND `学生表`.`学生id`=`选课表`.`学生id`
WITH CHECK OPTION

SELECT 成绩 FROM v1 WHERE 姓名='张三' AND 课程名称='数据库开发';

DELIMITER $$
   CREATE FUNCTION pysxcx(mz CHAR(20))
   RETURN CHAR(20) NO SQL
   BEGIN
       SET @i=1,
       SET @mcsx='',
       SET @l=CHAR_LENGTH(mz),
       WHILE(@i<=@l) DO
           SET @hz=SUBSTR(mz,@i,1);
           SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;
           SET @mcsx=CONCAT(@mcsx,@jp);
           SET @i=@i+1;
       END WHILE;
   END ;
DELIMITER ;

DROP PROCEDURE IF EXISTS HpxxWh;
DELIMITER $$
CREATE PROCEDURE HpxxWh(hpid INT,hpmc VARCHAR(50),hptm VARCHAR(50),jldw VARCHAR(20),
           hpzt INT,dj DECIMAL(18,3),cxj DECIMAL(18,3))
BEGIN
   IF(hpid=0) THEN#增加
       INSERT INTO hpxxb(`hpmc`,`hptm`,`jldw`,`hpzt`,`dj`,`cxj`,`mcsx`)
           VALUE (hpmc,hptm,jldw,hpzt);
   ELSE IF(hpid<1) THEN#删除
       DELETE * FROM hpxxb WHERE hpid=-hpid;
END $$
DELIMITER ;

DELIMITER $$

CREATE FUNCTION pysxcx(mz VARCHAR(20) )
RETURNS VARCHAR(20) NO SQL
BEGIN
   SET @i=1;
   SET @mcsx='';
   SET @len=CHAR_LENGTH(mz);
   WHILE(@i<=@len) DO
       SET @hz=SUBSTR(mz,@i,1);
       SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;
       SET @mcsx=CONCAT(@mcsx,@jp);
       SET @i=@i+1;
   END WHILE;
   RETURN @mcsx;
END $$
DELIMITER ;
DROP TRIGGER IF EXISTS update_mcsx_before_insert_hpccb;
DELIMITER $$

CREATE TRIGGER update_mcsx_before_insert_hpccb
BEFORE INSERT ON `shopping`.`tb_hpxxb`
FOR EACH ROW BEGIN
SET new.mcsx=pysxcx(new.hpmc);
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS update_mcsx_before_update_hpccb;
DELIMITER $$

CREATE TRIGGER update_mcsx_before_update_hpccb
BEFORE UPDATE ON `shopping`.`tb_hpxxb`
FOR EACH ROW BEGIN
SET new.mcsx=pysxcx(new.hpmc);
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS hpxxbcp;
DELIMITER $$
CREATE PROCEDURE hpxxbcp(hpid INT,hpmc VARCHAR(20),hptm VARCHAR(20),jldw VARCHAR(20),
       hpzt TINYINT,dj DECIMAL(18,3),cxj DECIMAL(18,3),mcsx VARCHAR(10))
BEGIN
   IF hpid=0 THEN
   INSERT INTO tb_hpxxb(hpmc,hptm,jldw,hpzt,dj,cxj,mcsx) VALUES(hpmc,hptm,jldw,hpzt,dj,cxj,mcsx);
   SET @hpid=@@identity;
   INSERT INTO tb_hpkcb(hpid,kcsl,zrkc,yckc) VALUES(@hpid,0,0,0);
   ELSE IF hpid<0 THEN
   DELETE FROM tb_hpkcb WHERE `tb_hpkcb`.`hpid`=-hpid;
   DELETE FROM tb_hpxxb WHERE `tb_hpxxb`.`hpid`=-hpid;
   ELSE UPDATE tb_hpxxb SET `tb_hpxxb`.`hpmc`=hpmc,`tb_hpxxb`.`hptm`=hptm,`tb_hpxxb`.`jldw`=jldw,
           `tb_hpxxb`.`hpzt`=hpzt,`tb_hpxxb`.`dj`=dj,`tb_hpxxb`.`cxj`=cxj,`tb_hpxxb`.`mcsx`=pysxcx(hpmc)
           WHERE `tb_hpxxb`.`hpid`=hpid;
   END IF;
END $$
DELIMITER ;

SELECT POSITION('a'IN'basdafk');

DROP PROCEDURE IF EXISTS xsjlcp;
DELIMITER $$
CREATE PROCEDURE xsjlcp(xsmx VARCHAR(1000))
BEGIN
   CREATE TEMPORARY TABLE lsb(hpid INT,xssl NUMERIC(18,3));
   WHILE (xsmx>'') DO
       SET @i=POSITION(',' IN xsmx);
       SET @hpid=LEFT(xsmx,@i-1);
       SET xsmx=SUBSTR(xsmx,@i+1);
       SET @i=POSITION(',' IN xsmx);
       SET @xssl=LEFT(xsmx,@i-1);
       INSERT INTO lsb VALUES(@hpid,@xssl);
       SET xsmx=SUBSTR(xsmx,@i+1);
   END WHILE;
   SELECT * FROM lsb;
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS xsjlcp_new $$
CREATE PROCEDURE xsjlcp_new(syyid INT,hyid INT,ysje DECIMAL(18,2),ssje DECIMAL(18,2),zffs TINYINT,xsmx VARCHAR(1000))
BEGIN
   INSERT INTO tb_syjlb(syyid,gkid,ysje,ssje,zffs)VALUES(syyid,hyid,ysje,ssje,zffs);
   SET @syid=@@identity;
   DROP TABLE IF EXISTS lsb;
   CREATE TEMPORARY TABLE lsb(hpid INT,xssl NUMERIC(18,3));
   WHILE (xsmx>'') DO
       SET @k=POSITION(','IN xsmx);
       SET @hpid=LEFT(xsmx,@k-1);
       SET xsmx=SUBSTR(xsmx,@k+1);
       SET @k=POSITION(','IN xsmx);
       SET @xssl=LEFT(xsmx,@k-1);
       INSERT INTO lsb VALUES(@hpid,@xssl);
       SET xsmx=SUBSTR(xsmx,@k+1);
   END WHILE;
   INSERT INTO tb_symxb(syid,hpid,xssl,dj,lsj)
       SELECT @syid,lsb.hpid,xssl,dj,cxj FROM lsb,tb_hpxxb WHERE lsb.hpid=tb_hpxxb.`hpid`;
   DROP TABLE IF EXISTS lsb1;
   CREATE TEMPORARY TABLE lsb1(hpid INT,xssl NUMERIC(18,3));
   INSERT INTO lsb1 SELECT hpid,SUM(xssl) FROM lsb GROUP BY hpid;
   UPDATE tb_hpkcb b1,lsb1 b2 SET b1.kcsl=b1.kcsl-b2.xssl WHERE b1.hpid=b2.hpid;
       IF hyid>1000 THEN
           UPDATE `tb_hyxxb` SET knye=knye-ssje,kyjf=kyjf+FLOOR(ssje) WHERE tb_hyxxb.`hyid`=hyid;
       END IF;
END $$
DELIMITER ;

CALL xsjlcp_new(1,11100,11,11,0,'8,1,9,2,10,3,11,1,');
SELECT * FROM lsb1;

SELECT syid,syyid,sysj,gkid,ysje,ssje,yhje,CASE zffs
   WHEN 0 THEN '现金'
   WHEN 1 THEN '储值卡'
   WHEN 2 THEN '支付宝'
   WHEN 3 THEN '微信支付'
   END AS 支付方式
   FROM tb_syjlb WHERE syyid=1 AND sysj>=CURDATE() ORDER BY syid DESC;

CREATE VIEW v_xsmx AS (SELECT mxid,syid,hpmc,jldw,xssl
,tb_symxb.dj,tb_symxb.lsj FROM tb_symxb,tb_hpxxb
WHERE tb_symxb.`hpid`=tb_hpxxb.`hpid`);

DECLARE SALARY_DIS cursor FOR

SELECT EmployeeID,InCome_OutCome

FROM Salary

ORDER BY InCome_OutCome DESC

SET @SEQ=0

SET @OK=0

OPEN SALARY_DIS

FETCH SALARY_DIS INTO @X_EM_ID,@ACT_IN

WHILE @SEQ<3 AND @OK=0

BEGIN

SET @SEQ=@SEQ+1