Oracle表管理相关知识8067.pptx

《Oracle表管理相关知识8067.pptx》由会员分享，可在线阅读，更多相关《Oracle表管理相关知识8067.pptx（67页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、Oracle 表管理主要内容o Oracle 表空间o 常用的数据类型o 表的创建和删除o 数据完整性（约束）o 对数据的CRUD 操作表空间o 表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件中；o 从逻辑上讲，数据库则是存放在表空间中，表空间由一个或多个数据文件组成。o 默认表空间是“system”通过表空间可以达到以下作用：o 1.控制数据库占用的磁盘空间o 2.dba 可以将不同数据类型部署到不同的位置，这样有利于提高i/o 性能，同时利于备份和恢复等管理操作。建立和使用表空间建立：createtablespacedata01datafiled:testdada01.

2、dbfsize20m使用：createtablestudent(snonumber(4),snamevarchar2(14)tablespacedata01;表空间名称表空间文件名表空间的大小扩展表空间1.增加数据文件altertablespace 表空间名adddatafiled:testsp01.dbfsize20m;2.修改数据文件的大小alterdatabasedatafiled:testsp01.dbfresize50m;注意：数据文件的大小不要超过500m。3.设置文件的自动增长。SQLalterdatabasedatafiled:testsp01.dbfautoextendonn

3、ext10mmaxsize500m;删除表空间droptablespace 表空间名includingcontentsanddatafiles;说明：n includingcontents 表示删除表空间时，删除该空间的所有数据库对象，n datafiles 表示将数据库文件也删除。o 1.知道表空间名，显示该表空间包括的所有表n select*fromall_tableswheretablespace_name=表空间名;o 2.知道表名，查看该表属于那个表空间n selecttablespace_name,table_namefromuser_tableswheretable_name=e

4、mp;o 此处查的是scott 这个用户表空间下的所有表名o selecttable_namefromall_tableswhereowner=upper(scott)表名和列的命名规则o 必须以字母开头o 长度不能超过30 个字符o 不能使用oracle 的保留字o 只能使用如下字符A-Z，a-z，0-9，$,#等Oracle 常用的数据类型字符类o char 定长 最大2000 个字符。o varchar2/varchar 变长最大4000 个字符.注意：varchar2 是oracle 自己开发的，想有向后兼容的能力，建议使用varchar2。o clob(characterlargeo

5、bject)字符型大对象 最大4G注意：char 查询的速度极快浪费空间，查询比较多的数据用。varchar2 节省空间数字型o number(p,s)范围-10 的38 次方 到10的38 次方，可以表示整数，也可以表示小数。p 和s 都为可选n number(5,2)，表示一位小数有5 位有效数，2 位小数。范围：-999.99 到999.99n number(5)，表示一个5 位整数。范围99999到-99999日期类型o date 包含年月日和时分秒oracle 默认格式1-1 月-1999o timestamp 这是oracle9i 对date 数据类型的扩展。可以精确到毫秒。语法t

