Oracal学习

Oracle是什么

是甲骨文公司的一款支持事务且吞吐量高的数据库

特点：
（1）支持多用户、大事务量的事务处理
（2）数据安全性和完整性控制
（3）支持分布式数据处理
（4）可移植性

Oracal数据类型及语句

Oracal数据类型

1.字符型

1.1CHAR:固定长度的字符类型,最多存储2000个字节
1.2VARVCHAR2:可变长度的字符类型,最多4000个字节
1.3LONG:打文本类型,最大可存储2个G

2.数值型

NUMBER:数值类型
例如:NUMBER(5) 最大可以存的数为99999
NUMBER(5,2) 最大可以存的数为999.99–(5为总位数)

3.日期型

3.1DATE:日期时间型,精确到秒
3.2TIMESTAMP:精确到秒的小数点后9位

4.二进制(大数据类型)

4.1CLOB:存储字符,最大4G
4.2BLOB:存储图像、声音、视频等二进制数据,最多4个G

Oracal语句（语句基本=MYSQL）

建表

CREATE TABLE 表名 (
    列名1 数据类型(长度) [约束条件],
    列名2 数据类型(长度) [约束条件],
    ...
    列名n 数据类型(长度) [约束条件],

    PRIMARY KEY (主键列名),
    FOREIGN KEY (外键列名) REFERENCES 外部表名(外部键列名),
    
    UNIQUE (唯一索引列名),
    CHECK (列名 BETWEEN 值1 AND 值2), -- 或其他检查条件
    INDEX 索引名 ON (列名)
);

