MySQL查询

MySQL查询

本文以部门表、员工表这两张表为例,来讲解如何进行mysql的查询

1 初始化三张表

1.1 部门表dept

CREATE TABLE dept  (
DEPTNO FLOAT(2) PRIMARY KEY, -- 部门号
DNAME VARCHAR(14) ,  -- 部门名称
LOC VARCHAR(13) ) ;  -- 部门地址

插入数据

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');  

1.2 员工表

CREATE TABLE emp  
(EMPNO float(4)  PRIMARY KEY,  -- 员工编号
ENAME VARCHAR(10),  -- 员工姓名
JOB VARCHAR(9),  -- 员工职位
MGR float(4),  -- 员工上级工号
HIREDATE DATE,  -- 生日
SAL float(7,2),  -- 薪水
COMM float(7,2),  -- 年终奖
DEPTNO float(2) not null);  -- 部门号

插入数据

insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566,'1981-12-02', 3000, null, 20);
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

基础查询

1)查询没有上级的员工全部信息（mgr是空的）

SELECT * FROM emp WHERE ISNULL(mgr);

2) 列出30号部门所有员工的姓名、薪资

SELECT ename,sal,deptno FROM emp WHERE deptno = '30';

3) 查询姓名包含 'A’的员工姓名、部门名称

SELECT ename,dname FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO AND ename LIKE "%A%";

4) 查询员工“TURNER”的员工编号和薪资

SELECT ename,empno,sal FROM emp WHERE ename = 'TURNER';

5) – 查询薪资最高的员工编号、姓名、薪资

SELECT empno,ename,sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

6) – 查询10号部门的平均薪资、最高薪资、最低薪资

SELECT AVG(sal),MAX(sal),MIN(sal) FROM emp WHERE deptno = '10';

子查询

1) 列出薪金比员工“TURNER”多的所有员工姓名（ename）、员工薪资（sal）

SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='TURNER');

2) 列出薪金高于公司平均薪金的所有员工姓名、薪金。

SELECT ename,sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp); 

3) 列出与“SCOTT”从事相同工作的所有员工姓名、工作名称(不展示Scott的姓名、工作)

SELECT ename,job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'scott') AND ename != 'scott'; 

4) 列出薪金高于30部门最高薪金的其他部门员工姓名、薪金、部门号。

SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = '30') AND deptno != '30';

5) – 查询薪资最高的员工编号、姓名、薪资

SELECT empno,ename,sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

6) 列出薪金高于本部门平均薪金的所有员工姓名、薪资、部门号、部门平均薪资。

SELECT emp.ename,emp.sal,emp.deptno,t.avgsal FROM emp ,(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) t WHERE emp.DEPTNO = t.deptno AND emp.sal > t.avgsal;

7) 列出所有部门的详细信息：部门名称、部门编号、部门人数。

SELECT d.dname,d.deptno,IFNULL(t.num,0) '部门人数' FROM (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) t RIGHT JOIN dept d ON t.deptno = d.deptno;

8) – 查询出king所在部门的工作年限最大的员工名字及入职时间

SELECT ename,HIREDATE FROM emp WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'king'));

9) 查询出管理员工人数最多的人的名字和他管理的人的名字

SELECT a.ename '员工',a.empno '员工编号',b.ename '领导',b.empno '领导编号' FROM emp a,emp b WHERE a.mgr = b.empno AND b.empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(*) = (SELECT COUNT(*) num FROM emp GROUP BY mgr ORDER BY num DESC LIMIT 0,1));

10) 查询出工资成本最高的部门的部门号、部门名称、部门工资成本

SELECT emp.DEPTNO,dept.DNAME,SUM(sal + IFNULL(comm,0)) total FROM emp,dept 	WHERE emp.DEPTNO = dept.DEPTNO 	GROUP BY emp.DEPTNO,dept.DNAME 	HAVING total = (SELECT SUM(sal + IFNULL(comm,0)) total FROM emp GROUP BY deptno ORDER BY total DESC LIMIT 0,1);

自连接

1) 列出所有员工的姓名及其直接上级的姓名。

解法1：SELECT a.ename,b.ename "上级" FROM emp a LEFT JOIN emp b ON a.mgr=b.empno;

解法2：SELECT ename '员工姓名',( SELECT ename FROM emp WHERE empno = se.mgr) '上级姓名'  FROM emp se;

2) 列出受雇日期早于其直接上级的所有员工编号、员工姓名、员工入职时间、上级姓名、上级入职时间

SELECT a.empno,a.ename,a.hiredate,b.ename,b.hiredate FROM emp a,emp b WHERE a.mgr=b.empno AND a.hiredate<b.hiredate;

左右连接

1) 查询所有的部门编号及部门下员工编号、员工姓名。

SELECT d.DEPTNO,e.EMPNO,e.ENAME FROM emp e RIGHT JOIN dept d ON e.DEPTNO = d.DEPTNO;

函数应用

1) 列出最低薪金大于1500的工作名称（job）以及最低薪金

SELECT ename,sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='TURNER');

SELECT job,MIN(sal) msal FROM emp GROUP BY job HAVING msal > 1500;

2) 列出在每个部门工作的员工数量、平均工资

SELECT deptno '部门号',COUNT(*) '员工数量' ,AVG(sal) '平均薪资' FROM emp GROUP BY deptno;

3) – 查询每个部门的部门号、最高薪资

SELECT deptno,MAX(sal) maxSal FROM emp GROUP BY deptno;

4) 查询每种工作的工作名称和最低工资

SELECT job,MIN(sal) '最低薪资' FROM emp GROUP BY job;

5) 列出所有员工的员工姓名、年工资,按年薪从低到高排序。

SELECT ename,(sal*12) AS yearSal FROM emp ORDER BY yearSal asc;

6) – 查询每个部门中薪资最高的员工姓名、薪资、部门号

SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT MAX(sal) sal,deptno FROM emp GROUP BY deptno) t WHERE e.sal = t.sal AND e.DEPTNO = t.deptno;

7) – 查询不是领导的员工编号、员工姓名、员工职位

SELECT empno,ename,job FROM emp WHERE empno NOT IN(SELECT DISTINCT IFNULL(mgr,'') FROM emp);

多表查询

1) 查询岗位（job）是 ‘CLERK’ 的员工编号、员工姓名、所在部门名称

SELECT e.EMPNO,e.ENAME,d.DNAME FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO AND e.JOB = 'CLERK';

2) 列出所有员工的姓名、部门名称和工资。

SELECT emp.ENAME,dept.DNAME,emp.SAL FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO;

3) 查询至少有一个员工的部门编号、员工数量

SELECT b.deptno,COUNT(*) num FROM emp a,dept b WHERE a.deptno = b.deptno GROUP BY b.DEPTNO,b.DNAME HAVING num >=1;

4) – 查询出没有员工的那个部门的部门编号和部门名称

SELECT deptno,dname FROM dept WHERE deptno NOT IN(SELECT DISTINCT deptno FROM emp);

5) 查询在部门“SALES”（销售部）工作的员工的姓名、部门编号

解法1：SELECT emp.ENAME,dept.DEPTNO FROM emp,dept WHERE emp.DEPTNO = dept.DEPTNO AND dept.DNAME = 'SALES';

解法2：SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');