mysql练习题

Mysql 60道练习题：

我的mysql版本为

 

创建数据库：create database oa;

使用数据库：use oa;

创建数据库表：

CREATE TABLE dept(

deptno INT PRIMARY KEY,

dname VARCHAR(20),

loc VARCHAR(20)

)

CREATE TABLE emp(

empno INT PRIMARY KEY,

ename VARCHAR(20) NOT NULL,

job VARCHAR(20) CHECK (job IN ('CLERK','SALESMAN','MANAGER','SALESMAN','ANALYST')),

mgp INT ,

hiredate DATETIME ,

sal DECIMAL(10,2),

comm DECIMAL(10,2),

deptno int

)

alter table emp add constraint emp_deptno foreign key (deptno) references emp(deptno);

 

插入数据：

INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK')

INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');

INSERT INTO dept VALUES (30,'SALES','CHICAGO');

INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

 

insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',1640,NULL,20);

insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',11400,300,30);

insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',5200,500,30);

insert into emp values(7566,'JOENS','MANAGER',7839,'1981-4-2',7015,NULL,20);

insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',5200,1400,30);

insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-1',5900,NULL,30);

insert into emp values(7782,'CLARK','MANAGER',7839,'1981-6-9',2470,NULL,10);

insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3040,NULL,20);

insert into emp values(7844,'TURNER','SALESMAN',7698,'1980-12-17',6200,0,30);

insert into emp values(7876,'ADAMS','CLERK',7788,'1981-9-8',2240,NULL,20);

insert into emp values(7900,'JAMES','CLERK',7698,'1987-5-23',4000,NULL,30);

insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-3',3040,NULL,20);

insert into emp values(7934,'MILLER','CLERK',7782,'1982-12-3',2620,NULL,10);

 

1、查询部门号为20的员工信息

select * from emp where deptno=20;

 

2、查询所有工种为clerk的员工的员工号、员工名、部门号

select empno ,ename ,deptno from emp where job=’clerk’;

 

3、查询奖金comm高于工资sal的员工的信息

select * from emp where  comm is not null and comm>sal;

4、查询奖金高于工资20%的员工的信息

select * from emp where comm>sal*0.2;

 

5、查询10号部门中工种为manager和20号部门工种为clerk的员工信息

select * from emp where (deptno=10 and job=’manager’ )or (deptno=20 and job =’clerk’);

 

6、查询所有工种不是manager和clerk且工资大于或等于2000的员工的详细信息

select * from emp where sal>=2000 and job not in(‘’manager,’clerk’);

 

7、查询有奖金的员工的不同工种

select distinct job from emp where comm is not null;

 

8、查询所有的员工工资与奖金的和

select ename, case when comm is null then 0+sal when comm is not null then comm+sal as salary from emp ;

 

9、查询没有奖金或者奖金低于100的员工的信息

select * from emp where comm is null or comm <100;

 

10、查询各月倒数第三天入职的员工信息

select * from emp where day(hiredate)>day(last_day(hiredate))-3

 

11、查询工龄大于或者等于25年的员工信息

select ename,hiredate from emp where (now()-hiredate)>=25;

 

12、查询员工信息，要求以字母小写的方式显示员工的姓名

select empno,lower(ename) from emp;

 

13、查询员工名正好为6个字符的员工信息

select * from emp where char_length(ename)=6

 

14、查询员工名字中不包含字母“S”的员工

select empno,ename from emp where ename not like ’%S%’;

 

15、查询员工姓名的第二个字母为‘M’的员工信息

select empno,ename from emp where ename like ‘_M%’;

 

16、查询员工姓名的前三个字符

select empno,substring(ename,1,3)  from emp ;

 

17、查询所有员工信息，将员工名字中的S替换成s并显示

select empno,replace(ename,’S’,’s’) from emp;

 

18、查询员工的姓名和入职日期，并按入职日期从先到后排序

