MySQL系列（六）多表连接和子查询

（1）查询选修课程号为c06108的学生的学号、姓名和期末成绩；

select student.studentno,sname,final from student,score 
where student.studentno = score.studentno and score.courseno = 'c06108';

（2）利用左外连接方式查询学生的学号、姓名、平时成绩和期末成绩；

select student.studentno,sname,daily,final from student 
left join score on student.studentno = score.studentno;

（3）利用右外连接方式查询教师的排课情况；

select teacher.teacherno,tname,major,courseno from teacher 
right join teach_course on teacher.teacherno = teach_course.teacherno;

（4）查询19级学生的学号、姓名、课程名、期末成绩和学分；

select student.studentno,sname,cname,final,round(period/16,1) as '学分' from score 
join student on student.studentno = score.studentno 
join course on score.courseno = course.courseno 
where substring(student.studentno,1,2) = '19';

（5）查询期末成绩高于90分、总评成绩高于85分的学生的学号、课程号和总评成绩；

select score.studentno as '学号',score.courseno as '课程号',score.final * 0.8 + score.daily * 0.2 as '总评' 
from score where score.final > 90 and score.final * 0.8 + score.daily * 0.2 > 85;

（6）查询期末成绩比选修该课程平均成绩低的学生的学号、课程号和期末成绩；

select studentno,courseno,final from score as a 
where final < (select avg(final) from score as b 
where a.courseno = b.courseno group by courseno);

（7）获取期末成绩中含有高于90分的学生的学号、姓名、电话和Email；

select studentno,sname,phone,Email from student 
where studentno in (select studentno from score where final > 95);

（8）查找score表中所有比c05103课程期末成绩都高的学生学号、姓名、电话和期末成绩；

select student.studentno,sname,phone,final from score 
inner join student on score.studentno = student.studentno 
where final > all (select final from score where courseno = 'c05103');

（9）将student表中1999年以后出生的学生记录添加到stud表中;

insert into info1.stud (select * from student where birthdate >= '1999-12-31');

（10）查询student表中学生电话号码尾数为8的学生的学号、姓名、电话和Email。

select studentno,sname,phone,Email from student where phone regexp '8$';