联合查询

在where后面，不要出现聚合函数。

一般先执行where语句，在执行分组语句，最后执行聚合函数

GROUP BY子句（分组的意思）
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
案例：
准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）

create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);

--查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;

HAVING
使用GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用
HAVING，其功能相当于where，和GROUP BY搭配使用

--显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

联合查询
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','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',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);

查询“许仙”同学的 成绩 我们可以发现，要求的信息来 自于2张表。 学生表 成绩表

--方式一
select stu.sn,stu.name,sco.score,cou.name as 课程名 from student stu 
inner join score sco on stu.id = sco.student_id 
inner join course cou on sco.course_id = cou.id 
and stu.id=4; 

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

--方式三
--1、inner可以省略 
--2、或者可以省略join on
select stu.sn,stu.name,sco.score,cou.name as 课程名
from student stu ,score sco,course cou 
where stu.id = sco.student_id  
and 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.name,sco.score 
from student stu 
left join score sco on stu.id = sco.student_id 
group by stu.id;

自连接
自连接是指在同一张表连接自身进行查询。
案例：
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

select s2.* from score s1,score s2 
where s1.student_id=s2.student_id 
and s1.course_id=1  --java的id 为1
and s2.course_id=3  --计算机原理的ID为3
and s1.score<s2.score;

子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
单行子查询：返回一行记录的子查询
例如：查询与“不想毕业” 同学的同班同学：

--1、先查这个人是哪个班的？ 
select classes_id from student stu 
where stu.name='不想毕业';

--2、查1班有写谁？ 
select * from student 
where classes_id = 1; 

--3、合并 
select * from student where classes_id = (select classes_id from student stu 
where stu.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!='英文');

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可作为外部查询的结果行，否则不能作为结果

合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：查询id小于3，或者名字为“白素贞”的学生：

select * from student where id<=3 
union 
select * from student where name='白素贞';

union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“白素贞”的学生：

select * from student where id<=3 
union all
select * from student where name='白素贞';