oracle创建表基础.docx
- 文档编号:26984474
- 上传时间:2023-06-24
- 格式:DOCX
- 页数:11
- 大小:20.43KB
oracle创建表基础.docx
《oracle创建表基础.docx》由会员分享,可在线阅读,更多相关《oracle创建表基础.docx(11页珍藏版)》请在冰豆网上搜索。
oracle创建表基础
☐VARCHAR2(size)和NVARCHAR2(size):
变长字符型数据。
首先,该数据类型存储变长的字符数据,在使用该数据类型定义数据时,该数据的存储区大小是不固定的,依据存储数据的长度进行动态分配存储区。
参数size是该变量存储的最大的字符数,该值最大为4000。
size的最小或默认值都是1。
一般在定义该数据类型时,都要指定该长度值,即指定size值。
NVARCHAR2(size)的不同之处在于它支持全球化数据类型,支持定长和变长字符集。
☐CHAR(size)NCHAR(size):
定长字符型数据
该数据类型一旦定义,则存储该变量的存储区的大小就固定下来。
显然在存储区分配上它没有VARCHAR2(size)和NVARCHAR2(size)数据类型具有动态性,但是在实际中,如果可以预测到一个变量存储的字符数量,且数量不是很大,则最好还是使用定长字符型数据来定义该变量,这样可以提高存储的效率。
因为使用变长字符型数据要不断的计算存储的数据长度,再分配存储数据块,会消耗计算资源。
定长字符型数据的最小值和默认值都为1个字符,而最大值为2000。
NCHAR(size)的不同之处在于它支持全球化数据类型,支持定长和变长字符集,此时定长字符型数据的最小和默认值都为1个字节。
☐DATE:
日期型数据
ORACLE服务器使用7个定长的存储区存储日期型数据,它可以月,年,日,实际,时,分和秒。
日期型数据的取值范围从公元前4712年1月1日到公元9999年12月31日。
☐NUMBER(P,S):
数字型数据。
参数p指十进制数的中长度,s为该十进制数小数点后的位数,如NUMBER(10,2)表示该数字型数据的中长度为10位,而小数后为2为。
其中参数p的最大值为38,最小值为1,而参数s的最大值为124,最小值为-84。
☐ROWID也是一种数据类型,但是这种数据类型是oracle服务器使用并管理的。
首先解释ROWID的特性,通过特性可以理解ROWID的作用。
*ROWID是数据库中每一行的唯一标识符。
*ROWID作为列值是隐式存储的。
*ROWID不直接给出行的物理地址,但是可以用ROWID来定位行。
*ROWID提供了最快速地访问表中行的方法。
☐SQL>selectdeptno,dname,loc,rowid
2fromdept;
DEPTNODNAMELOCROWID
-------------------------------------------------------
10ACCOUNTINGNEWYORKAAAQ+hAAEAAAAAOAAA
20RESEARCHDALLASAAAQ+hAAEAAAAAOAAB
30SALESCHICAGOAAAQ+hAAEAAAAAOAAC
40OPERATIONSBOSTONAAAQ+hAAEAAAAAOAAD
解释:
前六位AAAQ+h为数据对象号,在数据库中每个对象是唯一的。
接着三位是AAE为相对文件号,它和表空间中的一个数据文件对应。
接着6为AAAAAO为块号,块号为相对文件中存储该行的块的位置。
最后3位AAB为行号,行号标识块头中行目录的位置,而使用该行目录的位置可以找到行的起始地址。
☐创建表
SQL>createTABLEscott.temp_employees
(employee_idnumber(6),
employee_namevarchar(30),
employee_sexchar,
departmentvarchar(30))
tablespaceusers;
☐查询创建的表
SQL>selectowner,table_name,tablespace_name
fromdba_tables
wheretable_name=‘temp_employees’;
☐临时表:
临时表是非常特殊的表,该表只对当前用户的当前会话有效。
创建临时表的目的就是使得某些操作效率更高。
临时表中的数据只对当前的会话的用户有效,是当前会话的私有数据,当前会话只操作自己的数据,没有数据锁的争用,这极大提高了临时表操作的效率。
☐创建临时表
SQL>createglobaltemporaryTABLE
2scott.emp_temporary
3oncommitpreserverows
4as
5select*
6fromscott.emp
7wherejob='MANAGER';
☐查询是否成功创建临时表
SQL>selecttable_name,tablespace_name,temporary
2fromdba_tables
3whereowner='SCOTT'
4andtable_name='EMP_TEMPORARY';
☐临时表在当前用户的当前会话下可用。
如果用户使用其他用户登录如使用dba用户,或者重新启动了数据库,则无法使用该临时表。
☐使用新的用户登录数据库,再次使用临时表,看是否可用。
SQL>conn/assysdba
已连接。
SQL>select*
2fromemp_temporary;
☐插入列
SQL>ALTERTABLEscott.employees
2add(
3degreevarchar2(10)
4);
☐修改列
SQL>ALTERTABLEscott.employees
2modify(
3degreevarchar2(10)notnull
4)
☐删除列
SQL>ALTERTABLEscott.employeesDROPCOLUMNdegree;
☐删除表(DROP)
DROPTABLE语句,此时会彻底删除表中的数据和表的结构
☐截断表(TRUNCATE)
如果只想删除表中的数据而保留表的结构可以使用TRUNCATETABLE语句截断一个表。
☐数据字典是在数据库的创建过程中创建的,它由Oracle数据库服务器管理,存储在系统表空间中,它是一个集合,这个集合由只读表和视图组成,提供所有和数据库相关的信息。
数据字典由两大类组成。
即基表和数据字典视图
☐基表:
基表无法读取,必须通过数据字典视图才转化为可读的表。
数据字典视图:
是可读的数据字典,也是DBA经常使用的数据字典。
数据字典视图由两种对象组成
☐静态数据字典表:
它基于基表而创建,容易阅读,通过使用一个名为catalog.sql脚本文件创建。
如user_tables,dba_objects等等。
☐动态性能视图:
它在数据库运行期间存在,它读取内存或控制文件的内容,提供了数据库的动态性能信息。
如v$logfile,v$database,v$instance等等。
三类主要的静态数据字典表
☐dba_xxx
描述DBA用户拥有的数据字典。
☐all_xx
描述所有用户拥有的数据字典。
☐user_xx
描述当前用户拥有的数据字典
数据字典表,查询数据库对象,我们通过例子查看如下的三个数据字典表。
☐user_tables
☐user_indexes
☐user_views
其实,我们也可以查询dba_tables,dba_indexes和user_views的数据字典表。
动态性能视图以v$开头,它只存在于运行的数据库中,它是一组虚表。
☐V$instance
☐V$database
☐V$controlfile
☐V$log
V$logfile
☐使用管理员用户登录数据库
SQL>connectsystem/oracleassysdba
☐查询数据库名和创建时间
selectname,createdfromv$database;
☐查询主机名和实例名
selecthost_name,instance_name,versionfrom$instance;
☐查询控制文件配置
selectstatus,namefromv$controlfile
☐查询重做日志配置
selectgroup#,members,status,archived
fromv$log;
☐查询数据库的归档模式。
SQL>archivedloglist
☐查询数据文件
selectfile_id,file_name,tablespace_name
fromdba_data_files;
☐查询表空间信息
selecttablespace_name,block_size,contents
fromdba_tablespaces;
☐总结
通过数据字典,我们可以了解数据库的逻辑结构和物理结构,所有数据库对象的定义,磁盘分配以及Oracle用户、角色等信息。
作为DBA要熟练使用这些数据字典以观察数据库的系统参数以及运行状态,在数据库性能诊断和性能调优时数据字典起关键作用。
☐普通视图是一个虚表,不占用存储空间,在数据字典中只有视图的定义,视图可以通过DML语言操作,但是有一定限制,因为操作视图最终还是操纵创建视图的底层表。
☐视图是一种虚表,它不存储数据,在Oracle的数据字典中只是记录了视图的定义,视图通过select语句定义。
☐在SCOTT用户模式下,有一表对象EMP,即员工表,该表记录了员工号、员工名字、工作性质、雇佣时间、薪水以及部门号等等。
为了方便每个部门查询自己部门内部员工信息,我们为每个部门创建一个视图,这样不同的部门只要使用视图就可以完成查询,而不用再使用多表连接和WHERE条件语句来限制查询的部门(虽然实际的caozuo还是一样“复杂”,但至少对使用者简单),并且通过和表DEPT的联合查询给出该部门的名字。
我们创建属于ACCOUNTING部门的员工视图。
创建视图示例:
SQL>createviewaccounting_viewas
2selecte.ename"employee_name",e.job"job",e.hiredate"hiredate",e.sal"salary",d.dname"dep_name"
3fromdeptd,empe
4wheree.deptno=d.deptno
5andd.deptno<20;
☐SQL>selectview_name
2fromuser_views;
☐SQL>selecttext
2fromuser_views
3whereview_name='ACCOUNTING_VIEW';
☐查询ACCOUNTING部门的所有员工信息
SQL>select*
2fromaccounting_view;
☐WITHREADONLY字句。
SQL>createorreplaceviewresearch_view
2("employee_name","job","hiredate","salary","dep_name")
3as
4selecte.ename,e.job,e.hiredate,e.sal,d.dname
5fromdeptd,empe
6wheree.deptno=d.deptno
7andd.deptno=20
8withreadonly;
☐试图更新使用WITHREADONLY的视图
SQL>updateresearch_view
2set"salary"=1000
3*where"job"='CLERK'
☐WITHCHECKOPTION子句。
SQL>createviewemp_viewas
2select*
3fromemp
4whereJOBIN('SALESMAN','MANAGER');
5withcheckoption;
☐验证使用了WITHCHECKOPTION的视图
SQL>insertintoemp_view(empno,ename,job,mgr,hiredate,
2sal,comm,deptno)
3values(7565,'TOM','Marketing',7998,SYSDATE,2000,22,40);
☐删除视图的指令。
SQL>dropviewemp_view;
☐验证是否成功删除视图。
SQL>selectview_name
2fromuser_views;
☐事务的由来
在各种数据库教材中都使用银行取款的例子说明事务的作用,笔者也引用大家习以为常的行为来分析事务的概念和作用。
如果读者有在ATM机转账的体验,应该很好理解事务的概念。
如果要用户A要给用户B从银行转账10000元,此时我们考虑ATM机的行为,把ATM机的行为作为一个事务。
ATM机的实施步骤如下:
✓1.从用户A的账户减少1000元。
✓2.向用户B的账户增加1000元。
上述两个步骤必须都成功执行,如果两个步骤任何一个出现问题,我们说ATM机没有正确完成这次转账行为。
谁也不希望在自己的账户上白白丢失10000元吧。
此时ATM机的两个执行步骤是不可分割行为,它要么执行成功,要么不执行(回滚所有更改的数据),ATM机的两个操作在逻辑上就可以看做一个完整的事务。
☐什么是事务
事务是一组逻辑工作单元,它有一条或多条SQL语句组成。
一个事务可以在操作的数据库对象上执行一个或多个操作,事务可以作为程序的部分功能而执行。
☐事务开始于一条可执行的SQL语句,继续执行事务主体,然后结束于以下的一种情况发生。
✓显示提交COMMIT:
当事务遇到COMMIT指令时,将结束事务并永久保存所有更改的数据到数据库文件中。
✓显示回滚ROLLBACK:
当事务遇到ROLLBACK指令时,也结束事务的执行,但是此时它回滚所有更改的数据到其原始值,即取消所有更改。
✓DDL语句:
一旦用户在使用数据定义语言时,如CREATE、DROP等,则之前的所有DML语言操作都作为事务的一部分而提交,此时称为隐式提交。
✓正常结束程序:
如果Oracle数据库应用程序正常结束,如使用SQL*PLUS工具更改了数据,而正常退出该工具程序,则Oracle自动提交事务。
非正常地结束程序:
当程序崩溃或意外中止时,所有数据更改都被回滚,类似于显示回滚操作的结果,这里是隐式回滚的,因为没有用户参与
☐事务的特点
事务的四个特性,简写为ACID特性,即四个特性的英文首字母。
下面详细介绍事务的四个特性并说明Oracle是如何实现的。
✓原子性(Atomicity):
事务要么执行成功,要么什么也不执行。
如果事务执行了一部分而系统崩溃或发生异常,则Oracle将回滚所有更改的数据,此时Oracle使用还原段管理更改数据的原始值用户事务回滚。
✓一致性(Consistency):
事务必须保持数据库保持在一致状态,如在SCOTT用户的DEPT表中删除一条记录,但是EMP表中存在雇员属于要删除的部门,那就就拒绝这样的操作执行。
即保持数据库中的数据保持在一致状态。
✓隔离性(Isolation):
隔离性使得多个用户隔离执行实现数据库的并发访问。
这种隔离性要求一个事务修改的数据在未提交前,其他事务看不到它所做的更改。
Oracle使用并发控制机制实现事务的隔离性。
✓持久性(Durability):
该特性保证提交的事务永久的保存在数据库中,在Oracle数据库中提交的数据并不是立即写入数据文件,而是先保存在数据库高速缓存中,为了防止实例崩溃,Oracle使用日志优先的方法,首先将提交的数据更改写入重做日志文件,即使实例崩溃也可以在实例恢复时,保证事务的持久性。
☐事务控制
✓使用COMMIT的显示事务控制
✓使用ROLLBACK实现事务控制
✓程序异常退出对事务的影响
✓使用AUTOCOMMIT实现事务地自动提交
Oracle提供了一种自动提交DML操作的方式,这样一旦用户执行了DML操作如UPDATE、DELETE等,数据就自动提交。
✓设置自动提交
SQL>connsystem/oracle@orcl
SQL>setautocommiton;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 创建 基础