帮我建出相关表并且回答相关问题

好的，我将帮助您构建相关的表并回答您的问题。首先，我们来创建所需的表： ### 创建表的 SQL 语句 #### 学生表 `student` ```sql CREATE TABLE student ( sno CHAR(5) PRIMARY KEY, sname VARCHAR(20) NOT NULL, sdept VARCHAR(20) NOT NULL, sclass VARCHAR(10) NOT NULL, ssex VARCHAR(10), sbirth DATE, scredit INT ); ``` #### 课程表 `course` ```sql CREATE TABLE course ( cno CHAR(3) PRIMARY KEY, cname VARCHAR(50) UNIQUE, chour INT, ccredit FLOAT ); ``` #### 教师表 `teach` ```sql CREATE TABLE teach ( tno CHAR(6) PRIMARY KEY, tname VARCHAR(20), tsex VARCHAR(10), tdept VARCHAR(20) ); ``` #### 成绩表 `score` ```sql CREATE TABLE score ( sno CHAR(5), cno CHAR(3), tno CHAR(6), grade FLOAT, PRIMARY KEY (sno, cno, tno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno), FOREIGN KEY (tno) REFERENCES teach(tno) ); ``` ### 插入数据 #### 插入学生表 `student` 的数据 ```sql INSERT INTO student VALUES ('96001', '马小燕', '计算机', '01', '女', '2000-01-02', 0); INSERT INTO student VALUES ('96002', '黎明', '计算机', '01', '男', '2000-03-05', 0); INSERT INTO student VALUES ('96003', '刘东明', '数学', '01', '男', '2000-10-05', 0); INSERT INTO student VALUES ('96004', '赵志勇', '信息', '02', '男', '2000-08-08', 0); INSERT INTO student VALUES ('97001', '马蓉', '数学', '02', '女', '2001-03-04', 0); INSERT INTO student VALUES ('97002', '李成功', '计算机', '01', '男', '2001-09-10', 0); INSERT INTO student VALUES ('97003', '黎明', '信息', '03', '女', '2002-02-08', 0); INSERT INTO student VALUES ('97004', '李丽', '计算机', '02', '女', '2002-01-05', 0); INSERT INTO student VALUES ('96005', '司马志明', '计算机', '02', '男', '2001-11-23', 0); ``` #### 插入课程表 `course` 的数据 ```sql INSERT INTO course VALUES ('001', '数学分析', 64, 4); INSERT INTO course VALUES ('002', '普通物理', 64, 4); INSERT INTO course VALUES ('003', '微机原理', 56, 3.5); INSERT INTO course VALUES ('004', '数据结构', 64, 4); INSERT INTO course VALUES ('005', '操作系统', 56, 3.5); INSERT INTO course VALUES ('006', '数据库原理', 56, 3.5); INSERT INTO course VALUES ('007', '编译原理', 48, 3); INSERT INTO course VALUES ('008', '程序设计', 32, 2); ``` #### 插入教师表 `teach` 的数据 ```sql INSERT INTO teach VALUES ('052501', '王成刚', '男', '计算机'); INSERT INTO teach VALUES ('052502', '李正科', '男', '计算机'); INSERT INTO teach VALUES ('052503', '严敏', '女', '数学'); INSERT INTO teach VALUES ('052504', '赵高', '男', '数学'); INSERT INTO teach VALUES ('052505', '刘玉兰', '女', '计算机'); INSERT INTO teach VALUES ('052506', '王成刚', '男', '信息'); INSERT INTO teach VALUES ('052507', '马悦', '女', '计算机'); ``` #### 插入成绩表 `score` 的数据 ```sql INSERT INTO score VALUES ('96001', '001', '052503', 77.5); INSERT INTO score VALUES ('96001', '003', '052501', 89); INSERT INTO score VALUES ('96001', '004', '052502', 86); INSERT INTO score VALUES ('96001', '005', '052505', 82); INSERT INTO score VALUES ('96002', '001', '052504', 88); INSERT INTO score VALUES ('96002', '003', '052502', 92.5); INSERT INTO score VALUES ('96002', '006', '052507', 90); INSERT INTO score VALUES ('96005', '004', '052502', 92); INSERT INTO score VALUES ('96005', '005', '052505', 90); INSERT INTO score VALUES ('96005', '006', '052505', 89); INSERT INTO score VALUES ('96005', '007', '052507', 78); INSERT INTO score VALUES ('96003', '001', '052504', 69); INSERT INTO score VALUES ('97001', '001', '052504', 96); INSERT INTO score VALUES ('97001', '008', '052505', 95); INSERT INTO score VALUES ('96004', '001', '052503', 87); INSERT INTO score VALUES ('96003', '003', '052501', 91); INSERT INTO score VALUES ('97002', '003', '052502', 91); INSERT INTO score VALUES ('97002', '004', '052505', NULL); INSERT INTO score VALUES ('97002', '006', '052507', 92); INSERT INTO score VALUES ('97004', '005', '052502', 90); INSERT INTO score VALUES ('97004', '006', '052501', 85); ``` ### 实现相关功能 #### 基础部分 1. **在 `student` 表中增加籍贯字段** ```sql ALTER TABLE student ADD COLUMN nativeplace VARCHAR(20); ``` 2. **删除 `student` 表中籍贯字段** ```sql ALTER TABLE student DROP COLUMN nativeplace; ``` 3. **将 `student` 表中的 `ssex` 字段定义改为 `VARCHAR(3)`** ```sql ALTER TABLE student MODIFY COLUMN ssex VARCHAR(3); ``` 4. **在 `course` 表中 `cname` 上添加唯一性约束 `uk_cno`** ```sql ALTER TABLE course ADD CONSTRAINT uk_cno UNIQUE (cname); ``` 5. **在 `student` 表中插入一条数据** ```sql INSERT INTO student VALUES ('11111', '马明', '计算机', '01', '女', '2000-01-02', null); ``` 6. **将 `student` 表中所有学生的总学分加2，空值记录也参加计算** ```sql UPDATE student SET scredit = IFNULL(scredit, 0) + 2; ``` 7. **将 `student` 表中马小燕的出生日期修改为 ‘2000-01-22’** ```sql UPDATE student SET sbirth = '2000-01-22' WHERE sname = '马小燕'; ``` 8. **删除 `student` 表中学号为 '11111' 的记录** ```sql DELETE FROM student WHERE sno = '11111'; ``` 9. **利用 `student` 表创建表 `s1`，`s1` 的结构与内容与 `student` 表完全相同** ```sql CREATE TABLE s1 AS SELECT * FROM student; ``` 10. **删除学生表 `s1` 中计算机系学生信息** ```sql DELETE FROM s1 WHERE sdept = '计算机'; ``` 11. **删除 `s1` 表** ```sql DROP TABLE s1; ``` 12. **求2000年出生的学生的学号、姓名，结果列别名为汉字** ```sql SELECT sno AS '学号', sname AS '姓名' FROM student WHERE YEAR(sbirth) = 2000; ``` 13. **求选修了课程的学生学号，结果表中学号显示唯一** ```sql SELECT DISTINCT sno FROM score; ``` 14. **求学时在 1-50 之间的课程信息** ```sql SELECT * FROM course WHERE chour BETWEEN 1 AND 50; ``` 15. **求不是 计算机系或信息系学生** ```sql SELECT * FROM student WHERE sdept NOT IN ('计算机', '信息'); ``` 16. **求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生** ```sql SELECT * FROM student WHERE LENGTH(sname) >= 3 AND SUBSTRING(sname, -3, 1) = '马'; ``` 17. **求选修 '052501' 老师，成绩在80至90之间，学号为96xxx的学生成绩信息** ```sql SELECT * FROM score WHERE tno = '052501' AND grade BETWEEN 80 AND 90 AND sno LIKE '96%'; ``` 18. **求没有 成绩的学生的学号和课程号** ```sql SELECT sno, cno FROM score WHERE grade IS NULL; ``` 19. **求学生总人数** ```sql SELECT COUNT(*) AS '学生总人数' FROM student; ``` 20. **求选修了课程的学生人数, 输出列的别名为“选修人数”** ```sql SELECT COUNT(DISTINCT sno) AS '选修人数' FROM score; ``` 21. **求选修各门课程的最高、最低与平均成绩** ```sql SELECT cno, MAX(grade) AS '最高成绩', MIN(grade) AS '最低成绩', AVG(grade) AS '平均成绩' FROM score GROUP BY cno; ``` 22. **求学生人数不足3人的系及其相应的学生数** ```sql SELECT sdept, COUNT(*) AS '学生数' FROM student GROUP BY sdept HAVING COUNT(*) < 3; ``` 23. **求计算机系中各班的学生人数, 结果按班级人数倒序排列** ```sql SELECT sclass, COUNT(*) AS '学生人数' FROM student WHERE sdept = '计算机' GROUP BY sclass ORDER BY COUNT(*) DESC; ``` 24. **使用自然连接求选修了严敏老师的数学分析课程的学生的姓名、课程名称、教师名和成绩** ```sql SELECT s.sname, c.cname, t.tname, sc.grade FROM student s NATURAL JOIN score sc NATURAL JOIN course c NATURAL JOIN teach t WHERE c.cname = '数学分析' AND t.tname = '严敏'; ``` 25. **使用等值连接求选修数学分析课程的学生的姓名、课程名称和成绩** ```sql SELECT s.sname, c.cname, sc.grade FROM student s INNER JOIN score sc ON s.sno = sc.sno INNER JOIN course c ON sc.cno = c.cno WHERE c.cname = '数学分析'; ``` 26. **使用 JOIN 连接求选修了课程的学生的学号和姓名** ```sql SELECT s.sno, s.sname FROM student s INNER JOIN score sc ON s.sno = sc.sno; ``` 27. **使用自身连接查询 求年龄大于'刘东明' 的所有学生的姓名与出生日期** ```sql SELECT s1.sname, s1.sbirth FROM student s1 INNER JOIN student s2 ON s1.sbirth < s2.sbirth WHERE s2.sname = '刘东明'; ``` 28. **使用外部连接查询求未选修任何课程的学生的学号和姓名** ```sql SELECT s.sno, s.sname FROM student s LEFT JOIN score sc ON s.sno = sc.sno WHERE sc.sno IS NULL; ``` 29. **使用不相关子查询 求年龄大于'刘东明' 的所有学生的姓名与出生日期** ```sql SELECT sname, sbirth FROM student WHERE sbirth < (SELECT sbirth FROM student WHERE sname = '刘东明'); ``` 30. **求未选修任何课程的学生的学号和姓名** ```sql SELECT sno, sname FROM student WHERE sno NOT IN (SELECT sno FROM score); ``` 31. **求比数学系中全体学生年龄大的学生的姓名和系** ```sql SELECT sname, sdept FROM student WHERE sbirth < ALL (SELECT sbirth FROM student WHERE sdept = '数学'); ``` 32. **求选修了004课程的学生的姓名和系** ```sql SELECT s.sname, s.sdept FROM student s INNER JOIN score sc ON s.sno = sc.sno WHERE sc.cno = '004'; ``` 33. **求选修了'刘东明'同学选修的全部课程的学生的学号** ```sql SELECT sno FROM score WHERE cno IN (SELECT cno FROM score WHERE sno = (SELECT sno FROM student WHERE sname = '刘东明')) GROUP BY sno HAVING COUNT(cno) = (SELECT COUNT(cno) FROM score WHERE sno = (SELECT sno FROM student WHERE sname = '刘东明')); ``` 34. **求选修了全部课程的学生的学号** ```sql SELECT sno FROM score GROUP BY sno HAVING COUNT(DISTINCT cno) = (SELECT COUNT(*) FROM course); ``` ### 高级部分 1. **创建存储过程 `pro_findname` 对学生姓名进行模糊查找** ```sql DELIMITER // CREATE PROCEDURE pro_findname(IN name_part VARCHAR(20)) BEGIN SELECT * FROM student WHERE sname LIKE CONCAT('%', name_part, '%'); END // DELIMITER ; ``` 2. **设计函数 `count_credit`，根据学号计算该学生的总学分** ```sql DELIMITER // CREATE FUNCTION count_credit(sno_in CHAR(5)) RETURNS INT BEGIN DECLARE total_credit INT DEFAULT 0; SELECT SUM(c.credit) INTO total_credit FROM score s INNER JOIN course c ON s.cno = c.cno WHERE s.sno = sno_in AND s.grade >= 60; RETURN total_credit; END // DELIMITER ; ``` 3. **创建存储过程 `p_count_credit`，调用 `count_credit` 函数更新学生表的总学分值** ```sql DELIMITER // CREATE PROCEDURE p_count_credit() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE current_sno CHAR(5); DECLARE cur CURSOR FOR SELECT sno FROM student; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO current_sno; IF done THEN LEAVE read_loop; END IF; UPDATE student SET scredit = count_credit(current_sno) WHERE sno = current_sno; END LOOP; CLOSE cur; END // DELIMITER ; ``` 4. **创建触发器 `sum_credit`，实现对 `student` 表总学分的计算** ```sql DELIMITER // CREATE TRIGGER sum_credit AFTER INSERT ON score FOR EACH ROW BEGIN DECLARE total_credit INT DEFAULT 0; SELECT SUM(c.credit) INTO total_credit FROM score s INNER JOIN course c ON s.cno = c.cno WHERE s.sno = NEW.sno AND s.grade >= 60; UPDATE student SET scredit = total_credit WHERE sno = NEW.sno; END // DELIMITER ; ``` 5. **创建级联删除触发器 `del_student_score`，当删除 `student` 表中的学生时，也删除 `score` 表中的对应学号的学生成绩记录** ```sql DELIMITER // CREATE TRIGGER del_student_score BEFORE DELETE ON student FOR EACH ROW BEGIN DELETE FROM score WHERE sno = OLD.sno; END // DELIMITER ; ``` 以上是为您创建相关表并实现相关功能的 SQL 语句。如果您有任何进一步的问题或需要更多的帮助，请随时告诉我！