提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

零、题目要求

请完成以下SQL代码编写
1.创建coursesSelection数据库（3分）。
2.创建selection表（3分）
3.在学生表插入一条记录（学号【1008】，姓名【李林】，性别【男】，专业【计算机应用技术】，出
生年月【2003-10-18】，地址【柳州市鱼峰区官塘大道】，联系方式【0772-3156666】，总分【使用默
认值】）（3分）。
4.将课程号为“C008”的学时减少8节，学分增加1分（3分）。
5.删除专业中以“培训”结尾的所有学生记录（3分）。
6.查询所有学生的姓名，性别，出生日期，成绩（3分）。
7.查询总学分在80-180之间(包含边界)的学号、姓名、专业及总学分（3分）。
8.查询所有2003年出生的女生信息（3分）。
9.查询学生中姓“张”但不包含“学”的学生，并按出生日期降序显示（3分）。
10.查询所有各门课程的平均成绩、最高成绩、最低成绩（3分）。
11.请统计不同专业各有多少学生（3分）。
12.请计算不同课程的总学分（3分）。
13.查询所有“软件技术”专业，成绩大于85分（包含85分）的学生信息（3分）。
14.查询成绩最好的前8门课程名及成绩（3分）。
15.请查询“张佳宁”、“李翔”的课程号为’8080’和’8001’的成绩（3分）。
16.查询所有年龄小于平均年龄的学生姓名、性别，专业（3分）。
17.查询还未有选课成绩的“计算机应用技术”专业的学生信息（3分）。
18.查询每个姓“刘”学生总成绩大于800的学号及总成绩（3分）。
19.请使用右连接方式查询所有课程的课程名、学号和成绩（3分）。
20.现有new_student表结构为（studentId【学号】，name【姓名】，totalScore【成绩】）,将
所有学生总成绩进行统计后插入new_student表中（3分）。
21.创建一个v_myview的视图，视图查询地址不为空的所有学生信息（3分）。
22.参照选课数据库的三个表结构编写一个自定义函数fn_myfunc，实现如下功能
要求能根据学生姓名和课程名称，查询成绩

一、创建数据库与表

1.1 创建coursesSelection数据库

-- 1. 创建coursesSelection数据库
CREATE DATABASE coursesSelection;

1.2 创建selection表及相关表结构

-- 3. 创建selection表
USE coursesSelection;
CREATE TABLE selection (
    studentid char(9) NOT NULL,
    cNo char(9) NOT NULL,
    score float,
    PRIMARY KEY (studentid, cNo),
    FOREIGN KEY (studentid) REFERENCES student(studentid)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (cNo) REFERENCES course(cNo)
        ON DELETE CASCADE ON UPDATE CASCADE
);
-- 创建student表
CREATE TABLE student (
    studentid char(9) NOT NULL,
    name char(9) DEFAULT '' NOT NULL,
    gender bit(1) DEFAULT b'0',
    professional varchar(50),
    birth date,
    address varchar(100),
    contact char(12) DEFAULT '' NOT NULL,
    total float DEFAULT '0.00' NOT NULL,
    PRIMARY KEY (studentid)
);
-- 创建course表
CREATE TABLE course (
    cNo char(9) NOT NULL,
    cname varchar(50) NOT NULL,
    hours int,
    creditPoint float,
    remark text,
    PRIMARY KEY (cNo)
);

二、数据插入与更新

2.1 在学生表插入一条记录

-- 3.在学生表插入一条记录（学号【1008】，姓名【李林】，性别【男】，专业【计算机应用技术】，出
生年月【2003-10-18】，地址【柳州市鱼峰区官塘大道】，联系方式【0772-3156666】，总分【使用默认值】
INSERT INTO student VALUES ('1008', '李林', b'1', '计算机应用技术', '2003-10-18', '柳州市鱼峰区官塘大道', '0772-3156666', 0.00);

2.2 修改课程号为“C008”的学时和学分

-- 4.将课程号为“C008”的学时减少8节，学分增加1分
UPDATE course SET hours = hours - 8, creditPoint = creditPoint + 1 WHERE cNo = 'C008';

三、数据删除

3.1 删除专业中以“培训”结尾的学生记录

-- 5.删除专业中以“培训”结尾的所有学生记录
DELETE FROM student WHERE professional LIKE '%培训';

四、数据查询

4.1 查询所有学生的基本信息

--  6.查询所有学生的姓名，性别，出生日期，成绩
SELECT s.name, s.gender, s.birth, s.total FROM student AS s;

4.2 查询总学分在一定范围内的学生信息

--  7.查询总学分在80-180之间(包含边界)的学号、姓名、专业及总学分
SELECT s.studentid, s.name, s.professional, s.total FROM student AS s WHERE s.total BETWEEN 80 AND 180;

