MySQL学习5

MySQL查询练习

创建原始数据库

部门表dept（部门序号，部门名，部门位置）
员工表emp（员工编号，名字，职业，直接上级编号，受雇日期，月薪，补贴，部门序号）
 create database scott;
 use scott；

 CREATE TABLE DEPT
  (DEPTNO INT, 
	DNAME VARCHAR(20), 
	LOC VARCHAR(20), 
	CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO));


  CREATE TABLE EMP 
  (EMPNO int, 
	ENAME VARCHAR(20), 
	JOB VARCHAR(20), 
	MGR int, 
	HIREDATE DATE, 
	SAL int, 
	COMM int, 
	DEPTNO int, 
	CONSTRAINT PK_EMP PRIMARY KEY(EMPNO),
	CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
	REFERENCES DEPT (DEPTNO));
 

Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');


INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-4-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-9-8', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);

查询操作

查询部门编号为10的员工信息
SELECT * 
	FROM emp 
	WHERE DEPTNO = 10;

查询工资在2000到3000范围内的员工名字和薪资
SELECT ENAME,SAL 
	FROM emp 
	WHERE SAL BETWEEN 2000 AND 3000; 

列出至少有一个员工的部门(单表可查询不用连接查询可提高效率)
SELECT DNAME 
	FROM dept 
	WHERE DEPTNO IN (SELECT DEPTNO FROM emp);
SELECT emp.DEPTNO,DNAME 
	FROM emp INNER JOIN dept ON emp.DEPTNO = dept.DEPTNO GROUP BY DEPTNO;

列出薪水比SMITH高的所有员工信息
SELECT * 
	FROM emp 
	WHERE SAL > (SELECT SAL FROM emp WHERE ENAME = 'SMITH');

列出最低薪水低于1500的各种工作
SELECT JOB 
	FROM emp 
	WHERE SAL < 1500 GROUP BY JOB;

查询有补贴的员工信息
SELECT * 
	FROM emp 
	WHERE COMM IS NOT NULL;

查询员工是否有叫'SMITH','KING','Obama'
SELECT * 
	FROM emp 
	WHERE ENAME IN ('SMITH','KING','Obama');

查询员工名以s开头的员工信息
SELECT * 
	FROM emp 
	WHERE ENAME LIKE 's%';

查询员工姓名中，第二个字母是大写A的员工
SELECT * 
	FROM emp 
	WHERE ENAME LIKE '_A%'; 

查询在1982年之后入职的员工信息
SELECT *
	FROM emp
	WHERE HIREDATE > '1982-1-1';

查询各部门的平均薪资
SELECT DNAME,AVG(SAL)
	FROM emp INNER JOIN dept ON emp.DEPTNO = dept.DEPTNO
	GROUP BY DNAME;

查询各部门各岗位的平均工资和最高工资
SELECT DNAME,JOB,AVG(SAL),MAX(SAL)
	FROM emp INNER JOIN dept ON emp.DEPTNO = dept.DEPTNO
	GROUP BY DNAME,JOB;

查询各个部门经理的最低工资
SELECT MIN(SAL) 
	FROM emp
	WHERE JOB = 'MANAGER';

查询所有员工的年薪，并按年薪从低到高排序
select ENAME,SAL*12
	FROM emp
	ORDER BY SAL*12	ASC ;

ALTER TABLE emp ADD ySAL INT;
UPDATE emp set ySAl = 12* SAL;
SELECT ENAME,ySAL
	FROM emp
	ORDER BY ySAL;

查询比30部门最高薪资的人薪资更高的所有员工信息
SELECT *
	FROM emp
	WHERE SAL > (SELECT MAX(SAl) FROM emp WHERE DEPTNO = 30);

查询每一位雇员的姓名、职位及直接上级领导姓名
SELECT emp.ENAME,JOB,man.ENAME
	FROM emp LEFT JOIN (SELECT ENAME, EMPNO FROM emp WHERE JOB = 'MANAGER')man
	ON emp.MGR = man.EMPNO;

查询部门号是10号的部门名、雇员名及薪资
SELECT DNAME,ENAME,SAL
	FROM dept LEFT JOIN emp
	ON dept.DEPTNO = emp.DEPTNO
	WHERE dept.DEPTNO = 10;

查询在销售部工作的员工姓名
SELECT ENAME
	FROM emp
	WHERE DEPTNO = (SELECT DEPTNO FROM dept WHERE DNAME = 'SALES');

查询FORD的详细信息和领导姓名
SELECT e1.*,e2.ENAME
	FROM emp e1,emp e2
	WHERE e1.MGR = e2.EMPNO && e1.ENAME = 'FORD';

查询薪水高于公司平均薪水的所有员工信息
SELECT *
	FROM emp
	WHERE SAL > (SELECT AVG(SAL) FROM emp);

查询与SCOTT从事相同工作的员工
SELECT *
	FROM emp
	WHERE JOB = (SELECT JOB FROM emp WHERE ENAME = 'SCOTT');
列出所有工作的最低工资
SELECT JOB,MIN(SAL)
	FROM emp
	GROUP BY JOB;

列出薪资第2到第8的员工信息
SELECT *
	FROM emp
	ORDER BY SAL DESC
	LIMIT	1,7;

列出各个部门的员工数量和平均薪资
SELECT DNAME,COUNT(ENAME),AVG(SAL)
	FROM dept LEFT JOIN emp
	ON dept.DEPTNO = emp.DEPTNO
	GROUP BY DNAME;