一、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后面不能有组函数