Oracle学习——第一讲 (常规查询）

Oracle 通用表

/*

emp 表                             dept  部门表                 salgrade 表

empno 员工编号               deptno  部门编号          grade  工资等级

ename  员工姓名             dname  部门名称          losal    工资范围下限

job       职位                      loc 工作地点                 hisal   工资范围上限

mgr     经理编号

hiredate  入职日期

sal        薪金

comm  奖金

deptno  部门编号

*/

Oracle实例演练

 

--查询dept表中的部门编号，部门名称，工作地点

SELECT deptno, dname, loc

 FROM dept

--查询emp表中的员工编号，员工名称，职位和薪金

SELECT empno, ename, job, sal 

FROM emp

--查询salgrade表中的所有数据 *

Select *

from salgrade

--查询表中的数据个数(返回的是表中的记录个数，避免记录数过多)

SELECT COUNT(*)

FROM emp

--查询emp表中的员工工资涨完500，以及这一年的总收入

SELECT sal, sal + 500, sal * 12

FROM emp

--员工转正后，月薪上调20%，请查询出所有员工转正后的月薪工资

SELECT sal, sal*(1 + 0.2)

FROM emp

--员工试用期6个月转正后，月薪上调20%

--请查询出所有员工第一年的年薪所得

SELECT sal, sal * 6  + sal * 6 * (1.2)

FROM emp

--员工试用期后，第一年加上所有奖金所得

--( NULL代表空值，参与运算时，结果为空)

SELECT sal， comm, sal * 6 + sal * 6 * (1.2) + 12 * comm

FROM emp 

--解决：

--调用函数NVL

--nvl(参数1，参数2)

--功能：若参数1不为NULL,返回参数1，否则返回参数2

--前提：两个参数类型必须一致

SELECT sal comm, sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0)

FROM emp

--让奖金为空的，显示0

SELECT comm, NVL(comm, 0)

FROM emp

--采用别名方式

--别名方式1  (使用空格)

SELECT sal 工资，comm 奖金，sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) 年薪

FROM emp

--别名方式2 （使用AS）

SELECT sal AS 工资，comm AS 奖金，sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) AS 年薪

FROM emp

--三种特殊情况需要加“”

--1、区分大小写

SELECT sal Sal, comm Comm, sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) total

FROM emp

SELECT sal "Sal", comm "Comm",sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) "Total"

FROM emp

--2.包括空格

SELEct sal "工   资", comm "奖 金", sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) "年 薪"

FROm emp

--3.包括特殊符号

SELECT sal "工￥资", comm "奖￥金",sal * 6 + sal * 6 * (1.2) + 12 * NVL(comm, 0) "年薪&"

FROM emp

--连接操作符||

SELECT ename, job, ename||job

FROM emp

--字符类型全部使用单引号''

SELECT ename || '的职位是'|| job

FROM emp

--去重关键字 DISTINCT

SELECT  DISTINCT deptno

FROM emp

--对两列完全一样的去重

SELECT DISTINCT ename, deptno

FROM emp

--WHERE  条件查询

--1.数值型作为条件

--查询部分10，员工的编号，姓名，部分编号

SELECT empno, ename,deptno

FROM emp

WHERE deptno = 10

--字符型作为条件

--查询job是CLERK的员工姓名，职位

SELECT ename, job, empno

FROM emp

WHERE job = 'CLERK'

--查询10部门，并且job是CLERK的员工姓名，职位

SELECT ename, job, deptno

FROM emp

WHERE  job = 'CLERK' AND deptno = 10

--3.日期型作为条件

--查询入职日期在81年以后的员工信息（DD-MON-RR）即：日-月-年

SELECT *

FROM emp

WHERE hiredate >= '01-1月-1981'

/*   number(4) 最大长度为4 （-9999-9999）

      varchar2(10)变长字符类型

      number(7, 2)最大长度7小数点2位*/

--查询部门不等于10的员工信息

--不等于<> or  != 都可以

SELECT *

FROM emp

WHERE deptno <> 10

--BETWEEN  AND (在....之间)

--查询工资在2000-3000的员工 姓名以及工资

SELECT ename, sal

FROM emp

WHERE sal BETWEEN 2000 AND 3000

--查询82年1月1日到83年12月31日

SELECT ename, sal, hiredate

FROM emp

WHERE hiredate BETWEEN '01-1月-1982'  AND '31-12月-1983'

--IN关键字

--查询部门10-20的员工姓名以及部门编号

