一、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、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.删除数据对象 */-- 删除数据表drop table t_reply;drop table t_user;-- 删除序列drop sequence sq_user;drop sequence sq_reply;/* 2.创建数据对象 */-- 创建数据表create table t_user(u_id number(4) primary key,u_userName varchar2(40) unique not null,u_password varchar2(50) not null,u_gender number(1) check(u_gender in (0,1,2)),u_age number(3) check(u_age between 12 and 120),u_registeTime date not null);create table t_reply(r_id number(10) primary key,r_content varchar2(500) not null,r_userId number(4) not null,foreign key(r_userId) references t_user(u_id));-- 创建序列create sequence sq_user start with 1001;create sequence sq_reply start with 10000001;/* 3.添加测试数据 */-- 处理全新的数据表和全新的序列在使用时取不到初始值的问题insert into t_user values(1, '1', '1', 1, 25, sysdate);insert into t_reply values(1, '1', 1);delete from t_reply;delete from t_user;commit;/*insert into t_user values(1000, 'owner', '9999', 1, 25, sysdate);insert into t_reply values(10000000, '人员召集...', 1000);*/-- 添加数据insert into t_user values(sq_user.nextval, 'admin', '9999', 1, 25, sysdate);insert into t_reply values(sq_reply.nextval, '系统1.0上线...', sq_user.currval);/* 4.提交 */commit;/* 5.查询语句 *//*select * from t_user;select * from t_reply;*/

3、查询语句的结构

select [列名] [*] from 表名 [where 条件] [group by 分组条件] [having 过滤] [order by 排序]

4、伪表dual

dual : oracle中的虚表 ,伪表, 主要是用来补齐语法结构，

比如：select 1+1 from dual;注意：直接写一个常量比写 * 要高效select count(1) from emp;select count(*) from emp;

5、别名查询

使用as 关键字, 可以省略，别名中不能有特殊字符或者关键字, 如果有就加双引号(比如空格）

elect ename 姓名, sal 工资 from emp;select ename "姓 名", sal 工资 from emp;

6、去除重复数据 distinct

加在select和列名后面，多列去除重复: 每一列都一样才能够算作是重复

--单列去除重复select distinct job from emp;--多列去除重复的select distinct job,deptno from emp;

7、查询中四则运算

需要用到伪表dual

select 1+1 from dual;--查询员工年薪 = 月薪* 12select sal*12 from emp;

在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函数只能处理数值字段的空值

SELECT e.`ename` 姓名,e.`sal` 底薪,e.`comm` 奖金,(e.`sal`+e.`comm`) 实发,IFNULL(e.`sal`+e.`comm`, e.`sal`) 实发1,IF(e.`comm`, e.`sal`+e.`comm`, e.`sal`) 实发2FROM emp e;

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,
​ 默认结果值
​ )

select e.ename 姓名,e.sal 底薪,e.comm 奖金,nvl(e.sal+e.comm, e.sal) 实发1,nvl2(e.comm, e.sal+e.comm, e.sal) 实发2,decode(e.comm, null, e.sal, e.sal+e.comm) 实发3from emp e;

9、字符串拼接

• Oracle 特有的连接符: || 拼接
• concat(str1,str2) 函数, 在mysql和Oracle中都有

--查询员工姓名 : 姓名:SCOTTselect ename from emp;--使用拼接符select '姓名:' || ename from emp;--使用函数拼接select concat('姓名:',ename) from emp;

六、条件查询

条件查询就是where后面的写法

1、关系运算符和排序

> >= = < <= != <>注意：不等于既可以用！=也可以用<>但是<>效率更高

2、逻辑运算符

and or not

3、其它运算符

like 模糊查询in(set) 在某个集合内between..and.. 在某个区间内is null 判断为空is not null 判断不为空

4、模糊查询: like

% 匹配多个字符

_ 匹配单个字符

如果有特殊字符, 需要使用escape转义

--查询员工姓名中,包含%的员工信息select * from emp where ename like '%\%%' escape '\';select * from emp where ename like '%#%%' escape '#';

5、排序order by

升序: asc ascend

降序: desc descend

排序注意null问题 : nulls first | last指定null值显示的位置

同时排列多列, 用逗号隔开

--查询员工信息,按照奖金由高到低排序select * from emp order by comm desc nulls last;--查询部门编号和按照工资 按照部门升序排序, 工资降序排序select deptno, sal from emp order by deptno asc, sal desc;

分析函数：用来处理排名并列问题

rank——排名可并列，之后序号不会顺眼（如2个第二，下一个是第三名）

