ORACLE
常用的
SQL
语法和数据对象
一
.
数据控制语句
(DML)
部分
1.INSERT
(
往数据表里插入记录的语句
)
INSERT INTO
表名
(
字段名
1,
字段名
2, ……) VALUES (
值
1,
值
2, ……);
INSERT INTO
表名
(
字段名
1,
字段名
2, ……)
SELECT (
字段名
1,
字段名
2, ……) FROM
另外的表名
;
字符串类型的字段值必须用单引号括起来
,
例如
: ’GOOD DAY’
如果字段值里包含单引号
’
需要进行字符串转换
,
我们把它替换成两个单引号
''.
字符串类型的字段值超过定义的长度会出错
,
最好在插入前进行长度校验
.
日期字段的字段值可以用当前数据库的系统时间
SYSDATE,
精确到秒
或者用字符串转换成日期型函数
TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE()
还有很多种日期格式
,
可以参看
ORACLE DOC.
年
-
月
-
日 小时
:
分钟
:
秒 的格式
YYYY-MM-DD HH24:MI:SS
INSERT
时最大可操作的字符串长度小于等于
4000
个单字节
,
如果要插入更长的字符串
,
请考虑字段用
CLOB
类型
,
方法借用
ORACLE
里自带的
DBMS_LOB
程序包
.
INSERT
时如果要用到从
1
开始自动增长的序列号
,
应该先建立一个序列号
CREATE SEQUENCE
序列号的名称
(
最好是表名
+
序列号标记
) INCREMENT BY 1
START
WITH
1
MAXVALUE
99999
CYCLE
NOCACHE;
其中最大的值按字段的长度来定
,
如果定义的自动增长的序列号
NUMBER(6) ,
最大值为
999999
INSERT
语句插入这个字段值为
:
序列号的名称
.NEXTVAL
2.DELETE
(
删除数据表里记录的语句
)
DELETE FROM
表名
WHERE
条件
;
注意：删除记录并不能释放
ORACLE
里被占用的数据块表空间
.
它只把那些被删除的数据块标成
unused.
如果确实要删除一个大表里的全部记录
,
可以用
TRUNCATE
命令
,
它可以释放占用的数据块表空间
TRUNCATE TABLE
表名
;
此操作不可回退
.
3.UPDATE
(
修改数据表里记录的语句
)
UPDATE
表名
SET
字段名
1=
值
1,
字段名
2=
值
2, …… WHERE
条件
;
如果修改的值
N
没有赋值或定义时
,
将把原来的记录内容清为
NULL,
最好在修改前进行非空校验
;
值
N
超过定义的长度会出错
,
最好在插入前进行长度校验
..
注意事项
:
A.
以上
SQL
语句对表都加上了行级锁
,
确认完成后
,
必须加上事物处理结束的命令
COMMIT
才能正式生效
,
否则改变不一定写入数据库里
.
如果想撤回这些操作
,
可以用命令
ROLLBACK
复原
.
B.
在运行
INSERT, DELETE
和
UPDATE
语句前最好估算一下可能操作的记录范围
,
应该把它限定在较小
(
一万条记录
)
范围内
,.
否则
ORACLE
处理这个事物用到很大的回退段
.
程序响应慢甚至失去响应
.
如果记录数上十万以上这些操作
,
可以把这些
SQL
语句分段分次完成
,
其间加上
COMMIT
确认事物处理
.
二
.
数据定义
(DDL)
部分
1.CREATE (
创建表
,
索引
,
视图
,
同义词
,
过程
,
函数
,
数据库链接等
)
ORACLE
常用的字段类型有
CHAR
固定长度的字符串
VARCHAR2
可变长度的字符串
NUMBER(M,N)
数字型
M
是位数总长度
, N
是小数的长度
DATE
日期类型
创建表时要把较小的不为空的字段放在前面
,
可能为空的字段放在后面
创建表时可以用中文的字段名
,
但最好还是用英文的字段名
创建表时可以给字段加上默认值
,
例如
DEFAULT SYSDATE
这样每次插入和修改时
,
不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件
例如 不允许重复
UNIQUE,
关键字
PRIMARY KEY
2.ALTER
(
改变表
,
索引
,
视图等
)
改变表的名称
ALTER TABLE
表名
1
TO
表名
2;
在表的后面增加一个字段
ALTER TABLE
表名
ADD
字段名 字段名描述
;
修改表里字段的定义描述
ALTER TABLE
表名
MODIFY
字段名 字段名描述
;
给表里的字段加上约束条件
ALTER TABLE
表名
ADD CONSTRAINT
约束名
PRIMARY KEY (
字段名
);
ALTER TABLE
表名
ADD CONSTRAINT
约束名
UNIQUE (
字段名
);
把表放在或取出数据库的内存区
ALTER TABLE
表名
CACHE;
ALTER TABLE
表名
NOCACHE;
3.DROP
(
删除表
,
索引
,
视图
,
同义词
,
过程
,
函数
,
数据库链接等
)
删除表和它所有的约束条件
DROP TABLE
表名
CASCADE CONSTRAINTS;
4.TRUNCATE (
清空表里的所有记录
,
保留表的结构
)
TRUNCATE
表名
;
三
.
查询语句
(SELECT)
部分
SELECT
字段名
1,
字段名
2, …… FROM
表名
1, [
表名
2, ……] WHERE
条件
;
字段名可以带入函数
例如
:
COUNT(*), MIN(
字段名
),
MAX(
字段名
),
AVG(
字段名
), DISTINCT(
字段名
),
TO_CHAR(DATE
字段名
,'YYYY-MM-DD HH24:MI:SS')
NVL(EXPR1, EXPR2)
函数
解释
:
IF EXPR1=NULL
RETURN EXPR2
ELSE
RETURN EXPR1
DECODE(AA
﹐
V1
﹐
R1
﹐
V2
﹐
R2....)
函数
解释
:
IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
LPAD(char1,n,char2)
函数
解释
:
字符
char1
按制定的位数
n
显示，不足的位数用
char2
字符串替换左边的空位
字段名之间可以进行算术运算
例如
:
(
字段名
1*
字段名
1)/3
查询语句可以嵌套
例如
: 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 ……] ;
SELECT
字段名
1,
字段名
2, …… FROM
表名
1, [
表名
2, ……] WHERE
表名
1.
字段名
=
表名
2.
字段名
(+) [ AND ……] ;
有
(+)
号的字段位置自动补空值
查询结果集的排序操作
,
默认的排序是升序
ASC,
降序是
DESC
SELECT
字段名
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;
一个表的索引最好不要超过三个
(
特殊的大表除外
),
最好用单字段索引
,
结合
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
表名
;
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
表名
@
数据库链接名
;
五
.
权限管理
(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;
本文来自: 中国自学编程网(www.zxbc.cn) 详细出处参考：http://www.zxbc.cn/html/20071020/28290_3.html
本文转自左正博客园博客，原文链接：http://www.cnblogs.com/soundcode/archive/2011/01/03/1924366.html，如需转载请自行联系原作者
