oracle基础知识1.docx

1、oracle基础知识1Oracle基础主要数据类型* 数据类型 说明- char 字符型,最大长度2000B,缺省长度为1B -(单位为字节,不够指定长度也算指定长度空间)- nchar 基于NLS国家字符集的字符型,最大长度2000B,缺省为1字符 - (单位为字符)- varchar2 变长字符型,最大长度4000B -(单位为字节,长度为数据的长度,超过长度报错)- nvarchar2 基于NLS国家字符集的字符型,其余同varchar2- varchar 同varchar2- number(m, n) 数值型,m为总位数,n为小数位数,总长度最大为38位- date 日期型,有效表数范

2、围:公元前4712年1月1到公元后4712年12月31日- long 变长字符型,最大长度2GB,不支持对字符串内容进行搜索- raw 变长二进制数据类型,最大长度2000B- long raw 变长二进制数据类型,最大长度2GB- blob 二进制大对象类型,最大长度4GB- clob 字符大对象类型,最大长度4GB- nclob 基于NLS国家字符集的字符大对象类型,最大长度4GB- bfile 在数据库外部保存的大型二进制文件大对象类型,最大长度4GB SQL语句分类 * Select 查询语句* DML 语句(数据操作语句)- Insert, Update, Delete, Merge

3、(数据合并)* DDL 语句(数据定义语句)- Create, Alter, Drop, Truncate* DCL 语句(数据控制语言)- Grant, Revoke* 事务控制语句- Commit, Rollback, Savepoint(保存点)创建表空间create tablespace rootspacedatafile rootfile size 10mautoextend on创建用户CREATE USER usernameIDENTIFIED BY passwordDEFAULT TABLESPACE tablespace 默认表空间TEMPORARY TABLESPACE t

4、ablespace; 临时表空间数据库角色oracle为了版本兼容,提供了三种标准的角色（role）: CONNECT、RESOURCE和DBA。1. CONNECT (连接角色)临时用户,特别是那些不需要操作数据的用户，通常只赋予他们CONNECT角色.拥有CONNECT 角色权限的用户能够连接至数据库. 2. RESOURCE (资源角色) 允许用户使用数据库中的存储空间.3. DBA (数据库管理员角色) DBA拥有所有的系统权限,包括无限制的空间限额和给其他用户授予各种权限的能力。创建角色 除了前面讲到的三种系统角色,用户还可以在Oracle创建自己的role。用户创建的role可以由

5、表或系统权限或两者的组合构成。为了创建role，用户必须具有CREATE ROLE系统权限。下面给出一个create role命令的实例： create role student; 这条命令创建了一个名为student的role。 创建了一个role可以给他授权。给role授权的的语法和给用户授权的语法相同. 如: grant select on test to student; 这样,拥有student角色的所有用户都具有对test表的select权限。删除角色 使用drop role命令，如下所示： drop role student; 指定的角色连同与之相关的权限将从数据库中全部删除。用

6、户授权 grant connect to username; 允许用户连接至数据库 grant resource to username; 允许用户使用数据库中的存储空间 grant create sequence to username; 此系统权限允许用户在当前模式中创建序列，此权限 包含在connect 权限中. grant select on test to username; 允许用户查询 test表的记录. grant update on test to username; 允许用户更新 test表中的记录. grant all on test to username; 允许用户插

7、入、删除、更新和查询 test表中的记录.修改用户密码 alter user username identified by newpassword;删除用户 drop user username cascade; 撤销用户权限 revoke connect from username;用户加锁/解锁 ALTER USER 用户名 ACCOUNT LOCK; ALTER USER 用户名 ACCOUNT UNLOCK; 创建表 * 创建表的前提条件- 具备创建表的权限- 有可用的存储空间* 创建表语法 create table schema.table (column datatype defa

8、ult expr, .);* 例:create table scott.test1 ( eid number(10), name varchar2(20), hiredate date default sysdate, salary number(8, 2) default 0);* 说明- 创建表时必须指定表名, 字段名, 字段类型- create table 为 DDL 语句, 一经执行不可撤销 修改表结构 * 使用 alter table 语句可以修改表结构,包括:- 添加字段- 修改字段- 删除字段* alter 语句为 DDL 语句, 一经执行不可撤销 添加字段 * 在 alter

