很久前Oracle的一些笔记_4-6

-------------------------------------4----------------------------------------

!!1!! select * from T1 where exists (select 1 from T2 where T1.a=T2.a);  
!!2!! select * from T1 where T1.a in (select T2.a from T2); 这两句话查询相同效率不同
select * from (select a.* ,rownum r from emp a) where r<5 and r>10;
rownum 是伪列
not是不带索引的 所以效率最低
rowid 是不变的固定的二进制码 唯一标准
rownum  是用来表示结果集顺序的虚列 
insert  into codename (cn_id,cn_code,cn_name,cn_type)     
select 8032,21,'内训',76 FROM DUAL   union   
select 8033,22,'外派',76 FROM DUAL   union   
select 8034,23,'境外',76 FROM DUAL
1  笛卡尔积     标准SQL select from where group by having order by
 将本表的没一条记录和另一张表的所有记录列举起来
 即  4(本表数据行) * (另表数据行)14=52 
   select * from dept,emp e;
2 等值连接 就是关联的条件
 select emoloyee,id, department.id from emoloyee ,department where  department.id=emoloyee.departId;
3.三表等值查询
select * from dept d,emp e,sal s where d.deptno=e.deptno and e.salary=s.salary
等值连接又分为 内连接 和外连接    等值连接默认是内连接
select emoloyee,id, department.id from emoloyee ,department where  department.id=emoloyee.departId;(可能有的数据库不识别)
这个以标准sql 可以写成:
select emoloyee,id, department.id from emoloyee inner join department on emoloyee.departId=department.id;
（标准SQL 所有的都是识别）


外连接  
---外连接 某些不满足条件的列也会显示出来
--也就是说 只限制其中一个表 而不限制另一个表


外连接又分为 左连接 和 右连接


 左外连接     +在右边  以左边为准 左边的表记录全显示 如果左边表有记录  右边表没有记录 则补NULL
select emoloyee.id, department.id from emoloyee ,department where  department.id=emoloyee.departId;（14条数据）
select emoloyee.id, department.id from emoloyee ,department where  department.id=emoloyee.departId(+);(15 条数据)


标志左连接
select emoloyee,id, department.id from emoloyee left outer join
 department on emoloyee.departId=department.id;


右连接 +号在左边  以右边为准 右边的表记录全显示 如果右边表有记录  左边表没有记录 则补NULL
select emoloyee.id, department.id from emoloyee ,department where  department.id(+)=emoloyee.departId;
标志右连接
select emoloyee,id, department.id from emoloyee right outer join
 department on emoloyee.departId=department.id;


自连接 左连接 右连接的集合 
以左边 又以 右边为准
标志左连接
select emoloyee,id, department.id from emoloyee full outer join
 department on emoloyee.departId=department.id;


4 组合函数  聚集函数
COUNT(*|列名)   max(列名)   max(列名) min(列名)   sum(列名)//消除一列的null   AVG(列名)
select count(*) from emp;14
select count(1) from emp;14  用来看有没有结果
select avg(nvl(comm,0)) from temp;


5.数据分组  group by     
group by  子句的真正作用是和聚集函数配合使用 用来对查询出来的数据 进行分组
select deptno,count(*) from emp group by deptno;


如果select之后的查询列表中间有组函数的情况,那么查询列表的字段要么是组函数，要么在group by中
having 是对组进行筛选的 
select deptno,count(*) from emp group by deptno having sal(salary)>3000;


集合运算符：
Union   从两个查询中返回消除重复之后的结果   Or的意思
  select deptno from dept UNION select deptno from emp;


Union ALL       从两个查询中返回结果   包括重复的  
select deptno from dept UNION ALL select deptno from emp;


INTERSECT 交集  返回连两个查询中都出现的记录
select deptno from dept  INTERSECT select deptno from emp;




MINUS 差集  返回两个查询中除了都有的记录以外的记录
select deptno from dept  MINUS select deptno from emp;
~~~~子查询   使用子查询解决问题  子查询 可以出现在where  和 having  子句里
查询比ALLEN工资高的人


select sal from  emp where ename='ALLEN';
select ename from emp where sal>(select sal from  emp where ename='ALLEN');子查询


查询和ALLEN同一个部门的人
sselect ename from emp where deptno=(select deptno from emp where ename="ALLEN");


子查询特性： 子查询在(内部查询) 在主查询之前执行一次
 子查询的结果被用于主查询


单行子查询：
 只返回给主查询一条结果的查询
