Oracle数据库基础

15 ORACLE数据库
15.1 数据库基础
15.1.1 什么是数据库？数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此 独立的数据集合，可视为电子化的文件柜——存储电子文件的处所
15.1.2 为什么使用数据库：存储数据、查询数据功能
15.1.3 怎样学好数据库
明白数据库基本概念，作用
表的知识
函数、存储过程、触发器
常见的简单查询、复杂查询[多表查询，子查询]
15.1.4 Oracle数据库和一般数据库不一样
Oracle创建一个数据库等同于创建一个数据库实例
15.2 DML（数据库操作语言）
15.2.1 select（查询语句）
15.2.2 update（修改语句）
15.2.3 insert（插入语句）
insert into 表名（列名）value（内容）
15.2.4 delete（删除语句）
15.3 DDL（数据库定义语言）
15.3.1 create table（创建表）
15.3.2 drop table（删除表）
15.4 DCL（数据库控制语言）
15.4.1 grant
15.4.2 revoke
15.5 Oracle的数据类型
15.5.1 number:(4,1)
15.5.2 char、nchar：定长查询
15.5.3 varchar、varchar2(保留字)：变长查询
15.5.4 date:年月日
副主题
15.5.5 datetime:年月日时分秒
15.5.6 timestamp:年月日时分秒+时区
15.6 主键:区分每条记录不同
15.6.1 不能重复
15.6.2 不能为null
15.6.3 推荐每个表有一个主键，并不是表必须要有主键
15.6.4 主键分类
自然主键:当前主键目的只是为了区分不同记录
代理主键:当前主键目的只是为了区分不同记录,而且具有一定业务意义
15.7 常见函数处理
15.7.1 – 字符串转日期 select to_date(‘2019-10-01’,‘yyyy-MM-dd’) from dual;
15.7.2 – 日期转字符串 select to_char(sysdate,‘yyyy-MM-dd’) from dual;
15.7.3 – 按员工薪水高到低,默认是升序的(asc)，降序desc select ename,sal from emp order by sal asc;
15.7.4 – 选择员工姓名的第三个字母是a的员工信息 _:1个字符 %:0-n个字符 %A% select ename,job from emp where ename like ‘__A%’;
15.7.5 – 对null值的处理 – 算出所有员工的年薪 – 数据库中任何数据和null运算，最后结果为null – nvl(){nvl(comm,0)第一个参数为null返回第二个参数}、nvl2(){第一个参数为null返回第三个参数，不为null返回第二个参数} select ename,sal * 12 + nvl2(comm,comm,0) from emp;
15.7.6 select 字段名 from 表名 where 条件 group by havaing 分组条件 order by
15.7.7 – 去掉重复记录(值一模一样) select distinct job from emp;
15.7.8 判断一个值为空为is null而不是=null
15.7.9 select * from emp where sal not between 1000 and 2000;-- 包含边界
15.7.10 select count（）计算数量
15.7.11 – 去掉两边空格，不会去掉中间空格 select trim(’ ab cd ‘) from dual; select replace(‘jack and jue’,‘j’,‘bl’) from dual;
15.7.12 – dual:虚表，为了做临时查询测试使用的 select lower(‘ABC’) from dual; select upper(‘abc’) from dual; select initcap(‘abc’) from dual; select concat(‘a’,‘b’) from dual; select substr(‘abcdefg’,2) from dual; select length(‘abc’) from dual; select lpad(‘abc’,15,’’) from dual; select rpad(‘abc’,15,’’) from dual;
15.8 关系型数据库:表与表之间可以进行关联 主外键进行关联
15.9 笛卡尔积
15.9.1 笛卡尔集
– 查询员工及其所在的部门信息 重复记录现象:笛卡儿积 select * from emp,dept; – 去掉笛卡儿积 select e.,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;
15.10 连接查询
15.10.1 内连接
– 内链接 select e.,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno;
15.10.2 外连接
– 右外连接 以右表为主 select e.,d.dname,d.loc from emp e right join dept d on e.deptno=d.deptno;
– 左外连接 以左表为主 select e.,d.dname,d.loc from emp e left join dept d on e.deptno=d.deptno;
– 全外连接 select e.,d.dname,d.loc from emp e full outer join dept d on e.deptno=d.deptno;
15.10.3 子查询（跟在查询中的查询）
select后的子查询 from后的子查询 where后的子查询
子查询的结果集 单行单列 单行多列 多行单列 多行多列
– 查询工资>scott的工资的员信息 select ename,sal from emp where sal > (select sal from emp where ename=‘SCOTT’);
– 查询工资最低的员工信息 select ename,sal from emp where sal = (select min(sal) from emp);
– 单行子查询nul值问题 select ename,job from emp where job=(select job from emp where ename=‘MIKE’);
– 多行子查询 – 查询大于本部门平均工资的员工的信息 – 黄灯注意:where后不能使用聚合函数，但是havaing,order by select ename,sal,e1.deptno from emp e1, (select deptno,avg(sal) av from emp e group by deptno) e2 where e1.sal > e2.av and e1.deptno=e2.deptno;
15.11 分组、聚合函数
15.11.1 min
15.11.2 max
15.11.3 avg
15.11.4 sum
15.11.5 count:统计满足条件的记录数
count() count(数字[不受限制]) count(字段名):如果列值为null值，不加入计数
select count() from emp; select count(1) from emp; select count(comm) from emp;
– 统计每年入职的员工的人数 ??? select sum(e.cnum),extract(year from e.hiredate) from (select count() cnum,extract(year from hiredate),hiredate from emp group by extract(year from hiredate),hiredate) e ;
15.12 分页要素
15.12.1 页码:page 当前页:pageNow 满足条件总记录数:total 100 每页记录数:pageSize 3 总页数:total%pageZie == 0 ? total / pageSize : total / pageSize + 1 每页的具体数据
Oracle使用rownum(伪列) 1、里面的2层子查询目的是为了让rownum重新排序(对结果集进行排序等操作rownum会编程无序的) 2、最终查询的字段以最内层的字段为准 page start end 1 1 3 2 4 6 3 7 9 4 10 12 start = (page - 1) * pageSize + 1 end = page * pageSize
15.12.2 select * from (select rm.,rownum r1 from (select ename,job,sal from emp where sal > 2000) rm) where r1 >=4 and r1 <= 6;
15.13 约束:保证数据的完整性
15.13.1 主键约束:唯一且不能null
15.13.2 外键约束:不给存在外键的主表删除
15.13.3 惟一约束:唯一但是可以为null
15.13.4 非空约束:不能为null
15.13.5 检查约束:check约束
15.14 删除
15.14.1 delete
delete删除数据的时候会记录日志
15.14.2 truncate
truncate不会记录日志
数据量大的时候采用truncate速度快
15.14.3 只删除数据，都留下表结构
15.15 事务
15.15.1 标准:事务要么同时成功，要么同时失败
15.15.2 事务:保证在一定的业务场景下的一个整体
15.15.3 特征
原子性
一致性
隔离性
脏读:脏读发生在一个事务A读取了被另一个事务B修改，但是还未提交的数据。 幻读:操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。 不可重复读:这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。 针对事务的隔离性出现的问题，采用事务的隔离级别来界别 读未提交:都解决不了 读已提交:解决脏读 oracle 可重复读:解决脏读和不可重复度 mysql 串行化:都能解决
持久性
15.16 触发器
15.16.1

