Oracle 基本查询：dual、distinct、null、between and、in、like、order by、group by、dual、as、not、describe

蚩尤后裔-汪茂雄

已于 2025-04-18 15:01:00 修改

阅读量3.8k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： oracle 数据库 sql

于 2019-06-02 17:54:49 首次发布

本文链接：https://blog.csdn.net/wangmx1993328/article/details/90581383

Oracle 专栏收录该内容

21 篇文章

订阅专栏

本文介绍Oracle SQL的基本查询操作，包括dual表的使用、distinct去重、null处理、between and区间查询、in集合查询、like模糊查询、order by排序、group by分组、not条件取反等，并提供了详细的例子。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、SQL 语句大小写不敏感，可以写在一行或者多行。

Oracle 体系结构与查询语句执行顺序

1、数据库 -> 数据库实例 -> 表空间 -> 数据文件。

2、通常情况下 Oralce 数据库只会有一个实例叫 " orcl "。

Mysql 是多数据库的：新建一个 Java Web 应用，就会在 Mysql 中创建一个数据库，然后建表。

Oracle 是多用户的：新建 Java Web 应用，创建表空间，创建用户，用户去创建表。

3、Sql（结构化查询语言）主要分为：

DCL：数据控制语言，关键字有 grant、revoke ...

DDL：数据定义语言，关键字有 create、alter、drop、truncate ...

DML：数据操作语言，关键字有 insert、update、delete ...

DQL：数据查询语言，关键字有 select ...

4、查询语句格式：select [列名] [*] from 表名 [where 条件] [group 分组] [having 过滤] [order by 排序]。

执行顺序：from ...> where ...> group by ...> having ... > select ...> order by ...

1.where 和 having 都是对查询结果的一种筛选，where 不能放在group by 后面
2.having 是跟 group by 连在一起用的，放在 group by 后面，此时的作用相当于 where
3.where 后面的条件中不能有聚集函数，比如 sum(),avg() 等，而 having 可以

5、包含内外连接时的查询语句顺序：https://wangmaoxiong.blog.csdn.net/article/details/90664207。

SQL 操作符与单双引号

准备员工表与部门表测试数据

oracle 查询指定用户名下所有表：select * from all_tables where owner='SAF'; --SAF 为用户名称（必须大写）

查看当前登录用户下的所有表：select * from user_tables;

1、Oracle 12c 安装完成后，默认没有了以前的 soctt 用户，所以没有测试数据，这里手动建员工表与部门表，然后设置一些测试数据。

sql/oracle/Oracle 新建员工表和部门表.sql · 汪少棠/material - Gitee.com。

--创建员工表
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
);

--创建部门表
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

--添加约束
alter table emp add constraint emp_pk primary key(empno);
alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept foreign key(deptno) references dept;

--部门插入数据
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON ');

--员工表插入数据
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-08-1980', 'DD-MM-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, '张三', 'SALESMAN', 7698,TO_DATE('20-04-1981', 'DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-12-1981', 'DD-MM-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-10-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-07-1981', 'DD-MM-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-06-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-07-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-01-1982', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-10-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-11-1981', 'DD-MM-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-12-1983', 'DD-MM-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-04-1981', 'DD-MM-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, '李四', 'ANALYST', 7566,TO_DATE('3-07-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-09-1982', 'DD-MM-YYYY'), 1300, NULL, 10);

伪表/虚表（dual）与 as 设置别名

dual 伪表/虚表

1、dual 伪表/虚表：是 Oracle 中的一个实际存在的表,只有一个字段 DUMMY ，为 VARCHAR2(1) 型，任何用户均可读取，常用在没有目标表的 Select 语句块中。

2、DUAL 是属于 SYS schema 的一个表然后以 PUBLIC SYNONYM 的方式供其他数据库 USER 使用.

3、在创建数据库之后， DUAL 表中便已经被插入了一条记录,值为'X'。

4，主要用于补齐语法结构，以及计算等操作。如 "select 1+1" 在 Mysql 中可以正确执行，但是 Oracle 中会报错，因为语法不对，此时可以借助 dual 伪表："select 1+1 from dual".

-- 查看当前连接用户
select user from dual;
-- 查看当前日期、时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') nowdate from dual; 
-- 当计算器用
select 1+2 from dual;
-- 查看序列值
select 序列名.currval from dual;

