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值1234,将返回ORA-00001违反唯
一约束
--不提供主键值,将返回"id不能为空"错误
insert into student_ning(name)
values('peter');
 
--删除表
drop table student_ning;
 
--2.创建非空约束:只能建立在列级
create table student_ning1(
id number primary key,
name varchar2(20) not null);
 
select constraint_name, constraint_type
from user_constraints
where table_name='STUDENT_NING1';
 
--要求此列上必须有值.错误的sql:
insert into student_ning1(id) values(1);
 
--3.创建唯一约束
--email的唯一约束建立在列级
--nickname的唯一约束建立在表级
create table student_ning1(
id number primary key,
name varchar2(20),
nickname varchar2(20),
email char(30) unique,
constraint stu_ning1_nickname_uk1 unique(nickname));
 
--唯一约束允许为空,但不允许重复.
 
--4.check约束
create table student_ning2(
id number primary key,
name varchar2(20),
sex char,
constraint stuning2_sex_ck check (sex in ('M','F')));
--正确的例子:
insert into student_ning2 values(1234,'peter','M');
--错误的例子:
insert into student_ning2 values(1235,'chris','A');