Oracle

进入 Oracle

cmd 运行命令 sqlplus 或 开始菜单直接打开 SQL Plus

启动和开启

默认情况下，Oracle 数据库是启动状态的，可以通过客户端 SqlPlus使用 sys 用户进行登录，通过命令行关闭 Oracle 数据库

# 已经登录到 SQLPlus 中
$ shutdown immediate
# 通过该命令可以关闭 Oracle 数据库
$ startup open
# 通过该命令可以开启 Oracle 数据库和实例

用户

Oracle 用户的概念对于 Oracle 数据库至关重要，在现实环境中一个服务器一般只安装一个 Oracle 实例，一个 Oracle 用户代表一个用户群，通过该用户登录数据库，进行数据库对象创建查询。

每一个用户对应着该用户下的 N 多对象，因此在实际项目开发过程中，不同的项目组使用不同的 Oracle 用户进行开发，不相互干扰。

即，一个 Oracle 用户相当于一个业务模块，这些用户群构成一个完整的业务系统，不同模块间的关联通过 Oracle 用户的权限控制，从而获取其它业务模块的数据和操作其它业务模块的某些对象。

默认用户和密码

| 用户名 | 密码 | 登录身份 | 说明 |
| ——————— | —————– | ——————————————- |
| sys | change_on_install | SYSDBA 或 SYSOPER | 不能以 NORMAL 登录，可作为默认的系统管理员 |
| system | manager | SYSDBA 或 NORMAL | 不能以 SYSOPER 登录，可作为默认的系统管理员 |
| sysman | oem_temp | | sysman 为 oms 的用户名 |
| scott | tiger | NORMAL | 普通用户 |
| aqadm | aqadm | SYSDBA 或 NORMAL | 高级队列管理员 |
| Dbsnmp | dbsnmp | SYSDBA 或 NORMAL | 复制管理员sysman 为 oms 的用户名 |
| scott | tiger | NORMAL | 普通用户 |
| aqadm | aqadm | SYSDBA 或 NORMAL | 高级队列管理员 |
| Dbsnmp | dbsnmp | SYSDBA 或 NORMAL | 复制管理员 |

创建用户：

-- 创建用户
create user UserName -- UserName 为可自定义的用户名
identified by "123456" -- 123456 为可自定义的用户密码
default tablespace users -- users 为指定的表空间名
temporary tablespace temp -- 临时表空间名
profile default -- 数据文件 （默认）
account unlock; -- 账户是否解锁（lock：锁定，unlock：解锁）

通过以上语句，可以创建一个名为 UserName 的用户，但是该用户现在不能登录数据库，因为它没有登录数据库权限，它最少需要一个 create session 系统权限才能登录

用户权限

Oracle 数据库用户权限分为两种

• 系统权限
• 比如： create session 可以对数据库进行连接权限，create table, create view 等具有创建数据库对象权限
• 对象权限
• 比如：对表中数据进行增删改查操作，拥有数据库对象权限的用户可以对所拥有的对象进行相应操作

数据库角色

Oracle 数据库角色是若干系统权限的集合，给 Oracle 用户进行授权数据库角色，就是等于赋予该用户若干数据库系统权限

常用的数据库角色如下：

• CONNECT 角色
• 是 Oracle 用户的基本角色，connect 权限代表用户可以和 Oracle 服务器进行连接，建立 session （会话）
• RESOURCE 角色
• 是开发过程中常用的角色，该权限给用户提供了可以创建自己的对象，包括: 表、视图、序列、过程、触发器、索引、包、类型等
• DBA 角色
• 是管理数据库管理员该有的权限，拥有系统所有权限，和给其它用户进行授权的权限，system 用户就具有 DBA 权限

因此，在实际开发过程中，可以根据需求，把某个角色或系统权限赋予给某个用户

授权

-- 系统权限只能通过 DBA 用户授权
-- 对象权限由拥有该对象权限的对象授权（不一定是本身对象）
-- 用户不能对自己授权

-- 授权
-- grant 对象权限 on 对象 to 对象
-- 赋予 STUDENT 来自 JSQUESER 的 select, insert, update, delete 权限
grant select, insert, update, delete on JSQUSER to STUDENT;
-- grant 系统权限 to 用户
-- 赋予 选择所有表的权限给 STUDENT
grant select any table to STUDENT;
-- grant 角色 to 用户
-- 赋予 connect 角色给 STUDENT
grant connect to STUDENT;
-- 赋予 resource 角色给 STUDENT
grant resource to STUDENT;
-- 增加 with admin option / width grant option 则 获得的权限可以授权给其它用户
-- 使用 with admin option 获得的权限被收回时，收回前授予其它用户的该权限不会失效
-- 使用 with grant option 获得的权限被收回时，收回前授予其它用户的该权限会失效

