MySQL简单练习题的答案

个人理解编写之MySQL练习题

        这是一套mysql的练习题，数据的话，自己根据需求准备，不提供练习需要用到的数据，自己根据情况添加

表结构

    #创建员工信息表
CREATE TABLE Emp (
	Empno INT (5),      #编号
	Ename VARCHAR (30), #姓名
	Deptno INT (5),     #部门编号
	Job VARCHAR (20),   #工种
	Hiredate Date,      #入职时间
	Comm DOUBLE (6, 2), #佣金
	Sal DOUBLE (6, 2),  #薪金
	Mgr INT (5)         #上司编号
);
    #创建部门表
CREATE TABLE Dept (
	Dname VARCHAR (30), #部门名
	Deptno INT (5),     #部门号
	Loc VARCHAR (50)    #位置
);

录入表数据

一、单表查询

#1、选择部门30中的雇员 
select * from Emp where Deptno=30;
#2、列出所有办事员的姓名、编号和部门
select Ename 姓名,Empno 编号,Deptno 部门编号 from Emp where Job='办事员';
#3、找出佣金高于薪金的雇员
select * from Emp where Comm>Sal;
#4、找出佣金高于薪金60%的雇员
select * from Emp where Comm>Sal*0.6;
#5、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from Emp where (Deptno=10 and Job='经理') or (Deptno=20 and Job='办事员');
#6、找出既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from Emp where (Job!='经理' and Job!='办事员') and Sal>=2000;
#7、找出收取佣金的雇员的不同工作
select distinct Job from Emp where Comm>0;
#8、找出不收取佣金或收取的佣金低于100的雇员
SELECT * FROM emp WHERE ifnull(Comm,0)<100;
select * from Emp where Comm<100;
#9、找出各月最后一天受雇的所有雇员
select * from Emp where last_day(Hiredate)=Hiredate;
#10、找出早于25年之前受雇的雇员
select * from Emp where year(now())-25 > year(Hiredate);
#11、显示只有首字母大写的所有雇员的信息
select * from Emp where Ename regexp binary '^\[A-Z]';
select * from emp where (ascii(substr(ename,1,1)) between 65 and 90);
#12、显示正好为6个字符的雇员姓名
select Ename 雇员姓名 from Emp where Ename regexp '^......$';
SELECT * FROM emp WHERE length(ename)=6;
#13、显示不带有'R'的雇员姓名
select Ename 雇员姓名 from Emp where Ename not regexp binary '[R]';
select Ename 雇员姓名 from Emp where Ename regexp binary '^.[^R]';
SELECT ename FROM emp WHERE ename NOT LIKE '%R%';
#14、显示所有雇员的姓名的前三个字符
select left(Ename,3) 雇员姓名 from Emp;
SELECT substr(ename,1,3) FROM emp;
#15、显示所有雇员的姓名，用a替换所有'A'
select replace(Ename,'a','A') 雇员姓名 from Emp;
#16、显示所有雇员的姓名以及满10年服务年限的日期
select Ename 雇员姓名, if(YEAR(Hiredate)<YEAR(now())-10,Hiredate,"") 入职时间 from Emp;
#17、显示雇员的详细资料，按姓名排序
select * from Emp order by Ename;
#18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select Ename 雇员姓名 from Emp order by Hiredate;
#19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
select Ename 姓名,Job 工作,Sal 薪金 from Emp order by Job desc,Sal;
#20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面
select Ename 姓名,Hiredate 入职时间 from Emp order by Hiredate;
#21、显示在一个月为30天的情况下所有雇员的日薪金
select Ename 姓名, Sal/30 日薪金 from Emp;
#22、找出在（任何年份的）2月受聘的所有雇员
select Ename 姓名, Hiredate 受聘时间 from Emp where month(Hiredate)=2;
#23、对于每个雇员，显示其加入公司的天数--
select Ename 姓名, TO_DAYS(NOW())-TO_DAYS(Hiredate) 入职天数 from Emp;
#24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名
select Ename 姓名 from Emp where Ename regexp binary '[A]';
#25、以年、月和日显示所有雇员的服务年限
select Ename 姓名, DATE_FORMAT(CONCAT(year(now())-year(Hiredate),'-',month(now())-MONTH(Hiredate),'-',day(now())-day(Hiredate)),'%y-%m-%d') from Emp;

二、多表查询

#1、列出至少有一个雇员的所有部门
select e.Ename 姓名,d.Dname 部门 from emp e,dept d where e.Deptno=d.Deptno;
#2、列出薪金比"SMITH"多的所有雇员
select Ename 姓名,Sal 薪金 from emp where Sal>(select Sal from emp where Ename='江');
#3、列出所有雇员的姓名及其直接上级的姓名
select e.Ename 姓名,em.Ename 上司 from emp e,emp em where e.Mgr=em.Empno;
#4、列出入职日期早于其直接上级的所有雇员
select e.Ename 雇员,e.Hiredate 入职日期,em.Ename 上司,em.Hiredate 上司入职日期 from emp e,emp em where e.Mgr=em.Empno and e.Hiredate<em.Hiredate;
#5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select d.Dname 部门,e.Ename 姓名 from dept d left join emp e on d.Deptno=e.Deptno;
#6、列出所有“CLERK”（办事员）的姓名及其部门名称
select e.Ename 姓名,d.Dname 部门 from dept d,emp e where d.Deptno=e.Deptno and e.Job='办事员';
#7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录
select Job 工种,min(sal) 最低薪金 from emp group by job having min(sal)>1500;
#8、列出薪金高于公司平均水平的所有雇员
select Ename 姓名,Sal 薪金 from emp where Sal>(select avg(Sal) from emp);
#9、列出与“SCOTT”从事相同工作的所有雇员
select Ename 姓名 from emp where Job=(select Job from emp where Ename='江帅');
#10、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金
select Ename 姓名,Sal 薪金 from emp where Sal in (select Sal from emp where Deptno='30');
#11、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金
select Ename 姓名,Sal 薪金 from emp where Sal>(select max(Sal) from emp where Deptno='30');
#12、列出每个部门的信息以及该部门中雇员的数量　
select d.*,e.num 数量 from dept d left join (select Deptno,count(1) num from emp group by Deptno) e on d.Deptno=e.Deptno;
#14、列出各个部门的MANAGER（经理）的最低薪金
select d.Dname 部门,e.minSal 最低薪资 from dept d,(select Deptno,min(Sal) minSal from emp where Job='经理' group by Deptno) e where d.Deptno=e.Deptno;
#15、列出薪金水平处于第四位的雇员
select Ename 姓名,Empno 编号,Deptno 部门号,Job 工种,Comm 佣金,Sal 薪金 from (select *,(@i :=@i+1) as pm from emp,(select @i := 0) as it order by sal desc) a where a.pm=4;

以上内容编写来源于网络，个人的答案见解，如果有错误之处请见谅。