Oracle基础篇

发表于 | 更新于: | 分类于 | | 字数: 4.2k

基本查询、过滤、排序，多表查询、子查询以及连接Oracle数据库碰到的一些问题的总结


Oracle简介

Oracle数据库是由Oracle（甲骨文）公司推出的一款关系型数据库，Oracle公司在关系型数据库领域的地位是不可撼动的，就像Java在后台开发中具有无可比拟的优势一样，现在最为流行的开源数据库MySQL也被Oracle公司收购，成为了Oracle公司旗下的产品。

Oracle的基本操作

SQLPlus

SQLPlus是Oracle提供的连接数据库的工具，也可以直接使用windows提供的命令行连接数据库，在命令行下使用SQLPlus连接数据库：sqlplus 用户名/密码

格式化命令

在windows命令行下可能会出现数据表显示格式乱的情况，解决的办法是将命令行窗体调大些，如果数据表包含的数据条数比较多，可能会出现表字段重复的问题，此时可以对显示进行格式化，方便阅读，语法如下：

1
2
3
4
5
6
查看行宽：SHOW LINESIZE;
设置行宽：SET LINESIZE 300;
设置列宽：COLUM 列名 FOR 列宽; || 简写方式：COL 列名 FOR 列宽;
    |- 字符列：COLUM TNAME FORMAT a8;其中a8表示列宽为8个字符
    |- 数字列：COLUM TNAME FORMAT 9999;其中9999表示列宽为4为数字
设置每页长度：SET PAGESIZE 30;

切换用户

1
2
3
命令：CONN 用户名/密码 [AS SYSDBA]
	注意：如果当前的用户为sys,那么必须写上“AS SYSDBA”[]不用写，否则无法登录
命令：show user，显示用户名

HOST指令

在SQLplus中除了可以使用Oracle定义的命令另外，也可以使用HOST指令调用本机的操作系统命令， 例如

1
2
3
echo命令： HOST echo helloworld
清屏命令：HOST CLS
文件拷贝命令：copy 源文件路径 目标文件路径，HOST copy 源文件路径 目标文件路径

SQL查询

SQL指的是结构化的查询语言，SQL常用的语法：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、INSERT、UPDATE、DELETE、CREATE、DORP、ALTER、GRANT、REVOKE。
SQL分为三种类型：

1
2
3
DML（数据操作语言，开发中使用的部分）：主要指数据库的查询与更新操作
DDL（数据定义语言，开发前设计）：主要指的是数据对象的创建（表、用户）
DCL（数据控制语言，系统管理人员）：主要进行权限的管理操作（需要结合用户来观察），此部分由DBA负责

基本查询

1
2
3
4
1、查询一个用户下的所有表
    |- SELECT * FROM tab;
2、查询一个表的结构
    |- DESC 表名称;

SQL限定查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SELECT [DISTING] * 列名称,别名 列名称,别名 列名称,别名 
FORM 表名称 别名
WHERE 过滤条件

其中过滤条件：
	关系运算符：>、<、>=、<=、<>(!=)
	逻辑运算符：AND、OR、NOT
	范围运算符：BETWEEN...AND
	谓词范围：IN、NOTIN
	空判断：IS NULL、IS NOT NULL
	模糊查询：LIKE、NOT LIKE

以SCOTT用户下的EMP表为例

1、 查询所有工资大于5000的信息
    |=> SELECT * FROM EMP WHEER SAL>5000;

2、 查询部门编号为30的雇员姓名、职位以及工资
    |=> SELECT ENAME,JOB,SAL FROM EMP WHERE DEPTNO=30;

3、 查询工资不等于5000的员工姓名信息
    |=> SELECT ENAME,姓名 FORM EMP WHERE SAL<>5000;

4、 查询工资在2000到5000之间的员工姓名信息
    |=> SELECT ENAEM FROM EMP SAL>2000 AND SAL<5000;

5、 查询工资在2000到5000之间的员工姓名信息，
    |=> SELECT ENAEM FROM EMP BETWEEN 2000 AND 5000; 
    |=> 相比上面的那一句，这一句在性能上有所提升，因为只匹配一个条件