-- 示例：
CREATE TABLE Employees (
    EmployeeID NUMBER(6) PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    HireDate DATE NOT NULL,
    Salary NUMBER(8, 2) CHECK (Salary > 0),
    DepartmentID NUMBER(3),
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
NUMBER(p, s)：指定数字类型的精度（p）和小数位数（s），如NUMBER(6)表示整数，NUMBER(8, 2)表示最多有两位小数的数值。
VARCHAR2(length)：变长字符串类型，最大长度由括号内的数字决定。
DATE：日期时间类型。
PRIMARY KEY：定义主键约束，确保字段值唯一且非空。
FOREIGN KEY：定义外键约束，关联到另一个表的主键。
CHECK：定义检查约束，限制列值满足特定条件。
UNIQUE：定义唯一性约束，保证该列所有行的值都是唯一的。
INDEX：创建一个索引，提高查询性能。不过，在创建表时直接定义索引并不常见，通常在建表后单独创建。

复制表

-- 完全复制表结构及数据
CREATE TABLE new_table_name AS
SELECT * FROM old_table_name;

-- 只复制表结构（不包含数据）
CREATE TABLE new_table_name AS
SELECT * FROM old_table_name
WHERE 1 = 2; -- 这里添加一个永远不满足的条件，确保不会复制任何数据

-- 复制部分列并复制数据
CREATE TABLE new_table_name AS
SELECT column1, column2, column3 FROM old_table_name;

删除表

方式一：drop table 表名;
方式二：truncate table 表名;

在Oracle 中 ,双引号主要是别名的时候使用, 单引号是使用的值, 是字符

oracle函数

oracle的空值处理（类似于mysql的ifnull）

nvl（参数1，参数2）：如果1为null，则返回2，不为null就为1

nvl2（参数1, 参数2, 参数3）1为null，则返回,3，不为null，则返回2

decode(需要判断的字段,
 常量值1, 结果值1,
 常量值2, 结果值2,
 …
 常量值n, 结果值n,
 默认结果值
 )

select e.ename 姓名,
       e.sal 底薪,
       e.comm 奖金,
       nvl(e.sal+e.comm, e.sal) 实发1,
       nvl2(e.comm, e.sal+e.comm, e.sal) 实发2,
       decode(e.comm, null, e.sal, e.sal+e.comm) 实发3
from emp e;

nvl（mysql的ifnull）

nvl（参数1，参数2）：如果1为null，则返回2，不为null就为1

nvl2（mysql的if）

nvl2（参数1, 参数2, 参数3）1为null，则返回,3，不为null，则返回2

decode（没合适的试下这个）

···Oracle的DECODE函数是一个条件表达式函数，它在SQL查询中用于基于某个字段或表达式的值返回不同的结果。下面通过几个例子来说明如何使用DECODE函数：

示例1：简单条件判断

假设我们有一个员工表EMPLOYEES，其中包含一个字段SALARY_GRADE表示工资等级（例如，0代表初级，1代表中级，2代表高级），我们想在查询结果中显示对应的等级名称而不是数字。

SELECT 
    employee_name,
    DECODE(salary_grade, 0, '初级', 1, '中级', 2, '高级') AS salary_level
FROM 
    EMPLOYEES;

在这个例子中，DECODE(salary_grade, 0, '初级', 1, '中级', 2, '高级')的工作原理是：

如果 salary_grade 的值等于0，则返回字符串 '初级'；
如果 salary_grade 的值等于1，则返回 '中级'；
如果 salary_grade 的值等于2，则返回 '高级'；
若 salary_grade 不等于上述任何一个值，则默认返回 NULL（如果没有指定默认值）。

示例2：带有默认值的情况

如果我们还想为未列出的其他所有等级提供一个默认等级名称“未知”，可以这样写：

SELECT 
    employee_name,
        DECODE(salary_grade, 0, '初级', 1, '中级', 2, '高级', '未知') AS salary_level
FROM 
    EMPLOYEES;

在这里，最后的一个参数 '未知' 就是当 salary_grade 不匹配前面任何条件时返回的默认值。

示例3：嵌套使用

更复杂的场景下，可能需要多个条件和嵌套的DECODE函数，例如：

Sql
SELECT 
    employee_name,
    DECODE(
        department_id,
        10, DECODE(job_title, 'Manager', '部门经理', 'Employee'),
        20, DECODE(job_title, 'Engineer', '工程师'),
        '未知职位'
    ) AS position
FROM 
    EMPLOYEES;

这个例子中，首先根据 department_id 进行判断：

如果 department_id 是10，并且 job_title 是 'Manager'，则返回 '部门经理'；
否则，如果 department_id 是10且 job_title 不是 'Manager'，则返回 'Employee'；
如果 department_id 是20并且 job_title 是 'Engineer'，则返回 '工程师'；
对于所有其他情况，返回 '未知职位'。

示例4：达到nvl2效果的写法

decode ((SELECT 1
            FROM CM_MATERIAL b
            WHERE a.site_code = b.site_code
            AND b.CUSTOM_ATTRIBUTE = 'CELL'
            AND f.PART_MATERIAL_CODE = b.material_code), null,'','BatteryCell') as temp

  如果SELECT 1...
  这坨返回null，则temp显示为 ''
  这坨返回不为null，则temp显示为 BatteryCell

字符串拼接 ||

1. Oracle 特有的连接符: || 拼接

select 'SN:' ||  ppk.SN_CODE "字符串拼接后别名"
from PP_KEY_PART_BYTE ppk

2. concat(str1,str2) 函数, 在mysql和Oracle中都有

select concat('SN:',ppk.SN_CODE) "函数拼接字符串后别名"
from PP_KEY_PART_BYTE ppk;

在Oracle SQL中，ESCAPE 关键字通常与 LIKE 或 REGEXP_LIKE 这样的字符串匹配函数一起使用，用于指定通配符的转义字符。当你的搜索模式中包含特殊字符（如 %、_ 等）并且你想让这些字符按照字面意义进行匹配时，就需要使用转义字符。

示例1：LIKE 语句中的 ESCAPE

假设我们有一个名为 PRODUCTS 的表，其中有一个 DESCRIPTION 字段，现在要查找描述中包含 % 字符的产品：

Sql
SELECT * FROM PRODUCTS
WHERE DESCRIPTION LIKE '%\%%' ESCAPE '\';
在这个例子中，ESCAPE '\' 指定了反斜杠 \ 作为转义字符。因此，'\%' 在查询中表示的是一个字面上的 % 而不是一个通配符。这个查询将返回所有描述字段中包含 % 字符的产品记录。

排序时控制null的显示位置

···在Oracle SQL中，当进行排序时，默认情况下NULL值的处理可能因数据库版本和排序方式（升序或降序）而有所不同。但你可以通过NULLS FIRST 或 NULLS LAST 来明确指定NULL值在排序结果中的位置。

例如，在一个名为 EMPLOYEES 的表中，假设有一个字段 BONUS 可能包含NULL值，我们想要按照 BONUS 进行排序，并控制NULL值的位置：

将NULL值放在最后（默认在某些情况下的升序排序行为）：

Sql
1SELECT *
2FROM EMPLOYEES
3ORDER BY BONUS ASC NULLS LAST;

将NULL值放在最前（对于降序排序，这是默认行为；对于升序排序，需要明确指定）：

Sql
1SELECT *
2FROM EMPLOYEES
3ORDER BY BONUS DESC; -- 在降序排序下，NULL值默认显示在前面
4
5-- 如果你希望在升序排序时NULL值也显示在前面，则这样写：
6SELECT *
7FROM EMPLOYEES
8ORDER BY BONUS ASC NULLS FIRST;