dense_rank——排名可并列，之后序号会顺眼（如2个第二，下一个是第四名）

row_number——不可并列排名

over——添加分组条件

partition—by——分组

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之后执行，可以接聚合函数

  --分组统计所有部门的平均工资，找出平均工资大于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）数值函数

• 取整

  select ceil(45.926) from dual; --46 向上取整select floor(45.926) from dual; --45 向下取整

• 四舍五入

  round（a，b）

  b如果是正数代表取到小数点后b位数为止

  b如果是负数代表取到十位为止

  elect round(45.926,2) from dual; --45.93select round(45.926,1) from dual; -- 45.9select round(45.926,0) from dual; --46select round(45.926,-1) from dual; --50select round(45.926,-2) from dual; --0select round(65.926,-2) from dual; --100

• 截断（去尾法）

  select trunc(45.926,2) from dual; --45.92select trunc(45.926,1) from dual; -- 45.9select trunc(45.926,0) from dual; --45select trunc(45.926,-1) from dual; --40select trunc(45.926,-2) from dual; --0select trunc(65.926,-2) from dual; --0

• 求余

  select mod(9,3) from dual; --0select mod(9,4) from dual; --1

2）字符函数

substr(str1,起始索引,长度) --注意: 起始索引不管写 0 还是 1 都是从第一个字符开始截取select substr('abcdefg',0,3) from dual; --abcselect substr('abcdefg',1,3) from dual; --abcselect substr('abcdefg',2,3) from dual; --bcd--获取字符串长度 24 28select length('abcdefg') from dual;--去除字符左右两边的空格select trim(' hello ') from dual;--替换字符串Select replace('hello','l','a') from dual;

3） 日期函数

-查询今天的日期（查询的是服务器上的日期）select sysdate from dual;--查询3个月后的今天的日期select add_months(sysdate,3) from dual;--查询3天后的日期select sysdate + 3 from dual;--查询员工入职的天数select sysdate - hiredate from emp;select ceil(sysdate - hiredate) from emp;--查询员工入职的周数select (sysdate - hiredate)/7 from emp;--查询员工入职的月数select months_between(sysdate,hiredate) from emp;--查询员工入职的年份select months_between(sysdate,hiredate)/12 from emp;

4） 转换函数

数值转字符

select to_char(sal,'$9,999.99') from emp;select to_char(sal,'L9,999.99') from emp;/*to_char(1210.73, '9999.9') 返回 '1210.7' to_char(1210.73, '9,999.99') 返回 '1,210.73' to_char(1210.73, '$9,999.00') 返回 '$1,210.73' to_char(21, '000099') 返回 '000021' to_char(852,'xxxx') 返回' 354'*/

日期转字符 to_char() （不区分大小写）

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual; --默认的是24小时制度--只想要年select to_char(sysdate,'yyyy') from dual; --2017--只想要日select to_char(sysdate,'d') from dual; --2 代表一个星期中第几天（美国周天是一个星期的第一天）select to_char(sysdate,'dd') from dual; --10 代表一个月中的第几天select to_char(sysdate,'ddd') from dual; --100 代表一年中的第几天select to_char(sysdate,'day') from dual; --monday（星期的英文）select to_char(sysdate,'dy') from dual; --mon 星期的简写

字符转日期

select to_date('2017-04-10','yyyy-mm-dd') from dual;--查询1981年 -- 1985年入职的员工信息select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');

5） 通用函数

nvl(参数1,参数2) 如果参数1 = null 就返回参数2nvl2(参数1,参数2,参数3) 如果参数1 = null ,就返回参数3, 否则返回参数2nullif(参数1,参数2) 如果参数1 = 参数2 那么就返回 null , 否则返回参数1coalesce: 返回第一个不为null的值select nvl2(null,5,6) from dual; --6;select nvl2(1,5,6) from dual; --5;select nullif(5,6) from dual; --5select nullif(6,6) from dual; --nullselect coalesce(null,null,3,5,6) from dual; --3

2、多行函数（聚合函数）

对某一列的所有行进行处理

max() min count sum avg

注意：直接忽略空值

例子：

--统计员工的平均奖金 550 错误 2200/14 =，因为空值产生的影响select avg(comm) from emp;--统计员工的平均奖金 157.select sum(comm)/count(1) from emp;select ceil(sum(comm)/count(1)) from emp;

3、条件表达式

作用：查询的时候用来替换列中所有数据的值，区别于取别名，取别名只是单纯的给子弹取别名

