复合查询 复合函数
AVG
COUNT
SUM
MAX
MIN
分组查询
GROUP BY
HAVING
联合查询
内连接
外连接
左连接
右连接
自连接
子查询
合并查询
UNION
UNION ALL
复合查询
复合函数
通常对于统计数量,计算平均值,寻找最大值等操作,都可以通过复合函数来完成
MySQL给出了以下五种聚合函数
- AVG():返回查询到的数据的平均值
- COUNT():返回查询到的数据的数量
- SUM():返回查询到的数据的总和
- MAX():返回查询到的数据的最大值
- MIN():返回查询到的数据的最小值
注意:复合函数仅能作用于数字
下面对这个表进行案例演示
+------+-----------+------+---------------------+---------+-------+---------+
| id | name | age | birth | chinese | math | english |
+------+-----------+------+---------------------+---------+-------+---------+
| 3 | 孙悟空 | 22 | 1998-02-28 15:08:11 | 98.00 | 96.50 | 92.03 |
| 2 | 猪八戒 | 20 | NULL | 60.00 | 62.50 | 58.00 |
| 1 | 唐僧 | 16 | 2004-10-01 07:21:03 | 80.06 | 82.50 | 86.00 |
| 5 | 沙悟净 | 21 | 1999-05-08 21:10:18 | 72.00 | 75.08 | 70.03 |
| 4 | 白龙马 | 17 | NULL | 31.00 | 34.50 | 34.06 |
+------+-----------+------+---------------------+---------+-------+---------+
AVG
返回查询到的数据的平均值
// 计算所有学生语文的平均成绩
MariaDB [study]> select avg(chinese) from student;
+--------------+
| avg(chinese) |
+--------------+
| 68.212000 |
+--------------+
COUNT
返回查询到的数据的数量
// 统计有多少个学生
MariaDB [study]> select count(*) from student;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
SUM
返回查询到的数据的总和
// 计算所有学生数学成绩的总和
MariaDB [study]> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
| 351.08 |
+-----------+
MAX
返回查询到的数据的最大值
// 查询年龄最大的学生
MariaDB [study]> select max(age) from student;
+----------+
| max(age) |
+----------+
| 22 |
+----------+
MIN
返回查询到的数据的最小值
// 找出总分最低的
MariaDB [study]> select min(chinese + math + english) as total from student;
+-------+
| total |
+-------+
| 99.56 |
+-------+
分组查询
分组查询即使用GROUP BY子句对指定列进行分组查询
注意:SELECT指定的字段必须是“分组依据字段”,其他字段若想出现在SELECT 中则必须包含在聚合函数中。
GROUP BY
语法
SELECT 查询项 FROM 表名 GROUP BY 分组依据;
// 建立一个职员表
create table emp(
id int primary key auto_increment comment '工号',
name varchar(20) not null comment '职员姓名',
role varchar(20) not null comment '职位',
salary numeric(11,2) comment '月薪'
);
// 插入一些数据
insert into emp(name, role, salary) values
('孙悟空','实习生', 2500.20),
('猪八戒','普通职员', 7000.99),
('沙悟净','普通职员', 6000.11),
('唐僧','普通职员', 5833.5),
('白龙马','实习生', 2700.33),
('如来佛祖','经理', 12000.66);
接下来按照职位进行分组,查询各职位的平均月薪、最高月薪和最低月薪
MariaDB [study]> select role, max(salary), min(salary), avg(salary) from emp group by role;
+--------------+-------------+-------------+--------------+
| role | max(salary) | min(salary) | avg(salary) |
+--------------+-------------+-------------+--------------+
| 实习生 | 2700.33 | 2500.20 | 2600.265000 |
| 普通职员 | 7000.99 | 5833.50 | 6278.200000 |
| 经理 | 12000.66 | 12000.66 | 12000.660000 |
+--------------+-------------+-------------+--------------+
HAVING
如果使用GROUP BY进行分组,如果需要使用条件判断来过滤数据,就不能再使用WHERE,而是要使用HAVING
语法
SELECT 查询项 FROM 表名 GROUP BY 分组依据 HAVING 条件;
// 查询平均薪资大于6000的职位
MariaDB [study]> select role, avg(salary) from emp group by role having avg(salary) > 6000;
+--------------+--------------+
| role | avg(salary) |
+--------------+--------------+
| 普通职员 | 6278.200000 |
| 经理 | 12000.660000 |
+--------------+--------------+
联合查询
为了方便用例,首先建立学生表、班级表、课程表、成绩表
drop table if exists course;
create table course(
id int primary key auto_increment,
name varchar(8)
);
drop table if exists classes;
create table classes (
id int primary key auto_increment,
name varchar(20),
`desc` varchar(100)
);
drop table if exists student;
create table student(
id int primary key auto_increment,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(20),
classes_id int,
foreign key (classes_id) references classes(id)
);
drop table if exists score;
create table score (
id int primary key auto_increment,
score decimal(3, 1),
student_id int,
course_id int,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);
再插入一些数据方便后面的操作使用
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);
内连接
内连接即查找两个表中的交集,找到两个表中同时符合条件的数据,进行连接。
语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
// 查找白素贞的成绩
MariaDB [study]> select
-> stu.name, sco.score
-> from
-> student stu inner join score sco
-> on
-> stu.id = sco.student_id and stu.name = "白素贞";
+-----------+-------+
| name | score |
+-----------+-------+
| 白素贞 | 33.0 |
| 白素贞 | 68.0 |
| 白素贞 | 99.0 |
+-----------+-------+
3 rows in set (0.00 sec)
// 使用where条件查询也可以
MariaDB [study]> select
-> stu.name, sco.score
-> from
-> student stu, score sco
-> where
-> stu.id = sco.student_id and stu.name = "白素贞";
+-----------+-------+
| name | score |
+-----------+-------+
| 白素贞 | 33.0 |
| 白素贞 | 68.0 |
| 白素贞 | 99.0 |
+-----------+-------+
3 rows in set (0.00 sec)
外连接
外连接又分左外连接和右外连接
简单来说就是,如果左边的表完全显示就是左连接,右边的表完全显示就是右连接
左连接
对于左连接,以左表的数据为基准,在右表中查找符合条件的数据,找不到的以也会NULL展示。
语法
select 字段名 from 表名1 left join 表名2 on 连接条件;
示例
// 左连接,以学生表为基准,查找成绩表中所有学生的成绩
MariaDB [study]> select *
-> from
-> student stu left join score sco
-> on
-> stu.id = sco.student_id;
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | id | score | student_id | course_id |
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 |
| 3 | 391 | 白素贞 | NULL | 1 | 7 | 33.0 | 3 | 1 |
| 3 | 391 | 白素贞 | NULL | 1 | 8 | 68.0 | 3 | 3 |
| 3 | 391 | 白素贞 | NULL | 1 | 9 | 99.0 | 3 | 5 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 |
| 5 | 54 | 不想毕业 | NULL | 1 | 14 | 81.0 | 5 | 1 |
| 5 | 54 | 不想毕业 | NULL | 1 | 15 | 37.0 | 5 | 5 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 |
| 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 |
| 8 | 9527 | 老外学中文 | foreigner@qq.com | 2 | NULL | NULL | NULL | NULL |
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
21 rows in set (0.00 sec)
可以看到,此时进行左连接,以学生表为基准在成绩表中查找,所以对于成绩表中不存在的学生,会用NULL表示而不是直接忽略
右连接
对于右连接,以右表的数据为基准,在左表中查找符合条件的数据,找不到的以也会NULL展示。
语法
select 字段 from 表名1 right join 表名2 on 连接条件;
示例
// 右连接,以成绩表为基准,查找学生表中有成绩的学生
MariaDB [study]> select *
-> from
-> student stu right join score sco
-> on
-> stu.id = sco.student_id;
+------+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| id | sn | name | qq_mail | classes_id | id | score | student_id | course_id |
+------+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 |
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 |
| 3 | 391 | 白素贞 | NULL | 1 | 7 | 33.0 | 3 | 1 |
| 3 | 391 | 白素贞 | NULL | 1 | 8 | 68.0 | 3 | 3 |
| 3 | 391 | 白素贞 | NULL | 1 | 9 | 99.0 | 3 | 5 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 |
| 5 | 54 | 不想毕业 | NULL | 1 | 14 | 81.0 | 5 | 1 |
| 5 | 54 | 不想毕业 | NULL | 1 | 15 | 37.0 | 5 | 5 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 |
| 6 | 51234 | 好好说话 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 |
| 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 |
| 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 |
+------+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
20 rows in set (0.00 sec)
可以看到,此时进行右连接,以成绩表为基准在学生表中查找,所以对于学生表中不存在的学生,会用NULL表示而不是直接忽略
自连接
自连接即将自己的表进行连接,需要对表名进行别名显示
例如要查询本班学生中所有JAVA成绩比计算机原理成绩高的成绩信息
MariaDB [study]> select *
-> from
-> score s1 join score s2
-> on
-> s1.student_id = s2.student_id
-> and s1.score > s2.score
-> and s1.course_id = 1
-> and s2.course_id = 3;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
| 10 | 67.0 | 4 | 1 | 11 | 23.0 | 4 | 3 |
+----+-------+------------+-----------+----+-------+------------+-----------+
1 row in set (0.00 sec)
子查询
子查询又叫做嵌套查询,其实就是嵌入其他sql语句中的select语句,一般用于查询的条件是另一条语句的结果这一情况。
例如
// 单行子查询,查询与白素贞同学同班的同学。即查询到白素贞所在的班号,再通过班号查询该班学生
MariaDB [study]> select * from student where classes_id =
-> (select classes_id from student where name = "白素贞");
+----+------+-----------------+-----------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+------+-----------------+-----------------+------------+
| 1 | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 |
| 2 | 835 | 菩提老祖 | NULL | 1 |
| 3 | 391 | 白素贞 | NULL | 1 |
| 4 | 31 | 许仙 | xuxian@qq.com | 1 |
| 5 | 54 | 不想毕业 | NULL | 1 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.00 sec)
合并查询
UNION
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行
语法
结果集A
UNION
结果集B;
示例
// 查询id小于3或者课程名字是语文和英语
MariaDB [study]> select * from course where id < 3
-> union
-> select * from course where name in ("语文", "英文");
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中国传统文化 |
| 4 | 语文 |
| 6 | 英文 |
+----+--------------------+
4 rows in set (0.00 sec)
同样的结果也可以通过or语句来得到
MariaDB [study]> select * from course where id < 3 or name in ("语文", "英文");
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中国传统文化 |
| 4 | 语文 |
| 6 | 英文 |
+----+--------------------+
4 rows in set (0.00 sec)
但是OR这个逻辑运算符会忽略索引,所以会导致在海量数据查询中性能会下降很多。
UNION ALL
该操作符用于取得两个结果集的并集。当使用该操作符时,不自动去掉结果集中的重复行
与上面的使用相同,但是不会去掉重复数据