本系列文章在 https://github.com/freestylefly/javaStudy 持(huan)续(ying)更(jia)新(ru)中,欢迎有兴趣的童鞋们关注。
一、Oracle概念
oracle数据可系统是美国oracle(甲骨文)公司提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器或B/S体系结构的数据库之一,oracle数据库时目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完善的数据库管理功能,是关系型数据库,比mysql更为庞大,在现行的关系型数据库中排名第一(oracle、mysql、SqlServer),时间是最为精确的。
二、Oracle的安装
三、Oracle数据库的体系结构
1、数据库database
Oracle数据库是数据的物理储存,这就包括(数据文件ORA或者DBF、控制文件、联机日志、参数文件)。其实Oracle数据库的概念和其他数据库不一样,这里的数据库时一个操作系统只有一个库,可以看做Oracle就只有一个大数据库。
2、实例
一个数据库可以有n个实例,有一系列的后台进程和内存结构组成。
3、数据文件dbf
是数据库的物理储存单位,数据库的数据是储存在表空间中的,真正是在某一个或者多个数据文件中,而一个表空间可以由一个或多个数据文件组成,一个数据文件只能属于一个表空间,一旦数据文件被加入到某个表空间后,就不能删除这个文件,如果要删除某个数据文件,只能删除其所属于的表空间才行。
4、表空间
表空间是Oracle对物理数据库上相关数据文件的逻辑映射,一个数据库在逻辑上被划分成一到若干个表空间,每个表空间包含了在逻辑上相关联的一组结构,每个数据库至少有一个表空间(称之为system表空间)
每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文件,一个数据文件只能属于一个表空间。
5、用户
用户是在实例下建立的,不同实例中可以建相同名字的用户,表的数据是由用户放入某一个表空间的,而这个表龙剑会随机把这些表数据进行管理和存放的,但是表不是由表空间去查询的,而是由用户去查询。
6、SCOTT和HR用户
是Oracle帮我们建好的测试账户,Scott账户常用,里面有emp、dept表等。
四、Oracle和MYSQL的差别
1、新建项目的方式
MYSQL : 创建一个数据库,创建相应的表
2、Oracle是多用户的, MYSQL是多数据库的
3、Oracle安全级别要高,MYSQL开源免费
1、数据完整性
关注数据的有效性和准确性
1)实体完整
关注点:一条数据的唯一性
使用主键、唯一约束
2)域完整性
关注点:字段的格式
非空、检查、外键约束
3)引用完整
关注点:字段的引用关系
4)自定义完整
可使用存储过程、触发器等进行规范
2、数据冗余
字段的数据出现重复,重复的数据是可忍受可不处理
处理方式:将重复数据提取成一张新的数据表
五、基本查询
1、SQL概念
结构化查询语言
2、SQL分类
DDL : 数据定义语言 create alter drop truncate
DML : 数据操纵语言 insert update delete
DCL : 数据控制语言 安全 授权 grant revoke
DQL : 数据查询语言 select from子句 where子句
在oracle中将DQL放入DML中,多了TCL:事务控制语言 commit、rollback
3、Oracle中的数据字典
所谓数据字典就是编写sql语句,全选可以一次性执行不报错
1 |
|
3、查询语句的结构
1 | select [列名] [*] from 表名 [where 条件] [group by 分组条件] [having 过滤] [order by 排序] |
4、伪表dual
dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构,
1 | 比如:select 1+1 from dual; |
5、别名查询
使用as 关键字, 可以省略,别名中不能有特殊字符或者关键字, 如果有就加双引号(比如空格)
1 | elect ename 姓名, sal 工资 from emp; |
6、去除重复数据 distinct
加在select和列名后面,多列去除重复: 每一列都一样才能够算作是重复
1 | --单列去除重复 |
7、查询中四则运算
需要用到伪表dual
1 | select 1+1 from dual; |
在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符
8、空值处理函数
注意: null值 , 代表不确定的 不可预知的内容 , 不可以做四则运算
1)mysql的空值处理
ifnull(参数1, 参数2):
判断参数1是否为null,
不为null, 则函数的结果取参数1的值
为null, 则取参数2的值
if(参数1, 参数2, 参数3)
判断参数1是否为null,
为null, 则取参数3的值
不为null, 则取参数2的值
注意if函数只能处理数值字段的空值
1 | SELECT e.`ename` 姓名, |
2)oracle的空值处理
nvl(参数1,参数2):如果1为null,则返回2,不为null就为1
nvl2(参数1, 参数2, 参数3)1为null,则返回,3,不为null,则返回2
decode(需要判断的字段,
常量值1, 结果值1,
常量值2, 结果值2,
….
常量值n, 结果值n,
默认结果值
)
1 | select e.ename 姓名, |
9、字符串拼接
- Oracle 特有的连接符: || 拼接
- concat(str1,str2) 函数, 在mysql和Oracle中都有
1 | --查询员工姓名 : 姓名:SCOTT |
六、条件查询
条件查询就是where后面的写法
1、关系运算符和排序
1 | > >= = < <= != <> |
2、逻辑运算符
and or not
3、其它运算符
1 | like 模糊查询 |
4、模糊查询: like
% 匹配多个字符
_ 匹配单个字符
如果有特殊字符, 需要使用escape转义
1 | --查询员工姓名中,包含%的员工信息 |
5、排序order by
升序: asc ascend
降序: desc descend
排序注意null问题 : nulls first | last指定null值显示的位置
同时排列多列, 用逗号隔开
1 | --查询员工信息,按照奖金由高到低排序 |
分析函数:用来处理排名并列问题
rank——排名可并列,之后序号不会顺眼(如2个第二,下一个是第四名)1,2,2,4
dense_rank——排名可并列,之后序号会顺眼(如2个第二,下一个是第三名)1,2,2,3
row_number——不可并列排名
over——添加分组条件
partition—by——分组
1 | /* |
6、分组排序
- select 分组的条件,分组之后的条件 from 表名 group by 分组的条件 having 条件过滤
- 在分组查询语句的检索内容中, 只能出现分组字段和聚合函数
sql的编写顺序:
select,from,where,group by,having,order by
sql的执行顺序:
from,where,group by,having,select,order by…….
where和having的区别:
where后面不能跟聚合函数,可以接单行函数
having是在group by之后执行,可以接聚合函数1
2
3
4
5--分组统计所有部门的平均工资,找出平均工资大于1500的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>1500;
--报错,给分组之后的条件取别名的时候出错,因为sql语句执行的顺序问题
select deptno,avg(sal),bb from emp group by deptno having bb>1500;
七、函数
函数: 必须要有返回值
1、单行函数
对某一行中的某个值进行处理
1)数值函数
取整
1
2select ceil(45.926) from dual; --46 向上取整
select floor(45.926) from dual; --45 向下取整四舍五入
round(a,b)
b如果是正数代表取到小数点后b位数为止
b如果是负数代表取到十位为止
1
2
3
4
5
6elect round(45.926,2) from dual; --45.93
select round(45.926,1) from dual; -- 45.9
select round(45.926,0) from dual; --46
select round(45.926,-1) from dual; --50
select round(45.926,-2) from dual; --0
select round(65.926,-2) from dual; --100截断(去尾法)
1
2
3
4
5
6select trunc(45.926,2) from dual; --45.92
select trunc(45.926,1) from dual; -- 45.9
select trunc(45.926,0) from dual; --45
select trunc(45.926,-1) from dual; --40
select trunc(45.926,-2) from dual; --0
select trunc(65.926,-2) from dual; --0求余
1
2select mod(9,3) from dual; --0
select mod(9,4) from dual; --12)字符函数
1 | substr(str1,起始索引,长度) |
3) 日期函数
1 | -查询今天的日期(查询的是服务器上的日期) |
4) 转换函数
数值转字符
1 | select to_char(sal,'$9,999.99') from emp; |
日期转字符 to_char() (不区分大小写)
1 | select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; |
字符转日期
1 | select to_date('2017-04-10','yyyy-mm-dd') from dual; |
5) 通用函数
1 | nvl(参数1,参数2) 如果参数1 = null 就返回参数2 |
2、多行函数(聚合函数)
对某一列的所有行进行处理
max() min count sum avg
注意:直接忽略空值
例子:
1 | --统计员工的平均奖金 550 错误 2200/14 =,因为空值产生的影响 |
3、条件表达式
作用:查询的时候用来替换列中所有数据的值,区别于取别名,取别名只是单纯的给子弹取别名
1 | /* |
八、多表查询
1、笛卡尔积
实际上是两张表的乘积,但是在实际开发中没有太大意义,格式: select * from 表1,表2
1 | select * from emp; |
2、内连接
隐式内联接:
等值内联接: where e1.deptno = d1.deptno;
不等值内联接: where e1.deptno <> d1.deptno;
自联接: 自己连接自己
显式内联接:
select * from 表1 inner join 表2 on 连接条件
inner 关键字可以省略
1 | --查询员工编号,员工姓名,经理的编号,经理的姓名 |
2、外连接
外连接: (标准,通用写法)
左外连接: left outer join 左表中所有的记录,如果右表没有对 应记录,就显示空
右外连接: right outer join 右表中的所有记录,如果左表没有对应记录,就显示空
outer 关键字可以省略
Oracle中的外连接: (+) 实际上是如果没有对应的记录就加上空值
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
1 | select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno; |
九、子查询
查询语句中嵌套查询语句; 用来解决复杂的查询语句
1、单行子查询
= = < <= <> !=
1 | --查询最高工资的员工信息 |
2、多行子查询
in not in >any >all exists not exists
通常情况下, 数据库中不要出现null 最好的做法加上Not null
null值并不代表不占空间, char(100) null 100个字符
1 | --查询不是领导的信息 |
3、exists(查询语句)
存在的意思,判断一张表里面的记录是否存在与另外一张表中,作布尔值来处理:, 当查询语句有结果的时候, 就是返回true,否则返回的是false,数据量比较大的时候是非常高效的
1 | --查询有员工的部门的信息 |
十、分页
Oracle的分页查询需要使用伪列辅助
伪列 : 在Oracle的结果集中默认带有的可以使用的
不出现在结果集列中的内容.
rownum : 伪列序号, 从1开始
rowid : 伪列地址
SQL执行顺序
from .. where ..group by..having .. select..rownum..order by
默认都是从1开始。
1 | rownum按照员工id排序后再分页 |
1 | rowid去除表中重复数据 |
十一、集合运算
所有的查询结果可能不是来自同一张表, 将查询结果进行合并,并去除重复项
1、并集
union : 去除重复的,并且排序
union all : 不会去除重复的
1 | select * from emp where sal > 1500 |
2、交集运算: intersect
1 | --工资大于1500,并且20号部门下的员工 |
3、差集运算
两个结果相减
1 | --1981年入职员工(不包括总裁和经理) |
4、集合运算中的注意事项
1.列的类型要一致
2.按照顺序写
3.列的数量要一致,如果不足,用空值填充
1 | select ename,sal from emp where sal > 1500 |
十二、DDL语句管理表
1、表空间
逻辑单位, 通常我们新建一个项目,就会去新建表空间,在表空间中创建用户来创建表
创建表空间
语法:
create tablespace 表空间的名称
datafile ‘文件的路径(服务器上)’
size 大小
autoextend on 自动扩展
next 每次扩展的大小1
2
3
4
5
6--创建表空间-
create tablespace canghe
datafile 'C:\oracle\canghe.dbf'
size 100m
autoextend on
next 10m;删除表空间
1 | --删除表空间 |
2、创建用户
create user 用户名
identified by 密码
default tablespace 表空间的名称
1 | create user canghe |
- 授权
1 | --授予 dba的角色 |
3、创建表
create table 表名(
列名 列的类型 [列的约束],
列名 列的类型 [列的约束]
);
列的类型:
varchar ,在Oracle中,目前是支持的, 但是不保证以后还支持
varchar2(长度) 可变字符长度 varchar2(10) hello 占5个字符
char(长度) 固定长度字符 char(10) hello 占10个字符,用空格填充
number(总长度,小数长度) 数字类型 --小数长度不能大于等于总长度
date 年月日时分秒 2017/4/13 9:43:49
timestamp 时间戳, 比date类型更加精确 13-APR-17 09.44.08.272000 AM +08:00
LONG/CLOB : 存放一本小说
BLOB : 存放电影 java 存进去, 再读取出来
使用子查询的方式创建表
create table 表名 as 查询语句;
注意: 只会复制表结构和表中的数据,不会复制列的约束
如果查询语句有结果, 就是复制 表结构和数据
如果查询语句没有结果, 就是复制 表结构
1 | create table test1( |
4、修改表
添加咧
alter table 表名 add 列
修改列
修改列类型:modify
修改列名:rename
删除咧
alter table 表名 drop column 列名;
修改列名
alter table 表名 rename column 列名;
重命名表
rename 旧表名 to 新表名;
5、删除表
drop table 表名
6、表的五大约束
主键约束
primary key
非空约束
not null
唯一约束
unique
外键约束
添加外键约束:
alter table 表名 add foreign key(添加约束的字段) references 引用的表名 (引用的字段)
插入:先主表、再从表
删除:先从表,再主表
强制删除表(不建议):
drop table 表名 cascade constraint;
级联删除:
添加外键约束,使用级联约束 ,在删除的时候,使用级联删除
1
2
3
4
5
6
7
8
9----添加外键约束,使用级联约束 ,在删除的时候,使用级联删除
alter table product add foreign key(cno) references category(cid) on delete cascade;
insert into category values(2,'电脑办公');
insert into product values(11,'外星人',2);
--级联删除 : 首先去从表中找有没有 关联数据, 如果在从表中找到关联数据,先删除从表中关联数据,然后再删除表中的数据
delete from category where cid = 2;检查约束
check( 列名 in (‘值1’,’值2’,’值3’))
十三、DML管理表数据
1、插入数据
指定列名插入
insert into 表名 values(所有列的值都要对应写上)
不指定列名插入
insert into 表名(列1,列2) values(值1,值2);
使用子查询插入
insert into 表名 查询语句
2、修改数据
update 表名 set 列名 = 列的值 [where 条件]
3、删除数据
delete from 表名 [where 条件]
1 | delete和truncate 区别 |
十四、其他数据库对象
1、事务
事务: 就是一系列的操作,要么都成功,要么都失败
四大特性: 原子性,隔离性,持久性,一致性
如果不考虑隔离级别: 脏读,虚读,不可重复读
MYSQL隔离级别: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLE
ORACLE隔离级别: READ COMMITTED SERIALIZABLE READ ONLY
默认隔离级别: READ COMMITTED
提交 : commit
事务的保存点/回滚点: savepoint 保存点的名称
回滚: rollback
2、视图
视图: 是对查询结果的一个封装
视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据
1.能够封装复杂的查询结果
2.屏蔽表中的细节
语法:
create [or replace] view 视图的名称 as 查询语句 [ with read only]
注意: 通常不要通过视图去修改,视图创建的时候,通常要加上with read only
1 | --创建一个视图 |
-同义词的概念(就是为视图取的一个别名)
create synonym dept for view_test3;
3、序列
oracle中用来控制自增长的
1 | 语法: |
1 | --序列用的最多的一种写法 |
4、索引
1 | 索引:相当于是一本书的目录,能够提高我们的查询效率 |
十五、PLSQL编程
1、概述
procedure Language 过程语言 Oracle对SQL的一个扩展
让我们能够像在java中一样写 if else else if 条件, 还可以编写循环逻辑 for while
2、基本语法
1 | declare |
1 | declare |
3、变量
%type —引用型变量
%rowtype —记录型变量
4、PL条件判断
1 | if then |
1 | declare |
5、循环操作
1 | while 循环 |
1 | --输出1~10 |
十六、游标
用来操作查询的结果集.相当于JDBC中的ResultSe
1 | 语法: cursor 游标名[(参数名 参数类型)] is 查询结果集 |
1、不带参数游标
1 | */ |
2、带参数的游标
1 | --输出指定部门下的员工姓名和工资 |
3、系统引用游标
1 | --输出员工表中所有的员工姓名和工资 |
4、-使用for循环遍历游标
1 | --按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400 |
十七、例外
例外:(意外)程序运行的过程发生异常,相当于是JAVA中的异常
1 | declare |
1 | --查询指定编号的员工,如果没有找到,则抛出自定义的异常 |
十八、存储过程
1 |
|
1 | */ |
十九、存储函数
1、基本语法和例子
存储函数: 实际上是一段封装是Oracle服务器中的一段PLSQL代码片断,它是已经编译好了的代码片段
语法:
create [or replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型
is | as
begin
end;
存储过程和函数的区别:
1.它们本质上没有区别
2.函数存在的意义是给过程调用 存储过程里面调用存储函数
3.函数可以在sql语句里面直接调用
4.存储过程能实现的,存储函数也能实现,存储函数能实现的,过程也能实现
默认是 in
1 | */ |
2、java调用存储过程、存储函数
1)基本套路
1 | /* |
2)调用存储过程
1 | /** |
3)调用存储函数
1 | //调用存储函数 |
3)通过游标输出结果集
1 | /** |
二十、触发器
1 | 触发器: 当用户执行了 insert | update | delete 这些操作之后, 可以触发一系列其它的动作/业务逻辑 |
1 | --新员工入职之后,输出一句话: 欢迎加入黑马程序员 |
二十一、数据库设计
二十一、数据库优化
1.优化SQL:提升sql语句的执行效率
1) 查询语句中尽量不使用”*”
2) 查询语句中尽量将筛选效果好的条件放在前面
3) 可以使用连接查询替代子查询时, 尽量使用连接查询
4) 将模糊查询条件尽量放在后面
5) 将带有索引的字段条件放在前面
6) 可以使用in/not in/between替代的场合,就替代使用
2.优化索引
1) 不能随意添加
2) 为使用量大的字段添加
3.优化表分区(分区表)
根据指定字段将数据表的数据(数据巨大时), 划分成多个分区.
查询时, 首先判断数据所在分区, 再检索数据
4.优化系统