/*条件表达式：case 字段when 值1 then 值when 值2 then 值else默认值end "别名"case、when通用的写法，mysql和oracle中都可以用oracle特有的写法：decode(字段,if1,then1,if2,then2,else1)*/--给表中姓名取一个中文名select case enamewhen 'SMITH' then '刘备小二'when 'ALLEN' then '萨达'else '路人甲'end "中文名"from emp;--oracle 特有写法select decode(ename,'SMITH','刘备小二','ALLEN','萨达','路人甲') from emp;

八、多表查询

1、笛卡尔积

实际上是两张表的乘积,但是在实际开发中没有太大意义，格式: select * from 表1,表2

select * from emp;select * from dept;select * from emp, dept;select * from emp e1, dept d1 where e1.deptno = d1.deptno;

2、内连接

隐式内联接:
​ 等值内联接: where e1.deptno = d1.deptno;
​ 不等值内联接: where e1.deptno <> d1.deptno;
​ 自联接: 自己连接自己
显式内联接:
​ select * from 表1 inner join 表2 on 连接条件
​ inner 关键字可以省略

--查询员工编号,员工姓名,经理的编号,经理的姓名select e1.empno,e1.ename,e1.mgr,m1.enamefrom emp e1, emp m1 where e1.mgr= m1.empno;--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名select e1.empno,e1.ename,d1.dname,e1.mgr,m1.enamefrom emp e1, emp m1,dept d1 where e1.mgr= m1.empno and e1.deptno = d1.deptno;--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名,经理的部门名称select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dnamefrom emp e1, emp m1,dept d1,dept d2 where e1.mgr= m1.empno and e1.deptno = d1.deptnoand m1.deptno = d2.deptno ;--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dnamefrom emp e1, emp m1,dept d1,dept d2,salgrade s1 where e1.mgr= m1.empno and e1.deptno = d1.deptnoand m1.deptno = d2.deptnoand e1.sal between s1.losal and s1.hisal ;--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname,s2.gradefrom emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2 where e1.mgr= m1.empno and e1.deptno = d1.deptnoand m1.deptno = d2.deptnoand e1.sal between s1.losal and s1.hisal and m1.sal between s2.losal and s2.hisal ;--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级--将工资等级 1,2,3,4 显示成 中文的 一级 二级 三级...select e1.empno,e1.ename,d1.dname,case s1.gradewhen 1 then '一级'--为所有列更换成你想要的信息when 2 then '二级'when 3 then '三级'when 4 then '四级'else'五级'end "等级",--取别名e1.mgr,m1.ename,d2.dname,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') "等级"from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2 where e1.mgr= m1.empno and e1.deptno = d1.deptnoand m1.deptno = d2.deptnoand e1.sal between s1.losal and s1.hisal and m1.sal between s2.losal and s2.hisal ;--查询员工姓名和员工部门所处的位置select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno = d1.deptno;select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

2、外连接

外连接: (标准,通用写法)
​ 左外连接: left outer join 左表中所有的记录,如果右表没有对 应记录,就显示空
​ 右外连接: right outer join 右表中的所有记录,如果左表没有对应记录,就显示空
​ outer 关键字可以省略
Oracle中的外连接: (+) 实际上是如果没有对应的记录就加上空值
​ select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);

select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;insert into emp(empno,ename) values(9527,'HUAAN');select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;

九、子查询

查询语句中嵌套查询语句; 用来解决复杂的查询语句

1、单行子查询

= = < <= <> !=

--查询最高工资的员工信息 --1.查询出最高工资 --5000select max(sal) from emp;--2. 工资等于最高工资select * from emp where sal = (select max(sal) from emp);--查询出比雇员7654的工资高,同时和7788从事相同工作的员工信息--1.雇员7654的工资 1250select sal from emp where empno = 7654;--2.7788从事的工作 ANALYSTselect job from emp where empno = 7788;--3.两个条件合并select * from emp where sal > 1250 and job = 'ANALYST';select * from emp where sal > (select sal from emp where empno = 7654) and job = (select job from emp where empno = 7788);--查询每个部门最低工资的员工信息和他所在的部门信息--1.查询每个部门的最低工资,分组统计select deptno,min(sal) minsal from emp group by deptno;--2.员工工资等于他所处部门的最低工资select * from emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1 --将查询到的结果作为一张表进行两表连查where e1.deptno = t1.deptno and e1.sal = t1.minsal; --3.查询部门相关信息select * from emp e1,(select deptno,min(sal) minsal from emp group by deptno) t1,dept d1 where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;

2、多行子查询

in not in >any >all exists not exists

通常情况下, 数据库中不要出现null 最好的做法加上Not null
​ null值并不代表不占空间, char(100) null 100个字符

