初学 oracle 代码

--表已创建

create table vendor_master
(
   vencode varchar2(5),
   venname varchar2(20),
   venadd1 varchar2(20),
   venadd2 varchar2(20),
   venadd3 varchar2(20)
);

--表已更改
alter table vendor_master modify vencode varchar2(10);

--表结构描述
describe vendor_master;

--在表中添加新列
alter table vendor_master add venadd4 varchar2(20);

--在表中添加多个新列
alter table vendor_master add (venadd4 varchar2(20),venadd5 varchar(20));


--在表中删除新列
alter table vendor_master drop column venadd4;

--在表中删除多个列
alter table vendor_master drop (venadd4,venadd5);

--表已截掉
truncate table vendor_master;


--设置vendor_master表中venname字段不能为空
alter table vendor_master modify venname not null;


--设置vendor_master表中venname字段能为空
alter table vendor_master modify venname null;


--怎样给vendor_master表中vencode字段加上主键？
alter table vendor_master add constraint pk_vendor_master_vencode primary key(vencode);

alter table vendor_master drop constraint pk_vendor_master_vencode;

--查看用户自定义的约束
select * from user_constraints;

select * from user_constraints where constraint_name=upper('pk_vendor_master_vencode');

select * from user_constraints where owner=upper('scott');

select * from user_constraints where table_name=upper('vendor_master');


alter table vendor_master add constraint uq_vendor_master_venname unique(venname);

alter table vendor_master add age int;

alter table vendor_master add constraint ck_vendor_master_age check(age>10 and age<20);

alter table vendor_master modify age default(15);

--创建外键

create table student
(
stuid int primary key,
stuname varchar2(20) unique,
stuage int check(stuage>=10 and stuage<=30)
);


create table borrow
(
  borrowid int not null,
  stuid int,
  borrowdate date
);

alter table borrow add constraint fk_borrow_stuid foreign key(stuid) references student(stuid);

alter table borrow add constraint fk_borrow_stuid foreign key(stuid) references student(stuid) on delete cascade;

alter table borrow drop constraint fk_borrow_stuid;

--利用现有表创建新表
create table emp2 as select * from emp;


alter session set nls_date_format='yyyy-mm-dd'
select sysdate from dual;

alter session set nls_date_language='AMERICAN';
alter session set nls_date_language='simplified chinese';
select date'2005-10-25' from dual;

--insert 插入日期数据是要注意格式匹配 如：
insert into borrow values(1,1,'19-3月-07');


--也可以按自己指定的格式插入日期 如：
insert into borrow values(2,2,to_date('2007-3-19','yyyy-mm-dd'));



--从其他表中选择数据插入到存在的emp3表中去。
insert into emp3 select * from emp

--左连接
select * from emp e,dept d where e.deptno=d.deptno(+)
select * from emp e left join dept d on e.deptno=d.deptno;
--右连接
select * from emp e,dept d where e.deptno(+)=d.deptno
select * from emp e right join dept d on e.deptno=d.deptno;

select * from emp e full join dept d on d.deptno=e.deptno;

--事务保存点
savepoint save1;
delete from emp3 where empno='7369';
savepoint save2;
delete from emp3 where empno='7499';
select * from emp3;
rollback to savepoint save2;
select * from emp3;
rollback to savepoint save1;
select * from emp3;



--数据控制语言
--创建用户
conn sys/quan as sysdba;
create user AAA
identified by AAA;

--更改用户的密码
conn sys/quan as sysdba;
alter user AAA
identified by BBB;

--解除用户锁定状态
alter user AAA account unlock
--恢复用户锁定状态
alter user AAA account lock

grant create session,resource to AAA;

conn scott/tiger;
grant select on emp3 to AAA;

CONN AAA/AAA;

select * from scott.emp3;


--日期函数:(请把注释写出)
--给指定的日期加上月份
select hiredate,add_months(hiredate,2) from emp;

--给指定的日期减上月份
select hiredate,add_months(hiredate,-2) from emp;



--add_months(hiredate,2)
select hiredate,add_months(hiredate,2),months_between(add_months(hiredate,2),hiredate) from emp;

select hiredate,add_months(hiredate,2),months_between(hiredate,add_months(hiredate,2)) from emp;


select hiredate,last_day(hiredate) from emp;


select hiredate,round(hiredate,'year') year,round(hiredate,'month') month,round(hiredate,'day') day from emp;


