MySQL试题集锦
五． 数据库部分
1、用两种方式根据部门号从高到低， 工资从低到高列出每
个员工的信息。
employee:
eid,ename,salary,deptid;
select * from employee order by deptid desc,salary
2、列出各个部门中工资高千本部门的平均工资的员工数和
部门号， 并按部门号排序
创建表：
mysql> create table employee92l(id int primary key auto_increment,name varchar(5
O),salary bigint,deptid int);
插入实验数据：
mysql> insert into employee921 values(null,'zs',1000,1),(null,'ls',1100, l ),(null
,'ww', 1100, l ),(null,'zl',900, l) ,(null,'zl', 1000,2), (null,'zl',900,2) ,(null,'z
I', 1000,2) , (null,'zl',1100,2);
编写sql 语句：
() select avg(salary) from employee921 group by deptid;
() mysql> select employee921.id,employee921.name,employee921.salary,employee921.dep
tid ticl from employee921 where salary > (select avg(salary) from employee921 where deptid =
tid);
效率低的一个语句， 仅供学习参考使用（在group by 之后不能使用where , 只能使用
having, 在group by 之前司以使用where, 即表示对过滤后的结果分组）：
mysql> select employee921.id,employee921.name,employee921.salary,employee921.dep
tid tid from employee921 where salary > (select avg(salary) from employee921 group by deptid
having deptid = tid);
() select count(*) ,tid
from (
2021-08-21 14:10:35
1.69MB