oracle学习笔录

电玩女神 2022-09-28 05:45 192阅读 0赞

set serveroutput on  打开服务器屏幕显示信息  
set sqlblanklines on  编写SQL语句时，打开忽略空格功能

save   c:/sqlplus\_test01.txt    --保存  
edit    --打开记事本进行编辑  
get c:/sqlplus\_test01.txt    --把记事本中的信息调入到缓存中  
list    --显示缓存中的信息  
desc deptment   --显示部门表中的表定义\]构  
l2 4   --显示第二行到第四行的信息  
change  --修改缓存中的内容  
c/N/M  --把N改成M  
del  2 5  --删除2到5行的信息  
help  index   --帮助  
? set   --以set开头的关键字帮助信息  
col  ID format A10  heading '部门编号'     --把字段名ID显示为部门编号，字符类型，长度为10  
col bytes format 999,999,999     --把字段名bytes显示为指定的格式

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
set linesize 50  
ttitle center "我的标题"  skip 1-  
left "测试报表" right  "页" -  
format 999 sql.pno skip 2  
select \* from dept;  
     
\--输出结果  
                                 我的标题  
测试报表                                                            页  1  
   
   deptno           dname             log  
\-------------        ---------------       -----------  
      10               accounting       new york  
      20               research           dalias  
      30               sales                 chicago  
      40               operations        boston

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

ttitle off  --关闭标题  
select \* from dept;

\--输出结果  
   
   deptno           dname             log  
\-------------        ---------------       -----------  
      10               accounting       new york  
      20               research           dalias  
      30               sales                 chicago  
      40               operations        boston

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
select \* from deptment;

\--输出结果  
id             name  
\-------      -----------  
01          A部门  
02          A部门  
03          B部门  
04          B部门  
05          C部门

break on name    --把name字段去掉重复值

select \* from deptment;

\--输出结果  
id             name  
\-------      -----------  
01          A部门  
02          
03          B部门  
04           
05          C部门

comp count label "计数" of id on name   --对分组字段进行汇总  
select \* from deptment;

\--输出结果

id                 name  
\------------     ---------------  
01                A部门  
02                A部门  
\------------     \*\*\*\*\*\*\*\*\*\*\*\*\*  
            2     计数  
03               B部门  
04               B部门   
\------------     \*\*\*\*\*\*\*\*\*\*\*\*\*  
            2      计数  
05               C部门  
\------------     \*\*\*\*\*\*\*\*\*\*\*\*\*        
            1      计数

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
DDL    数据定义语言  
    1.create   
 create table abc(a varchar2(10),b char(10));  
    2.alter  
 alter table abc add c number;  
  alter table abc drop column c;  
    3.drop  
DCL   数据控制语言  
     1.grant   
 grant select on dept to tt;  
 conn tt/tt11  
 select \* from scott.dept;  
     2.revoke  
 revoke select  on dept from tt;  
 conn  tt/tt11  
 select \* from scott.dept  
DML  数据操作语言  
      1.select  
 select \* from abc;  
      2.insert  
 insert into abc(a,b) values('abc','xy');  
 insert into abc values('bcd','123');  
      3.delete  
 delete from abc where a = 'abc';  
      4.update  
 update abc set b='ttt' where a='abc';

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                                                    常用系统函数  
1、字符  
 lenth,ltrim,replace,rtrim,substr,trim

2、日期  
 sysdate,current\_date,next\_day  
3、转换  
 to\_char,to\_date,to\_number  
4、聚集函数  
 sum,avg,max,min,count  
5、其他  
 user,decode,nvl

select length('abcdef') from dual;

\--输出结果

length('abcded')  
\---------------------  
                         6

select length('abc好ef') from dual;

\--输出结果

length('abcded')  
\---------------------  
                         6

select lengthb('abc好ef') from dual;

\--输出结果

lengthb('abcded')  
\---------------------  
                         7

select ltrim('  abc好ef') from dual;

\--输出结果

