自学内容网 自学内容网
自学内容网 > 正文

Oracle学习笔记

🕗 发布于 2024-07-10 20:54 oracle  学习  笔记  数据库  sql  

Oracle

一、简介：

特点：

  • 多用户、大事务量的事务处理

  • 数据安全性和完整性控制

  • 支持分布式数据处理

  • 可以移植性

Oracle 19c 安装

登录甲骨文，安装Oracle

在这里插入图片描述

解压压缩包

在这里插入图片描述

安装

在这里插入图片描述

完毕

此处账户：qfedu

密码：wang8218.

在这里插入图片描述

检验

在这里插入图片描述

Oracle 19c 卸载

进入services

在这里插入图片描述

关闭服务

在这里插入图片描述

删除注册表信息

在这里插入图片描述

删除该文件的Oracle并删除

在这里插入图片描述

二、体系结构

1.核心组件

  • Oracle数据库实例：在内存中运行的数据库进程集合，负责管理数据库的运行时状态和资源。每个数据库实例都有自己的内存结构和进程集合，包括SGA（系统全局区）、PGA（程序全局区）和后台进程。
  • Oracle数据库：物理存储设备上的数据文件集合，包括数据文件、控制文件和日志文件等。数据库包含了数据库中所有的数据和对象，如索引、视图、存储过程等。

2.内存结构

  • 系统全局区（SGA）

    ：在实例启动时自动分配的共享内存结构，包含实例的数据和控制信息。SGA包括共享池、数据库缓存高速缓冲区、重做日志缓冲区等。

    • 共享池：包含库高速缓存、数据字典高速缓存和控制结构。
    • 数据库缓存高速缓冲区：用于缓存当前或最近使用的从磁盘读取的数据块的拷贝。
    • 重做日志缓冲区：用于短期存储将写入到磁盘中的重做日志信息。

  • 程序全局区（PGA）：包含某个服务器进程或后台进程的数据及控制信息的内存区域。每个服务器进程和后台进程都具有自己的PGA。

3.进程结构

  • 用户进程：与Oracle服务器进行交互的程序，如Oracle的sqlplus、sql developer等。
  • 服务器进程：负责处理连接到该实例的用户进程的请求。
  • 后台进程：为了保持最佳系统性能和协调多个用户请求而设置的一系列后台进程。常见的后台进程包括DBWn（数据库写进程）、LGWR（日志写进程）、SMON（系统监视器进程）和PMON（进程监视器进程）等。

4. 存储结构

  • 物理存储：包括数据文件、控制文件（ctl）和日志文件（log）等操作系统物理文件。

    控制文件最小，最重要

在这里插入图片描述

  • 逻辑存储：从逻辑角度分析数据库的构成，主要有表空间、段、区和块等。

5. 体系结构层级

  • 物理层：管理数据库的物理存储结构，如数据文件的存储和管理。
  • 逻辑层：管理数据库的逻辑数据结构，如索引、视图等对象的定义和操作。
  • 内存层：管理数据库的运行时内存，如数据缓冲区、共享池等。
  • 进程层：管理数据库的运行时进程，如用户连接、查询执行、事务管理等。

6. 核心功能

  • 事务管理：支持事务的原子性、一致性、隔离性和持久性。
  • 数据安全：提供身份认证、权限管理、数据加密、审计跟踪等功能。
  • 性能优化：包括查询优化、索引优化、存储优化等。
  • 可伸缩性：支持水平和垂直扩展，以满足不同规模和需求的数据库应用程序。
  • 高可用性：包括故障转移、数据复制、自动故障检测和自动故障恢复等。

结构解析

Oracle的体系结构大体上分为两部分：Instance（实例）和Database（数据库）。

在这里插入图片描述

Instance-SGA

主要包含 SGA 以及一些 Backgroud Process （后台进程）（例如：PMON、SMON、DBWR、LGWR、CKPT等）。

SGA包含6 个基本组件：Shared Pool（Library Cache,Data Dictionary Cache）、Database Buffer Cache、Redo Log Buffer、Java Pool、Large Pool、stream pool。

1） shared pool（共享池）

  • 共享池是对 SQL、PL/SQL 程序进行语法分析、编译、执行的内存区域。

  • 共享池由 Library Cache（库缓存）、Data Dictionary Cache（数据字典缓存）和 Server Result Cache（结果缓存）等组成。

  • 共享池的大小直接影响数据库的性能。

作用

  • Library Cache： SQL 和 PL/SQL 的解析场所，存放着经过编译解析后的 SQL 和 PL/SQL 语句的内容，以备所有用户共享。

  • Data Dictionary Cache： 存放重要的数据字典信息，以备数据库使用。

  • Server Result Cache: 存放服务器端的 SQL 结果集及 PL/SQL 函数返回值。

2） Database Buffer Cache （数据缓冲区）

  • Database Buffer Cache 用于存储从磁盘数据文件中读入的数据，为所有用户共享。

  • Server Process（服务器进程)将读入的数据保存在数据缓冲区中，当后续的请求需要这些数据时可以在内存中找到，则不需要再从磁盘读取。

  • Database Buffer Cache 的大小对数据库的读取速度有直接的影响。