取消授权

-- 取消授权
-- revoke 对象权限 on 对象 from 用户
-- 取消 STUDENT 的来自 JSQUSER 的 select, insert, update, delete 权限
revoke select, insert, update, delete on JSQUSER from STUDNET;
-- revoke 系统权限 from 用户
-- 取消 STUDENT 的选择所有表的权限
revoke select any table from STUDENT;
-- revoke 角色 from 用户
-- 取消 STUDENT 的 resource 权限
revoke resource from STUDENT

其它操作

-- 其它用户操作
-- 修改用户信息
alter user UserName -- 修改用户名为 UserName 的用户信息
identified by 123456 -- 修改用户密码为 123456
account lock -- 修改用户状态为锁定

查询用户具有那些权限

select * from dba_role_privs;
select * from dba_sys_privs;
select * from role_sys_privs;

删除用户

-- 删除用户
-- 加上 cascade 则会连同该用户创建的东西全部删除
drop user UserName cascade;

常用字段类型

| 数据类型 | 类型解释 |
| —————- | ———————————————————— |
| varchar2(length) | 字符串类型 存储可变长度的字符串，length为字符串最大长度，默认 1，不超过4000 |
| char(length) | 字符串类型 存储固定长度的字符串，length为固定长度，默认1，不超过2000 |
| number(a,b) | 数值类型，可存整数浮点数，a代表数值包含小数位和小数点在内的最大位数，b代表小数点的位数 |
| date | 时间类型，存储日期时间（年月日时分秒） |
| timestamp | 时间类型，在date基础上包含时区 |
| clob | 大字段类型，存储大文本，如非结构化的txt文本，字段长度大于4000的字符串 |
| blob | 二进制类型，存储二进制对象，如视频图片转换的二进制对象 |

dual

是一个单行单列的虚拟的表，任何用户均可读取，常用于没有目标的语句块中

表

通过 `create table` 命令可以建立表

创建表

-- 创建表
-- 在用户 STUDENT 下 建立表 stuinfo
create table STUDENT.stuinfo (
    stuid       varchar2(11) not null,
    stuname     varchar2(50) not null,
    sex         char(1) not null,
    age         number(2) not null
    classno     varchar2(7) not null,
    stuaddress  varchar2(100) default '地址未录入',
    grade       char(4) not null,
    enroldate   date,
    idnumber    varchar2(18) default '身份证未采集' not null
)
-- 表 stuinfo 存储的表空间是 USERS
-- storage 表示存储参数
-- 区段一次扩展 64k
-- 最小区段数为 1
-- 最大区段数不限制
tablespace USERS
    storage
    (
        initial 64k
        minextents 1
        maxextents unlimited
    );
-- 给表名注释
comment on table STUDENT.stuinfo is '学生信息表';
-- 给表字段注释
comment on column STUDENT.stuinfo.stuid is '学号';
comment on column STUDENT.stuinfo.stuname is '学生姓名';

添加约束

-- 创建主键、唯一、外键约束
-- 将 stuid 作为主键
-- 主键字段数据必须是唯一性的
-- 为表 STUDENT.stuinfo 添加名为 pk_stuinfo_stuid 的主键约束
alter table STUDENT.stuinfo 
    add constraint pk_stuinfo_stuid primary key(stuid)

-- 创建检查约束
-- 为表 STUDENT.stuinfo 添加名为 ch_stuinfo_age 的检查约束
alter table STUDENT.stuinfo
    add constraint ch_stuinfo_age
    check (age > 0 and age <= 50); -- 学生年龄在0 - 50 之间    
alter table STUDENT.stuinfo
    add constraint ch_stuinfo_sex
    check (sex = '1' or sex = '2');
alter table STUDENT.stuinfo
    add constraint ch_stuinfo_grade
    check (grade >= '1900' and grade <= '2999');

查询所有表

-- 查询所有来自 UserName 用户名下的数据表
-- 不加 where 则查询所有用户的表
-- 用户名需要大写
select * from all_tables where owner = UserName;

-- 查询指定用户名下某个实例数据库的所有表
select * from all_tables where owner = UserName and table_name = TABLE_NAME;

-- 模糊查询表名称
select * from all_tables where owner = UserName and table_name = '%S%';

-- 查询当前登录用户的所有表
select * from user_tables;
select * from tabs;

-- 查询所有用户的表、视图
select * from all_tab_comments;

-- 查询当前用户的表、视图
select * from user_tab_comments;

-- 查询所有用户的列名及注释
select * from all_col_comments;

-- 查询当前用户的列名及注释
select * from user_col_comments;

-- 查询所有用户的表的列名等信息
select * from all_tab_columns;

-- 查询当前用户的表的列名等信息
select * from user_tab_columns;

-- 可在后面增加 where owner = UserName 查询指定用户 UserName