as 设置别名

1、as 别名查询：别名中不能有特殊字符或者关键字，如果有，则需要使用双引号括起来。数字也是特殊字符。

2、as 关键字用于字段位置，不能用于表名。as 关键字可以省略。

select sysdate,9500 * 12 as 年薪 from dual;

select '速度' "路程/时间" from dual; -- 正确,含有特殊符号时必须使用双引号
select '速度' 路程/时间 from dual; -- 报错
select '速度' ''路程/时间'' from dual; -- 报错，不能是两个单引号
select '速度' 100 from dual; -- 报错，数字不能做别名

--as 设置别名，可以省略不写
select 
(case when t.pid < 30 then '华东区' when t.pid <60 then '华南区' else '华北区' end) as area,
 t.pid,t.pname pname,t.paddress as address,'true' as isShow from person2 t;

3、表或者视图设置别名。

如果使用了表的别名，则不能再使用表的真名。

--1、表可以设置别名，有了别名，编写sql时，编辑器就能自动提示字段。
--2、如果关联查询的多张表中有相同的字段，则必须指定别名，否则报错：ORA-00918:未明确定义列
SELECT t1.agency_id, t2.agency_code FROM BAS_AGENCY_INFO t1, BAS_AGENCY_EXT t2
 where t1.agency_id = t2.agency_id;
--3、同时表名也可以作为别名
SELECT dept.deptno, dept.dname, dept.loc FROM dept;
--4、两种方式不能一起用，如下所示报错：ORA-00904:"DEPT"。"DNAME": 标识符无效
SELECT t.deptno, dept.dname FROM dept t;

4、除了查询操作，update 、delete 等操作也可以设置别名，但是需要注意如果Oracle低于12c版本，比如11g，则表名加上后面的别名不能超过 30个字符长度，否则报错。

UPDATE emp "员工 表" SET "员工 表".comm = comm + 100;
DELETE from emp "员工 表" where "员工 表".comm = 1400;

distinct 关键字去重

distinct [[dɪˈstɪŋkt]，截然不同的;有区别的;]关键字去重：对于结果中重复的数据行，如果想要去掉它，则可以使用 distinct 关键字，它去除结果集中重复的数据行。

select tabtype from tab;  --不加 distinct 时的效果
select distinct tabtype from tab; -- 去重后的效果
select distinct * from tab t; --多个字段也是同理，去除重复的数据行

null 值不能做四则运算、逻辑运算

1、null 值不能做四则运算。比如 col_a + 100，col_a 为 null 时，结果为 null。col_a =null 或者 col_a != null 操作，无论 col_a 列的值是多少，结果恒为 false。

2、null 值不能做比较运算，比如 col_a = col_b 或者 col_a != col_b 操作，列 col_a 与 col_b 的值都不为null，且值相当于时，结果为 true，否则任意一个值为 null 时，比较的结果恒为 false。

3、null 值进行 like 与 not like 结果恒为 false。必须使用 is null 或者 is not null。

4、null 值进行 in(x,y,z) 或者 not in (x,y,z) 结果恒为 false。

select * from emp;--查看所有
select empno,job,sal * 12,comm from emp;--查询员工年薪。假设员工一年内薪资不变
--查询员工年薪加奖金。为 null 的字段表示值不确定，不能做四则运算，否则运算出来的结果也会为null，即结果不确定
--nvl(param1,param2)：如果参数 param1 为 null，则返回参数 param2 的值
select empno,job,sal * 12 + nvl(comm,0),comm from emp;

is null，is not null

1、is null：判断值是否为 null，is not null 判断值是否不为 null。

2、用法与 mysql 是一样的

select * from emp;--查看所有
select * from emp where comm is not null;--查询有奖金的员工
select * from emp where comm is null;--查询没有奖金的员工

"||" 连接符

1、Java 中字符串拼接可以使用 "+" 号，Oracle 使用自己独有的 "||" 符合进行字符串拼接。

2、Oracle 中双引号通常在设置别名的时候使用，单引号在设值时使用。

3、除了 "||" 符合拼接字符串，也可以使用 concat(str1,str2) 函数进行拼接两个字符串。

