Aggregation 联合查询 联合查询原理

文章目录

• 笛卡尔积
• 内连接
• 外连接：左外连接 和 右外连接

• 左外连接
• 右外连接

• 自连接
• 子查询

• 单行子查询：返回一行记录的子查询
• 多行查询：返回多行记录的子查询

• [NOT] IN关键字
• [NOT]EXISTS关键字

• 合并查询

• unoin
• unoin all

1、什么是联合查询：就是两张表或者两张以上的表，进行连接查询。

2、为什么要联合查询：就是因为，我们所需要的数据，不仅仅是来自于一张表的，他是来自于多张表的。所以，我们要进行联合查询！！！

3、前置知识：笛卡尔积！！！ 排列组合而已！

笛卡尔积

所有的联合查询，都是从这个笛卡尔积当中去取出数 据！！！当然在取数据的时候，一定是要满足某种规则的。 这种规则就是我们接下来学习的各种连表查询的方式！！！

准备数据：

drop database if exists test0311; 
create database test0311; 
use test0311; 
drop table if exists classes; 
create table classes( 
       id int primary key auto_increment, 
       name varchar(50), 
       `desc` varchar(50) 
     );
insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), 
('中文系2019级3班','学习了中国传统文学'), 
('自动化2019级5班','学习了机械自动化'); 

drop table if exists student; 
create table student( 
     id int primary key auto_increment, 
     sn int,
     name varchar(30), 
     qq_mail varchar(30), 
     classes_id int 
   );
insert into student(sn, name, qq_mail, classes_id) values 
('09982','黑旋风李逵','[e-mail]',1), 
('00835','菩提老祖',null,1), 
('00391','白素贞',null,1), 
('00031','许仙','[e-mail]',1), 
('00054','不想毕业',null,1), 
('51234','好好说话','[e-mail]',2), 
('83223','tellme',null,2), 
('09527','老外学中文','[e-mail]',2); 

drop table if exists course; 
create table course( 
    id int primary key auto_increment, 
    name varchar(20) 
  );
insert into course(name) values 
('Java'),
('中国传统文化'),
('计算机原理'),
('语文'),
('高 阶数学'),
('英文');

drop table if exists score; 
create table score( 
      id int primary key auto_increment, 
      score DECIMAL, 
      student_id int, 
      course_id int 
   );
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), 
-- tellme 
(80, 7, 2),(92, 7, 6);

内连接

语法：
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连 接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
建议：多张表联合查询的时候，去找每个表之间的关系。
1、inner可以省略
2、或者可以省略join on
查询许仙同学的成绩
一：

select stu.name,sc.score,co.name 
from student stu ,score sc,course co 
where stu.id=sc.student_id 
and co.id=sc.course_id 
and stu.name='许仙';

二：

select stu.sn,stu.name,sco.score,cou.name as 课程名 
from student stu 
join score sco 
on stu.id = sco.student_id  
join course cou 
on sco.course_id = cou.id 
and stu.name='许仙';

总结：内连接 就是求几张表的 交集

** 查询每个同学的总成绩，及同学的个人信息不用求课程名**

select stu.sn, stu.name,sum(score)
from student stu,score sco
where stu.id=sco.student_id
group by stu.id;

查询所有同学的成绩，及同学的个人信息：

select stu.id,stu.sn,stu.name,sco.score,sco.course_id,co.name 
from student stu,score sco,course co 
where stu.id=sco.student_id 
and sco.course_id=co.id 
order by stu.id;

SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
FROM
 student stu
 JOIN score sco ON stu.id = sco.student_id
 JOIN course cou ON sco.course_id = cou.id
ORDER BY stu.id;

外连接：左外连接 和 右外连接

左外连接

在进行连接查询的时候，以左边的表为准

查询每个同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示该同学

select stu.sn,stu.name,sco.score
 from student stu left join score sco
 on stu.id=sco.student_id
 group by stu.id;

右外连接

如果实现上述同样的功能，那么只需要这样修改就好了！！

select stu.id,stu.name,sco.score 
from score sco right join student stu
on stu.id = sco.student_id 
group by stu.id；

自连接

把一张表 看做两张表来使用。
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

– 先查询“计算机原理”和“Java”课程的id

select id,name from course where name=‘Java’ or name=‘计算机原理’;

– 再查询成绩表中，“计算机原理”成绩比“Java”成绩 好的信息

SELECT

s1.*

FROM

score s1,

score s2

WHERE

s1.student_id = s2.student_id

AND s1.score < s2.score

AND s1.course_id = 1

AND s2.course_id = 3;

方法二：使用join on

-- 也可以使用join on 语句来进行自连接查询
SELECT
 s1.* 
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;

以上查询只显示了成绩信息，并且是分布执行的。要显示学生及成绩信息，并在一条语句显示：

SELECT
 stu.*,
 s1.score Java, 
 s2.score 计算机原理
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 JOIN student stu ON s1.student_id = stu.id
 JOIN course c1 ON s1.course_id = c1.id
 JOIN course c2 ON s2.course_id = c2.id
 AND s1.score < s2.score
 AND c1.NAME = 'Java'
 AND c2.NAME = '计算机原理';

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套语句

单行子查询：返回一行记录的子查询

例：查询与“不想毕业”同学一班的同班同学

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

多行查询：返回多行记录的子查询

查询“语文”或“英文”课程的成绩信息

[NOT] IN关键字

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

可以使用多列包含：

---插入重复的分数：score，student_id,course_id 列重复
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( 0 ) > 1 );

[NOT]EXISTS关键字

EXISTS(表达式) 只要这个表达式为真 就返回true

例如这条SQL语句：SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A
2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id
3、如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

• 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，将子查询当作一个临时表使用。
  查询所有比中文系2019级3班平均分高的成绩信息

SELECT
 * 
FROM
 score sco,
 (
 SELECT
 avg( sco.score ) score 
 FROM
 score sco
 JOIN student stu ON sco.student_id = stu.id
 JOIN classes cls ON stu.classes_id = cls.id
 WHERE
 cls.NAME = '中文系2019级3班'
 ) tmp 
WHERE
 sco.score > tmp.score;

合并查询

为了合并多个select的执行结果，可以使用集合操作符union，union all.使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

unoin

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
查询id<3,或者名字为“英文”的课程

select * from course where id<3
unoin 
select * from course where name='英文';

---或者使用or来实现
select * from course where id<3 or name='英文';

unoin all

该操作符用于取得两个结果集的并集，当使用该操作符时，不会去掉结果集中的重复行。
查询id<3,或者名字为“Java”的课程

select * from course where id<3
unoin all
select * from course where name='java';