数据库题库大题

一、已知进销存数据库Jxcxt包含如下数据表：
1）商品表：商品id(自增1), 商品名称, 单位, 单价, 商品状态
2）库存表：库存id(自增1), 商品id, 库存数量 
CREATE DATABASE jxcxt;
USE jxcxt;

CREATE TABLE spb(
    spid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    spmc VARCHAR(50),
    dw VARCHAR(10),
    dj DOUBLE,
    STATUS INT
);

CREATE TABLE kcb(
    kcid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    spid INT,
    kcsl INT,
    FOREIGN KEY(spid) REFERENCES spb(spid)
);

-- 在商品表上创建一个触发器(名字自定),当删除商品时自动删除该商品在库存表中的记录
DELIMITER $$
CREATE TRIGGER delete_sp_auto_delete_kcb
BEFORE DELETE ON spb
FOR EACH ROW
BEGIN
    DELETE FROM kcb WHERE spid=old.spid;
END $$
DELIMITER ;

-- 创建一个存储过程（名字自定），实现商品表的的插入，需要判断当前商品名称是否存在
DELIMITER $$
CREATE PROCEDURE insert_spb_spmc_exists(
    IN sp_mc VARCHAR(50),
    IN sp_dw VARCHAR(20),
    IN sp_dj DOUBLE,
    IN sp_status INT
)
BEGIN
    DECLARE spmc_count INT;
    SELECT COUNT(*) INTO spmc_count FROM spb WHERE spmc=sp_mc;
    IF spmc_count>0 THEN
        SELECT '商品已存在！' AS result;
    ELSE
        INSERT INTO spb(spmc,dw,dj,STATUS) VALUES(sp_mc,sp_dw,sp_dj,sp_status);
        -- 或者下面这条
        -- INSERT INTO spb(spid,spmc,dw,dj,STATUS) VALUES((select MAX(spid) from spb)+1,sp_mc,sp_dw,sp_dj,sp_status);
    END IF;
END $$
DELIMITER ;

-- 调用验证
-- call insert_spb_spmc_exists('牛奶','元',3,'0');
-- call insert_spb_spmc_exists('苹果','斤',6,'1');

-- 在商品表上创建一个触发器(名字自定),当新增商品时
-- 自动在库存表中插入该商品的库存信息(库存数量为0)
DELIMITER $$
CREATE TRIGGER insert_sp_auto_insert_kcb
AFTER INSERT ON spb
FOR EACH ROW
BEGIN
    INSERT INTO kcb(spid,kcsl) VALUES(new.spid,0);
END $$
DELIMITER ;

-- 调用验证
-- call insert_spb_exist_spmc('香蕉','斤',2,'1')

---------------------------------------------------------------------------------------

二、已知选课管理系统Xkglxt包含如下数据表： 
1）学生表: 学生id, 学号, 姓名, 专业, 年级, 姓名拼音缩写 
2）课程表: 课程id, 课程名称, 学分   
3）选课表: 选课id, 学生id, 课程id, 成绩

CREATE DATABASE xkglxt;
USE xkglxt;

CREATE TABLE student(
    sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    xh VARCHAR(50),
    xm VARCHAR(50),
    zy VARCHAR(50),
    nj VARCHAR(50),
    xmpysx VARCHAR(50)
);

CREATE TABLE course(
    cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cname VARCHAR(50),
    xf INT
);

CREATE TABLE sc(
    xkid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sid INT,
    cid INT,
    score DOUBLE,
    FOREIGN KEY(sid) REFERENCES student(sid),
    FOREIGN KEY(cid) REFERENCES course(cid)
);

-- 创建视图 V_选课,其中包含这些字段: 选课id, 学号, 姓名, 课程名, 成绩
CREATE VIEW v_sc
AS
SELECT xkid,xh,xm,cname,score
FROM student,sc,course
WHERE student.sid=sc.sid AND sc.cid=course.cid;
或
CREATE VIEW v_sc
AS
SELECT xkid,xh,xm,cname,score
FROM sc
INNER JOIN student ON student.sid=sc.sid
INNER JOIN course ON sc.cid=course.cid;

-- 查询张三的数据开发技术课程的成绩
SELECT score
FROM v_sc
WHERE xm='张三' AND cname='数据开发技术';

-- 创建一个存储过程（名字自定），实现学生表的插入，姓名拼音缩写需要调用getstrpy函数(与pysx函数内容一样，函数名不同)实现。
DELIMITER $$
CREATE PROCEDURE insert_student_xmpysx(
    IN s_xh VARCHAR(20),
    IN s_xm VARCHAR(20),
    IN s_zy VARCHAR(20),
    IN s_nj VARCHAR(20),
    IN s_xmpysx VARCHAR(20)
)
BEGIN
    DECLARE sx VARCHAR(20);
    SET sx=getstrpy(s_xmpysx);
    INSERT INTO student(xh,xm,zy,nj,xmpysx) VALUES(s_xh,s_xm,s_zy,s_nj,sx);