6、imestamp(n)，n 指定秒的小数位数，取值范围0 9。缺省是。图片o blob 二进制数据 可以存放图片/声音4Go 注意：一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。创建表o 实际操作修改表o 添加一个字段n ALTERTABLEstudentadd(sexchar(2);o 修改一个字段的长度n ALTERTABLEstudentMODIFY(sexchar(5);o 删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)n ALTERTABLEstudentDROPCOLUMNse

7、x;o 修改表的名字 很少有这种需求n RENAME 原表名TO 新表名;删除表o DROPTABLEstudent;数据完整性o 在oracle 中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。约束o 约束用于确保数据库数据满足特定的商业规则。o 在oracle 中，约束包括：notnull、unique、primarykey、foreignkey 和check 五种。建表时添加约束createtablecustomer(customerIdchar(8)primarykey,-主键

8、namevarchar2(50)notnull,-不为空addressvarchar2(50),emailvarchar2(50)unique,sexchar(2)default 男check(sexin(男，女)，cardIdchar(18);表是默认建在SYSTEM 表空间的建表后添加约束o 使用altertable 命令为表增加约束。但是要注意：增加notnull约束时，需要使用modify 选项，而增加其它四种约束使用add选项。o 1.增加商品名也不能为空n altertablestuInfomodifystuNamenotnull;o 2.增加身份证也不能重复n altertabl

9、estuInfoaddconstraint 约束名unique(stuName);o 3.增加学生的住址只能是 海淀,朝阳,东城,西城,通州,崇文,昌平；n altertablestuInfoaddconstraint 约束名check(addressin(海淀,朝阳,东城,西城,通州,崇文,昌平);删除约束o 当不再需要某个约束时，可以删除。n altertable 表名dropconstraint 约束名称；o 注意：在删除主键约束的时候，可能会有错误n altertable 表名dropprimarykey；n 这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cas

10、cade 选项 如像：o altertable 表名dropprimarykeycascade;自动标识列o oracle 里面没有标识列！o 只能增加一个自增的序列，每当要用的时候调用这个序列！o 创建序列createsequencetest-test 为序列的名称startwith1-从1 开始incrementby1-每次递增1o 使用序列插入数据n insertintostuInfovalues(test.nextval,张三);向表中添加数据o oracle 中默认的日期格式dd-mon-yydd 日子（天）mon月份yy2 位的年09-6 月-99n INSERTINTOstude

11、ntVALUES(A001,张三,男,01-5 月-05,10);o 使用do_date 函数n insertintostudentvalues(mark,to_date(08-21-2003,MM-DD-YYYY);o 修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）n ALTERSESSIONSETNLS_DATE_FORMAT=yyyy-mm-dd;修改表中的数据o UPDATEstudentSETsex=女WHERExh=A001;o UPDATEstudentSETsex=男,birthday=1984-04-01WHERExh=A001;删除表中的数据o

12、 DELETEFROMstudent;n 删除所有记录，表结构还在，写日志，可以恢复的，速度慢。o savepointa;-创建保存点o DELETEFROMstudent;o rollbacktoa;-恢复到保存点o 一个有经验的DBA，在确保完成无误的情况下要定期创建还原点。o DROPTABLEstudent;-删除表的结构和数据；o deletefromstudentWHERExh=A001;-删除一条记录；o truncateTABLEstudent;-删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。表查询o 使用scott 用户中的几张表作示例emp 雇员表d

13、ept 部门表salgrade 工资级别salgrade 工资级别表grade 级别losal 最低工资hisal 最高工资dept 部门表deptno 部门编号Dname 部门名称loc 部门所在地点emp 雇员表Empno 员工编号Ename 员工姓名Job 工作mgr 上级的编号hiredate 入职时间sal 月工资comm 奖金deptno 部门查询一：o SELECTename,sal,job,deptnoFROMemp;o SELECTDISTINCTdeptno,jobFROMemp;o SELECTdeptno,job,salFROMempWHEREename=SMITH;o

14、 注意：oracle 对内容的大小写是区分的，所以ename=SMITH 和ename=smith是不同的o 如何处理null 值n 使用nvl 函数来处理o SELECTsal*13+nvl(comm,0)*13 年薪,ename,commFROMemp;o SELECTename 姓名,sal*12AS 年收入FROMemp;o 如何连接字符串（|）n SELECTename|isa|jobFROMemp;预设的值o 问题：如何查找1982.1.1 后入职的员工？n SELECTename,hiredateFROMempWHEREhiredate1-1 月-1982;使用likeo%：表示

15、0 到多个字符_：表示任意单个字符o 问题：如何显示首字符为S 的员工姓名和工资？n SELECTename,salFROMempWHEREenamelikeS%;o 如何显示第三个字符为大写O 的所有员工的姓名和工资？n SELECTename,salFROMempWHEREenamelike_O%;o 问题：如何显示empno 为7844,7839,123,456 的雇员情况？n SELECT*FROMempWHEREempnoin(7844,7839,123,456);o 问题：如何显示没有上级的雇员的情况？n SELECT*FROMempWHEREmgrisnull;查询二：使用逻辑操

16、作符号o 问题：查询工资高于500 或者是岗位为MANAGER 的雇员，同时还要满足他们的姓名首字母为大写的J？n SELECT*FROMempWHERE(sal500orjob=MANAGER)andenameLIKEJ%;o 问题：如何按照工资的从低到高的顺序显示雇员的信息？n SELECT*FROMempORDERbysal;o 问题：按照部门号升序而雇员的工资降序排列n SELECT*FROMempORDERbydeptno,salDESC;o 问题：按年薪排序n selectename,(sal+nvl(comm,0)*12 年薪fromemporderby 年薪asc;查询三：复杂

17、查询o 数据分组max，min，avg，sum，counto 问题：如何显示所有员工中最高工资和最低工资？n SELECTMAX(sal),min(sal)FROMempe;o 最高工资那个人是谁？n selectename,salfromempwheresal=(selectmax(sal)fromemp);练习：o 问题：如何显示所有员工的平均工资和工资总和？o 问题：如何计算总共有多少员o 查询最高工资员工的名字，工作岗位o 显示工资高于平均工资的员工信息groupby 和having 子句o 问题：如何显示每个部门的平均工资和最高工资？n SELECTAVG(sal),MAX(sal)

18、,deptnoFROMempGROUPbydeptno;o 问题：显示每个部门的每种岗位的平均工资和最低工资？n SELECTmin(sal),AVG(sal),deptno,jobFROMempGROUPbydeptno,job;o 问题：显示平均工资低于2000 的部门号和它的平均工资？n SELECTAVG(sal),MAX(sal),deptnoFROMempGROUPbydeptnohavingAVG(sal)2000;查询四：多表查询o 问题：显示雇员名，雇员工资及所在部门的名字n SELECTe.ename,e.sal,d.dnameFROMempe,deptdWHEREe.de

19、ptno=d.deptno;o 问题：显示部门号为10 的部门名、员工名和工资？n SELECTd.dname,e.ename,e.salFROMempe,deptdWHEREe.deptno=d.deptnoande.deptno=10;o 问题：显示各个员工的姓名，工资及工资的级别？n SELECTe.ename,e.sal,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal;o 扩展要求：o 问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？n SELECTe.ename,e.sal,d.dnameFROMemp

20、e,deptdWHEREe.deptno=d.deptnoORDERbye.deptno;自连接o 自连接是指在同一张表的连接查询o 问题：显示某个员工的上级领导的姓名？比如显示员工FORD 的上级n SELECTworker.ename,boss.enameFROMempworker,empbossWHEREworker.mgr=boss.empnoANDworker.ename=FORD;子查询o 1 查询出SMITH 的部门号n selectdeptnofromempWHEREename=SMITH;o 2 显示n SELECT*FROMempWHEREdeptno=(selectdep

21、tnofromempWHEREename=SMITH);o 请思考：如何查询和部门10 的工作相同的雇员的名字、岗位、工资、部门号n SELECT*FROMempWHEREjobIN(SELECTDISTINCTjobFROMempWHEREdeptno=10);在多行子查询中使用all 操作符o 问题：如何显示工资比部门30 的所有员工的工资高的员工的姓名、工资和部门号？n SELECTename,sal,deptnoFROMempWHEREsalall(SELECTsalFROMempWHEREdeptno=30);o 大家想想还有没有别的查询方法。n SELECTename,sal,de

22、ptnoFROMempWHEREsal(SELECTMAX(sal)FROMempWHEREdeptno=30);o 执行效率上，函数高得多o All 等价于N 个And 语句在多行子查询中使用any 操作符o 问题：如何显示工资比部门30 的任意一个员工的工资高的员工姓名、工资和部门号？n SELECTename,sal,deptnoFROMempWHEREsalANY(SELECTsalFROMempWHEREdeptno=30);o 大家想想还有没有别的查询方法。n SELECTename,sal,deptnoFROMempWHEREsal(SELECTmin(sal)FROMempWH

23、EREdeptno=30);o Any 等价于N 个or 语句多列子查询o 查询与SMITH 的部门和岗位完全相同的所有雇员。a)SELECTdeptno,jobFROMempWHEREename=SMITH;b)SELECT*FROMempWHERE(deptno,job)=(SELECTdeptno,jobFROMempWHEREename=SMITH);o 1.查出各个部门的平均工资和部门号n SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno;o 2.把上面的查询结果看做是一张子表n SELECTe.ename,e.deptno,e.sal,d

24、s.mysalFROMempe,(SELECTdeptno,AVG(sal)mysalFROMempGROUPbydeptno)dsWHEREe.deptno=ds.deptnoANDe.salds.mysal;小总结：o 在这里需要说明的当在from 子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from 子句中使用子查询时，必须给子查询指定别名。o 注意：给表取别名的时候，不能加as；但是给列取别名，是可以加as 的查询五：分页查询oracle 的分页一共有三种方式o 1.根据rowid 来分n select*fromt_xiaoxiwhererowidin(s

25、electridfrom(selectrownumrn,ridfrom(selectrowidrid,cidfromt_xiaoxiorderbyciddesc)whererownum9980)orderbyciddesc;o 执行时间0.03 秒o 2.按分析函数来分n select*from(selectt.*,row_number()over(orderbyciddesc)rkfromt_xiaoxit)whererk9980;o 执行时间1.01 秒o 3.按rownum 来分n select*from(selectt.*,rownumrnfrom(select*fromt_xiaox

26、iorderbyciddesc)twhererownum9980;o 执行时间0.1 秒o 1 的效率最好，3 次之，2 最差。o select*from(selecta1.*,rownumrnfrom(selectename,jobfromemp)a1o whererownum=5;o 下面最主要介绍第三种：按rownum 来分o 1.rownum 分页n SELECT*FROMemp;o 2.显示rownumoracle 分配的n SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)e;注：rn 相当于Oracle 分配的行的ID 号o 3.挑选出610 条记录,先

27、查出1-10 条记录n SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM=10;o 4.然后查出6-10 条记录n SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECT*FROMemp)eWHEREROWNUM=6;o 5.几个查询变化o a.指定查询列，只需要修改最里层的子查询只查询雇员的编号和工资n SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMemp)eWHEREROWNUM=6;o b.排序查询，只需要修改最里层的子查询工资排序后查询6-10

28、条数据n SELECT*FROM(SELECTe.*,ROWNUMrnFROM(SELECTename,salFROMempORDERbysal)eWHEREROWNUM=6;用查询结果创建新表o CREATETABLEmytable(id,name,sal,job,deptno)asSELECTempno,ename,sal,job,deptnoFROMemp;o 创建好之后，descmytable；和select*frommytable合并查询o 有时在实际应用中，为了合并多个select 语句的结果，可以使用集合操作符号union，unionall，intersect，minus 多用于

29、数据量比较大的数据局库，运行速度快。o 1).uniono 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。n SELECTename,sal,jobFROMempWHEREsal2500UNIONSELECTename,sal,jobFROMempWHEREjob=MANAGER;o 2).unionallo 该操作符与union 相似，但是它不会取消重复行，而且不会排序。o SELECTename,sal,jobFROMempWHEREsal2500o UNIONALLo SELECTename,sal,jobFROMempWHEREjob=MANAGER;o

30、 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中o 重复行。o 3).intersecto 使用该操作符用于取得两个结果集的交集。o SELECTename,sal,jobFROMempWHEREsal2500o INTERSECTo SELECTename,sal,jobFROMempWHEREjob=MANAGER;o 4).minuso 使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不o 存在第二个集合中的数据。o SELECTename,sal,jobFROMempWHEREsal2500o MINUSo SELECTename,sal,j

31、obFROMempWHEREjob=MANAGER;o（MINUS 就是减法的意思）Oracle 中常用函数o 字符函数o upper(char)：将字符串转化为大写的格式。o length(char)：返回字符串的长度。o substr(char,m,n)：取字符串的子串n 代表取n 个的意思，不是代表取o 到第n 个o replace(char1,search_string,replace_string)o instr(char1,char2,n,m)取子串在字符串的位置o 问题：将所有员工的名字按小写的方式显示o SQLselectlower(ename)fromemp;o 问题：将所有

32、员工的名字按大写的方式显示。o SQLselectupper(ename)fromemp;o 问题：显示正好为5 个字符的员工的姓名。o SQLselect*fromempwherelength(ename)=5;o 问题：显示所有员工姓名的前三个字符。o SQLselectsubstr(ename,1,3)fromemp;o 问题：以首字母大写,后面小写的方式显示所有员工的姓名。o SQLselectupper(substr(ename,1,1)|o lower(substr(ename,2,length(ename)-1)fromemp;o 问题：以首字母小写,后面大写的方式显示所有员工的

33、姓名。o SQLselectlower(substr(ename,1,1)|o upper(substr(ename,2,length(ename)-1)fromemp;o 问题：显示所有员工的姓名，用“我是老虎”替换所有“A”o SQLselectreplace(ename,A,我是老虎)fromemp;数学函数o 数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，o cosh，exp，ln,log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，o round(n,m)该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，o 如

34、果m 是正数，则四舍五入到小数点的m 位后。如果m 是负数，则四舍五入到小o 数点的m 位前。o trunc(n,m)该函数用于截取数字。如果省掉m，就截去小数部分，如果mo 是正数就截取到小数点的m 位后，如果m 是负数，则截取到小数点的前m 位。o mod(m,n)o floor(n)返回小于或是等于n 的最大整数o ceil(n)返回大于或是等于n 的最小整数o 对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报o 表有不同的结果。o 问题：显示在一个月为30 天的情况下，所有员工的日薪金，忽略余数。o SQLselecttrunc(sal/30),enamefrom

35、emp;o oro SQLselectfloor(sal/30),enamefromemp;o 在做oracle 测试的时候，可以使用dual 表o selectmod(10,2)fromdual;结果是0o selectmod(10,3)fromdual;结果是1o 其它的数学函数，有兴趣的同学可以自己去看看：o abs(n)：返回数字n 的绝对值o selectabs(-13)fromdual;o acos(n)：返回数字的反余弦值o asin(n)：返回数字的反正弦值o atan(n)：返回数字的反正切值o cos(n)：o exp(n)：返回e 的n 次幂o log(m,n)：返回对数

36、值o power(m,n)：返回m的n 次幂日期函数转换函数o 问题：显示薪水的时候，把本地货币单位加在前面o SQLselectename,to_char(hiredate,yyyy-mm-ddhh24:mi:ss),o to_char(sal,L99999.99)fromemp;o 问题：显示1980 年入职的所有员工o SQLselect*fromempwhereto_char(hiredate,yyyy)=1980;o 问题：显示所有12 月份入职的员工o SQLselect*fromempwhereto_char(hiredate,mm)=12;o to_date o 函数to_da

37、te 用于将字符串转换成date 类型的数据。o 问题：能否按照中国人习惯的方式年月日添加日期。系统函数o 1）terminal：当前会话客户所对应的终端的标示符o 2）lanuage:语言o 3）db_name：当前数据库名称o 4）nls_date_format：当前会话客户所对应的日期格式o 5）session_user：当前会话客户所对应的数据库用户名o 6）current_schema：当前会话客户所对应的默认方案名o 7）host：返回数据库所在主机的名称o 通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？o selectsys_context(USERENV,db_name)fromdual;o 注意：USERENV 是固定的，不能改的，db_name 可以换成其它,比如selecto sys_context(USERENV,lanuage)fromdual;又比如selecto sys_context(USERENV,current_schema)fromdual;