6、 日期的写法
    |=> SELECT * FROM EMP WHERE HIREDATE BETWEEN '01-1月 -81' AND '31-12月 -81';

7、 查询佣金为空的雇员姓名
    |=> SELECT ENAME FROM EMP WHERE COMM IS NULL;

8、查询指定雇员编号范围内的所有雇员信息
    |=> SELECT * FROM EMP WHERE EMPNO IN (7654,7900,7902,4563);
    |=> IN 类似于BETWEEN...AND,但是范围较小，有限元素个数

模糊查询

模糊查询事实上也是限定查询的一种，只是限定条件的不同，模糊查询的限定条件为LIKE或者NOT LIKE

Example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1、 查询ename中以B开头的所有信息
    |=> SELECT * FROM EMP WHERE ENAME LIKE 'B%';

2、 查询ename中包含有B的所有信息
    |=> SELECT * FROM EMP WHERE ENAME LIKE '%B%';

3、 查询ename中第二个字母为B的所有信息
    |=> SELECT * FROM EMP WHERE ENAME LIKE '_B%';

4、 查询ename中为任意字母的所有信息，相当于全部查询，但是不推荐这样进行查询，性能低
    |=> SELECT * FROM EMP WHERE ENAME LIKE '%%';

5、 查询姓名中没有‘R’的员工(模糊查询)
    |=> SELECT ENAME FROM EMP WHERE ENAME NOT LIKE '%R%';

Tips
1、WHERE语句一般写在在FROM语句的后面，并且紧随其后执行、SELECT语句控制列的显示数据、WHERE控制着行的显示数据，SELECT语句落后于WHERE执行，SELECT语句可以定义别名，WHERE语句不能定义别名。
2、在模糊查询中，如果不设置关键字（%%）、表示全部查询。
3、使用NOT IN进行查询的时候，查询范围中不能出现NULL，否则不会有任何查询结果返回。

查询排序

排序是根据需求对查询结果进行处理，方便开发人员更好地分析和处理数据。

基本语法
1
2
3
4
5
6
[2、控制要显示的数据列]SELECT [DISTINCT] * 列名称,别名 列名称,别名 列名称,别名 
[1、确定数据来源]FORM 表名称 别名
[3、确定数据满足的条件]WHERE 过滤条件
[4、针对查询结果进行排序]ORDER BY 字段 [ASC | DESC]，字段[ASC | DESC]...

// ASC为升序(从小到大)、DESC为降序（从大到小）、DISTINCT为去除重复，默认为升序
1
2
3
4
5
6
7
8
9
10
11
1、查询部门编号为30的雇员姓名、职位、年薪，并按月薪进行升序
    |=> SELECT ENAME,JOB,SAL*12 INCOME FROM EMP WHERE DEPTNO=30 ORDER BY SAL;

2、查询部门编号为30的雇员姓名、职位、年薪，并按年薪进行升序（只有ORDER BY子句可以使用SELECT语句的别名）
    |=> SELECT ENAME,JOB,SAL*12 INCOME FROM EMP WHERE DEPTNO=30 ORDER BY INCOME;

3、查询emp表所有信息，按月薪升序
    |=> SELECT * FROM EMP ORDER BY SAL ASC;

4、查询部门编号为30的雇员编号、雇员姓名、职位、年薪，按月薪升序、雇员编号降序
    |=> SELECT EMPNO,ENAME,JOB,SAL*12 INCOME FROM EMP WHERE DEPTNO=30 ORDER BY SAL ASC,EMPNO DESC;

单行函数

单行函数格式：返回值 函数名称（参数）

字符串函数

单行函数格式：返回值 函数名称（参数）

函数名称 描述
字符串 UPPER(列 or 字符串) 将传入的字符串或列变为大写
字符串 LOWER(列 or 字符串) 将传入的字符串或列变为小写
字符串 INITCAP(列 or 字符串) 开头首字母大写，其余字母小写
数字 LENGHT(列 or 字符串) 取得指定字符串的长度
字符串 SUBSTR (列 or 字符串，开始索引，长度) 进行字符串的截取，如果没有设置长度，则表示从开始索引一直截取到结尾
字符串 REPLACE(列 or 字符串，旧内容，新内容) 将指定的新数据替换掉旧数据
Example:
1
2
3
4
1、 查询姓名长度为5的所有员工信息
    |=> SELECT * FROM EMP WHERE LENGTH(ENAME)=5;
