聚合查询 分组查询 链表 多表

group by 使用

 

select * from 表名 group by 分组字段名;

 

 

 

严格模式的修改：

1.查看严格模式

 

show variables like "sql_mode";

 

 

 

2.修改严格模式

set global sql_mode'ONLY_FULL_GROUP_BY’；

3.退出重进

exit   #退出不关闭
mysql -uroot -p;  #进入
use 库名; #切到库下

 

再次查看严格模式，已经改好了

show variables like "sql_mode";

 

我们查看分组 也相应的改变

 

 

使用示例：

每隔部门都有多少个人

 

select depart_id,count(id) from emp group by depart_id;

having与where本质区别就是在于having是在分组之后发生过滤，可以使用聚合函数

示例:查询最高工资

select max(salary) from emp where max(salary) > 100000;
====》ERROR 1111 (HY000): Invalid use of group function
#虽然没有group by 但是这里已经把一整个表作为一个组 所以使用where会报错
这里应该使用hanving

select max(salary) from emp having max(salary) > 100000;

 

 

1 聚合查询（聚合函数：最大，最小，和，平均，总个数）

复制代码
from django.db.models import Avg,Max,Min,Count,Sum
#1 计算所有图书的平均价格
# aggregate结束，已经不是queryset对象了
# book=models.Book.objects.all().aggregate(Avg('price'))
# 起别名
# book=models.Book.objects.all().aggregate(avg=Avg('price'))
#2 计算总图书数
# book = models.Book.objects.all().aggregate(count=Count('id'))
# 3 计算最低价格的图书
# book = models.Book.objects.all().aggregate(min=Min('price'))
# 4 计算最大价格图书
# book = models.Book.objects.all().aggregate(max=Max('price'))
# print(book)
复制代码

 

 

2 分组查询
'''
查询每一个部门名称以及对应的员工数
book:
id name price publish
1 金品 11.2 1
2 西游 14.2 2
3 东游 16.2 2
4 北邮 19.2 3

 

复制代码
 '''
    # 示例一：查询每一个出版社id，以及出书平均价格
    # select publish_id,avg(price) from app01_book group by publish_id;
    # annotate


    #  annotate() 内写聚合函数
    #  values在前表示group by的字段
    #  values在后表示取某几个字段
    #  filter在前表示where
    #  filter在后表示having
    # from django.db.models import Avg, Count, Max, Min
    # ret=models.Book.objects.values('publish_id').annotate(avg=Avg('price')).values('publish_id','avg')
    # print(ret)

    # 查询出版社id大于1的出版社id，以及出书平均价格
    #select publish_id,avg(price) from app01_book where publish_id>1 group by publish_id;

    # ret=models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).values('publish_id','avg')
    # print(ret)

    # 查询出版社id大于1的出版社id，以及出书平均价格大于30的
    # select publish_id,avg(price)as aaa from app01_book where publish_id>1 group by publish_id HAVING aaa>30;
    # ret = models.Book.objects.values('publish_id').filter(publish_id__gt=1).annotate(avg=Avg('price')).filter(avg__gt=30).values(
    #     'publish_id', 'avg')
    # print(ret)


    ## 查询每一个出版社出版的书籍个数
    # pk 代指主键

    # ret=models.Book.objects.get(pk=1)
    # print(ret.name)
    # ret=models.Publish.objects.values('pk').annotate(count=Count('book__id')).values('name','count')
    # print(ret)
    # 如果没有指定group by的字段，默认就用基表（Publish）主键字段作为group by的字段
    # ret=models.Publish.objects.annotate(count=Count('book__id')).values('name','count')
    # print(ret)

    # 另一种方式实现
    # ret=models.Book.objects.values('publish').annotate(count=Count('id')).values('publish__name','count')
    # print(ret)


    #查询每个作者的名字，以及出版过书籍的最高价格(建议使用分组的表作为基表)
    # 如果不用分组的表作为基表，数据不完整可能会出现问题
    # ret=models.Author.objects.values('pk').annotate(max=Max('book__price')).values('name','max')

    # ret = models.Author.objects.annotate(max=Max('book__price')).values('name', 'max')

    # ret= models.Book.objects.values('authors__id').annotate(max=Max('price')).values('authors__name','max')
    # print(ret)

    #查询每一个书籍的名称，以及对应的作者个数

    # ret=models.Book.objects.values('pk').annotate(count=Count('authors__id')).values('name','count')
    # ret=models.Book.objects.annotate(count=Count('authors__id')).values('name','count')

    # ret=models.Author.objects.values('book__id').annotate(count=Count('id')).values('book__name','count')
    #
    # print(ret)

    #统计不止一个作者的图书
    # ret=models.Book.objects.values('pk').annotate(count=Count('authors__id')).filter(count__gt=1).values('name','count')
    # ret = models.Author.objects.values('book__id').annotate(count=Count('id')).filter(count__gt=1).values('book__name', 'count')
    # print(ret)

    # 统计价格数大于10元，作者的图书
    ret = models.Book.objects.values('pk').filter(price__gt=10).annotate(count=Count('authors__id')).values('name',
                                                                                                           'count')
    print(ret)

    #统计价格数大于10元，作者个数大于1的图书
    ret = models.Book.objects.values('pk').filter(price__gt=10).annotate(count=Count('authors__id')).filter(count__gt=1).values('name',                                                                                                    'count')
    print(ret)
