– 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;