复杂查询
1、连接查询
a、内连接
特点：
两张表相匹配的显示，不匹配的不显示
格式一：
select 字段列表 from 表1,表2,……
where 表1.列=表2.列 and ……
格式二：
select 字段列表 from 表1 join 表2
on 表1.列=表2.列 join 表3
on……
例如：
select * from emp a,dept b
where a.deptno=b.deptno;
select * from emp join dept
on emp.deptno=dept.deptno;
显示在NEW YORK工作的员工的信息以及部门名称
select emp.* ,dname from emp,dept
where emp.deptno=dept.deptno and loc='NEW YORK';
select a.*,dname from emp a join dept b
on a.deptno=b.deptno and loc='NEW YORK';
----自连接（内连接的特殊形式）
显示跟SMITH在同一个部门的其他员工信息
select b.* from emp a,emp b
where a.ename='SMITH' and a.deptno=b.deptno
and b.ename!='SMITH'
b、外连接
1）左向外联接
特点：左表全部显示，右表只显示匹配的
格式1：
select 字段列表 from 表1,表2
where 表1.列=表2.列(+);
格式2：
select 字段列表 from 表1 left outer join 表2
on 表1.列=表2.列;
select * from emp,dept
where dept.deptno=emp.deptno(+);
select * from dept left outer join emp
on emp.deptno=dept.deptno;
2)右向外联接
特点：右表全部显示，左表只显示匹配的
格式1：
select 字段列表 from 表1,表2
where 表1.列(+)=表2.列;
格式2：
select 字段列表 from 表1 right outer join 表2
on 表1.列=表2.列;
select * from emp,dept
where emp.deptno(+)=dept.deptno;
select * from dept right outer join emp
on dept.deptno=emp.deptno;
3)完全外连接
特点：一张表全部显示，另一张表只显示匹配的
select 字段列表 from 表1 full join 表2
on 表1.列=表2.列;
例如：
select * from emp full join dept
on emp.deptno=dept.deptno;

复杂查询之二——嵌套查询（子查询）
分类1：
1）相关子查询
2）无关子查询
分类2：
1）用关系运算符实现子查询（> >= < <= == != <>）
例如：
显示跟SMITH在同一个部门的员工信息
select* from emp
where deptno=(select deptno from emp
where ename='SMITH')
and ename!='SMITH';
显示比所有SALESEMAN员工最高工资还高的员工信息
select * from emp
where sal>(select max(sal) from emp
where job='SALESMAN');
2)用in实现子查询
例如：显示在NEW YORK 和DALLAS工作的员工信息
select * from emp
where deptno in (select deptno from dept
where loc in('NEW YORK','DALLAS'));
例如：显示不在NEW YORK 和DALLAS工作的员工信息
select * from emp
where deptno not in (select deptno from dept
where loc in('NEW YORK','DALLAS'));
3)用exists实现子查询
select * from emp
where exists
(
select* from dept
where loc in('NEW YORK','DALLAS')
and emp.deptno=dept.deptno
)
4)用any/all实现子查询
显示比所有SALESEMAN员工最高工资还高的员工信息
select * from emp
where sal>all(select sal from emp
where job='SALESMAN');
显示比某个CLERK员工工资还要高的员工信息
select * from emp
where sal>ANY(select sal from emp
where job='CLERK')
and job!='CLERK';
查询每个部门最高工资的员工信息
方法一：
SELECT * FROM EMP A
WHERE (SELECT COUNT(*) FROM EMP B
WHERE A.DEPTNO=B.DEPTNO AND B.SAL>A.SAL)=0
方法二：
select * from emp a
where exists
(
select * from
(
select deptno,max(sal) maxSal from emp
group by deptno
)kk
where a.deptno=kk.deptno and a.sal=kk.maxSal
);
select * from emp;
集合操作
交(intersect)
查询部门10和部门20都有的工作类型
select job from emp
where deptno=10
intersect
select job from emp
where deptno=20;
并（union）
注意：如果用union,结果会去掉重复的数据
如果全部显示，用union all
查询部门10的办事员和部门20的经理
select * from emp
where deptno=10 and job='CLERK'
union
select * from emp
where deptno=20 and job='MANAGER';
查询部门10和部门20的员工工种显示
select job from emp
where deptno=10
union
select job from emp
where deptno=20;
差（minus）
查询部门30中有，而部门10中没有的工作类型
select job from emp
where deptno=30
minus
select job from emp
where deptno=10;
伪列：
select rowid,rownum,empno,ename,job sal
from emp
where rownum<4;
select rowid,rownum,empno,ename,job sal
from emp
where rownum<4 and empno not in
(
select empno from emp
where rownum<4
);
select * from emp;

