MySQL联合查询

前言

使用联合查询的原因
在数据库设计的范式下，有时候数据不可以放在一个表中，我们要分成多个表，但是我们在查询的时候可能是要获取这多个表中的信息，因此我们就需要联合这多个表进行查询

create table class(
       id int primary key auto_increment,
	   name varchar(20));
create table student(
       id int primary key auto_increment,
	   name varchar(20),
	   sex char(1),
	   class_id int,
	   foreign key(id) references class(id));

insert into class(name) values ('java113'),('java78'),('C++110');
insert into student(name, sex,class_id) values ('张三','男',1),('李四','女',1),('王五','男',2);

如果我们用多表查询，则会出现下面这种情况

select * from student,class;

这两个表的联合查询，其实是笛卡尔积，因此其查询的列数就是两个表的列数之和，行数就是其两个表列数之积
如果表内容多，其数据就会变成非常庞大，因此上面这样我们联合查询是不对的，并且其班级id要一样才是正确的结果 ，剩余都是错误的结果

为了让其按照我们的要求查询，因此我们要加上where条件，当其class表中的id和student表中的class_id相等时候才可以

--此时只需要加上where条件即可
select * from student,class where student.class_id = class.id;

此时查询结果就合理了

由于使用select * 查询比较耗时，因此我们此时也可以指定列，但是我们的形式是 表名.列名，因为两个表中可能有相同的列名，因此要加上列名

如果两个表中列名不相同的列，其可以不添加表名

--查询其对应列的信息
select student.id,student.name,sex,student.class_id,class.id,class.name from student,class where student.class_id = class.id;
--查询张三的信息
select student.id,student.name,student.sex,student.class_id,class.id,class.name from student,class 
          where student.class_id = class.id and student.name = '张三';

如果我们不添加其对应的表，并且还是相同的列名，此时就会出现此时的id对应的列是摸棱两可的，就是我们不知道这个列是来自那个表，因此就会出错

联合查询步骤

1.确定查询表 -->进行笛卡尔积
2.确定连接条件
3.加入查询条件
4.精简查询列，也可以给表取别名

内连接

上面我们只是简单的介绍了可以联合查询，下面我们就来详细介绍一些联合查询的方式

1  select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;
--此时的别名不是必须要取的，并且此时取别名的as是可以省略的

此时我们创建四个表来举例

--创建课程表
create table course(
       id int primary key auto_increment,
			 name varchar(20)
);
--班级表
create table class(
			 id int primary key auto_increment,
			 name varchar(20)
);
--学生表
create table student(
       id int primary key auto_increment,
			 name varchar(20),
			 class_id int,
			 foreign key(class_id) references class(id)
);
--分数表
create table score(
			 student_id int ,
			 course_id int,
			 score int,
			 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, class_id) values 
('唐三藏', 1),
('孙悟空', 1),
('猪悟能', 1),
('沙悟净', 1),
('宋江', 2),
('武松',  2),
('李逹',  2),
('不想毕业', 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);
	

查询唐三藏的所有学科成绩

-- 我们用上面两种方式都可以
select s.name,sc.score from student as s,score as sc 
    where s.id = sc.student_id 
    and s.name = '唐三藏';

select s.name,sc.score from student as s 
      join score sc 
      on s.id = sc.student_id and s.name = '唐三藏';

例如查询所有学生的总成绩

select s.name, sum(sc.score) from student as s,score as sc
      where s.id = sc.student_id
      group by (s.id);-- 要进行分组，因为其求和肯定要是一个学生的成绩

查询所有同学的详细信息包括考试成绩

select
	s.id,
	s.name as 姓名,
	c.name as 课程,
	sc.score as 分数
from
	course c,
	student s,
	score sc 
where
	s.id = sc.student_id 
	and c.id = sc.course_id 
order by
	s.id;

外连接

外连接分为左外连接、右外连接和全外连接，但是MySQL是不支持全外连接
左外连接：返回左表中的所有记录和右表匹配的记录，如果右表没有匹配到，结果集会显示空
右外连接：和左外连接相反，返回右表中的所有记录和左表匹配的记录，如果左表没有匹配到，结果集会显示空
全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹配的记录，则结果集中对应字段会显⽰为NULL

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

左外连接

以上面的为例，查询没有参加考试的学生
这时候就可以让student左外连接score，这样就会把学生全部显示出来
到时候如果成绩为空，说明其没有参加考试

select s.id,s.name,sc.* from student s 
 left join score sc on s.id = sc.student_id; 

# 因为未考试的成绩为空，因此其可以筛选掉考试的同学
select s.id,s.name,s.class_id from student s 
left join score sc on s.id = sc.student_id 
where sc.score is null;

右外连接

查询没有学生的班级

select * from student s right join class c on s.class_id = c.id;

# 此时没有学生班级其学生的id肯定为空
select * from student s right join class c on s.class_id = c.id where s.id is null;