--查询不是领导的信息select * from emp where empno not in (select mgr from emp);select * from emp where empno <>all(select mgr from emp);--正确的写法select * from emp where empno not in (select mgr from emp where mgr is not null);

3、exists(查询语句)

存在的意思,判断一张表里面的记录是否存在与另外一张表中，作布尔值来处理:， 当查询语句有结果的时候, 就是返回true，否则返回的是false，数据量比较大的时候是非常高效的

--查询有员工的部门的信息select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno );

十、分页

Oracle的分页查询需要使用伪列辅助

伪列 : 在Oracle的结果集中默认带有的可以使用的
​ 不出现在结果集列中的内容.
​ rownum : 伪列序号, 从1开始
​ rowid : 伪列地址

SQL执行顺序
​ from … where …group by…having … select…rownum…order by

默认都是从1开始。

rownum按照员工id排序后再分页select t.*from (select rownum rn,e.*from (select *from emp order by empno) e) twhere t.rn between 5 and 9; rowid去除表中重复数据create table p(name varchar2(10));insert into p values('黄伟福');insert into p values('赵洪');insert into p values('杨华');delete from p where select rowid,p.* from p;select distinct * from p;delete from p p1 where rowid > (select min(rowid) from p p2 where p1.name = p2.name);

十一、集合运算

所有的查询结果可能不是来自同一张表, 将查询结果进行合并，并去除重复项

1、并集

union : 去除重复的,并且排序

union all : 不会去除重复的

select * from emp where sal > 1500unionselect * from emp where deptno = 20;select * from emp where sal > 1500union allselect * from emp where deptno = 20;

2、交集运算: intersect

--工资大于1500,并且20号部门下的员工select * from emp where sal > 1500;select * from emp where deptno = 20;select * from emp where sal > 1500intersectselect * from emp where deptno = 20;

3、差集运算

两个结果相减

--1981年入职员工(不包括总裁和经理)--1981年入职员工select * from emp where to_char(hiredate,'yyyy')='1981';--总裁和经理select * from emp where job = 'PRESIDENT' or job = 'MANAGER';select * from emp where to_char(hiredate,'yyyy')='1981'minusselect * from emp where job = 'PRESIDENT' or job = 'MANAGER';

4、集合运算中的注意事项

1.列的类型要一致
2.按照顺序写
3.列的数量要一致,如果不足,用空值填充

select ename,sal from emp where sal > 1500unionselect ename,sal from emp where deptno = 20;--列的类型不匹配select ename,sal from emp where sal > 1500unionselect sal,ename from emp where deptno = 20;--列的数量不匹配select ename,sal,deptno from emp where sal > 1500unionselect ename,sal from emp where deptno = 20;select ename,sal,deptno from emp where sal > 1500unionselect ename,sal,null from emp where deptno = 20;select ename,sal,deptno from emp where sal > 1500unionselect ename,sal,66 from emp where deptno = 20;select * from emp;select * from dept;

十二、DDL语句管理表

1、表空间

逻辑单位, 通常我们新建一个项目,就会去新建表空间,在表空间中创建用户来创建表

• 创建表空间

  语法:
  ​ create tablespace 表空间的名称
  ​ datafile ‘文件的路径(服务器上)’
  ​ size 大小
  ​ autoextend on 自动扩展
  ​ next 每次扩展的大小

  --创建表空间-create tablespace canghedatafile 'C:\oracle\canghe.dbf'size 100mautoextend onnext 10m;

• 删除表空间

--删除表空间drop tablespace canghe;

2、创建用户

create user 用户名
identified by 密码
default tablespace 表空间的名称

create user cangheidentified by canghedefault tablespace canghe;

• 授权

--授予 dba的角色grant dba to canghe;select * from scott.emp;

3、创建表

create table 表名(
​ 列名 列的类型 [列的约束],
​ 列名 列的类型 [列的约束]
​ );

列的类型:varchar ,在Oracle中,目前是支持的, 但是不保证以后还支持varchar2(长度) 可变字符长度 varchar2(10) hello 占5个字符char(长度) 固定长度字符 char(10) hello 占10个字符,用空格填充number(总长度,小数长度) 数字类型 --小数长度不能大于等于总长度date 年月日时分秒 2017/4/13 9:43:49timestamp 时间戳, 比date类型更加精确 13-APR-17 09.44.08.272000 AM +08:00LONG/CLOB : 存放一本小说BLOB : 存放电影 java 存进去, 再读取出来 使用子查询的方式创建表create table 表名 as 查询语句; 注意: 只会复制表结构和表中的数据,不会复制列的约束 如果查询语句有结果, 就是复制 表结构和数据如果查询语句没有结果, 就是复制 表结构 create table test1(name1 varchar2(10),name2 char(10),age number(4,2));create table 表名 as 查询语句; 复制表create table emp as select * from scott.emp;

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;