3）Redo Log Buffer （重做日志缓冲区）

  • 日志条目（Redo Entries ）记录了数据库的所有修改信息(包括 DML 和 DDL）,一条 Redo Entries 记录一次对数据库的改变 ，为的是数据库恢复。日志条目首先产生于日志缓冲区。
  • 日志缓冲区较小，它是以字节为单位的，它极其重要。
  • 在 Database Buffer Cache 中一旦有数据更改，马上写入 Redo Log Buffer，在内存中保留一段时间后，会写入磁盘，然后归档（3级结构）。

4） Large Pool （大池）（可选）

  • 为了进行大的后台进程操作而分配的内存空间，与 shared pool 管理不同，主要用于共享服 务器的 session memory，RMAN 备份恢复以及并行查询等。

5）Java Pool （Java 池）(可选）

  • 为了 java 虚拟机及应用而分配的内存空间，包含所有 session 指定的 JAVA 代码和数据。

6）Stream Pool （流池）(可选)

  • 为了 stream process 而分配的内存空间。stream 技术是为了在不同数据库之间共享数据，

  • 因此，它只对使用了 stream 数据库特性的系统是重要的。

Instance-Background process

在正式介绍 Background Process 之前，先简单介绍 Oracle 的 Process 类型。

Oracle Process 有三种类型：（前台进程）

User Process

  • 属于客户端的 Process，客户端要与服务器连接，在客户端启动起来的进程就是 User Process ；
  • 一般分为三种形式（sql*plus, 应用程序，web 方式（OEM））；
  • User Process 不能直接访问Oracle 。

Server Process

  • 属于服务器端 Process，用于接应User Process 发出的请求；
  • 给用户分配 PGA（程序全局区）。

background process

  • Oracle Instance的重要组成部分。下文会对其做详细讲解。

【User Process 不能直接访问 Oracle，必须通过相应的 Server Process 访问实例，进而访问数据库。】

1）PMON（Process Monitor）（后台进程）

PMON的主要作用如下：

  • 监控各个Oracle后台进程是否正常，发现异常的进程将其清除掉，重新生成该进程；

  • 监控空闲会话是否到达阀值；

  • 动态注册监听。

2）SMON（System Monitor）（后台进程）

SMON的主要作用如下：

  • 当Oracle运行时突然宕机，下次启动需要实例恢复（Instance Recovery），SMON负责实例恢复的全程监控；
  • 当Oracle运行时突然宕机，在下一次启动Oracle Instance的时候，它里面一些没有释放的资源会由SMON进行清理；
  • 一些事务失败的时候也由SMON进行清理；
  • 内存空间很分散（不连续）时需要SMON将这个分散的空间整合起来；
  • 释放不再使用的临时段（Segment）。

3）DBWR（Database Writer）（后台进程）

DBWn是Oracle中工作最繁重的进程。主要作用如下：

  • 将 Database Buffer Cache 中的脏块（Dirty Buffer）写到 Data File 中；

  • 释放Data Buffer Cache空间。

注意：以下几种情况发生时，都会触发DBWR Process将 Database Buffer Cache 的内容写到Data Files ：

  • Checkpoint Occurs
  • Dirty Buffer reach threshold
  • There are no free Buffers
  • Timeout occurs
  • RAC ping request is made
  • Tablespace OFFLINE
  • Tablespace READ ONLY
  • Table DROP or TRUNCATE
  • Tablespace BEGIN
  • BACKUP

4）LGWR（LOG Writer）（后台进程）

Oracle Instance中只有一个LGWR Process，这个Process的工作和DBWR Process类似。主要作用如下：

  • 将 Redo Log Buffer 中的内容写入到 Redo Log Files 中（必须在 DBWR 写脏块之前写入日志）。

注意：以下几种情况发生时，都会触发LGWR Process将 Redo Log Buffer 中的内容写到 Redo Log Files ：

  • At Commit
  • When one-third full
  • When there is 1 MB of redo
  • Every three seconds
  • Before DBWn writes

5）CKPT（Checkpoint）（后台进程）

CKPT的主要作用如下：

  • 生成检查点， 通知或督促 DBWR 写脏块;

*完全检查点：保证数据一致性。
*增量检查点：不断更新控制文件中的检查点位置，当发生实例崩溃时，可以尽量缩短实例恢复的时间。

  • 在Data File的文件头更新检查点信息;
  • 在Control File中更新检查点的信息。

6）ARCn（Archiver）（后台进程）

  • ARCn是一个可选的后台进程（几乎可看作必选进程）。
  • Oracle可以运行在两种模式下：ARCHIVELOG MODE（归档模式）、NOARCHIVELOG MODE（非归档模式）。DBA 必须做出的一个重要决策是，配置数据库在ARCHIVELOG模式下运行，还是在NOARCHIVELOG模式下运行。
  • 联机重做日志文件填满后，oracle实例开始写入下一个联机重做日志文件。从一个联机重做日志文件切换到另一个联机重做日志文件的过程称为日志切换。

ARCn的主要作用如下：

当Oracle运行在归档模式时

  • ARCn进程在每次进行日志切换时都会开始对已填满的日志组进行备份或归档；
  • ARCn进程会在可以重新使用日志之前自动归档重做日志文件，因此会保留对数据库所做的所有更改。这样，即使磁盘驱动器损坏，也可以将数据库恢复到故障点。

通过上面的学习，现把图 1 更新如下：

在这里插入图片描述

Database

Database 其实就是由一堆物理文件组成的，主要是用于存储数据，Database 中主要包含三种类型的文件：Data Files、Control Files、Redo Log Files。

1）Data Files（数据文件）

  • Data Files 就是用于存储数据的，Table 中的数据都是保存在 Data Files 中的。
  • 1个 Data File 对应磁盘上的 1 个物理操作系统文件。
  • Data Files 中频繁访问的数据块缓存在 Database Buffer Cache 中。
  • 新的数据块不会立刻写出到 Data Files，而是在 DBWR处于活动状态时再写出。

2）Control Files（控制文件）

  • Oracle为了操作Data File，提供了一些 Control Files，这些 Control Files 主要是记录数据库的一些控制信息。
  • 用于维护数据库对象的状态，维护数据库的元数据（数据库自身物理结构的数据：数据库名称、数据库创建时间、所有数据文件和重做日志文件的名称及位置）。

3）Redo Log Files（重做日志文件）

  • Redo Log Files 记录着数据库的改变，如果向数据库中放入数据或者是修改里面的数据，只要对数据库作了修改（insert，delete，update），那么就要将修改之前的状态、修改之后的状态都记录在 Redo Log Files 中，其作用就是恢复Data File。

例如：数据库有一个事务需要提交，但是提交失败了，事务就要回滚，那么事务回滚的依据就来自于这个 Redo Log Files。Redo Log Files 中记录着数据库的改变，关于这个事务的改变，如果需要回滚就需要把Redo Log Files中的数据取出来，依照 Redo Log Files 中的数据把 Data Files 恢复到修改之前的状态。

有 3 种状态：Active、Inactive、Current 。

4）Parameter File（参数文件）更多内容

  • 任何一个数据库都必须要有参数文件，这个参数文件规定了Oracle中的一些基本的参数、初始化的参数的值。

5）Archived Log Files（归档日志文件）

  • Archived Log Files 和 Redo Log Files 是相辅相成的，Redo Log Files 其实是一个反复利用的过程，会有几个（一般为3个）固定的文件，这些固定文件会被依次使用，用满了以后，Oracle就会再次写这个文件头，就把以前的东西冲掉了。为了进一步加强数据库的备份恢复能力，在覆盖之前把这些修改的信息都归档到 Archived Log Files 中。

6）Password File（密码文件）

  • 用户客户端连接到后台数据库系统时候存储口令的。

Oracle 的 Memory Structure 实际上包含两部分内容：SGA和PGA

  • SGA（System Global Area系统全局区）

  • PGA（Program Global Area程序全局区）

用户与 Oracle 服务器的连接方式

  • 专用连接模式(Dedicated Server Process)
  • 共享连接模式(shared server process)
  • 驻留连接池模式（Database Resident Connection Pooling，简称 DRCP）

字典：

在这里插入图片描述

动态视图：

在这里插入图片描述

三、sql语言基础

工具：

使用dbeaver程序使用运行Oracle

DBeaver Community | Free Universal Database Tool

下载安装windows （install）

在这里插入图片描述

双击exe文件进行安装

在这里插入图片描述

进入软件，连接Oracle，测试链接

在这里插入图片描述

语言：

一、基础
1.SELECT 语句
SELECT 语句用于从表中选取数据。

结果被存储在一个结果表中（称为结果集）。

SELECT 列名称 FROM 表名称

SELECT * FROM 表名称

**在结果集（result-set）中导航：**

由 SQL 查询程序获得的结果被存放在一个结果集中。大多数[数据库](https://cloud.tencent.com/solution/database?from_column=20065&from=20065)软件系统都允许使用编程函数在结果集中进行导航，比如：Move-To-First-Record、Get-Record-Content、Move-To-Next-Record 等等。
2.SELECT DISTINCT 语句
在表中，可能会包含重复值。关键词 DISTINCT 用于返回唯一不同的值。（去重）

SELECT DISTINCT 列名称 FROM 表名称
3.WHERE 子句
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
4.AND 和 OR 运算符
AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
5.ORDER BY 语句
ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序（ASC）对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
6.INSERT INTO 语句
INSERT INTO 语句用于向表格中插入新的行。

INSERT INTO 表名称 VALUES (值1, 值2,....)

我们也可以指定所要插入数据的列：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
7.Update 语句
Update 语句用于修改表中的数据。

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
8.DELETE 语句
DELETE 语句用于删除表中的行。

DELETE FROM 表名称 WHERE 列名称 = 值

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

DELETE FROM table_name

或者：

DELETE * FROM table_name、
9.COMMENT语句

注释

使用方法：为字段添加注释：

COMMENT ON COLUMN stu_A.DR IS '东软集团';
二、高级
1.TOP 子句
TOP 子句用于规定要返回的记录的数目。

对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。

SELECT TOP number|percent column_name(s)FROM table_name

例子：选择前两个记录

SELECT TOP 2 * FROM Persons

选择前50%的记录：

SELECT TOP 50 PERCENT * FROM Persons
2.LIKE 操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern

例子：从 "Persons" 表中选取居住在以 "N" 开始的城市里的人：

SELECT * FROM PersonsWHERE City LIKE 'N%'

"%" 可用于定义通配符（模式中缺少的字母）。可为空。

'%g'、'%lon%'均可

通过使用 NOT 关键字，可以从 "Persons" 表中选取居住在**不包含** "lon" 的城市里的人：

SELECT * FROM PersonsWHERE City NOT LIKE '%lon%'
3.SQL 通配符

直接上例子：

  • 使用 % 通配符
从 "Persons" 表中选取居住在包含 "lond" 的城市里的人：

SELECT * FROM PersonsWHERE City LIKE '%lond%'
  • 使用 _ 通配符
从 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人：

SELECT * FROM PersonsWHERE FirstName LIKE '_eorge'

从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是任意字符，然后是 "er"：

SELECT * FROM PersonsWHERE LastName LIKE 'C_r_er'
  • 使用 [ ] 通配符
从"Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：

SELECT * FROM PersonsWHERE City LIKE '[ALN]%'

从 "Persons" 表中选取居住的城市**不以** "A" 或 "L" 或 "N" 开头的人：

SELECT * FROM PersonsWHERE City LIKE '[!ALN]%'
4.IN 操作符
IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...)

例子：

SELECT * FROM PersonsWHERE LastName IN ('Adams','Carter')
5.BETWEEN 操作符
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SELECT column_name(s)FROM table_nameWHERE column_name BETWEEN value1 AND value2

如需选择范围之外的，请使用 NOT 操作符，加在between前面：
6.AS
为列名称和表名称指定别名（Alias）

表

SELECT column_name(s)FROM table_nameAS alias_name

列

SELECT column_name AS alias_nameFROM table_name
7.join

有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。

例子：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN OrdersON Persons.Id_P = Orders.Id_PORDER BY Persons.LastName

下面列出了几种 JOIN 类型，以及它们之间的差异。

· JOIN（即INNER JOIN）: 如果表中有至少一个匹配，则返回行##内交

· LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行##左外

· RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行##右外

· FULL JOIN: 只要其中一个表中存在匹配，就返回行##并
8.SQL集合
SQL UNION 并集操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT column_name(s) FROM table_name1 UNION (ALL)SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

例子：

列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA
SQL INTERSECT 交集操作符

在Oracle数据库中，INTERSECT 是一个集合操作符，用于返回两个或多个 SELECT 语句结果的交集。具体来说，它返回那些在所有查询结果中都出现的行，但是每个查询结果中的列数和数据类型必须相同，且这些列在结果集中的顺序也必须相同。

SELECT column1, column2  
FROM TableA  
INTERSECT  
SELECT column1, column2  
FROM TableB;
SQL MINUS 差集操作符

在Oracle数据库中，MINUS 是一个集合操作符，用于从一个查询结果中减去另一个查询结果，返回只存在于第一个查询结果中而不存在于第二个查询结果中的行。这个操作符非常有用，特别是在进行数据差异分析时。

SELECT column1, column2, ...  
FROM table1  
MINUS  
SELECT column1, column2, ...  
FROM table2;
9.SELECT INTO 语句
SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

您可以把所有的列插入新表：

SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename

或者只把希望的列插入新表：

SELECT column_name(s)INTO new_table_name [IN externaldatabase] FROM old_tablename

下面的例子会制作 "Persons" 表的备份复件：

SELECT *INTO Persons_backupFROM Persons

IN 子句可用于向另一个数据库中拷贝表：

SELECT *INTO Persons IN 'Backup.mdb'FROM Persons

可以在最后添加 WHERE 子句，也能使用join从一个以上的表中选取数据
10.CREATE DATABASE 语句
CREATE DATABASE 用于创建数据库。

CREATE DATABASE database_name
11.CREATE TABLE 语句
CREATE TABLE 语句用于创建数据库中的表。

CREATE TABLE 表名称(

列名称1 数据类型,

列名称2 数据类型,

列名称3 数据类型,....)
12.SQL 约束

约束用于限制加入表的数据的类型。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

主要有以下几种约束：

· NOT NULL

· UNIQUE

· PRIMARY KEY

· FOREIGN KEY

· CHECK

· DEFAULT
*SQL NOT NULL 约束*

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

例子：

CREATE TABLE Persons(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255))
*SQL UNIQUE 约束*

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 UNIQUE 约束：

CREATE TABLE Persons(

    Id_P int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255),UNIQUE (Id_P)
)

或者

CREATE TABLE Persons(

    Id_P int NOT NULL UNIQUE,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)
)

如果需要命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束，请使用下面的 SQL 语法：

CREATE TABLE Persons(

    Id_P int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255),CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束，请使用下列 SQL：

    ALTER TABLE PersonsADD UNIQUE (Id_P)

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

    ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

