【MYSQL】创建虚表来辅助数据库查询

在进行数据库查询时，有时需要用到对既有的数据表进行多表查询得出的临时条件的数据表，就可以暂时创建成为虚表，并赋予简单明了的字段名以及临时表名。

例题a：查询出每门课程低于平均成绩的学生姓名、课程名称、分数

分析：

1. 要完成题目，首先需要一张所有学生的课程及分数表，所需字段：学生姓名、课程名称、分数；

select a.Name as Name,b.C_name as C_name,b.Grade as Grade from students a,score b where a.Id=b.stu_id; #可以将临时创建的虚表定义为st，以备后用

2. 另外还需要一张各课程的平均分数的表，所需字段：课程名称、平均分数

select C_name,avg(Grade) as avgScore from score group by C_name; #创建虚表av

3. 利用新建的虚表st、av完成题目

select st.*,av.avgscore from
(select a.Name as Name,b.C_name as C_name,b.Grade as Grade
from students a,score b
where a.Id=b.stu_id) as st,
(SELECT C_name,avg(Grade) as avgScore from score group by C_name) as av
where st.C_name=av.C_name and st.Grade<av.avgScore;

 

例题b：查询同时参加计算机和英语考试的学生的信息

分析：

1. 要完成题目，分别需要所有学习计算机课程的学生id，以及所有学习英语课程的学生id，所需字段：学生ID，课程名称；

(SELECT stu_id AS stu_id1,c_name FROM score WHERE c_name = "计算机") AS computer;

(SELECT stu_id AS stu_id2,c_name FROM score WHERE c_name = "英语") AS english;

2. 通过各个表中的学生学号信息，找出同事参加计算机和英语考试的学生的信息

SELECT students.*
FROM
students,
(SELECT stu_id AS stu_id1,c_name FROM score WHERE c_name = "计算机") AS computer,
(SELECT stu_id AS stu_id2,c_name FROM score WHERE c_name = "英语") AS english
where computer.stu_id1=english.stu_id2 and students.id=computer.stu_id1;