Oracle学习笔记2

1》多表查询：

 select * from scott.dept;
 select * from scott.salgrade;

  select  * from scott.emp,scott.dept;
dept表的每一条记录，都会与emp表的每一条记录进行匹配；

笛卡尔积，在多表查询的时候，如果不带任何条件，则会出现笛卡尔积，怎么避免：
多表查询的条件至少不能少于表的个数减1；

例题：
1.显示各个员工的姓名，工资，及其工资的级别；
2.显示雇员名，工资，及所在部门的名字，并按部门排序。

3.显示各个员工的姓名，及其领导姓名 (自连接,把emp表看做两张表，一张是雇员表worker，一张是上级表boss)

 自连接：
 select worker.ename,boss.ename  from scott.emp worker,scott.emp boss where worker.mgr=boss.empno

 左外连接
 select worker.ename,boss.ename  from scott.emp worker,scott.emp boss where worker.mgr=boss.empno(+);
 右外连接
 select worker.ename,boss.ename  from scott.emp worker,scott.emp boss where worker.mgr(+)=boss.empno
2》

在多表查询时，什么时候加表名：如果两个表的列同名，则需要加表名区分，
否则可以不加，建议加表名。
注意： 建议在进行多表查询时，使用别名

3》
select distinct job from emp where deptno=10;
all操作符的使用：
select * from emp where sal > all(select sal from emp where deptno=30);
any操作符：
select * from emp where sal > any(select sal from emp where deptno=30);

4》多列子查询：
查询与SMITH的部门和岗位完全相同的所有雇员：

select * from emp where(deptno,job)=(select deptno,job from
emp where ename='SMITH');

5》在from子句中使用子查询 (把结果当做临时表) (重要的知识点)

如何显示高于自己部门平均工资的员工信息?
思路：
1.查询出各个部门的平均工资
select avg(sal),deptno from scott.emp group by deptno;
2.把上面查询的结果当做一个临时表来对待
select t1.deptno,t1.ename,t1.sal,t2.myavg from scott.emp t1,(select avg(sal) myavg,deptno from scott.emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal>t2.myavg;

每个部门工资最高的人的详细信息？
思路：
1.select max(sal),deptno from scott.emp group by deptno;
2.select t1.ename,t1.sal,t1.deptno,t2.maxsal from scott.emp t1,(select max(sal) maxsal,deptno from scott.emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal=t2.maxsal;

显示各个部门的信息（编号，名称）和人员数量?
select count(*) pnum,deptno from scott.emp group by deptno;

select d.deptno,d.dname,p.pnum from scott.dept d,(select count(*) pnum,deptno from scott.emp group by deptno) p where d.deptno=p.deptno;

6》分页查询：
mysql：
select * from 表名 where 条件 limit 从第几条取,取几条;
sql server:
select top 10 * from 表名 where id not in (select top 10 id from 表名 where 条件)
排除前10条，再取10条，实际上取出11-20条；
oralce：
select t2.* from (select t1.*,rownum rn from (select * from scott.emp) t1 where rownum<=6) t2 where rn>=4;

测试效率：
模拟一个40w条数据的表
create table mytest as select empno,ename,sal,comm,deptno from scott.emp;
自我复制
insert into mytest(empno,ename,sal,comm,deptno) select empno,ename,sal,comm,deptno from mytest;
分页测试：
select t2.* from (select t1.*,rownum rn from (select * from mytest) t1 where rownum<=6) t2 where rn>=4;
查看SCOTT 用户有哪些表
select table_name from dba_tables where owner='SCOTT';

7》合并查询
1.union取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行。
2.union all 与union相似，但不会取消重复行，而且不会排序
3.intersect取得交集
4.minus取得差集，显示存在第一个集合中的数据，不显示第二个集合中的数据。

cube函数：
select avg(sal),deptno,job from scott.emp group by cube(deptno,job);

8》内连接和外连接
1.内连接select 列名 from 表1 inner join 表2 on 条件
特点：只有两张表同时匹配，才被选择，显示出来。
2.外连接分三种：左外连，右外连，完全外连
create table stu(id number,name varchar2(33);
insert into stu values(1,'jack');
insert into stu values(2,'tom');
insert into stu values(3,'kity');
insert into stu values(4,'nono');

create table exam(id number,grade number);
insert into exam values(1,58);
insert into exam values(2,78);
insert into exam values(11,87);

1--左外连接：左边表完全显示，右边按条件匹配显示
select stu.name,stu.id,exam.grade from stu left join exam on stu.id=exam.id;
或者：
select stu.name,stu.id,exam.grade from stu,exam where stu.id=exam.id(+);
2--右外连接：右边表完全显示，左边按条件匹配显示
select stu.name,stu.id,exam.grade from stu right join exam on stu.id=exam.id;
或者：
select stu.name,stu.id,exam.grade from stu,exam where stu.id(+)=exam.id;
3--完全连接：左右两边都完全显示。

 

 

9》序列：
序列的创建：
create sequence myseq  --myseq表示序列名
start with 1
increment by 1
minvalue 1
maxvalue 30000
cycle      --若不希望循环用nocycle
nocache  --cache 10 ：表示一次产生10个号共你使用；使用缓存产生号，优点提高效率，缺点可能产生跳号。

序列的使用：
create table test1 (id numbe primary key,name varchar2(33));
insert into test1 values(myseq.nextval,'abc'); --nextval是关键字

system用户可以使用scott用户的序列。从上次使用的地方接着增长

查看当前的序列号：
若刚创建的序列，要先使用
select 序列名.nextval from dual; --再使用，每次使用后序列值都会增加，相应的步长
select 序列名.currval from dual;

删除序列： drop sequence myseq;