Oracle数据库学习笔记（一）（账户修改密码、查询顺序）

查询语句 where 、group by、having、order by使用

ALTER USER SCOTT IDENTIFIED BY scott ACCOUNT UNLOCK;–使用SYS账户给SCOTT用户修改密码以及解锁

--查询
SELECT * FROM USER_TABLES;
/*DEPT
EMP
BONUS
SALGRADE*/
SELECT * FROM EMP;

–注释EMP表

COMMENT ON table EMP    IS '员工表';
COMMENT ON COLUMN EMP.empno     IS '员工编号';
COMMENT ON COLUMN EMP.ename     IS '姓名';
COMMENT ON COLUMN EMP.job       IS '工作岗位';
COMMENT ON COLUMN EMP.mgr       IS '上级领导';
COMMENT ON COLUMN EMP.hiredate  IS '入职日期';
COMMENT ON COLUMN EMP.sal       IS '工资';
COMMENT ON COLUMN EMP.comm      IS '奖金';
COMMENT ON COLUMN EMP.deptno    IS '部门编号';
-------------------------------------------=

SELECT * FROM DEPT;
COMMENT on table DEPT IS '部门表';

--------查询员工表中的员工姓名

SELECT 
       EMP.ENAME 
FROM EMP;

--------查询员工表中的员工编号、员工工资、部门编号

SELECT 
       EMP.Empno
       ,EMP.Sal
       ,EMP.Deptno 
FROM EMP;

--------查询工资大于2000的员工姓名、工资、部门编号

SELECT
      A.Ename
     ,A.Sal
     ,A.DEPTNO
FROM EMP A
WHERE A.SAL>2000;

--------查询工资小于2000，部门号30的员工姓名、员工编号、部门编号

SELECT
      A.Ename
     ,A.EMPNO
     ,A.DEPTNO
FROM EMP A
WHERE A.SAL<2000 
     AND A.DEPTNO=30;

—查询工资大于2000或者部门号20的员工姓名、员工编号、部门编号

SELECT
      A.Ename
     ,A.EMPNO
     ,A.DEPTNO
FROM EMP A
WHERE A.SAL>2000 
     OR A.DEPTNO=20;

------GROUP BY–去重、分组

--------求EMP 表中每个部门的平均工资
SELECT
      A.DEPTNO
     ,AVG(A.SAL)    
FROM EMP A
GROUP BY A.DEPTNO
Order by A.DEPTNO;
-------求EMP 表中薪资大于2000的总工资
SELECT
      SUM(A.SAL)
FROM EMP A
WHERE A.SAL>2000;
-------求EMP 表中20号部门的最高和最低工资
SELECT
      A.DEPTNO
     ,MAX(A.SAL)
     ,MIN(A.SAL)
FROM EMP A
WHERE A.DEPTNO=20
GROUP BY A.DEPTNO;
------求 EMP表中入职时间最晚的员工比工资最低的员工多多少钱
SELECT
      MAX(A.SAL)-MIN(A.SAL)
FROM EMP A 
WHERE A.HIREDATE=(SELECT MAX(A.HIREDATE) FROM EMP A) OR A.SAL=(SELECT MIN(A.SAL) FROM EMP A);    
------求EMP 表中每个部门薪资大于2000的总工资
SELECT
      A.Deptno
     ,SUM(A.sal)
FROM EMP A
WHERE A.sal>2000
GROUP by A.DEPTNO;
-------求 EMP 表中平均工资大于2000的部门编号以及部门人数
SELECT
      A.DEPTNO
     ,COUNT(A.DEPTNO)
FROM EMP A
GROUP BY A.DEPTNO
HAVING AVG(A.SAL)>2000;

-------求 EMP 表中平均工资大于2000且人数大于3的部门编号以及部门人数，平均工资
SELECT
      A.DEPTNO
     ,COUNT(A.DEPTNO)
     ,AVG(A.SAL)
FROM EMP A
GROUP BY A.DEPTNO
HAVING AVG(A.SAL)>2000 
     AND COUNT(A.DEPTNO)>3;
     

-------ORDER BY 排序

-------查询每个部门按工资降序排序
SELECT
      *
FROM EMP A
ORDER BY A.DEPTNO
        ,A.SAL DESC;
----查询EMP 表中按工资降序排序，工资相同时按名字升序排序
SELECT
      *
FROM EMP A
ORDER BY A.SAL DESC
        ,A.ENAME;
----查询 EMP 表中各部门工资大于2000的员工人数，并且按人数从高到低排列
SELECT
       A.DEPTNO
       ,COUNT(A.SAL)
FROM EMP A
WHERE A.SAL>2000
GROUP BY A.DEPTNO
ORDER BY COUNT(A.SAL) DESC;
----查询 EMP 表中员工人数大于3的所在部门的部门编号，及对应的人数
SELECT
       A.DEPTNO
      ,COUNT(A.DEPTNO)
FROM EMP A
GROUP BY A.DEPTNO
HAVING COUNT(A.DEPTNO)>3;