Oracle 基础

2020 年 11 月 28 日
筆記
Oracle

SQL 分类

数据定义语言（DDL）：用于创建、修改和删除数据库对象。

DDL的核心指令是：

CREATE：创建表或其他对象。
ALTER：修改表或其他对象。
DROP：删除表或其他对象。
TRUNCATE：删除表数据，保留表结构

-- Create table
create table STUDENT
(
  sid   VARCHAR2(10),
  sname VARCHAR2(10),
  sage  VARCHAR2(255),
  ssex  VARCHAR2(10)
)
tablespace DEMO
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table STUDENT
  is '学生表';
-- Add comments to the columns 
comment on column STUDENT.sid
  is '学生编号';
comment on column STUDENT.sname
  is '学生姓名';
comment on column STUDENT.sage
  is '出生年月';
comment on column STUDENT.ssex
  is '学生性别';

修改表名：

 RENAME 原始表名 TO 新表名;

增加列：

 ALTER TABLE STUDENT ADD(AAC058 VARCHAR2(10));

删除列：

ALTER TABLE STUDENT DROP(AAC058);

删除所有表数据，表结构保留：

TRUNCATE TABLE STUDENT;

删除表：

DROP TABLE STUDENT;

数据操纵语言（DML）：用于改变数据表中的数据，和事务相关。

DML的核心指令（即增删改查）是：

INSERT：将数据插入数据表中

INSERT INTO STUDENT
VALUES ('09', '张三', '1994-01-04', '男');

UPDATE：更新数据表中的数据

UPDATE STUDENT
SET ssex='女'
WHERE sid = '01';

DELETE：删除表中的数据

DELETE FROM STUDENT
WHERE sid = '01';

清空表中的数据：

TRUNCATE TABLE STUDENT;

SELECT：查询表中的数据

SELECT * FROM STUDENT

-- 返回前 5 行
SELECT * FROM STUDENT LIMIT 5;
SELECT * FROM STUDENT LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM STUDENT LIMIT 2, 3;

SELECT DISTINCT
SSEX FROM STUDENT;

事务控制语言（TCL）：用于维护数据一致性

TCL的核心指令是：

COMMIT：提交
ROLLBACK：回滚
SAVEPOINT：保存点

数据控制语言（DCL）：用于执行权限的授予与回收工作

DCL：的核心指令是

GRANT：授权
REVOKE：回收用户或角色权限

SQL 子查询

子查询可以嵌套在SELECT，INSERT，UPDATE，DELETE*语句内或另一个子查询中
子查询通常会在另一个SELECT语句的WHERE子句中添加
可以使用比较运算符>，<，=。也可以是多行运算符IN，ANY，ALL
子查询必须被圆括号（）括起来
内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询

子查询的子查询

SELECT CUST_NAME, CUST_CONTACT
  FROM CUSTOMERS
 WHERE CUST_ID IN
       (SELECT CUST_ID
          FROM ORDERS
         WHERE ORDER_NUM IN
               (SELECT ORDER_NUM FROM ORDERITEMS WHERE PROD_ID = 'RGAN01'));

WHERE

WHERE 子句用于过滤记录
WHERE 后跟一个返回true或false的条件
WHERE 可以与SELECT、UPDATE、DELETE一起使用
可以在 WHERE 子句中使用操作符

运算符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定对某个列的多个可能值

IN ，NOT IN

-- IN：范围内，NOT IN：不在这个范围内
SELECT *
FROM SC
WHERE SCORE IN ('80', '90');

BETWEEN AND

SELECT *
FROM SC
WHERE score BETWEEN 60 AND 80;

AND、OR、NOT

AND、OR、NOT 是用于对过滤条件的逻辑处理指令
AND 优先级高于 OR，为了明确处理顺序，可以使用（）
AND 操作符表示左右条件都要满足
OR 操作符表示左右条件满足任意一个
NOR 操作符用于否定一个条件

ANY，ALL

需要判断内容>、>=、<、<= 列表中的多个值，需要结合 ANY 或 ALL 来使用。

ANY（LIST）：大于列表中其中之一即可，即大于最小的

ALL（LIST）：大于列表中所有，即大于最大的

SELECT * FROM SC WHERE SCORE > ANY(SELECT SCORE FROM SC WHERE CID='01')

