数据库学习笔记day04
--row_number()over(partition by xxx order by xxx)分组排序函数 特点:组内连续且唯一
select ename,sal,deptno,row_number() over(partition by deptno order by sal desc)rn_num from emp;
select ename,sal,deptno,row_number() over(partition by deptno order by sal desc)rn_num from emp;
--rank()over(partition by xxx order by xxx)分组排序函数 特点 组内不连续且不唯一
select ename,sal,deptno,rank()over(partition by deptno order by sal desc)rn_num2 from emp;
--dense_rank()over(partition by xxx order by xxx)分组排序函数 特点:组内连续不唯一
select ename,sal,deptno,dense_rank()over(partition by deptno order by sal desc)rn_num3 from emp;
select ename,sal,deptno,rank()over(partition by deptno order by sal desc)rn_num2 from emp;
--dense_rank()over(partition by xxx order by xxx)分组排序函数 特点:组内连续不唯一
select ename,sal,deptno,dense_rank()over(partition by deptno order by sal desc)rn_num3 from emp;
--并集 union:去掉重复的部分
select ename,job,sal,deptno from emp where sal>2500
union
select ename,job,sal,deptno from emp where deptno=20;
--union all:不会去掉重复的部分
select ename,job,sal,deptno from emp where sal>2500
union all
select ename,job,sal,deptno from emp where deptno=20;
--交集 intersect
select ename,job,sal,deptno from emp where sal>2500
intersect
select ename,job,sal,deptno from emp where deptno=20;
--差集minus:我有你没有
select ename,job,sal,deptno from emp where sal>2500
minus
select ename,job,sal,deptno from emp where deptno=20;
select ename,job,sal,deptno from emp where sal>2500
union
select ename,job,sal,deptno from emp where deptno=20;
--union all:不会去掉重复的部分
select ename,job,sal,deptno from emp where sal>2500
union all
select ename,job,sal,deptno from emp where deptno=20;
--交集 intersect
select ename,job,sal,deptno from emp where sal>2500
intersect
select ename,job,sal,deptno from emp where deptno=20;
--差集minus:我有你没有
select ename,job,sal,deptno from emp where sal>2500
minus
select ename,job,sal,deptno from emp where deptno=20;
--创建视图 create table name xxx as
--与表同级 只是select语句的结果集
create view emp_10 as
select * from emp where deptno=10;
--与表同级 只是select语句的结果集
create view emp_10 as
select * from emp where deptno=10;
select * from emp_10;
drop view emp_10;
drop view emp_10;
--将emp表中的miller的薪资改为1500,视图emp_10的薪资会不会发生变化
update emp set sal=1500 where ename='miller'; --都为1500了
commit;
--将emp_10 clark的薪资改为2500,看表emp中clark的薪资有无变化
update emp_10 set sal=2500 where ename='clark';
select * from emp where ename='clark'; --都为2500了
update emp set sal=1500 where ename='miller'; --都为1500了
commit;
--将emp_10 clark的薪资改为2500,看表emp中clark的薪资有无变化
update emp_10 set sal=2500 where ename='clark';
select * from emp where ename='clark'; --都为2500了
--通过视图将james的薪资改为6000
--不能更改视图中没有的数据
create view emp_james as
select * from emp where ename='james';
select * from emp_james;
update emp_james set sal=6000 where ename='james';
--不能更改视图中没有的数据
create view emp_james as
select * from emp where ename='james';
select * from emp_james;
update emp_james set sal=6000 where ename='james';
--连接视图
create view name_loc as
select ename,loc from emp join dept on emp.deptno=dept.deptno;
select * from name_loc;
create view name_loc as
select ename,loc from emp join dept on emp.deptno=dept.deptno;
select * from name_loc;
--数据污染:通过视图插入影响到了基表(视图无影响)
insert into emp_10 values(1000,'tom','player','7934',to_date('2008-08-08','yyyy-mm-dd'),8000,1,10);
--查看视图时会显示这条信息,emp表也有∧
insert into emp_10 values(1001,'jerry','player','7934',to_date('2007-12-31','yyyy-mm-dd'),7000,1,20);
--查看视图时无这条,但emp表中会有,这是数据污染∧
--避免数据污染
drop view emp_10;
create view emp_10 as
select * from emp where deptno=10 WITH CHECK OPTION;
insert into emp_10 values(1000,'tom','player','7934',to_date('2008-08-08','yyyy-mm-dd'),8000,1,10);
insert into emp_10 values(1001,'jerry','player','7934',to_date('2007-12-31','yyyy-mm-dd'),7000,1,20); -- (这行会报错)
--创建序列开始位置为1,步幅为1
--创建序列后要先执行一次nextval
--序列是数据库内部维护的一个对象,可以自增自减得一个值
create sequence emp_seq start with 1 increment by 1; --给emp添加序列
select emp_seq.nextval from dual;
select emp_seq.currval from dual;
insert into emp_10 values(1000,'tom','player','7934',to_date('2008-08-08','yyyy-mm-dd'),8000,1,10);
--查看视图时会显示这条信息,emp表也有∧
insert into emp_10 values(1001,'jerry','player','7934',to_date('2007-12-31','yyyy-mm-dd'),7000,1,20);
--查看视图时无这条,但emp表中会有,这是数据污染∧
--避免数据污染
drop view emp_10;
create view emp_10 as
select * from emp where deptno=10 WITH CHECK OPTION;
insert into emp_10 values(1000,'tom','player','7934',to_date('2008-08-08','yyyy-mm-dd'),8000,1,10);
insert into emp_10 values(1001,'jerry','player','7934',to_date('2007-12-31','yyyy-mm-dd'),7000,1,20); -- (这行会报错)
--创建序列开始位置为1,步幅为1
--创建序列后要先执行一次nextval
--序列是数据库内部维护的一个对象,可以自增自减得一个值
create sequence emp_seq start with 1 increment by 1; --给emp添加序列
select emp_seq.nextval from dual;
select emp_seq.currval from dual;
create table asd(
id number(4),
name varchar2(20),
age number(2),
adress varchar2(100)
);
insert into asd values(emp_seq.nextval,'cdd',20,'2#101');
insert into asd values(emp_seq.nextval,'cmm',20,'2#102');
select * from asd;
--索引 为了提高查询效率而创建,它是独立于表的结构,一旦创建则由oracle自动维护。
create index emp_ename on emp(ename);
drop index emp_ename;
select ename from emp;
--复合索引
create index emp_ename_sal on emp(ename,sal);
drop index emp_ename_sal;
select ename,sal from emp;
--重建索引
alter index emp_ename_sal rebuild;
--非空约束创建表
create table emp_nn(
id number(4) not null,
name varchar2(20) unique, ---唯一约束①
tel varchar2(20) constraint nn not null,
name2 varchar2(20),
CONSTRAINT emp_nn_name2_uk UNIQUE(name2)--唯一约束②
);
insert into emp_nn values(emp_seq.nextval,'卢法斯','13233333333','Li');
--insert into emp_nn values(emp_seq.nextval,'龙马','13233333333','Li'); 错误实例
select * from emp_nn;
drop table emp_nn;
id number(4),
name varchar2(20),
age number(2),
adress varchar2(100)
);
insert into asd values(emp_seq.nextval,'cdd',20,'2#101');
insert into asd values(emp_seq.nextval,'cmm',20,'2#102');
select * from asd;
--索引 为了提高查询效率而创建,它是独立于表的结构,一旦创建则由oracle自动维护。
create index emp_ename on emp(ename);
drop index emp_ename;
select ename from emp;
--复合索引
create index emp_ename_sal on emp(ename,sal);
drop index emp_ename_sal;
select ename,sal from emp;
--重建索引
alter index emp_ename_sal rebuild;
--非空约束创建表
create table emp_nn(
id number(4) not null,
name varchar2(20) unique, ---唯一约束①
tel varchar2(20) constraint nn not null,
name2 varchar2(20),
CONSTRAINT emp_nn_name2_uk UNIQUE(name2)--唯一约束②
);
insert into emp_nn values(emp_seq.nextval,'卢法斯','13233333333','Li');
--insert into emp_nn values(emp_seq.nextval,'龙马','13233333333','Li'); 错误实例
select * from emp_nn;
drop table emp_nn;
--主键
create table mainkey(
id number(2,0),
name varchar2(14 byte),
loc varchar2(13)
);
create table mainkey(
id number(2,0),
name varchar2(14 byte),
loc varchar2(13)
);