Oracle基础知识

admin管理员组

文章数量:1794759

Oracle基础知识

DML（data manipulation language）：数据操作语言        它们是select、update、insert、delete，就象它的名字一样 DDL（data definition language）：数据定义语言        主要的命令有create、alter、drop、truncate等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用 DCL（Data Control Language）：数据控制语言。     是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL 详细解释： 一、DDL：数据定义语言（没有delete，下面只是为了方便用了delete!!!!)

      1.1.、表的创建和删除

--供应商表 CREATE TABLE s (s_no VARCHAR(5) NOT NULL, --供应商代码 s_name VARCHAR(30) UNIQUE, --供应商名 s_status VARCHAR(20), --供应商状态 PRIMARY KEY(s_no)); --零件表 CREATE TABLE p (p_no VARCHAR(5) NOT NULL PRIMARY KEY, --零件号 p_name VARCHAR(30) UNIQUE, --零件名 p_weight NUMERIC(6,2), --重量 p_city VARCHAR(20) --产地 ); --关系模式：一个供应商可以供应多个零件，而一个零件可以由多个供应商供应 s_p(s_no, p_no, quantity) CREATE TABLE s_p (s_no VARCHAR(5), p_no VARCHAR(5), quantity NUMERIC(9), --数量 PRIMARY KEY(s_no, p_no), FOREIGN KEY(s_no) REFERENCES s (s_no), FOREIGN KEY(p_no) REFERENCES p (p_no)); --需要注意的地方： --1、如果已经定义的主键，如PRIMARY KEY(s_no)，则不能在s_no之后加unique，不然有错误 --2、foreign key(s_no) references s(s_no)定义了在s_p关系中s_no为外码，其取值必须来自s关系的s_no域    1.1.1、操作表、列

--在s_no表添加一列 s_city; Oracle中不能在某列前后固定插入，只会在最后列中追加 --插入新列不影响前面的数据 ALTER TABLE s ADD s_city VARCHAR2(20); ALTER TABLE s RENAME TO s_table; --修改表名 ALTER TABLE s_table RENAME COLUMN s_city TO s_country; --修改表列名 ALTER TABLE s_table MODIFY s_country NUMBER(20); --修改字段类型 ALTER TABLE s_table DROP COLUMN s_country; --删除表列 --注意:首先，增加和修改列是不需要加关键字COLUMN，否则会报错ora-00905。 --　　其次，对删除单列的话，一定要加COLUMN，然后记住，删除是不需要加列类型的。 ------------------------------------------------------------------------- --增加多列： ALTER TABLE s_table ADD (test1 VARCHAR2(10),test2 NUMBER); --修改多列： ALTER TABLE s_table MODIFY (test1 VARCHAR2(20),test2 VARCHAR2(20)); --删除多列： ALTER TABLE s_table DROP (test1,test2); --很奇怪的现象，再单列中要加关键字COLUMN，然而再删除多列的时候，不能加COLUMN关键字。

    1.2、创建、删除索引

创建索引语句格式:                 create [unique][cluster] index <索引名>                 on <表名>（列名 [<次序>],列名 [<次序>]...);  unique：表明此索引的每一个索引值只对应唯一的数据记录。  cluster：表明要建立的索引是聚簇索引，意为索引项的顺序是与表中记录的物理顺序一致的索引组织。  次序。默认次序是ASC

--建表 CREATE TABLE s_test (s_no VARCHAR(5) NOT NULL, --供应商代码 s_name VARCHAR(30), --供应商名 s_status VARCHAR(20) --供应商状态 ); --建立索引 CREATE UNIQUE INDEX test_index ON s_test(s_no DESC) ; --Oracle数据库创建表是有两个约束带有默认索引。 --1、主键primary Key：唯一索引、非空 --2、唯一Unique：唯一索引，可以是空值 --如果没有设定主键和唯一约束，表中不会有默认索引的，这时才可以建立索引！！！。 --查看索引。一定是大写的表名 SELECT * FROM User_Indexes WHERE table_name = 'S_TEST'; --删除索引 DROP INDEX test_index;

1.3、视图的创建、删除 视图创建语法：            create view 视图名（列表名）            as select 查询子句            [with check option]; 注意：1、子查询是任意复杂的 select 语句，但通常不允许含有 order by子句和distinct 语句           2、with check option 表示对update、insert、delete操作时保证更新、插入或删除的行为满足子查询的条件表达式

