oracle语法大全.docx

1、oracle语法大全Oracle数据库语句大全一入门部分1. 创建表空间create tablespace schooltbs datafile D:oracledatasourceschooltbs.dbf size 10M autoextend on;2. 删除表空间drop tablespace schooltbsincluding contents and datafiles;3. 查询表空间基本信息select *|tablespace_name from DBA_TABLESPACES;4. 创建用户create user lihuaidentified by lihuadefau

2、lt tablespace schooltbstemporary tablespace temp;5. 更改用户alter user lihua identified by 123default tablespace users;6. 锁定用户alter user lihua account lock|unlock;7. 删除用户drop user lihua cascade;-删除用户模式8. oracle数据库中的角色connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_datab

3、ase,imp_full_database,resource9. 授予连接服务器的角色grant connect to lihua;10. 授予使用表空间的角色grant resource to lihua with grant option;-该用户也有授权的权限11. 授予操作表的权限grant select,insert on user_tbl to scott;-当前用户grant delete,update on lihua.user_tbl to scott;-系统管理员二SQL查询和SQL函数1.SQl支持的命令：数据定义语言（DDL）:create,alter,drop数据操纵

4、语言（DML）:insert,delete,update,select数据控制语言（DCL）:grant,revoke事务控制语言（TCL）:commit,savepoint,rollback2.Oracle数据类型字符，数值，日期，RAW，LOB字符型char:1-2000字节的定长字符varchar2:1-4000字节的变长字符long:2GB的变长字符 注意：一个表中最多可有一列为long型Long列不能定义唯一约束或主键约束long列上不能创建索引过程或存储过程不能接受long类型的参数。数值型number:最高精度38位日期时间型date:精确到sstimestamp:秒值精确到小数

5、点后6位函数sysdate,systimestamp返回系统当前日期，时间和时区。更改时间的显示alter session set nls_date_language=american;alter session set nls_date_format=yyyy-mm-dd;Oracle中的伪列像一个表列，但没有存储在表中伪列可以查询，但不能插入、更新和修改它们的值常用的伪列：rowid和rownumrowid:表中行的存储地址，可唯一标示数据库中的某一行，可以使用该列快速定位表中的行。rownum:查询返回结果集中的行的序号，可以使用它来限制查询返回的行数。3.数据定义语言用于操作表的命令c

