Oracle数据库基础

Oracle的基本概念包括：数据库、全局数据库名、数据库实例、表空间、数据文件、控制文件、日志文件、模式和模式对象。
1.其中数据库不是通常情况下所说的数据库，而是Oracle的一个专业名词。它是磁盘上存储数据的集合，在物理上表现为数据文件、日志文件和控制文件等，在逻辑上以表空间形式存在。使用时，必须首先创建数据库，然后才能使用Oracle。可以在安装Oracle软件的同时创建数据库，也可以在安装完之后单独创建数据库。
2.全局数据库名适用于区分一个数据库的标识，在安装数据库、创建新数据库、创建控制文件、修改数据库结构、利用RMAN备份时都需要使用。它由数据库名称和域名构成，类似网络中的域名，使数据库的命名在整个网络中唯一。如果在一个网络中有两个数据库，数据库都是orcl，若这两个数据库之间要建立连接，则必须使用不同的域名加以区分。例如：“orcl.prd.com”其中prd.com为域名。
3.数据库实例
每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中的数据。如果把数据库简单地理解为硬盘上的文件，具有永久性，则数据实例就是通过内存共享运行状态的一组服务器后台进程。
4.表空间
每个Oracle数据库都是由若干个表空间构成，用户在数据库中建立的所有内容都被存储到表空间中。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个表空间。与数据文件这种物理结构相比，表空间属于数据库的逻辑结构。其中每个数据库都有一个名为SYSTEM的表空间，即系统表空间，还会有TEMP、USER等表空间，这些都是创建数据库时自动创建的，管理员可以创建自定义的表空间并分配给指定用户，也可以为表空间增加或删除数据文件。
5.数据文件
在Oracle中数据文件的拓展名是.dbf，用于存储数据库数据的文件，如存储数据库表中的记录、索引、存储过程、视图、数据字典定义等。
一个数据文件中可能存储很多个表的数据，同时一个表中的数据可能放在多个数据文件中。
6.控制文件
控制文件的拓展名是.ctl，是一个二进制文件。控制文件中存储的信息很多，其中包括数据文件和日志文件的名称和位置。控制文件是数据库启动及运行所必需的文件。当Oracle读写数据时，要根据控制文件的信息查找数据文件。因此控制文件是比较重要，所以一个数据库至少要有一个以上的控制文件，Oracle 11g默认包含三个控制文件。各个控制文件的内容相同，可以避免因为一个控制文件的损坏而无法启动数据库。
7.日志文件
日志文件的拓展名是.log，它记录了数据的所有更改信息，并提供了一种数据恢复机制，确保在系统崩溃或其他意外出现后重新恢复数据库。
8.模式和模式对象
模式是数据库对象的集合，Oracle会为每一个数据库用户创建一个模式，此模式为当前用户所拥有，和用户具有相同的名称。
安装数据库可以点击此链接https://blog.csdn.net/zzybbh/article/details/83818689
在Windows下启动数据库
Oracle中常用的三个服务。
1.OracleServiceSID服务是Oracle数据库服务，SID是全局数据库名
2.OracleOraDb11g_home1TNSListener服务是监听器服务。
3.OracleDBConsoleSID服务是数据库控制台服务。
连接Oracle数据库的几种方式
1.使用SQL*Plus工具
使用cmd界面进行操作，目前不使用
2.使用PL/SQL Developer工具
此工具是第三方客户端工具，此工具使用比较方便。
Oracle数据类型
1.字符数据类型
（1）CHAR数据类型
当需要固定长度的字符串时，使用char数据类型，它的长度可以是1~2000字节。其默认占一个字节，如果用户输入的值小于指定的长度，则数据库用空格填充至固定长度，此操作会浪费内存空间，如果输入的值大于，则数据库会返回报错。
（2）varchar2数据类型，可变长度的字符串它的长度为1~4000字节，与char类型相比，使用varchar2数据类型可以节省内存空间。
3.nchar数据类型
nchar，即国家字符集，使用方法和char相同，如果开发的项目需要国际化，那么数据类型要选择nchar数据类型，其中nchar和char的区别在于nchar用来存储Unicode字符集类型，即双字节字符数据。就是当定义char和nchar的两个字段，字段长度为1，则当都存储一个中文字"的"时char无法存储，nchar则可以，因为char是存储固定长度的字节而nchar是存储固定长度的字符，我们应该知道一个字符占两个字节，中文汉字都是占两个字节的，既然char有它国际化类型，那么varchar也有它的国际化类型即nvarchar2.国际化的都是存储字符的，而普通的则是存储字节。
数值数据类型
number数据类型可以存储正数、负数、零、定点数和精度为38位的浮点数。该数据类型的格式为：number(p,s)
其中p为规定的有效数字的长度，如果数值超过这个范围则会报错，小于等于不会报错，s为精确到小数点右边位数，并进行四舍五入。
日期时间数据类型
1.date数据类型
2.timestamp数据类型
LOB数据类型
LOB称为大对象数据类型，LOB可以存储4GB的非结构化信息，如声音和视频等，LOB允许对数据进行高效、随机、分段的访问。LOB可以是外部的，也可以是内部的，这取决于它相对于数据库的位置。
Oracle中的伪例
伪例就像Oracle中的一个表列，但实际上它并未存储在表中。伪例可以从表中查询，但是不能插入、更新或删除他们的值.
1.ROWID
数据库中的每行都有一个行地址，ROWID伪例返回改行地址，可以使用ROWID值来定位表中的一行。通常情况下，ROWID值可以唯一地标识数据库的一行。
ROWID伪例由以下重要的用途：
1.能以最快的方式访问表中的一行。
2.能显示表的行是如何存储的。
3.可以作为表中行的唯一标识。
每创建一个表的每一行会自动生成ROWID，我们可以通过SELECT语句来查看ROWID的值
语句：
select ROWID,eName from emp 可以查看emp表中所有行的ROWID。
2.ROWNUM
此关键字相当于MySql的分页关键字limit，只不过是用方式不同，它的操作方法如下：
ROWNUM是查询返回的结果集中行的序号,可以使用它来限制查询返回的行数

