Java绿地(~ming~)

Java 草地

常用链接

统计

最新评论

oracle数据库常用语句

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/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,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语言:模块语法和流程控制:(头控制符没有标点;尾控制符都有;)

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才能输出)


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 c1 from t1;

使用视图:select * from v1;


28.创建函数:create or replace function get_maxe_empno return varchar2   is

tmpvar 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='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;


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_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语句视图和索引储存过程和触发器维护。

posted on 2007-06-29 11:25 mrklmxy 阅读(2798) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。


网站导航: