Oracle操纵数据综述.docx

1、Oracle操纵数据综述1,表管理 (1)创建表 语句:CREATE TABLE table_name ( field1 datatype, /field:指定列名 datatype：指定列类型 field2 datatype,) (2)oracle常用数据类型 char(size):定长，字母占一字节，汉字占两字节，最大2000个字符。 如：create table test1(name char(32) /在name列，最多只能放32个字符，如果超过会报错，如果不够，则用空格补全。 varchar2(size):变长，最大可存放4000个字符。 nchar(size):定长，编码方式是un

2、icode，汉字和字母都占一个字符，最大字符2000 nvarchar2(size):变长，uinicode编码，最大可存放4000个字符 clob:字符型大对象，变长，最大8tb blob:变长，最大8tb，可存放声音，图片等对象 说明：实际应用中很少把文件存放到数据库（效率问题），当需要考虑文件安全时才放入。number(p,s): 可存放整数和小数,变长，p表有效位，s表小数位.保存数据范围：-1E-130=number value=1e126,1=p=38,-84500 or job=MANAGER) and (ename like J%);12 order by(排序) 使用：排列方

3、式有降序(desc)和升序(acs),默认是acs。如 select * from emp order by sal asc/desc; /没写排序方式，则默认升序排列。注：order by后面跟数字时，表示按第几个字段排序，所跟数不能超出列数。13 使用列的别名排序：select ename,sal*12 年薪 from emp order by 年薪;14 分页查询3.oracle表复杂查询 (1)数据分组 常用函数max,min,avg,sum,count。 max取最大值，min取最小值，avg取平均值，sum取和，count统计数量。 count(*)返回的是所有数据条数量，coun

4、t(字段)返回有值的数据条数量。2 group by.having使用：按条件将数据分组 如：select avg(sal) deptno from emp group by deptno,job order by deptno; /得到每个部门每个工作的平均工资并排序。 select avg(sal) deptno from emp group by deptno having avg(sal)all(select sal from emp where deptno=30); any的使用：select * from emp where salany(select sal from emp

5、where deptno=30); 多列子查询：子查询中返回多列。如：select * from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH); from子句中使用子查询：将查询结果当做一个临时表对待。 如 select t1.* from emp t1,(select avg(sal) myavg,deptno from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.salt2.myavg; /显示高于自己部门平均工资的员工信息。

6、(3) 分页查询 各数据库分页查询语句：如演示取出数据5到12数据。mysql:select * from users limit 5,8; sql server: select top 8 * from users where id not in (select top 4 id from users); oracle: select t2.* from (select t1.*,rownum rm from (select * from emp) t1 where rownum=5; /t1是第一层过滤语句得到数据的结果集，t2是第二层过滤得到前12条数据，第三层过滤后得到5到12的8条数

7、据。 测试分页查询效率 快速建表(从emp表中复制数据)：create table mytest as empno,ename from emp; 自我复制(快捷形成海量数据表)：insert into mytest (empno,ename) select empno,ename from mytest; (4) 合并查询 union:用于取得两个结果集的并集，会自动去掉结果集中重复行，并自动排序。 union all:与union相似，但不会取消重复行，且不会排序。 intersect:取两结果的交集。 minus: 取两结果的差集，即并集与交集的差。 (5) 使用子查询完成迁移的需求 如

8、 create table temp# as select empno,ename from emp where ename like S%; (6) 使用子查询完成更新 如 update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=SMITH) where ename=SCOTT; /把scott的工作，工资，补助改成更smith一样4.oracle表的内连接和外连接 (1)内连接：就是利用where子句对两张表形成的笛卡尔集筛选,只显示匹配的数据。 如 select emp.ename,dept.dnam

9、e from emp,dept where emp.deptno=dept.deptno; 等价于 select emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno; (2)外连接 左外连接：左侧表完全显示，右侧表显示匹配数据。 如 select d.deptno,d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;/此时会把没人的40部门也显示出来。 其他写法：select d.deptno,d.dname,e.ename

10、from dept d deft ,emp e where d.deptno=e.deptno(+);3 右外连接：右侧表完全显示，左侧显示匹配数据。如select d.deptno,d.dname,e.ename from emp e right join dept d on d.deptno=e.deptno; select d.deptno,d.dname,e.ename from emp e,dept d where d.deptno(+)=e.deptno;完全外连接：完全显示两个表，没有匹配的记录置为空。如 select d.deptno,d.dname,e.ename from

11、dept d outer join emp e on d.deptno=e.deptno;5.维护数据的完整性 定义:数据完整性可以使用约束,触发器,应用程序(过程,函数)三种方法来实现,约束是维护数据完整性的首选(1) 约束:用于确保数据库数据满足特定的商业规则a) not null（非空）i. 用于指定某列值不能为空b) unique（唯一）i. 指定列值不能重复，但是可以为nullc) primary key（主键）i. 用于唯一的标示表行的数据，指定列不但不能重复，而且不能为nullii. 一张表可以有多个unique,但最多只能有一个主键d) foreign key（外键）i. 用于

