Oracle操纵数据综述.docx

Oracle操纵数据综述.docx

《Oracle操纵数据综述.docx》由会员分享，可在线阅读，更多相关《Oracle操纵数据综述.docx（31页珍藏版）》请在冰点文库上搜索。

Oracle操纵数据综述

1,表管理

（1）创建表

语句:

CREATETABLEtable_name

（field1datatype,//field:

指定列名datatype：

指定列类型

field2datatype,

）

（2）oracle常用数据类型

①char（size）:

定长，字母占一字节，汉字占两字节，最大2000个字符。

如：

createtabletest1（namechar（32））//在name列，最多只能放32个字符，如果超过会报错，如果不够，则用空格补全。

②varchar2（size）:

变长，最大可存放4000个字符。

③nchar（size）:

定长，编码方式是unicode，汉字和字母都占一个字符，最大字符2000

④nvarchar2（size）:

变长，uinicode编码，最大可存放4000个字符

⑤clob:

字符型大对象，变长，最大8tb

⑥blob:

变长，最大8tb，可存放声音，图片等对象

说明：

实际应用中很少把文件存放到数据库（效率问题），当需要考虑文件安全时才放入。

⑦number（p,s）:

可存放整数和小数,变长，，p表有效位，s表小数位.

保存数据范围：

-1E-130<=numbervalue<=1e126,1<=p<=38,-84<=127,保存机器数范围：

1~22bytes,

如:

number（5,2）存入的数范围在-999.99—999.99,若s位超出2，会自动四舍五入,

当s是负数时，则往左取，如number（5,-2）,输入数123.45时实际存入的是100

⑧date日期类型：

用于表示时间（年/月/日/时/分/秒）

oracle添加时的默认格式是‘dd-mm-yyyy’;

（2）表修改

语句：

altertabletable_nameadd（column_namedatatype）;//添加新字段

altertabletable_namemodify（column_namedatatype）;//修改字段类型

altertabletable_namedropcolumncolumn_name;//删除字段

renameold_nametonew_name;//修改表名

2.oracle的crud操作:

c（create）,r（retrieve/read）,u（update）,d（delete）

（1）添加（insert）

语句：

insertintotable（column[,column...]）values（value[,value...]）;

1插入的数据应与字段的数据类型相同，且要在规定范围内。

2在values中列出的数据位置必须与被加入的列的排列顺序相对应。

3字符和日期数据应包含在单引号中。

4若给表每列都添加值，可以不带列名。

（2）修改（update）

语句：

updatetable_namesetcolumn_name=表达式where条件;

1set子句指示要修改哪些列和要改成什么值，where子句指定要更新哪些行，如没有，则更新所有行。

（3）删除（delete）

语句：

deletefromtb_namewhere条件;

1如果不使用where子句，将删除表中所有数据。

2delete语句不能删除某一列的值（可使用update）。

3删除数据的几种方法比较：

a）deletefrom表名：

删除所有记录，表结构还在，写日志，可以恢复，速度慢。

步骤：

设置保存点：

savepointxx;滚回保存点:

rollbacktoxx。

b）droptable表名：

删除表的数据和结构。

c）truncatetable表名：

删除表中所有记录，表结构还在，不写日志，无法找回，速度快。

（4）查询（select）

语法：

select[distinct]*|{col1,col2...}fromtable[条件][orderby字段];

1select指定查询哪些列的数据。

2distinct可选，指显示结果时是否剔除重复数据。

3oracle语句不区分大小写，内容区分大小写。

4处理null,用nvl（字段，值a）函数，若字段不为空则返回字段值，为空则返回值a。

5使用列别名：

如selectename“姓名”,sal*12年收入fromemp;//别名可加双引号也可不加。

6连接字符串：

如selectename||’爱’||jobfromemp;//显示结果为:

ename爱job.

7%表0或多个字符，_代表一个字符，如selectename,salfromempwhereenamelike‘S%’;//表示查询以s开头员工的工资。

8where条件中使用in:

select*fromempwhereempnoin（123,345,800）;

9where条件中使用between..and...：

select*fromempwheresalbetween2000and2500;

10isnull使用：

