Oracle 简单的高级查询

最新推荐文章于 2022-10-28 16:08:04 发布

bigdata7

最新推荐文章于 2022-10-28 16:08:04 发布

阅读量216

点赞数

分类专栏： # Oracle学习文章标签： oracle 数据库 database

by顾辞嘤嘤怪

本文链接：https://blog.csdn.net/qq_43636709/article/details/120873946

版权

Oracle学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

数据来源：– scott 里面的表数据

/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.5.0

Source Server         : MyOracle
Source Server Version : 110200
Source Host           : localhost:1521
Source Schema         : SCOTT

Target Server Type    : ORACLE
Target Server Version : 110200
File Encoding         : 65001

Date: 2021-10-20 20:06:01
*/


-- ----------------------------
-- Table structure for BONUS
-- ----------------------------
DROP TABLE "SCOTT"."BONUS";
CREATE TABLE "SCOTT"."BONUS" (
"ENAME" VARCHAR2(10 BYTE) NULL ,
"JOB" VARCHAR2(9 BYTE) NULL ,
"SAL" NUMBER NULL ,
"COMM" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of BONUS
-- ----------------------------

-- ----------------------------
-- Table structure for DEPT
-- ----------------------------
DROP TABLE "SCOTT"."DEPT";
CREATE TABLE "SCOTT"."DEPT" (
"DEPTNO" NUMBER(2) NOT NULL ,
"DNAME" VARCHAR2(14 BYTE) NULL ,
"LOC" VARCHAR2(13 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of DEPT
-- ----------------------------
INSERT INTO "SCOTT"."DEPT" VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO "SCOTT"."DEPT" VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO "SCOTT"."DEPT" VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO "SCOTT"."DEPT" VALUES ('40', 'OPERATIONS', 'BOSTON');

-- ----------------------------
-- Table structure for EMP
-- ----------------------------
DROP TABLE "SCOTT"."EMP";
CREATE TABLE "SCOTT"."EMP" (
"EMPNO" NUMBER(4) NOT NULL ,
"ENAME" VARCHAR2(10 BYTE) NULL ,
"JOB" VARCHAR2(9 BYTE) NULL ,
"MGR" NUMBER(4) NULL ,
"HIREDATE" DATE NULL ,
"SAL" NUMBER(7,2) NULL ,
"COMM" NUMBER(7,2) NULL ,
"DEPTNO" NUMBER(2) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of EMP
-- ----------------------------
INSERT INTO "SCOTT"."EMP" VALUES ('7369', 'SMITH', 'CLERK', '7902', TO_DATE('1980-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '800', null, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', TO_DATE('1981-02-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '1600', '300', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7521', 'WARD', 'SALESMAN', '7698', TO_DATE('1981-02-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '1250', '500', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7566', 'JONES', 'MANAGER', '7839', TO_DATE('1981-04-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2975', null, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', TO_DATE('1981-09-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '1250', '1400', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7698', 'BLAKE', 'MANAGER', '7839', TO_DATE('1981-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2850', null, '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7782', 'CLARK', 'MANAGER', '7839', TO_DATE('1981-06-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2450', null, '10');
INSERT INTO "SCOTT"."EMP" VALUES ('7839', 'KING', 'PRESIDENT', null, TO_DATE('1981-11-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5000', null, '10');
INSERT INTO "SCOTT"."EMP" VALUES ('7844', 'TURNER', 'SALESMAN', '7698', TO_DATE('1981-09-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '1500', '0', '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7900', 'JAMES', 'CLERK', '7698', TO_DATE('1981-12-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '950', null, '30');
INSERT INTO "SCOTT"."EMP" VALUES ('7902', 'FORD', 'ANALYST', '7566', TO_DATE('1981-12-03 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '3000', null, '20');
INSERT INTO "SCOTT"."EMP" VALUES ('7934', 'MILLER', 'CLERK', '7782', TO_DATE('1982-01-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '1300', null, '10');

-- ----------------------------
-- Table structure for SALGRADE
-- ----------------------------
DROP TABLE "SCOTT"."SALGRADE";
CREATE TABLE "SCOTT"."SALGRADE" (
"GRADE" NUMBER NULL ,
"LOSAL" NUMBER NULL ,
"HISAL" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of SALGRADE
-- ----------------------------
INSERT INTO "SCOTT"."SALGRADE" VALUES ('1', '700', '1200');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('2', '1201', '1400');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('3', '1401', '2000');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('4', '2001', '3000');
INSERT INTO "SCOTT"."SALGRADE" VALUES ('5', '3001', '9999');

-- ----------------------------
-- Indexes structure for table DEPT
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table DEPT
-- ----------------------------
ALTER TABLE "SCOTT"."DEPT" ADD PRIMARY KEY ("DEPTNO");

-- ----------------------------
-- Indexes structure for table EMP
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table EMP
-- ----------------------------
ALTER TABLE "SCOTT"."EMP" ADD PRIMARY KEY ("EMPNO");

-- ----------------------------
-- Foreign Key structure for table "SCOTT"."EMP"
-- ----------------------------
ALTER TABLE "SCOTT"."EMP" ADD FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO");

操作符

算术运算

只有 + - * / 四种， / 出来的结果是浮点数。求余运算只能借助函数：MOD(x, y) 返回 x / y 的余数

SQL> SELECT ENAME, SAL,(SAL*12+200) AS YEARSAL FROM EMP WHERE SAL > 2000;
ENAME           SAL      YEARSAL
---------- --------- ----------
JONES        2975.00      35900
BLAKE        2850.00      34400
CLARK        2450.00      29600
KING         5000.00      60200
FORD         3000.00      36200
SQL>

逻辑运算

AND OR NOT

关系运算

= <= >= < > !=/<>

字符串的连接

||

SQL> SELECT (ENAME ||'IS A'||JOB) AS "EMPLOYEE JOB" FROM EMP WHERE SAL > 2000;
EMPLOYEE JOB
-----------------------
JONESIS AMANAGER
BLAKEIS AMANAGER
CLARKIS AMANAGER
KINGIS APRESIDENT
FORDIS AANALYST
SQL>

字符串单引号和双引号都能用在Oracle 中但是如果有空格必须双引号

高级查询

消除重复行 DISTINCT

-- DISTINCT 写在SELECT 之后
SQL> SELECT DISTINCT DEPTNO FROM EMP;
DEPTNO
------
    30
    20
    10

SQL>

DISTINCT 写在SELECT 之后

Null 值操作

IS NULL / NOT IS NULL

WHERE 子句中的判断条件

SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL IS NOT NULL AND COMM IS NULL ;
ENAME      JOB             SAL      COMM
---------- --------- --------- ---------
SMITH      CLERK        800.00 
JONES      MANAGER     2975.00 
BLAKE      MANAGER     2850.00 
CLARK      MANAGER     2450.00 
KING       PRESIDENT   5000.00 
JAMES      CLERK        950.00 
FORD       ANALYST     3000.00 
MILLER     CLERK       1300.00 
8 rows selected

SQL>

NULL 值不等于 0 或者空格

IN 操作

IN / NOT IN :判断其列值是否是在列表中的列

WHERE 子句中的判断条件

SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE JOB IN (SELECT DISTINCT JOB FROM EMP WHERE SAL > 2000);
ENAME      JOB             SAL
---------- --------- ---------
CLARK      MANAGER     2450.00
BLAKE      MANAGER     2850.00
JONES      MANAGER     2975.00
KING       PRESIDENT   5000.00
FORD       ANALYST     3000.00

SQL>

BETWEEN…AND…

between a and b ：取值 [a, b] b包括边界的

WHERE 子句中的判断条件

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000;
ENAME      JOB             SAL
---------- --------- ---------
ALLEN      SALESMAN    1600.00
WARD       SALESMAN    1250.00
MARTIN     SALESMAN    1250.00
TURNER     SALESMAN    1500.00
MILLER     CLERK       1300.00

SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE SAL >= 1000 AND SAL <= 2000;

LIKE 模糊查询

% :代表零个或者多个任意字符
_: 代表一个任意字符
语法：LIKE '字符串'[ESCAPE + 转义字符]

通配符表达式	说明
`‘s%’`	以 s 开头的字符串
`_s%`	第二个字符是 s 的字符串
`%30\%%' escape ‘\’`	任意字符 + 30 + 百分号 + 任意字符 —》 `\%` 转义字符表示一个字符 `%` 百分号

-- 查找 以 J 开头 S 结尾的员工
SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE ENAME LIKE 'J%S';
ENAME      JOB             SAL
---------- --------- ---------
JONES      MANAGER     2975.00
JAMES      CLERK        950.00

SQL>

连接查询

连接查询是指多个表的数据显示在同一个返回结果集中。连接查询分为内联接（inner join）、外联结（outer join），外联结又分为左外联结（left outer join）和右外联结（right outer join）。书写 SQL 时，INNER 和 OUTER 可以省略不写

Oracle 中对多个表之间的外联结用 + 表示【写在等号条件两边的列的后面】

左，右外联结： a LEFT/RIGHT JOIN b ,a表作为基准，在a表的右边也就是后面去联结其它表的数据，左外的话这个b表的数据去对比a表的，后面b表不全则补NULL，相反，右外的话，这个a表去对比后面的b表数据，a却的补NULL

内联接：查询工资大于2000的员工姓名，部门，工作，工资。涉及 dept 和 emp两个表 INNER 可以省略

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行

SQL> SELECT e.ENAME, e.JOB, e.SAL, d.DNAME FROM EMP e, DEPT d WHERE e.DEPTNO = d.DEPTNO AND e.SAL > 2000;
ENAME      JOB             SAL DNAME
---------- --------- --------- --------------
CLARK      MANAGER     2450.00 ACCOUNTING
KING       PRESIDENT   5000.00 ACCOUNTING
FORD       ANALYST     3000.00 RESEARCH
JONES      MANAGER     2975.00 RESEARCH
BLAKE      MANAGER     2850.00 SALES

SQL> SELECT e.ENAME, e.JOB, e.SAL, d.DNAME FROM EMP e INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO WHERE e.SAL > 2000;

右外连接：查询每个部门下的员工姓名和工资【左表符合条件的数据和右表符合条件的数据按联结顺序去组合】

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

SQL> SELECT e.ENAME, e.SAL, d.DNAME FROM EMP e , DEPT d WHERE e.DEPTNO(+) = d.DEPTNO;
ENAME            SAL DNAME
---------- --------- --------------
KING         5000.00 ACCOUNTING
CLARK        2450.00 ACCOUNTING
MILLER       1300.00 ACCOUNTING
FORD         3000.00 RESEARCH
SMITH         800.00 RESEARCH
JONES        2975.00 RESEARCH
JAMES         950.00 SALES
TURNER       1500.00 SALES
MARTIN       1250.00 SALES
WARD         1250.00 SALES
ALLEN        1600.00 SALES
BLAKE        2850.00 SALES
                     OPERATIONS
13 rows selected

SQL> SELECT e.ENAME, e.SAL, d.DNAME FROM EMP e RIGHT JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

左外联结的写法

左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
```
SELECT e.ENAME, e.SAL, d.DNAME FROM EMP e LEFT JOIN DEPT d ON e.DEPTNO = d.DEPTNO;
SELECT e.ENAME, e.SAL, d.DNAME FROM EMP e , DEPT d WHERE e.DEPTNO = d.DEPTNO (+);
```
Oracle 特有的联结符 + ，条件中，出现在左边表示右外联结出现在右边表示左外联结

sql92的标准： … LFET/RIGHT JOIN… ON

子查询

在 SELECT,DELETE,UPDATE 内部可以出现 SELECT 语句。内部的 SELECT子句返回结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型：

单行子查询：不反回或者只返回一行结果

多行子查询：向外部返回零行，1行或者多行

-- 查询销售部下面的员工信息
SELECT ENAME, JOB ,SAL FROM EMP WHERE EMP.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');
ENAME      JOB             SAL
---------- --------- ---------
ALLEN      SALESMAN    1600.00
WARD       SALESMAN    1250.00
MARTIN     SALESMAN    1250.00
BLAKE      MANAGER     2850.00
TURNER     SALESMAN    1500.00
JAMES      CLERK        950.00
6 rows selected

SQL>

内部查询有时候查不到不会返回任何结果 null值也不会
单行子查询中，外部查询可以用比较运算符
内部查询返回的结果必须要和外部的条件字段相同
内部查询返回多行结果报错

与 ANY / ALL 搭配使用【任意一个 / 所有】

-- 查询emp 表中任意一个销售员（‘SALESMAN’） 工资低的员工信息
SQL> SELECT ENAME, JOB ,SAL FROM EMP WHERE SAL < ANY(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');
ENAME      JOB             SAL
---------- --------- ---------
SMITH      CLERK        800.00
JAMES      CLERK        950.00
WARD       SALESMAN    1250.00
MARTIN     SALESMAN    1250.00
MILLER     CLERK       1300.00
TURNER     SALESMAN    1500.00
6 rows selected

SQL>

Oracle中的伪列

在Oracle的表的使用中，实际表还会有一些附加的列，叫做伪列。伪列在表中不存储，只用作辅助查询。

ROWID

表中的每一行在数据中，都有一个物理地址，ROWID 返回的就是该行的物理地址，ROWID可以快速定位表的一行。可以唯一标识表中的一行。

SQL> SELECT ROWID,ENAME FROM EMP WHERE SAL > 3000;
ROWID                                                                            ENAME
-------------------------------------------------------------------------------- ----------
AAAE5wAAEAAAAFeAAH                                                               KING

SQL>

ROWNUM

在查询的结果集中，ROWNUM 为结果集中的每一行标识一个行号。可以通过它限制结果集中返回的行数。从1开始递增。

SQL> SELECT ROWNUM , ENAME ,SAL FROM EMP WHERE ROWNUM <= 5;
    ROWNUM ENAME            SAL
---------- ---------- ---------
         1 SMITH         800.00
         2 ALLEN        1600.00
         3 WARD         1250.00
         4 JONES        2975.00
         5 MARTIN       1250.00

SQL>

两者的区别

ROWID 是插入记录是生成的，ROWNUM 是查询返回结果集生成的。
ROWID标识的是行的物理地址，ROWNUM标识的是结果集行的次序

常用查询[分页查询]

-- 查询出工资最高的前5名员工
SQL> SELECT ROWNUM, T.* FROM (SELECT ENAME, JOB, SAL FROM EMP ORDER BY SAL DESC) T WHERE ROWNUM <= 5;
    ROWNUM ENAME      JOB             SAL
---------- ---------- --------- ---------
         1 KING       PRESIDENT   5000.00
         2 FORD       ANALYST     3000.00
         3 JONES      MANAGER     2975.00
         4 BLAKE      MANAGER     2850.00
         5 CLARK      MANAGER     2450.00

SQL>

排序会连同 ROWNUM 一起排序所以可以先排序再查询返回结果集

-- 分页的应用【查询emp中第5-10条记录】
SQL> SELECT * FROM (SELECT ROWNUM R,ENAME,JOB,SAL FROM EMP WHERE ROWNUM <= 10) WHERE R > 5;
         R ENAME      JOB             SAL
---------- ---------- --------- ---------
         6 BLAKE      MANAGER     2850.00
         7 CLARK      MANAGER     2450.00
         8 KING       PRESIDENT   5000.00
         9 TURNER     SALESMAN    1500.00
        10 JAMES      CLERK        950.00

SQL>

WHERE R > 5;
R ENAME JOB SAL

       6 BLAKE      MANAGER     2850.00
       7 CLARK      MANAGER     2450.00
       8 KING       PRESIDENT   5000.00
       9 TURNER     SALESMAN    1500.00
      10 JAMES      CLERK        950.00

SQL>

bigdata7

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 简单的高级查询

文章目录操作符算术运算逻辑运算关系运算字符串的连接高级查询消除重复行 DISTINCTNull 值操作IN 操作BETWEEN…AND…LIKE 模糊查询连接查询子查询Oracle中的伪列ROWIDROWNUM两者的区别常用查询[**分页查询**]数据来源：– scott 里面的表数据/*Navicat Oracle Data TransferOracle Client Version : 10.2.0.5.0Source Server : MyOracleSource Ser
复制链接

扫一扫