Oracle笔记一

一、Oracle体系结构

1.oracle概念

Oracle是分布式关系型数据库。

2.oracle组成

数据库：Oracle只有一个数据库，是物理存储
实例：一个实例由一系列后台进程和内存结构组成，一个数据库有n个实例
表空间：一个数据库在逻辑上被划分为n个表空间，一个表空间由同一磁盘上多个数据文件组成，是逻辑映射
数据文件（dbf）:是数据库的物理存储单位，一个数据文件只属于一个表空间。当数据文件加入到表空间后，不能直接删除，只能先删除其所属的表空间才行。
用户：数据是通过用户放到表空间的，而这个表空间会随机的把数据放入一个或多个数据文件中。用户是实例创建的，不同的实例可以创建相同的用户。不同的用户可以在同一个表空间建立同一个名字的表。

对应关系：一个数据库有多个实例，一个数据库有多个表空间；
一个实例有多个用户，一个实例有多个表空间；
一个表空间有多个用户。一个表空间有多个表；
一个表的数据随机存储在一个或多个dbf/ora数据文件上；

类比：
数据库：比作共享办公区
实例：比作公司
用户：比作领导
表空间：比作部门
表：比作员工
数据文件：比作共享工位
数据：比作办公用品

对应类比：
一个共享办公区有多个公司，一个共享办公区有多个部门。
一个公司有多个领导，一个公司有多个部门。
一个部门有多个领导，一个部门有多个员工。
一个员工的办公用品可在一个或多个共享工位上。

二、Oracle基本查询

sql简介

DML：增删改数据库的内容，如insert,delete,update等。
DDL：创建或删除数据库的表，如create，drop，为表创建索引等。
DCL：控制用户或用户组的访问权限，如grant，revoke等。

语法格式

1.查询语法
select * from 表名;
select 列名 from 表名;
2.别名
select 列名 别名，列名 别名，…from 表名;
3.去重查询
select distinct * from 表名;
select distinct 部门 from 表名；
4.查询中四则运算
支持+，-，，/
select 列名，列名12 from 表名;

空值

空值是未指录入数据，即不等于0，不等于空格，可以用null表示。
空值不能和空值比较，也不相等。

连接符 ||

示例：查询雇员编号，姓名
查询展示：雇员编号是：1111，姓名是：
select ‘雇员编号是：’ || empno || ‘姓名是:’ || ename from 表名;

三、Oracle条件查询和排序

where条件

非空和空

支持null, not null,
select * from 表名 where 列名 is not null;
select * from 表名 where 列名 is null;

比较运算符

支持=,>,>=,<,<=,<>(不等于),!=(不等于），
oracle里的不等于有2种形式。
select * from 表名 where 列名>100;

其它比较运算符

支持between… and…, in(等于值列表的某个）, like(模糊查询), is null,
模糊匹配中，%可以匹配任意长度，_可以匹配一个值。
select * from 表名 where 列名 between ‘区间开始’ and ‘区间结束’;
select * from 表名 where 列名 in (100,200,300);
select * from 表名 where 列名 like “_B%”

逻辑运算符

支持and, or, not,
select * from 表名 where 列名 is not null and 列名 >1000;
select * from 表名 where 列名 is not null or 列名 <1000;
select * from 表名 where 列名 is null and not (列名 <1000);

排序

支持order by排序，有asc升序，desc降序两种，默认升序。
where条件可写可不写，如果有where，则需要再order前面。
select * from 表名 {where 条件} order by 列名1 asc|desc,列名2 asc|desc;

排序空值问题

排序时有空值回传出现问题，可以用nulls first, nulls last来指定null显示的位置。
select * from 表名 order by 列名nulls first;
select * from 表名 order by 列名nulls last;

四、单行函数

字符函数

1.字符串连接可以使用concat，可以使用||，建议使用||
如concat (’‘hello’,‘world’)
select concat(‘hello’,‘world’) from 表名;
2.字符串截取用substr，第一个参数是要截取的字符串，第二个参数是起始索引，第三个参数是截取的长度。
注意：起始索引使用0和1都代表从第一个字母开始。
substr(‘world’, 1,3)
select substr(‘world’,1,3) from 表名;
3.获取字符串长度
length(‘world’)
select length(‘world’) from 表名;
4.字符串替换，第一个参数是源字符串，第二个参数是被替换的部分，第三个参数是替换部分。
replace(‘world’,d,n)
select replace(‘world’,d,n) from 表名;

数值函数

1.ROUND函数：四舍五入
ROUND(98.128，2), 结果是98.13
2.TRUNC函数：截断
TRUNC(98.128,2) ，结果是98.12
3.MOD求余
MOD(100,30),结果是10

日期函数

1.日期默认包含日期和时间
2.日期支持加减运算
3.日期有特定函数months_between(),ADD_MONTHS(),大小写无所谓。
如获取两个时间段的月数
select months_between(today,yesterday) from 表名;
如求2个月后的日期
select add_months(today,2) from 表名;

转换函数

to_char
to_number
to_date

通用函数

指适用于任何数据类型的函数，也包括空值。
当需要空值和数值相加时，需要把空值转成0再相加，可以用空值处理函数nvl处理
select nvl(列名1,0)+列名2 from 表名;

五、多行函数

avg,count,max,min,sum

多行函数示例

select count() from 表名;
通常不建议使用count(),会影响性能，可以查询某个列，如
select count(列名) from 表名;
select min(列名) from 表名;
select max(列名) from 表名;
select avg(列名) from 表名;
select sum(列名) from 表名;

分组

select 列名1 count(列名2) from 表名 group by 列名1;

过滤分组数据

select 列名1 count(列名2) from 表名 group by 列名1 having avg(列名3 ) >100;

where和having区别

where后面不能有组函数