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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 管理 相关 知识 8067
限制150内