下面一些演示图像为mysql 的 workbench，因为有些语法基本相同，就用mysql的查询做下演示，其中world为schema的名字（oracle不用直接表名即可，不用schema.table），目前学习阶段自己的电脑上没装成功oracle，但是不耽误学习。
文章目录
Oracle
介绍
Oracle数据库系统是美国ORACLE公司提供的以分布式数据库为核心的一组软件产品，是目前最流行的C/S,B/S体系结构的数据库之一。
基本概念
数据库
Oracle数据库是数据的物理存储，这就包括(数据文件ORA或者DBF、控制文件、联机日志、参数文件)。其实Oracle数据库的概念和其他数据库不一样，这里的数据库是一个操作系统只有一个库，可以看作是Oracle就只有一个大数据库。
实例
一个Oracle实例有一系列的后台进程和内存结构组成，一个数据库可以有n个实例。
用户
用户是在实例下建立的，不同的实例下可以有相同名字的用户
表空间
表空间是Oracle对物理数据库上相关数据文件(ORA或者DBF文件)的逻辑映射，一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构，每个数据库至少有一个表空间(称之为system表空间)。实际上就是存储数据的物理文件。
每个表空间由同一磁盘上的一个或者多个文件组成，这些文件叫数据文件(datafile)，一个数据文件只能属于一个表空间
总结：
- Oracle数据库:是一个文件系统，是物理概念
- 实例:在Oracle的数据库中可以有多个实例，通常我们只用一个实例
- 用户:一个实例下可以有多个用户
- 表空间: 一个实例下可以多个表空间，表空间是逻辑概念，一个表空间对应着一个或者多个物理存储文件(.dbf/.ora文件)
- 关系:一个用户有一个默认的表空间，一个表空间可以为多个用户作为默认表空间，用户和用户之间的数据是隔离的，数据不会混在一起
- Oracle数据库源
driver: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@localhost:1521.ord(ord实例名)
username: xxxx
password: xxxx
常用字段解释
Number(X): 最大长度为X的数值类型
Vachar2(X): 最大长度为X的字符串，varchar2用于存储可变长度的字符串，varchar2把所有字符都占两个字节处理(一般情况下)，varchar只对汉字和全角等字符占两个字节，数字英文字符都是一个字节，varchar2把空串等同于null处理，而varchar仍按照空字符串处理，建议在oracle中使用varchar2
Number(x,y): 数值类型整数位占x-y位，小数位y位，一共x位
Date：时间类型
SQL
简介
结构化查询语言简称sql，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方式，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
DML(数据库操作语言)：其语句包括动词 INSERT, UPDATE和DELETE。他们分别用于添加，修改和删除表中的行。也称为动作查询语言。
DLL（数据库定义语言）：其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE或DROP TABLE）为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字，它也是动作查询的一部分。
DCL（数据库控制语言）：它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
简单查询
查询语句
全查
Select * | 列名 from 表名
查询某几列
select 列名,列名... from 表名
别名用法
在查询的结果列中可以使用别用
select 列名 as '别名', 列名 as '别名'... from 表名
常用:
select 列名 别名,列名 别名... from 表名
数据去重
select distinct *|列名,... form 表名
使用distinct可以消除重复的行，如果查询多列的必须保证多列都重复才能去掉重复
字符串连接查询
字符串的连接使用||
select '编号是:' || empno || '的雇员，姓名是:' || ename form 表名
查询中四则运算
sql中支持四则运算+ - * /
查询每个雇员的年薪(sal*12)
select ename,sal*12 from emp
限定查询
在查询绝大数都有有条件的限制
select *|列名 form 表名 where 条件
非空和空的限制
实例：查询每月能得到奖金(comm)的雇员
分析：只要字段中存在内容表示不为空，如果不存在内容就是null
语法：
列名 is not null
列名 is null
语句：
select * from emp where comm is not null
AND关键字
分析：多个查询条件同时满足之间使用 AND
OR关键字
多个查询条件或满足，条件之间使用 OR
NOT 关键字
不满足此条件的使用NOT
Between and 关键字
例：基本工资(sal)大于等于1500小于等于3000
分析：Between and 等于 sal >= 1500 and sal <= 3000
select * from emp where sal between 1500 and 3000
注意：
sql语句是不区分大小写的，但是表中的值是区分大小写的
select * from emp where ename="SMITH" // "smith"是不一样的
IN 关键字
之前如果查询雇员比那好是7369，7499，7521的雇员编号(empno)的具体信息，可以用OR关键字。实际上，此时指定了查询范围，那么sql可以使用in 关键字
语法：
列名 IN (值1，值2...)
列名 NOT IN (值1，值2....)
其中的值不仅是数值类型也是字符串
select * from emp where empno in (7369, 7499, 7521)
Like关键字
在常用的站点中经常会有模糊查询，即：输入一个关键字，把符合的内容全部都查询出来，在sql中使用LIKE语句完成
在LIKE中主要使用一下两种通配符
% ：可以匹配任意长度的内容
_ ：可以匹配一个长度的内容
例：查询出所有雇员姓名（ename）中第二个字符包含“M”的雇员
select * from emp where ename like '_M%'
非’<>‘或’!=’
在oracle中不等号的用法 可以有两种形式“<>”和“!=”
例：查询雇员编号（empno）不是7369的雇员信息
select * from emp where empno <> '7369'
对结果集排序
在sql中可以使用order by对查询结果进行排序
语法：
select *| 列名 from 表名 {where 查询条件} order by 列名1 ASC|DESC...
例：查询雇员的工资(sal)从低到高
分析：order by 列名 默认的排序规则是升序排列，可以不指定ASC，如果是按照降序排列必须指定DESC
select * from emp order by sal
例：查询雇员的工资工资(sal)倒序，入职日期(hiredate)也倒叙,先入职的在前面
select * from emp order by sal desc, hiredate desc
单行函数
字符函数
接收字符输入返回字符或者数值，dual是伪表
-
把小写的字符转换成大写的字符
upperselect upper('smith') from dual // SMITH -
把大写的字符变成小写的字符
lowerselect lower('SMITH') from dual // smith -
首字母大写
initcapselect initcap('smith') from dual // Smith -
字符串的连接
concat也可以使用||建议使用||select concat('hello', 'world') from dual // helloworld -
字符串的截取，使用
substr，第一个参数是源字符串，第二个参数是开始索引，第三个参数结束的索引，开始的索引使用1和0效果相同select substr('hello', 0, 3) from dual // hel -
获取字符串的长度
lengthselect length('hello') from dual // 5 -
替换字符串
replaceselect replace('hello', 'l', 'x') from dual // hexxo
数值函数
-
四舍五入函数
round(x, y)第二个参数可填为保留几位select round(12。534) from dual // 13 -
取整
trunc()默认去掉小数，也可以指定保留的位数select trunc(12.536, 2) from dual // 12.53 -
取余
MOD()select mod(10， 3) from dual // 1
日期函数
oracle中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 - 数字 = 日期
日期 + 数字 = 日期
日期 - 日期 = 数字（天）
-
查询雇员的进入公司的周数
分析: 查询雇员进入公司的天数（sysdate - 入职日期）/7 就是周数
select truncate(sysdate() - hiredate)/7 from emp -
获得两个时间段中的月数：
MONTHS_BETWEEN()查询所有雇员进入公司的月数
select ename, round(months_between(sysdate, hiredate)) from emp -
获得几个月后的日期:
add_months()也可加上负数为减去多个少月以后的日期为多少
12月后的日期
select add_months(sysdate, 12) from dual -
指定给出下次某日期数
NEXT_DATE()求出下一个星期一是什么日期
select next_day(sysdate, '星期一') from dual -
求出一个日期的最后一天
last_day()求出本月的最后一天是几号
select last_day(sysdate) from dual
转换函数
-
TO_CHAR：字符串转换函数TO_CHAR函数拆分，拆分时需要使用通配符年： y 年是四位使用yyyy
月：m 月是两位使用mm
日：d 日是两位使用dd
时：h 时 HH是十二进制，HH24是24进制
分： mi 分
秒：ss 秒
查询所有的雇员的入职日期
hiredate将年月日分开，此时可以使用TO_CHARselect empno, ename, to_char(hiredate, 'yyyy') 年, to_char(hiredate, 'mm') 月, to_char(hiredate, 'dd') 日, to_char(hiredate, 'HH') 时, to_char(hiredate, 'mi') 分, to_char(hiredate, 'ss') 秒 from emp将现在的日期以指定的格式做展示
select to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') from dual // 24进制 select to_char(sysdate, 'yyyy-mm-dd HH:mi:ss') from dual // 12进制查询雇员信息，列指定为雇员编号
empno雇员姓名ename指定的日期格式select empno, ename, to_char(hiredate, 'yyyy-mm-dd') from emp在结果中10以下的月前面被补了前导零，可以使用
fm去掉select empno, ename, to_char(hiredate, 'fmyyyy-mm-dd') from emp不只是可以分割日期，也可以分割数字
将雇员工资
sal以三位来表示select ename, to_char(sal, '99,999') from emp | ename |to_char(sal, '99,999') | smith |1,600如果在钱的前面加上国家的符号可以使用
$代表的是美元，如果要使用本地的钱的单位使用Lselect ename, to_char(sal, '$99,999') from emp | ename |to_char(sal, '$99,999') | smith |$1,600 select ename, to_char(sal, 'L99,999') from emp | ename |to_char(sal, 'L99,999') | smith |￥1,600 -
TO_NUMBER：数值转换函数可以把字符串转化成数值（字母符号无效）
select to_number('10') + to_number('10') from dual // 20 -
TO_DATE：日期转换函数（用的较多）可以把字符串的数据转换成日期类型
select to_date('1985-04-22', 'yyyy-mm-dd') from dual // 1985/4/22 星期一 select to_date('1985-04-22 12:30:20', 'yyyy-mm-dd HH24:mi:ss') from dual // 1985/4/22 星期一 下午12:30:20
通用函数
-
空值处理
nvl例：查询所有雇员的年薪(sal月薪*12 + comm奖金)有些人的奖金为空
select ename, sal*12 + comm from emp |ename |sal*12 + nvl(comm, 0) |smith | |allen |15500 // 因为有些人奖金为空，所以结果为空如何处理：使用
nvl处理select ename, sal*12 + nvl(comm, 0) from emp |ename |sal*12 + nvl(comm, 0) |smith |9000 //(9000+0) |allen |15500 // 因为有些人奖金为空，所以结果为空 -
DECODE函数该函数类似
if...else if...else语法：
DECODE(col/expression),[search1, result1],[search1, result1]...[default]col/expression：列名或表达式search1,search2 ...：用于比较的条件result1,result2 ...：返回值如果
col/expression和Searchi匹配就返回resulti，否则就返回default的默认值例：
select decode(1,1,'我是1',2,'我是2','我是其他') from dual | decode(1,1,'我是1',2,'我是2','我是其他') | 我是1相当于
if (x == 1) { console.log('我是1'); } else if (x == 2) { console.log('我是2'); } else { console.log('我是其他'); }例：查询出所有雇员的职位(job)的中文名
select ename, decode(job, 'CLERK','业务员', 'SALESMAN','销售', 'PERSIDENT','总裁', 'ANALYST','分析员', 'MANAGER','经理', '无业', ) | ENAME | decode(job,... | SIMITH| 业务员 | ALLEN | 销售 | KING | 总裁 -
case when函数case when comparison_expr1 then return_expr1 [ when comparison_expr2 then return_expr2 when comparison_expr2 then return_expr2 else else_expr ] endcomparison_expr1：表达式return_expr1：对应的结果else_expr：谁都没匹配上返回改写上述范例：
select ename, case when job = 'CLERK' then '业务员' when job = 'SALESMAN' then '销售' when job = 'PERSIDENT' then '总裁' when job = 'ANALYST' then '分析员' when job = 'MANAGER' then '经理' else '无业' end | ENAME | case when job =... | SIMITH| 业务员 | ALLEN | 销售 | KING | 总裁
多表查询
多表连接基本查询
使用一张以上的表做查询就是多表查询
语法:
select { distinct } * |列名... from 表名 别名，表名1 别名...
{ where 限制条件 order by 排序字符 asc| desc...}
例：查询员工表emp和部门表dept
select * from emp, dept
这样查会出现笛卡尔积：即emp表里数据有14条，dept表里数据有8条，出现的结果为112条，正好时14条×8条，它会将14条分别和8条进行匹配。
如果多张表一起进行查询而且每张表的数据很大的话，笛卡尔积就会变得很大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询：在两张表中我们发现有一个共同的字段是deptno，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是 其中一张表的主键，另外一张表的外键。
select * from emp,dept where emp.deptno = dept.deptno
例：查询出每一个员工的上级领导
分析：emp表中的MGR字段为对应的员工的上级领导员工号，所以做一张表的联合查询即可
select e.empno, e.ename, el.empno, el.ename
from emp e, emp el
where e.MGR = el.empno
例：查询每一个用户的上级领导和部门名称
select e.empno, e.ename, el.empno, el.ename, d.dname
from emp e, emp el, dept d
where e.empno = el.empno and e.deptno = d.deptno
例：查询出每个员工编号，姓名，部门名称，工资等级和和他的上级领导的姓名，工资等级
select e.empno,e.ename,d.dname,g.grade,el.ename,gl.grade
from emp e,emp el,dept d,salgrade g, salgrade gl
where e.empno = el.empno and e.deptno = d.deptno and
e.sal between g.losal and gl.hisal and
el.sal between gl.losal and gl.hisal
分析：查员工编号姓名部门名称及上级领导的姓名做之前的查询即可，如果加一个工资等级的话只需要将sal这个字段值between于每个工资等级的最高工资和最低工资之间即可，但是工资等级表需要两个，一张作为员工的参照，一张作为领导的参照，不然就会出现两者全匹配一张工资等级表，会出现问题
格式化级别:
select e.empno,e.ename,d.dname,
decode(g.grade,'1','一级','2','二级','3','三级','4','四级','5','五级','无级别'),
el.ename,
decode(gl.grade,'1','一级','2','二级','3','三级','4','四级','5','五级','无级别')
from emp e,emp el,dept d,salgrade g, salgrade gl
where e.empno = el.empno and e.deptno = d.deptno and
e.sal between g.losal and gl.hisal and
el.sal between gl.losal and gl.hisal
外连接（左右连接）※
外连接
当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为40的部门下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的。
emp表：
dept表：
如果按之前的查询：
dept表里的8个数据不能全部展现出来
因为30后面的部门没有对应的数据，此时就需要使用外连接。外连接可以保存一张表的数据是全量，即使没有一条数据可以跟它对应，它也可以保留那些对应不上的数据
select * from dept, emp where emp.deptno(+) = dept.deptno
如果两张表做联合查询时，如果其中一张表需要保存全量数据，则使用外连接，这张表的数据不会被筛选掉，在两张便关联的时候，非全量表的字段后面加上(+)就可以做外连接
左连接
做外连接查询的时候全量表在左端，则被称为左连接。
select * from dept, emp where emp.deptno(+) = dept.deptno
右连接
做外连接查询的时候全量表在右端，则被称为右连接
select * from emp, dept where dept.deptno = emp.deptno(+)
例：查询员工的编号和姓名和他的上级领导的编号和姓名
select e.empno, e.ename, el.empno, el.ename
from emp e, emp el
where e.mgr = el.empno(+)
SQL1999对SQL的支持
以下都是sql1999的语法
交叉连接
cross join（了解）交叉连接会产生笛卡尔积
例：
select * from emp e cross join dept d
会出现结果行=emp数据行*dept数据行
这样查询没有意义，因为会产生笛卡尔积
自然连接
NATURAL JOIN （了解）自然连接会自动的分析管理条件进行连接，去掉笛卡尔积
select * from emp natural join dept
相当于
select * from emp, dept
USING子句
直接管理操作（了解）用于联合查询，直接制定了用哪一列进行查询
select * from emp join dept using(deptno) where deptno = 30
相当于
select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 30
on子句
自己编写连接条件(重要) on 相当于 where
select * from emp e join dept d on e.deptno = d.deptno
尤其在左连接和右连接时尤为重要
上述查询语句相当于:
select * from emp e, dept d where e.deptno = d.deptno
左连接用on: left join
select * from dept d left join emp e on d.deptno = e.deptno
那么left join 左边的表一定是全量表，在此处是dept
right join 同理
实际开发中，很多人使用left join 和 right join 写左右连接
使用sql1999的左右连接改写示例：
查询员工的编号和姓名和他的上级领导的编号和姓名
select e.empno, e.ename, el.empno, el.ename
from emp e left join emp el
on e.mgr = el.empno
分组函数
统计记录数 count()
例：查询所有员工的记录数
不过不建议使用count(*)，可以使用一个具体的列以免影响性能
如：
select count(ename) from emp
最小值查询 min()
例：查询出来员工的最低工资
最大值查询 max()
例：查询出来员工的最高工资
查询平均值avg()
例：查询出来员工工资的平均值
求和函数 sum()
例：查询出20部门的员工的工资总和
分组统计
分组统计需要使用group by 来分组
语法：
select *|列名
from 表名
{ where 查询条件 }
{ group by 分组字段1，... }
order by 列名1 ASC | DESC, 列名2 ASC | DESC...
例：查询每个部门的编号和部门下的人数
例：查询每个部门的编号和部门下的人数如果不加group by
分析：因为没有group by分组，所以count是对整个表进行统计，强加在deptno上查询会出错
exists和not exists 关键字
exists 存在：(sql返回结果集为真)
not exists 不存在：（sql不返回结果集为真）
引例：查询出所有员工的部门有那些
select * from dept d where d.deptno in (select distinct deptno from emp)
注：关键字in 尽量少用 性能较低，可以使用exists来代替性能很高
exists( )子查询的记录数是0则整个表达式是false，如果大于0为true exists子查询一般是要和外侧查询关联的
select * from emp t where exists (select * from dept d where d.deptno = 50)
上面的sql查询语句，因为exists里面的deptno=50在emp中没有数据，那么括号里面的记录数则为0，为false，如果为30则大于0，为true
改写上面的查询:
select * from dept d where exists (select * from emp e where e.deptno = d.deptno)
dept中有那些是存在于emp中的
Union和Union All的用法
Union ：对两个结果集进行并集操作，不包括重复行
把两个集合做并集的时候，会把重复的数据去掉
例：找出empno为7499和7788的员工信息
select * from emp e where e.empno = '7499'
union
select * from emp e where e.empno = '7788'
Union All : 对两个结果集进行并集操作，不去重
注：并集时，要合并的列类型必须一致，列名可以不一致，两个合并的并集的列数必须一致
select e.ename, e.empno, e.job from emp e where e.empno = 7499
union
select e.empno, e.ename, e.job from emp e where e.empno = 7788
// 报错，因为e.ename和e.empno不时同一个类型
数据库变更
Insert 增加
语法:
insert into 表名[(列名1，列名2...)] values (值1， 值2, ...)
标准写法:
insert into myemp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values
(7788,
'张三',
'开发',
7839,
to_date('1992-10-22', 'yyyy-mm-dd'),
2000,
200,
10);
简单写法(不建议)
insert
into myemp
values
(7788,
'张三',
'开发',
7839,
to_date('1992-10-22', 'yyyy-mm-dd'),
2000,
200,
10);
注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null
update 更新
全部修改 ：
update 表名 set 列名 1 = 值 1，列名 2 = 值 2 ...
布局修改：
update 表名 set 列名 1 = 值 1，列名 2 = 值 2 ... where 修改条件
例：将编号为7566的员工的名字改为’ss’
update myemp e set e.ename = 'ss' where e.empno = 7566
delete 删除
语法:
delete from 表名 where 删除条件
在删除语句中如果不指定删除条件的话就会删除所有数据
例：删除empno 为 7788 7789 7790的员工
delete from emp where empno in (7788, 7789, 7790)
数据库事务
事务提交: commit 操作提交
增删改操作都要开启事务，这种事务必须提交，这样我们的数据才会在数据库中进行真正的变更。
事务回滚: rollback 操作回滚
事务在没提交之前都可以回滚，提交了就不能回滚
执行update时没有提交之前的事务是挂起状态，这时这条数据被锁住：比如两个sql查询窗口，一个窗口先执行了update，但是没有提交事务，此时第二个窗口如果进行查询操作，数据是先被锁住无法查询的，只有等第一个窗口提交了事务才能查询出来。
表的管理
常用的数据类型
| NO | 数据类型 | 描述 |
|---|---|---|
| 1 | varchar， varchar2 | 表示一个字符串 |
| 2 | number | number(n)表示一个整数，长度是n。number(m,n)表示一个小数，长度是m，小数位是n，整数位是m-n |
| 3 | date | 表示日期类型 |
| 4 | clob | 大对象，表示大文本数据类型，可存4G |
| 5 | blob | 大对象，表示二进制数据，可存4G |
建表
语法：
create table 表名 (
字段1 数据类型 [default 默认值]
字段2 数据类型 [default 默认值]
字段3 数据类型 [default 默认值]
...
字段n 数据类型 [default 默认值]
)
表删除
DROP TABLE 表名
表的修改
在sql中使用alter可以修改表
添加语法：
alter table 表名 add(字段1 数据类型 [default 默认值],字段1 数据类型 [default 默认值]... )
修改语法：
alter table 表名 modify (字段1 数据类型 [default 默认值],字段1 数据类型 [default 默认值]... )
注意：修改时如果原有某列的数据长度为200，则不可以把该列修改为小于200的列
例：在person表中增加列address
alter table person add(address varchar2(20));
例：把person表的address列的长度修改为30长度
alter table person modify(address varchar(30));
截断表
谨慎使用
在person表中使用delete语句删除数据，额可以使用rollback回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用阶段表的语法
语法：
truncate table 表名
例：截断person表
truncate table person
约束
在数据库开发中约束是必不可少的，使用约束可以更好的保证数据的完整性
主键约束 primary key
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定
例：创建一张表，把pid作为主键
create table person(
pid number(10) primary key,
name varchar2(10),
gender number(1) default 1
)
如果要自己设定主键的名字：constaint 新主键名 primary key(旧主键名)
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
constraint new_pid primary key(pid) // 指定某个为主键
)
非空约束 not null
使用非空约束，可以使指定的字段不可以为空
例：建立一张pid和name不为空的表
create table person(
pid number(10) not null,
name varchar2(10) not null,
gender number(1) default 1
)
唯一约束 unique
表中的一个字段的内容是唯一的
例：建一个name是唯一的表
create table person(
pid number(10),
name varchar2(10) unique,
gender number(1) default 1
)
insert into person values(1, 'zhang', 1);
insert into person values(2, 'zhang', 2);
// 报错因为name唯一约束
自己指定唯一性约束
create table person(
pid number(10),
name varchar2(10),
gender number(1) default 1,
constraint new_name unique(name) // 指定某个为唯一性约束
)
检查约束
使用检查约束可以来约束字段值的合法范围
例：创建一张表，性别字段只能是1或2
create table person(
pid number(10),
name varchar2(10) unique,
gender number(1) check(gender in (1,2))
)
insert into person values(2, 'zhang', 3); // 报错,因为性别只能是1或2
自己创建检查约束
create table person(
pid number(10),
name varchar2(10) unique,
gender number(1)，
constraint new_gender check(gender in (1,2)) // 指定某个为检查约束
)
外键约束
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性
例：创建两张表，一张订单表，一张是订单明细表，订单和明细是一对多的关系
create table orders(
order_id number(10),
total_price number(10,2),
order_time date,
constraint orders_order_id_pk primary key(order_id)
)
create table orders_detail(
detail_id number(10),
item_name number(10,2),
prices number(7,2),
order_id number(10),
quantity number(4),
constraint orders_order_detail_id_pk primary key(detail_id),
constraint orders_order_detail_id_fk foreign key(order_id) references orders(detail_id) //添加外键
)
删除数据时，如果有外键约束，需要先删除子表中的数据，再删除主表当中的数据，
如果想要强制删除的话(不建议)会连同约束的数据一起删除：
-
可以在建表的时候加上
on delete cascadecreate table orders_detail( detail_id number(10), item_name number(10,2), prices number(7,2), order_id number(10), quantity number(4), constraint orders_order_detail_id_pk primary key(detail_id), constraint orders_order_detail_id_fk foreign key(order_id) references orders(detail_id) on delete cascade //添加外键 ) -
在使用
drop table的时候可以加上cascade constraintdrop table orders cascade constraint
rownum和分页 ※
select rownum, t.* from emp t
这样查询后，查询的列会多出一个rownum 以后做分页用
在oracle中分页全靠rownum
例：如果要查询前五行数据
select rownum, t.* from emp t where rownum < 6
如果前5行为第一页的话，那么我们查询第二页的话，是不是按照下面的语句查询呢
select rownum, t.* from emp t where rownum > 5 and rownum < 11;
会查不出来
注：rownum 不支持大于号，所以这种查询是错误的
分页步骤：
-
查询全量的数据
select * from emp -
以第一步的结果集作为一张表，限定条件是rownum 小于结束行号，结果列把rownum作为结果集
select rownum a.* from (select * from emp) a where rownum < 6 -
以第二部的结果集作为一张表，限定条件是rownum列大于开始行号，结果列是*
select * from (select rownum a.* from (select * from emp) a where rownum < 6) b where b.rownum > 0这样分页就完成了，如果一页的数据为5条，那么上面的查询为查询第一页（大于0，小于6，5条数据）。如果要查询第二页,(小于11，大于5，五条数据），语句则为:
select * from ( select rownum a.* from ( select * from emp //如果要做表的限定只需要改这块即可 ) a where rownum < 11) b where b.rownum > 5在oracle分页中，如果要对一个数据库的表进行限定条件，那么分页的时候，只需要改中间的
(select * from 表名)即可，其他为固定的格式。
开始行号和结束行号的计算(startNum和endNum)
是由每页展示多少条pageSize和当前页码pageNum算出，是由前台页面传过来的
以上面的查询为例：
已知条件：pageSize 页面个数 pageNum页码
未知条件：startNum 起始行号 endNum 结束行号
公式： startNum = (pageNum - 1) * pageSize
endNum = pageNum*pageSize + 1
| pageNum | pageSize | startNum | endNum |
|---|---|---|---|
| 1 | 5 | 0 | 6 |
| 2 | 5 | 5 | 11 |
| n | 5 | (pageNum - 1) * 5 | pageNum*5+ 1 |
视图
视图就是封装了一条复杂的查询的语句
注：创建视图的sql不允许有列名重复，比如某列叫ename另外一列还是叫ename
语法1:
Create view 视图名称 as 子查询
例：建立一个视图，此视图包括了20部门的全部员工信息
如果要查询20部门的全部员工信息
select * from emp t where t.deptno = 20
创建部门是20的员工的视图：
create view view_d20 as
select *
from emp t
where t.deptno = 20
如果要查询20部门员工的信息的话只需要
select * from view_d20
语法2：
create or replace view 视图名称 as 子查询
如果视图已经存在我们可以使用语法2来创建视图，这样已有的视图会被覆盖
create or replace view empvd20 as
select *
from emp t
where t.deptno = 20
那么视图可以修改嘛？
update view_d20
set t.ename = '史密斯'
where t.empno = 8765
是可以修改的，但是同时emp表中的这行数据也会被修改，但是我们一般修改数据的时候，不会通过视图来修改。
我们如何防止这种修改视图情况下同时修改了原表数据呢？
答：可以创建只读视图with read only
create or replace view empvd20 as
select *
from emp t
where t.deptno = 20
with read only
这样就可以防止这种情况的发生了
序列 ※
在很多数据库中都存在一个自动增长的列，如果现在要想在oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理。
注：序列虽然是给某个表使用的，但是序列并没有绑定在某一张表上，序列给任何一张表使用都可以
语法：
create sequence 序列名
[increment by n] // 自增n 默认1
[start with n] // 从几开始的序列
[{maxvalue/minvalue n|nomaxvalue}] // 设置最大值
[{cycle | nocycle}] // 设置是否循环序列 一般都不是循环序列
[{cache n | nocache}] // 设置缓存
例：创建一个seqpersonid的序列，验证自动增长的操作
create sequence seqpersonid
序列创建完成以后，所有的自动增长应该由用户自己处理，所以在序列中提供了以下两种操作：
NextVal：取得序列的下一个内容
CurrVal：取得序列的当前内容
select seqpersonid.nextval from dual // 重点
select seqpersonid.currval from dual // 不太常用
例：
现在往一张空表person表中添加一个数据
insert into person(person_id, pname, gender, birthday)
values(seqpersonid.nextval, '张三', 1, sysdate)
// 如果你之前已经将seqpersonid.nextval增到20，那么此insert语句person_id为21
以后id就可以自增了，非常常用
oracle 索引
索引是用于加速数据存取的数据对象，合理的使用索引可以大大降低i/o 次数，从而提高数据访问性能，索引又很多种我们主要介绍常用的几种。
为什么添加了索引以后，会加快查询速度呢
图书馆：如果杂乱地放书的话，检索起来就非常困难，所以将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询，按姓名查询或者按类别查询，但是正是因为你建立了索引，就应该有人专门来维护这些索引，维护所以是要有时间精力的开销的，也就是说索引是不能乱建的。所以建立索引有个原则：如果有一个字段不经常查询就不要去建立索引，现在把书变成我们的表，把卡片变成我们的索引，就知道为什么索引会快，为什么会有开销。
索引不需要主动使用，根据索引列查询时就自动的使用了索引
单例索引
单例索引是基于单个列所建立的索引，比如：
create index 索引名 on 表名(列名)
例：给person表中的pname字段建立索引
create pname_index on person(pname)
使用索引
select * from person t where t.pname = '张三'
复合索引
复合索引是基于两个列或多个列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：
create index emp_idx1 on emp(ename, job)
create index emp_idx2 on emp(job, ename)
使用索引(查询数据使用索引的时候，索引是有顺序的)
select *
from emp t
where t.ename = '张三' and t.job = 'salemn'
PL/SQL 高级查询
什么是PL/SQL？
PL/SQL（procedure language/sql)
pl/sql 是oracle 对sql语言的过程化扩展，指在sql命令语言中增加了过程处理语句(如分支，循环等)，使sql语言具有过程处理能力，把sql语言的数据操纵能力与过程语言的数据处理能力结合起来，使得plsql面向过程但比过程语言更简单、高效、灵活和实用。
例：为职工涨工资，每人涨10%的工资
update emp set sql = sal*1.1
例：按职工的职称涨工资，总裁涨1000元，经理涨800元，其他人员涨400元。
这样的需求我们就无法使用一条sql来实现，需要借助其他程序来帮助完成，也可以使用pl/sql。
PL/SQL 程序语法
declare
说明部分 (变量定义，游标申明，例外说明)
begin
语句序列 (DML语句)
exception
例句处理语句
end
常量和变量定义
在程序的声明阶段declare...begin之间可以用来定义常量和变量
变量的基本类型就是oracle中建表时字段的变量，如char，varchar2，date，number，boolean，long
定义语法：
varl char(15);
psal number(19);
说明变量名，数据类型和长度后用分号结束说明语句。
例：基本变量
declare
pname varchar2(10);
begin
pname := 'zhangsan';
dbms_output.put_line(pname);
end;
// output: 张三
常量定义：
married boolean:=true;
例：
declare
pname varchar2(10);
age nunmber(3) := 20; // 定义常量
begin
pname := 'zhangsan';
dbms_output.put_line(pname);
dbms_output.put_line(age); // 输出常量
end;
引用变量
Myname emp.ename%type;
引用型变量，即my_name的类型与emp表中ename列的类型一样，在sql中使用into来赋值。
declare
pname myemp.ename%type;
begin
select t.ename into pname from myemp y where t.empno = 7369 // 引用类型赋值
dbms_output.put_line(pname); // 输出史密斯，7368的ename值
end;
记录型变量
在开发psql程序中，有可能要把一行的数据作为一个变量，相当于java中的对象，一张表所对应的对象。比如emp一行数据，相当于java中的一个对象的实例。
declare
prec myemp%rowtype;
begin
select * into prec from myemp y where t.empno = 7369 // 赋值一行数据
dbms_output.put_line(prec.pname || ' ' || prec.sal); // 输出 史密斯 1500
end;
if 分支
语法1：
if 条件 then 语句1;
语句2;
end if
例：如果输入数小于5，则输出编号小于5
declare
pno number(4) := #
begin
if pno < 5 then
dbms_output.put_line('编号小于5')
end if;
end
语法2:
if 条件 then 语句序列1;
else 语句序列2;
end if
例：如果输入的数小于5，则输出编号小于5否则输出大于5
declare
pno number(4) := #
begin
if pno < 5 then
dbms_output.put_line('编号小于5');
else
dbms_output.put_line('编号大于5');
end if;
end
语法3:
if 条件 then 语句;
elsif 条件 then 语句;
else 语句
end if
例：如果输入的数小于5，则输出编号小于5否则输出大于5
declare
pno number(4) := #
begin
if pno < 5 then
dbms_output.put_line('编号小于5');
elsif pno > 5 then
dbms_output.put_line('编号大于5');
else
dbms_output.put_line('编号等于5');
end if;
end
loop 循环语句
其中语法2比较常用
语法1：
while total <= 25000 loop
...
total := total + salary
end loop;
例：定义一个变量total 从0自增到100 并输出
declare
total number(4) := 0;
begin
while total < 100 loop
total := total + 1;
dbms_output.put_line(total)
end loop;
end
语法2：（最常用）
loop
exit [when 条件]
...
end loop
例：改写上述例子
declare
total number(4) := 0;
begin
loop
exit when total = 100
total := total + 1;
dbms_output.put_line(total)
end loop;
end
语法3：
for 变量 in 1..3 loop
语句序列
end loop
例：改写上述例子
declare
total number(4) := 0;
begin
for total in 1..100 loop
total := total + 1;
dbms_output.put_line(total)
end loop;
end
游标 Cursor
在写java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候我们就要用游标，游标可以存储查询返回的多条数据。
语法：
cursor 游标名 [(参数名 数据类型，参数名 数据类型...)]
is select 语句
例：
cursor c1 is select ename from emp
游标的使用步骤：
- 打开游标： open c1; （打开游标执行查询）
- 取一行游标的值： fetch c1 into pjob; （取一行到变量中）
- 关闭游标： close c1; （关闭游标释放资源）
- 游标的结束方式：exit when c1%notfound
- 注意：上面的pjob必须和emp表中的job列类型一致：
pjob emp.empjob%type
例：使用游标的方式输出emp表中的员工编号和姓名
declare
prec emp%rowtype; -- 定义变量
cursor c1 is
select * from emp; -- 定义游标
begin
open c1
loop
fetch c1 into prec;
exit when c1%notfound;
dbms_output.put_line(prec.empno || ' ' || prec.ename)
end loop;
close c1;--关闭游标
end
例：按员工的工种涨工资，总裁1000元，经理800元，其他人员400元
declare
prec emp%rowtype; -- 定义变量
addsal number(4);
cursor c1 is
select * from emp; -- 定义游标
begin
open c1
loop
fetch c1 into prec;
exit when c1%notfound;
if prec.job = 'king' then
addsal := 1000;
elsif prec.job = 'manager' then
addsal := 800;
else
addsal := 400;
end if
update emp t set t.sal = t.sal + addsal where t.empno = prec.empno;
end loop;
close c1;--关闭游标
commit;
end
带有参数的游标
例：写一段pl/sql 程序，为部门号为10的员工涨工资
declare
prec emp%rowtype; -- 定义变量
cursor c1(dno emp.deptno%type) is
select * from emp
where emp.deptno = dno; -- 定义游标
begin
open c1(10) --这里传参数
loop
fetch c1 into prec;
exit when c1%notfound;
update emp set emp.sal = emp.sal + 1000 where emp.deptno = prec.empno;
end loop;
close c1;--关闭游标
commit;
end
例外
例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性
系统定义例外：
no_data_found ：没有找到数据
too_many_rows：select…into语句匹配多个行
zero_divide ：被零除
value_error;算数或转换错误
timeout_on_resource：在等待资源时发生超时
例：被零除的错误
declare
pnum number(4) := 5;
begin
pnum := pnum/0; --发生异常
exception -- 处理异常
when zero_divide then
dbms_output.put_line('被0除');
when value
例：查询部门编号是50的员工
declare
prec emp%rowtype
cursor c1 is select * from emp t where t.deptno = 50
no_data exception; --异常类型的定义
no_date
begin
open c1;
loop
fetch c1 into prec;
if c2%notfound then
raise no_date;
end if;
end loop;
exception
when no_data then
dbms_output.put_line('没有员工')
when others then
dbms_output.put_line('其他异常');
end;
存储过程
存储过程是在大型数据库系统中，一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它，存储过程时数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
语法1:
create [or replace] PROCEDURE 过程名[{参数名 in/out 数据类型}]
AS
begin
PLSQL 子程序体
end
语法2：
create [or replace] PROCEDURE 过程名[{参数名 in/out 数据类型}]
is
begin
PLSQL 子程序体
end 过程名
例：
create or replace PROCEDURE helloworld as
begin
dbms_output.put_line('hello world');
end
执行它：
begin
helloworld
end
例：给指定的员工涨100工资，并打印出涨前和涨后的工资
分析：我们需要使用带有参数的存储过程
create or replace proceduce addSall(eno in emp.empno%type) as
-- 定义变量
prec emp%rowtype
begin
select * into prec from emp t where t.empno = eno;
update emp t set t.sal = t.sal + 100 where t.empno = eno;
dbms_output.put_line('涨工资前时:' || prec.sal || '涨工资后是：' || (prec.sal+100));
end;
测试这段存储过程
begin
addSall(7369) // 输入员工号
commit;
end;
存储函数
语法：
create or replace function 函数名(Name in type,Name in type...) return 数据类型
is 结果变量数据类型
begin
return(结果变量)
end 函数名
例：计算年薪
create or replace function countysalf(pno in emp.empno%type) return number is
Result number;
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal, t.comm into psal, pcomm from emp t where t.empno = pno;
Result = psal * 12 + nvl(pcomm, 0);
return(Result);
end countysalf;
编译测试：
begin
:result := countysalf(pno => pno);
end;
存储过程和存储函数的区别：
一般来讲，过程和函数的区别在于函数可以有一个返回值，而过程没有返回值，但过程都可以通过out指定一个或多个输出参数，我们可以利用out参数，在过程中实现返回多个值；
建议使用存储过程
触发器
数据库触发器是一个与表相关联的、存储的PL/SQL程序，每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时，oracle自动地执行触发器中定义地语句序列。
触发器可用于
- 数据确认
- 实施复杂地安全性检查
- 做审计跟踪表上所做地数据操作等
- 数据地备份和同步
触发器的类型
- 语句级触发器：在指定地操作语句操作之前或之后执行一次，不管这条语句影响了多少行。
- 行级触发器：触发语句作用的每一条记录都被触发，在行级触发器中使用oid和new伪记录变量，识别值的状态。
语法
create [or replace] trigger 触发器名
{before | after}
{Delete | Insert | Update{of 列名}}
on 表名
[for each row[when(条件)]]
begin
plsql 块
end 触发器名
语句级触发器：
例：插入一条数据时，打印插入一条数据
create or replace trigger insertptrg
before insert on person
begin
dbms_output.put_line('插入一条数据');
end insertptrg
例：不能在休息时间（星期三）插入员工
create or replace trigger valid_insert_p
before insert on person
declare
cruday varchar2(10);
begin
select to_char(sysdate, 'day') into cruday from dual;
if cruday = '星期三' then
raise_application_error(-20001, '星期三不允许插入员工')
end if;
end valid_insert_p;
如果星期三插入数据，会提示错误
行级触发器：
| 触发语句 | :old | :new |
|---|---|---|
| insert | 所有字段都是空null | 将要插入的数据 |
| update | 更新以前该行的值 | 更新后的值 |
| delete | 删除以前该行的值 | 所有字段都是空null |
例：判断员工张工资之后的工资值一定要大于涨工资之前的工资
create or replace trigger addsal
before update of sal on myemp
for each row
begin
if :new.sal <= :old.sal then
raise_application_error(-2002, '涨后的工资不能比涨前的低');
end if;
end addsal;
如果执行update语句涨工资比之前低，则报错。
扫一扫
236
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
