oracle基础知识1.docx
《oracle基础知识1.docx》由会员分享，可在线阅读，更多相关《oracle基础知识1.docx（23页珍藏版）》请在冰豆网上搜索。
oracle基础知识1
Oracle基础
[主要数据类型]
*数据类型说明
-char字符型,最大长度2000B,缺省长度为1B
--（单位为字节,不够指定长度也算指定长度空间）
-nchar基于NLS国家字符集的字符型,最大长度2000B,缺省为1字符
--（单位为字符）
-varchar2变长字符型,最大长度4000B
--（单位为字节,长度为数据的长度,超过长度报错）
-nvarchar2基于NLS国家字符集的字符型,其余同varchar2
-varchar同varchar2
-number（m,n）数值型,m为总位数,n为小数位数,总长度最大为38位
-date日期型,有效表数范围:
公元前4712年1月1到公元后4712年12月31日
-long变长字符型,最大长度2GB,不支持对字符串内容进行搜索
-raw变长二进制数据类型,最大长度2000B
-longraw变长二进制数据类型,最大长度2GB
-blob二进制大对象类型,最大长度4GB
-clob字符大对象类型,最大长度4GB
-nclob基于NLS国家字符集的字符大对象类型,最大长度4GB
-bfile在数据库外部保存的大型二进制文件大对象类型,最大长度4GB
[SQL语句分类]
*Select查询语句
*DML语句（数据操作语句）
-Insert,Update,Delete,Merge（数据合并）
*DDL语句（数据定义语句）
-Create,Alter,Drop,Truncate
*DCL语句（数据控制语言）
-Grant,Revoke
*事务控制语句
-Commit,Rollback,Savepoint（保存点）
[创建表空间]
createtablespacerootspace
datafile'rootfile'size10m
autoextendon
[创建用户]
CREATEUSERusername
IDENTIFIEDBYpassword
[DEFAULTTABLESPACEtablespace]默认表空间
[TEMPORARYTABLESPACEtablespace];临时表空间
[数据库角色]
oracle为了版本兼容,提供了三种标准的角色（role）:
CONNECT、RESOURCE和DBA。
1.CONNECT（连接角色）
临时用户,特别是那些不需要操作数据的用户，通常只赋予他们CONNECT角色.拥有
CONNECT角色权限的用户能够连接至数据库.
2.RESOURCE（资源角色）
允许用户使用数据库中的存储空间.
3.DBA（数据库管理员角色）
DBA拥有所有的系统权限,包括无限制的空间限额和给其他用户授予各种权限的能力。
[创建角色]
除了前面讲到的三种系统角色,用户还可以在Oracle创建自己的role。
用户创建的role可以由表或系统权限或两者的组合构成。
为了创建role，用户必须具有CREATEROLE系统权限。
下面给出一个createrole命令的实例：
createrolestudent;
这条命令创建了一个名为student的role。
创建了一个role可以给他授权。
给role授权的的语法和给用户授权的语法相同.
如:
grantselectontesttostudent;
这样,拥有student角色的所有用户都具有对test表的select权限。
[删除角色]
使用droprole命令，如下所示：
droprolestudent;
指定的角色连同与之相关的权限将从数据库中全部删除。
[用户授权]
grantconnecttousername;允许用户连接至数据库
grantresourcetousername;允许用户使用数据库中的存储空间
grantcreatesequencetousername;此系统权限允许用户在当前模式中创建序列，此权限
包含在connect权限中.
grantselectontesttousername;允许用户查询test表的记录.
grantupdateontesttousername;允许用户更新test表中的记录.
grantallontesttousername;允许用户插入、删除、更新和查询test表中的记录.
[修改用户密码]
alteruserusernameidentifiedbynewpassword;
[删除用户]
dropuserusernamecascade;
[撤销用户权限]
revokeconnectfromusername;
[用户加锁/解锁]
ALTERUSER用户名ACCOUNTLOCK;
ALTERUSER用户名ACCOUNTUNLOCK;
[创建表]
*创建表的前提条件
-具备创建表的权限
-有可用的存储空间
*创建表语法
createtable[schema.]table（columndatatype[defaultexpr][,...]]）;
*例:
createtablescott.test1（
eidnumber（10）,
namevarchar2（20）,
hiredatedatedefaultsysdate,
salarynumber（8,2）default0
）;
*说明
-创建表时必须指定表名,字段名,字段类型
-createtable为DDL语句,一经执行不可撤销
[修改表结构]
*使用altertable语句可以修改表结构,包括:
-添加字段
-修改字段
-删除字段
*alter语句为DDL语句,一经执行不可撤销
[添加字段]
*在altertable语句中,使用add字句添加新字段,新字段只能被加到整个表的最后
*语法:
-altertabletable_name
-add（columndatatype[defaultexpr][,columndatatype]...）;
*例:
-droptabletest;
-createtabletest（tidnumber（10）,namevarchar2（20））;
-insertintotestvalues（10,'AAA'）;
-altertabletest
-add（
-sexvarchar2
（2）,
-addressvarchar2（200）default'未知'
-）;
-select*fromtest;
[修改字段]
*在altertable语句中,使用modify子句修改现有字段,包括字段的数据类型,大小和默认值
*语法:
-altertabletable_name
-modify（columndatatype[defaultexpr][,columndatatype]...）;
*例:
-altertabletest
-modify（addressvarchar2（50）default'未知'）;
-desctest;
*说明:
-修改操作会受到当前表中已有数据的影响--当已有记录的相应字段只包含空值时,类型,大小都可以修改,否则修改可能失败
-修改的缺省值设置,只对此后新插入的记录有效
[删除字段]
*在altertable语句中,使用drop子句删除字段--从每行中删除掉该字段占据的长度和数据,释放在数据块中占用的存储空间
*语法:
-altertabletable_name
-drop（column[,column]...）;
*例:
-altertabletest
-drop（sex,address）;
-select*fromtest;
[清空表中的数据]
*truncatetable语句用于清空表中数据:
-清除表中所有记录
-释放表的存储空间
-为DDL语句,一经执行不可撤销
*语法:
-truncatetabletable_name;
*例:
-truncatetabletest;
*注意:
-跟delete区别很大,truncatetable不可回滚,delete可以回滚
[删除表]
*droptable语句用于删除表:
-表中所有数据将被删除
-此前未完成的事务将被提交
-所有相关的索引被删除
-为DDL语句,一以执行不可撤销
*语法:
-droptabletable_name;
*例:
-droptabletest;
[重命名表]
*使用rename语句可以改变现有表的名称
-也可修改其他数据对象（视图,序列,同义司等）的名称
-执行重命名操作的必须是对象的所有者
-为DDL语句,一经执行不可撤销
*语法:
-renameold_nametonew_name;
*例:
-createtabletest（tidnumber（10）,namevarchar2（20））;
-insertintotestvalues（10,'AAA'）;
-insertintotestvalues（20,'BBB'）;
-insertintotestvalues（30,'CCC'）;
-renametesttott;
[insert语句]
*insert语句用于向表中插入数据
*语法:
-insertintotable[（column[,column...]）]
-values（value[,value...]）;
*举例:
-insertintodeptvalues（88,'装备部','北京'）;
-insertintodept（deptno,dname）values（99,'交通部'）;
*说明:
-insert语句每次只能向表中插入一条记录
-缺省字段名列表时,应为新插入记录中的每个字段设定新值
-也可在insert语句中指定赋值字段列表,中为部分字段显式设定新值,其余字段将被缺省赋值为null
[表间数据拷贝]
*可以在insert语句中使用子查询,实现表间数据拷贝:
-insertintodept1（id,name）selectdeptno,dnamefromdept;
*说明:
-此时不必再给出values子句
-子查询中的值列表应与insert子句中的字段列表相对应
[update语句]
*update语句用于更新表中数据
*语法:
-updatetable
-setcolumn=value[,column=value,...
-[wherecondition];
*举例:
-updateempsetsal=sal+88;
-updateempsetsal=sal+1000,comm=comm+0.5whereempno=7778;
-updatedept1setloc=nvl（loc,'未知'）;
*说明:
-update语句每次可更新多条记录
-可使用where子句限定要更新的记录,如果缺省where子句,则更新表中的所有记录
[delete语句]
*delete语句用于从表中删除数据
*语法:
-delete[from]table
-[wherecondition];
*举例:
-deleteemp;
-deletefromemp;
-deleteempwhereempno=7788;
*说明:
-delete语句每次可删除多条记录
-可使用where子句限定要删除的记录,如果缺省where子句,则删除表中的所有记录
[merge语句]
*merge语句用于进行数所合并--根据条件在表中执行数据的修改或插入操作,如果要插入的记录在目标表中已
*经存在,则执行更新操作,否则执行插入操作
*语法
-mergeintotable[alias]
-using（table|view|sub_query）[alias]
-on（join_condition）
-whenmatchedthen
-updatesetcol1=col1_val,col2=col2_val
-whennotmatchedthen
-insert（cloumn_list）values（cloumn_values）;
*举例:
-createtabletest1（eidnumber（10）,namevarchar2（20）,birthdate,salarynumber（8,2））;
-insertintotest1values（1001,'张三',sysdate,2300）;
-insertintotest1values（1002,'李四',sysdate,6600）;
-insertintotest1values（1003,'王五',sysdate,5100）;
-select*fromtest1;
-createtabletest2（eidnumber（10）,namevarchar2（20）,birthdate,salarynumber（8,2））;
-select*fromtest2;
-mergeintotest2
-usingtest1
-on（test.eid=test2.eid）
-whenmatchedthen
-updatesetname=test1.name,birth=test1.birth,salary=test1.salary
-whennotmatchedthen
-insert（eid,name,birth,salary）values（test1.eid,test1.name,test1.birth,test1.salary）;
-select*fromtest2;
[事务控制]
*事务（Transaction）
-组成单个逻辑工作单元的一系列操作被称为事务
*事务特性
-原子性（Atomicity）
-一致性（Consistency）
-隔离性（Isolation）
-持久性（Durability）
*事务控制
-通过将一组相关操作组合为一个要么全部成功,要么全部失败的逻辑工作单元,以简
化错误恢复,提高应用程序的可靠性
[事务的开始和结束]
*事务开始于第一条可执行语句
*事务结束:
-遇到commit或rollback语句
-遇到DDL或DCL语句
-用户会话结束
-系统崩溃
*事务的提交和回滚
-显式的提交和回滚操作是为了更好地保证数据的一致性
-下述情况下事务会被自动提交
-执行一个DDL语句
-执行一个DCL语句
-正常结束会话
-会话异常终止/系统崩溃时事务会被自动回滚
*SqlPlus中执行SQL语句时可以设置是否自动提交,缺省设置为非自动提交
*查看设置:
-showautocommit;
-autocommitOFF
-autocommitIMMEDIATE
*改变设置
-setautocommiton;
-setautocommitoff;
[提交/回滚前数据状态]
*当事务中数据状态的改变是可以恢复的
*当前事务中的DML操作结果只对当前用户（会话）可见,其它用户（会话）看不到当前事务中数据状态的改变,直到当前事务结束
*当前事务中DML语句所涉及到的行被锁定,其他用户（会话）不能对其进行修改操作
*举例:
-会话窗口1:
-setautocommitoff;
-updatedeptsetloc='China'wheredeptno=55;
-会话窗口2:
-updatedeptsetdname='KKK'wheredeptno=55;
-这时会话窗口2则无反映,因为该数据已被锁定,会话窗口1commit之后则可以对其进行操作,同样,如
-果会话窗口2没有对其commit,则该数据就被会话窗口2锁定.可以开N个会话窗口
[事务提交后数据状态]
*数据的修改永久生效,不可再撤销
*数据以前的状态永久性丢失,无法恢复
*所有的用户（会话）都将看到操作后的结果
*记录锁被释放,其它用户可对这些进行修改操作
*事务中的保存点（savepoints）被清除
[事务回滚后数据状态]
*数据的修改被撤销
*数据恢复到修改前的状态
*记录锁被释放
[数据字典]
*什么是数据字典
-数据字典是Oracle数据库的核心,用于描述数据库及其所有对象
-数据字典由一系列只读的表和视图组成,这些表和视图属sys用户拥有,由Oracleserver负责维护,用户可以通过select语句进行访问
*数据字典的内容
-数据库的物理和逻辑结构
-对象的定义和空间分配
-完整性约束条件
-用户
-角色
-权限
-审计记录
*数据字典视图可分为三类:
-dba-所有方案包含的对象信息
-all-用户可以访问的对象信息
-user-用户方案的对象信息
*举例:
*查看当前用户拥有的所有表的名字;
-selecttable_namefromuser_tables;
*查看当前用户可以访问的所有表的名字;
-selecttable_namefromall_tables;
*查看约束
-select*fromuser_constraints;
*查看当前用户
-selectuserfromdual;
*查看当前用户拥有的权限
-select*fromuser_sys_privs;
[约束]
*约束（Constraint）是在表上强制执行的数据校验规则,用于保护数据的完整性,具体包括如下五种:
-notnull（非空）
-uniquekey（唯一）
-primarykey（主键）
-foreignkey（外键）
-check（检查）
*相关说明:
-Oracle使用SYS_Cn格式命约束,也可以由用户命名
-创建约束的时机
-在建表的同时创建
-建表后单独添加
-可以在表级或列级定义约束
-可以通过数据字典视图查看约束
[建表的同时创建约束]
*语法格式
-createtable[schema.]table（
-columndatatype[defaultexpr][column_constraint],
-...
-[table_constraints]
-）;
[非空约束（notnull）]
*非空约束特点
-确保字段值不能为空（null）
-只能在字段级定义
*举例
-droptablestudent;
-createtablestudent（sidnumber（3）,
namevarchar2（20）,
birthdateconstraintstudent_birth_nnnotnull;
-insertintostudentvalues（null,'AAA',sysdate）;
-select*fromstudent;
[唯一性约束（unique）]
*唯一性约束特点:
-唯一性约束用于确保所在的字段（或字段组合）不出现重复值
-唯一性约束的字段允许出现空值
-Oracle会自动为唯一性约束创建对应的唯一性索引
-唯一性约束既可以在字段级定义,也可以在表级定义
*举例:
-droptablestudent;
-createtablestudent（
-sidnumber（3）unique,
-namevarchar2（20）
-）;
-insertintostudentvalues（10,'AAA'）;
-droptablestudent;
-createtablestudent（
-sidnumber（3）,
-namevarchar2（20）,
-constraintstudent_sid_ununique（sid）
-）;
-droptablestudent;
-createtablestudent（
-sidnumber（3）,
-namevarchar2（20）,
-constraintstudent_sid_ununique（sid,name）->列组合唯一
-）;
[主键约束（primarykey）]
*主键约束特点:
-主键用于唯一标识表中的某一行记录,功能上相当非空且唯一
-一个表中只允许一个主键,主键可以是单个字段或多字段的组合
-Oracle会自动为主键字段创建对应的唯一性索引
-主键约束既可以在字段级定义,可以在表级定义
*举例:
-droptablestudent;
-createtablestudent（
-sidnumber（3）primarykey,
-namevarchar2（20）
-）;
-droptablestudent;
-createtablestudent（
-sidnumber（3）primarykey,
-namevarchar2（20）,
-constraintstudent_sid_pkprimarykey（sid）
-）;
*联合主键:
-由多个字段组合而成的主键也称联合主键
-联合主键中每一个字段都不能为空
-联合主键字段组合的值不能出现重复
-联合主键只能定义为表级约束
*举例
升级会员 