Oracle数据库

已于 2024-01-23 15:58:15 修改
阅读量1.1w 收藏 88
点赞数 18
分类专栏： Oracle 文章标签： 数据库 oracle database
于 2022-03-09 22:25:01 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_46357847/article/details/123388884
版权

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

容器启动成功后，我们只完成了最简单的一步，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

至此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;

在这里插入图片描述
因此启动容器时我们就可以使用 -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

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;

在这里插入图片描述
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件（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 即可如下图

在这里插入图片描述

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

例如查看所有表空间信息（可自行测试下查询SQL）
在这里插入图片描述
那么如何创建自己的表空间呢？

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

再次查询所有表空间信息
在这里插入图片描述
表空间创建好后，如何给用户使用呢？也可以使用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];

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
  );

追加字段

ALTER TABLE LN.LN_USER ADD (
	REMARK VARCHAR2(100)
)

修改字段类型

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

修改字段名

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

删除字段名

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

删除表

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

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

2.5、Oracle数据类型

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

1、字符型

类型描述
CHAR类型定长字符串，会用空格填充来达到最大长度。例如非NULL的CHAR(12)总是包含12字节信息。CHAR类型字段最多可以存储2000字节信息，如果创建表是，不指定长度则默认为1。
VARCHAR2类型可变长字符串，最大可以存储4000字节的消息。不会使用空格填充至最大长度。
NVARCHAR2类型这是一个包含UNICODE格式数据的变长字符串。也就是说存储单位是字符（例如NVARCHAR2(10)可以存10个汉字也也只可以存10个英文，而VARCHAR2(10)可以存5个汉字，英文则可以存10个），最大可以存储4000字节的消息。

2、数字型

类型描述
NUMBER类型NUMBER(P, S)是最常见的数字类型，P 是Precision 的英文缩写，即精度缩写，表示有效数字的位数，最多不能超过38 个有效数字。S 是Scale 的英文缩写，表示小数点数字的位数。例如 NUMBER(5)最大可以存储的数为99999。NUMBER(5, 2)最大可以存储的数为999.99.
INTEGER类型INTEGER 是NUMBER 的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。

3、日期类型

类型描述
DATE类型DATE 是最常用的数据类型，日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有特殊关联的属性。为每个日期值，Oracle存储以下信息： 世纪、年、月、日期、小时、分钟和秒。一般占用7 个字节的存储空间。
TIMESTAMP类型这是一个7 字节或12 字节的定宽日期/时间数据类型。它与DATE 数据类型不同，因TIMESTAMP 可以包含小数秒，带小数秒的TIMESTAMP 在小数点右边最多可以保留9 位。

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

类型描述
BLOB类型BLOB即二进制大型对象（Binary Large Object），适用于存贮非文本的字节流数据（如程序、图象、影音等），最多能容纳4GB的数据。
CLOB类型CLOB，即字符型大型对象（Character Large Object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等），最多能容纳4GB的数据。

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;

插入语句

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

SELECT U.* FROM LN.LN_USER U;

更新语句

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

SELECT U.* FROM LN.LN_USER U;

删除语句

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

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

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>

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

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);
    }
}

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>

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

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;
    }
}

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

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

}

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);
    }
}

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

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);

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 表名;
*/

在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

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 '\';

在这里插入图片描述

/*
排序查询 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;

在这里插入图片描述
我们发现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; -- 二级排序

在这里插入图片描述

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

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

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;

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;

在实际开发中，我们使用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; -- 笛卡尔积

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
*/

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
*/

END

THANK YOU