如需撤销 UNIQUE 约束，请使用下面的 SQL：

    ALTER TABLE PersonsDROP CONSTRAINT uc_PersonID
*SQL PRIMARY KEY 约束*

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：

CREATE TABLE Persons(

    Id_P int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255),PRIMARY KEY (Id_P)
)

或者

CREATE TABLE Persons(

    Id_P int NOT NULL PRIMARY KEY,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)
)

如果需要命名约束，以及为多个列定义约束、表已存在的情况下创建或命名、撤销约束。语法和上述UNIQUE相同。
*SQL FOREIGN KEY 约束*

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

下面的 SQL 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY：

CREATE TABLE Orders(

    Id_O int NOT NULL,

    OrderNo int NOT NULL,

    Id_P int,PRIMARY KEY (Id_O),FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

或者

CREATE TABLE Orders(

    Id_O int NOT NULL PRIMARY KEY,

    OrderNo int NOT NULL,

    Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

如果需要命名约束，以及为多个列定义约束、表已存在的情况下创建或命名、撤销约束。语法和上述UNIQUE相同。
*SQL CHECK 约束*

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

下面的 SQL 在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数。

CREATE TABLE Persons(

    Id_P int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255),CHECK (Id_P>0)
)

或者

CREATE TABLE Persons(

    Id_P int NOT NULL CHECK (Id_P>0),

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255)
)

如果需要命名约束，以及为多个列定义约束、表已存在的情况下创建或命名、撤销约束。语法和上述UNIQUE相同。
*SQL DEFAULT 约束*

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束：

CREATE TABLE Persons(

    Id_P int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255) DEFAULT 'Sandnes'
)

通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

CREATE TABLE Orders(

    Id_O int NOT NULL,

    OrderNo int NOT NULL,

    Id_P int,

    OrderDate date DEFAULT GETDATE()
)

如果在表已存在的情况下为 "City" 列创建 DEFAULT 约束，请使用下面的 SQL：

    ALTER TABLE PersonsALTER (COLUMN) City SET DEFAULT 'SANDNES'

如需撤销 DEFAULT 约束，请使用下面的 SQL：

    ALTER TABLE PersonsALTER (COLUMN) City DROP DEFAULT
13.CREATE INDEX 语句（索引）
用于在表中创建索引。

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

SQL CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_nameON table_name (column_name)

注释："column_name" 规定需要索引的列。

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_nameON table_name (column_name)

本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列：

CREATE INDEX PersonIndexON Person (LastName) 

可以索引不止一个列，在括号中列出这些列的名称，用逗号隔开。
14.DROP 语句
通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

删除索引

DROP INDEX index_name ON table_name

DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：

DROP TABLE 表名称

DROP DATABASE 语句用于删除数据库：

DROP DATABASE 数据库名称

如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？

请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：

TRUNCATE TABLE 表名称
15.ALTER TABLE 语句
ALTER TABLE 语句用于在已有的表中添加、修改或删除列

如需在表中添加列，请使用下列语法:

ALTER TABLE table_nameADD column_name datatype

要删除表中的列，请使用下列语法：

ALTER TABLE table_name DROP COLUMN column_name
16.AUTO INCREMENT 字段（自增）
我们通常希望在每次插入新记录时，自动地创建主键字段的值。

我们可以在表中创建一个 auto-increment 字段。

例子：

CREATE TABLE Persons(

    P_Id int NOT NULL AUTO_INCREMENT,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Address varchar(255),

    City varchar(255),PRIMARY KEY (P_Id)
)

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：

    ALTER TABLE Persons AUTO_INCREMENT=100

要在 "Persons" 表中插入新记录，我们不必为 "P_Id" 列规定值（会自动添加一个唯一的值）：
17.SQL CREATE VIEW 语句（视图）

什么是视图？

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

可以从某个查询内部、某个存储过程内部，或者从另一个视图内部来使用视图。通过向视图添加函数、join 等等，我们可以向用户精确地提交我们希望提交的数据。

样本数据库 Northwind 拥有一些被默认安装的视图。视图 “Current Product List” 会从 Products 表列出所有正在使用的产品。这个视图使用下列 SQL 创建：

CREATE VIEW [Current Product List] ASSELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=No

我们可以查询上面这个视图：

SELECT * FROM [Current Product List]

Northwind 样本数据库的另一个视图会选取 Products 表中所有单位价格高于平均单位价格的产品：

CREATE VIEW [Products Above Average Price] ASSELECT ProductName,UnitPriceFROM ProductsWHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

您可以使用下面的语法来更新视图：

CREATE OR REPLACE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

现在，我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图：

CREATE VIEW [Current Product List] ASSELECT ProductID,ProductName,CategoryFROM ProductsWHERE Discontinued=No

您可以通过 DROP VIEW 命令来删除视图。

DROP VIEW view_name
18.SQL 日期

在这里插入图片描述

数据库常用sql语句总结

MySQL](https://cloud.tencent.com/product/cdb?from_column=20065&from=20065) 使用下列数据类型在数据库中存储日期或日期/时间值：

· DATE - 格式 YYYY-MM-DD

· DATETIME - 格式: YYYY-MM-DD HH:MM:SS

· TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS

· YEAR - 格式 YYYY 或 YY

如果不涉及时间部分，那么我们可以轻松地比较两个日期！

如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！

19.SQL NULL 值

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。

NULL 值的处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。

我们必须使用 IS NULL 和 IS NOT NULL 操作符。

请始终使用 IS NULL 来查找 NULL 值。

Oracle 没有 ISNULL() 函数。不过，我们可以使用 NVL() 函数

【语法】NVL (expr1, expr2)

【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。

注意两者的类型要一致

20.SQL ANY

在SQL查询中，ANY 可以与比较运算符（如 =, >, <, <> 等）一起使用，来测试一个值是否满足子查询结果集中的任意一个值。（<>为！=）

SELECT sname,sage  
FROM student WHERE SDEPT !='jg' AND sage < ANY (
SELECT sage FROM student WHERE sdept='jg'
)

查找sdept非jg中满足小于jg中的其中一个或多个sage的sname以及sage。

21.SQL ALL

在SQL查询中，ALL 可以与比较运算符（如 =, >, <, <> 等）一起使用，来测试一个值是否满足子查询结果集中的所有值。（<>为！=）

SELECT  sname,sage FROM student WHERE SDEPT <>'jg' AND 
sage<all(
SELECT sage FROM student WHERE SDEPT ='jg'
);

查找非jg中比所有jg学生年龄小的sname以及sage。

22.SQL EXISTS

在Oracle数据库中，EXISTS是一个用于检查子查询是否返回任何行的关键字。它返回一个布尔值：如果子查询返回至少一行记录，则返回TRUE；如果子查询没有返回任何记录，则返回FALSE。EXISTS通常与WHERE子句结合使用，根据子查询的结果来过滤主查询的记录。

SELECT column1, column2, ...  
FROM table_name  
WHERE EXISTS (subquery);

在这个语法中，subquery是一个嵌套在EXISTS中的子查询，它可以是任意有效的SELECT语句。对于主查询中的每一行记录，都会执行一次子查询。

--查询所有选修了1课程的学生--
SELECT * FROM STUDENT WHERE  EXISTS (
SELECT  * FROM sc WHERE student.SNO =sc.SNO AND cno='1'
)
三、SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。

内建 SQL 函数的语法是：

SELECT function(列) FROM 表

*1.SQL AVG 函数*

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

SQL AVG() 语法

SELECT AVG(column_name) FROM table_name

*2.SQL COUNT() 函数*

COUNT() 函数返回匹配指定条件的行数。

SQL COUNT() 语法

SQL COUNT(column_name) 语法

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) 语法

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) 语法

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name

*3.FIRST() 函数*

FIRST() 函数返回指定的字段中第一个记录的值。

提示：可使用 ORDER BY 语句对记录进行排序。

SQL FIRST() 语法

SELECT FIRST(column_name) FROM table_name

*4.LAST() 函数*

LAST() 函数返回指定的字段中最后一个记录的值。

提示：可使用 ORDER BY 语句对记录进行排序。

SQL LAST() 语法

SELECT LAST(column_name) FROM table_name

*5.MAX() 函数*

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

SQL MAX() 语法

SELECT MAX(column_name) FROM table_name

注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。

*6.MIN() 函数*

MIN 函数返回一列中的最小值。NULL 值不包括在计算中。

SQL MIN() 语法

SELECT MIN(column_name) FROM table_name

注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。

*7.SUM() 函数*

SUM 函数返回数值列的总数（总额）。

SQL SUM() 语法

SELECT SUM(column_name) FROM table_name

*8.GROUP BY 语句*

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

例子：

希望查找每个客户的总金额（总订单）。（每个客户有多个订单）

我们想要使用 GROUP BY 语句对客户进行组合。

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

*9.HAVING 子句*

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SQL HAVING 语法

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value

现在，我们希望查找订单总金额少于 2000 的客户。

我们使用如下 SQL 语句：

SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000

*10.UCASE() 函数*

UCASE 函数把字段的值转换为大写。

SQL UCASE() 语法

SELECT UCASE(column_name) FROM table_name

*11.LCASE() 函数*

LCASE 函数把字段的值转换为小写。

SQL LCASE() 语法

SELECT LCASE(column_name) FROM table_name

*12.MID() 函数*

MID 函数用于从文本字段中提取字符。

SQL MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name

column_name必需。要提取字符的字段。
start必需。规定开始位置（起始值是 1）。
length可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

现在，我们希望从 “City” 列中提取前 3 个字符。

我们使用如下 SQL 语句：

SELECT MID(City,1,3) as SmallCity FROM Persons

*13.LEN() 函数*

LEN 函数返回文本字段中值的长度。

SQL LEN() 语法

SELECT LEN(column_name) FROM table_name

*14.ROUND() 函数*

ROUND 函数用于把数值字段舍入为指定的小数位数。

SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM table_name

*15.NOW() 函数*

NOW 函数返回当前的日期和时间。