那么在主查询使用单行比较运算符 来操作从子查询返回的一条记录
单行比较运算符    =       >    >=    <    <=    <>  


---查询工资高过公司平均水平的员工
select ename from emp where sal>(select avg(sal) from emp);


HAVING 子查询  找出最低工资比 部门号为50的最低工资 高的部门标号 以及最低工资
      子查询返回的是单条记录  然后返回给having 子句


Oracle 首先执行子查询   再将结果返回给主查询的HAVING子句
select deptno,min(salary) from emp group by deptno 
having min(salary) >(selecct min(salary) from emp where deptno=50);


当子查询返回多个结果(不管在where子句 还是having子句) 你所使用的运算符是
单行比较运算符    =       >    >=    <    <=    <> 
那么绝对报错


如果子查询没有返回结果
那么整个查询 将不返回结果
 单行比较运算符    =       >    >=    <    <=    <> 


多行子查询 (返回的子查询数据有多条)
多行比较运算符
 IN  与列表的任一成员相等
ANY 与子查询返回的每一个值比较 只要其中一个满足条件 就能出现在结果集
ALL 与子查询返回的所有值比较   只有满足所有条件 才能出现在结果集里


IN  与列表的任一成员相等
select min(sal) from emp group by deptno ;返回(950,800,1300)


select * from emp where sal in (select min(sal) from emp group by deptno)；其实就是
select * from emp where sal in (950,800,1300)；


ANY 与子查询返回的每一个值比较
select min(sal) from emp group by deptno ;返回(950,800,1300)
select * from emp where sal > any(select min(sal) from emp group by deptno);
单行运算符和多行运算符组合？> any？~~~~~~原来一直不懂 ~
相当于 select * from emp where sal < any(950,800,1300)；   只需要满足任意一个就OK
只要salary<1300的都能返回~~~~~ 相当于 select * from emp where sal < 1300；
ALL 与子查询返回的所有值比较   只有满足所有条件 才能出现在结果集里
select * from emp where sal > all(select sal from emp where job='SALESMAN');


select sal from emp where job='SALESMAN';结果为(1500,1600,1300);
那么上一句就等于
select * from emp where sal > all(1500,1600,1300);  只有工资大于1600的才能出现在结果集


IN与EXIST的作用相同  当为了提高效率 而设置了这两个 来分别应付不同的情况


EXISTS  存在   它就根据子查询有没有值  来判断要不要执行主查询
select id,name from where exists(select * from B where B.id=1);
它判断select * from B where B.id=1这条子句是否有返回值 有返回值
则执行主查询 若没有则不执行主查询？ 是这样吗？


where exists  怎么看着别扭啊？
！！1！！select * from T1 where exists (select 1 from T2 where T1.a=T2.a);
！！2！！select * from T1 where T1.a in (select T2.a from T2);


！！1！！select * from T1 where exists (select 1 from T2 where T1.a=T2.a);
外表的查询记录10行 内表的查询记录上亿行 那么它执行的也很快
--由于exists使用loop的方式,所以循环的次数对于exist影响最大,
所以外表要记录数少,内标就无所谓了  exist专治内表?
T1的数量少  而T2的数量则非常大 时  T1<<T2 那么查询效率高


！！2！！select * from T1 where T1.a in (select T2.a from T2);
--由于in使用hasn join的方式,所以如果内表范围小的话,整个查询的范围就小
如果内表很大，外表也很大 那么查询的效率就很差 很差了
T1的数量非常大  而T2的数据量小时  T1>>T2 那么in  查询效率高
             in            
    外表  |     /
          |    /   exist     内表多是exist的天下   外表比较多 是 in的天下
          |   /
          |__/_________
                内表
rownum 是伪列  rownum  重要


在生成结果时自动生成
select rownum,deptno,dname,loc from dept;   4条记录


select deptno,dname,loc from dept where rownum=1;  1条记录


select deptno,dname,loc from dept where rownum=2; 0条语句？ 
Oracle 首先查询到了4条记录然后找出第2条为输出结果(此时原来的rownum是4 找到rownum=2
这条数据时 Oracle更新了rownum  此时条数据的rownum为1了(因为过滤后只有它一条记录了))
这是它的rownum等于1  它又发现 需要的条件是rownum=2 这时结果就空集了。


select deptno,dname,loc from dept where rownum<3;  2条记录


