Oracle自学笔记

雨点打透心脏的1/2处 2022-09-26 02:51 290阅读 0赞

oracle 命令:  
  
  
1.登录  
  
  
 sqlplus system/yeren  
  
  
2.切换用户(连接命令)  
  
  
 conn sys/yeren@oracle as sysdba(即切换到了超级管理员)    
  
  
3.修改密码  
  
  
 passw   
  
  
4.断开连接  
  
  
 disc  
  
  
5.显示当前用户  
  
  
 show user  
  
  
6.退出  
  
  
 exit  
  
  
7.用@或start执行脚本文件  
  
  
 @ d:\\tab1.txt  
  
  
8.将控制台上的内容输出到文件  
  
  
 spool d:\\out.txt  
 select \* from tab1;  
 spool off  
  
  
9.修改表结构  
  
  
 添加一列  
 alter table tablename add(clumnname,type);  
 修改某列  
 alter table tablename modify(clumnname,type);   
  
  
10.事务  
  
  
 设置保存点  
 savepoint name;  
 回滚至保存点  
 rollback to name;  
 提交  
 commit;  
 设置只读事务  
 set transaction read only;  
  
  
11.字符函数  
  
  
 select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;  
 而mssql 是这样实现的  
 select upper(substring(id,1,1))+lower(substring(id,2,len(id)-1)) from person  
 -----------------------------------------------------------------------------  
 select replace(ename,'A','我是A') from emp; mssql无区别  
 -----------------------------------------------------------------------------  
 to\_date('1999/8/8','yyyy-mm-dd')进行自定义格式时间转换 默认是dd-mm月-yyyy  
  
  
12.数学函数  
  
  
 四舍五入  
 round(dataclumn,位数);round(34.45)=34; round(34.45,1)=34.5  
 直接舍弃  
 trunc(dataclumn) 截取到整数 trunc(55.99)=55; trunc(55.49,1)=55.4;trunc(55.99,-1)=50;  
 向下(小)方向取整 即原值>=新值  
 floor(11.99)=11;floor(-11.99)=-12;  
 向上(大)方向取整 即原值<=新值  
 ceil(11.01)=12;ceil(-11.01)=-10;  
 取模函数  
 mod(10,2)=0;mod(5,3)=2;  
 abs(-1)=1; acos(n);asin(n);atan(n);cos(n);sin(n);exp(n);log(m,n);power(m,n);  
  
  
13.日期函数  
  
  
 当前系统时间  
 sysdate  
 add\_months(clumn,n);在clumn基础上增或者减去相应的月数  
 last\_day(hiredate)取得该日期月份的最后一天  
  
  
14.转化函数  
  
  
 to\_char(data,exp)转换为指定格式的字符串  
 select to\_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;  
 select to\_char(sal,'L99,999.99') from emp; L本地货币 $ 美元 C本地国际货币符  
  
  
15.系统函数  
  
  
 sys\_context  
 select sys\_context('userenv','db\_name') from dual;  
 select sys\_context('userenv','current\_schema') from dual;  
 select sys\_context('userenv','SESSION\_USER') from dual;  
 select sys\_context('userenv','NLS\_DATE\_FORMAT') from dual;  
  
  
16.导出表  
  
  
 exp userid=system/yeren@oracle tables=(scott.emp) file=d:\\emp.dmp //导出表及数据  
 exp userid=system/yeren@oracle tables=(scott.emp) file=d:\\emp.dmp rows=n //只导出表结构(间接)  
 exp userid=system/yeren@oracle tables=(scott.emp) file=d:\\emp.dmp direct=y //只导出表结构(直接)  
  
  
17.导出方案  
  
  
 exp userid=system/yeren@oracle owner=scott file=d:\\scott.dmp //导出方案  
  
  
18.导出数据库  
  
  
 exp userid=system/yeren@oracle full=y inctype=complete file=d:\\oracle.dmp  
  
  
19.导入表  
  
  
 只要把exp替换成imp,userid=scott/yeren@oracle  
 但要注意由于导出是由system创建的,所以要暂时把scott设置成dba角色权限  
  
  
20.导入数据  
  
  
 imp userid=scott/yeren@oracle tables=(emp) file=d:\\emp.dmp ignore=y  
  
  
