Oracle学习笔记（三）


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 9999 
col comm for 9999 
 
select * from emp; 
 
--创建约束条件 
--如果创建的约束条件没有命名,由系统自动命名的.SYS_Cn. 
--1.创建主键约束: 
--1)建表时创建,可以建立在列级或表级. 
--2)在建表后创建,建立在表级. 
 
例1. --以student_ning为例 
--创建主键约束,不命名,由系统命名,建立在列级. 
create table student_ning( 
id number primary key, 
name varchar2(20), 
email char(40), 
registtime date default sysdate); 
 
例2. 
--创建主键约束,自己命名,建立在表级 
--约束条件命名为:stuning1_id_pk 
CREATE TABLE student_ning1( 
id NUMBER, 
name VARCHAR2(20), 
CONSTRAINT stuning1_id_pk PRIMARY KEY(id)); 
 
例3. 
--建表后创建主键约束 
--约束条件名称自定义(stuning2_id_pk),最好有意义. 
create table student_ning2( 
id number, 
name varchar2(20) 
); 
alter table student_ning2 
add constraint stuning2_id_pk primary key (id); 
 
--查看主键约束条件 
select constraint_name, constraint_type 
from user_constraints 
where table_name='STUDENT_NING2'; 
 
--了解主键约束条件的作用. 
insert into student_ning(id, name) 
values(1234, 'peter'); 
--重复执行上一条语句,试图插入重复id