表空间

oracle 数据库被划分成称作表空间的逻辑区域，从而形成 oracle 数据库的逻辑结构

一个 oracle 数据库能够拥有一个及一个以上的表空间

一个表空间可以对应一个及一个以上的物理的数据库文件

一个数据库只能和一个表空间相联系

表空间是 oracle 数据库恢复的最小单位，容纳着许多数据库实体，如表、视图、索引、聚簇、回退段和临时段

表空间类型

• 永久性表空间：一般存放表、视图、过程、索引等数据
• 临时性表空间：只用于保存系统中短期活动数据
• 撤销表空间：用于帮助回退未提交的事务数据

表空间作用

可以帮助 dba 完成以下工作

• 决定数据库实体的空间分配
• 设置数据库用户的空间份额
• 控制数据库部分数据的可用性
• 分布数据于不同的设备之间以改变性能
• 备份和恢复数据

用户创建数据库实体时，应赋予其相应权利，对于一个用户来说，操作一个数据库数据，应有以下权力：

• 被授予关于一个或多个的表空间中的 resource 特权
• 被指定缺省表空间
• 被分配指定表空间的存储空间使用份额
• 被指定缺省临时表空间，建立不同的表空间，设置最大的存储容量

创建表空间

create tablespace TABLESPACE_NAME -- TABLESPACE_NAME 为可自定义的表空间名称
datafile PATH -- PATH 为指定的数据文件路径
size SIZE_NUMBER -- SIZE_NUMBER 为指定的表空间初始大小
autoextend on -- 是否自动扩展表空间 on 或 off
next SIZE_NUMBER -- 文件满了后自动扩展 SIZE_NUMBER 大小
maxsize SIZE_NUMBER -- 文件最大大小为 SIZE_NUMBER 或 unlimited 不限大小

查询表空间

select file_name, tablespace_name, bytes, autoextensible from dba_data_filea where tablespace_name = '';

修改表空间

alter tablespace TABLESPACE_NAME -- TABLESPACE_NAME 为需要修改的表空间名称
add datafile PATH -- 添加数据文件 PATH
size SIZE_NAME -- 初始为 SIZE_NAME 大小
autoextend on -- 是否自动扩展表空间 on 或 off
next SIZE_NUMBER -- 文件满了后自动扩展 SIZE_NUMBER 大小
maxsize SIZE_NUMBER -- 文件最大大小为 SIZE_NUMBER 或 unlimited 不限大小

删除表空间

-- 仅删除表空间
drop tablespace TABLESPACE_NAME;
-- 含数据文件
drop tablespace TABLESPACE_NAME including contents and datafiles;

查询

-- * 所有
-- COL_NAME 为指定的列名
-- Expression 为选取数据时的表达式 如: 聚合函数等
-- TABLE_NAME 为 选择的数据存放的表名称
-- Condition 为选择数据时指定的条件 如: 此数据大于 0 小于 50
-- 选择 数据（* | COL_NAME | Expression） 来自 表（TABLE_NAME）选择条件为 Condition 排序依据为 COL_NAME
select * | COL_NAME | Expression from TABLE_NAME where Condition order by COL_NAME

-- 与mysql类似 可以给表和列添加别名
-- order by 默认升序 desc 降序

备份查询数据

create table TABLE_NAME as select ...

插入

-- 数据操作等只有提交后才能持久化到数据库
-- 插入数据 VALUE... 到 TABLE_NAME 的 列 COL_NAME... 中
-- 列名可省略 不填列名默认所有列
-- 默认列顺序为创建表时的列顺序
insert into TABLE_NAME (COL_NAME...) values(VALUE...)

插入一个 select 结果集

insert into TABLE_NAME select...

更新

-- 只有提交后才能持久化到数据库
-- 更新来自表 TABLE_NAME 的列 COL_NAME 数据为 VALUE，在条件 Condition 满足时更新
update TABLE_NAME set COL_NAME=VALUE... where Condition

利用另一张表更新此表

update TABLE_NAME_1 set 
    COL_NAME = ( select COL_NAME from TABLE_NAME_2 where Condition )
    where exists (
        select 1 from TABLE_NAME_2 where
        TABLE_NAME_1.COL_NAME = TABLE_NAME_2.COL_NAME);

删除

-- 只有提交后才能持久化到数据库
-- 删除来自表 TABLE_NAME 的 满足 Condition 条件的数据
-- 不加 where 时 删除所有
delete from TABLE_NAME where Condition

-- 是直接删除表的命令
truncate table TABLE_NAME

