准备：建表，插入数据

drop table if exists emp;

create table if not exists emp(

empno int primary key auto_increment,

ename varchar(10) not null,

job varchar(20),

salary double(8,2),

mgr int(10),

bonus double(6,2),

hiredate date,

deptno int(10)

);

insert into emp values(null,'mary','sales',7000,6,1000,'2014-1-1',10);

insert into emp values(null,'lily','sales',6000,1,800,'2014-5-1',10);

insert into emp values(null,'tom','sales',5000,1,4000,'2014-3-1',10);

insert into emp values(null,'james','account',8000,6,null,'2014-2-1',20);

insert into emp values(null,'scott','teaching',8000,6,3000,'2014-1-20',30);

insert into emp values(null,'tom','BOSS',38000,null,null,'2013-1-20',30);

insert into emp values(null,'kitty','teaching',7000,5,700,'2014-5-20',30);

insert into emp values(null,'kitty','teaching',6000,5,500,'2014-6-20',30);

insert into emp values(null,'green','analyst',15000,6,1000,'2014-2-20',40);

insert into emp values(null,'brown','analyst',12000,9,7000,'2014-4-20',40);

insert into emp values(null,'danis','department',3000,6,800,'2014-3-4',50);

insert into emp values(null,'brown','department',1800,11,600,'2014-4-20',50);

insert into emp values(null,'smith','department',1200,11,500,'2014-5-20',50);

#1：查询EMP表的全部职工的EMPNO、ENAME和JOB。

SELECT empno,ename,job FROM emp

#2：查询EMP表的全部职工的EMPNO、ENAME和JOB，按salary升序次序排列。

#按照某个字段进行排序，不需要将其显示，一定要确认查询的表中存在这个字段

SELECT empno,ename,job FROM emp ORDER BY salary 

#3：查询EMP表的全部列，列的次序为：JOB，SALARY，ENAME，EMPNO，MGR，HIREDATE，BONUS，DEPTNO，查询结果按年薪降序。

SELECT job,salary,ename,mgr,hiredate,bonus,deptno FROM emp ORDER BY salary DESC

#4：列出EMP表中的不同的JOB名称

SELECT job FROM emp GROUP BY job

#5：查询在部门10中工作，其工资高于6000的职工信息

SELECT * FROM emp WHERE deptno=10 AND salary>6000

#6：列出其JOB为BOSS或ANALYST的职工名及职工号

SELECT ename,empno FROM emp WHERE job="BOSS" OR job="analyst"

#7：列出工资在5500至20000之间的职工名字、职工号。使用两种方式实现

(1)SELECT ename,empno FROM emp WHERE salary BETWEEN 5500 AND 20000

(2)SELECT ename,empno FROM emp WHERE salary>=5500 AND salary<=20000

#8：查出工资为6k的人员所在的部门名称和地址。

SELECT deptno,adrress FROM emp WHERE salary=6000

#9：查出以W开头或以S结尾的职工名的职工信息。

SELECT job,salary,ename,mgr,hiredate,bonus,deptno,adrress FROM emp WHERE ename LIKE "w%" OR ename LIKE "%s"

#10：查出以k开头或以y结尾的职工信息和部门信息。

SELECT job,salary,ename,mgr,hiredate,bonus,deptno,adrress FROM emp WHERE ename LIKE "k%" OR ename LIKE "%y"

#11：列出没有奖金的职工信息。

SELECT * FROM emp WHERE bonus IS NULL

查询奖金为 NULL 的时候要用 IS NULL

#12：查询工资最高的人的信息以及部门信息

SELECT * FROM emp ORDER BY salary DESC LIMIT 1

#13：查询每个部门的平均工资，部门名称，部门编号，并按照平均工资倒序排列

SELECT deptno,job,AVG(salary) FROM emp GROUP BY deptno ORDER BY AVG(salary) DESC

#14：查询每个部门的平均工资，按照部门编号，部门名称，平均薪资顺序显示，低于5000的不要求显示，并按照平均工资倒序排列

SELECT deptno,job,AVG(salary)FROM emp a  GROUP BY deptno HAVING AVG(salary)>=5000 ORDER BY AVG(salary)

  

#15：查询‘lily’的领导信息

SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp WHERE ename="lily")

16：查詢在梵蒂冈办公的人员信息

SELECT * FROM emp WHERE adrress="梵蒂冈"

#17：列出所有部门的详细信息和部门人数

SELECT COUNT(deptno),deptno FROM emp GROUP BY deptno

SELECT COUNT(1),deptno FROM emp GROUP BY deptno

#18：查询出所有薪水在'james'之上的所有人员信息，并按照工资降序排列

SELECT * FROM emp

WHERE salary>(SELECT salary FROM emp WHERE ename="james") ORDER BY salary DESC

#19：查询出所有有奖金的人员信息，并按照奖金的多少进行降序排列

SELECT * FROM emp

WHERE bonus IS NOT NULL ORDER BY bonus DESC

#20：查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

(1)SELECT deptno,MAX(salary),MIN(salary) FROM emp WHERE deptno <> 10 GROUP BY deptno 

(2)SELECT deptno,MAX(salary),MIN(salary) FROM emp GROUP BY deptno HAVING deptno <> 10

#21：查询所有mary下属的信息和工作地点，并且按照他们的工资降序排列

SELECT * FROM emp WHERE mgr IN (SELECT empno FROM emp WHERE ename="mary") ORDER BY salary DESC