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 值的

  1. MAX 和 MIN：获取列或表达式的最大、最小值，可以统计任何数据类型。

    -- 查询最高成绩和最低成绩
SELECT MAX(score),MIN(score) FROM SC;

  2. AVG 和 SUM：统计列或表达式的平均值和和值，只能操作数据类型，并且忽略 NULL 值。

    -- 学生的平均成绩和总成绩
SELECT AVG(score) avg_score,SUM(score) sum_score from SC;

  3. COUNT：统计表中的记录条数，忽略 NULL 值。

    SELECT COUNT(*) from SC;

分组

  1. 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;

  2. 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语句，结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

作用

  1. 简化复杂的 SQL 操作，比如复杂的联结；
  2. 只使用实际表的一部分数据；
  3. 通过只给用户访问视图的权限，保证数据的安全性；
  4. 更改数据格式和表示。

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

  • 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 语句，而是被该语句检索出来的结果集。
  • 在存储过程中使用游标可以对一个结果集进行移动遍历。
  • 游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

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

触发器（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
  1. 触发器名：触发器对象的名称。

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

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

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

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

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

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

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

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

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

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

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/ghostwolf1/p/14036906.html