2023年11月29日发(作者：)
oracle数据库连接
Oracle 是⼀个数据库管理系统，是Oracle公司的核⼼产品。其在数据安全性与安整性控制⽅⾯的优越性能，以及跨操作系统、跨硬件平台的
数据操作能⼒。基于“客户端/服务 器”(Client/Server)系统结构。
主要特点：
1.⽀持多⽤户、⼤事务量的事务处理。
2.在保持数据安全性和完整性⽅⾯性能优越。
3.⽀持分布式数据处理。将公布在不同物理位置的数据库⽤通信⽹络连接起来，组成⼀个逻辑上统⼀的数据库，完成
数据处理任务。
4.具有可移植性。Oracle可以在Windows、Linux等多个操作系统平台上使⽤。
Oracle基本概念：
1.数据库。这⾥的数据库是磁盘上存储数据的集合，在物理上表现为数据⽂件、⽇志⽂件和控制⽂件等。在逻辑上以表空
间形式存在。必须⾸先创建数据库，然后才能⽤Oracle。可以在Database Configuation Assistant上创建。
2.全局数据库名。⽤于区分⼀个数据库的标识。它由数据库名称和域名构成，类似⽹络中的域名，使数据库的命名在整个
⽹络环境中唯⼀。
3.数据库实例。每个启动的数据库都对应⼀个数据库实例，由这个实例来访问数据库中的数据。
4.表空间。每个数据库都是由若⼲个表空间构成的，⽤户在数据库中建⽴的所有内容都被存储到表空间中。⼀个表空间可
以由多个数据⽂件组成，但⼀个数据⽂件只能属于⼀个表空间。
5.数据⽂件。扩展名是.dbf，是⽤于存储数据库数据的⽂件。⼀个数据⽂件中可能存储很多个表的数据，⽽⼀个表的数据
也可以存放在多个数据⽂件中。数据⽂件和数据库表不存在⼀对⼀的关系。
6.控制⽂件。技展名.ctl，是⼀个⼆进制⽂件。控制⽂件是数据库启动及运⾏所必需的⽂件。存储数据⽂件和⽇志⽂件的
名称和位置。Oracle 11g默认包含三个控制⽂件。
7.⽇志⽂件。扩展名.log，它记录了数据的所有更改信息，并提供了⼀种数据恢复机制，确保在系统崩溃或其他意外出现
后重新恢复数据库。在⼯作过程中，多个⽇志⽂件组之间循环使⽤。
8.模式和模式对象。模式是数据库对象(表、索引等，也称模式对象)的集合。
Windows下Oracle数据库服务：
1.OracleServiceSID服务是Oracle数据库服务。此服务对应名为SID(系统标识符)的数据库实例创建的。必须启动。
2.OracleOraDb11g_homelTNSListener服务是监听器服务。要远程连接数据库服务器，必须连接监听进程，该服务只有
在数据库需要远程访问时候才需要。(PL/SQL等第三⽅⼯具连接就相当于远程连接)。
3.OracleDBConsoleSID服务是数据库控制台服务，是采⽤浏览器⽅式打开的，⽤于使⽤Oracle企业管理器的程序。如
果进⼊ 基于Web形式的企业管理(EM)控制平台，必须要启动OracleDBConsoleSID服务。
连接数据库：
Sys和System⽤户都是Oracle的系统⽤户，Scott⽤户是Oracle数据库的⼀个测试账户，⾥男包含⼀些测试例表。每
个⽤户下所有的对象称为模式对象。
1.⽤SQL Plus⼯具登录：
1)cmd->sqlplus->⽤户名：->密码:
2)直接打开程序SQL Plus->⽤户名：->密码
3)在输⼊⽤户名处->⽤户名/密码@⽹络服务名
4)命令窗⼝：sqlplus /nolog 这样就只是打开了sqlplus⽽并没有连接数据库，之后要连接数据库得继续敲：
connect ⽤户名/密码@数据库名;
2.PL/SQL Developer⼯具：
1)⽤户名：输⼊System.
2)⼝令：输⼊对应密码。
3)数据库：输⼊已经配置好的⽹络服务名,如orclDB。
4)连接为:Normal普通⽤户;sysOper数据库操作员;sysDBA数据库管理员。
3.Java连接Oracle:
driver: Driver
url: jdbc:oracle:thin:@localhost:1521:数据库名
DUAL伪表，在Oracle的查询语句中select和from关键字⼀定有值，但在某些情况下不需要from,如查询常量或函数，为
了满⾜要求，Oracle使⽤DUAL伪表来实现要求。
如: select sysdate from dual;--查询出当前系统⽇期和时间
Oracle数据类型：
1.字符数据类型：
1)char数据类型。当需要固定长度字符串时，使⽤char。长度1~2000字节。如果没有指定⼤⼩，默认1字节。如果
⽤户输⼊的值⼩于指定的⼤⼩，系统⽤空格填充。如果⼤于，报错。
2)varchar2数据类型，⽀持可变长度的字符串。⼤⼩为1~4000字节。如果⽤户输⼊的值⼩于指定的⼤⼩，不⽤填充
，varchar2数据类型可以节省磁盘空间。如果varchar2想根据定义的长度存储相应的汉字个数，可以声明为
如：varchar2(10 char)
3)nchar数据类型，即国家字符集，使⽤⽅法和char相同。⽤来存储Unicode字符集类型，即双字节字符数据。最
⼩单位1字符。
4)nvarchar2与nchar类似，只是可变长度的。
2.数值数据类型number：
number数据类型可以存储正数、负数、零、定点数、精度为38信的浮点数。
语法：number(p,s) --如果不写p,s内容，是否是没有限制?
p 为精度，表⽰数字的有效位数，在1~38之间，从左边第⼀个不为0的数算起，⼩数点和负号不计⼊有效位数。
s 为范围，表⽰⼩数右边数字的位数，在-84~+127之间。
规则：⾸先精确到⼩数中右边s位，并四舍五⼊。如果精确后值的有效位数<=p，则正确;否则报错。
3.⽇期时间数据类型：⽤于存储⽇期值和时间值
1)date数据类型，⽤于存储表中的⽇期和时间数据。使⽤7字节固定长度，每个字节分别存储世纪、年、⽉、⽇、⼩
时、分和秒。sysdate函数的功能是返回当前的⽇期和时间。
2)timestamp数据类型，⽤于存储⽇期的年、⽉、⽇以及时间的⼩时、分和秒。秒值精确到⼩数⼩后6位，包含时区
信息。systimestamp函数的功能是返回当前⽇期、时间和时区。
改变⽇期格式：
Oracle中的SQL窗⼝：alter session set nls date format=’YYYY-MM-DD HH24:MI:SS’;//只对当前窗⼝有效。
环境变量：键：nls_date_format
值:YYYY-MM-DD HH24:MISS
4.游标类型，SYS_REFCURSOR类型是Oracle提供的系统游标类型。
如下，过程返回⼀个游标类型：
CREATE OR REPLACE PROCEDURE get_sals(
cur_salary OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN our_salary FOR
SELECT empno, sal FROM employee;
END;
LOB
数据类型：
LOB⼜称为“⼤对象”数据类型。我叫存储4GB的⾮结构化信息，如声⾳剪辑和视频剪辑。LOB允许对数据进⾏⾼效、随
机、分段的访问。⼀个表中可以有多个列被定义为LOB数据类型。可以通过PL/SQL中提供的程序包DBMS_LOB修改。
(Character LOB,字符LOB)存储⼤量字符数据。可以存储单字节字符数据和多字节字符数据，主要⽤于存储⾮结
构化的XML多档。如新闻、内容介绍等含⼤量⽂字内容的⽂档。
(Binary LOB,⼆进制LOB)可以存储较⼤的⼆进制对象，如图形、视频剪辑和声⾳剪辑等。
(Binary File，⼆进制⽂件)能够将⼆进制⽂件存储在数据库外部的操作系统⽂件中。BFILE列存储⼀个BFILE
定位器，指向位于服务器⽂件系统上的⼆进制⽂件。最⼤4GB。
数据类型⽤于存储⼤的nchar字符数据。⽀持固定宽度字符和可变宽度字符(Unicode字符数据)。使⽤⽅法同
CLOB类似。
早期使⽤Long数据类型存储⼤数据。
Oracle中的伪列：
1.rouid，数据库中每⼀⾏都有⼀个⾏地址，rowid伪列返回该⾏地址。rowId值可以唯⼀标识数据库中的⼀⾏。
2.rounum,返回⼀个数值代表⾏的次序。通过使⽤rownum，⽤户可以限制查询返回的⾏数。
rownum对于等于某值的查询条件，可以使⽤rownum=1作为条件，但是不能等于⼤于1的值。
rownum对于⼤于某值的查询条件，可以使⽤rownum>1，但是不能⼤于1以上的值。
rownum对于⼩于某值的查询条件，可以rownum<⼤于1的整数。
例: select * from(
select a.*,rownum rn from ( --这⾥设置了rownum的列别名，这样就可以在外层查询⽤between and语句了
select * from employee order by sal desc
) a
)
where rn between 5 and 9;
SQL语⾔分类：
数据定义语⾔(DDL Data Definition)：create创建、alter更改、truncate截断、drop删除
数据操纵语⾔(DML Data Manipulation Language)：insert插⼊、select选择、delete删除、update更新
事务控制语⾔(TCL Transaction Control Language)：commit提交、savepoint保存点、rollback回滚
数据控制语⾔(DCL Data Control Language)：grant授予、revoke回收
数据定义语⾔DDL：
1.create table命令：
create table [schema.]table_name
(
column_name datatype,
column_name datetype,
....
)
schema表⽰对象的所有者，即模式的名称。如果⽤户在⾃⼰的模式中创建表，则可以不指定所有者名称。
table表⽰表的名称。
column表⽰列的名称。
datatype表⽰该列的数据类型及其宽度。
表名命名规则：
1)表名⾸字符应该为字母。
2)不能使⽤Oracle保留字来为表命名。
3)表各的最⼤长度为30个字符。
4)同⼀⽤户模式下的不同表不能具有相同的名称。
5)可以使⽤下划线、数字、字母，但不能使⽤空格和单引号。
Oracel和SQL Server数据库对象表之间的差异：
列数：Oracle,254; SQL Server,1024
⾏⼤⼩：Oracle没有限制;SQL Server,8060字节，加16字节指向每个text或image列。
最⼤⾏数：Oracle没有限制;SQL Server没有限制
表命名规则：Oracle,[schema.]table_name; SQL Server,[[[server.]database.]owner.]table_name
2.truncate table命令：
可以只删除表中的记录⽽不删除表结构，删除表中的所有⾏⽽不记录⽇志。
truncate table
数据操纵语⾔DML：(insert,select,update,delete)
1.选择⽆重复的⾏，在select命令中包含distinct⼦句。
select distinct stuName,stuAge from stuInfo;
2.使⽤列别名，列别名不会影响列的实际名称。列别名位于列表达式后⾯。
select stuName as “姓 名”,stuAge as 年龄,stuNo 编号 from stuInfo;
如果列别名中指定有特殊字符（如空格）的列标题使⽤双引号括起来。
3.利⽤现有表创建新表。
语法： create table
as
select {* | column(s)} from
复制表结构和记录：
create table newStuInfo
as
select * from stuInfo;
复制表指定列和记录：
create table newStuInfo
as
select stuName,stuAge,stuNo from stuInfo;
只复制表结构：
create table newStuInfo
as
select * from stuInfo where 1=2;
4.查看表中⾏数：
select count(1) from stuInfo; --1⽐*号效率较⾼
5.取出stuName,stuAge列不存在重复的数据记录：
select stuName,stuAge from stuInfo
group by stuName,stuAge
having(count(stuName||stuAge));//“||”在这⾥是连接操作符，类似于“+”，意思为将两部分内容连接在
⼀起，因为count()⾥⾯只能有⼀个列，所以连接起来。
6.删除stuName、stuAge列重复的⾏（相同数据只保留⼀⾏）
delete from stuInfo where rowid not in(
select max(rowid) from stuInfo group by stuName,stuAge
having (count(stuName||stuAGe)>1)
union
select max(rowid) from stuInfo group by stuName,stuAge
having (count(stuName||stuAge)=1)
);
7.根据当前⽤户所有数据量>100万的表的信息
2)rollback:回滚事务，即取消对数据库所做的作何修改。
3)savepoint
4)rollback to savepoint
开启事务：在Oracle中，上⼀次事务结束以后，数据第⼀次被修改时⾃动开启。
结束事务： ①数据被提交:发出commit命令;执⾏DDL或DCL语句后，当前事务⾃动commit;与Oracle分离。
②数据被撤销：发出rollback命令;服务器进程异常结束;DBA停⽌会话。
例: insert into dept values (10,’ACCOUNTING’,’NEW YORK’);
commit;
savepoint a;//a只是⼀个名称
insert into dept values (20,’SALES’,’NEW YORK’);
rollback to savepoint a;
rollback;--结果只有第⼀条记录插⼊成功
数据控制语⾔DCL：
数据控制语⾔为⽤户提供权限控制命令。数据库对象（如表）的所有者对这些对象拥有控制权限。所有者可以根据⾃⼰
的意愿决定其他⽤户如何访问对象，授予其他⽤户权限（insert,select,update,delete,....）
SQL操作符：
1.算术操作符。算术表达式由number数据类型的列名、数值常量和连接它们的算术操作符组成。
+(加)、-(减)、*(乘)、/(除)。
2.⽐较操作符：=、!=、<、<=、>、>=、between.. and 、in、not in、like、is null、is not null。
Oracle中的between-and语句，包含前也包含后。如：rn between 5 and 9;结果是5,6,7,8,9
3.逻辑操作符：and , or , not。
4.集合操作符，将多个查询的结果组合成⼀个结果集。
规则： 1）通过集合操作符连接的条个查询具有相同的列数，且对应列的数据类型必须兼容。
2）这种查询不应含有long类型的列。列标题来⾃第⼀个select语句。
union (并集)操作符返回两个查询选定的所有不重复的⾏。使⽤order by ⼦句时，必须放在最后⼀个select后。
select empno from employee
union
select rempno from retireEmp
order by empno;
union all (并集all)操作符合并两个查询选定的所有⾏。包括重复的⾏。
intersect (交集)操作符返回两个查询都有的⾏。
select empno from employee
intersect
select rempno from retireEmp;
minus (减集)操作符只返回由第⼀个查询选定⽽未被第⼆个查询选定的⾏，即在第⼀个查询结果中排除在第⼆个查询
结果中出现的⾏。
select empno from employee
minus
select rempno from retireEmp;
5.连接操作符(||),⽤于将两个或多个字符串合并成⼀个字符串，或者将⼀个字符串与⼀个数值合并在⼀起。
select job||’ ‘||ename from employee;--输出的时候将岗位和姓名连接在⼀起输出。
SQL函数：⼤致分为单⾏函数、聚合函数、分析函数。
单⾏函数：字符函数、⽇期函数、数字函数、转换函数及其他函数。
聚合函数(Aggregate Function)也称为分组函数是基于数据库表的多⾏进⾏运算，返回⼀个结果。
分析函数是对⼀个查询结果中的每个分组进⾏运算，但每个分组对应的结果可以有多个。
字符函数：操作结果可能是字符数据类型，也可能是数字类型。
函数说明输⼊结果
initcap(char)⾸字母⼤写initcap(‘hello’)Hello
lower(char)转换为⼩写lower(‘FUN’)fun
upper(char)转换为⼤写upper(‘sun’)SUN
ltrim(char,set)左剪裁ltrim(‘xyzadams’,’xyz’)zdams
rtrim(char,set)右剪裁rtaim(‘xyzadams’,’ams’)xyzad
translate(char,from,to)按字符翻译translate(‘Jack’,’abcd’,’1234’)J13k
replace(char,search_str,
replace_str)
instr(char,substr[.pos])查找⼦串位置instr(‘worldwide’,’d’)5
substr(char,pos,len)取⼦字符串,下标从1开始substr(‘abcdefg’,3,2)cd
concat(char1,char2)连接字符串concat(‘Hello’,’world’)Helloworld
数字函数，接受数字输⼊并返回数字作为输出结果，返回的值可以精确到⼩数点后38位。
函数说明输⼊结果
abs(n)取绝对值abs(-15)15
ceil(n)向上取整ceil(44.178)45
floor(n)向下取整floor(44.99)44
round(m,n)四舍五⼊round(100.256,2)100.26
trunc(m,n)截断trunk(100.256,2)100.25(⼩数后⼏位?)
sin(n)正弦sin(1.571).999999979
cos(n)余弦cos(0)1
power(m,n)m的n次幂power(4,2)16
sqrt(n)平⽅根sqrt(4)2
sign(n)取符号sign(-32)-1
mod(m,n)取m%n的结果mod(10,3)1 (Oracle中没有%运算符)
⽇期函数，对⽇期值进⾏运算，根据函数的⽤途产⽣⽇期数据类型或数值类型的结果。
函数功能实例结果
sysdate返回当前⽇期和时间select sysdate from dual;当前⽇期和时间
systimestamp返回当前⽇期、时间、时区select systimestamp from
next_day返回指定⽇期后的星期对应的新⽇期next_day(‘06-2⽉-3’,’星期⼀’)10-2⽉03
last_day返回指定⽇期所在⽉的最后⼀天last_day(‘06-2⽉-03’)28-2⽉-03
round按指定格式对⽇期进⾏四舍五⼊round(to_date(‘13-2⽉-03’),’YEAR’)01-1⽉-03
对年份四舍五⼊，⽇期>6⽉30⽇，则
年份+1;
add_months(‘06-2⽉-03’,1)06-1⽉-03
round(to_date(‘13-2⽉-03’),’MONTH’)01-2⽉-03
round(to_date(‘13-2⽉-03’),’DAY’)16-2⽉-03
trunc(to_date(‘06-2⽉-03’),’YEAR’)01-1⽉-03trunc对⽇期按指定⽅式进⾏截断
trunc(to_date(‘06-2⽉-03’),’MONTH’)01-2⽉-03
trunc(to_date(‘06-2⽉-03’),’DAY’)02-2⽉-03
2014extract(year extract(year from sysdate)
from date)day,
返回date中的年份，也可以是month
转换函数，将值从⼀种数据类型转换为另⼀种数据类型。Oracle中没有convert函数。
函数功能实例结果
to_char转换成指定字符串类型to_char(123.45,’$9999.9’) (只有$符号可以)$123.5
to_date转换成指定⽇期类型to_date(‘1980-01-01’,’yyyy-mm-dd’)01-1⽉-80
to_number转换成指定数值类型to_number(‘1234.5’)数字：1234.5
to_char()的语法为:
to_char(d|n[,fmt]);--其中d是⽇期，n是数字，fmt是指定⽇期或数字的格式 。转换成的是varchar2数据类型
例: select to_char(sysdate,’YYYY”年”fmMM”⽉”fmDD”⽇” HH24:MI:SS’) from dual;
--使⽤了填充模式”fm”格式掩码来避免空格填充和数字零填充
--格式中的⼩数有多少位，结果就会四舍五⼊进多少位。
to_date()的语法为：
to_date(char[,fmt]);--将char或varchar数据类型转换为⽇期数据类型。fmt指定字符的形式。
--这⾥的fmt是指定Oracle服务器该如果解析这个char字符串的格式，返回的结果统⼀是：yyyy-mm-dd;
select to_date(‘2013-07-18’,’yyyy-mm-dd’) from dual;--结果：18-7⽉-13
--这Oracle中mm是⽉份，mi是分种数
to_number()函数将包含数字的字符串转换为number数据类型。通常不必这样做，因为Oracle可以对数字字符串进⾏隐
式转换。
select sqrt(to_number(‘100’)) from dual;--结果：10
其他函数，除去已介绍函数还不其他⼀些单⾏函数，在此称为其他函数。
函数功能
nvl(exp1,exp2)如果exp1的值为null,则返回exp2的值，否则返回exp1的值
nvl2(exp1,exp2,exp3)如果exp1的值为null,则返回exp2的值，否则返回exp3的值
decode(value,if1,then1
,if2,then2,...,else
例: select ename,
sal+nvl(comm,0) sal1,
nvl2(comm,sal+comm,sal) sal2,
decode(to_char(hiredate,'MM'),'01','⼀⽉','02','⼆⽉','03','三⽉','04','四⽉','05'
,'五⽉','06','六⽉','下半年') mon
如果value的值为if1，则返回then1的值;如果value的值为if2，则返回then2的值
，.... 否则返回else的值
from employee;
聚合函数：能基于列进⾏计算，将多个直合并为⼀个值。对⼀组值进⾏计算，并返回计算后的值。
使⽤聚合函数进⾏统计值时，将忽略NULL值，但不忽略重复项。
()，返回表达式中所有数值的总和，只能⽤于数字列，只能返回⼀个数值，不能够直接与可能返回多⾏的列⼀起 使⽤来进⾏查询。
例：select sun(score) from result where studentid=33 --查询学号为33的学⽣的成绩总和
(),返回表达式中所有数值的平均值，只能⽤于数值列。
例：select avg(score) from result where score>=60 --查询及格的平均值。
()和min():max()返回表达式中的最⼤值，min()返回表达式中的最⼩值，它们都可以⽤于数字型，字符型以及 ⽇期/时间类型的列。
例：select avg(score),max(score),min(score) from result where score>=60
--查询及格线上的平均分、最⾼分、最低分
(),返回提供的组成或记录龄中的计数。可以⽤于数字和字符类型的列，也可以使⽤星号’*’作为count()的 表达式。
例：select count(*) from score where score>=60 --查询及格⼈数的语句
也可以使⽤整数类型的值当作表达式，如：
select count(1000) from score where score>=60 --结果将和上⾯的⼀样
⼩提⽰：count(distinct 表达式),遇到重复数据，只记录⼀次
分析函数：Oracle从8.1.6版本开始提供分析函数。分析函数是对⼀组查询结果进⾏运算，然后获⾏结果。与聚合函数的区别在于返回多⾏，
聚合函数每组返回⼀⾏。⽤于对分组后组内进⾏排序。
语法：
函数名([参数]) over ([分区⼦句][排序⼦句]);--分区⼦句(partition by)表⽰将查询结果分为不同的组，功能
类似于group by语句。默认将所有结果作为⼀个分组。排序⼦句(order by)默认是asc升序。
rank、dense_rank、row_number函数⽤于为每条记录产⽣⼀个从1开始⾄N的⾃然数，N的值可能⼩于记录的总数。这
3个函数⽤于解决排名问题。
1)row_number函数返回⼀个唯⼀的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。
2)dense_rank函数返回⼀个唯⼀的值，当遇到相同数据时，所有相同数据的排名都是⼀样的。
3)rank函数返回⼀个唯⼀的值，当遇到相同的数据时，所有相同数据的排名是⼀样的，同时会在最后⼀条相同记录和
下⼀条不同记录的排名之间空出排名。
例：select ename,deptno,sal,
rank() over (partition by deptno order by sal desc) rank,
dense_rank() over (partition by deptno order by sal desc) "dense_rank",
row_number() over (partition by deptno order by sal desc) "row_number"
from employee;
发布评论