oracle笔记
数据库的基本概念
1.数据库(Database,DB)
2.数据库管理系统(Database Management System,DBMS)
3.数据库管理员(Database Administrator,DBA)
4.数据库系统(Database System,DBS)
5.关系型数据库(Relationship Database,RDB)
6.关系型数据库管理系统(RDBMS)
7.SQL语言(Structured Query Language)
使用关系模型的数据库语言，用于和各类数据库的交互，提供通用的数据管理和查询功能。
常用SQL指令：SELECT INSERT DELETE UPDATE CREATE DROP
Oracle基本概念/数据
1.数据库(Database)
是磁盘上存储的数据的集合(包括配置文件、数据文件、日志文件和控制文件等)
2.数据库实例(Database Instance)
运行在数据库文件上的一组Oracle后台进程/线程以及一个共享内存区。数据库可以由实例装载和打开。
3. Oracle实时应用集群(RAC,Real Application Clusters)
Oracle并行服务器架构/RAC架构
4.数据库服务名(Database Service_Name)
从oracle8i开始引入，数据库客户端与服务器端连接时建议指定其数据库服务名，而不是数据库实例名。
5.网络服务名(Net Service Name)
数据库在客户端的逻辑表示，包含数据库服务名和网络地址两方面信息
6.监听器(Monitor)
7.数据库对象：表 视图 约束条件 索引 序列 同义词 存储过程 函数 触发器 包
8.数据库安全：用户 方案 权限 角色 配额
Oracle数据库存储结构
1.物理存储结构
数据文件(Data File) 重做日志文件(Redo LogFile) 控制文件(Control File)
2.逻辑存储结构
表空间(Table Space) 段(Segment) 区(Extent) 块(Block)
Oracle基本工具
1.服务器管理工具OEM
1.1集成式系统管理控制台
1.2提供管理、诊断以及调优数据库功能
1.3远程管理多个网络数据库节点和服务
1.4管理并行服务器
2.客户端工具：Sql Plus Sql Plus WorkSheet iSqlplus
简单的表操作
//创建一个简单的表
create table student(
name varchar2(20),
age number(3)
);
//插入新纪录
insert into student values('Tom',18);
//查询记录
select * from student;
//查看表结构
desc student;
//删除表
drop table student;
SQL语句分类
1.Select查询语言
2.DML语句(数据操作语言):Insert、Update、Delete、Merge
3.DDL语句(数据控制语言)：Create、Alter、Drop、Truncate
4.DCL语句(数据控制语言):Grant、Revoke
5.事物控制语句：Commit、Rollback、Savepoint
=============================================================
=============================================================
简单的Select查询语句
1.句法结构：SELECT *|{ <字段名> , ... } FROM <表名>;
2.查询结果的默认显示效果
2.1Sql*plus的默认显示
Date和character型数据左对齐
Numeric型数据右对齐
列标题默认显示为大写
2.2iSql*默认显示
列标题默认显示为大写
列标题缺省居中对齐
3.使用算术表达式
3.1在Select语句中，对NUMBER型数据可以使用算术运算符创建表达式。
select empno, ename, sal, sal*12 form emp;
3.2算术运算符：+ - * /
3.3运算优先级
乘法和除法的优先级高于加法和减法
同优先级运算的顺序是从左到右
表达式中可以使用小括号强行改变运算顺序
4.连接运算符
4.1连接运算符‘||'可以把列与字符、或其它表达式连接在一起，得到一个新的字符串，实现’合成‘列的功能
5.使用字段别名
5.1字段别名
重命名查询结果中的字段，以增强可读性
如果别名中使用特殊字符，或者是强制大小写敏感需使用双引号
5.2语法格式：
SELECT <字段名>|<表达式>[[AS]<字段别名>],...FROM <表名>;
5.3语法举例：
select empno as 员工编号, ename 员工姓名, sal*12 "年薪" from emp;
select empno, ename "Ename", sal*12 "Anual Salary" from emp;
6.在表达式中使用空值
6.1算术表达式中如果出现空值，则整个表达式结果为空
6.2连接表达式中出现的空值被当作一个空的(长度为零的)字符串处理
7.去除重复行
7.1在缺省情况下查询结果中包含所有符合条件的记录行，包括重复行。
7.2使用DISTINCT关键字可从查询结果中清除重复行。
7.3DISTINCT的作用范围是后面所有的字段的组合。
8.查询结果排序
8.1查询结果缺省按照记录的插入顺序进行排列
8.2也可使用ORDER BY子句对查询结果进行排序，排序方式包括升序(ASC，缺省)和降序(DESC)两种：
select empno, ename, sal from emp order by sal;
select empno, ename, sal from emp order by sal desc;
8.3按多字段排序
select deptno, empno, ename, sal from emp order by deptno, sal;
8.4使用字段别名排序
select empno, ename, sal*12 annsal from emp order by annsal;
9.条件查询
9.1语法格式：SELECT * |{[DISTINCT]<字段名>|<表达式>[<别名>],...} FROM <表名> [WHERE<查询条件>];
9.2查询语句中使用字符串和日期
字符串和日期值要用单引号
字符串大小写敏感
日期值格式敏感，缺省的日期格式是'DD-MON-RR'
select * from emp where ename = 'SMITH';
select * from emp where hiredate = '02-4月-81';
获取当前缺省日期格式
select sysdate from dual;
10.比较运算符：= > >= < <= <>
11.比较运算符：
BETWEEN...AND... 介于两值之间（包括边界）
IN(set) 出现在集合中
LIKE 模糊查询
IS NULL 为空值
12.模糊查询
12.1使用LIKE运算符执行模糊查询(通配查询)
%表示零或多个字符
_表示一个字符
对于特殊符号可使用ESCAPE标识符来查找
13.逻辑运算符
AND 逻辑与
OR 逻辑或
NOT 逻辑非
14.运算符优先级
1 * /
2 + -
3 ||
4 = > >= < <= <>
5 IS [NOT] NULL LIKE [NOT] IN
6 [NOT] BETWEEN...AND
7 NOT
8 AND
9 OR
(可使用小括号强行改变运算顺序)
Oracle主要数据类型
------------------------------------------------------------------------------------------------------------------------------------------
数据类型 说明
-------------------------------------------------------------------------------------------------------------------------------------------
char 字符型，最大长度2000B，缺省长度为1B
-------------------------------------------------------------------------------------------------------------------------------------------
nchar 基于NI.S国家字符集的字符型，最大长度2000B，缺省为1字符
-------------------------------------------------------------------------------------------------------------------------------------------
varchar2 变长字符型，最大长度4000B
-------------------------------------------------------------------------------------------------------------------------------------------
nvarchar2 基于NI.S国家字符集的字符型，其余同varchar2
-------------------------------------------------------------------------------------------------------------------------------------------
varchar 同varchar2
-------------------------------------------------------------------------------------------------------------------------------------------
number(m,n) 数值型，m为总位数，n为小数位，总长度最大为38位
-------------------------------------------------------------------------------------------------------------------------------------------
date 日期型，有效表数范围：公元前4712年1月1日到公元后4712年12月31日
-------------------------------------------------------------------------------------------------------------------------------------------
long 变长字符型，最大长度2GB,不支持对字符串内容进行搜索
-------------------------------------------------------------------------------------------------------------------------------------------
raw 变长二进制数据类型，最大长度2000B
-------------------------------------------------------------------------------------------------------------------------------------------
long raw 变长二进制数据类型，最大长度2GB
-------------------------------------------------------------------------------------------------------------------------------------------
blob 二进制大对象类型，最大长度4GB
-------------------------------------------------------------------------------------------------------------------------------------------
clob 字符大对象类型，最大长度4GB
-------------------------------------------------------------------------------------------------------------------------------------------
nclob 基于NLS国家字符集的字符字符大对象类型，最大长度4GB
-------------------------------------------------------------------------------------------------------------------------------------------
bfile 在数据库外部保存的大型二进制文件大对象类型，最大长度4GB
-------------------------------------------------------------------------------------------------------------------------------------------
=============================================================
=============================================================
函数
1.Oracle函数分为单行函数和多行函数两大类
2.单行函数
操作数据项
接受参数并返回处理结果
对每一返回行起作用
可修改数据类型
可嵌套使用
3.单行函数分类
字符函数
数值函数
日期函数
转换函数
通用函数
3.1字符函数
字符大小写转换函数
------------------------------------------------------------------------------------------------------------------------------------------
函数 功能 用法 返回结果
------------------------------------------------------------------------------------------------------------------------------------------
lower() 转换为小写 lower('John Smith') john smith
------------------------------------------------------------------------------------------------------------------------------------------
upper() 转换为大写 upper('John Smith') JOHN SIMTH
------------------------------------------------------------------------------------------------------------------------------------------
initcap() 单词首字母大写 initcap('JOHN smith') John Smith
------------------------------------------------------------------------------------------------------------------------------------------
字符处理函数
------------------------------------------------------------------------------------------------------------------------------------------
函数 功能 用法 返回结果
------------------------------------------------------------------------------------------------------------------------------------------
concat() 字符串连接 concat('Hello ','World') Hello World
------------------------------------------------------------------------------------------------------------------------------------------
substr() 截取子串 substr('HelloWorld',4,3) loW
------------------------------------------------------------------------------------------------------------------------------------------
length() 返回字符串长度 length('Hello World') 11
------------------------------------------------------------------------------------------------------------------------------------------
instr() 定位子串 instr('Hello World','or') 8
------------------------------------------------------------------------------------------------------------------------------------------
lpad() 左侧填充 lpad('Smith',10,'*') *****Smith
------------------------------------------------------------------------------------------------------------------------------------------
rpad() 右侧填充 rpad('Smith',10,'*') Smith*****
------------------------------------------------------------------------------------------------------------------------------------------
trim() 过滤首尾空格 trim(' Mr Smith ') Mr Smith
------------------------------------------------------------------------------------------------------------------------------------------
replace() 替换 replace('Smith','mi','na') Snath
------------------------------------------------------------------------------------------------------------------------------------------
3.2数值函数
------------------------------------------------------------------------------------------------------------------------------------------
函数 功能 用法 返回结果
------------------------------------------------------------------------------------------------------------------------------------------
abs() 取绝对值 abs(-3.14) -3.14
------------------------------------------------------------------------------------------------------------------------------------------
round() 四舍五入 round(314.15,-2) 300
------------------------------------------------------------------------------------------------------------------------------------------
trunc() 截断 trunc(3.1415,3) 3.141
------------------------------------------------------------------------------------------------------------------------------------------
ceil() 向上取整 ceil(3.14) 4
------------------------------------------------------------------------------------------------------------------------------------------
floor() 向下取整 floor(3.14) 3
------------------------------------------------------------------------------------------------------------------------------------------
sign() 判断数值正负 sign(-3.14) -1
------------------------------------------------------------------------------------------------------------------------------------------
sin()... 三角函数... sin(3.14) .001592653
------------------------------------------------------------------------------------------------------------------------------------------
power() 幂运算 power(4.5,2) power(4.5,2)
------------------------------------------------------------------------------------------------------------------------------------------
sqrt() 开平方根 sqrt(9) 3
------------------------------------------------------------------------------------------------------------------------------------------
mod() 取模 mod(10,3) 1
------------------------------------------------------------------------------------------------------------------------------------------
exp() 基数为e的幂运算 exp(1) 2.71828183
------------------------------------------------------------------------------------------------------------------------------------------
log() 对数运算 log(4,16.0) 2
------------------------------------------------------------------------------------------------------------------------------------------
ln() 自然对数运算 ln(7) 1.94591015
------------------------------------------------------------------------------------------------------------------------------------------
3.3日期类型
3.3.1关于日期类型
Oracle内部以数字格式存储日期和时间信息：世纪，年，月，日，小时，分钟，秒
缺省的日期格式是DD-MON-YY
可使用sysdate函数获取当前系统日期和时间
3.3.2日期型数据的算术运算
日期型数据可以直接加或减一个数值，结果仍为日期
两个日期型数据可以相减，结果为二则相差多少天
3.3.3日期函数
--------------------------------------------------------------------------------------------------------------------------------------------------------
函数 功能 用法 返回结果
--------------------------------------------------------------------------------------------------------------------------------------------------------
add_months(x,y) 计算在日期x基础上增加y个月后的日期 add_month(sysdate,2)
--------------------------------------------------------------------------------------------------------------------------------------------------------
last_day(x) 返回日期x当月最后一天的日期 last_day(sysdate)
--------------------------------------------------------------------------------------------------------------------------------------------------------
months_between(x,y) 返回日期x和y之间相差的月数 month_between(sysdate,hiredate)
--------------------------------------------------------------------------------------------------------------------------------------------------------
round(x,y) 将日期x四舍五入到y所指定的 round(sysdate,'month')
日期单位(月或年)的第一天 round(sysdate,'year')
--------------------------------------------------------------------------------------------------------------------------------------------------------
trunc(x,y) 将日期x截断到y所指定的日期 trunc(sysdate,'month')
单位(月或年)的第一天 trunc(sysdate,'year')
--------------------------------------------------------------------------------------------------------------------------------------------------------
next_day(x,y) 计算指定日期x后的第一个星期 next_day(sysdate,'星期二')
几(有参数y指定)对应的日期
--------------------------------------------------------------------------------------------------------------------------------------------------------
3.4转换函数
数据类型转换包括隐含转换和显式转换两种方式，建议使用显式的数据类型转换，确保SQL语句的可靠性
to_number() to_date()
<---------------------------------------- ---------------------------------------->
数值类型 字符类型 日期类型
------------------------------------------> <----------------------------------------
to_char() to_char()
3.5常用日期格式符
------------------------------------------------------------------------------------------------------------------------------------------
格式 说明 举例
------------------------------------------------------------------------------------------------------------------------------------------
yyyy 年份 2008
------------------------------------------------------------------------------------------------------------------------------------------
dd 用数字显示月份 02
------------------------------------------------------------------------------------------------------------------------------------------
day 星期几 28
------------------------------------------------------------------------------------------------------------------------------------------
am/pm 显示上午/下午 上午
------------------------------------------------------------------------------------------------------------------------------------------
hh/hh12/hh24 小时 2：30 14：30
------------------------------------------------------------------------------------------------------------------------------------------
mi 分钟 30
------------------------------------------------------------------------------------------------------------------------------------------
ss 秒钟 46
------------------------------------------------------------------------------------------------------------------------------------------
说明：除上述格式符外，日期模式串中还可直接出现如下字符-:;/，如要显示其它文本字符串则需使用双
引号括起来；也可在模式串的开头使用“fm"标记去掉数字前面的零
3.6 通用函数
3.6.1通用函数适用于任何类型数据(包括空值):
nvl() nvl2() nullif() coalesce() case表达式 decode()
3.6.2 NVL()函数
#NVL()函数用于将空值null替换为指定的缺省值，适用于字符、数字、日期等类型数据。
#语法格式：NVL(exp1,exp2)
#说明：如果表达式exp1的值为null,则返回exp2的值，否则返回exp1的值。
#用法举例：
select empno, ename, sal, comm, sal+nvl(comm,0) from emp;
select empno, ename, hiredate, nvl(hiredate, sysdate) from emp;
select empno,ename,job, nvl(job,'No job yet') from emp;
3.6.3 NVL2()函数
#NVL2()函数用于实现条件表达式功能。
#句法格式：NVL2(exp1,exp2,exp3)
#说明；如果表达式exp1的值不为null,则返回exp2的值，否则返回exp3的值。
#用法举例：select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) total from emp;
3.6.4 NULLIF()函数
#nullif()函数用于数据等价性比较根据比较结果返回null或其中一个被比较的数值。
#语法格式：nullif(exp1,exp2)
#说明：如果表达式exp1与exp2的值相等则返回null，否则返回exp1的值。
#语法举例：select select name 原名, nullif(pen_name,name) 化名 from author;
3.6.5 COALESCE()函数
#coalesce()函数用于实现数据“结合”功能
#语法格式：coalesce(exp1,exp2,...)
#说明：依次考察各参数表达式，遇到非null值即停止并返回该值。
#用法举例：select empno, emane, sal, comm, coalesce(sal+comm, sal, 0) 总收入 from emp;
3.6.6 CASE函数
#case表达式用于实现多路分支结构
#语法格式:
case exp when comparison_exp1 then return_exp1
when comparison_exp2 then return_exp2
when comparison_exp3 then return_expn
else else_exp
end
#语法举例：
select empno, ename, sal,
case deptno when 10 then '财务部'
when 20 then '研发部'
when 30 then '销售部'
else '未知部门'
end 部门
from emp;
3.6.7 DECODE()函数
#和case表达式类似，decode()函数也用于实现多路分支机构
#语法格式：decode(col | expression, search1, result1
[,search2, result2,...,]
[,default])
#用法举例：
select empno, ename, sal
decode(deptno,10, '财务部',
20, '研发部',
30, '销售部',
'未知部门')
部门
from emp;
3.7 函数嵌套
3.7.1 单行函数可以嵌套使用，嵌套层次无限制
3.7.2 嵌套函数的执行顺序是由内到外
select empno, lpad(initcap(trim(ename)),10,' ') name, job, sal from emp;
4.分组函数
4.1 分组函数对一组数据进行运算，针对一组数据(多行记录)只返回一个结果，也称多行函数。
4.2 常用分组函数
---------------------------------------------------------------------------------------------------------------------------------------
函数 功能说明 适用类型
---------------------------------------------------------------------------------------------------------------------------------------
avg() 计算平均值 数值型
---------------------------------------------------------------------------------------------------------------------------------------
count() 返回查询所得到的记录行数 任何类型数据
---------------------------------------------------------------------------------------------------------------------------------------
max() 计算最大值 任何类型数据
---------------------------------------------------------------------------------------------------------------------------------------
min() 计算最小值 任何类型数据
---------------------------------------------------------------------------------------------------------------------------------------
sum() 求和 数值型
---------------------------------------------------------------------------------------------------------------------------------------
4.3 COUNT()函数
4.3.1 count(*)返回组中总记录数目
4.3.2 count(exp)返回表达式exp值非空记录数目
4.3.3 count(distinct(exp))返回表达式exp值不重复的、非空的记录数目。
4.4分组函数与空值
4.4.1分组函数省略列中的空值
4.4.2可使用NVL()函数强制分组函数处理空值
4.5 GROUP BY子句
4.5.1 GROUP BY子句将表中的数据分成若干小组
4.5.2 语法格式：select deptno, avg(sal) from emp group by depno;
4.5.3 说明：
#出现在SELECT列表中的字段，如果不是包含在组函数中，那么该字段必须同时在GROUP BY子句中出现
#包含在GROUP BY子句中的字段则不必须出现在SELECT列表中。
4.6 组函数的错误用法
4.6.1 如果没有GROUP BY子句，SELECT列表中不允许出现字段(单行函数)与分组函数混用的情况。
4.6.2 不允许在WHERE 子句中使用分组函数。
4.7 HAVING子句
4.7.1 HAVING子句用于过滤分组
4.7.2 句法格式
slect deptno, job, avg(sal)
from emp
where hiredate>=to_date('1981-05-01','yyyy-mm-dd')
group by deptno, job
having avg(sal)>1200
order by deptno, job;
4.8 分组函数嵌套
4.8.1 分组函数最多可嵌套两层
上一篇： 正则里的贪婪表达式。解决思路
下一篇： Python基础