Oracle全.docx
《Oracle全.docx》由会员分享，可在线阅读，更多相关《Oracle全.docx（29页珍藏版）》请在冰豆网上搜索。
Oracle全
Oracle:
1、不等于
select*fromusernamewherename!
='god';
select*fromusernamewherename<>'god';
select*fromusernamewherenotname='god';
2、查询表结构：
descusername;
3、多表连接查询：
无条件连接：
selectemp.empno,emp.ename,emp.depno,dept.dname
fromscott.emp,scott.dept;
返回笛卡乐积。
等值查询：
selectemp.empno,emp.ename,emp.depno,dept.dname
fromscott.emp,scott.dept
wherescott.emp.depno=scott.dept.depno;
非等值查询
selectemp.empno,emp.ename,emp.depno,dept.dname
fromscott.emp,scott.dept
wherescott.emp.depno!
=scott.dept.depnoandscott.dept.depno='20';
4、嵌套查询：
简单嵌套查询：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresal>=（selectsalfromscott.empwhereename='ward'）;
带in嵌套查询：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresalin（selectsalfromscott.empwhereename='ward'）;
带any嵌套查询：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresal>any（selectsalfromscott.empwhereename='manager'）;
执行步骤：
先执行selectsalfromscott.empwhereename='manager'可能返回多个值：
如：
1250,1130,2500
再执行：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresal>1250orsal>1130orsal>2500;
带some的嵌套查询：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresal=some（selectsalfromscott.empwhereename='manager'）;
执行步骤：
先执行selectsalfromscott.empwhereename='manager'可能返回多个值：
如：
1250,1130,2500
再执行：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresal=1250orsal=1130orsal=2500;
带all的嵌套查询：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresal>all（selectsalfromscott.empwhereename='manager'）;
执行步骤：
先执行selectsalfromscott.empwhereename='manager'可能返回多个值：
如：
1250,1130,2500
再执行：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp
wheresal>1250andsal>1130andsal>2500;
带exists的嵌套查询：
selectemp.empno,emp.ename,emp.job,emp.sal
fromscott.emp,scott.dept
whereexists
（select*fromscott.empwherescott.emp.deptno=scott.dept.deptno）;
集合并操作：
（selectdeptnofromscott.emp）
union
（selectdeptnofromscott.dept）;
交集合操作：
（selectdeptnofromscott.emp）
intersect
（selectdeptnofromscott.dept）;
差集合操作：
即属于集合A且不属于集合B的元素总和就是差集。
（selectdeptnofromscott.dept）
minus
（selectdeptnofromscott.emp）;
5、使用函数查询：
ceil函数：
selectsal,sal/100,ceil（sal/100）fromscott.emp;
ceil（n）取大于等于数值n的最小整数。
如sal/100=78.39,则ceil（sal/100）=79
注意它并不是四舍五入的。
同时注意oracle中sal/100指的不是整除。
floor函数：
floor（n）取小于等于数值n的最大整数。
如：
sal/100=17.12,则floor（sal/100）=17
mod（m,n）函数：
mod（m,n）取m整除n后的余数：
如mod（17,3）=2,mod（17/2）=1
power（m,n）取m的n次方
power（3,2）=9
round（m,n）
四舍五入，保留n位。
如：
round（1.333,2）=1.33
sign（n）
n>0取1,n=0取0,n<0取-1;
avg（字段名）
求平均值，字段为数值型。
count（*）
统计总数；
min（字段名）,max（字段名）
计算数值型字段最小数最大数。
sum（字段名）计算数值型字段总和。
6、表间复制
注意没有mssql中语句：
select*into..from...
用法如下：
createtablesystem.username1
as
（
select*fromsystem.username
）;
即创建表username1,并将username表中的数据复制到username1表。
7、删除语句：
deletefrom
truncatefromtablename
这两个语句的区别：
delete删除的数据内容会存储在系统回滚段中，需要的时候，数据仍可回滚恢复，但truncate命令删除的数据是不可恢复的。
8、系统默认账户名：
密码：
systemmanager
syschange_on_install
scotttiger
9、创建表格时使用主键、外键约束
CREATETABLE"SCOTT"."STUDENT"（"STUDENT_ID"NUMBER（8）NOTNULL,
"NAME"VARCHAR2（10byte）NOTNULL,"BIRTHDAY"DATENOTNULL,
"DIRECTOR_ID"NUMBER（6）NOTNULL,
CONSTRAINT"导师编码外键"FOREIGNKEY（"DIRECTOR_ID"）
REFERENCES"SCOTT"."DIRECTOR"（"DIRECTOR_ID"）,
CONSTRAINT"学生编码主键"PRIMARYKEY（"STUDENT_ID"）
USINGINDEX
TABLESPACE"USERS"
STORAGE（INITIAL64KNEXT0KMINEXTENTS1MAXEXTENTS
2147483645PCTINCREASE0）PCTFREE10INITRANS2MAXTRANS255）
TABLESPACE"USERS"PCTFREE10PCTUSED0INITRANS1MAXTRANS
255
STORAGE（INITIAL64KNEXT0KMINEXTENTS1MAXEXTENTS
2147483645PCTINCREASE0）
LOGGING
10、使用truncatetable时，如果不事先将导师表和学生表的外键关系去掉而直接删除导师表将会出错。
先执行：
altertablescott.student
disableconstraint"导师编码外键";
再truncatetablescott.Direactordropstorage;
11、插入日期数据时注意的地方
INSERTINTO"SCOTT"."STUDENT"（"STUDENT_ID","NAME","BIRTHDAY","DIRECTOR_ID"）
VALUES（90002,'zhangji',TO_DATE（'03-11月-1983','dd-Mon-yyyyHH:
MI:
SSAM'）,200202）
注意BIRTHDAY为DATE类型数据，插入时应使用：
TO_DATE（'03-11月-1983','dd-Mon-yyyyHH:
MI:
SSAM'）
12、创建索引
CREATEUNIQUEINDEX"SCOTT"."学生编码主键"
ON"SCOTT"."STUDENT"（"STUDENT_ID"）
TABLESPACE"USERS"
13、创建约束条件
CONSTRAINT"约束"CHECK（STUDENT_ID>9000ANDSTUDENT_ID<90005）
14、ORACLE用户管理
用户名口令登录身份说明
syschang_on_installsysdba或sysoper,但不能以normal身份登录，可做默认系统管理员。
systemmanagersysdba或normal,但不能以sysoper身份登录，可做默认系统管理员。
scotttigernormal普通用户
aqadmaqadmsysdba或normal,高级队列管理员
Dbsnmpdbsnmpsysdba或normal,复制管理员
15、创建用户
CREATEUSER"TEMPUSER"PROFILE"DEFAULT"
IDENTIFIEDBY"sys833199"DEFAULTTABLESPACE"USERS"
ACCOUNTUNLOCK;
GRANT"CONNECT"TO"TEMPUSER";
16、PL/SQLprocedurelanguage过程化SQL
如创建表：
CREATETABLE"TEMPUSER"."TESTTABLE"（"RECORDNUMBER"NUMBER（4）NOT
NULL,"CURRENTDATE"DATENOTNULL）
TABLESPACE"USERS"
在sqlplusworksheet中执行以下语句：
setserveroutputon
declare
maxrecordsconstantint:
=100;
iint:
=1;
begin
foriin1..maxrecordsloop
insertintotempuser.testtable（recordnumber,currentdate）
values（i,sysdate）;
endloop;
dbms_output.put_line（'成功录入数据！
'）;
commit;
end;
在表testtable中插入了一百条数据，recordnumber从1到100,currentdate为当前系统日期值。
1、完整的PLSQL结构为：
declare
定义语句段
begin
执行语句段
exception
异常处理语句段
end
2、基本语法
常量：
常量名constant类型标识符[notnull]:
=值；
如：
declare
piconstantnumber（9）:
=3.1415926;
begin
commit;
end;
变量：
变量名类型标识符[notnull]:
=值；
declare
agenumber（6）:
=26;
begin
commit;
end;
3、PLSQL中的常用的基本类型
类型标识符说明
Number数字型
Int整数型
Pls_integer整数型，产生溢出时出现错误
Char定长字符型，最大255个字符
Varchar2变长字符型，最大2000个字符
Long变长字符型，最大2GB
Date日期型
Boolean布尔型（true,false,null三都取一）
4、PLSQL中的复合类型变量
4.1、plsql中的类型与ORACLE中的类型有的含义一样，有的则不同。
这样ORACLE引入了%TYPE方法来定义变量
如：
testtable表中字段currentdate为date类型，
在PLSQL中引入此变量类型，这样当表中字段改变时，在对应的PLSQL中的变量类型也相应改变了。
变量名数据表名。
列名%type;
使变量获得与表中某一列相同的类型。
declare
mydatetempuser.testtable.currentdate%type;
begin
commit;
end;
4.2、定义记录类型的变量即多个基本数据类型捆绑在一起。
如
setserveroutputon
declare
typemyrecordisrecord（
myrecordnumberint,
mycurrentdatedate）;
srecordmyrecord;
begin
select*intosrecordfromtempuser.testtablewhererecordnumber=21;
dbms_output.put_line（srecord.mycurrentdate）;
end;
以上定义了名为myrecord的记录类型，srecord为记录类型变量。
4.3、使用%rowtype定义变量
变量名数据表%rowtype
获得整个记录的数据类型。
declare
mytabletempuser.testtable%rowtype;
begin
select*intomytablefromtempuser.testtablewhererecordnum=32;
dbms_output.put_line（mytable.currentdate）;
end;
4.4、定义一维表类型变量
type表类型istableof类型indexbybinary_integer;
表变量名表类型；
其中类型可以是基本类型也可以是上面1,2,3中定义的类型
declare
typetabletype1istableofvarchar2（4）indexbybinary_integer;
typetabletype2istableoftempuser.testtable.recordnumber%typeindexbybinary_integer;
table1tabletype1;
table2tabletype2;
begin
table1
（1）:
='大学';
table1
（2）:
='大专';
table2
（1）:
=88;
table2
（2）:
=99;
dbms_output.put_line（table1
（1）||table2
（1））;
dbms_output.put_line（table1
（2）||table2
（2））;
end;
以上定义了两个一维表类型tabletype1,tabletype2,相当两个一维数组，然后定义了两个一维表类型变量table1,table2.
在执行部分提供了对他们的赋值，最后通过||字符串连接符输出，输出如：
大学88大专99
在表类型变量中可以使用count,delete,first,last,next,exists,prior等属性操作，使用方法为变量名。
属性名
如：
setserveroutputon
declare
typetabletype1istableofvarchar2（9）indexbybinary_integer;
table1tabletype1;
begin
table1
（1）:
='北京市';
table1
（2）:
='青岛市';
table1（3）:
='上海市';
table1（4）:
='长沙市';
table1（5）:
='南昌市';
dbms_output.put_line（'总记录数为：
'||to_char（table1.count））;
dbms_output.put_line（'第一条记录：
'||table1.first）;
dbms_output.put_line（'最后一条记录：
'||table1.last）;
dbms_output.put_line（'第一条记录：
'||table1.first）;
dbms_output.put_line（'第二条的前一条记录：
'||table1.prior
（2））;
dbms_output.put_line（'第二条的后一条记录：
'||table1.next
（2））;
end;
输出结果为：
总记录数为：
5
第一条记录：
1
最后一条记录：
5
第一条记录：
1
第二条的前一条记录：
1
第二条的后一条记录：
3
4.5、定义多维表类型变量
setserveroutputon
declare
typetabletype1istableoftesttable%rowtypeindexbybinary_integer;
table1tabletype1;
begin
select*intotable1（60）
fromtempuser.testtable
whererecordnumber=60;
dbms_output.put_line（table1（60）.recordnumber||table1（60）.currentdate）;
end;
输出：
6021-7月-08
5、表达式
字符表达式：
||
当变量为数值型时，需使用to_char（变更名）转换为字符名再通过dbms_output.put_line输出。
关系表达式：
in在。
。
之中
常见类型转换函数
to_char（）:
将其它类型转换成字符型
to_date（）:
将其它类型转换成日期型
to_number（）:
将其它类型转换成数值型
6、流程控制
条件控制1：
if条件then
语句段;
endif;
条件控制2：
if条件then
语句段1;
else
语句段2;
endif;
条件控制3：
if嵌套
if条件then
if条件then
语句段；
else
语句段；
endif;
else
语句段；
endif;
示例：
setserveroutputon
declare
num1Integer:
=12;
num2Integer:
=23;
begin
ifnum1>num2then
dbms_output.put_line（'num1>num2'）;
else
dbms_output.put_line（'num1<=num2'）;
endif;
end;
循环控制1:
loop...exit..endloop
loop
循环语句段；
if条件语句then
exit;
else
退出循环的处理语句段；
endif;
endloop;
示例：
declare
num1Integer:
=20;
num2Integer:
=30;
iInteger:
=0;
begin
loop
num1:
=num1+1;
ifnum1=num2then
exit;
else
i:
=i+1;
endif;
endloop;
dbms_output.put_line（'共循环次数'||to_char（i））;
end;
输出结果：
9
循环控制2:
loop...exitwhen..endloop
declare
num1Integer:
=20;
num2Integer:
=30;
iInteger:
=0;
begin
loop
num1:
=num1+1;
i:
=i+1;
exitwhennum1=num2
endloop;
dbms_output.put_line（'共循环次数'|
升级会员 