MySQL进阶-增删查改(全网最详细sql教学)-2

MySQL进阶-增删查改(全网最详细sql教学)-1

https://developer.aliyun.com/article/1517119

查

聚合查询

在查询的过程中, 对表的行与行之间进行一定的运算, 这种查询需要依赖SQL提供的库函数. 常见的有统计总数, 计算平均值等操作, 如下表:

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;

1.分组前查询

使用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 = {| xA ^ y B}.

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

AXB = {,,,,,<,2>}.

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

存在两张表:

studentList (studentID, name, class)

classList (classID, className )

里面存在着如上图的数据

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

resultList (studentID, name, classID, className);

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

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

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

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

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

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

MySQL进阶-增删查改(全网最详细sql教学)-3

https://developer.aliyun.com/article/1517152