ORDER BY：按照指定的字段的值进行升序或者降序进行排序。

ASC：升序，默认升序
DESC：降序

-- 学生成绩降序
select t.*, t.rowid from SC t ORDER BY score DESC;

假如排序的字段中有 NULL 值， NULL 会被视为最大值。当多个字段进行排序时，每个字段可以分别指定升降序，并且排序顺序按照第一个字段优先排序，以此类推。

聚合函数（分组函数、组函数）：聚合函数是忽略 NULL 值的

MAX 和 MIN：获取列或表达式的最大、最小值，可以统计任何数据类型。
```
-- 查询最高成绩和最低成绩
SELECT MAX(score),MIN(score) FROM SC;
```
AVG 和 SUM：统计列或表达式的平均值和和值，只能操作数据类型，并且忽略 NULL 值。
```
-- 学生的平均成绩和总成绩
SELECT AVG(score) avg_score,SUM(score) sum_score from SC;
```
COUNT：统计表中的记录条数，忽略 NULL 值。
```
SELECT COUNT(*) from SC;
```

分组

GROUP BY 子句：是为聚合函数服务的，可以统计数据时细化分组，允许将某个字段值一样的记录看成一组，然后进行统计。
- group by 子句将记录分组汇总行中
- group by 为每个组返回一个记录
- group by 通常还涉及聚合：count、max、sum、avg等
- group by 可以按一列或多列进行分组
- group by 按分组字段进行排序后，order by 可以汇总字段来进行排序
```
SELECT cid FROM sc  GROUP BY cid;
```
HAVING 字句：用于添加过滤条件，是在统计结果之后进行过滤，不能独立出现，必须跟在 group by 子句后面
- having 用于对汇总的group by 结果进行过滤
- having 要求存在一个 group by 子句
- where 和 having 可以在相同的查询中
```
SELECT score FROM sc  GROUP BY score HAVING(score) > 80;
```

查询语句的执行顺序

from 子句：从后往前、从左到右，数据量少的表尽量放在后面
where 子句：自伤而下、从右到左。能过滤掉最大数量记录的条件写在where的最右
group by：从左往右分组，最好在group by 前使用where将不需要的记录在group by 之前过滤掉
having 子句：消耗资源，尽量避免使用。会检索出所有记录之后才对结果集过滤，需要排序等操作
select 子句：少用 * 号，尽量取字段名。
order by 子句：从左到右排序，消耗资源

SQL 关联查询

内连接（INNER JOIN）：也称为等值连接，返回两张表都满足条件的部分

注释：inner join 等于 join

innerjoin

SELECT * FROM student a INNER JOIN sc b ON  a.sid=b.sid

外连接（out join）:不仅返回满足连接条件的记录，还将返回不满足条件的记录。

左外连接：取左边的表全部，右边的表按条件，符合的显示，不符合则显示 null

leftjoin
右外连接：取右边的表全部，左边的表按条件，符合的显示，不符合则显示 null

rightjoin
全外连接

注释：left outer join 与left join 等价，一般写成 left join

right outer join 与right join 等价，一般写成right join

自连接

SELECT * FROM student a , student b WHERE  a.sname = b.sname

自然连接

SELECT * FROM student a  NATURAL JOIN sc

SQL 函数

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

RANK：生成不连续不唯一的数字排序字段相同的记录，得到的数字一样后续内容会根据重复的行数自动跳号

DENSE_RANK()：生成连续但不唯一的数字

UNION

union 运算符将两个或更多查询的结果组合起来，并生成一个结果集
union 规则
- 所有查询的列数和列顺序必须相同
- 每个查询中涉及表的列的数据类型必须相同或兼容
- 通常返回的列名取第一个查询
默认会去除相同行，如果需要保留相同行，使用 union all
只能包含一个 order by 子句，并且必须位于语句的最后

SELECT * FROM student a 
UNION
SELECT * FROM student_bak

UNION 和 UNION ALL：用来获取两个或两个以上结果集的并集（结果集的列必须一一对应）

UNION 操作符会自动去掉合并后的重复记录
UNION ALL 返回两个结果集中的所有行，包括重复的行
UNION 操作符对查询结果排序，UNION ALL 不排序