select deptno,dname,loc from dept where rownum>1;  0条语句？
rownum一直变 导致 所有的数据的rownum一直减小 
从 有4条(1(过滤)---2,3,4)---->3条(原来过滤的的 2,3,4)重新分配rownum--(1(过滤)--2,3)-->--2条>--1条>-->没有数据
这样一直产生新的结果集 和新的rownum  导致没有结果了


select deptno,dname,loc from dept where rownum>0;  4条记录


Oracle对于rownum的处理
rownum是在得到结果集的时候产生的,用于标记结果集中的顺序的一个字段
这个字段被称为伪数列   也就是事实上不存在的一个数列  它的特点是按！！顺序标记！！
而且是!!逐次递加!!的   换句话说只有rownum=1的记录 才可能存在rownum=2的记录


假设我们的查询条件是rownum=2  那么在查询第一条记录时候
oracle标记这条记录的rownum为1  结果发现和rownum=2的条件不符合 于是结果为空


rowid 
oracle 还提供了另一个伪数列 rowid(二进制)
rowid和rownum不同  一般来说 每一行数据对应的rowid是固定唯一的
在这一行数据存入数据库时候就确定了
可以利用rowid来查询记录，通过rowid查询记录的速度是最快的
rowid只有表结构发生变化时才改变
 例如 select rowid,ename from emp whererowid='AAAMgxAEAAAAAQ';
删除重复数据
----如何删除重复数据(name,age) 1 重复数据完全删除  2删除重复的只留下一行记录


解决：1
通过创建临时表   效率最高？  开玩笑         几亿条数据试过？
create table person_temp select distinct name,age from person;
truncate table person; //清空记录
insert into person select * from person_temp;


2利用 rowid
delete from person where rowid in 
(select a.rowid as id from person a,person b where a.rowid>b.rowid and a.name=b.name and a.age=b.age)


3利用 rowid和max 或min函数    每一条数据都要里面比较
delete from person a where rowid not in (select max(rowid) from person b where a.age=b.age and a.name=b.name);


delete from person a where rowid not in (select min(rowid) from person b group by b.name,b.age);

-------------------------------------5----------------------------------------

select T4.name ,T1.JDBC,T2.Hibernate,T3.Spring from 
(select name,grade as JDBC from tb_course where course="JDBC") T1,
(select name,grade as Hibernate from tb_course where course="Hibernate") T2,
(select distinct name from tb_course) T4,
where T4.name=T1.name and T4.name=T1.name and T4.name=T2.name and T4.name=T3.name;


1.查询语句①②③④⑤⑥⑦⑧⑨⑩ 
create table student(id number primary key,name varchar2(10) not null,
  sex varchar2(4),age number,adress varchar2(50),tel varchar2(30),Email varchar2(30));


create table exam(Id number primary key ,course varchar2(10),
score number,exam_date date,constraints foreign key Id reference student(id));


① 查询小强的详细信息
  select * from student where name="小强";


② 查询20岁以上所有男同学的信息
  select * from student where age>20 and sex="男";


③ 按年龄降序显示学生信息
select * from student order by age desc;




④ 按学生的Id升序,分数降序 显示学生的ID 姓名 性别 课程  分数
select s.id ,s.name,s.sex,e.course,e.score from student s,exam e
where s.id=e.id order by s.id asc,e.score desc;


⑤ 查询所有学生的信息？
select * from student;


⑥ 查询成绩表 前4条记录的信息
select * from where rownum<5;


⑦列出 所有考试分数的一个总和
select sum(score) from exam;


⑧ 列出鲁智深的成绩总和
select sum(score) from exam where id=(select id from student where name="鲁智深");
select sum(score) from exam e,student s where s.id=e.id  s.name="鲁智深";


⑨列出C语言考试的平均分
select avg(score) from exam where course="C语言";


⑩列出李云参加了几次考试
select count(score) from exam where id=(select id from student where name="李云")
select count(score) from exam e,student s where s.id=e.id  s.name="李云";


① 列出学生中年龄 最大的
select max(age) from student;


② 列出C语言的最高分数
select max(score) from  exam;


③列出每种考试的考试名称和成绩总和
select score ,sum(score) from exam group by score;


④ 列出每个学生的姓名和他考试的次数
 select s.name ,count(course) from student s,exam e where s.id=e.id group by s.name;


⑤ 列出每个学生的姓名和考试次数，小于2次的不显示
select s.name ,count(course) from student s,exam e 
where s.id=e.id group by s.name having count(course)>=2;


⑥select * from student where age between 20 and 25;