判断一数值是否为空时不能直接等于null.

如select*fromempwheremgrisnull;

11使用逻辑运算符：

or和and.

如select*fromempwhere（sal>500orjob=’MANAGER’）and（enamelike‘J%’）;

12orderby（排序）使用：

排列方式有降序（desc）和升序（acs）,默认是acs。

如select*fromemporderbysal[asc/desc];//没写排序方式，则默认升序排列。

注：

orderby后面跟数字时，表示按第几个字段排序，所跟数不能超出列数。

13使用列的别名排序：

selectename,sal*12年薪fromemporderby年薪;

14分页查询

3.oracle表复杂查询

（1）数据分组

①常用函数max,min,avg,sum,count。

max取最大值，min取最小值，avg取平均值，sum取和，count统计数量。

count（*）返回的是所有数据条数量，count（字段）返回有值的数据条数量。

2groupby...having使用：

按条件将数据分组

如：

selectavg（sal）deptnofromempgroupbydeptno,joborderbydeptno;//得到每个部门每个工作的平均工资并排序。

selectavg（sal）deptnofromempgroupbydeptnohavingavg（sal）<2000;

③在选择列中如果有列、表达式和分组函数，那么这些列或表达式必须有一个出现groupby子句中。

④sql执行在默认情况下是从右到左执行。

如：

selectename,jobfromempwheresal=（selectmax（sal）fromemp）;

（2）多表查询

①多表查询如果不带任何条件，则出现笛卡尔集，多表查询得条件至少不能少于表的个数-1.

如：

selectemp.ename,emp.sal,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;

②多表查询时，如果两表的列同名，则需加表名区分。

③自连接：

在同一张表的连接查询,将一张表当成两张表看。

如：

selectw.ename,b.enamefromempw,empbwherew.mgr=b.empno（+）;

（3）子查询:

是指嵌入在其它sql语句中的select语句，也叫嵌套查询。

①单行子查询：

只返回一行数据的子查询语句。

如select*fromempwheredeptno=（selectdeptnofromempwhereename=’SMITH’）andename!

=’SMITH’;//子句只返回一个数据;

②多行子查询：

返回多行数据的子查询。

如select*fromempwherejobin（selectdistinctjobfromempwheredeptno=10）;

all的使用：

select*fromempwheresal>all（selectsalfromempwheredeptno=30）;

any的使用：

select*fromempwheresal>any（selectsalfromempwheredeptno=30）;

③多列子查询：

子查询中返回多列。

如：

select*fromempwhere（deptno,job）=（selectdeptno,jobfromempwhereename=’SMITH’）;

④from子句中使用子查询：

将查询结果当做一个临时表对待。

如selectt1.*fromempt1,（selectavg（sal）myavg,deptnofromempgroupbydeptno）t2wheret1.deptno=t2.deptnoandt1.sal>t2.myavg;//显示高于自己部门平均工资的员工信息。

（3）分页查询

①各数据库分页查询语句：

如演示取出数据5到12数据。

mysql:

select*fromuserslimit5,8;

sqlserver:

selecttop8*fromuserswhereidnotin（selecttop4idfromusers）;

oracle:

selectt2.*from（selectt1.*,rownumrmfrom（select*fromemp）t1whererownum<=12）t2wheret2.rm>=5;//t1是第一层过滤语句得到数据的结果集，t2是第二层过滤得到前12条数据，第三层过滤后得到5到12的8条数据。

②测试分页查询效率

快速建表（从emp表中复制数据）：

createtablemytestasempno,enamefromemp;

自我复制（快捷形成海量数据表）：

insertintomytest（empno,ename）selectempno,enamefrommytest;

（4）合并查询

①union:

用于取得两个结果集的并集，会自动去掉结果集中重复行，并自动排序。

②unionall:

与union相似，但不会取消重复行，且不会排序。

③intersect:

取两结果的交集。

④minus:

取两结果的差集，即并集与交集的差。

（5）使用子查询完成迁移的需求

如createtabletemp#asselectempno,enamefromempwhereenamelike‘S%’;

（6）使用子查询完成更新

