函数	说明
COUNT([DISTINCT] exam)	返回查询到的数据的数量
SUM([DISTINCT] exam)	返回查询到的数据的总和
AVG([DISTINCT] exam)	返回查询到的数据的平均值
MAX([DISTINCT] exam)	返回查询到的数据的最大值
MIN([DISTINCT] exam)	返回查询到的数据的最小值

COUNT

新建一张学生表 :

student (id , name)

create table student(id int, name varchar(20));

然后向里面插入四个数据如下:

然后使用count, 语句如下:

select count(*) from student;

select count(id) from student;

select count(name) from student;

这个操作可以理解为, 先执行select* 操作去查询所有数据, 然后去计算select* 查询结果有多少行.例如上面的select count(id) 和 select count(*) 操作, 但是如果数据里面遇到的记录为NULL, 那么这一行记录是不会被count计算进去的, 就例如select count(name) .

如果你不想将NULL指定为有效行数, 那么就可以直接使用count来指定列查询, 如果你认为NULL也为有效数据, 那么你可以使用count(*) 来进行查询.

SUM

sum求和只是针对数字列有效, 如果是字符串或者其他类型的列, 将无法计算

例如我们创建这样的一张表:

INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30)
(8,'鲁班七号', null, null, null);

使用sum函数来对所有记录的chinese 字段进行求和:

select sum(chinese) from exam_result;

说明: 如果里面数据为null, sum会自动跳过结果为null的数据.

如果对name这种非数字类型进行sum操作:

select sum(name) from exam_result;

结果显示:

1 row in set, 8 warnings (0.00 sec)

使用show warnings 来查看警告内容:

show warnings;

Truncated incorrect DOUBLE value : 当前类型向double类型转换失败.

AVG

计算平均数据,计算出来的数据为double类型

还是这个数据表:

对其chinese 的成绩进行AVG计算平均分操作:

select avg(chinese) from exam_result;

也可以搭配表达式使用: 例如求总分的成绩 :

select avg( chinese + math + english) from exam_result;

也可以使用as来对其命名:

select avg( chinese + math + english) as totalAVG from exam_result;

下面max和min的例子同样使用如下表格:

MAX

查询表中指定列的最大值, 例如查询chinese的最大值:

select max(chinese) from exam_result;

MIN

同MAX, 这里不做过多介绍.

GROUP BY

通常搭配聚合函数,select 语句中使用group by 可以对指定列进行分组查询,

例如这里创建一个员工表,如下:

create table emp (id int, name varchar(20), role varchar(20), salary int);

插入七条记录:

如何求出每个岗位的平均工资?

这个时候就需要使用分组查询了, 也就是group by操作.

使用group by语句, 可以让指定的列里面, 相同的值分到同一个组当中:

要求每个岗位的平均工资, 就需要指定岗位的类型来进行分组

然后对其进行平均工资计算:

select role, avg(salary) from emp group by role;

分组前查询

使用where.

例如求每个岗位的平均值, 但是不包括"张三".

select role, avg(salary) from emp where name != "张三" group by role;

2.分组后查询

使用having关键字

例如, 求所有岗位的平均薪资, 但是不包括老板:

select role, avg(salary) from emp group by having role != "老板";

3.同时在分组前分组后筛选

使用where在分组前筛选, 同时使用having关键字在分组后筛选.

select role, avg(salary) from emp where name != "张三" group by role having role != "老板";

联合查询

也成为多表查询, 这种多表查询依赖于一个笛卡尔积的前提.

笛卡尔积

定义:设A,B为集合, 用A中元素为第一元素, B中的元素为第二元素, 构成有序数对, 所有这样的有序数对组成的集合称作A和B的笛卡尔积,记作AXB = {<x,y>| x $\text{[math]}$ A ^ y $\text{[math]}$ B}.

例如 A = {a, b}, B = {0, 1, 2}.

