Oracle数据库深入学习

一、Oracle概念

oracle数据可系统是美国oracle（甲骨文）公司提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器或B/S体系结构的数据库之一，oracle数据库时目前世界SEO靠我上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完善的数据库管理功能，是关系型数据库，比mysql更为庞大，在现行的关系型数据库中排名第一（oracle、mysql、SqlServerSEO靠我），时间是最为精确的。

二、Oracle的安装

安装教程

三、Oracle数据库的体系结构

1、数据库database

Oracle数据库是数据的物理储存，这就包括（数据文件ORA或者DBF、控制文件、联机日志SEO靠我、参数文件）。其实Oracle数据库的概念和其他数据库不一样，这里的数据库时一个操作系统只有一个库，可以看做Oracle就只有一个大数据库。

2、实例

一个数据库可以有n个实例，有一系列的后台进程和内存结SEO靠我构组成。

3、数据文件dbf

是数据库的物理储存单位，数据库的数据是储存在表空间中的，真正是在某一个或者多个数据文件中，而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间，一旦数据文SEO靠我件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

4、表空间

表空间是Oracle对物理数据库上相关数据文件的逻辑映射，一个数据库在逻辑上被划分成一到若干SEO靠我个表空间，每个表空间包含了在逻辑上相关联的一组结构，每个数据库至少有一个表空间（称之为system表空间）

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件，一个数据文件只能属于一个表空间SEO靠我。

5、用户

用户是在实例下建立的，不同实例中可以建相同名字的用户，表的数据是由用户放入某一个表空间的，而这个表龙剑会随机把这些表数据进行管理和存放的，但是表不是由表空间去查询的，而是由用户去查询。

6、SSEO靠我COTT和HR用户

是Oracle帮我们建好的测试账户，Scott账户常用，里面有emp、dept表等。

四、Oracle和MYSQL的差别

1、新建项目的方式

MYSQL : 创建一个数据库,创建相应的表

2SEO靠我、Oracle是多用户的, MYSQL是多数据库的

3、Oracle安全级别要高,MYSQL开源免费

五、基本查询

1、SQL概念

结构化查询语言

2、SQL分类

DDL : 数据定义语言 create alteSEO靠我r drop truncate

DML : 数据操纵语言 insert update delete

DCL : 数据控制语言 安全 授权 grant revoke

DQL : 数据查询语言 select fSEO靠我rom子句 where子句

在oracle中将DQL放入DML中，多了TCL：事务控制语言 commit、rollback

3、Oracle中的数据字典

所谓数据字典就是编写sql语句，全选可以一次性执行不SEO靠我报错

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

3、查询语句的SEO靠我结构

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

4、伪表dual

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

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

5、别名查询

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

elect enSEO靠我ame 姓名, sal 工资 from emp; select ename "姓 名", sal 工资 from emp;

6、去除重复数据 distinct

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

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

7、查询中四则运算

需要用到伪表dual

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

在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符

8、空值处理函数

注意: null值 , 代表不确定的 不可预知的内容 , 不可以做SEO靠我四则运算

1）mysql的空值处理

​ ifnull(参数1, 参数2):

​ 判断参数1是否为null,

​ 不为null, 则函数的结果取参数1的值

​ 为null, 则取参数2的值

​ if(参数1, 参数SEO靠我2, 参数3)

​ 判断参数1是否为null,

​ 为null, 则取参数3的值

​ 不为null, 则取参数2的值

​ 注意if函数只能处理数值字段的空值SELECT e.`ename` 姓名,e.`salSEO靠我` 底薪,e.`comm` 奖金,(e.`sal`+e.`comm`) 实发,IFNULL(e.`sal`+e.`comm`, e.`sal`) 实发1,IF(e.`comm`, e.`sal`+e.SEO靠我`comm`, e.`sal`) 实发2 FROM emp e;

2）oracle的空值处理

nvl（参数1，参数2）：如果1为null，则返回2，不为null就为1

nvl2（参数1, 参SEO靠我数2, 参数3）1为null，则返回,3，不为null，则返回2

decode(需要判断的字段,

​ 常量值1, 结果值1,

​ 常量值2, 结果值2,

​ …

​ 常量值n, 结果值n,

​ 默认结果值

​ )sSEO靠我elect e.ename 姓名,e.sal 底薪,e.comm 奖金,nvl(e.sal+e.comm, e.sal) 实发1,nvl2(e.comm, e.sal+e.comm, e.sal) 实SEO靠我发2,decode(e.comm, null, e.sal, e.sal+e.comm) 实发3 from emp e;

