Oracle表管理相关知识

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

2、dada01.dbf size 20m使用：create table student(sno number(4), sname varchar2(14) tablespace data01;表空间名称表空间文件名表空间的大小扩展表空间1. 增加数据文件alter tablespace 表空间名 add datafile d:testsp01.dbf size 20m;2. 修改数据文件的大小alter database datafile d:testsp01.dbf resize 50m;注意：数据文件的大小不要超过500m。3. 设置文件的自动增长。SQL alter database da

3、tafile d:testsp01.dbf autoextend on next 10m maxsize 500m;删除表空间drop tablespace 表空间名 including contents and datafiles;说明：n including contents 表示删除表空间时，删除该空间的所有数据库对象，n datafiles 表示将数据库文件也删除。o 1. 知道表空间名，显示该表空间包括的所有表n select * from all_tables where tablespace_name=表空间名;o 2. 知道表名，查看该表属于那个表空间n select tabl

4、espace_name, table_name from user_tables where table_name=emp;o 此处查的是scott这个用户表空间下的所有表名o select table_name from all_tables where owner=upper(scott) 表名和列的命名规则o 必须以字母开头o 长度不能超过30 个字符o 不能使用oracle 的保留字o 只能使用如下字符 A-Z，a-z，0-9，$,#等Oracle常用的数据类型字符类o char 定长 最大2000 个字符。o varchar2/varchar 变长最大4000 个字符.注意：varc

5、har2是oracle自己开发的，想有向后兼容的能力 ，建议使用varchar2。o clob(character large object) 字符型大对象 最大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 包含年月日和时分秒

6、 oracle 默认格式 1-1 月-1999o timestamp 这是oracle9i 对date 数据类型的扩展。可以精确到毫秒。语法timestamp(n)，n指定秒的小数位数，取值范围09。缺省是。 图片o blob 二进制数据 可以存放图片/声音 4Go 注意： 一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。创建表o 实际操作修改表o添加一个字段nALTER TABLE student add (sex char(2);o修改一个字段的长度nALTER TABLE student MODIFY (sex

7、char(5);o删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)nALTER TABLE student DROP COLUMN sex;o修改表的名字 很少有这种需求nRENAME 原表名 TO 新表名;删除表o DROP TABLE student;数据完整性o 在oracle 中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。约束o 约束用于确保数据库数据满足特定的商业规则。o 在oracle 中，约束包括：not null、 unique、primar

8、y key、 foreign key和check 五种。建表时添加约束create table customer( customerId char(8) primary key, -主键name varchar2(50) not null, -不为空address varchar2(50),email varchar2(50) unique,sex char(2) default 男 check(sex in (男，女)， cardId char(18);表是默认建在SYSTEM 表空间的建表后添加约束o使用alter table 命令为表增加约束。但是要注意：增加not null 约束时，需

9、要使用modify 选项，而增加其它四种约束使用add 选项。o1. 增加商品名也不能为空nalter table stuInfo modify stuName not null;o2. 增加身份证也不能重复nalter table stuInfo add constraint 约束名 unique(stuName);o3. 增加学生的住址只能是海淀,朝阳,东城,西城,通州,崇文,昌平；nalter table stuInfo add constraint 约束名 check (address in (海淀,朝阳,东城,西城,通州,崇文,昌平);删除约束o 当不再需要某个约束时，可以删除。n

10、alter table 表名 drop constraint 约束名称；o 注意：在删除主键约束的时候，可能会有错误n alter table 表名 drop primary key；n 这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade 选项 如像：o alter table 表名 drop primary key cascade;自动标识列o oracle里面没有标识列！o 只能增加一个自增的序列，每当要用的时候调用这个序列！o 创建序列 create sequence test-test为序列的名称start with 1-从1开始increment b

11、y 1 -每次递增1o 使用序列插入数据ninsert into stuInfo values(test.nextval,张三);向表中添加数据ooracle 中默认的日期格式dd-mon-yy dd 日子（天） mon 月份 yy 2 位的年 09-6 月-99nINSERT INTO student VALUES (A001, 张三, 男, 01-5 月-05, 10);o使用do_date函数ninsert into student values(mark,to_date(08-21-2003,MM-DD-YYYY);o修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改

12、注册表）nALTER SESSION SET NLS_DATE_FORMAT =yyyy-mm-dd;修改表中的数据o UPDATE student SET sex = 女 WHERE xh = A001;o UPDATE student SET sex = 男, birthday = 1984-04-01 WHERE xh = A001;删除表中的数据oDELETE FROM student;n删除所有记录，表结构还在，写日志，可以恢复的，速度慢。osavepoint a; -创建保存点oDELETE FROM student;orollback to a; -恢复到保存点o一个有经验的DB

13、A，在确保完成无误的情况下要定期创建还原点。oDROP TABLE student; -删除表的结构和数据；odelete from student WHERE xh = A001; -删除一条记录；otruncate TABLE student; -删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。表查询o 使用scott用户中的几张表作示例emp 雇员表dept部门表salgrade 工资级别salgrade 工资级别表工资级别表grade 级别级别losal 最低工资最低工资hisal 最高工资最高工资dept 部门表部门表deptno 部门编号部门编号Dname 部

14、门名称部门名称loc 部门所在地点部门所在地点emp 雇员表雇员表Empno 员工编号员工编号Ename 员工姓名员工姓名Job 工作工作mgr 上级的编号上级的编号hiredate 入职时间入职时间sal 月工资月工资comm 奖金奖金deptno 部门部门查询一：o SELECT ename, sal, job, deptno FROM emp;o SELECT DISTINCT deptno, job FROM emp;o SELECT deptno,job,sal FROM emp WHERE ename = SMITH;o 注意：oracle 对内容的大小写是区分的，所以ename=

15、SMITH和ename=smith是不同的o如何处理null 值n使用nvl 函数来处理oSELECT sal*13+nvl(comm, 0)*13 年薪 , ename, comm FROM emp;oSELECT ename 姓名, sal*12 AS 年收入 FROM emp;o如何连接字符串（|）nSELECT ename | is a | job FROM emp;预设的值o 问题：如何查找1982.1.1 后入职的员工？n SELECT ename,hiredate FROM emp WHERE hiredate 1-1 月-1982;使用likeo %：表示0 到多个字符 _：表

16、示任意单个字符o 问题：如何显示首字符为S 的员工姓名和工资？nSELECT ename,sal FROM emp WHERE ename like S%;o 如何显示第三个字符为大写O 的所有员工的姓名和工资？nSELECT ename,sal FROM emp WHERE ename like _O%;o 问题：如何显示empno 为7844,7839,123,456 的雇员情况？n SELECT * FROM emp WHERE empno in (7844, 7839,123,456);o 问题：如何显示没有上级的雇员的情况？n SELECT * FROM emp WHERE mgr

17、is null;查询二：使用逻辑操作符号o 问题：查询工资高于500 或者是岗位为MANAGER 的雇员，同时还要满足他们的姓名首字母为大写的J？n SELECT * FROM emp WHERE (sal 500 or job = MANAGER) and ename LIKE J%;o 问题：如何按照工资的从低到高的顺序显示雇员的信息？nSELECT * FROM emp ORDER by sal;o 问题：按照部门号升序而雇员的工资降序排列nSELECT * FROM emp ORDER by deptno, sal DESC;o 问题：按年薪排序nselect ename, (sal+

18、nvl(comm,0)*12 年薪 from emp order by 年薪 asc;查询三：复杂查询o 数据分组 max，min， avg， sum， counto 问题：如何显示所有员工中最高工资和最低工资？n SELECT MAX(sal),min(sal) FROM emp e;o 最高工资那个人是谁？n select ename, sal from emp where sal=(select max(sal) from emp);练习：o 问题：如何显示所有员工的平均工资和工资总和？o 问题：如何计算总共有多少员o 查询最高工资员工的名字，工作岗位o 显示工资高于平均工资的员工信息g

19、roup by 和 having 子句o 问题：如何显示每个部门的平均工资和最高工资？nSELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno;o 问题：显示每个部门的每种岗位的平均工资和最低工资？nSELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job;o 问题：显示平均工资低于2000 的部门号和它的平均工资？nSELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG

20、(sal) all (SELECT sal FROM emp WHERE deptno = 30);o 大家想想还有没有别的查询方法。n SELECT ename, sal, deptno FROM emp WHERE sal (SELECT MAX(sal) FROM emp WHERE deptno = 30);o 执行效率上， 函数高得多o All等价于N个And语句 在多行子查询中使用any 操作符o 问题：如何显示工资比部门30 的任意一个员工的工资高的员工姓名、工资和部门号？nSELECT ename, sal, deptno FROM emp WHERE sal ANY (SEL

21、ECT sal FROM emp WHERE deptno = 30);o 大家想想还有没有别的查询方法。nSELECT ename, sal, deptno FROM emp WHERE sal (SELECT min(sal) FROM emp WHERE deptno = 30);o Any等价于N个or语句 多列子查询o 查询与SMITH 的部门和岗位完全相同的所有雇员。a)SELECT deptno, job FROM emp WHERE ename = SMITH;b)SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, j

22、ob FROM emp WHERE ename = SMITH);o 1. 查出各个部门的平均工资和部门号n SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;o 2. 把上面的查询结果看做是一张子表n SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno,AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal ds.mysal;小总结：o 在这里

23、需要说明的当在from 子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在from 子句中使用子查询时，必须给子查询指定别名。o 注意：给表取别名的时候，不能加as；但是给列取别名，是可以加as 的查询五：分页查询oracle 的分页一共有三种方式o 1.根据rowid 来分n select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rown

24、um9980) order by cid desc;o 执行时间0.03 秒o 2.按分析函数来分nselect * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk9980;o 执行时间1.01 秒o 3.按rownum 来分nselect * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum9980;o 执行时间0.1 秒o 1 的效率最好，3 次之

25、，2 最差。o select * from (select a1.*, rownum rn from(select ename,job from emp) a1o where rownum=5;o下面最主要介绍第三种：按rownum 来分o1. rownum 分页nSELECT * FROM emp;o2. 显示rownumoracle 分配的nSELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;注：rn 相当于Oracle 分配的行的ID 号o3.挑选出610 条记录 ,先查出1-10 条记录nSELECT e.*, ROWNUM rn FROM

26、 (SELECT * FROM emp) e WHERE ROWNUM = 10;o4. 然后查出6-10 条记录nSELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM = 6;o 5. 几个查询变化o a. 指定查询列，只需要修改最里层的子查询只查询雇员的编号和工资n SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM = 6;o b. 排序查询，只需要修改最里层的子查询工资

27、排序后查询6-10 条数据n SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM = 6;用查询结果创建新表o CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename,sal, job, deptno FROM emp;o 创建好之后，desc mytable；和select * from mytable合并查询o 有时在实际应用中，为了合并多个select 语

28、句的结果，可以使用集合操作符号union，union all，intersect，minus多用于数据量比较大的数据局库，运行速度快。o 1). uniono 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。nSELECT ename, sal, job FROM emp WHERE sal 2500 UNIONSELECT ename, sal, job FROM emp WHERE job = MANAGER;o2).union allo该操作符与union 相似，但是它不会取消重复行，而且不会排序。oSELECT ename, sal, job FROM e

29、mp WHERE sal 2500oUNION ALLoSELECT ename, sal, job FROM emp WHERE job = MANAGER;o该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中o重复行。o3). intersecto使用该操作符用于取得两个结果集的交集。oSELECT ename, sal, job FROM emp WHERE sal 2500oINTERSECToSELECT ename, sal, job FROM emp WHERE job = MANAGER;o4). minuso使用改操作符用于取得两个结果集的差集，他只会显示

