Oracle之单表查询和常用函数实例讲解

一、oracle概念

1.

oracle 数据库是数据的物理存储。这就包括（数据文件 ora 或者 dbf、控制文件、联机日志、参数文件）。其实 oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作只有一个库。可以看作是 oracle 就只有一个大数据库。

2. 实例

一个 oracle 实例（oracle instance）有一系列的后台进程（backguound processes)

和内存结构（memory structures)组成。一个数据库可以有 n 个实例（集群模式）。

3. 用户

用户是在实例下建立的。不同实例可以建相同名字的用户。 、

oraclede用户划分:

系统管理员：sys，最高权限，以dba身份登录

一般管理员 system 日常管理数据库试用

普通用户 ：数据库初始化自带的用户，管理员创建的用户（开发使用）

用户在操作管理表空间之前需要授予权限

4. 表空间

表空间是 oracle 对物理数据库上相关数据文件（ora 或者 dbf 文件）的逻辑映射。

一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间 ( 称之为 system 表空间 ) 。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

数据库与表空间是1：n（n可以为1）

5. 数据文件（dbf、ora）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数

据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

二、oracle基本查询（列的注意事项）

--一、基本查询 
--oracle的dual虚拟表 作用是补全查询语法
select 1+1 --错误
select 1+1 count from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  查询系统时间

---查询员工编号 员工姓名 员工工作
select empno,ename,job from emp;
--1. 给列起别名
--1）给列起别名 使用as，as可以省略
--2）使用规则 a:特殊字符 b空格 c:数字 必须使用双引号括起来
--3）特殊字符中的#$_，不在首位也可以不用加双引号
select empno as "员工编号" , ename 员工姓名#, job "工 作" , sal "123" from emp;

--2.去除重复使用 distinct,
--  对查询结果去重，写在列名前
--例：查询员工表中工作

select distinct job from emp;

--3.对列值计算的查询 注意null值
--例：查询员工年薪
select  sal*12+comm from emp;  --为null的会忽略

--4.nvl（v1,v2)  判断v1是否为空，如果不为空返回v1 如果为空返回v2
--例：查询员工年薪
select nvl(sal*12+comm,0) from emp

select concat(sal*12+comm,0) from emp

select nvl(sal*12+comm,0) sal from emp order by sal desc;

--5.concat列值的拼接,oracle支持两个参数拼接 |mysql中支持多个
--需求：通过查询得到 编号 姓名结果数据  员工编号:=7369 员工姓名：=smith
select concat('员工编号==',empno) from emp;
select concat(concat('编号: ==',empno),'===姓名: ==',ename) from emp;
--嵌套使用
select concat(concat('编号：==',empno),'===姓名：==',ename) from emp;

--6 || : oracle特有连接符 可以拼接多个 

select '编号=='||empno from emp;
select '编号=='||empno||'---姓名=='||ename as 员工简介 from emp;

二、条件查询

  where 条件表达式
   1. 比较运算 = > < >= <=  !=  <>
   2.逻辑运算  and or not
   3.其余运算   1）like    模糊查询 （ %  匹配任意个  _  站位一个）
                        2）between ...and...  判断区间   包含边界
                        3）in     | not in   判断范围
                        4）is null    |  is not null 判断空值

--1
--需求：查询员工的工作 不是manager的员工信息
select * from emp where job !='manager'  --方案1
select * from emp where job <>'manager'  --方案2
--使用not实现不是manager的员工信息
--java非表达式  !job.equals('manager')   --方案3
select * from emp where not job ='manager'

--2模糊查询
--查询员工姓名包含m的员工信息
select * from emp where ename like '%m%'
--查询员工姓名第二位为m的员工信息
select * from emp where ename like '_m%'

--3between ... and... 大的在后
--查询工资位于1500---3000的员工信息
select * from emp where sal between 1500 and 3000

--4 in
--查询员工的工作是 manager 和 president
select * from emp where job in('manager','president')
select * from emp where job ='manager' or job ='president'

--5.not|is not 
--查询有奖金的员工信息
select * from emp where comm is not null
select * from emp where not comm is  null

三、查询的排序

关键字：order by 列 asc|desc

1）desc降序排序

2）asc 升序 默认升序

3）nulls last 将null值至于末尾：oracle特有语法

--将员工数据按照工资从小到大排序
select * from emp order by sal
--将员工数据按照奖金从小到大排序
select * from emp order by comm 
--将员工数据按照奖金从大到小排序 --null倒序排序的问题
select * from emp order by comm  desc
--使用关键字 nulls last 将null值至于末尾
select * from emp order by comm  desc nulls last

四、单行函数

针对表中每条记录的列值处理

1. 数值函数

2. 字符函数

3. 日期函数

4. 转换函数

5. 通用函数

1.数值函数

/*
数值函数 对数值做处理
    1四舍五入  round(v1,v2) v1原始数值 v2是保留的小数位数
    2数值截取  trunc(v1,v2) v1原始数值 v2是保留的小数位数
                           不做四舍五入运算
    3求余数    mod(v1,v2)  v1是被模数 v2是模数   10%3 结果为===1
*/
--1四舍五入运算
select round(43.726) from dual;  --默认位数是0
select round(43.726,0) from dual;--44
select round(43.726,1) from dual;  --43.7
select round(43.726,2) from dual;  --43.73
select round(43.726,-1) from dual;  --40
select round(43.726,-2) from dual;--0
select round(53.726,-2) from dual;--100
--2截取操作
select trunc(43.726) from dual;  --43
select trunc(43.726,0) from dual;--43
select trunc(43.726,1) from dual;  --43.7
select trunc(43.726,2) from dual;  --43.72
select trunc(43.726,-1) from dual;  --40 
select trunc(43.726,-2) from dual;--0
select trunc(53.726,-2) from dual;--0
--3求余数--答案为1
select mod(10,3) from dual;