select hiredate,next_day(hiredate,'星期一') as 下个星期一 from emp;

select sysdate,next_day(sysdate,'星期一') 星期一 from emp;


select hiredate,trunc(hiredate,'year') as year from emp;

select hiredate,trunc(hiredate,'month') as month from emp;

select hiredate,trunc(hiredate,'day') as day from emp;

select trunc(sysdate,'year') from emp;

select trunc(sysdate,'month') from dual;


select extract(year from sysdate) from dual;
select to_char(sysdate,'yy') from dual;

select extract(month from sysdate) from dual;
select to_char(sysdate,'mm') from dual;

select extract(day from sysdate) from dual;
select to_char(sysdate,'dd') from dual;
select to_char(sysdate,'HH24') from dual;

select extract(hour from timestamp '2005-10-10 10:11:12') from dual;
select to_char(sysdate,'hh') from dual;

select extract(minute from timestamp '2005-10-11 10:11:12') from dual;
select to_char(sysdate,'mi') from dual;

select extract(second from timestamp '2005-10-11 10:11:12') from dual;
select to_char(sysdate,'ss') from dual;


--字符函数
select initcap('hello') from dual;
select initcap('how are you') from dual;

select lower('HOW ARE YOU') from dual;

select upper('how are you') from dual;

select ltrim('    how are you   ') from dual;
select ltrim('xyzadams','xyz') from dual;

select rtrim('xyzadams','ams') from dual;
select rtrim('     xyzadams','ams') from dual;

--这个函数是:用第三个参数按位置代替第二个参数,然后用代替好的字符去取代第一个参数相对应的字符
select translate('jack','abcd','1234') from dual;
select translate('abc','ac','fn') from dual;


select replace('jack and jue','j','bl') from dual;

select instr('worldwide','d') from dual;
select instr('how are you','o') from dual;
--从指定的位置开始查找
select instr('how are you','o',3) from dual;

select substr('abcdefg',3,2) from dual;

select concat('hello',' world') from dual;


--一些其他字符函数:
select chr(65) from dual;

select lpad('how are you',20,'*') from dual;
select lpad('how',3,'*') from dual

select rpad('how are you',20,'&') from dual;


select trim('   how are you      ') from dual;
--去掉前面的9
select trim(leading 9 from 999978888876699999) from dual;

select trim(leading 'a' from 'aaaaaadgereraafdfeghrtraaaaaaaaaaaa') from dual;

--去掉后面的9
select trim(trailing 9 from 999978889899876699999) from dual

select trim(trailing 'a' from 'aaaaaadgereraafdfeghrtraaaaaaaaaaaa') from dual;

--不用leading也不用trailing的话就把前后的9都去掉
select trim(9 from 999877699999) from dual;
select trim('a' from 'aaaaaeeeaaabbbeertaaaaa') from dual;

--区分大小写
select trim('a' from 'AAAAAAAAbbbbbaderAAAAa') from dual;


select length('aaaaaaaaaaa') from dual;


--返回字符串所占空间的字节数
select vsize('abcde') from dual;


select empno,deptno,decode(deptno,20,'ccc',10,'aaa') from emp;



--数学函数:
select abs(-9) from dual;
select abs(9) from dual;


select ceil(44.99) from dual;
select ceil(44.11) from dual;


select ceil(-44.99) from dual;
select ceil(-44.11) from dual;


select floor(44.11) from dual;
select floor(44.99) from dual;

select floor(-44.11) from dual;
select floor(-44.99) from dual;


select power(2,3) from dual;
select power(3.3,3) from dual;
select power(3.3,3.2) from dual;

select round(100.23456,3) from dual;

select trunc(100.2345,3) from dual;

select sqrt(9) from dual;



