数据库笔试练习题

在数据库增删改查纯基础之上稍作提升的练习，我也是一边给自己出题一边求解。

有更好的解法欢迎大佬指教！

设计了3张表，学生、课程、分数，其中数据如图：

1. 获取名字包含‘ma’的学生信息

   SELECT * FROM student WHERE NAME LIKE '%ma%';
   

2. 查询‘老马’教的课程所有成绩

   SELECT * FROM score where class_id=(SELECT id FROM class where teacher_name= '老马');

3. 查询每门课的最低和最高分

   SELECT class_id,max(score),min(score) from score GROUP BY class_id;
   

4. 查询年纪最大的学生信息

   SELECT * from student where birthday=(SELECT MIN(birthday) FROM student);

5. 查询所有课程成绩都不及格的学生信息

   筛选成绩，按学生id分组，找到其最大分数也低于60分的id

   SELECT student_id FROM score GROUP BY student_id HAVING max(score)<60;

   再通过student_id嵌套子查询

   SELECT * FROM student where id=(SELECT student_id FROM score GROUP BY student_id HAVING max(score)<60);
   

6. 查询不及格课程最多的学生信息

   先查询不及格数最多的学生id

   如下直接查询会报错

   SELECT MAX(c) from (SELECT student_id,count(*) AS c from score WHERE score<60 group by student_id);

   1248 - Every derived table must have its own alias

   子查询先生成了一张新的临时表，如果这个临时表没有命名，就会导致 1248 错误。

   改成这样既可

   SELECT MAX(c) from (SELECT student_id,count(*) AS c from score WHERE score<60 group by student_id)AS t;

   通过获取到的最大值，筛选出id

   SELECT student_id FROM (SELECT student_id,count(*) as cou from score WHERE score<60 group by student_id)AS t1 where cou=(SELECT MAX(c) from (SELECT student_id,count(*) AS c from score WHERE score<60 group by student_id)AS t2);

   获取到id后再嵌套查询既可

   SELECT * FROM student WHERE id IN (SELECT student_id FROM (SELECT student_id,count(*) as cou from score WHERE score<60 group by student_id)AS t1 where cou=(SELECT MAX(c) from (SELECT student_id,count(*) AS c from score WHERE score<60 group by student_id)AS t2));

   得到2个并列挂2科的学生信息

1. 查询和mary同性别的其他同学的信息

   先筛选出和mary性别相同的同学信息，再加一个条件排除mary自己

   SELECT * FROM student WHERE gender=(SELECT gender FROM student WHERE name='mary') and name<>'mary';

2. 获取各科成绩最高值/平均值

   SELECT class_id,max(score),AVG(score) FROM score GROUP BY class_id ORDER BY class_id;
   

3. 将课程名是‘数学’，分数不及格但大于55分的同学分数设置为60

   获取到数学的课程id，查找到所有分数在55到60之间的score

   SELECT score from score WHERE class_id=(SELECT id FROM class WHERE class_name='数学') HAVING score<60 and score>55;
   

   再更新对应的分数

   注意，mysql这样执行会报错：

   UPDATE score set score=60 WHERE score in (SELECT score from score WHERE class_id=(SELECT id FROM class WHERE class_name='数学') HAVING score<60 and score>55);

   You can't specify target table 'score' for update in FROM clause

   在同一语句中不能先select出同一表中的某些值，再update这个表

   需要将select出的结果再通过中间表select一遍，来规避错误

   UPDATE score set score=60 WHERE score in(SELECT a.score FROM (SELECT score from score WHERE class_id=(SELECT id FROM class WHERE class_name='数学') HAVING score<60 and score>55)AS a);
   

4. 查询平均分大于80分的学生名字，学号和平均分

   SELECT st.id,st.`name`,AVG(sc.score) FROM student as st JOIN score AS sc ON st.id=sc.student_id GROUP BY st.id HAVING AVG(sc.score)>80;

5. 获取数学成绩最高的学生信息查

   先查询成绩最高的学生ID

   SELECT student_id FROM score where score=(SELECT max(score) FROM score where class_id=(SELECT id FROM class where class_name='数学'));

   再嵌套子查询

   SELECT * FROM student where student.id=(SELECT student_id FROM score where score=(SELECT max(score) FROM score where class_id=(SELECT id FROM class where class_name='数学')));

    连表查询，内连接、左连接，结果都是一样的

    SELECT * FROM student INNER JOIN score on student.id=score.student_id where         student.id=(SELECT student_id FROM score where score=(SELECT max(score) FROM     score where class_id=(SELECT id FROM class where class_name='数学')));