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.修改数据文件的大小alterdatabasedatafile‘d:\test\sp01.dbf’resize50m;注意:数据文件的大小不要超过500m。3.设置文件的自动增长。SQLalterdatabasedatafile‘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。缺省是6。图片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(sal500orjob='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(s