-- 二者区别
-- truncate 是 DDL 命令 执行完就提交 删除的内容无法恢复
-- delete 是 DML 命令 需提交生效 可通过日志文件恢复删除内容
-- 表中数据过大 则 truncate 比 delete 快
-- trancate 删除将重新设置表索引的初始大小 delete 不能
-- delete 能够触发表上相关 delete 触发器 truncate 不会触发
-- delete 原理是一次一条的删除并将删除操作记录到日志中方便进行回滚
-- truncate 则一次性进行数据页的删除 执行速度快 但无法回滚

关键字

any

表示任意值

--年龄只要大于当中子查询的最小值26岁即可
select * from stuinfo t where t.age>any(26,27,28);

all

表示所有值

--年龄必须大于子查询当中的最大值28岁才可以
select * from stuinfo t where t.age>all(26,27,28);

运算符

算术运算符

| 符号 | 解释 |
| —- | —————- |
| + | 加 |
| - | 减 |
| * | 乘 |
| / | 除，结果为浮点数 |

关系运算符

| 符号 | 解释 |
| ——– | ——– |
| = | 等于 |
| > | 大于 |
| < | 小于 |
| <> 或 != | 不等于 |
| >= | 大于等于 |
| <= | 小于等于 |

逻辑运算符

| 符号 | 解释 |
| —- | —- |
| and | 和 |
| or | 或 |
| not | 非 |

字符串连接符

| 符号 | 解释 |
| —- | ——————– |
| \|\| | 通过\|\|连接查询结果 |

-- 例子
select 'a' || 'b' || 'c' || 'c' from ...
-- 结果
'abcd'

去重

distinct

-- 当 distinct 后只有一个列字段时，显示此列字段不重复数据
-- 否则显示多字段组成的不重复数据
select distinct COL_NAME ... from TABLE_NAME

条件

=

在条件查询语句中 = 表示列值等于一个固定值所查询出的结果

-- 获取 TABLE_NAME 表中 列 COL_NAME 值为 VALUE 的数据
select COL_NAME from TABLE_NAME where COL_NAME = VALUE

in

在 where 子句中可以使用 in 操作符查询列值在指定的列表中的查询结果

-- 获取 TABLE_NAME 中的 COL_NAME 条件为 COL_NAME 存在于 另一表 TABLE_NAME 中
select COL_NAME from TABLE_NAME where COL_NAME in (select * from TABLE_NAME)
-- 获取 TABLE_NAME 中的 COL_NAME 条件为 COL_NAME 值在 （1,3,5,7,9）范围中
select COL_NAME from TABLE_NAME where COL_NAME in (1,3,5,7,9)

between … and

在 where 子句中 可以使用 between … and 操作符查询列值包含在指定区间内的查询结果

-- 获取 TABLE_NAME 中的 COL_NAME 条件为 COL_NAME 值在 0 - 100 区间中
select COL_NAME from TABLE_NAME where COL_NAME between 0 and 100

like

查询不清楚时可以使用 like 进行模糊查询

like 有通配符 % 和 _

• %

  表示零个或多个任意字符

• _

  表示任一字符

• \

  转义字符，\% 表示一个 %，\\ 表示一个单独的 \

-- 获取 TABLE_NAME 中 COL_NAME 值包含有 '值:' 的数据
select COL_NAME from TABLE_NAME where COL_NAME like '值:__';
-- 获取 TABLE_NAME 中 COL_NAME 值包含 '省' 的数据
select COL_NAME from TABLE_NAME where COL_NAME like '%省';

集合运算

即，把多个查询结果集进行交集

oracle 的集合运算包括：

• intersect 交集

  返回两个集共同的内容 去重

• union all 并集重复

  返回每个集的所有记录 不去重

• union 并集不重复

  返回每个集的所有记录 去重

• minus 补集

  返回第一个检索出的记录减去第二个检索记录后剩余的记录

使用集合运算时，每个独立查询的列名尽量一致（列名不同，取第一个查询的列名），列的数据类型，个数需要一致

例子表结构 example_1

| a | b | c |
| —– | —– | —– |
| 1 | a | 1a |
| 2 | b | 2b |
| 3 | c | 3c |
| 4 | d | 4d |

例子表结构 example_2

| a | b | c |
| —– | —– | —– |
| 3 | c | 3c |
| 4 | d | 4d |
| 5 | e | 5e |
| 6 | f | 6f |

交集

select * from example_1 intersect select * from example_2;

结果

| a | b | c |
| —- | —- | —- |
| 3 | c | 3c |
| 4 | d | 4d |

并集重复

select * from example_1 union all select * from example_2;

结果

| a | b | c |
| —– | —– | —– |
| 1 | a | 1a |
| 2 | b | 2b |
| 3 | c | 3c |
| 3 | c | 3c |
| 4 | d | 4d |
| 4 | d | 4d |
| 5 | e | 5e |
| 6 | f | 6f |

并集不重复

select * from example_1 union select * from example_2;

结果

