数据库笔记

虚表:用于计算的表达式,显示单条记录的值

​ select 1+1 from dual;

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 过滤行记录条件 条件有

1)、比较条件

a)、= 、 >、 <、 >=、 <=、 !=、 <>、 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;
-- all 大于最大值 小于最小值
select * from emp where sal >=all(900,2000);
select * from emp where sal <=all(900,2000);
c)、null :is null、 is not null、 not is null
d)、like :模糊查询 % _ escape('单个字符') 
f)、in 、 exists(难点) 及子查询

2)、且 或 非

b)、and 、or、 not、 

3)、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;

4)、集合操作

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

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') --任意指定转义字符,需要使用escape() 中说明;

6)、in 与 exists

in (匹配后面结果集中的任意一个数据,无法做区间判断) 相当于使用or的多个等值，定值集合 ,如果存在 子查询，段数为1，如果记录多，效率不高，用于 一些 少量定值判断上：

--子查询(查询中再有查询) in 只能存在一个字段
select * from emp where sal in (select sal from emp e where
deptno=10)
--查询薪资为 1500 2000 2500 5000的员工信息
select * from emp where sal=1500 or sal = 2000 or sal=2500 or sal=5000;
select * from emp where sal in (1500,2000,2500,5000);

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

exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true)

--销售部和财务部所有员工信息 
select *
  from emp 
 where exists (select deptno, dname
          from dept 
         where dname in ('SALES', 'ACCOUNTING')
           and emp.deptno = dept.deptno);

–事务
commit;
rollback;

7)、获取所有行的记录

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

3、排序

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

–排序 : order by desc 降序 asc升序

–select * from 表名 where 行过滤条件 order by 排序字段; --默认升序

 多字段: 在前面字段相等时，使用后面的字段排序

空排序: 降序为 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;

一、函数—单行函数

函数分为系统内置函数 自定义函数, 根据函数的返回结果，我们将函数分为单行函数和多行函数

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

1、日期函数

日期函数: 注意区分 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,‘星期一’) 下周星期一

1)、当前时间

select current_date from dual where 1=1;
select sysdate from dual where 1=1;

2)、修改日期（天数±）

2、转换函数（重点）

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;

3、其他函数 (保证类型兼容)

--1)、nvl nvl(string1,string2)  如果string1为null,则结果为string2的值
select ename, nvl(null,0) from emp;
select ename, nvl(to_char(comm),'hello') from emp;
--2)、decode decode(condition,case1,express1,case2 , express2,….casen , expressn, expressionm)
select ename,decode(deptno, 10,'十',20,'二十') from emp;
--3)、case when then else end

二、组函数

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

1)、count :统计记录数 count() -->* 或一个列名
2)、max min: 最大值 最小值
3)、sum：求和
4)、avg:平均值

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

组信息 与单条记录不能同时查询
组函数 不能用在 where中，能使用的地方 select having
null 不参与运算

1、count 统计所有的员工数

select count(comm) from emp;

2、max min: 最大值 最小值

--查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
select max(sal) maxSal , min(sal) minSal , count(1) from emp;
--查询 最高薪水的员工名称 及薪水
--组信息 与单条记录不能同时查询
select max(sal), ename, sal from emp;  错误
select ename, sal from emp where sal=(select max(sal) from emp );

3、sum：求和

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

4、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 );

三、分组

 解析步骤 1)、from 2)、where 3)、group 4)、having 5)、select 6)、order by

group by :分组
1)、select 出现分组函数,就不能使用 非分组信息，可以使用 group by 字段
2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必
须出现在group by 中
过滤组 having :
where :过滤行记录，不能使用组函数， having:过滤组 可以使用组函数