复制代码

 

 

 

order by排序

select * from emp order by salary;
 select * from emp order by salary desc;
 select * from emp order by age,id desc;
#查询出来的结果按照年龄升序 如果年龄相同就以id作为降序排列，默认是升序(asc)  降序（desc）

 

limit一次展示多少条

select * from emp limit 0,5;显示从第一到第五条
select * from emp limit 5,5; 显示从第六条到第十条

 

 

链表：把多张物理表合并成一张虚拟表，再进行后续查询

内链接（把两张表真正有关系的链接到一起）

on(连表的条件是什么)

select * from emp,dep where emp.dep_id=dep.id;
select dep.name,emp.name from emp inner join dep on emp.dep_id=dep.id
    where dep.name = "技术";
select dep.name,emp.name from emp inner join dep on emp.dep_id=dep.id  where dep.name = "技术";

 

左链接：在内链接的基础上保留左表的记录

select * from emp left join dep on emp.dep_id=dep.id;

 

右链接：在内链接的基础上保留右表的记录

select * from emp right join dep on emp.dep_id=dep.id;

 

 

全外链接：在内链接的基础上保留左右表的记录，结合左链接和右连接

union  #去重
full join 全外链接
full join

select * from emp left join dep on emp.dep_id=dep.id
union  #链接后去重
select * from emp right join dep on emp.dep_id=dep.id;

 

去重：distinct

select distinct dep_id from emp;

 

 

select * from emplovee  group by hire_date  desc limit 1 ;

 

把多张表链接到一起

select * from
(select emp.*,dep.name as dep_name  from emp inner join dep on emp.dep_id = dep.id) as t1
inner join
dep on t1.dep_id = dep.id;

查询部门内最新入职的员工

select * from employee
inner join
(select depart_id,max(hire_date) as maxd from employee group by depart_id) as t1
on employee.depart_id = t1.depart_id
where employee.hire_date = t1.maxd;

 

 

子查询

从一张表中查询出结果，用该结果作为查 询下一张表的过滤条件

内层查询语句的查询结果，可以为外层查询语句提供查询条件

子查询中还可以包括IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字

还可以包含比较运算符

in的用法



select * from emp where age=18 or age=38 or age=28;
select * from emp where age in (18,38,28);


 




 not  in的用法：不支持null


复制代码


查询出有员工的部门，
select * from dep where id in
(select distinct dep_id from emp);

查询出没有员工的部门，
select * from dep where id not in
(select distinct dep_id from emp); # 这样只会显示0,不要用这种

