oracle数据库常用语句

caixuqad

1．  数据类型： 字符类型：char(标准通用拉丁字符),nchar(汉字等其他字符),varchar2(长度可变长字符),nvarchar2,long; 数字类型：number(通用),integer,float 日期和时间：date,timestamps(分秒，时区) 行：rowid(逻辑地址),urowid（逻辑地址，内存地址）; 二进制:raw(size)(原始二进制数据),long raw,blob(二进制大型对象;最大4G字节),clob(字符大型对象),nclob,bfile; 2.oracle WEB管理页面：localhost:5560/isqlplus; localhost:5500/em 3.net设置远程测试：tnsping datebasename；远程连接:sqlplus name/password@datebasename; 4.创建表空间：create tablespace test datafile 'test.dbf' size 10m autoextend on next 2m maxsize unlimited logging permanent extent management local autoallocate blocksize 8k segment space management manuaL;//段空间 5．创建用户并连接： create user "TEST" identified by "TEST" default tablespace TEST temporary tablespace TEMP quota unlimited on TEST quota unlimited on TEMP grant "connect" to test//分配基本权限。 conn test/test; 6．重设用户密码：       scott/tiger为默认用户，       alter user scott identified by tiger;       解锁：alter user scott account unlock; 7.sql脚本的执行：@路径/filename.sql; 8.创建表：create table t1(c1 type 约束,c2 type 约束（not null,unique,check,primary key）） 9.查询：select distinct c1 from t1 where 条件 group by c1 having by 子条件order by c1; 10.连接字符串：select c1 (as可省) 列1 ||c2 from t1; 11.单行函数：select lower(c1) from t1；upper全大写，initcap第一个字母大写，length； 12.select Sysdate from dual(系统默认的空表)显示系统时间,months_between(date,date); 13.round(数据,5位数),to_date(1997-10-11,’yyyy-mm-dd’)，to_char()函数使用要转换。 14.nvl(c1,0)把字段为空的值换为0，nvl2(c1,1,0)不空的为1，空的值为0； 15.操作符：     比较：=，<>,>=,<=,>,<；逻辑：and,or,not     其他：in/not in,between..and..,is null/is not null,like/not like，exists/not exists；      Eg:select count(distinct c1) as 种类 from t1 where c1 like ‘%l%’(模糊查询如m_n)(c1 between 10 and 100) group by c1 order by c1 desc，c2 desc（不写就默认asc） 16.聚合函数：count(*)返回所有行的记录数,min(c1),max(c1),sum(c1),avg(c1); Eg:select c1,count(*) from t1 group by c1 having by count(*)>1;(having不能用变量申明);等价于select c1,count(*) as cn from t1 group by c1 where cn>1; 17.声明主键和check：一.create table t1(c1 primary key) 二.创建表的时候指定限制的名称:create table t1(c1  constraint pk_emp primary key); 三：create table t1(emp_no number not null , constraint pk_emp primary key(emp_no))； 为已经存在的表增加主键:alter table t1 add constraint pk_emp2 primary key (c1)； 创建check: create table t1(budget number(7),constraint CHK_PROJ check(budget>10000 and budget<1000000)) 18.创建外键: create table t1(dept_no varchar2(4) not null, constraint fk_emp foreign key (dept_no) references t2(dept_no), 对已经存在表创建外键:alter table t1 add constraint foreign_work_emp foreign key(c1) references t2(c1)；   删除一个外键alter table t1 drop constraint foreign_work_emp； 19.删除表：drop table t1; 查看回收站:show recyclebin; 清空回收站 purge recyclebin; 从回收站中恢复数据表:flashback table t1 to before drop; 20.更新数据操作：插入记录：insert into t1(c1,c2)values(‘’,’’)； 插入一字段:insert into t1(c1,c2) select c3,c4 from t2; 更新记录：update t1   set c1=’’ where ; 删除记录：delete from t1 where;truncate table t1;drop table t1; 21.合并查询：intersect(select * intersect select *),union(去掉空),union all(包括空),minus（减集）; 22.多表查询:select * from t1,t2;(笛卡尔集c1行*c2行)；select * from t1 join t2 using(num);等价于select * from t1 inner join t2 on(t1.no=t2.no);join逻辑连接，只连接有联系的字段，full join物理机械连接，out join,left out join(右边变成空),right out join; 23．控制语句：select emp_no, case project_no   when 'p1' then case when enter_date<to_date('1997-10-1','yyyy-mm-dd')then '三室'   when enter_date>to_date('1998-10-1','yyyy-mm-dd')then '等两年'//时间非字段时间型        else '两室一厅'end   when 'p2' then case when enter_date<to_date('1997-10-1','yyyy-mm-dd')        then '三室两厅' when enter_date>to_date('1998-10-1','yyyy-mm-dd')        then '看情况再分' else ' 一室一厅'end end as 分房情况from works_on； if then end if,loop end loop,declare type *,while( )loop end loop,case when then end 24.嵌套查询select c1 from t1 where c2 in(select c2 from t2 where c3=(select c3 from t3)) 25．Pl/sql语言：     模块语法和流程控制头控制符没有标点;尾控制符都有;) 26．序列Sequence.           create sequence se_1  increment by 1  start with 100 maxvalue 999999 //minvalue n --表示序列可以生成的最小值(降序).  cycle;//到头自动循环           查看：select se_1.nextval from dual;                     select se_1.currval from dual;（必先next初始化）           使用：create table stu1(stu_no number(10) not null,stu_name varchar2(20) not null);                    insert into stu1(stu_no,stu_name) values(se_1.nextval,'joi');           修改序列：alter sequence <sequencen_name>//start with 语句不能用了，否则重复定义           删除序号：drop sequence <sequence_name>；           自动序列：rownum                 select * from t1 where rownum<5;                select * from(select rownum as a,e.* from t1 e)where a=3//单行必先关联，e为表的别名 27．创建视图        create or replace view v1 as 使用视图：select * from v1; 28．创建函数：        create or replace function get_maxe_empno return varchar2   is            begin  //可执行语句                select max(emp_no) into tmpvar from employee;//把取出的值赋给一个变量                return tmpvar;//函数的重要特征就是通过return语句来传递参数              end get_maxe_empno;         使用：select get_maxe_empno() from dual;         数字字典：select distinct(name) from user_source where type='FUNCTION'         从数字字典里查找信息：select text from all_source where name='GET_MAXE_EMPNO';         删除函数：drop function get_maxe_empno; 29.过程：        create or replace procedure sp_test (fname in varchar2, update_count out int)//参数列表是过程的特征  is              cursor emp_cursor//定义游标             is             select emp_no from employee where employee.emp_fname=fname;//输入in         begin        //   可执行语句     update_count:=0;     for emp_rec in emp_cursor     loop             update employee set emp_fname=emp_lname,emp_lname=fname     where employee.emp_no=emp_rec.emp_no;update_count:=update_count+1;     end loop;commit;//控制语句，强制执行         end；//过程可以有in/out变量          查看过程：                   select distinct(name) from user_source where type='PROCEDURE'                   select text from all_source where name='SP_TEST' order by line;          使用过程：                  declare fname varchar2(200); update_count number; begin fname:='John';update_count:=0; sp_test(fname,update_count);commit;dbms_output.put_line(update_count); end; declare icount number; begin icount :=0; for mycount in reverse 0..10//mycount 为自定义变量 ---while(icount<100) Loop   icount :=icount+10; ---exit when(icount>100); if(mycount>5) then   dbms_output.put_line('result='||icount);//流程输出语句 end if; end loop; end; //(set serverout on才能输出)tmpvar varchar2(20);//局部变量声明 30.触发器：create or replace trigger tgr_emp_change after insert or update or delete on employee      referencing new as n old as o// :new为引用最新列值; ld为引用以前列值;    for each row begin       if inserting then      insert into emp_update_info(emp_no,oper_type) values(:n.emp_no,1);      end if;      if updating then  // updating为触发器的操作符；      insert into emp_update_info(emp_no,oper_type) values(.emp_no,3);      end if; end; 建表：create table emp_update_info(emp_no varchar2(20) not null,oper_type integer); 查看trigger：select distinct( name ) from user_source where type='TRIGGER'; 对employee的操作就自动出发的emp_update_info 31．声明包：create or replace package pkg_test as function get_max_empno   return varchar2;//子程序说明，公共对象声明 procedure sp_test(fname in varchar2,update_count out int); end pkg_test; 创建包体:create or replace package body pkg_test  as // is也行           function get_max_empno return varchar2   as //公共类型与对象声明            tmpvar    varchar2(20);//公共类型和对象声明，           begin   // 语句体中是初始化语句，特殊用途            select max(emp_no) into tmpvar from employee; return tmpvar;         end get_max_empno;          //子程序主体部分           procedure sp_test(fname in varchar2,update_count out int) is            cursor emp_cursor  is            select emp_no from employee where employee.emp_fname = fname;           begin            update_count := 0;            for emp_rec in emp_cursor            loop            update employee set emp_fname = emp_lname, emp_lname = fname            where employee.emp_no = emp_rec.emp_no;            update_count := update_count + 1;            end loop;            commit;//强制执行   end; end pkg_test; 查看定义的package：select distinct( name ) from user_source where type='PACKAGE' 使用package中的对象：select pkg_test.get_max_empno from dual; 32．数据库设计：业务规则和流程—实体和关系—ER图（一对一，一对多，出现多对多进行拆分）—SQL语句—视图和索引—储存过程和触发器—维护。

张兴康

caixuqad