提示：如果您在使用 Sql Server 数据库，请使用 getdate() 函数来获得当前的日期时间。

SQL NOW() 语法

SELECT NOW() FROM table_name

*16.FORMAT() 函数*

FORMAT 函数用于对字段的显示进行格式化。

SQL FORMAT() 语法

SELECT FORMAT(column_name,format) FROM table_name

参数描述
column_name必需。要格式化的字段。
format必需。规定格式。

现在，我们希望显示每天日期所对应的名称和价格（日期的显示格式是 “YYYY-MM-DD”）。

我们使用如下 SQL 语句：

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products

练习：

1.建立
--建立student--
CREATE TABLE student(
sno char(10) PRIMARY KEY,
sname char(20) UNIQUE ,
ssex char(2),
sage SMALLINT,
sdept char(20)
);
--建立course--
CREATE TABLE course(
cno char(10) PRIMARY KEY,
cname char(40),
cpno char(4),
ccredit SMALLINT,
);
--建立sc--
CREATE TABLE sc(
sno char(10),
cno char(10),
grade SMALLINT,
PRIMARY KEY(sno,cno),
FOREIGN KEY (sno) REFERENCES student(sno),
FOREIGN KEY (cno) REFERENCES course(cno)
);
--增加--
ALTER TABLE student ADD s_entrance DATE;
--修改字符类型--
ALTER TABLEstudent modify sage int;
--加课程名，唯一值得约束条件--
ALTER TABLE COURSE ADD UNIQUE(cname);
--查询--
SELECT * FROM student;
--增删改字段--
ALTER TABLE student ADD  id char(20);
ALTER TABLE student DROP column ID;
ALTER TABLE student RENAME COLUMN sno TO ssno;
ALTER TABLE student MODIFY (ssex char(10));
ALTER TABLE student ADD sno char(10); 
ALTER TABLE student DROP COLUMN sno; 
ALTER TABLE student RENAME COLUMN ssno TO sno;
--新增内容--
ALTER TABLE student DROP COLUMN s_entrance;
GRANT dba TO scott;
INSERT INTO STUDENT(sno,SNAME) VALUES ('1001','牛牛');
insert into STUDENT  values('18122221','何白露','女',13,'heyy@sina.com ');
insert into STUDENT  VALUES ('181251111','敬横江','男',14,'jing@sina.com ');
insert into STUDENT  values('181251211','梁一苇', '女',24,'bing@126.com ');
insert into STUDENT  values ('181352222','凌浩风', '女',23,'tang@163.com ');
insert into STUDENT  values ('181372215','赵临江','男',14,'ping@163.com ');
insert into STUDENT  values('191111330','崔依歌','女',34,'cui@126.com ');
insert into STUDENT  values ('191121072','宿沧海', '男',13,'su12@163.com');
insert into STUDENT  values ('191121108','韩山川', '男',15,'han@163.com ');
insert into STUDENT  values ('191222067','封月明', '女',16,'jiao@126.com');
insert into STUDENT  values('191235697','赵既白', '女',18,'pingan@163.com');
insert into STUDENT  values('191261307','梅惟江', '女',20,'zhu@163.com');

SELECT * FROM COURSE ;
insert into COURSE   values('1','数据库',NULL ,4);
insert into COURSE  VALUES ('2','数学',NULL ,2);
insert into COURSE  VALUES ('3','信息系统',null,4);
insert into COURSE  values ('4','操作系统',NULL,3);
insert into COURSE  values ('5','数据结构',NULL,4);
insert into COURSE  values('6','数据处理',NULL,2);
insert into COURSE  values ('7','python',NULL,4);
insert into COURSE  values ('8','java',NULL,3);

SELECT * FROM sc; 
insert into sc  values ('191222067','1',87);
insert into sc  values('191235697','1',90);
insert into sc  values('191261307','3',95);
insert into sc  values('18122221','3',88);
--新表--
CREATE TABLE sstudent(
id char(10)
);
--删表--
DROP TABLE SSTUDENT ;
2.基础
--查student的sno，sname--
SELECT sno,sname FROM student ;
--查询学生sname ， sno，联系方式
SELECT sno,SNAME,SDEPT  FROM student ;
--查详细记录--
SELECT SNAME,SNO,SSEX,SAGE,SDEPT FROM student ;
--查询姓名，出生年--
SELECT sname,2024-sage FROM student;
--查sname,sage,sdept（大写）--
SELECT sname,2024-SAGE,upper(SDEPT) FROM student;
--查sname,sage,sdept（小写）--
SELECT sname,2024-SAGE,LOWER(SDEPT) FROM student;
--更改列标题--
SELECT sname AS 姓名,2024-SAGE AS 出生年,LOWER(SDEPT) AS 邮箱 FROM student;
--查询选修了课程的学生学号-
SELECT ALL sno FROM sc;
--去重--
SELECT DISTINCT sno FROM sc;
--查询20以上年龄sname,sage--
SELECT SNAME,SAGE FROM STUDENT WHERE SAGE >=20;
--不及格sno--
SELECT DISTINCT SNO FROM sc WHERE GRADE >=60;
--20-23岁--
SELECT * FROM student WHERE sage>=20 AND sage<=23;
--<20&&>23--
SELECT * FROM student WHERE sage NOT BETWEEN 20 AND 23;
--in--
SELECT * FROM student WHERE sage IN(13,12,14,15,16);
--通配%--
SELECT * FROM student WHERE sno LIKE '10%';
--通配_--
SELECT * FROM student WHERE sname LIKE '赵_白';
--有成绩--
SELECT DISTINCT sno FROM sc WHERE GRADE IS NOT NULL;
--排序--
SELECT * FROM student ORDER BY sno;
--聚集函数count--
SELECT count(DISTINCT sno) FROM sc;
--avg--
SELECT avg(grade) FROM sc where cno='3';
--max--
SELECT max(grade) FROM sc WHERE cno='1';
--1001的平均绩点--
SELECT avg(CCREDIT) FROM COURSE WHERE cno IN (SELECT cno FROM sc WHERE sno='1001')
--sum--
SELECT sum(grade) FROM sc WHERE sno='1001';
--group by--
SELECT cno,count(sno) FROM sc  GROUP BY cno;
--having--
SELECT sno FROM sc GROUP BY sno HAVING count(*)>=2;
3.高级

连接+子查询

--删除--
DROP TABLE STU_A ;
--交叉连接建立新表--
CREATE TABLE stu_A  AS SELECT student.sno,sname,cno FROM student,sc WHERE student.SNO =sc.SNO ;
--每个学生及选课情况--
SELECT * FROM STU_A ;
--新加字段--
ALTER TABLE stu_A ADD DR char(11);
--为字段设置注释--
COMMENT ON COLUMN stu_A.DR IS '东软集团';
--每门课先修课--
SELECT DISTINCT c2.* FROM COURSE c1,COURSE c2 WHERE c2.cno=c1.cpno; 
--外连接，左--
SELECT * FROM student LEFT JOIN sc ON student.sno=sc.sno;
--外连接，右--
SELECT * FROM student RIGHT JOIN sc on student.SNO=sc.SNO;   
--内连接--
SELECT * FROM student INNER JOIN sc on student.SNO=sc.SNO;   
--内连接等同于--
SELECT * FROM student,sc WHERE  student.SNO=sc.SNO;   
--查询每个学生的sno，sname,cname,grade
SELECT s.sno,s.sname,c.cname,sc.grade FROM student s,sc,course c
WHERE s.sno=sc.sno AND sc.cno=c.cno;
--使用join改造--
SELECT s.SNO,s.SNAME,c.cname,sc.GRADE FROM student s 
JOIN sc ON s.SNO =sc.SNO 
JOIN COURSE c ON c.cno=sc.CNO ;
--子查询--
SELECT * FROM sc WHERE sno IN （SELECT sno FROM student WHERE sname='何白露'）;
--join代替子查询--
SELECT sc.* FROM student s JOIN sc ON s.sno=sc.sno AND s.sname='何白露';
--查选课”信息系统“学生的sno，sname--
SELECT sno,sname FROM STUDENT s WHERE sno IN (
SELECT sno FROM sc WHERE cno IN (
SELECT cno FROM COURSE c WHERE cname='信息系统'
)
);
--连接查询改造--
SELECT s.sno,s.sname FROM STUDENT s ,SC s2 ,COURSE c 
WHERE s.SNO =s2.SNO AND s2.CNO =c.CNO AND c.CNAME='信息系统' ;
--join--
SELECT s.sno,s.sname FROM STUDENT s 
JOIN sc ON s.SNO =sc.SNO 
JOIN COURSE c ON c.CNO =sc.CNO AND c.CNAME ='信息系统';
--=----尽量不用，因为如果 （不唯一），会报错，尽量用in--
SELECT * FROM student
WHERE sno=(SELECT sno FROM student WHERE sname='牛牛');
--找出超出平均成绩的课程--
SELECT * FROM sc x WHERE GRADE >=(
SELECT avg(GRADE) FROM sc y WHERE x.SNO =y.sno
);
--非jg中比jg任意学生年龄小的学生姓名和年龄--
SELECT sname,sage,SDEPT  
FROM student WHERE SDEPT !='jg' AND sage < ANY (
SELECT sage FROM student WHERE sdept='jg'
);
--min函数--
SELECT min(sage) FROM student WHERE SDEPT ='jg';
--比所有jg学生年龄小--
SELECT  sname,sage FROM student WHERE SDEPT <>'jg' AND 
sage<all(
SELECT sage FROM student WHERE SDEPT ='jg'
);
--聚集函数--
SELECT sname,sage FROM student WHERE SDEPT <>'jg' AND 
sage < (
SELECT min(sage) FROM student WHERE SDEPT ='jg'
);
--查询所有选修了1课程的学生--
SELECT * FROM STUDENT WHERE  EXISTS (
SELECT  * FROM sc WHERE student.SNO =sc.SNO AND cno='1'
);
--或者--
SELECT * FROM student WHERE sno IN (
SELECT sno FROM sc WHERE cno='1'
);
--查询非计算机比计算机任意1个学生年龄小姓名，年龄--
SELECT sage,sname FROM STUDENT WHERE sdept<>'jg' AND sage< ANY (
SELECT sage FROM student WHERE SDEPT='jg' 
);
--查询非计算机比计算机所有学生年龄小姓名，年龄--
SELECT  sname,sage FROM student WHERE SDEPT<>'jg' sage < all(
SELECT sage FROM student WHERE SDEPT ='jg'
);
--查询‘牛牛’在同一系--
SELECT * FROM student s1 WHERE EXISTS (
SELECT * FROM student s2 WHERE s1.SDEPT=s2.sdept AND  s2.sname='牛牛'
);
4.函数
--查询jg学及sage>19--
SELECT * FROM student WHERE SDEPT ='jg' OR sage>19;
--或者union--
SELECT * FROM student WHERE SDEPT ='jg'
UNION 
SELECT * FROM student WHERE sage>19; 
--查选了1或2的学生--
SELECT * FROM sc WHERE cno='1'
UNION 
SELECT * FROM sc where cno='2'; 
--交集--
SELECT * FROM student WHERE SDEPT ='jg'
INTERSECT 
SELECT * FROM student WHERE sage>19; 
--1,2--
SELECT * FROM sc WHERE cno='1'
INTERSECT 
SELECT * FROM sc WHERE cno='2';
--差集--
SELECT * FROM sc WHERE cno='1'
MINUS 
SELECT * FROM sc WHERE cno='2';
--找出超过选修成绩平均分的课程号--
SELECT * FROM 
sc,(SELECT sno,avg(GRADE)AS avggrade FROM sc GROUP BY sno) savg
WHERE sc.SNO =savg.sno AND sc.GRADE >= savg.avggrade;
--查询所有选了1的sname--
SELECT sname FROM
student ,(SELECT sno FROM sc WHERE cno='1') sc1
WHERE student.SNO =sc1.sno;