如updateempset（job,sal,comm）=（selectjob,sal,commfromempwhereename=’SMITH’）whereename=’SCOTT’;//把scott的工作，工资，补助改成更smith一样

4.oracle表的内连接和外连接

（1）内连接：

就是利用where子句对两张表形成的笛卡尔集筛选,只显示匹配的数据。

如selectemp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept..deptno;

等价于selectemp.ename,dept.dnamefromempinnerjoindeptonemp.deptno=dept.deptno;

（2）外连接

①左外连接：

左侧表完全显示，右侧表显示匹配数据。

如selectd.deptno,d.dname,e.enamefromdeptdleftjoinempeond.deptno=e.deptno;//此时会把没人的40部门也显示出来。

其他写法：

selectd.deptno,d.dname,e.enamefromdeptddeft,empewhered.deptno=e.deptno（+）;

3右外连接：

右侧表完全显示，左侧显示匹配数据。

如selectd.deptno,d.dname,e.enamefromemperightjoindeptdond.deptno=e.deptno;

selectd.deptno,d.dname,e.enamefromempe,deptdwhered.deptno（+）=e.deptno;

③完全外连接：

完全显示两个表，没有匹配的记录置为空。

如selectd.deptno,d.dname,e.enamefromdeptdouterjoinempeond.deptno=e.deptno;

5.维护数据的完整性

定义:

数据完整性可以使用约束,触发器,应用程序（过程,函数）三种方法来实现,约束是维护数据完整性的首选

（1）约束:

用于确保数据库数据满足特定的商业规则

a）notnull（非空）

i.用于指定某列值不能为空

b）unique（唯一）

i.指定列值不能重复，但是可以为null

c）primarykey（主键）

i.用于唯一的标示表行的数据，指定列不但不能重复，而且不能为null

ii.一张表可以有多个unique,但最多只能有一个主键

d）foreignkey（外键）

i.用于定义主表和从表之间的关系

ii.外键约束要定义在从表上，主表则必须具有主键约束或是unique约束

iii.要求外键列数据必须在主表的主键列存在或是为null

e）check（约束）

i.用于强制数据必须满足的条件

（2）用法举例

createtablegoods（

goodsIdnumberprimarykey,--设置主键

goodsNamevarchar2（36）notnull,--商品名字不能为空

unitpricenumbercheck（unitprice>0）,--单价要大于0

categoryvarchar2（64）check（categoryin（'食物','日用品'））,

）

createtablecustomer（

customerIdnumberprimarykey,

cusNamevarchar2（32）notnull,

emailvarchar2（64）unique）,--电邮不能重复

sexchar

（2）default'男'check（sexin（'男','女'））,--性别只能选男或女，默认是男

）

createtablepurchase（

customerIdnumberreferencescustomer（customerId）,

goodsIdnumberreferencesgoods（goodsId）,

numsnumbercheck（nums>0）

）

（3）表约束修改

a）修改语句：

altertable表名modify字段约束;

b）添加语句：

altertable表名addconstraintxxx约束（字段）

（4）删除约束

a）语句：

altertable表名dropconstraintxxx（字段）;

b）当两张表存在主从关系时，那么删除主表的主键约束时，必须带上cascade，如altertable表名dropprimarykeycascade;

（5）列级定义和表级定义

a）列级定义：

列级定义是在定义列的同时定义约束

b）表级定义时指在定义了所有列后，再定义约束，需要注意的是：

notnull约束只能在列级定义

c）一般情况下使用列级定义即可，但当含复合主键时要用表级定义

（6）序列（sequence）:

用于处理自动增长列

a）可以为表中的列自动产生值，一般用于主键或唯一列

b）由用户创建数据库对象，并可由多个用户共享

c）案例说明：

创建一个序列

i.createsequencemyseq--创建序列名

ii.startwith1--从1开始

iii.incrementby1--每次增长1

iv.maxvalue9999//NOMAXVALUE--设置最大值9999//不设最大值

v.minvalue1--最小值

vi.cycle//NOCYCLE--循环//一直累加，不循环

vii.nocache;--缓存

viii.创建后在表中使用：

createtabletest1（idnumberprimarykey,namevarchar2（32））;