SELECT ename, deptno

FROM emp

WHERE deptno IN (10, 20)

--查询工作是CLERK 和SALESMAN的员工姓名

SELECT ename, job

FROM emp

WHERE job IN ('CLERK', 'SALESMAN')

--like模糊查询

--%代表任意多个字符

--_代表一个字符

--查询姓名为以S开头的员工姓名

SELECT ename

FROM emp

WHERE ename LIKE 'S%'

--没有任何返回结果

Select  *

 from emp

  where ename like 'M_%'

  and comm = null;

--查询姓名以第二个S开头

SELECT ename

FROM emp

WHERE ename LIKE '_S%'

--查询倒数第二个字符为T的

SELECT ename

FROM emp

WHERE ename LIKE '%T_'

--查询姓名中包括C的

SELECT ename

FROM emp

WHERE ename LIKE '%C%'

--查询S@_中不带@的:S_

SELECT ename

FROM emp

WHERE ename LIKE 'S@_%' ESCAPE '@'

--查询S'

SELECT ename

FROM emp

WHERE ename LIKE 'S''%'

--IS NULL (NOT IN , NOT BETWEEN , IS NOT)

--查询奖金为空

SELECT ename, sal, comm

FROM emp

WHERE comm IS NULL

--AND OR 优先级：AND > OR

SELECT ename, job, sal

FROM emp

WHERE (job = 'SALESMAN' OR job = 'PRESIDENT') AND sal > 1500

SELECT ename, job, sal

FROM emp

WHERE job = 'SALESMAN' OR (job = 'PRESIDENT' AND sal > 1500)

--ORDER BY 排序(默认是升序)

--升序ASC

--降序DESC

--两者的书写和执行都在最后

--工资升序排列

SELECT *

FROM emp

ORDER BY sal ASC

--工资降序排列

SELECT *

FROM emp

ORDER BY sal DESC

--按照列别名排序

SELECT  ename, sal  工资

FROM emp

ORDER BY 工资 DESC

--按照列序号排序：对第二列进行排序

SELECT ename, sal

FROM emp

ORDER BY 2 DESC

--进行多列排序：部门升序，工资降序

SELECT ename, sal, deptno

FROM emp

ORDER BY deptno ASC, sal DESC

--查询员工姓名的第三个字母是a的员工姓名

SELECT ename

FROM emp

WHERE ename like '__A%'

--查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。

SELECT ename, hiredate, job

FROM emp

WHERE hiredate >= '07-9月-1982' AND job <> 'SALESMAN'

--查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。

SELECT ename, sal, deptno

FROM emp

WHERE sal >= 4500 AND (deptno = 10 OR deptno = 20)

--查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。

SELECT ename, hiredate, job

FROM emp

WHERE hiredate IN('01-1月-1982'， '31-12月-1983') AND (job like 'SALES%'  OR job like 'MAN%')

ORDER BY hiredate DESC

--查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。用IN(2000, 3000)的意思是2000  or 3000

SELECT ename, deptno, sal

FROM emp

WHERE (sal BETWEEN 2000 AND 3000) AND deptno <> 10

ORDER BY deptno ASC, sal DESC

--查询员工姓名中第四个字符是R的员工信息

select *

from emp

where  ename like '___R%';

法二：

select *

from emp

where instr(ename, 'R', 4, 1) = 4;

法三：

select *

from emp

where substr(ename, 4, 1) = 'R';

--查询员工工资等级大于2的员工姓名，工资，部门名称，工作地点

Select ename, sal, dname, loc

FROM emp e, dept d, salgrade

WHERE e.deptno = d.deptno

AND sal between losal and hisal

AND grade > 2;

-- 在员工表中查询出员工的工资，并计算应交税款：

--如果工资小于1000,税率为0，

--如果工资大于等于1000并小于2000，税率为10％，

--如果工资大于等于2000并小于3000，税率为15％，

--如果工资大于等于3000，税率为20％.

SELECT ename, sal, (case TRUNC(NVL(sal, 0) / 1000, 0)

                                   WHEN 0 THEN 0

                                     WHEN 1 THEN 0.1

                                       WHEN 2 THEN 0.15

                                         ELSE 0.2  end )  税率

FROM emp;

法二：

SELECT ename 员工姓名, sal 工资, DECODE(TRUNC(NVL(sal, 0) / 1000, 0),

                                                    0, 0,

                                                    1, 0.1,

                                                    2, 0.15,

                                                     0.2) 税率

FROM emp;