4.3 查询特定年份出生的女生信息

--  8.查询所有2003年出生的女生信息
SELECT * FROM student WHERE birth = '2003-01-01' AND gender = b'1';

4.4 查询姓“张”但不包含“学”的学生信息

--  9.查询学生中姓“张”但不包含“学”的学生，并按出生日期降序显示
SELECT * FROM student WHERE name LIKE '张%' AND name NOT LIKE '%学%' ORDER BY birth DESC;

4.5 查询各门课程的平均、最高、最低成绩

-- 10.查询所有各门课程的平均成绩、最高成绩、最低成绩
SELECT c.cname, AVG(s.score), MAX(s.score), MIN(s.score) 
FROM course AS c JOIN selection AS s ON c.cNo = s.cNo GROUP BY c.cNo;

4.6 统计不同专业的学生人数

-- 11.请统计不同专业各有多少学生
SELECT professional, COUNT(*) FROM student GROUP BY professional;

4.7 计算不同课程的总学分

-- 12.请计算不同课程的总学分
SELECT c.cname, SUM(s.creditPoint) FROM course AS c JOIN selection AS s ON c.cNo = s.cNo GROUP BY c.cNo;

4.8 查询特定专业且成绩大于85分的学生信息

-- 13.查询所有“软件技术”专业，成绩大于85分（包含85分）的学生信息
SELECT a.*,b.score FROM student a JOIN SELECTION b ON a.studentId = b.studentId WHERE a.professional='软件技术' AND b.score>=85;

4.9 查询成绩最好的前8门课程

-- 14.查询成绩最好的前8门课程名及成绩
SELECT cNo, MAX(score) AS max_score
FROM selection
GROUP BY cNo
ORDER BY max_score DESC
LIMIT 8;

4.10 查询特定学生特定课程的成绩

-- 15.请查询“张佳宁”、“李翔”的课程号为'8080'和'8001'的成绩
USE coursesSelection;
SELECT student.name, selection.score
FROM student
JOIN selection ON student.studentid = selection.studentid
WHERE (name = '张佳宁' OR name = '李翔')
 AND (cNo = '8080'OR cNo = '8001');

4.11 查询年龄小于平均年龄的学生信息

-- 16. 查询所有年龄小于平均年龄的学生姓名、性别、专业
SELECT s.name, s.gender, s.professional FROM student AS s WHERE s.birth > (SELECT AVG(birth) FROM student);

4.12 查询还未选课的特定专业学生信息

-- 17. 查询还未有选课成绩的“计算机应用技术”专业的学生信息
SELECT * FROM student WHERE professional = '计算机应用技术' AND studentid NOT IN (SELECT studentid FROM selection);

4.13 查询姓“刘”且总成绩大于800的学生信息

-- 18. 查询每个姓“刘”的学生总成绩大于800的学号及总成绩
SELECT studentid, totalScore FROM new_student WHERE name LIKE '刘%';

4.14 使用右连接查询课程信息

-- 19. 使用右连接方式查询所有课程的课程名、学号及成绩
SELECT c.cname, s.studentid, s.score FROM course AS c RIGHT JOIN selection AS s ON c.cNo = s.cNo;

五、数据迁移与视图创建

5.1 将学生总成绩迁移至new_student表

-- 20. 将new_student表结构为（studentid【学号】，name【姓名】，totalScore【成绩】），将所有学生总成绩进行统计后插入new_student表中
INSERT INTO new_student SELECT studentid, name, total FROM student;

5.2 创建地址不为空的学生信息视图

-- 21. 创建一个myview的视图，视图查询地址不为空的所有学生信息
CREATE VIEW myview AS SELECT * FROM student WHERE address IS NOT NULL;

六、自定义函数编写

6.1 编写根据学生姓名和课程名称查询成绩的自定义函数

-- 22. 参照选课数据库的三个表结构编写一个自定义函数fn_myfunc，实现如下功能：能根据学生姓名和课程名称，查询成绩
DELIMITER 
CREATE FUNCTION fn_myfunc(name VARCHAR(50), cname VARCHAR(50)) RETURNS FLOAT
BEGIN//
    DECLARE score FLOAT;
    SELECT s.score INTO score FROM selection AS s JOIN student AS st ON s.studentid = st.studentid JOIN course AS co ON s.cNo = co.cNo WHERE st.name = name AND co.cname = cname;
    RETURN score;
END//
DELIMITER ;

七、总结

通过以上步骤，我们完成了从创建数据库、表结构到数据插入、更新、删除、查询、迁移及视图、自定义函数的整个SQL操作流程。这些操作涵盖了SQL数据库管理的基本方面，为实际的数据管理提供了全面的解决方案。