/*******************************************************************************************/
四、where过滤
where过滤条件
基本语法select ... from ... where condl
其中这个条件就涉及到运算符
其中，比较运算符要注意就是不等的意思between and是一对整体
1.比较运算符 = != < > = between and
--查询10号部门的员工信息
SQL> select * from emp where deptno=10;
--查询员工名字为king的员工信息
SQL> select * from emp where ename='KING';//注意在sql中单引号代表字符串，双引号代表别名,注意字符串要全匹配也就是说区分大小写
--查找薪水不等于1250员工的信息
SQL> select * from emp where sal != 1250;
SQL> select * from emp where sal 1250;
--查询入职日期为1981年11月17日的员工信息---日期型的查询
注意日期型的查询，也就是如何匹配日期，注意日期格式敏感
首先查看数据库的参数配置，也就是查看日期格式的设置：select * from v$nls_parameters;
然后查询的时候比较的字符串格式要与设置的一致
或者设置日期格式：alter session set NLS_DATE_FORMAT='yyyy-mm-dd';,然后按照该格式输入字符串查询,注意登录的是会话所以设置的是所属于会话的。
所以如果有人修改了设置，查询会报错。所以一般不会轻易去改动
SQL> select * from emp where hiredate='1981-11-17';
select * from emp where hiredate='1981-11-17'
*
第1行出现错误:
ORA-01861:文字与格式字符串不匹配
--查看日期设置select * from v$nls_parameters; year-月份-日期yyyy-mm-dd
见图1：
[attach]1318[/attach]
SQL> select * from emp where hiredate='17-11月-81';--日期格式敏感
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
SQL> select * from emp where hiredate ='1981-11-17';
带来一个问题,如果有人修改了设置了,我们的语句将报错!!!
SQL> alter session set NLS_DATE_FORMAT='dd-mon-rr';--恢复原状
2.前面都是一个条件，如果需要多个条件的就需要用到逻辑运算符：逻辑运算符 and or not
--查询10号部门或者20部门的员工信息
SQL> select * from emp where deptno =10 or deptno = 20;
--查询10号部门员工工资为1300的员工信息
SQL> select * from emp where deptno =10 and sal=1300;
--查找工资介于1000-2000之间的员工信息，
SQL> select * from emp where sal >=1000 and sal select * from emp where sal between 1000 and 2000 ;
注意，between 1000 and 2000这个是闭区间，即[]
--查询81年2月(含2月)至82年2月(不含2月)入职的员工信息81-2-1 -->82-1-31
SQL>select * from emp where hiredate between '1981-2-1' and '1982-1-31';
--查询奖金为空的员工信息-null
注意，查询为空的记录时，也就是null时，要注意，
null重要结论，不能用= !=,也就是说columnname=null或者!=null均永远为假
正确的写法:SQL>select *from emp where comm is null;
或者SQL>select *from emp where comm is not null;
--多个逻辑运算符的执行顺序
注意出现多个逻辑运算符的执行顺序，也就是优先级，从前面我们可以得到从右边开始比对，所以逻辑运算是从右开始执行，
当一个结果计算好后再由右向左与下一个进行运算。如果想打破这种顺序，就需要用()将先执行的包括起来：
SQL>select * from emp where (deptno=10 or deptno=30) and sal=1250
3.in：在集合中
--查询部门号是10或者20的员工信息：
SQL>select * from emp where deptno in (10,20);
--查询部门号不是10或者20的员工信息：
SQL>select * from emp where deptno not in (10,20);
注意，如果想查询部门号不是10或者20也不为空的员工信息：
select * from emp where deptno not in (10,20,null);
这样是错误的，因为null不能用来进行判断的，否则为假，这个前面也说了这个重要结论了
所以结论是：not in的集合不能有null
4.like:模糊查询
知识点：‘%’匹配任意多个字符。‘_’匹配一个字符
--查询员工首字母是S的员工信息
SQL>select * from emp where ename like 'S%';
--查询四个字母的员工信息
SQL>select * from emp where ename like '____';//四个下划线即可
--查询带有下划线的，涉及到转义，要使用关键字escape
SQL>select * from emp where ename like '%\_%' escape'\';//把_转义为普通字符，注意escape用来定义转义字符
SQL>select * from emp where ename like '%/_%' escape '/';
select ...指定列
from ... 指定表
where...指定条件
作用是为了实例可以解析sql命令
/*******************************************************************************************/
五、排序
完整sql语句
select...
from...
where...
group by...
having...
order by...
排序可以列名，表达式，别名，序号，语法是order by col|alias|number默认是asc模式，升序，desc降序
其中，这个序号的意思是属于查询结果集中第几个的意思，如果查询结果集是*那么序号就是第几列了。
例如：
1.
--员工信息按入职日期排序
SQL>select * from emp order by hiredate;
2.排序（序号）
--员工薪水按从大到小排序（序号）
SQL>select * from emp order by sal desc;
SQL>select * from emp order by 6 desc;//第6列降序排序，6等价于sal
SQL>select empno,ename,sal from emp order by 3 desc;//等价于前面的，还是按照第6列(sal)降序排序
3.多种条件排序
--员工信息按部门、薪水排列
//两种或多种条件排序，条件越靠前面的越优先进行排序，即当前面排序条件相同时再用后面的条件排序，即
排序的原则:先按第一个字段,然后再按第二个字段...
SQL>select * from emp order by deptno,sal;//先按deptno排序，deptno中一样的再按sal排序。
4.作用范围
--asc和desc作用范围是它之前的一个字段
SQL>select * from emp order by deptno,sal desc;//desc只作用于sal,即sal是按递减排序的。
5.null代表无穷大
--员工信息按奖金逆序 (nulls last --null)
null代表无穷大,排序默认排在最后,
如果在降序中则在最前面，要想把null的放在最后则要在语句后面加上nulls last，强制放到最后
或者使用滤空函数把空的变为最小再排序：
SQL>select * from emp order by nvl(comm,-1) desc;//可能数据库的数据里有0的，所以填-1来保证最小(一旦comm为null就等于-1
)。
--trim函数，
trim函数是去空格的,去掉字符串左边和右边的空格,字符串中间的空格是不会去的.如果不是去掉指定字符串的首尾的空格，
而是去掉字符串首尾的其他字符，则原先的写法由trim(' 字符串 ')改为trim('字符' from '字符串')
SQL>select 'aaa'||trim(' hello world ')||'bbb' from dual;
SQL>select trim('H' from 'HHHHHelloHHHworldHHHHH') from dual;--trim(C from str)去掉str首尾为C的字符
--replace函数，
SQL>select replace('helloworld','llo','kk') from dual;
SQL>select replace('helloworld','llo','') from dual;
//表示的是将helloword中的llo替换为kk，如果kk不写得话就相当于去掉了其中的llo
1.case...end -------sql99标准
语法为：
CASE expr WHEN comparison_expr1THEN return_expr1
[WHENcomparison_expr2 THENreturn_expr2
WHENcomparison_exprn THENreturn_exprn
ELSE else_expr]
END
case变量when变量值then该变量值下要做的动作
(when变量值then该变量值下要做的动作)
(else其他条件要做的动作)
end
示例：
SQL> select empno,ename,job,sal涨前薪水,case job when 'PRESIDENT' then sal+0
when 'MANAGER' then sal+200
else sal+1000 end涨后薪水
from emp;
2.分组数据
分组数据的含义就是把数据进行分组，然后按组进行处理，使用GROUP BY子句数据分组：
按照group by后给定的表达式，将from后面的table进行分组。针对每一组，使用组函数(分组数据的含义就是把分出来的各个组的数据一起显示出来)。
group by中出现的也必须在在select中出现，这样才能选中显示出来(在select中出现的非组函数的列则必须有分组即group by...，
同时在select中出现的非组函数的列,必须在group by中出现.)
示例：
统计各个部门的平均工资，先统计10号的，再统计20的，最后统计30的
SQL> select deptno,avg(sal) from emp group by deptno;//按部门号计算每个部门的平均工资，即按部门号分组，一号一组，再计算每组的平均工资，(再按部门号列出来)
SQL> select a,avg(X) from tablename group by a
SQL> select a,b,avg(X) from tablename group by a,b
SQL> select a,b,c,avg(X) from tablename group by a,b,c
...
结论：(首先出现a,avg(x)则必须有分组即group by...，同时在select中出现的非组函数的列，必须在group by中出现)。
--查询各部门平均工资
SQL> select deptno,avg(sal) from emp group by deptno
--查询各部门各工种平均工资
SQL> select deptno,job,avg(sal) from emp group by deptno,job;
where后不能使用分组函数. having的作用对分组进行过滤
--查询平均薪水大于2000的部门
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--求10号部门员工的平均薪水
select deptno,avg(sal) from emp group by deptno having deptno=10;
select deptno,avg(sal) from emp where deptno=10 group by deptno;
having与where哪个好？
如果都能用的情况下，优先使用where，因为先过滤再分组(sql语句是顺序执行的)效率更高。
4.常见的非法使用组函数的情况
--缺失group by字句,语法不符合规范.
//
select查询可以全部列,部分列,表达式,别名
group by分组的要求：在select中出现的非组函数的列，必须在group by后出现，--数据按什么分组，数据如何显示？。
分组函数也叫组函数，或者聚合函数
like效率比较低，能少用就少用。
2.连接条件写法:1.表名.列名 2.给表起别名 别名.列名
推荐用第二种，因为当表名很长的时候别名更方便。
例子：
select * from emp,dept;//emp,dept就是卡尔全集:
select * from emp e,dept d where e.deptno=d.deptno;//e d是别名，连接条件是deptno相等的
3.根据连接条件的不同可以划分为：等值连接，不等值连接，外连接，自连接
Oracle连接:
Equijoin：等值连接
Non-equijoin：不等值连接
Outer join：外连接
Self join：自连接
SQL: 1999
Cross joins
Natural joins
Using clause
Full or two sided outer joins
1).等值连接(使用等号，在mysql中称为内连接)
--查询员工信息：员工号 姓名 月薪(emp)和部门名称dept
SQL> select empno,ename,sal,dname from emp e, dept d where e.deptno = d.deptno ;
注意，都有的列不能放在select后面，这样会有冲突，必须要指定时属于哪张表的列，放在连接条件内其实就不用指定了
2).不等值连接(不使用等号)
--查询员工信息：员工号 姓名 月薪 和 薪水级别(salgrade表)
SQL> select empno,ename,sal,grade
from emp e,salgrade s
where e.sal>=s.losal and e.sal select empno,ename,sal,grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
注意，当前薪水符合范围的，这样的记录中的薪水级别才是对的。
也就是说，记录要符合连接条件，也就是经过连接条件过滤后的记录是要有意义的。
例子:
--按部门统计员工人数，显示如下信息：部门号 部门名称 人数,注意有的部门没有人
SQL> select d.deptno,d.dname,count(empno)//count(empno)统计有人的部门，所以不能用*。由于不知道用哪一列，以及不知道列的别名是什么，所以这里最后写
from dept d,emp e
where e.deptno(+)=d.deptno//需要包含那条数据不完整的记录，也就是想保留等号右边的，(+)放在等号左边，称为右外连接
group by d.deptno,d.dname;
--左外连接:想保留等号左边的,(+)放在右边
SQL> select d.deptno,d.dname,count(empno)
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname
order by 1
--右外连接:想保留等号右边的,(+)放在左边
SQL> select d.deptno,d.dname,count(empno)
from dept d,emp e
where e.deptno(+)=d.deptno
group by d.deptno,d.dname
order by 1
4).自连接
一个表中的两条记录需要组合起来或者说需要相连接起来,这时就需要自连接,也就是把一张表当作两张表.
所以自连接时特殊的外连接。
例子:
--查询员工信息：xxx的老板是yyy
分析：可以把员工表emp当成两个表，一个是员工表，一个是老板表。
SQL> select e.ename||'''s boss is' || nvl(b.ename,'himself')//字符串内部还需要显示单引号，则直接加单引号即可
from emp e,emp b
where e.magr=b.empno(+)
//老板没有上级，所以这个条件不成立，但是为了把这条不成立的记录保留下来，也就是需要保留这条不完整的记录，这条记录
只有左边的，即只有左边的自己没有老板，所以保留左边的。
--下面的查询结果少了大老板,因为连接条件不成立
select e.ename||'''s boss is'||b.ename
from emp e,emp b
where e.mgr = b.empno
/*******************************************************************************************/
十、子查询
什么情况下使用子查询?一个查询不能完成的时候。
子查询的本质：sql嵌套sql
例子：
查询比scott工资更高的员工信息
分析：首先找到scott的工资，然后条件大于这个工资的。
SQL> select * from emp where sal>(select sal from emp where ename='scott');
注意事项：
1.合理的书写风格(如上例，当写一个较复杂的子查询的时候，要合理的添加换行、缩进)
2.小括号( )
3.主查询和子查询可以是不同表，只要子查询返回的结果主查询可以使用即可
--查询部门名称是“SALES”的员工信息
分析: 1.先找到SALES部门名称对应的编号2.用这个编号做条件过滤员工
select deptno from dept where dname ='SALES';
select * from emp where deptno = 30;
===>
SQL> select * from emp where deptno = (select deptno from dept where dname ='SALES');
4.可以在主查询的where,select,having,from后都可以放置子查询，即"两个by"后面不行。
select ..
from…
where…
group by…err
having…
order by…err
1).select后
--查询10号部门员工号，员工姓名，部门编号，部门名称
(1).等值连接写法
SQL> select e.empno,e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.deptno=10;
(2).子查询写法
SQL> select empno,ename,deptno,(select dname from dept where deptno=10)
from emp
where deptno=10;
2).from后
--查询员工的姓名，薪水和年薪
SQL> select * from (select empno,ename,sal*14 from emp);
3).where后
--查询与ward相同job并且薪水比他高的员工信息
先找到ward的工种和薪水,然后用作过滤条件
select sal,job from emp where ename ='WARD';
select * from emp where job='SALESMAN' and sal > 1250;
==>变成子查询
SQL> select * from emp where job=(select job from emp where ename ='WARD') and sal > (select sal from emp
where ename ='WARD');
4).having后
--查询高于30号部门最低薪水的部门及其最低薪水
SQL> select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 30)
5.不可以在主查询的group by后面放置子查询(SQL语句的语法规范)
6.强调：在from后面放置的子查询(***) from后面放置是一个集合(表、查询结果)
7.单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
单行的查询只能使用返回结果是单行的子查询(单行子查询就是该条子查询执行结束时，只返回一条记录(一行数据)),
即整个查询语句是单行的则括号里面返回的结果也要是单行的。多行子查询(子查询的结果是多行的)
这个规范的意思也就是说，结果要能组合起来。//其实主要意思是多行操作符操作的多行，所以多行操作符后面得是多行的集合，也就是多行子查询(子查询的结果是多行的集合)
单行操作符：
=、>、>=、 select * from emp where deptno in (select deptno from dept where dname in
('SALES','ACCOUNTING'));
2).多行操作符ANY，any后面为多行子查询(子查询的结果是多行的)
--查询薪水比30号部门任意一个(any某一个，即薪水最低的)员工高的员工信息
--多行子查询写法，any后面为多行子查询(子查询的结果是多行的)
SQL> select * from emp where sal > any (select sal from emp where deptno=30);
--单行子查询写法
SQL> select * from emp where sal > (select min(sal) from emp where deptno=30);
3).多行操作符ALL，all后面为多行子查询(子查询的结果是多行的)
--查询薪水比30号部门所有人工资高的员工信息
--多行子查询写法，all后面为多行子查询(子查询的结果是多行的)
SQL> select * from emp where sal > all (select sal from emp where deptno=30);
--单行子查询写法
SQL> select * from emp where sal > (select max(sal) from emp where deptno=30);
--得到老板
select * from emp where empno in( select mgr from emp);
--取反
SQL> select * from emp where empno not in( select mgr from emp where mgr is not null);//not in里面不能出现有空值，一旦有，那么结果始终为假
9.一般先执行子查询(内查询)，再执行主查询(外查询)；但是相关子查询除外
10.一般不在子查询中使用order by,但在Top-N分析问题中，必须使用order by
也可以使用工具来完成这个增加的操作：
工具名称：oracle net configuration assistant
注意，
登陆数据库的工具：pl/sql developer 中的drop命令表示的是删除的意思 。
/*******************************************************************************************/
十二、集合运算
1.集合运算符
集合运算的操作符：
1).A并B并集 对应sql关键字:union(集合的交集只保留一份), union all(两个集合的内容全部保留)
SQL> select * from emp where deptno in(10,20) union select * from emp where deptno in(20,30)
SQL> select * from emp where deptno in(10,20) union all select * from emp where deptno in(20,30)
2).A交B交集 对应sql关键字: intersect
SQL> select * from emp where deptno in(10,20) intersect select * from emp where deptno in(20,30)
3).A-B差集 对应sql关键字: minus
SQL> select * from emp where deptno in(10,20) minus select * from emp where deptno in(20,30)
SQL> select deptno,job,sum(sal) from emp group by deptno,job union
SQL> select deptno,to_char(null),sum(sal) from emp group by deptno union
SQL> select to_number(null),to_char(null),sum(sal) from emp;
SQL> break on deptno skip 2;//设置为 去掉相同的部门号两个，这个是报表的处理技巧
SQL> break on null;//恢复回去break on null
/*******************************************************************************************/
十三、数据处理
SQL语言类型：
DML data manlpulation language 数据操纵语言：insert,select,update,delete，所有的dml语句中都可以使用子查询，同时子查询的规则同样适用
DDL data definition language 数据定义语言: create,truncate
DCL data control language 数据控制语言: grant(赋权)/revoke(回收权限)
1.insert插入数据
语法格式：
INSERT INTO table[(column[,colum...])] VALUES (value{,value...});
value与column (数目)要对应
1).可以插入全部列，即所有的列都有新数据的插入，此时不用指明列名
insert into dept values(50,'50name','50loc');
2).插入部分列
insert into dept(deptno,dname) values(51,'51name');//隐式插入Null
insert into dept(deptno,dname,loc) values(51,'51name',null);//显式插入Null
同时还可以用格式化输入，比如加入引号后，则用户输入就不需要再输入引号了：
insert into dept values(&dpt,'&dnm','&loc');
4).批量新增数据
create table emp10 as select * from emp where 1=2;
复制emp表的结构，由于1=2条件不会成立，所以表的数据不会被复制
批量插入10号部门的信息
insert into emp10 select * from emp where deptno=10;//将查询返回的结果插入到新表中。
5).insert也可以使用选择一个集合的方式来插入
insert into dept select 57,'57name','57loc' from dual;
其中表示选中列的内容分别为的记录(特定的一条记录)
从伪表中去内容等同于直接输入指。
即等同于
insert into dept values(57,'57name','57loc');
2.update 更新数据
语法格式：
update tablename set column1=val1,column2=val2,... where cond;
对于更新操作来说,一般会有一个where条件,如果没有这个限制条件,更新的就是整张表.
例子：
update dept set loc ='51loc',dname='51dname' where deptno=51;
update dept set loc ='51loc',dname='51dname' where deptno=&dpt;//由用户输入来决定该得的是哪个编号的行
update dept set dname='52name',loc=null where deptno=52;//set中的等值可以有null
子查询的规则和注意事项在DML语句都适用!!
3.delete 删除数据
1).语法格式：
delete from tablename where cond...;
2).例子：
delete from dept where deptno=52;
3).注意：
做事情之前要做好备份：
批量新增就是备份了或者用客户端工具拷贝到excl(可以拷贝回来)。
(6).【做实验sql.sql】：验证delete和truncate的时效性
关闭时间开关就不会显示:已用时间:xxxx
关闭回显开关就不会显示:已选择x行
@符号代表要执行哪个脚本
语句执行时间记录开关：set timing on/off
回显开关：set feedback on/off
示例：
SQL> set feedback off
SQL> set timing off
SQL> @c:\sql.sql
SQL> select count(*) from testdelete;
COUNT(*)
----------
5000
SQL> delete from testdelete;
SQL> rollback;
SQL> select count(*) from testdelete;
COUNT(*)
----------
5000
SQL> set timing on
SQL> set feedback on
SQL> delete from testdelete;
已删除5000行。
已用时间: 00: 00: 00.03
SQL> select count(*) from testdelete;
COUNT(*)
----------
0
SQL> set feedback off
SQL> set timing off
SQL> @c:\sql.sql
SQL> select count(*) from testdelete;
COUNT(*)
----------
5000
SQL> set timing n
SP2-0265: timing必须设置为ON或OFF
SQL> set timing on
SQL> set feedback on
SQL> truncate table testdelete;
表被截断。
已用时间: 00: 00: 00.31
SQL> select count(*) from testdelete;
COUNT(*)
----------
0
已选择1行。
1).Top-N问题分析
(1).一般不在子查询中使用order by,但是在top-n分析问题中，必须使用order by
(2).rownum行号(伪列)，也就是查询这个就会显示行号出来。
(3).rownum随着集合的创建就会存在，不会随着后面修改，也就是无论怎么排序显示出来，记录对应的行号是不会变的
(4).由于rownum是按照默认顺序生成，所以只能使用=符号。
2).Top-N问题答案
SQL> select rownum,empno,ename,sal from ( select * from emp order by sal desc ) e where rownum 4来过滤了。
4).分页问题答案
SQL> select *
from (select rownum r, empno, ename, sal
from (select * from emp order by sal desc) e
where rownum 4;
2.【第二题提示】：找到emp表中薪水大于本部门平均薪水的员工
1).两张表中没有相同的列，即没有冲突，所以直接选择查询即可：
先得到各个部门的平均薪水
select deptno,avg(sal) from emp group by deptno
把上述结果当成一个集合(表)
SQL>select empno,ename,sal,avgsal
from (select deptno,avg(sal) avgsal from emp group by deptno ) a ,emp e
where a.deptno = e.deptno
and e.sal > a.avgsal
2).相关子查询写法:
相关子查询先执行主查询，再执行子查询
原因在于，子查询的执行需要的东西由主查询产生，即子查询依赖于主查询。
示例：
---求10号部门 大于10号部门平均薪水的员工
SQL>select empno,ename,sal,(select avg(sal) from emp where deptno=10) avgsal
from emp
where deptno=10
and sal > (select avg(sal) from emp where deptno=10)
===>将10号部门替换为emp表每条记录的部门：这个就是相关子查询
SQL>select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno= e.deptno)//注意依赖：e.deptno
4.统计部门人数小于4的部门情况，显示部门编号，名称，部门人数
select d.deptno,d.dname,count(empno)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.name
having count(empno) < 4
order by 1;
//1表示按照第一列进行排序
2).查看回收站
SQL> show recyclebin;//sql plus工具里才看得到结果
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$A8EsHYSkSiKQfnHrOw4E7g==$0 TABLE 2017-02-16:09:45:54
3).闪回表，(flashback闪回，需要开启行移动，但是表的闪回不需要)
flashback table t2 to before drop;//oracle 10g才支持的功能，从回收站里恢复(闪回)
注意：并不是所有的用户都有“回收站”，对于没有回收站的用户（管理员）来说，删除操作是不可逆的。
4).不经过回收站删除
SQL> drop table t3 purge;//不经过回收站删除，类似win：shift+delete;
SQL> purge recyclebin;//清空回收站,注意回收站是在服务器中的，如果清除回收站，则有可能把别人的也清除了。
回收站已清空。
3.修改表
增加一列
alter table t1 add email varchar2(30);//t1表增加一列,email是新列的名字，varchar2(30)列内容的类型
修改列属性(内容的类型)
alter table t1 modify email varchar2(40);//如果表非空，长度往小改，这时如果有数据长度大雨想要修改的长度值，则会修改失败
修改列名
alter table t1 rename column email to address;
删除列
alter table t1 drop column address;
3).创建索引：
--create index indexname on tablename(columnname1,columnname2,...);
create index myinde on emp(ename);//create index创建索引，myinde索引名，on emp(ename)使用emp(ename)作为索引