Oracle基础语法操作语句Word下载.docx

1、如果要插入更长的字符串,请考虑字段用CLOB类型,（有时候报错就是插入的数值太长的原因），方法借用ORACLE里自带的DBMS_LOB程序包。5.INSERT时如果要用到从1开始自动增长的序列号,应该先建立一个序列号CREATESEQUENCE序列号的名称（最好是表名+序列号标记）INCREMENTBY1STARTWITH，MAXVALUE99999CYCLENOCACHE，其中最大的值按字段的长度来定,如果定义的自动增长的序列号NUMBER（6）,最大值为999999。语句插入这个字段值为:序列号的名称.NEXTVAL（二）DELETE（删除数据表里记录的语句）DELETEFROM表名WHE

2、RE条件;1.删除记录并不能释放ORACLE里被占用的数据块表空间.它只把那些被删除的数据块标成unused。2.如果确实要删除一个大表里的全部记录,可以用TRUNCATE命令,它可以释放占用的数据块表空间。TABLE表名;此操作不可回退。（三）UPDATE（修改数据表里记录的语句）UPDATE表名SET字段名1=值1,字段名2=值2,1.如果修改的值N没有赋值或定义时,将把原来的记录内容清为NULL,最好在修改前进行非空校验;2.值N超过定义的长度会出错,（四）注意事项A.以上SQL语句对表都加上了行级锁，确认完成后,必须加上事物处理结束的命令COMMIT才能正式生效，否则改变不一定写入数据

3、库里。如果想撤回这些操作,可以用命令ROLLBACK复原。B.在运行INSERT,和UPDATE语句前最好估算一下可能操作的记录范围，应该把它限定在较小（一万条记录）范围内，否则ORACLE处理这个事物用到很大的回退段。程序响应慢甚至失去响应.如果记录数上十万以上这些操作,可以把这些SQL语句分段分次完成，其间加上COMMIT确认事物处理。二、数据定义（DDL）（一）CREATE（创建表,索引,视图,同义词,过程,函数,数据库链接等）ORACLE常用的字段类型有：CHAR固定长度的字符串VARCHAR2可变长度的字符串NUMBER（M,N）数字型；M是位数总长度,N是小数的长度、DATE日期类

4、型1.创建表时要把较小的不为空的字段放在前面,可能为空的字段放在后面2.创建表时可以用中文的字段名,但最好还是用英文的字段名3.创建表时可以给字段加上默认值,例如DEFAULTSYSDATE，这样每次插入和修改时,不用程序操作这个字段都能得到动作的时间。4.创建表时可以给字段加上约束条件，例如：不允许重复UNIQUE,关键字PRIMARYKEY（二）ALTER（改变表,视图等）1.改变表的名称ALTER表名1TO表名2;2.在表的后面增加一个字段TABLE表名ADD字段名字段名描述;3.修改表里字段的定义描述MODIFY字段名4.给表里的字段加上约束条件表名CONSTRAINT约束名KEY（字

5、段名）;UNIQUE5.把表放在或取出数据库的内存区CACHE;NOCACHE;（三）DROP（删除表,删除表和它所有的约束条件DROPCASCADECONSTRAINTS;（四）TRUNCATE（清空表里的所有记录,保留表的结构）三、查询语句（SELECT）SELECT字段名1,表名1,表名2,字段名可以带入函数例如:COUNT（*）,MIN（字段名）,MAX（字段名）,AVG（字段名）,DISTINCT（字段名）,TO_CHAR（DATE字段名,YYYY-MM-DDSS）1.NVL（EXPR1,EXPR2）函数解释:IFEXPR1=NULLRETURNEXPR2ELSEEXPR1DECOD

6、E（AAV1R1V2R2.）函数AA=V1THENR1AA=V2R2.NULL2.LPAD（char1,n,char2）函数字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位3.字段名之间可以进行算术运算（字段名1*字段名1）/34.查询语句可以嵌套（SELECTFROM表名1,条件）条件2;两个查询语句的结果可以做集合操作并集UNION（去掉重复记录）,并集UNIONALL（不去掉重复记录）,差集MINUS,交集INTERSECT5.分组查询GROUPBY字段名1HAVING条件;两个以上表之间的连接查询表名1.字段名=表名2.AND字段名（+）有（+）号的字段位置

