ORACLE 常用的SQL语法和数据对象Word下载.docx

1、TO_DATE（）还有很多种日期格式, 可以参看ORACLE DOC. 年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SSI NSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,方法借用ORACLE里自带的DBMS_LOB程序包.INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号CREATE SEQUENCE 序列号的名称 （最好是表名+序列号标记） INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;其中最大的值按字段的长

2、度来定, 如果定义的自动增长的序列号 NUMBER（6） , 最大值为999999INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL2.DELETE （删除数据表里记录的语句）DELETE FROM表名 WHERE 条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间TRUNCATE TABLE 表名;此操作不可回退.3.UPDATE （修改数据表里记录的语句）UPDATE表名 SET 字段名1=值1, 字段名2=值2,

3、WHERE 条件;如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;值N超过定义的长度会出错, 最好在插入前进行长度校验.注意事项:A. 以上SQL语句对表都加上了行级锁, 确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效, 否则改变不一定写入数据库里. 如果想撤回这些操作, 可以用命令 ROLLBACK 复原.B. 在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围, 应该把它限定在较小 （一万条记录） 范围内,. 否则ORACLE处理这个事物用到很大的回退段. 程序响应慢甚至失去响应.

4、如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理.二.数据定义 （DDL） 部分1.CREATE （创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等）ORACLE常用的字段类型有CHAR 固定长度的字符串VARCHAR2 可变长度的字符串NUMBER（M,N） 数字型M是位数总长度, N是小数的长度DATE 日期类型创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面创建表时可以用中文的字段名, 但最好还是用英文的字段名创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE这样每次插入和修改时, 不

5、用程序操作这个字段都能得到动作的时间创建表时可以给字段加上约束条件例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY 2.ALTER （改变表, 索引, 视图等）改变表的名称Rename 表名1 TO 表名2 ;在表的后面增加一个字段ALTER TABLE表名 ADD 字段名 字段名描述;修改表里字段的定义描述ALTER TABLE表名 MODIFY字段名 字段名描述;给表里的字段加上约束条件ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY （字段名）;ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE （字

6、段名）;把表放在或取出数据库的内存区ALTER TABLE 表名 CACHE;ALTER TABLE 表名 NOCACHE;3.DROP （删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等）删除表和它所有的约束条件DROP TABLE 表名 CASCADE CONSTRAINTS;4.TRUNCATE （清空表里的所有记录, 保留表的结构）TRUNCATE table 表名;三.查询语句 （SELECT） 部分SELECT字段名1, 字段名2, FROM 表名1, 表名2, WHERE 条件;字段名可以带入函数 例如: COUNT（*）, MIN（字段名）, MAX（字段名）,

7、 AVG（字段名）, DISTINCT（字段名）, TO_CHAR（DATE字段名,YYYY-MM-DD HH24:SS）NVL（EXPR1, EXPR2）函数解释:IF EXPR1=NULL RETURN EXPR2ELSE RETURN EXPR1DECODE（AAV1R1V2R2.）函数IF AA=V1 THEN RETURN R1IF AA=V2 THEN RETURN R2.RETURN NULLLPAD（char1,n,char2）函数字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位字段名之间可以进行算术运算例如: （字段名1*字段名1）/3查询语句可以

8、嵌套 SELECT FROM （SELECT FROM表名1, 表名2, WHERE 条件） WHERE 条件2;两个查询语句的结果可以做集合操作 并集UNION（去掉重复记录）, 并集UNION ALL（不去掉重复记录）, 差集MINUS, 交集INTERSECT分组查询SELECT字段名1, 字段名2, FROM 表名1, 表名2, GROUP BY字段名1 HAVING 条件 ;两个以上表之间的连接查询SELECT字段名1, 字段名2, FROM 表名1, 表名2, WHERE 表名1.字段名 = 表名2. 字段名 AND ; 表名1.字段名 = 表名2. 字段名（+） AND ;有（+

9、）号的字段位置自动补空值查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESCSELECT字段名1, 字段名2, FROM 表名1, 表名2, ORDER BY字段名1, 字段名2 DESC;字符串模糊比较的方法INSTR（字段名, 字符串）0 字段名 LIKE 字符串% %字符串%每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性. 四.ORACLE里常用的数据对象 （SCHEMA）1.索引 （INDEX）CREATE INDEX 索引名ON 表名 （ 字段1, 字段2, ）;ALTER INDEX 索引名 REBUILD;一个表的索引最好不要超过三个 （特殊的大表除外）,

10、 最好用单字段索引, 结合SQL语句的分析执行情况, 也可以建立多字段的组合索引和基于函数的索引ORACLE8.1.7字符串可以索引的最大长度为1578 单字节ORACLE8.0.6字符串可以索引的最大长度为758 单字节ORACLE DOC上说字符串最大可以建索引的长度约是:数据块的大小（db_block_size）*40%2.视图 （VIEW）CREATE VIEW 视图名AS SELECT . FROM .;ALTER VIEW视图名 COMPILE;视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.3.同义词 （SYNONMY）CREATE SYNONYM同义词名FOR 表

11、名;CREATE SYNONYM同义词名FOR 表名数据库链接名;4.数据库链接 （DATABASE LINK）CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING 数据库连接字符串;数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样数据库全局名称可以用以下命令查出SELECT * FROM GLOBAL_NAME;查询远端数据库里的表SELECT FROM 表名数据库链接名;五.权限管理 （

12、DCL） 语句1.GRANT 赋于权限常用的系统权限集合有以下三个:CONNECT（基本的连接）, RESOURCE（程序开发）, DBA（数据库管理）常用的数据对象权限有以下五个:ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名GRANT CONNECT, RESOURCE TO 用户名;GRANT SELECT ON 表名 TO 用户名;GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;2.REVOKE 回收权限REVOKE CONNECT, RESOURCE FROM 用户名;REVOKE SELECT ON 表名 FROM 用户名;REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;