MySQL单表查询、聚合函数、多表连接查询、子查询
2021/7/9 2:08:43
本文主要是介绍MySQL单表查询、聚合函数、多表连接查询、子查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
文章目录
- 单表查询
- 聚合函数
- 多表查询（select查询的是虚拟表）
- 连接查询
- 多表连接查询案例
- 多表连接查询之物理表和虚拟表链接
- 子查询
- 带in关键字的子查询
- 带ANY关键字的子查询
- 带ALL关键字的子查询
- 带比较运算符的子查询
- 带EXISTS关键字的子查询
- 逻辑梳理图
- 作业
单表查询
select distinct 字段1,字段2,... from 库.表 where 过滤条件 group by 分组字段 having 过滤条件 order by 排序字段 limit 条数
聚合函数
首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 指定表中的字段名，将指定字段名的数据进行分类
分组的意义在于指定表中的字段，将表中数据分类，以组的形式进行数据的操作
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
# 聚合函数：聚合函数只能在分组之后使用，聚合的是组的内容，若是没有分组，则默认一组
# 关键字====>每个 平均 最高 最低
# 聚合函数
max 最大
min 最小
sum 求和
count 查数
avg 求平均值
group_concat
# group by分组
分组实际应用场景 分组应用场景非常的多
男女比例
部门平均薪资
国家之间数据统计
#查看分组
mysql> select @@global.sql_mode;
+--------------------+
| @@global.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
# 设置分组
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
# 建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum("male","female") not null default "male",
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);
# 插入数据
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
按照部门分组
select * from employee group by post;
# 案例
取出每个部门的平均薪资
select post,avg(salary) from employee group by post;
# select 只能看到分组字段以及聚合的结果
# 案例
取出每个部门的员工数
select post,count(id) from employee group by post;
求男人数与女人数
select sex,count(id) from employee group by sex;
求年龄在20岁以上的男人数与女人数
select sex,count(id) from employee where age > 20 group by sex;
求每个部门20岁以上人的平均薪资
select post,avg(salary) from employee where age > 20 group by post;
用group_concat可以看到name和age的值
select post,group_concat(name,":",age) from employee group by post;
聚合函数整体使用
select post,avg(salary),max(salary),min(age),count(id),sum(age) from employee group by post;
# 分组注意事项
关键字where和group by同时出现的时候group by必须在where的后面 （聚合函数只能在分组之后使用）
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select max(salary) from employee; # 不分组 默认整体就是一组
# having分组之后的筛选条件
having的语法跟where是一致的
只不过having是再分组之后进行的过滤操作
即having是可以直接使用聚合函数的
# 案例
查出平均薪资在10000以上的部门
select post,avg(salary) from employee group by post having avg(salary) > 10000;
查出部门内男员工平均工资在3000以上的部门
select post,avg(salary) from employee where sex="male" group by post
having avg(salary) > 3000;
# order by 排序
select * from employee order by age;
select * from employee order by age asc; # 同上命令的意思一样（升序）
select * from employee order by age desc; # 降序
order by 默认是升序（从低到高） asc 该asc可以省略不写
也可以修改为降序（从高到低） desc
# 案例
查出部门内男员工平均工资在3000以上的部门升序排序
select post,avg(salary) from employee where sex="male" group by post
having avg(salary) > 3000
order by avg(salary);
# 先按age字段排，在按id字段排（在age分不出胜负的时候按id子段排）
select * from employee order by age asc,id desc;
# limit限制展示条数
select * from employee; # 该命令是将所有的数据都展示出来
针对数据过多的情况，我们通常都做分页处理
select * from employee limit 3; # 只展示emp表中的3条数据
select * from employee limit 0,5; # 展示1-5的数据
select * from employee limit 5,5; # 展示6-10的数据
第一个参数是起始位置
第二个参数是展示条数（分页展示）
# 取出薪资最高那个人的信息
select * from employee order by salary desc limit 1;
单表查询详解
多表查询（select查询的是虚拟表）
- 多表连接查询
- 复合条件连接查询
- 子查询
# 准备表
mysql> create database db4;
mysql> use db4;
#建部门表
create table department(
id int,
name varchar(20)
);
#建员工表
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
#查看表结构和数据
desc department;
desc employee;
select * from department;
select * from employee;
连接查询
#1 交叉连接：不适用任何匹配条件。生成笛卡尔积
#两张表拼接成一张大表(虚拟表)
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0),(b, 1), (b, 2)}
select * from employee,department;
#2 内连接：只保留有对应关系的记录 （常用）
# inner join 将表进行拼接，找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
select * from employee inner join department on employee.dep_id = department.id;
# left join:左连接 将表进行拼接，以左表为准，优先显示左表全部记录
select * from employee left join department on employee.dep_id = department.id;
# right join 右连接：将表进行拼接，以右表为准，优先显示右表全部记录
select * from employee right join department on employee.dep_id = department.id;
# 全外连接： union （了解）
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
多表连接查询案例
#分析步骤： 1）#先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） 2）#再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #foreign key的对应关系 #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
# 多表合成一张虚拟表 然后根据单表查询 可以用 where group by ......
# 建作者表
create table author(
id int primary key auto_increment,
name varchar(20)
);
# 建作者书表
create table book(
id int primary key auto_increment,
name varchar(20)
);
#建关联表
create table author2book(
id int primary key auto_increment,
author_id int not null,
book_id int not null,
foreign key(author_id) references author(id) on delete cascade on update cascade,
foreign key(book_id) references book(id) on delete cascade on update cascade
);
# 插入数据
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
insert into book(name) values
('九阳神功'),
('九阴真经'),
('九阴白骨爪'),
('独孤九剑'),
('降龙十巴掌'),
('葵花宝典')
;
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
# 两张表拼接成一张表查询
select * from author inner join author2book
on author.id = author2book.author_id
inner join book
on author2book.book_id = book.id;
+----+---------+----+-----------+---------+----+-----------------+
| id | name | id | author_id | book_id | id | name |
+----+---------+----+-----------+---------+----+-----------------+
| 1 | egon | 1 | 1 | 1 | 1 | 九阳神功 |
| 1 | egon | 2 | 1 | 2 | 2 | 九阴真经 |
| 1 | egon | 3 | 1 | 3 | 3 | 九阴白骨爪 |
| 1 | egon | 4 | 1 | 4 | 4 | 独孤九剑 |
| 1 | egon | 5 | 1 | 5 | 5 | 降龙十巴掌 |
| 1 | egon | 6 | 1 | 6 | 6 | 葵花宝典 |
| 2 | alex | 7 | 2 | 1 | 1 | 九阳神功 |
| 2 | alex | 8 | 2 | 6 | 6 | 葵花宝典 |
| 3 | yuanhao | 9 | 3 | 4 | 4 | 独孤九剑 |
| 3 | yuanhao | 10 | 3 | 5 | 5 | 降龙十巴掌 |
| 3 | yuanhao | 11 | 3 | 6 | 6 | 葵花宝典 |
| 4 | wpq | 12 | 4 | 1 | 1 | 九阳神功 |
+----+---------+----+-----------+---------+----+-----------------+
# 查询技术部的员工姓名
select employee.name from employee inner join department on employee.dep_id = department.id
where department.name = "技术";
# 查询平均年龄大于25岁的部门名
select department.name,avg(age) from employee inner join department on employee.dep_id = department.id
group by department.name
having avg(age) > 25
;
多表连接查询之物理表和虚拟表链接
use db3; select * from employee; # 案例 查询每个部门最新入职的哪些员工？ # 插入数据 insert employee values(19,"tom","male",19,"2017-03-01","老男孩驻沙河办事处外交大使 ",3000); #查看整表 mysql> select * from employee; #找到最大时间（最新时间） mysql> select post,max(hire_date) from employee group by post; select * from employee inner join (select post,max(hire_date) from employee group by post) as t1 on employee.post = t1.post #as 定义别名 select employee.name,employee.hire_date,employee.post,t1.m_d from employee inner join (select post,max(hire_date) as m_d from employee group by post) as t1 on employee.post = t1.post where employee.hire_date = t1.m_d; +--------+------------+------------------------------------------+------------+ | name | hire_date | post | m_d | +--------+------------+------------------------------------------+------------+ | egon | 2017-03-01 | 老男孩驻沙河办事处外交大使 | 2017-03-01 | | alex | 2015-03-02 | teacher | 2015-03-02 | | 格格 | 2017-01-27 | sale | 2017-01-27 | | 张野 | 2016-03-11 | operation | 2016-03-11 | | tom | 2017-03-01 | 老男孩驻沙河办事处外交大使 | 2017-03-01 | +--------+------------+------------------------------------------+------------+
子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等 #案例 use db4; mysql> select id from department where name = "技术" # 子查询 mysql> select * from employee where dep_id = (select id from department where name = "技术""); #查询平均年龄在25岁以上的部门名 select dep_id from employee group by dep_id having avg(age) > 25; select name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); #查看不足1人的部门名(子查询得到的是有人的部门id) mysql> select distinct dep_id from employee; #先查有人的表 select name from department where id not in (select distinct dep_id from employee); #查询大于所有人平均年龄的员工名与年龄 select avg(age) from employee; #先查所有人的平均年龄 select name,age from employee where age > (select avg(age) from employee); #查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from employee t1 inner join (select dep_id,avg(age) avg_age from employee group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
带in关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理，如下 mysql> select * from emp; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | | 7 | xxx | male | 19 | NULL | +----+------------+--------+------+--------+ 7 rows in set (0.00 sec) mysql> select * from dep; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) # 子查询中存在null mysql> select * from dep where id not in (select distinct dep_id from emp); Empty set (0.00 sec) # 解决方案如下 mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null); +------+--------+ | id | name | +------+--------+ | 203 | 运营 | +------+--------+ 1 row in set (0.00 sec) ！！！注意not in
带ANY关键字的子查询
#在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。 # ANY 和 IN 运算符不同之处1 ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义 例如：使用 IN 和使用 ANY运算符得到的结果是一致的 select * from employee where salary = any ( select max(salary) from employee group by depart_id); select * from employee where salary in ( select max(salary) from employee group by depart_id); 结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符 # ANY和 IN 运算符不同之处2 ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的 SELECT * FROM T_Book WHERE FYearPublished < ANY (2001, 2003, 2005)
带ALL关键字的子查询
# all同any类似，只不过all表示的是所有，any表示任一 查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家 select * from employee where salary > all ( select avg(salary) from employee group by depart_id); 查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众 select * from employee where salary < all ( select avg(salary) from employee group by depart_id); 查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工select * from employee where salary < any ( select avg(salary) from employee group by depart_id); 查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工 select * from employee where salary > any ( select avg(salary) from employee group by depart_id);
带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<> #查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec) #查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=203，Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee
-> where exists
-> (select id from department where id=204);
Empty set (0.00 sec)
逻辑梳理图
作业
作业详细连接
这篇关于MySQL单表查询、聚合函数、多表连接查询、子查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-20部署MySQL集群入门：新手必读指南
- 2024-11-20部署MySQL集群教程：初学者指南
- 2024-11-20部署MySQL集群项目实战：新手教程
- 2024-11-20部署MySQL集群资料：新手入门教程
- 2024-11-20MySQL集群部署教程：入门级详解
- 2024-11-20MySQL集群教程：入门与实践指南
- 2024-11-20部署MySQL集群教程：新手入门指南
- 2024-11-20MySQL读写分离教程：轻松入门
- 2024-11-20部署MySQL集群入门：一步一步搭建你的数据库集群
- 2024-11-19部署MySQL集群学习：入门教程
