oracle 数据库常识以及使用

工具

默认自带 sql plus 工具

• sys 用户，是 oracle 中的超级用户，拥有的权限最大，可以完成数据库的所有管理任务。使用该用户登录的时候，只能以系统管理员（sysdba）和系统操作员（sysoper） 进行登录。
• system 用户，没有 sys 权限大，通常用于创建一些用户进行管理信息的表或者视图。不建议使用该用户创建一些与管理无关的表或者视图。
• scott，用于学习开发的用户。

表空间

永久表空间： 表空间是数据库的逻辑划分，有个表空间只属于一个数据库，所有的数据对象都存储在表空间当中，但是主要存放的是表，称之为表空间。

临时表空间： 临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因，是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间。

create tablespace 永久表空间名称 datafile '永久表空间物理文件位置' size 100m autoextend on next 10m permanent online;

-- 栗子
CREATE TABLESPACE CADB DATAFILE 'CADB.DBF' size 100M AUTOEXTEND ON NEXT 10M permanent online;

--创建表空间
CREATE TABLESPACE SUMEC LOGGING DATAFILE 'SUMEC.dbf' SIZE 100m AUTOEXTEND ON NEXT 100m MAXSIZE 20480m EXTENT MANAGEMENT LOCAL;
--创建用户
CREATE USER SUMEC IDENTIFIED BY 123456 DEFAULT TABLESPACE SUMEC;
--授权用户权限 CONNECT (链接角色)
GRANT CONNECT, RESOURCE, DBA TO SUMEC;
--授权用户权限
--GRANT CREATE DATABASE LINK TO SUMEC;

数据类型

• varchar2 变长，最大存储 4000 字节
• nvarchar2 单位是字符。
• number 既可以存储整数，也可以存储小数
• integer 是 number 的子类，他等同于 number(38,0) 用来存储整数，数值有小数则会四舍五入。
• clob 类型（character large object） 二进制数据，存储单字节和多字节数据，最大长度 4G。
• blob 类型（binary large object） 存储非结构化的二进制文件，视频，影音等，最大也是 4G。

简单 sql

SELECT SYSDATE FROM dual;

CREATE TABLE emp (
  id NUMBER(4),
  name VARCHAR2(20) not null ,
  gender char(10)default 'M',
  birth date,
  sal number(6,2) default 5000,
  job VARCHAR2(30),
  deptno number(2)
);

DESC EMP_table;

drop table emp;

rename emp to emp_table;

ALTER TABLE emp_table add(
 hide_date date,
 accuent number(12)
);

ALTER table emp_table drop(accuent) ;

AlTer table emp_table MODIFY(job varchar2(100));

INSERT INTO EMP_TABLE(
 id,
 name,
 birth
)
VALUES(
 1,
 '张三',
 TO_DATE('2021-10-14','YYYY-MM-DD')
);

基本数据类型

char： 最大取值为 2000 个字节，最多保留2000 个英文字符，1000 个汉字（GBK）。

varchar: 最大取值 4000 个字节，最多保存 4000 个英文字符，2000 个汉字 (GBK)。
char 可以不指定 长度，默认值是 1，varchar2 必须指定长度.

long: varchar2 的加长版，存储变长的字符串，最多达到 2GB 的字符串数据。但是 long 有很多限制，每个表只能有一个 long 类型列；不能作为主键，不能建索引，不能出现在查询条件中等…

clob: 存储定长或者变长的字符串，做多达到 4GB 的字符串数据。

date: 最常用的日期类型，用来保存日期和时间，date 表示日期范围可以是公元前 4712 年 1 月 1 日至公元9999 年 12 月 31 日，date 类型在数据库中存储固定为 7 个字节，格式为：

• 第 1字节：世纪 + 100
• 第 2 字节：年
• 第 3 字节：月
• 第 4 字节：天
• 第 5 字节：小时 + 1
• 第 6 字节： 分 + 1
• 第 7 字节： 秒 + 1

TIMESTAMP: 日期类型，与 DATE 的区别是不仅可以保存到日期和时间，还能保存小数秒，最高精度可以达到 ns(纳秒)。数据库内部用 7 或者 11 个字节存储，精度为 0, 用 7 字节存储，与 DATE 功能相同，精度大于 0 则用 11 字节进行存储。
格式为：