---------------------------
SELECT * FROM STUDENT s ;
SELECT * FROM sc;
--插入--
INSERT INTO STUDENT VALUES ('1002','KK','男',21,'jg',null);
--或者--**
INSERT INTO STUDENT（sno,sname,ssex,sage） VALUES ('1003','LL','男',21);
--存新表--
CREATE TABLE STU_B(
sdept char(25),
avg_age SMALLINT
)
insert INTO STU_B(sdept,avg_age)
SELECT sdept,avg(sage) FROM student GROUP BY sdept
SELECT * FROM STU_B ;
--修改update--
UPDATE student SET sage=22 WHERE sno ='1001';
--all--
UPDATE student SET sage=sage+1;
--成绩0--
UPDATE sc set grade=0 WHERE sno IN (
SELECT sno FROM student WHERE sdept='jg'
);
--删除delet--
DELETE FROM student WHERE sno='191261307';
--删表--
DELETE FROM STU_B
SELECT * FROM STU_B;
--增加中空值处理insert--
INSERT INTO sc(sno,cno,GRADE) VALUES ('1002','1',NULL)
SELECT * FROM sc;
--改--
UPDATE student SET SDEPT =NULL WHERE sno='1002';
--查找系院为空的信息 is null--
SELECT * FROM student WHERE SDEPT IS NULL ;
--填写信息1003，LL的sdept--
UPDATE STUDENT SET SDEPT ='jg' WHERE sno='1003';



--------------函数---------------------


--数学--临时表,chr（90）=Z
SELECT chr(90),chr(72) FROM dual;
 --数学--连接helloworld 重命名information
SELECT CONCAT('hello','world') information FROM dual;
--数学--向上取整
SELECT ceil(7.3) FROM dual;
--时间--查询函数sysdate--
SELECT SYSDATE AS 系统日期 FROM dual;
--时间--增加x月add_months--
SELECT ADD_MONTHS(sysdate,2) FROM dual;

四、PL/SQL编程

PL/SQL安装：PL/SQL安装+汉化教程

1、简介

PL/SQL（Procedural Language/Structured Query Language）是Oracle数据库的过程化语言扩展，它将SQL的数据操纵功能与过程化语言的数据处理功能结合起来，形成了一种强大的编程语言。以下是对PL/SQL的简介：

一、基本概念
  • 定义：PL/SQL是过程语言（Procedural Language）与结构化查询语言（SQL）结合而成的编程语言，是Oracle数据库对SQL语句的扩展。它允许在SQL语句中嵌入复杂的程序结构，如条件语句、循环、异常处理等。
  • 特点：PL/SQL结合了SQL的数据处理能力和过程语言的控制能力，可以创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑。它支持面向对象编程（OOP），具有可移植性、灵活性和安全性。
二、主要功能和优势
  1. 模块化：PL/SQL可以将一组SQL语句封装成一个独立的单元（如过程、函数或包），从而提高代码的重用性和可维护性。
  2. 过程化控制：通过引入条件语句、循环等控制结构，PL/SQL能够处理复杂的业务逻辑。
  3. 性能优化：与单独执行SQL语句相比，PL/SQL程序可以减少网络交互次数，提高数据处理效率。此外，PL/SQL还支持编译存储，进一步提升了性能。
  4. 安全性：通过存储过程等机制，PL/SQL可以限制用户对数据的直接访问，从而增强数据库的安全性。
  5. 可移植性：PL/SQL编写的应用程序可以移植到任何支持Oracle数据库的操作系统和平台上。
三、基本结构

PL/SQL程序通常包含以下几个部分：

  • 声明部分：用于声明变量、常量、游标、异常等。
  • 执行部分（或称为可执行部分）：包含SQL语句和PL/SQL语句，用于实现程序的业务逻辑。
  • 异常处理部分：用于捕获和处理程序执行过程中可能发生的错误。
四、示例

以下是一个简单的PL/SQL匿名块示例，用于演示变量声明、赋值和输出：

DECLARE  
    v_name VARCHAR2(50) := 'John Doe'; -- 声明并初始化变量  
BEGIN  
    DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name); -- 输出变量值  
EXCEPTION  
    WHEN OTHERS THEN  
        DBMS_OUTPUT.PUT_LINE('An error occurred.'); -- 异常处理  
END;
五、应用场景

PL/SQL广泛应用于Oracle数据库的开发和管理中，包括但不限于：

  • 数据处理和分析
  • 自动化任务（如定时清理过期数据）
  • 应用程序的后端逻辑实现
  • 数据库安全和权限管理

总之，PL/SQL是一种功能强大、灵活且安全的编程语言，它极大地扩展了SQL的能力，为Oracle数据库的开发和管理提供了强大的支持。

六、PL/SQL与SQL区别：

在这里插入图片描述

2、数据类型、变量常量

同编程语言类似

Oracle数据库支持多种数据类型和变量常量，这些数据类型和变量常量在数据库设计和程序开发中起着重要作用。以下是对Oracle数据类型、变量和常量的详细说明：

一、Oracle数据类型

Oracle数据类型主要分为数值类型、字符类型、日期时间类型、大对象类型等几大类。

  1. 数值类型

    • NUMBER：用于存储可变精度的数值，可以指定精度（precision）和标度（scale），即存储的最大位数和小数点后的位数。
    • INTEGER：整数类型，是NUMBER类型的一种特殊形式，用于存储不带小数的整数。
    • FLOAT：浮点数类型，用于存储近似值，具有比NUMBER更大的范围和较低的精度要求。
    • BINARY_FLOAT和BINARY_DOUBLE：二进制浮点数类型，分别用于存储单精度和双精度浮点数，提供高精度的计算和存储能力。
    • DECIMAL（或DEC）：用于存储带有指定精度和标度的小数值，与NUMBER类型相似，但更明确地表示其用途。

  2. 字符类型

    • CHAR：定长字符类型，用于存储指定长度的字符串，无论实际值的长度如何，都会占用指定长度的存储空间。

      char(x) ==固定长度x ， x<32767

    • VARCHAR2：可变长度字符类型，用于存储可变长度的字符串，只占用实际需要的存储空间。

      varcher2（x）==上限为x个单位，x<32767

    • NCHAR和NVARCHAR2：分别用于存储定长和可变长度的Unicode字符，适用于多语言环境。

    • LONG：长字符串数据类型，但Oracle推荐使用CLOB类型来替代LONG类型，因为CLOB类型更灵活。

  3. 日期时间类型

    • DATE：用于存储日期和时间值，包含年、月、日、时、分和秒的信息。

      (yyy-mm-dd hh:mi:ss)

    • TIMESTAMP：时间戳类型，用于存储日期和时间，并提供了更高的精确度，可以存储小数秒。

    • INTERVAL：时间间隔类型，用于存储两个日期或时间之间的差异，可以表示年、月、日、小时、分钟或秒之间的差异。

  4. 大对象类型

    • BLOB：二进制大对象类型，用于存储二进制数据，如图像、视频和音频文件。
    • CLOB：字符大对象类型，用于存储大量的字符数据，包括长文本和文档。
    • NCLOB：大规模Unicode字符数据类型，与CLOB类似，但用于存储Unicode字符数据。

  5. 其他类型

    • BOOLEAN：布尔类型，用于存储真或假的值，在Oracle中通过PL/SQL程序的特殊数据类型实现。
    • RAW：原始二进制类型，用于存储不需要进行字符集转换的二进制数据。
二、Oracle变量和常量
  1. 变量 变量名 数据类型 ：= 变量值；
    • 变量是存储在内存中以获得值的数据存储结构，能被PL/SQL块引用。
    • 在使用变量前需要先声明，声明的格式通常包括变量名、数据类型和可选的初始值。（null）
    • 变量在程序运行的过程中，其值可以发生变化。
  2. 常量 常量名 constant 数据类型 := 常量值
    • 常量与变量相似，但其值在程序运行过程中不可改变。
    • 定义常量时必须包括关键字CONSTANT，并指定数据类型和常量值。
