• oracle 基础表 mysql版


    emp 员工表(empno 员工号/ename 员工姓名/job 工作/mgr 上级编号/hiredate 受雇日期/sal 薪金/comm 佣金/deptno 部门编号)

    dept 部门表(deptno 部门编号/dname 部门名称/loc 地点)

    ----------------------------------------------------------emp表-------------------------------------------------------------

    1. DROP TABLE IF EXISTS `emp`;
    2. CREATE TABLE `emp` (
    3. `EMPNO` int(4) NOT NULL,
    4. `ENAME` varchar(10) DEFAULT NULL,
    5. `JOB` varchar(9) DEFAULT NULL,
    6. `MGR` int(4) DEFAULT NULL,
    7. `HIREDATE` date DEFAULT NULL,
    8. `SAL` int(7) DEFAULT NULL,
    9. `COMM` int(7) DEFAULT NULL,
    10. `DEPTNO` int(2) DEFAULT NULL,
    11. PRIMARY KEY (`EMPNO`),
    12. KEY `FK_DEPTNO` (`DEPTNO`),
    13. CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
    14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    15. -- ----------------------------
    16. -- Records of emp
    17. -- ----------------------------
    18. INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
    19. INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
    20. INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
    21. INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
    22. INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
    23. INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
    24. INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
    25. INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', null, '20');
    26. INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
    27. INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
    28. INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', null, '20');
    29. INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
    30. INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
    31. INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');

    --------------------------------------------------------dept表-------------------------------------------------

    1. DROP TABLE IF EXISTS `dept`;
    2. CREATE TABLE `dept` (
    3. `DEPTNO` int(2) NOT NULL,
    4. `DNAME` varchar(14) DEFAULT NULL,
    5. `LOC` varchar(13) DEFAULT NULL,
    6. PRIMARY KEY (`DEPTNO`)
    7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    8. -- ----------------------------
    9. -- Records of dept
    10. -- ----------------------------
    11. INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
    12. INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
    13. INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
    14. INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

    --------------------------------------------salgrade----------------------------------------

    1. DROP TABLE IF EXISTS `salgrade`;
    2. CREATE TABLE `salgrade` (
    3. `grade` int(11) NOT NULL DEFAULT '0',
    4. `losal` int(11) DEFAULT NULL,
    5. `hisal` int(11) DEFAULT NULL,
    6. PRIMARY KEY (`grade`)
    7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    8. -- ----------------------------
    9. -- Records of salgrade
    10. -- ----------------------------
    11. INSERT INTO `salgrade` VALUES ('1', '700', '1200');
    12. INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
    13. INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
    14. INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
    15. INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

    ---------------------------------------------------------------练习-----------------------------------------------------------

    1. SELECT * from emp
    2. /*1-数据分组的重要函数: max、min、avg、sum、count*/
    3. SELECT ename,sal FROM emp WHERE sal= (SELECT max(sal) from emp )
    4. /*2-查询出工资高于平均工资的员工信息*/
    5. SELECT ename,job,sal from emp WHERE SAL>(SELECT avg(sal) from emp)
    6. /*查询出每个部门的平均工资和最高工资;*/
    7. SELECT avg(sal),max(sal),deptno from emp group by deptno
    8. /*查询出每个部门的每种岗位的平均工资和最低工资;*/
    9. SELECT avg(sal),min(sal),DEPTNO,job from emp group by deptno,job
    10. /*查询出平均工资低于2000的部门号和它的平均工资.*/
    11. SELECT avg(sal),deptno FROM emp GROUP BY DEPTNO having avg(sal)<2000
    12. SELECT avg(sal),deptno FROM emp GROUP BY DEPTNO having avg(sal)>2000 ORDER BY avg(sal)
    13. /*查询出每个员工的姓名、工资及其工资的级别*/
    14. SELECT e.ename,e.sal,s.grade from emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal
    15. /*查询出某个员工的上级领导的姓名*/
    16. SELECT e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno
    17. /*询出工资比部门编号30的所有员工的工资高的员工的姓名、工资和部门编号*/
    18. SELECT ename,sal,deptno from emp where sal>all (SELECT sal from emp WHERE deptno=30)
    19. /*查询出与SMITH的部门和岗位完全相同的所有雇员*/
    20. SELECT * FROM emp WHERE (job,deptno)=(SELECT job ,deptno FROM emp WHERE ename='SMITH')
    21. /*查询出高于自己部门平均工资的员工的信息*/
    22. SELECT e1.ename,e1.sal FROM emp e1,(SELECT deptno,avg(sal) mysal FROM emp GROUP BY deptno) e2 WHERE e1.deptno=e2.deptno AND e1.sal>e2.mysal
    23. /*谁挣的钱的最多*/
    24. select ename, sal from emp where sal = (select max(sal) from emp);
    25. /*求部门人中那些人的薪水最高*/
    26. /*SELECT ename,DEPTNO,max(sal) from emp GROUP BY deptno;*/
    27. select ename, sal,emp.deptno
    28. from emp
    29. join (select max(sal) max_sal, deptno from emp group by deptno) t
    30. on (emp.sal =t.max_sal and emp.deptno =t.deptno)
    31. SELECT * FROM salgrade
    32. /*部门平均薪水的等级*/
    33. SELECT t.deptno,s.grade FROM salgrade s,
    34. (SELECT deptno,AVG(sal) avg from emp GROUP BY deptno) t
    35. WHERE t.avg between s.losal and s.hisal;
    36. select deptno,avg_sal,grade from
    37. (select deptno,avg(sal) avg_sal from emp group by deptno) t
    38. join salgrade s on (t.avg_sal between s.losal and s.hisal);
    39. /*那些人是经理人*/
    40. select ename from emp where empno in (select distinct mgr from emp);
    41. select distinct d.ename from emp e ,emp d where e.mgr=d.empno
    42. /*不用组函数求最高薪水*/
    43. select distinct sal
    44. from emp
    45. where sal not in
    46. (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal))
    47. /*平均薪水最高的部门编号*/
    48. SELECT deptno ,max(avg) FROM
    49. (SELECT avg(sal) avg,deptno FROM emp GROUP BY deptno)t
    50. /*平均薪水最高的部门名称*/
    51. SELECT * from dept
    52. SELECT d.deptno,d.dname,max(avg) FROM
    53. (SELECT avg(sal) avg,deptno FROM emp GROUP BY deptno) t,dept d
    54. WHERE t.deptno=d.deptno
    55. /*平均薪水的等级最低的部门名称*/
    56. SELECT t.dname,t.deptno,min(s.grade) grade,t.avg_sal FROM
    57. (SELECT avg(sal) avg_sal,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno= d.deptno GROUP BY e.deptno)t
    58. join salgrade s on(t.avg_sal between s.losal and s.hisal);
    59. select dname, t1.deptno,grade,avg_sal from
    60. (
    61. select deptno, grade, avg_sal from
    62. (select deptno, avg(sal) avg_sal from emp group by deptno)t
    63. join salgrade s on(t.avg_sal between s.losal and s.hisal)
    64. )t1
    65. join dept on (t1.deptno=dept.deptno)
    66. where t1.grade=
    67. ( select min(grade) from(
    68. select deptno, grade, avg_sal from
    69. (select deptno, avg(sal) avg_sal from emp group by deptno)t
    70. join salgrade s on(t.avg_sal between s.losal and s.hisal)
    71. )m
    72. );
    73. /*比普通员工的最高薪水还要高的经理人*/
    74. select ename
    75. from emp
    76. where empno in (select distinct mgr from emp where mgr is not null) and sal >
    77. (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null))


  • 相关阅读:
    jquery-easyUI第一篇【介绍、入门、使用常用的组件】
    Lucene第二篇【抽取工具类、索引库优化、分词器、高亮、摘要、排序、多条件搜索】
    Lucene第一篇【介绍Lucene、快速入门】
    Oracle总结第三篇【PLSQL】
    Oracle总结第二篇【视图、索引、事务、用户权限、批量操作】
    Oracle卸载
    纳税服务系统【统计图Fusionchart】
    纳税服务系统【自动受理,Quartz任务调度】
    纳税服务系统【投诉受理管理,显示投诉信息、处理回复、我要投诉】
    导航条样式代码
  • 原文地址:https://www.cnblogs.com/jpfss/p/9485453.html
Copyright © 2020-2023  润新知