2、 查询员工姓名以及取得员工姓名后三位的信息
    |=> SELECT ENAME,SUBSTR(ENAME,LENGTH(ENAME)-2) FROM EMP;

数值函数

数值函数主要是进行数值的处理，核心的函数有三个

函数名称 描述
数字 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
2
3
计算年份，有两种方法：
	方法一：（日期 - 日期）/ 12 = 年  这种方法无法处理闰年的问题
	方法二：MONTHS_BETWEEN(SYSDATE,HIREDATE) / 12 = 年

举个栗子：以年、月、日的方式计算出每个雇员的到现在为止的雇佣年限。

1
2
计算年份：ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)YEAR
计算月份：ROUND(MOD(MONTHS_BETWEEN(SYSDATE,HIREDATE),12))MONTH

年份和月份的计算相对来说比较简单，但天数的计算比较麻烦，在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 字符串) 将字符串变为数字
TO_CHAR()函数的转换格式：
1
2
3
日期：年（yyyy）、月（mm）、日（dd）
时间：时（hh、hh24）、分（mi）、秒（ss）;
数字：任意数字（9）、本地货币符号（L）
举个栗子：格式化数字
1
SELECT TO_CHAR(36374578888,'L999,999,999,999') FROM DUAL;

多表查询

多表查询的形式

1
2
3
4
[2、控制要显示的数据列]SELECT [DISTINCT] * 列名称,别名 列名称,别名 列名称,别名... 
[1、确定数据来源]FORM 表名称 别名,表 别名...
[3、确定数据满足的条件]WHERE 过滤条件
[4、针对查询结果进行排序]ORDER BY 字段 [ASC | DESC]，字段[ASC | DESC]...

表连接

内连接

内连接也叫等值连接，所有满足条件的数据都会被显示出来，如果条件不满足，则无法显示。例如：

1
SELECT e.ename,e.job,d.deptno,d.loc FROM EMP e,DEPT d WHERE e.deptno = d.deptno;

外连接

外连接相比等值连接，可以查询左表或者右表中所有的数据，无论条件是否满足。外连接分为左外连接和右外连接。左外连接：返回左表中所有的数据，即使在右表中没有匹配的行。右外连接：返回右表中所有的数据，即使左表中没有匹配的行。

Oracle语法
1
2
左外连接： 字段 = 字段（+）
右外连接： 字段（+）= 字段
SQL语法（通用）
1
2
3
4
5
6
7
8
9
10
// 左外连接
SELECT column_name...
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column_name=table2.column_name
// 右外连接
SELECT column_name...
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column_name=table2.column_name

例如：

1
2
SELECT e.ename,e.job,m.ename mgr FROM emp e,emp m WHERE e.mgr = m.empno(+);
SELECT e.ename,e.job,m.ename mgr FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;

笛卡尔积

笛卡尔积是数学上的一个公式，即两个集合的乘积，数据库的多表操作也是基于这个公式，但是在实际情况下，不同的数据表有可能存在相同的字段，这就导致了在最原始的多表查询的操作中出现了错误数据的情况，例如：EMP,DEPT两张表，两张表存在关联字段deptno，当我们执行查询操作：select * from emp,dept;的时候，会发现得到的结果是两张表记录的乘积：

count

查询得到56条数据，其中就出现了一些错误的数据

笛卡尔积

所以为了避免出现这种问题，应该加上一个where条件，例如select * from A a,B b where a.deptno = b.deptno;，加上限制条件之后，就能过滤掉一些不应该出现的错误的数据：

多表查询

tips
上面的查询语句并不能消除笛卡尔积，只是在显示上消除了笛卡尔积的问题，多表查询的性能是非常差的，在开发中尽可能避免使用多表查询。

