前言
Oracle数据库是什么?
Oracle Database,又名Oracle RDBMS,简称Oracle。是甲骨文公司推出的一款关系数据库管理系统。
Oracle和MySQL的区别?
- Oracle是大型数据库而Mysql是中小型数据库,Oracle市场占有率达40%,Mysql只有20%左右,同时Mysql是开源的而Oracle价格非常高。
- Oracle支持大并发,大访问量,是OLTP最好的工具。
- 安装所用的空间差别也是很大的,Mysql安装完后才152M而Oracle有3G左右,且使用的时候Oracle占用特别大的内存空间和其他机器性能。
- Oracle也Mysql操作上的一些区别
Oracle学习链接:
实操:
https://www.w3cschool.cn/oraclejc/oraclejc-dxgu2qqt.html
理论:
http://zw1840.com/oracle/translation/concepts/index.htm
此博客的内容主要是慕课课程的笔记。
课程连接:https://www.imooc.com/learn/337
Oracle 概述
Oracle 数据库服务器由:数据库(一组存储数据的文件)、数据库实例(管理存储数据的内存结构)组成。
1. Oracle 数据库
1.1 物理存储结构:存储数据的纯文件
当create database 执行时,将创建一下文件:
- 数据文件:真实数据
- 控制文件:无数据,用于描述数据库名称,文件位置,物理结构
- 联机重做日志文件:由重做条目组成,记录下所有对数据所作的更改
1.2 逻辑存储结构:对磁盘空间使用进行精细控制
- 数据块:存储数据,对应于磁盘上的字节数
- 范围:逻辑连续数据块的具体数量
- 段:分配用于存储用户对象(eg.表/索引)的一组范围
- 表空间:逻辑存储单元(永久表空间、临时表空间、UNDO)
2. Oracle实例:用户和数据库的接口,组成:系统全局区(SGA)、程序全局区(PGA)、后台进程
3. Oracle基础语句
-
SELECT: SELECT col1_name,col2_name,… FROM table_name;
-
ORDER BY: 放在SELECT 最后一个子句
SELECT col_1, col_2 FROM table_name ORDER BY col_1 asc, col_2 desc;
1)default值为asc,先按col_1排序,再按col_2排序。 列名可替换为列的序号。
2)含NULL值时,可指定NULL 排序位置: ORDER BY state asc NULLS LAST;
3)ORDER BY 可应用一个函数排序:ORDER BY UPPER(name) -
DISTINCT: SELECT DISTINCT col_1,col_2,…,col_n (组合的唯一性)
1)Distinct 将NULL 视为重复值 -
WHERE: 过滤
1)先FROM → WHERE → SELECT
=, != , < , > , >= , <=
IN 等于值列表中的任何值,NOT IN 不等于值列表中的任何值
ANY / SOME / ALL 将值与列表或子查询进行比较,必须以另一运算符作为前缀,如(<, =)
[NOT] BETWEEN xxx AND yyy
[NOT] EXISTS
IS [NOT] NULL2) AND, OR, NOT 组合条件
-
AND, OR 可以用IN代替。如 IN(20,21,22) 等价于 20 OR 21 OR 22
-
FETCH:限制查询返回的行数(替换SQL 中的Limit)
[ OFFSET offset ROWS]
FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]
ONLY: 仅返回FETCH NEXT(或FIRST)后的行数或行数的百分比。
WITH TIES: 返回与最后一行相同的排序键。请注意,如果使用WITH TIES,则必须在查询中指定一个ORDER BY子句。如果不这样做,查询将不会返回额外的行。 -
IN
-
BETWEEN
WHERE order_date BETWEEN ‘2016-12-01’ AND ‘2016-12-13’ -
LIKE: 模式匹配
last_name LIKE ‘st%’ 以st开头的last_name
last_name LIKE ‘%er’ 以er结尾的last_name
last_name LIKE ‘%tr%’ 中间字符有tr的last_name
’je_i’ 其中下划线_代表任意字符
表空间
SYSTEM 系统信息
SYSAUX 作为example的辅助
UNDOTBSI 撤销信息
TEMP 临时
USERS 用户创建的对象
向表空间增加数据文件
ALTER TABLESPACE tablespace_name ADD DATAFILE ‘XX.dbf’ SIZE XXX
删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS
表
最基本存储空间,2维结构(行和列),列具有相同数据类型,列名唯一,行数据唯一性。
数据类型:
a) 字符型:char(n)←max:2000, nchar(n) ←max:1000, varchar2←可变长度节省空间max:4000, nvarchar2←max:2000
b) 数值型:numbers(p,s)←(有效数字,小数点后的位数), float(n)
c) 日期型:DATE精确到秒, timestamp 精确到小数秒
d) 其他:blob(可存放4GB,2进制) ,clob(可存放4GB,字符串)
管理表
管理表可分为三个部分:创建表,修改表,删除表
创建表
包含:所需字段、字段的类型
CREATE TABLE table_name(col_name datatype,…);
例如:CREATE TABLE userinfo(id number(6,0), user_name varchar2(20), reg_date date);
修改表
- 添加字段:alter table table_name add column_name datatype;
- 更改字段数据类型:最好无值前修改,alter table table_name modify column_name datatype; 设置默认值,modify email default ‘none’
- 删除字段:alter table table_name drop column col_name
- 修改字段名:alter table table_name rename column col_name to new_name
- 修改表名:rename table_name to new_name 注意这里没有table关键字
删除表
delete table table_name
truncate table table_name
drop table table_name
truncate和drop都是ddl语句(数据定义语句),执行后会自动提交
区别:
1、truncate和delete都不删除表结构,只删除表数据。drop语句会将表结构、被依赖的关系、触发器、索引都删除,保留依赖于该表的存储过程和函数,但是变为invalid状态。
2、delete是dml(数据库操作语言),这个操作会放到rollback segment中,会记录日志,可以通过回滚操作撤销删除的数据,必须通过提交才会真的删除数据。如果有相应的触发器trigger,则会触发。
truncate、drop都是ddl(数据库定义语言),操作不能回滚,不会记录到日志中。且不会触发trigger。
3、delete不影响表占用的表空间extent,高水线(high watermark)保持不动,即删除内容后,在插入别的语句时,表中的开始位置为删除前的最高位置。如删除前表中有三条数据,删除后插入新数据时,会从第四条开始插入。
drop 将表所占用的表空间全部释放。
truncate语句缺省情况下会将表空间释放到最小表空间minextents个extents,除非使用reuse storage;
truncate语句会将高水位线复位。
4、速度上,一般来说drop>truncate >delete。
操作表中的数据
添加数据
insert 语句: insert into table_name (col1_name,col2_name,…) values(col1_val,col2_val,…)
添加默认值 default
复制表数据
create table table_new as select col1,col2,… from table_old
在添加时复制
insert into table_new [(col1,col2,…)] select col1,col2… from table_old
修改数据
update table_name set col1=value1,… [where]
删除数据
delete table_name where …
约束
约束:定义规则,确保完整性。分为外键约束、唯一约束、检查约束。
-
非空约束:
在创建表时:create table table_name(col_name datatype NOT NULL,…);
在修改时:alter table table_name modify col_name datatype NOT NULL(也可以改成NULL) -
主键约束:确保每一行的唯一性
在创建表时:create table table_name(col_name datatype PRIMARY KEY,…)
联合主键:所有字段写完后,CONSTRAINT constraint_name PRIMARY KEY(col1_name,col2_name,…)
在修改时:ADD CONSTRAINT constraint_name PRIMARY KEY(col_name,…)
删除主键约束:
禁用: DISABLE /ENABLE CONTRAINT constraint_name
删掉:DROP CONSTRAINT constraint_name;
DROP PRIMARY KEY [caseade] -
约束字典
user_constraints
查询约束的名称:select constraint_name from user_constraints where table_name = ‘XXX’ -
外键约束
在创建表时:
create table table1 (col1 datatype REFERENCES table2(col2),…);
table1是从表,table2是主表,col2须为table2的主键,且col1和col2的数据类型应该一致。
CONSTRAINT constraint_name FOREIGN KEY (col1) REDERENCES table_name(col_name) [ON DELETE CASCADE]
constraint_name 要唯一在修改表时:
alter table table_name ADD CONSTRAINT constraint_name FOREIGN KEY(col_name) REFERENCES table_name(col_name)
删除约束:
禁用:DISABLE /ENABLE CONSTRAINT con_name[ ON DELETE CASCADE];
删除:DROP CONSTRAINT con_name
On Delete和On Update都有Restrict,No Action, Cascade,Set Null属性。
restrict(约束): 当在父表(即外键的来源表)中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。
cascade 表示级联操作,就是说,如果主键表中被参考字段更新,外键表中也更新,主键表中的记录被删除,外键表中改行也相应删除
no action:意思同restrict.即如果存在从数据,不允许删除主数据。
set null:当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(不过这就要求该外键允许取null) -
唯一约束
唯一约束可为NULL,可有多个。而主键约束必须是非空的、在每个表中只能有一个。
创建表时(列级创建):create table table_name (col1_name datatype UNIQUE,…)
表级创建:constraint constraint_name UNIQUE(col1_name ) 每个唯一约束都要单独创建
在修改表时:add constraint constraint_name UNIQUE(col_name)
删除:禁用:distable/enable constraint constraint_name
删除:drop constraint constraint_name -
检查约束
检查约束让表中的值具有实际意义。
创建表时:create table table_name check(expressions)
表级设置:constraint constraint_name check(expressions)
修改表时:add constraint constraint_name check(expressions)
删除:禁用:disable/enable constraint constraint_name
删除:drop constraint constraint_name
非空约束只能在列级设置,不能在表级设置,非空约束没有名字
查询
- 基本查询:select [DISTINCT] col_name,…|* from table_name where …
- 设置新的字段名: column(或者简写成col) col_name heading new_name
- 设置结果显示的格式:column col_name format dataformat
dataformat类型:
a10(字符类型长度为10,字符类型只能设置显示的长度)
999.9(用9来占位,数值型,当格式与实际值不符,显示#)
$99.9(美元) - 清除格式:column col_name clear
- 查询所有字段:select * from table_name
- 查询指定字段:select col_name1,col_name2 from table_name
- 给字段设置别名: select col_name [as] new_name 与heading区别:一次可以设置多个
- 运算符和表达式: 表达式= 运算符+操作数
- 算数运算符:+,-,*,/
- 比较运算符:>, >=, < , <=, = 返回FALSE/TRUE
- 逻辑运算符:and, not, or
-
带条件的查询:where, 用括号()放置较高的优先级, 运算符自身的优先级为 not>and>or
-
模糊查询:like,通配符的使用(_,%)←(代替一个字符,代替0到多个字符)
例子:
where col_name like ‘a%’ 以a开头的
where col_name like ‘_a%’ 第2个字符为a
where col_name like ‘%a%’ 含有字符a -
范围查询: where salary >=900 and salary <= 2000
等价于between 900 and 2000 (闭区间)
IN/ NOT IN 列表查询
where user_name in (‘aaa’,‘bbb’) -
对查询结果进行排序:select … from … [where…] order by col_name desc/asc [, col2_name desc/asc] 字符按ASCII码排序
-
条件查询:case when 语句
- case col_name when val1 then result1,when … then …,… [ELSE resultn] end
- case when col_name = value1 then result 1, … [ELSE resultn] end
-
decode函数:decode(col_name, value ,result1,…, default valuen)
-
ifnull函数:ifnulll(a,b) 判断a是否为null ,是则返回b,否则返回a
-
union:表的并联,union all即使遇到重复的记录也会保存
-
联合组成键值来分组:group by concat(x,’,’,y)
Oracle 函数
数值函数、字符函数、日期函数、转换函数
- 数值函数
- round: 四舍五入 round(x,m) 保留数值x的m位小数, m>0:小数点后m位,m<0:小数点前m位
select round(23.4),round(23.45,1),round(23.45,-1) from dual
结果依次为:23,23.5,20 oracle里面必须接目标表dual - 取整函数:ceil(n) 向上取整 floor(n) 向下取整
select ceil(23.45), floor(23.45) from dual - 常用计算:
abs(n) 绝对值
mod(m,n) 若m/n为null,返回null
power(m,n) m^n
sqrt(n) 算数平方根
三角函数: sin(n),asin(n),cos(n),acos(n),tan(n),atan(n)
- 日期函数
- 系统时间:sysdate 默认格式DD-MON-YY
- 日期操作:
add_months(date,i)
next_day(date,char) 若char为’Monday’,返回date之后的下一个Monday
last_day(date) 返回date所在月份的最后一天
months_between(date1,date2) 两个日期间隔的月份
extract(date/year/hour from datetime)提取datetime的某个粒度 年月日小时分等
datediff(date1,date2) 等价于date1-date2的值
- 字符函数
- 大小写转换:
upper(char)
lower(char)
initcap(char) 首字符变为大写 - 获取子字符串:substr(char,m,n)
char 表示原字符串,
m表示取子串的位置(0,1)均表示从首字符开始截取,m<0 表示尾部开始,向前算abs(m)个然后按顺序开始截取n个,若n>abs(m) 则n=abs(m),
n表示截取子串的长度 - 获取字符串长度:length(char) 包含空格长度
- 字符串连接函数:concat(char1,char2) 与||操作符一样
select ‘a’ || ‘b’ from dual
等价于 _select concatr(‘a’,‘b’) from dual
- 去除子串
a) 去除空格:
trim(char) :去除左右空格,中间不回去除
ltrim(char): 仅去除左侧空格
rtrim(char): 仅去除右侧空格
都无法去除全角空格
b) 去除指定字符串
trim函数用来去除一个字符串的开头或结尾(或两者)的字符。
trim(leading| trailing | both char1 from char2)
char1为单个字符
trim(leading ‘a’ from ‘aabbaaccaa’) 结果为:bbaaccaa
trim( trailing ‘a’ from ‘aabbaaccaa’) 结果为:aabbaacc
trim(both ‘a’ from ‘aabbaaccaa’) 结果为bbaacc
ltrim、rtrim
ltrim(string1,string2) string1为原始字符串,string2为要去除的字符串(可以为多个字符)
例子:
ltrim(‘abccba’,‘abc’) 结果:空串null
rtrim(‘abccba’,‘abc’) 结果:空串null
解析:若string2为多个字符,以单个字符开始匹配,直到遇到string1中出现string2中没有的字符。
另一个例子:
ltrim(‘abcxcba’,‘abc’) 结果:xcba(结果并不是只剩一个“x”,而是包括stirng1中“x”以及右侧的字符)
rtrim(‘abcxcba’,‘abc’)结果:abcx(结果并不是只剩一个“x”,而是包括stirng1中“x”以及左侧的字符)
解析:ltrim函数从string1左侧往右读,左侧前三个字符分别在string2中找到了对应的单个字符匹配,所以都被去除掉,读到“x”的时候,没有匹配上,所以从这开始到string1右侧都被保留并返回;相反,rtrim函数从string2右侧往左读,右侧前三个字符分别在string2中找到了对应的单个字符匹配,所以都被去掉,读到“x”的时候,没有匹配上,所以从这开始到string1左侧都被保留并返回。
由此类推,如果string1中左侧(右侧)第一个字符在string2中找不到对应,那么ltrim(rtrim)函数将会把string1字符串完整返回 - 替换函数
replace(char,string,newstring) 用newstring(default值为空格)替换char中的string
- 转换函数
a) 日期→字符:to_char(date,format,param) ←(日期,转换的格式,日期的语言)
常用格式:
YY,YYYY,YAER
MM,MONTH
DD,DAY
HH24,HH12
MI,SS
select to_char(sysdate,‘YYYY-MM-DD HH24:MI:SS’) from dual
结果:‘2020-04-20 21:24:41’
b) 字符→日期:to_date(char,format,params)
select to_date (‘2019-09-08’,‘YYYY-MM-DD’) from dual
结果:2019-SEP-08
c) 数字→字符:to_char(number,format)
常用格式:
9:显示数字并忽略前面的0
0:显示数字,位数不足,用0补齐
.或0:显示小数位
,或G:显示千位符
$:美元符号
s:加正负号(前后都可以)
d) 字符→数字:to_number(char,format)