12、定义主表和从表之间的关系ii. 外键约束要定义在从表上，主表则必须具有主键约束或是unique约束iii. 要求外键列数据必须在主表的主键列存在或是为nulle) check（约束）i. 用于强制数据必须满足的条件(2) 用法举例create table goods(goodsId number primary key, -设置主键goodsName varchar2(36) not null, -商品名字不能为空unitprice number check (unitprice0), -单价要大于0category varchar2(64) check (category in (食物,日用

13、品),)create table customer(customerId number primary key,cusName varchar2(32) not null,email varchar2(64) unique), -电邮不能重复sex char(2) default 男 check (sex in(男,女) , -性别只能选男或女，默认是男)create table purchase(customerId number references customer(customerId), goodsId number references goods(goodsId),nums nu

14、mber check (nums0)(3) 表约束修改a) 修改语句：alter table 表名 modify 字段 约束;b) 添加语句：alter table 表名 add constraint xxx 约束(字段)(4) 删除约束a) 语句：alter table 表名 drop constraint xxx（字段）;b) 当两张表存在主从关系时，那么删除主表的主键约束时，必须带上cascade，如 alter table 表名 drop primary key cascade;(5) 列级定义和表级定义a) 列级定义：列级定义是在定义列的同时定义约束b) 表级定义时指在定义了所有列后

15、，再定义约束，需要注意的是：not null约束只能在列级定义c) 一般情况下使用列级定义即可，但当含复合主键时要用表级定义(6) 序列(sequence):用于处理自动增长列a) 可以为表中的列自动产生值，一般用于主键或唯一列b) 由用户创建数据库对象，并可由多个用户共享c) 案例说明：创建一个序列i. create sequence myseq -创建序列名ii. start with 1 -从1开始iii. increment by 1 -每次增长1iv. maxvalue 9999/NOMAXVALUE -设置最大值9999/不设最大值v. minvalue 1 -最小值vi. cyc

16、le /NOCYCLE -循环/一直累加，不循环vii. nocache; -缓存viii. 创建后在表中使用：create table test1 (id number primary key,name varchar2(32);insert into test1 values(myseq.nextval,tang)可用myseq.currval返回当前的序列值，必须在调用了次nextval后才可用用myseq.nextval增加序列值，然后返回6.索引 (1) 单列索引:基于单个别所建立的索引 语法:create index index_name on table(columnname);

17、 (2) 复合索引:基于两列或是多列的索引。在同一张表上可以有多个索引，但要求列的组合必须不同。 语法：create index index_name on table(col_name,col_name); 注：在大表上建立索引才有意义 在where子句或是连接条件上经常引用的列上建立索引索引的层次不要超过4层 在逻辑类型字段上，或者值就固定几种的列上也不索引(3)索引的缺点： a) 建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引 b) 更新数据的时候，系统必须要又额外的时间来同时对索引更新，一维持数据和索引的一致性。7.oracle 的pl/sql编程 (1)概念pl/s

18、ql是在标准sql语句基础上扩展的一种对oracle数据库进行编程的语句。 可以定义常量和变量，而且可以使用条件语句和循环语句。 (2)pl/sql编程优点 a)提高应用程序的运行性能 b)模块化的设计思想分页的过程，订单的过程，转账的过程. c)减少网络传输量 d)提高安全性 (3) 缺点是移植性不好 (4)案例解析: 创建一简单的存储过程，可以完成向某表添加一条记录 create procedure pro1(in_empno number,in_ename varchar(16) is begin insert into emp(empno,ename) values(in_empno,

19、in_ename); end;/ 调用存储过程 exec pro1(3333,juan); (5) pl/sql作用 pl/sql可以开发 过程、函数、包（包体）、触发器。他们的基础编程单元是块(7) 编写规范a) 注释：单行注释-; 多行注释/*.*/b) 标识符号的命名规范i. 定义变量时，建议用v_作为前缀 如 v_salii. 定义常量时，建议用c_作为前缀 如 c_rateiii. 当定义游标时，建议用_cursor作为后缀 如 emp_cursoriv. 当定义例外时，建议用e_作为前缀 如 e_error(8) pl/sql块结构a) pl/sql块由三部分构成：i. decla

20、re /定义部分-定义常量、变量、游标、例外、复杂数据类型ii. begin /执行部分-要执行的pl/sql语句iii. exception /例外不理部分-处理运行的各种错误iv. end; /declare和exception部分是可选的，begin是必须的b) 案例解析i. declare1. v_ename varchar2(16); -格式：变量名 变量的类型ii. begin 1. select ename into v_ename form emp where empno=&empno;-把ename值放入变量，&表示要接收从控制台输入的变量2. dbms_output.put

21、_line(雇员名是|v_ename); -输出v_ename3. exception -异常处理语法： when 异常名称 then4. when no_data_found then dbms_output.put_line(未找到该编号的雇员); 5. end;/注：在默认情况下，结果不输出，需要设置set serveroutput on no_data_found异常要在含select .into的语句下才可捕获 块执行完就消失，所以应融入过程中案例解析2：create procedure pro2 (in_empno number) isv_ename varchar2(8);beg

22、inselect ename into v_ename from emp where empno=in_empno;dbms_output.put_line(雇员名是|v_ename); -|表示把两个字符串拼接end;(9) 过程详解a) oracle过程，可以指定参数是输入的参数，还有输出的参数b) 基本语法：create procedure 过程名(变量名in 变量类型. 变量名 out 变量类型) is.c) 调用过程方法： exec 过程名(参数值) call 过程名(参数值)d) 案例解析：i. create or replace procedure pro3(in_ename in varchar2,in_new_sal in number) is -加or replace时，当方案中已存在pro3时会字段替换ii. beginiii. update emp set sal=in_new_sal where ename=in_ename;iv. end;/e) 当出现错误时，可以输入show error来显示具体错误8.pl/sql基础知识 (1) 函数：用于返回特定的数据，在头部必须包含return子句，在函数体内必须包含return语句返回的数据。 (2) 案例分析： a)