| a | b | c |
| —– | —– | —– |
| 1 | a | 1a |
| 2 | b | 2b |
| 3 | c | 3c |
| 4 | d | 4d |
| 5 | e | 5e |
| 6 | f | 6f |

补集

select * from example_1 minus select * from example_2;

| a | b | c |
| —– | —– | —– |
| 1 | a | 1a |
| 2 | b | 2b |

连接查询

oracle 连接查询 有两种类型:

• 内连接 （inner join)

• 外连接 (outer join)

  其中外连接又分为三种:

  • 左外连接 (left outer join)
  • 右外连接 (right outer join)
  • 全外连接 (full outer join)

外连接可以用 + 表示

内连接

两张表通过某个字段进行内连接，查询结果是通过该字段以关系运算符匹配的数据

其中可以包括：

• 等值连接

  在连接条件中使用 = 运算符比较连接列的值，查询结果列出被连接表中的所有列

• 不等连接

  在连接中使用 = 运算符以外的其它比较运算符（包括：>、>=、<=、!>、!<、<>）比较连接列的值

select * from example_1 a, example_2 b where a.a = b.a;

外连接

查询结果包含符合连接条件的行，同时包含左表（左外连接，左连接）或右表（右外连接，右连接）或两个边接表（全外连接）中的所有数据行

• left join === left outer join 返回左表中的所有记录和右表中连接字段相同的记录
• right join === right outer join 返回右表中的所有记录和左表中连接字段相同的记录
• full join === full outer join 查询结果等于左外连接和右外连接的和

-- 左
select a.*, b.id from TAB a left join TAB b on a.id = b.id;
-- 左外
select a.*, b.id from TAB a, TAB b where a.id = b.id(+);
-- 右
select a.*, b.id from TAB a right join TAB b on a.id = b.id;
-- 右外
select a.*, b.id from TAB a, TAB b where a.id(+) = b.id;
-- 全外
select a.*, b.id from TAB a full join TAB b on a.id = b.id;

伪列

在存储表或查询表过程中，表会有一些附加列，伪列就像表中字段，但是表中不储存，伪列只能查询，不能更改，oracle 的伪列有 rowid，rownum

rowid

oracle 表的每一行在数据文件中都有一个物理地址，rowid伪列返回的就是该行物理地址，可以快速定位表中某行并唯一标识表中某行，通过 select 查询出的 rowid，返回的就是该行物理地址

select rowid from TABLE_NAME; -- TABLE_NAME 为可自定义的表名称

rownum

表示的是查询结果集的顺序，rownum 为每个查询结果集的行标识一个行号，第一行为1，依次顺序递增

rowid 为 插入记录时生成，rownum 是查询时生成，row 标识的是行的物理地址，rownum 标识的是查询结果集的行的次序

select rownum fom TABLE_NAME; -- TABLE_NAME 为可自定义的表名称

函数

oracle 主要有两种函数

• 单行函数，对每个函数应用在表中的记录时，只能输入一行的列值作为参数，会返回一个结果，常用单行函数有以下几种：
• 字符串函数，对字符串进行操作
• 数值函数，对数值进行计算或操作，返回一个数字
• 转换函数，将一个类型转换为另一个类型
• 日期函数，对时间日期进行操作的函数
• 聚合函数，可以对多行数据进行操作，并返回一个结果

字符串函数

常用如下：