三、特殊数据类型声明方式
  • %TYPE：可以声明一个与指定列相同的数据类型，它通常紧跟在指定列名的后面。使用%TYPE定义变量有两个好处：一是用户不必查看表中各个列的数据类型，就可以确保所定义的变量能够存储检索的数据；二是对表中已有列的数据类型进行修改时，用户不必考虑对已定义的变量所使用的数据类型进行更改，因为%TYPE类型的变量会根据列的实际类型自动调整自身的数据类型。
  • %RECORD: 类似于结构体，在 PL/SQL 和其他编程环境中，RECORD 通常被用作一种自定义的数据类型，用于将多个不同类型的数据项组合成一个单一的结构化单位。这通常通过 TYPE ... IS RECORD() 语句完成。
  • %ROWTYPE：结合了“%TYPE类型”和“记录类型”变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。
四、练习：

变量/常量


--变量--
DECLARE 
    var_countrynname VARCHAR2(50);
BEGIN
    var_countrynname:='China';
dbms_output.put_line('Country name is : ' || var_countrynname);
END;

DECLARE
  v_a NUMBER := 100;
  v_b NUMBER := 100;
BEGIN
v_b := 30;
dbms_output.put_line('The result is:' || (v_a+v_b) );
END;  

--常量--
DECLARE 
    var_c CONSTANT VARCHAR2(50) := 'ch';
BEGIN
    var_c :='China';--会报错
dbms_output.put_line('Country name is : ' || var_c);
END;

特殊类型:

--%TYPE类型--
DECLARE
    var_name student.sname%TYPE;
var_sdept student.sdept%TYPE;
BEGIN
SELECT sname,sdept INTO var_name,var_sdept FROM student WHERE sno='1001';
  dbms_output.put_line('The name is :'|| var_name||',sdept is :'||var_sdept);
END;

--%RECORD类型（类似于结构体）--
DECLARE
TYPE stu_type IS RECORD(
     var_name student.sname%TYPE,
     var_sdept student.sdept%TYPE
);
stuinfo stu_type;

BEGIN
SELECT sname,sdept INTO stuinfo FROM student WHERE sno='1003';
  dbms_output.put_line('The name is :'|| stuinfo.var_name||',sdept is :'||stuinfo.var_sdept);
END;

--%ROWTYPE类型--
DECLARE
rowvar_stu student%ROWTYPE;--封装一整行类型给rowvar_stu--rowvar_stu可以调用student此行的所有值
BEGIN
SELECT * INTO rowvar_stu FROM student WHERE sno='18122221';
  dbms_output.put_line('The name is :'|| rowvar_stu.sname||',sdept is :'||rowvar_stu.sdept);
END;

3、流程控制

异常：
--输入 x := &x--
DECLARE
  a int;
b INT;
c NUMBER;
BEGIN
dbms_output.put_line('please input a value for a');
a := &a;
dbms_output.put_line('please input b value for b');
b := &b;
c := (a+b)/(a-b);
dbms_output.put_line(c);
--异常处理EXCEPTION.. when(条件)..then--
EXCEPTION
WHEN zero_divide THEN
--如果分母为0，返回   Divisor can not be zero--
dbms_output.put_line('Divisor can not be zero');
END;
判断：
--判断if..elsif..else--
DECLARE
  v_name1 VARCHAR2(30);
  v_name2 VARCHAR2(30);
BEGIN
v_name1 := 'qf';
v_name2 := 'qfedu';
IF LENGTH(v_name1) < LENGTH(v_name2)THEN
dbms_output.put_line (v_name2||'is longer then ' || v_name1);
ELSIF LENGTH(v_name1) = LENGTH(v_name2)THEN
  dbms_output.put_line (v_name2||'is equal then ' || v_name1);
ELSE
  dbms_output.put_line (v_name2||'is short then ' || v_name1);
END IF ;
END;


--case--
DECLARE
  season INT ;
aboutinfo VARCHAR2(50);
BEGIN
season := &season;
CASE season
WHEN 1 THEN
aboutinfo := season||' month : 1,2,3';
WHEN 2 THEN
aboutinfo := season||' month : 4,5,6';
WHEN 3 THEN
aboutinfo := season||' month : 7,8,9';
WHEN 4 THEN
aboutinfo := season||' month : 10,11,12';
  ELSE
aboutinfo := 'season error';
END CASE;
dbms_output.put_line(aboutinfo);
END;

--goto--
DECLARE
  v_result INT;
BEGIN
  FOR v_result IN 1..100 LOOP
IF v_result = 4 THEN
GOTO endpoint;
END IF;
dbms_output.put_line(v_result);
END LOOP;
<<endpoint>>
  dbms_output.put_line(v_result);
END;
循环：
--循环--
--循环变量
--循环条件
--循环体
--迭代部分

--loop..(循环体)..exit when(循环条件)..end loop--
DECLARE 
  sum_i INT := 0;
  i INT := 0;
BEGIN
LOOP
i := i+1;
sum_i := sum_i + i;
EXIT WHEN i = 100;
END  LOOP;
dbms_output.put_line(sum_i);
END;
--while(循环条件) loop..(循环体)..end loop--
DECLARE
  sum_i INT := 0;
i INT := 0;
BEGIN
WHILE i < 100 LOOP
i := i+1;
sum_i := sum_i + i;
END LOOP;
dbms_output.put_line(sum_i);
END;
--for(循环变量) in （循环条件） loop..(循环体)..end loop--
DECLARE
  sum_i INT := 0;
i INT := 0;
BEGIN
FOR i IN 1..100 LOOP
sum_i := sum_i + i;
END LOOP;
dbms_output.put_line(sum_i);
END;

4、游标

游标

类似于指针，可循环搭配，当与循环搭配时，依次读取存储的数据，类似指针。使用完毕记得关闭。

--游标--（指针）
--声明 CURSOR 游标名 is （一个查询表示游标指向）
--开启 OPEN 游标名
--读取 FETCH 游标名 INTO 变量（将游标指向读取出来传递给变量）
--cur_stu%FOUND （检查上一个FETCH是否有效传输，适用于先传递后输出）
--cur_stu%NOTFOUND （表示游标没有更多行）
--关闭 CLOSE cur_stu;（本案例中不需要，因为块结束时所有资源都会被自动清理，但在存储过程或函数中关闭游标是一个好习惯）
DECLARE
 CURSOR cur_stu IS SELECT sno,sname,sage FROM student WHERE sdept='jg';
 TYPE record_stu IS RECORD(
    var_sno student.sno%TYPE,
    var_sname student.sname%TYPE,
    var_sage student.sage%TYPE
 );
 stu_row record_stu;
BEGIN
OPEN cur_stu;
FETCH cur_stu INTO stu_row;
WHILE cur_stu%FOUND LOOP --检查上一个FETCH是否有效传输
dbms_output.put_line( 'id:'|| stu_row.var_sno || 'name:' || stu_row.var_sname || 'age:' || stu_row.var_sage);
FETCH cur_stu INTO stu_row;
END LOOP;
END;
隐式游标

在PL/SQL中，隐式游标（Implicit Cursor）是Oracle数据库自动为你管理的游标，你不需要显式地声明、打开、获取数据、关闭它。隐式游标主要用于处理DML（数据操纵语言）语句，如INSERT、UPDATE、DELETE，以及单行SELECT INTO语句。

当你执行一个DML语句时，Oracle会自动使用隐式游标来处理这个操作。隐式游标包含了被DML语句影响的行数和其他相关信息，如SQL%ROWCOUNT和SQL%FOUND、SQL%NOTFOUND等属性就是通过隐式游标来提供的。

隐式游标的属性

  • SQL%ROWCOUNT：返回最近一次DML语句影响的行数，或者从单行SELECT INTO语句中检索到的行数（如果SELECT语句成功执行）。
  • SQL%FOUND：如果最近一次DML语句影响了至少一行，或者单行SELECT INTO语句成功返回了一行数据，则SQL%FOUND的值为TRUE。
  • SQL%NOTFOUND：与SQL%FOUND相反，如果最近一次DML语句没有影响任何行，或者单行SELECT INTO语句没有返回任何行，则SQL%NOTFOUND的值为TRUE。
  • SQL%ISOPEN：对于隐式游标，这个属性总是返回FALSE，因为隐式游标是由Oracle自动管理的，你无法显式地打开或关闭它。

下面是一个使用隐式游标的示例，它展示了如何使用SQL%ROWCOUNT和SQL%FOUND属性：

DECLARE  
    v_rows_affected NUMBER;  
BEGIN  
    -- 执行一个DML语句，Oracle会自动使用隐式游标  
    DELETE FROM employees WHERE department_id = 50;  
  
    -- 获取受影响的行数  
    v_rows_affected := SQL%ROWCOUNT;  
  
    -- 判断是否有行被删除 （是否有行受影响，上述DML语句为DELETE删除） 
    IF SQL%FOUND THEN  
        DBMS_OUTPUT.PUT_LINE('Deleted ' || v_rows_affected || ' rows.');  
    ELSE  
        DBMS_OUTPUT.PUT_LINE('No rows were deleted.');  
    END IF;  
  
    -- 尝试从employees表中检索一个不存在的员工ID，以演示SQL%NOTFOUND  
    BEGIN  
        SELECT employee_id INTO v_rows_affected  
        FROM employees  
        WHERE employee_id = 999999; -- 假设这个ID不存在  
    EXCEPTION  
        WHEN NO_DATA_FOUND THEN  
            IF SQL%NOTFOUND THEN  
                DBMS_OUTPUT.PUT_LINE('No employee found with ID 999999.');  
            END IF;  
    END;  
END;

在这个示例中，DELETE语句和SELECT INTO语句都使用了隐式游标。对于DELETE语句，我们通过SQL%ROWCOUNT和SQL%FOUND属性来获取受影响的行数并判断是否有行被删除。对于SELECT INTO语句，我们使用了异常处理来捕获NO_DATA_FOUND异常，并通过SQL%NOTFOUND属性来确认确实没有找到数据。然而，在这个特定的SELECT INTO示例中，SQL%NOTFOUND实际上并没有被直接用在异常处理块之外，因为NO_DATA_FOUND异常已经足够说明问题了。不过，了解SQL%NOTFOUND的存在和用途仍然是有价值的。

练习：

