Oracle表管理

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.99number(5)，表示一个5位整数。范围99999到-99999日期类型date包含年月日和时分秒oracle默认格式1-1月-1999timestamp这是oracle9i对date数据类型的扩展。可以精确到毫秒。语法timestamp(n)，n指定秒的小数位数，取值范围0～9。缺省是６。图片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