7、自动补空值查询结果集的排序操作,默认的排序是升序ASC,降序是DESCORDERBY字段名1,字段名2DESC;字符串模糊比较的方法INSTR（字段名,字符串）0LIKE字符串%字符串%每个表都有一个隐含的字段ROWID,它标记着记录的唯一性.四、ORACLE里常用的数据对象（SCHEMA）（一）索引（INDEX）CREATEINDEX索引名ON字段1,字段2,）;索引名REBUILD;1.索引的概念：（1）类似书的目录结构（2）Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度（3）索引直接指向包含所查询值的行的位置，减少磁盘I/O（4）与所索引的表是相互独立的物理结

8、构（5）Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引一个表的索引最好不要超过三个（特殊的大表除外）,最好用单字段索引,结合SQL语句的分析执行情况,也可以建立多字段的组合索引和基于函数的索引2.唯一索引（1）何时创建：当某列任意两行的值都不相同（2）当建立Primary Key（主键）或者Unique constraint（唯一约束）时，唯一索引将被自动建立（3）语法：CREATE UNIQUE INDEX index ON table （column）;3.组合索引当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引（2）组合索引中列的顺序是任

9、意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面CREATE INDEX index on table（column, column.）;4.位图索引列中有非常多的重复的值时候。例如某列保存了“性别”信息，Where 条件中包含了很多OR操作符。较少的update操作，因为要相应的跟新所有的bitmap（2）结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE（1）、FALSE（0）、或NULL值。（3）优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多（4）语法：CREATE BITMAP INDEX index ON table （column,

10、 column.）;5.基于函数的索引在WHERE条件语句中包含函数或者表达式时（2）函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。CREATE INDEX index ON table （FUNCTION（column）;（二）视图（VIEW）VIEW视图名AS.;VIEW视图名COMPILE;视图仅是一个SQL查询语句,它可以把表之间复杂的关系简洁化。1.视图的特点（1）集中用户感兴趣的数据，通常用户只是对表中的某一部分数据感兴趣, 对其他的数据不是那么敏感, 所以用户通过视图就可以操纵自己所需的数据。对于开发人员来说, 也可以屏蔽一些数据。（2）掩码数据

11、库的复杂性，通过视图机制将数据库设计的复杂性与用户屏蔽分开, 这样用户通过视图的操作就可以达到简化对数据库的复杂操作。（3）简化用户的权限，由于视图只是基表的逻辑表, 所以通过视图可以将视图的权限和基表权限分离。（4）重组数据，视图可以来自多个基表，从而可以利用视图对数据进行进一步地分析。2.视图可以由以下任意一项组成（1）一个基表的任意子集（2）两个或两个以上的基表的合集（3）两个或两个以上基表的交集（4）一个或者多个基表运算的结果集合（5）另一个视图的子集3.创建视图的基本语法CREATE OR REPLACE FORCE NOFORCEVIEW view_name （column_nam

12、e）,.nAS Select_statementWITH CHECK OPTIONCONSTRAINT constraint_nameWITH READ ONLY字段说明:view_name : 视图的名字column_name: 视图中的列名在下列情况下 , 必须指定视图列的名称：A.由算术表达式 , 系统内置函数或者常量得到的列；B.共享同一个表名连接得到的列；C.希望视图中的列名与表中的列名不同的时候REPLACE: 如果创建视图时, 已经存在此视图, 则重新创建此视图, 相当于覆盖FORCE: 强制创建视图，无论的视图所依赖的基表否存在或是否有权限创建NOFORCE: 只有基表存在且具

