ORACLE数据库学习笔记（三）（算数、逻辑、关系、关联、NULL值、UNOIN)

122 阅读 0 评论 81 点赞

我是靠谱客的博主俭朴小熊猫，这篇文章主要介绍ORACLE数据库学习笔记（三）（算数、逻辑、关系、关联、NULL值、UNOIN)，现在分享给大家，希望可以做个参考。

操作符

Oracle开发中，依然存在算术运算，关系运算，和逻辑运算。

算术运算

Oracle中的算术运算符，只有+、-、*、/四个，其中除号(/)的结果是浮点数。
求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。

复制代码

1
2
select mod(11,5)from dual;

复制代码

1
2
3
4
5
6
7
8
--【例】每名员工年终奖是2000元，请显示基本工资在2000元以上的员工的月工资，年总工资。--(先不计算奖金)
SELECT 
      E.ENAME
     ,E.SAL
     ,(E.SAL * 12 + 2000) 
FROM EMP E 
WHERE E.SAL > 2000;

复制代码

1
2
3
4
5
6
7
---- 求 emp表中 20 号部门工资大于2000 的 年总工资 --(先不计算奖金)。
SELECT
      A.SAL*12
FROM EMP A
WHERE A.SAL>2000
    AND A.DEPTNO=20;

复制代码

1
2
3
4
5
6
7
8
--- 求 30 号部门 最高工资、最低工资、最高工资比最低工资高多少钱
SELECT
     MAX(A.SAL)
    ,MIN(A.SAL) 
    ,MAX(A.SAL)-MIN(A.SAL) 
FROM EMP A
WHERE A.DEPTNO=30;

关系运算和逻辑运算

Oracle中 Where 子句 X 中经常见到关系运算和逻辑运算。

常见的关系运算有：
运算符说明

复制代码

1
2
3
4
5
6
7
  =           			等于  
  >           			大于
<>或者!=      			不等于 
<=            			小于或者等于
<             			小于  
>=            			大于或者等于

三个逻辑运算符优先级：NOT>AND>OR

字符串连接操作符（||）

在Oracle中，字符串的连接用双竖线（||）表示。

复制代码

1
2
3
4
5
6
---【例】在EMP表中，查询工资在2000元以上的姓名以及工作。
SELECT
      A.ENAME||' 的工作是 '||A.JOB
FROM EMP A
WHERE A.SAL>2000;

代码解析：
Oracle中字符串可以用单引号，存在特殊字符的时候，必须用双引号。

DISTINCT操作

在Oracle查询中结果中，可能出现若干行相同的情况，那么可以使用 DISTINCT 消除重复行。

复制代码

1
2
3
4
5
6
---【例】DISTINCT 消除重复行（GROUP BY(分组去掉重复数据)性能较好）
--查询EMP表中有哪些部门
SELECT DISTINCT
      A.DEPTNO
FROM EMP A ;

复制代码

1
2
3
4
5
SELECT
      A.DEPTNO
FROM EMP A 
GROUP BY  A.DEPTNO;

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--查询 EMP 表中每个部门都有哪些工作岗位

SELECT
      A.DEPTNO
     ,A.JOB
FROM EMP A
GROUP BY A.DEPTNO,A.JOB
ORDER BY A.DEPTNO;

SELECT DISTINCT
      A.DEPTNO
     ,A.JOB
FROM EMP A
ORDER BY A.DEPTNO;

NULL操作

如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不是0或者空格，空值是指未赋值、未知或不可用的值。
任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。

在查询条件中 NULL 值用 IS NULL 作条件，
非NULL值用 IS NOT NULL 做条件。

空值的一些特性：
1、空值跟任何值进行算术运算，得到的结果都为空值
2、空值跟任何值进行关系运算，得到的结果都为不成立
3、空值不参与任何聚合运算
4、排序的时候，空值永远是最大的

复制代码

1
2
3
4
5
6
--【例】查询EMP表中没有发奖金的员工。
SELECT
     *
FROM EMP A
WHERE A.COMM IS NULL;

条件有两种表示方法：IN 操作

在 Where 子句中可以使用 IN 操作符来查询其列值在指定的列表中的行。

复制代码

1
2
3
4
5
6
---【例】查询出工作职责是SALESMAN、PRESIDENT或者ANALYST的员工。
SELECT
      *