子查询

所谓子查询就是在一个查询语句中嵌套其他的查询语句。在SQL99（SQL1999）标准中，子查询可以在任意位置插入，例如：SELECT子句、WHERE子句、FROM子句…

举个栗子：查询每个部门的编号、名称以及部门的人数

问题拆分：
涉及到的数据表：部门表（部门编号、名称）、员工表（部门人数）
限定的查询条件：EMP.deptno = DEPT.deptno

查询方式一：多表查询
1
2
3
4
SELECT d.deptno,d.dname,count(e.empno) count
FROM emp e RIGHT JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.deptno,d.dname;

查询结果：

子查询

查询方式二：子查询
1
2
3
SELECT d.deptno,d.dname,temp.count
FROM dept d LEFT JOIN (SELECT deptno,COUNT(empno) count FROM emp e GROUP BY deptno) temp
ON d.deptno = temp.deptno;

查询结果：

子查询

可以发现两种查询得到的结果是一样的，但是实际上子查询的性能要比多表查询的性能要好，我们可以假设部门表中有100条记录，员工表中有10000条记录，那么多表查询操作的记录数就是100*10000条，而在子查询中temp表的记录数实际上最多是100条，因为子查询中按照部门编号进行了分组，那么也就意味着子查询操作的记录数实际上是100*100条，显然子查询的性能要比多表查询的性能要好。

tips
子查询的结果如果存在null值，那么在使用NOT IN操作的时候不会有任何结果返回。

原生连接Oracle数据库

操作步骤

在操作之前我已经在自己的电脑上安装了Oracle数据库，这里以SOCTT用户为例，使用java.sql包下的DriverManager类来获取数据库连接，关于JDBC连接数据库的操作几乎都是固定的，步骤如下：

1
2
3
4
1、加载数据库驱动程序（向容器加载）；
2、进行数据库连接（通过DriverManager类完成）
3、进行数据的CRUD（增删改查）[Statement、ResultSet、PrepareStatement]
4、关闭连接

连接形式

1
2
数据库的连接地址：jdbc:oracle:连接方式:主机名称:端口名称:数据库的SID
例如：jdbc:oracle:thin:@localhost:1521:mack
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.my.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 原生连接Oracle数据库
 * @author  Mackvord
 * @date    2018年8月21日
 * @version 1.0
 */
public class OracleConnection {

    /**
     * 声明数据库驱动
     */
    private static final String DRIVERCLASS = "oracle.jdbc.driver.OracleDriver";
    /**
     * 声明数据库连接地址
     */
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:mack";
    /**
     * 声明连接用户
     */
    private static final String USERNAME = "scott";
    /**
     * 声明连接密码
     */
    private static final String PASSWORD = "tiger";
    
    /**
     * 获取数据库连接的方法 
     * @throws ClassNotFoundException 
     * @throws SQLException 
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        // 加载数据库驱动
        Class.forName(DRIVERCLASS);
        // 获取数据库连接并返回
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
    
    public static void main(String[] args) {
        try {
            Connection connection = OracleConnection.getConnection();
            Statement statement = connection.createStatement();
            String sql = "SELECT JOB FROM EMP WHERE ENAME = 'KING'";
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                System.out.println(rs.getString(1));
            }
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    
}

关于连不上Oracle数据库的一些原因及解决办法

如果发现连不上数据库，先检查数据库相关的服务是否启动，再排查其他的问题：

1、监听服务出错
  • 检查监听的主机名称是不是本地的计算机名称
  • 监听配置文件路径：~/product/11.2.0/dbhome_1/NETWORK/ADMIN/
    • listener.ora
    • tnsnames.ora
2、找不到指定的SID
  • 数据库的名字就是SID的名称，但是很多时候会发现该名字不会被自动注册，就是说只有数据库名称没有对应的SID名称，此时可以打开数据库的网络- 管理工具（Net Manager），重新添加数据库并保存网络配置，那么此时就会自动在listener.ora文件中注册一个SID
如果您觉得我的文章对您有帮助，请随意赞赏，您的支持将鼓励我继续创作！
0%