2.查询语句
create table emp(id number primary key,name varchar2(20) not null,sex varchar2(4) not null,
age number not null check(age>=18),adress varchar2(30) not null,
tel varchar2(20) not null,Email varchar2(20))


create table product(id number primary key,type varchar2(20) not null,
mark varchar2(20) not null,spec varchar2(20));   id商品编号   type商品类型 mark商品品牌 spec商品规格


create table sales(id number primary key,eid number foreign key emp(id),
pid number foreign key product(id),cost number not null,quanties number not null,saletime date);


①查询所有职员的信息
select * from emp;


② 查询所有职员的姓名 电话 地址
select name ,tel,adress from emp;


③查询所有女职员的详细信息
 select * from emp where sex="女";


④ 查询年龄在24 到26之间的职员姓名,性别
  select name ,sex from emp where age between 24 and 26;


⑤ 查询家住长沙的女职员的姓名 电话 地址
  select name ,tel,address from emp where address like "%长沙%" and sex="男";


⑥ 查询李云 孙一成 林晓的电话 住址
 select tel,address from emp where ename="李云" or ename="孙一成" or ename="林晓";
select tel,address from emp where ename in(="李云" ,"孙一成" ,"林晓");


⑦select name ,sex,age from emp where address like "%郴州%" or address like "%株洲%";


⑧查询家在长沙, 年龄在25到28岁之间的男职员的姓名
seelect name from emp where address like "%长沙%" and sex="男" and age between 25 and 28； 


⑨ 查询邮件地址为空的职员
select * from emp where Email is null;


⑩ 总共有多少个职员
select count(*) from emp;


① 查询年龄最小的女职员的年龄
select min(age) from emp where sex="女";


②按性别统计职员的最大年龄 最小年龄 
select sex,min(age),max(age) from emp group by sex;


③笔记本 单笔销售最高的单笔销售值
select max(cost) from sales s,product p where p.id=s.pid and p.type="笔记本"


④熊猫电视机的平均销售价格
select sum(cost)/sum(quanties)  from sales s,product p where p.id=s.pid and p.mark="熊猫" 
and p.type="电视机"


⑤ 紫光笔记本 四月的销售总额
select sum(cost) from sales s,product p where p.id=s.pid and p.mark="紫光"
and p.type="笔记本" and to_char(s.saletime,'mm')='04'; 


⑥按类型 品牌 统计商品的销售总数量  销售总数量 低于20的不统计
select p.type,p.mark,sum(s.quanties) from sales s,product p where p.id=s.pid 
group by p.type,p.mark having sum(s.quanties)>=20;


⑦按品牌 规格统计笔记本的平均销售价格,按价格从高到低
select p.mark,p.spec,sum(cost)/sum(quanties) as avgcost from sales s,product p where p.id=s.pid and 
p.type="笔记本"
group byp.type,p.spec order by avgcost desc;


⑧按姓名统计男职员3月份的销售总额 销售总额低于80000的不统计
select e.ename ,sum(cost) from emp e,sales s where e.id=s.eid  and e.sex="男" 
and to_char(s.costtime,'mm')='03' group by e.ename having sum(cost)>=80000;




⑨找出销售总额最高的职员姓名
select * from (select e.ename ,max(cost) from emp e,sales s where e.id=s.eid  group by e.ename order by max(cost))
where rownum=1 ;




⑩按姓名 统计每个职工一共完成了多少记录
select e.ename ,count(s.eid) from emp e,sales s   where e.id=s.eid group by e.ename;




①找出销售业务笔数最少的职员 的姓名 和他完成的业务数
select * from(select e.ename ,min(s.eid) as take from   emp e,sales s   where e.id=s.eid group by e.ename 
order by min(s.eid)) where take =(select min(count(s1.eid)) from emp e1,sales s1   where e1.id=s1.eid);


多表查询
②所有职员,所有商品的销售情况 包括职员姓名 商品类别 商品品牌 商品规格 销售价值 销售时间
按销售时间从高到低
select e.name,p.type,p.mark,m.spac,s.cost,s.costtime form emp e,sales s,product p
where p.id=s.pid and e.id=s.eid order by s.costtime desc;


③笔记本4月份的销售情况
包括:商品编号 商品品牌 商品规格 销售数量 销售价值 销售数量从低到高排列
select p.id,p.mark,p.spec,sum(s.quanties),sum(s.cost) from product p,sales s where p.id=s.pid and
to_char(s.costtime,"mm")='04' group by  p.id,p.mark,p.spec order by sum(s.quanties);


