Oracle基础知识

目录

1.别名的使用

2.AND的优先级高于OR

3.where后面可以接别名，order by后面不可以

4.Oracle中SQL的执行顺序(重点)

5.dual万用表

6.是否区分大小写

7.Oracle常用数据类型

8.Oracle常用函数

(1)length字符、lengthb字节和cast强制类型转换

(2)数据类型转换

to_date()：将字符串转换为日期类型 

to_char()：将日期、时间戳或数字转换为字符串格式

to_number()：将字符串转为数字类型

9.NOT BETWEEN  AND 反向区间

10.NOT IN

11.LIKE模糊查询

12.空值NULL

(1)空值的过滤

(2)空值的排序

(3)空值运算

(4)空值转换NVL与NVL2

1.别名的使用

 别名–数字、字母、下划线组成，不能数字和符号打头 

必须以字母开头；

长度不能超过 30 个字符；

只能包含字母（A-Z、a-z）、数字（0-9）、下划线（_）和美元符号（$）；

不能使用 Oracle 的保留字（比如：SELECT、FROM、WHERE……）；

不能和同一个用户下的其他对象重名；

中文别名，as可以省略

表的别名不能使用as。

如果想使用一些特殊字符或不符合上述规则的名称作为别名，可以将别名用双引号括起来。

select ename 员工姓名, sal as "员工薪资", deptno "部门编号"
from emp
where job = 'SALESMAN';

2.AND的优先级高于OR

使用括号明确优先级：

SELECT * FROM employees
WHERE (dept_id = 10 OR dept_id = 20) AND salary > 5000;

3.where后面可以接别名，order by后面不可以

具体原因是根据Oracle中SQL的执行顺序来的，详见下面 

4.Oracle中SQL的执行顺序(重点)

5.dual万用表

dual/万能表/伪表

Oracle 中dual表的存在主要是为了满足语法要求和提供一些便利。

dual表是 Oracle 与数据字典一起自动创建的一个单行单列的虚拟表，它只有一列

虚拟的，不包含任何数据

select 7+9 from dual;

select sysdate from dual;

6.是否区分大小写

字符串里面要区分大小写

不区分大小写的：字段名、表名

select * from emp where ename='SCOTT';

select * from emp where ename='scott';

7.Oracle常用数据类型

CHAR()和VARCHAR2()区别：

CHAR()始终占用定义的最大长度的存储空间，如果存储的数据长度小于这个长度，会使用空格填充剩余的空间

VARCHAR2()只占用实际字符数据的存储空间，可变长度

8.Oracle常用函数

(1)length字符、lengthb字节和cast强制类型转换

LENGTH(x)：返回字符个数 11 'WH' '你好'         — 文本信息的基本单位

LENGTHB(x)：返回字节数                                 — 数据存储的基本单位

CAST()：转换数据类型                                       — cast('123' as number)

— 一个英文/数字占用1个字节 — 一个中文占用2个字节 — GBK：一个汉字占两个字节 — utf-8：一个汉字占三个字节

select lengthb('你好') from dual; — 4

select length('你好') from dual; — 2

select length(cast(123 as char(4))) from dual; — 4

SELECT LENGTH(11), — 2
LENGTH('HH'), — 2
LENGTHB('HH') — 2
FROM DUAL;

(2)数据类型转换

–隐式转换 会造成索引失效

SELECT ename e1,ENAME E2–字段不区分大小写
FROM EMP
WHERE DEPTNO='10'; — 错误写法，会导致索引失效

to_date()：将字符串转换为日期类型 

TO_DATE(string, format_model)

注意：前后要保持一致。 

TO_DATE 括号里面 年月日必须完整 否则会给一个默认值

select to_date('20250520', 'YYYYMMDD') as a1,
to_date('2025-05-20', 'YYYY-MM-DD') as a2
from dual;

例题：查询 所有 1982年之前入职的员工薪资，输出：员工姓名   员工入职时间  员工薪资

select EMPNO, hiredate, SAL
from emp
where hiredate < to_date('1982-01-01', 'yyyy-MM-DD')

to_char()：将日期、时间戳或数字转换为字符串格式

TO_CHAR 括号里面 可以只取年  只取月  只取日

select hiredate as a1,
to_char(hiredate, 'YYYYMMDD') as a2,
to_char(hiredate, 'YYYY-MM-DD') as a3,
to_char(hiredate, 'MM/DD/YYYY') as a4,
to_char(hiredate, 'YYYY') as a5,
to_char(hiredate, 'MM/DD') as a6
from emp;

使用to_char()查询1982年之前入职的员工： 

select * from emp where to_char(hiredate, 'yyyy') < 1982;

select * from emp where to_number(to_char(hiredate, 'yyyy')) < 1982;

将系统当前时间 以年月日的形式展示，并且是日期类型：