select * from dep where id not in
(select distinct dep_id from emp where dep_id is not null);  
#需要在后面加where 条件+is not null


复制代码


 


 


any的用法：


any和in的区别：in后可以跟子查询和值（例如 in(值1，值2，值3）


any后只能跟子查询，any必须跟比较运算符配合使用，指多个


示例：


复制代码




select * from emp where dep_id in
(select id from dep where name in ("技术","人力资源"));

select * from emp where dep_id = any
(select id from dep where name in ("技术","人力资源"));

select * from emp where dep_id not in
(select id from dep where name in ("技术","人力资源"));

select * from emp where ! (dep_id = any(select id from dep where name in ("技术","人力资源")));



View Code
复制代码


 


all的用法


ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。
ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询，指单个


复制代码




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

查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的
select * from employee where salary < all
(select avg(salary) from employee where depart_id is not null group by depart_id);

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

select * from employee where salary < any
(select avg(salary) from employee where depart_id is not null group by depart_id);



View Code
复制代码


 


exists的用法：


exists是判断存不存在


例如：




select * from 表1 where exists (select * from 表2);




会先取出表1与表2对应 看存不存在，是一个循环，取一次表1也要取一次表2


而在in中 表2只一次，表1去循环即可，所以in的效果高与exists


 


 


 


 not exists的用法：




select * from dep where not exists (select * from emp where 203=emp.dep_id);




 示例：


复制代码




# 查询选修了所有课程的学生id、name:

# 实现方式一：选修了三门课程的学生就是选修了所有课程的学生

select s.id,s.name from student as s inner join student2course as sc
on s.id = sc.sid
group by sc.sid
having count(sc.cid) = (select count(id) from course);

# 实现方式二：找到这样的学生，该学生不存在没有选修过的课程

select * from student as s where not exists (
    select * from course as c not exists (
        select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);


select * from student as s where not exists (
    select * from course as c where not exists (
        select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);



学生记录可以过滤出来，一定是子查询内没有记录

for 学生: # s.id=2
    for 课程: # c.id=1
        for 学生2课程: # sc.sid = 2 and sc.cid = 1
            pass

==================================
for sid in [1,2,3,4]:
    for cid in [1,2,3]:
        (sid,cid)


最外层循环一次

# (1,1)

# (1,2)

# (1,3)

最外层循环二次

# (2,1)

# (2,2)

# (2,3)

最外层循环三次

# (3,1)

# (3,2)

# (3,3)

最外层循环四次

# (4,1)

# (4,2)

# (4,3)

===================================

# 例2、查询没有选择所有课程的学生，即没有全选的学生。=》找出这样的学生，存在没有选修过的课程

select * from student as s where exists (
    select * from course as c where not exists (
        select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);

# 例3、查询一门课也没有选的学生=》找出这样的学生，不存在选修过的课程

select * from student as s where not exists (
    select * from course as c where exists (
        select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);

# 例4、查询至少选修了一门课程的学生=》找出这样的学生，存在选修过课程

select * from student as s where exists (
    select * from course as c where exists (
        select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);



View Code
复制代码


 






            

            
posted @ 
 
朱饱饱 
阅读(25) 
评论(0) 
编辑 
收藏 
举报


        

        
        




    
    

    
    



    

        
    

        

            
                
                
            
        

    



    相关博文：

    

            

                ·
                F和Q查询
            

            

                ·
                django-单表操作
            

            

                ·
                记录相关操作-单表查询、多表查询
            

            

                ·
                Django聚合函数与分组查询
            

            

                ·
                表前期准备、where、group by、having、distinct、order by、limit、正则表达式
            

    





    

    阅读排行：
    

 ·          阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
        

 ·          SQL Server 2025 AI相关能力初探
        

 ·          AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
        

 ·          开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
        

 ·          Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
        



    

    



    



            

        


        
        

    

    

    




    

    
    








  

  
点击右上角即可分享

  微信分享提示

   
    
AI IDE Trae