| 函数 | 说明 | 案例 | 结果 |
| ———————— | ———————————————————— | ———————————————————— | ————– |
| ASCII(X) | 求字符X的ASCII码 | select ASCII('A') FROM DUAL; | 65 |
| CHR(X) | 求ASCII码对应的字符 | select CHR(65) FROM DUAL; | 'A' |
| LENGTH(X) | 求字符串X的长度 | select LENGTH('ORACLE技术圈')from DUAL; | 9 |
| CONCATA(X,Y) | 返回连接两个字符串X和Y的结果 | select CONCAT('ORACLE','技术圈') from DUAL; | ORACLE技术圈 |
| INSTR(X,Y[,START]) | 查找字符串X中字符串Y的位置，可以指定从Start位置开始搜索，不填默认从头开始 | SELECT INSTR('ORACLE技术圈','技术') FROM DUAL; | 7 |
| LOWER(X) | 把字符串X中大写字母转换为小写 | SELECT LOWER('ORACLE技术圈') FROM DUAL; | oracle技术圈 |
| UPPER(X) | 把字符串X中小写字母转换为大写 | SELECT UPPER('Oracle技术圈') FROM DUAL; | ORACLE技术圈 |
| INITCAP(X) | 把字符串X中所有单词首字母转换为大写，其余小写。 | SELECT INITCAP('ORACLE is good ') FROM DUAL; | Oracle Is Good |
| LTRIM(X[,Y]) | 去掉字符串X左边的Y字符串，Y不填时，默认的是字符串X左边去空格 | SELECT LTRIM('–ORACLE技术圈','-') FROM DUAL; | ORACLE技术圈 |
| RTRIM(X[,Y]) | 去掉字符串X右边的Y字符串，Y不填时，默认的是字符串X右边去空格 | SELECT RTRIM('ORACLE技术圈–','-') FROM DUAL; | ORACLE技术圈 |
| TRIM(X[,Y]) | 去掉字符串X两边的Y字符串，Y不填时，默认的是字符串X左右去空格 | SELECT TRIM('–ORACLE技术圈–','-') FROM DUAL; | ORACLE技术圈 |
| REPLACE(X,old,new） | 查找字符串X中old字符，并利用new字符替换 | SELECT REPLACE('ORACLE技术圈','技术圈','技术交流') FROM DUAL; | ORACLE技术交流 |
| SUBSTR(X,start[,length]) | 截取字符串X，从start位置（其中start是从1开始）开始截取长度为length的字符串，length不填默认为截取到字符串X末尾 | SELECT SUBSTR('ORACLE技术圈',1,6) FROM DUAL; | ORACLE |
| RPAD(X,length[,Y]) | 对字符串X进行右补字符Y使字符串长度达到length长度 | SELECT RPAD('ORACLE',9,'-') from DUAL; | ORACLE— |
| LPAD(X,length[,Y]) | 对字符串X进行左补字符Y使字符串长度达到length长度 | SELECT LPAD('ORACLE',9,'-') from DUAL; | —ORACLE |

日期函数

系统日期、时间函数

-- sysdate 无参数，可得到系统当前时间
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') fro dual;
-- 2000-01-01 00:00:00

-- systimestamp 无参数，可得到系统当前时间，该时间包含时区，精确到毫秒
select systimestamp from dual;

-- 数据库时区函数 无参数 返回数据库时区
select dbtimezone from dual;

-- 给日期加上指定月份
-- add_months(r, n)
-- r 为 指定日期
-- n 为增加月份 负数为减去
select to_char(add_months(to_date('2018-11-12', 'yyyy-mm-dd'),1), 'yyyy-mm-dd') from dual;
-- 2018-11-12

-- 月份最后一天
-- last_day(r) 指定 r 日期的当前月份最后一天的日期
select last_day(sysdate) from dual;

-- 指定日期后一周
-- next_day(r, c)
-- r 指定日期
-- c 对应的星期几
 select next_day(to_date('2018-11-12','yyyy-mm-dd'),'星期四') from dual;

 -- 返回指定日期中特定部分函数
 -- exract (time) 返回指定 time 中的 年月日等部分
 select  extract( year from timestamp '2018-11-12 15:36:01') as year,
        extract( month from timestamp '2018-11-12 15:36:01') as month,        
        extract( day from timestamp '2018-11-12 15:36:01') as day,  
        extract( minute from timestamp '2018-11-12 15:36:01') as minute,
        extract( second from timestamp '2018-11-12 15:36:01') as second
 from dual;

 -- 返回两个日期间的月份数
 -- months_between(r1, r2)
 -- 返回 r1, r2 两个指定日期之间的月份
 -- r1 > r2 时，返回正数， r1 < r2 时发挥负数
 -- r1, r2 是同日不同月，返回整数，否则返回小数
select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-11-12', 'yyyy-mm-dd')) as zs, --整数
       months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-10-11', 'yyyy-mm-dd')) as xs, --小数
       months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
                      to_date('2018-10-12', 'yyyy-mm-dd')) as fs --负数
  from dual;

  -- 日期截取函数
  -- round(r[,f])
  -- 将日期 r 按 f 格式四舍五入
  -- 默认四舍五入到最近一天
  select sysdate, --当前时间
       round(sysdate, 'yyyy') as year, --按年
       round(sysdate, 'mm') as month, --按月
       round(sysdate, 'dd') as day, --按天
       round(sysdate) as mr_day, --默认不填按天
       round(sysdate, 'hh24') as hour --按小时
  from dual;

  -- 日期截取函数
  -- trunc(r[,f])
  -- 将日期 r 按 f 的格式进行截取
  -- 默认截取当前日期
  select sysdate, --当前时间
       trunc(sysdate, 'yyyy') as year, --按年
       trunc(sysdate, 'mm') as month, --按月
       trunc(sysdate, 'dd') as day, --按天
       trunc(sysdate) as mr_day, --默认不填按天
       trunc(sysdate, 'hh24') as hour --按小时
  from dual;

数值函数

常用如下：

