Oracle表管理相关知识

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Oracle,表管理,主要内容,Oracle,表空间,常用的数据类型,表的创建和删除,数据完整性（约束）,对数据的,CRUD,操作,表空间,表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件中；,从逻辑上讲，数据库则是存放在表空间中，表空间由一个或多个数据文件组成。,默认表空间是“,system,”,通过表空间可以达到以下作用：,1. 控制数据库占用的磁盘空间,2. dba 可以将不同数据类型部署到不同的位置，这样有利于提高i/o 性能，同时利于备份和恢复等管理操作。,建立和使用表空间,建立：,create tablespace,data01,datafile,d:testdada01.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,datafile,d:testsp01.dbf,autoextend on next,10m,maxsize,500m;,删除表空间,drop tablespace,表空间名,including contents and datafiles,;,说明：,including contents,表示删除表空间时，删除该空间的所有数据库对象，,datafiles,表示将数据库文件也删除。,1.,知道表空间名，显示该表空间包括的所有表,select * from all_tables where tablespace_name=,表空间名,;,2.,知道表名，查看该表属于那个表空间,select tablespace_name, table_name from user_tables where table_name=emp;,此处查的是scott这个用户表空间下的所有表名,select table_name from all_tables where owner=upper(scott),表名和列的命名规则,必须以字母开头,长度不能超过30 个字符,不能使用oracle 的保留字,只能使用如下字符 A-Z，a-z，0-9，$,#等,Oracle常用的数据类型,字符类,char,定长 最大,2000,个字符。,varchar2/varchar,变长最大,4000,个字符,.,注意：,varchar2,是,oracle,自己开发的，想有向后兼容的能力 ，建议使用,varchar2,。,clob,(character large object),字符型大对象 最大,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,注意： 一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。,创建表,实际操作,修改表,添加一个字段,ALTER TABLE,student,add,(sex char(2);,修改一个字段的长度,ALTER TABLE,student,MODIFY,(sex char(5);,删除一个字段 不建议做,(,删了之后，顺序就变了。加就没问题，应为是加在后面,),ALTER TABLE,student,DROP COLUMN,sex;,修改表的名字 很少有这种需求,RENAME,原表名,TO,新表名,;,删除表,DROP TABLE,student;,数据完整性,在,oracle,中，数据完整性可以使用,约束,、,触发器,、,应用程序,（过程、函数）三种方法来实现，在这三种方法中，因为,约束,易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。,约束,约束用于确保数据库数据满足特定的商业规则。,在,oracle,中，约束包括：,not null,、,unique,、,primary 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,表空间的,建表后添加约束,使用,alter table,命令为表增加约束。但是要注意：增加,not null,约束时，需要使用,modify,选项，而增加其它四种约束使用,add,选项。,1.,增加商品名也不能为空,alter table,stuInfo,modify,stuName,not null,;,2.,增加身份证也不能重复,alter table,stuInfo,add constraint,约束名,unique,(stuName);,3.,增加学生的住址只能是海淀,朝阳,东城,西城,通州,崇文,昌平；,alter table,stuInfo,add constraint,约束名,check,(address in (,海淀,朝阳,东城,西城,通州,崇文,昌平,);,删除约束,当不再需要某个约束时，可以删除。,alter table,表名,drop constraint,约束名称；,注意：在删除主键约束的时候，可能会有错误,alter table,表名,drop primary key,；,这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上,cascade,选项 如像：,alter table,表名,drop primary key,cascade,;,自动标识列,oracle,里面没有标识列！,只能增加一个自增的序列，每当要用的时候调用这个序列！,创建序列,create sequence test-test,为序列的名称,start with 1-,从,1,开始,increment by 1-,每次递增,1,使用序列插入数据,insert into stuInfo values(test.nextval,张三,);,向表中添加数据,oracle,中默认的日期格式,dd-mon-yy dd,日子（天）,mon,月份,yy 2,位的年 ,09-6,月,-99,INSERT INTO student VALUES (A001, ,张三, ,男,01-5,月,-05, 10);,使用,do_date,函数,insert into student values(mark,to_date(08-21-2003,MM-DD-YYYY);,修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）,ALTER SESSION SET NLS_DATE_FORMAT =yyyy-mm-dd;,修改表中的数据,UPDATE student SET sex = 女 WHERE xh = A001;,UPDATE student SET sex = 男, birthday = 1984-04-01 WHERE xh = A001;,删除表中的数据,DELETE FROM student;,删除所有记录，表结构还在，写日志，可以恢复的，速度慢。,savepoint a; -,创建保存点,DELETE FROM student;,rollback to a; -,恢复到保存点,一个有经验的,DBA,，在确保完成无误的情况下要定期创建还原点。,DROP TABLE student; -,删除表的结构和数据；,delete from student WHERE xh = A001; -,删除一条记录；,truncate TABLE student; -,删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。,表查询,使用,scott,用户中的几张表作示例,emp,雇员表,dept,部门表,salgrade,工资级别,salgrade,工资级别表,grade,级别,losal,最低工资,hisal,最高工资,dept,部门表,deptno 部门编号,Dname,部门名称,loc 部门所在地点,emp,雇员表,Empno,员工编号,Ename,员工姓名,Job,工作,mgr 上级的编号,hiredate 入职时间,sal 月工资,comm 奖金,deptno 部门,查询一：,SELECT ename, sal, job, deptno FROM emp;,SELECT DISTINCT deptno, job FROM emp;,SELECT deptno,job,sal FROM emp WHERE ename = SMITH;,注意：,oracle,对,内容的大小写是区分的,，所以,ename=SMITH,和,ename=smith,是不同的,如何处理,null,值,使用,nvl,函数来处理,SELECT sal*13+nvl(comm, 0)*13 ,年薪, , ename, comm FROM emp;,SELECT ename ,姓名, sal*12 AS ,年收入, FROM emp;,如何连接字符串（,|,）,SELECT ename | is a | job FROM emp;,预设的值,问题：如何查找,1982.1.1,后入职的员工？,SELECT ename,hiredate FROM emp WHERE hiredate 1-1,月,-1982;,使用like,%,：表示,0,到多个字符,_,：表示任意单个字符,问题：如何显示首字符为,S,的员工姓名和工资？,SELECT ename,sal FROM emp WHERE ename like S%;,如何显示第三个字符为大写,O,的所有员工的姓名和工资？,SELECT ename,sal FROM emp WHERE ename like _O%;,问题：如何显示,empno,为,7844,7839,123,456,的雇员情况？,SELECT * FROM emp WHERE empno in (7844, 7839,123,456);,问题：如何显示没有上级的雇员的情况？,SELECT * FROM emp WHERE mgr is null;,查询二：使用逻辑操作符号,问题：查询工资高于,500,或者是岗位为,MANAGER,的雇员，同时还要满足他们的姓名首字母为大写的,J,？,SELECT * FROM emp WHERE (sal 500 or job = MANAGER) and ename LIKE J%;,问题：如何按照工资的从低到高的顺序显示雇员的信息？,SELECT * FROM emp ORDER by sal;,问题：按照部门号升序而雇员的工资降序排列,SELECT * FROM emp ORDER by deptno, sal DESC;,问题：按年薪排序,select ename, (sal+nvl(comm,0)*12 ,年薪, from emp order by ,年薪, asc;,查询三：复杂查询,数据分组,max,，,min,，,avg,，,sum,，,count,问题：如何显示所有员工中最高工资和最低工资？,SELECT MAX(sal),min(sal) FROM emp e;,最高工资那个人是谁？,select ename, sal from emp where sal=(select max(sal) from emp);,练习：,问题：如何显示所有员工的平均工资和工资总和？,问题：如何计算总共有多少员,查询最高工资员工的名字，工作岗位,显示工资高于平均工资的员工信息,group by 和 having 子句,问题：如何显示每个部门的平均工资和最高工资？,SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno;,问题：显示每个部门的每种岗位的平均工资和最低工资？,SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job;,问题：显示平均工资低于,2000,的部门号和它的平均工资？,SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) all (SELECT sal FROM emp WHERE deptno = 30);,大家想想还有没有别的查询方法。,SELECT ename, sal, deptno FROM emp WHERE sal (SELECT MAX(sal) FROM emp WHERE deptno = 30);,执行效率上， 函数高得多,All,等价于,N,个,And,语句,在多行子查询中使用any 操作符,问题：如何显示工资比部门,30,的任意一个员工的工资高的员工姓名、工资和部门号？,SELECT ename, sal, deptno FROM emp WHERE sal ANY (SELECT sal FROM emp WHERE deptno = 30);,大家想想还有没有别的查询方法。,SELECT ename, sal, deptno FROM emp WHERE sal (SELECT min(sal) FROM emp WHERE deptno = 30);,Any,等价于,N,个,or,语句,多列子查询,查询与,SMITH,的部门和岗位完全相同的所有雇员。,a)SELECT deptno, job FROM emp WHERE ename = SMITH;,b)SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = SMITH);,1.,查出各个部门的平均工资和部门号,SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;,2.,把上面的查询结果看做是一张子表,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;,小总结：,在这里需要说明的当在,from,子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在,from,子句中使用子查询时，必须给子查询指定别名。,注意：给表取别名的时候，不能加,as,；但是给列取别名，是可以加,as,的,查询五：分页查询,oracle,的分页一共有三种方式,1.,根据,rowid,来分,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 rownum9980) order by cid desc;,执行时间,0.03,秒,2.,按分析函数来分,select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk9980;,执行时间,1.01,秒,3.,按,rownum,来分,select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum9980;,执行时间,0.1,秒,1 的效率最好，3 次之，2 最差。,select * from (select a1.*, rownum rn from(select ename,job from emp) a1,where rownum=5;,下面最主要介绍第三种：按,rownum,来分,1. rownum,分页,SELECT * FROM emp;,2.,显示,rownumoracle,分配的,SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;,注：,rn,相当于,Oracle,分配的行的,ID,号,3.,挑选出,610,条记录,先查出,1-10,条记录,SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM 2500,UNION,SELECT ename, sal, job FROM emp WHERE job = MANAGER;,2).union all,该操作符与,union,相似，但是它不会取消重复行，而且不会排序。,SELECT ename, sal, job FROM emp WHERE sal 2500,UNION ALL,SELECT ename, sal, job FROM emp WHERE job = MANAGER;,该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中,重复行。,3). intersect,使用该操作符用于取得两个结果集的交集。,SELECT ename, sal, job FROM emp WHERE sal 2500,INTERSECT,SELECT ename, sal, job FROM emp WHERE job = MANAGER;,4). minus,使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不,存在第二个集合中的数据。,SELECT ename, sal, job FROM emp WHERE sal 2500,MINUS,SELECT ename, sal, job FROM emp WHERE job = MANAGER;,（,MINUS,就是减法的意思）,Oracle中常用函数,字符函数,upper(char),：将字符串转化为大写的格式。,length(char),：返回字符串的长度。,substr(char,m,n),：取字符串的子串,n,代表取,n,个的意思，不是代表取,到第,n,个,replace(char1,search_string,replace_string), instr(char1,char2,n,m),取子串在字符串的位置,问题：将所有员工的名字按小写的方式显示,SQL select lower(ename) from emp;,问题：将所有员工的名字按大写的方式显示。,SQL select upper(ename) from emp;,问题：显示正好为,5,个字符的员工的姓名。,SQL select * from emp where length(ename)=5;,问题：显示所有员工姓名的前三个字符。,SQL select substr(ename,1,3) from emp;,问题：以首字母大写,后面小写的方式显示所有员工的姓名。,SQL select upper(substr(ename,1,1) |,lower(substr(ename,2,length(ename)-1) from emp;,问题：以首字母小写,后面大写的方式显示所有员工的姓名。,SQL select lower(substr(ename,1,1) |,upper(substr(ename,2,length(ename)-1) from emp;,问题：显示所有员工的姓名，用“我是老虎”替换所有“,A”,SQL select replace(ename,A, ,我是老虎,) from emp;,数学函数,数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，,cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，,round(n,m),该函数用于执行四舍五入，如果省掉,m,，则四舍五入到整数，,如果,m,是正数，则四舍五入到小数点的,m,位后。如果,m,是负数，则四舍五入到小,数点的,m,位前。,trunc(n,m),该函数用于截取数字。如果省掉,m,，就截去小数部分，如果,m,是正数就截取到小数点的,m,位后，如果,m,是负数，则截取到小数点的前,m,位。,mod(m,n),floor(n),返回小于或是等于,n,的最大整数,ceil(n),返回大于或是等于,n,的最小整数,对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报,表有不同的结果。,问题：显示在一个月为,30,天的情况下，所有员工的日薪金，忽略余数。,SQL select trunc(sal/30), ename from emp;,or,SQL select floor(sal/30), ename from emp;,在做,oracle,测试的时候，可以使用,dual,表,select mod(10,2) from dual;,结果是,0,select mod(10,3) from dual;,结果是,1,其它的数学函数，有兴趣的同学可以自己去看看：,abs(n),： 返回数字,n,的绝对值,select abs(-13) from dual;,acos(n),： 返回数字的反余弦值,asin(n),： 返回数字的反正弦值,atan(n),： 返回数字的反正切值,cos(n),：,exp(n),： 返回,e,的,n,次幂,log(m,n),： 返回对数值,power(m,n),： 返回,m,的,n,次幂,日期函数,日期函数用于处理,date,类型的数据。,默认情况下日期格式是,dd-mon-yy,即,12-7,月,-78,(1)sysdate:,该函数返回系统时间,(2)add_months(d,n),(3)last_day(d),：返回指定日期所在月份的最后一天,问题：查找已经入职,8,个月多的员工,SQL select * from emp where sysdate=add_months(hiredate,8);,问题：显示满,10,年服务年限的员工的姓名和受雇日期。,Generated by Foxit PDF Creator Foxit Software, For evaluation only.,SQL select ename, hiredate from emp where,sysdate=add_months(hiredate,12*10);,问题：对于每个员工，显示其加入公司的天数。,SQL select floor(sysdate-hiredate) ,入职天数,ename from emp;,or,SQL select trunc(sysdate-hiredate) ,入职天数,ename from emp;,问题：找出各月倒数第,3,天受雇的所有员工。,SQL select hiredate,ename from emp where last_day(hiredate)-2=hiredate,转换函数,问题：日期是否可以显示 时,/,分,/,秒,SQL select ename, to_char(hiredate, yyyy-mm-dd hh24:mi:ss) from emp;,问题：薪水是否可以显示指定的货币符号,SQL,yy,：两位数字的年份,2004-04,yyyy,：四位数字的年份,2004,年,mm,：两位数字的月份,8,月,-08,dd,：两位数字的天,30,号,-30,hh24,：,8,点,-20,hh12,：,8,点,-08,mi,、,ss-,显示分钟,秒,9,：显示数字，并忽略前面,0,0,：显示数字，如位数不足，则用,0,补齐,.,：在指定位置显示小数点,：在指定位置显示逗号,Generated by Foxit PDF Creator Foxit Software, For evaluation only.,$,：在数字前加美元,L,：在数字前面加本地货币符号,C,：在数字前面加国际货币符号,G,：在指定位置显示组分隔符、,D,：在指定位置显示小数点符号,(.),问题：显示薪水的时候，把本地货币单位加在前面,SQL select ename, to_char(hiredate, yyyy-mm-dd hh24:mi:ss),to_char(sal,L99999.99) from emp;,问题：显示,1980,年入职的所有员工,SQL select * from emp where to_char(hiredate, yyyy)=1980;,问题：显示所有,12,月份入职的员工,SQL select * from emp where to_char(hiredate, mm)=12;,to_date,函数,to_date,用于将字符串转换成,date,类型的数据。,问题：能否按照中国人习惯的方式年,月,日添加日期。,系统函数,1,）,terminal,：当前会话客户所对应的终端的标示符,2,）,lanuage:,语言,3,）,db_name,： 当前数据库名称,4,）,nls_date_format,： 当前会话客户所对应的日期格式,5,）,session_user,： 当前会话客户所对应的数据库用户名,6,）,current_schema,： 当前会话客户所对应的默认方案名,7,）,host,： 返回数据库所在主机的名称,通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？,select sys_context(USERENV,db_name) from dual;,注意：,USERENV,是固定的，不能改的，,db_name,可以换成其它,比如,select,sys_context(USERENV,lanuage) from dual;,又比如,select,sys_context(USERENV,current_schema) from dual;,