Oracle数据库相关知识点.docx
《Oracle数据库相关知识点.docx》由会员分享，可在线阅读，更多相关《Oracle数据库相关知识点.docx（9页珍藏版）》请在冰豆网上搜索。
Oracle数据库相关知识点
通过dba_tablespaces数据字典视图可以查询当前数据库中各个表空间的区、段空间管理方式。
selecttablespace_name,extent_management,allocation_type,
segment_space_management,contentsfromdba_tablespaces
当system表空间的区管理方式是本地管理方式，则不可能创建字典管理的表空间，但可以创建本地管理的表空间。
Createtablespacedic_tbs1
datafile‘f:
\oracle\dic_tbs1.dbf’size5m
Extentmanagementdictionary;
Createtablespacedic_tbs2
datafile‘f:
\oracle\dic_tbs2.dbf’size5m
Extentmanagementlocal;
创建表空间命令格式
create[temporary|undo]tablespacetablespace_name
datafile‘path/filename’[sizeinteger[k∣m]][reuse]
[autoextend[off∣on[nextinteger[k∣m]]
[maxsize[umlimited∣integer[k∣m]]]]
[online∣offline]
[logging∣nologging]
[permanent∣temporary]
[extentmanagement[dictionary
∣local[autoallocate∣uniform[sizeinteger[k∣m]]]]]
[segmentspacemanagementauto|manual]
实例讲解Oracle数据库设置默认表空间问题
通过执行以alteruserscottaccountunlock;//然后解锁
alteruserUSERNAMEidentifiedbyNEWPASSWORD
SQL>conn/assysdba
SQL>createusertest1identifiedbytest1defaulttablespaceringidx;
用户已创建。
SQL>alterdatabasedefaulttablespacering;
数据库已更改。
SQL>createusertestidentifiedbytest;
用户已创建。
SQL>selectusername,default_tablespacedefspacefromdba_userswhere
username=’TEST’;
USERNAMEDEFSPACE
创建用户hh，并授予dba权限，指定该用户使用users表空间：
createuserhhidentifiedbyhhdefaulttablespaceusers；
创建表空间hh，修改hh用户的表空间为hh：
alteruserhhdefaulttablespacehh;
在sqlplus中使用hh用户连接数据库，并创建表test。
查看test表及其所在的表空间：
Selecttable_name,tablespace_namefromdba_tables
wheretablespace_name='HH';
创建表（命令方法）
Createtable[schema].table_name
（column_namedatatype[column_constraint[enable|disable]],..n）
[tablespacetablespace_name]
[pctfreeinteger]
[pctusedinteger]
[initransinteger]
[maxtransinteger]
[storagestorage_clause]
[assubquery]
在Oracle中查看表:
select*fromtab/dba_tables/dba_objects/cat;//查看表
selecttable_namefromuser_tables;//当前用户的表
selecttable_namefromall_tables;//所有用户的表
selecttable_namefromdba_tables;//包括系统表
修改表
altertable[schema.]table_name
/*增加新列*/
add（column_namedatatype[defaultexpression][column_constraint],…n）]
/*修改已有列的属性*/
[modify（column_name[datatype][defaultexpression][column_constraint],…n）]
/*删除约束条件*/
[dropcolumncolumn_name]
修改表stu中列“专业”的名称为zhuanye。
altertabletable_namerenamecolumnoldcolumn_nametonewcolumn_name
【例】
（1）在表stu中增加2列：
xueli（学历）和性别（sex）。
altertablestuadd学历char（6）,sexvarchar2（4）default‘男';
（2）在表stu中修改名为zhuangye的列的默认值为‘网络’。
altertablestumodifyzhuangyedefault‘网络';
（3）在表stu中删除名为sex的列。
altertablestudropcolumnsex;
（4）修改表stu的存储特征。
altertablestupctfree20maxtrans200;
（5）为表stuno加上主键。
altertablestuaddconstraint"pk_no"primarykey（“stuno"）;
select*fromuser_constraints;--查到用户的所有约束
select*fromuser_cons_columns;--获知约束建立在哪个字段上
建表后通过修改表添加约束
createtablestudent（
sidnumber（3）notnull，
namevarchar2（20）
）;
altertablestudent
modifynamevarchar2（20）notnull;
或
altertablestudent
modifynameconstraintname_nullnotnull;
唯一性约束
createtablestudent（
sidnumber（3）unique,
namevarchar2（20）
）;
createtablestudent（
sidnumber（3）,
namevarchar2（20）,
constraintstudent_sid_ununique（sid）
）;
主键约束（primarykey）
createtablestudent（
sidnumber（3）constraintpk_idprimarykey,
namevarchar2（20）
）;
createtablestudent（
sidnumber（3）,
namevarchar2（20）,
constraintstudent_sid_pkprimarykey（sid）
）;
createtableempinfo1（
eid1number（3）,
basic_salarynumber（8,2）,
job_allowancenumber（8,2）,
constraintsalary_eid_fkforeignkey（eid1）referencesempinfo（eid）-->表级约束
）;
检查约束
createtabletest1（
namevarchar2（20）primarykey
agenumber（3）check（age>=0andage<=120）
）;
创建索引的SQL命令语法格式如下所示：
CREATEINDEX索引名称
ON表名（字段名称1,字段名称2）
[TABLESPACE表空间]
[INITRANSinteger]
[MAXTRANSinteger]
[NOSORT][REVERSE]
函数索引
例：
createtableti
（idchar（3）,
namevarchar2（8））;
createindextiionti（lower（ID））
利用alterindex命令维护索引
语法格式：
alterindex[schema.]index_name
[initransinteger]
[maxtransinteger]
[pctfreeinteger]/*建立索引的物理和存储特征值*/
[storagestorage_clause]/*为索引建立存储特征*/
[renametonew_index_name]
【例】创建视图v3，该视图仅包括部门编号为10的员工信息。
createviewv3
as
select*fromempwheredeptno=10;
利用CREATESYNONYM命令创建同义词
语法格式：
CREATE[PUBLIC]SYNONYM[schema.]synonym_name
FOR[schema.]object
【例】修改上例创建的序列。
altersequenceadmin.stest
incrementby-1
maxvalue9000
minvalue4500
noorder;
返回前n行记录
【例】从emp表中检索所有的信息，要求只显示前5行数据
select*fromempwhererownum<=5
排序后再取前5条数据
select*fromempwhererownum<=5orderbysal;
只能取前5条后再排序
select*from（select*fromemporderbysal）whererownum<=5
返回表中从第m行到第n行的记录如何解决？
select*fromempwhererownum<=8
minus
select*fromempwhererownum<5
多表查询
相等连接、自然连接、带有选择条件的连接
问题:
[笛卡儿积]运算过程？
在笛卡儿积的结果中，分析存在的问题以及解决方法：
（1）两列deptno的列值不同
解决方法：
加连接条件，成为相等连接
【注】为了避免笛卡儿积，必须在WHERE子句中给出表格的连接条件：
通常对于N个表格的检索，要有N-1个连接条件
（2）出现了相同的列：
解决方法：
去掉相同的列，成为自然连接
（3）加入部门名称为“sales”的条件，成为带有选择条件的连接
18.非相等连接（比较连接）
——连接条件不是等号，而是比较运算符
【问题】检索emp表中自己不是自己的上级主管的员工信息，要求显示员工编码、员工姓名、员工上级主管编码
selectempno,ename,mgrfromempwhereemp.empno<>emp.mgr
19.自连接
——自己和自己的连接
【例】检索emp表中自己是自己的上级主管的员工信息，要求显示员工编码、员工姓名、员工上级主管编码
selectempno,ename,mgrfromempwhereemp.empno=emp.mgr
.使用EXISTS关键字
如果要解决这样的问题：
【问题】检索已经安排有员工的部门信息
select*fromdeptwhereexists
（select*fromempwheredeptno=dept.deptno）;
左外连接
Select*fromusersleftjoinuser_grouponusers.uid=user_group.uid;
内连接连接3个表
Selectusers.name,groups.namefromusers,groups,user_group
Whereusers.uid=user_group.uidandgroups.gid=user_group.gid;
全外连接
Select*fromuser_groupfulljoingroupsusing（gid）；
右外连接
Select*fromuser_grouprightjoingroupsusing（gid）；
升级会员 