FROM EMP A
WHERE A.JOB IN('SALESMAN','PRESIDENT','ANALYST');

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-----用两种方法找出10、20号部门中工资大于1500的员工姓名和工资以及部门编号
SELECT
     A.ENAME
    ,A.SAL
    ,A.DEPTNO 
FROM EMP A
WHERE A.DEPTNO IN(10,20)
    AND A.SAL>1500;

--------------------
SELECT
     A.ENAME
    ,A.SAL
    ,A.DEPTNO 
FROM EMP A
WHERE A.DEPTNO =10
    AND A.SAL>1500
    OR A.DEPTNO =20
    AND A.SAL>1500;
---------------------

BETWEEN…AND…–一个范围内的数据，包含临界值
在 WHERE 子句中，可以使用 BETWEEN 操作符来查询列值包含在指定区间内的行。

复制代码

1
2
3
4
5
6
---【例】查询工资从1000到2000之间的员工。
SELECT
     *
FROM EMP A 
WHERE A.SAL BETWEEN 1000 AND 2000;

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
-----查询EMP 表中在1981年1月1日到1981年10月1日之间入职的员工信息
SELECT
      *
FROM EMP A
WHERE A.HIREDATE BETWEEN TO_DATE('19810101','YYYYMMDD')AND TO_DATE('19811001','YYYYMMDD');

----------------------------------
SELECT
      *
FROM EMP A
WHERE A.HIREDATE >= TO_DATE('19810101','YYYYMMDD')
    AND A.HIREDATE<=TO_DATE('19811001','YYYYMMDD');

复制代码

1
2
3
4
5
6
7
8
---------------------------------------------
SELECT
      *
FROM EMP A
WHERE TO_CHAR(A.HIREDATE,'YYYYMMDD' ) BETWEEN '19810101' AND '19811001';

---------------------------------------------

LIKE模糊查询

在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。
模糊查询使用 LIKE 关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_”:

?%：表示零个或者多个任意字符。
?：代表一个任意字符。
---- 倒数第一位是A的字符串
‘%A’
---- 倒数第二位是A的字符串
'%A’
----第三位是A 和最后一位是S
‘__A%S’

复制代码

1
2
3
4
5
 ----查询EMP表中，名字第1位是'A'的员工信息
 SELECT *
 FROM EMP A 
 WHERE A.ENAME LIKE 'A%';

复制代码

1
2
3
4
5
6
7
 ----查询EMP表中，名字第三位是'R'或者倒数第二位是'E'的员工信息
SELECT*
FROM EMP A 
WHERE A.ENAME LIKE '__R%'
   OR A.ENAME LIKE '%E_' ;
----------------------------------

复制代码

1
2
3
4
5
6
SELECT*
FROM EMP A
WHERE SUBSTR(A.ENAME,3,1)='R'
   OR SUBSTR(A.ENAME,-2,1)='E';
------------------------------------

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
----查询 EMP 表中 1982年入职的员工信息
SELECT
   *
FROM EMP A
WHERE TO_CHAR(A.HIREDATE,'YYYYMMDD') LIKE '1982%';
-------------------


SELECT
   *
FROM EMP A
WHERE SUBSTR(TO_CHAR(A.HIREDATE,'YYYYMMDD'),1,4) = '1982';

复制代码

1
2
3
4
5
6
7
8
9
10
---- 查询 EMP 表中 员工姓名倒数第二是 'I' 或者第二位是‘A’的员工信息
SELECT *
FROM EMP A
where a.ename like '%I_' 
    OR A.ENAME LIKE '_A%';
--- TO_NUMBER 转化成数字的数据类型
--- TO_DATE  转化成日期类型的数据类型
--- TO_CHAR 转化成字符串的数据类型
-----------------------------------

集合运算

集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

INTERSECT(交集)，–返回两个查询共有的记录。

复制代码

1
2
3
4
SELECT A.DEPTNO FROM EMP A
    INTERSECT
SELECT B.DEPTNO FROM DEPT B  ;

UNION ALL(并集)，–返回各个查询的所有记录，包括重复记录。

复制代码

1
2
3
4
5
6
7
8
9
10
11
SELECT A.DEPTNO FROM EMP A
    UNION ALL
SELECT B.DEPTNO FROM DEPT B  ; 

/*SELECT
      A.DEPTNO
     ,B.DEPTNO 
FROM EMP A
INNER JOIN DEPT B
        ON A.DEPTNO=B.DEPTNO;*/