30、存在第一个集合中，而不o存在第二个集合中的数据。oSELECT ename, sal, job FROM emp WHERE sal 2500oMINUSoSELECT ename, sal, job FROM emp WHERE job = 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,repla

31、ce_string)o instr(char1,char2,n,m)取子串在字符串的位置o问题：将所有员工的名字按小写的方式显示oSQL select lower(ename) from emp;o问题：将所有员工的名字按大写的方式显示。oSQL select upper(ename) from emp;o问题：显示正好为5 个字符的员工的姓名。oSQL select * from emp where length(ename)=5;o问题：显示所有员工姓名的前三个字符。oSQL select substr(ename,1,3) from emp;o问题：以首字母大写,后面小写的方式显示所有员

32、工的姓名。oSQL select upper(substr(ename,1,1) |olower(substr(ename,2,length(ename)-1) from emp;o问题：以首字母小写,后面大写的方式显示所有员工的姓名。oSQL select lower(substr(ename,1,1) |oupper(substr(ename,2,length(ename)-1) from emp;o问题：显示所有员工的姓名，用“我是老虎”替换所有“A”oSQL select replace(ename,A, 我是老虎) from emp;数学函数o 数学函数的输入参数和返回值的数据类型都

33、是数字类型的。数学函数包括cos，o cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，oround(n,m) 该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，o如果m 是正数，则四舍五入到小数点的m 位后。如果m 是负数，则四舍五入到小o数点的m 位前。o trunc(n,m) 该函数用于截取数字。如果省掉m，就截去小数部分，如果mo是正数就截取到小数点的m 位后，如果m 是负数，则截取到小数点的前m 位。o mod(m,n)ofloor(n) 返回小于或是等于n 的最大整数oceil(n) 返回大于或是等于n 的最小整数o对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报o表有不同的结果。o问题：显示在一个月为30 天的情况下，所有员工的日薪金，忽略余数。oSQL select