| 函数 | 解释 | 案例 | 结果 |
| ———— | ———————————————————— | ———————————————————— | —————- |
| ABS(X) | 求数值X的绝对值 | select abs(-9) from dual; | 9 |
| COS(X) | 求数值X的余弦 | select cos(1) from dual; | 0.54030230586814 |
| ACOS(X) | 求数值X的反余弦 | select acos(1) from dual; | 0 |
| CEIL(X) | 求大于或等于数值X的最小值 | select ceil(7.8) from dual; | 8 |
| FLOOR(X) | 求小于或等于数值X的最大值 | select floor(7.8) from dual; | 7 |
| log(x,y) | 求x为底y的对数 | select log(2,8) from dual; | 3 |
| mod(x,y) | 求x除以y的余数 | select mod(13,4) from dual; | 1 |
| power(x,y) | 求x的y次幂 | select power(2,4) from dual; | 16 |
| sqrt(x) | 求x的平方根 | select sqrt(16) from dual; | 4 |
| round(x[,y]) | 求数值x在y位进行四舍五入。y不填时，默认为y=0;当y>0时，是四舍五入到小数点右边y位。当y<0时，是四舍五入到小数点左边\|y\|位。 | select round(7.816, 2), round(7.816), round(76.816, -1) from dual; | 7.82 / 8 / 80 |
| trunc(x[,y]) | 求数值x在y位进行直接截取y不填时，默认为y=0;当y>0时，是截取到小数点右边y位。当y<0时，是截取到小数点左边\|y\|位。 | select trunc(7.816, 2), trunc(7.816), trunc(76.816, -1) from dual; | 7.81 / 7 / 70 |

转换函数

常用如下：

| 函数 | 解释 | 案例 | 结果 |
| —————————- | ———————————————————— | ———————————————————— | ————————————— |
| asciistr(x) | 把字符串x转换为数据库字符集对应的ASCII值 | select asciistr('Oracle技术圈') from dual; | Oracle\6280\672F\5708 |
| bin_to_num(x1[x2…]) | 把二进制数值转换为对应的十进制数值 | select bin_to_num(1,0,0) from dual; | 4 |
| cast(x as type) | 数据类型转换函数，该函数可以把x转换为对应的type的数据类型，基本上用于数字，字符，时间类型安装数据库规则进行互转， | select cast('123' as number) num,cast(123 as varchar2(3)) as ch,cast(to_date('20181112','yyyymmdd') as varchar2(12)) as time from dual; | 123/'123'/12-11月-18(三列值，用”/“隔开) |
| convert(x,d_chset[,r_chset]) | 字符串在字符集间的转换函数，对字符串x按照原字符集r_chset转换为目标字符集d_chset，当r_chset不填时，默认选择数据库服务器字符集。 | select CONVERT('oracle技术圈','US7ASCII','ZHS16GBK') from dual; | oracle??? |
| to_char(x[,f]) | 把字符串或时间类型x按格式f进行格式化转换为字符串。 | select to_char(123.46,'999.9') from dual; select to_char(sysdate,'yyyy-mm-dd') from dual; | 123.52018-11-13 |
| to_date(x[,f]) | 可以把字符串x按照格式f进行格式化转换为时间类型结果。 | select to_date('2018-11-13','yyyy-mm-dd') from dual; | 2018/11/13 |
| to_number(x[,f]) | 可以把字符串x按照格式f进行格式化转换为数值类型结果。 | select to_number('123.74','999.99') from dual | 123.74 |

其中数值格式 f 参考如下

| 参数 | 示例 | *说明 * |
| ——— | ——— | ———————— |
| 9 | 999 | 指定位置返回数字 |
| . | 99.9 | 指定小数点的位置 |
| ， | 99,9 | 指定位置返回一个逗号 |
| $ | $99.9 | 指定开头返回一个美元符号 |
| EEEE | 9.99EEEE | 指定科学计数法 |

聚合函数

avg 函数

avg([distinct] expr)

可以求列或列组成的表达式 expr 的平均值，返回数值类型，distinct 为可选的是否去掉重复行的参数

count 函数

count(* | [distinct] expr)

用于计算查询结果的条数或行数，必须指定列名或表达式 expr 否则需要使用 *

max / min 函数

max ([distinct] expr)
min ([distinct] expr)

返回指定列或列组成的表达式 expr 中的最大或最小值，常与 where， group by 等结合使用

sum 函数

sum([distinct] expr)

可以对指定列或列组成的表达式 expr 进行求和，若不使用 group by 分组，则默认整表为一个分组

子查询

即 嵌套查询

将 select 查询结果 作为另一个 select、update、delete 的条件，本质就是 where 条件查询的一个条件表达式

子查询根据结果函数的多少，可以分为两类

• 单行子查询 向外部返回的结果为空或返回一行
• 多行子查询 向外部返回的记过为空或一行及一行以上

单行子查询

是利用 where 条件 = 关联 查询结果的，如果当子查询返回多行会报单行子查询不能返回多行的错误

