跳至主要內容

03. 连接查询

Frankie.Lee2024年10月28日大约 7 分钟

03. 连接查询

1. 连接查询-常用方式

  • 基本概念

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

    • 连接查询可以通过连接运算符（连接条件）可以实现多个表查询

  • 连接方式

    1. 内连接
    2. 外连接
      1. 左连接
      2. 右连接
    3. 自关联

1. 内连接

  • 结果为两个表匹配到的数据：查询的结果只显示两个表中满足连接条件的部分

    image-20230217184852758
    image-20230217184852758

2. 外连接

1. 左外连接

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

    image-20230217184841044
    image-20230217184841044

2. 右外连接

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

    image-20230217184813201
    image-20230217184813201

3. 自关联

  • 左表和右表是同一个表，根据连接查询条件查询两个表中的数据

2. 准备数据

-- 数据准备-创建数据库表（此表之前已经创建过）
drop table if exists students;  
create table students (
    studentNo varchar(10) primary key, 
    name varchar(10),
    sex varchar(1),  
    hometown varchar(20),  
    age tinyint(4),
    class varchar(10),  
    card varchar(20)
);
-- 数据准备-插入数据
insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');

-- 创建课程表并插入数据
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');

3. 连接查询-左连接

目标

  • 能够写出左连接查询的SQL语句

1. 左连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用 null 值填充

  • 左连接查询效果图
image-20230217184841044
image-20230217184841044
image-20230214102158440
image-20230214102158440
  • 左连接查询语法格式
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2

  • 说明

    • left join 就是左连接查询关键字

    • on 就是连接查询条件

    • 表1 是左表

    • 表2 是右表

2. 实例

  • 例1: 查询所有学生的成绩，包括没有成绩的学生
select * from students stu left join scores sc on stu.studentNo = sc.studentNo
image-20230512185539257
image-20230512185539257
  • 例2: 查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select * from students stu left join scores sc on stu.studentNo = sc.studentNo left join courses cs on cs.courseNo = sc.courseNo
image-20230512185452884
image-20230512185452884

3. 小结

  • 左连接使用left join .. on .., on 表示两个表的连接查询条件
  • 左连接以左表为主根据条件查询右表数据，右表数据不存在使用 null 值填充。

4. 连接查询-右连接

目标

  • 能够写出右连接查询的SQL语句

1. 右连接查询

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

  • 右连接查询效果图
image-20230217184813201
image-20230217184813201
image-20230214102224408
image-20230214102224408
  • 右连接查询语法格式
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

  • 说明

    • right join 就是右连接查询关键字

    • on 就是连接查询条件

    • 表1 是左表

    • 表2 是右表

2. 实例

  • 例1: 查询所有学生的成绩，包括没有成绩的学生
select * from scores sc right join students stu on stu.studentNo = sc.studentNo
image-20230210154144051
image-20230210154144051
  • 例2: 查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select * from scores sc right join courses cs on cs.courseNo = sc.courseNo right join students stu on stu.studentNo = sc.studentNo
image-20230210154203420
image-20230210154203420

3. 小结

  • 右连接使用right join .. on .., on 表示两个表的连接查询条件
  • 右连接以右表为主根据条件查询左表数据，左表数据不存在使用 null 值填充。

5. 连接查询-内连接

目标

  • 能够写出内连接查询的SQL语句

1. 内连接查询

查询两个表中符合条件的共有记录

  • 内连接查询效果图
image-20230217184852758
image-20230217184852758
image-20230214102132250
image-20230214102132250
  • 内连接查询语法格式
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

  • 说明

    • inner join 就是内连接查询关键字

    • on 就是连接查询条件

2. 实例

  • 例1：查询学生信息及学生的成绩

    • 方式一

      select * from students stu inner join scores sc on stu.studentNo = sc.studentNo
      image-20230210154607803
      image-20230210154607803

    • 方式二

      select * from students stu, scores sc where stu.studentNo = sc.studentNo
-- 不加查询条件，结果为两张表的笛卡尔积
      image-20230210154624145
      image-20230210154624145

  • 例2：查询课程信息及课程的成绩

select * from courses cs inner join scores sc on cs.courseNo = sc.courseNo
image-20230210154639166
image-20230210154639166
  • 例3：查询王昭君的成绩，要求显示姓名、课程号、成绩
select stu.name, sc.courseNo, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo where stu.name = '王昭君'
image-20230210154712555
image-20230210154712555
  • 例4：查询王昭君的成绩，要求显示姓名、课程名称、成绩
select stu.name, cu.name, sc.score from students stu inner join scores sc on stu.studentNo = sc.studentNo left join courses as cu on cu.courseNo=sc.courseNo where stu.name = '王昭君'
image-20230512190726750
image-20230512190726750

3. 小结

  • 内连接使用inner join .. on .., on 表示两个表的连接查询条件
  • 内连接根据连接查询条件取出两个表的 “交集”

6. 连接查询-自连接

目标

  • 能够写出自连接查询的SQL语句

1. 自连接查询

左表和右表是同一个表，根据连接查询条件查询两个表中的数据。

  • 区域表效果图
区域表
区域表

2. 实例

  • 例1：查询省的名称为“山西省”的所有城市
区域表
区域表
  • 创建areas表
-- 创建表:
drop table if exists areas;
create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

-- 插入数据:
insert into areas values 
('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市',	'130000'),
('130600', '保定市',	'130000'),
('130700', '张家口市', '130000'),
('130800', '承德市',	'130000'),
('410000', '河南省',	NULL),
('410100', '郑州市', '410000'),
('410300', '洛阳市',	'410000'),
('410500', '安阳市',	'410000'),
('410700', '新乡市', '410000'),
('410800', '焦作市',	'410000'),
('410101', '中原区',	'410100'),
('410102', '二七区', '410100'),
('410301', '洛龙区',	'410300');

  • 自连接查询的用法

    • 自关联：inner join 关联同一个表，不同的字段。

    • 自连接查询必须对表起别名

  • 例1：查询河南省所有的市

select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='河南省';
image-20230210155430604
image-20230210155430604
  • 例2：查询郑州市的所有的区
select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid where a1.atitle='郑州市';
image-20230210155443992
image-20230210155443992
  • 例3：查询河南省的所有的市区
select * from areas as a1 inner join areas as a2 on a1.aid=a2.pid inner join areas as a3 on a2.aid=a3.pid where a1.atitle='河南省'
image-20230210155506450
image-20230210155506450
image-20230214103210432
image-20230214103210432
image-20230214103253840
image-20230214103253840

3. 小结

  • 自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
  • 自连接就是一种特殊的连接方式，连接的表还是本身这张表
上次编辑于: 2024/12/21 14:32:53
贡献者: lxtao369