mysql 查询练习题

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

select d.deptno, d.dname, d.loc, r.count from dept d , (select deptno,count(*) count from emp group by deptno) r where d.deptno = r.deptno;

2. 列出薪金比smith高的所有员工。

select * from emp where sal+ifnull(comm,0) > (select sal+ifnull(comm,0) from emp where ename = 'smith');

3. 列出所有员工的姓名及其直接上级的姓名,如果没有上级，则显示BOSS。

select a.ename '员工姓名',ifnull(b.ename,'BOSS') '领导姓名' from emp a left join emp b on a.mgr = b.empno;

4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

select a.empno, a.ename,d.dname from emp a join emp b on a.mgr=b.empno join dept d on a.deptno=d.deptno where a.hiredate < b.hiredate;

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

select e.*,d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;

6. 列出所有小职员(CLERK)的姓名及其部门名称，部门的人数。

select e.ename,d.dname,t.countno from emp e join dept d on e.deptno=d.deptno join (select deptno,count(deptno) as countno from emp GROUP BY deptno) t on t.deptno = e.deptno where job = 'clerk';

7. 列出最低薪金大于1500的各种工作及从事此工作的员工人数。

select job,count(*) from emp where job in (select job from emp where sal+ifnull(comm,0) > 1500 group by job) group by job;

8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

select e.ename from emp e join (select deptno from dept where dname = 'sales') d on e.deptno=d.deptno ;

9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

select e.empno,e.ename,m.empno,e.ename,d.deptno,d.dname,d.loc,s.grade from emp e ,dept d , emp m,salgrade s where e.sal >(select avg(sal) from emp) and e.deptno=d.deptno and e.mgr = m.empno and e.sal BETWEEN s.losal and s.hisal;

10.列出与smith从事相同工作的所有员工及部门名称。

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE job=( SELECT job FROM emp WHERE ename='smith')
AND ename!='smith'
AND e.deptno = d.deptno;

11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

SELECT ename,sal FROM emp WHERE sal IN(SELECT sal FROM emp WHERE deptno =30);
AND deptno != 30

12.列出在每个部门工作的员工数量、平均工资。

SELECT deptno,AVG(sal),count(*) FROM emp GROUP BY deptno;

13.找出部门编号为10中所有经理，和部门编号为30中所有销售员的详细资料。

SELECT * FROM emp WHERE (deptno=10 and job='manager') or (deptno=30 and job='salesman')

14.找出有奖金的职位。

select DISTINCT job from emp where comm is not null and comm != 0

15.找出员工名字由5个长度的员工信息。

select * from emp where LENGTH(ename) = 5

16.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

SELECT * FROM emp ORDER BY sal DESC ,hiredate ASC;

17.显示非销售人员的工作名称，以及从事同一工作的工资的总和，并满足从事同一工作的员工的工资总和大于等于5000，结果按照工资总和的降序排列。

SELECT job,SUM(sal) sum FROM emp WHERE job !='salesman' GROUP BY job HAVING ((sum(sal))>5000) order by sum;

18.列出各种工作的最低工资及从事此工作的雇员姓名

select e.ename,t.* from emp e join
(select e.job,MIN(e.sal) sal from emp e group by e.job) t on
e.job=t.job and e.sal=t.sal;

19.求出部门名称中带‘S’字符的部门员工的工资合计、部门人数

select deptno,sum(sal),count(empno) from emp where deptno in(select deptno from dept where dname like '%S%' ) group by deptno;

20.统计每个部门的详细信息，并且要求这些部门的平均工资大于2000

select d.*,avg(e.sal) from dept d left outer join emp e on(d.deptno=e.deptno) group by e.deptno having avg(e.sal)>2000;

创建 user表（5个字段） role表 menu表 user_role表 role_menu表 添加测试数据

用户和角色有关联 角色和菜单有关联

查询 所有用户的全部信息，包含用户信息，角色名称，菜单名称

select a.*,c.role_name,c.role_desc,e.menu_name from t_user a
LEFT JOIN t_user_role b on a.u_id = b.uid
LEFT JOIN t_role c on b.rid=c.rid
LEFT JOIN t_role_menu d on c.rid =d.rid
LEFT JOIN t_menu e on d.mid=e.mid ;

查询 是管理员的所有用户信息（其他角色）

select a.*,c.role_name,e.menu_name from t_user a
LEFT JOIN t_user_role b on a.u_id = b.uid
LEFT JOIN t_role c on b.rid=c.rid
LEFT JOIN t_role_menu d on c.rid =d.rid
LEFT JOIN t_menu e on d.mid=e.mid where c.rid in(1,2);

查询 ***菜单归属于那个角色，那些用户

select c.role_name '角色',a.username '用户' from t_user a
LEFT JOIN t_user_role b on a.u_id = b.uid
LEFT JOIN t_role c on b.rid=c.rid
LEFT JOIN t_role_menu d on c.rid =d.rid
LEFT JOIN t_menu e on d.mid=e.mid where e.menu_name='商品管理';

查询 用户A有那些菜单

select e.menu_name from t_user a
LEFT JOIN t_user_role b on a.u_id = b.uid
LEFT JOIN t_role c on b.rid=c.rid
LEFT JOIN t_role_menu d on c.rid =d.rid
LEFT JOIN t_menu e on d.mid=e.mid where a.username='zhangsan';

查询 用户名字当中带有“花”的用户

select * from t_user a
LEFT JOIN t_user_role b on a.u_id = b.uid
LEFT JOIN t_role c on b.rid=c.rid
LEFT JOIN t_role_menu d on c.rid =d.rid
LEFT JOIN t_menu e on d.mid=e.mid where a.username like '%花%';

查询 每个角色各有多少用户数量。

select c.role_name,count(b.rid) from t_user a
LEFT JOIN t_user_role b on a.u_id = b.uid
LEFT JOIN t_role c on b.rid=c.rid GROUP BY b.rid;

查询 每个角色各有多少菜单

select a.role_name,count(a.rid) from t_role a
LEFT JOIN t_role_menu b on a.rid=b.rid
LEFT JOIN t_menu c on b.mid=c.mid GROUP BY a.rid;