Oracle基础语法操作语句.docx
- 文档编号:3884175
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:9
- 大小:21.87KB
Oracle基础语法操作语句.docx
《Oracle基础语法操作语句.docx》由会员分享,可在线阅读,更多相关《Oracle基础语法操作语句.docx(9页珍藏版)》请在冰豆网上搜索。
Oracle基础语法操作语句
Oracle基础语法_操作语句
一、数据控制语句 (DML) 部分
(一)INSERT (往数据表里插入记录的语句)
INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);
INSERT INTO 表名(字段名1, 字段名2, ……) SELECT (字段名1, 字段名2, ……) FROM 另外的表名;
注意:
1.字符串类型的字段值必须用单引号括起来,例如:
‘GOOD DAY’,如果字段值里包含单引号’ 需要进行字符串转换,我们把它替换成两个单引号’’。
问题:
插入表的字段值含有’’如何转换之后插进去
2.字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验。
3.日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’),TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC。
年-月-日 小时:
分钟:
秒 的格式YYYY-MM-DD HH24:
MI:
SS
4.INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,(有时候报错就是插入的数值太长的原因),方法借用ORACLE里自带的DBMS_LOB程序包。
5.INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 ,MAXVALUE 99999 CYCLE NOCACHE,其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999。
INSERT 语句插入这个字段值为:
序列号的名称.NEXTVAL
(二)DELETE (删除数据表里记录的语句)
DELETE FROM表名 WHERE 条件;
注意:
1.删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused。
2.如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间。
TRUNCATE TABLE 表名; 此操作不可回退。
(三)UPDATE (修改数据表里记录的语句)
UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;
注意:
1.如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;
2.值N超过定义的长度会出错, 最好在插入前进行长度校验。
(四)注意事项
A. 以上SQL语句对表都加上了行级锁,确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效,否则改变不一定写入数据库里。
如果想撤回这些操作, 可以用命令 ROLLBACK 复原。
B. 在运行INSERT, DELETE 和 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 TABLE 表名1 TO 表名2;
2.在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名 字段名描述;
3.修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;
4.给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
5.把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;
(三)DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)
删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;
(四)TRUNCATE (清空表里的所有记录, 保留表的结构)
TRUNCATE 表名;
三、查询语句 (SELECT) 部分
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件;
字段名可以带入函数
例如:
COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名),
TO_CHAR(DATE字段名,’YYYY-MM-DD HH24:
MI:
SS’)
1.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
2.LPAD(char1,n,char2)函数
解释:
字符char1按制定的位数n显示,不足的位数用char2字符串替换左边的空位
3.字段名之间可以进行算术运算
例如:
(字段名1*字段名1)/3
4.查询语句可以嵌套
例如:
SELECT …… FROM
(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;
两个查询语句的结果可以做集合操作
例如:
并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS, 交集INTERSECT
5.分组查询
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)
(一)索引 (INDEX)
CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );
ALTER INDEX 索引名 REBUILD;
1.索引的概念:
(1)类似书的目录结构
(2)Oracle的“索引”对象,与表关联的可选对象,提高SQL查询语句的速度
(3)索引直接指向包含所查询值的行的位置,减少磁盘I/O
(4)与所索引的表是相互独立的物理结构
(5)Oracle自动使用并维护索引,插入、删除、更新表后,自动更新索引
一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况, 也可以建立多字段的组合索引和基于函数的索引
2.唯一索引
(1)何时创建:
当某列任意两行的值都不相同
(2)当建立PrimaryKey(主键)或者Uniqueconstraint(唯一约束)时,唯一索引将被自动建立
(3)语法:
CREATEUNIQUEINDEXindexONtable(column);
3.组合索引
(1)何时创建:
当两个或多个列经常一起出现在where条件中时,则在这些列上同时创建组合索引
(2)组合索引中列的顺序是任意的,也无需相邻。
但是建议将最频繁访问的列放在列表的最前面
(3)语法:
CREATEINDEXindexontable(column[,column]...);
4.位图索引
(1)何时创建:
列中有非常多的重复的值时候。
例如某列保存了“性别”信息,Where条件中包含了很多OR操作符。
较少的update操作,因为要相应的跟新所有的bitmap
(2)结构:
位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE
(1)、FALSE(0)、或NULL值。
(3)优点:
位图以一种压缩格式存放,因此占用的磁盘空间比标准索引要小得多
(4)语法:
CREATEBITMAPINDEXindexONtable(column[,column]...);
5.基于函数的索引
(1)何时创建:
在WHERE条件语句中包含函数或者表达式时
(2)函数包括:
算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。
(3)语法:
CREATEINDEXindexONtable(FUNCTION(column));
(二)视图 (VIEW)
CREATE VIEW 视图名AS SELECT ….. FROM …..;
ALTER 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)
CREATE SYNONYM同义词名FOR 表名;
CREATE SYNONYM同义词名FOR 表名@数据库链接名;
相当于alias(别名),例如:
user1.table1在用户账户user2中建一个同义词table1
createsynonymtable1foruser1.table1;
在user2中查select*fromtable1时就相当于查select*fromuser1.table1;
在oracle中对用户的管理是使用权限的方式来管理的,也就是说,如果我们想使用数据库,我们就必须得有权限,但是如果是别人将权限授予我们,我们也是能对数据库进行操作的,但是我们必须要已授权的表的名称前键入该表所有者的名称,所以这就是比较麻烦的,遇到这种情况,我们该怎么办呢?
创建个同义词,这样我们就可以直接使用同义词来使用表了。
(四)数据库链接 (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) 语句
(一)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;
(二)REVOKE 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 基础 语法 操作 语句