自连接

自连接就是自己对自己求笛卡尔积，在查询的时候可以使用where条件对结果进行过滤，或者可以使用自己对自己表的数据进行比较，但是自连接的时候要对表起别名

select * from score,score;

此时会报错，因为使用两个名字相同的表或者是没有取别名

select * from score as s1,score as s2;

查询MySQL成绩大于java成绩大的学生

分开写
# 先获取起对应课程id
select id,name from course where name = 'MySQL' or name = 'java';

select * from  score s1,score s2 
            where s1.student_id = s2.student_id 
	        and s1.course_id = 3 and s2.course_id = 1 #学生id相同
	        and s1.score > s2.score; # MysSQL 成绩>java

-- 放在一起写
SELECT
	s1.*,
	s2.* 
FROM
	score s1,
	score s2,
	course c1,
	course c2 
WHERE
	s1.student_id = s2.student_id 
	AND s1.course_id = c1.id 
	AND s2.course_id = c2.id 
	AND c1.NAME = 'MySQL' 
	AND c2.NAME = 'java' 
	AND s1.score > s2.score;

此时如果想看到其所有信息，就要将其student 、class和score都要进行笛卡尔积

	# 显示其所有信息
	SELECT
	stu.name as 姓名,
	c.name as 班级,
	s1.score as MySQL,
	s2.score as java
FROM
	score s1,
	score s2,
	course c1,
	course c2,
	student stu,
	class c 
WHERE
	s1.student_id = s2.student_id 
	AND s1.course_id = c1.id 
	AND s2.course_id = c2.id 
	and stu.id = s1.student_id
	and stu.class_id = c.id
	AND c1.NAME = 'MySQL' 
	AND c2.NAME = 'java' 
	AND s1.score > s2.score

子查询

有时候我们进行查询时候的where条件可能还需要进行查询，就是一个查询的结果作为另一个查询的条件

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

单行子查询

where条件里的查询只返回一行数据

例如查询姓名为 '不想毕业’的同学
因此要先查找其班级id，后面再根据其班级id进行查找

select * from student where class_id = 
  (select class_id from student where name = '不想毕业');

当然这个我们还是可以分开写的

select class_id from student where name = '不想毕业';
select * from student where class_id = 2;

多列子查询

返回多行数据，因此我们要是用[not ] in 关键字

查询课程MySQL和java的成绩

select * from score where score.course_id in(select id from course where name = 'MySQL' or name = 'java');

当然也可以查询除了MySQL和java的成绩

select * from score where score.course_id in(select id from course where name = 'MySQL' or name = 'java');

多列子查询

多列子查询就是返回的是多个列的数据
例如：查询重复录入的分数

insert into score(score, student_id, course_id) 
 values(70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);
 # 查询重复成绩
select * from score where (score,student_id,course_id) in 
(select score,student_id,course_id from score 
group by score,student_id,course_id having count( * ) >1);

from 语句中使用子查询

我们在进行查询的时候，MySQL是自动创建一个临时表来存放，因此我们可以那这个临时表进行子查询或者表连接操作
例如：查询所有比‘java001班’平均分高的成绩信息

# 先求平均分
select avg(sc.score) from score sc,class c,student s where c.id = s.class_id and sc.student_id = s.id and c.name = 'java001班';

SELECT
	* 
FROM
	score s,(
	SELECT
		avg( sc.score ) score 
	FROM
		score sc,
		class c,
		student s 
	WHERE
		c.id = s.class_id 
		AND sc.student_id = s.id 
		AND c.NAME = 'java001班' 
	) tmp 
WHERE
	s.score > tmp.score;

tmp是临时表

合并查询

开发过程中，为了合并多个select 返回的结果，可以使用操作集 union 、union all

# 先创建一个和原本student相同的表
create table student1 like student;
# 插入一些数据
insert into student1 (name,  class_id) values 
('唐三藏',  1),
('刘备',  3),
('张飞',  3),
('关羽',  3);
# 此时
select * from student1;

union

用于两个结果的并集，并且可以去掉重复行
⽰例：查询student表中id<3的同学和student1表中的所有同学

# 查询student表中student中id < 3的同学和student1表中的所有同学
select * from student where id < 3 
          union select * from student1;

此时会根据要求将两个select结果合并，并且去掉了重复部分

Union all

也是用于合并结果集，此操作符是不可以去掉结果的重复行

select * from student where id < 3 
        union all select * from student1;

此时就可能会有重复的行

插入查询结果

可以将一个查询结果当作数据插入一个表中

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

例如：将student表中C++001班的学⽣复制到student1表中

insert into student1 (name,class_id) 
 select s.name ,s.class_id from student s ,class c 
 where s.class_id = c.id and c.name = 'C++001班';

到这里就结束了，欲知后事如何，请听下回分解