— 思路：
SELECT SYSDATE FROM DUAL;–查看默认时间样式

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL;–提取年月日字符串类型

SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD')–转化为日期格式
FROM DUAL;

to_number()：将字符串转为数字类型

字符串靠左，数字类型靠右

总结：

日期 → 字符串类型 ：TO_CHAR(日期字段,'输出格式')  'YYYYMMDD'/'YYYY'

字符串类型 → 日期 ：TO_DATE(字符串,'输出格式') 'YYYYMMDD'/'YYYY-MM-DD'

字符串 → 数字类型 ：TO_NUMBER(字符串)  可以不用 做隐式转换

–练习：查询入职时间在1981年的员工
–TO_CAHR
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981';
–TO_DATE
SELECT *
FROM EMP
WHERE HIREDATE >= TO_DATE('19810101', 'YYYYMMDD')
AND HIREDATE <= TO_DATE('19811231', 'YYYYMMDD');
–NUMBER–CAST
SELECT *
FROM EMP
WHERE CAST(TO_CHAR(HIREDATE, 'YYYY') AS NUMBER) = 1981;

9.NOT BETWEEN  AND 反向区间

NOT BETWEEN  AND 不包括最大值和最小值

BETWEEN  AND 是包括端点值的

SELECT *
FROM EMP
WHERE SAL NOT BETWEEN 1000 AND 2000;
–WHERE SAL>2000 OR SAL<1000;

10.NOT IN

示例：查询所有不是经理(MANAGER) 也不是文员(CLERK)的信息    –AND/OR

SELECT *
FROM EMP
WHERE JOB <> 'MANAGER'
AND JOB <> 'CLERK';
–WHERE JOB NOT IN ('MANAGER','CLERK')

例题：找出 所有 10号部门 以及 20 号部门 以及 薪资 不在 2000 ~ 3000 这个区间的员工信息。输出：ENAME,SAL,DEPTNO                –IN（X,Y,Z…）

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (10, 20)
AND SAL NOT BETWEEN 2000 AND 3000;

11.LIKE模糊查询

关键字是 LIKE (长的像…) 两个 通配符

 % ：表示 0位以及以上任意字符   

 _  下划线表示  固定一位字符  

示例：查询EMP表中姓名包含S的员工信息

SELECT *
FROM EMP
–WHERE ENAME LIKE '%S%'
–WHERE ENAME LIKE '%S'
WHERE ENAME LIKE 'S%';

示例：找出 姓名是 S开头 且是五位长度的员工姓名 

SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'S%'
AND LENGTH(ENAME) = 5; –五位长度

SELECT ENAME
FROM EMP
WHERE ENAME LIKE 'S____'; — 4个_

查询出名字是5个字，并且第二个字母是C的

SELECT ENAME
FROM EMP
–WHERE ENAME LIKE '_C___'
WHERE LENGTH(ENAME) = 5 –5位长度
AND ENAME LIKE '_C%';

12.空值NULL

(1)空值的过滤

需要 用 IS NULL / IS NOT NULL来判断

示例：筛选 奖金为 空(没有奖金) 的员工信息

SELECT * FROM EMP WHERE COMM IS NULL;

(2)空值的排序

空值排序永远在最大

SELECT * FROM EMP ORDER BY COMM;

–NULLS LAST 最后 –NULLS FIRST 最前

SELECT * FROM EMP ORDER BY COMM NULLS first;

示例：找出所有没有奖金的员工，输出员工姓名  薪资  部门编号，奖金，并且按照薪资的降序排序

SELECT ENAME, SAL, DEPTNO, comm
FROM EMP
WHERE COMM IS NULL
or comm = 0
–WHERE nvl(comm,0)=0
ORDER BY SAL DESC;

(3)空值运算

— 空值不参与比较运算

— 空值跟任何值 进行 加减乘除 都是得到 空(NULL)

(4)空值转换NVL与NVL2

NVL(expr1, expr2)

如果 expr1 为非 NULL，则返回 expr1；如果 expr1 为 NULL，则返回 expr2。

NVL2(表达式,不为空设值,为空设值)
SELECT NVL(comm, 0) FROM EMP;
SELECT NVL2(null, 1, 0) FROM dual;

示例：找出年收入高于3万的员工信息(年收入 = (薪资+奖金) * 12)，并且输出员工姓名、员工薪资、奖金、年收入。按照年收入的倒序排序。

select ename
, sal
, nvl(comm, 0)
, 12 * (sal + nvl(comm, 0)) year_sal–年收入
from emp
where 12 * (sal + nvl(comm, 0)) > 30000–年收入高于3万
order by year_sal desc;

总结：

• 空值：未知的 没有意思的数 不是0 也不是空格
• NULL排序在最大
• 对 null 做+-*/运算得到的还是 NULL
• NULL 不参与比较