ltrim('  
\-----------  
abc好ef

select rtrim('   abc好ef   ') from dual;

\--输出结果

rtrim('ab  
\----------  
   abc好ef

select trim('   abc好ef   ') from dual;

\--输出结果

trim('a  
\---------  
abc好ef

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

\--输出结果  
sub  
\-----  
bcd

select sysdate from dual;

\--输出结果

sysdate  
\---------------  
21-6月 -10

select current\_date from dual;

\--输出结果

current\_date  
\---------------  
21-6月 -10

alter session set nls\_date\_format='dd-mon-yyyy hh:mi:ss';  
select current\_date from dual;

\--输出结果

current\_date  
\-------------------  
21-6月 -2010  03：44：22

select next\_day(sysdate,'星期三') from dual;

\--输出结果

next\_day(sysdate,'星期三')  
\-------------------------------------  
23-6月 -2010  03:47:03

select to\_char(systdate,'yyyy-mm-dd hh:mi:ss') from dual;

\--输出结果

to\_char(sysdate,'yy  
\---------------------------  
2010-06-21  03:54:08

select to\_date('12-3月-10')  from dual;

\--输出结果

to\_date('12-3月-10)  
\---------------------------  
12-3月 -0010 12:00:00

select to\_number('00333')  from dual;

\--输出结果

to\_number('00333')  
\---------------------------  
                             333

select user from dual;

\--输出结果

user  
\--------------------------------------------  
li

select \* from e;

\--输出结果

eid             ename          sex  
\----------      ----------------  ------------  
001 赵1      男         
002 钱2      男   
003 孙3      男   
003 李4      女   
003 周5      女

select sum(decode(sex,'男',1,0))  男人数  ,sum(decode(sex,'女',1,0)  女人数  from e;

\--输出结果

男人数               女人数  
\------------           -----------  
              3                       2

select  \*  from  aa;

\--输出结果

a1               a2                a3  
\-------------   ---------------  -----------------  
abc xyz     aa  
bca dee     aa  
abc ttt                   aa  
cba                       aa  
bbb                       aa  
abc ggd               aa  
abc dfd                aa

select a1,nvl(a2,'未输入') from aa;

\--输出结果

a1               a2                a3  
\-------------   ---------------  -----------------  
abc xyz     aa  
bca dee     aa  
abc ttt                   aa  
cba 未输入          aa  
bbb 未输入          aa  
abc ggd               aa  
abc dfd                aa

select \* from aa where a2 is null;

\--输出结果

a1               a2                a3  
\-------------   ---------------  -----------------  
cba                       aa  
bbb                       aa

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\--游标的属性  
 %FOUND  
 %ISOPEN  
 %NOTFOUND  
 %ROWCOUNT

\--游标与记录集  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
declare  
cursor mycur is  
select \* from book;  
myrecord books%rowtype;  --定义记录集与books表类型大小一样  
begin  
open mycur;  
fetch mycur into myrecord;  
while mycur%fount loop  
dbms\_output.put\_line(myrecord.books\_id||','||myrecord.books\_name);  
fetch mycur into myrecord;  
end loop;  
close mycur;  
end;  
/  
\--输出结果：  
0001,中国文学  
0002,外国文学  
0003,英语阅读  
0004,建筑艺术  
0005,计算机入门  
0006,数值算法  
0007,C语言  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\--带参数的游标  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
declare  
cursor cur\_para(id varchar2) is  
select books\_name from books where book\_id=id;  
t\_name  books.books\_name%type;  
begin  
open cur\_para('0001');  
loop  
fetch cur\_para into t\_name;  
exit when cur\_para%notfound;  
dbms\_output.put\_line(t\_name);  
end loop;  
close cur\_para;  
end;  
/

\--输出结果：  
中国文学

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\--利用游标修改表数据  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
declare  
cursor cur is  
select name from deptment for update;  
text  varchar2(10);  
begin  
open cur;  
fetch cur into text;  
while  cur%found loop  
update deptment set name=name||'\_t' where current of cur;  
fetch cur into text;  
end loop;  
close cur;  
end;  
/

\--输出结果：  
id             name  
\-------      -----------  
01          A部门\_t  
02          B部门\_t  
03          C部门\_t  
04          D部门\_t  
05          E部门\_t

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\--游标在for循环中不用打开和关闭  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
begin  
for cur in(select name from deptment) loop  
dbms\_output.put\_line(cur.name);  
end loop;  
end;  
/

\--输出结果  
A部门\_t  
B部门\_t  
C部门\_t  
D部门\_t  
E部门\_t

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*