|
- -- 创建表
- drop table if exists emp;
- create table emp(
- empno int,
- ename varchar(50),
- job varchar(50),
- mgr int,
- hiredate date,
- sal decimal(7,2),
- comm decimal(7,2),
- deptno int
- )engine=innodb default charset=utf8;
- drop table if exists dept;
- create table dept(
- deptno int,
- dname varchar(14),
- loc varchar(13)
- )engine=innodb default charset=utf8;
- 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);
- 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、按员工编号升序排列不在10号部门工作的员工信息
- SELECT *
- FROM emp
- WHERE deptno != 10
- ORDER BY empno;
- -- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息,按薪水降序排列
- SELECT *
- FROM emp
- where ename not LIKE "_A%" and CEIL(sal) > 800;
- -- 3、求每个部门的平均薪水
- SELECT deptno, avg(sal)
- FROM emp
- GROUP BY deptno;
- -- 4、求各个部门的最高薪水
- SELECT deptno, max(sal)
- FROM emp
- GROUP BY deptno;
- -- 5、求每个部门每个职位的最高薪水
- SELECT deptno, job, MAX(sal)
- FROM emp
- group by deptno, job
- -- 6、求平均薪水大于2000的部门编号
- SELECT deptno, avg(sal) avgSal
- from emp
- group by deptno
- HAVING avgSal > 2000;
- -- 7、将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来,按部门平均薪水降序排列
- select deptno, avg(sal) avgSal
- from emp
- where sal > 1200
- GROUP BY deptno
- HAVING avgSal > 1500
- ORDER BY avgSal desc;
- -- 8、求最高薪水的员工信息
- SELECT *
- from emp
- where sal = (
- SELECT max(sal)
- FROM emp
- )
- -- 9、求多于平均薪水的员工信息
- SELECT *
- FROM emp
- where sal > (
- SELECT AVG(sal)
- FROM emp
- )
- -- 10、求各个部门薪水最高的员工信息 显示部门名称
- SELECT dept.dname, t2.*
- FROM dept,(
- select emp.*
- from emp, (
- SELECT deptno, MAX(sal) maxSal
- FROM emp
- GROUP BY deptno) t1
- where emp.deptno = t1.deptno AND emp.sal = t1.maxSal) t2
- where dept.deptno = t2.deptno
- -- 11. 查询和Scott相同部门的员工姓名和雇用日期
- select ename, hiredate
- FROM emp
- WHERE ename != "SCOTT" and deptno = (
- select deptno
- from emp
- where ename = "SCOTT");
- -- 12. 查询工资比公司平均工资高的员工的员工号,姓名和工资。
- SELECT empno, ename, sal
- FROM emp
- WHERE sal > (
- select AVG(sal)
- FROM emp);
- -- 13. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
- -- 13.1
- SELECT empno, ename, sal
- FROM emp,(
- SELECT deptno, avg(sal) avgSal
- from emp
- GROUP BY deptno
- ) t1
- where emp.deptno = t1.deptno and emp.sal > t1.avgSal;
- -- 13.2
- select empno, ename, sal
- FROM emp inner join (
- SELECT deptno, avg(sal) avgSal
- FROM emp
- GROUP BY deptno
- ) t1
- on emp.deptno = t1.deptno and emp.sal > t1.avgSal
- -- 14. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
- SELECT empno, ename
- FROM emp
- WHERE deptno in (
- SELECT deptno
- FROM emp
- WHERE ename LIKE "%u%"
- )
- -- 15. 查询在部门的loc为 DALLAS 的部门工作的员工的员工号
- SELECT empno
- from emp
- WHERE deptno in (
- SELECT deptno
- FROM dept
- WHERE loc = "DALLAS");
- -- 16. 查询所有部门的名字,loc,员工数量 和 工资平均值 ()
- -- 16.1
- select dept.dname, dept.loc, t1.*
- from dept, (
- select avg(sal), count(empno), deptno
- from emp
- group by deptno
- ) t1
- where t1.deptno = dept.deptno;
- -- 16.2
- select d.*, count(e.empno), avg(sal)
- from dept d, emp e
- where d.deptno = e.deptno
- GROUP BY d.deptno, d.dname, d.loc
- -- 17.查询平均工资最低的部门信息
- -- 17.1
- select *
- from dept
- where deptno = (
- select deptno
- from emp
- GROUP BY deptno
- order by avg(sal)
- limit 1)
- -- 17.2
- select *
- from dept
- where deptno = (
- select deptno
- from emp
- group by deptno
- having avg(sal) like (
- select min(t1.avgSal)
- from (
- select deptno, avg(sal) avgSal
- from emp
- GROUP BY deptno
- ) t1
- ))
- -- 18.查询平均工资最低的部门信息和该部门的平均工资
- select dept.*, avgSal
- from dept, (
- select deptno, avg(sal) avgSal
- from emp
- group by deptno) t1
- where dept.deptno = t1.deptno
- -- 19.查询平均工资高于公司平均工资的部门有哪些?
- select deptno
- from emp
- group by deptno
- having avg(sal) > (
- select avg(sal) avgSal
- from emp
- )
- -- 20. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
- select min(sal)
- from emp
- where deptno = (
- select deptno
- from emp
- group by deptno
- order by max(sal) asc
- limit 1
- )
- -- 21.查询部门号大于20 或者 姓名中包含 a的 员工信息
- -- 21.1
- select *
- from emp
- where deptno > 20 or ename Like "%a%";
- -- 21.2
- select *
- from emp
- where deptno > 20
- union
- select *
- from emp
- where ename like "%a%";
复制代码 来源:https://www.cnblogs.com/paopaoT/p/17357181.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作! |
|