BEGIN
UPDATE student SET sage = sage + 1 WHERE sdept='xx';
IF SQL%NOTFOUND THEN
dbms_output.put_line('not found any student');
ELSE
dbms_output.put_line(SQL%ROWCOUNT||'students has chaned');
END IF;
END;

5、异常处理

  • –编译异常：

在编译时出现问题，如：语法错误

  • –运行异常

在运行时出错，如：分母为0

  • 异常处理：

添加异常处理语句EXCEPTION

--对异常进行处理--
DECLARE
  v_vara VARCHAR(1);
  v_varb VARCHAR(4) := 'java';
BEGIN
  v_vara := v_varb;--出现异常，1空间无法存储4内容，跳到下方异常不在返回
EXCEPTION  --如果出现异常问题
WHEN value_error THEN  --问题是value_error
dbms_output.put_line('value error');--打印value error
END;


--自定义异常--
DECLARE
  v_data NUMBER;
  v_myexp EXCEPTION;--定义异常变量
BEGIN
v_data := &inputdata;
IF v_data>100 THEN--我们不希望data大于100，如果大于100，我们抛出异常
RAISE v_myexp;--抛出异常进入EXCEPTION
END IF;
dbms_output.put_line(v_data);--如果没有抛出，则输出data
EXCEPTION
WHEN OTHERS THEN--OTHERS 是一个特殊的异常处理器，它用来捕获所有未被前面特定异常处理器捕获的异常。使用 OTHERS 可以确保你的代码能够处理所有可能的异常，尽管这可能不是最佳实践，因为它可能隐藏了具体的错误细节。
dbms_output.put_line('MY EXCEPTION');
END;

五、存储过程与触发器

存储过程：

--存储过程
--将重复内容放在存储过程中，实现代码复用--
CREATE PROCEDURE my_proc1 IS
BEGIN 
INSERT INTO student(sno,sname,sage) VALUES('1004','zhangsan','20');
dbms_output.put_line('insert success');
END my_proc1;
BEGIN
 my_proc1 ;
END;
SELECT * FROM student;


--修改存储过程--
CREATE OR REPLACE PROCEDURE my_proc1 IS
BEGIN 
INSERT INTO student(sno,sname,sage) VALUES('1005','lisi','21');
dbms_output.put_line('insert success');
END my_proc1;
BEGIN
 my_proc1;
END;
SELECT * FROM student;
--删除--
DROP PROCEDURE my_proc1;

函数：

练习解读：

OR REPLACE–覆盖

FUNCTION–函数

TRIM(sdept)–去除sdept两端空格

ROUND(avg_age_result, 2); --保留两位小数

CREATE OR REPLACE FUNCTION get_avg_age(param_sdept VARCHAR2) RETURN NUMBER IS  
    avg_age_result NUMBER;  
BEGIN  
    SELECT AVG(sage) INTO avg_age_result FROM student WHERE TRIM(sdept) = param_sdept;  
      
    IF avg_age_result IS NULL THEN  
        -- 如果没有数据，返回0或者你可以选择其他合适的值  
        dbms_output.put_line('sdept not found or no data for this department');  
        RETURN 0;  
    ELSE  
        RETURN ROUND(avg_age_result, 2);  
    END IF;  
EXCEPTION  
    WHEN OTHERS THEN  
        -- 处理其他可能的异常  
        dbms_output.put_line('An error occurred: ' || SQLERRM);  
        RETURN NULL; -- 或者你可以根据需求返回其他值  
END;  
/  
  
DECLARE  
    avg_age NUMBER;  
BEGIN  
    avg_age := get_avg_age('jg');  
    dbms_output.put_line(avg_age);  
END;  
/

触发器：

–语句级触发器，针对一条DML语句而引发的触发器执行，无论多少行数据受影响，都只执行一次
–替换触发器，定义在视图上的触发器，当用户操作视图时执行替换触发器，实现对基表操作
–系统事件触发器，系统事件触发器（System Event Trigger）是由数据库系统事件（如数据库启动、关闭、用户登录、退出等）触发的特殊存储过程。这些触发器能够自动执行特定的操作，如记录事件日志、执行安全检查、更新系统状态等，从而帮助数据库管理员更有效地管理数据库。

--语句级触发器
--创建表--
CREATE TABLE student_log
(
       operate_tag VARCHAR2(10),
 operate_time DATE
)

--触发器--
CREATE OR REPLACE TRIGGER tri_student BEFORE INSERT OR UPDATE OR DELETE ON student
DECLARE
  var_tag VARCHAR2(10);
BEGIN
IF inserting(sname) THEN--如果插入sname
var_tag := 'insert';
ELSIF updating THEN     --如果有修改操作
var_tag := 'update';
ELSIF deleting THEN     --。。。
var_tag := 'delete';
END IF;
  INSERT INTO student_log VALUES(var_tag,SYSDATE);
END tri_student;

SELECT * FROM student_log;
SELECT * FROM student;
INSERT INTO student(sno,sname,sage) VALUES('1008','wangwu',20);
UPDATE student SET sage=sage+1 WHERE sno ='1008';
DELETE FROM student WHERE sno='1008'; 

--替换触发器
--视图
CREATE VIEW view_student_sc
AS SELECT s.sno,s.sname,sc.cno,sc.grade FROM student s ,sc WHERE s.sno=sc.sno;

SELECT * FROM sc;
SELECT * FROM view_student_sc;

--替换触发器--
CREATE OR REPLACE TRIGGER tri_insert_view
INSTEAD OF INSERT  --插入出发
ON view_student_sc --触发的视图
FOR EACH ROW
BEGIN
INSERT INTO student(sno,sname) VALUES(:new.sno,:new.sname);
INSERT INTO sc(sno,cno,grade) VALUES(:new.sno,:new.cno,:new.grade);
END tri_insert_view;

INSERT INTO view_student_sc VALUES('1009','wangmazi','1',100);
SELECT * FROM view_student_sc;

程序包：

包头+包体

包头：接口，有存储，函数等

包体：函数实现

--程序包--
--包头
CREATE OR REPLACE PACKAGE pack_student IS --包
  FUNCTION fun_avg_sage(num_sdept VARCHAR2) RETURN NUMBER;--函数
  PROCEDURE pro_regulage_sage(num_sdept VARCHAR2);--存储
END pack_student;


--包体
CREATE OR REPLACE PACKAGE BODY pack_student IS
  FUNCTION fun_avg_sage(num_sdept VARCHAR2) RETURN NUMBER IS
num_avg_sage NUMBER;
BEGIN
SELECT AVG(sage) INTO num_avg_sage FROM student WHERE trim(sdept)=num_sdept;
    RETURN (num_avg_sage);
EXCEPTION
WHERE no_data_found THEN
dbms_output.put_line('no this sdept');
RETURN 0;
END fun_avg_sage;
  PROCEDURE pro_regulage_sage(num_sdept VARCHAR2) IS
BEGIN
UPDATE student SET sage=sage+1 WHERE TRIM(sdept)=num_sdept;
END pro_regulage_sage;
END pack_student;

索引

  1. 快速存取数据：通过索引，数据库可以快速定位到表中的特定行，减少查询时间。
  2. 改善数据库性能：索引可以减少数据库在查询、排序和分组等操作时的I/O开销。
  3. 保证列值的唯一性：唯一索引可以确保表中不会有两行记录具有相同的索引键值。
  4. 减少排序和分组时间：在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。

在这里插入图片描述

分类：

B-Tree索引案例

场景描述：
假设有一个员工表employees，包含员工ID（emp_id）、姓名（name）和部门ID（department_id）等字段。经常需要根据部门ID查询员工信息。

索引创建：

CREATE INDEX idx_department ON employees(department_id);

这条SQL语句在employees表的department_id字段上创建了一个B-Tree索引，以优化基于部门ID的查询。

效果：
创建B-Tree索引后，当执行如SELECT * FROM employees WHERE department_id = 10;的查询时，数据库可以利用索引快速定位到部门ID为10的员工记录，从而提高查询效率。

位图索引案例

场景描述：
继续使用上述employees表，但这次假设有一个性别字段（gender），且性别只有“男”和“女”两个值。

索引创建：

CREATE BITMAP INDEX idx_gender ON employees(gender);

这条SQL语句在employees表的gender字段上创建了一个位图索引，以优化基于性别的查询。

效果：
位图索引特别适用于低基数（即不同值很少）的列。在这个例子中，由于性别只有两种可能的值，使用位图索引可以显著提高基于性别的查询效率。

函数索引案例

场景描述：
假设经常需要根据员工的入职年份进行查询，但入职日期（hire_date）字段是DATE类型，包含年、月、日信息。

索引创建：

CREATE INDEX idx_hire_year ON employees(EXTRACT(YEAR FROM hire_date));

这条SQL语句创建了一个函数索引，它基于hire_date字段的年份部分。这允许数据库直接使用索引来加速基于入职年份的查询。

效果：
创建函数索引后，当执行如SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2020;的查询时，数据库可以利用索引快速定位到入职年份为2020年的员工记录。

复合索引案例

场景描述：
假设经常需要根据部门ID和姓名同时查询员工信息。

索引创建：

CREATE INDEX idx_dept_id_name ON employees(department_id, name);

这条SQL语句在employees表的department_id和name字段上创建了一个复合索引。

效果：
复合索引适用于在多个字段上同时进行查询的场景。在这个例子中，如果查询条件同时包含部门ID和姓名，那么这个复合索引将非常有效。此外，Oracle还会根据查询条件中列的顺序和索引中的列顺序来优化查询计划。

六、用户管理与权限分配

Oracle安全

1、用户向数据库提供身份识别

2、输入密码

3、验证密码

用户与模式

模式：用户拥有对象的集合

创建用户

--建立用户--
CREATE USER wangliukun IDENTIFIED BY 123456
  DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp;
--增加上限
ALTER USER wangliukun QUOTA 20m ON USERS;
--修改密码
ALTER USER wangliukun IDENTIFIED BY 1111111;
--解锁
ALTER USER wangliukun ACCOUNT UNLOCK;
--删除
  DROP USER wangliukun[Cascade] --Cascade,一并删除用户拥有的对象