级联删除:

添加外键约束,使用级联约束 ,在删除的时候,使用级联删除

----添加外键约束,使用级联约束 ,在删除的时候,使用级联删除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 条件]

delete和truncate 区别delete: truncate:DML DDL逐条删除 先删除表再创建表支持事务操作 不支持事务操作,执行效率要高

十四、其他数据库对象

1、事务

事务: 就是一系列的操作,要么都成功,要么都失败
​ 四大特性: 原子性,隔离性,持久性,一致性
​

如果不考虑隔离级别: 脏读,虚读,不可重复读MYSQL隔离级别: READ UNCOMMITTED , READ COMMITTED, REPEATABLE READ, SERIALIAZABLEORACLE隔离级别: READ COMMITTED SERIALIZABLE READ ONLY 默认隔离级别: READ COMMITTED提交 : commit事务的保存点/回滚点: savepoint 保存点的名称回滚: rollback

2、视图

视图: 是对查询结果的一个封装
​ 视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据
​ 1.能够封装复杂的查询结果
​ 2.屏蔽表中的细节
​ 语法:
​ create [or replace] view 视图的名称 as 查询语句 [ with read only]
​ 注意: 通常不要通过视图去修改,视图创建的时候,通常要加上with read only

--创建一个视图create or replace view view_test1 as select ename,job,mgr from emp;--通过视图修改数据update view_test1 set ename='SMITH2' where ename = 'SMITH';--创建一个只读视图create or replace view view_test2 as select ename,job,mgr from emp with read only;update view_test2 set ename='SMITH3' where ename = 'SMITH2';

-同义词的概念（就是为视图取的一个别名）
create synonym dept for view_test3;

3、序列

oracle中用来控制自增长的

语法:create sequence 序列的名称start with 从几开始increment by 每次增长多少maxvalue 最大值 | nomaxvalueminvalue 最小值 | nominvaluecycle | nocycle 是否循环 1,2,3,1,2,3cache 缓存的数量3 | nocache 1,2,3,4,5,6 如何从序列获取值currval : 当前值nextval : 下一个值注意: currval 需要在调用nextval之后才能使用 永不回头,往下取数据, 无论发生异常, 回滚 --序列用的最多的一种写法create sequence seq_test2;select seq_test2.nextval from dual;

4、索引

索引:相当于是一本书的目录,能够提高我们的查询效率如果某一列,你经常用来作为查询条件,那么就有必要创建索引,数据量比较的情况语法: create index 索引的名称 on 表名(列) 注意:主键约束自带主键索引, 唯一约束自带唯一索引索引原理: btree balance Tree 平衡二叉树如果某列作为查询条件的时候,可以提高查询效率,但是修改的时候,会变慢索引创建好之后,过了一段,DBA都会去做重构索引SQL调优:1.查看执行计划F52. 分析里面的cost 和 影响行数, 想办法降低

十五、PLSQL编程

1、概述

procedure Language 过程语言 Oracle对SQL的一个扩展
​ 让我们能够像在java中一样写 if else else if 条件, 还可以编写循环逻辑 for while

2、基本语法

declare--声明变量变量名 变量类型;变量名 变量类型 := 初始值;vsal emp.sal%type; --引用型的变量 vrow emp%rowtype; --声明记录型变量 begin--业务逻辑,比如输出一句话：dbms_output.put_line()相当于java中 syso end; declarei varchar2(10) := '张三'; begindbms_output.put_line(i);end;

3、变量

%type --引用型变量

%rowtype --记录型变量

4、PL条件判断

if thenelsif thenelse end if; declareage number := &aaa;beginif age < 18 thendbms_output.put_line('小屁孩');elsif age>=18 and age <=24 thendbms_output.put_line('年轻人');elsif age>24 and age < 40 thendbms_output.put_line('老司机');else dbms_output.put_line('老年人'); end if;end;

5、循环操作

