Oracle学习笔记（三）
过滤分组结果
select
from
where
group by
having
order by
--凡是对组函数过滤,使用having
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000;
where -> group by -> having
select deptno, count(*)
from emp
group by deptno
having count(*) >= 5
order by count(*);
非关联子查询
select sal from emp
where ename = 'FORD';
select ename from emp
where sal > 3000;
--子查询
select ename from emp
where sal >
(select sal from emp
where ename='SMITH');
select ename, sal
from emp
where sal = (select min(sal) from emp);
--如果子查询返回一个数据,可以使用=,>,<,>=,<=这些单行比较运算符.
--如果子查询返回多行数据,必须使用多行比较运算符:in
select ename, job
from emp
where deptno in
(select deptno from emp
where job = 'CLERK');
--如果子查询返回结果中有null值,且使用not in 运算符,则无结果.
select ename from emp
where empno not in
(select mgr from emp);
--整个emp表中薪水最高的人
select ename, sal
from emp
where sal = (select max(sal) from emp);
--每个部门薪水最高的人
select deptno, ename, sal
from emp
where (deptno, sal) in
(select deptno, max(sal)
from emp
group by deptno);
--比部门20的平均薪水高的部门
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) >
(select avg(sal) from emp
where deptno = 20) ;
关联子查询
--哪些员工的薪水比本部门的平均薪水高
select ename, sal, deptno
from emp outer
where sal > (select avg(sal) from emp
where deptno = outer.deptno);
select ename, job, deptno
from emp outer
where exists (select 'a' from emp
where mgr = outer.empno);
--哪些部门没有员工
select deptno, dname
from dept outer
where not exists (select 'x' from emp
where deptno = outer.deptno);
--集合操作
A = {1,2,3,4,5,6}
B = {2,4,6,8}
A union B = {1,2,3,4,5,6,8}
A union all B = {1,2,2,3,4,4,5,6,6,8}
A intersect B = {2,4,6}
A minus B = {1,3,5}
--union all
select worker.ename, manager.ename
from emp worker join emp manager
on worker.mgr = manager.empno
union all
select ename, 'boss'
from emp
where mgr is null;
--将sql中的union换成
--union all/intersect/minus试一下
select job from emp where deptno = 10
union
select job from emp where deptno = 20;
约束条件
主键:primary key, PK = unique + not null
外键:foreign key, FK
唯一:unique
非空:not null
检查:check
F:female / M:male
Major: 专业
--建表
create table student_ning(
id number primary key,
name varchar2(20),
email char(40),
registtime date default sysdate);
--新增记录
INSERT INTO student_ning(id,name,email,registtime)
VALUES(1,'peter','peter@123.com',sysdate);
--如果新增全部字段,可以省略字段名称
INSERT INTO student_ning VALUES (3,'chris','chris@1.com',sysdate);
--如果增加的不是全部字段,必须提供字段列表
INSERT INTO student_ning(id, name) VALUES(13,'bono');
--一次增加多条记录
--使用子查询代替values
insert into student_ning(id, name)
select empno, ename from emp
where deptno = 10;
--在sqlplus中调整格式,以列宽为例
--特别强调:sqlplus命令,非sql语句.
--将指定列明调整为10个字符长度
column 字符列名 format a10
column 数字列名 format 9999
column sal for 9999
column ename for a8
column job for a9
column mgr for 999