select p.id,p.mark,p.spec,s.quanties,s.cost from product p,sales s where p.id=s.pid and
to_char(s.costtime,"mm")='04' and p.type="笔记本" order by sum(s.quanties);


day21
select T4.name ,T1.JDBC,T2.Hibernate,T3.Spring from 
(select name,grade as JDBC from tb_course where course="JDBC") T1,
(select name,grade as Hibernate from tb_course where course="Hibernate") T2,
(select distinct name from tb_course) T4,
where T4.name=T1.name and T4.name=T1.name and T4.name=T2.name and T4.name=T3.name;


select name,
       sum(decode(t.course,"JDBC",t.grade,0)) JDBC,
       sum(decode(t.course,"Hibernate",t.grade,0)) Hibernate,
       sum(decode(t.course,"Spring",t.grade,0)) Spring,
from
    tb_course t group by t.name;

-------------------------------------6----------------------------------------

---创建序列   序列名  select seq_tb_clazz.currval from dual;查看当前sequence的值
自动生成唯一的序列号  select seq_tb_clazz .nextval from dual;查看当前sequence的值下一个值
可以共享的对象           一个表建议用一个sequence 不要共享
通常用来创建主键值 
把序列缓存在内存里 可以加速访问序列的效率
create sequence seq_tb_clazz;
create sequence seq_tb_1;
那么就会在后台执行
create sequence SEQ_TB_CLAZZ   
minvalue 1           最小值
maxvalue 99999999999999999   最大值
start with 1   起点
increment by 1  增点
cache 20;  缓存


假如不做任何操作直接create sequence seq_tb_clazz; 那么他就会按以上方式
创建sequence




可以通过 
select sequence_name,min_value,max_value,increment_by,last_number from user_sequences；
来查看你在数据库里创建的sequence;


sequence 有两个属性  NEXTVAL 和 CURRVAL 
NEXTVAL 用于返回下一个可以用的sequence的值. 当他被使用时，每次都会返回一个唯一的值 
(即每个用户得到的返回值是不一样的)


CURRVAL获取当前的sequence的值
insert into tb_1(id,code) values(seq_tb_1.nextval,'jsd1311');


修改序列  一般不建议修改
alter swquence seq_tab_1 increment by 20 maxvalue 99999 nocache nocycle;


删除序列
drop sequence seq_tab_1;


-----同义字
通过创建同义字(对象的另一个名字) 简化访问对象的操作  使用同义字
你可以：
   使参考另一个用户拥有的表更加容易
    缩短对象名称长度
create [public] synonym sysonym_name;
select * from scott.emp;
create synonym tb_emp for scott.emp   为scott.emp创建同义词
select * from tb_emp;


------索引
索引是模式(schema)中的一个数据库对象 
在数据库里加速对表的查询
通过使用快捷路径访问方法  快速定位数据 减少磁盘的I/O
与表独立存在 但它自己不能独立存在 必须依附于表
由数据库自动维护 表被删除时   该表上的索引自动被删除




索引类似于书的目录  几乎没有一本书没有目录
因此几乎没有一张表没有索引




怎么创建索引：
自动：当表上定义了Primary key或者unique约束时候  数据库自动创建一个对应的索引 
手动：用户可以创建索引以加速查询




    在一列或者多列上创建索引
          create INDEX　index　on table (column[,column]);
例如   下面的索引 将会提高对EMPLOYEES表基于NAME字段的查询速度
  
create index index_emp_name on employees(name);
select * from tb_student where name="jack"  加快了搜索因为有索引


数据库的查询
1.全表扫描   非常慢
2.通过索引查询  通过索引页查询 
  索引页只有两个字段  
一个是：索引的名字字段(索引名)
另一个是:Location   它是磁盘的内存地址
当SQL语句发现有where语句 那么它先不进行全表查询
而是先查询有没有匹配的索引页  有则直接找个了磁盘地址
那么就更快找到了 


创建索引后  查询时候需要在where条件中带有索引字段的列名才可以使用索引


在经常查询的字段上面建索引  不要在所有字段上面建立索引


因为索引是用来快速查询的 如果一张表很少select 而经常insert delete update 
 不建议建立索引 因为Oracle需要对索引要额外的维护


如果一张表 字段 很少 建议不要建立索引


索引由Oracle自动维护  索引使用久了会产生索引碎片
所以使用久了 建议删除 在重建
          
删除索引 drop index index_name;