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 empWHERE HIREDATE > '1982-1-1';

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

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

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

查询所有员工的年薪，并按年薪从低到高排序select ENAME,SAL*12FROM empORDER BY SAL*12ASC ;ALTER TABLE emp ADD ySAL INT;UPDATE emp set ySAl = 12* SAL;SELECT ENAME,ySALFROM empORDER BY ySAL;

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

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

查询部门号是10号的部门名、雇员名及薪资SELECT DNAME,ENAME,SALFROM dept LEFT JOIN empON dept.DEPTNO = emp.DEPTNOWHERE dept.DEPTNO = 10;

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

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

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

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

列出薪资第2到第8的员工信息SELECT *FROM empORDER BY SAL DESCLIMIT1,7;

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