大佬教程收集整理的这篇文章主要介绍了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,它记录了数据的所有更改信息,并提供了一种数据恢复机制,确保在系统崩溃或其他意外出现
后重新恢复数据库。在@R_618_10038@中,多个日志文件组之间循环使用。
8.模式和模式对象。模式是数据库对象(表、索引等,也称模式对象)的集合。
windows下Oracle数据库服务:
1.OracleserviceSID服务是Oracle数据库服务。此服务对应名为SID(系统标识符)的数据库实例创建的。必须启动。
2.OracLeoraDb11g_homelTNSListener服务是监听器服务。要远程连接数据库服务器,必须连接监听进程,该服务只有
在数据库需@R_801_8485@时候才需要。(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: Oracle.jdbc.driver.OracleDriver
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-R_868_11845@M-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修改。
1.CLOB(Character LOB,字符LOB)存储大量字符数据。可以存储单字节字符数据和多字节字符数据,主要用于存储非结
构化的XML多档。如新闻、内容介绍等含大量文字内容的文档。
2.bLOB(Binary LOB,二进制LOB)可以存储较大的二进制对象,如图形、视频剪辑和声音剪辑等。
3.bfile(Binary file,二进制文件)能够将二进制文件存储在数据库外部的操作系统文件中。Bfile列存储一个Bfile
定位器,指向位于服务器文件系统上的二进制文件。最大4GB。
4.NCLOB数据类型用于存储大的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
)
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 [@R_674_10906@ma.]table_name
(
column_name datatype,
column_name datetype,
....
)
@R_674_10906@ma表示对象的所有者,即模式的名称。如果用户在自己的模式中创建表,则可以不指定所有者名称。
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,[@R_674_10906@ma.]table_name; sql Server,[[[server.]database.]owner.]table_name
2.truncate table命令:
可以只删除表中的记录而不删除表结构,删除表中的所有行而不记录日志。
truncate table <table_name>;
数据操纵语言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 <newtable_name>
as
SELEct {* | column(s)} from <oldtable_name> [where <condition>];
复制表结构和记录:
as
复制表指定列和记录:
as
SELEct stuname,stuAge,stuNo from stuInfo;
只复制表结构:
as
SELEct * from stuInfo where 1=2;
4.查看表中行数:
SELEct count(1) from stuInfo; --1比*号效率较高
5.取出stuname,stuAge列不存在重复的数据记录:
SELEct stuname,stuAge from stuInfo
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万的表的信息
SELEct tabke_name from user_all_tables a where a.num_rows>1000000;
--user_all_tables为系统提供的数据视图,使用者可以通过查询该视图获得当前用户表中描述。
事务控制语言TCL:
1)commit:提交事务,提事务中对数据库的修改进行永久保存。
2)rollBACk:回滚事务,即取消对数据库所做的作何修改。
3)savepoint <savepoint_name>:在事务中创建存储点。
4)rollBACk to savepoint <savepoint_name>:将事务回滚到存储点。即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,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后。
union
order by empno;
union all (并集all)操作符合并两个查询选定的所有行。包括重复的行。
intersect (交集)操作符返回两个查询都有的行。
intersect
@H_910_4@minus (减集)操作符只返回由第一个查询选定而未被第二个查询选定的行,即在第一个查询结果中排除在第二个查询结果中出现的行。
@H_910_4@minus5.连接操作符(||),用于将两个或多个字符串合并成一个字符串,或者将一个字符串与一个数值合并在一起。
SELEct job||’ ‘||ename from employee;--输出的时候将岗位和姓名连接在一起输出。
sql函数:大致分为单行函数、聚合函数、分析函数。
单行函数:字符函数、日期函数、数字函数、转换函数及其他函数。
聚合函数(Aggregate@R_997_3816@)也称为分组函数是基于数据库表的多行进行运算,返回一个结果。
分析函数是对一个查询结果中的每个分组进行运算,但每个分组对应的结果可以有多个。
字符函数:操作结果可能是字符数据类型,也可能是数字类型。
函数 |
说明 |
输入 |
结果 |
initcap(char) |
@H_450_529@ initcap(‘Hello’) |
Hello |
|
lower(char) |
@H_450_529@ lower(‘FUN’) |
fun |
|
upper(char) |
@H_450_529@ upper(‘sun’) |
SUN |
|
ltrim(char,set) |
@H_450_529@ ltrim(‘xyzadams’,’xyz’) |
zdams |
|
rtrim(char,set) |
@H_450_529@ rtaim(‘xyzadams’,’ams’) |
xyzad |
|
translate(char,from,to) |
@H_450_529@ translate(‘Jack’,’abcd’,’1234’) |
J13k |
|
replace(char,search_str, replace_str) |
@H_450_529@ replace(‘jack and jue’,’j’,’bl’) |
black and blue |
|
instr(char,substr[.pos]) |
@H_450_529@ instr(‘worlDWIDe’,’d’) |
5 |
|
substr(char,pos,len) |
@H_450_529@ substr(‘abcdefg’,3,2) |
cd |
|
concat(char1,char2) |
@H_450_529@ concat(‘Hello’,’world’) |
Helloworld |
数字函数,接受数字输入并返回数字作为输出结果,返回的值可以精确到小数点后38位。
函数 |
说明 |
输入 |
结果 |
abs(n) |
取绝对值 |
abs(-15) |
15 |
ceil(n) |
向上取整 |
ceil(44.178) |
45 |
floor(n) |
向下取整 |
floor(44.99) |
44 |
round(R_868_11845@,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) |
@H_910_4@m的n次幂 | power(4,2) |
16 |
sqrt(n) |
平方根 |
sqrt(4) |
2 |
sign(n) |
取符号 |
sign(-32) |
-1 |
@H_910_4@mod(m,n) | 取m%n的结果 |
@H_910_4@mod(10,3) | 1 (Oracle中没有%运算符) |
日期函数,对日期值进行运算,根据函数的用途产生日期数据类型或数值类型的结果。
函数 |
功能 |
实例 |
结果 |
返回当前日期和时间 |
当前日期和时间 |
||
systimestamp |
返回当前日期、时间、时区 |
dual; |
当前日期、时间 和时区 |
@H_910_4@months_between( prevdate,nextdatE) |
返回两个日期间的月份 |
@H_910_4@months_between(‘04-11月 -05’,’11-1月-01’) |
57.7741935 |
add_months |
返回把月份数加到日期上的新日期 |
add_months(‘06-2月-03’,1) add_months(‘06-2月-03’,1) |
06-3月-03 06-1月-03 |
next_day |
返回指定日期后的星期对应的新日期 |
next_day(‘06-2月-3’,’星期一’) |
10-2月03 |
last_day |
返回指定日期所在月的最后一天 |
last_day(‘06-2月-03’) |
28-2月-03 |
round |
按指定格式对日期进行四舍五入 对年份四舍五入,日期>6月30日,则 年份+1; |
round(to_date(‘13-2月-03’),’YEAR’) |
01-1月-03 01-2月-03 16-2月-03 |
trunc |
对日期按指定方式进行截断 |
trunc(to_date(‘06-2月-03’),’YEAR’) trunc(to_date(‘06-2月-03’),’MONTH’) trunc(to_date(‘06-2月-03’),’DAY’) |
01-1月-03 01-2月-03 02-2月-03 |
extract(year from datE) |
返回date中的年份,也可以是month day, |
extract(year from sysdate) |
2014 |
转换函数,将值从一种数据类型转换为另一种数据类型。Oracle中没有convert函数。
函数 |
功能 |
实例 |
结果 |
转换成指定字符串类型 |
TO_CHAR(123.45,’$9999.9’) (只有$符号可以) |
$123.5 |
|
to_date |
转换成指定日期类型 |
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-R_868_11845@m-dd’) from dual;--结果:18-7月-13
--这Oracle中R_868_11845@m是月份,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的值 |
如果value的值为if1,则返回then1的值;如果value的值为if2,则返回then2的值 ,.... 否则返回else的值 |
sal+nvl(comm,0) sal1,
NVL2(comm,sal+comm,sal) sal2,
decode(TO_CHAR(hiredate,@R_2_8772@,‘01‘,‘一月‘,‘02‘,‘二月‘,‘03‘,‘三月‘,‘04‘,‘四月‘,‘05‘
from employee;
聚合函数:能基于列进行计算,将多个直合并为一个值。对一组值进行计算,并返回计算后的值。
使用聚合函数进行统计值时,将忽略NulL值,但不忽略重复项。
1.sun(),返回表达式中所有数值的总和,只能用于数字列,只能返回一个数值,不能够直接与可能返回多行的列一起 使用来进行查询。
例:SELEct sun(score) from result where studentID=33 --查询学号为33的学生的成绩总和
2.avg(),返回表达式中所有数值的平均值,只能用于数值列。
例:SELEct avg(score) from result where score>=60 --查询及格的平均值。
3.max()和min():max()返回表达式中的最大值,min()返回表达式中的最小值,它们都可以用于数字型,字符型以及 日期/时间类型的列。
例:SELEct avg(score),max(score),min(score) from result where score>=60
--查询及格线上的平均分、最高分、最低分
4.count(),返回提供的组成或记录龄中的计数。可以用于数字和字符类型的列,也可以使用星号’*’作为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函数返回一个唯一的值,当遇到相同的数据时,所有相同数据的排名是一样的,同时会在最后一条相同记录和
下一条不同记录的排名之间空出排名。
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;
以上是大佬教程为你收集整理的Oracle数据库(一)全部内容,希望文章能够帮你解决Oracle数据库(一)所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。