网站建设需要啥,静态网站建设的技术运用,企业设计方案,北京做校园的网站Oracle
一、简介#xff1a;
特点#xff1a; 多用户、大事务量的事务处理 数据安全性和完整性控制 支持分布式数据处理 可以移植性
Oracle 19c 安装
登录甲骨文#xff0c;安装Oracle 解压压缩包 安装 完毕
此处账户#xff1a;qfedu
密码#xff1a;wang8218.…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 PoolLibrary 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 的大小对数据库的读取速度有直接的影响。
3Redo Log Buffer 重做日志缓冲区
日志条目Redo Entries 记录了数据库的所有修改信息(包括 DML 和 DDL,一条 Redo Entries 记录一次对数据库的改变 为的是数据库恢复。日志条目首先产生于日志缓冲区。日志缓冲区较小它是以字节为单位的它极其重要。在 Database Buffer Cache 中一旦有数据更改马上写入 Redo Log Buffer在内存中保留一段时间后会写入磁盘然后归档3级结构。
4 Large Pool 大池可选
为了进行大的后台进程操作而分配的内存空间与 shared pool 管理不同主要用于共享服 务器的 session memoryRMAN 备份恢复以及并行查询等。
5Java Pool Java 池(可选
为了 java 虚拟机及应用而分配的内存空间包含所有 session 指定的 JAVA 代码和数据。
6Stream Pool 流池(可选) 为了 stream process 而分配的内存空间。stream 技术是为了在不同数据库之间共享数据 因此它只对使用了 stream 数据库特性的系统是重要的。
Instance-Background process
在正式介绍 Background Process 之前先简单介绍 Oracle 的 Process 类型。
Oracle Process 有三种类型前台进程
User Process
属于客户端的 Process客户端要与服务器连接在客户端启动起来的进程就是 User Process 一般分为三种形式sql*plus, 应用程序web 方式OEMUser Process 不能直接访问Oracle 。
Server Process
属于服务器端 Process用于接应User Process 发出的请求给用户分配 PGA程序全局区。
background process
Oracle Instance的重要组成部分。下文会对其做详细讲解。
【User Process 不能直接访问 Oracle必须通过相应的 Server Process 访问实例进而访问数据库。】
1PMONProcess Monitor后台进程
PMON的主要作用如下 监控各个Oracle后台进程是否正常发现异常的进程将其清除掉重新生成该进程 监控空闲会话是否到达阀值 动态注册监听。
2SMONSystem Monitor后台进程
SMON的主要作用如下
当Oracle运行时突然宕机下次启动需要实例恢复Instance RecoverySMON负责实例恢复的全程监控当Oracle运行时突然宕机在下一次启动Oracle Instance的时候它里面一些没有释放的资源会由SMON进行清理一些事务失败的时候也由SMON进行清理内存空间很分散不连续时需要SMON将这个分散的空间整合起来释放不再使用的临时段Segment。
3DBWRDatabase Writer后台进程
DBWn是Oracle中工作最繁重的进程。主要作用如下 将 Database Buffer Cache 中的脏块Dirty Buffer写到 Data File 中 释放Data Buffer Cache空间。
注意以下几种情况发生时都会触发DBWR Process将 Database Buffer Cache 的内容写到Data Files
Checkpoint OccursDirty Buffer reach thresholdThere are no free BuffersTimeout occursRAC ping request is madeTablespace OFFLINETablespace READ ONLYTable DROP or TRUNCATETablespace BEGINBACKUP
4LGWRLOG Writer后台进程
Oracle Instance中只有一个LGWR Process这个Process的工作和DBWR Process类似。主要作用如下
将 Redo Log Buffer 中的内容写入到 Redo Log Files 中必须在 DBWR 写脏块之前写入日志。
注意以下几种情况发生时都会触发LGWR Process将 Redo Log Buffer 中的内容写到 Redo Log Files
At CommitWhen one-third fullWhen there is 1 MB of redoEvery three secondsBefore DBWn writes
5CKPTCheckpoint后台进程
CKPT的主要作用如下
生成检查点 通知或督促 DBWR 写脏块;
*完全检查点保证数据一致性。 *增量检查点不断更新控制文件中的检查点位置当发生实例崩溃时可以尽量缩短实例恢复的时间。
在Data File的文件头更新检查点信息;在Control File中更新检查点的信息。
6ARCnArchiver后台进程
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。
1Data Files数据文件
Data Files 就是用于存储数据的Table 中的数据都是保存在 Data Files 中的。1个 Data File 对应磁盘上的 1 个物理操作系统文件。Data Files 中频繁访问的数据块缓存在 Database Buffer Cache 中。新的数据块不会立刻写出到 Data Files而是在 DBWR处于活动状态时再写出。
2Control Files控制文件
Oracle为了操作Data File提供了一些 Control Files这些 Control Files 主要是记录数据库的一些控制信息。用于维护数据库对象的状态维护数据库的元数据数据库自身物理结构的数据数据库名称、数据库创建时间、所有数据文件和重做日志文件的名称及位置。
3Redo Log Files重做日志文件
Redo Log Files 记录着数据库的改变如果向数据库中放入数据或者是修改里面的数据只要对数据库作了修改insertdeleteupdate那么就要将修改之前的状态、修改之后的状态都记录在 Redo Log Files 中其作用就是恢复Data File。
例如数据库有一个事务需要提交但是提交失败了事务就要回滚那么事务回滚的依据就来自于这个 Redo Log Files。Redo Log Files 中记录着数据库的改变关于这个事务的改变如果需要回滚就需要把Redo Log Files中的数据取出来依照 Redo Log Files 中的数据把 Data Files 恢复到修改之前的状态。
有 3 种状态Active、Inactive、Current 。
4Parameter File参数文件更多内容
任何一个数据库都必须要有参数文件这个参数文件规定了Oracle中的一些基本的参数、初始化的参数的值。
5Archived Log Files归档日志文件
Archived Log Files 和 Redo Log Files 是相辅相成的Redo Log Files 其实是一个反复利用的过程会有几个一般为3个固定的文件这些固定文件会被依次使用用满了以后Oracle就会再次写这个文件头就把以前的东西冲掉了。为了进一步加强数据库的备份恢复能力在覆盖之前把这些修改的信息都归档到 Archived Log Files 中。
6Password File密码文件
用户客户端连接到后台数据库系统时候存储口令的。
Oracle 的 Memory Structure 实际上包含两部分内容SGA和PGA SGASystem Global Area系统全局区 PGAProgram 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_column20065from20065)软件系统都允许使用编程函数在结果集中进行导航比如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 Persons2.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然后是任意字符然后是 erSELECT * 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_name7.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_USASQL 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 PersonsIN 子句可用于向另一个数据库中拷贝表SELECT *INTO Persons IN Backup.mdbFROM Persons可以在最后添加 WHERE 子句也能使用join从一个以上的表中选取数据10.CREATE DATABASE 语句
CREATE DATABASE 用于创建数据库。CREATE DATABASE database_name11.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 约束请使用下列 SQLALTER TABLE PersonsADD UNIQUE (Id_P)如需命名 UNIQUE 约束并定义多个列的 UNIQUE 约束请使用下面的 SQL 语法ALTER TABLE PersonsADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)如需撤销 UNIQUE 约束请使用下面的 SQLALTER 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 KEYCREATE 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_P0)
)或者CREATE TABLE Persons(Id_P int NOT NULL CHECK (Id_P0),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 约束请使用下面的 SQLALTER TABLE PersonsALTER (COLUMN) City SET DEFAULT SANDNES如需撤销 DEFAULT 约束请使用下面的 SQLALTER TABLE PersonsALTER (COLUMN) City DROP DEFAULT13.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_nameDROP 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_name16.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_INCREMENT100要在 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 DiscontinuedNo我们可以查询上面这个视图
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 DiscontinuedNo您可以通过 DROP VIEW 命令来删除视图。
DROP VIEW view_name18.SQL 日期 数据库常用sql语句总结
MySQL](https://cloud.tencent.com/product/cdb?from_column20065from20065) 使用下列数据类型在数据库中存储日期或日期/时间值· 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返回expr2expr1不为NULL返回expr1。
注意两者的类型要一致
20.SQL ANY
在SQL查询中ANY 可以与比较运算符如 , , , 等一起使用来测试一个值是否满足子查询结果集中的任意一个值。为
SELECT sname,sage
FROM student WHERE SDEPT !jg AND sage ANY (
SELECT sage FROM student WHERE sdeptjg
)查找sdept非jg中满足小于jg中的其中一个或多个sage的sname以及sage。
21.SQL ALL
在SQL查询中ALL 可以与比较运算符如 , , , 等一起使用来测试一个值是否满足子查询结果集中的所有值。为
SELECT sname,sage FROM student WHERE SDEPT jg AND
sageall(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 cno1
)三、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 TABLE student 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,heyysina.com );
insert into STUDENT VALUES (181251111,敬横江,男,14,jingsina.com );
insert into STUDENT values(181251211,梁一苇, 女,24,bing126.com );
insert into STUDENT values (181352222,凌浩风, 女,23,tang163.com );
insert into STUDENT values (181372215,赵临江,男,14,ping163.com );
insert into STUDENT values(191111330,崔依歌,女,34,cui126.com );
insert into STUDENT values (191121072,宿沧海, 男,13,su12163.com);
insert into STUDENT values (191121108,韩山川, 男,15,han163.com );
insert into STUDENT values (191222067,封月明, 女,16,jiao126.com);
insert into STUDENT values(191235697,赵既白, 女,18,pingan163.com);
insert into STUDENT values(191261307,梅惟江, 女,20,zhu163.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的snosname--
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 sage20 AND sage23;
--2023--
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 cno3;
--max--
SELECT max(grade) FROM sc WHERE cno1;
--1001的平均绩点--
SELECT avg(CCREDIT) FROM COURSE WHERE cno IN (SELECT cno FROM sc WHERE sno1001)
--sum--
SELECT sum(grade) FROM sc WHERE sno1001;
--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.cnoc1.cpno;
--外连接左--
SELECT * FROM student LEFT JOIN sc ON student.snosc.sno;
--外连接右--
SELECT * FROM student RIGHT JOIN sc on student.SNOsc.SNO;
--内连接--
SELECT * FROM student INNER JOIN sc on student.SNOsc.SNO;
--内连接等同于--
SELECT * FROM student,sc WHERE student.SNOsc.SNO;
--查询每个学生的snosname,cname,grade
SELECT s.sno,s.sname,c.cname,sc.grade FROM student s,sc,course c
WHERE s.snosc.sno AND sc.cnoc.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.cnosc.CNO ;
--子查询--
SELECT * FROM sc WHERE sno IN SELECT sno FROM student WHERE sname何白露;
--join代替子查询--
SELECT sc.* FROM student s JOIN sc ON s.snosc.sno AND s.sname何白露;
--查选课”信息系统“学生的snosname--
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 sdeptjg
);
--min函数--
SELECT min(sage) FROM student WHERE SDEPT jg;
--比所有jg学生年龄小--
SELECT sname,sage FROM student WHERE SDEPT jg AND
sageall(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 cno1
);
--或者--
SELECT * FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno1
);
--查询非计算机比计算机任意1个学生年龄小姓名年龄--
SELECT sage,sname FROM STUDENT WHERE sdeptjg AND sage ANY (SELECT sage FROM student WHERE SDEPTjg
);
--查询非计算机比计算机所有学生年龄小姓名年龄--
SELECT sname,sage FROM student WHERE SDEPTjg sage all(SELECT sage FROM student WHERE SDEPT jg
);
--查询‘牛牛’在同一系--
SELECT * FROM student s1 WHERE EXISTS (SELECT * FROM student s2 WHERE s1.SDEPTs2.sdept AND s2.sname牛牛
);
4.函数
--查询jg学及sage19--
SELECT * FROM student WHERE SDEPT jg OR sage19;
--或者union--
SELECT * FROM student WHERE SDEPT jg
UNION
SELECT * FROM student WHERE sage19;
--查选了1或2的学生--
SELECT * FROM sc WHERE cno1
UNION
SELECT * FROM sc where cno2;
--交集--
SELECT * FROM student WHERE SDEPT jg
INTERSECT
SELECT * FROM student WHERE sage19;
--1,2--
SELECT * FROM sc WHERE cno1
INTERSECT
SELECT * FROM sc WHERE cno2;
--差集--
SELECT * FROM sc WHERE cno1
MINUS
SELECT * FROM sc WHERE cno2;
--找出超过选修成绩平均分的课程号--
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 cno1) sc1
WHERE student.SNO sc1.sno;---------------------------
SELECT * FROM STUDENT s ;
SELECT * FROM sc;
--插入--
INSERT INTO STUDENT VALUES (1002,KK,男,21,jg,null);
--或者--**
INSERT INTO STUDENTsno,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 sage22 WHERE sno 1001;
--all--
UPDATE student SET sagesage1;
--成绩0--
UPDATE sc set grade0 WHERE sno IN (SELECT sno FROM student WHERE sdeptjg
);
--删除delet--
DELETE FROM student WHERE sno191261307;
--删表--
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 sno1002;
--查找系院为空的信息 is null--
SELECT * FROM student WHERE SDEPT IS NULL ;
--填写信息1003LL的sdept--
UPDATE STUDENT SET SDEPT jg WHERE sno1003;--------------函数-----------------------数学--临时表,chr90Z
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/SQLProcedural Language/Structured Query Language是Oracle数据库的过程化语言扩展它将SQL的数据操纵功能与过程化语言的数据处理功能结合起来形成了一种强大的编程语言。以下是对PL/SQL的简介
一、基本概念
定义PL/SQL是过程语言Procedural Language与结构化查询语言SQL结合而成的编程语言是Oracle数据库对SQL语句的扩展。它允许在SQL语句中嵌入复杂的程序结构如条件语句、循环、异常处理等。特点PL/SQL结合了SQL的数据处理能力和过程语言的控制能力可以创建存储过程、触发器和程序包给SQL语句的执行添加程序逻辑。它支持面向对象编程OOP具有可移植性、灵活性和安全性。
二、主要功能和优势
模块化PL/SQL可以将一组SQL语句封装成一个独立的单元如过程、函数或包从而提高代码的重用性和可维护性。过程化控制通过引入条件语句、循环等控制结构PL/SQL能够处理复杂的业务逻辑。性能优化与单独执行SQL语句相比PL/SQL程序可以减少网络交互次数提高数据处理效率。此外PL/SQL还支持编译存储进一步提升了性能。安全性通过存储过程等机制PL/SQL可以限制用户对数据的直接访问从而增强数据库的安全性。可移植性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数据类型主要分为数值类型、字符类型、日期时间类型、大对象类型等几大类。 数值类型 NUMBER用于存储可变精度的数值可以指定精度precision和标度scale即存储的最大位数和小数点后的位数。INTEGER整数类型是NUMBER类型的一种特殊形式用于存储不带小数的整数。FLOAT浮点数类型用于存储近似值具有比NUMBER更大的范围和较低的精度要求。BINARY_FLOAT和BINARY_DOUBLE二进制浮点数类型分别用于存储单精度和双精度浮点数提供高精度的计算和存储能力。DECIMAL或DEC用于存储带有指定精度和标度的小数值与NUMBER类型相似但更明确地表示其用途。 字符类型 CHAR定长字符类型用于存储指定长度的字符串无论实际值的长度如何都会占用指定长度的存储空间。 char(x) 固定长度x x32767 VARCHAR2可变长度字符类型用于存储可变长度的字符串只占用实际需要的存储空间。 varcher2x上限为x个单位x32767 NCHAR和NVARCHAR2分别用于存储定长和可变长度的Unicode字符适用于多语言环境。 LONG长字符串数据类型但Oracle推荐使用CLOB类型来替代LONG类型因为CLOB类型更灵活。 日期时间类型 DATE用于存储日期和时间值包含年、月、日、时、分和秒的信息。 (yyy-mm-dd hh:mi:ss) TIMESTAMP时间戳类型用于存储日期和时间并提供了更高的精确度可以存储小数秒。 INTERVAL时间间隔类型用于存储两个日期或时间之间的差异可以表示年、月、日、小时、分钟或秒之间的差异。 大对象类型 BLOB二进制大对象类型用于存储二进制数据如图像、视频和音频文件。CLOB字符大对象类型用于存储大量的字符数据包括长文本和文档。NCLOB大规模Unicode字符数据类型与CLOB类似但用于存储Unicode字符数据。 其他类型 BOOLEAN布尔类型用于存储真或假的值在Oracle中通过PL/SQL程序的特殊数据类型实现。RAW原始二进制类型用于存储不需要进行字符集转换的二进制数据。
二、Oracle变量和常量
变量 变量名 数据类型 变量值 变量是存储在内存中以获得值的数据存储结构能被PL/SQL块引用。在使用变量前需要先声明声明的格式通常包括变量名、数据类型和可选的初始值。null变量在程序运行的过程中其值可以发生变化。 常量 常量名 constant 数据类型 : 常量值 常量与变量相似但其值在程序运行过程中不可改变。定义常量时必须包括关键字CONSTANT并指定数据类型和常量值。
三、特殊数据类型声明方式
%TYPE可以声明一个与指定列相同的数据类型它通常紧跟在指定列名的后面。使用%TYPE定义变量有两个好处一是用户不必查看表中各个列的数据类型就可以确保所定义的变量能够存储检索的数据二是对表中已有列的数据类型进行修改时用户不必考虑对已定义的变量所使用的数据类型进行更改因为%TYPE类型的变量会根据列的实际类型自动调整自身的数据类型。%RECORD: 类似于结构体在 PL/SQL 和其他编程环境中RECORD 通常被用作一种自定义的数据类型用于将多个不同类型的数据项组合成一个单一的结构化单位。这通常通过 TYPE ... IS RECORD() 语句完成。%ROWTYPE结合了“%TYPE类型”和“记录类型”变量的优点它可以根据数据表中行的结构定义一种特殊的数据类型用来存储从数据表中检索到的一行数据。
四、练习
变量/常量 --变量--
DECLARE var_countrynname VARCHAR2(50);
BEGINvar_countrynname:China;dbms_output.put_line(Country name is : || var_countrynname);
END;DECLAREv_a NUMBER : 100;v_b NUMBER : 100;
BEGINv_b : 30;dbms_output.put_line(The result is: || (v_av_b) );
END; --常量--
DECLARE var_c CONSTANT VARCHAR2(50) : ch;
BEGINvar_c :China;--会报错dbms_output.put_line(Country name is : || var_c);
END;特殊类型:
--%TYPE类型--
DECLAREvar_name student.sname%TYPE;var_sdept student.sdept%TYPE;
BEGINSELECT sname,sdept INTO var_name,var_sdept FROM student WHERE sno1001;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;BEGINSELECT sname,sdept INTO stuinfo FROM student WHERE sno1003;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此行的所有值
BEGINSELECT * INTO rowvar_stu FROM student WHERE sno18122221;dbms_output.put_line(The name is :|| rowvar_stu.sname||,sdept is :||rowvar_stu.sdept);
END;
3、流程控制
异常
--输入 x : x--
DECLAREa int;b INT;c NUMBER;
BEGINdbms_output.put_line(please input a value for a);a : a;dbms_output.put_line(please input b value for b);b : b;c : (ab)/(a-b);dbms_output.put_line(c);
--异常处理EXCEPTION.. when(条件)..then--
EXCEPTIONWHEN zero_divide THEN
--如果分母为0返回 Divisor can not be zero--dbms_output.put_line(Divisor can not be zero);
END;
判断
--判断if..elsif..else--
DECLAREv_name1 VARCHAR2(30);v_name2 VARCHAR2(30);
BEGINv_name1 : qf;v_name2 : qfedu;IF LENGTH(v_name1) LENGTH(v_name2)THENdbms_output.put_line (v_name2||is longer then || v_name1);ELSIF LENGTH(v_name1) LENGTH(v_name2)THENdbms_output.put_line (v_name2||is equal then || v_name1);ELSEdbms_output.put_line (v_name2||is short then || v_name1);END IF ;
END;--case--
DECLAREseason INT ;aboutinfo VARCHAR2(50);
BEGINseason : season;CASE seasonWHEN 1 THENaboutinfo : season|| month : 1,2,3;WHEN 2 THENaboutinfo : season|| month : 4,5,6;WHEN 3 THENaboutinfo : season|| month : 7,8,9;WHEN 4 THENaboutinfo : season|| month : 10,11,12;ELSEaboutinfo : season error;END CASE;dbms_output.put_line(aboutinfo);
END;--goto--
DECLAREv_result INT;
BEGINFOR v_result IN 1..100 LOOPIF v_result 4 THENGOTO endpoint;END IF;dbms_output.put_line(v_result);END LOOP;endpointdbms_output.put_line(v_result);
END;循环
--循环--
--循环变量
--循环条件
--循环体
--迭代部分--loop..(循环体)..exit when(循环条件)..end loop--
DECLARE sum_i INT : 0;i INT : 0;
BEGINLOOPi : i1;sum_i : sum_i i;EXIT WHEN i 100;END LOOP;dbms_output.put_line(sum_i);
END;
--while(循环条件) loop..(循环体)..end loop--
DECLAREsum_i INT : 0;i INT : 0;
BEGINWHILE i 100 LOOPi : i1;sum_i : sum_i i;END LOOP;dbms_output.put_line(sum_i);
END;
--for(循环变量) in 循环条件 loop..(循环体)..end loop--
DECLAREsum_i INT : 0;i INT : 0;
BEGINFOR i IN 1..100 LOOPsum_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;本案例中不需要因为块结束时所有资源都会被自动清理但在存储过程或函数中关闭游标是一个好习惯
DECLARECURSOR cur_stu IS SELECT sno,sname,sage FROM student WHERE sdeptjg;TYPE record_stu IS RECORD(var_sno student.sno%TYPE,var_sname student.sname%TYPE,var_sage student.sage%TYPE);stu_row record_stu;
BEGINOPEN 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的存在和用途仍然是有价值的。
练习
BEGINUPDATE student SET sage sage 1 WHERE sdeptxx;IF SQL%NOTFOUND THENdbms_output.put_line(not found any student);ELSEdbms_output.put_line(SQL%ROWCOUNT||students has chaned);END IF;
END;
5、异常处理
–编译异常
在编译时出现问题如语法错误
–运行异常
在运行时出错如分母为0
异常处理
添加异常处理语句EXCEPTION
--对异常进行处理--
DECLAREv_vara VARCHAR(1);v_varb VARCHAR(4) : java;
BEGINv_vara : v_varb;--出现异常1空间无法存储4内容跳到下方异常不在返回
EXCEPTION --如果出现异常问题WHEN value_error THEN --问题是value_errordbms_output.put_line(value error);--打印value error
END;--自定义异常--
DECLAREv_data NUMBER;v_myexp EXCEPTION;--定义异常变量
BEGINv_data : inputdata;IF v_data100 THEN--我们不希望data大于100如果大于100我们抛出异常RAISE v_myexp;--抛出异常进入EXCEPTIONEND IF;dbms_output.put_line(v_data);--如果没有抛出则输出data
EXCEPTIONWHEN 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;
BEGINmy_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;
BEGINmy_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
DECLAREvar_tag VARCHAR2(10);
BEGINIF inserting(sname) THEN--如果插入snamevar_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 sagesage1 WHERE sno 1008;
DELETE FROM student WHERE sno1008; --替换触发器
--视图
CREATE VIEW view_student_sc
AS SELECT s.sno,s.sname,sc.cno,sc.grade FROM student s ,sc WHERE s.snosc.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
BEGININSERT 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 ISFUNCTION fun_avg_sage(num_sdept VARCHAR2) RETURN NUMBER ISnum_avg_sage NUMBER;BEGINSELECT AVG(sage) INTO num_avg_sage FROM student WHERE trim(sdept)num_sdept;RETURN (num_avg_sage);EXCEPTIONWHERE no_data_found THENdbms_output.put_line(no this sdept);RETURN 0;END fun_avg_sage;PROCEDURE pro_regulage_sage(num_sdept VARCHAR2) ISBEGINUPDATE student SET sagesage1 WHERE TRIM(sdept)num_sdept;END pro_regulage_sage;
END pack_student;索引
快速存取数据通过索引数据库可以快速定位到表中的特定行减少查询时间。改善数据库性能索引可以减少数据库在查询、排序和分组等操作时的I/O开销。保证列值的唯一性唯一索引可以确保表中不会有两行记录具有相同的索引键值。减少排序和分组时间在使用ORDER BY、GROUP BY子句进行数据检索时利用索引可以减少排序和分组的时间。 分类
B-Tree索引案例
场景描述 假设有一个员工表employees包含员工IDemp_id、姓名name和部门IDdepartment_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 123456DEFAULT TABLESPACE USERSTEMPORARY 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 snamemm WHERE sno111;
SAVEPOINT s2;
UPDATE student SET snamexxx WHERE sno111;
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数据库能够自动检测死锁并选择一个事务进行回滚以释放其持有的锁资源从而解决死锁问题。
补充持续更新
兄弟们今天找了个网站里面的sql有些关键字是我没有写出来的sql 有兴趣可以看一下以下是我之前练题时常用的以及一下之前没提到的
补充
DENSE_RANK() OVER (ORDER BY salary DESC) AS rn --排名相同则编号相同
--对结果集中的行按照salary列的值进行降序排序并为每行分配一个DENSE_RANK值这个值被命名为rn。如果有多名员工拥有相同的薪水即并列则这些员工将获得相同的排名并且紧随其后的员工的排名将是下一个连续的数字而不是跳过并列排名的数量。TO_CHAR()
TO_DATE()
规范时间
YYYY-MM-DD年-月-日
MM/DD/YYYY月/日/年
DD.MM.YYYY日.月.年
YYYY-MM-DD HH24:MI:SS年-月-日 时:分:秒
YYYY-MM-DD HH24:MI:SS.FF年-月-日 时:分:秒.毫秒窗口
排名函数
ROW_NUMBER()为每一行分配一个唯一的整数从1开始根据指定的排序顺序。
RANK()对每一行进行排名当遇到相同值时排名会跳过一些数字。
DENSE_RANK()对每一行进行排名但不会跳过排名数字即使遇到相同值。
偏移量函数
LAG()用于获取当前行前一行或前N行的值。
LEAD()与LAG相反用于获取当前行后一行或后N行的值。decode()--判断
decode(mod(id,2),1 ,x,y)--如果mod(id,2)结果为1则返回x否者返回y
例力扣626.换座位分布函数
FIRST_VALUE()获取窗口中第一个非空值。
LAST_VALUE()获取窗口中最后一个非空值。聚合函数作为窗口函数
聚合函数如SUM()、AVG()、COUNT()、MAX()、MIN()等也可以作为窗口函数使用它们会对窗口内的数据进行相应的聚合计算。例如开窗排序 PARTITION BY customer_id按照customer_id 分组排序 order by order_date组内升降关系
ROW_NUMBER() over(PARTITION BY customer_id order by order_date ) as ref SUM() OVER()计算窗口内的累计总和。
AVG() OVER()计算窗口内的平均值。
COUNT() OVER()计算窗口内的行数。
MAX() OVER()找出窗口内的最大值。
MIN() OVER()找出窗口内的最小值。nvl(bonus,0)0
--将bonus某字段中的null当作0进行比较
--NVL函数将NULL替换为0然后比较结果是否等于0。返回boolbitand(id, 1)1判断为奇数
bitand(id, 1)0判断为偶数
被执行时它会将 id 的二进制表示与 1即 0001假设 id 是正整数进行位与操作。如果 id 是奇数那么它的最低位二进制中的最右边一位将是 1与 1 进行位与操作的结果也是 1。因此bitand(id, 1) 1 可以用来检查 id 是否为奇数。roundx,2保留2位小数rownum1 只返回第一条数据
例如力扣1204. 最后一个能进入巴士的人首字母大写
initcap()
获取字符
SUBSTR(my_column, 1, 1) 获取my_column字段中每个值的第一个字符。
SUBSTR(my_column, 2) 获取从第二个字符开始到字符串末尾的所有字符。
UPPER()转大写
LOWER()转小写trim()去除两端特殊字符一般指空格不同行字符串合并
mysql
GROUP_CONCAT(distinct product order by product SEPARATOR ,)
--将一个分组里的product按product顺序查找并,分割为一行
oracle--当判断某个字段值为null使用is
xxx is null--行列转换
--pivot即为转换列---行
输入
Department table:
----------------------
| id | revenue | month |
----------------------
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
----------------------
输出
---------------------------------------------------------------
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
---------------------------------------------------------------
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
---------------------------------------------------------------
--查询变为唯一表如某月某部门只有一项收入
select * from (select id, month,sum(revenue ) as revenuefrom Department group by id,month
)
--转换表将收入集合起来对于日期month中数据转换
pivot(sum(revenue) for month in (Jan Jan_Revenue,Feb Feb_Revenue,Mar Mar_Revenue,Apr Apr_Revenue,May May_Revenue,Jun Jun_Revenue,Jul Jul_Revenue,Aug Aug_Revenue,Sep Sep_Revenue,Oct Oct_Revenue,Nov Nov_Revenue,Dec Dec_Revenue)
)--unpivot即为转换行---列
select t.product_id, lower(t.store) store, t.price
from Products
unpivot(price for store in(store1, store2, store3)
) t
输入
Products table:
------------------------------------
| product_id | store1 | store2 | store3 |
------------------------------------
| 0 | 95 | 100 | 105 |
| 1 | 70 | null | 80 |
------------------------------------
输出
---------------------------
| product_id | store | price |
---------------------------
| 0 | store1 | 95 |
| 0 | store2 | 100 |
| 0 | store3 | 105 |
| 1 | store1 | 70 |
| 1 | store3 | 80 |
-----------------------------约束分类
NOT NULL--不为空
UNIQUE --唯一
PRIMARY KEY--主键
FOREIGN KEY--外键
CHECK --约束条件
DEFAULT --默认值CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P0),对Id_P进行约束
LastName varchar(255) NOT NULL
FirstName varchar(255) UNIQUE
Address varchar(255) PRIMARY KEY
City varchar(255) FOREIGN KEY REFERENCES Person(Id_P)
Name varcher(255) DEFAULT Sandnes)