管理用户

创建的用户不能操作数据库，需要给权限，系统权限，对象权限

--系统权限
GRANT connect TO wangliukun [WITH ADMIN OPTION]--将连接权限给wangliukun 其中 WITH ADMIN OPTION可将权限交给他人
  --对象权限
GRANT select,insert,delete,UPDATE ON scott.student TO wangliukun;
--回收
REVOKE select,insert,delete,UPDATE ON scott.student FROM wangliukun;
    --删除用户
DROP USER wangliukun;

--进入用户后如何使用其他用户表
select * from SCOTT.student;

角色管理

用户：一个独立的数据库实体，包括了一组权限，包含一个或多个权限的集合

--角色--
CREATE ROLE wanghr IDENTIFIED BY 123456; --创建角色wanghr
GRANT CREATE VIEW,CREATE TABLE TO wanghr; --给角色赋权

--查看权限
select * from role_sys_privs where role = 'wanghr';  --role_sys_privs 是存放角色权限的数据字典

--给用户角色权限
GRANT wanghr TO wangliukun  

--删除角色
drop role wanghr;
--删除用户
DROP USER wangliukun;

资源配置

使用profile管理密码

1、账户锁定：连续输入两次

2、锁定时间：30天警告，33天强制修改密码

3、密码历史：密码可重复次数，可重用时间

4、密码复杂程度

--资源管理--
--密码文件
CREATE PROFILE PASSWORD_LIFE_TIME LIMIT PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 3;  --第一个PASSWORD_LIFE_TIME为文件名
--将密码文件内容限制加到用户
ALTER USER wangliukun PROFILE PASSWORD_LIFE_TIME;

使用profile管理资源

1、一次会话使用cpu时间

2、用户发起会话总次数

3、会话连接时间

七、数据库控制

事务

性质：

原子性，一致性，隔离性，持久性

--事务--
SELECT * FROM student;
INSERT INTO student(sno,sname) VALUES('111','xiaoming');
SELECT * FROM student;
COMMIT;--提交

--回滚
SELECT * FROM student;
INSERT INTO student(sno,sname) VALUES('222','xiaohong');
SELECT * FROM student;
ROLLBACK;
SELECT * FROM student;


--事务回退点
SAVEPOINT s1;
SELECT * FROM student;
UPDATE student SET sname='mm' WHERE sno='111';
SAVEPOINT s2;
UPDATE student SET sname='xxx' WHERE sno='111';
ROLLBACK TO s2;
COMMIT;

--COMMIT 提交
--ROLLBACK 回滚
--SAVEPOINT s1 定点
--ROLLBACK TO s2 定点回滚（记得提交）

锁

Oracle中的锁是一种机制，用于管理对共享资源的并发访问，以保证多用户环境下数据库的完整性和一致性。这些锁可以应用于用户对象（如表或表行）和系统对象（如共享数据结构以及数据字典行）。

一、锁的类型

从不同的角度，Oracle中的锁可以分为多种类型：

按用户与系统划分

  • 隐式锁（自动锁）：Oracle系统自动添加和释放的锁，无需用户手动干预。这类锁包括DML锁（数据锁）和DDL锁等。DML锁用于控制事务并发中的数据操作，如INSERT、UPDATE、DELETE等操作会自动获取行级锁（TX）或表级锁（TM）。DDL锁则在执行DDL语句（如CREATE、ALTER、DROP等）时自动添加，以保护表结构不被其他会话修改。
  • 显式锁：用户手动添加的锁，通过LOCK TABLE语句实现。显式锁允许用户指定锁的模式，如行共享锁（RS）、行排他锁（RX）、共享锁（S）、共享行排他锁（SRX）和排他锁（X）等。

按锁的性质划分

  • 共享锁（Shared Lock, S锁）：允许多个事务同时读取同一资源，但不能进行写操作。共享锁之间互不影响，读操作可以并发执行。
  • 排他锁（Exclusive Lock, X锁）：当一个事务获取了排他锁后，其他事务无法获取该资源的任何锁（包括共享锁和排他锁），直到该锁被释放。排他锁保证了在写操作期间数据的完整性。
二、锁的级别

Oracle中的锁还可以按照粒度不同分为多种级别：

行级锁（Row-Level Locks）

  • 行级锁是最细粒度的锁，只在需要修改的数据行上加锁。行级锁可以最大程度地支持并发处理，因为多个事务可以同时对不同行进行读写操作。

表级锁（Table-Level Locks）

  • 表级锁是对整张表进行加锁，当某个事务需要对整个表进行修改操作时，会申请表级锁。表级锁会阻塞其他事务对该表的并发访问，因此并发性能较低。
三、锁的特性

锁的自动管理

  • Oracle数据库能够自动管理锁，根据事务的需求和资源的状态自动决定锁的类型和级别。在大多数情况下，用户无需手动干预锁的管理。

锁的兼容性

  • 不同类型的锁之间具有不同的兼容性。例如，共享锁可以与多个共享锁共存，但排他锁则与其他任何类型的锁不兼容。

死锁处理

  • 当两个或多个事务相互等待对方持有的锁时，会发生死锁。Oracle数据库能够自动检测死锁，并选择一个事务进行回滚，以释放其持有的锁资源，从而解决死锁问题。

n;
–删除用户
DROP USER wangliukun;


```sql
--进入用户后如何使用其他用户表
select * from SCOTT.student;

角色管理

用户：一个独立的数据库实体，包括了一组权限，包含一个或多个权限的集合

--角色--
CREATE ROLE wanghr IDENTIFIED BY 123456; --创建角色wanghr
GRANT CREATE VIEW,CREATE TABLE TO wanghr; --给角色赋权

--查看权限
select * from role_sys_privs where role = 'wanghr';  --role_sys_privs 是存放角色权限的数据字典

--给用户角色权限
GRANT wanghr TO wangliukun  

--删除角色
drop role wanghr;
--删除用户
DROP USER wangliukun;

资源配置

使用profile管理密码

1、账户锁定：连续输入两次

2、锁定时间：30天警告，33天强制修改密码

3、密码历史：密码可重复次数，可重用时间

4、密码复杂程度

--资源管理--
--密码文件
CREATE PROFILE PASSWORD_LIFE_TIME LIMIT PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 3;  --第一个PASSWORD_LIFE_TIME为文件名
--将密码文件内容限制加到用户
ALTER USER wangliukun PROFILE PASSWORD_LIFE_TIME;

使用profile管理资源

1、一次会话使用cpu时间

2、用户发起会话总次数

3、会话连接时间

七、数据库控制

事务

性质：

原子性，一致性，隔离性，持久性

--事务--
SELECT * FROM student;
INSERT INTO student(sno,sname) VALUES('111','xiaoming');
SELECT * FROM student;
COMMIT;--提交

--回滚
SELECT * FROM student;
INSERT INTO student(sno,sname) VALUES('222','xiaohong');
SELECT * FROM student;
ROLLBACK;
SELECT * FROM student;


--事务回退点
SAVEPOINT s1;
SELECT * FROM student;
UPDATE student SET sname='mm' WHERE sno='111';
SAVEPOINT s2;
UPDATE student SET sname='xxx' WHERE sno='111';
ROLLBACK TO s2;
COMMIT;

--COMMIT 提交
--ROLLBACK 回滚
--SAVEPOINT s1 定点
--ROLLBACK TO s2 定点回滚（记得提交）

锁

Oracle中的锁是一种机制，用于管理对共享资源的并发访问，以保证多用户环境下数据库的完整性和一致性。这些锁可以应用于用户对象（如表或表行）和系统对象（如共享数据结构以及数据字典行）。

一、锁的类型

从不同的角度，Oracle中的锁可以分为多种类型：

按用户与系统划分

  • 隐式锁（自动锁）：Oracle系统自动添加和释放的锁，无需用户手动干预。这类锁包括DML锁（数据锁）和DDL锁等。DML锁用于控制事务并发中的数据操作，如INSERT、UPDATE、DELETE等操作会自动获取行级锁（TX）或表级锁（TM）。DDL锁则在执行DDL语句（如CREATE、ALTER、DROP等）时自动添加，以保护表结构不被其他会话修改。
  • 显式锁：用户手动添加的锁，通过LOCK TABLE语句实现。显式锁允许用户指定锁的模式，如行共享锁（RS）、行排他锁（RX）、共享锁（S）、共享行排他锁（SRX）和排他锁（X）等。

按锁的性质划分

  • 共享锁（Shared Lock, S锁）：允许多个事务同时读取同一资源，但不能进行写操作。共享锁之间互不影响，读操作可以并发执行。
  • 排他锁（Exclusive Lock, X锁）：当一个事务获取了排他锁后，其他事务无法获取该资源的任何锁（包括共享锁和排他锁），直到该锁被释放。排他锁保证了在写操作期间数据的完整性。
二、锁的级别

Oracle中的锁还可以按照粒度不同分为多种级别：

行级锁（Row-Level Locks）

  • 行级锁是最细粒度的锁，只在需要修改的数据行上加锁。行级锁可以最大程度地支持并发处理，因为多个事务可以同时对不同行进行读写操作。

表级锁（Table-Level Locks）

  • 表级锁是对整张表进行加锁，当某个事务需要对整个表进行修改操作时，会申请表级锁。表级锁会阻塞其他事务对该表的并发访问，因此并发性能较低。
三、锁的特性

锁的自动管理

  • Oracle数据库能够自动管理锁，根据事务的需求和资源的状态自动决定锁的类型和级别。在大多数情况下，用户无需手动干预锁的管理。

锁的兼容性

  • 不同类型的锁之间具有不同的兼容性。例如，共享锁可以与多个共享锁共存，但排他锁则与其他任何类型的锁不兼容。

死锁处理

  • 当两个或多个事务相互等待对方持有的锁时，会发生死锁。Oracle数据库能够自动检测死锁，并选择一个事务进行回滚，以释放其持有的锁资源，从而解决死锁问题。

原文地址：https://blog.csdn.net/wang_8218/article/details/140332261

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024