15.17 序列
15.17.1 是Oracle特有的功能，适用于表主键是整数且递增

15.18 索引
15.18.1 提高查询效率，占用磁盘空间，主键默认有索引
15.18.2 使用场景：1、根据主键查询；2、外键查询；3、where 条件查询
15.18.3 注意：1、占用磁盘空间；2、数量大的时候，会降低查询效率
15.19 视图
15.19.1 提高查询效率；是一个表 的投影，占用磁盘空间的
create view emp_view as select * from emp; select * from emp_view
15.20 程序包
15.20.1

15.20.2

16 JDBC：使用不同语言操作不同的数据库（当前使用java操作Oracle数据库）
16.1 1、将ojdbc14的jar包复制到要创建连接的项目包
16.2 2、右键项目–Build Path --Add Library–JUnit–选择JUnit4–Finally
16.3 3、右键项目–Properties–Libraries–Add JARs–选中粘贴到项目里的ojdbc14的jar包–Apply and Close
16.4 4.创包，创类，编写代码
16.4.1 @Test
16.4.2 加载驱动
Class.forName(“oracle.jdbc.OracleDriver”);
16.4.3 获取连接
Connection connection = DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:orcl”,“用户名”,“登陆口令”);
16.5 实体类包名
16.5.1 pojo

16.5.2 domain
16.5.3 entity
16.5.4 bean
16.6 数据库连接池:管理数据库的连接
16.6.1 先和数据库创建10(自定义大小)连接[连接池],如果操作数据库需要使用连接从连接池中取连接 ,如果暂时不用，将该连接回收到连接池中

16.6.2 通过方法获取数据库连接方法

