oracle的概念理解

orale是属于关系型数据库中的一种，跟mysql、sqlserver一样，是以关系模型建立的数据库，以表的行和列作为关系，对数据进行操作。
这里介绍一个oracle的集群模式(rac)(real application cluster),涉及到两个概念
数据库和数据库实例
什么是数据库和数据库实例呢?
我们可以把数据库看作是一个物理结构,存在磁盘上的文件(oracle数据库结构目录会待会介绍),然后实例就是加载到内存中的数据库，也就意味着一个数据库可以有多个实例，这也叫做集群，集群的概念到现在也已经是很火了。
集群的好处：(总的来说)
1.load balance(负载均衡)：
在多个实例中，对来自客户端的请求将其平均分给多个实例
2.fail over(失败迁移):
当某个实例发生故障的时候，将该实例的任务分给其他实例

oracle的目录结构

1.log日志
2.dbf(ora)数据文件
是orcale数据库存储数据的物理单位,orcale的数据是存在表空间中的，真正是存在一个或者多个数据文件中，表空间可以由一个或多个数据文件构成，一个数据文件只能存在一个表空间中,一但某个数据文件被加入到某个表空间，就不能直接将数据文件删除，只有将表空间删除后才可以将该数据文件删除.
表空间:
是oracle数据库中数据文件的逻辑映射，一个oracle数据库在逻辑上被划分为若干个表空间，表空间包含了在逻辑上相关联的一组结构，每个数据库至少有一个表空间(称为system表空间)
用户：
oracle是在用户下建表的，相同表名的表可以存放在不同用户下的同一表空间中，一个表空间可以有一个或多个用户，表中的数据是由用户放到表空间中去的；而用户是在实例中创建的，相同的用户名可以存放在不同的实例中.

3.ctl控制文件

常见命名

1.当前用户
show user;
2.查看当前用户下的表
select * from tab;
3.查看某个表的详情
desc emp;
4.清除命令窗口中的文字
1)在windows中是host cls
2)在linux中是host clear
5.查看某个表中的数据
select * from 表名;
6.查看和设置当前的行宽(一行上面可以显示多少个字符)
show linesize;
set linesize 150;
7.设置列宽
col(column) 列名 for(format) a9;//该列名为字符串且字符长度为9,如果是字符串就以a开头
col(column) 列名 for(format) 99999;//该列名下的数据为数字，长度为6，以9开始
8.查看和设置当前页的个数
show pagesize;
set pagesize 20;
注意:
列名不区分大小写
9.当命名行sql语句写错了的时候怎么修改
1)通过c --change命令对某行的某个位置进行修改
先选定某行 2(选定第二行)
再修改某个词语
c /form/from --将第二行中的form修改成from
10.执行上一条sql语句
/
11.sql语句中的NULL
1).空值永远不等于空值
select * from emp where comm =null;//查不出来,应该是is null

2).含有空值的表达式永远为空值

解决方案:使用oracle中的滤空函数nvl(a,b)或者nvl2，为空就等于后面那个值，不为空就等于前面那个值

12.设置别名
1)通过as ""设置
2)通过edit(ed)命令，然后打开默认的编辑器，在默认的编辑器中进行修改

注意:
使用as "年收入",直接使用"年收入"和直接命名(年收入)三者之间的区别
as和直接使用"年收入"没有区别，直接命名(年收入)不能加入关键字和特殊的符号及纯数字,如果要加就必须加双引号
13.去掉重复记录
select distinct(作用于后面的所有列,只要后面所有的列不是重复的就不会被去掉)

连接符||相当于java中的+

concat跟表没得关系的
mysql中的连接函数concat("str1","str2");//将两个str连接起来
oracle里面使用concat后面必须跟from；都遵循sql99标准
但是没得from后面那个表嘛，所以oracle给我们提供了一个表dual(伪表，仅仅是为了满足sql99语法)；

14.spool命令：是oracle提供的一个文本录屏功能
1)spool d:基本查询.txt//开始录屏
2)spool off:录屏结束
14.过滤和排序

条件查询: where condition1 condition2

注意:

oracle的条件判断是从右往左的,因此当以and连接多个condition时尽量将可能为假的值放置在右边,or则将尽量为真的值放置在右边

排序:order by 列名 asc|desc

注意:

在oracle中null值最大，如果要将null放置在最后，需要在order by 列名 desc 后面加上nulls last,这是对sql99的一个补充
desc|asc只对挨着最近的一个列有效
后面可以加列名、表达式、序号、别名、序号(序号是从1开始的,数组才是从0开始的)
oracle中查询条件的值是区分大小写的

15.修改日期格式

查看数据字典
select * from v$nls_parameters;
修改日期格式
alter sesson(当前会话有效)|system(都有效) set NLS_DATE_FORMAT='yyyy-mm-dd'

16.between and 是包含边界的

字符串

在数据库中字符串可以是select列表中的一个字符，数字，日期
日期和字符只能在单引号中出现，双引号表示列的别名,对于字符串大小写敏感，对日期格式敏感,日期默认格式为DD-MONTH-YY;mysql是不区分字符串的大小写的
每当返回一行时，字符串被输出一次

