04、数据库的查询

xian_yu_ben_xian 2026-08-30 128

group by例子

select post,avg(salary) from employee group by post;

取出每个部门的员工数
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;


----------------------------------------
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 asc;
select * from employee order by age desc;

查出部门内男员工平均工资在3000以上的部门且以这个平均工资排序(升序)
select post,avg(salary) from employee where sex="male" group by post having avg(salary) > 3000 order by avg(salary);


----------------------------------------
limit

取出薪资最高的那个人的信息
select * from employee order by salary desc limit 1;

从0开始往后读5条,以此类推读到第20条
select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;
select * from employee limit 15,5;


-----------------------------------
多表查询
内连接

查询技术部的员工的姓名
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;


-----------------------------------------------------
子查询

查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all
(select avg(salary) from employee group by post);

查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all
(select avg(salary) from employee group by post);

查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > all
(select avg(salary) from employee group by post);

Table of Contents

一分组查询：group by

1.1 查询顺序

1、单表查询

select distinct 字段1，字段2，。。。 from 库.表
    where 过滤条件
    group by 分组字段
    having 过滤条件
    order by 排序字段
    limit 条数;
查询顺序：from库.表----->where 过滤条件----->group by 分组字段----->having 过滤条件----->distinct 字段1，字段2，。。。

2、带IN关键字的子查询

1.2 聚合函数

avg平均
max最大值
min最小值
sum和
count总数
group_concat

分组之后：select只能看到分组字段以及聚合的结果

为了避免我们写出有歧义、可能产生不可预知结果的GROUP BY查询，需要开启ONLY_FULL_GROUP_BY这个安全模式

1、查看是否开启ONLY_FULL_GROUP_BY模块
SELECT @@sql_mode;
如果返回结果里没有 ONLY_FULL_GROUP_BY，说明这个功能是关闭的。

2、全局开启，数据库重启失效
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3、永久开启(修改配置文件)

vim /data/3307/my.cnf

----------------------------
[mysqld]

sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

----------------------------

二 having过滤：可以使用聚合函数

select distinct 字段1，字段2，。。。 from 库.表
    where 过滤条件
    group by 分组字段
    having 过滤条件

三 order by与limit

3.1 asc和desc

在使用order by对数据进行排序时，默认是asc–>从小到大

desc则是从大到小

3.2 如何在同一个级别的情况下排序

# 在这个命令里，如果age相同的情况下，会再根据id来进行降序排列
select * from employee order by age asc,id desc;

3.3 limit控制显示的条数

四多表查询

笛卡尔体积：左边一个记录比对右边全表，依次比对

多表查询是将两个表拼起来并查询

select * from employee,department where employee.dep_id = department.id;

4.1 内连接：只保留有对应关系的记录

select * from employee inner join department on employee.dep_id = department.id;

#在表多对多的情况下
select * from author inner join author2book on author.id = author2book.author_id inner join book on author2book_id = book.id;

4.2 左连接：在内连接的基础之上将左边没有对应关系的记录也保留了

select * from employee left join department on employee.dep_id = department.id;

4.3 右连接：在内连接的基础之上将右边没有对应关系的记录也保留了

select * from employee right join department on employee.dep_id = department.id;

4.4 全连接：full join

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;

五物理表与虚拟表的连接

如何将虚拟表变为物理表的形式？
(select id,name from employee) as t1; 

# 查询每个部门最新入职的那些员工
select employee.name,employee.hire_date,employee.post,t1.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;

六子查询的用法

# 先从一张表中查出结果，然后以该结果作为条件去查下一张
select * from employee where dep_id = 
(select id from department where name = "技术");

#查询平均年龄在25岁以上的部门名
select name from department where id in
(select dep_id from employee group by dep_id having avg(age) > 25);

#查看不足1人的部门名(子查询得到的是有人部门的id)
select * from department where id not in
(select distinct dep_id from employee);

# 查询大于所有人平均年龄的员工名与年龄
select * from employee where age >
(select avg(age) from employee);

6.1 关于子查询的关键字

1、带in关键字的子查询，in后面的条件是等于条件中的任意一个即可

2、带any关键字的子查询，通常用于比较，能满足比较any后面的条件中的任意一个字段即可

3、带all关键字的子查询，通常用于比较，能满足比较all后面的条件中的所有字段

注意：any和all只能用在子查询之中

暂无评论

发表评论取消回复

OωO表情