1.oracle数据库的体系结构
1.1数据库
1.2实例
1.3用户
1.4表空间
1.5数据文件
2.创建表空间
-- 创建表空间 create tablespace itheima datafile 'e:\itheima.dbf' size 100m -- 后定义的是表空间的初始大小 autoextend on --自动增长 ，当表空间存储都占满时，自动增长 next 10m; --后指定的是一次自动增长的大小 -- 删除表空间 drop tablespace itheima;
3.用户
3.1创建用户
-- 创建用户 create user itheima identified by itheima default tablespace itheima;
3.2用户授权
-- 给用户授权 -- oracle数据库中常用授权 connect -- 连接角色，基本角色 resource -- 开发者角色 dba -- 超级管理员角色 -- 给itheima用户授予dba角色 grant dba to itheima;
4.oracle数据类型
5.表的管理
5.1建表
-- 创建一个person表 create table person( pid number(20), pname varchar2(10) );
5.2修改表结构
-- 修改表结构 ---- 添加一列 alter table person add (gender number(1)); ---- 修改列类型 alter table person modify gender char(1); ---- 修改列的名称 alter table person rename column gender to sex; ---- 删除一列 alter table person drop column sex;
5.3数据的增删改查
---- 查看表中记录 select * from person; ---- 添加一条记录 insert into person(pid,pname) values (1,'小明'); commit; ---- 修改表中的记录 update person set pname = '小马' where pid = 1; commit; --三个删除 ---- 删除表中全部记录 delete from person; ---- 删除表结构 drop table person; ---- 先删除表，再次创建表，效果等同于删除表中的全部记录 ---- 在数据量大的情况下，尤其表中带有索引的情况下，该操作效率高。 truncate table person;
6.序列
-- 序列不真的属于任何一张表，但是可以逻辑和表做绑定 -- 序列：默认从1开始，依次递增，主要用来给主键赋值使用 -- dual：虚表，只是为了补全语法，没有任何意义。 create sequence s_person; select s_person.nextval from dual; select s_person.currval from dual; insert into person(pid,pname) values (s_person.nextval,'小明');
7.单行函数
-- 单行函数 ---- 字符函数 select upper('yes') from dual; -- YES select lower('YES') from dual; -- yes ---- 数值函数 select round(26.18,1) from dual; -- 26.2,四舍五入 select trunc(26.18,1) from dual; -- 26.1直接截取 select mod(10,3) from dual; -- 1,求余 ---- 日期函数 ----查询出emp表中所有员工入职时间距离现在多少天 select sysdate - e.hiredate from emp e; ---- 查询明天此刻 select sysdate + 1 from dual; ----查询出emp表中所有员工入职时间距离现在几个月 select months_between(sysdate , e.hiredate) from emp e; ----查询出emp表中所有员工入职时间距离现在几年 select months_between(sysdate , e.hiredate) /12 from emp e; ----查询出emp表中所有员工入职时间距离现在几周 select round((sysdate - e.hiredate)/7 ) from emp e; -- 转换函数 ---- 日期转字符串 select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;--2020-04-05 11:58:20 select to_char(sysdate, 'fm yyyy-mm-dd hh:mi:ss') from dual; -- 2020-4-5 11:58:48 select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual; --2020-4-5 11:59:15,24小时制 ---- 字符串转日期 select to_date('2020-4-5 11:59:15' , 'fm yyyy-mm-dd hh24:mi:ss') from dual; --2020/4/5 11:59:15日期类型 --通用函数 ----算出emp表中所有员工的年薪 ----奖金里面有null值 ----如果null值和任意数字做运算，结果都是null select e.sal*12+nvl(e.comm,0) from emp e; -- nvl(a,b)如果a是null，则取b的值；否则，取a的值
8.条件表达式
--条件表达式 ----条件表达式的通用写法，mysql和oracle通用 ----给emp表中员工起别名 select e.ename, case e.ename when 'SMITH' then '小s' when 'ALLEN' then '小a' when 'WARD' then '小w' else '无名' end from emp e; ----判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，其余显示低收入 select e.sal, case when e.sal>3000 then '高收入' when e.sal>1500 then '中等收入' else '低收入' end from emp e; ----oracle条件表达式专用 ----oracle中除了起别名，都用单引号 select e.ename, decode( e.ename, 'SMITH', '小s', 'ALLEN' , '小a', 'WARD' , '小w', '无名' ) 中文名 -- 中文名3个字可以不加引号，或者加双引号 from emp e;
9.分组查询
---- 分组查询 ----查询出每个部门的平均工资 ----分组查询中出现在group by后面的原始列，才能出现在select后面； ----没有出现在group by后面的列，想在select后面，必须加上聚合函数。 select e.deptno, avg(e.sal) from emp e group by e.deptno; ----查出平均工资高于2000的部门信息 select e.deptno, avg(e.sal) asal from emp e group by e.deptno having avg(e.sal) > 2000; --having asal > 2000; 所有查询条件都不能使用别名来判断 ----查询出每个部门高于800的员工的平均工资 select e.deptno, avg(e.sal) asal from emp e where e.sal > 800 group by e.deptno; ----where是过滤分组前的数据，having是过滤分组后的数据 ----表现形式：where必须在group by 之前，having是在group by 之后 ----查询出每个部门高于800的员工的平均工资，且平均工资高于2000的部门 select e.deptno, avg(e.sal) asal from emp e where e.sal > 800 group by e.deptno having avg(e.sal) > 2000;
10.多表查询
----等值连接 select * from emp e, dept d where e.deptno = d.deptno; ----内连接 select * from emp e inner join dept d on e.deptno = d.deptno; ----外连接 ----查询所有部门，以及部门下的员工信息 select * from emp e right join dept d on e.deptno = d.deptno; ----查询所有员工信息，以及员工所属部门 select * from emp e left join dept d on e.deptno = d.deptno; ----oracle 中专用外连接 ----查询所有部门，以及部门下的员工信息 select * from emp e , dept d where e.deptno(+) = d.deptno; ----查询所有员工信息，以及员工所属部门 select * from emp e , dept d where e.deptno = d.deptno(+);
11.自连接
----等值连接 select * from emp e, dept d where e.deptno = d.deptno; ----内连接 select * from emp e inner join dept d on e.deptno = d.deptno; ----外连接 ----查询所有部门，以及部门下的员工信息 select * from emp e right join dept d on e.deptno = d.deptno; ----查询所有员工信息，以及员工所属部门 select * from emp e left join dept d on e.deptno = d.deptno; ----oracle 中专用外连接 ----查询所有部门，以及部门下的员工信息 select * from emp e , dept d where e.deptno(+) = d.deptno; ----查询所有员工信息，以及员工所属部门 select * from emp e , dept d where e.deptno = d.deptno(+); select * from emp; ----查询出员工姓名，员工领导姓名 ----自连接：自连接其实就是站在不同的角度，把一张表看成多张表 select e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno; ---查询出员工姓名，员工部门名称，员工领导姓名，员工领导部门名称 select e1.ename, d1.dname,e2.ename,d2.dname from emp e1, emp e2, dept d1, dept d2 where e1.mgr = e2.empno and e1.deptno = d1.deptno and e2.deptno = d2.deptno;
12.子查询
----子查询返回一个值 --------查询出工资和SCOTT一样的员工信息 select * from emp where sal in (select sal from emp where ename = 'SCOTT'); ----子查询返回一个集合 --------查询出工资和10号部门任意一个员工一样的员工信息 select * from emp where sal in (select sal from emp where deptno = 10); ----子查询返回一张表 -------查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称 -------1.先查询出每个部门最低工资 select deptno, min(sal) msal from emp group by deptno; ------2.三表联查，得到最终结果 select t.deptno, t.msal, e.ename ,d.dname from (select deptno, min(sal) msal from emp group by deptno) t, emp e, dept d where t.deptno = e.deptno and t.msal = e.sal and e.deptno = d.deptno;
13.分页查询
---oracle中的分页 ---rownum行号：当我们做select操作的时候 ---每查询出一行记录，就会在该行上加一个行号 ---行号从1开始，依次递增，不能跳着走 ----排序操作会影响rownum的顺序 select * from emp e order by e.sal desc ---emp表工资倒叙排列后，每页5条记录，查询第二页 select * from( select rownum rn, e.* from( select * from emp order by sal desc ) e where rownum < 6 ) where rn >3;
14.plsql编程语言定义变量
----pl/sql编程语言 ----pl/sql编程语言是sql语言的扩展，使得sql语言具有过程化编程的特性。 ----pl/sql编程语言比一般的过程化编程语言，更加灵活高效。 ----pl/sql编程语言主要用来编写存储过程和存储函数等。 ----声明方法 ----赋值操作可以使用：= 也可以使用into查询语句赋值 declare i number(2) := 10; s varchar2(10) := '小明'; ena emp.ename%type;----引用型变量 emprow emp%rowtype;----记录型变量 begin dbms_output.put_line(i); dbms_output.put_line(s); select ename into ena from emp where empno = 7788; dbms_output.put_line(ena); select * into emprow from emp where empno = 7788; dbms_output.put_line(emprow.ename||'的工作为：'||emprow.job); end;
15.plsql中的if判断
----pl/sql中的if判断 ----输入小于18的数字，输出未成年 ----输入大于18小于40的数字，输出中年人 ----输入大于40的数字，输出老年人 declare i number(3):=ⅈ----输入数字 begin if i<18 then dbms_output.put_line('未成年'); elsif i<40 then dbms_output.put_line('中年人'); else dbms_output.put_line('老年人'); end if; end;
16.pl/sql中的循环
----pl/sql中的循环 --用三种方式输出1到10 --1.while循环 declare i number(2) := 1; begin while i<11 loop dbms_output.put_line(i); i := i+1; end loop; end; --2.exit循环 declare i number(2) :=1; begin loop exit when i>10; dbms_output.put_line(i); i := i+1; end loop; end; ----3.for循环 declare begin for i in 1..10 loop dbms_output.put_line(i); end loop; end;
17.plsql中的游标
----游标：可以存放多个对象，多行记录。 -----输出emp表中所有员工的姓名 declare cursor c1 is select * from emp; emprow emp%rowtype; begin open c1; loop fetch c1 into emprow; exit when c1%notfound; dbms_output.put_line(emprow.ename); end loop; close c1; end;
----给指定部门员工涨工资 declare cursor c2(eno emp.deptno%type) is select empno from emp where deptno = eno; en emp.empno%type; begin open c2(10); loop fetch c2 into en; exit when c2%notfound; update emp set sal = sal+100 where empno = en; commit; end loop; close c2; end; ----查询10号部门员工信息 select * from emp where deptno = 10;
18.存储过程
----存储过程 ----存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端 --------可以直接被调用。这一段plsql一般都是固定步骤的业务。 -----给指定员工涨100块钱 create or replace procedure p1(eno emp.empno%type) is begin update emp set sal = sal+100 where empno = eno; commit; end; select * from emp where empno=7788; ----测试p1 declare begin p1(7788); end;
19.存储函数
-----通过存储函数实现计算指定员工的年薪 ----存储过程和存储函数的参数都不能带长度 ----存储函数的返回值类型不能带长度 create or replace function f_yearsal(eno emp.empno%type) return number is s number(10); begin select sal*12+nvl(comm,0) into s from emp where empno =eno; return s; end; ----测试f_yearsal ----存储函数在调用的时候，返回值需要接收 declare s number(10); begin s:= f_yearsal(7788); dbms_output.put_line(s); end;
20.out类型参数如何使用
-----out类型参数如何使用 ----使用存储过程来算年薪 create or replace procedure p_yearsal(eno emp.empno%type,yearsal out number) is s number(10); c emp.comm%type;--奖金 begin select sal*12,nvl(comm,0) into s, c from emp where empno=eno; yearsal := s+c; end; ----测试p_yearsal declare yearsal number(10); begin p_yearsal(7788, yearsal); dbms_output.put_line(yearsal); end; ----in和out类型参数的区别是什么？ ----凡是涉及到 into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。
21.存储过程和存储函数的区别
----存储过程和存储函数的区别 --------语法区别：关键字不一样 ------------存储函数比存储过程多了两个return --------本质区别：存储函数有返回值，而存储过程没有返回值 ------------即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值， ------------而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值 ----我们可以使用存储函数有返回值的特性，来自定义函数。 ----而存储过程不能用来自定义函数。 --------案例需求：查询出员工姓名，员工所在部门的名称。 ----使用传统方式来实现案例需求 select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno; ----使用存储函数来实现提供一个部门编号，输出一个部门名称 create or replace function fdna(dno dept.deptno%type) return dept.dname%type is dna dept.dname%type; begin select dname into dna from dept where deptno = dno; return dna; end; -----使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门的名称 select e.ename, fdna(e.deptno) from emp e;
22.触发器的概念和分类
----触发器，就是制定一个规则，在我们做增删改操作的时候， ----只有满足该规则，自动触发，无需调用。 --------语句级触发器：不包含有for each row的触发器。 --------行级触发器：包含有for each row的就是行级触发器。 -------------------------加 for each row 是为了使用:old或者:new --------语句触发器 --------插入一条记录，输出一个新员工入职 create or replace trigger t1 after insert on person declare begin dbms_output.put_line('新员工入职'); end; ------触发t1 insert into person values(1,'小红'); commit; select * from person;
插入一条记录之后，控制台将输出
-----行级触发器 -----不能给员工降薪 ------ raise_application_error(-20001~-20999之间，'错误提示信息') create or replace trigger t2 before update on emp for each row declare begin if :old.sal > :new.sal then raise_application_error(-20001,'不能给员工降薪'); end if; end; ---------触发t2 update emp set sal = sal-1 where empno = 7788; commit; select * from emp where empno = 7788;
更新员工的工资后将抛出异常提示
23.触发器实现主键自增
------触发器实现主键自增 --------分析：在用户插入操作之前，拿到即将插入的数据，给该数据中的主键列赋值。 create sequence s_person;----创建一个序列 create or replace trigger auid before insert on person for each row declare begin select s_person.nextval into :new.pid from dual; end; select * from person; -----使用auid实现主键自增 insert into person (pname) values('a'); commit; insert into person (pname) values('b'); commit;
24.java连接oracle
oracle10g-----------------ojdbc14.jar
oracle11g-----------------ojdbc6.jar
package oracle_learning; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class TestDemo { public static void main(String[] args) throws Exception { //加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger"); //得到预编译的Statement对象 PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?"); //给参数赋值 pstm.setObject(1, 7788); //执行数据库查询操作 ResultSet rs = pstm.executeQuery(); //输出结果 while(rs.next()){ System.out.println(rs.getString("ename")); } //释放资源 rs.close(); pstm.close(); connection.close(); } }
25.java调用存储过程
package oracle_learning; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import oracle.jdbc.OracleTypes; /** * java调用存储过程 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用 */ public class JavaCallProcedure { public static void main(String[] args) throws Exception { //加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger"); //得到预编译的Statement对象 CallableStatement cstm = connection.prepareCall("{call p_yearsal(?, ?)}"); //给参数赋值 cstm.setObject(1, 7788); cstm.registerOutParameter(2, OracleTypes.NUMBER); //执行数据库查询操作 cstm.execute(); //输出结果[第二个参数] System.out.println(cstm.getObject(2)); cstm.close(); connection.close(); } }
26.java调用存储函数
package oracle_learning; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import oracle.jdbc.OracleTypes; /** * java调用存储函数 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用 * {call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储过程使用 */ public class JavaCallFunction { public static void main(String[] args) throws Exception { //加载数据库驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //得到Connection连接 Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger"); //得到预编译的Statement对象 CallableStatement cstm = connection.prepareCall("{?= call f_yearsal(?)}"); //给参数赋值 cstm.setObject(2, 7788); cstm.registerOutParameter(1, OracleTypes.NUMBER); //执行数据库查询操作 cstm.execute(); //输出结果[第二个参数] System.out.println(cstm.getObject(1)); cstm.close(); connection.close(); } }
