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 设置文件的自动增长 SQL alterdatabasedatafile 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 缺省是 图片 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 sal 500orjob 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 sal deptnoFROMempGROUPbydeptno 问题 显示每个部门的每种岗位的平均工资和最低工资 SELECTmin sal AVG sal deptno jobFROMempGROUPbydeptno job 问题 显示平均工资低于2000的部门号和它的平均工资 SELECTAVG sal MAX sal deptnoFROMempGROUPbydeptnohavingAVG sal 2000 查询四 多表查询