https://chat18.aichatos.xyz/#/chat/1702650198606

复杂查询

将查询结果截图，截图的右下角要求标上你的学号最后两位

COALESCE()函数、FORMAT()函数；CASE WHEN建立左连接

SELECT Tno, Tname, Tprof 

FROM Teachers T

WHERE Dno=

( SELECT Dno FROM Department

  WHERE Dname='计算机与信息工程学院')

AND NOT EXISTS

(SELECT * FROM Tutors Tut 

WHERE Tut.Tno=T.Tno AND Tut.Cno='112p0054');

 

 

SELECT S.Sno,Sname,SUM(Ccredit) TotalCredit

FROM Students S, Reports R, Courses C

WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND R.Grade>=60

GROUP BY S.Sno,Sname

HAVING SUM(Ccredit) >ALL

(SELECT SUM(Ccredit)

FROM Students Stu, Reports Rep, Courses Cou

WHERE Stu.Sno=Rep.Sno AND Cou.Cno=Rep.Cno

AND LEFT(Stu.Sno,4)='2014' AND Rep.Grade>=60

AND Dno=

(SELECT Dno FROM Department WHERE Dname='计算机与信息工程学院') 

GROUP BY Rep.Sno

);

 

select sno'学号',sname'姓名',sum(Ccredit)'总学分'
from courses30,students30
group by sno,sname
having sum(Ccredit)>(select sum(Ccredit) as suc from courses30
where cno in (select cno from reports30 where Racademicyear=

更新操作约束

ALTER TABLE Teachers

    ADD CONSTRAINT CHK_Tprof_Tsal

    CHECK (Tprof = '教授' and Tsal > 3500

    OR Tprof = '副教授' and Tsal BETWEEN 2600 AND 3500

        OR Tprof = '讲师' and Tsal BETWEEN 1600 AND 2600

   

ALTER TABLE Students

    ADD CONSTRAINT CHK_LenSno CHECK (CHAR_LENGTH(Sno)=10);

    OR Tprof = '助教' and Tsal BETWEEN 800 AND 1600);

 

① 添加授课记录

INSERT INTO Tutors(Tno,Cno,Tacademicyear,Tterm,Sclass)

VALUES

((SELECT Tno FROM Teachers WHERE Tname='王平'),

(SELECT Cno FROM Courses WHERE Cname='数据库系统原理'),

2019,1,'20171202');

-- ② 再插入选课信息

INSERT INTO Reports(Sno,Cno,Racademicyear,Rterm)

(SELECT Sno,Cno,2019,1

 FROM Students, Courses

 WHERE Sclass='20171202' AND Cname='数据库系统原理');

③ 插入授课工作量统计表

INSERT INTO TeaQuantity(Tno,Tacademicyear,Tquantity)

(SELECT Tut.Tno,Tacademicyear,SUM(Chours)

FROM Tutors Tut,Courses C, Teachers T

WHERE Tut.Cno=C.Cno AND T.Tno=Tut.Tno AND Tname='王平'

GROUP BY Tno,Tacademicyear);

 

CREATE TABLE TeaQuantity

(Qid Binary(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),

Tno CHAR(4),

 Tacademicyear YEAR,

 Tquantity INT,

 CONSTRAINT UK_Tno UNIQUE KEY(Tno,Tacademicyear)

);

 

INSERT INTO TeaQuantity(Tno, Tacademicyear, Tquantity)

(SELECT Tno,Tacademicyear,SUM(Chours)

FROM Tutors Tut,Courses C

WHERE Tut.Cno=C.Cno

GROUP BY Tno,Tacademicyear);

ALTER TABLE students

    DROP CONSTRAINT FK_Mno;

ALTER TABLE students

ADD CONSTRAINT FK_Mno FOREIGN KEY(Mno)

REFERENCES major(Mno)

ON UPDATE CASCADE;

 

UPDATE Tutors

SET Tno=(SELECT Tno FROM Teachers WHERE Tname='李力')

WHERE Tno=(SELECT Tno FROM Teachers WHERE Tname='李桂清')

AND Cno=(SELECT Cno FROM Courses WHERE Cname='数字图像制作')

AND Sclass ='20161121' AND Tacademicyear=2018 AND Tterm=2;

 

INSERT INTO Tutors

(Tno,Cno,Tacademicyear,Tterm,Sclass)

VALUES

((SELECT Tno FROM Teachers WHERE Tname='许永军'),

(SELECT Cno FROM Courses WHERE Cname='计算机网络'),

2018,1,'20171151');

 

ALTER TABLE tutors

ADD Tutid INT AUTO_INCREMENT PRIMARY KEY FIRST;

 

 

UPDATE Reports

SET Grade=63

WHERE 

 

INSERT INTO

VALUES

存储

DELIMITER //

CREATE PROCEDURE PROC_StuRepInfo(

Sid CHAR(10))

BEGIN

    SELECT S.Sno, Sname, Racademicyear, Rterm, R.Cno, Cname, Grade

    FROM Students S, Reports R, Courses C

    WHERE S.Sno=R.Sno AND R.Cno=C.Cno

        AND S.Sno=Sid

    ORDER BY Racademicyear,Rterm;

END //call  PROC_StuReplnfo_30('2015112101');

DELIMITER ;

 

DELIMITER //

CREATE PROCEDURE PROC_StuGrade(

SN VARCHAR(4),CN VARCHAR(20))

BEGIN

    SELECT S.Sno,Sname,Cname,Grade,

        (CASE

            WHEN Grade<60 OR Grade IS NULL THEN 0

            ELSE Ccredit

         END) AS "Credit"

    FROM Students S INNER JOIN Reports R ON S.Sno=R.Sno

        INNER JOIN Courses C ON R.Cno=C.Cno

    WHERE S.Sname=SN AND C.Cname=CN;

END //

DELIMITER ;

 

DELIMITER //

CREATE PROCEDURE PROC_StuGradeNum(

CLS CHAR(8), CN VARCHAR(20))

BEGIN

    SELECT (CASE

            WHEN Grade BETWEEN 90 AND 100 THEN '90~100'

            WHEN Grade BETWEEN 80 AND 89 THEN '80~89'

            WHEN Grade BETWEEN 70 AND 79 THEN '70~79'

            WHEN Grade BETWEEN 60 AND 69 THEN '60~69'

            WHEN Grade BETWEEN 0 AND 59 THEN '0~59'

            ELSE '未考'

            END

) AS "成绩分段", COUNT(Sno) AS "分段人数"

FROM Reports R,Courses C

WHERE R.Cno=C.Cno AND LEFT(Sno,8)=CLS AND Cname=CN

GROUP BY (CASE

          WHEN Grade BETWEEN 90 AND 100 THEN '90~100'

          WHEN Grade BETWEEN 80 AND 89 THEN '80~89'

          WHEN Grade BETWEEN 70 AND 79 THEN '70~79'

          WHEN Grade BETWEEN 60 AND 69 THEN '60~69'

          WHEN Grade BETWEEN 0 AND 59 THEN '0~59'

          ELSE '未考'

          END);

END //

DELIMITER ;

 

DELIMITER //

CREATE PROCEDURE PROC_StuCreditInfo(

SN CHAR(10), ADyear INT, TM INT, OUT GetCredits INT, OUT NotGetCredits INT)

BEGIN

    SELECT SUM(Ccredit)

    FROM Reports R, Courses C

    WHERE R.Cno=C.Cno AND Sno=SN AND Racademicyear=ADyear

    AND Rterm=TM AND Grade>=60

    INTO GetCredits;

 

    SELECT SUM(Ccredit)

    FROM Reports R, Courses C

    WHERE R.Cno=C.Cno AND Sno=SN AND Racademicyear=ADyear

    AND Rterm=TM AND (Grade<60 OR Grade IS NULL)

    INTO NotGetCredits;

END //

DELIMITER ;

SET @GetCredit=0,@NotGetCredit=0;

CALL PROC_StuCreditInfo('2016115101',2017,2,@GetCredit,@NotGetCredit);

SELECT '2016115101' AS "学号",2017 AS "学年", 2 AS "学期", 

    IFNULL(@GetCredit,0) AS "已获学分", IFNULL(@NotGetCredit,0) AS "未获学分

 

DELIMITER //

CREATE PROCEDURE PROC_TeaWork(

 TN VARCHAR(4), ADyear INT, OUT CouHours INT)

BEGIN

    SELECT SUM(Chours)

    FROM Teachers T,Tutors TC,Courses C

    WHERE T.Tno=TC.Tno AND TC.Cno=C.Cno

        AND Tname=TN AND Tacademicyear=ADyear;

END //

DELIMITER ;

SET @CourseHours=0;

CALL PROC_TeaWork('刘伟',2018,@CourseHours);

SELECT '刘伟' AS "教师姓名",2018 AS "学年",IFNULL(@CourseHours,0) AS "教学工作量";

 

 

DELIMITER //

CREATE PROCEDURE PROC_TeaTax(

 TN VARCHAR(4), OUT Ttax DECIMAL)

BEGIN

    DECLARE tmp INT;

    DECLARE prof VARCHAR(8);

    SELECT Tprof  FROM Teachers

    WHERE Tname=TN

    INTO prof;

 

    IF (prof='教授') THEN

        SET tmp=2500;

    ELSEIF (prof='副教授') THEN

        SET tmp=2000;

    ELSE

        SET tmp=1500;

    END IF;

 

    SELECT (Tsal+Tcomm-tmp)*0.05 FROM Teachers

    WHERE Tname=TN

    INTO Ttax;

END //

DELIMITER ;call PROC_TeaTax('张雪',@shuijin);
select @shuijin ;
call PROC_TeaTax('田峰',@shuijin);
select @shuijin ;

DELIMITER //

CREATE PROCEDURE PROC_StuRepSel(

 SelColumn VARCHAR(8), SelValue VARCHAR(30))

BEGIN

    CASE SelColumn

    WHEN '学号' THEN

    BEGIN

        SELECT S.Sno,Sname,C.Cno,Cname,Racademicyear,Rterm,Grade

        FROM Students S,Reports R,Courses C

        WHERE S.Sno=R.Sno AND R.Cno=C.Cno

        AND S.Sno=SelValue;

    END;

    WHEN '学生姓名' THEN

    BEGIN

        SELECT S.Sno,Sname,C.Cno,Cname,Racademicyear,Rterm,Grade

        FROM Students S,Reports R,Courses C

        WHERE S.Sno=R.Sno AND R.Cno=C.Cno

        AND Sname=SelValue;

    END;

    WHEN '课程编号' THEN

    BEGIN

        SELECT S.Sno,Sname,C.Cno,Cname,Racademicyear,Rterm,Grade

        FROM Students S,Reports R,Courses C

        WHERE S.Sno=R.Sno AND R.Cno=C.Cno

        AND C.Cno=SelValue;

    END;

    WHEN '课程名称' THEN

    BEGIN

        SELECT S.Sno,Sname,C.Cno,Cname,Racademicyear,Rterm,Grade

        FROM Students S,Reports R,Courses C

        WHERE S.Sno=R.Sno AND R.Cno=C.Cno

        AND Cname=SelValue;

    END;

    WHEN '学年' THEN

    BEGIN

        SELECT S.Sno,Sname,C.Cno,Cname,Racademicyear,Rterm,Grade

        FROM Students S,Reports R,Courses C

        WHERE S.Sno=R.Sno AND R.Cno=C.Cno

        AND Racademicyear=CONVERT(SelValue,SIGNED);

    END;

    WHEN '班级编号' THEN

    BEGIN

        SELECT S.Sno,Sname,C.Cno,Cname,Racademicyear,Rterm,Grade

        FROM Students S,Reports R,Courses C

        WHERE S.Sno=R.Sno AND R.Cno=C.Cno

        AND Sclass=SelValue;

    END;

    ELSE

        SELECT '未找到对应字段' AS '错误';

    END CASE;

END //

DELIMITER ;

 

触发器

DELIMITER //

CREATE TRIGGER Trig_UpdStuBirth

BEFORE UPDATE

ON Students

FOR EACH ROW

BEGIN

    DECLARE msg VARCHAR(200);

    IF (NEW.Sbirth!=OLD.Sbirth) THEN

        IF ( (CONVERT(LEFT(NEW.Sno,4),UNSIGNED)-YEAR(NEW.Sbirth)) NOT BETWEEN 14 AND 40)

        THEN

            SET msg='学生的出生日期有误,请确认后重新输入!';

            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;

        END IF;

    END IF;

END //

DELIMITER ;

CREATE TRIGGER Trig_InsStuSno

BEFORE INSERT

ON Students

FOR EATCH ROW

BEGIN

DECLARE msg VARCHAR(200);

        IF LENGTH(NEW.Sno)!=10 THEN

            SET msg='学号的长度必须为10个数字!';

            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;

        ELSEIF NOT NEW.Sno REGEXP '^[0-9]+$' THEN

    SET msg='学号必须全部为数字！';

            SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT=msg;

END IF;

END;

 

 

DELIMITER //

CREATE TRIGGER Trig_UpdTeaProf

BEFORE UPDATE

ON Teachers

FOR EACH ROW

BEGIN

    DECLARE msg VARCHAR(200);

    IF (NEW.Tprof!=OLD.Tprof) THEN

        IF (NEW.Tprof NOT IN ('助教','讲师','副教授','教授')) THEN

            SET msg='教师职称有误！';

            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT =msg;

        ELSEIF (OLD.Tprof='助教' AND NEW.Tprof='讲师') THEN

            SET NEW.Tcomm=NEW.Tcomm+300;

        ELSEIF (OLD.Tprof='讲师' AND NEW.Tprof='副教授') THEN

            SET NEW.Tcomm=NEW.Tcomm+500;

        ELSEIF (OLD.Tprof='副教授' AND NEW.Tprof='教授') THEN

            SET NEW.Tcomm=NEW.Tcomm+900;

        ELSE

            SET msg='教师职称不允许越级晋升或降级!';

            SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT=msg;

        END IF;

    END IF;

END //

DELIMITER ;

 

 

 

DELIMITER //

DROP TRIGGER IF EXISTS Trig_UpdTeaProf;

CREATE TRIGGER Trig_UpdTeaProf

BEFORE UPDATE

ON Teachers

FOR EACH ROW

BEGIN

    DECLARE msg VARCHAR(200);

    IF (NEW.Tprof!=OLD.Tprof) THEN

        IF (NEW.Tprof NOT IN ('助教','讲师','副教授','教授')) THEN

            SET msg='教师职称有误！';

            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT =msg;

        ELSEIF (OLD.Tprof='助教' AND NEW.Tprof='讲师') THEN

            SET NEW.Tsal=1600;

            SET NEW.Tcomm=NEW.Tcomm+300;

        ELSEIF (OLD.Tprof='讲师' AND NEW.Tprof='副教授') THEN

            SET NEW.Tsal=2600;

            SET NEW.Tcomm=NEW.Tcomm+500;

        ELSEIF (OLD.Tprof='副教授' AND NEW.Tprof='教授') THEN

            SET NEW.Tsal=3600;

            SET NEW.Tcomm=NEW.Tcomm+900;

        ELSE

            SET msg='教师职称不允许越级晋升或降级!';

            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;

        END IF;

    END IF;

END //

DELIMITER ;

 

 

DELIMITER //

DROP TRIGGER IF EXISTS Trig_InsTutCno;

CREATE TRIGGER Trig_InsTutCno

BEFORE INSERT ON Tutors

FOR EACH ROW

BEGIN

    DECLARE msg VARCHAR(200);

    IF EXISTS(SELECT * FROM Tutors WHERE Cno=NEW.Cno AND Sclass=NEW.Sclass) THEN

        SET msg='已有该班学生该门课程的授课信息,请确认课程和班级后重新输入!';

        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;

    ELSE

        INSERT INTO Reports(Sno, Cno, Racademicyear, Rterm)

            (SELECT Sno,NEW.Cno,NEW.Tacademicyear,NEW.Tterm

             FROM Students WHERE Sclass=NEW.Sclass);

    END IF;

END //

DELIMITER ;

 

 

DELIMITER //

DROP TRIGGER IF EXISTS Trig_DelCou;

CREATE TRIGGER Trig_DelCou

BEFORE DELETE ON Courses

FOR EACH ROW

BEGIN

    DECLARE msg VARCHAR(200);

    IF EXISTS(SELECT * FROM reports WHERE Cno=OLD.Cno AND Grade IS NOT NULL) THEN

        SET msg='已有学生选修该门课程,且已获成绩,因此不能删除该门课程!';

        SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;

    ELSE

        DELETE FROM reports WHERE Cno=OLD.Cno;

        DELETE FROM tutors WHERE tutors.Cno=OLD.Cno;

    END IF;

END //

DELIMITER ;

 

1）创建学分表

CREATE TABLE StuCredits

(Sno CHAR(10) primary key,

 TotalCredit INT,

 FOREIGN KEY (Sno) REFERENCES Students(Sno));

（2）触发器程序

DELIMITER //

DROP TRIGGER IF EXISTS Trig_UpdRepGrade;

CREATE  TRIGGER Trig_UpdRepGrade

AFTER UPDATE

ON Reports

FOR EACH ROW

BEGIN

    IF (NEW.Grade!=OLD.Grade) THEN

        IF NOT EXISTS(SELECT * FROM StuCredits WHERE Sno=NEW.Sno) THEN

            INSERT INTO StuCredits

                SELECT Sno,SUM(Ccredit)

                FROM Reports R,Courses C

                WHERE R.Cno=C.Cno

                    AND Grade>=60 AND Sno=NEW.Sno;

        ELSE

            UPDATE StuCredits,

                (SELECT Sno,SUM(Ccredit) TC

                FROM Reports R,Courses C

                WHERE R.Cno=C.Cno

                    AND Grade>=60 AND Sno=NEW.Sno) SC

            SET StuCredits.TotalCredit=SC.TC

            WHERE StuCredits.Sno=SC.Sno;

        END IF;

    END IF;

END //

DELIMITER ;

蛋白

SELECT Sno AS "学号",Cno AS "课程编号",

(CASE WHEN Grade>=90 THEN '优'

           WHEN Grade BETWEEN 80 AND 89 THEN '良'

           WHEN Grade BETWEEN 70 AND 79 THEN '中'

           WHEN Grade BETWEEN 60 AND 69 THEN '及格'

           ELSE '不及格'

END) AS "成绩等级"

FROM Reports

WHERE Cno='112p0024' AND Grade IS NOT NULL

ORDER BY Grade DESC;

 

SELECT Cno AS "课程编号", COUNT(DISTINCT Tno) AS "授课人数"

FROM Tutors

GROUP BY Cno

HAVING COUNT(DISTINCT Tno)>=3

ORDER BY COUNT(DISTINCT Tno) DESC;

SELECT Tno AS "教师编号",Tacademicyear AS "学年",COUNT(DISTINCT Cno) AS "授课门数" 

FROM Tutors
GROUP BY Tno,Tacademicyear 

ORDER BY Tno

LIMIT 9,5;

 

 

SELECT Cno AS "课程编号", COUNT(*) AS "选课人数", AVG(Grade) AS "平均成绩" 

FROM Reports
WHERE SUBSTRING(Sno,5,2)='11' 

GROUP BY Cno
HAVING COUNT(*)>=5;

SELECT Sno,Sname,Snative 

FROM Students

SELECT Sno,Sname,YEAR(Sbirth)  AS "BornYear"

FROM Students
WHERE Sname LIKE '_一%' AND Sgender='男'

ORDER BY Sno DESC;SELECT *
FROM Courses
WHERE Cname LIKE '%计算机%' OR Cname LIKE '%原理%';

WHERE Snative <>'广东潮州' AND Snative NOT LIKE '山东%';