select * from emp;--查看所有
--必须是单引号，双引号会报错。单引号才是设置值时使用。双引号通常用于设置别名
select empno,'尉迟 ' || ename,job,sal from emp
select empno,concat('上官',ename) ,job,sal from emp;--必须是单引号

between and 区间查询

与 mysql 用法一样。区间查询包含边界，是闭区间。

select * from emp;--查看所有
select * from emp where sal between 1100 and 1500;--查询工资在 [1100,1500] 用户
select * from emp where sal >= 1100 and sal <= 1500;--查询工资在 [1100,1500] 用户
select * from emp where sal not between 2000 and 5250.50; --查询工资不在 [2000,5250.50] 中的员工

in 关键字集合查询

1、与 mysql 用法一样。

2、注意：in(x,y,z,...) 括号中个数不能超过 1000 个，否则执行报错；如果是子查询的方式，如 uid in(select uid from xxx where xxx)，则无论括号中子查询结果是多少都没关系。

3、注意：in 中参数可以为 null，而 not in 中不能有 null，否则查询结果恒为空，即查不到数据。

--查询部门是30号，且工作为 'SALESMAN'的员工
--in 注意事项：参数中即使含有 null ，也并不会查询出值为null的记录
SELECT T.* FROM emp t where (t.deptno,t.job) in ((30,'SALESMAN', null));

-- 查询姓名不是 'WARD','SCOTT','FORD','MILLER','ZhangSan' 的用户（值为null的记录不会被查出）
-- not in 注意事项1：参数中不能有 null，否则结果恒为空
-- not in 注意事项2：来源数据中目标列的值为 null 时，无论 not in 的参数是什么，都不会被查出。
select * from emp where ename not in ('WARD','SCOTT','FORD','MILLER','ZhangSan');

like 模糊查询

和 mysql 用法一样。

1、"%" 匹配多个字符，下划线"_" 匹配单个字符。

2、如果有特殊字符，则需要使用 "escape" 关键字转义。

3、特别注意：null 值进行 like 与 not like 结果恒为 false。必须使用 is null 或者 is not null。

select * from emp;--查看所有

select * from emp where ename like '__A%';--查询姓名中第三个字母为 'A' 的员工
select * from emp where ename like '%A%';--查询姓名中含有 'A' 字符的员工
select * from emp where ename like 'A%';--查询姓名中以 'A' 字符开头的员工
select * from emp where ename like '%E';--查询姓名中以 'E' 字符结尾的员工

--如果查询的字符串只是单纯的特殊字符，则必须转义,否则条件无效，相当于查所有。
--escpae 指定的字符表示就是 like 中使用的转义字符，可以是任意的，只需要 like 中与 escpe 保持一致即可
--escpe 中指定的字符，即 like 中的转义字符后面紧跟的必须是特殊字符，否则报错。
select * from emp where ename like '%_%'; --此时相当于查询所有
select * from emp where ename like '%\_%' escape '\';
select * from emp where ename like '%#_%' escape '#';
select * from emp where ename not like '%A%';--查询姓名中不含有 'A' 字符的员工

-- like 百分号还可以动态拼接
-- 在pl/sql中同样也可以用于动态拼接参数
-- 查询 dname 与 loc 第一个字符相同的数据
SELECT T.* FROM dept t where t.dname like substr(t.loc, 1, 1) || '%';

order by 排序

1、升序：order by asc；降序：order by desc。默认为升序。放在 Sql 的最后。后面可以跟列，表达式，别名，序号。

2、如果排序字段可能等于 null，则可以使用 "nulls firset" 或者 "nulls last" 指定 null 值的数据在前还是在后。

3、如果要按照多列进行排序，则规则是先按照第一列排序如果相同，则按照第二列排序:

select * from emp order by comm ;--按奖金由低到高排序，此时 null 值默认在最后
select * from emp order by comm asc;--按奖金由低到高排序，此时 null 值默认在最后
select * from emp order by comm nulls first;--按奖金由低到高排序，指定 null 值在最前
select * from emp order by comm desc ;--按奖金由高到低排序，此时 null 值默认在最前
select * from emp order by comm desc nulls last;--按奖金由高到低排序，指定 null 值在最后
select * from emp order by sal ,comm desc;--按薪资升序，奖金降序。当薪资出现相等时，则再按照奖金进行排序
select empno, ename, sal, nvl(sal, 0) * 12 年薪  from emp order by nvl(sal,0) * 12 desc;
SELECT empno, ename, sal, nvl(sal, 0) * 12 年薪  FROM emp T order by 年薪 desc;
--按第4列(年薪)排序
select empno, ename, sal, nvl(sal, 0) * 12 年薪 from emp order by 4 desc;年薪 from emp order by 4 desc;

