Oracle表管理.pptx
《Oracle表管理.pptx》由会员分享,可在线阅读,更多相关《Oracle表管理.pptx(92页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、Oracle表管理表管理主要内容oOracle表空间o常用的数据类型o表的创建和删除o数据完整性(约束)o对数据的CRUD操作表空间o表空间是数据库的逻辑组成部分。从物理上讲,数据库数据存放在数据文件中;o从逻辑上讲,数据库则是存放在表空间中,表空间由一个或多个数据文件组成。o默认表空间是“system”通过表空间可以达到以下作用:o1.控制数据库占用的磁盘空间o2.dba可以将不同数据类型部署到不同的位置,这样有利于提高i/o性能,同时利于备份和恢复等管理操作。建立和使用表空间建立:createtablespacedata01datafiled:testdada01.dbfsize20m使用
2、:createtablestudent(snonumber(4),snamevarchar2(14)tablespacedata01;表空间名称表空间文件名表空间的大小扩展表空间1.增加数据文件altertablespace表空间名adddatafiled:testsp01.dbfsize20m;2.修改数据文件的大小alterdatabasedatafiled:testsp01.dbfresize50m;注意:数据文件的大小不要超过500m。3.设置文件的自动增长。SQLalterdatabasedatafiled:testsp01.dbfautoextendonnext10mmaxsize
3、500m;删除表空间droptablespace表空间名includingcontentsanddatafiles;说明:nincludingcontents表示删除表空间时,删除该空间的所有数据库对象,ndatafiles表示将数据库文件也删除。o1.知道表空间名,显示该表空间包括的所有表nselect*fromall_tableswheretablespace_name=表空间名;o2.知道表名,查看该表属于那个表空间nselecttablespace_name,table_namefromuser_tableswheretable_name=emp;o此处查的是scott这个用户表空间下
4、的所有表名oselecttable_namefromall_tableswhereowner=upper(scott)表名和列的命名规则o必须以字母开头o长度不能超过30个字符o不能使用oracle的保留字o只能使用如下字符A-Z,a-z,0-9,$,#等Oracle常用的数据类型字符类ochar定长最大2000个字符。ovarchar2/varchar变长最大4000个字符.注意:varchar2是oracle自己开发的,想有向后兼容的能力,建议使用varchar2。oclob(characterlargeobject)字符型大对象最大4G注意:char查询的速度极快浪费空间,查询比较多的数
5、据用。varchar2节省空间数字型onumber(p,s)范围-10的38次方到10的38次方,可以表示整数,也可以表示小数。p和s都为可选nnumber(5,2),表示一位小数有5位有效数,2位小数。范围:-999.99到999.99nnumber(5),表示一个5位整数。范围99999到-99999日期类型odate包含年月日和时分秒oracle默认格式1-1月-1999otimestamp这是oracle9i对date数据类型的扩展。可以精确到毫秒。语法timestamp(n),n指定秒的小数位数,取值范围09。缺省是。图片oblob二进制数据可以存放图片/声音4Go注意:一般来讲,在
6、真实项目中是不会把图片和声音真的往数据库里存放,一般存放图片、视频的路径,如果安全需要比较高的话,则放入数据库。创建表o实际操作修改表o添加一个字段nALTERTABLEstudentadd(sexchar(2);o修改一个字段的长度nALTERTABLEstudentMODIFY(sexchar(5);o删除一个字段不建议做(删了之后,顺序就变了。加就没问题,应为是加在后面)nALTERTABLEstudentDROPCOLUMNsex;o修改表的名字很少有这种需求nRENAME原表名TO新表名;删除表oDROPTABLEstudent;数据完整性o在oracle中,数据完整性可以使用约束、
7、触发器、应用程序(过程、函数)三种方法来实现,在这三种方法中,因为约束易于维护,并且具有最好的性能,所以作为维护数据完整性的首选。约束o约束用于确保数据库数据满足特定的商业规则。o在oracle中,约束包括:notnull、unique、primarykey、foreignkey和check五种。建表时添加约束createtablecustomer(customerIdchar(8)primarykey,-主键namevarchar2(50)notnull,-不为空addressvarchar2(50),emailvarchar2(50)unique,sexchar(2)default男che
8、ck(sexin(男,女),cardIdchar(18);表是默认建在SYSTEM表空间的建表后添加约束o使用altertable命令为表增加约束。但是要注意:增加notnull约束时,需要使用modify选项,而增加其它四种约束使用add选项。o1.增加商品名也不能为空naltertablestuInfomodifystuNamenotnull;o2.增加身份证也不能重复naltertablestuInfoaddconstraint约束名unique(stuName);o3.增加学生的住址只能是海淀,朝阳,东城,西城,通州,崇文,昌平;naltertablestuInfoaddconstra
9、int约束名check(addressin(海淀,朝阳,东城,西城,通州,崇文,昌平);删除约束o当不再需要某个约束时,可以删除。naltertable表名dropconstraint约束名称;o注意:在删除主键约束的时候,可能会有错误naltertable表名dropprimarykey;n这是因为如果在两张表存在主从关系,那么在删除主表的主键约束时,必须带上cascade选项如像:oaltertable表名dropprimarykeycascade;自动标识列ooracle里面没有标识列!o只能增加一个自增的序列,每当要用的时候调用这个序列!o创建序列createsequencetest-
10、test为序列的名称startwith1-从1开始incrementby1-每次递增1o使用序列插入数据ninsertintostuInfovalues(test.nextval,张三);向表中添加数据ooracle中默认的日期格式dd-mon-yydd日子(天)mon月份yy2位的年09-6月-99nINSERTINTOstudentVALUES(A001,张三,男,01-5月-05,10);o使用do_date函数ninsertintostudentvalues(mark,to_date(08-21-2003,MM-DD-YYYY);o修改日期的默认格式(临时修改,数据库重启后仍为默认;如
11、要修改需要修改注册表)nALTERSESSIONSETNLS_DATE_FORMAT=yyyy-mm-dd;修改表中的数据oUPDATEstudentSETsex=女WHERExh=A001;oUPDATEstudentSETsex=男,birthday=1984-04-01WHERExh=A001;删除表中的数据oDELETEFROMstudent;n删除所有记录,表结构还在,写日志,可以恢复的,速度慢。osavepointa;-创建保存点oDELETEFROMstudent;orollbacktoa;-恢复到保存点o一个有经验的DBA,在确保完成无误的情况下要定期创建还原点。oDROPTA
12、BLEstudent;-删除表的结构和数据;odeletefromstudentWHERExh=A001;-删除一条记录;otruncateTABLEstudent;-删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快。表查询o使用scott用户中的几张表作示例emp雇员表dept部门表salgrade工资级别salgrade 工资级别表工资级别表grade 级别级别losal 最低工资最低工资hisal 最高工资最高工资dept 部门表部门表deptno 部门编号部门编号Dname 部门名称部门名称loc 部门所在地点部门所在地点emp 雇员表雇员表Empno 员工编号员工
13、编号Ename 员工姓名员工姓名Job 工作工作mgr 上级的编号上级的编号hiredate 入职时间入职时间sal 月工资月工资comm 奖金奖金deptno 部门部门查询一:oSELECTename,sal,job,deptnoFROMemp;oSELECTDISTINCTdeptno,jobFROMemp;oSELECTdeptno,job,salFROMempWHEREename=SMITH;o注意:oracle对内容的大小写是区分的,所以ename=SMITH和ename=smith是不同的o如何处理null值n使用nvl函数来处理oSELECTsal*13+nvl(comm,0)*
14、13年薪,ename,commFROMemp;oSELECTename姓名,sal*12AS年收入FROMemp;o如何连接字符串(|)nSELECTename|isa|jobFROMemp;预设的值o问题:如何查找1982.1.1后入职的员工?nSELECTename,hiredateFROMempWHEREhiredate1-1月-1982;使用likeo%:表示0到多个字符_:表示任意单个字符o问题:如何显示首字符为S的员工姓名和工资?nSELECTename,salFROMempWHEREenamelikeS%;o如何显示第三个字符为大写O的所有员工的姓名和工资?nSELECTenam
15、e,salFROMempWHEREenamelike_O%;o问题:如何显示empno为7844,7839,123,456的雇员情况?nSELECT*FROMempWHEREempnoin(7844,7839,123,456);o问题:如何显示没有上级的雇员的情况?nSELECT*FROMempWHEREmgrisnull;查询二:使用逻辑操作符号o问题:查询工资高于500或者是岗位为MANAGER的雇员,同时还要满足他们的姓名首字母为大写的J?nSELECT*FROMempWHERE(sal500orjob=MANAGER)andenameLIKEJ%;o问题:如何按照工资的从低到高的顺序显
16、示雇员的信息?nSELECT*FROMempORDERbysal;o问题:按照部门号升序而雇员的工资降序排列nSELECT*FROMempORDERbydeptno,salDESC;o问题:按年薪排序nselectename,(sal+nvl(comm,0)*12年薪fromemporderby年薪asc;查询三:复杂查询o数据分组max,min,avg,sum,counto问题:如何显示所有员工中最高工资和最低工资?nSELECTMAX(sal),min(sal)FROMempe;o最高工资那个人是谁?nselectename,salfromempwheresal=(selectmax(sa
17、l)fromemp);练习:o问题:如何显示所有员工的平均工资和工资总和?o问题:如何计算总共有多少员o查询最高工资员工的名字,工作岗位o显示工资高于平均工资的员工信息groupby和having子句o问题:如何显示每个部门的平均工资和最高工资?nSELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptno;o问题:显示每个部门的每种岗位的平均工资和最低工资?nSELECTmin(sal),AVG(sal),deptno,jobFROMempGROUPbydeptno,job;o问题:显示平均工资低于2000的部门号和它的平均工资?nSELECTAVG(
18、sal),MAX(sal),deptnoFROMempGROUPbydeptnohavingAVG(sal)all(SELECTsalFROMempWHEREdeptno=30);o大家想想还有没有别的查询方法。nSELECTename,sal,deptnoFROMempWHEREsal(SELECTMAX(sal)FROMempWHEREdeptno=30);o执行效率上,函数高得多oAll等价于N个And语句在多行子查询中使用any操作符o问题:如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号?nSELECTename,sal,deptnoFROMempWHEREsal
19、ANY(SELECTsalFROMempWHEREdeptno=30);o大家想想还有没有别的查询方法。nSELECTename,sal,deptnoFROMempWHEREsal(SELECTmin(sal)FROMempWHEREdeptno=30);oAny等价于N个or语句多列子查询o查询与SMITH的部门和岗位完全相同的所有雇员。a)SELECTdeptno,jobFROMempWHEREename=SMITH;b)SELECT*FROMempWHERE(deptno,job)=(SELECTdeptno,jobFROMempWHEREename=SMITH);o1.查出各个部门的平
20、均工资和部门号nSELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno;o2.把上面的查询结果看做是一张子表nSELECTe.ename,e.deptno,e.sal,ds.mysalFROMempe,(SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno)dsWHEREe.deptno=ds.deptnoANDe.salds.mysal;小总结:o在这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名。o注意:给表取别名
21、的时候,不能加as;但是给列取别名,是可以加as的查询五:分页查询oracle的分页一共有三种方式o1.根据rowid来分nselect*fromt_xiaoxiwhererowidin(selectridfrom(selectrownumrn,ridfrom(selectrowidrid,cidfromt_xiaoxiorderbyciddesc)whererownum9980)orderbyciddesc;o执行时间0.03秒o2.按分析函数来分nselect*from(selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit)wh
22、ererk9980;o执行时间1.01秒o3.按rownum来分nselect*from(selectt.*,rownumrnfrom(select*fromt_xiaoxiorderbyciddesc)twhererownum9980;o执行时间0.1秒o1的效率最好,3次之,2最差。oselect*from(selecta1.*,rownumrnfrom(selectename,jobfromemp)a1owhererownum=5;o下面最主要介绍第三种:按rownum来分o1.rownum分页nSELECT*FROMemp;o2.显示rownumoracle分配的nSELECTe.*,
23、ROWNUMrnFROM(SELECT*FROMemp)e;注:rn相当于Oracle分配的行的ID号o3.挑选出610条记录,先查出1-10条记录nSELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM=10;o4.然后查出6-10条记录nSELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM=6;o5.几个查询变化oa.指定查询列,只需要修改最里层的子查询只查询雇员的编号和工资nSELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,sa
24、lFROMemp)eWHEREROWNUM=6;ob.排序查询,只需要修改最里层的子查询工资排序后查询6-10条数据nSELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMempORDERbysal)eWHEREROWNUM=6;用查询结果创建新表oCREATETABLEmytable(id,name,sal,job,deptno)asSELECTempno,ename,sal,job,deptnoFROMemp;o创建好之后,descmytable;和select*frommytable合并查询o有时在实际应用中,为了合并多个select
25、语句的结果,可以使用集合操作符号union,unionall,intersect,minus多用于数据量比较大的数据局库,运行速度快。o1).uniono该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行。nSELECTename,sal,jobFROMempWHEREsal2500UNIONSELECTename,sal,jobFROMempWHEREjob=MANAGER;o2).unionallo该操作符与union相似,但是它不会取消重复行,而且不会排序。oSELECTename,sal,jobFROMempWHEREsal2500oUNIONALLoSELE
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 管理
限制150内