MySQL练习题21道

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),
address VARCHAR(50)
);
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。

2：查询EMP表的全部职工的EMPNO、ENAME和JOB，按salary升序次序排列。
按照某个字段进行排序，不需要将其显示，一定要确认查询的表中存在这个字段

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

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

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

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

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

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

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

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

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

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

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

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

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

16：查詢在上海办公的人员信息//职业为销售的

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

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

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

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

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

答案

#1
SELECT empno,enmae,job FROM emp;
#2
SELECT empno,ename,job FROM emp ORDER BY salary DESC;
#3
SELECT DISTINCT empno,ename,job FROM emp ORDER BY salary*12+bonus DESC;
#4
SELECT DISTINCT job FROM emp;
#5
SELECT empno,ename,job,hiredate FROM emp WHERE salary>6000 AND deptno=10;
#6
SELECT ename,empno FROM emp WHERE job='manager' OR job='analyst';
#7
SELECT empno,ename FROM emp WHERE salary BETWEEN 5500 AND 20000;
SELECT empno,ename FROM emp WHERE salary >=5500 AND salary <=20000;
#8
SELECT job,address FROM emp WHERE salary=6000;
#9
SELECT empno,ename,job,hiredate FROM emp WHERE ename LIKE'w%' OR ename LIKE '%s' ;
#10
SELECT empno,ename,job,hiredate,deptno FROM emp WHERE ename LIKE'k%' OR ename LIKE '%y' ;
#11
SELECT empno,ename,job,hiredate FROM emp WHERE bonus=0 OR bonus IS NULL;
#12
SELECT empno,ename,job,hiredate,deptno,MAX(salary) FROM emp  ;
#13
SELECT AVG(salary),deptno FROM emp GROUP BY deptno ORDER BY AVG(salary) DESC;
#14
SELECT AVG(salary),deptno FROM emp GROUP BY deptno  HAVING AVG(salary)>5000 ORDER BY AVG(salary) DESC ;
#15
SELECT mgr FROM emp WHERE ename='lily';
#16
SELECT empno,ename,job,hiredate FROM emp WHERE address='上海';
#17
SELECT deptno,COUNT(deptno)FROM emp GROUP BY deptno;
#18
SELECT empno,ename,job,hiredate FROM emp WHERE 
SELECT empno,ename,job,hiredate,(salary*12+IFNULL(bonus,0)) FROM emp   WHERE (salary*12+IFNULL(bonus,0))>
(SELECT (salary*12+IFNULL(bonus,0)) FROM emp WHERE ename='james') GROUP BY empno ORDER BY(salary*12+IFNULL(bonus,0)) DESC
#19
SELECT empno,ename,job,hiredate,bonus FROM emp WHERE bonus IS NOT NULL ORDER BY bonus DESC;
#20
SELECT deptno,MAX(salary),MIN(salary) FROM emp WHERE deptno!=10 GROUP BY deptno;
#21
SELECT a.* FROM emp a
JOIN emp b ON a.mgr=b.empno
WHERE b.`ename`='mary' ORDER BY salary ;