Oracle数据库

Oracle数据库

Oracle Database，又名Oracle RDBMS，简称Oracle。直至2022年仍是当今使用最广泛最流行的关系型数据库，由甲骨文公司开发、运营与维护。在数据库领域中一直处于领先地位，系统可移植性好、使用方便、功能与性能强大、效率高，适用于大、中、小软件开发。放眼当下因高效率（适应高吞吐量）、可靠性好仍是开发商业软件最好的选择（就是有点贵）。

2022年数据库排行榜一览

1、Oracle安装

1.1、Oracle环境搭建

这里我们主要学习Oracle数据的相关知识为主，在本地安装Oracle数据库安装过程时间非常长而且文件资源巨大（卸载更麻烦）！为了方便我们直接使用Docker搭建oracle数据库环境即可。

# 1.oracle镜像拉取
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

# 2.运行容器
docker run -d -p 1521:1521 --name oracle registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g

# 3.查看是否启动成功
docker ps
1
2
3
4
5
6
7
8

容器启动成功后，我们只完成了最简单的一步，oracle与其他数据库不同，还需要额外添加一些配置才能使用客户端工具进行连接与访问。

# 1.进入容器
docker exec -it [containerId] /bin/bash
# 2.切换root用户(root用户密码是 helowin )
su root

# 3.配置oracle环境变量，注意容器没有安装vim/vi，也没有安装 apt-get 工具，因此这里我们选择以拷贝的方式修改profile文件
# 3.1/退出容器
exit
# 3.2/将容器中的profile文件拷贝出来
docker cp [containerId]:/etc/profile /home/oracle/profile

# 3.3/编辑profile文件，添加如下环境变量配置
vim /home/oracle/profile

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH

# 3.4/将profile文件拷贝会容器并重启
docker cp /home/oracle/profile [containerId]:/etc/profile
docker restart [containerId]

# 4.创建软连接(将某个文件在另一个位置建立一个同步的连接)，注意进入容器后在root账号下执行
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin

# 5.切换回oracle用户(必须要 - 连接，否则软连接无效)例如
[root@9fa414625962 /]# su - oracle
[oracle@9fa414625962 ~]$ 

