聚合查询、联合查询【mysql数据库】

把查询结果插入到另一个表中（相当于复制表）

 要求查询结果临时表的列数和列的类型，要和student2这里匹配

聚合查询

本质上是针对行和行之间的运算

聚合查询需要搭配聚合函数

1、count 

 

 不一定非要写星号（*），还可以写成任意的 列名/表达式

 虽然有一行是全NULL，count计算会把全为NULL的行也给计算进去

 2、sum

sum求和，把这一列的所有行进行加和，要求这个列得是数字（不能是字符串/日期）

3、AVG

4、MAX&&MIN

 求和，平均，最大，最小这些操作都是针对数字类型的列进行的。

 表达式的聚合计算

 联合查询

包括：内连接，外连接，子查询，合并查询

多表查询的基本执行过程：笛卡尔积

 

 笛卡尔积通过排列组合：列数是两个表列数的和，行数是两个表行数的积

 通过条件，排除无意义的数据。

 练习：

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) , classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('class1', 'desc1'),
('class2','desc2'),
('class3','desc3');

insert into student(sn, name, qq_mail, classes_id) values
('09982','likui','xuanfeng@qq.com',1),
('00835','puti',null,1),
('00391','baisuzhen',null,1),
('00031','xuxian','xuxian@qq.com',1),
('00054','buxiangbiye',null,1),
('51234','haohaoshuohua','say@qq.com',2),
('83223','tellme',null,2),
('09527','laowai','foreigner@qq.com',2);

insert into course(name) values
('Java'),('wenhua'),('yuanli'),('chinese'),('math'),('english');

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

 4个表：student 学生表 、 classes 班级表 、 course 课程表 、 score 分数表

 

 查询许仙同学的成绩

写法1：from多个表 

 1、先笛卡尔积

 2、引入连接条件

 3、根据必要需求，加入必要的条件即可

 4、把不必要的列去掉，保留想关注的列

写法二：join on

 写法三：inner join

注意： from多个表只能够实现内连接，而join on既可以实现内连接也可以使用外连接。

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

 第一步：笛卡尔积

 第二步：加上连接条件

 第三步：加上聚合查询，把同一个学生的行合并到一个组里，同时计算总分

 

第一步：笛卡尔积

 第二步：引入连接条件：三张表需要两个连接条件

 第三步：精简一些不必要的列

 ​​​

 用join on也是没问题的

         内连接和外连接在大多数情况下是没有区别的，比如要连接的两个表里面的数据都是一一对应的，这时是没有区别的，如果不是一一对应，内连接和外连接就有区别了。

 

 如果不是一一对应

 

自连接：自己和自己进行笛卡尔积

SQL中无法针对行和行之间的条件比较，需要使用自连接，把行转成列

 显示所有‘计算机原理’成绩比‘java’成绩高的成绩信息

 

 

子查询：把多个SQL组合成一个,把一个查询作为另一个查询的一部分条件（套娃），代码可读性非常差，执行效率也非常差（慎用）

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

insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
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);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
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), 
4.2.1 内连接  
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

查询“不想毕业”的同学的同班同学

 

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

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

1. 先根据课程名字查询出课程id
2. 再根据课程id查询出课程分数

 

注意：查询结果放在内存中，如果查询结果太大了，内存放不下，in就用不了了，可以使用exists代替，exists本质上就是让数据库执行很多个查询操作， 但是exists可读性比较差，执行效率也大大低于in（适合解决特殊场景）

合并查询：本质上是把两个查询的结果集合成一个，union、union all

查询id小于3，或者名字为“英文”的课程： 

 or和union的区别：

        or查询只能来自同一张表，union查询可以是来自不同的表，只要查询的结果的列匹配即可。

union和union all的区别：

        union 会进行去重（把重复的行只保留一行），union all可以保留多份，不会去重。