while 循环while 条件 loopend loop;for循环for 变量 in [reverse] 起始值..结束值 loopend loop;loop循环 loopexit when 条件end loop; --输出1~10declarei number :=1;beginwhile i<=10 loopdbms_output.put_line(i);i := i+1; end loop;end;--输出1~10declarebeginfor i in reverse 1..10 loopdbms_output.put_line(i);end loop;end;--输出1~10declarei number :=1;beginloopexit when i>10;dbms_output.put_line(i); i := i+1;end loop;end;/************** 输出 m x : [-m,m]y : [-m,m]输出所有满足条件的 : abs(y)+abs(x) <=mm取值*/--使用PLSQL输出菱形declarem number := 10;beginfor x in -m..m loopfor y in -m..m loopif abs(y) + abs(x) <= m thendbms_output.put('*');elsedbms_output.put(' ');end if; end loop;dbms_output.new_line();end loop; end;--使用PLSQL输出三角形,只要是三个角declarem number := 10;beginfor x in reverse -m..m loopfor y in -m..m loopif abs(y) + abs(x) <= m and x>=0 thendbms_output.put('*');elsedbms_output.put(' ');end if; end loop;dbms_output.new_line();end loop; end;

十六、游标

用来操作查询的结果集.相当于JDBC中的ResultSe

语法: cursor 游标名[(参数名 参数类型)] is 查询结果集开发步骤:1. 声明游标2. 打开游标 open 游标名3. 从游标中取数据 fetch 游标名 into 变量游标名%found :找到数据游标名%notfound : 没有找到数据 4. 关闭游标 close 游标名系统引用游标1. 声明游标 : 游标名 sys_refcursor2. 打开游标: open 游标名 for 结果集3. 从游标中取数据4. 关闭游标for循环遍历游标:不需要声明额外变量不需要打开游标不需要关闭游标

1、不带参数游标

*/--输出员工表中所有的员工姓名和工资(不带参数游标)/*游标:所有员工声明一个变量,用来记录一行数据 %rowtype*/declare--游标cursor vrows is select * from emp;--s声明变量,记录一行数据vrow emp%rowtype;begin--1.打开游标 open vrows;--2.从游标提取数据--循环取数据loopfetch vrows into vrow; exit when vrows%notfound; dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);end loop;--3.关闭游标close vrows;end;

2、带参数的游标

--输出指定部门下的员工姓名和工资/*游标: 指定部门的所有员工声明一个变量记录一行数据*/declare--声明游标cursor vrows(dno number) is select * from emp where deptno = dno;--声明变量vrow emp%rowtype;begin--1.打开游标 , 指定10号部门open vrows(10);--2. 循环遍历,取数据loopfetch vrows into vrow;exit when vrows%notfound; dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);end loop;close vrows;end;

3、系统引用游标

--输出员工表中所有的员工姓名和工资declare--声明系统引用游标vrows sys_refcursor;--声明一个变量vrow emp%rowtype;begin--1.打开游标open vrows for select * from emp;--2.取数据loopfetch vrows into vrow;exit when vrows%notfound;dbms_output.put_line('姓名:'||vrow.ename ||' 工资: ' || vrow.sal);end loop;close vrows;end;

4、-使用for循环遍历游标

--按照员工工作给所有员工涨工资,总裁涨1000,经理涨800,其他人涨400/*游标 : 所有员工声明一个记录一行数据 */declare--声明游标cursor vrows is select * from emp;--声明一个变量vrow emp%rowtype; begin--1.打开游标open vrows;--2.循环取数据loop--取数据fetch vrows into vrow;--退出条件exit when vrows%notfound; --根据不同的职位,涨工资 总裁涨1000,经理涨800,其他人涨400if vrow.job = 'PRESIDENT' thenupdate emp set sal = sal + 1000 where empno = vrow.empno;elsif vrow.job = 'MANAGER' thenupdate emp set sal = sal + 800 where empno = vrow.empno;elseupdate emp set sal = sal + 400 where empno = vrow.empno; end if; end loop;--3.关闭游标close vrows;--4.提交事务commit;end;

十七、例外

例外:(意外)程序运行的过程发生异常,相当于是JAVA中的异常

