MySQL多表查询一
示例一:
查询"1234"课程比"1235"课程成绩高的学生的信息及课程分数
分析:学生信息在student表,成绩信息在student_course表,同时要比较student_course表中1234课程和1235课程成绩的大小,还要显示其成绩
所以,要三表连接查询
mysql> select a.* , b.* ,c.*
from student a , student_course b , student_course c;
加上查询条件
mysql> select a.* , b.score as 1234score ,c.score 1235score
from student a , student_course b , student_course c
where a.sid = b.sid and a.sid = c.sid and b.cid = '1234' and c.cid = '1235' and c.score > b.score;
+-----+-------+---------------------+------+-----------+-----------+
| sid | sname | sage | ssex | 1234score | 1235score |
+-----+-------+---------------------+------+-----------+-----------+
| 01 | 赵一 | 1990-01-01 00:00:00 | 男 | 10 | 90 |
| 02 | 赵二 | 1990-01-01 00:00:00 | 男 | 70 | 90 |
+-----+-------+---------------------+------+-----------+-----------+
2 rows in set
最后就是这个结果
示例二:
student a , student_course b两表连接查询
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
mysql> select a.sid 学生编号, a.sname 学生姓名, count(b.cid) 选课总数, sum(score) 所有课程的总成绩
from student a , student_course b
where a.sid = b.sid
group by a.sid,a.sname
order by a.sid;
+----------+----------+----------+------------------+
| 学生编号 | 学生姓名 | 选课总数 | 所有课程的总成绩 |
+----------+----------+----------+------------------+
| 01 | 赵一 | 2 | 100 |
| 02 | 赵二 | 2 | 160 |
| 03 | 赵三 | 2 | 146 |
+----------+----------+----------+------------------+
3 rows in set
mysql>
示例三:
两表的左外连接
查询所有(包括有成绩和无成绩)的SQL
mysql> select a.sid 学生编号, a.sname 学生姓名, count(b.cid) 选课总数, sum(score) 所有课程的总成绩
from Student a left join student_course b
on a.sid = b.sid
group by a.sid,a.sname
order by a.sid;
+----------+----------+----------+------------------+
| 学生编号 | 学生姓名 | 选课总数 | 所有课程的总成绩 |
+----------+----------+----------+------------------+
| 01 | 赵一 | 2 | 100 |
| 02 | 赵二 | 2 | 160 |
| 03 | 赵三 | 2 | 146 |
| 04 | 赵四 | 0 | NULL |
+----------+----------+----------+------------------+
4 rows in set
示例四:
四表连接查询
查询上xx老师课的学生的信息
mysql> select distinct student.* from student , student_course , course , teacher
where student.sid = student_course.sid and student_course.cid = course.cid and course.tid = teacher.tid and teacher.tname = '李四'
order by student.sid;
+-----+-------+---------------------+------+
| sid | sname | sage | ssex |
+-----+-------+---------------------+------+
| 01 | 赵一 | 1990-01-01 00:00:00 | 男 |
| 02 | 赵二 | 1990-01-01 00:00:00 | 男 |
+-----+-------+---------------------+------+
2 rows in set
示例五:
和示例四一样,这个用join来实现
mysql> select distinct a.*
from student a join student_course b on a.sid = b.sid
join course c on b.cid = c.cid join teacher d on c.tid = d.tid
where d.tname = '李四'
order by a.sid;
+-----+-------+---------------------+------+
| sid | sname | sage | ssex |
+-----+-------+---------------------+------+
| 01 | 赵一 | 1990-01-01 00:00:00 | 男 |
| 02 | 赵二 | 1990-01-01 00:00:00 | 男 |
+-----+-------+---------------------+------+
2 rows in set
====更新中====