oracle常用SQL.doc

Oracle10g数据库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/设置远程测试：tnsping datebasename；远程连接:sqlplus name/passworddatebasename;4.创建表空间：create tablespace test datafile test.dbf size 10m autoextend on next 2mmaxsize unlimitedloggingpermanentextent management local autoallocateblocksize 8ksegment space management manuaL;/段空间5创建用户并连接： create user TEST identified by TEST default tablespace TEST temporary tablespace TEMPquota unlimited on TESTquota unlimited on TEMPgrant 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；12Select 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.操作符：比较：=，,=,1;(having不能用变量申明);等价于select c1,count(*) as cn from t1 group by c1 where cn1;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(budget10000 and budget1000000)18.创建外键: create table t1(dept_no varchar2(4) not null, constraint fk_emp foreign key (dept_no) references t2(dept_no), 对已经存在表创建外键:alter table t1add 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_dateto_date(1998-10-1,yyyy-mm-dd)then 等两年/时间非字段时间型 else 两室一厅end when p2 then case when enter_dateto_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 end24.嵌套查询select c1 from t1 where c2 in(select c2 from t2 where c3=(select c3 from t3)25Pl/sql语言：模块语法和流程控制：（头控制符没有标点；尾控制符都有；）declare icount number;begin icount :=0; for mycount in reverse 0.10/mycount 为自定义变量 -while(icount100); if(mycount5) then dbms_output.put_line(result=|icount);/流程输出语句end if; end loop; end; /(set serverout on才能输出)26序列Sequence. create sequence se_1 increment by 1 start with 100maxvalue 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 /start with 语句不能用了，否则重复定义删除序号：drop sequence ；自动序列rownum:select * from t1 where rownum5;select * from(select rownum as a,e.* from t1 e)where a=3/单行必先关联，e为表的别名27创建视图create or replace view v1 asselect c1 from t1;使用视图：select * from v1;28创建函数：create or replace function get_maxe_empno return varchar2 istmpvar varchar2(20);/局部变量声明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=PROCEDUREselect text from all_source where name=SP_TEST order by line;使用过程：declare fname varchar2(200);update_count number;beginfname:=John;update_count:=0;sp_test(fname,update_count);commit;dbms_output.put_line(update_count);end;30.触发器：create or replace trigger tgr_emp_change after insert or update or delete on employee referencing new as n old as o/ :new为引用最新列值; :old为引用以前列值; 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(:o.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_info31声明包：create or replace package pkg_test asfunction 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 / 语句体中是初始化语句，特殊用途