表连接等

11号遗留问题length(sysdate) =9
select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
select * from nls_instance_parameters where parameter='NLS_DATE_FORMAT';
select * from nls_database_parameters where parameter='NLS_DATE_FORMAT';
千年虫


where 子句中常用的运算符


算数运算符
+-*/
逻辑运算符
not ,and ,or
比较运算符
单行比较=,>,>=,<=,<
多行比较符>any,>all,<any,<all,in,not in
模糊比较like(%,_)
特殊比较is null
()优先级最高
between and
select ename,sal from emp where sal between 3000 and 5000;
create table test6(a varchar(20));
insert into test6
values('AZZL');
insert into test6
values('A%ZZL');
                                                                                   where与having的区别：
select * from test6 where a like 'A%%'                                          不能再where字句中使用组函数
select * from test6 where a like 'A\%%' escape '\'                              可以在having字句中使用组函数                                      
'' 和 ""
''内表示字符
"" 别名 ，保持原样
select a from test6
select "a" from test6
select "A" from test6


连续两个'表示转义
select empno||'''s name is ' ||ename from emp
in 检验一个值是否在一个列表中
select * from emp where empno in(7788,7900)


交互输入变量符&
select * from emp where empno=&emp_no


使用逻辑操作符
select * from emp where sal >1000 and job ='CLERK';
select * from emp where sal >1000 or job ='CLERK';
select * from emp where job not in('CLERK');


分组函数


最重要的5个分组函数
sum(),avg(),count(),max(),min() , distinct()--去掉重复的记录, wm_concat()--行转列


select sum(sal) sum,avg(sal) avg,max(sal) max,min(sal) min ,count(*) count from emp;
select job, sum(sal) sum,avg(sal) avg,max(sal) max,min(sal) min ,count(*) count from emp group by job
select min(hiredate),max(hiredate) ,min(ename),max(ename)  from emp ;
select count(*),count(comm) from emp ;
select deptno,avg(comm) from emp group by deptno
select deptno,avg(nvl(comm,0)) from emp group by deptno --nvl滤空函数，使函数不忽略空值，当第一个值为空时，返回第二个值




group by 创建组
一旦使用了group by ，select后面只能有两种列：组函数和分组特性列；在select列表中所有未包含在组函数中的列都应该包含在group by字句中
                                                                                  包含在group by子句中的列不必包含在select列表中
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000；
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno；
select deptno,avg(sal) from emp where sal>2000 group by deptno；
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000 order by avg(sal);
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000 order by job;


select from where group by having order by


分组函数的嵌套
单行函数可以嵌套任意层，分组函数最多可以嵌套两层
select sum(sal) from emp group by deptno
select avg(sum(sal)) from emp group by deptno
select count(avg(sum(sal))) from emp group by deptno




数据限定与排序


where限定 from后面的表或视图，限定的选项只能是表的列或列单行函数或列表达式，where后不可以直接使用分组函数
select job,ename,sal from emp where sal >2000
select job,ename,sal from emp where length(sal)>3
select job,ename,sal from emp where sal +comm >2000


having 限定group by的结果，限定的选项必须是group by 后的聚合函数或分组列，不可以直接使用where后的限定选项
select deptno,sum(sal) from emp group by deptno having deptno=10
select deptno,sum(sal) from emp group by deptno having sum(sal)>8000


排序
order by 总是在一个select语句的最后边
排序可以使用列名，列表达式，列函数，列别名，列位置编号，select的投影列可不包含排序列，除指定的列位置编号外


升序asc 降序 desc，有空值的列的排序，缺省（升序）时，null排在最后面
使用多个列排序，多列用逗号隔开，每列后面可以指定升降序
select ename,sal from emp order by sal
select ename,sal salary from emp order by salary
select ename,sal salary from emp order by 2
select ename,sal,comm ,sal+comm from emp order by sal +comm
select deptno,avg(sal) from emp group by deptno order by avg(sal) desc
select ename,job,sal+comm from emp order by 3 desc
select ename,job,sal+comm from emp order by 3 desc nulls last
select ename,job,deptno from emp order by deptno asc,job desc




空值


空值的数据行将对算数表达式返回空值
select ename,sal,comm,sal+comm from emp
select sum(sal),sum(sal+comm) from emp
比较表达式有空值时返回假
select ename,sal,comm from emp where sal >=comm


非空值与空值做||时，null转化为""，合并的数据为varchar2
select ename,sal,comm,sal||comm from emp 
外键值可以为null
空值在where子句里用is null 或 is not null
空值在update或insert时，直接使用null
update emp set comm =null where empno =7788


处理空值的几种函数方法
nvl(expr1,expr2)
select nvl(1,2) from dual;
select nvl(null,2) from dual;
nvl2(expr1,expr2,expr3)
当第一参数不为null时，取第二个参数的值，否则取第三个参数的值
select nvl2(1,2,3) from dual;
select nvl2(null,2,3) from dual;
select ename ,nvl2(comm,sal+comm,sal) from emp;
nullif(expr1,expr2)
当第一个参数和第二个参数一样时返回为空，当两个参数不一样时返回第一个参数，第一个参数不允许为空
select nullif(1,1) from dual
select nullif(1,2) from dual
coalesce(expr1,expr2,...)
返回从左起始，第一个不为空的值，如果所有参数都为空，则返回空值
select coalesce(null,null,1,1) from dual
select coalesce(null,null,null,null) from dual






多表连接技术


交叉连接（笛卡尔积）
create table L(id int ,name1 varchar(10));
create table U(id int,name2 varchar(10));
insert into L
values(1,'a');
insert into L
values(2,'b');
insert into L
values(2,'c');
insert into L
values(4,'d');
insert into U
values(1,'A');
insert into U
values(2,'B');
insert into U
values(3,'C');


select * from l cross join u--sql99
select * from l,u--oracle
非等值连接
select empno,ename,sal,grade,losal,hisal from emp,salgrade where sal between losal and hisal
等值连接
内连接 --求公共部分
select * from l inner join u on l.id = u.id
select * from l, u where l.id = u.id
外连接（左外，右外，全连接）
select * from l left outer join u on l.id = u.id;
select * from l left join u on l.id = u.id;
select * from l,u where l.id = u.id(+);


select * from l right outer  join u on l.id = u.id;
select * from l right   join u on l.id = u.id;
select * from l,u where l.id(+) = u.id;


select * from l full  join u on l.id = u.id;
自连接 --通过别名方式
select * from l ,l
select * from l a,l  
自然连接（隐含连接条件，自动匹配连接字段）
select * from emp natural join dept
select * from emp  join dept using(deptno)


select * from l natural join u
alter table l add  A varchar(2);
alter table u add  A varchar(2);
select * from l natural join u


update l set a='A' where id =1;
update u set a='A' where id =1;
select * from l natural join u
select * from l  join u using(id)
select * from l  join u using(id,a)
select l.id from l  join u using(id)
select a from l  join u using(id)




集合运算
create table emp1 as select * from emp where rownum =1
insert into emp1
values(3030,'张振磊','CLERK',7788,sysdate,800,null,10)


union
select * from emp1
union
select * from emp
union all
select * from emp1
union all
select * from emp
intersect
select * from emp1
intersect
select * from emp
minus
select * from emp1
minus
select * from emp






select id,name1 from l
union all
select empno,ename from emp


select id,name1 from l
union all
select empno,ename from emp


select id,name1 from l order by name1
union all
select empno,ename from emp order by ename




select id,name1 from l order by name1
union all
select empno,ename from emp order by ename


select id,name1 from l 
union all
select empno,ename from emp order by ename




select id,name1 from l 
union all
select empno,ename from emp order by name1








子查询
单行单列子查询（>,<,=,<>,>=,<=）
select ename,sal from emp where sal >(select sal from emp where empno =7788)


多行单列子查询（in,not in,all,any）
select ename from emp where empno in(select mgr from emp  )
select ename from emp where empno not in(select mgr from emp  )
select ename from emp where empno not in(select nvl(mgr,0) from emp  )
select ename ,deptno,sal from emp where sal >all(select avg(sal) from emp group by deptno)
select ename ,deptno,sal from emp where sal >any(select avg(sal) from emp group by deptno)


多行多列子查询
drop table emp1;
create table emp1 as select * from emp;
update emp1 set sal=1600,comm=300 where deptno <>30 and empno =7369;
select * from emp1 where (sal,comm) in(select sal,comm from emp1 where deptno =30) and deptno <> 30






布尔运算符 not
select * from emp where empno =7788
select * from emp where not empno =7788
select * from emp where empno !=7788
select * from emp where not empno !=7788


select * from emp where ename like 'S%'
select * from emp where ename not like 'S%'
select * from emp where not ename like 'S%'
select * from emp where not ename not like 'S%'


select * from emp where deptno in(10)
select * from emp where deptno not in(10)
select * from emp where not deptno in(10)
select * from emp where not deptno not in(10)


select * from emp where sal between 1500 and 3000
select * from emp where sal between 1500 and 3000
select * from emp where not sal between 1500 and 3000
select * from emp where not sal not between 1500 and 3000




select * from emp where comm is null
select * from emp where comm is not null
select * from emp where not comm is null
select * from emp where not comm is not null


select * from emp where exists(select * from dept where deptno = emp.deptno and dname ='SALES')
select * from emp where not exists(select * from dept where deptno = emp.deptno and dname ='SALES')




from 子句使用子查询（也叫内联视图）


select ename,sal,avgsal from emp,(select deptno,avg(sal) avgsal from emp group by deptno ) b
where emp.deptno = b.deptno and emp.sal > b.avgsal


关联子查询
select ename,sal from emp outer where outer.sal > (select  avg(sal) avgsal from emp inner where outer.deptno = inner.deptno )


关联子查询用于update语句
drop table emp1;
create table emp1 as select emp.*,loc from emp,dept where emp.deptno = dept.deptno(+);
select * from emp1;
update emp1 set loc = null;
update emp1 set deptno =99 ,loc ='aaaa' where empno =7788
select * from emp1;
update emp1 set loc =(select loc from dept where deptno = emp1.deptno)
select * from emp1;
update emp1 set loc = null;
update emp1 set deptno =99 ,loc ='aaaa' where empno =7788;
select * from emp1;
update emp1 set loc =(select loc from dept where deptno = emp1.deptno)
where exists(select 1 from dept where deptno = emp1.deptno)
select * from emp1;


关联子查询特殊形式 exists和not exists
select * from emp where exists(select * from dept where deptno = emp.deptno and dname ='SALES')
select * from emp where not exists(select * from dept where deptno = emp.deptno and dname ='SALES')






别名
表别名和列别名
select ename 姓名 from emp 员工
select ename 姓 名 from emp 员工
select ename "姓 名" from emp 员工
select * from emp where rownum >1
select * from (select ename,rownum rm from emp )  where rm >1