INTERSECT：获得两个结果集的交集，只有同时存在于两个结果集中的数据才会被显示输出。INTERSECT操作符后的结果集会以第一列的数据做升序排序

MINUS：获得两个结果集的差集，只有在第一个结果集中存在，在第二个结果集中不存在的数据才能被显示出来，就相当于结果集一减去结果集二的结果

SQL 视图

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示，其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

作用

简化复杂的 SQL 操作，比如复杂的联结；

只使用实际表的一部分数据；

通过只给用户访问视图的权限，保证数据的安全性；

更改数据格式和表示。

根据视图所对应的子查询种类分为几种类型：

SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视图，此时视图是基表的子集；
SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY子句，叫做复杂视图；
SELECT语句是基于多个表的，叫做连接视图。

创建视图

CREATE VIEW v_student
AS
SELECT SID,sname,sage,ssex
FROM student WHERE SID=01

修改视图：由于视图自身没有结构，完全取决于对应的查询语句，所以修改视图就是替换对应的查询语句。

CREATE OR REPLACE VIEW v_student
AS
SELECT SID,sname,sage,ssex
FROM student WHERE SID=02;

删除视图

DROP VIEW v_student

对视图进行DML操作就是对视图数据来源的基表进行操作。只能对简单试图进行DML操作，复杂视图不允许DML操作，即视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作。

-- 直接对视图进行插入操作，查询视图是查询不到插入的数据，查基表才能看到插入的数据
insert into v_student values(10,'萨达','1993-03-04','女');

创建具有 CHECK OPTION 约束的视图

可以为视图添加CHECK OPTION选项，这样对视图进行DML操作时，视图会检查操作完毕后对该记录是否可见，可见不允许操作。

CREATE OR REPLACE VIEW v_student
AS
SELECT SID,sname,sage,ssex
FROM student 
WHERE SID=02
WITH CHECK OPTION;

-- 创建完视图后再插入数据
insert into v_student values(10,'萨达','1993-03-04','女');
-- 结果是报错的：视图WITH CHECK OPTION where 子句违规

创建具有 READ ONLY 约束的视图

当视图被设置为READ ONLY后，不允许对该视图进行DML操作，其为只读的。

CREATE OR REPLACE VIEW v_student
AS
SELECT SID,sname,sage,ssex
FROM student 
WHERE SID=02
WITH READ ONLY;

-- 创建完视图后再插入数据
insert into v_student values(10,'萨达','1993-03-04','女');
-- 结果：无法对只读视图执行 DML 操作

SQL 序列

序列是一个数据库对象作用是根据指定的规则生成一组数字，每次返回一个数字。常用于为表中的主键提供值。

主键：通常每张表的第一个字段就是主键，主键字段的值要求在整张表中不能为空，且值不能重复。目的是用于唯一标识每一个记录。

-- 创建起始数据是100，步进是1的序列
CREATE SEQUENCE student_seq
START WITH 100
INCREMENT BY 1;

序列的两个伪列

NEXTVAL：使序列生成一个数字，第一次使用时，返回的是START WITH指定的值。需要注意，序列不可逆，一旦获取下一个数字后，就不能得到上一个数字了。
CURRVAL：获取序列最后一次生成的数字，可以调用多次，不会造成序列生成下一个数字。CURRVAL必须要在序列创建完毕后，至少调用过一侧NEXTVAL输出生成一个数字后才可以使用。

SQL 索引

索引也是数据库对象，用来提高检索效率，排序效率有效的使用可以带来很好的效果。数据库管理系统自行维护索引算法。我们只需要指定何时为某表的某字段添加即可。

注意：一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

唯一索引：唯一索引表明此索引的每一个索引值只对应唯一的数据记录。

创建索引

create index idx_student_id on student(SID);

创建唯一索引

CREATE UNIQUE INDEX idx_student_id
ON student (id);

复合索引

CREATE INDEX idx_student_id
ON student (id,sname);

删除索引

DROP INDEX idx_student_id;

SQL 约束

如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
约束类型
- 非空约束(Not Null)，简称NN，指示某列不能存储 NULL 值。
- 唯一性约束(Unique)，简称UK，保证某列的每行必须有唯一的值。
- 主键约束(Primary Key)，简称PK， NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- 外键约束(Foreign Key)，简称FK，保证一个表中的数据匹配另一个表中的值的参照完整性。
- 检查约束(Check)，简称CK，保证列中的值符合指定的条件。

