Oracle笔记之select查询
概述
本篇博文中主要探讨以下内容:
-
查询列(字段)
1.1. 部分列
1.2. 所有列
1.3. 去除重复
1.4. 别名
1.5. 字符串
1.6. 伪列
1.7. 虚表
1.8. null -
查询行(记录)
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
select * from emp where sal <=all(900,2000);
--查询 工种为’SALESMAN’的员工信息 (注意 内容区分大小写)
--检索 工资 大于 2000员工名称 岗位 工资
--检索 工资 小于 3000员工名称 岗位 工资
--检索 工资 2000, 3000员工名称 岗位 工资
--查询部门编号为20的员工名称
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;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库