21.导入方案  
  
  
 导入自己创建的方案  
 imp userid=scott/yeren@oracle file=d:\\scott.dmp  
 导入别人创建的方案  
 imp userid=system/yeren@oracle file=d:\\scott.dmp fromuser=system touser=scott;  
  
  
22.导入数据库  
  
  
 imp userid=system/yeren@oracle full=y file=d:\\oracle.dmp  
  
  
23.查询当前用户创建的所有表  
  
  
 select \* from user\_tables;  
  
  
24.查询当前用户能访问的所有表  
  
  
 select \* from all\_tables;  
  
  
25.查询dba拥有的表(需具有dba权限)  
  
  
 select \* from dba\_tables;  
  
  
26.查询当前所有用户信息(需具有dba权限)  
  
  
 select \* from dba\_users;  
  
  
27.查询所有的角色  
  
  
 SELECT \* FROM DBA\_ROLES;  
  
  
28.查询角色包含的权限  
  
  
 系统权限  
 select \* from dba\_sys\_privs where grantee='CONNECT';  
 对象权限  
 select \* from dba\_tab\_privs where grantee='DBA';  
  
  
29.查询用户包含的权限  
  
  
 系统权限  
 select \* from dba\_sys\_privs where grantee='SCOTT';  
 对象权限  
 select \* from dba\_tab\_privs where grantee='SCOTT';  
  
  
30.查询所有的系统权限  
  
  
 select \* from dba\_sys\_privs;  
  
  
31.查询所有的对象权限  
  
  
 select \* from dba\_tab\_privs;  
  
  
32.查询某个用户具有的角色  
  
  
 select \* from dba\_role\_privs where grantee='SCOTT';  
  
  
33.显示当前数据库的全称  
  
  
 select \* from global\_name;  
  
  
34.显示当前用户可以访问的数据字典视图  
  
  
 select \* from dict where comments like '%grant%';  
  
  
35.创建表空间  
  
  
 create tablespace sp1 datafile 'd:\\sp1001.dbf' size 20m  
   uniform size 128k;--uniform:区大小  
 ALTER TABLESPACE tbs\_03   
     ADD DATAFILE 'tbs\_f04.dbf'  
     SIZE 50K  
     AUTOEXTEND ON  
     NEXT 10K  
     MAXSIZE 100K;  
  
  
36.使表空间脱机或联机  
  
  
 alter tablespace name offline;  
 alter tablespace name online;  
  
  
37.使表空间只读或可读写  
  
  
 alter tablespace name read only;  
 alter tablespace name read write;  
  
  
38.查询表空间下的所有表  
  
  
 select \* from all\_tables where TABLESPACE\_NAME='SP1';  
  
  
39.查询某表相关信息  
  
  
 select \* from USER\_tables where TABLE\_NAME='EMP';  
  
  
40.删除表空间(包含所有表内容)  
  
  
 drop tablespace name including contents and datafiles;  
  
  
41.扩展表空间  
  
  
 a.增加数据文件  
 alter tablespace sp1  
 add datafile 'd:\\sp1002.dbf'  
 size 20m  
 autoextend on  
 next 10m  
 maxsize 200m;  
 b.增加数据文件的大小(报错)  
 alter tablespace sp1 datafile 'd:\\sp1001.dbf' resize 40m;  
 c.设置文件的自增长(报错)  
 alter tablespace sp1 datafile 'd:\\sp1001.dbf' autoextend on next 10m maxsize 500m;  
  
  
42.蠕虫式添加数据  
  
  
 insert into myorz select \* from myorz;  
  
  
43.查询所有数据文件  
  
  
 select \* from dba\_data\_files;  
44.移动数据库文件  
  
  
 cmd下 move 原路径 新路径;  
45.修改表空间文件路径  
  
  
 alter tablespace sp1 rename datafile 原路径 to 新路径;  
  
  
46.建表  
  
  
SQL> create table goods(  
  2  goodsId char(8) primary key,  
  3  goodsName varchar2(30),  
  4  unitprice number(10,2) check(unitprice>0),  
  5  category varchar2(8),  
  6  provider varchar2(30)  
  7  );  
   
Table created  
   