select * from TABLE_NAME a where a.id = (
    select * from TABLE_NAME b where b.id = ''
);

多行子查询

需要利用 in 关键值接收子查询结果，可以使用量化关键字 any，all 和关系运算符 >、>=、=、<、<= 组合使用

select * from TABLE_NAME t where t.id in (
    select b.id from b
);

-- any 和 all 例子
--年龄只要大于当中子查询的最小值26岁即可
select * from stuinfo t where t.age>any(26,27,28);
--年龄必须大于子查询当中的最大值28岁才可以
select * from stuinfo t where t.age>all(26,27,28);

synonym 同义词

通过同义词，可以让当前数据库用户对另一个用户的数据库对象创建别名，并对别名进行操作，等价于直接操作数据库对象

常用于给 表，视图，函数，过程，包等指定别名，可通过 create 创建，alter 修改，drop 删除

按照访问权限分为私有同义词和公有同义词

• 私有同义词 只能当前用户访问 （当前用户具有 ceate synonym 权限）
• 公有同义词 只能由 dba 创建，所有用户均可访问

create [or reolace] [public] sysnonym [当前用户.]synonym_name 
for [其它用户.]object_name
-- create [or replace] 通 create 建表一样，当前用户下同义词对象名存在，就删除原有同义词，以新的代替
-- public 创建的是公有同义词，较少用，一般访问其它用户对象时，需要该用户授权
-- 用户名.object_name 用户对象权限都是自己用户进行管理的，需要其它用户的某个对象操作权限
-- 只能通过对象拥有者授权，或当前用户有 dba 权限，则可使用它操作对象

```sql
create synonym QUERYNAME for USERNAME.OBJECT
-- QUERYNAME 为 自定义的同义词名称
-- USERNAME 为 需要操作的对象的拥有者名称
-- OBJECT 为 需要操作的对象名称

同义词只能通过同义词拥有者的用户或 dba 才能删除

序列

用于生成连续的整数数据对象，常用于业务中的无规则主键

可以是升序或降序

create sequence SEQUENCE_NAME -- SEQUENCE_NAME 为自定义的序列名称
[maxvalue num] [nomaxvalue] -- 是否有最大值 num 为指定最大值 nomaxvalue 表示无最大值
[minvalue num] [nominvalue] -- 是否有最小值 num 为指定最小值 nominvalue 表示无最小值
[start with num] -- 从 num 数字开始生成序列，num 可以是任何非零整数，表示自增自减
[increment by increment] -- 每次增加的数量 increment
[cycle | nocycle] -- 到达最大值后是否重新开始 nocycle 不重新开始
[cache num | nocache] -- 是否预先缓存 num 个序列在内存中 nocache 为不指定
[order | noorder] -- 按请求顺序生成序列 noorder 不按顺序

自增序列

create sequence autoAdd -- autoAdd 是可自定义的序列名称
increment by 1 -- 每次自增 1
start with 1 -- 从该值开始自增自减
maxvalue 1.0E20 -- 最大值
minvalue 1 -- 最小值
cycle
cache 20 
order -- 按请求顺序生成序列 noorder 不按顺序

-- 使用时，如插入
insert into TABLE_NAME values(autoAdd.nextval, '', '');
-- 不适用于 
insert into TABLE_NAME (key1, key2) select key1, key2 from TABLE_NAME;

视图

索引

触发器

create or replace trigger auto_add -- auto_add 为自定义的触发器名称
before insert on TABLE_NAME -- 在表 TABLE_NAME 执行插入时触发
for each row -- 对每一行进行检测是否触发
begin -- 开始触发器
-- 触发器内容 即 触发后执行动作
-- 此为取得序列 autoAdd 的下一个值插入表 TABLE_NAME 中的 userid 字段
select autoAdd.nextval into :new.userid from dual;
end; -- 结束触发器

错误处理

表空间无权限

-- USERNAME 为对表空间无权限的用户名称
-- TABLESPACE_NAME 为无权限的表空间名称
alter user USERNAME quota unlimited on TABLESPACE_NAME;

练习

创建表

create table ink.test (
    id number(9) not null,
    name varchar2(50) default '名称') 
tablespace USERS storage ( initial 64K minextents 1 maxextents unlimited );

添加约束

alter table test add constraint pk_test_id primary key (id);
alter table test add constraint ck_test_name check (name='甲' or name='乙');

查询数据并备份

create table Tmp_test as (select t.id as "唯一id值", t.name as "姓名代号" from test t);
select * from Tmp_test;

插入一个结果集数据

insert into test select id + 1 * 1 as "id", name from test;

根据另一表更新此表

update test set name = (                                       
select "姓名代号" from Tmp_test)                                   
where exists (
    select "唯一id值" as id from Tmp_test where test.id = Tmp_test."唯一id值");