13、有创建视图权限时, 才可以创建视图WITH CHECK OPTION 指出在视图上所进行的修改都要符合select_statement 所指定的限制条件WITH READ ONLY 只允许查看视图4.视图的定义原则： （1）视图的查询可以使用复杂的 SELECT 语法，包括连接 / 分组查询和子查询（2）在没有 WITH CHECK OPTION 和 READ ONLY 的情况下，查询中不能使用 ORDER BY 子句（3）如果没有为 CHECK OPTION 约束命名，系统会自动为之命名，形式为 SYS_Cn （4）OR REPLACE 选项可以不删除原视图便可更改其定义并重建，或重新授予对

14、象权限。5.视图操作操作:视图分为简单视图（ 基于单个基表, 且不包含函数和数据分组操作 ） 和复杂视图（ 基于多个基表或视图）.简单视图可以通过视图修改数据，这些修改包括插入数据、更新数据和删除数据，但是对于复杂视图来说，通过视图修改数据必须满足一定的规则。在视图定义中没有设定READ ONLY 的前提下, 如果视图包含了下面的内容, 那么不能通过视图删除表中的数据。分组函数, 如SUM,AVG,MIN,MAX 等、GROUP BY 子句、包含了表达式ROWNUM 伪列插入数据时, 除了满足上面的条件外, 还需要保证那些没有包含在视图定义中的基表的列必须允许空值。如果在视图定义中还包含了WI

15、TH CHECK OPTION 子句, 那么对视图的修改除了前面的那些原则外, 还必须满足指定的约束条件。（1）查询视图: 可依赖于多个基表.SELECT *FROM view_name; /* 类似于查询表数据*/（2）更新视图的前提:没有使用连接函数, 集合运算和组函数。创建视图的select 语句中没有集合函数且没有GROUP BY,ONNECT BY ,START WITH 子句及DISTINCT 关键字，创建视图的SELECT 语句中不包含从基表列通过计算所得的列，创建视图没有包含只读属性。（3）插入数据INSERT INTO view_name VALUES（）;（4）修改数据:U

16、PDATE view_name SET 若一个视图依赖于多个基本表, 则一次修改该视图只能修改一个基本表的数据.（5）删除数据:Delete from view_name where 同样, 当视图依赖多个基表时, 不能使用此语句来删除基表中的数据. 只能删除依赖一个基表的数据。（6）修改视图定义:所有相关的权限都依然存在. 语法同创建视图相同.（7）删除视图:DROP VIEW view_name;只有视图所有者和具备DROP VIEW 权限的用户可以删除视图。删除视图的定义不影响基表中的数据，视图被删除后，基于被删除视图的其他视图或应用将无效。（三）同义词（SYNONMY）SYNONYM同

17、义词名FOR表名数据库链接名;相当于alias（别名），例如：user1.table1在用户账户user2中建一个同义词table1 create synonym table1 for user1.table1;在user2中查select * from table1时就相当于查select * from user1.table1;在oracle中对用户的管理是使用权限的方式来管理的，也就是说，如果我们想使用数据库，我们就必须得有权限，但是如果是别人将权限授予我们，我们也是能对数据库进行操作的，但是我们必须要已授权的表的名称前键入该表所有者的名称，所以这就是比较麻烦的，遇到这种情况，我们该怎么

18、办呢？创建个同义词，这样我们就可以直接使用同义词来使用表了。（四）数据库链接（DATABASELINK）DATABASELINK数据库链接名CONNECT用户名IDENTIFIED密码USING数据库连接字符串;数据库连接字符串可以用NET8EASYCONFIG或者直接修改TNSNAMES.ORA里定义.数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样数据库全局名称可以用以下命令查出*GLOBAL_NAME;查询远端数据库里的表五、权限管理（DCL）语句（一）GRANT赋于权限常用的系统权限集合有以下三个:CONNECT（基本的连接）,RESOURCE（程序开发）,DBA（数据库管理）常用的数据对象权限有以下五个:ALLON数据对象名,数据对象名,DELETE数据对象名GRANTCONNECT,RESOURCE用户名;SELECT,INSERT,ON表名用户名1,用户名2;（二）REVOKE回收权限REVOKE