• 第 1 字节至第 7 字节：与 date 相同。
• 第 8-11 字节：纳秒，采用 4 字节存储，内部运算类型为整形。

函数

|| 符号可以连接字符串：

select name || ',' || gender from emp;

LENGTH（char） 用于返回字符的长度，如果字符类的 varchar2，返回字符的实际长度，如果字符类型是 char，长度还要包括后补的空格。

select LENGTH(name) from emp;

---

upper(char) 用于将字符转换为大写形式。
lower(char) 用于将字符串转换为小写形式。
initcap(char) 用于将字符串中的每个单词的首字母大写。
如果输入的参数值为 NULL 值，仍然返回 NULL 值。

dual 数据库中的伪表；

select upper('hello'),lower('HELLO'),initcap('hello world') from dual；

---

去除字符串两边的指定字符串，用于截取字符串

• TRIM('c2' FROM 'c1') 从 c1 的前后截去 c2。
• LTRIM('c1','c2') 从 c1 的左边（LEFT） 截去 c2;
• RTRIM('c1','c2')，从 c1 的右边（Right）截去 c2
  如果没有 c2 就除去空格

select trim('e' from 'eeliteee') from dual;

select ltrim('eeeeliteee','e') from dual;

---

补位函数：

• LPAD(char1 ,n,char2) 左部位函数。
• RPAD(char1,n,char2) 右补位函数。

select LPAD(sal,6,'$') from emp;

---

SUBSTR(char,m,n) 用于获取字符串的子串，返回 char 中，从 m 位开始取 n 个字符。
如果 m=0，则从首字符开始，如果 m 是负数，则从尾部开始。如果没有设置 n, 或者 n 的长度超过了 char 的长度，则取到字符串的末尾为止。
数据库中的下标都从 1 开始。

select substr('thinking in java',-7,2) from dual;

---

INSTR(char1,char2,m,n) 查看 char2 在 char1 当中的位置，m 为从哪里开始查找，不写的话默认值为 1, n 为第几次出现，不写默认值为1。

select instr('thinking in java','in',4,2) from dual;

---

TRUNC 函数，与 ROUND 函数的意义一致，但是是截取数字。

select TRUNC(22.123,2) from dual;

---

MOD(m,n) 返回 m 除以 n 的余数，n 为 0则直接返回 m。

select MOD(1000,9) from dual;

---

CEIL(n) 和 FLOOR(n) 这两个函数顾名思义，一个是天花板，就是取大于或等于 n 的最小整数值。一个是地板，就是取小于或等于 n 的最大整数值。

SELECT CEIL(4.5),FLOOR(4.5)  FROM dual;

---

SYSDATE 本质上是一个 oracle 内部上的一个函数，返回当前的系统时间，精确到秒。

SYSTIMESTAMP 内部函数，返回当前的系统日期和时间，精确到毫秒(时间戳)

---

TO_DATE() 可以将给定的字符串按照指定的日期格式，转换为 DATE 类型值。

YY	两位数字的年份
YYYY	四位数字的年份
MM	两位数字的月份
MON	简拼的月份
MONTH	全拼的月份
DD	两位数字的天
DY	星期几的缩写
DAY	星期几全拼
HH24	24 小时制的小时
HH12	12 小时制的小时
MI	显示分钟
SS	显示秒

在日期格式字符串中凡不是英文，符号的其他字符都需要使用双引号括起来。

select TO_DATE('2021-10-01 13:49:57','YYYY-MM-DD HH24-MI-SS') FROM dual;
-- 有汉字的 
SELECT TO_DATE('2021年11月15日 13时14分15秒','YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"') FROM dual;

TO_CHAR 可以将 DATE 按照给定的日期格式转换为字符串。

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual;

日期类型是可以计算的，对于日期加减一个数字等同于加减天数。两个日期做减法，差为相差的天数。

select SYSDATE+1 from dual;

---

LAST_DAY(date): 返回日期 date 所在月的最后一天，在按照自然月计算某些业务逻辑，或者安排月末周期性活动时候很用用处。

select LAST_DAY(SYSDATE) from dual;

