基本查询、过滤、排序,多表查询、子查询以及连接Oracle数据库碰到的一些问题的总结
Oracle简介
Oracle数据库是由Oracle(甲骨文)公司推出的一款关系型数据库,Oracle公司在关系型数据库领域的地位是不可撼动的,就像Java在后台开发中具有无可比拟的优势一样,现在最为流行的开源数据库MySQL也被Oracle公司收购,成为了Oracle公司旗下的产品。
Oracle的基本操作
SQLPlus
SQLPlus
是Oracle提供的连接数据库的工具,也可以直接使用windows提供的命令行连接数据库,在命令行下使用SQLPlus
连接数据库:sqlplus 用户名/密码
格式化命令
在windows命令行下可能会出现数据表显示格式乱的情况,解决的办法是将命令行窗体调大些,如果数据表包含的数据条数比较多,可能会出现表字段重复的问题,此时可以对显示进行格式化,方便阅读,语法如下:
1 | 查看行宽:SHOW LINESIZE; |
切换用户
1 | 命令:CONN 用户名/密码 [AS SYSDBA] |
HOST指令
在SQLplus中除了可以使用Oracle定义的命令另外,也可以使用HOST指令调用本机的操作系统命令, 例如
1 | echo命令: HOST echo helloworld |
SQL查询
SQL指的是结构化的查询语言,SQL常用的语法:SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、INSERT、UPDATE、DELETE、CREATE、DORP、ALTER、GRANT、REVOKE
。
SQL分为三种类型:
1 | DML(数据操作语言,开发中使用的部分):主要指数据库的查询与更新操作 |
基本查询
1 | 1、查询一个用户下的所有表 |
SQL限定查询
1 | SELECT [DISTING] * 列名称,别名 列名称,别名 列名称,别名 |
模糊查询
模糊查询事实上也是限定查询的一种,只是限定条件的不同,模糊查询的限定条件为LIKE
或者NOT LIKE
1 | 1、 查询ename中以B开头的所有信息 |
Tips
1、WHERE语句一般写在在FROM语句的后面,并且紧随其后执行、SELECT语句控制列的显示数据、WHERE控制着行的显示数据,SELECT语句落后于WHERE执行,SELECT语句可以定义别名,WHERE语句不能定义别名。
2、在模糊查询中,如果不设置关键字(%%)、表示全部查询。
3、使用NOT IN进行查询的时候,查询范围中不能出现NULL,否则不会有任何查询结果返回。
查询排序
排序是根据需求对查询结果进行处理,方便开发人员更好地分析和处理数据。
1 | [2、控制要显示的数据列]SELECT [DISTINCT] * 列名称,别名 列名称,别名 列名称,别名 |
1 | 1、查询部门编号为30的雇员姓名、职位、年薪,并按月薪进行升序 |
单行函数
单行函数格式:返回值 函数名称(参数)
字符串函数
单行函数格式:返回值 函数名称(参数)
函数名称 | 描述 |
---|---|
字符串 UPPER(列 or 字符串) | 将传入的字符串或列变为大写 |
字符串 LOWER(列 or 字符串) | 将传入的字符串或列变为小写 |
字符串 INITCAP(列 or 字符串) | 开头首字母大写,其余字母小写 |
数字 LENGHT(列 or 字符串) | 取得指定字符串的长度 |
字符串 SUBSTR (列 or 字符串,开始索引,长度) | 进行字符串的截取,如果没有设置长度,则表示从开始索引一直截取到结尾 |
字符串 REPLACE(列 or 字符串,旧内容,新内容) | 将指定的新数据替换掉旧数据 |
1 | 1、 查询姓名长度为5的所有员工信息 |
数值函数
数值函数主要是进行数值的处理,核心的函数有三个
函数名称 | 描述 |
---|---|
数字 ROUND(列 or 数字 [,小数位]) | 实现数据的四舍五入,可以设置保留的小数位 |
数字 TRUNC(列 or 数字 [,小数位]) | 实现数据的截取,即不进位 |
数字 MOD(列 or 数字 ,列 or 数字…) | 求模 |
日期函数
要处理日期,有一个前提就是必须要知道当前的日期,Oracle提供了一个专门的伪劣“SYSDTAE”,不包含时分秒,(SYSTIMESTAMP,时间戳,包含时分秒)伪劣:可以作为列来使用,但又不是真正的列。
日期操作公式: 日期 + 数字 = 日期(表示若干天之后的日期) 日期 - 数字 = 日期(表示若干天之前的日期) 日期 - 日期 = 数字(天数)
函数名称 | 描述 |
---|---|
ADD_MONTHS(列 or 日期,月数) | 表示在指定的日期增加若干个月之后日期 |
数字 MONTHS_BETWEEN(列 or 日期,列 or 日期) | 返回两个日期之间所经过的月数 |
日期 LAST_DAY(列 or 日期) | 取得指定日期所在月的最后一天 |
日期 NEXT_DAY(列 or 日期,星期X) | 返回下一个一周时间数对应的日期 |
1 | 计算年份,有两种方法: |
举个栗子:以年、月、日的方式计算出每个雇员的到现在为止的雇佣年限。
1 | 计算年份:ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)YEAR |
年份和月份的计算相对来说比较简单,但天数的计算比较麻烦,在Oracle之中计算天数的方法只有一种,就是日期1 - 日期2,日期1很容就知道是SYSDATE,但是日期2是多少?由于时间跨度比较大,并且每个月的日期不是固定的,所以要想精确计算天数,必须保证(日期1 - 日期2) <= 30 ,所以计算天数的完整语句因该是:
1 | ROUND(SYSDATE - ADD_MONTHS(HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE)))DAY, |
注:进行四舍五入是因为有时分秒
转换函数
转换函数主要是进行三类数据的相互转换操作,数字型、字符串型、日期型,转换函数如下:
函数名称 | 描述 |
---|---|
字符串 TO_CHAR(列 or 数字,转换格式) | 将日期或者数字转换为指定结构的字符串 |
日期 TO_DATE(列 or 字符串,转换格式) | 将日期型的字符串转换为日期型数据 |
数字 TO_NUMBER(列 or 字符串) | 将字符串变为数字 |
1 | 日期:年(yyyy)、月(mm)、日(dd) |
1 | SELECT TO_CHAR(36374578888,'L999,999,999,999') FROM DUAL; |
多表查询
多表查询的形式
1 | [2、控制要显示的数据列]SELECT [DISTINCT] * 列名称,别名 列名称,别名 列名称,别名... |
表连接
内连接
内连接也叫等值连接,所有满足条件的数据都会被显示出来,如果条件不满足,则无法显示。例如:
1 | SELECT e.ename,e.job,d.deptno,d.loc FROM EMP e,DEPT d WHERE e.deptno = d.deptno; |
外连接
外连接相比等值连接,可以查询左表或者右表中所有的数据,无论条件是否满足。外连接分为左外连接和右外连接。左外连接:返回左表中所有的数据,即使在右表中没有匹配的行。右外连接:返回右表中所有的数据,即使左表中没有匹配的行。
1 | 左外连接: 字段 = 字段(+) |
1 | // 左外连接 |
例如:
1 | SELECT e.ename,e.job,m.ename mgr FROM emp e,emp m WHERE e.mgr = m.empno(+); |
笛卡尔积
笛卡尔积是数学上的一个公式,即两个集合的乘积,数据库的多表操作也是基于这个公式,但是在实际情况下,不同的数据表有可能存在相同的字段,这就导致了在最原始的多表查询的操作中出现了错误数据的情况,例如:EMP,DEPT两张表,两张表存在关联字段deptno
,当我们执行查询操作:select * from emp,dept;
的时候,会发现得到的结果是两张表记录的乘积:
查询得到56条数据,其中就出现了一些错误的数据
所以为了避免出现这种问题,应该加上一个where
条件,例如select * from A a,B b where a.deptno = b.deptno;
,加上限制条件之后,就能过滤掉一些不应该出现的错误的数据:
tips
上面的查询语句并不能消除笛卡尔积,只是在显示上消除了笛卡尔积的问题,多表查询的性能是非常差的,在开发中尽可能避免使用多表查询。
子查询
所谓子查询就是在一个查询语句中嵌套其他的查询语句。在SQL99(SQL1999)
标准中,子查询可以在任意位置插入,例如:SELECT子句、WHERE子句、FROM子句…
举个栗子:查询每个部门的编号、名称以及部门的人数
问题拆分:
涉及到的数据表:部门表(部门编号、名称)、员工表(部门人数)
限定的查询条件:EMP.deptno = DEPT.deptno
1 | SELECT d.deptno,d.dname,count(e.empno) count |
查询结果:
1 | SELECT d.deptno,d.dname,temp.count |
查询结果:
可以发现两种查询得到的结果是一样的,但是实际上子查询的性能要比多表查询的性能要好,我们可以假设部门表中有100条记录,员工表中有10000条记录,那么多表查询操作的记录数就是100*10000
条,而在子查询中temp表的记录数实际上最多是100条,因为子查询中按照部门编号进行了分组,那么也就意味着子查询操作的记录数实际上是100*100
条,显然子查询的性能要比多表查询的性能要好。
子查询的结果如果存在null值,那么在使用NOT IN操作的时候不会有任何结果返回。
原生连接Oracle数据库
操作步骤
在操作之前我已经在自己的电脑上安装了Oracle数据库,这里以SOCTT
用户为例,使用java.sql
包下的DriverManager
类来获取数据库连接,关于JDBC连接数据库的操作几乎都是固定的,步骤如下:
1 | 1、加载数据库驱动程序(向容器加载); |
连接形式
1 | 数据库的连接地址:jdbc:oracle:连接方式:主机名称:端口名称:数据库的SID |
1 | package com.my.jdbc; |
关于连不上Oracle数据库的一些原因及解决办法
如果发现连不上数据库,先检查数据库相关的服务是否启动,再排查其他的问题:
1、监听服务出错- 检查监听的主机名称是不是本地的计算机名称
- 监听配置文件路径:
~/product/11.2.0/dbhome_1/NETWORK/ADMIN/
- listener.ora
- tnsnames.ora
- 数据库的名字就是SID的名称,但是很多时候会发现该名字不会被自动注册,就是说只有数据库名称没有对应的SID名称,此时可以打开数据库的网络- 管理工具(Net Manager),重新添加数据库并保存网络配置,那么此时就会自动在listener.ora文件中注册一个SID