16.7 优化代码过程
16.7.1 写一个获取连接的通用方法
16.7.2 使用PreparedStatement替换Statement
16.7.3 使用对象封装参数
16.7.4 编写通用的添加方法(注意日期类的处理)
16.8 PreparedStatement（表示预编译的 SQL 语句的对象）
16.8.1 预编译过程
16.8.2 防止sql注入漏洞
16.9 日期类的工具类
16.9.1 SimpleDateFormat可以选择任何用户定义的日期-时间格式的模式

16.10 通用的增删改方法
16.10.1

16.10.2 params[i]是从1开始的
16.10.3

16.10.4

16.10.5

16.11 查询所有数据方法
16.11.1

16.11.2

16.12 分页工具类
16.12.1

16.13 事务提交回滚
16.13.1

17 PL/SQL编程语句
17.1 – 打开控制台输出流 set serveroutput on; – 匿名块 declare 说明部分（变量说明，光标申明，例外申明） begin 语句序列（DML语句） end;
17.2 使用匿名块将查询的单行单列的结果放入到变量中
17.2.1 set serveroutput on; declare – empname varchar2(20); empname emp.ename%type;-- 指定定义的变量和表中对应字段的数据类型一样 begin select ename into empname from emp where empno=7369; dbms_output.put_line(empname); end;
17.3 使用rowtype定义一行记录变量
17.3.1 set serveroutput on; declare emp_row emp%rowtype;-- 记录变量 begin select * into emp_row from emp where empno=7369; dbms_output.put_line(emp_row.ename || emp_row.sal || emp_row.hiredate); end;
17.4 使用匿名块给定义变量赋值
17.4.1 set serveroutput on; declare empname varchar2(20); esal float; sex varchar2(4):=‘男’; begin dbms_output.put_line(sex); end;
17.5 loop循环
17.5.1 set serveroutput on; declare i number:=1; j number:=1; begin loop exit when i = 10; loop exit when j = i + 1; dbms_output.put(i || ‘’ || j || ‘=’ || i * j || ’ ‘); j := j + 1; end loop; dbms_output.put_line(’’); --换行 i := i + 1; j := 1; end loop; end;
17.6 for循环
17.6.1 set serveroutput on; declare num number:=9; begin for i in 1…num loop exit when i = 10; for j in 1…i loop exit when j = (i + 1); dbms_output.put(i || '’ || j || ‘=’ || i * j || ’ ‘); end loop; dbms_output.put_line(’’); end loop; end;
17.7 while循环
17.7.1 set serveroutput on; declare i number:=1; j number:=1; begin while i <= 9 loop while j <= i loop dbms_output.put(i || ‘*’ || j || ‘=’ || i * j || ’ ‘); j := j + 1; end loop; j := 1; dbms_output.put_line(’’); --换行 i := i + 1; end loop; end;
17.8 游标遍历结果集
17.8.1 set serveroutput on; declare cursor emp_cursor is select * from emp; – 查询出emp表的结果集，让游标指向该结果集 emp_row emp%rowtype; begin open emp_cursor;–打开游标 loop fetch emp_cursor into emp_row;–取一行到变量中 exit when emp_cursor % notfound;–游标关闭条件 dbms_output.put(emp_row.ename || ‘:’ || emp_row.sal); dbms_output.put_line(’’); end loop; close emp_cursor;–关闭游标 end;
17.9 存储过程 注意:参数(in/out:输入输出参数)
17.9.1

create or replace procedure updateSal(eno in emp.empno%type,num out number) is oldSal emp02.sal%type; empName emp02.ename%type; newSal emp02.sal%type; begin num := 0;–out不可以再is后面定义，只有in可以 select ename,sal into empName,oldSal from emp02 where empno=eno; update emp02 set sal = sal + 100 where empno=eno; select sal into newSal from emp02 where empno=eno; dbms_output.put(empName); dbms_output.put_line(oldSal || ‘:’ || newSal); num := num + 1; end updateSal; set serveroutput on; call updateSal(7369);–传入参数运行语句
– 利用存储过程查询工资>1500的员工信息 create or replace procedure querySal(eSal in emp.sal%type,outResult out SYS_REFCURSOR) is begin open outResult for select * from emp where sal > eSal; end;
17.10 存储参数 如果无参函数不用加括号
17.10.1

create or replace function queryAllFun return SYS_REFCURSOR is emp_cursor SYS_REFCURSOR; begin open emp_cursor for select * from emp; return emp_cursor; end;
17.11 java调用存储过程
17.11.1

17.11.2

17.12 java调用存储函数 区别在于传入参数
17.12.1

17.13 java封装结果集
17.13.1