1.查询所有学生的stu_name，cour_num，degree列：
其中stu_name字段来自于student表；cour_num和degree字段来自于score表；
mysql> select * from student; +---------+----------+---------+---------------------+-------+ | stu_num | stu_name | stu_sex | stu_birth | class | +---------+----------+---------+---------------------+-------+ | 11215 | JiaWei | F | 1993-07-28 00:00:00 | 112 | | 11328 | DingQi | F | 1994-08-15 00:00:00 | 113 | | 11422 | Baker | F | 1999-09-22 00:00:00 | 114 | | 11423 | Bob | M | 1998-04-25 00:00:00 | 114 | | 11424 | LinJie | M | 1994-06-12 00:00:00 | 114 | | 11425 | XieZhou | M | 1995-03-11 00:00:00 | 114 | | 11426 | MingHui | F | 1998-08-09 00:00:00 | 114 | | 11427 | NanNan | F | 1995-10-20 00:00:00 | 114 | +---------+----------+---------+---------------------+-------+
mysql> select * from score; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11423 | 1-245 | 84 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11425 | 2-271 | 89 | | 11426 | 1-245 | 61 | | 11426 | 2-271 | 82 | | 11427 | 1-245 | 78 | +---------+----------+--------+
如何汇总？
mysql> select * from score; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11423 | 1-245 | 84 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11425 | 2-271 | 89 | | 11426 | 1-245 | 61 | | 11426 | 2-271 | 82 | | 11427 | 1-245 | 78 | +---------+----------+--------+
mysql> select stu_name,stu_num from student; +----------+---------+ | stu_name | stu_num | +----------+---------+ | JiaWei | 11215 | | DingQi | 11328 | | Baker | 11422 | | Bob | 11423 | | LinJie | 11424 | | XieZhou | 11425 | | MingHui | 11426 | | NanNan | 11427 | +----------+---------+
mysql> select stu_name,cour_num,degree from student,score -> where student.stu_num == score.stu_num; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== score.stu_num' at line 2 mysql> select stu_name,cour_num,degree from student,score -> where student.stu_num = score.stu_num; +----------+----------+--------+ | stu_name | cour_num | degree | +----------+----------+--------+ | Baker | 3-105 | 92 | | Bob | 1-245 | 84 | | Bob | 2-271 | 75 | | LinJie | 4-321 | 75 | | XieZhou | 2-271 | 89 | | MingHui | 1-245 | 61 | | MingHui | 2-271 | 82 | | NanNan | 1-245 | 78 | +----------+----------+--------+
这样就对应上了。双等号有错误，改成一个等号。
2.查询所有学生的stu_num，cour_name，degree列：
cour_name在course表中，stu_num与degree在score表中：
mysql> select stu_num,cour_name,degree from score,course -> where score.cour_num=course.cour_num; +---------+-----------+--------+ | stu_num | cour_name | degree | +---------+-----------+--------+ | 11423 | Math | 84 | | 11426 | Math | 61 | | 11427 | Math | 78 | | 11423 | Circuit | 75 | | 11425 | Circuit | 89 | | 11426 | Circuit | 82 | | 11422 | OS | 92 | | 11424 | Bio | 75 | +---------+-----------+--------+
先分开查，然后再汇总。
3.查询所有学生的stu_name,cour_name,degree列：
stu_name在表student中，cour_name在course表中，degree在score中。
mysql> select stu_name,cour_name,degree from student,course,score -> where student.stu_num=score.stu_num and course.cour_num=score.cour_num; +----------+-----------+--------+ | stu_name | cour_name | degree | +----------+-----------+--------+ | Bob | Math | 84 | | MingHui | Math | 61 | | NanNan | Math | 78 | | Bob | Circuit | 75 | | XieZhou | Circuit | 89 | | MingHui | Circuit | 82 | | Baker | OS | 92 | | LinJie | Bio | 75 | +----------+-----------+--------+
通过表中某些共有的字段进行连接。
4.查询114班学生每门课的平均分：
mysql> select cour_num,avg(degree) -> from score -> where stu_num in (select stu_num from student where class='114') -> group by cour_num; +----------+-------------+ | cour_num | avg(degree) | +----------+-------------+ | 3-105 | 92.0000 | | 1-245 | 74.3333 | | 2-271 | 82.0000 | | 4-321 | 75.0000 | +----------+-------------+
5.查询课程1-245的成绩高于11426同学课程1-245成绩的所有同学的记录：
查出11426号同学1-245课程的成绩：
mysql> select degree from score where stu_num=11426 and cour_num='1-245'; +--------+ | degree | +--------+ | 61 | +--------+
查询成绩大于这位同学的记录：
mysql> select * from score where degree>(select degree from score where stu_num=11426 and cour_num='1-245'); +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11423 | 1-245 | 84 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11425 | 2-271 | 89 | | 11426 | 2-271 | 82 | | 11427 | 1-245 | 78 | +---------+----------+--------+
筛选课程号：
mysql> select * from score where degree>(select degree from score where stu_num=11426 and cour_num='1-245') and cour_num='1-245'; +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11423 | 1-245 | 84 | | 11427 | 1-245 | 78 | +---------+----------+--------+
6.查询成绩高于11426同学课程1-245成绩的所有同学的记录：
mysql> select * from score where degree>(select degree from score where stu_num=11426 and cour_num='1-245'); +---------+----------+--------+ | stu_num | cour_num | degree | +---------+----------+--------+ | 11422 | 3-105 | 92 | | 11423 | 1-245 | 84 | | 11423 | 2-271 | 75 | | 11424 | 4-321 | 75 | | 11425 | 2-271 | 89 | | 11426 | 2-271 | 82 | | 11427 | 1-245 | 78 | +---------+----------+--------+
子查询就按部就班的依次查询，然后进行嵌套。