SQL> create table customer(  
  2  customerId char(8) primary key,  
  3  name varchar2(50) not null,  
  4  address varchar2(50),  
  5  email varchar2(50) unique,  
  6  sex char(2) default '男' check(sex in ('男','女')),  
  7  cardId char(18)   
  8  );  
   
Table created  
   
SQL> create table purchase(  
  2  customerId char(8) references customer(customerid),  
  3  goodsId char(8) references goods(goodsid),  
  4  nums number(10) check(nums between 1 and 30));  
   
Table created  
  
  
47.添加非空约束  
  
  
 alter table goods modify goodsName not null;  
  
  
48.添加唯一约束  
  
  
 alter table customer add constraint unq\_carid unique(cardId);  
  
  
49.添加检查约束  
  
  
 alter table customer add constraint ch\_address check(address in('东城','西城','海淀','朝阳','通州','崇文'));  
  
  
50.添加外键  
  
  
 alter table PURCHASE add foreign key (GOODSID) references GOODS (GOODSID);  
  
  
51.删除约束  
  
  
 alter table 表名 drop constraint 约束名;  
 alter table 表名 drop primary key cascade;  
  
  
52.查询约束信息  
  
  
 显示约束信息  
 select \* from user\_constraints where table\_name='PURCHASE';  
 显示约束列  
 select \*  from user\_cons\_columns where table\_name='PURCHASE';  
  
  
53.列级定义约束(如:非空)  
  
  
  create table customer(  
     customerId char(8) constraint pk\_customer primary key,  
     name varchar2(50) not null,  
      address varchar2(50),  
     email varchar2(50) unique,  
      sex char(2) default '男' check(sex in ('男','女')),  
      cardId char(18)   
    );  
  
  
54.表级定义约束  
  
  
 create table customer(  
     customerId char(8),  
     name varchar2(50) not null,  
      address varchar2(50),  
     email varchar2(50),  
      sex char(2) default '男',  
      cardId char(18) ,  
  constraint pk\_customer primary key,  
  constraint uq\_email unique(email),  
  constraint ck\_sex check (address in('男','女'));  
    );  
  
  
55.新建索引index  
  
  
 单列索引  
 create index indexname on customer(name);  
 复合索引  
 create index indexname on customer(name,cardId);  
  
  
56.查询索引信息  
  
  
 显示索引信息  
 select \* from user\_indexes where table\_name='CUSTOMER';  
 显示索引列信息  
 select \* from user\_ind\_columns where table\_name='CUSTOMER';  
  
  
57.创建用户  
  
  
 create user name identified by password  
58.授予用户系统权限  
  
  
 grant create session,create table to user \[with admin option\];  
 with admin option --表示权限可继续授权  
  
  
59.回收系统权限  
  
  
 revoke create session from user;(系统权限不是级联回收)  
  
  
60.对象权限(用户访问其他方案对象的权利)  
  
  
 grant select on emp to user with grant option;  
 grant all on emp to user with grant option;  
 grant update on emp(sal) to user;  
 with grant option --可以将对象权限继续授权(带上该属性后user不能是角色)  
  
  
61.回收对象权限  
  
  
 revoke select on emp from user;(对象权限是级联回收)  
  
  
62.创建角色  
  
  
 create role 角色名 not identified;  
 create role 角色名 identified by 密码;  
  
  
63.给角色赋权限和给用户赋权几乎没有区别  
  
  
64.把角色赋给用户  
  
  
 grant 角色名 to 用户名;  
  
  
65.删除角色  
  
  
 drop role 角色名;  
  
  
66.查询角色所具有的系统权限  
  
  
 select \* from role\_sys\_privs where role='角色名'   
  
  
67.查询角色所具有的对象权限  
  
  
 select \* from dba\_tab\_privs where role='角色名'   
  
  
68.查询所有角色  
  
  
 select \* from dba\_roles;  
   
69.显示错误信息   
  
  
 show error;  
70.代码块  
SQL> set serveroutput on  
SQL> begin  
  2  dbms\_output.put\_line('hello');  
  3  end;  
  4  /  
  
  
