导入表格数据可以新建一个数据库后用命令直接导入，mysqldump -u root -d db1 < db2.sql -p #db2.sql数据写入db1中（db1提前需要建立好）。或者看着练习题博客中的表格建立后，插入数据，然后对着参考1中实际插入的数据使用update table set 列名='XXX' from XXX;来改一下，数据量大就使用可视化操作来编辑即可，主要是练习要会写。
#2 查询“生物”课程比“物理”课程成绩高的所有学生的学号 # 第一步应当查询上过生物课程的学生分数 #select score.sid,score.student_id,course.cname,score.number from score left join course on score.course_id = course.cid where course.cname='生物'; # 第二步查物理课程成绩分数 #select score.sid,score.student_id,course.cname,score.number from score left join course on score.course_id = course.cid where course.cname='物理'; # 第三步，将一、二步联合起来，进行比较分数,根据学生id连接，这样就相当于上过生物、物理课程的学生 select A.student_id from (select score.sid,score.student_id,course.cname,score.number from score left join course on score.course_id = course.cid where course.cname='生物') as A inner join (select score.sid,score.student_id,course.cname,score.number from score left join course on score.course_id = course.cid where course.cname='物理') as B on A.student_id = B.student_id where A.number > B.number; #3 查询平均成绩大于60分的同学的学号和平均成绩 首先分组 利用聚合函数avg和having select student_id,avg(number) from score group by student_id having avg(number) > 60; #3 问题3扩充，加一个联表，看学生名字，会用一个临时表B和as来表示列名 select B.student_id,student.sname,B.平均成绩 from (select student_id,avg(number) as 平均成绩 from score group by student_id having avg(number) > 60) as B left join student on B.student_id = student.sid; #4 查询所有同学的学号、姓名、选课数、总成绩 两种方法查询 核心一样 第一种查询和问题3扩充类似 第二种查询老师写的简单 都要经过联表、分组、聚合 #第一种 select student.sid,student.sname,B.选课数,B.总成绩 from (select student_id,count(student_id) as 选课数,sum(number) as 总成绩 from score group by student_id) as B left join student on student.sid = B.student_id; #第二种 select score.student_id,student.sname,count(student_id),sum(number) from score left join student on score.student_id = student.sid group by score.student_id; #5 查询姓李老师的个数，模糊查询 这个好理解，%代表任意 _则是一位 select count(tname) as 姓李老师个数 from teacher where tname like('李%'); #6 查询没学过"李平"老师课的同学的学号、姓名 #思路 找学过李平老师的所有学生，进行分组后关联学生表查询 #看视频比看文字好 select student.sid,student.sname from student where sid not in (#利用学生表来反向选择没有上过李平老师课程的 select #这里筛选的是的是上过李平老师课程的 score.student_id from score where course_id in ( select course.cid #这里筛选的是李平老师所带课程 from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师" )group by student_id ); #7 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名 select A.sid1,A.sname1 from#第三个将查出的两个表整合起来比较筛选 (select student.sid as sid1,student.sname as sname1,score.course_id as course_id1 from student left join score on student.sid = score.student_id where score.course_id = 1) as A #第一个先查出学过1课程的学生 inner join #第二个查出学过2课程的学生 (select student.sid as sid2,student.sname as sname2,score.course_id as course_id2 from student left join score on student.sid = score.student_id where score.course_id = 2) as B on A.sid1 = B.sid2 where A.course_id1 = 1 and B.course_id2 = 2; # 8查询学过“张磊老师“所教的所有课的同学的学号、姓名； select student.sid,student.sname from student left join score on student.sid = score.student_id where score.course_id in(select teacher.tid from course left join teacher on teacher.tid = course.teacher_id where teacher.tname = '张磊老师'); #9 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名 # 这题和第7题思路一样 select A.sid1,A.sname1 from#第三个将查出的两个表整合起来比较筛选 (select student.sid as sid1,student.sname as sname1,score.number as number1 from student left join score on student.sid = score.student_id where score.course_id = 1) as A #第一个先查出学过1课程的学生 inner join #第二个查出学过2课程的学生 (select student.sid as sid2,student.sname as sname2,score.number as number2 from student left join score on student.sid = score.student_id where score.course_id = 2) as B on A.sid1 = B.sid2 where A.number1 > B.number2; # 10查询有课程成绩小于60分的同学的学号、姓名; 这题我自己要注意分组和where顺序 select student.sid,student.sname from student left join score on score.student_id = student.sid where number < 60 group by sid; #24 查询男生、女生人数，首先分组,gender中男女就各自分为一组，然后聚合函数统计个数 select gender,count(gender) from student group by gender;