# 6.登录sqlplus并修改sys和system用户密码
[oracle@9fa414625962 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 12 09:23:40 2022

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> # 这里面书写修改密码SQL语句
SQL> conn /as sysdba
Connected.
SQL> ALTER USER SYSTEM IDENTIFIED BY SYSTEM; # 修改system用户账号密码为 SYSTEM 客户端可用 SYSTEM/SYSTEM 进行登录

User altered.

SQL> ALTER USER SYS IDENTIFIED BY SYSTEM; # 修改sys用户账号密码为 SYSTEM

User altered.

SQL> CREATE USER LN IDENTIFIED BY LN; # 创建内部管理员账户密码

User created.

SQL> GRANT CONNECT,RESOURCE,DBA TO LN; # 将dba权限等授权给内部管理员账号和密码

Grant succeeded.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; # 修改密码规则策略为密码永不过期

Profile altered.

SQL> ALTER SYSTEM SET PROCESSES=1000 SCOPE=SPFILE; # 修改数据库最大连接

System altered.

# 7.修改以上信息后，需要重新启动数据库(依次执行一下命令即可)
conn /as sysdba
shutdown immediate;
startup;

# 8.记住这个命令 lsnrctl status 我们可以随时查看 oracle 的 Service instance
[oracle@9fa414625962 ~]$ lsnrctl status
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
62
63
64
65
66
67
68
69
70

至此oracle数据库环境已经全部搭建完毕，我们就可以通过客户端工具进行访问与连接了，例如

另外需要注意的是客户端连接工具(Navicat Premium / PLSQL Developer等)访问Oracle的时候需要配置Oracle的客户端实例也称为OCI环境。

1.2、Oracle数据文件存放路径

我们是使用Docker搭建的Oracle数据库环境，数据都是保存在容器中。假如我们不小心删除了容器，那么以前保存的数据库数据也会删除！因此启动Oracle容器时也应该考虑数据持久化问题（使用数据卷技术）。

要想进行目录挂载实现数据双向同步，首先要知道在Linux中Oracle的数据库文件存放在哪里？这里可以使用SQL进行查询例如：

SELECT F.* FROM V$DBFILE F ORDER BY F.FILE# ASC;
1

因此启动容器时我们就可以使用 -v 命令进行目录挂载：

docker run -d -p 1521:1521 -v /home/oracle/data:/home/oracle/app/oracle/oradata \
--name oracle registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
1
2

2、重新认识Oracle

2.1、简介与相关概念

ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户 / 服务器（client / server 或 B / S）体系结构的数据库之一。迄今为止ORACLE数据库是世界上使用最广泛的数据库管理系统。

ORACLE数据库具有以下特点：

1. 支持多用户、大事务量的事务处理
2. 数据安全性和完整性控制（银行金融等行业比较看重这一点）
3. 支持分布式数据处理
4. 可移植性

ORACLE数据库相关概念：

1、数据库

oracle数据库是数据的物理存储包括数据文件ORA或者DBF、控制文件、联机日志、参数文件等。其实ORACLE数据库的概念与其他数据库不一样，这里的数据库是一个操作系统只有一个库，可以看作是ORACLE就只有一个大数据库。

---

2、实例

一个ORACLE实例（Oracle Instance）有一系列的后台进程（Background Processes）和内存结构（Memory Structures）组成，一个数据库可以有 n 个实例。例如前面我们搭建环境所连接的helowin就是一个实例。

---

3、数据文件（dbf）

数据文件时数据库的物理存储单位，数据库的数据是存储在表空间的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或者多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

---

4、表空间（类似文件夹）

表空间是ORACLE对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射，一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间（称之为SYSTEM表空间）。例如查看当前用户使用的表空间情况：

SELECT U.USERNAME, U.DEFAULT_TABLESPACE FROM USER_USERS U;
1

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件（datafile）一个数据文件只能属于一个表空间。

需要注意的是ORACLE是由用户和表空间对数据进行管理和存放的，但是表不是由表空间去查询的，而是由用户去查的，因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了。

---

5、用户

用户是在表空间下建立的，用户登录后只能看到和操作自己的表，ORACLE的用户与MYSQL的数据库类似，每建立一个应用需要创建一个用户。

可以这么说在MYSQL中一个用户有多个数据库，而在ORACLE中一个数据库有多个用户，恰好相反。

---

体系结构划分图

2.2、中文编码设置

关于ORACLE的相关概念就先说这边多，后面还会详细展开与接触。

学习环境的搭建我们还有一步未完成！向表中插入一些测试数据难免会使用中文，为避免出现乱码，还需要配置NLS_LANG环境变量。

-- 检查 NLS_LANG 环境变量
SELECT USERENV('LANGUAGE') FROM DUAL;

--查询结果为 SIMPLIFIED CHINESE_CHINA.AL32UTF8
--配置上 NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8 即可如下图
1
2
3
4
5

2.3、查看、创建表空间

认识ORACLE首先从认识表空间开始：

-- 1.查看当前用户使用的表空间情况
SELECT U.* FROM USER_USERS U;

-- 2.查看所有表空间信息(名称、物理文件、大小等)
SELECT
	TABLESPACE_NAME,
	FILE_ID,
	FILE_NAME,
	ROUND(BYTES / ( 1024 * 1024 ), 0) TOTAL_SPACE 
FROM
	DBA_DATA_FILES 
ORDER BY FILE_ID ASC;

-- 2.查看表空间使用情况
SELECT
	SUM(BYTES) / (1024 * 1024) AS FREE_SPACE,
	TABLESPACE_NAME 
FROM
	DBA_FREE_SPACE 
GROUP BY
	TABLESPACE_NAME;

-- 3.查看表空间使用情况(详细信息)
SELECT
	A.TABLESPACE_NAME "表空间名",
	TOTAL "表空间大小",
	FREE "表空间剩余大小",
	(TOTAL - FREE) "表空间使用大小",
	TOTAL / (1024 * 1024 * 1024) "表空间大小(G)",
	FREE / (1024 * 1024 * 1024) "表空间剩余大小(G)",
	(TOTAL - FREE) / (1024 * 1024 * 1024) "表空间使用大小(G)",
	ROUND( (TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %" 
FROM
	(SELECT TABLESPACE_NAME, SUM(BYTES) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,
	(SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B 
WHERE 1 = 1
	AND A.TABLESPACE_NAME = B.TABLESPACE_NAME
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

例如查看所有表空间信息（可自行测试下查询SQL）

那么如何创建自己的表空间呢？

-- 创建表空间 LN_TEST
CREATE TABLESPACE LN_TEST
DATAFILE '/home/oracle/app/oracle/oradata/helowin/ln_test.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
1
2
3
4
5
6

再次查询所有表空间信息

表空间创建好后，如何给用户使用呢？也可以使用SQL语句进行指定

-- 1.给用户重新指定表空间(因为LN这个用户我们之前就创建好了)
ALTER USER LN DEFAULT TABLESPACE LN_TEST;

-- 2.再次检查当前用户使用的表空间
SELECT U.* FROM USER_USERS U;

-- 3.为了方便我们一般都是先创建表空间，然后再创建用户，创建用户时指定表空间。例如
CREATE USER [USER_NAME] IDENTIFIED BY [USER_PASSWORD] DEFAULT TABLESPACE [TABLESPACE_NAME];
-- 然后再给用户赋相应的权限即可 DBA 权限在 ORACLE 中权限是比较高的
GRANT DBA TO [USER_NAME];
1
2
3
4
5
6
7
8
9
10

2.4、表的创建、修改与删除

建表语句其实绝大多数数据都遵循统一的规范，所以语法都是一样的，例如

-- Create table
create table DL_PROJECT_RELATION
(
  discriminator   NVARCHAR2(31) not null,
  id              NUMBER(32) not null,
  fk_orgbusno     NVARCHAR2(255) not null,
  eaprojectid     NUMBER(32),
  parentprojectid NUMBER(32),
  fk_project_id   NUMBER(32),
  remark          NVARCHAR2(255),
  type            VARCHAR2(255),
  businessid      NUMBER(32)
)
tablespace GJ_INST
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column DL_PROJECT_RELATION.id
  is '主键';
comment on column DL_PROJECT_RELATION.fk_orgbusno
  is '电力业务流水号';
comment on column DL_PROJECT_RELATION.eaprojectid
  is '外网pid';
comment on column DL_PROJECT_RELATION.parentprojectid
  is '主流程pid';
comment on column DL_PROJECT_RELATION.fk_project_id
  is 'pid';
comment on column DL_PROJECT_RELATION.remark
  is '关联标记';
comment on column DL_PROJECT_RELATION.type
  is '类型';
comment on column DL_PROJECT_RELATION.businessid
  is '业务id';
-- Create/Recreate indexes 
create index DL_PROJECT_EA_PROJECT_ID on DL_PROJECT_RELATION (EAPROJECTID)
  tablespace GJ_INST
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index DL_PROJECT_FK_ORGBUSNO on DL_PROJECT_RELATION (FK_ORGBUSNO)
  tablespace GJ_INST
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index DL_PROJECT_FK_PROJECT_ID on DL_PROJECT_RELATION (FK_PROJECT_ID)
  tablespace GJ_INST
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index DL_PROJECT_PP_PROJECT_ID on DL_PROJECT_RELATION (PARENTPROJECTID)
  tablespace GJ_INST
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table DL_PROJECT_RELATION
  add primary key (ID)
  using index 
  tablespace GJ_INST
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105

追加字段

ALTER TABLE LN.LN_USER ADD (
	REMARK VARCHAR2(100)
)
1
2
3

修改字段类型

-- 仅限于没有数据的时候
ALTER TABLE LN.LN_USER MODIFY(REMARK NVARCHAR2(100) DEFAULT 'ALEX');
1
2

修改字段名

-- 语法如下
ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名;
-- 示例
ALTER TABLE LN.LN_USER RENAME COLUMN REMARK TO TYPE;
1
2
3
4

删除字段名

-- 语法如下
ALTER TABLE 表名称 DROP COLUMN 字段名1, 字段名2;
-- 示例
ALTER TABLE LN.LN_USER DROP COLUMN TYPE;
1
2
3
4

删除表

DROP TABLE [TABLE_NAME];
-- TRUNCATE DDL语句 会释放所有空间与数据包括索引等
TRUNCATE TABLE [TABLE_NAME];
1
2
3

以上语法大家了解即可包括插入更新等语句，工作中也很少用到，不会的时候查一下文档即可。重要的是要学会编写存储过程、索引、触发器、函数、游标、理解事务、分析执行计划进行索引优化等这才是工作中最常用的。

2.5、Oracle数据类型

ORACLE的数据类型也叫内置数据类型，可以按类型分为字符型、数字型、日期类型、LOB类型等实际开发中这四种类型是最常用的。

1、字符型

2、数字型

3、日期类型

4、LOB类型（LOB类型分为BLOB和CLOB两种）

ORACLE实际上数据类型不止这些，这里只是列举了常用的数据类型。

2.5、数据的增删改

由于和其他数据库大同小异，这里就简单介绍下

# 建表SQL
CREATE TABLE "LN"."LN_USER" (
  "ID" NUMBER(10,0) NOT NULL,
  "USERNAME" VARCHAR2(255 BYTE),
  "PASSWORD" VARCHAR2(255 BYTE),
  CONSTRAINT "SYS_C0011150" PRIMARY KEY ("ID"),
  CONSTRAINT "SYS_C0011149" CHECK ("ID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE
)
TABLESPACE "LN_TEST"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT

# 序列创建SQL(ORACLE里没有主键自增，主键大多使用序列)
CREATE SEQUENCE "LN"."S_LN_USER" MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 CACHE 20;

SELECT LN.S_LN_USER.NEXTVAL FROM DUAL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

插入语句

INSERT INTO LN.LN_USER(ID, USERNAME, PASSWORD) VALUES(LN.S_LN_USER.NEXTVAL, '马超', '123');
COMMIT;

SELECT U.* FROM LN.LN_USER U;
1
2
3
4

更新语句

UPDATE LN.LN_USER U SET U.USERNAME = '元歌' WHERE U.ID = 3; 
COMMIT;

SELECT U.* FROM LN.LN_USER U;
1
2
3
4

删除语句

DELETE FROM LN.LN_USER U WHERE U.ID = 3; 
COMMIT;

SELECT U.* FROM LN.LN_USER U;
# 还有一种删除方法区别于 DELETE 语句就是 TRUNCATE DDL 语句
# DELETE 删除的数据可以 ROLLBACK，删除可能产生碎片，并且不会释放空间
# TRUNCATE 直接摧毁表结构，释放所有资源，再重构表结构
1
2
3
4
5
6
7

3、JDBC/JPA连接Oracle

学会使用程序访问ORACLE数据库，也非常重要，最终我们都是以开发业务功能为主。

3.1、使用JDBC连接ORACLE

1、使用SpringBoot的初始化向导创建一个测试工程

2、引入相关依赖（JDBC/数据库驱动）

<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-jdbc -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--ojdbc6(Oracle11g)-->
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc6 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12

3、编写application.properties/application.yml文件

server.port=8080
spring.application.name=oracle-test
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@121.41.571.179:1521:helowin
spring.datasource.username=LN
spring.datasource.password=LN
spring.jdbc.template.query-timeout=3
1
2
3
4
5
6
7

4、编写测试方法

@SpringBootTest
class DemoApplicationTests {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void oracleTest() {
        String sql = "SELECT U.* FROM LN.LN_USER U";
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql);
        System.out.println(data);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12

5、测试

3.2、使用JPA连接ORACLE

使用SpringData JPA访问ORACLE数据库比较重要也是比较推荐的一种方式，这里我们要完成基本的增删改查功能。

1、使用SpringBoot的初始化向导创建一个测试工程

2、引入相关依赖（JPA/数据库驱动）

<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc6 -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11

3、编写application.properties/application.yml文件

server.port=8080
spring.application.name=oracle-test
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@121.41.571.179:1521:helowin
spring.datasource.username=LN
spring.datasource.password=LN
spring.jpa.show-sql=true
1
2
3
4
5
6
7

4、编写实体类（注意注解的使用方式）

/**
 * @description: UserEntity
 * @date: 2022/3/13 11:00
 */
@Entity
@Table(name = "LN_USER", schema = "LN") // schema属性在ORACLE数据库中填用户ID
@JsonIgnoreProperties(value = {"hibernateLazyInitializer", "handler"})
public class UserEntity implements Serializable {
    private static final long serialVersionUID = 7521045049709813121L;
    private Integer id;
    private String username;
    private String password;

    @Id
    @Column(name = "ID", unique = true, nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "LN_USER_ID")
    @SequenceGenerator(name = "LN_USER_ID", schema = "LN", sequenceName = "S_LN_USER", allocationSize = 1)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name = "USERNAME")
    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Column(name = "PASSWORD")
    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}
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

5、编写Mapper接口，需要继承JPA提供的JpaRepository<T, ID>接口

/**
 * @description: UserMapper
 * @date: 2022/3/13 11:38
 */
public interface UserMapper extends JpaRepository<UserEntity, Integer> {

}
1
2
3
4
5
6
7

6、编写增删改查业务代码

/**
 * @description: UserController
 * @date: 2022/3/13 11:00
 */
@RestController
@RequestMapping(value = "user")
public class UserController {
    @Autowired
    private UserMapper mapper;
    
    @RequestMapping(value = "/add", method = RequestMethod.POST)
    public R addUser(@RequestBody UserEntity userEntity) {
        UserEntity entity = mapper.save(userEntity);
        return R.ok().put("data", entity);
    }
    @RequestMapping(value = "/delete/{id}", method = RequestMethod.GET)
    public R deleteUser(@PathVariable(value = "id") Integer id) {
        mapper.deleteById(id);
        return R.ok();
    }
    @RequestMapping(value = "/update", method = RequestMethod.POST)
    public R updateUser(@RequestBody UserEntity userEntity) {
        UserEntity entity = mapper.save(userEntity);
        return R.ok().put("data", entity);
    }
    @RequestMapping(value = "/getList", method = RequestMethod.GET)
    public R getUserList() {
        List<UserEntity> userList = mapper.findAll();
        return R.ok().put("data", userList);
    }
    @RequestMapping(value = "/get/{id}")
    public R getUserById(@PathVariable(value = "id") Integer id) {
        UserEntity entity = mapper.getById(id);
        return R.ok().put("data", entity);
    }
}
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

7、使用Postman工具进行测试

例如添加

查询

4、Oracle数据导入与导出

当我们使用一个数据库时，总是希望数据库的内容是可靠的、正确的，但是由于计算机系统的故障（硬件故障、软件故障、网络故障、进程故障和系统故障）影响数据库系统的操作，影响数据库中数据的正确性，甚至破坏数据库，使数据库中全部或部分数据丢失。因此当发生上述故障后，希望能重构这个完整的数据库，这个过程称为数据库恢复，而要进行数据库的恢复必须要有数据库的备份工作。

推荐文章（总结的比较好）：https://blog.csdn.net/bugzeroman/article/details/102803866

# 导出
exp YTGZSBDB/YTGZSBDB#13@192.168.6.88:3526/gbk19c file=/u01/app/oracle/oradata/YTGZSBDB_0605.dmp statistics=none log=YTGZSBDB.log

# 导入
imp YZYTGZSB/YZYTGZSB@192.168.200.12:3526/yangzhouyth file=/home/oracle/anhui_ytgz_db/YTGZSBDB_2023_06_01.dmp fromuser=YTGZSBDB touser=YZYTGZSB
1
2
3
4
5

5、Oracle查询（重点）

需要了解的是数据库对语言的划分有四种分别是：

1. DQL(Data Query Language)查询语言（重点），包括单表查询、多表查询、子查询、分页查询等
2. DML(Data Manipulation Language)操作语言，表数据的增、删、改等
3. DDL(Data Definition Language)定义语言，库与表的管理建表、建库语句
4. DCL(Data Controller Language)控制语言，事务提交COMMIT、授予权限GRANT、撤销权限REVOKE、事务回滚ROLLBACK、设置保存点SAVEPOINT、行锁定LOCK等

因此学习查询语句之前先要准备测试数据，为了尽可能的简单我们使用三张表（LN_ORG / LN_USER / LN_ORG_USER）即可如下所示

-- CREATE TABLE SQL
CREATE TABLE "LN"."LN_ORG" (
  "ID" NUMBER(30,0) NOT NULL,
  "ORG_NAME" VARCHAR2(255 BYTE),
  "MANAGERID" NUMBER(30,0),
  "PARENTID" NUMBER(30,0),
  CONSTRAINT "LN_ORG_ID_PK" PRIMARY KEY ("ID")
)
TABLESPACE "LN_TEST"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT

-- INSERT SQL
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(1, 'Administration', 200, 1700);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(2, 'Marketing', 201, 1800);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(3, 'Purchasing', 114, 1700);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(4, 'Human Resources', 203, 2400);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(5, 'Shipping', 121, 1500);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(6, 'IT', 103, 1400);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(7, '前端开发', 103, 100);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(8, '后端端开发', 103, 100);
INSERT INTO LN.LN_ORG(ID, ORG_NAME, MANAGERID, PARENTID) VALUES(9, '运维', 103, 100);

-- CREATE TABLE SQL
CREATE TABLE "LN"."LN_USER" (
  "ID" NUMBER(10,0) NOT NULL,
  "USERNAME" VARCHAR2(255 BYTE),
  "PASSWORD" VARCHAR2(255 BYTE),
  "FIRST_NAME" VARCHAR2(255 BYTE),
  "LAST_NAME" VARCHAR2(255 BYTE),
  "PHONE" CHAR(11 BYTE),
  "SALARY" NUMBER(8,2),
  CONSTRAINT "LN_USER_ID_PK" PRIMARY KEY ("ID"),
  CONSTRAINT "LN_USER_ID_DEFERRABLE" CHECK ("ID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE
)
TABLESPACE "LN_TEST"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "LN"."LN_USER"."USERNAME" IS '登录名'

-- INSERT SQL
INSERT INTO LN.LN_USER(ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, PHONE, SALARY)
VALUES( LN.S_LN_USER.NEXTVAL, 'Steven', '123', 'Steven', 'King', '515.1234567', 1200);

INSERT INTO LN.LN_USER(ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, PHONE, SALARY)
VALUES(LN.S_LN_USER.NEXTVAL, 'Neena', '123', 'Neena', 'Kochhar', '515.1234568', 17000);

INSERT INTO LN.LN_USER(ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, PHONE, SALARY)
VALUES(LN.S_LN_USER.NEXTVAL, 'Lex', '123', 'Lex', 'De Haan', '515.1234569', 17000);

INSERT INTO LN.LN_USER(ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, PHONE, SALARY)
VALUES(LN.S_LN_USER.NEXTVAL, 'Alexander','123', 'Alexander', 'Hunold', '590.4234567', 9000);

INSERT INTO LN.LN_USER(ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, PHONE, SALARY)
VALUES(LN.S_LN_USER.NEXTVAL, 'Bruce', '123', 'Bruce', 'Ernst', '590.4234568', 6000);
--
-- CREATE TABLE SQL
CREATE TABLE "LN"."LN_ORG_USER" (
  "ID" NUMBER(30,0) NOT NULL,
  "FK_USER_ID" NUMBER(30,0),
  "FK_ORG_ID" NUMBER(30,0),
  "REMARK" VARCHAR2(255 BYTE),
  CONSTRAINT "LN_ORG_USER_ID_PK" PRIMARY KEY ("ID")
)
TABLESPACE "LN_TEST"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT

INSERT INTO LN.LN_ORG_USER(ID, FK_USER_ID, FK_ORG_ID) VALUES(1, 4, 7);
INSERT INTO LN.LN_ORG_USER(ID, FK_USER_ID, FK_ORG_ID) VALUES(2, 5, 7);
INSERT INTO LN.LN_ORG_USER(ID, FK_USER_ID, FK_ORG_ID) VALUES(3, 22, 7);
INSERT INTO LN.LN_ORG_USER(ID, FK_USER_ID, FK_ORG_ID) VALUES(4, 24, 8);
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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

5.1、基本查询

/*
一、语法
　　SELECT 查询列表 FROM 表名;

二、特点
　　1、查询列表可以是字段、常量、表达式、函数，也可以是多个
　　2、查询结果是一个虚拟表

三、示例

1、查询单个字段
　　SELECT 字段名 FROM 表名;

2、查询多个字段
　　SELECT 字段名, 字段名 FROM 表名;

3、查询所有字段
　　SELECT * FROM 表名;

4、查询常量
　　SELECT 常量值 FROM DUAL;
　　注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

5、查询函数
　　SELECT 函数名(实参列表) DUAL;

6、查询表达式
　　SELECT 100 / 1234 FROM DUAL;

7、起别名
　　①AS
　　②空格

8、去重
　　SELECT DISTINCT 字段名 FROM 表名;
*/
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

在ORACLE中需要注意的是：

1. SQL语句大小写不敏感
2. SQL可以写在一行或者多行
3. 关键字不能被缩写也不能分行
4. 各子句一般要分行写
5. 使用缩进提高语句的可读性
6. 数字和日期可以使用算数运算符，例如+ - * /，SELECT 8 / 4 FROM DUAL; / SELECT SYSDATE - 1 FROM DUAL;
7. 空值不同于0，凡是空值参与的运算，结果都为空（null）
8. 日期和字符串只能在单引号中出现

-- 1.起别名可以使用空格或者 AS 关键字为查询结果起别名
SELECT U.LAST_NAME NAME, U.SALARY * 12 AS 年薪 FROM LN.LN_USER U WHERE 1 = 1;
-- 2.查询结果字段名显示默认是大写，我们可以使用 "" 改为小写
SELECT SYSDATE AS "now" FROM DUAL;
-- 3.连接符 || 可以使列与字符连接/合成在一起。类似System.out.println("hello " + "world"); // hello world
SELECT U.FIRST_NAME || ' ' || U.LAST_NAME AS NAME FROM LN.LN_USER U; -- Alexander Hunold
1
2
3
4
5
6

5.2、过滤与排序

/*
过滤查询：

1 - 语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件;
2 - 筛选条件的分类
	1.条件运算符：= > >= < <= <> != 后面两个是不等于
	2.逻辑运算符：AND OR NOT
	3.模糊查询：LIKE 一般搭配通配符使用，可以判断字符型或数值型。通配符 % 任意多个字符 _ 任意单个字符
	4.用于判断NULL：IS NULL / IS NOT NULL
	5.其他运算符：BETWEEN ... AND ... / IN(set)  / EXISTS / NOT EXISTS
*/
-- 1.例如查询薪资在5000-9000
SELECT U.LAST_NAME, U.SALARY FROM LN.LN_USER U WHERE 1 = 1 
AND U.SALARY BETWEEN 5000 AND 9000; -- WHERE U.SALARY >= 5000 AND U.SALARY <= 9000

-- 2.查询用户id是22或23的数据
SELECT U.* FROM LN.LN_USER U WHERE U.ID IN (22, 23); -- WHERE U.ID = 22 OR U.ID = 23

-- 3.查询FIRST_NAME字段第三个字母是e的数据
SELECT U.* FROM LN.LN_USER U WHERE 1 = 1 AND U.FIRST_NAME LIKE '__e%';

-- 4.查询 LAST_NAME 有下划线的数据，注意 %_% 会查出所有数据此时下划线表示某一个字符，因此需要一个转义字符转义下划线
SELECT U.* FROM LN.LN_USER U WHERE U.LAST_NAME LIKE '%\_%' ESCAPE '\';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

/*
排序查询 ORDER BY：

1 - 语法
SELECT 查询列表 FROM 表 WHERE 筛选条件 ORDER BY 排序列表 [ASC/DESC]

2 - 特点
    1、ASC ：升序，如果不写默认升序 DESC：降序
    2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
    3、ORDER BY的位置一般放在查询语句的最后（除 ROWNUM 语句之外）
*/
-- 查询前端开发部门(部门id=7)的用户数据并且薪资降序排列
SELECT 
	U.LAST_NAME,
	O.ORG_NAME,
	U.SALARY
FROM LN.LN_USER U
	INNER JOIN LN.LN_ORG_USER OU ON OU.FK_USER_ID = U.ID
	INNER JOIN LN.LN_ORG O ON O.ID = OU.FK_ORG_ID
WHERE 1 = 1
	AND O.ID = 7
ORDER BY U.SALARY DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

我们发现SALARY有相同的情况！针对这种情况我们还可以继续进行二级排序或n级排序

SELECT 
	U.ID,
	U.LAST_NAME,
	O.ORG_NAME,
	U.SALARY
FROM LN.LN_USER U
	INNER JOIN LN.LN_ORG_USER OU ON OU.FK_USER_ID = U.ID
	INNER JOIN LN.LN_ORG O ON O.ID = OU.FK_ORG_ID
WHERE 1 = 1
	AND O.ID = 7
ORDER BY U.SALARY DESC, U.ID DESC; -- 二级排序
1
2
3
4
5
6
7
8
9
10
11

5.3、常见函数

函数概述：类似于Java中的方法，提高重用性与隐藏实现细节。

/*
1、字符函数
	CONCAT(ch1, ch2) 连接 与 || 功能类似
	SUBSTR(ch, pos, length) 截取子串
	UPPER(ch) 变大写
	LOWER(ch) 变小写
	REPLACE(source, search_str, replace_str) 替换
	LENGTH(ch) 获取字节长度
	TRIM(trim_source) 去前后空格
	LPAD(expr1, n, expr2) 左填充(不够了才填充)
	RPAD(expr1, n, expr2) 右填充(不够了才填充)
	INSTR(str, substr) 获取E在STR第一次出现的索引下标
*/
SELECT CONCAT('1', '2') FROM DUAL; -- 12
SELECT SUBSTR('12ABC', 0, 2) FROM DUAL; -- 12
SELECT UPPER('abc') FROM DUAL; -- ABC
SELECT LOWER('ABC') FROM DUAL; -- abc
SELECT REPLACE('AAAA', 'A', 'B') FROM DUAL; -- BBBB
SELECT LENGTH('IPHONE') FROM DUAL; -- 6
SELECT TRIM('  ABC  ') FROM DUAL; -- ABC
SELECT LPAD('MC', 10, '+') FROM DUAL; -- ++++++++MC
SELECT INSTR('ABC', 'B') FROM DUAL; -- 2

/*
2、数学函数
    CEIL(n) 向上取整
    ROUND(n, int) 四舍五入
    MOD(n1, n2) 取模
    FLOOR(n) 向下取整
    TRUNC(date, fmt) 截断
*/
SELECT CEIL(3.2) FROM DUAL; -- 4
SELECT ROUND(5.1666, 2), ROUND(5.1666) FROM DUAL; -- 5.17 5
SELECT MOD(9, 2) FROM DUAL; -- 1
SELECT FLOOR(2.9) FROM DUAL; -- 2
SELECT TRUNC(1.666, 2) FROM DUAL; -- 1.66

/*
3、日期函数(日期 + 时间)
	1.SYSDATE 系统日期变量 SELECT SYSDATE FROM DUAL;
	2.在日期上加上或减去一个数字结果任为日期。例如 SELECT SYSDATE - 1 FROM DUAL; -- 获取昨天这个点的日期
	3.两个日期相减返回日期之间相差的 天数(日期不允许做加法运算，无意义)
	4.可以使用数字除24来向日期中加上或减去天数
	
	MONTHS_BETWEEN(date1, date2) 两个日期相差的月数
	ADD_MONTHS(date, int) 向指定日期中加上若干月数
	NEXT_DAY(date, ch) 指定日期的下一个星期 * 对应的日期
	LAST_DAY(date) 本月的最后一天
	ROUND(date, fmt) 日期的四舍五入 fmt 可以是 MONTH/YEAR
*/
SELECT ROUND(TO_DATE('2022-03-16 19:31:54', 'yyyy-mm-dd hh24:mi:ss'), 'MONTH') FROM DUAL; -- 2022-04-01 00:00:00

/*
4、转换函数
	TO_CHAR(x) 转 char
	TO_DATE(ch, fmt) 转 date
	TO_NUMBER(expr, fmt) 转 number
*/
SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24/mi/ss') FROM DUAL; -- 2022/03/16 19/49/41
SELECT TO_DATE('2022-03-16 19:31:54', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

/*
5、通用函数（这些函数适用于任何数据类型，同时也适用于空值）
	NVL(expr1, expr2) 等价于 expr1 == null ? expr2 : expr1
	NVL2(expr1, expr2, expr3) expr1 == null 返回expr3，否侧返回expr2
	COALESCE(expr1, ... exprn) 与NVL相比的优点在于 COALESCE可以处理交替的多个值，如果第一个表达式为空，则返回下一个表达式
	NULLIF(expr1, expr2) 相等返回NULL，不等返回 expr2
*/
SELECT NVL2(1, 'expr2', 'expr3') FROM DUAL; -- expr2
SELECT NVL2(NULL, 'expr2', 'expr3') FROM DUAL; -- expr3
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
62
63
64
65
66
67
68
69
70

5.4、条件表达式/流程控制函数

在编程语言中都会有if else的条件表达式，在SQL中我们也有相应的条件表达式来处理特殊的场景，在SQL中也称为流程控制函数。

/*
在SQL中，我们可以使用以下两种方式处理 if else 逻辑
CASE WHEN 语句
DECODE(expr, [search, result]*, default) 函数

CASE WHEN 的具体语法：
	CASE 变量或表达式或字段 
		WHEN comparison_expr1 THEN return_expr1
		[WHEN comparison_expr2 THEN return_expr2
		WHEN comparison_expr3 THEN return_expr3
		ELSE else_expr]
	END
	或者是
	CASE 
		WHEN 条件1 THEN 值1
		WHEN 条件2 THEN 值2
		...
		ELSE 值N
	END
*/

/*
CASE WHEN案例：查询员工的工资的情况
如果工资 > 20000,显示A级别
如果工资 > 15000,显示B级别 
如果工资 > 10000,显示C级别
否则，显示D级别
*/
SELECT SALARY,
	CASE
		WHEN SALARY > 20000 THEN 'A'
		WHEN SALARY > 15000 THEN 'B'
		WHEN SALARY > 10000 THEN 'C'
	ELSE 'D'
	END AS 工资级别
FROM LN.LN_USER;
/*
DECODE(expr, [search, result]*, default) 使用案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示个工资为1.2倍 
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT 
	U.LAST_NAME,
	DECODE(O.ID, 
		30, U.SALARY * 1.1, 
		40, U.SALARY * 1.2, 
		50, U.SALARY * 1.3, 
		U.SALARY) AS SALARY
FROM LN.LN_USER U 
	INNER JOIN LN.LN_ORG_USER OU ON OU.FK_USER_ID = U.ID
	INNER JOIN LN.LN_ORG O ON O.ID = OU.FK_ORG_ID 
WHERE 1 = 1
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

5.5、分组函数

分组函数用作统计使用，又称为聚合函数或统计函数或函数。作用于一组数据，并对一组数据返回一个值。

/*
1、常见的分组函数有如下几种
	MAX(expr) 最大值
	MIN(expr) 最小值
	SUM(expr) 和
	AVG(expr) 平均值
	COUNT(expr) 计算个数

2、分组函数支持的类型说明
	SUM 和 AVG 一般用于处理数值型
	MAX MIN COUNT 可以处理任何数据类型

3、其他说明
	分组函数都忽略NULL
	分组函数都可以搭配 DISTINCT 使用，实现去重的统计 SELECT SUM(DISTINCT U.SALARY) FROM LN.LN_USER U;
	COUNT(字段) 统计该字段非空值得个数
	COUNT(*) 统计结果集的行数
	和分组函数一同查询的字段，要求是GROUP BY后出现的字段
*/

SELECT SUM(SALARY) FROM LN.LN_USER;
SELECT AVG(SALARY) FROM LN.LN_USER;
SELECT MIN(SALARY) FROM LN.LN_USER;
SELECT MAX(SALARY) FROM LN.LN_USER;
SELECT COUNT(SALARY) FROM LN.LN_USER;
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

5.6、多表查询

多表查询又称连接查询，当查询的字段来自于多个表时，就会用到连接查询。连接查询需要注意的是避免产生笛卡尔乘积（表1有n行，表2有n行，查询结果就是 n * m 行）造成冗余数据查询。大多数造成笛卡尔乘积的原因都是没有有效的连接条件，因此使用连接查询时一定要添加有效的连接条件（等值连接）。

-- 笛卡尔乘积现象举例
SELECT 
	 U.LAST_NAME,
	 O.ORG_NAME
FROM LN.LN_USER U, LN.LN_ORG O
WHERE 1 = 1
-- 总共输出 63 条记录

SELECT COUNT(*) FROM LN.LN_USER; -- 7
SELECT COUNT(*) FROM LN.LN_ORG; -- 9

-- 这里我们使用第三张表维护了它们之间的关系，所以正确的写法如下
-- SQL92标准写法(仅仅支持内连接、往后的SQL都不推荐这种写法) 
SELECT 
	 U.LAST_NAME,
	 O.ORG_NAME
FROM LN.LN_USER U, LN.LN_ORG O, LN.LN_ORG_USER OU
WHERE 1 = 1
	AND U.ID = OU.FK_USER_ID
	AND O.ID = OU.FK_ORG_ID
/*
关于SQL92标准的等值连接补充：
	1. 多表等值连接的结果为多表的交集部分
	2. n表连接，至少需要n-1个连接条件（例如上述例子三张表相连，需要有2个连接条件）
	3. 多表的顺序没有要求
	4. 一般需要为表起别名
	5. 可以搭配前面介绍的所有子句使用，如排序、分组、筛选
*/	

/*
SQL99标准写法(支持多种连接方式，推荐写法)
语法如下
SELECT 查询列表 FROM 表1 别名 [连接类型] JOIN 表2 别名 ON 连接条件 [WHERE 筛选条件]
                                                                   [GROUP BY 分组]
                                                                   [HAVING 筛选条件]
                                                                   [ORDER BY 排序列表]
其中 连接类型 又有多种：
	1. 内连接(等值 & 非等值)：INNER
	2. 外连接：OUTER
	    左外：LEFT [OUTER]
	    右外：RIGHT [OUTER]
	    全外：FULL [OUTER]
	3. 交叉连接：CROSS
*/
# 因此SQL99标准写法如下
# 等值连接
SELECT 
	 U.LAST_NAME,
	 O.ORG_NAME
FROM LN.LN_USER U 
	INNER JOIN LN.LN_ORG_USER OU ON OU.FK_USER_ID = U.ID
	INNER JOIN LN.LN_ORG O ON O.ID = OU.FK_ORG_ID
WHERE 1 = 1
/*
关于SQL99标准的等值连接补充：
	1. 添加排序、分组、筛选
	2. INNER 可以省略
	3. 筛选条件放在WHERE后面，连接条件
	4. INNER JOIN连接和SQL192语法中的等值连接效果是一样的，都是查询多表的交集
*/

# 非等值连接
# 查询员工的工资级别
SELECT SALARY, GRADE_LEVEL FROM EMPLOYEES E 
INNER JOIN JOB_GRADES G ON E.SALARY BETWEEN G.LOWEST_SAL AND G.HIGHEST_SAL;
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
62
63
64
65

在实际开发中，我们使用INNER JOIN（INNER 可以省略）和LEFT JOIN就能适应百分之90的场景。更多JOIN连接理论参照文章：https://blog.csdn.net/m0_46357847/article/details/120210352

内外连接补充：

1. 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
2. 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。

/*
外连接的应用场景：用于查询一个表中有，另一个表没有的记录
特点：
    1.外连接的查询结果为主表中的所有记录
        如果从表中有和它匹配的，则显示匹配的值
        如果从表中没有和它匹配的，则显示NULL值
        外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
    2. 左外连接，LEFT JOIN 左边的是主表
       右外连接，RIGHT JOIN 右边的是主表
    3. 左外和右外交换两个表的顺序，可以实现同样的效果
    4. 全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的
*/
# 案例：查询哪个部门没有员工
# 左外
SELECT D.*, E.EMPLOYEE_ID FROM DEPARTMENTS D
LEFT OUTER JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.EMPLOYEE_ID IS NULL;

# 右外
SELECT D.*, E.EMPLOYEE_ID FROM EMPLOYEE E
RIGHT OUTER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.EMPLOYEE_ID IS NULL;

# 交叉连接
SELECT B.*, BO.*
FROM BEAUTY B
CROSS JOIN BOYS BO; -- 笛卡尔积
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

5.7、转列函数

所谓转列就是按某字段进行分组后，将多行合并为一行，如：

1、转列函数基础用法（LISTAGG(xx, ',') WITHIN GROUP (ORDER BY xx)）分组以后将每一列按指定字符进行拼接

WITH TB_USER AS (
    SELECT 1 AS ID, 'alex' AS NAME, 15 AS AGE, 10 AS HIGH, '1358226000000' AS PHONE FROM DUAL UNION ALL
    SELECT 2 AS ID, 'howie' AS NAME, 15 AS AGE, 15 AS HIGH, '1358226000000' AS PHONE FROM DUAL UNION ALL
    SELECT 3 AS ID, 'oracle' AS NAME, 21 AS AGE, 19 AS HIGH, '1358226000000' AS PHONE FROM DUAL UNION ALL
    SELECT 4 AS ID, 'python' AS NAME, 18 AS AGE, 19 AS HIGH, '1358226000000' AS PHONE FROM DUAL
)

/*SELECT
    U.PHONE,
    LISTAGG(U.AGE, ',') WITHIN GROUP (ORDER BY U.ID) AS AGES
FROM TB_USER U GROUP BY U.PHONE;*/
-- 使用案例：查询年龄分布情况
SELECT
    U.AGE,
    LISTAGG(U.NAME, ',') WITHIN GROUP (ORDER BY U.ID) AS NAMES
FROM TB_USER U GROUP BY U.AGE;
/*
AGE NAMES
15  alex,howie
18  python
21  oracle
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2、转列函数高级用法（使用开窗函数进行分组）

WITH TB_USER AS (
    SELECT 1 AS ID, 'alex' AS NAME, 15 AS AGE, 10 AS HIGH, '1358226000000' AS PHONE FROM DUAL UNION ALL
    SELECT 2 AS ID, 'howie' AS NAME, 15 AS AGE, 15 AS HIGH, '1358226000000' AS PHONE FROM DUAL UNION ALL
    SELECT 3 AS ID, 'oracle' AS NAME, 21 AS AGE, 19 AS HIGH, '1358226000000' AS PHONE FROM DUAL UNION ALL
    SELECT 4 AS ID, 'python' AS NAME, 18 AS AGE, 19 AS HIGH, '1358226000000' AS PHONE FROM DUAL
)

SELECT
    U.HIGH,
    LISTAGG(U.NAME, ',') WITHIN GROUP (ORDER BY U.ID) OVER (PARTITION BY U.HIGH) AS NAMES
FROM TB_USER U ORDER BY U.HIGH;
/*
HIGH NAMES
10  alex
15  howie
19  oracle,python
19  oracle,python
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

END

THANK YOU