AXB = {<a,0>,<a,1>,<a,2>,<b,0>,<b,1>,<,2>}.

有序数对: 由两个元素x和y按一定的顺序成的二元组成为一个有序对, 记作<x,y>.

存在两张表:

studentList (studentID, name, class)
classList (classID, className )

里面存在着如上图的数据

让两张表进行笛卡尔积运行算,形成一张新表如下:

resultList (studentID, name, classID, className);

新的表的数据如图: 班级表里面的数据依次对学生表里面的每一个记录进行组合的一个排列

这份结果表, 就是学生表和班级表的笛卡尔积结果.

笛卡尔积出来的表格有很多数据,但并不是所有的数据都是有效数据, 就比如说上表数据中的记录:

这些记录的左边的班级ID和右边的班级ID不相同, 是没有意义的数据.

当我们去掉这些数据之后, 笛卡尔积的结果表就是两个表合并的结果.

要去掉这些数据, 可以增加where条件语句, 来过滤掉这些没有意义的数据.

联合查询

下面创建和插入以下表格和数据:

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 
('计算机系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);

四张表的记录显示如下:

classes

student

course

score

各表之间的关联如下:

student和classes >一对多

student和course > 多对多关系

score相当于一张关联表, 起着关联student 和 course 的作用, 从图中可以明显看出来

我们需要实现以下功能:

查询"许仙"同学的成绩和其他相关信息

由于许仙同学的成绩和相关信息,存在于两张中表,就需要联合查询了, 首先我们需要score和student两张表进行笛卡尔积运算, 然后过滤掉无效信息

1.求两张表的笛卡尔积:

select * from student, score;

2.过滤无效数据:

去掉id 和student_id不匹配的情况,使用where语句

select * from student, score where student.id = score.student_id;

(此处通过表名.列名的形式来指定列, 因为如果两张表中有重名的列则会存在指定不明确的问题)

得到下表,一张学生id能匹配正确的表格.

在过滤掉无效信息后, 还需要指定为许仙同学的数据:

select * from student, score where student.id = score.student_id and name = "许仙";

于是就得到了我们所需要的许仙的成绩和相关信息表了.

也可以过滤掉除了name和score外的其他信息:

3.联合合并聚合查询

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

首先求笛卡尔积, 然后过滤掉无效数据

select * from student, score where student.id = score.student_id;

这个里面已经有了学神搞得各科成绩,但是我们需要的是总成绩 :

所以我们需要根据学生的名字或者学号进行分组(group by), 然后再针对分组进行求和:

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

JOIN ON

有以下语句

select * from student, score;

此操作为获取student和score的笛卡尔积表, student和score中间用","逗号连接, 但是除了用逗号, 还可以使用关键字join, 也可以完成笛卡尔积表的操作:

select * from student join score;

不同于使用逗号获取的笛卡尔积表, join的条件筛选需要使用on关键字,而不是where

select student.name, score.score from student join score on student.id = score.student_id and student.name = "许仙";

以上这两种方法效果是一样的

内外连接

我们上面所说的例子, 都是两张表的记录都能一一对应, 但是在实际操作当中, 肯定存在数据量不一样的两张表, 或者是记录不能一一对应的两张表. 这种情况进行笛卡尔积, 没有一一对应的数据和其他数据进行笛卡尔积计算, 就必定只能产生无效数据,或者没有意义的数据, 这个时候不仅浪费空间,而且浪费性能,所以就要指定一个表为主的显示模式.

例如:

内连接

如果两张表的记录不一一对应, 这个时候使用内连接来连接两个表,两张表里面都有相互一一对应的记录的记录进行笛卡尔积.如上图:将会忽略 student中id = 4, 和score表中student_id = 3 的记录.

内连接

select * from 表1, 表2 where 条件1 and 条件2;

语法如上

外连接

外连接分两种, 一种是左外连接, 一种是右外连接.顾名思义, 左外连接就是以左表为基础, 与表2进行笛卡尔积计算. 如果表1中的数据没有与表2中与之对应的, 那么这个数据任然后存在于生成的临时表, 对于找不到的字段记录, 一般使用NULL填写:

例如: 存在两张表

使用左连接来进行笛卡尔积:

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

右连接同理:

select name, score from student right join score on student.id = score.student_id;

多表连接

使用join语法:

select * from 表1 join 表2 on 条件1 join 表3 on 条件2......;

执行逻辑为: 表1 先和表2 进行笛卡尔积计算然后根据条件1筛选有效数据, 随后让表1和表2 生成的笛卡尔积表和表3进行笛卡尔积, 然后一句条件2进行有效数据的筛选. 根据情况进行左外连接或者右外连接或者是内连接

自连接

自连接是一种特殊情况的特殊操作, 如何理解? 也就是一张表, 自己和自己进行笛卡尔积表计算. 自己和自己笛卡尔积有什么用处呢??

首先存在一张表:

将这张表进行自连接:

select * from student, student;

如果直接这样写, 会出现warnings:

因为生成的临时表, 系统不知道对应的字段为那个表的,需要使用 as 来指定这两张表的别名加以区分, 同时过滤掉无效数据:

select * from student as student1, student as student2 where student1.id = student2.id;

此时想要进行行与行之间的条件查询, 现在转到一个列来了

SQL中的条件查询, 都是指定列进行查询, 他没办法进行行与行之间的条件查询

子查询

多个查询语句合并成一个.

单行子查询

例如有student表, 如下:

想查询许仙的同班同学, 该如何查询? 首先使用条件查询找到许仙所在的班级, 然后通过班级搜索在这个班级里面的同学:

select class_id from student where name = "许仙";

得知许仙同学的班级号为1后, 开始查看class_id 为1 的班级, 来搜寻他的同班同学:

select name, class_id from student where class_id = 1;

子查询该如何进行? 也就是将这个class_id = 1 的这个1 替换为上面第一个的查询语句:

select name, class_id from student where class_id = (select class_id from student where name = "许仙");

这种子查询虽然语言简便, 但是不方便理解, 当有多层次的子查询嵌套的时候, 非常影响SQL语句的可行性.

多行子查询

有以下两张表:

要求: 查询"语文" 和"英文"课程的成绩信息

首先查询英文和语文的课程id,

select id from course where name = "英文" or name = "语文";

2.根据课程id到student表中去查询成绩

select * from score where course_id in(4,6);

使用多行子查询, 合并为一句:

select * from score where course_id in (select id from course where name = "语文" or name = "英文");

具有相同的效果

合并查询

在实际操作当中, 为了合并多个select语句的执行结果,可以使用集合操作符union.

有如下表:

要求查询:课程id 小于3 或者课程名称为英文的记录

union

select * from course where id < 3 union select * from course where name = "英文";

union all

union all 会去重

联合查询的一般步骤

分析清楚需求,所设计到的信息都在哪些表中

针对分析出来的这些表,来进行笛卡尔积

根据实际情况(关联字段), 筛选出有效数据

进一步加强条件查询, 查询更加精确的记录

针对需要对表进行简化

注意事项

在实际操作当中我们应该慎重使用

联合查询的基础是笛卡尔积,笛卡尔积是把两个表进行重组, 例如第一张表的记录量为20, 第二张表记录量为30, 那么这两张表格进行笛卡尔积计算, 那么新的笛卡尔积表的记录量就会达到20 x 30 = 600 条, 因为其中还包含了许多无效数据需要我们去主动过滤, 但是当数据量来到千万, 甚至几亿的级别的时候, 那么笛卡尔积的操作就会产生大量的数据, 这些数据可能是当前外存无法存储的.

数据量大的时候, 笛卡尔积的开销是非常大的, 会对机器的性能造成很大的影响.