71.创建存储过程  
  
  
 A:无参的  
 create or replace procedure proc\_emp\_insert is  
  begin  
   insert into emp values('12','afsdfa');  
   end;  
   /  
 B:有参的  
 只有输入参数的  
 create or replace procedure proc\_emp\_insert(empid in varchar2,empname in varchar2) is  
  begin  
   insert into emp values(empid,empname);  
   end;  
 /  
 带输出参数的  
 create or replace procedure proc\_emp\_selectnamebyId(empid in varchar2,v\_empname out  varchar2) is  
  begin  
   select empname into v\_empname from emp where empno=empid;  
   end;  
 /  
 返回结果集的  
 create or replace package testpackage as type testcursor is ref cursor;  
 end testpackage;  
 create or replace procedure sp\_pro(spno in number,p\_cursor out  testpackage.testcursor) is  
 begin  
  open p\_cursor for select \* from emp where empno=spno;  
 end;  
 只有输入参数的执行  
 exec proc\_emp\_insert('67','ayiueyria');  
 带输出参数的执行  
 declare name varchar2;  
 exec proc\_emp\_insert('67',name out);  
  
  
72.调用存储过程  
  
  
 exec 存储过程名(参数1，参数2...);  
 call 存储过程名(参数1，参数2...);(报错)  
  
  
73.创建函数  
  
  
create or replace function func\_emp\_empname(v\_empid varchar2)  
 return varchar is v\_empname varchar2(20);  
 begin  
       select empname into v\_empname from emp where empid=v\_empid;  
       return v\_empname;  
 end;  
   
SQL> var ab varchar2(20);  
SQL> call func\_emp\_empname('8888') into:ab;  
  
  
74.创建包  
  
  
 create package sp\_package1 is  
 procedure 存储过程声明;  
 function 函数声明;  
 end;  
  
  
75.实现包体  
  
  
 create or replace package body sp\_package1 is  
 存储过程代码  
 函数代码  
 end;  
  
  
76.变量常量的定义赋值  
  
  
 set serveroutput on;  
 declare c\_rate number(3,2):=0.03;  
 v\_ename emp.ename%type;  
 v\_sal emp.sal%type;  
 v\_tax\_sal number(7,2);  
 begin  
  select ename,sal into v\_ename,v\_sal from emp where empno=&no;  
  v\_tax\_sal:=v\_sal\*c\_rate;  
  dbms\_output.put\_line('姓名是'||v\_ename||'工资是'||v\_sal||'所得税'||v\_tax\_sal);   
 end;  
  
  
77.复合类型  
  
  
自定义记录类型(实际上相当于一种数据结构)  
declare type emp\_type is record(empno emp.empno%type,ename emp.ename%type);  
自定义表类型(实际上相当于数组)  
declare type emp\_type is table of ename emp.ename%type index by binary\_integer;  
游标变量  
declare type emp\_cursor is ref cursor;  
test\_cursor emp\_cursor;  
v\_ename emp.ename%type;  
v\_sal emp.sal%type;  
v\_empno emp.empno%type;  
begin  
open test\_cursor for select ename,sal,empno from emp where deptno=&no;  
loop  
    fetch test\_cursor into v\_ename,v\_sal,v\_empno;  
    if v\_sal<1000   
    then  
         update emp set sal=sal+100 where empno=v\_empno;  
    end if;  
    exit when test\_cursor%notfound;  
    dbms\_output.put\_line(v\_ename||':'||v\_sal);  
end loop;  
end;  
  
  
78.条件判断语句  
  
  
if 条件表达式 then 语句 end if;  
if 条件表达式 then 语句 else 语句 end if;  
if 条件表达式 then 语句 elsif 条件表达式 then 语句 else 语句 end if;  
case   
when 条件表达式 then   
语句;  
when 条件表达式 then   
语句;  
else  
语句;  
end case;  
  
  
79.循环语句  
  
  
loop 语句 end loop;(相当于do-while)  
while  条件表达式 loop 语句 end loop;(相当于while)  
for循环  
for i in reverse 1..10 loop  
 insert into users values(i,"xx");  
end loop;  
  
  
80.顺序控制 goto  
  
  
begin  
 loop   
  语句  
  if 退出循环条件表达式 then  
   goto end\_loop;  
  end if;  
 end loop;  
 <<end\_loop>>  
end;  
null;相当于什么都不做  
  
  
81.分页存储过程  
  
  
create or replace package testpackage as type testcursor is ref cursor;  
 end testpackage;  