declare--声明变量begin--业务逻辑exception--处理异常when 异常1 then...when 异常2 then...when others then...处理其它异常end;zero_pide : 除零异常value_error : 类型转换异常too_many_rows : 查询出多行记录,但是赋值给了rowtype记录一行数据变量no_data_found : 没有找到数据自定义异常:异常名 exception;raise 异常名 --查询指定编号的员工,如果没有找到,则抛出自定义的异常/*--错误的演示1.声明一个变量 %rowtype2.查询员工信息,保存起来3.判断员工信息是否为空4. 如果是 则抛出异常*/declare-- 1.声明一个变量 %rowtypevrow emp%rowtype;--2 .声明一个自定义的异常no_emp exception; begin--查询员工信息,保存起来select * into vrow from emp where empno = 8888; --抛出异常if vrow.sal is null thenraise no_emp; --抛出自定义的异常end if;exceptionwhen no_emp thendbms_output.put_line('输出了自定义的异常'); when others thendbms_output.put_line('输出了其它异常'||sqlerrm); end;--查询指定编号的员工,如果没有找到,则抛出自定义的异常/*游标来判断%found %notfound声明一个游标声明一个变量,记录数据从游标中取记录如果有,则不管它如果没有就抛出自定义的异常*/declare--声明游标cursor vrows is select * from emp where empno=8888; --声明一个记录型变量vrow emp%rowtype;--声明一个自定义异常no_emp exception; begin--1.打开游标open vrows;--2.取数据fetch vrows into vrow;--3.判断游标是否有数据if vrows%notfound thenraise no_emp;end if;close vrows;exceptionwhen no_emp thendbms_output.put_line('发生了自定义的异常');end;

十八、存储过程

存储过程: 实际上是封装在服务器上一段PLSQL代码片断,已经编译好了的代码1.客户端取调用存储过程,执行效率就会非常高效语法:create [or replace] procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型)is | as--声明部分begin--业务逻辑 end; */--给指定员工涨薪,并打印涨薪前和涨薪后的工资/*参数 : in 员工编号参数 : in 涨多少声明一个变量 : 存储涨工资前的工资查询出当前是多少打印涨薪前的工资更新工资打印涨薪后的工资 */create or replace procedure proc_updatesal(vempno in number,vnum in number)is--声明变量.记录当前工资vsal number; begin--查询当前的工资select sal into vsal from emp where empno = vempno;--输出涨薪前的工资dbms_output.put_line('涨薪前:'||vsal);--更新工资update emp set sal = vsal + vnum where empno = vempno;--输出涨薪后的工资dbms_output.put_line('涨薪后:'||(vsal+vnum));--提交commit;end;--调用方式1call proc_updatesal(7788,10);--调用方式2 用的最多的方式declarebeginproc_updatesal(7788,-100);end;

十九、存储函数

1、基本语法和例子

存储函数: 实际上是一段封装是Oracle服务器中的一段PLSQL代码片断,它是已经编译好了的代码片段
​

语法: create [or replace] function 存储函数的名称(参数名 in|out 参数类型,参数名 in|out 参数类型) return 参数类型is | asbeginend;存储过程和函数的区别:1.它们本质上没有区别2.函数存在的意义是给过程调用 存储过程里面调用存储函数3.函数可以在sql语句里面直接调用4.存储过程能实现的,存储函数也能实现,存储函数能实现的,过程也能实现默认是 in */--查询指定员工的年薪/*参数 : 员工的编号返回 : 年薪 */create or replace function func_getsal(vempno number) return numberis--声明变量.保存年薪vtotalsal number; beginselect sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;return vtotalsal;end;--调用存储函数declarevsal number;beginvsal := func_getsal(7788);dbms_output.put_line(vsal);end;

2、java调用存储过程、存储函数

1）基本套路

/*JAVA调用存储过程JDBC的开发步骤:1.导入驱动包2.注册驱动3.获取连接4.获取执行SQL的statement5.封装参数6.执行SQL7.获取结果8.释放资源 *//*封装一个存储过程 : 输出所有表中的记录输出类型 : 游标 */create or replace procedure proc_getemps(vrows out sys_refcursor)isbegin--1.打开游标, 给游标赋值open vrows for select * from emp;end;

2）调用存储过程

