Oracle笔记之select查询干货

QJ程序员 2021-11-28

1279

概述
本篇中主要探讨以下内容：

1.查询列(字段）

1.1. 部分列
1.2. 所有列
1.3. 去除重复
1.4. 别名
1.5. 字符串
1.6. 伪列
1.7. 虚表
1.8. null

2.查询行(记录)

2.1. 比较条件
2.2. 且或非
2.3. null
2.4. 集合操作
2.5. like :模糊查询
2.6. in 与 exists
2.7. 获取所有行的记录
2.8. 排序

SELECT [DISTINCT] {*,column alias,..}
FROM table alias
WHERE 条件表达式
ORDER BY 排序字段列表 [asc|desc]

解析步骤: from -> where -> select -> order by

1.查询列(字段）

select distinct *|字段|表达式 as 别名 from 表 表别名
SELECT * FROM 表名; ->查询某个表中所有的记录的所有字段信息
SELECT 列名 FROM 表名; ->查询某个表中所有的记录的指定字段信息
SELECT 列名1,列名2 FROM 表名; -> 查询某个表中所有的记录的字段1 字段2
SELECT distinct 列名 FROM 表名; ->去除重复记录
SELECT 表达式 FROM 表名; ->查询表达式
SELECT xxx as 别名 FROM 表名 表别名 ->使用别名

1.1. 部分列

查询部分字段，指定的字段名:

--1)、检索单个列
select ename from emp; --查询雇员姓名
--2)、检索多个列
select deptno,dname,loc from dept; --查询部门表
的deptno,dname, loc 字段的数据。
--以下查询的数据顺序不同(查询的字段顺序代表数据顺序)
select loc,dname,deptno from dept;
select deptno,dname,loc from dept;

‍

1.2. 所有列

查询所有的字段通配符 * ( 书写方便、可以检索未知列;但是降低检索的性能) ,数据的顺序跟定义表结构的顺序一致

--1)、检索所有列1
select * from dept; --查询部门的所有信息
--2)、检索所有列2
select deptno,dname,loc from dept; --查询部门的所有信息

1.3. 去除重复

使用distinct去重，确保查询结果的唯一性

select distinct deptno from emp; --去重

1.4. 别名

使用别名便于操作识别、隐藏底层信息。存在字段别名和表别名

select ename as "雇员 姓名" from emp;
select ename "雇员姓名" from emp;
select ename 雇员姓名 from emp;
select ename as 雇员姓名 from emp;
select ename as " Ename" from emp;
select 'my' from emp;sql
select ename||'a'||'-->' info from emp;

as: 字段别名可以使用as;表别名不能使用as
“” : 原样输出，可以存在空格与区分大小写

1.5. 字符串

使用’’表示字符串(注意区分””) ，拼接使用 ||

select 'my' from emp;
select ename||'a'||'-->' info from emp;

1.6. 伪列

不存在的列，构建虚拟的列

select empno, 1*2 as count,'cmj' as
name,deptno from emp;

1.7. 虚表

用于计算表达式，显示单条记录的值

select 1+1 from dual;

1.8. null

null 遇到数字参与运算的结果为 null,遇到字符串为空串

select 1+null from dual;
select '1'||null from dual;
select 1||'2'||to_char(null) from dual;
select ename,sal*12+comm from emp;
--nvl内置函数，判断是否为null,如果为空，取默认值0，否
则取字段实际值 select ename,sal*12+nvl(comm,0) from emp;

2.查询行(记录)

where 过滤行记录条件 ,条件有

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 between and
b)、and 、or、 not、 union、 union all、
intersect 、minus
c)、null :is null、 is not null、 not is
null
d)、like :模糊查询 % _ escape(‘单个字符’)
f)、in 、 exists(难点) 及子查询

2.1. 比较条件

= 、>、 <、 >=、 <=、 !=、 <>

select * from emp where deptno !=20;
select * from emp where deptno <>20;
select * from emp where sal between 800 and950; 
--between and是成对出现的
--查询 员工的年薪大于20000的 员工名称、岗位 年薪
--1)、nvl
select ename,job,12*(nvl(comm,0)+sal) income from emp;
--2)、年薪大于20000
--错误不能使用别名: select ename,job,12*(nvl(comm,0)+sal) income from emp where income>2000;
--a)、嵌套一个: 查询在前 过滤在后
select ename,job,income from (select ename,job,12*(nvl(comm,0)+sal) income from emp) where income>2000;
--b)、不使用别名 (推荐) :过滤在前，查询在后
select ename,job,12*(nvl(comm,0)+sal) income
from emp where 12*(nvl(comm,0)+sal) >2000 ;
--了解 any some all
-- >=any(值列表) 大于最小值<=any(值列表)小于最大值
select * from emp where sal >=any(900,2000);
select * from emp where sal <=any(900,2000);
-- some与any 一样的效果
-- all 大于最大值 小于最小值
select * from emp where sal >=all(900,2000);
select * from emp where sal <=all(900,2000);
--查询 工种为’SALESMAN’的员工信息 （注意 内容区分大小写）
--检索 工资 大于 2000员工名称 岗位 工资
--检索 工资 小于 3000员工名称 岗位 工资
--检索 工资 2000, 3000员工名称 岗位 工资
--查询部门编号为20的员工名称

2.2. 且或非

and、 or、 not

select * from emp where sal>=900 and sal<=950;
--查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门 编号，工资
--查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门 编号，工资
--查询 岗位 不是 CLERK 员工名称 部门编号，工资

2.3. null

null不能使用条件判断，只能使用is

--null是不知道
不知道 不等于 不知道
不知道 是 不知道
select * from emp where comm is null;
--不能写成
--select * from emp where comm = null;这样会报错
select * from emp where comm is not null;