9、字符串拼接

Oracle 特有的连接符: || 拼接concaSEO靠我t(str1,str2) 函数, 在mysql和Oracle中都有 --查询员工姓名 : 姓名:SCOTT select ename from emp; SEO靠我 --使用拼接符 select 姓名: || ename from emp;--使用函数拼接 select concat(姓名:,ename) from emp;

六、条SEO靠我件查询

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

1、关系运算符和排序

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

2、逻辑运算符

and or not

3、SEO靠我其它运算符

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

4、模糊查询: like

% 匹配多个字符

_ 匹配单个字符

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

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

5、排序order by

升序: asc aSEO靠我scend

降序: desc descend

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

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

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

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

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

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

row_number——不可并列排名

over——添加分组条件

partition—by——分组

6、分组排序

select 分SEO靠我组的条件，分组之后的条件 from 表名 group by 分组的条件 having 条件过滤在分组查询语句的检索内容中, 只能出现分组字段和聚合函数

sql的编写顺序：

​ select,from,whSEO靠我ere,group by,having,order by

sql的执行顺序：

​ from,where,group by,having,select,order by…

where和having的区别：

whSEO靠我ere后面不能跟聚合函数，可以接单行函数

having是在group by之后执行，可以接聚合函数--分组统计所有部门的平均工资，找出平均工资大于1500的部门 select deptnSEO靠我o,avg(sal) from emp group by deptno having avg(sal)>1500;--报错,给分组之后的条件取别名的时候出错，因为sql语句执行的顺序问题 SEO靠我 select deptno,avg(sal),bb from emp group by deptno having bb>1500;

七、函数

函数: 必须要有返回值

1、单行函数

对某一行中的某个值进行SEO靠我处理

1）数值函数

取整

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

四舍五入

round（a，b）

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

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

elect round(45.926,2) from dual; --45.93 seleSEO靠我ct round(45.926,1) from dual; -- 45.9 select round(45.926,0) from dual; --46 select SEO靠我round(45.926,-1) from dual; --50 select round(45.926,-2) from dual; --0 select roundSEO靠我(65.926,-2) from dual; --100

截断（去尾法）

select trunc(45.926,2) from dual; --45.92 select trunc(45SEO靠我.926,1) from dual; -- 45.9 select trunc(45.926,0) from dual; --45 select trunc(45.92SEO靠我6,-1) from dual; --40 select trunc(45.926,-2) from dual; --0 select trunc(65.926,-2)SEO靠我 from dual; --0

求余

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

2）字符函数

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

3） 日期函数

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

4） 转换函数

数值转字符

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

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

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

字符转日期

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

5） 通用函数

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

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

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

max() min count sum avg

注意：直接忽略空值

例子：

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

3、条件表达式

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

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

八、多表查询

1SEO靠我、笛卡尔积

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

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

2、内连接

隐式内联接:

​ 等值内联接SEO靠我: where e1.deptno = d1.deptno;

​ 不等值内联接: where e1.deptno <> d1.deptno;

​ 自联接: 自己连接自己

显式内联接:

​ select * fSEO靠我rom 表1 inner join 表2 on 连接条件

​ inner 关键字可以省略--查询员工编号,员工姓名,经理的编号,经理的姓名 select e1.empno,e1.enamSEO靠我e,e1.mgr,m1.ename from emp e1, emp m1 where e1.mgr= m1.empno;--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的SEO靠我姓名 select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename from emp e1, emp m1,dept d1 wheSEO靠我re e1.mgr= m1.empno and e1.deptno = d1.deptno;--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名,经理的部门名称 seleSEO靠我ct e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname from emp e1, emp m1,dept d1,dept d2 SEO靠我 where e1.mgr= m1.empno and e1.deptno = d1.deptnoand m1.deptno = d2.deptno ;--查询员工编号,员SEO靠我工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称 select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1SEO靠我.ename,d2.dname from emp e1, emp m1,dept d1,dept d2,salgrade s1 where e1.mgr= m1.empSEO靠我no and e1.deptno = d1.deptnoand m1.deptno = d2.deptnoand e1.sal between s1.losal and s1.hisal SEO靠我 ;--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级 select e1.empno,e1.ename,d1.dnaSEO靠我me,s1.grade,e1.mgr,m1.ename,d2.dname,s2.grade from emp e1, emp m1,dept d1,dept d2,salgrade sSEO靠我1,salgrade s2 where e1.mgr= m1.empno and e1.deptno = d1.deptnoand m1.deptno = d2.deptnoand eSEO靠我1.sal between s1.losal and s1.hisal and m1.sal between s2.losal and s2.hisal ;--查询员工编号,员工姓名,SEO靠我员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级 --将工资等级 1,2,3,4 显示成 中文的 一级 二级 三级...select e1.empSEO靠我no,e1.ename,d1.dname,case s1.gradewhen 1 then 一级--为所有列更换成你想要的信息when 2 then 二级when 3 then 三级when 4 thSEO靠我en 四级else五级end "等级",--取别名e1.mgr,m1.ename,d2.dname,decode(s2.grade,1,一级,2,二级,3,三级,4,四级,五级) "等级" SEO靠我 from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2 where e1.mgr= m1.empno and e1SEO靠我.deptno = d1.deptnoand m1.deptno = d2.deptnoand e1.sal between s1.losal and s1.hisal and m1.sal betwSEO靠我een s2.losal and s2.hisal ;--查询员工姓名和员工部门所处的位置 select e1.ename,d1.loc from emp e1,depSEO靠我t d1 where e1.deptno = d1.deptno;select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