利用ROWNUM分页 5到10行 
     select * from (select rownum rn from test where rownum <=10) t
     where t.rn >=5
     **其他函数具体操作如下：**
     
     精确度函数
round(列名,位数)精确到小数点后几位,不写默认0,-1为精确到10位

字符截取函数(Oracle char类型指定长度后,长度不够会用空格补充)
substr(列名,下标,长度)
可以从0或1开始,一般认为从1开始,然后截取长度
如果下标为负数,开始的位置就从后往前,然后截取长度

字符串去空格
trim(列名)

当前系统时间
select sysdate+1 from dual
sysdate为系统时间,每加1为加一天
小时可以用分数表示如1/4六小时

字符串改日期格式
select to_date(‘1986-12-12’,‘yyyy-mm-dd’) from dual

数字转字符串类型
select to_char(1122) from dual
还可以设定格式如to_char(‘111111’,‘999,999’)
得到如下格式111,111

时间转字符串类型
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’)
24小时的时分秒
注意 oracle 里面分钟为mi而不是如java用大小写区分

字符串转数字类型
select to_number(‘112’) from dual

nvl()函数
select nvl(列名,‘自设值’)
如果此列为空,就返回自设值,否则不变

nvl2()函数
select nvl2(列名,‘操作’,'自设值 ')
如果此列为空,返回自设值
如果不为空,就对该列执行相应操作后返回

decode函数
decode(列名,条件1,操作/值1,条件2,操作/值2, … ,如果都不满足的 操作/值 )
select num,decode(num,null,0,25,2500,35,3500,num) from test

查询一列,首先放列名,然后条件和满足条件对应的操作或者直接返回值一一对应
最后一个都好后面写如果前面所有条件都不满足时,返回的操作或者值

聚合函数(从一组数据中计算数据)
也就是以前学过的 最大值 max(),最小值 min(),计数 count(),求和 sum(),平均数 avg()
select sum(num),avg(num),max(num),min(num),count(0) from test

分组 group by
group by 分组列 就好

过滤 having
having 聚合函数条件 可以过滤掉不符合条件的数据

分析函数(不常用)–根据一组行来计算聚合值 主要用于排序

RANK()
select name,type,rank() over( [partition by type] order by num) rank from test
查询 姓名,类型,排序rank() over(分组 by 类型 根据 num排序) 别名
如果不填写分组,会把全表的拿来排序

ROW_NUMBER() ,DENSE_RANK()语法和RANK相同

不同:
比如有5个人的分数分别是100分,98分,98分,96分,94分
RANK()的排序结果为 1-100,2-98,2-98,4-96,5-94
DENSE_RANK()的排序结果为 1-100,2-98,2-98,3-96,4-94
ROW_NUMBER()的排序结果为 1-100,2-98,3-98,4-96,5-94

select name,type,
rank() over(partition by type order by num) rank,
dense_rank() over(partition by type order by num) dense_rank,
row_number() over(partition by type order by num) row_number
from test

联合查询:
INTERSECT:返回两个查询共有的记录

UNION/UNION ALL:返回各个查询的所有记录

区别:如果 两个记录都有5行 有3行重复, UNION ALL 返回10行
UNION 只返回7行(去重复)

MINUS:返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

如:这样的一笔数据
id name num type
1 黄勇琳 25 1
2 郭峰 35 1
3 陈军峰 45 2
4 李杰 55 3

select * from test UNION select * from test where type=1
得到
id name num type
1 黄勇琳 25 1
2 郭峰 35 1
3 陈军峰 45 2
4 李杰 55 3
(去掉了重复)

select * from test UNION ALL select * from test where type=1
得到
id name num type
1 黄勇琳 25 1
2 郭峰 35 1
3 陈军峰 45 2
4 李杰 55 3
1 黄勇琳 25 1
2 郭峰 35 1
(重复的也会显示)

SELECT * FROM test INTERSECT select * from test where type=1
得到
id name num type
1 黄勇琳 25 1
2 郭峰 35 1
(只显示重复的)

SELECT * FROM test MINUS select * from test where type=1
得到
id name num type
3 陈军峰 45 2
4 李杰 55 3
(显示前者有,后者没有的)

select * from test where type=1 MINUS SELECT * FROM test
得到
id name num type
(因为后者数据更多)

伪列就是实际上不存在的,但是可以查询的列 ROWID ROWNUM
ROWID是表中行的物理位置(ORACLE的地址,而不是电脑磁盘里的),用于唯一标识一行

ROWNUM是查询返回的结果集中行的序号,可以使用它来限制查询返回的行数

利用ROWNUM分页 5到10行 2
select * from (select rownum rn from test where rownum <=10) t
where t.rn >=5