insertintotest1values（myseq.nextval,’tang’）

可用myseq.currval返回当前的序列值，必须在调用了次nextval后才可用

用myseq.nextval增加序列值，然后返回

6.索引

（1）单列索引:

基于单个别所建立的索引

语法:

createindexindex_nameontable（columnname）;

（2）复合索引:

基于两列或是多列的索引。

在同一张表上可以有多个索引，但要求列的组合必须不同。

语法：

createindexindex_nameontable（col_name,col_name）;

注：

①在大表上建立索引才有意义

②在where子句或是连接条件上经常引用的列上建立索引

③索引的层次不要超过4层

④在逻辑类型字段上，或者值就固定几种的列上也不索引

（3）索引的缺点：

a）建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引

b）更新数据的时候，系统必须要又额外的时间来同时对索引更新，一维持数据和索引的一致性。

7.oracle的pl/sql编程

（1）概念

pl/sql是在标准sql语句基础上扩展的一种对oracle数据库进行编程的语句。

可以定义常量和变量，而且可以使用条件语句和循环语句。

（2）pl/sql编程优点

a）提高应用程序的运行性能

b）模块化的设计思想[分页的过程，订单的过程，转账的过程...]

c）减少网络传输量

d）提高安全性

（3）缺点是移植性不好

（4）案例解析:

①创建一简单的存储过程，可以完成向某表添加一条记录

createprocedurepro1（in_empnonumber,in_enamevarchar（16））is

begininsertintoemp（empno,ename）values（in_empno,in_ename）;

end;/

②调用存储过程

execpro1（3333,’juan’）;

（5）pl/sql作用

pl/sql可以开发过程、函数、包（包体）、触发器。

他们的基础编程单元是块

（7）编写规范

a）注释：

单行注释--;多行注释/*...*/

b）标识符号的命名规范

i.定义变量时，建议用v_作为前缀如v_sal

ii.定义常量时，建议用c_作为前缀如c_rate

iii.当定义游标时，建议用_cursor作为后缀如emp_cursor

iv.当定义例外时，建议用e_作为前缀如e_error

（8）pl/sql块结构

a）pl/sql块由三部分构成：

i.declare//定义部分---定义常量、变量、游标、例外、复杂数据类型

ii.begin//执行部分---要执行的pl/sql语句

iii.exception//例外不理部分---处理运行的各种错误

iv.end;//declare和exception部分是可选的，begin是必须的

b）案例解析

i.declare

1.v_enamevarchar2（16）;--格式：

变量名变量的类型

ii.begin

1.selectenameintov_enameformempwhereempno=&empno;---把ename值放入变量，&表示要接收从控制台输入的变量

2.dbms_output.put_line（‘雇员名是’||v_ename）;--输出v_ename

3.exception---异常处理语法：

when异常名称then

4.whenno_data_foundthendbms_output.put_line（‘未找到该编号的雇员’）;

5.end;/

注：

在默认情况下，结果不输出，需要设置setserveroutputon

no_data_found异常要在含select...into的语句下才可捕获

块执行完就消失，所以应融入过程中

案例解析2：

createprocedurepro2（in_empnonumber）is

v_enamevarchar2（8）;

begin

selectenameintov_enamefromempwhereempno=in_empno;

dbms_output.put_line（‘雇员名是’||v_ename）;---||表示把两个字符串拼接

end;

（9）过程详解

a）oracle过程，可以指定参数是输入的参数，还有输出的参数

b）基本语法：

createprocedure过程名（变量名in变量类型...变量名out变量类型）is...

c）调用过程方法：

①exec过程名（参数值）②call过程名（参数值）

d）案例解析：

i.createorreplaceprocedurepro3（in_enameinvarchar2,in_new_salinnumber）is---加orreplace时，当方案中已存在pro3时会字段替换

ii.begin

iii.updateempsetsal=in_new_salwhereename=in_ename;

iv.end;/

e）当出现错误时，可以输入showerror来显示具体错误

8.pl/sql基础知识

（1）函数：

用于返回特定的数据，在头部必须包含return子句，在函数体内必须包含return语句返回的数据。

（2）案例分析：

a）