Mysql练习

## 基础表

- 1、 部门表：DEPT

CREATE TABLE dept(

deptno BIGINT(2) PRIMARY Key,

dname VARCHAR(20),

loc VARCHAR(20)

)

- 2、 雇员表：EMP

CREATE TABLE emp(

empno BIGINT(4) PRIMARY KEY,

ename VARCHAR(20),

job VARCHAR(20),

mgr BIGINT,

hiredate DATE,

sal DOUBLE(7,2),

comm DOUBLE(7,2),

deptno BIGINT

)

## 基础数据

- 导入DEPT 表数据:

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

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

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

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

- 导入EMP 表数据:

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);

INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);

INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);

INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);

INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);

INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);

INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);

INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);

INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);

INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);

INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

## 练习题

- 01.查询每个雇员的编号、姓名、职位。

SELECT empno,ename,job from emp,dept WHERE emp.deptno=dept.deptno;

- 02.查询每个雇员的职位，使用 DISTINCT 消除掉显示的重复行记录。

SELECT DISTINCT ename,job FROM emp,dept WHERE emp.deptno=dept.deptno;

- 03.计算每个雇员的编号、姓名、基本年薪。年薪=(工资+奖金)*12，(使用 IFNULL 函数判断 NULL）

SELECT empno,ename,(sal+IFNULL(comm,0))*12 FROM emp;

- 04.每个雇员每个月公司会补贴饭食 200 元，交通补助 300 元，计算年薪。

SELECT ename,(sal+IFNULL(comm,0))*12+200+300 FROM emp;

- 05.查询基本工资高于 2000 的全部雇员信息。

SELECT * FROM emp,dept WHERE (sal+IFNULL(comm,0))>2000 and emp.deptno=dept.deptno;

- 06.查询出 smith 的信息。

SELECT * FROM emp,dept WHERE ename='SMITH' and emp.deptno=dept.deptno;

- 07.查询出所有不是 CLERK 的详细信息。

SELECT * FROM emp,dept WHERE job<>'CLERK' and emp.deptno=dept.deptno;

- 08.查询出所有销售人员(SALESMAN)的基本信息，并且要求销售人员的工资高于 1300。

SELECT * FROM emp,dept WHERE job='SALESMAN' and emp.deptno=dept.deptno;

- 09.查询出工资范围在 1500~3000 之间的全部雇员信息（包含 1500 和 3000）

SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERE sal BETWEEN 1500 and 3000;

- 10.查询出所有经理或者是销售人员的信息，并且要求这些人的基本工资高于 1500。

SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERE job='MANAGER' or job='SALESMAN';

- 11.要求查询出所有在 1981 年雇佣的雇员信息。

SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERE YEAR(hiredate)>1981;

- 12.查询所有领取奖金的雇员信息（comm 不为空） 。

SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERE NOT comm IS NULL AND comm!=0;

- 13.查询所有领取奖金高于 100 的雇员信息。

SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno WHERE comm>100;

- 14.查询出雇员编号是 7369、7566、9999 的雇员信息。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno WHERE empno IN(7369,7566,9999);

- 15.查询出所有雇员姓名是以 A 开头的全部雇员信息。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno WHERE ename LIKE 'A%';

- 16.查询出雇员姓名第二个字母是 M 的全部雇员信息。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno WHERE ename LIKE '_M%';

- 17.查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno WHERE ename LIKE '%A%';

- 18.查询出所有雇员的信息，要求按照工资排序。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno ORDER BY sal;

- 19.要求查询所有雇员的信息，按照雇佣日期由先后排序。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno ORDER BY hiredate;

- 20.查询全部雇员信息，按照工资由高到低排序，如果工资相同，则按照雇佣日期由先后排序。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno ORDER BY sal ASC,hiredate;

- 21.查询部门 30 中的所有员工。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno WHERE emp.deptno=30;

- 22.查询出所有办事员（CLERK）的姓名，编号和部门编号。

SELECT ename,empno,deptno FROM emp WHERE job='CLERK';

- 23.查询出奖金高于薪金的员工。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno WHERE comm>sal;

- 24.查询出奖金高于薪金的 60%的员工。

SELECT * FROM emp JOIN dept ON emp.deptno= dept.deptno WHERE comm>(sal*0.6);

- 25.查询出部门 10 中所有经理（MANAGER）和部门 20 中所有办事员（CLERK）的详细资料。

SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno 

WHERE (emp.deptno=10 AND job='MANAGER')or(emp.deptno=20 AND job='CLERK');

- 26.查询出部门 10 中所有经理， 部门 20 中所有办事员， 既不是经理又不是办事员但其薪金大于或等于 2000的所有员工的信息。

SELECT * FROM emp JOIN dept ON emp.deptno=dept.deptno 

WHERE (emp.deptno=10 AND job='MANAGER') or(emp.deptno=20 AND job='CLERK') 

OR (!((emp.deptno=10 AND job='MANAGER') or(emp.deptno=20 AND job='CLERK')) AND sal>2000);

- 27.查询出收取奖金的员工的不同工作。

SELECT DISTINCT job FROM emp WHERE NOT comm IS NULL;

- 28.查询出不收取奖金或收取的奖金低于 100 的员工。

SELECT ename FROM emp WHERE IFNULL(comm,0)<100;

- 29.查询出不带有“R”的员工的姓名。

SELECT ename FROM emp WHERE NOT ename LIKE '%R%';

- 30.查询出每个雇员的姓名、职位、领导姓名。

SELECT e1.ename,e1.job,e2.ename FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno;

- 31.查询出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。

SELECT e1.empno,e1.ename,e2.empno,e2.ename FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno 

ORDER BY e2.sal*12 DESC;

- 32.查询出在销售部（SALES）工作的员工姓名、基本工资、雇佣日期、部门名称。 （不知道销售部编号）。

SELECT ename,sal,hiredate,IFNULL(dname,'xxx') FROM emp JOIN dept ON emp.deptno=dept.deptno

- 33.查询出所有员工的姓名、部门名称和工资。

SELECT ename,dname,sal FROM emp  JOIN dept ON emp.deptno=dept.deptno;

- 34.查询出所有员工的年工资，所在部门名称，按年薪从低到高排序。

SELECT ename,dname,sal*12 FROM emp  JOIN dept ON emp.deptno=dept.deptno

ORDER BY sal;

- 35.查询出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过 3000。

SELECT e1.ename,e2.ename,dname FROM emp e1 

JOIN emp e2 ON e1.mgr=e2.empno 

JOIN dept ON e2.deptno=dept.deptno

WHERE e2.sal>3000;

- 36.查询出公司的最高和最低工资。

SELECT MAX(sal),MIN(sal) FROM emp

- 37.查询出每个部门的人数、平均工资，只显示部门编号。

SELECT COUNT(deptno),deptno,AVG(sal) FROM emp 

GROUP BY deptno 

- 38.查询出每种职位的最高和最低工资。

SELECT job,MAX(sal),MIN(sal) FROM emp 

GROUP BY job

- 39.查询平均工资高于 2000 的职位信息，以及从事此职位的雇员人数、平均工资。

SELECT job,COUNT(deptno),AVG(sal) FROM emp

GROUP BY job

HAVING AVG(sal)>2000 

- 40.查询员工编号大于其领导编号的每个员工的姓名、职位、领导名称。

SELECT e1.ename,e1.job,e2.ename FROM emp e1 

JOIN emp e2 ON e1.mgr=e2.empno

WHERE e1.empno>e2.empno;

- 41 查询出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。

SELECT emp.deptno,dname,AVG(sal),MIN(sal),MAX(sal) from emp,dept WHERE emp.deptno=dept.deptno

GROUP BY emp.deptno

HAVING COUNT(emp.deptno)>0;

- 42.查询出部门名称和这些部门的员工信息（数量、平均工资） ，同时列出那些没有员工的部门。

SELECT COUNT(emp.deptno),dept.dname,AVG(sal) from emp RIGHT JOIN dept ON emp.deptno=dept.deptno

GROUP BY emp.deptno

- 43.查询工资比 smith 更高的全部员工信息。

SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename='SMITH')

- 44.查询工资和职位和 smith 相同的所有员工信息。

SELECT * FROM emp WHERE sal=(SELECT sal FROM emp WHERE ename='SMITH') AND job=(SELECT job FROM emp WHERE ename='SMITH')

- 45.查询各部门的部门编号，部门名词，部门地址，员工人数和平均工资

SELECT dept.deptno,dept.dname,dept.loc,COUNT(emp.deptno),AVG(sal) from emp JOIN dept ON emp.deptno=dept.deptno

GROUP BY emp.deptno