数据库查询之内连接,左连接,右连接
连接查询
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回
等值连接查询:查询的结果为两个表匹配到的数据
左连接查询:查询的结果为两个表匹配到的数据加左表特有的数据,对于右表中不存在的数据使用null填充
右连接查询:查询的结果为两个表匹配到的数据加右表特有的数据,对于左表中不存在的数据使用null填充
等值连接
方式一
select * from 表1,表2 where 表1.列=表2.列
方式二(又称内连接)
select * from 表1 inner join 表2 on 表1.列=表2.列
例1:查询学生信息及学生的成绩
select * from
students stu,
scores sc
where
stu.studentNo = sc.studentNo
select * from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
例2:查询课程信息及课程的成绩
select * from
courses cs,
scores sc
where
cs.courseNo = sc.courseNo
select * from
courses cs
inner join scores sc on cs.courseNo = sc.courseNo
例3:查询学生信息及学生的课程对应的成绩
select * from
students stu,
courses cs,
scores sc
where
stu.studentNo = sc.studentno
and cs.courseNo = sc.courseNo
select * from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo
例4:查询王昭君的成绩,要求显示姓名、课程号、成绩
select
stu.name,
sc.courseNo,
sc.score
from
students stu,
scores sc
where
stu.studentNo = sc.studentNo
and stu.name = '王昭君'
select
stu.name,
sc.courseNo,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
where
stu.name = '王昭君'
例5:查询王昭君的数据库成绩,要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
students stu,
scores sc,
courses cs
where
stu.studentNo = sc.studentNo
and sc.courseNo = cs.courseNo
and stu.name = '王昭君'
and cs.name = '数据库'
select
stu.name,
cs.name,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo
where
stu.name = '王昭君' and cs.name = '数据库'
例6:查询所有学生的数据库成绩,要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
students stu,
scores sc,
courses cs
where
stu.studentNo = sc.studentNo
and sc.courseNo = cs.courseNo
and cs.name = '数据库'
select
stu.name,
cs.name,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo
where
cs.name = '数据库'
例7:查询男生中最高成绩,要求显示姓名、课程名、成绩
select
stu.name,
cs.name,
sc.score
from
students stu,
scores sc,
courses cs
where
stu.studentNo = sc.studentNo
and sc.courseNo = cs.courseNo
and stu.sex = '男'
order by
sc.score desc
limit 1
select
stu.name,
cs.name,
sc.score
from
students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on sc.courseNo = cs.courseNo
where
stu.sex = '男'
order by
sc.score desc
limit 1
左连接
select * from 表1 left join 表2 on 表1.列=表2.列
例1:查询所有学生的成绩,包括没有成绩的学生
select * from
students stu
left join scores sc on stu.studentNo = sc.studentNo
例2:查询所有学生的成绩,包括没有成绩的学生,需要显示课程名
select * from
students stu
left join scores sc on stu.studentNo = sc.studentNo
left join courses cs on cs.courseNo = sc.courseNo
右连接
select * from 表1 right join 表2 on 表1.列=表2.列
例1:查询所有课程的成绩,包括没有成绩的课程
select * from
scores sc
right join courses cs on cs.courseNo = sc.courseNo
例2:查询所有课程的成绩,包括没有成绩的课程
注意:第一次右连接是为了显示所有的cources数据,然后用左连接,可以把前面两张表看成合成了一张表,并且为左边的表,为了显示左边的courses数据,所以用 left join来保证courses所有数据可以显示
select * from
scores sc
right join courses cs on cs.courseNo = sc.courseNo
left join students stu on stu.studentNo = sc.studentNo