Oracle表管理相关知识

Oracle表管理主要内容Oracle表空间常用的数据类型表的创建和删除数据完整性（约束）对数据的CRUD操作表空间表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件中；从逻辑上讲，数据库则是存放在表空间中，表空间由一个或多个数据文件组成。默认表空间是“system”

通过表空间可以达到以下作用：1.控制数据库占用的磁盘空间2.dba可以将不同数据类型部署到不同的位置，这样有利于提高i/o性能，同时利于备份和恢复等管理操作。建立和使用表空间建立：createtablespacedata01datafile'd:\test\dada01.dbf'size20m使用：createtablestudent(snonumber(4),snamevarchar2(14))

tablespacedata01;表空间名称表空间文件名表空间的大小扩展表空间1.增加数据文件altertablespace

表空间名adddatafile‘d:\test\sp01.dbf’size20m;2.修改数据文件的大小alterdatabase

datafile‘d:\test\sp01.dbf’resize50m;注意：数据文件的大小不要超过500m。3.设置文件的自动增长。SQL>alterdatabase

datafile‘d:\test\sp01.dbf’autoextendonnext10mmaxsize500m;删除表空间droptablespace

表空间名

includingcontentsanddatafiles;说明：includingcontents表示删除表空间时，删除该空间的所有数据库对象，datafiles表示将数据库文件也删除。1.知道表空间名，显示该表空间包括的所有表select*fromall_tableswheretablespace_name=’表空间名’;2.知道表名，查看该表属于那个表空间selecttablespace_name,table_namefromuser_tableswheretable_name=’emp’;此处查的是scott这个用户表空间下的所有表名selecttable_namefromall_tableswhereowner=upper('scott')表名和列的命名规则必须以字母开头长度不能超过30个字符不能使用oracle的保留字只能使用如下字符A-Z，a-z，0-9，$,#等Oracle常常用用的的数数据据类类型型字符符类类char定长长最最大大2000个字字符符。。varchar2/varchar变长长最最大大4000个字字符符.注意意：：varchar2是oracle自己己开开发发的的，，想想有有向向后后兼兼容容的的能能力力，，建建议议使使用用varchar2。clob(characterlargeobject)字符符型型大大对对象象最最大大4G注意意：：char查询询的的速速度度极极快快浪浪费费空空间间，，查查询询比比较较多多的的数数据据用用。。varchar2节省省空空间间数字字型型number(p,s)范围围-10的38次方方到到10的38次方方，，可可以以表表示示整整数数，，也也可可以以表表示示小小数数。。p和s都为为可可选选number(5,2)，表表示示一一位位小小数数有有5位有效效数，，2位小数数。范范围：：-999.99到999.99number(5)，表示示一个个5位整数数。范范围99999到-99999日期类类型date包含年年月日日和时时分秒秒oracle默认格格式1-1月-1999timestamp这是oracle9i对date数据类类型的的扩展展。可可以精精确到到毫秒秒。语法timestamp(n)，n指定秒秒的小小数位位数，，取值值范围围0～9。缺省省是６６。图片blob二进制制数据据可可以存存放图图片/声音4G注意：：一一般来来讲，，在真真实项项目中中是不不会把把图片片和声声音真真的往往数据据库里里存放放，一一般存存放图图片、、视频频的路路径，，如果果安全全需要要比较较高的的话，，则放放入数数据库库。创建表实际操作修改表添加一个字字段ALTERTABLEstudentadd(sexchar(2));修改一个字字段的长度度ALTERTABLEstudentMODIFY(sexchar(5));删除一个字字段不建建议做(删了之后，，顺序就变变了。加就就没问题，，应为是加加在后面)ALTERTABLEstudentDROPCOLUMNsex;修改表的名名字很少少有这种需需求RENAME原表名TO新表名;删除表DROPTABLEstudent;数据完整整性在oracle中，数据据完整性性可以使使用约束、触发器、应用程序序（过程、、函数））三种方方法来实实现，在在这三种种方法中中，因为为约束易于维护护，并且且具有最最好的性性能，所所以作为为维护数数据完整整性的首首选。约束约束用于于确保数数据库数数据满足足特定的的商业规规则。在oracle中，约束束包括：：notnull、unique、primarykey、foreignkey和check五种。建表时添添加约束束createtablecustomer(customerIdchar(8)primarykey,--主键namevarchar2(50)notnull,--不为空addressvarchar2(50),emailvarchar2(50)unique,sexchar(2)default‘‘男’check(sexin(‘男’，‘女’))，cardIdchar(18));表是默认建在在SYSTEM表空间的建表后添加约约束使用altertable命令为表增加加约束。但是是要注意：增增加notnull约束时，需要要使用modify选项，而增加加其它四种约约束使用add选项。1.增加商品名也也不能为空altertablestuInfomodifystuNamenotnull;2.增加身份证也也不能重复altertablestuInfoaddconstraint约束名unique(stuName);3.增加学生的住住址只能是’’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；altertablestuInfoaddconstraint约束名check(addressin(’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’));删除约束当不再需要某某个约束时，，可以删除。。altertable表名dropconstraint约束名称；注意：在删除除主键约束的的时候，可能能会有错误altertable表名dropprimarykey；这是因为如果果在两张表存存在主从关系系，那么在删删除主表的主主键约束时，，必须带上cascade选项如像：：altertable表名dropprimarykeycascade;自动标识列oracle里面没有标识识列！只能增加一个个自增的序列列，每当要用用的时候调用用这个序列！！创建序列createsequencetest--test为序列的名称称startwith1--从1开始incrementby1--每次递递增1使用序序列插插入数数据insertintostuInfovalues(test.nextval,‘‘张三');向表中中添加加数据据oracle中默认认的日日期格格式‘‘dd-mon-yy’dd日子（（天））mon月份yy2位的年年‘‘09-6月-99’INSERTINTOstudentVALUES('A001','张三','男','01-5月-05',10);使用用do_date函数数insertintostudentvalues('mark',to_date('08-21-2003','MM-DD-YYYY'));修改改日日期期的的默默认认格格式式（（临临时时修修改改，，数数据据库库重重启启后后仍仍为为默默认认；；如如要要修修改改需需要要修修改改注注册册表表））ALTERSESSIONSETNLS_DATE_FORMAT='yyyy-mm-dd';修改改表表中中的的数数据据UPDATEstudentSETsex='女女'WHERExh='A001';UPDATEstudentSETsex='男男',birthday='1984-04-01'WHERExh='A001';删除除表表中中的的数数据据DELETEFROMstudent;删除除所所有有记记录录，，表表结结构构还还在在，，写写日日志志，，可可以以恢恢复复的的，，速速度度慢慢。。savepointa;--创建建保保存存点点DELETEFROMstudent;rollbacktoa;--恢复复到到保保存存点点一个个有有经经验验的的DBA，在在确确保保完完成成无无误误的的情情况况下下要要定定期期创创建建还还原原点点。。DROPTABLEstudent;--删除除表表的的结结构构和和数数据据；；deletefromstudentWHERExh='A001';--删除一条条记录；；truncateTABLEstudent;--删除表中中的所有有记录，，表结构构还在，，不写日日志，无无法找回回删除的的记录，，速度快快。表查询使用scott用户中的的几张表表作示例例emp雇员表dept部门表salgrade工资级别别salgrade工资级别别表grade级别losal最低工资资hisal最高工资资dept部门表deptno部部门编编号Dname部门名称称loc部部门所所在地点点emp雇员表Empno员工编号号Ename员工姓名名Job工作mgr上上级的的编号号hiredate入入职职时间间sal月月工资资comm奖奖金金deptno部部门门查询一一：SELECTename,sal,job,deptnoFROMemp;SELECTDISTINCTdeptno,jobFROMemp;SELECTdeptno,job,salFROMempWHEREename='SMITH';注意：：oracle对内容的的大小小写是是区分分的，所以以ename='SMITH'和ename='smith'是不同同的如何处处理null值使用nvl函数来来处理理SELECTsal*13+nvl(comm,0)*13"年薪",ename,commFROMemp;SELECTename"姓名",sal*12AS"年收入"FROMemp;如何连接字字符串（||）SELECTename||'isa'||jobFROMemp;预设的值问题：如何何查找后入职的员员工？SELECTename,hiredateFROMempWHEREhiredate>'1-1月-1982';使用like%：表示0到多个字符符_：表示任意意单个字符符问题：如何何显示首字字符为S的员工姓名名和工资？？SELECTename,salFROMempWHEREenamelike'S%';如何显示第第三个字符符为大写O的所有员工工的姓名和和工资？SELECTename,salFROMempWHEREenamelike'__O%';问题：如何何显示empno为7844,7839,123,456的雇员情况况？SELECT*FROMempWHEREempnoin(7844,7839,123,456);问题：如何何显示没有有上级的雇雇员的情况况？SELECT*FROMempWHEREmgrisnull;查询二：使使用逻辑操操作符号问题：查询询工资高于于500或者是岗位位为MANAGER的雇员，同同时还要满满足他们的的姓名首字字母为大写写的J？SELECT*FROMempWHERE(sal>500orjob='MANAGER')andenameLIKE'J%';问题：如何何按照工资资的从低到到高的顺序序显示雇员员的信息？？SELECT*FROMempORDERbysal;问题：按照照部门号升升序而雇员员的工资降降序排列SELECT*FROMempORDERbydeptno,salDESC;问题：按年年薪排序selectename,(sal+nvl(comm,0))*12"年薪"fromemporderby"年薪"asc;查询三：复复杂查询数据分组——max，min，avg，sum，count问题：如何何显示所有有员工中最最高工资和和最低工资资？SELECTMAX(sal),min(sal)FROMempe;最高工资那那个人是谁谁？selectename,salfromempwheresal=(selectmax(sal)fromemp);练习：问题：如何何显示所有有员工的平平均工资和和工资总和和？问题：如何何计算总共共有多少员员查询最高工工资员工的的名字，工工作岗位显示工资高高于平均工工资的员工工信息groupby和和having子句问题：如何何显示每个个部门的平平均工资和和最高工资资？SELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptno;问题：显示示每个部门门的每种岗岗位的平均均工资和最最低工资？？SELECTmin(sal),AVG(sal),deptno,jobFROMempGROUPbydeptno,job;问题：显示示平均工资资低于2000的部门号和和它的平均均工资？SELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptnohavingAVG(sal)<2000;查询四：多多表查询问题：显示示雇员名，，雇员工资资及所在部部门的名字字SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptno;问题：显示示部门号为为10的部门名、、员工名和和工资？SELECTd.dname,e.ename,e.salFROMempe,deptdWHEREe.deptno=d.deptnoande.deptno=10;问题：显示示各个员工工的姓名，，工资及工工资的级别别？SELECTe.ename,e.sal,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal;扩展要求：：问题：显示示雇员名，，雇员工资资及所在部部门的名字字，并按部部门排序？？SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.deptno=d.deptnoORDERbye.deptno;自连接自连接是指指在同一张张表的连接接查询问题：显示示某个员工工的上级领领导的姓名名？比如显显示员工‘‘FORD’’的上级SELECTworker.ename,boss.enameFROMempworker,empbossWHEREworker.mgr=boss.empnoANDworker.ename='FORD';子查查询询1查询询出出SMITH的部部门门号号selectdeptnofromempWHEREename='SMITH';2显示示SELECT*FROMempWHEREdeptno=(selectdeptnofromempWHEREename='SMITH');请思思考考：：如如何何查查询询和和部部门门10的工工作作相相同同的的雇雇员员的的名名字字、、岗岗位位、、工工资资、、部部门门号号SELECT*FROMempWHEREjobIN(SELECTDISTINCTjobFROMempWHEREdeptno=10);在多多行行子子查查询询中中使使用用all操作作符符问题题：：如如何何显显示示工工资资比比部部门门30的所所有有员员工工的的工工资资高高的的员员工工的的姓姓名名、、工工资资和和部部门门号号？？SELECTename,sal,deptnoFROMempWHEREsal>all(SELECTsalFROMempWHEREdeptno=30);大家想想还有有没有别的查查询方法。SELECTename,sal,deptnoFROMempWHEREsal>(SELECTMAX(sal)FROMempWHEREdeptno=30);执行效率上，，函数高得得多All等价于N个And语句在多行子查询询中使用any操作符符问题：如何显显示工资比部部门30的任意一个员员工的工资高高的员工姓名名、工资和部部门号？SELECTename,sal,deptnoFROMempWHEREsal>ANY(SELECTsalFROMempWHEREdeptno=30);大家想想还有有没有别的查查询方法。SELECTename,sal,deptnoFROMempWHEREsal>(SELECTmin(sal)FROMempWHEREdeptno=30);Any等价于于N个or语句多列子子查询询查询与与SMITH的部门门和岗岗位完完全相相同的的所有有雇员员。a)SELECTdeptno,jobFROMempWHEREename='SMITH';b)SELECT*FROMempWHERE(deptno,job)=(SELECTdeptno,jobFROMempWHEREename='SMITH');1.查出各各个部部门的的平均均工资资和部部门号号SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno;2.把上面面的查查询结结果看看做是是一张张子表表SELECTe.ename,e.deptno,e.sal,ds.mysalFROMempe,(SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno)dsWHEREe.deptno=ds.deptnoANDe.sal>ds.mysal;小总结结：在这里里需要要说明明的当当在from子句中中使用用子查查询时时，该该子查查询会会被作作为一一个视视图来来对待待，因因此叫叫做内内嵌视视图，，当在在from子句中使用用子查询时时，必须给给子查询指指定别名。。注意：给表表取别名的的时候，不不能加as；但是给列列取别名，，是可以加加as的查询五：分分页查询oracle的分页一共共有三种方方式1.根据rowid来分select*fromt_xiaoxiwhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,cidfromt_xiaoxiorderbyciddesc)whererownum<10000)wherern>9980)orderbyciddesc;执行时间0.03秒2.按分析函数数来分select*from(selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit)whererk<10000andrk>9980;执行时间1.01秒3.按rownum来分select*from(selectt.*,rownumrnfrom(select*fromt_xiaoxiorderbyciddesc)twhererownum<10000)wherern>9980;执行时间0.1秒1的效率率最好，3次之，，2最差差。select*from(selecta1.*,rownumrnfrom(selectename,jobfromemp)a1whererownum<=10)wherern>=5;下面最主要要介绍第三三种：按rownum来分1.rownum分页SELECT*FROMemp;2.显示rownum[oracle分配的]SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)e;注：rn相当于Oracle分配的行的的ID号3.挑选出6—10条记录,先查出1-10条记录SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM<=10;4.然后查出6-10条记录SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM<=10)WHERErn>=6;5.几个查询变变化a.指定查询列列，只需要要修改最里里层的子查查询只查询询雇员的编编号和工资资SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMemp)eWHEREROWNUM<=10)WHERErn>=6;b.排序查询，，只需要修修改最里层层的子查询询工资排序序后查询6-10条数据SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMempORDERbysal)eWHEREROWNUM<=10)WHERErn>=6;用查询结果果创建新表表CREATETABLEmytable(id,name,sal,job,deptno)asSELECTempno,ename,sal,job,deptnoFROMemp;创建好之后后，descmytable；和select*frommytable合并查询有时在实际际应用中，，为了合并并多个select语句的结果果，可以使使用集合操操作符号union，unionall，intersect，minus多用于数据据量比较大大的数据局局库，运行行速度快。。1).union该操作符用用于取得两两个结果集集的并集。。当使用该该操作符时时，会自动动去掉结果果集中重复复行。SELECTename,sal,jobFROMempWHEREsal>2500UNIONSELECTename,sal,jobFROMempWHEREjob='MANAGER';2).unionall该操作符与与union相似，但是是它不会取取消重复行行，而且不不会排序。。SELECTename,sal,jobFROMempWHEREsal>2500UNIONALLSELECTename,sal,jobFROMempWHEREjob='MANAGER';该操作符符用于取取得两个个结果集集的并集集。当使使用该操操作符时时，会自自动去掉掉结果集集中重复行。。3).intersect使用该操操作符用用于取得得两个结结果集的的交集。。SELECTename,sal,jobFROMempWHEREsal>2500INTERSECTSELECTename,sal,jobFROMempWHEREjob='MANAGER';4).minus使用改操操作符用用于取得得两个结结果集的的差集，，他只会会显示存存在第一一个集合合中，而而不存在第二二个集合合中的数数据。SELECTename,sal,jobFROMempWHEREsal>2500MINUSSELECTename,sal,jobFROMempWHEREjob='MANAGER';（MINUS就是减法的意意思）Oracle中常用函数数字符函数upper(char)：将字符串转转化为大写的的格式。length(char)：返回字符串串的长度。substr(char,m,n)：取字符串的的子串n代表取n个的意思，不不是代表取到第n个replace(char1,search_string,replace_string)instr(char1,char2,[,n[,m]])取子串在字符符串的位置问题：将所有有员工的名字字按小写的方方式显示SQL>selectlower(ename)fromemp;问题：将所有有员工的名字字按大写的方方式显示。SQL>selectupper(ename)fromemp;问题：显示正正好为5个字符的员工工的姓名。SQL>select*fromempwherelength(ename)=5;问题：显示所所有员工姓名名的前三个字字符。SQL>selectsubstr(ename,1,3)fromemp;问题：以首字字母大写,后面小写的方方式显示所有有员工的姓名名。SQL>selectupper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1))fromemp;问题：以首首字母小写写,后面大写的的方式显示示所有员工工的姓名。。SQL>selectlower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1))fromemp;问题：显示示所有员工工的姓名，，用“我是是老虎”替替换所有““A”SQL>selectreplace(ename,'A','我是老虎')fromemp;数学函数数学函数的的输入参数数和返回值值的数据类类型都是数数字类型的的。数学函函数包括cos，cosh，，exp，，ln,log，，sin，，sinh，sqrt，tan，tanh，acos，，asin，atan，round，，round(n,[m])该函数用于于执行四舍舍五入，如如果省掉m，则四舍五五入到整数数，如果m是正数，则则四舍五入入到小数点点的m位后。如果m是负数，则四四舍五入到小小数点的m位前。trunc(n,[m])该函数用于截截取数字。如如果省掉m，就截去小数数部分，如果果m是正数就截取取到小数点的的m位后，如果m是负数，则截截取到小数点点的前m位。mod(m,n)floor(n)返回小于或是是等于n的最大整数ceil(n)返回大于或是是等于n的最小小整数数对数字字的处处理，，在财财务系系统或或银行行系统统中用用的最最多，，不同同的处处理方方法，，对财财务报报表有不不同的的结果果。问题：：显示示在一一个月月为30天的情情况下下，所所有员员工的的日薪薪金，，忽略略余数数。SQ