表的操作：
1、插入（insert）
1)一次只插入一条记录
格式：
insert into 表名[(字段列表)]
values(值列表)
例如：
(1)
insert into emp
values(8000,'张三','保安',7902,'01-12月-13',1500,null,40);
(2)
insert into emp(empno,ename,job,sal,deptno)
values(8001,'李四','CLERK',2000,40);
(3)
insert into emp
values(8002,'王五','MANAGER',NULL,sysdate,5000,1000,40);
(4)
insert into emp
values(8003,'赵柳',NULL,NULL,to_date('20140101','yyyyMMdd'),3000,NULL,40);
(5)
做序列
--序列的创建
create sequence seq
start with 2014001 increment by 1
--序列的使用
select seq.nextval from dual;
--序列的删除
drop sequence seq;
create table student
(
sno number primary key,
sname varchar2(20) not null,
ssex char(3),
sage number,
sdept varchar2(20)
);
insert into student
values(seq.nextval,'李永','男',21,'计算机');
insert into student
values(seq.nextval,'刘晨','男',19,'计算机');
select * from student;
2)一次插入多条记录
格式：
insert into 表2[(字段列表)]
select 字段列表 from 表1
[where 条件……]
create table sale
(
sno number primary key,
sname varchar2(20) not null,
hiredate date,
sal number,
deptno number
);
insert into sale
select empno,ename,hiredate,sal,deptno from emp
where job='SALESMAN';
select * from sale;
2、修改（update）
格式：
update 表名 set 列1=值1,列2=值2,……
where 条件
(1)update emp set sal=sal+200;
(2)update emp set sal=sal+500
where job='CLERK';
(3)update emp set ename=replace(ename,'张','高')
where ename='张三';
select * from emp;
(4)update emp set sal=(select sal from emp
where ename='李四')
where ename='高三';
update emp set sal=sal+1000
where deptno =(select deptno from dept
where loc='NEW YORK');
3、删除（delete）
格式：
delete [from] 表名
where 条件
注意：删除数据时要先删除外键表，后删除主键表
例如：
在BOSTON的部门撤了，将所有跟他相关的信息删除
delete from emp
where deptno=(select deptno from dept
where loc='BOSTON')
delete from dept
where loc='BOSTON';
delete emp;
delete dept;
select * from emp;

1、创建表空间
Create tablespace 表空间名 datafile ‘d:\oracle\oradata\oral\my.dbf’ size100M;
2、用户管理
创建用户： create user xxx identified by 密码 default tablespace xx;
注意：default tablespace xx可以省略，如果省略，表空间由系统提供
修改用户：alter user xxx identified by 密码;
解锁： alteruser scott account unlock; 用户解锁
锁定： alter user scott sccount lock; 用户锁定
alter user scott account password expire;设置密码过期
删除用户： drop user xx cascade;
查看用户： select * from all_users 所有用户
dba_users 管理员用户
用户授权：
1）系统权限 授予连接、使用表空间等权限
grantconnect,resource to xxx;
2）对象权限 授予查询修改等权限
grant select onemp to tea;
grant all on empto tea;
管理权限 with grant option 看一下收回权限后，级联授权的能否收回
grant all on emp toxxx with grant option;
按照角色授予权限
撤销权限 revoke xx on表名 from 用户
3、表管理
创建表，讲解数据类型
Number(m,n) char(n) varchar2(n) date
a>创建新表
create table users(
userId number primarykey,
userName varchar2(10),
password varchar2(20),
addr varchar2(20));
添加字段
格式：altertable 表名add字段名类型;
alter table usersadd pubdate date;
desc users;-------查看表结构
alter table usersadd age number;
修改字段
格式：altertable 表名modify 字段名新类型;
alter table users modify userNamevarchar2(20);
insert into users values(32,'aa','aa','aa',sysdate,10);
删除字段
格式：altertable 表名drop column字段名;
alter table users drop password;
或者
alter table usersdrop column password
desc users;
给表添加约束
格式：altertable 表名add constraint 约束名约束;
或 alter table 表名add 约束;
alter table usersadd constraint ck check(age>10 and age<60)
alter table usersadd primary key(userId)
给表删除约束
格式：altertable 表名drop约束;
或 alter table 表名drop constraint 约束名;
alter table usersdrop primary key;
字段重新命名
altertable 表名 rename column 列名 to 新列名;
表重新命名
rename表名 to 新表名;
rename users totest;//将users表重新命名为test
删除表(即：表结构)
drop table 表名
droptable users;//删除表结构
truncatetable users //删除记录（数据），记录不可恢复，不写日志（快）
deletefrom emp //删除记录（数据），但可以恢复，写日志
b>利用现有的表创建表
createtable test2 as select * from usersinfo;
4、SQL简单查询
1）select 简单查询
Select * from emp;
Select empno,ename from emp;
Select sal+500 from emp;
2）select distinct查询
Select distinct job from emp;
3）给列或表达式取别名
Select empno as 编号,ename as 姓名 from emp;
注：as可以省略
4）带条件的查询
Select empno,ename,sal fromemp where sal>=1500;
Select empno,ename,sal fromemp
where sal between 1500 and 3000;
Select empno,ename,sal,jobfrom emp
Where jobin(ANALYST,PRESIDENT);
Select empno,ename,sal,jobfrom emp
Where job like ‘SAL%’;
Select empno,ename,sal,job,comm
Where comm is NULL;
5）排序查询
Select empno,ename,sal from emp order by saldesc;
6）分组查询
聚合函数
count() sum() avg() max() min()
例
统计每个部门工资在1400元以上的所有员工的工资总额
selectdeptno,sum(sal) from emp
where sal>1400
group by deptno
统计不同工作的个数
selectcount(distinct job) from emp
select job,avg(sal)from emp group by job;

