连接查询

连接查询

• 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

• 等值连接查询：查询的结果为两个表匹配到的数据

• 左连接查询：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充

• 右连接查询：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充

准备数据

drop table if exists courses;
create table courses (
  courseNo int(10) unsigned primary key auto_increment,
  name varchar(10)
);

insert into courses values 
('1', '数据库'),
('2', 'qtp'),
('3', 'linux'),
('4', '系统测试'),
('5', '单元测试'),
('6', '测试过程');

drop table if exists scores;
create table scores (
  id int(10) unsigned primary key auto_increment,
  courseNo int(10),
  studentno varchar(10),
  score tinyint(4)
);

insert into scores values 
('1', '1', '001', '90'),
('2', '1', '002', '75'),
('3', '2', '002', '98'),
('4', '3', '001', '86'),
('5', '3', '003', '80'),
('6', '4', '004', '79'),
('7', '5', '005', '96'),
('8', '6', '006', '80');

等值连接

方式一

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.列

添加两门课程

insert into courses values 
(0, '语文'),
(0, '数学');

例1：查询所有课程的成绩，包括没有成绩的课程

select
    *
from
    scores sc
right join courses cs on cs.courseNo = sc.courseNo

例2：查询所有课程的成绩，包括没有成绩的课程

select
    *
from
    scores sc
right join courses cs on cs.courseNo = sc.courseNo
left join students stu on stu.studentNo = sc.studentNo