SQL 事务

事务在数据库中是工作的逻辑单元，单个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制，可以确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。

事务的特性（ACID）

一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性（Durability），这就是ACID特性。

Atomicity （原子性）：一个事务里面所有包含的SQL语句都是一个整体，是不可分割的，要么不做，要么都做。
Consistency （一致性）：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
Isolation （隔离性）：多个并发事务可以独立运行，而不能相互干扰，一个事务修改数据未提交前，其他事务看不到它所做的更改。
Durability （持久性）：当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。

事务的相关语句

   -- 设置事物属性
   Set transaction 
   -- 设置事物的约束模式：在事务中修改数据时,数据库中的约束立即应用于数据,还是将约束推迟到当前事务结束后应用。
   Set constrains  
   -- 在事务中建立一个存储的点.当事务处理发生异常而回滚事务时,可指定事务回滚到某存储点.然后从该存储点重新执行。
   Savepoint 
   -- 删除存储点
   Release  savepoint 
   -- 取消对数据库所作的任何操作
   Rollback 回滚事务 
   -- 对数据库的操作做持久的保存。
   Commit 提交事务

权限控制

创建用户

CREATE USER myuser IDENTIFIED BY 'mypassword';

修改用户名

UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;

删除用户

DROP USER myuser;

查看权限

SHOW GRANTS FOR myuser;

授权

GRANT SELECT, INSERT ON *.* TO myuser;

删除权限

REVOKE SELECT, INSERT ON *.* FROM myuser;

更改密码

SET PASSWORD FOR myuser = 'mypass';

存储过程

存储过程可以看成是对一系列sql操作的批处理

好处

代码封装，保证一定的安全性

代码复用

因为是预先编译，所以具有很高的性能

创建过程

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

游标

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

声明游标，这个过程没有实际检索出数据；
打开游标；
取出数据；
关闭游标；

触发器（trigger）

触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用

触发器类型

DML 触发器

对数据表进行DML语句操作（如insert、update、delete）时所触发的触发器
- 语句级触发器或行级触发器：行级触发器会对数据库表中的受影响的每一行触发一次触发器代码，语句级触发器则只触发一次，与语句所影响到的行数无关
- before触发器或after触发器：before触发器在触发事件发生之前执行触发器代码，after触发器则在触发事件发生之后执行
替代触发器（instead of 触发器）

对视图进行操作时定义的触发器，替代触发器只能定义在视图上
系统事件触发器

对数据库实例或某个用户模式进行操作时定义的触发器
- 数据库系统触发器
- 用户触发器

触发器语法

CREATE [OR REPLACE] TIGGER 触发器名 触发时间 触发事件
ON 表名
[FOR EACH ROW]
BEGIN
     pl/sql 语句
END

触发器名：触发器对象的名称。

由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。

触发时间：指明触发器何时执行，该值可取：

before—表示在数据库动作之前触发器执行；

after—表示在数据库动作之后出发器执行。

触发事件：指明哪些数据库动作会触发此触发器：

insert：数据库插入会触发此触发器；

update：数据库修改会触发此触发器；

delete：数据库删除会触发此触发器。

表名：数据库触发器所在的表。

for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

Tags: Oracle

Oracle 基础

SQL 分类

SQL 子查询

SQL 关联查询

SQL 函数

SQL 视图

SQL 序列

SQL 索引

SQL 约束

SQL 事务

权限控制

存储过程

游标

触发器（trigger）

VirMach 便宜 VPS

QNews

Oracle 基础

SQL 分类

SQL 子查询

SQL 关联查询

SQL 函数

SQL 视图

SQL 序列

SQL 索引

SQL 约束

SQL 事务

权限控制

存储过程

游标

触发器（trigger）

分享此文：

Related Posts

Vim基础用法，最常用、最实用的命令介绍（保姆级教程）

springboot 集成 docsify 实现随身文档

卢伟冰一句话米粉沸腾 雷军：欢迎大家监督！

天价尼尔人像

VirMach 便宜 VPS

QNews

熱門搜尋

卢伟冰一句话米粉沸腾雷军：欢迎大家监督！