oracle 11g - 命令

使用sqlplus进入oracle登陆窗口，输入用户名和密码，或使用cmd进入dos窗口使用 sqlplus scott/tiger as sysdba直接进入数据库
使用alter user scott account unlock解锁用户
dual表：数学表达式
获取系统当前时间：select sysdate from dual;
别名：select ename,sal*12 annual_sal from emp;
字符串相加：select ename||sal from emp; (||相加)字符串使用单引号，使用两个单引号来转义一个单引号
转义字符默认为\ ，select ename from where ename like '%\%%';选择名字中含有%的记录，也可使用select ename from emp where ename like '%#%%' escape '#'自定义转义字符;
lower(upper)函数：select lower(ename) from emp;
substr函数，从第一个字符开始取三个字符：select substr(ename,1,3) from emp;
chr函数将ascii码的值转化成字符： select char(65) from dual; ascii则相反：select ascii('A') from dual;
to_char函数，格式化成指定格式的字符串：select to_char(sal,'$99,999.9999') from emp;使用L代替$则为本地货币即(人民币)：￥
to_date函数：select ename,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp where hiredate > to_date('1981-02-20 12:00:00','YYYY-MM-DD HH24:MI:SS');
to_number函数：select sal from emp where sal > to_number('$1,547.99','$99,999.99');
nvl函数：select ename,sal*12+comm from emp; ---> select ename,sal*12+nvl(comm,0) from emp;
max,min,avg,sum,count函数:select count(distinct deptno) from emp;
group by: select deptno,job,max(sal) from emp group by deptno,job;
select enam from emp where sal=(select max(sal) from emp);
select ename,deptno,max(sal) from emp group by ename,deptno;
select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
tip:敲入ed，复制命令到afiedt.buf,修改后关闭保存返回到sqlplus命令行输入/执行修改后的命令
子查询join .. on ..：select ename,sal from emp
join (select deptno,max(sal) max_sal from emp group by deptno) em
on (emp.sal=em.max_sal and emp.deptno = em.deptno)
自身表连接：1.select emp.ename,t.ename from emp join (select ename,empno from emp) t on emp.mgr = t.empno
　　 2.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno
select ename,dname from emp join dept on (emp.deptno = dept.deptno) <--->select ename,dname from emp join dept using(deptno)
dba登陆：conn sys/密码 as sysdba; 授权：grant create table,create view to scott;
create view v$_dept_avg_sal_grade as select deptno,grade,avg_sal from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s
on (t.avg_sal between losal and hisal);
备份数据库,使用dba帐号登陆：使用exp导出，在此过程中要输入要备份的用户
创建新用户并授予权限：create user 用户名 identified by 密码 default tablespace users quota 10M on users;
　　 grant create session,create table,create view to 用户
导入数据：imp 事务回滚：rollback
创建一个新表并把指定表的数据copy到新表中：create table emp2 as select * from emp; (insert into emp2 select * from emp)
rownum: select empno,ename from emp where rownum <= 5;
分页查询：select ename,sal,r from (select ename,sal,rownow r from (select ename,sal from emp order by sal)) where r between 6 and 10;
transaction管理：commit，DDL语句，正常退出事务自动提交，rollback，非正常退出事务自动回滚。
创建表：
create table stu(id number(6),name varchar2(20),sex number(1),sdate date,grade number default 1,email varchar2(50));
约束条件（字段）：create table stu(id nunber(6) unique,name varchar2(20) not null,.....);
约束条件（表）：create table stu (id..... email varchar2(50) constraint stu_name_email_un unique(name,email));
create table class(id nubmer(6) primary key, name varchar2(20) not null);
约束：not null,unique,primary key,foreign key
修改表结构： alter table stu add(addr varchar2(100)); alert table stu drop(addr);
alert table stu modify(addr varchar2(150));
alert table stu drop constraint xxx;
desc user_tables; 查看当前用户下的所有的表：select table_name from user_tables; 查看视图：select view_name from user_views;
数据字典：desc dictionary;
创建索引：create index idx_stu_email on stu(email);
sequence:create sequence seq; select seq.nextval from dual; insert into article values(seq.nextval,'a','b');
PL/SQL:    begin
　　          dbms_output.put_line('HelloWorld');
                end;
                /
              在执行以上命令之前(打开显示功能）:set serveroutput on;
变量声明：declare v_name varchar2(20); begin v_name='myname'; dbms_output.put_line(v_name); end; /
--注释一行
数据类型：binary_integer(计数),number,long(最大2G),boolean(不能使用dbms_output.put_line来打印boolean类型的数据),date,char,varchar2
使用%type来指定变量类型：declare
                                     v_empno0 number(4);
                                     v_empno1 emp.empno%type;
                                     v_empno2 empno1%type;
ddl语句：begin
　　 execute immediate 'create table T (name varchar2(20) default ''aaa'')'
end;
/
if... elsif...else end if:declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp
      where empno = 7369;
if(v_sal <1200) then
     dbms_output.put_line('low');
elsif(v_sal<2000) then
      dbms_output.put_line('middle');
else
     dbms_output.put_line('high');
end if;
end;
循环：declare
i binary_integer := 1;
begin
loop
   dbms_output.put_line(i);
i := i+1;
exit when (i>=11);
end loop;
end;

declare
j binary_integer := 1;
begin
while j<11 loop
   dbms_output.put_line(j);
   j := j + 1;
end loop;
end;

begin
for k in 1..10 loop
   dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
   dbms_output.put_line(k);
end loop;
end;
创建序列： create sequence seq_errorlog_id start with 1 increment by 1;
游标（cursor）：
declare
cursor c is
          select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;

declare
cursor c is
          select * from emp;
v_emp c%rowtype;
begin
open c;
   loop
    fetch c into v_emp;
    exit when (c%notfound);
    dbms_output.put_line(v_emp.ename);
   end loop;
close c;
end;

declare
cursor c is
         select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while (c%found) loop
   dbms_output.put_line(v_emp.ename);
   fetch c into v_emp;
end loop;
close c;
end;

declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
procedure(存储过程):创建-->
create or replace procedure p
is
   cursor c is
     select * from emp2 for update;
begin
for v_emp in c loop
     if(v_emp.deptno = 10) then
       update emp2 set sal = sal + 10 where current of c;
     elsif(v_emp.deptno =20) then
       update emp2 set sal = sal + 20 where current of c;
     else
       update emp2 set sal = sal + 50 where current of c;
     end if;
   end loop;
   commit;
end;
调用-->: exec p; 或者 begin p; end;
带参数的存储过程：创建-->
create or replace procedure p
(v_a in number, v_b number, v_ret out number,v_temp in out number)--in传入参数,out返回值,in out传入和返回参数,默认为in
is
begin
     if(v_a > v_b) then
      v_ret := v_b;
     else
      v_ret := v_b;
     end if;
     v_temp := v_temp + 1;
end;
调用-->
declare
v_a number := 3;
v_b number := 4;
v_ret number;
v_temp number := 5;
begin
p(v_a,v_b,v_ret,v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
函数：创建-->
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal <2000) then
return 0.10;
elsif (v_sal <2750) then
return 0.15;
else
return 0.20;
end if;
end;
调用-->与其他内置函数一样 select lower(ename),sal_tax(sal) from emp;
trigger(触发器):
创建一张log表-->
create table emp2_log(
uname varchar2(20),
action varchar2(10),
atime date
)
创建触发器-->(级联更新—— update emp2 set deptno = :NEW.deptno where deptno = :OLD.deptno;)
create or replace trigger trig
   after insert or delete or update on emp2 for each row
begin
    if inserting then
       insert into emp2_log values( USER, 'insert',sysdate);
    elsif updating then
       insert into emp2_log values (USER,'update',sysdate);
    elsif deleting then
       insert into emp2_log values (USER,'detele',sysdate);
    end if;
end;

posted on 2013-06-30 23:36 zcjava 阅读(270) 评论(0) 编辑收藏举报