--创建视图时需要权限， system用户有两种方式给权限 GRANT CREATE ANY VIEW TO changwen; GRANT CONNECT, RESOURCE,DBA TO changwen; --创建视图 CREATE VIEW s_view AS SELECT s_no, s_name FROM s_table; --删除视图 DROP VIEW s_view; 1.4、delete 与 truncate 区别？ 1） truncate 是DDL语句，delete 是DML语句； 2） truncate 比delete删除速度快，效率高，主要原因是truncate 使用的系统和事务日志资源少。；    具体原因：1、当我们执行delete操作时所有表数据先被copy到回滚表空间，数据量不同花费时间长短不一。                      而truncate是直接删除数据不进回滚表空间。              2、DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。                TRUNCATE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 3）接（2）这也就导致了 delete数据后我们可以运行rollback进行数据回滚，而truncate则是永久删除不能回滚； 4）由于truncate table不记录在日志中,所以 truncate 操作不会触发表上的delete触发器；而delete会正常触发； 5） truncate语句不能带where条件意味着只能全部数据删除，而delete可删除部分数据； 6）truncate 操作会重置表的高水位线(High Water Mark)，而delete不会。 7） 对于由 foreign key 约束引用的表，不能使用 truncate table，而应使用不带 WHERE 子句的 delete语句。 8） truncate table 不能用于参与了索引视图的表。 9）truncate  删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。     如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

1.5、COMMENT--注释

SQL-->COMMENT ON TABLE s_test IS '存放雇员信';  --没有多大的用

1.6、RANT 赋于权限 常用的系统权限集合有以下三个: 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; 1.7、revoke   回收权限   revoke  connect, resource from  用户名;   revoke  select  on   表名 from  用户名;   revoke  select, insert,  delete  on 表名  from   用户名1,   用户名2;

二、DML:数据操作语言

       1. SELECT - -    查询        2. INSERT --     添加         3. UPDATE --    更新        4. DELETE --   删除        5. CALL - call a PL/SQL or Java subprogram        6.EXPLAIN PLAN - explain access path to data        Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以， 了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。        7.LOCK TABLE - control concurrency 锁，用于控制并发 三、DCL:数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等        1.COMMIT - save work done 提交         2.SAVEPOINT - identify a point in a transaction to which you can later roll back 保存点        3.ROLLBACK - restore database to original since the last COMMIT   回滚

       4.SET TRANSACTION - Change transaction options like what rollback segment to use   设置当前事务的特性，它对后面的事务没有影响

----------------------------------------------------------------------------------------------------

二、数据库和实例

Oracle实例的组成：进程+内存结构 进程：DBWR，LGWR， CKPT， ARCH， SMON 。PMON等 内存结构：SGA:大池，JAVA池，共享池，数据缓冲，日志缓冲等

一、数据库实例名

什么是数据库实例名？     数据库实例是用于和操作系统进行联系的标识，也就是说数据库和操作系统之间的交互使用的是数据库实例。数据库实例名(instance_name)  实例名也被写入参数文件中，该参数为instance_name，在winnt平台中，实例名同时也被写入注册表。     数据库名和实例名可以相同也可以不同。     在一般情况下，数据库名和实例名是一对一的关系，但如果在oracle并行服务器架构(即oracle实时应用集群)中，数据库名和实例名是一对多的关系。

如何查看当前数据库实例名呢? 方式有三：   ·使用SQL语句：select instance_name from v$instance;  ·使用show命令：show parameter instance   ·查看参数文件：查看init.ora文件 

数据库实例名与ORACLE_SID     虽然两者都表是oracle实例，但两者是有区别的。instance_name是oracle数据库参数。而ORACLE_SID是操作系统的环境变 量。 ORACLD_SID用于与操作系统交互，也就是说，从操作系统的角度访问实例名，必须通过ORACLE_SID。在winnt不台， ORACLE_SID还需存在于注册表中。 且ORACLE_SID必须与instance_name的值一致，否则，你将会收到一个错误，在unix平台，是“ORACLE not available”,在winnt平台，是“TNS:协议适配器错误”。

数据库实例名与网络连接 数据库实例名除了与操作系统交互外，还用于网络连接的oracle服务器标识。当你配置oracle主机连接串的时候，就需要指定实例名。当然8i以后版本的网络组件要求使用的是服务名SERVICE_NAME。

二、实例

    实例是oracle使用到的内存和进程的总称。     一个oracle数据库可以有多个实例，不过这个属于RAC的应用了；单机中是一个实例对应一个数据库的。 oracle的数据库存放在硬盘上的有数据文件、日志文件、控制文件。      你安装oracle时候 如果同时建立一个orcl的数据库的话；那么SID就是orcl，这是一个数据库，有一个实例与它对应；如果你又通过DBCA建立了另外一个db_test，这是就有两个sid；需要两个实例分别打开各自的数据库

    实例是由访问数据库的一组进程和内存组成。而数据库则是一系列储存数据的文件。 一个数据库可以被一个实例或者多个实例打开（RAC）。 访问数据库的时候，你连上的是一个实例，通过实例访问到数据库中的数据。     一个实例一般就是一个库

本文标签： 基础知识oracle