2.4. 集合操作

Union、Union All、Intersect、Minus

Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

--查询工资大于1500 或 含有佣金的人员姓名
--union 去除重复行
select ename from emp where sal>1500 union select ename from emp where comm is not null;
-- union all 不去除重复行
select ename from emp where sal>1500 union all
select ename from emp where comm is not null;
--查询显示不存在雇员的所有部门号。
select deptno from dept minus select distinct deptno from emp
--查询工资大于1500 且 含有佣金的人员姓名
select ename,sal,comm from emp where sal>1500
intersect
select ename,sal,comm from emp where comm is not null

2.5. like :模糊查询

模糊查询，使用通配符:

%:零个及以上(任意个数的)的字符
_:一个字符
遇到内容中包含 % _ 使用escape(‘单个字符’)指定转义符

--查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
--查询员工姓名中包含第二个A的员工名称信息
select * from emp where ename like '_A%';
--数据中 员工姓名中 存在 _ % ,如何查找：
--1)、编写测试数据
insert into emp(empno,ename,sal) values(1000,'t_%test',8989); 
insert into emp(empno,ename,sal) values(1200,'t_tes%t',8000);
--2)、查找
--查询员工姓名中包含字符%的员工名称 岗位 工资 部门编号
select ename,job,sal,deptno from emp where ename like '%a%%' escape('a');
--查询员工姓名中包含第二个_的员工名称 岗位 工资 部门编号

2.6. in 与 exists

in相当于使用or的多个等值，定值集合 ,如果存在子查
询，确保类型相同、字段数为1，如果记录多，效率不
高，用于一些少量定值判断上

select * from emp where sal in(900,800);
--子查询(查询中再有查询) in 只能存在一个字段
select * from emp where sal in (select sal from emp e where deptno=10);
--10或30部门的雇员信息
select * from emp where deptno in(10,30);
--部门名称为 SALES 或 ACCOUNTING 的雇员信息
select deptno from dept where dname in('SALES','ACCOUNTING'); 
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname IN
('SALES', 'ACCOUNTING'));
/*
便于理解 使用java思维
while(外层结果集){
  while(内层结果集){
    if(emp.deptno==10){
      syso("....");
    }
    if(emp.deptno==30){
      syso("....");
    }
  }
}
==>in :如果记录多，效率不高，用于 一些 少量定值判断上
*/

exists条件为true,存在记录则返回结果，后续不再继续比较查询，与查询的字段无关，与记录有关

--exists :条件为true,存在记录，则返回结果，后续不再继续 ,与字段无关，与记录有关
--exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true)
/*
while(外层结果集){
  while(内层结果集){
    if(emp.deptno==dept.deptno||true){
      syso("....");
      break; //跳出本层
    }
  }
}
*/
--无关系列
/**
while(emp 的14条记录){
  while(dept的2条记录){
    if(true){
      syso();
      break;
    }
  }
}
*/
select *
from emp
where exists
(select deptno,dname from dept where dname in
('SALES', 'ACCOUNTING'));
--加入关系列
/**
while(emp 的14条记录){
  while(dept的2条记录){
    if(e.deptno=d.deptno){
      syso();
      break;
    }
  }
}
*/
select * from emp e where exists (select deptno, dname from dept d where dname in ('SALES', 'ACCOUNTING') and e.deptno = d.deptno);
/**
while(emp 的14条记录){
  while(dept的2条记录){
    if(e.deptno!=d.deptno){
    syso();
    break;
    }
  }
}
*/
select *from emp e where exists (select deptno, dname from dept d
 where dname in ('SALES','ACCOUNTING')and e.deptno != d.deptno);
--分析以下结果
select *
  from emp e
 where not exists(select deptno, dname from dept d
 where dname in ('SALES','ACCOUNTING') and e.deptno = d.deptno);
 
select ename, sal
  from emp
 where sal in (select sal from emp e2 where e2.sal >= 2000);
 
select ename, sal
  from emp
 where exists (select ename, sal from emp e2 where e2.sal >= 2000); --exists
 
select empno, ename, sal
  from emp e1
 where exists (select empno, ename, sal, comm
  from emp e2 where comm is not null and e1.empno = e2.empno);
  
select empno, ename, sal from emp e1
 where exists (select empno, ename, sal, comm
  from emp e2
 where comm is not null and e1.deptno = e2.deptno);

2.7. 获取所有行的记录

select * from emp;
select * from emp where 1=1 ;
select * from emp where ename like '%';

2.8. 排序

使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。

顺序 :asc（默认） desc
多字段: 在前面字段相等时，使用后面的字段排序
空排序: 降序为 desc，注意 null 为最后

--按工资降序
select * from emp order by sal desc;
--null问题
select * from emp order by nvl(comm,0),comm desc;
select * from emp order by comm nulls first;
--查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;
--查询雇员姓名，年薪 按佣金排序 默认为升序(asc)，降序为desc，注意null为最后
select ename,(sal+nvl(comm,0))*12,comm total from emp order by comm desc;
--对部门编号为 20 或30的雇员，工资+佣金 进行升序排序，如果相同，则按姓名降序。
--1、查询20、30 雇员
select * from emp where deptno in(20,30);
--2、工资+佣金排序
select ename,sal,comm,sal+nvl(comm,0) c from emp where deptno in(20,30) order by c;
--3、多个字段排序使用, 排序的字段可以使构建出来的虚拟的字段
select ename,sal,comm from emp where deptno in(20,30) order by sal+nvl(comm,0),ename desc;

oracle

文章转载自QJ程序员，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle笔记之select查询干货

评论