2.字符函数

字符函数

1）获取长度 length()

2）截取字符串 substr(v1,v2,v3)v1原始字符串 v2截取起始位置 v3截取的长度,v**2为负值为倒着截取，如-2表示倒着截取后两位**

3）替换字符串 replace(v1,v2,v3)v1原始字符串v2 要被替换的字符换 v3替换成的字符 –替换匹配的所有字符

4）大小写互换 upper() lower() 验证码

图片验证码 xyz6 xyz6 xyz6 xyz6

数据库匹配转成同一个格式判断

5) 去除空格 trim() 去除两端的空格

--1.abcde--
select length('abcde') from dual;
--2.截取字符串
--从0和1开始截取都是第一位
select substr('abcde',0,2) from dual; -- ab 
select substr('abcde',1,2) from dual; -- ab
select substr('abcde',-1,2) from dual;--e
select substr('abcde',-2,200) from dual;--de
--3.替换字符串  hello
select replace('hello','l','o') from dual; --heooo 
--4.大小写互换 忽略大小写 查询员工姓名为smith的信息
select * from emp where upper(ename)=upper('smith');
select * from emp where ename=upper('smith');
select * from emp where ename=upper('smith');
--5.去除空格__abc_de_
select trim('  abc de ') from dual;--abc de
select replace('  abc de ',' ','') from dual;

3 日期函数

1）.获取当前时间 sysdate

2）.给时间增加月数 add_months(v1 date,v2 month)

3 .获取两个时间间隔月数，日期后的写在前

/*
  日期函数
  1.获取当前时间     sysdate 
  2.给时间增加月数   add_months(v1date,v2month)                     
  3.获取两个时间间隔月数，日期后的写在前 months_between(date1,date2)
*/
--1.获取当前时间
select sysdate from dual;

--2.给当前时间增加一个月
select add_months(sysdate,1) from dual;
--  ticket
--  id     tickeynum   creatime  validate
--  1        111111     2018-4-1   2018-4-28
--  2        222222     2018-3-2   2019-2-3--
--查询一个月内要过期的票
select * from ticket where  sysdate
4.  转换函数 

    1) .  to_number : 数值和字符的互换 

    2）.to_char() :日期转字符串,  分钟日期使用mi 

    3）.to_date() :字符串转日期, 24小时使用hh24
/*
  转换函数
  1.数值和字符的互换
  to_number()
  2.日期转字符串
  to_char(v1,v2) v1要转的日期 v2转换后的格式
  3.字符串转日期  
  to_date(v1,v2)   v1字符串格式 v2 日期
*/
select '123'+1 from dual;  --1默认识别数值格式的字符串自动化转换
select to_number('123')+1 from dual;
--2将系统时间转成特定格式的字符显示
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'dd') from dual;
--获取当前的星期
select to_char(sysdate,'day') from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;--oracle分钟使用mi
--将字符串转换日期
select to_date('2018-04-04','yyyy-mm-dd') from dual;
select to_date('2018-04-04','yyyy-mm-dd hh:mi:ss') from dual;
--jsp页面取值 是日期格式取值 yyyy-mm-dd 会自动补充时分秒显示 00:00:00
select to_date('2018-04-04 10:43:43','yyyy-mm-dd hh:mi:ss') from dual;
select to_date('2018-04-04 18:43:43','yyyy-mm-dd hh:mi:ss') from dual;
--java jsp页面区分24和12 使用大写hh hh
select to_date('2018-04-04 18:43:43','yyyy-mm-dd hh24:mi:ss')
五、通用函数
通用函数 nvl(v1,v2)
concat
nvl2(v1,v2,v3) 判断v1是否为null如果为null返回v3 不为null返回v2
select nvl2(1,2,3) from dual; ---2
select nvl2(null,2,3) from dual;--3
五、多行函数 聚合函数
使用表中多条记录参与运算 结果返回一条
count 统计记录数 sum 求和运算 max 求最大值 min 求最小值 avg 求平均值
1.查询员工的数量（count（1）最优）
select count(1) from emp;
2.分组统计
group by 列,列2 将分组后的列值完全一致 分为一组
分组之后过滤数据使用 having 聚合函数
分组前过滤使用 where
3分组的规则：
group by作为分组 select 语句中只能查询group by后的列和聚合函数
--1.查询员工的数量（count（1）最优）

-- 1）三种模式 如果表中存在唯一索引 效率一致
-- 表中没有索引推荐后两种

select 'abc' from dual;
select count(*) from emp;
select count(empno) from emp;
select count(1) from emp;

--2）使用奖金的列统计数量
select count(comm) from emp;-- 4 
select sum(comm) from emp; --2200
select avg(comm) from emp;--550
/*
  2.分组统计
  group by 列,列2  将分组后的列值完全一致 分为一组
  分组之后过滤数据使用 having 聚合函数
  分组前过滤使用 where
*/
--查询每个部门的平均工资
select * from emp;
select deptno,avg(sal) from emp group by deptno
--查询部门平均工资大于2000的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal) >2000
--查询部门的平均工资  参与统计的员工工资必须大于1500
select deptno,avg(sal) from emp where sal>1500 group by deptno
--统计同一个工作，同一个部门的员工数量
select deptno,job,count(1) from emp group by deptno,job
/*
 3分组的规则：
    group by作为分组 select 语句中只能查询group by后的列和聚合函数
*/
select ename,deptno,avg(sal) from emp group by deptno,ename