-- 面试可能要求用3中以上方法写
-- 方法1：排序 + limit（有bug，性能差，不推荐）
select ename,
sal
from tb_emp
order by sal desc
limit 1;
-- 方法2：嵌套查询
select ename,
sal
from tb_emp
where sal = ( select max(sal) from tb_emp);
-- 1. 关系运算：封闭 - 关系运算的结果仍然是关系
-- a. 投影（select）
-- b. 别名（as）
-- c. 选择（where）
-- d. 交集（intersect）/ 并集（union）/ 差集（except）
-- e. 连接（join）
-- 2. 谓词 --> true / false / unknown
-- in / not in
-- exists / not exists
-- is pull / is not pull
-- all / any / some
-- 方法3：all 谓词
select ename,
sal
from tb_emp
where sal >= all(select sal from tb_emp);
-- 查常量
select 'x' from tb_emp;
select 'x' from dual;
-- 方法4：exists 谓词
select ename,
sal
from tb_emp t1
where not exists (select 'x'
from tb_emp t2
where t2.sal > t1.sal);
-- 方法5：计数法（可推广到前几名）
select ename,
sal
from tb_emp t1
where (select count(*)
from tb_emp t2
where t2.sal > t1.sal) = 0;
select concat('a', 'xxx') from dual;
2. 查询员工的姓名和年薪(年薪=(sal+comm)*13)
-- 推荐用coalesce()
select ename as 姓名,
(sal + coalesce(comm, 0))*13 as 年薪
from tb_emp
order by 年薪 desc;
select ename as 姓名,
(sal + ifnull(comm, 0))*13 as 年薪
from tb_emp;
3. 查询有员工的部门的编号和人数
select dno,
count(*)
from tb_emp
group by dno;
4. 查询所有部门的名称和人数
select dname,
count(eno)
from tb_emp right outer join tb_dept
on tb_emp.dno = tb_dept.dno
group by tb_dept.dno;
select dname,
coalesce(total, 0) as total
from tb_dept t1 left join (select dno,
count(*) as total
from tb_emp
group by dno) t2
on t1.dno = t2.dno;
5. 查询月薪超过平均月薪的员工的姓名和月薪
select ename,
sal
from tb_emp
where sal > (select avg(sal)
from tb_emp);
6. 查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪
select ename,
dno,
sal
from tb_emp natural join ( select dno,
avg(sal) as avg_sal
from tb_emp
group by dno) t
where sal > avg_sal;
select ename,
t1.dno,
sal
from tb_emp t1 inner join ( select dno,
avg(sal) as avg_sal
from tb_emp
group by dno) t2
on t1.dno = t2.dno
where sal > avg_sal;
7. 查询部门中月薪最高的人姓名、月薪和所在部门名称
select ename,
dno,
sal
from tb_emp natural join tb_dept natural join ( select dno,
max(sal) as max_sal
from tb_emp
group by dno) t
where sal = max_sal;
select ename,
sal,
dname
from tb_emp t1 inner join tb_dept t2
inner join ( select dno,
max(sal) as max_sal
from tb_emp
group by dno) t3
on t1.dno = t2.dno
and t2.dno = t3.dno
where sal = max_sal;
8. 查询主管的姓名和职位
select ename,
job
from tb_emp t1
where exists (select 'x'
from tb_emp t2
where t1.eno = t2.mgr);
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 right join (select distinct mgr
from tb_emp
where mgr is not null) t
on tb_emp.eno = t.mgr;
9. 查询月薪排名4~6名的员工排名、姓名和月薪
-- MySQL 5.x
select *
from (select @x := @x + 1 as r,
ename,
sal
from tb_emp, (select @x := 0) t1
order by sal desc) t2
where r between 4 and 6;
-- MySQL 8.x
-- 窗口函数
-- row_number() - 对列排名
-- rank() - 排序 并列之后 按数量排
-- dense_rank() - 排序 并列之后 按排名排
select *
from (select ename,
sal,
row_number() over (order by sal desc) as r1,
rank() over (order by sal desc) as r2,
dense_rank() over (order by sal desc) as r3
from tb_emp) t
where r2 between 4 and 6;
10. 查询每个部门月薪排前2名的员工姓名、月薪和部门编号
-- MySQL 5.x
select ename,
sal,
dno
from tb_emp t1
where (select count(*)
from tb_emp t2
where t1.dno = t2.dno
and t2.sal > t1.sal) < 2
order by dno asc, sal desc;
-- MySQL 8.x
select ename,
sal,
dno
from (select ename,
sal,
dno,
rank() over (partition by dno order by sal desc) as r
from tb_emp) t
where r < 3;
11. DCL - Data Control Language - 数据控制语言
-- grant（授予权限）/ revoke（召回权限）
-- 删除用户
drop user 'wangdachui'@'%';
-- 创建用户（访问账号）
create user 'wangdachui'@'%'identified by 'wang.618.888';
create user 'wangdachui'@'10.7.183.%' identified by 'Dachui.618';
-- 修改用户密码
alter user 'wangdachui'@'%' identified by 'Hello.world!123';
-- 授权
grant select on hrs.* to 'wangdachui'@'%';
grant insert, delete, update on hrs.* to 'wangdachui'@'%';
grant select on school.* to 'wangdachui'@'%';
grant create on *.* to 'wangdachui'@'%';
-- 授予所有权限
grant all privileges on *.* to 'wangdachui'@'%' with grant option;
-- 召回
revoke create on *.* from 'wangdachui'@'%';
revoke select on school.* from 'wangdachui'@'%';
-- 召回所有权限
revoke all privileges on *.* from 'wangdachui'@'%';