2、SEO靠我外连接

外连接: (标准,通用写法)

​ 左外连接: left outer join 左表中所有的记录,如果右表没有对 应记录,就显示空

​ 右外连接: right outer join 右表中的所有记录,SEO靠我如果左表没有对应记录,就显示空

​ outer 关键字可以省略

Oracle中的外连接: (+) 实际上是如果没有对应的记录就加上空值

​ select * from emp e1,dept d1 wherSEO靠我e e1.deptno = d1.deptno(+);select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno; SEO靠我 insert into emp(empno,ename) values(9527,HUAAN); select * from emp e1,dept d1 where e1SEO靠我.deptno = d1.deptno(+);select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno; SEO靠我 select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;

九、子查询

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

1、单行子查询

=SEO靠我 = < <= <> !=

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

2、多行子查询

in not in >any >all exists not exists

通常情况下, 数据库中不要出现null 最好的做法加上Not null

​ null值并不代表不占空间, SEO靠我char(100) null 100个字符--查询不是领导的信息 select * from emp where empno not in (select mgr from emp);SEO靠我 select * from emp where empno <>all(select mgr from emp); --正确的写法 select * SEO靠我from emp where empno not in (select mgr from emp where mgr is not null);

3、exists(查询语句)

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

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

十、分页

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

伪列 : 在Oracle的结果集中默认带有的可以使用的

​ 不出现在结果集列中的内容.

​ rownum : 伪列序号, 从1开始

​ rowid : 伪列地址

SQL执行顺序

​ from … wheSEO靠我re …group by…having … select…rownum…order by

默认都是从1开始。

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

十一、集合运算

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

1、并集

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

union all : 不会去除重复的

select * from emp where sal > 1500 union select * from emp where SEO靠我deptno = 20;select * from emp where sal > 1500 union all select * from emp where depSEO靠我tno = 20;

2、交集运算: intersect

--工资大于1500,并且20号部门下的员工 select * from emp where sal > 1500; SEO靠我 select * from emp where deptno = 20;select * from emp where sal > 1500 intersect seSEO靠我lect * from emp where deptno = 20;

3、差集运算

两个结果相减

--1981年入职员工(不包括总裁和经理) --1981年入职员工 seleSEO靠我ct * from emp where to_char(hiredate,yyyy)=1981;--总裁和经理 select * from emp where job = PRESIDSEO靠我ENT or job = MANAGER;select * from emp where to_char(hiredate,yyyy)=1981 minus selecSEO靠我t * from emp where job = PRESIDENT or job = MANAGER;

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

1.列的类型要一致

2.按照顺序写

3.列的数量要一致,如果不足,用空值填充SEO靠我select ename,sal from emp where sal > 1500 union select ename,sal from emp where depSEO靠我tno = 20; --列的类型不匹配 select ename,sal from emp where sal > 1500 union SEO靠我 select sal,ename from emp where deptno = 20;--列的数量不匹配 select ename,sal,deptno from emp wherSEO靠我e sal > 1500 union select ename,sal from emp where deptno = 20;select ename,sal,deptSEO靠我no from emp where sal > 1500 union select ename,sal,null from emp where deptno = 20;SEO靠我select ename,sal,deptno from emp where sal > 1500 union select ename,sal,66 from empSEO靠我 where deptno = 20;select * from emp; select * from dept;