END $$
DELIMITER ;

/*编写函数pysx(拼音缩写(查询)) 实现汉字字符串的拼音缩写查询*/
DELIMITER $$
CREATE FUNCTION pysx(zw VARCHAR(50)) RETURNS VARCHAR(10) CHARSET utf8
NO SQL
BEGIN
    SET @l=CHAR_LENGTH(zw);
    SET @i=1;
    SET @pysx='';
    SET @jp='';
    WHILE @i<=@l DO
        SET @hz=SUBSTRING(zw,@i,1);
        SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;
        SET @pysx=CONCAT(@pysx,@jp);
        SET @i=@i+1;
    END WHILE;
    RETURN @pysx;
END $$
DELIMITER ;

-- 自定义函数拼音缩写查询: pysx('中文字符串') 返回该中文字符串的拼音缩写
-- 在学生表上创建一个触发器(名字自定),当新增学生时自动处理姓名拼音缩写
DELIMITER $$
CREATE TRIGGER insert_student_xmpysx
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    INSERT INTO student(xmpysx) VALUES(pysx(new.xm));
    -- 或
    -- update student set xmpysx=pysx(new.xm) where sid=new.sid;
END $$
DELIMITER ;

-- 在学生表上创建一个触发器(名字自定),
-- 当修改学生表的姓名时自动处理姓名拼音缩写的SQL语句
DELIMITER $$
CREATE TRIGGER update_student_xm_auto_xmpysx
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    UPDATE student SET xmpysx=pysx(new.xm) WHERE sid=new.sid;
END $$
DELIMITER ;

-- 在课程表上创建一个触发器(名字自定)。
-- 当删除课程信息时自动删除该课程的选课信息
DELIMITER $$
CREATE TRIGGER delete_course_auto_delete_sc
AFTER DELETE ON course
FOR EACH ROW
BEGIN
    DELETE FROM sc WHERE cid=old.cid;
END $$
DELIMITER ;

-- 创建一个存储过程（名字自定），实现选课表的插入，
-- 需要判断当前课程id和学生id是否存在
DELIMITER $$
CREATE PROCEDURE insert_course_exists_cid_sid(
    IN input_sid INT,
    IN input_cid INT,
    IN input_score DOUBLE
)
BEGIN
    DECLARE kcid_exists INT;
    DECLARE xsid_exists INT;
    
    -- 判断课程id是否存在
    SELECT cid INTO kcid_exists FROM course WHERE cid=input_cid;
    IF kcid_exists IS NULL THEN
        signal SQLSTATE '45000' SET message_text='课程id不存在！';
    END IF;
    -- 判断学生id是否存在
    SELECT sid INTO xsid_exists  FROM student WHERE sid=input_sid;
    IF sxid_exists IS NULL THEN
        signal SQLSTATE '45000' SET message_text='学生id不存在！';
    END IF; 
    -- 插入选课表sc
    INSERT INTO sc(sid,cid,score) VALUES(input_sid,input_cid,input_score);
END $$
DELIMITER ;

-- 创建一个存储过程（名字自定），实现课程表的插入，
-- 需要判断当前课程名称的课程是否存在
DELIMITER $$
CREATE PROCEDURE insert_course_exists_cname(
    IN input_cname VARCHAR(20),
    IN input_xf INT
)
BEGIN
    DECLARE cid_exists VARCHAR(20);
    SELECT cid INTO cid_exists FROM course WHERE cname=input_cname;
    
    IF cid_exists IS NULL THEN
        INSERT INTO course(cname,xf) VALUES(input_cname,input_xf);
    ELSE
        SELECT '该课程已存在！' AS result;
    END IF;
END $$
DELIMITER ;

-- 创建一个存储过程（名字自定），实现学生表的插入，
-- 需要判断当前学号的学生是否存在
DELIMITER $$
CREATE PROCEDURE insert_student_exists_xh(
    IN input_xh VARCHAR(20),
    IN input_xm VARCHAR(20),
    IN input_zy VARCHAR(20),
    IN input_nj VARCHAR(20),
    IN input_xmpysx VARCHAR(20)
)
BEGIN
    DECLARE student_count INT;
    SELECT COUNT(*) INTO student_count FROM student WHERE sid=input_sid;
    IF student_count>0 THEN
        SELECT '该学号已存在！' AS result;
    ELSE
        INSERT INTO student(xh,xm,zy,nj,xmpysx) VALUES(input_xh,input_xm,input_zy,input_nj,input_xmpysx);
        SELECT '插入成功！' AS result;
    END IF;
END $$
DELIMITER ;