MySQL SQL语句面试经典50题详解，助你轻松应对面试挑战

本文针对MySQL SQL语句面试中的经典50题进行详细解析，旨在帮助求职者更好地掌握MySQL数据库操作技能，提升面试竞争力。通过学习这些问题及其解答，您可以深入了解MySQL的SQL语句应用，为面试做好充分准备。

一、基础查询

1. 查询姓“王”的学名单

   SELECT sname FROM Student WHERE sname LIKE '王%';

2. 查询姓“张”的教师的个数

   SELECT COUNT(*) FROM Teacher WHERE tname LIKE '张%';

二、汇总分析

3. 查询课程编号为“0001”的选修人数

   SELECT COUNT(DISTINCT sid) FROM Score WHERE cid = '0001';

4. 查询各科成绩的平均分

   SELECT cid, AVG(sscore) AS avg_score FROM Score GROUP BY cid;

三、多表查询

5. 查询学生姓名、课程名称和教师姓名

   SELECT Student.sname, Course.cname, Teacher.tname
   FROM Student
   JOIN Score ON Student.sid = Score.sid
   JOIN Course ON Score.cid = Course.cid
   JOIN Teacher ON Course.tid = Teacher.tid;

6. 查询选修了“张三”教授的所有学生姓名

   SELECT Student.sname
   FROM Student
   JOIN Score ON Student.sid = Score.sid
   JOIN Course ON Score.cid = Course.cid
   JOIN Teacher ON Course.tid = Teacher.tid
   WHERE Teacher.tname = '张三';

四、条件查询

7. 查询年龄大于20岁的学生姓名和性别

   SELECT sname, ssex FROM Student WHERE TIMESTAMPDIFF(YEAR, sbirth, CURDATE()) > 20;

8. 查询成绩大于90分的所有学生姓名和课程名称

   SELECT Student.sname, Course.cname
   FROM Student
   JOIN Score ON Student.sid = Score.sid
   JOIN Course ON Score.cid = Course.cid
   WHERE Score.sscore > 90;

五、排序与分页

9. 查询学生姓名按年龄升序排序

   SELECT sname, sbirth FROM Student ORDER BY TIMESTAMPDIFF(YEAR, sbirth, CURDATE());

10. 查询学生姓名按成绩降序排序，每页显示5条记录

    SELECT sname, sscore FROM Student ORDER BY sscore DESC LIMIT 5, 5;

六、子查询

11. 查询所有选修了“张三”教授课程的学生姓名

    SELECT Student.sname
    FROM Student
    WHERE Student.sid IN (SELECT Score.sid FROM Score JOIN Course ON Score.cid = Course.cid JOIN Teacher ON Course.tid = Teacher.tid WHERE Teacher.tname = '张三');

12. 查询成绩排名前三的学生姓名

    SELECT sname FROM Student ORDER BY sscore DESC LIMIT 0, 3;

七、事务与锁

13. 查询学生编号为“1001”的课程编号

    SELECT cid FROM Score WHERE sid = '1001' FOR UPDATE;

14. 查询学生编号为“1001”的课程编号，并更新成绩为95分

    START TRANSACTION;
    SELECT cid FROM Score WHERE sid = '1001' FOR UPDATE;
    UPDATE Score SET sscore = 95 WHERE sid = '1001';
    COMMIT;

八、备份与恢复

15. 创建数据库备份

    mysqldump -u root -p database_name > backup_file.sql

16. 恢复数据库备份

    mysql -u root -p database_name < backup_file.sql

通过以上经典50题的解析，相信您已经对MySQL SQL语句面试有了更深入的了解。在实际面试过程中，灵活运用这些知识点，相信您能够轻松应对挑战。祝您面试顺利！

当前文章不喜欢？试试AI生成哦！