Oracle表管理相关知识

Oracle表管理表管理主要内容o Oracle表空间o 常用的数据类型o 表的创建和删除o 数据完整性（约束）o 对数据的CRUD操作表空间o 表空间是数据库的逻辑组成部分。从物理上讲，数据库数据存放在数据文件中；o 从逻辑上讲，数据库则是存放在表空间中，表空间由一个或多个数据文件组成。o 默认表空间是“system”通过表空间可以达到以下作用：o 1.控制数据库占用的磁盘空间o 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;说明：n including contents 表示删除表空间时，删除该空间的所有数据库对象，n datafiles 表示将数据库文件也删除。o 1.知道表空间名，显示该表空间包括的所有表n select*from all_tables where tablespace_name=表空间名;o 2.知道表名，查看该表属于那个表空间n select tablespace_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 个字符.注意：varchar2是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 包含年月日和时分秒 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 char(5);o删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)nALTER TABLE student DROP COLUMN sex;o修改表的名字 很少有这种需求nRENAME 原表名 TO 新表名;删除表o DROP TABLE student;数据完整性o 在oracle 中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。约束o 约束用于确保数据库数据满足特定的商业规则。o 在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 表空间的建表后添加约束o使用alter table 命令为表增加约束。但是要注意：增加not null 约束时，需要使用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 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 by 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修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）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一个有经验的DBA，在确保完成无误的情况下要定期创建还原点。oDROP TABLE student;-删除表的结构和数据；odelete from student WHERE xh=A001;-删除一条记录；otruncate TABLE student;-删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。表查询o 使用scott用户中的几张表作示例emp 雇员表dept部门表salgrade 工资级别salgrade 工资级别表工资级别表grade 级别级别losal 最低工资最低工资hisal 最高工资最高工资dept 部门表部门表deptno 部门编号部门编号Dname 部门名称部门名称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=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 到多个字符 _：表示任意单个字符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 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+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 显示工资高于平均工资的员工信息group 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(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(SELECT 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,job 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 在这里需要说明的当在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 rownum9980)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 次之，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(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.排序查询，只需要修改最里层的子查询工资排序后查询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 语句的结果，可以使用集合操作符号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 emp 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使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不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,replace_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问题：以首字母大写,后面小写的方式显示所有员工的姓名。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 数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括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 trunc(sal/30),ename from emp;ooroSQL select floor(sal/30),ename from emp;o在做oracle 测试的时候，可以使用dual 表oselect mod(10,2)from dual;结果是0oselect mod(10,3)from dual;结果是1o其它的数学函数，有兴趣的同学可以自己去看看：oabs(n)：返回数字n 的绝对值oselect abs(-13)from dual;oacos(n)：返回数字的反余弦值oasin(n)：返回数字的反正弦值oatan(n)：返回数字的反正切值ocos(n)：oexp(n)：返回e 的n 次幂olog(m,n)：返回对数值opower(m,n)：返回m 的n 次幂日期函数转换函数o问题：显示薪水的时候，把本地货币单位加在前面oSQL select ename,to_char(hiredate,yyyy-mm-dd hh24:mi:ss),oto_char(sal,L99999.99)from emp;o问题：显示1980 年入职的所有员工oSQL select*from emp where to_char(hiredate,yyyy)=1980;o问题：显示所有12 月份入职的员工oSQL select*from emp where to_char(hiredate,mm)=12;oto_dateo函数to_date 用于将字符串转换成date 类型的数据。o问题：能否按照中国人习惯的方式年月日添加日期。系统函数o 1）terminal：当前会话客户所对应的终端的标示符o 2）lanuage:语言o 3）db_name：当前数据库名称o 4）nls_date_format：当前会话客户所对应的日期格式o 5）session_user：当前会话客户所对应的数据库用户名o 6）current_schema：当前会话客户所对应的默认方案名o 7）host：返回数据库所在主机的名称o 通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？o select sys_context(USERENV,db_name)from dual;o 注意：USERENV 是固定的，不能改的，db_name 可以换成其它,比如selecto sys_context(USERENV,lanuage)from dual;又比如selecto sys_context(USERENV,current_schema)from dual;演讲完毕，谢谢观看！