jdbc连接oracle、oracle函数、高级查询

一、课程目标

【掌握】jdbc连接Oracle
【理解】Oracle函数
【理解】Oracle高级查询

二、Oarcle的连接

1、导入jar包

2、注册驱动

3、创建连接

4、准备sql进行预编译

5、执行处理结果集

6、关闭连接释放资源

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class OracleJDBC {
    public static void main(String[] args) throws Exception {
//        1、导入jar包
//        2、注册驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
//        3、创建连接
        Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "ahx", "123456");
//        4、准备sql进行预编译
//        String sql="update dept set loc=? where deptno=? ";
        String sql = "select * from dept ";
        PreparedStatement ps = con.prepareStatement(sql);
//        ps.setObject(1,"test");
//        ps.setObject(2,5);
//        int i = ps.executeUpdate();
        ResultSet rs = ps.executeQuery();
//        5、执行处理结果集
        while (rs.next()) {
            for (int i = 1; i <= 4; i++) {
                System.out.print(rs.getObject(i) + " ");
            }
            System.out.println();
        }

//        6、关闭连接释放资源
        rs.close();
        ps.close();
        con.close();
    }
}

三、Oracle函数

3.1 字符串函数

initcap将首字母转换为大写

select initcap('hello') from dual;

lower将所有字母转换为小写

select lower('HELLO') from dual

upper将所有字母转换为大写

select upper('hello') from dual

ltrim将执行字符串左侧删除

select ltrim('hello','he') from dual

ltrim将执行字符串右侧删除

select rtrim('hello','llo') from dual

translate按位进行替换

select translate('hello','e','1') from dual

查找字符串出现位置

从1开始

select instr('hello','e') from dual

substr截取字符串

第一个数值为起始位置，第二个数字为截取长度

select substr('hello',3,2) from dual

concat拼接字符串

select concat('hello',' world!') from dual

replace替换字符串

将操作字符串中所有的指定字符替换为另一个字符串

select replace('hello','ll','r') from dual

3.2 日期函数

months_between返回两个月之间的月份

如果不满一个月返回小数

select months_between(birthday2,birthday1) from test;

add_months在指定日期月份添加指定月

select add_months(birthday1,-1) from test;

next_day获取指定日期后的第一个指定星期

select next_day (birthday1,'fri') from test;

last_day获取指定日期当月最后一天

select last_day (birthday2) from test;

round对指定日期四舍五入

按照year、month、day分别对日期进行四色五入

select round (birthday2,'day') from test;

trunc截取日期

只获取指定year的数据

3.3 转换函数

to_number(数值类型的字符)：将字符转换为数值

select to_number('1234124214') from dual;

to_char(数值或者是日期)：将数值或者日期转换为字符

可以设置转换的样式进行转换

select birthday1,substr(to_char(birthday1,'yyyy-MM-dd'),1,4)  from test;

to_date(‘要转的字符’,‘日期格式’)函数将字符转换为日期

select to_date('2020-01-01','yyyy-mm-dd') from dual;

3.4 数字函数

dbms_random.value(1,100)随机数函数

select ceil(dbms_random.value(1,100)) from dual;

四、Oracle高级查询

4.1 多表查询

内连接: inner join 只展示两个表向关联的数据

--等值连接
select  e.empno,e.ename,e.sal,d.dname from emp e, dept d where e.deptno=d.deptno;
--内连接
select  e.empno,e.ename,e.sal,d.dname from emp e inner join dept d on(e.deptno=d.deptno)

左连接： left join 左表展示所有数据，右表只展现向关联的数据

select  e.empno,e.ename,e.sal,d.dname from emp e left join dept d on(e.deptno=d.deptno)
select  e.empno,e.ename,e.sal,d.dname from emp e ,dept d  where e.deptno=d.deptno(+);

右连接: right join 右表展示所有数据，左表只展现向关联的数据

select  e.empno,e.ename,e.sal,d.dname from emp e right join dept d on(e.deptno=d.deptno)
select  e.empno,e.ename,e.sal,d.dname from emp e ,dept d where e.deptno(+)=d.deptno;

交叉连接: cross join 笛卡尔乘积

select * from emp,dept
select  e.empno,e.ename,e.sal,d.dname from emp e cross join dept d 

全连接: full join

select  e.empno,e.ename,e.sal,d.dname from emp e full join dept d on(e.deptno=d.deptno)

注意：如果进行分组查询，只能查询分组字段与聚合函数字段，因为其余字段在进行聚合后都没有实际的意义

4.2 子查询

就是将其他sql查询的结果当做当前sql使用的数据的值继续查询

如果返回的是单个数值可以直接使用=进行判断，如果返回的是多行数据一般使用in或者not in 进行范围操作

--查询平均工资>2000,的部门编号,部门名称，平均工资
--平均工资>2000的部门编号

--查询平均工资>2000的部门编号的部门编号和平均工资
select deptno,avg(sal)from emp group by deptno having avg(sal)>2000
 
select d1.deptno,d1.dname,d2.savg from dept d1 inner join 
       (select deptno,avg(sal) savg from emp group by deptno having avg(sal)>2000) d2 --将查询的结果看成一张表进行连接查询
       on (d1.deptno=d2.deptno)      
 
--查询平均工资>2000,的部门编号,部门名称
--查询平均工资>2000的部门编号
select deptno from emp group by deptno having avg(sal)>2000

select deptno,dname from dept where deptno in(select deptno from emp group by deptno having avg(sal)>2000)
--查询平均工资>2000,的部门编号,部门名称，平均工资
select d.deptno,d.dname,avg(e.sal) from emp e,dept d where e.deptno=d.deptno 
group by d.deptno,d.dname having avg(e.sal)>2000

4.3 分页查询

分页的原因如下：

提高性能，一次查20个，比一次查20000个性能肯定更好；另外如果数据量很大，一次性将内容都查询出来，查询出来的结果是放在内存里面的，内存没有这么大

不需要这么多数据，如新闻，一般人可能只看最近前20条；如果我们将后面的也都查询出来了，就是浪费

展现层面的考虑：如果一次展现太多的数据，不管是排版，还是美观上都不好

在oracle不能使用limit关键字对结果集分集分割，但是oracle提供了rownum伪列操作，对每个表中的数据创建了一个伪列用于标识行在当前表中的数据位置（行号）

rownum：是一个伪列，它会根据返回的记录生成一个序列化的数字。可以使用ROWNUM列返回查询结果集中前N条记录

在进行分页查询时，可以使用关联查询或子查询的形式获取伪列与实际数据的关系之后通过between进行分页操作

由于rownum是伪列所以不能直接通过添加进行使用，所以需要先将数据与伪列查询出来当做临时表继续查询

select e2.* from (select rownum rn, e.* from 表名 e) e2 where e2.rn>0 and e2.rn<=5

注意：在oracle中表名起别名时不能使用as 但是在对列起别名时可以

分页的语法

select e2.* from (select rownum rn, e.* from 表名 e) e2 where e2.rn>(page-1)*pagenum and e2.rn<=pagenum*page