需求:基于昨天设计的Mysql数据库而写的多表查询
数据库结构设计:http://blog.csdn.net/m0_37949305/article/details/79212596
多表查询:
查询结果过长,就不列出来了。
(1)查询出所有学生的学号、科目和科目成绩:
通过查询 score 表 和student表
查询条件为:score.subject_id=`subject`.subject_id
排序方式为:按学号 从小到大 排序
SELECT score.stu_id,`subject`.subject_name,score.score
FROM score,`subject`
WHERE score.subject_id=`subject`.subject_id
ORDER BY score.stu_id ASC
(2)查询出所有学生的学号、姓名、性别、科目、科目成绩:
在(1)的基础上进行修改
通过查询 student 表、 score 表 和 subject 表
查询条件为:student.stu_id = score.stu_id AND score.subject_id=`subject`.subject_id
排序方式为:按学号 从小到大 排序
SELECT student.stu_id,stu_name,stu_sex,`subject`.subject_name,score.score
FROM student,score,`subject`
WHERE student.stu_id = score.stu_id
AND score.subject_id=`subject`.subject_id
ORDER BY student.stu_id ASC
(3)查询出特定学生的学号、姓名、性别、科目、科目成绩:
如查询出学号为1201000101的学生的学号、姓名、性别、科目、科目成绩
只需要在(2)的基础上一句条件来限定:
student.stu_id = 1201000101(与上一句sql语句用AND连接符连接)
合起来就是 AND student.stu_id = 1201000101
SELECT student.stu_id,stu_name,stu_sex,`subject`.subject_name,score.score
FROM student,score,`subject`
WHERE student.stu_id = score.stu_id
AND score.subject_id=`subject`.subject_id
AND student.stu_id = 1201000101
ORDER BY student.stu_id ASC
(4)查询出所有学生的学号、姓名、性别和语文成绩:
SELECT student.stu_id,stu_name,stu_sex,`subject`.subject_name,score.score
FROM student,score,`subject`
WHERE student.stu_id = score.stu_id
AND score.subject_id=`subject`.subject_id
AND student.stu_id = 1201000101
AND `subject`.subject_name = '语文'
ORDER BY student.stu_id ASC
将(3)跟(4)整合在一起
SELECT student.stu_id,stu_name,stu_sex,`subject`.subject_name,score.score
FROM student,score,`subject`
WHERE student.stu_id = score.stu_id
AND score.subject_id=`subject`.subject_id
AND student.stu_id = 1201000101
AND `subject`.subject_name = '语文'
ORDER BY student.stu_id ASC
(6)查询出所以
学生的学号、姓名、性别和班级:
通过查询grade表、clazz表和student表
查询条件为: grade.grade_id = clazz.grade_id AND student.clazz_id = clazz.clazz_id
排序方式为:按学号从小到大排序
SELECT student.stu_id,stu_name,stu_sex,clazz_name
FROM grade,clazz,student
where grade.grade_id = clazz.grade_id AND student.clazz_id=clazz.clazz_id
ORDER BY student.stu_id ASC
(7)查询出所以学生的学号、姓名、性别、班级、科目和科目成绩:
将(1)与(6)整合到一起
排序方式为:按学号从小到大排序
运用到了as字段
SELECT stu_info.*,sub_score.subject_name,score FROM
(SELECT score.stu_id,`subject`.subject_name,score.score
FROM score,`subject`
WHERE score.subject_id=`subject`.subject_id
)as sub_score,
(SELECT student.stu_id,stu_name,stu_sex,clazz_name
FROM grade,clazz,student
where grade.grade_id = clazz.grade_id AND student.clazz_id=clazz.clazz_id
)as stu_info
WHERE sub_score.stu_id=stu_info.stu_id
ORDER BY stu_info.stu_id ASC
注意:使用as table_name之后,操作都要用as的table_name而不是原来的table_name
如 as stu_info 后 设置排序方式要用stu_info.stu_id取学号