数据库-＞联合查询

一、联合查询

1.联合查询

2.多表联合查询时MYSQL内部是如何进⾏计算的

3.多表联合查询

3.1语法

3.2指定多个表，进行联合查询

3.3通过表与表中的链接条件过滤掉无效数据

3.4通过指定列查询，精简查询结果编辑

3.5可以通过给表起别名的方式，来简化查询SQL语句

二、内连接

1.语法

2.示例

2.1查询唐三藏同学的成绩

2.2查看所有同学的总成绩，及同学的个人信息

2.3查询所有同学每⻔课的成绩，及同学的个⼈信息

三、外连接

1.语法

2.示例

2.1查询没有参加考试的同学信息

2.2查询没有学⽣的班级

四、自连接

1.应用场景

2.示例

2.1显⽰所有"MySQL"成绩⽐"JAVA"成绩⾼的成绩信息

2.2表连接练习

五、子查询

1.语法

2.示例

2.1查询与"不想毕业"同学的同班同学（单行子查询）

2.2查询"MySQL"或"Java"课程的成绩信息（多行子查询）

2.3查询重复录⼊的分数（多列子查询）

六、合并查询

1.创建新表并初始化数据

2.Union

2.1作用

2.2示例

3.Union all

3.1作用

3.2示例

七、插入查询结果

1.语法

2.示例

一、联合查询

1.联合查询

在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询⼀个条数据的完整信息，就
要从多个表中获取数据，如下图所⽰：要获取学⽣的基本信息和班级信息就要从学⽣表和班级表中获取，这时就需要使⽤联合查询，这⾥的联合指的是多个表的组合

在工作中单独查询这两张表，都得不到一个完整的数据

一次查询涉及到两张或两张以上的表，就称为联合查询或（表关联查询）

2.多表联合查询时MYSQL内部是如何进⾏计算的

参与查询的所有表取笛卡尔积，结果集在临时表中

3.多表联合查询

drop table if exists class;
create table class(
  id bigint primary key auto_increment,
  name varchar(20)
);

drop table if exists student;
create table student(
  id bigint primary key auto_increment,
  name varchar(20) not null,
  sno varchar(10) not null,
  age int default 18,
  gender tinyint(1),
  enroll_date date,
  class_id bigint not null,
  foreign key (class_id) references class(id)
);

drop table if exists course;
create table course(
  id bigint primary key auto_increment,
  name varchar(20)
);

drop table if exists score;
create table score(
  id bigint primary key auto_increment,
  score float,
  student_id bigint,
  course_id bigint,
  foreign key (student_id) references student(id),
  foreign key (course_id) references course(id)
);

insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计算机网络'), ('数据结构');

insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');

insert into student (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('孙悟空', '100002', 18, 1, '1986-09-01', 1),
('猪悟能', '100003', 18, 1, '1986-09-01', 1),
('沙悟净', '100004', 18, 1, '1986-09-01', 1),
('宋江', '200001', 18, 1, '2000-09-01', 2),
('武松', '200002', 18, 1, '2000-09-01', 2),
('李逹', '200003', 18, 1, '2000-09-01', 2),
('不想毕业', '200004', 18, 1, '2000-09-01', 2);

insert into score (score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);

3.1语法

select * from 表一, 表二

3.2指定多个表，进行联合查询

通过观察，两张表联合后的结果集中，有些是无效数据

3.3通过表与表中的链接条件过滤掉无效数据

class_id 在 student 表中，但两张表里都有 id 列，不指定表名时，MYSQL 无法分清要使用那张表中的 id 列

3.4通过指定列查询，精简查询结果

3.5可以通过给表起别名的方式，来简化查询SQL语句

在工作中两张表的关联，尽量使用联合和查询

二、内连接

1.语法

select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件; -- 精简写法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件; -- 规范写法

2.示例

2.1查询唐三藏同学的成绩

2.2查看所有同学的总成绩，及同学的个人信息

2.3查询所有同学每⻔课的成绩，及同学的个⼈信息

三、外连接

外连接分为左外连接、右外连接和全外连接三种类型，MySQL不⽀持全外连接。

左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。

右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。

全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。

1.语法

-- 左外连接，表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;

2.示例

2.1查询没有参加考试的同学信息

2.2查询没有学⽣的班级

四、自连接

1.应用场景

⾃连接是⾃⼰与⾃⼰取笛卡尔积，可以把⾏转化成列，在查询的时候可以使⽤where条件对结果进⾏过滤，或者说实现⾏与⾏之间的⽐较。在做表连接时为表起不同的别名

2.示例

2.1显⽰所有"MySQL"成绩⽐"JAVA"成绩⾼的成绩信息

两个表名重复

精简查询结果用别名表示表头

2.2表连接练习

显⽰所有"MySQL"成绩⽐"JAVA"成绩⾼的学⽣信息和班级以及成绩信息

动态接收的条件放最后

五、子查询

⼦查询是把⼀个SELECT语句的结果当做别⼀个SELECT语句的条件，也叫嵌套查询

1.语法

select * from table1 where col_name1 {= | IN} (
    select col_name1 from table2 where col_name2 {= | IN} [(
        select ...)
    ] ...
)

子查询是由很多条SQL语句组成的可以把子查询中的SQL一条一条的执行最终拿到想要的结果

由于嵌套的层级数没有固定的限制如果多层嵌套的话查询的效率不可控工作中谨慎使用

2.示例

2.1查询与"不想毕业"同学的同班同学（单行子查询）

在子查询中先执行内层的SQL，内层的SQL会产生一个值，做为外层查询的条件

2.2查询"MySQL"或"Java"课程的成绩信息（多行子查询）

子查询中返回的多个数据行

不包含MYSQL和Java：

2.3查询重复录⼊的分数（多列子查询）

过滤重复

解题思路：

1.对重复的列进行分组

2.统计下每个分组数

3.如果分组数大于1 则表示有重复记录

2.4查询所有⽐"Java001班"平均分⾼的成绩信息（在from⼦句中使⽤⼦查询）

解题思路：

1.先算出平均分

2.再用成绩表中的真实成绩与平均分做比较

temp：为临时表起一个别名以便再条件过滤中使用临时表

六、合并查询

合并多个select操作返回的结果最终返回一个结果集

1.创建新表并初始化数据

create table student1 like student;
insert into student1 (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('刘备', '300001', 18, 1, '1993-09-01', 3),
('张⻜', '300002', 18, 1, '1993-09-01', 3),
('关⽻', '300003', 18, 1, '1993-09-01', 3);

2.Union

2.1作用

该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，会⾃动去掉结果集中的重复⾏

2.2示例

查询student表中 id < 3 的同学和student1表中的所有同学

单独查询单表也是可以的

3.Union all

3.1作用

该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，不会去掉结果集中的重复⾏

3.2示例

查询student表中 id < 3 的同学和student1表中的所有同学

七、插入查询结果

1.语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

2.示例

-- 将student表中的C++001班的学生复制到student1表中
insert into student1 (name, sno, age, gender, enroll_date, class_id)
select s.name, s.sno, s.age, s.gender, s.enroll_date, s.class_id
from student s, class c where s.class_id = c.id and c.name = 'C++001班';

为重复的列指定表名.列号不重复的列可以不加表名推荐还是用表名.列名的方式指定查询结果