MySQL高级 第12章练习

发布时间:2019年03月19日作者:yafei浏览次数:2,529

1. 案例一

列出自己的掌门比自己年龄小的人员

更换为大表,进行分析:

2. 案例二

列出所有年龄低于自己门派平均年龄的人员

思路: 先取门派的平均年龄,再跟自己的年龄做对比!

如何优化:

①首先在子查询中,需要根据deptid做groupby操作,因此,需要在deptid上面建立索引;

②因为是inner join,因此会自动将小表作为驱动表,也就是说,分组后的tmp是驱动表,而e1是被驱动表;

③而在e1中,需要查询deptid和age两个字段,因此这两个字段也需要建立索引

 

结果:创建deptid和age的符合索引:   create index idx_deptid_age on emp(deptid,age);

3. 案例三

列出至少有2个年龄大于40岁的成员的门派

思路: 先查询大于40岁的成员,然后按照门派分组,然后再判断至少有2个的门派!

大表优化:

优化:

①两表关联,我们可以考虑将小表作为驱动表。

②group by的字段 id,deptName还可以建立索引: create index idx_id_deptName on dept(id,deptName);

③被驱动表的deptid作为关联字段,可以建立索引:create index idx_deptid on emp(deptid);

create index idx_id_deptname on dept(id,deptName);

4. 案例四

至少有2位非掌门人成员的门派

select d2.deptName from t_emp e inner join t_dept d2 on e.deptid=d2.id

 left join t_dept d on e.id=d.ceo

 where d.id is null and e.deptid is not null

 group by d2.deptName,d2.id

 having count(*)>=2;

切换大表:

explain select d2.deptName from emp e inner join dept d2 on e.deptid=d2.id

 left join dept d on e.id=d.ceo

 where d.id is null and e.deptid is not null

 group by d2.deptName,d2.id

 having count(*)>=2;

没有索引的情况下:

优化分析: 三个表关联,然后做group by分组!

①group by 的字段,可以加上索引:create index idx_deptname_id on dept(deptName,id);

②可以将部门表作为驱动表

③第一次join时,e表作为被驱动表,可以将deptid设置索引:create index idx_deptid on emp(deptid);

④最有一次join中,使用了dept表作为被驱动表,查询ceo字段,因此可以在ceo上面建立索引

create index idx_ceo on dept(ceo);

5. 案例五

列出全部人员,并增加一列备注“是否为掌门”,如果是掌门人显示是,不是掌门人显示否

6. 案例六

列出全部门派,并增加一列备注“老鸟or菜鸟”,若门派的平均值年龄>40显示“老鸟”,否则显示“菜鸟”

 

思路: 先从emp表求出,各门派的平均年龄,分组,然后在关联dept表,使用if函数进行判断!

优化:

①使用dept作为驱动表

②在dept上建立deptName和id的索引:create index idx_deptName_id on dept(deptName,id);

③在emp上建立deptid字段的索引: create index index_deptid on emp(deptid);

7. 案例七

显示每个门派年龄最大的人

思路:先查询emp表,求出每个门派年龄最大的人,并按照deptid分组;然后再次关联emp表,关联其他的信息!

优化思路:

①子查询中,emp表根据deptid进行分组,因此可以建立deptid字段的索引;

②inner join查询中,关联了age和deptid,因此可以在deptid,age字段建立索引

create index idx_deptid_age on emp(deptid,age);

 


上一篇:
下一篇:
相关课程

java培训 大数据培训 前端培训 UI/UE设计培训

关于尚硅谷
教育理念
名师团队
学员心声
资源下载
视频下载
资料下载
工具下载
加入我们
招聘岗位
岗位介绍
招贤纳师
联系我们
全国统一咨询电话:010-56253825
地址:北京市昌平区宏福科技园2号楼3层(北京校区)

深圳市宝安区西部硅谷大厦B座C区一层(深圳校区)

上海市松江区谷阳北路166号大江商厦6层(上海校区)

武汉市东湖高新开发区东湖网谷(武汉校区)

西安市高新区和发智能大厦(西安校区)