/*** 查询指定员工的年薪，用存储过程实现* create or replace procedure proc_gettotalsal(vempno in number,vtotalsal out number)isbeginselect sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;end;* @throws Exception*/public void test1() throws Exception {//注册驱动Class.forName("oracle.jdbc.driver.OracleDriver");//获取连接String url="jdbc:oracle:thin:@106.13.43.205:1521:orcl";String user="canghe";String password="canghe";Connection conn = DriverManager.getConnection(url, user, password);//获取执行对象String sql="{call proc_gettotalsal(?,?)}";CallableStatement state = conn.prepareCall(sql);//为问号赋值state.setInt(1, 7654);//设置员工编号state.registerOutParameter(2, OracleTypes.NUMBER);//执行statementstate.execute();//获取结果int totle = state.getInt(2);//输出结果System.out.println(totle);//释放资源state.close();conn.close();}

3）调用存储函数

//调用存储函数/*create or replace function func_getsal(vempno number) return numberis--声明变量.保存年薪vtotalsal number; beginselect sal*12 + nvl(comm,0) into vtotalsal from emp where empno = vempno;return vtotalsal;end; */public void test2() throws Exception{//注册驱动Class.forName("oracle.jdbc.driver.OracleDriver");//2.获取连接String url = "jdbc:oracle:thin:@106.13.43.205:1521:orcl";String username = "canghe";String password = "canghe";Connection conn = DriverManager.getConnection(url, username,password);//3.获取执行SQL的statementString sql = " {?= call func_getsal(?)}";CallableStatement state = conn.prepareCall(sql);//4.封装参数//注册返回类型参数state.registerOutParameter(1, OracleTypes.NUMBER);//设置第二个参数state.setInt(2, 7788);//5.执行SQLstate.execute();//6.获取结果int totalsal = state.getInt(1);System.out.println("年薪 : ====" +totalsal);//7.释放资源state.close();conn.close();}

3）通过游标输出结果集

/*** 通过游标输出结果集* @throws Exception*/public void test3() throws Exception{//注册驱动Class.forName("oracle.jdbc.driver.OracleDriver");//2.获取连接String url = "jdbc:oracle:thin:@106.13.43.205:1521:orcl";String username = "canghe";String password = "canghe";Connection conn = DriverManager.getConnection(url, username,password);//3.获取执行SQL的statementString sql = "{call proc_getemps(?)}";CallableStatement call = conn.prepareCall(sql);//接口 --- > 对象 -->实现类的名称System.out.println(call.getClass().getName());OracleCallableStatement oracleCall = (OracleCallableStatement)call;//4.注册输出类型的参数call.registerOutParameter(1, OracleTypes.CURSOR);//5.执行SQLcall.execute();//6.获取执行的结果ResultSet resultSet = oracleCall.getCursor(1);while(resultSet.next()){int empno = resultSet.getInt("empno");String name = resultSet.getString("ename");System.out.println(empno +" ==== "+name);}//7.释放资源resultSet.close();call.close();conn.close();}

二十、触发器

触发器: 当用户执行了 insert | update | delete 这些操作之后, 可以触发一系列其它的动作/业务逻辑作用 : 在动作执行之前或者之后,触发业务处理逻辑插入数据,做一些校验语法:create [or replace] trigger 触发器的名称before | afterinsert | update | delete on 表名[for each row]declarebeginend;触发器的分类:语句级触发器: 不管影响多少行, 都只会执行一次行级触发器: 影响多少行,就触发多少次:old 代表旧的记录, 更新前的记录:new 代表的是新的记录 --新员工入职之后,输出一句话: 欢迎加入黑马程序员create or replace trigger tri_test1afterinserton empdeclarebegindbms_output.put_line('欢迎加入黑马程序员');end;insert into emp(empno,ename) values(9527,'HUAAN');--数据校验, 星期六老板不在, 不能办理新员工入职--在插入数据之前--判断当前日期是否是周六--如果是周六,就不能插入create or replace trigger tri_test2beforeinsert on empdeclare--声明变量vday varchar2(10);begin--查询当前select trim(to_char(sysdate,'day')) into vday from dual;--判断当前日期:if vday = 'saturday' thendbms_output.put_line('老板不在,不能办理入职');--抛出系统异常raise_application_error(-20001,'老板不在,不能办理入职');end if;end;insert into emp(empno,ename) values(9528,'HUAAN2');--更新所有的工资 输出一句话create or replace trigger tri_test3afterupdateon emp for each rowdeclarebegindbms_output.put_line('更新了数据');end;update emp set sal = sal+10;--判断员工涨工资后的工资一定要大于涨工资前的工资/*200 --> 100触发器 : before旧的工资 新的工资如果旧的工资大于新的工资 , 抛出异常,不让它执行成功 触发器中不能提交事务,也不能回滚事务 */create or replace trigger tri_updatesalbeforeupdateon empfor each rowdeclarebeginif :old.sal > :new.sal thenraise_application_error(-20002,'旧的工资不能大于新的工资');end if;end;update emp set sal = sal + 10;select * from emp;update emp set sal = sal - 100;/*模拟mysql中ID的自增属性 auto_increment insert into person(null,'张三'); 触发器:pid=1 insert pid=1序列 : create sequence seq_person_pid; */create table person(pid number primary key,pname varchar2(20) );insert into person values(null,'张三'); create sequence seq_person_pid;--触发器create or replace trigger tri_add_person_pidbeforeinserton personfor each rowdeclarebegindbms_output.put_line(:new.pname);--给新记录 pid 赋值select seq_person_pid.nextval into :new.pid from dual;end;insert into person values(null,'张三'); select * from person;