7）having子句
统计部门最低工资大于900的部门和最低工资
selectdeptno,min(sal) from emp
group by deptno
havingmin(sal)>900
Selectjob,avg(sal) from emp group by job
havingavg(sal)>1500;
3、连接查询-----重点
（1）连接查询
内连接：两张表相匹配的显示，不匹配的不显示
格式:
Select 字段列表 from 表1,表2,……
Where 表1.列=表2.列 and……
或
Select字段列表 from 表1 join 表2
On表1.列=表2.列 join 表3
On……
1）、查询员工以及他所在的部门
select a.*,b.* from emp a,dept b wherea.deptno=b.deptno----（内连接）
或
select a.*,b.*from emp a join dept b on a.deptno=b.deptno----（内连接）
外连接：
左向外连接：左表全部显示，右表显示匹配的
右向外连接：右表全部显示，左表显示匹配的
完全外连接：一张表全部显示，另外一张表跟着匹配
左向外连接格式：
Select 字段列表 from 表1,表2,……
Where 表1.列=表2.列(+) and……
或
Select字段列表 from 表1 leftouter join 表2
On表1.列=表2.列 join 表3
On……
右向外连接格式：
Select 字段列表 from 表1,表2,……
Where 表1.列(+) =表2.列and……
或
Select字段列表 from 表1 rightouter join 表2
On表1.列=表2.列 join 表3
On……
完全外连接的格式：
Select字段列表 from 表1 full join表2
On表1.列=表2.列 join 表3
On……
2）、查询所有部门以及员工姓名
select a.ename,b.deptno,b.dname from empa,dept b where a.deptno(+)=b.deptno----（右外连接）
selecta.ename,b.deptno,b.dname from emp a,dept b where b.deptno=a.deptno(+)---（左外连接）
或者
select a.ename,b.deptno,b.dname from deptb left outer join emp a on b.deptno=a.deptno---（左外连接）
select a.ename,b.deptno,b.dname from emp aright outer join dept b on a.deptno=b.deptno----（右外连接）
3）、显示员工以及其直接上级
select a.ename 员工,b.ename 经理 from emp a,emp bwhere a.mgr=b.empno(+) ----(自连接)
5、子查询(嵌套查询)—重点
分类：
1)无关子查询
2)关联子查询
（1）使用关系运算符实现子查询
> >= < <= = !=
特点：子查询得到的结果只能为一个值
例如：查询工资高于平均工资的员工
select * from empwhere sal>(select avg(sal) from emp) ------（非关联子查询）
先执行子查询，后执行主查询，叫做非关联子查询
（2）使用In实现的子查询：值范围
特点：子查询得到的结果可以为多个值
（3）使用Exists实现子查询：存在与否
（4）使用any/all实现子查询
5）、查询每个部门最高工资的员工
select * from empa where (select count(*) from emp where deptno=a.deptno and sal>a.sal)=0---(关联子查询)
先执行主查询，后执行子查询，将主查询的当作已经查询出来的结果
也可非关联实现
select * from empwhere (deptno,sal) in (select deptno,min(sal) from emp group by deptno)
6、层次查询
1）、显示员工领导关系
selectlpad(ename,5*level,'+') from emp connect by prior empno=mgr start withename='KING'
或者start with mgr isnull
connect by 用于执行记录之间的父子关系，start with 用于指定从哪个节点记录开始遍历访问 Level 在整个查询记录中的层次
2）、查询BLAKE所领导团队工资总额
select sum(sal)from emp connect by prior empno=mgr start with ename='BLAKE'
7、集合运算
A集合 (1、2、3)
B集合 (2、3、4)
交集：(2、3)
并集：(1、2、3、2、3、4)或(1、2、3、4)
差集：A-B(1) B-A(4)
create table a(id number);//插入1，2，3
create table b(id number);//插入2，3,4
1、交集
select * from a intersect select * from b
2、并集
select * from a union all select * from b(去掉重复的记录则去掉all)
3、差集
select * from a minus select * from b (A-B)
select * from b minus select * from a (B-A)
1、查询部门10和部门20都有的工作类型
select job from emp where deptno=10intersect select job from emp where deptno=20;
2、查询部门10的办事员和部门20的经理
select * from emp where deptno=10 andjob='CLERK' union all select * from emp where deptno=20 and job='MANAGER'
3、查询部门30中有，而部门10中没有的工作类型
select job from emp where deptno=30 minusselect job from emp where deptno=10;
8、rowId,rownum伪列
完成分页
例如：显示前5条记录
selectuserId,userName,age from
(select rownum rn,a.* from users a) temp
where rn>0 and rn<6;
显示前6-12条记录
selectuserId,userName,age from
(select rownum rn,a.* from usersa) temp
where rn>5 andrn<11;
9、导入导出：
Cmd->exp或imp
Exp
Imp
导出：exp abc/123;
D:\a.dmp
导入：imp abc/123;
D:\a.dmp