oracle 笔记
database
SQL命令：
1.DDL:数据库定义语句(create/drop/alter)
2.DML:数据库管理语句(insert/delete/update)
3.DQL:数据库查询语句(select)
4.DCL:数据库控制语句
DDL(数据库定义语言:create/drop/alter)操作
一.查看当前系统时间
select sysdate from dual;
二.创建表
create table employee_jiange(
id number(4),
name varchar2(20),
gender char(1),
birth date,
salary number(6,2),
job varchar2(30),
deptno number(2)
);
三.显示表的结构
desc 表名;
desc openlab.myemp_jiange
四.删除表
drop table employee_jiange;
五.字段默认值
1.在数据库中，无论字段是什么类型，默认值都是null。
2.在创建表时,可以通过default为指定字段设置指定的默认值。
3.当向该表插入数据时，若某个字段没有给值则会将默认值插入该字段。
六.字符串
在数据库中，字符串的字面量使用 单引号 括起来，这与java不同，注意区分。
虽然SQL语句不区分大小写，但是字符串内容区分大小写。
create table emplayee_jiange(
id number(4),
name varchar2(20),
gender char(1) default 'M',
birth date,
salary number(6,2),
job varchar2(30),
deptno number(2)
);
drop table emplayee_jiange;
create table employee_jiange(
id number(4),
name varchar2(20) not null,
gender char(1) default 'M',
birth date,
salary number(6,2),
job varchar2(30),
deptno number(2)
);
七.not null 约束(非空约束)
当某个字段使用not null 约束，那么该字段的值不允许为null.
八.修改表
1、修改表名
rename old_name to new_name;
将employee_jiange 修改为myemp_jiange
rename emplayee_jiange to myemp_jiange;
2、修改表结构
2.1 添加新的字段
向myemp表中添加字段hriedate
alter table myemp_jiange
add(
hiredate date default sysdate
);
2.2 删除现有字段
alter table myemp_jiange
drop(hiredate);
2.3 修改现有字段
可以修改字段的类型，长度，添加默认值或者设置为not null
修改字段应尽量在表中没有数据时进行
alter table myemp_jiange
modify(
job varchar2(40) default 'CLERK'
);
DML(数据库管理语句:insert/delete/update)操作
一.insert语句(插入数据)
向表中插入数据
insert into myemp_jiange
(id, name, job, salary)
values
(1001, 'rose', 'PROGRAMMER',5500);
insert语句可以不指定字段，但若不指定则表示全字段插入,
那么所有字段必须给值且顺序必须与结构一致。
插入日期可以使用字符串形式，但是必须是"DD-Mon-RR"的形式，由于有语言差异，
所以推荐使用数据库内置函数to_date
insert into myemp_jiange
(id, name, job, birth)
values
(1002,'jack','CLERK',to_date('1992-08-28','YYYY-MM-DD'))
select * from myemp_jiange;
二.update语句(修改数据)
update 在修改表中数据时，通常会添加where条件，
这样只会将满足where条件要求的记录进行修改。否则是全表数据都修改
update myemp_jiange
set gender='F',salary=6000 where name='rose';
三.delete语句(删除数据)
删除表中数据时，通常会添加where来删除满足条件的记录，若不添加where则是清空表操作。
delete from myemp_jiange
where name='jack';
DQL(数据库查询语句:select)操作
一.select语句(查询数据)
select ename,job,sal,deptno from emp_jiange
where deptno>=20 and deptno<=40;
select * from emp_jiange
where sal>2000;
二.字符串操作
1 concat函数，用于连接字符串
select concat (ename,job) from emp_jiange;
'||'也可以实现连接字符串操作，更常用
select ename||':'||job from emp_jiange;
2 length函数，返回字符串长度
select ename,length(ename) from emp_jiange;
伪表：
dual,不是一张实际的表，当查询的内容与现有任何表中的字段无关时，可以查询伪表。
伪表只会查询出一条记录。
3 upper,lower,initcap函数，将字符串转换为全大写，全小写与首字母大写
select
upper('helloworld'),
lower('HELLOWorLD'),
initcap('HELLO WorLD')
from dual;
4 trim函数，去除字符串两边的重复字符
select trim('e' from 'eeeelisteeeeee') from dual;
select ltrim('esgesgsgegessegseglisteeeee','esg') from dual;
rtrim 跟 ltrim 一样的用法。
5 lpad,rpad补位函数
select lpad(sal,6,' ') from emp_jiange;
6 substr函数，截取字符串
substr("",n,m)
数据库中下标都是从1开始的。从当前字符串char的第n个字符开始，截取m个字符，
其中m可以不指定，若不指定则是截取到字符串末尾，若m超过了可截取的字符串数量也是截取到末尾。
m若为负数，则是从倒数位置开始截取。
select substr('thinking in java',10,2) from dual; //in
select substr('thinking in java',10) from dual;
select substr('thinking in java',-7,2) from dual; //in
7 instr函数，检索字符串位置
select instr('thinking in java','in') from dual;
select instr('thinking in java','in',4) from dual;
select instr('thinking in java','in',4,1) from dual;
select instr('thinking in java','in',4,2) from dual;
select instr('thinking in java','in',4,3) from dual;
三.数值操作
1 round函数，四舍五入
round(m,n)
n表示保留到小数点后的位数
select
round(45.678,2),
round(45.678,0),
round(45.678,-1)
from dual;
2 trunc函数，截取数字
trunc(m,n)
n表示保留到小数点后的位数
select
trunc(45.678,2),
trunc(45.678,0),
trunc(45.678,-1),
trunc(45.678,-2)
from dual;
3 mod（m,n）函数，m除以n后的余数
select ename,sal,mod(sal,1000) from emp_jiange;
4 ceil 和 floor 函数，向上取整与向下取整
select ceil(45.678) from dual;
select floor(45.678) from dual;
四.日期操作
1 日期关键字
sysdate:表示当前系统时间的一个date类型值
systimestamp:表示当前系统时间的一个时间戳的值
2 转换函数
1)to_date函数
可以将字符串按照指定的日期格式解析为一个date数值。
在日期格式字符串中除了字母数字和符号之外的的其他字符，都需要用单引号括起来。
select
to_date('2008-08-08 20:08:08','YYYY-MM-DD HH24:MI:SS')
from dual;
2)to_char函数
可以按照指定的日期格式将date转换为字符串。
select
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
from dual;
3)date可以进行计算
对date之间加减一个数字，等同于加减天数。两个date之间相减，差为相差的天数。
date也可以比较大小，越晚的越大。
查看明天的日期？
select sysdate+1 from dual;
select ename,sysdate-hiredate from emp_jiange;
select ename,trunc(sysdate-hiredate,0) from emp_jiange;
select
trunc(sysdate-to_date('1994-12-11','YYYY-MM-DD'),2)
from dual;
4)last_day(date)
查看给定日期所在月的月底。
查看当月月底？
select last_day(sysdate) from dual;
5)add_months(date,i)
对指定的日期加减指定的月数。i为正数是加，为负数是减。
查看每个员工转正日期：
select ename,hiredate,add_months(hiredate,3) from emp_jiange;
6)months_between(date1,date2)
计算两个日期之间相差的月数，计算是根据date1-date2得到的
查看每个员工至今入职多少个月了？
select ename,trunc(months_between(sysdate,hiredate)) from emp_jiange;
7)next_day(date,i)
查看指定日期之后一周内的周几对应的日期。
select next_day(sysdate,1) from dual;
8)least,greatest函数
求最小值与最大值
select least(sysdate,to_date('2008-10-10','YYYY-MM-DD')) from dual;
select greatest(sysdate,to_date('2008-10-10','YYYY-MM-DD')) from dual;
9)extract(xxx from date)
获取指定时间的时间分量值
select extract(year from sysdate) from dual;
查看1981年入职的员工？
select ename,hiredate from emp_jiange
where extract(year from hiredate)=1981;
五.null操作(空值操作)
1 插入null值
create table student_jiange(
id number(4),
name char(20),
gender char(1)
);
dele
insert into student_jiange values(1000,'李莫愁','F');
insert into student_jiange values(1001,'林平之',null);
insert into student_jiange(id,name) values(1002,'张无忌');
select * from student_jiange;
判断某个字段的值是否为空，要使用is null 或 is not null判断不能使用“=”。
delete from student_jiange where gender is null;
drop table student_jiange;
2 null的计算
null与字符串拼接，结果等于什么都没有做。
null与数字进行运算，结果还是null.
select ename,sal,comm,sal+comm from emp_jiange;
3 nvl(a1,a2)
若a1为null,函数返回a2的值，否则返回a1；
该函数的作用是将null值替换为非null值。
select ename,sal,comm,sal+nvl(comm,0) from emp_jiange;
4 nvl2(a1,a2,a3)
当a1不为null,则函数返回a2；
当a1为null，则函数返回a3。
查看每个人的奖金情况，即：该员工有奖金则显示“有奖金”;没有则显示“没有奖金”。
select ename,comm,nvl2(comm,'有奖金','没有奖金') from emp_jiange;
commit;
六.基本查询
1 别名
当查询的字段是函数或表达式时，查询结果集对应的该列的字段名就是这个函数或表达式，
这样的可读性差，所以，通常我们会为函数或表达式的字段添加别名。别名不可以是关键字。
别名可以用 双引号 括起来，这样可以区分大小写和添加空格。
select ename,sal*12 sal from emp_jiange;
2 where子句
select * from emp_jiange
where deptno=10;
select ename,sal,job from emp_jiange
where job='SALESMAN';
3 查询条件
select * from emp_jiange
where sal<2000;
select * from emp_jiange
where deptno<>10;
select * from emp_jiange
where hiredate>to_date('2002-01-01','YYYY-MM-DD');
4 使用and,or关键字
select * from emp_jiange
where sal>1000 and job='CLERK';
and 的优先级高于 or
select ename,sal,job from emp_jiange
where sal>1000 and (job='SALESMAN' or job='CLERK');
5 使用like条件（模糊查询）
支持两个通配符：
%：0-多个字符
_：单一的一个字符
查看名字第二个字母是A的员工：
select ename,sal,deptno from emp_jiange
where ename like '_A%';
6 in 和 not in:
在列表中或不在列表中
select ename,job from emp_jiange
where job in('MANAGER','CLERK');
7 between...and...
判断在一个范围内(and前面数小，后面数大)
select ename,sal from emp_jiange
where sal between 1500 and 3000;
8 any,all
用于配合比较运算符：>,>=,<=,<使用
>any(list): 大于列表其中之一即可
>all(list): 大于列表所有
列表通常不会给确定值，否则没意义，一般是在子查询中使用
select empno ename,job,sal,deptno from emp_jiange
where sal > any(1500,4000,4500);
select empno ename,job,sal,deptno from emp_jiange
where sal > all(1500,4000,4500);
9 查询条件中使用表达式和函数
select ename,sal,job from emp_jiange
where upper(ename)=upper('scott');
10 distinct 过滤重复
用于去除后面指定字段值中重复数据后，得到的查询结果
select distinct job from emp_jiange;
对多字段去重
用于去除指定字段值的组合中重复记录后，得到的查询结果
select distinct job,deptno from emp_jiange;
七.排序(order by字句)
1 单字段排序
order by 可以根据后面指定的字段，按照升序或降序排序。
其中升序为asc，可以不写，默认就是升序；
desc为降序。
select ename,sal from emp_jiange
order by sal desc;
2 多字段排序
order by 可以根据多字段排序，但是排序有优先级顺序，先按照第一字段排序，
当第一字段值相同的情况下，再按照第二个字段值排序，依此类推。
select ename,sal,deptno from emp_jiange
order by sal desc,deptno desc;
若排序的字段中含有null值，那么null被当做最大值。
select ename,comm from emp_jiange
order by comm;
八.聚合函数（分组函数，多行函数，集合函数）
作用是将结果集按照指定字段进行统计然后得到一条记录。
集合函数都是忽略null值的。
1 max与min
select max(sal) max_SAL,min(sal) min_SAL from emp_jiange;
2 avg与sum
select avg(sal) avg_SAL,sum(sal) sum_SAL from emp_jiange;
3 count
统计指定字段值不为null的记录总数。
select count(ename) from emp_jiange;
select count(nvl(comm,0))from emp_jiange;
查看一张表中的记录,常用count（*）
select count(*) from emp_jiange;
九.分组
1 group by 子句
group by 可以将结果集按照指定的字段值相同的记录进行分组，
配合 集合函数 可以实现对每组记录进行统计。
当select中含有集合函数时，
那么不是集合函数的其他单独字段都需要出现在 group by 字句中，
但是反过来则不是必须的。
select avg(sal),deptno from emp_jiange
group by deptno;
select sum(SAL),job from emp_jiange
group by job;
group by 按照多字段分组原则：
这些字段值的组合相同的看作一组
查看每个部门每种职位的平均工资？
select avg(sal),job,deptno from emp_jiange
group by job,deptno;
where 中不允许使用聚合函数作为过滤条件，
原因在于时机不对。
where 是在查询表中每条数据时进行过滤的，所以，where决定着表中那条数据可以被查询出来。
而分组统计是 在表中数据查询出来后 基于结果 进行的。
所以根据分组统计的结果作为过滤条件是不能在where中使用的。
2 having 子句
having 子句不能独立存在，必须跟在 group by 子句之后，
having 可以使用聚合函数作为过滤条件，
它是用来根据统计结果决定保留那些分组的
查看部门人数高于3人的,这些部门的平均工资？
select avg(sal),deptno from emp_jiange
group by deptno
having count(*)>3;
查看每种职位的人数，前提是该职位最低工资高于1000；
select count(*),job from emp_jiange
group by job
having min(sal)>1000;
十.SQL(关联查询)
查看每个员工的名字,以及所在部门的名字
emp_jiange.ename:员工的名字 dept_jiange.dname:部门的名字 通过部门号关联
select emp_jiange.ename,dept_jiange.dname
from emp_jiange,dept_jiange
where emp_jiange.deptno=dept_jiange.deptno;
select e.ename,d.dname
from emp_jiange e,dept_jiange d
where e.deptno=d.deptno;
在关联查询中，过滤条件要与关联条件同成立。
查看SALES部门的员工信息
select e.ename,e.sal,d.dname
from emp_jiange e,dept_jiange d
where e.deptno=d.deptno
and d.dname='SALES';
查看每个地区工作的人数？
select count(*),d.loc
from emp_jiange e,dept_jiange d
where e.deptno = d.deptno
group by d.loc;
1.内连接( join on )
也可以实现关联查询
select e.ename,e.sal,d.dname
from emp_mao e join dept_mao d
on e.deptno=d.deptno
where d.dname='SALES';
无论是关联查询，还是内连接，都忽略不满足连接条件的记录
2 外连接
外连接可以在关联查询中将不满足条件的记录也查出来
外连接分为：
左外连接，右外连接和全外连接
以左外连接为例：
左外连接以join左侧的表作为驱动表（主要显示数据的表），
该表中的所有记录都会被查询出来，那么当某条记录不满足连接条件时，
那么来自join右侧表的字段全部以null作为值。
emp_mao表做驱动表(左外连接)
select e.ename,d.dname
from emp_mao e left outer join dept_mao d
on e.deptno=d.deptno;
dept_mao表做驱动表（右外连接）
select e.ename,d.dname
from emp_mao e right outer join dept_mao d
on e.deptno=d.deptno;
全外连接
select e.ename,d.dname
from emp_mao e full outer join dept_mao d
on e.deptno=d.deptno;
(右外连接)
select e.ename,d.dname
from emp_mao e ,dept_mao d
where e.deptno(+)=d.deptno;
(左外连接)
select e.ename,d.dname
from emp_mao e ,dept_mao d
where e.deptno=d.deptno(+);
3 自连接(把一张表看做两张表来)
用于解决相同类型数据(字段的类型相同)，但是有存在上下级关系(一个表中存在员工的信息和员工领导的信息)的树状结构的存储
自连接，当前表的记录与当前表的其他记录有对应关系。
查看员工的领导
select e.ename,m.ename
from emp_jiange e,emp_jiange m
where e.mgr = m.empno(+);//e.mgr下级的领导的员工号,m.empno:领导的员工号
查看每个员工的领导在哪里工作，列出3个字段，
分别是：员工名，上司名，上司所在地。
select e.ename,m.ename,d.loc
from emp_jiange e,emp_jiange m,dept_jiange d
where e.mgr=m.empno and m.deptno=d.deptno;
或：
select e.ename,m.ename,d.loc
from emp_mao e join emp_mao m
on e.mgr=m.empno
join dept_mao d
on m.deptno=d.deptno;
十一.SQL(高级查询)
1 子查询
1)概念
子查询是嵌套在其他查询语句之中的作用是为了外层SQL语句提供数据。
子查询常用语句DQL，但是也可以用于DML和DDL之中。
谁的工资比CLARK的工资高？
select ename,sal
from emp_jiange
where sal>(select sal
from emp_jiange
where ename='CLARK');
select ename,job
from emp_jiange
where job=(select job
from emp_jiange
where ename='SMITN');
查看谁的工资高于公司平均工资？
select ename,sal
from emp_jiange
where sal>(select avg(sal)
from emp_jiange);
查看部门的平均工资，前提是该部门的平均工资要高于30号部门的平均工资？
select deptno,avg(sal)
from emp_jiange
group by deptno
having avg(sal)>(select avg(sal)
from emp_jiange
where deptno=30);
查看和CLERK职位同部门的员工都有谁？
select ename,deptno
from emp_jiange
where deptno in(select deptno
from emp_jiange
where job='CLERK')
and job<>'CLERK';
查看比20号部门所有员工工资都高的员工？
select ename,sal
from emp_jiange
where sal>(select max(sal)
from emp_jiange
group by deptno
having deptno=20);
2)查询结果分类
子查询根据查询结果不同，有如下分类单行单列子查询，即：查询结果为一个值,
多行单列子查询
多行多列子查询
其中单行单列与多行单列常用语where和having中作为过滤条件，
而多行多列（包括单行多列）则常常当做一张表使用。
exists用在过滤条件中，其后需要跟一个子查询，只要该子查询可以查询出一条记录，(exists只是判断子查询里有没有记录)
那么exists表达式就返回true.
查看有员工的部门信息(意思是:有员工部门信息就查询出来,没有就不出出来)
select deptno,dname
from dept_jiange d
where exists(select * from emp_jiange e
where e.deptno= d.deptno);
查看比自己所在部门平均工资高的员工
create view v_avg_sal
as
select avg(sal) avg_sal,deptno
from emp_mao
group by deptno;//查询到部门号和部门的平均工资
select e.ename,e.sal,e.deptno
from emp_mao e,v_avg_sal t
where e.deptno=t.deptno //通过关联条件(部门号)把两个表连起来(同部门)
and e.sal>t.avg_sal;//比自己部门平均工资高的员工
select e.ename,e.sal,e.deptno
from emp_mao e,(select deptno,avg(sal) avg_sal
from emp_mao
group by deptno)t
where e.deptno=t.deptno
and e.sal<t.avg_sal;
查看每个部门最低工资是谁？
select e.ename,e.sal,e.deptno
from emp_jiange e, (select min(sal) min_sal,deptno
from emp_jiange
group by deptno
having count(*)>=2) t
where e.deptno=t.deptno
and e.sal=t.min_sal;
3)select子句作为字段值
子查询在 select 子句中可以将该查询的结果作为一个字段的值，显示在外层查询语句的结果集中。
select e.ename,e.sal,
(select d.dname from dept_jiange d
where d.deptno=e.deptno) dname
from emp_jiange e;
2 分页查询
当一个查询的结果集数据量过大时，通常采取分页查询。
分页查询是将数据分批查询出来，这样的好处可以提高服务端到客户端的相应速度，
减少网络间的传输量，降低资源消耗等。
分页由于在标准SQL中没有定义，所以不同的数据库分页语句不一样（方言）。
rownum:是一个伪列
rownum 不存在于任何一张表中，但是每张表都可以查询该字段，
该字段的值为结果集中每条记录的行号。rownum 字段的值是在查询的过程中动态生成的，
只要可以从表中查询出一条记录，那么rownum 就从1开始为结果集每条记录指定行号。
select rownum,ename,sal,deptno
from emp_jiange;
在使用rownum对结果集编行号的过程中是不能通过rownum做大于1以上数字的判断的，
否则将查询不出任何结果。
查询5到10条记录
select *
from (select rownum rn,ename,sal,deptno from emp_jiange)
where rn between 5 and 10;
若在分页中有排序需求时，那么应当先进行排序，
因为排序的优先级是最低的。
select *
from (select rownum rn,t.*
from (select ename,sal,deptno
from emp_jiange
order by sal desc) t
where rownum<=10)
where rn>=6 ;
pageSize:每页显示的条目数
page:第几页
根据上述两个参数，计算结果集的范围：
start:(page-1)*pageSize+1
end:pageSize*page
十二.decode函数
可以实现分支效果
1)decode函数的语法:
decode(expr,
search1, result1,
search2, result2,
... , ... ,
default
)
它用于比较参数expr的值，
如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，
如果任何一个search条件都没有匹配到，则返回最后default的值。
default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回null。
select ename,job,sal,
decode(job,
'MANAGER',sal*1.2, //如果job=MANAGER,那么这个字段(bonus)的值为sal*1.2
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05,
sal
) bonus
from emp_jiange;
等价于 case语句:
select ename,job,sal,
case job when 'MANAGER' then sal*1.2
when 'ANALYST' then sal*1.1
when 'SALESMAN' then sal*1.05
else sal end
bonus
from emp_jiange;
2)decode函数可以按字段内容分组:
如下:分组为 VIP和OTHER
select count(*),
decode(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER'
) bonus
from emp_jiange
group by decode(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER'
);
3)decode函数也可以按字段内容排序:
select deptno,dname,loc
from dept_jiange
order by decode(dname,
'OPERATIONS',1,
'ACCOUNTING',2,
'SALES',3
);
十三.排序函数
排序函数可以根据结果集按照指定的字段分组，
然后再按照指定的字段在各组内排序，然后生成组内编号。
1 row_number:生成组内连续并唯一的数字
语法:
row_number() over(
partition by ...
order by ...
)
查看各部门的工资排名
select ename,sal,deptno,
row_number() over( //生成部门内连续并唯一的数字
partition by deptno //各部门
order by sal desc //薪资排序
) rank
from emp_jiange;
2 rank: 生成组内不连续也不唯一的数字
select ename,sal,deptno,
rank() over(
partition by deptno
order by sal desc
) rank
from emp_jiange;
3 dense_rank: 生成组内连续但不唯一的数字
select ename,sal,deptno,
dense_rank() over(
partition by deptno
order by sal desc
) rank
from emp_jiange;
十四.集合操作
为了合并多个select语句的结果,可以使用集合操作符,实现集合的并/交/差.
集合操作符包括:union / union all / intersect / minus;
1 准备
1) 创建表
create table sales_ge(
year_id number not null,
month_id number not null,
day_id number not null,
sales_value number(10,2)not null
);
2)向表插入1000条数据:
dbms_random.value(2010,2012)as year_id:数据库生成2010到2012的随机数,作为年的id
insert into sales_ge
select trunc(dbms_random.value(2010,2012))as year_id,
trunc(dbms_random.value(1,13))as month_id,
trunc(dbms_random.value(1,32))as day_id,
round(dbms_random.value(1,100))as sales_value
from dual
connect by level<=1000;
2 并集(union / union all)
1) union操作符
会自动去掉合并后的重复记录.
select ename,job,sal from emp_jiange
where job='MANAGER'
union
select ename,job,sal from emp_jiange
where sal>2500;
2) union all操作符
返回结果合并后的所有的行,包括重复的行.
3 交集(intersect)
获得两个结果集的交集.
操作后的结果集会以第一列的数据作升序排列.
select ename,job,sal from emp_jiange
where job='MANAGER'
intersect
select ename,job,sal from emp_jiange
where sal>2500;
4 差集(minus):交集以外的
获取两个结果集的差集
只有在第一个结果集中存在的,第二个结果集中不存在的数据,才能够被显示出来.
select ename,job,sal from emp_jiange
where job='MANAGER'
minus
select ename,job,sal from emp_jiange
where sal>2500;
十五.高级分组函数
rollup、cube 和 grouping sets 运算符是 group by 子句的扩展，
可以生成与使用 union all 来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。
语法形式如下：
group by rollup(a, b, c)
group by cube(a, b, c)
group by grouping sets ( (a), (b))
1 rollup函数
1)语法
假设有表test，有a、b、c、d四个列。
select a,b,c,sum(d) from test group by rollup(a,b,c);
等价于：
select a,b,c,sum(d) from test group by a,b,c //每天
union all
select a,b,null,sum(d) from test group by a,b //每月
union all
select a,null,null,sum(d) from test group by a //每年
union all
select null,null,null,sum(d) from test; //总和
2)例子
查看每天，每月，每年，以及所有营业额
select year_id,month_id,day_id,sum(sales_value)
from sales_ge
group by
rollup(year_id,month_id,day_id)//每天，每月，每年，以及所有营业额
order by
year_id,month_id,day_id;
2 cube函数
1)语法
group by cube(a, b, c)
对cube的每个参数，都可以理解为取值为 参与分组 和 不参与分组 两个值的一个维度，
所有维度取值组合的集合就是分组后的集合。对于n个参数的cube，有2^n次分组。
如果group by cube(a,b,c)，首先对(a,b,c)进行group by，
然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，
最后对全表进行group by操作，所以一共是2^3=8次分组。
select a,b,c,sum(d) from test group by cube(a,b,c);
等价于：
select a,b,c,sum(d) from test group by a,b,c
union all
select a,b,null,sum(d) from test group by a,b
union all
select a,null,c,sum(d) from test group by a,c
union all
select a,null,null,sum(d) from test group by a
union all
select null,b,c,sum(d) from test group by b,c
union all
select null,b,null,sum(d) from test group by b
union all
select null,null,c,sum(d) from test group by c
union all
select null,null,null,sum(d) from test ;
2)例子
select year_id,month_id,day_id,sum(sales_value)
from sales_ge
group by
cube(year_id,month_id,day_id)
order by
year_id,month_id,day_id;
3 grouping sets函数
1)语法
grouping sets 运算符可以生成与使用单个 group by、rollup 或 cube 运算符所生成的结果集相同的结果集，但是使用更灵活。
如果不需要获得由完备的 rollup 或 cube 运算符生成的全部分组，
则可以使用 grouping sets 仅指定所需的分组。
grouping sets 列表可以包含重复的分组
select year_id, month_id, sum(sales_value)
FROM sales_tab
group by cube (year_id,month_id)
order by 1, 2;
select year_id, month_id, sum(sales_value)
FROM sales_tab
group by grouping sets ((year_id), (month_id))
order by 1, 2;
其中分组方式示例如下：
使用group by grouping sets(a,b,c)，则对(a),(b),(c)进行group by
使用group by grouping sets((a,b),c), 则对(a,b),(c)进行group by
使用group by grouping sets(a,a) , 则对(a)进行2次group by, grouping sets的参数允许重复
十六.视图
1 创建简单的视图
create view v_emp_10_ge
as
select empno,ename,sal,deptno
from emp_jiange
where deptno=10;
desc v_emp_10_ge;
select * from v_emp_10_ge;
2 概念
视图是数据库对象之一，数据库中所有的数据库对象命名不能重复，
所以通常视图的名字以v_开头。视图在SQL中体现的角色与表一致，
但视图并非真实存在的表，它只是对应一条select语句的查询结果集。
视图分为 简单视图 和 复杂视图
简单视图：该视图对应的select语句不含有对数据做加工的操作，
例如：函数表达式，分组等，且数据只来自单一的一张表。
复杂视图：除简单视图的情况外都是复杂视图。
简单视图可以进行DML操作，但是实际上对视图的DML操作，
就是对该视图数据来源的基础表的DML操作。而复杂视图不能进行DML操作。
视图对应的子查询的字段若含有函数或表达式,必须制定别名，
那么别名就是当前视图对应该字段的名字。
create or replace view v_emp_10_ge
as
select empno id,ename name,sal salary,deptno
from emp_jiange
where deptno=10;
select * from v_emp_10_ge;
3 对视图进行DML操作
insert into v_emp_10_ge
(id,name,salary,deptno)
values
(1001,'JACK',5000,10);
select * from v_emp_10_ge;
select * from emp_jiange;
4 修改视图数据
update v_emp_10_ge
set salary=8000
where id=1001;
select * from v_emp_10_ge;
对视图的DML操作可能 对基表数据进行污染。
对视图进行的DML操作的数据影响了基表对应数据，但是视图对这些数据不可见。
insert into v_emp_10_ge
(id,name,salary,deptno)
values
(1002,'ROSE',3000,20);
select * from v_emp_10_ge;
select * from emp_jiange;
update v_emp_10_ge
set deptno=20;
5 删除操作
不会对基表污染的DML是删除操作。
delete from v_emp_10_ge
where deptno=20;
6 检查选项(check option)
对视图添加 检查选项 后，可以避免视图对基表污染，
因为检查选项的添加要求对视图进行 DML操作后的数据视图必须对其可见 才允许操作。(这里表示:只有插入部门号为10的,才能允许操作)
create or replace view v_emp_10_ge
as
select empno id,ename name,sal salary,deptno
from emp_jiange
where deptno=10
with check option;
7 只读选项(read only)
视图添加了只读选项后
create or replace view v_emp_10_ge
as
select empno id,ename name,sal salary,deptno
from emp_jiange
where deptno=10
with read only;
select object_name from user_objects
where object_type='view';
select text from user_views;
select view_name from user_views;
select table_name from user_tables;
8 创建复杂视图
视图内容：每个部门的部门编号，名字，以及该部门员工的工资最大值，
最小值，平均值以及工资总和。
create view v_dept_sal_ge
as
select d.deptno,d.dname,
max(e.sal) max_sal,
min(e.sal) min_sal,
avg(e.sal) avg_sal,
sum(e.sal) sum_sal
from emp_jiange e,dept_jiange d
where e.deptno=d.deptno
group by d.deptno,d.dname;
查看每个部门的最高工资
select e.ename,e.sal,e.deptno
from emp_jiange e, v_dept_sal_ge v
where e.deptno=v.deptno
and e.sal=v.max_sal;
十七.序列
1 概念
序列也是数据库对象之一，
作用是可以按照指定的方法生成一系列数字，
经常用于为表主键提供数据。
通常用来自动产生表的主键值，是一种高效率获得唯一键值的途径。
序列是独立的数据库对象，和表是独立的对象，序列并不依附于表。
通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值。
create sequence seq_emp_id_ge
start with 1
increment by 1; //每次增加1
2 伪列(nextval / currval)
序列支持两个伪列，以获取该序列的数字：
nextval:使序列生成下一个数字，若是刚创建的序列，
这是将 start with 指定的数字返回，
以后则是根据步长计算下一个数字后返回。
currval：获取当前序列最后生成的数字。
select seq_emp_id_ge.currval
from dual;
currval要在创建的序列至少调用一次nextval后才可以使用，
nextval会导致序列步进，
并且序列是不可以回退的。
insert into emp_jiange
(empno,ename,sal,deptno)
values
(seq_emp_id_ge.nextval,'ROSE',5000,20);
select * from emp_jiange;
3 删除序列
drop sequence seq_emp_id_ge;
4 主键生成机制：UUID
UUID:是一个32位不重复字符串。
select SYS_GUID()
from dual;
十八.索引:能快速找到
索引是一种允许直接访问数据表中某一数据行的树型结构，
为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（tablespace）中。
索引记录中存有索引关键字和指向表中数据的指针（地址）。
对索引进行的 I/O操作 比 对表进行操作 要少很多。
索引一旦被建立就将被Oracle系统自动维护，
查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。
rowid: 伪列，唯一标识一条数据记录，可理解为行地址。
1 创建索引
create index idx_emp_ename_ge on emp_jiange(ename);
create index idx_emp_job_sal_ge on emp_jiange(job,sal);
select empno,ename,sal,job
from emp_jiange
order by job,sal;
2 基于函数的索引:upper是函数
create index emp_ename_upper_idx_ge on emp_jiange(upper(ename));
3 重构索引
alter index idx_emp_ename_ge rebuild;
4 删除索引
drop index idx_emp_ename_ge;
5 合理使用索引提升查询效率
为提升查询效率，创建和使用索引的原则：
1)为经常出现在where子句中的列创建索引
2)为经常出现在order by、distinct后面的字段建立索引。
如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
3)为经常作为表的连接条件的列上创建索引
4)不要在经常做DML操作的表上建立索引
5)不要在小表上建立索引
6)限制表上的索引数目，索引并不是越多越好
7)删除很少被使用的、不合理的索引
十九.约束
1 作用
约束（constraint）的全称是约束条件，也称作完整性约束条件。
约束是在数据表上强制执行的一些数据校验规则，
当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行。
约束条件可以保证表中数据的完整性，保证数据间的商业逻辑。
2 约束条件包括
非空约束(Not Null)，简称NN
唯一性约束(unique)，简称UK
主键约束(Primary Key)，简称PK
外键约束(Foreign Key)，简称FK
检查约束(Check)，简称CK
3 非空约束（not null）(NN)
非空约束用于确保字段值不为空。默认情况下，任何列都允许有空值，
但业务逻辑可能会要求某些列不能取空值。
当执行insert操作时，必须提供这个列的数据
当执行update操作时，不能给这个列的值设置为null
create table employees_ge(
eid number(6),
name varchar2(30) not null,
salary number(7,2),
hiredate date constraint employees_hiredate_nn_ge not null
);
desc employees_ge;
drop table emplayees_ge;
1)添加非空约束:
alter table employees_ge
modify(eid number(6) not null);
2)取消非空约束：
alter table employees_ge
modify(eid number(6) null);
4 唯一性约束
唯一性(unique)约束条件用于保证字段或者字段的组合不出现重复值。
当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是unll值。
唯一性约束条件可以在建表同时建立，也可以在建表以后再建立。
1)建立唯一性约束(建表同时建立)
create table employees1_ge(
eid number(6) unique,
name varchar2(30),
email varchar2(50),
salary number(7,2),
hiredate date,
constraint employees_email_uk_ge unique(email)//强制再给email添加一个唯一性约束
);
desc employees1;
唯一性约束要求字段值在整张表中的记录不允许重复值，但是null除外。
insert into employees1_ge
(eid,name,email)
values
(1,'JACK','QQ.COM');
2)建立唯一性约束(建表之后建立)
alter table employees1_ge
add constraint employees1_name_uk_ge unique(name);
select * from employees1_ge;
5 主键约束
1)意义
主键(Primary Key)约束条件从功能上看相当于非空（not null）且唯一（unique）的组合。
主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。
主键可以用来在表中唯一的确定一行数据。
一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。
2)主键选取的原则
主键应是对系统无意义的数据
永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
主键不应包含动态变化的数据，如时间戳
主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
主键尽量建立在单列上
3)添加主键约束
一张表只能有一个主键约束
create table employees2_ge(
eid number(6) PRIMARY KEY,
name varchar2(30),
email varchar2(50),
salary number(7,2),
hiredate date
);
insert into employees2_ge
(eid,name,email)
values
(null,'JACK','QQ.COM');
select * from employees2_ge;
数据库中要实现 两个表之间 多对多 的关系,要在中间设计一个 关系表.
