Oracle表管理相关知识

Oracle表管理,主要内容,Oracle表空间常用的数据类型表的创建和删除数据完整性（约束）对数据的CRUD操作,表空间,表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件中；从逻辑上讲，数据库则是存放在表空间中，表空间由一个或多个数据文件组成。默认表空间是“system”,通过表空间可以达到以下作用：,1.控制数据库占用的磁盘空间2.dba可以将不同数据类型部署到不同的位置，这样有利于提高i/o性能，同时利于备份和恢复等管理操作。,建立和使用表空间,建立：createtablespacedata01datafiled:testdada01.dbfsize20m使用：createtablestudent(snonumber(4),snamevarchar2(14)tablespacedata01;,表空间名称,表空间文件名,表空间的大小,扩展表空间,1.增加数据文件altertablespace表空间名adddatafiled:testsp01.dbfsize20m;2.修改数据文件的大小alterdatabasedatafiled:testsp01.dbfresize50m;注意：数据文件的大小不要超过500m。3.设置文件的自动增长。SQLalterdatabasedatafiled:testsp01.dbfautoextendonnext10mmaxsize500m;,删除表空间,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指定秒的小数位数，取值范围09。缺省是。,图片,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-yydd日子（天）mon月份yy2位的年09-6月-99INSERTINTOstudentVALUES(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-01WHERExh=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,hiredateFROMempWHEREhiredate1-1月-1982;,使用like,%：表示0到多个字符_：表示任意单个字符问题：如何显示首字符为S的员工姓名和工资？SELECTename,salFROMempWHEREenamelikeS%;如何显示第三个字符为大写O的所有员工的姓名和工资？SELECTename,salFROMempWHEREenamelike_O%;,问题：如何显示empno为7844,7839,123,456的雇员情况？SELECT*FROMempWHEREempnoin(7844,7839,123,456);问题：如何显示没有上级的雇员的情况？SELECT*FROMempWHEREmgrisnull;,查询二：使用逻辑操作符号,问题：查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J？SELECT*FROMempWHERE(sal500orjob=MANAGER)andenameLIKEJ%;,问题：如何按照工资的从低到高的顺序显示雇员的信息？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)all(SELECTsalFROMempWHEREdeptno=30);大家想想还有没有别的查询方法。SELECTename,sal,deptnoFROMempWHEREsal(SELECTMAX(sal)FROMempWHEREdeptno=30);执行效率上，函数高得多All等价于N个And语句,在多行子查询中使用any操作符,问题：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号？SELECTename,sal,deptnoFROMempWHEREsalANY(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.salds.mysal;,小总结：,在这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。注意：给表取别名的时候，不能加as；但是给列取别名，是可以加as的,查询五：分页查询,oracle的分页一共有三种方式1.根据rowid来分select*fromt_xiaoxiwhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,cidfromt_xiaoxiorderbyciddesc)whererownum9980)orderbyciddesc;执行时间0.03秒,2.按分析函数来分select*from(selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit)whererk9980;执行时间1.01秒3.按rownum来分select*from(selectt.*,rownumrnfrom(select*fromt_xiaoxiorderbyciddesc)twhererownum9980;执行时间0.1秒,1的效率最好，3次之，2最差。,select*from(selecta1.*,rownumrnfrom(selectename,jobfromemp)a1whererownum=5;,下面最主要介绍第三种：按rownum来分1.rownum分页SELECT*FROMemp;2.显示rownumoracle分配的SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)e;注：rn相当于Oracle分配的行的ID号3.挑选出610条记录,先查出1-10条记录SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM=6;,5.几个查询变化a.指定查询列，只需要修改最里层的子查询只查询雇员的编号和工资SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMemp)eWHEREROWNUM=6;b.排序查询，只需要修改最里层的子查询工资排序后查询6-10条数据SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMempORDERbysal)eWHEREROWNUM=6;,用查询结果创建新表,CREATETABLEmytable(id,name,sal,job,deptno)asSELECTempno,ename,sal,job,deptnoFROMemp;创建好之后，descmytable；和select*frommytable,合并查询,有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，unionall，intersect，minus多用于数据量比较大的数据局库，运行速度快。1).union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。SELECTename,sal,jobFROMempWHEREsal2500UNIONSELECTename,sal,jobFROMempWHEREjob=MANAGER;,2).unionall该操作符与union相似，但是它不会取消重复行，而且不会排序。SELECTename,sal,jobFROMempWHEREsal2500UNIONALLSELECTename,sal,jobFROMempWHEREjob=MANAGER;该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。3).intersect使用该操作符用于取得两个结果集的交集。SELECTename,sal,jobFROMempWHEREsal2500INTERSECTSELECTename,sal,jobFROMempWHEREjob=MANAGER;4).minus使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。SELECTename,sal,jobFROMempWHEREsal2500MINUSSELECTename,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)取子串在字符串的位置,问题：将所有员工的名字按小写的方式显示SQLselectlower(ename)fromemp;问题：将所有员工的名字按大写的方式显示。SQLselectupper(ename)fromemp;问题：显示正好为5个字符的员工的姓名。SQLselect*fromempwherelength(ename)=5;问题：显示所有员工姓名的前三个字符。SQLselectsubstr(ename,1,3)fromemp;问题：以首字母大写,后面小写的方式显示所有员工的姓名。SQLselectupper(substr(ename,1,1)|lower(substr(ename,2,length(ename)-1)fromemp;问题：以首字母小写,后面大写的方式显示所有员工的姓名。SQLselectlower(substr(ename,1,1)|upper(substr(ename,2,length(ename)-1)fromemp;问题：显示所有员工的姓名，用“我是老虎”替换所有“A”SQLselectreplace(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天的情况下，所有员工的日薪金，忽略余数。SQLselecttrunc(sal/30),enamefromemp;orSQ