6、reate tablealter tabletruncate tabledrop table修改表的命令alter table stu_table rename to stu_tbl;-修改表名alter table stu_tbl rename column stu_sex to sex;-修改列名alter table stu_tbl add (stu_age number);-添加新列alter table stu_tbl drop(sex);-删除列alter table stu_tbl modify(stu_sex varchar2(2);-更改列的数据类型alter table s

7、tu_tbl add constraint pk_stu_tbl primary key(id);-添加约束4.数据操纵语言select,update,delete,insert利用现有的表创建表create table stu_tbl_log as select id,stu_name,stu_age from stu_tbl;-选择无重复的行select distinct stu_name from stu_tbl;- 插入来自其他表中的记录insert into stu_tbl_log select id,stu_name,stu_age from stu_tbl;5.数据控制语言gra

8、nt,revoke6.事务控制语言commit,savepoint,rollback7.SQL操作符算术操作符:+-*/比较操作符:=,!=,=,=20;-比较操作符select * from stu_tbl where stu_name like %a%;-比较操作符：likeselect * from stu_tbl where stu_name like a_;-比较操作符：likeselect * from stu_tbl where stu_age in(20,30);-比较操作符：inselect * from stu_tbl where stu_age between 20 an

9、d 30;-比较操作符：betweenselect stu_name from stu_tbl union allselect stu_name from stu_tbl_log;-集合操作符：union all，测试结果具体如下：STU_NAME-李华accpadmin李华accpnimda已选择6行。select stu_name from stu_tbl union select stu_name from stu_tbl_log;-集合操作符：union，测试结果具体如下：STU_NAME-accpadminnimda李华select stu_name from stu_tbl int

10、ersect select stu_name from stu_tbl_log;-集合操作符：intersect，测试结具体如下：STU_NAME-accp李华select stu_name from stu_tbl minus select stu_name from stu_tbl_log;-集合操作符：minus，测试结果如下：STU_NAME-Admin从中可以看出：minus是获取第一张表独有的数据intersect是获取两张表中都有的数据union是整合两张表的数据，都有的只显示一次union all是纯粹的两张表数据整合select id,stu_name| |stu_sex a

11、s name_sex,stu_age from stu_tbl;-连接操作符|，测试结果具体如下： ID NAME_SEX STU_AGE- - - 1000 李华 男 20 1001 accp 男 20 1002 admin 男 308.SQL函数单行函数：从表中查询的每一行只返回一个值，可出现在select子句，where子句中日期函数数字函数字符函数转换函数：ToChar(),ToDate(),ToNumber()其他函数:Nvl(exp1,exp2):表达式一为null时，返回表达式二Nvl2(exp1,exp2,exp3):表达式一为null时返回表达式三，否则返回表达式二Nulli

12、f(exp1,exp2):两表达式相等时，返回null，否则返回表达式一分组函数：基于一组行来返回Avg,Min,Max,Sum,CountGroup by,having分析函数Row_number,rank,dense_rank示例：select u.user_name,sum(oi.order_num*oi.order_price) as total,row_number() over (order by sum(oi.order_num*oi.order_price) desc) as sort from order_item_tbl oi,user_tbl u,order_tbl o

13、where oi.order_id = o.id and o.user_id = u.id group by u.user_name;三锁和数据库对象1.锁：数据库用来控制共享资源并发访问的机制。锁的类型：行级锁，表级锁行级锁：对正在被修改的行进行锁定。行级锁也被称之为排他锁。在使用下列语句时，Oracle会自动应用行级锁：insert,update,delete,select for updateselectfor update允许用户一次锁定多条记录进行更新。使用commit or rollback释放锁。表级锁：lock table user_tbl in mode mode;表级锁类型

14、：行共享 row share行排他 row exclusive共享 share共享行排他 share row exclusive排他 exclusive死锁：两个或两个以上的事务相互等待对方释放资源，从而形成死锁2.数据库对象oracle数据库对象又称模式对象数据库对象是逻辑结构的集合，最基本的数据库对象是表数据库对象：表，序列，视图，索引序列用于生成唯一，连续序号的对象。创建语法：create sequence user_id_seqstart with 1000increment by 1maxvalue 2000minvalue 1000nocyclecache 1000;-指定内存中预

15、先分配的序号访问序列：select user_id_seq.currval from dual;select user_id-seq.nextval from dual;更改删除序列：alter sequence user_id_seq maxvalue 10000;-不能修改其start with 值drop sequence user_id_seq;在Hibernate中访问序列： user_id_seq视图以经过定制的方式显示来自一个或多个表的数据创建视图：create or replace view user_tbl_view (vid,vname,vage)as select id,

16、user_name,age from user_tblwith check option|with read only;创建带有错误的视图：create force view user_tbl_force_view asselect * from user_table;-此时user_table可以不存在创建外联接视图：create view user_stu_view asselect u.id,u.user_name,u.password,s.ddressfrom user_tbl u,stu_tbl swhere u.s_id(+)=s.id;-哪一方带有(+)，哪一方就是次要的删除视图

17、：drop user_stu_view;索引用于提高SQL语句执行的性能索引类型：唯一索引，位图索引，组合索引，基于函数的索引，反向键索引创建标准索引：create index user_id_index on user_tbl(id) tablespace schooltbs;重建索引：alter index user_id_index rebuild;删除索引：drop index user_id_index;创建唯一索引：create unique index user_id_index on user_tbl(id);创建组合索引：create index name_pass_inde

18、x on user_tbl(user_name,password);创建反向键索引：create index user_id_index on user_tbl(id) reverse;四使用PL/SQL可用于创建存储过程，触发器，程序包，给SQL语句的执行添加程序逻辑。支持SQL，在PL/SQL中可以使用：数据操纵命令事务控制命令游标控制SQL函数和SQL运算符支持面向对象编程(OOP)可移植性更佳的性能，PL/SQL经过编译执行分为三个部分：声明部分，可执行部分和异常处理部分declaredeclarationsbeginexecutable statementsexception han

19、dlersend;打开输出set serverout on;-根据输入编号获取某学员的成绩-ifdeclare score user_tbl.score%type;beginselect score into score from user_tbl where id=&id;if score90 thendbms_output.put_line(优秀);elsif score80 thendbms_output.put_line(良好);elsif score60 thendbms_output.put_line(及格);else dbms_output.put_line(差);end if;

20、end;-根据学员姓名获取某学员的成绩-ifdeclare score user_tbl.score%type;beginselect score into score from user_tbl where user_name=&name;if score90 thendbms_output.put_line(优秀);elsif score80 thendbms_output.put_line(良好);elsif score60 thendbms_output.put_line(及格);else dbms_output.put_line(差);end if;end;-case的使用decla

21、regrade user_tbl.grade%type;beginselect grade into grade from user_tbl where id=&id;case gradewhen A then dbms_output.put_line(优异);when B then dbms_output.put_line(优秀);when C then dbms_output.put_line(良好);else dbms_output.put_line(一般);end case;end;-基本循环declare i number(4):=1;beginloopdbms_output.put

22、_line(loop size:|i);i:=i+1;exit when i10;end loop;end;-while循环declarei number(4):=1;beginwhile i=10 loopdbms_output.put_line(while loop size=|i);i:=i+1;end loop;end;-for循环declarei number(4):=1;beginfor i in 1.10 loopdbms_output.put_line(for loop Size:|i);end loop;end;declarei number(2):=1;j number(2

23、):=1;beginfor i in reverse 1.9 loopfor j in 1.i loopdbms_output.put(j|x|i|=|j*i| );end loop;dbms_output.put_line();end loop;end;-动态SQLdeclareuserId number(2);sql_str varchar2(100);userName user_tbl.user_name%type;beginexecute immediate create table testExe(id number,test_name varchar2(20);userId:=&u

24、serId;sql_str:=select user_name from user_tbl where id=:id;execute immediate sql_str into userName using userId;dbms_output.put_line(userName);end;(ordeclare id_param number:=&id_param; sql_str varchar2(100); name_param stu_tbl.stu_name%type; begin sql_str:=select stu_name from stu_tbl where id=:p;

25、execute immediate sql_str into name_param using id_param; dbms_output.put_line(name_param); end; /)-异常处理declaregrade number(4);begingrade:=&grade;case gradewhen 1 then dbms_output.put_line(好的);-else dbms_output.put_line(不好);end case;exceptionwhen case_not_found then dbms_output.put_line(输入类型不匹配！);en

26、d;-系统异常declarerowD user_tbl%rowtype;beginselect * into rowD from user_tbl;dbms_output.put_line(rowD.id|rowD.user_name| |rowD.password); exceptionwhen too_many_rows thendbms_output.put_line(不能将多行赋予一个属性！);end;ordeclarerowD user_tbl%rowtype;beginselect * into rowD from user_tbl where id=5;dbms_output.p

27、ut_line(rowD.id| |rowD.user_name| |rowD.password); exceptionwhen too_many_rows thendbms_output.put_line(不能将多行赋予一个属性！);when no_data_found thendbms_output.put_line(没有您要查找的数据！);end;-自定义错误declareinvalidError exception;category varchar2(20);begincategory:=&category;if category not in(附件,顶盘,备件) then raise invalidError;elsedbms_output.put_line(您输入的类别是：|category);end if;exceptionwhen invalidError thendbms_output.put_line(无法识别的类别！);end;-引发应用程序异常declareapp_exception exception;grade user_tbl.grade%type;beginselect grade into grade from user_tbl where id=&i