Oracle基本函数查询

2022-01-06 15:24:35

Oracle基本函数查询

查询行(记录)

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(难点) 及子查询

比较条件

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



select * from emp where deptno !=20;
select * from emp where deptno <>20;
select * from emp where sal between 800 and
950; --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的员工名称

且 或 非

	**and、 or、 no**

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

null

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

--存在佣金的员工名称
select * from emp where comm is null;
--不存在佣金的员工名称
select * from emp where comm is not null;
select * from emp where not comm is null;

集合操作

​ Union、Union All、Intersect、Minus

  • Union，并集(去重) 对两个结果集进行并集操作，不
    包括重复行同时进行默认规则的排序；
  • Union All，全集(不去重) 对两个结果集进行并集操
    作，包括重复行，不进行排序 ；
  • Intersect，交集(找出重复) 对两个结果集进行交集操
    作，不包括重复行，同时进行默认规则的排序；
  • Minus，差集(减去重复) 对两个结果集进行差操作，不
    包括重复行，同时进行默认规则的排序 
--查询工资大于1500 或 含有佣金的人员姓名
--union 去除重复行
select ename from emp where sal>1500
union
7.2.5. like :模糊查询
模糊查询，使用通配符:
%:零个及以上(任意个数的)的字符
_:一个字符
遇到内容中包含 % _ 使用escape('单个字符')指定转义
符
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;

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');
--查询员工姓名中包含第二个_的员工名称 岗位 工资 部门编
号

获取所有行的记录

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

排序

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

  • 顺序 :asc（默认） desc
  • 多字段: 在前面字段相等时，使用后面的字段排序
  • 空排序: 降序为 desc，注意 null 为最后
--按工资降序
7.3. 作业
1. 使用基本查询语句.
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) sal+nvl(comm,0),ename desc

函数

函数分为系统内置函数 自定义函数（后期学习的 plsql
中定义）；了解系统内置函数（方法），重点掌握
to_date，to_char （字符和日期的转换）根据函数的
返回结果，我们将函数分为单行函数和多行函数

  1. 单行函数:一条记录返回一个结果

  2. . 多行函数 组函数 聚合函数 (重点) :多条记录 返回一
    个结果 (重点）

    单行函数

    日期函数

    日期函数: 注意区分 db数据库时间 ，java应用服务器的
    时间。以一方为准
    oracle以内部数字格式存储日期年月日小时分钟秒

    • sysdate/current_date 以date类型返回当前的日期
    • add_months(d,x) 返回加上x月后的日期d的值
    • LAST_DAY(d) 返回的所在月份的最后一天
    • months_between(date1,date2) 返回date1和date2之
      间月的数目
    • next_day(sysdate,星期一) 下周星期一
当前时间
select current_date from dual where 1=1;
select sysdate from dual where 1=1;
修改日期（天数±）
-两天后的时刻
select sysdate+2 from dual;
修改月份
--当前5个月后的时间
select add_months(sysdate,5) from dual; --雇佣
日期 2个月的时间
select ename,hiredate, add_months(hiredate,2)
after from emp;
月份之差
--雇佣日期 距离现在的 月份数
select ename, months_between(sysdate ,
hiredate) from emp;
最后一天
--返回雇佣日期 当月最后一天的时间
select ename, last_day(hiredate) d from emp;
下一个星期的时间
--下一个星期二
select next_day(sysdate, '星期二') from dual;

转换函数（重点）

  • to_date(c,m) -> 字符串以指定格式转换为日期
  • to_char(d,m) -> 日期以指定格式转换为字符串
select to_date('2017-3-21 18:12:12', 'yyyy-mm-
dd hh24:mi:ss') time from dual;
select to_char(sysdate, 'yyyy-mm-dd') from
dual;
select to_char(sysdate, 'yyyy/mm/dd') from
dual;
select to_char(sysdate, 'yyyy\mm\dd') from
dual;
--注意中文的问题
--select to_char(sysdate,'yyyy年mm月dd日')
from dual;
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"')
from dual;
查询82的员工信息
select * from emp where hiredate like '%82%' ;
或
select * from emp where
to_char(hiredate,'yyyy')='1982';
或
select *
from emp
where hiredate between to_date('1982-01-01',
'yyyy-mm-dd') and to_date('1982-12-31', 'yyyy-
mm-dd');

多行|聚合|组函数

组函数|多行函数|聚合函数 即多条记录 返回一个结果。
我们需要掌握如下几个组函数

​ avg 、sum、 min、max、count

  • count :统计记录数 count() -->* 或一个列名

  • max min: 最大值 最小值

  • sum：求和

  • avg:平均值

    注意:

    1. 组函数仅在选择列表和Having子句中有效
    2. 出现组函数，select 只能有组函数或分组字段

说明：

  • 组信息 与单条记录不能同时查询

  • 组函数 不能用在 where中，能使用的地方 select having

  • null 不参与运算

    count： 求和

    --1、count统计所有的员工数
--1)、*
--2)、主键
--3)、推荐 
select ename,1 from emp;
select count(1) from emp where 1=1;
--2、null不参与运算
--存在佣金的员工数
--不推荐/不需要
select count(comm) from emp where comm is not
null; --推荐
select count(comm) from emp;
--统计 部门编号30的员工数
select count(1) from emp where deptno=30;
--统计数量过程中 ，可能处理重复
--统计 存在员工的 部门数量
select count(distinct(deptno)) 有人的部门 from
emp;
8.2.2. max min: 最大值 最小值
8.2.3. sum：求和
8.2.4. avg: 平均
--统计10和20部门一共有多少人
select distinct(count(1)) from emp where
deptno in(10,20);

    sum：求和

    -- 查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;

    avg: 平均

    -- 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where
sal<(select avg(sal) from emp);
--查看 高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select
avg(sal) from emp e2 where e1.deptno=e2.deptno
);
上一篇：一对多 多对一 多对多 多条语句只出现一条的问题


下一篇：使用useGeneratedKeys属性与keyProperty实现获取添加后数据库自动生成的主键