SQL 联合查询

使用的前提条件, 联合查询的表,需要有一定的关系(一对一, 一对多, 多对多......). 没有关系的话. 联合查询的结果, 无异议~~

1. 为什么要使⽤联合查询

在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询⼀个条数据的完整信息，就

要从多个表中获取数据

2. 笛卡尔积

数学上的概念. 就是一个" 排列组合 "

笛卡尔积是两个表中的运算,通过排列组合的方式, 形成一张" 大表 "

create table class(class_id int, class_name varchar(20)); insert into class values(1,'java100'),(2,'java101'); create table student(id int, name varchar(20),class_id int); insert into student values(100,'张三',1),(101,'李四',1),(102,'王五',2),(103,'赵六',2); select * from student; select * from class; select * from student, class;

笛卡尔积 排列组合, 把所有的情况, 都罗列出来了 .其实这里包含了无效数据

那些是合理的数据, 有啥特点?

链接条件, 两个表进行笛卡尔积的时候,其中对应列的值,相同, 此时数据就应该被筛选出来, 作为合理的数据

select * from student, class where student.class_id = class.class_id;

要进行 多表联合查询

1.先进行笛卡尔积

2.通过链接条件,进行筛选

3.根据实际需求场景, 进一步添加其他条件 / order by / limit

4.针对列进行筛选/计算 / 分组 / 聚合

多表查询并不复杂,但是写的时候,有点麻烦~~

构造练习案例数据

# 课程表 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.首先确定这个操作要查询的数据,来自哪些表.

2.确定是否存在可以关联的id 这俩实体是否有关系.

1)笛卡尔积

# 第一步计算笛卡尔积 select * from student,class;

2) 链接条件

# 2.引入链接条件 select * from student,class where student.class_id = class.class_id;

3) 添加更多的条件

# 再次添加条件 select *from student, class where student.class_id = class.class_id and student.name = '孙悟空';

4)针对列进行筛选/计算表达式/聚合查询

# 针对列进行筛选 select student.name, student.age, student.gender, student.enroll_date, class.name from student, class where student.class_id = class.class_id and student.name = '孙悟空';

4. 内连接

join on(把 , 替换成join where替换成on)

select student.name, student.age, student.gender, student.enroll_date, class.name from student join class on student.class_id = class.class_id where student.name = '孙悟空';

5. 外连接

外连接分为左外连接、右外连接.

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

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

use java118_2; create database java118_2; show tables; create table student(id int,name varchar(20)); create table score(student_id int, score int); insert into student values(1,'张三'),(2,'李四'),(3,'王五'); insert into score values(1,90),(2,80),(3,70); select * from student; select * from score;

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

同一个表 , 自己和自己连接

通过自连接, 把行和行之间的关系,转成列和列之间的关系!!!!!!

select * from score as s1,score as s2;

不使用别名会报错!!!

select * from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.scorse = 1 and s1.score > s2.score;

7.合并查询

select * from student where student_id < 3 UNION select * from student1;

union自动去重

union all不去重

确保合并的两组查询结果,列的个数/类型 要匹配