ADD_MONTHS(date,1) 返回日期 date 后加上 i 个月后的日期值。

• 参数 i 可以是任何数字，大部分时候取正值的整数
• 如果 i 是小数，则会被截取后在参与运算。
• 如果 i 是负数，则获取的是减去 i 个月之后的日期值。

select ADD_MONTHS(SYSDATE,3) from dual;

MONTHS_BETWEEN(date1,date2): 计算 date1 和 date2 两个日期值之间间隔了多少个月。
实际运算是 date1-date2，如果 date2 比 date1 时间晚，则会得到负值，除非两个日期是整月，否则会得到带两位小数的结果，比如计算 2009年9月1日 到 2009年10月10日 之间间隔多少个月，会得到1.29 个月。

select MONTHS_BETWEEN
       (TO_DATE('02-02-1995','MM-DD-YYYY'),
        TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"
  FROM DUAL;

NEXT_DATE(date,char): 返回给定日期的第二天开始一周内指定周几的星期。
在中文环境中，直接使用 星期三 这种形式，英文环境下，需要使用 WEDNESDAY 这种英文的星期几，为了避免麻烦，可以使用 1-7 来表示周日-周六

-- 下周三是几号
  select NEXT_DAY(SYSDATE,4) from dual;

---

LEAST(expr1,expr2,expr3)，GREATEST(expr1,expr2,expr3) 被称之为比较函数，可以存在多个参数值，返回结果是参数列表中的最大或者最小的值。参数的类型必须一致，在比较之前，在参数列表中第二个以后的参数，会被隐含的转换为第一个参数的数据类型，所有如果可以转换，则继续比较，如果不能转换则会报错。

---

EXTRACT(date FROM datetime) 从 datetime 中提取参数 date 指定的数据，比如提取年，月，日。date 数据类型只能提取到年月日，提取不到时分秒，但是时间戳类型则可以。

-- 今年是那一年
  SELECT EXTRACT(YEAR FROM SYSDATE) FROM  dual;

---

空值函数：

1. nvl(arg1,arg2)，当 arg1 为 null 的时候返回 arg2 的值，否则返回 arg1 自身，该函数的意义是将 null 值替换为非 null 值。

select nvl(null,0) from dual;

2. nvl2(arg1,arg2,arg3) 当 arg1 不为 null 的时候返回参数 arg2，若为 null，则返回 arg3

sql 查询

我们可以给复杂的列名添加别名，若别名使用双引号，那么别名就可以区分大小写并且包括空格。

select name, sal+1 as "s al" from emp;

---

ANY 和 ALL 条件：ALL 和 ANY 不能单独使用，需要配合单行比较操作符 >,>=,<,<= 一起使用。

• > any: 大于最小
• <any: 小于最大
• >all: 大于最大
• <all: 小于最小

HAVING 字句必须跟 GROUP BY 子句之后，作用是添加过滤条件来过滤 GROUP BY 的分组，它可以将不满足条件的分组除去。HAVING 子句可以使用聚合函数作为过滤条件

selec min(sal),deptno sal from emp group by deptno having min(sal)>500;

... LEFT OUTER JOIN ... ON ... 左外连接
... RIGHT OUTER JOIN ... ON ... 右外连接
... FULL OUTER JOIN ... ON ... 全外连接

-- 左外连接，那边有 + 号，那边补 null
SELECT 
e.empno,e.ename,e.sal,e.job,
d.deptno,d.dname,d.loc
from emp e,dept d
WHERE
e.deptno=d.deptno(+)

在 DDL 语句中使用子查询，直接创建表：

CREATE TABLE myemp 
AS
SELECT 
e.empno,e.ename,e.sal,e.job,
d.deptno,d.dname,d.loc
from emp e,dept d
WHERE
e.deptno=d.deptno(+)

子查询

• 单行单列子查询 —— 返回单行数据
• 多行单列子查询 —— 返回多行数据
• 多行多列子查询 —— 返回多行多列数据

EXISTS 关键字，后面的子查询是否有数据，有数据的话返回 true，没有数据的话返回 false

SELECT d.deptno,d.dname,d.loc FROM dept d
WHERE EXISTS(
SELECT * FROM emp e WHERE e.deptno=d.deptno
)

HAVING 子查询：

SELECT MIN(sal), deptno from emp
GROUP BY deptno 
HAVING MIN(sal) > (
SELECT MIN(sal) from emp WHERE deptno=30
)

FROM 子查询：在 FROM 子句中使用子查询通常是将子查询的结果当作一张表看待，基于该查询结果进行二次查询使用。

栗子：查看谁的工资高于其所在部门的平均工资。

SELECT e.ename,e,sal,e.deptno FROM emp e, 
(
SELECT  AVG(sal) avg_sal ,deptno FROM emp GROUP BY deptno
) t
WHERE e.deptno=t.deptno
and e.sal>t.avg_sal

SELECT 部分子查询：把子查询放在 SELECT 子句部分，可以认为是外连接的另一种表现，使用更灵活：

SELECT e.ename, e.sal (
SELECT d.deptno FROM dept d WHERE d.deptno=e.depetno
) dname FROM emp e;

分页

分页在不同的数据库中， SQL 语句是不同的。
ORACLE 中提供了一个伪列：ROWNUM，该字段不存在于任何一张表中，但是每张表都可以查询到该字段，该字段的值是结果集中每条记录的行号。 ROWNUM 字段的值是动态生成的，伴随着查询过程，只要查询出一条记录，ROWNUM 就会为该条记录生成行号，从 1 开始每次递增 1。

SELECT ROWNUM,name FROM emp;

SELECT *
FROM (
  SELECT ROWNUM rn,t.* 
  FROM ( 
     SELECT empno,ename,sal,deptno
     FROM emp
     ORDER BY sal DESC )  t
     WHERE ROWNUM <= 10)
     WHERE rn >=6

DECODE 函数

DECODE(expr,search1,result1...) 函数，用于比较 expr 的值，如果匹配到哪一个 search 条件，就返回对用的 result 结果。可以有多组 search 和 result 的对应关系，如果没有任何一个 search 条件匹配的上，则返回最后的 default 的值。default 的值是可选的，如果没有提供 default 参数值，当没有匹配的时候，将返回 NULL。

SELECT ename,job,sal,
DECODE(
job,
'MANAGER',sal*2,
'ANALYST',sal*1.1,
'SALESMAN',sal*1.05,
sal) bonus
FROM emp

和 DECODE 函数功能相似的 CASE 语句，实现类似于 if-else 的操作：

SELECT ename,job,sal,
CASE job WHEN 'MANAGER' THEN sal*1.2
         WHEN 'ANALYST' THEN sal*1.1
         WHEN 'SALESMAN' THEN sal*1.05
         ELSE sal END
    bonus
    FROM emp;

DECODE 可以在分组中和排序中使用。

排序函数

排序函数允许将结果集按照指定字段分组，在组内按照指定字段排序，然后该函数为每组生成一个行号。

ROW_NUMBER()： 生成组内连续且唯一的数字

SELECT ename,deptno,sal,
ROW_NUMBER()
OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

RANK 函数： 生成组内不连续也不唯一的数字。

SELECT ename,deptno,sal
RANK()
OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

DENSE_RANK 函数： 生成组内连续但不唯一的数字。

SELECT ename,deptno,sal
DENSE_RANK()
OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

集合操作

UNION 和 UNION ALL，为了合并多个 SELECT 语句的结果，可以使用集合的操作符，实现集合的并，交，差。

集合操作符包括 UNIION，UNION ALL，INTERSECT 和 MINUS，多条做集合操作的 SELECT 语句的列的个数和数据的类型必须匹配，ORDER BY 子句只能放在最后一个查询语句中，语法操作如下：

SELECT statement1 
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;

高级分组函数

ROLLUP(a，b，c....)函数：

GROUP BY ROLLUP(a,b,c)
等同于
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表

CUBE() 函数： 分组策略是为每个参数的组合进行一个分组。

GROUP BY CUBE(a,b,c)
等同于
a,b,c
a,b
a,c
b,c
a
b
c
全表

组合多少次等于 2 的参数的个数次方。

GROUPING SETS() 该函数允许自行指定分组策略，然后将这些分组统计的结果并在一起。函数的每个参数为一种分组方式。

SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY 
GROUPING SETS(
(year_id,month_id,day_id),
(year_id,month_id)
)
ORDER BY year_id,month_id,day_id

视图

视图又称为虚表，即虚拟的表，是一组数据的逻辑表示，视图对应一条 SELECT 语句，结果集被赋予的名字，即视图名，视图本身不包含任何数据，它只包含映射到基表的一个查询语句，当基表的数据发生变化的时候，视图也随之变化。若该字段含有函数或者表达式，那么该字段必须指定别名

CREATE OR REPLACE VIEW v_emp_1 
AS 
SELECT * FROM emp;

简单视图： SELECT 语句是基于单表建立的，且不包含任何函数的运算，表达式或分组函数，叫做简单视图。

复杂视图： SELECT 语句同样是基于单表，但是包含了单行函数，表达式，分组函数或者 GROUP BY 子句。

连接视图： SELECT 语句基于多个表的。

简单的视图可以进行 DML SQL 语句的操作（增删改查），复杂的不行！

可以为视图添加检查选项，来保证对视图进行 DML 操作时不会基础表数据污染。 WITH CHECK OPTION 当视图添加了检查选项后，视图要求对视图中的数据进行 DML 操作后，视图必须对该记录可见，否则不允许操作。

为视图添加只读选项：当一个视图添加了只读选项后，该视图不能进行 DML 操作。WITH READ ONLY

删除视图：

DROP  VIEW v_emp_10;

删除视图不会影响基表中的数据。

数据字典

查看数据字典，有助于了解曾经创建过的的数据库对象。
和视图相关的数字字典

• USER_OBJECTS
• USER_VIEWS
• USER_UPDATE_COLUMNS

-- 在数据库字典 USER_OBJECTS 中查询所有视图的名称
SELECT object_name FROM user_objects WHERE object_type = 'VIEW'

序列

序列也是数据库对象之一，作用是根据指定的规则生成一系列的数字，一般用于表的每一条数据主键提供数据的值。

CREATE SEQUENCE sequence_name
[START WITH i] [INCREMENT BY j]
[MAXVALUE m | NPMAXVALUE]
[MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE p | NOCACHE ]
sequence_name 序列名
序列的第一个序列值是 i，步进是 j
如果 j 是证书，表示递增，如果是负数，表示递减
序列课生成最大值是 m ，最小值是 n
如果没有设置任何可选参数，序列值是 1 步进值也是 1
CYCLE 表示在递增到最大值或递减到最小值后，是否重复使用序列，若递减并有最大值，从最大值开始。若递增并有最小值，从最小值开始。若没有从 START WITH 指定的值开始。默认是 NOCYCLE。
CACHE 用来指定先预取 p 个数据在序列中，以提高序列值的生成效率，默认是 20

CREATE SEQUENCE seq_emp_id
START WITH 1
INCREMENT BY 1

序列支持两个伪列：
NEXTVAL:获取序列下一个数字，序列会根据序列最后生成的数字加上步进来得到。NEXTVAL 会导致序列发生步进，序列是不能回退的。

CURRVAL： 获取序列最后一次生成的数字，需要注意的是，新创建的序列必须在使用 NEXTVAL 生成一个数字之后才可以使用 CURRVAL.

select seq_emp_id.NEXTVAL from dual;
select seq_emp_id.CURRVAL from dual;

删除序列：

DROP SEQUENCE emp_seq

索引

创建索引的语法：
like 模糊查询是不使用索引的

在 EMP 表的 ENAME 列上建立索引
CREATE INDEX idx_emp_ename ON emp(ename)
复合索引
CREATE INDEX idx_emp_job_sal ON emp(job,sal)
给函数添加索引
CREATE INDEX emp_ename_upper_idx ON emp(UPPER(ename))

重建索引：

ALTER INDEX idx_emp_ename REBUILD

删除索引：

DROP INDEX idx_emp_ename

• 不要在小表上建立索引
• 经常出现在 WHERE 子句中的列创建索引
• 经常出现在排序，去重的后面可以添加索引
• 关联查询的时候一般可以添加索引
• 不要在经常做 DML 操作的表上创建索引