MySQL数据库（查询操作）

目录

1. 聚合查询

1.1 count

1.2 sum

1.3 avg

1.4 max

1.5 min

2. 分组查询

2.1 GROUP BY子句

2.2 HAVING

3. 多表/联合查询

3.1 多表查询的核心计算：笛卡尔积

3.2 多表查询操作举例

3.2.1 先建表

3.2.2 查询许仙同学的成绩

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

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

3.3 外连接

3.3.1 外链接是什么

3.3.2 操作举例

3.4 图示内外连接

3.5 自连接

3.6 子查询

3.6.1 单行子查询

3.6.2 多行子查询

3.6.3 in 和 exists 区别

3.7 合并查询

---

1. 聚合查询

聚合函数：

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

1.1 count

 count里面写列名的时候，null是不作数的

1.2 sum

sum为求和，是行和行之间求和，与列无关

 

 1.3 avg

求平均值，行和行之间的

1.4 max

求最大值

 

 1.5 min

求最小值

2. 分组查询

2.1 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查 询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中

例：按照职业分组求平均工资

也可以和where等条件一起用

 

 

2.2 HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

3. 多表/联合查询

3.1 多表查询的核心计算：笛卡尔积

 

3.2 多表查询操作举例

3.2.1 先建表

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),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

表的信息：

1. 班级表：

2. 学生表：

 

 3. 课程表：

4. 分数表：

 

 

3.2.2 查询许仙同学的成绩

先需要"许仙"这个名字需要到student表中查找，还需要知道成绩.成绩是包含在score这个表里~~

但是score表里没有学生姓名，如何把score这张表和student这张表关联起来呢?

就可以通过score这个表和student这个表进行计算笛卡尔积，笛卡尔积的结果中就既包含分数,又包含学生姓名了~~

 查询结果：

 可以再精简一下：

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

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

3.3 外连接

3.3.1 外链接是什么

有些功能,用where能实现, join on也能实现

还有些功能,用where实现不了, join on才能实现

内连接:

1. 使用where就是在进行内连接

2. [inner] join on也是在进行内连接~~

外连接：

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

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

3.3.2 操作举例

 创建两张表：

 使用内连接：

 使用外连接：

 3.4 图示内外连接

内连接就是两张表的交集

 左外连接就是左表+交集

 右外连接就是右表+交集

 

全外连接就是全部表内容：

 3.5 自连接

存在的目的:

有些查询,是希望把行和行之间的数据按照-定的条件进行比较

就相当于把行和行之间的数据转成了列和列

笛卡尔积： 

 

3.6 子查询

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

3.6.1 单行子查询

返回一行记录的子查询 查询与“不想毕业” 同学的同班同学

3.6.2 多行子查询

 [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!='英文');

返回多行记录的子查询

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

 [NOT] EXISTS关键字：

-- 使用 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);

3.6.3 in 和 exists 区别

in

 exists

3.7 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all

使用UNION 和UNION ALL时，前后查询的结果集中，字段需要一致

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

--查询id小于3，或者名字为“英文”的课程：
select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行

--查询id小于3，或者名字为“Java”的课程
-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';