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

（1）创建数据库的语句

CREATE DATABASE IF NOT EXISTS jxdb

CHARACTER SET UTF8MB4;

（1）创建基本表的语句

CREATE TABLE Department

(Dno CHAR(2) PRIMARY KEY COMMENT '学院编号',

 Dname VARCHAR(15) NOT NULL COMMENT '学院名称');

 Sgender ENUM('男','女') DEFAULT '男' COMMENT '性别',

修改基本表的语句

ALTER TABLE Courses

    ADD Pre_Cno CHAR(8) COMMENT '先序课程编号' AFTER Cname;

创建索引的语句

CREATE INDEX IDX_Stu_Mno ON Students (Mno DESC)

创建外键的语句

ALTER TABLE Department

            ADD CONSTRAINT FK_Dep_Dheader FOREIGN KEY (Dheader) REFERENCES Teachers(Tno);

coalesce函数

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

SELECT C.Cno '课程号', Cname '课程名',

(CASE WHEN SelNum IS NULL THEN '尚未选'

ELSE CAST(SelNum AS CHAR)

END) '选课人数',

(COALESCE(CAST(FORMAT(AvgGrd,2) AS CHAR),'尚无成绩')) '平均分',

(COALESCE(CAST(FORMAT(MaxGrd,2) AS CHAR),'尚无成绩')) '最高分',

(COALESCE(CAST(FORMAT(MinGrd,2) AS CHAR),'尚无成绩')) '最低分'

FROM Courses C LEFT JOIN

(SELECT Cno, COUNT(*) 'SelNum', AVG(Grade) 'AvgGrd',

MAX(Grade) 'MaxGrd', MIN(Grade) 'MinGrd'

FROM Reports

GROUP BY Cno) R

ON C.Cno=R.Cno

ORDER BY C.Cno;

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 "成绩等级"

自动增长的整数列

ALTER TABLE tutors

ADD Tutid INT AUTO_INCREMENT PRIMARY KEY FIRST;

级联删除

alter table reports13

add foreign key(sno) references students13(sno)

on delete cascade;

二进制转化

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

 CONSTRAINT UK_Tno UNIQUE KEY(Tno,Tacademicyear

CHECK约束

ALTER TABLE Students

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

创建存储过程

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 //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE PROC_StuCreditInfo(

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

调用存储过程（out形）in类型直接call

1.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 "未获学分";

2.set @huode=0,@weihuode=0;

call PROC_StuCreditInfo_13(2016115101, 2017, 2, @huode, @weihuode);

select @huode,@weihuode;

触发器

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 ;