Mysql学习04

– mysqldump -u root -p school tb_student > school.sql
– DQL —> select —> index（索引）
– constant —> 常量

– 执行计划
explain select * from tb_emp where eno=7800;

– 创建索引
– 前缀索引
create unique index idx_emp_ename on tb_emp (ename(1));

– 复合索引
create index idx_emp on tb_emp (ename, job);

– 删除索引
drop index idx_emp_ename on tb_emp;

explain select * from tb_emp where ename=‘张三丰’;

– 覆盖查询（直接在索引上就可以获得所需要的数据，不需要回表查询）
explain select eno, ename, job from tb_emp where ename=‘苗人凤’;

– 下面的查询无法使用索引
explain select * from tb_emp where ename=‘张三丰’ or job=‘架构师’;

– 下面的查询会让复合索引失效（最左匹配原则）
explain select * from tb_emp where job=‘架构师’;

select例题：

– 1查询月薪最高的员工姓名和月薪
SELECT ename, sal from tb_emp
where sal=(SELECT max(sal) FROM tb_emp);

– 2查询员工的姓名和年薪((月薪+补贴)*13)
select ename as 员工姓名,
(sal+comm)*13 as 年薪
from tb_emp where ifnull(comm,0);

– 3查询有员工的部门的编号和人数
select tb_emp.dno as 部门编号, count(eno) as 人数 from tb_dept right JOIN tb_emp on tb_dept.dno=tb_emp.dno group by tb_emp.dno;

– 4查询所有部门的名称和人数
select dname, ifnull(total,0) from tb_dept t1 left join
(select dno, count(eno) as total from tb_emp group by dno) t2
on t1.dno=t2.dno;

– 5查询月薪最高的员工(Boss除外)的姓名和月薪
select ename, sal from tb_emp
where sal=(
select max(sal) from tb_emp where mgr is not null
);

– 6查询薪水超过平均薪水的员工的姓名和月薪
select @a:=(select avg(sal) from tb_emp);

select @a;

select ename, sal, round(sal-@a,2) from tb_emp
where sal>@a;

– 7查询薪水超过其所在部门平均月薪的员工的姓名、部门编号和月薪
select ename, t1.dno, sal from tb_emp t1 inner join
(select dno, avg(sal) as avgsal from tb_emp group by dno) t2
on t1.dno=t2.dno where sal>avgsal;

– 8查询部门中薪水最高的人姓名、月薪和所在部门名称
select ename, sal, dname from tb_emp, tb_dept,
(select dno, max(sal) as 部门最高薪水 from tb_emp group by dno) t1
where tb_emp.dno=t1.dno AND tb_dept.dno=tb_emp.dno and sal=部门最高薪水;

– 9查询主管的姓名和职位
– distinct - 耗时操作（去重）
– in / not in - 耗时操作（集合中元素多了以后性能急剧下降）（集合运算，判断是否在里边）
– 建议：使用exists/ not exists取代去重和集合运算
select ename, job from tb_emp where eno in (
select distinct mgr from tb_emp where mgr is not null
);

select ename, job from tb_emp t1
where exists (select ‘x’ from tb_emp t2 where t1.eno=t2.mgr);

– 10查询月薪排名4~6名的员工排名、姓名和月薪
select rn, ename, sal from
(select @no:=@no+1 as rn, ename, sal from tb_emp
order by sal desc) t1, (select @no:=0) t2
limit 3 offset 3;

select @no:=@no+1 as rn, ename, sal
from tb_emp, (select @no:=3) t2
order by sal desc limit 3,3;