Oracle基础语法操作语句Word下载.docx
- 文档编号:16790395
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:9
- 大小:21.87KB
Oracle基础语法操作语句Word下载.docx
《Oracle基础语法操作语句Word下载.docx》由会员分享,可在线阅读,更多相关《Oracle基础语法操作语句Word下载.docx(9页珍藏版)》请在冰豆网上搜索。
如果要插入更长的字符串,
请考虑字段用CLOB类型,(有时候报错就是插入的数值太长的原因),方法借用ORACLE里自带的DBMS_LOB程序包。
5.INSERT时如果要用到从1开始自动增长的序列号,
应该先建立一个序列号CREATE
SEQUENCE
序列号的名称
(最好是表名+序列号标记)
INCREMENT
BY
1
START
WITH
,MAXVALUE
99999
CYCLE
NOCACHE,其中最大的值按字段的长度来定,
如果定义的自动增长的序列号
NUMBER(6)
最大值为999999。
语句插入这个字段值为:
序列号的名称.NEXTVAL
(二)DELETE
(删除数据表里记录的语句)
DELETE
FROM表名
WHERE
条件;
1.删除记录并不能释放ORACLE里被占用的数据块表空间.
它只把那些被删除的数据块标成unused。
2.如果确实要删除一个大表里的全部记录,
可以用
TRUNCATE
命令,
它可以释放占用的数据块表空间。
TABLE
表名;
此操作不可回退。
(三)UPDATE
(修改数据表里记录的语句)
UPDATE表名
SET
字段名1=值1,
字段名2=值2,
……
1.如果修改的值N没有赋值或定义时,
将把原来的记录内容清为NULL,
最好在修改前进行非空校验;
2.值N超过定义的长度会出错,
(四)注意事项
A.
以上SQL语句对表都加上了行级锁,确认完成后,
必须加上事物处理结束的命令
COMMIT
才能正式生效,否则改变不一定写入数据库里。
如果想撤回这些操作,
可以用命令
ROLLBACK
复原。
B.
在运行INSERT,
和
UPDATE
语句前最好估算一下可能操作的记录范围,应该把它限定在较小
(一万条记录)
范围内,否则ORACLE处理这个事物用到很大的回退段。
程序响应慢甚至失去响应.
如果记录数上十万以上这些操作,
可以把这些SQL语句分段分次完成,其间加上COMMIT
确认事物处理。
二、数据定义
(DDL)
(一)CREATE
(创建表,
索引,
视图,
同义词,
过程,
函数,
数据库链接等)
ORACLE常用的字段类型有:
CHAR
固定长度的字符串
VARCHAR2
可变长度的字符串
NUMBER(M,N)
数字型;
M是位数总长度,
N是小数的长度、DATE
日期类型
1.创建表时要把较小的不为空的字段放在前面,
可能为空的字段放在后面
2.创建表时可以用中文的字段名,
但最好还是用英文的字段名
3.创建表时可以给字段加上默认值,
例如
DEFAULT
SYSDATE,这样每次插入和修改时,
不用程序操作这个字段都能得到动作的时间。
4.创建表时可以给字段加上约束条件,例如:
不允许重复
UNIQUE,
关键字
PRIMARY
KEY
(二)ALTER
(改变表,
视图等)
1.改变表的名称
ALTER
表名1
TO
表名2;
2.在表的后面增加一个字段
TABLE表名
ADD
字段名
字段名描述;
3.修改表里字段的定义描述
MODIFY字段名
4.给表里的字段加上约束条件
表名
CONSTRAINT
约束名
KEY
(字段名);
UNIQUE
5.把表放在或取出数据库的内存区
CACHE;
NOCACHE;
(三)DROP
(删除表,
删除表和它所有的约束条件
DROP
CASCADE
CONSTRAINTS;
(四)TRUNCATE
(清空表里的所有记录,
保留表的结构)
三、查询语句
(SELECT)
SELECT字段名1,
表名1,
[表名2,
……]
字段名可以带入函数
例如:
COUNT(*),
MIN(字段名),
MAX(字段名),
AVG(字段名),
DISTINCT(字段名),
TO_CHAR(DATE字段名,’YYYY-MM-DD
SS’)
1.NVL(EXPR1,
EXPR2)函数
解释:
IF
EXPR1=NULL
RETURN
EXPR2
ELSE
EXPR1
DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数
AA=V1
THEN
R1
AA=V2
R2
..…
NULL
2.LPAD(char1,n,char2)函数
字符char1按制定的位数n显示,不足的位数用char2字符串替换左边的空位
3.字段名之间可以进行算术运算
(字段名1*字段名1)/3
4.查询语句可以嵌套
(SELECT
FROM表名1,
条件)
条件2;
两个查询语句的结果可以做集合操作
并集UNION(去掉重复记录),
并集UNION
ALL(不去掉重复记录),
差集MINUS,
交集INTERSECT
5.分组查询
GROUP
BY字段名1
[HAVING
条件]
;
两个以上表之间的连接查询
表名1.字段名
=
表名2.
[
AND
字段名(+)
有(+)号的字段位置自动补空值
查询结果集的排序操作,
默认的排序是升序ASC,
降序是DESC
ORDER
BY字段名1,
字段名2
DESC;
字符串模糊比较的方法
INSTR(字段名,
‘字符串’)>
0
LIKE
‘字符串%’
[‘%字符串%’]
每个表都有一个隐含的字段ROWID,
它标记着记录的唯一性.
四、ORACLE里常用的数据对象
(SCHEMA)
(一)索引
(INDEX)
CREATE
INDEX
索引名ON
字段1,
[字段2,
);
索引名
REBUILD;
1.索引的概念:
(1)类似书的目录结构
(2)Oracle的“索引”对象,与表关联的可选对象,提高SQL查询语句的速度
(3)索引直接指向包含所查询值的行的位置,减少磁盘I/O
(4)与所索引的表是相互独立的物理结构
(5)Oracle自动使用并维护索引,插入、删除、更新表后,自动更新索引
一个表的索引最好不要超过三个
(特殊的大表除外),
最好用单字段索引,
结合SQL语句的分析执行情况,
也可以建立多字段的组合索引和基于函数的索引
2.唯一索引
(1)何时创建:
当某列任意两行的值都不相同
(2)当建立PrimaryKey(主键)或者Uniqueconstraint(唯一约束)时,唯一索引将被自动建立
(3)语法:
CREATEUNIQUEINDEXindexONtable(column);
3.组合索引
当两个或多个列经常一起出现在where条件中时,则在这些列上同时创建组合索引
(2)组合索引中列的顺序是任意的,也无需相邻。
但是建议将最频繁访问的列放在列表的最前面
CREATEINDEXindexontable(column[,column]...);
4.位图索引
列中有非常多的重复的值时候。
例如某列保存了“性别”信息,Where条件中包含了很多OR操作符。
较少的update操作,因为要相应的跟新所有的bitmap
(2)结构:
位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE
(1)、FALSE(0)、或NULL值。
(3)优点:
位图以一种压缩格式存放,因此占用的磁盘空间比标准索引要小得多
(4)语法:
CREATEBITMAPINDEXindexONtable(column[,column]...);
5.基于函数的索引
在WHERE条件语句中包含函数或者表达式时
(2)函数包括:
算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。
CREATEINDEXindexONtable(FUNCTION(column));
(二)视图
(VIEW)
VIEW
视图名AS
…..
…..;
VIEW视图名
COMPILE;
视图仅是一个SQL查询语句,
它可以把表之间复杂的关系简洁化。
1.视图的特点
(1)集中用户感兴趣的数据,通常用户只是对表中的某一部分数据感兴趣,对其他的数据不是那么敏感,所以用户通过视图就可以操纵自己所需的数据。
对于开发人员来说,也可以屏蔽一些数据。
(2)掩码数据库的复杂性,通过视图机制将数据库设计的复杂性与用户屏蔽分开,这样用户通过视图的操作就可以达到简化对数据库的复杂操作。
(3)简化用户的权限,由于视图只是基表的逻辑表,所以通过视图可以将视图的权限和基表权限分离。
(4)重组数据,视图可以来自多个基表,从而可以利用视图对数据进行进一步地分析。
2.视图可以由以下任意一项组成
(1)一个基表的任意子集
(2)两个或两个以上的基表的合集
(3)两个或两个以上基表的交集
(4)一个或者多个基表运算的结果集合
(5)另一个视图的子集
3.创建视图的基本语法
CREATE[ORREPLACE][FORCE][NOFORCE]VIEWview_name[(column_name)[,….n]]
ASSelect_statement
[WITHCHECKOPTION[CONSTRAINTconstraint_name]]
[WITHREADONLY]
字段说明:
view_name:
视图的名字
column_name:
视图中的列名
在下列情况下,必须指定视图列的名称:
A.由算术表达式,系统内置函数或者常量得到的列;
B.共享同一个表名连接得到的列;
C.希望视图中的列名与表中的列名不同的时候
REPLACE:
如果创建视图时,已经存在此视图,则重新创建此视图,相当于覆盖
FORCE:
强制创建视图,无论的视图所依赖的基表否存在或是否有权限创建
NOFORCE:
只有基表存在且具有创建视图权限时,才可以创建视图
WITHCHECKOPTION指出在视图上所进行的修改都要符合select_statement所指定的限制条件
WITHREADONLY只允许查看视图
4.视图的定义原则:
(1)视图的查询可以使用复杂的SELECT语法,包括连接/分组查询和子查询
(2)在没有WITHCHECKOPTION和READONLY的情况下,查询中不能使用ORDERBY子句
(3)如果没有为CHECKOPTION约束命名,系统会自动为之命名,形式为SYS_Cn
(4)ORREPLACE选项可以不删除原视图便可更改其定义并重建,或重新授予对象权限。
5.视图操作操作:
视图分为简单视图(基于单个基表,且不包含函数和数据分组操作)和复杂视图(基于多个基表或视图).
简单视图可以通过视图修改数据,这些修改包括插入数据、更新数据和删除数据,但是对于复杂视图来说,通过视图修改数据必须满足一定的规则。
在视图定义中没有设定READONLY的前提下,如果视图包含了下面的内容,那么不能通过视图删除表中的数据。
分组函数,如SUM,AVG,MIN,MAX等、GROUPBY子句、包含了表达式
ROWNUM伪列
插入数据时,除了满足上面的条件外,还需要保证那些没有包含在视图定义中的基表的列必须允许空值。
如果在视图定义中还包含了WITHCHECKOPTION子句,那么对视图的修改除了前面的那些原则外,还必须满足指定的约束条件。
(1)查询视图:
可依赖于多个基表.
SELECT*FROMview_name;
/*类似于查询表数据*/
(2)更新视图的前提:
没有使用连接函数,集合运算和组函数。
创建视图的select语句中没有集合函数且没有GROUPBY,ONNECTBY,STARTWITH子句及DISTINCT关键字,创建视图的SELECT语句中不包含从基表列通过计算所得的列,创建视图没有包含只读属性。
(3)插入数据
INSERTINTOview_nameVALUES();
(4)修改数据:
UPDATEview_nameSET…
若一个视图依赖于多个基本表,则一次修改该视图只能修改一个基本表的数据.
(5)删除数据:
Deletefromview_namewhere…
同样,当视图依赖多个基表时,不能使用此语句来删除基表中的数据.只能删除依赖一个基表的数据。
(6)修改视图定义:
所有相关的权限都依然存在.语法同创建视图相同.
(7)删除视图:
DROPVIEWview_name;
只有视图所有者和具备DROPVIEW权限的用户可以删除视图。
删除视图的定义不影响基表中的数据,视图被删除后,基于被删除视图的其他视图或应用将无效。
(三)同义词
(SYNONMY)
SYNONYM同义词名FOR
表名@数据库链接名;
相当于alias(别名),例如:
user1.table1在用户账户user2中建一个同义词table1
createsynonymtable1foruser1.table1;
在user2中查select*fromtable1时就相当于查select*fromuser1.table1;
在oracle中对用户的管理是使用权限的方式来管理的,也就是说,如果我们想使用数据库,我们就必须得有权限,但是如果是别人将权限授予我们,我们也是能对数据库进行操作的,但是我们必须要已授权的表的名称前键入该表所有者的名称,所以这就是比较麻烦的,遇到这种情况,我们该怎么办呢?
创建个同义词,这样我们就可以直接使用同义词来使用表了。
(四)数据库链接
(DATABASE
LINK)
DATABASE
LINK数据库链接名CONNECT
用户名
IDENTIFIED
密码
USING
‘数据库连接字符串’;
数据库连接字符串可以用NET8
EASY
CONFIG或者直接修改TNSNAMES.ORA里定义.
数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样
数据库全局名称可以用以下命令查出
*
GLOBAL_NAME;
查询远端数据库里的表
五、权限管理
(DCL)
语句
(一)GRANT
赋于权限
常用的系统权限集合有以下三个:
CONNECT(基本的连接),
RESOURCE(程序开发),
DBA(数据库管理)
常用的数据对象权限有以下五个:
ALL
ON
数据对象名,
数据对象名,DELETE
数据对象名
GRANT
CONNECT,
RESOURCE
用户名;
SELECT,
INSERT,
ON表名
用户名1,
用户名2;
(二)REVOKE
回收权限
REVOKE
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 基础 语法 操作 语句