MySQL面试试题与答案

本次试题设计两个表：student、exam

           student表

          exam表

 

一、写一条SQL语句，按学号排序输出数学成绩

SELECT s.sno sno,score
FROM exam e,student s
WHERE e.`subject` = '数学' AND e.name = s.name
GROUP BY s.sno

 

二、写一条SQL语句，按学号排序，输出：学号，姓名，总分，平均分

SELECT sno,s.`name`,SUM(score) ss,AVG(score) gg
FROM exam e,student s
WHERE e.`name` = s.`name`
GROUP BY `name`
ORDER BY ss

 

三、写一条SQL语句，查询出数学考试成绩最高的学生学号和姓名

SELECT sno,exam.name name,score
FROM exam,student
WHERE (exam.`subject` = '数学') AND (exam.name = student.name)
ORDER BY exam.score DESC
LIMIT 0,1;

 

四、写一条SQL语句，查询出每门课都大于80分的学生姓名

方法一：ALL()函数

思路：查找出每个人所有科目是否都大于80

SELECT DISTINCT NAME 
FROM exam a
WHERE 80 < ALL(SELECT score 
               FROM exam e
               WHERE e.name = a.name );

 

方法二：not exists 字段

思路：排除掉存在学科分数低于80的学生，剩下的就是所有学科分数都大于80的学生

SELECT DISTINCT NAME
FROM exam a
WHERE NOT EXISTS (SELECT name
                  FROM exam e 
                  WHERE e.name = a.name AND e.score < 80 );

 

方法三：not in 字段

思路：同上

SELECT DISTINCT NAME 
FROM exam 
WHERE NAME NOT IN (SELECT  NAME 
                   FROM exam
                   WHERE score<=80)

 

方法四：having 字段

思路：过滤出学科中最低分数大于80的学生

SELECT name
FROM exam
GROUP BY name
HAVING min(score) > 80

 

五、张三退学后，请写相关的SQL语句，注意严谨

DELETE
FROM exam
WHERE name = '张三';
DELETE
FROM student
WHERE name = '张三';

 

DELETE exam,student
FROM exam
LEFT JOIN student
ON exam.name = student.name
WHERE exam.name = '张三' ;

 

六、你会对该表哪些字段做索引

id - 唯一索引

七、这两个表的设计，有哪些可以改进的地方

student表中sno字段可以改成‘studentID’，更语义化，

exam表中取消name字段及该列数据，改用student中sno学号字段代替，更易于检索