9、table 语句中, 使用 add 字句添加新字段, 新字段只能被加到整个表的最后* 语法:- alter table table_name- add (column datatype default expr , column datatype .);* 例:- drop table test;- create table test(tid number(10),name varchar2(20);- insert into test values(10, AAA);- alter table test- add(- sex varchar2(2),- address varchar2(20

10、0) default 未知- );- select * from test; 修改字段 * 在 alter table 语句中, 使用 modify 子句修改现有字段, 包括字段的数据类型,大小和默认值* 语法:- alter table table_name- modify(column datatype default expr, column datatype.);* 例:- alter table test- modify(address varchar2(50) default 未知);- desc test;* 说明:- 修改操作会受到当前表中已有数据的影响-当已有记录的相应字段只

11、包含空值时,类型,大小都可以修改,否则修改可能失败- 修改的缺省值设置,只对此后新插入的记录有效 删除字段 * 在 alter table 语句中, 使用 drop 子句删除字段-从每行中删除掉该字段占据的长度和数据,释放在数据块中占用的存储空间* 语法:- alter table table_name- drop (column , column .);* 例:- alter table test- drop(sex, address);- select * from test; 清空表中的数据 * truncate table 语句用于清空表中数据:- 清除表中所有记录- 释放表的存储空间

12、- 为 DDL 语句, 一经执行不可撤销* 语法:- truncate table table_name;* 例:- truncate table test;* 注意:- 跟 delete 区别很大, truncate table 不可回滚, delete 可以回滚 删除表 * drop table 语句用于删除表:- 表中所有数据将被删除- 此前未完成的事务将被提交- 所有相关的索引被删除- 为 DDL 语句, 一以执行不可撤销* 语法:- drop table table_name;* 例:- drop table test; 重命名表 * 使用 rename 语句可以改变现有表的名称-

13、也可修改其他数据对象(视图,序列,同义司等)的名称- 执行重命名操作的必须是对象的所有者- 为 DDL 语句, 一经执行不可撤销* 语法:- rename old_name to new_name;* 例:- create table test(tid number(10),name varchar2(20);- insert into test values(10, AAA);- insert into test values(20, BBB);- insert into test values(30, CCC);- rename test to tt; insert 语句 * insert

14、 语句用于向表中插入数据* 语法:- insert into table (column, column .)- values (value, value .);* 举例:- insert into dept values(88, 装备部, 北京);- insert into dept(deptno, dname) values(99, 交通部);* 说明:- insert 语句每次只能向表中插入一条记录- 缺省字段名列表时, 应为新插入记录中的每个字段设定新值- 也可在 insert 语句中指定赋值字段列表, 中为部分字段显式设定新值, 其余字段将被缺省赋值为 null 表间数据拷贝 * 可

15、以在 insert 语句中使用子查询, 实现表间数据拷贝:- insert into dept1(id, name) select deptno, dname from dept;* 说明:- 此时不必再给出 values 子句- 子查询中的值列表应与 insert 子句中的字段列表相对应 update 语句 * update 语句用于更新表中数据* 语法:- update table- set column = value , column = value, .- where condition;* 举例:- update emp set sal = sal + 88;- update em

16、p set sal = sal + 1000, comm = comm + 0.5 where empno = 7778;- update dept1 set loc = nvl(loc, 未知);* 说明:- update 语句每次可更新多条记录- 可使用 where 子句限定要更新的记录, 如果缺省 where 子句, 则更新表中的所有记录 delete 语句 * delete 语句用于从表中删除数据* 语法:- delete from table- where condition ;* 举例:- delete emp;- delete from emp;- delete emp wher

17、e empno = 7788;* 说明:- delete 语句每次可删除多条记录- 可使用 where 子句限定要删除的记录, 如果缺省 where 子句, 则删除表中的所有记录 merge 语句 * merge 语句用于进行数所合并-根据条件在表中执行数据的修改或插入操作,如果要插入的记录在目标表中已* 经存在,则执行更新操作,否则执行插入操作* 语法- merge into table alias- using (table | view | sub_query) alias- on(join_condition)- when matched then- update set col1 =

18、 col1_val, col2 = col2_val- when not matched then- insert(cloumn_list) values (cloumn_values);* 举例:- create table test1(eid number(10), name varchar2(20), birth date, salary number(8, 2);- insert into test1 values(1001, 张三, sysdate, 2300);- insert into test1 values(1002, 李四, sysdate, 6600);- insert

19、into test1 values(1003, 王五, sysdate, 5100);- select * from test1;- create table test2(eid number(10), name varchar2(20), birth date, salary number(8, 2);- select * from test2;- merge into test2- using test1- on(test.eid = test2.eid)- when matched then- update set name = test1.name, birth = test1.bir

20、th, salary = test1.salary- when not matched then- insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary);- select * from test2; 事务控制 * 事务(Transaction)- 组成单个逻辑工作单元的一系列操作被称为事务* 事务特性- 原子性(Atomicity)- 一致性(Consistency)- 隔离性(Isolation)- 持久性(Durability)* 事务控制- 通过将一组相关操作组

21、合为一个要么全部成功,要么全部失败的逻辑工作单元,以简 化错误恢复,提高应用程序的可靠性 事务的开始和结束 * 事务开始于第一条可执行语句* 事务结束:- 遇到 commit 或 rollback 语句- 遇到 DDL 或 DCL 语句- 用户会话结束- 系统崩溃* 事务的提交和回滚- 显式的提交和回滚操作是为了更好地保证数据的一致性- 下述情况下事务会被自动提交- 执行一个 DDL 语句- 执行一个 DCL 语句- 正常结束会话- 会话异常终止/系统崩溃时事务会被自动回滚* Sql Plus 中执行 SQL 语句时可以设置是否自动提交,缺省设置为非自动提交* 查看设置:- show auto

22、commit;- autocommit OFF- autocommit IMMEDIATE* 改变设置- set autocommit on;- set autocommit off; 提交/回滚前数据状态 * 当事务中数据状态的改变是可以恢复的* 当前事务中的 DML 操作结果只对当前用户(会话)可见,其它用户(会话)看不到当前事务中数据状态的改变,直到当前事务结束* 当前事务中 DML 语句所涉及到的行被锁定,其他用户(会话)不能对其进行修改操作* 举例:- 会话窗口1:- set autocommit off;- update dept set loc = China where dep

23、tno = 55;- 会话窗口2:- update dept set dname = KKK where deptno = 55;- 这时 会话窗口2 则无反映,因为该数据已被锁定,会话窗口1 commit 之后则可以对其进行操作,同样,如- 果 会话窗口2 没有对其 commit, 则该数据就被 会话窗口2 锁定.可以开 N 个会话窗口 事务提交后数据状态 * 数据的修改永久生效,不可再撤销* 数据以前的状态永久性丢失,无法恢复* 所有的用户(会话)都将看到操作后的结果* 记录锁被释放,其它用户可对这些进行修改操作* 事务中的保存点( savepoints )被清除 事务回滚后数据状态 *

24、数据的修改被撤销* 数据恢复到修改前的状态* 记录锁被释放 数据字典 * 什么是数据字典- 数据字典是 Oracle 数据库的核心, 用于描述数据库及其所有对象- 数据字典由一系列只读的表和视图组成, 这些表和视图属 sys 用户拥有, 由 Oracle server 负责维护, 用户可以通过 select 语句进行访问* 数据字典的内容- 数据库的物理和逻辑结构- 对象的定义和空间分配- 完整性约束条件- 用户- 角色- 权限- 审计记录* 数据字典视图可分为三类:- dba - 所有方案包含的对象信息- all - 用户可以访问的对象信息- user - 用户方案的对象信息* 举例:* 查

25、看当前用户拥有的所有表的名字;- select table_name from user_tables;* 查看当前用户可以访问的所有表的名字;- select table_name from all_tables;* 查看约束- select * from user_constraints;* 查看当前用户- select user from dual;* 查看当前用户拥有的权限- select * from user_sys_privs; 约束 * 约束 ( Constraint ) 是在表上强制执行的数据校验规则,用于保护数据的完整性,具体包括如下五种:- not null (非空)-

26、unique key (唯一)- primary key (主键)- foreign key (外键)- check (检查)* 相关说明:- Oracle 使用 SYS_Cn格式命约束,也可以由用户命名- 创建约束的时机- 在建表的同时创建- 建表后单独添加- 可以在表级或列级定义约束 - 可以通过数据字典视图查看约束 建表的同时创建约束 * 语法格式- create table schema.table(- column datatype default exprcolumn_constraint,- .- table_constraints- ); 非空约束(not null) * 非空

27、约束特点- 确保字段值不能为空(null)- 只能在字段级定义* 举例- drop table student;- create table student(sid number(3), name varchar2(20), birth date constraint student_birth_nn not null;- insert into student values(null, AAA, sysdate);- select * from student; 唯一性约束(unique) * 唯一性约束特点:- 唯一性约束用于确保所在的字段(或字段组合)不出现重复值- 唯一性约束的字段允许

28、出现空值- Oracle 会自动为唯一性约束创建对应的唯一性索引- 唯一性约束既可以在字段级定义,也可以在表级定义* 举例:- drop table student;- create table student(- sid number(3) unique,- name varchar2(20)- );- insert into student values(10, AAA);- drop table student;- create table student(- sid number(3),- name varchar2(20),- constraint student_sid_un un

29、ique(sid)- );- drop table student;- create table student(- sid number(3),- name varchar2(20),- constraint student_sid_un unique(sid, name) - 列组合唯一- ); 主键约束(primary key) * 主键约束特点:- 主键用于唯一标识表中的某一行记录,功能上相当非空且唯一- 一个表中只允许一个主键,主键可以是单个字段或多字段的组合- Oracle 会自动为主键字段创建对应的唯一性索引- 主键约束既可以在字段级定义,可以在表级定义* 举例:- drop t

30、able student;- create table student(- sid number(3) primary key,- name varchar2(20)- );- drop table student;- create table student(- sid number(3) primary key,- name varchar2(20),- constraint student_sid_pk primary key(sid)- );* 联合主键:- 由多个字段组合而成的主键也称联合主键- 联合主键中每一个字段都不能为空- 联合主键字段组合的值不能出现重复- 联合主键只能定义为表级约束* 举例