group by 分组

1、group by 分组表达式格式：select 查询的列,分组后的操作/组函数 from 表名 where 条件 group by 分组条件 having 条件筛选。

2、在 SELECT 查询的列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。

--如下所示，字段d会报错，而字段e不会报错
SELECT a,b,d,max(e) FROM xxx T group by a,b,c ;

1）分组条件：通常就是需要分组/显示的字段(1个或多个)，select 后面的查询的列可以省略，group by 后面的分组条件必须写。
2）分组后的操作：比如求和、求平均值、max、min 等
3）where：针对表中的列发挥作用，对查询数据附加条件
4）having：针对分组查询的结果发挥作用，筛选组

分组统计各部门的平均薪资	select deptno,avg(sal) from emp group by deptno;
分组统计各部门的平均薪资，过滤出大于 2000 的部门	select deptno,avg(sal) from emp group by deptno having avg(sal) >2000
分组统计各部门的平均薪资，过滤出大于 2000 的部门,且以倒序排列	select deptno,avg(sal) from emp group by deptno having avg(sal) >2000 order by avg(sal) desc;
统计每年入职的员工人数	select to_char(hiredate,'yyyy'）,count(1) from emp group by to_char(hiredate,'yyyy'）;
查询 gbm_bs_tx_sy_interface 表中身份证(id_card) 出现2次及以上的身份证，并显示个数	select t.id_card,count() from gbm_bs_tx_sy_interface t where t.agency_id = 20802 group by t.id_card having count() >=2;
查询各个部门中各个岗位的薪资最小值	select min(sal) from emp t group by t.deptno,t.job;

-- 查询平均工资最低的部门信息，以及该部门的平均工资
--     DEPTNO  DNAME  LOC  AVG_SAL
--     1	30	SALES	CHICAGO	1566.67
SELECT d.*,  round((select avg(sal) from emp where deptno = d.deptno), 2) avg_sal
  FROM dept d
 WHERE deptno =
       (SELECT deptno
          FROM emp
         GROUP BY deptno
        HAVING avg(sal) = (SELECT min(avg(sal)) FROM emp GROUP BY deptno));

select 后面的查询列省略的写法，在复杂查询中用的多，如：rowid 伪列删除表中重复数据

not 条件取反

1、oracle 中 not 用于对指定的条件取反，当指定的条件为真时，not 的结果为假，当指定的条件为假时，not的结果为真，语法：Select xxx from xxx where not 条件表达式。

2、逻辑运算符：

and 双值运算符	与、如果左右两个条件都为真，则得到的值就为真
or 双值运算符	或、只要左右两个条件有一个为真，则得到的值就为真
not 单值运算符	非、取反运算符，相当于 Java 的 !(条件表达式)

--查询员工信息，除去10号部门中薪水大于2k的人
select * from emp t where not (t.deptno = '10' and t.sal > 2000) order by t.deptno, t.sal;

--下面这个写法需要特别注意：
--错误理解：查询全部员工信息，除去20号部门中 job='SALESMAN' 的员工
--正确理解：查询全部员工信息，且20号部门中只查询 job='SALESMAN' 的员工
select * from emp t where not (t.deptno = '20' and t.job not in('SALESMAN')) order by t.deptno, t.sal;

describe 显示表结构

1、使用 desc[ribe] 命令，查询表、视图结构，在命令窗口中执行。

SQL> desc emp
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
EMPNO        NUMBER(4)                     主键     
ENAME        VARCHAR2(10) Y                姓名     
JOB          VARCHAR2(9)  Y                工作岗位 
MGR          NUMBER(4)    Y                职务     
HIREDATE     DATE         Y                雇佣日期  
SAL          NUMBER(7,2)  Y                薪水     
COMM         NUMBER(7,2)  Y        0       奖金     
DEPTNO       NUMBER(2)    Y                部门编号