create or replace procedure fenye  
(tablename in varchar2,pagesize in number,pageindex in number,descclumn in varchar2, totalrows out   
number,pagecount out number,p\_cursor out testpackage.testcursor) is  
v\_sql varchar2(1000);  
v\_begin number:=(pageindex-1)\*pagesize+1;  
v\_end number:=pageindex\*pagesize;  
begin  
 v\_sql:='select \* from (select rownum rn,t1.\* from (select \* from '||  
tablename||' order by '||descclumn||' desc) t1 where rownum<='||v\_end||') where rn >= '||v\_begin;  
 open p\_cursor for v\_sql;  
 v\_sql:='select count(\*) from '||tablename;  
 execute immediate v\_sql into totalrows;  
 if mod(totalrows,pagesize)=0 then   
 pagecount:=totalrows/pagesize;  
 else  
 pagecount:=totalrows/pagesize+1;  
 end if;  
 dbms\_output.put\_line(v\_sql);  
end;  
  
  
82.三种分页  
  
  
\--rowid分页  
select \* from (select rownum rn,t2.\* from (select t1.\*,rowid rid from temp t1) t2 where rownum <= 1000) where rn >=800;  
\--rownum分页  
select \* from (select rownum rn,t1.\* from (select \* from student) t1 where rownum<=10) where rn >= 5;  
\--row\_number函数分页  
select  \*  from   
(select  t.\* ,row\_number() over (order  by xh  desc ) rk   from  student  t)   
 where  rk <=10  and  rk  >=5;  
\--三种方式比较  
select rownum rn,t1.\* from (select \* from temp) t1 where rownum<=1000 minus select rownum rn,t1.\* from (select \* from temp) t1 where rownum<=800;  
set timing on;  
select \* from (select rownum rn,t1.\* from (select \* from temp) t1 where rownum<=1000) where rn >= 800;  
select \* from (select rownum rn,t2.\* from (select t1.\*,rowid rid from temp t1) t2 where rownum <= 1000) where rn >=800;  
  
  
83.预定义例外  
  
  
exception when no\_data\_found then dbms\_output.put\_line('异常消息');  
case\_not\_found case分支语句错误  
dup\_val\_on\_index 在唯一索引列上插入重复值  
invaild\_cursor 试图在不合法的游标上操作或是游标没打开或是关闭没有打开的游标  
invaild\_number 输入数字错误  
too\_many\_rows 返回超过一行  
zero\_divide 除以0  
value\_error 定义的变量长度不足以接受返回值  
  
  
84.自定义例外  
  
  
create or replace procedure extest(spno number) is  
myex exception;  
begin  
 update emp set sal=sal+1000 where empno=spno;  
 if sql%notfound then  
 raise myex;  
 end if;  
 exception when myex then dbms\_output.put\_line('异常消息');  
end;  
  
  
85.视图  
  
  
create view 试图名 as select语句;  
   
86.触发器  
  
  
create or replace trigger tri\_banjibefore before update on ban\_ji  
for each row  
begin  
insert into ban\_log values(:old.banid);  
end;  
   
87.权限相关字典：  
  
  
DBA\_USERS     所有用户  
DBA\_ROLES     所有角色  
SYSTEM\_PRIVILEGE\_MAP                  所有权限  
   
ROLE\_SYS\_PRIVS 角色拥有的系统权限  
ROLE\_TAB\_PRIVS  角色拥有的对象权限  
   
USER\_TAB\_PRIVS\_MADE     查询授出去的对象权限(通常是属主自己查）  
USER\_TAB\_PRIVS\_RECD  用户拥有的对象权限  
   
USER\_COL\_PRIVS\_MADE     用户分配出去的列的对象权限  
USER\_COL\_PRIVS\_RECD 用户拥有的关于列的对象权限  
   
USER\_SYS\_PRIVS   用户拥有的系统权限  
USER\_TAB\_PRIVS  用户拥有的对象权限  
USER\_ROLE\_PRIVS  用户拥有的角色  
  
  
   
88.同义词  
  
  
如果要创建一个远程数据库上的某张表的同义词，需要先创建一个dblink来拓展访问，然后使用如下语句创建数据库同义词：create synonym table\_alias\_name for tablename@dblink ;