MySQL数据库SQL语句练习
参考csdn博客：MySQL（06）——SQL练习题目（部门员工表）
1.建表并插入数据
emp（员工表）
create table emp(
empno int primary key auto_increment,-- 员工编号
ename varchar(10), -- 员工姓名 -
job varchar(9), -- 岗位
mgr int, -- 直接领导编号
hiredate date, -- 雇佣日期，入职日期
sal int, -- 薪水
comm int, -- 提成
deptno int not null, -- 部门编号
foreign key (deptno) references dept(deptno)
);
dept（部门表）
create table dept(
deptno int primary key auto_increment, -- 部门编号
dname varchar(14) , -- 部门名字
loc varchar(13) -- 地址
) ;
插入数据
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);
2.练习题
-- 1．列出至少有一个员工的所有部门。
-- 2．列出薪金比"刘一"多的所有员工。
-- 3．***** 列出所有员工的姓名及其直接上级的姓名。
-- 4．列出受雇日期早于其直接上级的所有员工。
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
-- 6．列出所有job为“职员”的姓名及其部门名称。
-- 7．列出最低薪金大于1500的各种工作。
-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
-- 9．列出薪金高于公司平均薪金的所有员工。
-- 10．列出与"周八"从事相同工作的所有员工。
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
-- 13．列出在每个部门工作的员工数量、平均工资。
-- 14．列出所有员工的姓名、部门名称和工资。
-- 15．列出所有部门的详细信息和部门人数。
-- 16．列出各种工作的最低工资。
-- 17．列出各个部门的 经理 的最低薪金。
-- 18．列出所有员工的年工资,按年薪从低到高排序。
-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
-- 20.查询出所有薪水在'陈二'之上的所有人员信息。
-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
-- 22.查询出emp表中所有的工作种类（无重复）
-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
-- 27.查询出名字第三个汉字是“多”的所有员工信息。
-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc;
-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
-- 32.显示出薪水最高人的职位。
-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
-- 34.删除10号部门薪水最高的员工。
-- 35.将薪水最高的员工的薪水降30%。
-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end
3.练习题（答案）
答案仅供参考
-- 1．列出至少有一个员工的所有部门。
select deptno from emp group by deptno having count(*) >= 1
-- 2．列出薪金比"SMITH"多的所有员工。
select * from emp where sal > (select sal from emp where ename = 'SMITH')
-- 3．***** 列出所有员工的姓名及其直接上级的姓名。
select e1.*,e2.ename mgrname from emp e1 ,emp e2 where e1.mgr = e2.empno
-- 4．列出受雇日期早于其直接上级的所有员工。
select e1.*,e2.ename mgrname from emp e1 ,emp e2 where e1.mgr = e2.empno
and e1.hiredate < e2.hiredate
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select e.*,d.dname,d.loc from emp e right join dept d on d.deptno = e.deptno
-- 6．列出所有job为“CLERK”的姓名及其部门名称。
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = 'CLERK'
-- 7．列出最低薪金大于1500的各种工作。
select distinct job from emp where sal > 1500
-- 8．列出在部门 "SALES" 的员工的姓名，假定不知道SALES的部门编号。
select ename from emp where deptno = (select deptno from dept where dname = 'SALES')
-- 9．列出薪金高于公司平均薪金的所有员工。
select * from emp where sal > (select avg(sal) from emp)
-- 10．列出与"SCOTT"从事相同工作的所有员工。
select * from emp where job = (select job from emp where ename = 'SCOTT')
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。（没看明白）
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select * from emp where sal > (select max(sal) from emp where deptno = 30 )
and deptno != 30
-- 13．列出在每个部门工作的员工数量、平均工资。
select count(*),avg(sal)
from emp e,dept d where e.deptno = d.deptno group by d.deptno
-- 14．列出所有员工的姓名、部门名称和工资。
select e.ename,d.dname,e.sal from emp e join dept d on(e.deptno = d.deptno)
-- 15．列出所有部门的详细信息和部门人数。
select t2.deptno,t2.dname,t2.loc,ifnull(t1.empsize,0) empsize
from
(
select d.deptno,d.dname,d.loc,count(*) empsize
from emp e , dept d where e.deptno = d.deptno group by d.deptno
) t1 right join dept t2 on t1.deptno = t2.deptno
-- 16．列出各种工作的最低工资。
select job,min(sal) from emp group by job
-- 17．列出各个部门的 经理 的最低薪金。
select d.deptno,min(e.sal) from emp e , dept d
where e.deptno = d.deptno and e.job = 'MANAGER' group by d.deptno
-- 18．列出所有员工的年工资,按年薪从低到高排序。
select ename,sal*12 year_sal from emp order by year_sal
-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,ename,sal from emp where sal >= 3000
-- 20.查询出所有薪水在'ALLEN'之上的所有人员信息。
select * from emp where sal > (select sal from emp where ename = 'ALLEN')
-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
select empno,ename,sal from emp where deptno = 20 and sal > 2000
-- 22.查询出emp表中所有的工作种类（无重复）
select distinct job from emp
-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
select * from emp where comm is not null
-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
第一种：select * from emp where sal >=800 and sal<=2500
第二种：select * from emp where sal between 800 and 2500
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
第一种：select * from emp where empno in (7521,7900,7782)
第二种：select * from emp where empno = 7521 or empno = 7900 or empno = 7782
-- 26.查询出名字中有“S”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select * from emp where ename like '%S%' and sal > 2000
-- 27.查询出名字第三个字是“A”的所有员工信息。
select * from emp where ename like '__A%'
-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select * from emp order by sal,hiredate desc
-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc;
-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
select * from emp where hiredate = (select min(hiredate) from emp)
-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
select ename,sal,ifnull(comm,100) from emp
-- 32.显示出薪水最高人的职位。
select job from emp where sal = (select max(sal) from emp)
-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
select deptno,max(sal),min(sal) from emp where deptno != 10 group by deptno
-- 34.删除10号部门薪水最高的员工。
注意：如果子查询的语句和更新，删除在同一张表上，MY SQL会出现错误，因此要双重子查询！！！)
delete from emp where sal = (select t.maxsal from (select max(sal) maxsal from emp where deptno = 10) t)
-- 35.将薪水最高的员工的薪水降30%。
update emp set sal = (select t1.maxsal from (select max(sal) maxsal from emp) t1)*0.7
where sal = (select t2.maxsal from (select max(sal) maxsal from emp) t2)
-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end
select ename,sal,
case
when sal <=2000 then '1级'
when sal >2000 and sal < 3000 then '2级'
when sal >=3000 then '3级'
else '无'
end '工资级别'
from emp