sql和sqlplus

sqlplus是oracle给我们提供的命令行工具，在命令行工具中可以执行sql语句;
sql:

一种语言
ANSI标准
关键字不能缩写
使用语句控制数据库中的表的订阅信息和表中的数据

sqlplus：

一种环境
oracle的特性之一
关键字可以缩写
命令不能改变数据库中的数据的值
集中运行

isqlplus(sqlplus的网页版):

表述表结构
编辑sql语句
执行sql语句
将sql报错在文件中并将sql语句执行结果保存在文件中
在保存的文件中执行语句
将文本文件装入sql*plus编辑窗口

服务

oraclelistener
isqlplus
相当于是一个服务器，端口是5560,负责5560的网页，默认使用普通用户，也可以通过配置使用管理员用户
jobscheduler
oracle任务调度器
console
监听的端口是1158,负责1158的网页，可以通过该网页对oracle数据库进行管理,用管理员登录

oracleListener

监听器，要登录到数据库中去，监听器必须开启，如果登录不了，就可能是监听器有问题，在window中可以通过命令lsnrctl status start stop对oracle监听器进行操作

sql语句的优化

1.*和列名，优先选择列名
2.condition1 and condition2:
sql中的where条件是从右往左判断的，在and时尽量把假的条件放在右边(只要一个为假就为假)；or时尽量把真的条件放置在右边(一真为真),可以提高效率
3.如果where和having的效果一样，优先where;where是先过滤再分组，而having是先分组了再过滤，明显前者要节约资源和时间些
4.多表查询和子查询，尽量使用多表查询
5.尽量不要使用集合运算(如union,随着表的数量增多,执行的时间会越长)

函数

单行函数

字符函数

大小写控制函数

lower(转小写)
upper转大写
initcap首字母大写

字符控制函数

concat(str1,str2):将字符连接起来
substr:求一个字符串的子字串,从第几个字符开始,连续多少个字符
length(字符数)/lengthb(字节数):字符串的长度
instr:一个字符串当中查找另一个字符串,找到了就返回字节位置,从1开始
lpad|rpad:左填充|右填充
lapd(str,字节数量,'要填充的字符串');注意:如果填充的字符串不够字节数量，就从左往右多次填充，直到字节数量达到规定的；填充的字符串超过字节数量，就从左往右填充直到字节刚刚达到规定的数量。
trim:去掉前后指定的字符
trim('截取集' from str) from dual;注意:截取集只能取一个字符
replace:替换
replace('目标字符串','目标字符串中要替换的字符串','替换的字符串')

数值函数

round():四舍五入,同时可以作为日期函数

trunc():截断,用法跟round一样
mod:求余

日期函数

months_between:计算两个日期相差的月数
select months_between('25-4月-19',sysdate) from dual;
add_months():向某个日期添加月份
select add_months(sysdate,12) from dual; $XTI{8O3EO8_YH8DD49_UY60.png$
next_day:指定日期的下一个日期
last_day:本月的最后一天
round:四舍五入
trunc:截断
用法跟round类似

转换函数

隐式类型转换:由oracle数据库自动转换
显示类型转换:

to_char(date,'format_model')

注意:格式中出现字符串时，需要加双引号将其包括起来 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

to_number
to_date

通用函数

可以是任意数据类型

nvl2(a,b,c) 当a=null的时候,返回c，否则返回b
nullif(a,b) 当a=b时返回null,否则返回a
coalesce(expr1,expr2,....) 从左往右，找到第一个不为空的值

条件表达式

在sql语句中使用if-then-else
实现方式:
case表达式:sql99的语法，比较繁琐

可以在when条件中加入范围判断
decode函数:oracle自己的语法,比较简介

多行函数

多行函数作用于一组数据，并对一组数据返回一个值，又称为组函数和分组函数
注意:组函数会忽略空值;nvl函数使分组函数无法忽略空值

avg
count
注意：count(*)和count(列名)不一样
max
min
sum
注意:在select列表中所有位包含在组函数中的列都应该包含在group by子句中,包含在group by字句中的列不必包含在select列表中

多个列的分组:
先按照第一个列分组,如果相同,再按照第二个列分组
where子句中不能使用组函数,可以在having中使用

group by语句的增强

group by rollup(deptno,job)相当于group by deptno,job+group deptno+没有group by;报表功能
break on dep

去掉报表中重复的列,并将不同的列之间空两行

break on deptno skip 2;//去掉重复的deptno,并将不同的列之间空两行

多表查询

外连接:
where条件
右外连接condition1(+)=condition2
左外连接condition1=condition2(+)
自连接
自连接不适合大表查询,使用层次查询

层次查询

connect by 上一层的员工=下一层的老板(prior empno = mgr) start with 起始条件(empno=xxx或者 mgr is null)(对树的遍历)
oracle提供伪列(level),遍历树的深度

思考题

排序后的表跟原来的表是否一样?

单行函数.docx
日期函数和条件表达式.txt
多表查询.txt

oracle基本使用和概念