oracle简单笔记学习2

select from where group by haing order by
先看一下表的大小： select count(*) from table _name;
min /max/avg/sum/count忽略空值
group by
having
部门的平均工资大于2000的
select deptno,avg(sal) from emp group by deptno
having avg(sal)>2000;
---------|凡是对组函数过滤分组以后使用的是having
select deptno,count(*)
from emp
group by deptno
having count(*)>=5
order by count(*);
where-->group by-->having
关联查询
内连接 t1 join t2 on t1.c1=t2.c2
外连接 t1 left/right/full outer join t2
on t1.c1=t2.c2

子查询 (非关联子查询) //内表的查询的次数为整个的查询依据
select ename from emp where sal>
(select sal from emp where ename='FORD');
select *from emp
where sal=(select min(sal) from emp);
如果子查询，返回一个数据。可以使用＝><＝这些单行比较运算符号
如果子查询返回多行数据，必须使用多行比较运算符号：in
------------------------>把每一个部门下的clerk都列出来的人都列出来
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) //无结果

每个部门最多薪水的人
select deptno,ename,sal
from emp
where (deptno,sal) in
(select deptno,max(sal) from emp group by deptno);
多行与多行匹配的时候where以后要与子查询select后面的匹配相同

对分组结果进一步过滤
----比部门20的平均薪水高的是
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>
(select avg(sal) from emp where deptno=20);

关联子查询
loop的方式
求那些员工比本部门的平均工资要高
select deptno,ename,sal
from emp o
where sal>(select avg(sal) from emp where deptno=o.deptno)
---group by deptno;

exists ------>只判断是否有下属， 遍历完外层为止，取决于外表的记录条数
查询出所有有下属 的员工（是其他人的经理）
select ename,job,deptno
from emp outer
where exists(select 'x'from emp where mgr=outer.empno);
exists如果outer.empno每一个去遍历mgr，一旦存在，就return返回true

not exists
查询出没有员工的部门
select deptno,dname
from dept outer --外层主查询中的表，，从外层开始循环，判断外层在内层中是否存在
where not exists(select 'X' from emp where deptno=outer.deptno);
not exists如果outer.deptno的每一个被emp中的deptno去遍历，返回false的时候，就存在结果集中

集合操作
union 去掉重复的值的 /union all 不去掉重复的数值 并
intersect 交集
minus 差
A＝｛1，2，3，4，5，6，7｝
B＝｛2，4，5，6｝
A union B={1,2,3,4,5,6,7}
A union all B={1,2,2,3,4,4,5,5,6,6,7,}
A intersect B={2,4,5,6} //交集合
A minus B={1,3,6,7}
数据类型和个数都是相同的

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;

select job from emp where deptno=10;
select job from emp where deptno=20;

约束条件。
主键primary key,,,PK unique+not null
外键foreign key .,,,FK --------------->描述两个表之间的关系
唯一:unique
非空:not null
检查:check

建表
---------------create table
create table student_sun(
id number primary key,
name varchar2(20),
email char(40),
registtime date default sysdate
)
varchar2(size)变长的

insert into student_sun
(id,name,email,registtime)
values
(001,'sunzongbao','sunzongbao@sina.com',to_date('2000-12-12','yyyy-mm-dd'));
===insert into student_sun values(2,'chris','chris@1.com',sysdate);
如果增加的不是全部的字段，必须提供字段列表
insert into student_ning(id,name) values(3,'zhangbuji');

char如果不写的话就一个字节，varchar2按照字符的长度去存，varchar是按照指定的长度
Number(4,3)总共4位，小数点后面有3位
Number(2,4)有效数字是2位，小数点后要有4位 0.0011;

------------>一次增加多条语句
insert into student_sun(id,name)
select empno,ename from emp
where deptno=20;

在sqlplus中调整格式
以列宽为例子
------将指定列名调整为指定的10个字符长度(a)
column 列名 format a10
column name format a15
col email for a15

--创建主键约束
－－－1）建表时创建，可以建立在列级或表级
constraint
例1.----yi student_sun为例
create table student_sun(
id number primary key,
name varchar2(20),
email char(40),
registtime date default sysdate
)
1) select constraint_name,constraint_type
from user_constraints
where table_name='STUDENT_SUN'; --保存当前信息不能修改的表
2)创建主键约束，自己命名，建立在表级
create table student_sun1(
id number,
name varchar2(20),
CONSTRAINT student_sun1_id_pk primary key(id)
); --student_sun1_id_pk是约束条件的名字
3)--建表后创建主键约束
--->约束条件名称自定义(studentsun2_id_pk),最好有意义
create table student_sun2(
id number,
name varchar2(20)
);
alter table student_sun2 add constraint studentsun2_id_pk primary key(id);
--------->了解主键约束条件的作用
insert into student_sun(id,name) values (1,'sun');
--重复执行上一条语句ORA-00001违反唯一约束
--不提供主键值，将返回"id不能为空"错误
insert into student_sun(name) values('pert');

--删除表->
drop table student_sun2;
drop table student_sun1;
create table student_sun1(
id number primary key,
name varchar2(20) not null
);
--要求此列上必须有值,错误的sql
insert into student_sun1(id) values(1);
-->创建唯一约束
---email 建立在列级
--nickname 建立在表级别
create table student_sun1(
id number primary key,
name varchar2(20),
nickname varchar2(20),
email char(30) unique,
constraint student_sun1_nickname_uk unique(nickname)
);
insert into student_sun1(id,name,nickname,email)
values
(1234,'perter','monkeystar','s');
----唯一约束允许为空，但不允许重复
------4 check约束
create table student_sun2(
id number primary key,
name varchar2(20),
sex char,
constraint student_sun2_sex_ck check (sex in('M','F'))
);
insert into student_sun2 values(1234,'dsad','M');
insert into student_sun2 values(1234,'dsad','A'); --错误