关系型数据库
概念
- 关系数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
- 关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。
- 简单说,关系型数据库是由多张能互相联接的二维行列表格组成的数据库。
- 因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。
JDBC
- 注册数据库驱动
- 获取数据库连接
- 创建发送
sql
的statement
对象 - 执行
sql
语句,并返回结果集 - 遍历结果集
- 释放资源
Oracle
基本常识SQL PLUS
- 显示当前用户:
show user;
- 显示当前用户下的表:
select * from tab;
- 显示某个表的结构:
desc [表名];
- 清屏:
host cls;
- 显示行宽:
show linesize;
设置行宽:set linesize 120;
- 显示/设置列宽:
col [列名] for a8/9999;
SQL
中的null
- 包含
null
的表达式都为null
,即一个数以null
值运算,结果始终为null
。 - 当有
null
值参与运算时,需要调用Oracle
自有的函数nvl(a,b)
。即当a
不为null
返回本身,为null
时返回b
。 null
永远!= null
。即where comm = null;
永远都查不到数据,即使comm
字段中有null
值。要查询某列中为null
的数据,语句是:select * from emp where comm is null;
- 包含
distinct
作用于后面所有的列,只要列组合起来不重复即不重复。select distinct deptno, job from emp;
Oracle
查询语句时需要携带from
关键字,而有时候我们的操作不与任何表相关联,此时就需要引入一个概念伪表,是Oracle
特有的。伪表dual
的存在没有任何作用,只是充当满足SQL99
规范。select concat('hello',' world') from dual;
Oracle
数据库中,可以通过||
把字符串连接起来,用来拼接字符串。Oracle
数据库中单引号表示字符串,双引号寓意不解析的信息,常用于别名中。- 别名不能以数字开头,若非要数子开头则需要使用双引号;中文字符串,若中间有空格也需要使用双引号。
SQL和SQL PLUS
SQL:
一种语言,ANSI
标准,关键字不能缩写,使用语句控制数据库中的表的定义信息和表的数据。SQL PLUS:
一种环境,Oracle
的特性之一,关键字可以缩写,命令不能改变数据库中的值。
表的命名
- 表名和列名的基本规范:
- 必须以字母开头
- 必须在
1~30
个字符之间 - 必须只能包含
A-Z, a-z, 0-9, _, $和#
- 必须不能和用户定义的其它对象重名
- 必须不能是
Oracle
的保留字 Oracle
默认存储是存为大写- 数据库名只能是
1~8
位。
过滤和排序
- 字符和日期都要包含在单引号中。
Oracle
字符大小写敏感,日期格式敏感。默认的日期格式是DD-MON-RR
。 - 修改系统默认的时间日期格式:
select * from v$nls_parameters;
alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';
between...and...:
**含有边界,小值在前,大值在后。**这与MySQL
一致。in
关键字的用法,与MySQL
一致,但需要注意的是:如果in
后面的范围中含有null
,则不能使用not in
进行查询,但是还可以使用in
进行查询。- 模糊查询:与
MySQL
一致。_
占一个字符,%
占多个字符。当模糊查询的关键字与_
和%
冲突时,需要使用escape
定义转义字符的方式来进行查询,例如:select * from emp where ename like '%\_%' escape '\';
- 排序,默认是按升序排序
asc
,降序关键字:desc
。这也与MySQL
一致。- 注意:
order by
后面 + **列、表达式、别名、序号。**序号就是查询时select
后列出的列名的排序号,该序号从1
开始计数。 - 多个列的排序,先按照第一个关键字进行排序,如果第一个关键字对应的值相同,再按照第二个关键字对应的值进行排序,以此类推。
- 关键字的作用域只局限于离它最近的列。
null
值的排序:- 当按照升序排序时,
null
值的行会排在最后;当按降序排序时,null
值所在的行则会排在最前面。 - 解决办法:
select * from emp order by comm desc nulls last;
- 在
Oracle
数据库中,null
最大。
- 当按照升序排序时,
- 注意:
单行函数
SQL函数:
函数可以没有参数,但是必须有返回值。
字符函数
substr(a,b):
从字符串a
中的第b
位开始取,从1
开始计数。substr(a,b,c):
从字符串a
中的第b
位开始取,取c
个字符。length:
字符数;lengthb:
字节数lpad:
左填充;rpad:
右填充lpad('abcd',10,'*'):
三个参数,第一个参数:需要被填充的字符串;第二个参数:字符串的总长度;第三个参数:填充的字符。
instr(a,b):
在a
中查找b
,返回b
首个字符在a
中的位置。也是从1
开始计数。trim:
去掉前后指定的字符,全部去除。中间的字符不能去除。默认去除的是' '
。replace:
替换字符。select replace('Hello World','1','*') from dual;
。
数值函数
round:
四舍五入。round(a, count);
- 当为
0
时,即不要小数位;当为-1
时,四舍五入到十位,即查看个位数,以此类推;当为1
时,保留一位小数,以此类推;
- 当为
trunc:
截断,与round
不同的是它不进行四舍五入,直接截断。- 当为
0
时,即不要小数位;当为-1
时,四舍五入到十位,即查看个位数,以此类推;当为1
时,保留一位小数,以此类推;
- 当为
mod:
求余,也称取模;
日期函数
Oracle
中的日期型数据实际含有两个值:日期和时间。默认格式为DD-MON-RR
。select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
- 在日期上加上或者减去一个数字结果仍然为日期;
- 两个日期相减返回日期之间相差的天数;日期与日期之间不可以相加,因为没有实际意义。
months_between(a,b):
比较的是a,b之间的月数。即两个日期相差的月数。add_months(a,b):
当前日期a
的基础上加上b
个月后的日期。next_months(a,b):
相对于a
,下一个b
出现的日期。- 日期的四舍五入
数据类型转换
- 隐式数据类型转换
源数据类型 | 目标数据类型 |
---|---|
varchar2 or char | NUMBER |
varchar2 or char | Date |
Number | varchar2 |
Date | varchar2 |
- 显示数据类型转换
源数据类型 | 转换函数 | 目标数据类型 |
---|---|---|
Number | to_char | Character |
Character | to_number | Number |
Date | to_char | Character |
Character | to_date | Date |
to_char
函数对日期的转换:to_char(date, 'format_model')
,日期的格式如下
格式 | 说明 | 举例 |
---|---|---|
YYYY | Full year in numbers | 2011 |
YEAR | Year spelled out (年的英文全称) | twenty eleven |
MM | Two-digit value of month月份 (两位数字) | 04 |
MONTH | Full name of the month (月的全称) | 4月 |
DY | Three-letter abbreviation of the day of the week (星期几) | 星期一 |
DAY | Full name of the day of the week | 星期一 |
DD | Numeric of the month | 02 |
to_char
函数对数字的转换:to_char(number,'format_model')
,数字转换的格式如下
9 | 数字,一位数字 |
---|---|
0 | 零 |
$ | 美元符 |
L | 本地货币符号 |
. | 小数点 |
, | 千位符 |
通用函数
nvl(a,b):
当a
为null
时,返回b
;否则就返回a
本身。nvl2(a,b,c):
当a
为null
时,返回c
,否则返回b
;nullif(a,b):
当a = b
时,返回null
,否则返回a
;coalesce:
从左到右,找到第一个不为null
的值。
条件表达式
- 相当于在
SQL
语句中使用IF-THEN-ELSE
- 实现方式:
CASE
表达式:SQL99
的语法,类似Basic
,比较繁琐;DECODE
函数:Oracle
自己的语法,类似Java
,比较简洁;
CASE
表达式
select ename,job,sal pre,
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end over
from emp;
decode
表达式
select ename,job,sal pre,
decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) over
from emp;
多行函数
- 多行函数作用于一组数据,并对一组数据返回一个值。也叫:组函数、分组函数。
- 组函数会忽略空值,
nvl
函数使分组函数无法忽略空值。
常用的多行函数
count:
返回找到的记录数count(列名):
该列的记录数,会忽略为null
的行。count(*):
表中的记录数,
min:
返回一个数字列或计算列的最小值max:
返回一个数字列或计算列的最大值sum:
返回一个数字列或计算列的总和avg:
对一个数字列或计算列的平均值- 采用忽略空值的方式来计算平均数。
Group By子句
- 在
SELECT
列表中所有未包含在组函数中的列都应该包含在GROUP BY
子句中; - 包含在
GROUP BY
子句中的列不必包含在SELECT
列表中。 - 即所有包含于
SELECT
列表中,而未包含于组函数中的列必须包含于GROUP BY
子句中。 - 多个列的分组:先按照第一个列分组。如果相同,再按第二个列分组,以此类推。
-- 多个列分组
SELECT deptno,job,sum(sal) FROM emp
GROUP BY deptno,job ORDER BY 1; -- 先按deptno分组,相同的话再按job分组。
where和having
where
后面不能使用多行函数,having
后面可以使用多行函数。- 当在不使用多行函数时,
where
和having
关键字的用法一致。但是在相同都可用的情况下,优先使用where
关键字。因为where
关键字是执行在group by
之前,having
关键字执行在group by
之后。即先过滤再分组的效率是比先分组再过滤的效率高的。 break on deptno skip2;
break on deptno
,相同的部门只显示一次,skip 2
,不同的部门之间跳过两行。break on null;
恢复初始化。
多表查询
多表查询的目的:解决笛卡尔积的问题
内连接
- 等值连接,又称等值内连接
-- 隐式内连接(Oracle, MySQL都支持)
SELECT * FROM emp e,dept d WHERE e.deptno = d.deptno;
-- 显式内连接(所有符合SQL99规范的都支持)
SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
- 不等值连接,又称不等值内连接
-- 隐式语法
SELECT * FROM emp e,salgrade s WHERE e.sal >= s.losal AND e.sal <= s.hisal;
-- 显式语法
SELECT * FROM emp e INNER JOIN salgrade s ON e.sal >= s.losal AND e.sal <= s.hisal;
- 表的别名
- 使用别名可以简化查询;
- 使用表名前缀可以提高执行效率。 —
SQL
性能优化方案 - 在不同表中具有相同列名的列,可以用表的别名作为前缀来加以区分。
- **注意:**如果一旦使用了表的别名,则不能再使用表的真名。
外连接
- 左外连接
-- SQL99语法
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
-- oracle私有语法(MySQL不支持),+ 放到右边是左外
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno(+);
- 右外连接
-- SQL99语法
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;
-- oracle私有语法(MySQL不支持), + 放到左边是右外
SELECT * FROM emp e, dept d WHERE e.deptno(+) = d.deptno;
- 全外连接
-- 左外连接 (MySQL支持)
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno
UNION
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
-- oralce没有私有扩展的语法。而且,mysql没有全外
SELECT * FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;
- 自连接
-- 查询员工
SELECT * FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;
SELECT * FROM emp e1, emp e2 WHERE e1.mgr = e2.empno(+);
自连接是一种特殊的多表连接方式,其实含有内连接和外连接的操作,不适合操作大表(数据量大的表)。
- 层次查询
-- 层次查询
SELECT level,empno,ename,mgr FROM emp connect by prior empno = mgr
start with mgr is NULL ORDER BY 1;
可知,层次查询查出来的就是遍历下图中的数据数。自上而下,从1
开始计数。层次查询的要点在于它是单表查询,不会产生笛卡尔积,但是不是很直观。而自连接查询出来的结果可以很直观的显示我们所需要的数据结构。
子查询
子查询所要解决的问题:不能进一步求解出的问题。
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename= 'SCOTT');
- 子查询 (内查询) 在主查询之前一次执行完成。子查询的结果被主查询使用 (外查询)。
- 注意问题:
- 子查询语句的
()
不能丢。抒写格式虽然没有要求,但应尽量优雅规范。 - 可以在主查询
where, select, having, from
后面使用子查询; - 不可以在
group by
使用子查询; - 强调
from
后面的子查询; - 主查询和子查询可以不是同一张表;只要子查询返回的结果主查询可以使用即可。
- 一般不在子查询中排序,但是
top-n
分析问题中,必须对子查询排序; - 一般先查询子查询,再执行主查询,但相关子查询例外。
- 单行子查询只能使用单行操作符,多行子查询只能使用多行操作符;
- 子查询中的
null
,又可以细分为单行子查询中的null
和多行子查询的null
。
- 子查询语句的
- 单行子查询
- 顾名思义,子查询出来的是单行语句。但是主查询出来的不一定是单行语句,因为满足条件的不止一个。但是子查询出来的结果必定是单行的,否则就不能称之为单行子查询。
- 多行子查询
- 即子查询查询出来的结果不止一条,但是主查询出来的结果不一定是多行结果,因为可能满足条件的数据就只有一条。常见的有
in、any
和all
。in
关键字的用法,查询的结果是一个集合,当然该集合可能元素只有一个。any
关键字,取子查询结果集合中的任意一个元素且元素能够满足条件即可。all
关键字,取子查询结果集合中的任意一个元素且都能满足条件才可以。
- 即子查询查询出来的结果不止一条,但是主查询出来的结果不一定是多行结果,因为可能满足条件的数据就只有一条。常见的有
- 单行子查询的空值问题
- 如果单行子查询查询出来的是一个
null
,则子查询不返回任何行。
- 如果单行子查询查询出来的是一个
- 多行子查询的空值问题
- 集合中有
null
不可以使用not in
,但是可以使用in
关键字。所以在使用not in
查询时,如果子查询中可能会含有null
值,则应该在子查询中先过滤掉null
的数据,如下:
- 集合中有
SELECT * FROM emp WHERE emp NOT IN(SELECT mgr FROM WHERE mgr IS NOT NULL);
- 相关子查询:将主查询的值作为参数传递给子查询的查询,就是相关子查询。
SELECT empno,ename,sal,(SELECT avg(sal) FROM emp WHERE deptno = e.deptno) avgsal FROM emp e
WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno = e.deptno);
子查询和多表查询
- 在相同情况下,尽可能选择多表查询。
- 因为子查询会操作两次,多表查询只操作一次,多表的效率高。
伪列
- 伪列是
Oracle
中的一个虚拟的列; - 列的数据有
Oracle
进行维护和管理,用户不能对这个列进行修改,只能查看。 - 所有伪列要得到值必须要显式的指定。
ROWNUM行号
- 在查询操作时由
Oracle
为每一行记录自动生成一个编号。 - 每一次查询
ROWNUM
都会重新生成。(查询的结果中Oracle
给你增加的一个编号,根据结果来重新生成) ROWNUM
永远按照默认的顺序生成,不受ORDER BY
的影响。ROWNUM
只能使用< <=
,不能使用> >=
符号。因为,Oracle
是基于行的数据库,行号永远是从1
开始,即必须有第一行,才有第二行。ROWNUM
是由数据库自己产生的。ROWNUM
查询的时候自动产生的。
分页
- 多层嵌套子查询的方式,进行分页查询。因为
ROWRUN
伪列只能是< <=
,所以可以先查询,将ROWNUM
转化为表的一个列,而列是可以进行范围> >=
查询的。 - 写的时候从里到外来写,即先写小于的条件的子查询(过滤掉
rownum
大于指定值的数据),再写大于的条件的查询(过滤掉rownum
小于的值)。 Oracle
的分页如果需要排序显示,则要先排序、再分页。
SELECT *
FROM (SELECT rownum r,e1.*
FROM (SELECT * FROM emp ORDER BY sal) e1
WHERE rownum <= 8) e2
WHERE e2.r >= 5;
ROWID行记录编号
- 表的伪列,用来标识表中唯一的一条记录,记录着表行的地址,定位表行最快的一种方式。
- 主键: 标识唯一的一条业务数据的标识。主键是给业务给用户用的,不是给数据库用的。
- 记录编号
rowid:
标识唯一的一条数据的。主要是给数据库用的。类似UUID
。 - 可以通过
rowid
的值查询到数据。
SELECT rowid,empno,ename,sal FROM emp;
SELECT * FROM emp WHERE rowid = 'AAAMfPAAEAAAAgAAA';
ROWID
的产生- 使用
INSERT
语句插入数据时,Oralce
会自动生成rowid
并将其值与表数据一起存放到表行中。 ROWNUM
不是表中原本的数据,只是在查询的时候才生成的。rownum
默认的排序就是根据rowid
。
- 使用
ROWID
的作用- 去除重复的数据
- 与主键的区别:
- 主键:针对业务数据,用来标识不同的一条业务数据。
rowid:
针对具体数据的,用来标识不同的唯一的一条数据,跟业务无关。
数据处理
SQL数据类型
DML(Data Manipulation Language 数据操作语言): SELECT, INSERT, UPADTE, DELETE
DDL(Data Definition Language 数据定义语言): CREEATE table, ALTER table, TRUNCATE table, DROP table, CREATE/DROP view, sequence, index, synonym(同义词)
DCL(Data Control Language 数据控制语言): grant(授权), revoke(撤销权限)
INSERT插入语句
-
语法:
INSERT INTO table [(column[, column ...])] VALUES(value[, value ...]);
-
可以使用地址符
&
,类似于MySQL
中的preparedstatement
,预编译。
-
一次性插入多条数据的语句
- 首先创建一张表:
CREATE table emp10 as SELECT * FROM emp WHERE 1=2;
WHERE
条件不成立,则只拷贝过来表的结构,数据则不拷贝。
- 首先创建一张表:
-
从其它表中拷贝数据:在
INSERT
语句中加入子查询- 不必书写
VALUES
子句;子查询中的值列表应与INSERT
子句中的列名对应
- 不必书写
-
插入海量数据:数据泵(
PLSQL
程序),dbms_datapump(程序包)
;SQL*Loader
;外部表
创建表
- 语法:
CREATE table 表名 (column datatype [DEFAULT expr][, cloumn datatype ...]);
- 必须具备
CREATE table
权限、存储空间;必须指定表名、列名、数据类型、数据类型的大小。 - 复制表:
CREATE table 表名 as SELECT from 就表 条件;
条件不成立则只复制结构,条件成立则复制结构和值- 注意: 指定的列和子查询中的列要一一对应。通过列名和默认值定义列。
Oracle
的数据类型
修改表
- 增加新列:
ALTER table 表名 add 列名 列名数据类型;
- 修改列:
ALTER table 表名 modify 列名 列名类型;
- 删除列:
ALTER table 表名 drop column 列名;
- 重命名列:
ALTER table 表名 rename column 旧列名 to 新列名;
- 重命名表:
rename 旧表名 to 新表名;
删除表
- 删除表:
drop table 表名;
- 数据和结构都被删除,所有正在运行的相关事务都被提交。
(DDL语句)
。所有相关索引被删除。 DROP table
语句不能回滚,但是可以闪回。Oracle
删除表时,会将表放入回收站中。查看和清空回收站命令如下:
-- 查看回收站
show recyclebin;
-- 清空回收站
purge recyclebin;
- 在回收站的表,依然可以查询其表中的数据,这是该表的表名变了,要根据新表名。
- 注意: 回收站针对的是普通用户,管理员(身份)删除表时不经过回收站。
- 【补充】登录
oracle
数据库时,首先按照密码认证登录,即输入用户名/密码的方式登录;但是当用户名/密码有错或全错的时候,会采用第二种认证方式—主机认证。认证的权限就是管理员sys
的权限,即当前操作系统的登录人如果是数据库管理员则认证通过。 - 恢复表:闪回删除
flashback table TESTSAVEPOINT to before drop;
DELETE删除语句
DELETE
和TRUNCATE
的区别:DELETE
是DML
,可以回滚;TRUNCATE
是DDL
,不可以回滚。DELETE
逐条删除,TRUNCATE
直接先摧毁表,再重建;DELETE
不会释放空间,TRUNCATE
会释放空间;DELETE
会产生碎片,TRUNCATE
不会产生碎片;DELETE
可以闪回(flashback)
,TRUNCATE
不可以。
- 在
MySQL
数据库中,DELETE
执行效率比TRUNCATE
效率低;但是在Oracle
数据库中正好相反,原因在于Oracle
数据库有undo
(还原)数据的功能。
事务
- 事务是保持数据的一致性,它由相关的
DDL
或者DML
语句做为载体。 - 这组语句执行的结果要么一起成功,要么一起失败。
事务的特性
- 原子性
(Atomicity)
:一个事务里面所包含的SQL
语句是一个执行整体,不可分割,要么都成功,要么都失败 - 一致性
(Consistency)
:事务开始时,数据库中的数据是一致的,事务结束时,数据库中的数据也应是一致的 - 隔离性
(Isolation)
:多个事务可以并发的独立运行,并且不会互相干扰。 - 持久性
(Durability)
:事务被提交后,数据会被永久保存。
事务的生命周期
Oracle
的事务默认是手动管理事务,事务是自动开启(不需要显式的开启),但一般需要手动关闭或提交。Oracle
事务的开始和结束的触发条件:- 事务的开始:第一个
DML
语句(insert, update, delete)
的执行作为开始,即是自动开启事务。 - 事务的结束:
- 显式结束:
commit, rollback
- 隐式结束:
DDL(create table, alter table ....)
和DCL
,exit
事务正常退出。 - 隐式回滚:系统异常终止,如:关闭窗口,断电,死机等等。
- 显式结束:
- 事务的开始:第一个
事务的保留点
- 事务的过程是可以控制的,通过
SAVEPOINT
语句。 SAVEPOINT
的作用:- 使用
SAVEPOINT
语句在当前事务中创建保存点,语法:SAVEPOINT 保留点名称;
- 使用
ROLLBACK
语句回滚到创建的保存点。语法:ROLLBACK TO 保留点名称;
- 注意:当前事务提交后,事务中所有的保存点将被释放。即事务结束后,所有基于该事务的保留点全部会被清空。
- 使用
隔离级别
Oracle
只支持3
种事务隔离级别:READ COMMITED、SERIALIZABLE、READ ONLY;
- 而
Oracle
默认的事务隔离级别为:READ COMMITED;
约束
约束的概念和作用
- 约束是可以更好地保证数据库数据的完整性和一致性的一套机制。
- 约束可以限制加入表的数据的类型。
- 如果存在依赖关系,约束可以防止错误的删除数据,也可以级联删除数据。
- 总的来说,数据库的约束可以认为是对表的数据的一种规则。
约束的创建时机
- 创建表的时候,同时创建约束
- 表结构创建完成后,可以再添加约束。
常见的约束类型
NOT NULL:
不能为空;UNIQUE:
保证唯一;PRIMARY KEY:
主键;FOREIGN KEY:
外键;DEFAULT:
指定的默认值。- 并不是表初始化时字段的值,而是插入数据时,如果该字段没有赋值则默认为该指定的值。
CHECK:
用来检查一个字段值是否符合某表达式,表达式的结果必须是布尔值。
约束的应用选择
- 主键约束一般要设置,其他如非空、唯一、默认值、检查等约束,可以根据实际情况来添加。而外键约束是否要设置,是存在一点争议的。(争议在性能上)
- 在大型系统中(性能要求不高,安全要求高),可以使用外键;
- 在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便。
- 不用外键的话,可以用程序控制数据一致性和完整性,在代码的数据层通过代码来保证一致性和完整性。
- 用外键要适当,不能过分追求。
- 从
JAVA
开发的角度上说,一般不建议使用外键,除了性能外,使用程序控制业务更灵活。 - 比如客户和订单,这两个之间的关联虽然可以建立外键关系,实现级联效果(如级联删除)。
- 如果有外键约束,则删除客户的时候,必须先删除客户下的订单,否则,不允许删除。
- 从数据完整一致性的角度上说,如果客户被删除了,订单也无意义了,这是合理的。
- 但从业务角度上说,客户被删除了,是否意味这订单也必须删除呢?单纯保留订单的行为也是合理的。
数据库对象
序列 sequence
Oracle
中,主键没有自增长这一特性,需要通过创建序列来解决。
-
概念和作用
- 可供多个用户来产生唯一数值的数据库对象;
- 自动提供唯一的数据;
- 共享对象;
- 主要用于提供主键值,高效的生成主键值。
- 将序列值装入内存可以提供访问效率
-
创建序列:
CREATE SEQUENCE seq_test;
-
序列使用:在
Oracle
中为序列提供了两个伪列。NEXTVAL:
获取序列对象的下一个值(指针向前移动一个,并且获取到当前的值)。CURRVAL:
获取序列对象当前的值。- 注意:序列初始化之后指针在第一个数之前。必须先向前移动才可以查询的到。数组的指针默认在
1
之前,并没有指向第一个值,要想使用必须向前移动一下。(指针只能向前不能向后)
-
序列的应用:
- 插入数据时:
INSERT INTO TEST VALUES(seq_test.nextval,'kuraki');
- 注意:共享对象序列是个独立对象,谁都能用,所以是个共享对象。
- 插入数据时:
-
序列的裂缝
- 序列是一个共有对象,多个表都可以调用。但实际开发中,可以避免多个表用一个序列(创建多个序列)。序列是独立的对象。任意表都可以使用,但是编号就不能保证有序。
- 当插入记录时报错,序列对象值也被使用,下一次再使用时,序列的值就会
+1
。 - 用序列插入数据库的值不一定是连续的。
- 出现裂缝的条件:
- 事务回滚;
- 系统异常;
- 多个表同时使用同一个序列。
- 这个序列是公用的对象。如果你很在意的话,就一个表用一个序列,但大多数情况下,这个主键值(代理主键)没有什么意义的话,可以多个表公用一个序列。
用户和权限
- 创建用户
CREATE user 用户名
identified by 密码;
如果是字符不要急啊引号,如果是数字需要加引号"123456"
;default tablespace 默认表空间名 qutoa 10M on 表空间名
- 修改用户
alter user 用户名 identified by 密码 qutoa 10M on 表空间名;
alter user 用户名 account lock/unlock;
- 删除用户
drop user 用户名 [cascade];
如果要删除的用户中有模式对象,必须使用cascade
。
视图 view
- 概念
- 视图是一种虚表。视图建立在已有表的基础上,视图赖以建立的这些表称为基表。
- 向视图提供数据内容的语句为
SELECT
语句,可以将视图理解为存储起来的SELECT
语句。 - 视图是向用户提供基表数据的另一种表现形式。
- 删除视图只会删除视图的定义,并不会删除基表中的数据。
- 作用
- 限制数据的访问
- 简化复杂查询
- 提供数据的相互独立
- 同样的数据,可以有不同的显示方式
- 但是,视图不能提高性能。
- 创建/替换视图
CREATE OR REPLACE view empinfoview AS
SELECT e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname FROM emp e, dept d
WHERE e.deptno = d.deptno;
- 查询视图
-- 显示视图的详细信息
desc empinfoview;
-- 查询视图中存储的数据
SELECT * FROM empinfoview;
- 删除视图
DROP view empinfoview;
- 屏蔽
DML
操作,即只能只读
CREATE OR REPLACE view emoinfoview AS
SELECT e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname FROM emp e, dept d
WHERE e.deptno = d.deptno WITH READ ONLY;
- 视图中使用
DML
的规定- 当视图定义中包含以下元素之一时不能使用
DELETE
- 组函数,
GROUP BY
子句,DISTINCT
关键字,ROWNUM
伪列
- 组函数,
- 当视图定义中包含以下元素之一时不能使用
UPDATE
- 组函数,
GROUP BY
子句,ROWNUM
伪列,列的定义为表达式时
- 组函数,
- 当视图定义中包含以下元素之一时不能使用
INSERT
- 组函数,
GROUP BY
子句,ROWNUM
伪列,列的定义为表达式时,表中非空的列在视图定义时未包括。
- 组函数,
- 当视图定义中包含以下元素之一时不能使用
- 小结
- 视图是实体表的映射,视图和实体表的区别就是在于视图中没有真实数据的存在。
- 当有一些表结构是不希望过多的人去接触,就把实体表映射为一个视图。
- 对于一些复杂的
SQL
语句,设计人员会提前把这些语句封装到一个视图中,供程序人员去调用。 - 你查询的对象(表)他可能不是一张的表,可能是视图;你看到的视图的字段可能也不是真实的字段。
同义词 SYNONYM
- 概念
- 同义词就是别名,可以对表、视图等对象起个别名,然后通过别名就可以访问原来的对象了。
- 作用
- 方便访问,缩短了对象名字的长度
- 创建同义词:
CREATE [PUBLIC] SYNONYM 别名 FOR object;
- 关键字
PUBLIC
表示该同义词是否公有
- 关键字
-- 为视图dept_sum_vn创建同义词
CREATE SYNONYM d_sum FOR dept_sum_vn;
- 删除同义词
DROP SYNONYM d_sum;
索引 index
- 概念
- 索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低
I/O
次数,从而提高数据访问性能。它的作用就是提升查询效率。
- 索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低
- 特性
- 一种独立于表的数据库对象,可以存储在与表不同的磁盘或空间中。
- 索引被删除或损坏,不会对表(数据)产生影响,其影响的只是查询的速度。
- 索引一旦建立,
Oracle
管理系统就会对其进行自动维护,而且由Oracle
管理系统决定何时使用索引,用户不用在查询语句中指定使用哪种索引。 - 在删除一个表时,所有基于该表的索引会自动被删除。
- 如果建立索引的时候,没有指定表空间,那么默认索引会存储在所属用户默认的表空间中。
- 作用
- 通过指针(地址)加速
Oracle
服务器的查询速度。 - 提升服务器的
I/O
性能,减少了查询的次数。
- 通过指针(地址)加速
- 索引工作原理图
- 创建索引
- 自动创建:在定义
PRIMARY KEY
或UNIQUE
约束后系统自动在相应的列上创建唯一性索引。 - 手动创建:用户可以在其它列上创建非唯一的索引,以加速查询。
- 唯一索引的数据不重复,一般用于主键或唯一约束上创建。
- 其他普通的列的数据,一般可以重复,那么就不要创建唯一索引,创建一个非唯一、普通索引。
- 自动创建:在定义
-- 手动创建
CREATE INDEX 索引名 ON table (column[,column]...);
- 单列索引:基于单个列所建立的索引,
CREATE INDEX 索引名 ON 表名(列名);
- 复合索引:基于两个列或多个列的索引。在同一张表上可以有多个索引,但是要求列的组合必须不同
CREATE INDEX ename_job ON emp(ename,job);
CREATE INDEX ename_gender ON emp(ename,gender);
- 删除索引
DROP INDEX 索引名;
-
适用场景
- 以下场景可以创建索引
- 列中数据值分布范围很广;
- 列经常在
WHERE
子句或连接条件中出现 - 表经常被访问而且数据量很大,访问的数据大概占数据总量的
2%~4%
- 以下情况不适合建立索引
- 表很小
- 表经常更新
- 查询数据量不到
2%~4%
- 列不是经常在
WHERE
子句或连接条件中出现
- 以下场景可以创建索引
-
执行计划
Explain plan
Oracle
数据库中,SQL
语句的执行计划语句如下:
explain plan for SELECT * FROM emp WHERE deptno = 10;
- 查看执行计划的语句
SELECT * FROM table(dbms_xplan.display);
PL/SQL编程语言
概念
PL/SQL
是Oracle
对sql
语言的过程化扩展,指在SQL
命令语言中增加了过程处理语句(如分支、循环等),使SQL
语言具有过程处理能力。- 结合
SQL
语言的数据操纵能力与过程语言的数据处理能力,使得**PLSQL
面向过程**但比过程语言简单、高效、灵活和实用。
语法
PL/SQL
可以分为三个部分:声明部分、可执行部分、异常处理部分。
declare
-- 说明部分(变量说明,光标说明,异常说明)
begin
-- 语句序列(DML语句) ...
exception
-- 异常处理语句
end;
/
-
常量和变量的定义
- 普通数据类型
(char, varchar2, date, number, boolean, long)
DECLARE v_name VARCHAR(20) :='kuraki'; -- 声明的时候直接赋值 v_sal NUMBER; -- 薪资 v_local VARCHAR(200); -- 工作地点 BEGIN -- 开始程序逻辑,程序运行时赋值 v_sal :=9999; -- 直接赋值 SELECT '上海' INTO v_local FROM dual; -- 语句赋值 -- 输出打印 dbms_output.put_line('姓名:'||v_name||',薪资:'||v_sal||',工作地点:'||v_local); END;
- 引用型变量:引用表中字段的类型,推荐使用。
- 使用普通变量定义方式,需要知道表中列的类型,而使用引用类型,不需要考虑列的类型。
- 使用引用类型,当列中的数据类型发生改变,不需要修改变量的类型。而使用普通方式,当列的类型改变时,需要修改变量的类型
DECLARE v_ename emp.ename%TYPE; -- 姓名使用emp表中的ename字段的数据类型 v_sal emp.sal%TYPE; BEGIN -- 查询的结果必须只有一个值,不能有多行记录 SELECT ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7893; -- 输出打印 dbms_output.put_line('7893号员工的姓名是:'||v_ename||',薪资'||v_sal); END;
- 记录型变量:代表一行,可以理解为一个数组,里面元素时每一个字段值。如:
v_emp emp%rowtype;
v_emp
变量代表emp
表中的一行数据的类型,它可以存储emp
表中的任意一行数据。
DECLARE v_emp emp%rowtype; -- 该变量可以存储emp表中的一行记录 BEGIN SELECT * INTO v_emp FROM emp WHERE empno=7389; -- 输出打印 dbms_output.put_line('7839号员工的姓名是:'||v_emp.ename||',薪资'||v_emp.sal); END;
-
流程控制:顺序结构,条件结构,循环结构
- 条件分支
if
-- 1.第一种 IF 条件 THEN 语句1; 语句2; END IF; -- 2.第二种 IF 条件 THEN 语句1; ELSE 语句2; END IF; -- 3.第三种 IF 条件1 THEN 语句1 ELSEIF 条件2 THEN 语句2; ELSE 语句3; END IF;
--判断emp表中记录是否超过20条,,10-20之间,10以下打印一句 DECLARE --用来存储数量 v_count NUMBER; BEGIN --查询数量赋值 SELECT COUNT(1) INTO v_count FROM emp ; --判断 IF v_count>20 THEN -- 第一个判断条件 dbms_output.put_line('记录数超过20条:'||v_count); ELSIF v_count BETWEEN 10 AND 20 THEN -- 第二个判断条件 dbms_output.put_line('记录数在10到20条之间:'||v_count); ELSE -- 剩余情况 dbms_output.put_line('记录数不足10条:'||v_count); END IF; END;
- 循环:
PL/SQL
语言中也有三种循环语句,对应Java
中三种循环语句
-- 1.第一种 WHILE total <=2500; -- 对应Java中的while循环 LOOP .... total := total + salary; END LOOP; -- 2.第二种 LOOP -- 对应Java中的do...while循环 EXIT [WHEN 条件] .... END LOOP; -- 3.第三种 FOR I IN 1...3 -- 对应Java中的for循环 LOOP 语句序列 END LOOP;
-- 打印数字1-10 DECLARE v_number NUMBER :=1; -- 声明一个变量 BEGIN LOOP EXIT WHEN v_num > 10; --退出循环的条件 dbms_output.put_line(v_num); v_num := v_num + 1; -- 定增+1 END LOOP; END;
- 条件分支
-
游标
- 概念
- 游标
(Cursor)
,也称之为光标。从字面意思理解就是游动的光标。 - 游标是映射在结果集中一行数据上的位置实体。
- 游标是从表中检索出结果集,并从中每次指向一条记录进行交互的机制。
- 游标从概念上讲基于数据库的表返回结果集,也可以理解为游标就是个结果集,但该结果集是带向前移动的指针的,每次只指向一行数据。
- 游标
- 作用
- 用于临时存储一个查询返回的多行数据(结果集),通过遍历游标,可以逐行访问处理该结果集的数据。
- 使用方式:声明—>打开—>读取—>关闭
- 游标的声明
CURSOR 游标名 [(参数名 数据类型[,参数名 数据类型]...)] IS SELECT 语句 -- 示例 cursor c_emp is select ename from emp; -- 无参游标 cursor c_emp(v_deptno emp.deptno%TYPE) is select ename from emp where deptno = v_deptno; -- 有参游标
- 游标的打开
OPEN 游标名(参数列表); -- 示例 open c_emp; -- 打开游标执行查询
- 游标的取值
FETCH 游标名 INTO 变量列表|记录型变量 -- 示例 -- 取一行游标的值到变量中,注意:v_ename必须与emp表中的ename列类型一致 fetch c_emp into v_ename;
- 游标的关闭
CLOSE 游标名; -- 示例 close c_emp; -- 关闭游标,释放资源。
-
游标获取数据的基本原理
-
游标刚
open
的时候,指针结果集的第一条记录之前。游标不能回头。
-
游标与结果集的区别:游标是有位置的,
fetch
会向前移动,并获取游标位置上的内容。
-
-
游标的属性
游标的属性 返回值类型 说明 %ROWCOUNT
整型 获得 FETCH
语句返回的数据行数%FOUND
布尔型 最近的 FETCH
语句返回一行数据则为真,否则为假%NOTFOUND
布尔型 与 %FOUND
属性返回值相反%ISOPEN
布尔型 游标已经打开时值为 true
,否则为false
-
具体应用
- 引用变量获取游标的值
--使用游标查询emp表中所有员工的姓名和工资,并将其依次打印出来。 DELCARE -- 声明一个游标 CURSOR c_emp IS SELECT ename, sal FROM emp; -- 声明变量 v_ename emp.ename%TYPE; -- 姓名 v_sal emp.sal%TYPE; -- 工资 BEGIN -- 打开游标,执行查询 OPEN c_emp; -- 使用游标,循环取值 LOOP -- 获取游标的值放入变量的时候,必须要into前后对应(数量和类型) FETCH c_emp INTO v_ename,v_sal; -- 循环退出的条件 EXIT WHEN c_emp%NOTFOUND; -- 输出打印 dbms_output.put_line('员工的姓名:'|| v_ename || ',员工的工资' || v_sal); END LOOP; CLOSE c_emp; --关闭游标,释放资源 END;
- 使用记录型变量存值
--使用游标查询emp表中所有员工的姓名和工资,并将其依次打印出来。 DECLARE -- 声明一个游标 CURSOR c_emp IS SELECT * FROM emp; -- 声明一个记录型变量 v_emp emp%rowtype; BEGIN -- 打开游标 OPEN c_emp; -- 使用游标,循环取值 LOOP --获取游标的值放入变量的时候,必须要into前后要对应(数量和类型) FETCH c_emp INTO v_emp; -- 循环退出的条件 EXIT WHEN c_emp%NOTFOUND; dbms_output.put_line('员工姓名:'|| v_emp.ename||',员工工资'||v_emp.sal); END LOOP; CLOSE c_emp; -- 关闭游标,释放资源 END;
- 带参数的游标
-- 查询10号部门的员工的姓名和薪资 DECLARE -- 定义游标,带参数的游标 CURSOR c_emp(v_deptno emp.deptno%type) IS SELECT ename,sal FROM emp WHERE deptno = v.deptno; -- 声明变量 v_enmae emp.ename%type; v_sal emp.sal%type; BEGIN -- 打开游标 OPEN c_emp(10); -- 输入的参数不同,查询出来的结果集也不相同 -- 循环,遍历 LOOP FETCH c_emp INTO v_ename,v_sal; EXIT WHEN c_emp%NOTFOUND; -- 输出打印 dbms_output.put_line('姓名:'||v_ename||',薪资:'||v_sal); END LOOP; CLOSE c_emp; -- 关闭游标,释放资源 END;
- 概念
- 普通数据类型
存储过程
-
概念
- 指存储在数据库中供所有用户程序调用的子程序,叫做存储过程或者存储函数。一组为了完成特定功能的
SQL
语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 PLSQL
是存储过程的基础。Java
是不能直接调用PLSQL
的,但可以通过存储过程这些对象来调用。
- 指存储在数据库中供所有用户程序调用的子程序,叫做存储过程或者存储函数。一组为了完成特定功能的
-
作用
- 在开发程序中,为了一个特定的业务功能,会向数据库进行多次连接关闭(连接和关闭是很耗费资源)。这种就需要对数据库进行多次
I/O
读写,性能比较低。如果把这些业务放到PLSQL
中,在应用程序中只需要调用PLSQL
就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率。 ORACLE
官方给的建议:能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误,在程序中操作可能会存在错误。
- 在开发程序中,为了一个特定的业务功能,会向数据库进行多次连接关闭(连接和关闭是很耗费资源)。这种就需要对数据库进行多次
-
无参存储
- 创建存储
CREATE [OR REPLACE] procedure 存储过程名 IS/AS PLSQL 子程序体 -- 示例 CREATE OR REPLACE procedure sayhelloworld as BEGIN dbms_output.put_line('Hello World'); END;
- 调用存储过程
- 一种是用
EXEC
命令来调用;exec sayhelloworld();
- 一种是用其它的程序
(Java或PLSQL)
来调用。 - 注意:调用存储过程之前,要显式设置显示输出开启:
set serveroutput on;
- 一种是用
-
带输入参数存储
in
-- 给指定员工增加指定额度的工资
CREATE OR REPLACE procedure raiseSalary(empid in number, rate in number)
AS
psal emp.sal%type;
BEGIN
-- 查询该员工的资源,给psal赋值
SELECT sal INTO psal FROM emp WHERE empno = empid;
-- 给该员工涨工资
UPDATE emp SET sal = sal * rate WHERE empno = empid;
-- 打印涨工资前后的工资
dbms_output.put_line('员工号:'||empid||',涨工资前:'||psal||',涨工资后:'||psal * rate);
END;
- 带输入输出参数存储
-- 输入员工号查询某个员工(7839号(老大)员工)信息,要求,将薪水作为返回值输出,给调用的程序使用。
CREATE OR REPLACE procedure p_queryempsal_out(id IN emp.empno%type,o_sal OUT emp.sal%type)
AS
BEGIN
-- 赋值,将薪水的值赋给输出的参数o_sal
SELECT sal INTO o_sal FROM emp WHERE empno = id;
END;
-- 调用该存储过程
DECLARE
v_empno emp.empno%type :=7839; -- 输入参数值
-- 声明一个变量来接收输出参数
v_sal emp.sal%type;
BEGIN
-- 第二个参数是输出的参数,必须有变量来接收!!
p_queryempsal_out(v_empno,v_sal);
dbms_output.put_line('员工编号为:' || v_empno || '的薪资为:' || v_sal);
END;
- 总结
- 存储过程作用:主要用来执行一段程序
- 无参参数:只要用来做数据处理的。存储内部写一些处理数据的逻辑。
- 带输入参数:数据处理时,可以针对输入参数的值来进行判断处理。
- 带输入输出参数:一般用来传入一个参数值,经过数据库复杂逻辑处理后,得到想要的值然后输出。
- 存储过程作用:主要用来执行一段程序
存储函数
- 概念
- 为一个命名的存储程序。可以带参数,也可以不带参数,必须返回一个计算值。函数和过程的结构类似,但必须有一个
RETURN
子句,用于返回函数值。函数说明要指定函数名、结果值的类型以及参数类型等。
- 为一个命名的存储程序。可以带参数,也可以不带参数,必须返回一个计算值。函数和过程的结构类似,但必须有一个
- 使用:如果只有一个返回值,用存储函数;否则,就用存储过程。
CREATE OR REPLACE FUNCTION 函数名(参数列表) RETURN 数据类型
IS/AS
结果变量 数据类型
BEGIN
PLSQL 子程序体;
return 结果变量;
END [函数名];
过程和函数的区别
- 一般来讲,过程和函数的区别在于函数必须有一个返回值;而过程可以没有返回值。
- 过程和函数都可以通过
out
指定一个或多个输出参数。我们可以利用out
参数,在过程和函数中实现返回多个值。 - 一般优先选择使用存储过程
- 函数是必须有返回值,存储可以有也可以没有,存储的更灵活!
- 既然存储也可以有返回值,可以代替存储函数。
Oracle
的新版本中,已经不推荐使用存储函数了
Java调用过程和函数
封装一个连接数据库工具类
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@192.168.25.130:1521/orcl";
private static String user = "scott";
private static String password = "tiger";
// 注册驱动
static {
try {
Calss.forName(driver);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, passworld);
} catch () {
e.printStackTrace();
}
}
public static void release(Connection conn, Statement st, ResultSet rs) {
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs = null; ///-----> 原因:Java GC: Java的GC不受代码的控制
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
}
}
调用存储过程
public void testProcedure() {
String sql = "{call queryEmpInformation(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 对于in参数,赋值
call.setInt(1, 1002);
// 对于out参数,声明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
// 执行
call.execute();
// 输出
String name = call.getString(2);
double price = call.getDouble(3);
String job = call.getString(4);
System.out.println(name + "\t" + price + "\t" + job);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
调用存储函数
public void testFunction() {
String sql = "{?=call qeryEmpIncome(?)}";
Connection conn = null;
CallableStatemeng call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 设置in 输入参数
call.setInt(2, 1002);
// 声明输入参数
call.registerOutParameter(1, OracleTypes.NUMBER);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
包结构和包体
- 作用:解决
out
参数太多; 查询某个部门中的所有员工信息------>返回一个集合即可 - 声明包结构
CREATE OR REPLACE package 包名 as/is
type 光标名 is ref cursor;
procedure 存储过程;
end[包名];
-- 创建包头
create or replace package mypackage is
type empcursor is ref cursor;
procedure queryEmpList(dno in number, empList out empcursor);
end mypackage;
-- 创建包体
create or replace package body mypackage is
procedure queryEmpList(dno in number,empList out empcursor) as
BEIGIN
open empList for select * from emp where deptno = dno;
END;
end mypackage;
-- 查看包
desc mypackage;
调用带有游标类型参数的存储过程
public void testCursor() {
String sql = "{call mypackage.QUERYEMPLIST(?,?)}";
Conncetion conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
// 对于in参数,赋值
call.setInt(1, 20);
// 对于out参数,声明
call.registerOutParameter(2, OracleTypes.CURSOR);
// 执行
call.excute();
// 取出结果
rs = ((OracleCallableStatement) call).getCursor(2);
while (rs.next()) {
// 取出一个员工
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(ename + "\t" + sal);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, rs);
}
}
触发器
-
概念:
- 数据库触发器是一个与表相关联的、存储的
PL/SQL
程序。每当一个特定的数据操作语句(Insert, update, delete)
在指定的表上发出时,Oracle
自动地执行触发器中定义的语句序列。
- 数据库触发器是一个与表相关联的、存储的
-
语法
CREATE [OR REPLACE] trigger 触发器名
{before|after} {DELETE|INSERT|UPDATE[OF 列名]} -- 加of指定列名时,修改时触发器才会被触发
on 表名
[FOR EACH ROW[WHEN(条件)]]
PLSQL 语句块
-- 示例
create or replace trigger firsttriger
after insert
on emp
delcare
begin
dbms_output.put_line('成功插入新员工');
end;
-- 当向表emp中插入一条语句时,将会执行"打印输出'成功插入新员工'"。
-
触发器的类型
- 语句级触发器
(表级触发器)
:在指定的操作语句操作之前或之后执行一次,不论这条语句影响了多少行 - 行级触发器:每一条记录都被触发。在行级触发器中使用
old
和new
伪记录变量,识别值的状态。 - 二者的区别:
- 在语法上,行级触发器就多了一句话:
for each row
- 在表现上,行级触发器,在每一行的数据进行操作的时候都会触发。
- 但语句级触发器,对表的一个完整操作才会触发一次。
- 简单的说:行级触发器,是对应行操作的;语句级触发器,是对应表操作的。
- 在语法上,行级触发器就多了一句话:
- 使用注意
- 触发器会引起锁,降低效率!使用时要慎重。如无必要,尽量不要使用触发器。
- 行级触发器会引发行级锁(锁行数据)
- 语句级触发器可能会引起表级锁(锁表)
- 触发语句和伪记录变量的值
触发语句 :old
:new
Insert
所有字段都是空 (null)
将要插入的数据 Update
更新以前该行的值 更新后该行的值 Delete
删除以前该行的值 所有字段都为空 (null)
- 使用场景
- 实施复杂的安全性检查,如在指定工作时间内才允许进行
DML
操作。 - 数据确认,如涨前的薪水不能低于涨后的薪水
- 做审计,跟踪表上所做的数据操作等
- 数据的备份和同步。
- 实施复杂的安全性检查,如在指定工作时间内才允许进行
- 语句级触发器
MySQL
SQL分类
- 数据定义语言
(DDL,Data Definition Language):
用来定义数据库对象,数据库、表、列名等。关键字:drop、create、alter
- 数据操作语言
(DML,Data Manipulation Language):
用来对数据库中的表的记录进行更新。关键字:insert、delete、update
- 数据控制语言
(DCL,Data Control Language):
用来定义数据库的访问权限和安全级别,以及创建用户。 - 数据查询语言
(DQL,Data Query Language):
用来查询数据库中表的记录。关键字:select
数据库操作
创建数据库
create database 数据库名;
采用数据库默认的编码create database 数据库名 character set 字符集;
创建数据库时指定其编码集
查看数据库
show databases;
查看所有数据库名show create database 数据库名;
查看某个数据库定义的信息
删除数据库
drop database 数据库名;
其它数据库操作
use 数据库名;
切换数据库select database();
查看正在使用的数据库
数据表操作
创建表
-- 1. 第一种方式
create table 表名(字段名 类型(长度) 约束[,字段名 类型(长度) 约束]...);
-- 2. 第二种方式
create table 表名(字段名 类型(长度) 约束[,字段名 类型(长度) 约束]...,primary key(主键字段名));
查看表
show tables;
查看某数据库中所有的表desc 表名;
查看表结构
删除表
drop table 表名;
连表带数据,一并删除truncate table 表名;
先将表删除,然后重新创建一个新表
修改表结构格式
- 表中追加列:
alter table 表名 add 列名 类型(长度) 约束;
- 修改列的类型长度及约束:
alter table 表名 modify 列名 类型(长度) 约束;
- 修改列的名字,类型长度及约束:
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
- 删除列:
alter table 表名 drop 列名;
- 修改表的字符集:
alter table 表名 character set 字符集;
- 修改表名:
rename table 旧表名 to 新表名;
增删改查
insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3...);
向指定列添加数据insert into 表名 values(值1,值2,值3...);
添加所有列的数据- 插入的数据应与字段的数据类型相同,数据的大小应该在列的长度范围内;
- 在
values
中列出的数据位置必须与被加入列的排列位置相对应。 - 除了数值类型外,其它的字段类型的值必须使用单引号引起;
- 如果要插入空值,可以不写字段,或者插入
null
。 - 对于自动增长的列在操作时,直接插入
null
值即可。
update 表名 set 字段名=值,字段名=值;
修改表中指定字段的所有值update 表名 set 字段名=值,字段名=值 where 条件;
修改指定条件成立下的某行数据该字段的值- 值如果是字符串或者日期需要加一对单引号;
- 列名的类型要与修改的值要一致,修改的值不能超过最大长度。
delete from 表名 where 条件;
删除表中满足条件的数据delete from 表名;
删除整张表- 数据可以恢复,可以找回,可以有条件的删除。
select 字段1,字段2,... from 表名;
select distinct 字段 from 表名 [where 条件];
过滤掉重复的字段select * from 表名 [as] 别名;
给表取别名select 字段名 [as] 别名 from 表名;
给字段取别名
SQL语句执行顺序
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
FROM <left_table>:
第一步,先执行FROM
关键字的语句,从哪张表开始查询ON <join_condition>:
第二步,执行ON
关键字的语句,与外表连接的条件<join_type> JOIN <right_table>:
第三步,执行JOIN
连接部分的语句,与哪张表进行何种类型的连接WHERE <where_condition>:
第四步,执行WHERE
关键字(过滤条件)
GROUP BY <group_by_list>:
第五步,执行GROUP BY
分组的语句HAVING <having_condition>:
第六步,执行分组后条件过滤SELECT:
第七步,执行需要查询的字段DISTINCT <select_list>:
第八步,如果这里有DISTINCT
,过滤掉重复的字段值。ORDER BY <order_by_condition>:
第九步,执行排序LIMIT <limit_number>:
第十步,分页
SQL聚合函数
count
函数:select count(*)/count(具体列名) from 表名;
- 如果该列中有
null
,则该条数据不被统计再内。
- 如果该列中有
sum
函数:select sum(列名),sum(列名),... from 表名;
- 使用
sum
多列进行求和时,如果某一列中含有null
,这一列所在行中的其他数据不会被加载到总和中。 null
值和任何值相加仍然为null
。- 使用数据库提供的函数**
ifnull(列名, 默认值)
来解决上述问题。**ifnull(列名, 默认值)
函数表示判断该列名是否为null
;如果为null
,返回默认值,如果不为null
,返回实际的值。ifnull(age,0)
:age
列的值是null
,返回值是0
- 使用
avg
函数:select avg(列名) from 表名;
- 如果某列值为
null
,则该列不被计算在内。
- 如果某列值为
max、min
函数:select max(列名)/min(列名) from 表名;
null
排除在外
多表设计
- 概念
- 数据之间必然会有一定的联系,我们把不同的数据保存在不同的数据表中之后,同时还要在数据表中维护这些数据之间的关系。这时就会导致表和表之间必然会有一定的联系。
- 表与表之间的关系:一对多(多对一)、一对一(极少)、多对多关系。
多对多关系
E(Entity)-R(relation)
图,又称实体关系图- 一个
Java
类,可以对应数据库中的一张表。一个Java
对象,可以对应表中的一行,而Java
中类的属性,可以对应表中的字段。 - 在
E-R
图中:一张表,可以称为一个实体,使用矩形表示,每个实体的属性(字段,表的列),使用椭圆表示。表和表之间的关系,使用菱形表示。
- 一个
外键约束
- 中间表:创建第三张关系表即中间表,来维持两张表之间的关系。
- 中间表插入的数据,必须在多对多的主表中存在。
- 如果主表的记录在中间表维护了关系,就不能随意删除。如果删除,中间表就找不到对应的数据,那么就没有意义了。
- 语法:
foreign key(当前表中的列名) references 被引用表名(被引用表的列名);
-- 1. 第一种语法
alter table coder_project add foreign key(coder_id) references coder(id);
alter table coder_project add foreign key(project_id) references project(id);
-- 2. 第二种语法
create table coder_project(
coder_id int,
project_id int,
foreign key(coder_id) references coder(id),
foreign key(project_id) references project(id)
);
- 注意问题
- 如果从表(引入字段的表,
coder_project
)要去添加一个外键约束。要求主表(被引用字段的那个表,coder
或者project
)被引用的字段是主键或者唯一的。通常使用主键。 - 如果要删除主表中的数据。要求在从表中这个数据,要没有被引用,才可以去删除。
- 如果要向从表中去添加数据。要求在主表中,要有对应的数据。才可以去添加。
- 如果要删除表。要先删除从表。然后去删除主表。
- 新建表的时候。需要先去创建主表,然后去创建从表。
- 外键约束作用:保持数据的完整性,和有效性。
- 如果从表(引入字段的表,
数据库设计
数据规范式
- 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式。而范式是用来规范数据库的,并且来优化数据的设计和存储。各种范式呈递增次规范,越高的范式数据库冗余越小。也就是说性能越好。
- 关系数据库有六种范式:第一范式
(1NF)
、第二范式(2NF)
、第三范式(3NF)
、巴斯-科德范式(BCNF)
、第四范式(4NF)
和第五范式(5NF,又称完美范式)
。满足最低要求的范式是第一范式(1NF)
。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)
,其余范式依次类推。一般说来,数据库只需满足第三范式(3NF)
就行了。
第一范式
- 数据库表的每一列都是不可分割的原子数据项。即**表中的某个列有多个值时,必须拆分为不同的列。**直到不能拆分为止。
- 简而言之,第一范式每一列不可再拆分,称为原子性。
第二范式
- 在满足第一范式的前提下,表中的每一个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。
- 简而言之,第二范式就是在第一范式的基础上所有列完全依赖于主键列。
- 一张表只描述一件事情。
- 表中的每一列都完全依赖于主键
第三范式
- 在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其它的列来间接依赖于主键。
- 简而言之,第三范式就是所有列不依赖于其它非主键列,也就是在满足
2NF
的基础上,任何非主键列不得传递依赖于主键。 - 所谓传递依赖,指的是如果存在
A → B → C
的决定关系,则C
传递依赖于A
。 - 因此,满足第三范式的数据库表应该不存在如下依赖关系:主键列 → 非主键列x → 非主键列y。
小结
- 1NF 原子性:表中每列不可再拆分。
- 2NF 不产生局部依赖,一张表只描述一件事情。表中的每一列是完全依赖于主键的。
- 3NF 不产生传递依赖,表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。
多表查询
内连接查询
- 隐式内连接查询
SELECT 列名,列名... FROM 表名1,表名2 WHERE 条件;
- 显式内连接查询
SELECT 列名,列名... FROM 表名1 INNER JOIN 表名2 ON 条件;
外连接查询
- 左外连接
SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 条件;
- 右外连接
SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;
- 全外连接
SELECT * FROM 表1 FULL OUTER JOIN 表2 ON 条件;
-- mysql不支持全外连接,但可以通过如下操作实现全外连接
SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 条件;
UNION
SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;
-- UNION 和 UNION ALL 区别
UNION ALL 不去除重复的部分
SQL关联子查询
-
子查询:把一个sql的查询结果作为另外一个查询的参数存在。
-
in
的用法:SELECT 列名 FROM 表名 WHERE a.age in();
例如:age=24 or age=25 等价于 age in(24,25);
-
exists
的用法:如果子查询有返回数据行,就认为true
,否则就认为false
。即exists
后面的判断条件可以是一个范围。SELECT * FROM 表名 WHERE exists(SELECT ... FROM 表名 WHERE 条件);
exists
的查询方式:将外表中的数据逐行拿到内表中去判断条件是否成立,如果成立返回该行数据。如果不成立,丢弃该行数据。显示的是外表的数据。
-
in
和exists
的区别:select * from A where id in(select id from B);
in
先查询的是内表,即先进行子查询。所以in
适合B
表比A
表数据小的情况。select * from A where exists(select 1 from B b where a.id=b.id);
exists
先查询外表,即先进行主查询。所以exists
适合B
表比A
表数据大的情况。
-
not in
和not exists
的区别- 如果查询语句使用了
not in
那么内外表都进行全表扫描,没有用到索引;而not extsts
的子查询依然能用到表上的索引。所以无论那个表大,用not exists
都比not in
要快。
- 如果查询语句使用了
-
all
用法:**表示所有,和union
一起使用。**如果在查询时,单独使用union
可以把多个查询的结果进行合并, 会过滤掉重复的数据。a > all(1,2,3) 等价于 a>1 and a>2 and a>3 等价于 a > max(1,2,3);
a < all(1,2,3) 等价于 a<1 and a<2 and a<3 等价于 a < min(1,2,3);
-
any
和some
的使用any
和some
是没有区别的,some
和any
效果一样 ,代表一部分记录。a > any(1,2,3) 等价于 a > min(1,2,3);
a < any(1,2,3) 等价于 a < max(1,2,3);
-
as
的使用- 不仅可以用来做列的别名,还可以将查询结果通过
as
作为一张表来使用。
- 不仅可以用来做列的别名,还可以将查询结果通过
-
limit
的使用- 语法:
SELECT * FROM 表名 LIMIT offset, row_count;
- 例如:
SELECT * FROM 表名 1,4;
1 表示索引,这里的索引从0开始;4 表示查询记录数。
- 语法:
Oracle和MySQL区别
使用区别
Oracle
没有offset, limit
,在mysql
中用它们来控制显示的行数,用得最多的就是分页了。oracle
要分页的话,要换成rownum
。Oracle
建表时,没有auto_increment
,所有要想让表的一个字段自增,要自己添加序列,插入时,把序列的值,插入进去。oracle
有一个dual
表,当select
后没有表时须加上它才不会报错。select 1
这个在mysql
不会报错的,oracle
下会。select 1 from dual
这样的话,oracle
就不会报错了。oracle
下对单引号,双引号要求的很死,单双引号有不同的语义,MySQL
下基本可以互用。oracle
有to_number,to_date
这样的转换函数,oracle
表字段是number
型的,如果你$_POST
得到的参数是123456
,入库的时候,还要to_number
来强制转换一下,不然后会被当成字符串来处理。而mysql
不会。mysql
的用户权限管理,是放到mysql
自动带的一个数据库mysql
里面的,而**oracle
是用户权限是跟着表空间走的**。- 在下
oracle
下用group by
的话,group by
后面的字段必须在select
后面出现,不然会报错的,而mysql
却不会。 mysql
存储引擎有好多,常用的mysiam,innodb
等,而创建oracle
表的时候,不需如此。oracle
字段无法选择位置,alter table add column before|after
,这样会报错的。oracle
的表字段类型也没有mysql
多,并且有很多不同。oracle
查询时from
表名后面不能加上as
不然会报错的,select t.username from test as t
在mysql
下是可以的。- 两种数据库自带的系统函数也不尽相同。
Oracle
自动开启事务,MySQL
手动开启事务;
性能差异
- 并发性:并发性是oltp数据库最重要的特性,但并发涉及到资源的获取、共享与锁定。
mysql
以表级锁为主,对资源锁定的粒度很大,如果一个session
对一个表加锁时间过长,会让其他session
无法更新此表中的数据。虽然InnoDB
引擎的表可以用行级锁,但这个行级锁的机制依赖于表的索引,如果表没有索引,或者sql
语句没有使用索引,那么仍然使用表级锁。oracle
使用行级锁,对资源锁定的粒度要小很多,只是锁定sql
需要的资源,并且加锁是在数据库中的数据行上,不依赖于索引。所以oracle
对并发性的支持要好很多。
- 一致性
oracle
支持serializable
的隔离级别,可以实现最高级别的读一致性。每个session
提交后其session
才能看到提交的更改。oracle
通过在undo
表空间中构造多版本数据块来实现读一致性,每个session
查询时,如果对应的数据块发生变化,oracle
会在undo
表空间中为这个session
构造它查询时的旧的数据块。mysql
没有类似oracle
的构造多版本数据块的机制,只支持read commited
的隔离级别。一个session
读取数据时,其他session
不能更改数据,但可以在表最后插入数据。session
更新数据时,要加上排它锁,其他session
无法访问数据。
- 事务
oracle
很早就完全支持事务。mysql
在innodb
存储引擎的行级锁的情况下才支持事务。
- 数据持久性
oracle
保证提交的数据均可恢复,因为oracle
把提交的sql
操作线写入了在线联机日志文件中,保存到了磁盘上,如出现数据库或主机异常重启,重启后oracle
可以拷联机在线日志恢复客户提交的数据。mysql
默认提交sql
语句,但如果更新过程中出现宕机或主机重启的问题,也许会丢失数据。
- 提交方式
oracle
默认不自动提交,需要用户手动提交。mysql
默认是自动提交。
- 逻辑备份
oracle
逻辑备份时不锁定数据,且备份的数据是一致的。mysql
逻辑备份时要锁定数据,才能保证备份的数据是一致的,影响业务正常的DML
使用。
- 热备份
oracle
有成熟的热备工具rman
,热备时,不影响用户使用数据库。即使备份的数据库不一致,也可以在恢复时通过归档日志和联机重做日志进行一致的回复。myisam
的引擎,用mysql
自带的mysqlhostcopy
热备时,需要给表加读锁,影响DML
操作。innodb
的引擎,它会备份innodb
的表和索引,但是不会备份.frm
文件。用ibbackup
备份时,会有一个日志文件记录备份期间的数据变化,因此可以不用锁表,不影响其他用户使用数据库。但此工具是收费的。
innobackup
是结合ibbackup
使用的一个脚本,他会协助对.frm
文件的备份。
SQL
语句的扩展性和灵活性mysql
对sql
语句有很多非常实用而方便的扩展,比如limit
功能,insert
可以一次插入多行数据,select
某些管理数据可以不加from
。oracle
在这方面感觉更加稳重传统一些。
- 复制
oracle:
既有推或拉式的传统数据复制,也有dataguard
的双机或多机容灾机制,主库出现问题是,可以自动切换备库到主库,但配置管理较复杂。mysql:
复制服务器配置简单,但主库出问题时,丛库可能丢失一定的数据。且需要手工切换丛库到主库
- 性能诊断
oracle
有各种成熟的性能诊断调优工具,能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof
等。mysql
的诊断调优方法较少,主要有慢查询日志。
- 权限和安全
mysql
的用户与主机有关,感觉没有什么意义,另外更容易被仿冒主机及ip
有可乘之机。oracle
的权限与安全概念比较传统,中规中矩。
- 分区表和分区索引
oracle
的分区表和分区索引功能很成熟,可以提高用户访问db
的体验。mysql
的分区表还不太成熟稳定。
- 管理工具
oracle
有多种成熟的命令行、图形界面、web
管理工具,还有很多第三方的管理工具,管理极其方便高效。mysql
管理工具较少,在linux
下的管理工具的安装有时要安装额外的包(phpmyadmin, etc)
,有一定复杂性。