【MySQL面试题解析】
1. **查询每个学生的平均成绩和姓名**
```sql
SELECT s.id, s.name, AVG(sc.score) AS average_score
FROM Student s
JOIN Sc sc ON s.id = sc.sid
GROUP BY s.id, s.name;
```
这个查询通过JOIN操作将学生表St与分数表Sc连接，然后按学生ID和姓名分组，计算每个学生的平均分数。
2. **列出平均成绩不及格的学生姓名和平均成绩**
```sql
SELECT s.id, s.name, AVG(sc.score) AS average_score
FROM Student s
JOIN Sc sc ON s.id = sc.sid
GROUP BY s.id, s.name
HAVING AVG(sc.score) < 60;
```
在这个查询中，我们添加了HAVING子句来筛选平均成绩低于60分的学生。
3. **列出有二门以上(含两门)不及格课程的学生姓名及平均成绩**
```sql
SELECT s.id, s.name, AVG(sc.score) AS average_score
FROM Student s
JOIN Sc sc ON s.id = sc.sid
WHERE sc.score < 60
GROUP BY s.id, s.name
HAVING COUNT(DISTINCT sc.kid) >= 2;
```
这个查询首先找出所有不及格的课程，然后按学生分组，使用HAVING子句检查不及格课程数量是否大于等于2。
4. **查出重名的名字**
```sql
SELECT name
FROM user
GROUP BY name
HAVING COUNT(*) > 1;
```
这个查询通过GROUP BY name将名字分组，然后使用HAVING子句找出出现次数超过一次的名字。
5. **查询“高一语文组”的所有女教师发表的文章信息**
```sql
SELECT d.dept_name AS '部门名称', t.name AS '教师姓名', t.gender, a.title AS '文章标题', a.journal AS '发表杂志', a.publish_date AS '发表日期'
FROM Article a
JOIN Teacher t ON a.teacher_id = t.id
JOIN Department d ON t.dept_id = d.id
WHERE t.gender = '女' AND d.dept_name = '高一语文组'
ORDER BY a.publish_date DESC;
```
这个查询通过JOIN操作将文章表、教师表和部门表连接，然后根据指定条件筛选并按发表日期降序排列。
6. **中小学管理信息系统的ER图设计**
- 学校 (School) - 学段 (GradeLevel) - 年级 (Grade) - 班级 (Class) - 学生 (Student)
- 学校 (School) - 教师 (Teacher) - 课程 (Course)
- 教师 (Teacher) - 课程 (Course) - 班级 (Class) - 学生 (Student) - 成绩 (Score)
7. **教务系统模块规划**
- 学院管理模块: 包括学院信息表（ID, 名称）
- 系管理模块: 包括系信息表（ID, 学院ID, 名称）
- 专业管理模块: 包括专业信息表（ID, 系ID, 名称）
- 班级管理模块: 包括班级信息表（ID, 班级名, 专业ID）
- 学生管理模块: 包括学生信息表（ID, 姓名, 生日, 性别, 家庭住址, 身份证号, 专业ID, 班级ID）
- 教师管理模块: 包括教师信息表（ID, 姓名, 性别）
- 课程管理模块: 包括课程信息表（ID, 名称, 专业ID, 状态）
- 成绩管理模块: 包括成绩信息表（ID, 记录日期, 考试时间, 分数, 学生ID, 课程ID, 备注）
8. **查询身份证号和申请日期**
- 查询身份证号为440401430103082的申请日期:
```sql
SELECT g_applydate
FROM g_cardapply
WHERE g_idcard = '440401430103082';
```
- 查询同一身份证号有两条以上记录的身份证号及记录个数:
```sql
SELECT g_idcard, COUNT(*)
FROM g_cardapply
GROUP BY g_idcard
HAVING COUNT(*) > 1;
```
9. **改变记录状态和删除记录**
- 修改身份证号为440401430103082的记录状态:
```sql
UPDATE g_cardapply, g_cardapplydetail
SET g_state = '0'
WHERE g_cardapply.g_idcard = '440401430103082'
AND g_cardapply.g_applyno = g_cardapplydetail.g_applyno;
```
- 删除所有姓李的记录:
```sql
DELETE FROM g_cardapplydetail
WHERE g_name LIKE '李%';
```
10. **LEFT JOIN 和 RIGHT JOIN 的区别**
- LEFT JOIN（左连接）返回所有左侧表的记录，即使右侧表没有匹配的记录，右侧表无匹配时填充NULL。
- RIGHT JOIN（右连接）返回所有右侧表的记录，即使左侧表没有匹配的记录，左侧表无匹配时填充NULL。
11. **SQL语句实现合并列显示**
```sql
SELECT s.name, MAX(CASE WHEN k.name = '语文' THEN sc.score ELSE NULL END) AS '语文', MAX(CASE WHEN k.name = '数学' THEN sc.score ELSE NULL END) AS '数学'
FROM Student s
JOIN Sc sc ON s.id = sc.sid
JOIN K k ON sc.kid = k.id
GROUP BY s.id, s.name;
```
这个查询使用CASE语句按科目分组并取最大值（即该科目的分数），以合并列的方式显示每个学生的语文和数学成绩。
12. **学生表建立**
```sql
CREATE TABLE Student (
S# INT PRIMARY KEY,
Sname VARCHAR(100),
Sage INT,
Ssex CHAR(1)
);
```
创建学生表，包括学号（S#）、姓名（Sname）、年龄（Sage）和性别（Ssex）。主键为学号（S#）。