--转换函数
--使用fm可以避免空格和数字0填充
select to_char(sysdate,'yyyy"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM DUAL;
--24小时算法
select to_char(sysdate,'yyyy"年"MM"月"DD"日" HH24:MI:SS') FROM DUAL;
--12小时算法
select to_char(sysdate,'yyyy"年"MM"月"DD"日" HH:MI:SS') FROM DUAL;

select to_char(sal,'C999999') from emp;
select to_char(sal,'u999999') from emp;
select to_char(sal,'u0000000000') from emp;


select to_date('2005-12-06','yyyy-mm-dd') from dual;
select to_date('12-06-2005','dd-mm-yyyy') from dual;
select to_date('12-06-2005','mm-dd-yyyy') from dual;
select to_date('2005/12/06','yyyy/mm/dd') from dual;


select to_number('345678') from dual;



--其他函数：

select comm,nvl(comm,0) from emp;

select comm,nvl2(comm,comm,0) from emp;
select comm,nvl2(comm,1111,0000) from emp;


select nullif(12,12) from dual;
select nullif(12,23) from dual;
select nullif(23,12) from dual;



--集合操作符:
--测试表
create table test1
(
testid varchar2(20)
);
insert into test1 values('a001');
insert into test1 values('a002');
insert into test1 values('a003');
insert into test1 values('a004');
insert into test1 values('a005');

create table test2
(
testno varchar2(20)
);
insert into test2 values('a004');
insert into test2 values('a005');
insert into test2 values('a006');
insert into test2 values('a007');
insert into test2 values('a008');
insert into test2 values('a009');

select testid from test1
union
select testno from test2;


select testid from test1
union all
select testno from test2;

select testid from test1
intersect
select testno from test2;

select testid from test1
minus
select testno from test2;


--分析函数:
select ename,job,deptno,sal,row_number() over(order by sal desc) as sal_rank from emp;
select ename,job,deptno,sal,row_number() over(order by sal asc)as sal_rank from emp;
select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as sal_rank from emp;
select ename,job,deptno,sal,comm,row_number() over(partition by deptno order by sal desc,comm) from emp;

select ename,job,deptno,sal,comm,row_number() over(partition by deptno order by sal desc) from emp;
select ename,job,deptno,sal,comm,row_number() over(partition by deptno order by sal desc,comm desc) from emp;

select empno,ename,job,sal,rank() over(order by sal desc) from emp;

select empno,ename,job,sal,comm,deptno,rank() over(partition by deptno order by sal desc) from emp;
select empno,ename,job,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm) from emp;
select empno,ename,job,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm desc) from emp;

select empno,ename,job,sal,comm,deptno,dense_rank() over(order by sal) from emp;
select empno,ename,job,sal,comm,deptno,dense_rank() over(partition by deptno order by sal desc) from emp;
select empno,ename,job,sal,comm,deptno,dense_rank() over(partition by deptno order by sal desc,comm asc) from emp;


select empno,ename,sal,comm,e.deptno,dname,dense_rank() over(partition by e.deptno order by sal
) denserank from emp e,dept d where e.deptno=d.deptno;


select deptno,sum(sal) from emp group by rollup(deptno);
select deptno,job,sum(sal) from emp group by rollup(deptno,job);

select deptno,sum(sal) from emp group by cube(deptno);
select deptno,job,sum(sal) from emp group by cube(deptno,job);




--rollup函数的使用
--示例表是emp;
select to_char(deptno),sum(sal) sumsal from emp group by deptno
union all
select '合计' as deptno,sum(sal) from emp;


--rollup的使用
select deptno,sum(sal) from emp group by rollup(deptno);

select nvl(to_char(deptno),'合计') deptno,sum(sal) sumsal from emp group by rollup(deptno);

select deptno,job,sum(sal) sumsal from emp group by rollup(deptno,job);

--系统只是根据rollup的第一个参数deptno对结果集的数据做了汇总处理,
--而没有对job做汇总分析处理,cube函数就是为了这个而设计的.

select nvl(to_char(deptno),'总合计') deptno,nvl(to_char(job),'合计') job,sum(sal) sumsal from emp
group by rollup(deptno,job);

select job,sum(sal) from emp group by job;


--cube的使用
--可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.
--这就是cube函数根据job做的汇总统计结果
select nvl(to_char(deptno),'总合计') deptno,nvl(to_char(job),'合计') job,sum(sal) sumsal from emp
group by cube(deptno,job);


--从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
--我们如何来区分到底是根据那个字段做的汇总呢,
--这时候,oracle的grouping函数就粉墨登场了.
--如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0

select decode(grouping(deptno),1,'all deptno',0,to_char(deptno)) deptno,
decode(grouping(job),1,'all job',0,to_char(job)) job,sum(sal) sumsal
from emp group by cube(deptno,job) order by deptno,job nulls last;



select mgr from emp order by mgr nulls last;
select mgr from emp order by mgr nulls first;