！！！！！！_假设你已经为tutors表的tacademicyear字段和courses表的chours字段添加了-CSDN博客

本文链接：https://blog.csdn.net/balobalo/article/details/135398465

1.网站

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

2.建立

<1> 创建数据库的语句

要: use 数据库名；才能建表成功

CREATE DATABASE IF NOT EXISTS jxdb

CHARACTER SET UTF8MB4;

判分标准：① 是否有IF NOT EXISTS；②数据库名按照要求定义；③按要求指定了字符集

<2> 表+索引+外键声明

(1) 创建基本表的语句

CREATE TABLE Department(学院表)

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

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

///声明主键及非空属性列

CREATE TABLE Reports
(Sno CHAR(10) COMMENT '学号',
 Cno CHAR(8) COMMENT '课程号',
 Racademicyear YEAR NOT NULL COMMENT '选课学年',
 Rterm CHAR(1) NOT NULL COMMENT '选课学期',
 Grade TINYINT COMMENT '成绩',
 PRIMARY KEY (Sno,Cno)
);

//声明性别的默认值

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

(2) 基本表的增，删，改

增加‘学院院长’字段，字段名为Dheader，数据类型为CHAR(4)

ALTER TABLE Department
    ADD Dheader CHAR(4) COMMENT '学院院长';

（1）将“年龄”字段Sage改为“出生日期”，同时字段名改为Sbirth，数据类型为DATE；

（2）增加一个“生源地”字段，字段名为Snative，数据类型为VARCHAR(20)，将该字段放在出生日期Sbirth的后面

ALTER TABLE Students
    CHANGE Sage Sbirth DATE COMMENT '出生日期';
ALTER TABLE Students
    ADD Snative VARCHAR(20) COMMENT '生源地' AFTER Sbirth;

(3) 索引

（1）使用ALTER TABLE语句，在授课表的Tno列上按升序创建普通索引；

（2）使用ALTER TABLE语句，在授课表的Sclass列上按降序创建普通索引

ALTER TABLE Tutors
    ADD INDEX IDX_Tut_Tno(Tno);
ALTER TABLE Tutors
    ADD INDEX IDX_Tut_Sclass(Sclass DESC);

使用ALTER TABLE语句，在教师表的职称列上按升序，同时在出生日期列上按降序创建混合索引

ALTER TABLE Teachers
    ADD INDEX IDX_Tea_Tprof(Tprof, Tbirth DESC);

删除索引的语句

使用DROP INDEX命令，将教师表中在职称列和出生日期列上创建的索引删除

DROP INDEX IDX_Tea_Tprof ON Teachers;

(4)外键声明

（1）学院表Department，外键Dheader，参照Teachers表的Tno；

ALTER TABLE Deparment32 ADD foreign key (Dheader) references Teachers32(Tno)
on update restrict
on DELETE restrict;

3.数据库的单表查询

(SELECT-FROM)

(WHERE)

(GROUP BY-HAVING)分组统计

(ORDER BY)

(LIMIT)

LIMIT

分页显示第10条开始的后5条结果：

limit 9,5;

CASE WHEN

查询所有选修了课程号为112p0024并且有成绩的学生的考试情况，结果要求显示学生的学号、课程号和成绩等级('优'、'良'、'中、'及格'和'不及格)，并按成绩降序排序”

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;

DISTINCT

(消除重复)

COUNT(DISTINCT Cno) AS "授课门数"

4.复杂查询

一般

SELECT T.Tno, Tname,Tacademicyear,Tterm,C.Cno,Cname,Sclass
FROM Teachers T, Tutors Tut, Courses C
WHERE T.Tno=Tut.Tno AND Tut.Cno=C.Cno
AND Tname='张雪'
ORDER BY Tacademicyear,Tterm;

自身连接

用自身连接查询前序课程是数据结构的课程的信息

SELECT C1.Cno, C1.Cname, C1.Ccredit
FROM Courses C1, Courses C2
WHERE C1.Pre_Cno=C2.Cno AND C2.Cname='数据结构';

左连接

查询课程表中的所有课程在每个学期的选课情况

SELECT C.Cno AS "课程编号", Cname AS "课程名称", 
Racademicyear AS "选课学年", Rterm AS "选课学期", COUNT(*) AS "选课人数"

FROM Courses C

LEFT JOIN Reports R ON C.Cno=R.Cno

GROUP BY C.Cno, Cname, Racademicyear, Rterm

ORDER BY C.Cno, Racademicyear,Rterm;

5.数据库的更新操作与完整性约束

更新

学号为2014112103的学生的数据结构课程的补考成绩为63分，请用补考成绩更新原有的考试成绩

UPDATE Reports
SET Grade=63
WHERE Sno='2014112103'
AND Cno=
(SELECT Cno FROM Courses
WHERE Cname='数据结构'
);

自动增长的整数列

为表Tutors增加新列Tutid，将该列设置为第一列，并将Tutid列设置为自动增长的整数列，同时设置该列为主键

ALTER TABLE tutors
ADD Tutid INT AUTO_INCREMENT PRIMARY KEY FIRST;

增

INSERT INTO Tutors
(Tno,Cno,Tacademicyear,Tterm,Sclass)
VALUES
((SELECT Tno FROM Teachers WHERE Tname='许永军'),
(SELECT Cno FROM Courses WHERE Cname='计算机网络'),
2018,1,'20171151');

删除

DELETE FROM Teachers
WHERE Tname='李桂清';

默认输入UNIQUEINDETIFIER(UUID)的二进制数（建表时）

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

6.存储过程

创建存储过程的MySQL语句，存储过程名命名为PROC_StuRepInfo_学号最后两位。利用学生学号查询该名学生各学期的选修课程情况，要求显示学生的学号，姓名，选修的学年，学期，选修的课程号，课程名及其成绩，显示结果按学年和学期升序排列

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 ;

查询学号为‘2015112101'的选课情况

CALL Proc_StuRepInfo('2015112101');

OUTPUT

利用学生学号查询该名学生在指定学年，指定学期所获得的学分数以及还未获得的学分数，若该门课程尚无成绩或成绩小于60分，则为尚未获得的学分，结果以变量形式输出

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 ;

查询学号为2016115101的学生2017年第2学期的学分情况

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

OUT

统计指定教师指定学年的教学工作量(教学工作量=所有教授的课程学时数的总和)，教学工作量要求作为输出参数

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 ;

查询教师刘伟2018学年的教学工作量

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

7.触发器

当更新学生的出生日期时，检查此学生的入学年龄是否是在14~40岁之间，若是则允许更新，若不是则提示错误信息‘学生的出生日期有误，请确认后重新输入！’。”

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 ;