Oracle表管理ppt课件

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

2、：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.db

3、f 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 u

4、ser_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。c

5、lob(character large object) 字符型大对象 最大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

6、数据类型的扩展。可以准确到毫秒。语法timestamp(n)，n指定秒的小数位数，取值范围09。缺省是。 .图片blob 二进制数据 可以存放图片/声音 4G留意： 普通来讲，在真实工程中是不会把图片和声音真的往数据库里存放，普通存放图片、视频的途径，假设平安需求比较高的话，那么放入数据库。.创建表实践操作.修正表添加一个字段ALTER TABLE student add (sex char(2);修正一个字段的长度ALTER TABLE student MODIFY (sex char(5);删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)ALTER TABLE s

7、tudent DROP COLUMN sex;修正表的名字 很少有这种需求RENAME 原表名 TO 新表名;.删除表DROP TABLE student;.数据完好性在oracle 中，数据完好性可以运用约束、触发器、运用程序过程、函数三种方法来实现，在这三种方法中，由于约束易于维护，并且具有最好的性能，所以作为维护数据完好性的首选。.约束约束用于确保数据库数据满足特定的商业规那么。在oracle 中，约束包括：not null、 unique、primary key、 foreign key和check 五种。.建表时添加约束create table customer( customerI

8、d char(8) primary key, -主键name varchar2(50) not null, -不为空address varchar2(50), 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 s

9、tuName 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 prima

10、ry 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 d

11、d 日子天 mon 月份 yy 2 位的年 09-6 月-99INSERT 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

12、 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 stu

13、dent; -删除表中的一切记录，表构造还在，不写日志，无法找回删除的记录，速度快。.表查询运用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;SELEC

14、T 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;预设的值.

15、问题：如何查找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 * FR

16、OM 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;问题：按照部门号升序而雇员

17、的工资降序陈列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);.练习：问题：如何

18、显示一切员工的平均工资和工资总和？问题：如何计算总共有多少员查询最高工资员工的名字，任务岗位显示工资高于平均工资的员工信息.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)

19、, 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 en

20、ame, 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 W

21、HERE (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.dept

22、no 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 orde

23、r 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 秒

24、.1 的效率最好，3 次之，2 最差。.select * from (select a1.*, rownum rn from(select ename,job from emp) a1where 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

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

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

27、操作符号union，union all，intersect，minus多用于数据量比较大的数据局库，运转速度快。1). union该操作符用于获得两个结果集的并集。当运用该操作符时，会自动去掉结果集中反复行。SELECT ename, sal, job FROM emp WHERE sal 2500 UNIONSELECT ename, sal, job FROM emp WHERE job = MANAGER;.2).union all该操作符与union 类似，但是它不会取消反复行，而且不会排序。SELECT ename, sal, job FROM emp WHERE sal 2500U

28、NION ALLSELECT ename, sal, job FROM emp WHERE job = MANAGER;该操作符用于获得两个结果集的并集。当运用该操作符时，会自动去掉结果集中反复行。3). intersect运用该操作符用于获得两个结果集的交集。SELECT ename, sal, job FROM emp WHERE sal 2500INTERSECTSELECT ename, sal, job FROM emp WHERE job = MANAGER;4). minus运用改操作符用于获得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。SELECT

29、 ename, sal, job FROM emp WHERE sal 2500MINUSSELECT 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)取子串在字符串的位置.

30、问题：将一切员工的名字按小写的方式显示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

31、(ename,2,length(ename)-1) from emp;问题：以首字母小写,后面大写的方式显示一切员工的姓名。SQL select lower(substr(ename,1,1) |upper(substr(ename,2,length(ename)-1) from emp;问题：显示一切员工的姓名，用“我是老虎交换一切“ASQL select replace(ename,A, 我是老虎) from emp;.数学函数数学函数的输入参数和前往值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，a

32、sin，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 天的情况下，一

33、切员工的日薪金，忽略余数。SQL select trunc(sal/30), ename from emp;orSQL select floor(sal/30), ename from emp;在做oracle 测试的时候，可以运用dual 表select mod(10,2) from dual;结果是0select mod(10,3) from dual;结果是1其它的数学函数，有兴趣的同窗可以本人去看看：abs(n)： 前往数字n 的绝对值select abs(-13) from dual;acos(n)： 前往数字的反余弦值asin(n)： 前往数字的反正弦值atan(n)： 前往数字的

34、反正切值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 b

35、y Foxit PDF Creator Foxit Softwarefoxitsoftware For evaluation only.SQL select ename, hiredate from emp wheresysdate=add_months(hiredate,12*10);问题：对于每个员工，显示其参与公司的天数。SQL select floor(sysdate-hiredate) 入职天数,ename from emp;orSQL select trunc(sysdate-hiredate) 入职天数,ename from emp;问题：找出各月倒数第3 天受雇的一切员工。SQL select hiredate,ename fro