UNION(并集)，–返回各个查询的所有记录，不包括重复记录。

复制代码

1
2
3
4
SELECT A.DEPTNO FROM EMP A
    UNION
SELECT B.DEPTNO FROM DEPT B  ;

MINUS(补集)，–返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。–（数据效验的时候常用）

复制代码

1
2
3
4
5
6
7
8
SELECT A.DEPTNO FROM EMP A
    MINUS
SELECT B.DEPTNO FROM DEPT B  ; 

SELECT B.DEPTNO FROM DEPT B 
    MINUS
SELECT A.DEPTNO FROM EMP A ;

当使用集合操作的时候，要注意：
1.查询所返回的列数以及列的类型必须匹配，列名可以不同。
2.只有UNION ALL不会去重。其他三个都需要排序后去重，性能比较差。
3、这些查询得到的结果都是行并列，显示的字段名都是以第一个查询结果集的字段名

复制代码

1
2
3
4
5
6
7
8
9
10
---【例】查询出dept表中哪个部门下没有员工。只需求出dept表中的部门号和emp表中的部门号的补集即可。
SELECT
      A.DEPTNO
FROM DEPT A
MINUS
SELECT
      A1.DEPTNO
FROM EMP A1;
------------------------------------------

连接查询

在实际应用中，单表查询较少，经常是从多张表中查询数据，这就需要多表连接查询。
通过各个表之间共同列的关联性（两个表或者多个表共同有的字段），可以查询存放在多个表中的不同实体的信息
，将多个表以某个或某列为条件进行连接操作而检索出关联数据的过程称为连接查询。

包括内联接(inner join)、外联接(outer join)。Oracle中对两个表或者若干表之间的外联接用（+）表示。

内连接(inner join)：inner 可省略 —只显示两个表共同有的数据

复制代码

1
2
3
4
5
6
7
8
语法结构：
        SELECT
             A.*
            ,B.* 
        FROM 表名1 A
        INNER JOIN 表名2 B
                ON A.字段=B.字段 ---（两个字段的数据要相同）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--------------------------------
SELECT
      *
FROM EMP A
INNER JOIN DEPT B
        ON A.empno=B.DEPTNO;

----------------------------------------
SELECT
      *
FROM DEPT A
INNER JOIN  EMP B
        ON A.DEPTNO=B.DEPTNO;

--------------------------------------------

LEFT JOIN :左外关联（join 的左边是主表，不管两个表关联不关联得上，都会保留主表的数据）

复制代码

1
2
3
4
5
6
7
8
语法结构：
        SELECT
             A.*
            ,B.* 
        FROM 表名1 A
        LEFT JOIN 表名2 B
                ON A.字段=B.字段 ---（两个字段的数据要相同）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
----------------------------------------
SELECT
      *
FROM EMP A
LEFT JOIN DEPT B
        ON A.DEPTNO=B.DEPTNO;

----------------------------------------
SELECT
      *
FROM DEPT A
LEFT JOIN  EMP B
        ON A.DEPTNO=B.empno;

INNER JOIN 和 LEFT JOIN 的区别

区别1、INNER JOIN 时只显示两个表共同有的数据
LEFT JOIN 时 join 的左边是主表，不管两个表关联不关联得上，都会保留主表的数据
区别2、过滤条件的区别

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT
      *
FROM EMP A
LEFT JOIN  DEPT B
        ON A.DEPTNO=B.DEPTNO
where A.SAL>2000 ;   -----LEFT JOIN 的时候：过滤条件放在 WHERE 后面时，会影响最后的结果
------------
SELECT
      *
FROM EMP A
LEFT JOIN  DEPT B
        ON A.DEPTNO=B.DEPTNO  -----LEFT JOIN 的时候：过滤条件放在 ON 后面时，不会影响最后的结果
       AND A.SAL>2000
ORDER BY A.SAL DESC ;

-----------------------------------------------------------------------

SELECT
      *
FROM EMP A
inner JOIN  DEPT B
        ON A.DEPTNO=B.DEPTNO
where A.SAL>2000 ;   -----inner JOIN 的时候：过滤条件放在 WHERE 后面时，会影响最后的结果
------------
SELECT
      *
FROM EMP A
inner JOIN  DEPT B
        ON A.DEPTNO=B.DEPTNO  -----inner JOIN 的时候：过滤条件放在 ON 后面时，会影响最后的结果
       AND A.SAL>2000 ;