oracle学习手册.docx
《oracle学习手册.docx》由会员分享,可在线阅读,更多相关《oracle学习手册.docx(18页珍藏版)》请在冰点文库上搜索。
oracle学习手册
Oracle数据库学习手册
1、数据库语言基础:
1、sql命令动词:
数据定义:
create、drop、alter
数据操作:
select、insert、update、delete
数据控制:
grant、revoke
2、sql语言编写规则:
A、关键字不区分大小写
B、对象名和列名也不区分大小写
C、字符值和日起值区分大小写
3、数据定义:
操作对象
操作方式
创建
删除
修改
表
Createtable
Droptable
Altertable
视图
Createview
Dropview
索引
Createindex
Dropindex
4、创建表:
5、创建视图:
视图是从一个或几个表(或视图)导出的表,它与基表不同,是一个虚表;从视图中可以看到数据库中自己感兴趣的数据。
创建视图?
A、as<子查询>:
是不包含orderby子句和distinct短语的任意select语句
B、Withcheckoption表示对视图进行update、insert、delete操作时,要保证更新、插入或删除的行满足定义中的谓词条件(即查询语句中的条件表达式)
6、索引:
create[unique][cluster]index<索引名>on<表名>(列名<索引次序>,列名<索引次序>,。
。
。
。
。
。
);
A、unique表示此索引每一个索引值不能重复
B、Cluster表示要建立的索引是聚簇索引
C、次序用于指定索引值的排列次序
7、Alter
一般格式:
altertable<表名>
Add<新表名><数据类型>[完整性约束条件]
Drop<完整性约束名>
Modify<列名><数据类型>;
用modify将email的可用长度改为了30
此处不需要id,因为在一个表中只能声明一个primarykey,因此直接删除就行;
利用drop删除表中的primarykey约束条件
为id属性添加primarykey约束条件
完整实例
→删除某一列的时候要添加关键字column
8、向表中插入数据:
insertinto<表名>(列名,列名。
。
。
)values(数值,数值。
。
。
;
其中未赋值的列均按照null或者default来处理
9、修改表中数据信息:
update<表名>set(所需修改的信息)where(条件约束语句;
默认的score值为0,属性设置通过语句:
关键字default
10、数据查询:
A、查询所有:
select*from表名;
B、使用select指定查询列:
selectcolumnname_1,name_2...fromtable_name1,table_name2;
C、使用select语句时杜宇数字数据和日期数据都已使用算术表达式
D、使用distinct来删除及结果集中的重复行;Selectdistinctdepartment_idfromemploees;
E、Where子句:
a条件表达式:
如alikeb——like为匹配运算符:
通配符%代表0个,1个或多个任意字符,'s%'表示s开头的任意字符串;not<条件表达式>;大小比较。
。
。
b链接运算符:
and、or
cNULL值:
isnull、isnotnull
dOrderby子句:
orderby列名(如salary),列名。
。
。
顺序(desc降序/asc升序);
eGroupby子句:
用于对查询结果集中分组汇总:
函数
描述
Count
返回找到的记录数
Min
最小值
Max
。
。
Sum
列求和
Avg
求平均
相同的分数可以对应不同的id值,按照分数进行统计,得到谋一分数下学号最大和最小的同学id
fhaving子句:
having与groupby一起使用,可以在汇总相关数据后进一步筛选汇总的数据——
Select语句中子句的处理顺序:
首先由from子句找到数据表,where子句则接受from子句输出的数据,而having子句则接受来自groupby、where或from子句的输入,最后是select语句的执行:
即从e)选项的信息中筛选出id值大于26的同学信息;
即从e)选项的信息中筛选出分数大于90的同学信息;
g多表连接查询:
1 简单连接:
表与表之间用逗号隔开,并用where语句进行限定——使用表别名时语句中的所有子句都必须使用别名,而不允许在使用其原名;
Fromemployeeses,departmentdepwhere。
。
。
2 Join连接:
Ø内连接innerjoin:
使用innerjoin指定用于连接的两个表,使用on指定连接表的连接条件,后还可跟where来限定
Ø自然连接:
连接多个表中具有相同的名称的列——naturaljoin。
。
。
where
Ø外连接:
包括左外连接leftjoin、右外连接rightjoin、全外连接fulljoin,它不止列出了与连接条件相匹配的航,还列出了左表(左外连接)、右表(右外连接)、两个表(全外连接)中所有符合搜索条件的数据行
Ø自连接:
自引用式外键表示表中的一个列可以是该表主键的一个外键。
自连接是在from
中两次指定同一个表,分别为表指定了两个别名,即可以看做是两个分离的数据源了
g集合操作:
将两个或多个sql查询结果合并构成符合查询,集合操作符包括union并运算、unionall、intersect交运算、minus差运算
✧
Union:
合并两个查询结果,并消除重复行,unionall不消除重复行
✧Intersect:
保留二者的公共部分
✧Minus:
保留前者未在后者出现过的部分
h子查询:
1 In关键字:
可以将原表中特定列的值与子查询返回的结果集中地值进行比较,如果某行的特定列的值存在,则在select语句的查询结果中就包含这一行
Selectemplyee_id,last_namefromemplyeeswheredepartment_idin(selectdepartment_idfromdepartmentswherelocation_id=1700;
2 Exists关键字:
exists只注重子查询的结果是否返回,要在exists关键字有意义,则应在子查询中建立搜索条件
3 比较运算符:
即与子查询所返回的结果进行比较
11、数据操纵:
A、数据的插入:
insertintotable名(列名)values(属性值);
B、查看用户表:
selecttable_namefromuser_tables;
C、查看某特定表的结构:
describetable名;
D、表名的修改:
altertable原表名renameto新表名;
E、批量插入:
即使用select语句替换values语句,由select语句提供添加的数据
v将查询结果制成新表:
createablebasselect*froma;
v把a表的全部字段数据插入到b表中:
insertintobselect*froma;
v部分:
。
。
。
D、删除操作:
删除行,deletefromtable名where。
。
。
,当删除之前用commit提交表格数据后,删除的信息能够用rollback来进行恢复;
vTruncate删除:
truncatetable表名(reusestoage|dropstoage);保留或删除空间
12、数据控制:
A、grant语句,授权语句:
grant<权限><权限><权限>。
。
On<对象类型><对象名>to<用户><用户>。
。
。
对象
对象类型
操作权限
属性列
Tablecolumn
Select、insert、update、delete
视图
Tableview
Select、insert、update、delete
基表
Table
Select、insert、update、delete、alter、index
数据库
Database
createtab
结尾时可用withgrantoption子句指定该用户是否可以传播该权限:
B、Revoke语句:
收回权限
C、删除用户:
dropuseruser_namecascade;--cascade表示级联删除该用户的所有对象,无此参数删除不了则表示表空间中还有内容
13、oracle中常用函数一览表:
Ø字符类函数:
Ø数字类函数:
Ø日期类函数:
Ø转换类函数:
Ø聚集类函数:
二、PL/SQL语言及编程:
1、基本结构:
Declare--声明一些变量、常量、用户定义的数据类型以及游标,可舍去
Begin--主程序体
Exception--异常处理程序,当程序出现错误时执行之一部分
End;--结束
2、注释
A、单行注释:
--注释部分
B、多行注释:
/*注释部分*/
3、字符集
4、
数据类型:
✧数字类型:
number、pslinteger、binary_integernumber(p,s:
p是精度,s是刻度范围如果刻度范围是一个负数,就由小数点开始向左边计算数字位的个数
✧字符类型:
varchar(n)、char(n)、long(n)[long和varchar的结构基本一致]
✧日期类型:
date——世纪、年、月、日、时、分、秒
✧Type定义的数据类型:
Type<数据类型名>is<数据类型>:
oracle中允许定义RECODE(记录类型)和TABLE(表类型);
例1:
typeteacher_recordisRECORD
(
.........................
例2:
Ateacherteacher_record;
5、常量的定义:
<常量名>constant<数据类型>:
=<值>;
6、控制结构:
ØCase语句:
Case检测表达式
When表达式1then语句序列1
When表达式2then语句序列2
。
。
。
Else其他语句序列
End;
ØNULL结构:
NULl结构必须添加null判断语句
Ø循环结构:
⏹Loop...表达式...if(exit;endif;...值改变句...endloop;
⏹Loop...表达式...when(...值改变句...endloop;
⏹While(......loop...表达式...;...值改变句...endloop;
⏹For...in...loop..endloop;
FORcontrolin0...5LOOP
NUll;--for语句会自动给controll_var加1,因此此为空
ENDLOOP;
⏹Goto表达式:
。
。
。
--程序其他部分
<>
。
。
。
--程序其他部分
IFno>9THEN
GOTOgoto_mark;
。
。
.--程序其他部分
7、表达式:
并运算符(||):
链接字符串
布尔表达式:
and、or、not
Between:
划定一个范围,在范围内为真
In:
判断某一元素是否属于某个聚合,返回真或假
8、PL/SQL游标:
显示游标和隐式游标,如同c语言中的指针类型
1
提取游标
打开游标
声明游标
游标的一般使用方法:
否
关闭游标
空?
是
A、声明游标:
cursor<游标名>isselect<语句>;select<语句>为即将处理的查询动作;
B、打开游标:
open<游标名>;
C、提取游标:
fetch<游标名>into,<表的列表(属性)>或fetch<游标名>intoPL/SQL记录;
D、关闭游标:
close<游标名>;
范例1:
找出成绩大于90分的同学,并添加到表优秀当中:
查看表youxiu数据结果:
✓显示游标使用前需用%isopen检查其打开状态,只有此值为true的游标才可使用,否则要用则先将游标打开
✓用%FOUND或%NOTFOUND属性检查是否返回成功,及是否还有要操作的行
✓将游标行取至变量组时,对应变量个数和数据类型必须完全一致
✓使用完要关闭游标,以释放相应的内存资源
2 游标的属性:
a.是否找到游标:
游标名+%FOUND或者是游标名+%NOTFOUND
b.游标行数:
游标名+%ROWCOUNT
c.是否打开:
游标名+%ISOPEN
d.参数化游标:
定义游标时,可以带上参数
a如:
declarecursorteacher_cur(cursor_idnumber)is。
。
。
3 游标变量:
指在打开时才确定所对应的查询
A、定义游标变量类型:
TYPE<类型名>ISREFCURSOR
RETURN<返回类型>;
vREF关键字指明新的类型是必须是一个指向经过定义的类型的指针;
v游标变量是一个引用行变量
v<返回类型>必须是一个记录类型,它指明了最终由游标返回的选择列表的类型
v它可以显示声明为一个用户定义的记录,或隐式使用%ROWTYPE进行声明
B、打开游标变量
9、过程:
●创建语法:
Createorreplaceprocedure名称(参数1方式数据类型,参数2方式数据类型。
。
。
)
Is|as
Pl/sql过程体;
A、常见方式有in、out、inout
B、数据类型:
teacher.sex%type表示以sex的数据类型为依据,保持一致
C、过程体中一般放将要返回的变量名+类型
D、过程不需要返回值
E、Count函数返回行的个数;
●调用过程:
EXECUTEcount_num('m';
EXECUTEcount_num('f'';
删除:
dropprocedurecount_num;
10、过程的参数及其传递:
A、in参数类型:
输入类型的参数如:
in_numinNUMBER
B、Out参数类型:
输出型的参数如:
out_numinNUMBER
C、Inout参数类型
11、函数:
Createorreplacefunction名称(参数1方式数据类型,参数2方式数据类型。
。
。
)
Is|as
Pl/sql过程体
Return表达式--表达式就是要函数返回的值
Is|as
Pl/sql程序体;
Ø函数中必须有返回值:
return(out_name;
函数调用:
A、以全局变量接收其返回值:
B、在程序块中调用函数:
C、函数的删除同过程的一致
12、包:
如同c++语言中的类,它可以包含变量、函数、过程
A、包的创建:
说明部分和包体部分
a包的说明:
一般形式:
Createpackage包名
Is
变量、常量、及其数据类型定义;
游标定义开头部分;
函数、过程的定义和参数列表及返回类型
b包体部分:
Createpackagebody包名
As
游标、函数、过程的具体定义;
End包名;
B、包的调用:
包名.变量名(常量名)包名.游标名包名.函数(过程)名
SQL>VARIABLEman_numnumber;
SQl>EXECUTIVEman_num:
=my_package.f_count_num('m';
Ø如果包体的过程或函数定义中有变量声明,则包外不能使用这些私有变量
13、触发器:
14、Selectusernamefromdba_users;--查找用户
15、List--调用缓冲区的代码
16、Edit--文本编辑
17、Run--命令运行缓冲区程序
18、更改dos下属性的输出选项的宽度:
19、修改某一属性名:
altertable表名renamecolumn列名to新列名;
20、novalidate--用于添加约束条件的末尾,表示不验证原表格中的数据