数据库建、表的建立，以及简单查询

-- 创建学生信息数据库
CREATE DATABASE IF NOT EXISTS StudentDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

USE StudentDB;

-- 创建学生信息表
CREATE TABLE IF NOT EXISTS Student_Info (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    gender VARCHAR(10),
    birthday DATETIME,
    admission DATETIME,
    graduate DATETIME,
    major VARCHAR(50),
    address VARCHAR(100),
    phone VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建课程信息表
CREATE TABLE IF NOT EXISTS Course_Info (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    teacher VARCHAR(50),
    credit INT,
    hour INT,
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建成绩信息表
CREATE TABLE IF NOT EXISTS Score_Info (
    score_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    score DECIMAL(5,2),
    FOREIGN KEY (student_id) REFERENCES Student_Info(student_id),
    FOREIGN KEY (course_id) REFERENCES Course_Info(course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建选课信息表
CREATE TABLE IF NOT EXISTS Select_Info (
    select_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Student_Info(student_id),
    FOREIGN KEY (course_id) REFERENCES Course_Info(course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

以上 SQL 语句首先创建了名为 "StudentDB" 的数据库（如果不存在）。接下来分别创建了 Student_Info、Course_Info、Score_Info 和 Select_Info 四张表。

其中，学生信息表 Student_Info 定义了 10 个字段，其中 student_id 为主键。课程信息表 Course_Info 定义了 6 个字段，其中 course_id 为主键。成绩信息表 Score_Info 定义了 4 个字段，其中 score_id 为主键，student_id 和 course_id 均是外键，用于关联到学生信息表和课程信息表中的相应数据。选课信息表 Select_Info 定义了 3 个字段，其中 select_id 为主键，student_id 和 course_id 均是外键，也用于关联到学生信息表和课程信息表中的相应数据。

这些 SQL 语句基本上可以创建出一个完整的学生信息数据库，并建立起四张关联的表。

以下是上述10道查询题目及答案

1、显示每个学生的姓名和平均考试成绩。

SELECT name, AVG(score) AS avg_score 
FROM Student_Info 
LEFT JOIN Score_Info ON Student_Info.student_id=Score_Info.student_id 
GROUP BY Student_Info.student_id;

2、找出所有未通过课程的学生。

SELECT DISTINCT Student_Info.name FROM Student_Info LEFT JOIN Score_Info ON Student_Info.student_id=Score_Info.student_id WHERE Score_Info.score < 60;

3、查找成绩大于等于90分的学生及其所选的课程信息。

SELECT Student_Info.name, Course_Info.course_name, Score_Info.score FROM Student_Info INNER JOIN Score_Info ON Student_Info.student_id=Score_Info.student_id INNER JOIN Course_Info ON Score_Info.course_id=Course_Info.course_id WHERE Score_Info.score >= 90;

4、输出平均学分最高的专业名称。

SELECT Student_Info.major, AVG(Course_Info.credit) AS avg_credit FROM Student_Info LEFT JOIN Select_Info ON Student_Info.student_id=Select_Info.student_id LEFT JOIN Course_Info ON Select_Info.course_id=Course_Info.course_id GROUP BY Student_Info.major ORDER BY avg_credit DESC LIMIT 1;

5、找出每门课程的平均分数，并按照从高到低排列。

SELECT Course_Info.course_name, AVG(Score_Info.score) AS avg_score FROM Course_Info INNER JOIN Score_Info ON Course_Info.course_id=Score_Info.course_id GROUP BY Course_Info.course_id ORDER BY avg_score DESC;

6、列出所有学生的选择课程数目及对应的学生姓名。

SELECT Student_Info.name, COUNT(Select_Info.course_id) AS course_count FROM Student_Info LEFT JOIN Select_Info ON Student_Info.student_id=Select_Info.student_id GROUP BY Student_Info.student_id;

7、找到总成绩排名前三名的学生及其对应的所有成绩记录。

SELECT Student_Info.name, Score_Info.score FROM Student_Info INNER JOIN Score_Info ON Student_Info.student_id=Score_Info.student_id ORDER BY Score_Info.score DESC LIMIT 3;

8、查找教师授课次数最多的三位老师。

SELECT Course_Info.teacher, COUNT(*) AS course_count FROM Course_Info GROUP BY Course_Info.teacher ORDER BY course_count DESC LIMIT 3;

9、统计出各个性别的学生总人数以及占比情况。

SELECT gender, COUNT(*) AS count, COUNT(*) / (SELECT COUNT(*) FROM Student_Info) AS rate FROM Student_Info GROUP BY gender;

10、找出紧接着某个学生入学的同专业学生，并按照入学时间排序输出。

SELECT * FROM Student_Info WHERE major = '某个专业名称' AND admission > (SELECT admission FROM Student_Info WHERE name = '某个学生姓名') ORDER BY admission;