lambda.
关注
  • 18
    点赞
  • 踩
  • 88
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
专栏目录
Oracle
2203_75975871的博客
05-17 1171
``sql-- 创建新表CREATE TABLE 表名(字段名 数据类型 约束[, 字段名 数据类型 约束, ...]);-- 示例id number,``````sql-- 复制表CREATE TABLE 表名 as select查询语句;-- 示例```**一、含义：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行****二、特点（ACID）**1. 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行。
mybaits中date类型显示时分秒(orcle数据库)
weixin_33852020的博客
04-01 252
<insert id="insert" parameterType="daSysLoginLog"> insert into DA_SYS_LOGINLOG( LOGINIP, LOGINDATE, USERID, ID )VALUES ( #{loginip}, #{logindate}, ...
数据库基础教程(Oracle)
lLlLlL__lL的博客
08-13 1万+
　　随着网络化的快速发展，信息技术也向着多样化的趋势发展，同时人们的生活也越来越离不开信息技术。对信息进行管理的数据库技术在计算机中也不断被使用，组成了信息系统中不可缺少的部分。随着数据库技术在信息管理中的普遍使用，对信息管理变得更科学、更便捷，而且实现了信息管理的现代化和合理化，更好的促进了信息管理的效率，从而得到了更多人的欢迎和推广。该文将对计算机数据库技术相关理论进行合理的分析，对数据库技术在信息管理中的使用情况进行分析，并提出一些科学有效改善的措施和方法，促进数据库技术在信息管理中更好地发挥其优势。
Oracle数据库 入门教程
热门推荐
qq_42589612的博客
07-06 12万+
Oracle数据库1.1  数据库环境安装1.Oracle数据库产品是免费的，我们可以从Oracle的官方网站(http://www.oracle.com)下载到程序安装包，Oracle在Windows下的安装非常方便，安装开始后，一直点击安装程序的“下一步”即可。2. 下载Oracle10g后，解压到一个文件夹下，单击“setup.exe”文件即可启动安装界面。。填写全局数据库名，以及管理员的密...
三.数据库基础-Oracle入门到精通学习这一篇就够了（详细）
ThatsThePrice的博客
03-07 4570
oracle入门到精通
一篇文章让你了解Oracle(Oracle知识点总汇)
qq_51753851的博客
07-21 1601
视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中，视图的结构和数据是对数据表进行查询的结果。根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个基表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使用。优点OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
Oracle数据库深入学习
一个成长中的程序员，期待用技术的眼光生活
01-03 1万+
一、Oracle概念 oracle数据可系统是美国oracle（甲骨文）公司提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器或B/S体系结构的数据库之一，oracle数据库时目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完善的数据库管理功能，是关系型数据库，比mysql更为庞大，在现行的关系型数据库中排名第一（oracle、mysql、SqlServe...
mysql和oracle有什么区别
asd3331380的博客
11-15 954
转自：微点阅读www.weidianyuedu.com 1. mysql和oracle都是关系型数据库，可以应用于各种平台。我们用的oracle的版本是oracle11g ,用的mysql的版本是mysql5.5。mysql最开始是瑞典一个公司开发的，开源的，但是后来被sun公司收购，后来sun又被oracle收购，所以现在可以说mysql属于甲骨文公司了！现在用mysql的公司也有很多，mysql价钱便宜些，处理千万级别的数据不成问题的，并且开源，很友好！ 2. mysql默认端口：3306 默认.
Veeam 备份恢复oracle数据库详细配置文档
10-07
Veeam 备份恢复 Oracle 数据库详细配置文档 本文档旨在详细介绍如何使用 Veeam 备份恢复 Oracle 数据库的配置过程。该文档将指导读者从环境准备到推送 Oracle RMAN Plugin，再到创建备份作业和运行备份作业，最后...
nacos适配oracle数据库
03-01
1.nacos服务，适配oracle数据库11g。 2.提供nacos，oracle的创建nacos数据库脚本。 3.nacos-dm/conf/nacos-oracle-11g.sql文件。 4.nacos版本1.4.2
Oracle 数据库 常用巡检脚本
06-08
Oracle 数据库 常用巡检脚本
oracel 数据库安全
05-22
oracel 数据库安全
oracle数据库从入门到精通.pdf
07-26
详细介绍了oracle数据库，从表空间的创建到存储过程、游标的使用等内容，通过阅读这篇文档能够让各位对oracle数据库有个深入的理解和掌握
Oracle数据库安装
02-26
可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的 适应高吞吐量的数据库解决方案。
oracle rdbms归档,Oracle归档日志管理
weixin_30641571的博客
04-11 284
归档日志的两点好处：完成部分Recovery在线Backup非归档模式只能进行全部的备份和离线的备份，相应的在数据恢复时也是相应的全恢复，而运行在归档模式下的数据库可以做部分的修复与备份，可以实现历史数据的恢复，也可以完成即时错误的恢复。查看归档模式SQL> archive log list数据库日志模式非存档模式自动存档禁用存档终点USE_DB_RECOVERY_FILE_DEST最早的联...
oracle-rdbms,Oracle数据库管理脚本介绍 (RDBMS目录下)
weixin_33134401的博客
04-04 1422
Oracle中的数据字典是数据库中的非常重要的一部分，提供了相关数据库的信息，它是所有Oracle用户(从应用的最终用户、应用的设计开发者到数据库管理员)的非常重要的信息来源。数据字典的创建，是在Oracle数据库创建完毕后，运行一些相关的数据库管理脚本来完成的。当采用Oracle统一安装器(Oracle Universal Installer)创建数据库时，Oracle服务器有关的字典视图和脚本...
一篇文章搞定oracle数据库--oracle12c
烛照@123的博客
11-12 1216
oracle也是数据库的一种，学会oracle数据库，对于企业开发也是有很大的帮助。
Oracle数据库统计数据量的大小的命令
Small_Casee的博客
05-26 1万+
通过这个命令查询出来的是数据库中所有表的大小总和。如果你想查询某个特定用户的表，可以在 WHERE 子句中添加条件。这个命令将会查询 HR 用户的所有表的大小，并按照表大小从大到小排序。
Oracle数据库环境
最新发布
03-20
Oracle数据库环境是指安装和配置Oracle数据库所需的软件和硬件环境。以下是Oracle数据库环境的一般要求和组成部分： 1. 硬件要求：Oracle数据库通常需要一台或多台服务器来运行。硬件要求包括处理器、内存、存储和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lambda.

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值