select ename,hiredate from emp order by hiredate;

 

19、显示员工姓名、工种、工资、奖金，按工种降序排列

select ename,job,sal,comm from emp order by job desc;

 

20、显示所有员工的姓名、入职年份和月份

select ename ,year(hiredate),month(hiredate)fromemp ;

 

21、查询2月份入职的员工信息

select empno,ename from emp where month(hiredate)=2;

 

 

22、查询所有员工入职以来的工作年限，用XX年显示

select empno,ename, concat(cast(year(now())-year(hieredate)) as char),’年’)from emp;

 

23、查询至少有一个员工的部门信息

select dept.deptno,dname,loc,count(empno) from dept,emp

where dept.deptno=emp.deptno

group by dept.deptno

having count(empno)>=1;

 

24、查询工资比SMITH员工工资高的所有员工信息

select * frmp emp where sal>(select sal from emp where ename=’SMITH’);

 

25、查询员工的姓名及其直接上级的姓名

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgp=e2.empno;

 

26、查询入职日期早于直接上级领导的员工信息

select e1.empno,e1.ename from emp e1,emp e2

where e1.mgp=e2.empno and e1.hiredate>e2.hiredate;

 

27、查询所有部门及其员工信息，包括那些没有员工的部门

select empno,ename,dept.deptno,dname

from dept left outer join emp on dept.deptno=emp.deptno ;

 

 

28、查询所有部门及其员工信息，包括那些不属于任何部门的员工

select dept.dname,emp.ename

from emp

left outer join dept on emp.deptno=dept.deptno

 

29、查询所有工种为CLERK的员工的姓名及其部门名称。

select dept.dname,emp.ename,emp.job

from emp

left outer join dept on emp.deptno=dept.deptno

where job='CLERK'

 

30、 查询最低工资大于2500的各种工作

select dept.deptno,dname

from emp,dept

where emp.deptno=dept.deptno

group by dept.deptno

having min(sal)>2500;

 

31、查询平均工资低于2000的部门及其员工信息

select empno,ename,dname

from dept,emp

where dept.deptno=emp.deptno

group by emp.deptno

having avg(sal)<2000;

32、查询在SALES部门工作的员工的姓名信息

select * from emp

where emp.deptno=(

select deptno from dept where dname='SALES');

 

33、查询工资高于公司平均工资的所有员工信息

select * from emp

where sal>(select avg(sal)from emp)

 

34、查询出与SMITH员工从事相同工作的所有员工信息

select *

from emp

where job = (select job from emp

where ename='SMITH');

 

35、 列出工资等于30部门中某个员工的工资的所有员工的姓名和工资

select *

from emp

where sal in (

select sal

from emp

where deptno=30) and deptno!=30;

36、 查询工资高于30部门工作的所有员工的工资的员工姓名和工资

select *

from emp

where sal > all(

select sal

from emp

where deptno=30) ;

37、查询每个部门中的员工数量、平均工资和平均工作年限

select dept.deptno,count(empno),avg(sal),avg(year(hiredate)) from emp,dept

where dept.deptno=emp.deptno

group by dept.deptno;

38、 查询从事同一种工作但不属于同一部门的员工信息

select *

from emp e1

where e1.job in (

select distinct e2.job

from emp e2

where e2.deptno != e1.deptno )

 

39、查询各个部门的详细信息以及部门人数、部门平均工资

select d.dname ,d.deptno ,count(e.empno) ,avg(e.sal)  

from dept d

left outer join emp e on d.deptno=e.deptno

group by d.deptno,d.dname;

 

select d.dname ,d.deptno ,count(e.empno) ,avg(e.sal)  

from dept d ,emp e

where  d.deptno=e.deptno

group by d.deptno,d.dname;

 

40、查询各种工作的最低工资

select job ,min(sal)

from emp

group by job

 

41、查询各个部门中不同工种的最高工资

select dname ,job ,max(sal)

