《oracle常用SQL》由会员分享，可在线阅读，更多相关《oracle常用SQL（5页珍藏版）》请在金锄头文库上搜索。
1、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; local
2、host: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
3、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 tabl
4、e 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(
5、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 cons
6、traint 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
7、(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 re
8、cyclebin;从回收站中恢复数据表: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
9、 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_datet
10、o_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 c
11、2 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.
12、 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) v
13、alues(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;/把取出的值赋给一个变量
