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;预设的值问题：如何查查找1982.1.1后入职的员工工？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天的情情况下下，所所有员员工的的日薪薪金，，忽略略余数数。SQL>selecttrunc(sal/30),enamefromemp;orSQ