十二、DDL语句管理表

1、表空间

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

创建表空间

语法:

​ create tablespace 表空间的名称

​ datafile ‘文件的路径(服务器上)’

​ size 大小

​ autoextend SEO靠我on 自动扩展

​ next 每次扩展的大小--创建表空间- create tablespace canghe datafile C:\oracle\canghe.dbfSEO靠我 size 100m autoextend on next 10m;

删除表空间

--删除表空间 drop tablespace canghSEO靠我e;

2、创建用户

create user 用户名

identified by 密码

default tablespace 表空间的名称create user canghe identifieSEO靠我d by canghe default tablespace canghe; 授权 --授予 dba的角色 grant dba to SEO靠我canghe; select * from scott.emp;

3、创建表

create table 表名(

​ 列名 列的类型 [列的约束],

​ 列名 列的类型 [列的约束]

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

4、修改表

添加咧

alter table 表名 add 列

修改列

修改列类型：modify

修改列名：rename

删除咧

alter table 表名 drop column 列名;

修改SEO靠我列名

alter table 表名 rename column 列名;

重命名表

rename 旧表名 to 新表名;

5、删除表

drop table 表名

6、表的五大约束

主键约束

primary key

非空约SEO靠我束

not null

唯一约束

unique

外键约束

添加外键约束：

alter table 表名 add foreign key（添加约束的字段） references 引用的表名 （引用的字段）

插入：先主表SEO靠我、再从表

删除：先从表，再主表

强制删除表（不建议）：

drop table 表名 cascade constraint;

级联删除:

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

----添加外键约SEO靠我束,使用级联约束 ,在删除的时候,使用级联删除 alter table product add foreign key(cno) references category(cid) onSEO靠我 delete cascade;insert into category values(2,电脑办公); insert into product values(11,外星人,2);--SEO靠我级联删除 : 首先去从表中找有没有 关联数据, 如果在从表中找到关联数据,先删除从表中关联数据,然后再删除表中的数据 delete from category where cid = SEO靠我2;

检查约束

check( 列名 in (‘值1’,‘值2’,‘值3’))

十三、DML管理表数据

1、插入数据

指定列名插入

insert into 表名 values(所有列的值都要对应写上)

不指定列名插入SEO靠我

insert into 表名(列1,列2) values(值1,值2);

使用子查询插入

insert into 表名 查询语句

2、修改数据

update 表名 set 列名 = 列的值 [where 条件SEO靠我]

3、删除数据

delete from 表名 [where 条件]

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

十四、其他数据库对象

1、事务

事务: 就是一系列的操作,要么都成功,要么都失败

​ 四大特性: 原子性,隔离性,持久性,一致性

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

2、视图

视图: 是SEO靠我对查询结果的一个封装

​ 视图里面所有的数据,都是来自于它查询的那张表,视图本身不存储任何数据

​ 1.能够封装复杂的查询结果

​ 2.屏蔽表中的细节

​ 语法:

​ create [or replace] vSEO靠我iew 视图的名称 as 查询语句 [ with read only]

​ 注意: 通常不要通过视图去修改,视图创建的时候,通常要加上with read only--创建一个视图 creSEO靠我ate or replace view view_test1 as select ename,job,mgr from emp;--通过视图修改数据 update view_test1SEO靠我 set ename=SMITH2 where ename = SMITH;--创建一个只读视图 create or replace view view_test2 as selectSEO靠我 ename,job,mgr from emp with read only;update view_test2 set ename=SMITH3 where ename = SMITH2;

-同义词的SEO靠我概念（就是为视图取的一个别名）

create synonym dept for view_test3;

3、序列

oracle中用来控制自增长的

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

4、索引

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

十五、PLSQL编程

1、概SEO靠我述

procedure Language 过程语言 Oracle对SQL的一个扩展

​ 让我们能够像在java中一样写 if else else if 条件, 还可以编写循环逻辑 for while

2、基SEO靠我本语法

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

3、变量

%type --引用型变量

%rowtype --记录型变量

4、PL条件判断

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

5、循环操作

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

十六、游标

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

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

1、不带参数游标

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

2、带参数的游标

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

3、系统引用游标

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

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

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

十七、例外

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

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

十八、存储过程

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

十SEO靠我九、存储函数

1、基本语法和例子

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

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

2、jaSEO靠我va调用存储过程、存储函数

1）基本套路

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

2）调用存储过程

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

3）调用存储函数

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

3）通过游标输出结果集

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

二十、触发器

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