在數(shù)據(jù)庫增刪改查純基礎之上稍作提升的練習，我也是一邊給自己出題一邊求解。

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

設計了3張表，學生、課程、分數(shù)，其中數(shù)據(jù)如圖：

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分組，找到其最大分數(shù)也低于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. 查詢不及格課程最多的學生信息

   先查詢不及格數(shù)最多的學生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. 將課程名是‘數(shù)學’，分數(shù)不及格但大于55分的同學分數(shù)設置為60

   獲取到數(shù)學的課程id，查找到所有分數(shù)在55到60之間的score

   SELECT score from score WHERE class_id=(SELECT id FROM class WHERE class_name='數(shù)學') HAVING score<60 and score>55;
   

   再更新對應的分數(shù)

   注意，mysql這樣執(zhí)行會報錯：

   UPDATE score set score=60 WHERE score in (SELECT score from score WHERE class_id=(SELECT id FROM class WHERE class_name='數(shù)學') HAVING score<60 and score>55);

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

   在同一語句中不能先select出同一表中的某些值，再update這個表

   需要將select出的結果再通過中間表select一遍，來規(guī)避錯誤

   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='數(shù)學') 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. 獲取數(shù)學成績最高的學生信息查

   先查詢成績最高的學生ID

   SELECT student_id FROM score where score=(SELECT max(score) FROM score where class_id=(SELECT id FROM class where class_name='數(shù)學'));

   再嵌套子查詢

   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='數(shù)學')));

????連表查詢，內(nèi)連接、左連接，結果都是一樣的

????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='數(shù)學')));