from dept d left join emp e on d.deptno=e.deptno

group by job,dname;

 

42、查询10号部门员工的领导的信息

select deptno ,ename ,(select e2.ename from emp e2 where e2.mgp=e1.empno) as leader

from emp e1

where deptno=10；

 

 

select deptno ,ename

from emp e1

where deptno=10 and empno in (select mgp from emp e where e.deptno=10);

这个查询不会将查找不到的上级显示为null

 

43、 查询各个部门的人数及平均工资

select dname ,count(ename) ,avg(sal)

from emp e right outer  join dept d on d.deptno=e.deptno

group by d.dname;

 

select dname ,count(ename) ,avg(sal)

from dept d left outer join emp e on d.deptno=e.deptno

group by d.dname;

 

44、查询工资为某个部门平均工资的员工的信息

select * from emp

where sal in(

select avg(sal)

from emp group by deptno

);

45、查询工资高于本部门平均工资的员工的信息

select *

from emp e1

where sal>(

select avg(sal)

from emp e2

where e2.deptno=e1.deptno

);

 

46、查询工资高于本部门平均工资的员工的信息及其部门的平均工资

select *,(select avg(sal) from emp e2 where e2.deptno=e1.deptno) as deptAvgSal

from emp e1

where sal>(

select avg(sal) from emp e2 where e2.deptno=e1.deptno

);

 

47、查询工资高于20号部门某个员工工资的员工的信息

select *

from emp e1

where sal> any(

select sal from emp e where deptno=20

);

 

48、统计各个工种的员工人数与平均工资

select job ,count(empno) ,avg(sal)

from emp

group by job ;

 

49、统计每个部门中各工种的人数与平均工资

select deptno,job ,count(empno) ,avg(sal)

from emp

group by deptno ;

 

 

50、查询其他部门中工资、奖金与30号部门某员工工资、--奖金都相同的员工的信息。没有查询结果

Select sal,comm

From emp where sal in (select sal from emp where deptno=30) and

Comm in (select comm from emp where deptno=30) and deptno !=30;

51、查询部门人数大于5的部门的员工信息

Select empno,ename from dept left outer join emp on dept.deptno=emp.deptno

Group by dept.deptno having count(empno)>5

 

52、查询所有员工工资都大于1000的部门的信息

Select dept.deptno,dname from dept,emp where dept.deptno=emp.deptno

Group by dept.deptno

having min(sal)>1000;

 

53、查询所有员工工资都大于1000的部门的信息及其员工信息

Select empno,ename,dept.deptno,dname

From emp,dept

Where emp.deptno=dept.deptno

Group by emp.deptno

Having min(sal)>1000

 

54、查询所有员工工资都在900～3000之间的部门的信息

select * from dept

where deptno in(

select deptno from emp

group by deptno

having min(sal)>900 and max(sal)<3000

)；

 

55、查询有工资在900～3000之间的员工所在部门的员工信息

select * from emp

where deptno in(

select deptno from emp

group by deptno

having min(sal)>900 and max(sal)<3000

56、查询每个员工的领导所在部门的信息

select ename ,(

select e1.ename from emp e1 where emp.mgp=e1.empno

)as leader ,(

select d.dname

from emp e left outer join dept d on e.deptno=d.deptno

where emp.mgp=e.empno

) as leaderDept

from emp;

 

57、查询人数最多的部门信息

select dept.deptno,dname ,count(empno)  from dept,emp

Where dept.deptno=emp.deptno

group by dept.deptno

order by count(empno) desc

limit 0,1;

 

58、查询30号部门中工资排序前3名的员工信息

select empno,ename

from emp

where deptno=30

order by sal

limit 0,3;

 

59、查询所有员工中工资排序在5到10名之间的员工信息

select empno,ename from emp

order by sal

limit 4,6;

 

60、查询指定年份之间入职的员工信息。(1980-1985)

select *

from emp

where year(hiredate) between 1980 and 1985;