Oracle学习记录——2.Oracle的CRUD

偏执的太偏执、 2023-06-14 05:24 0阅读 0赞

### 文章目录 ###

*  1.SQL语言简介
 *  2.创建和管理表
 *   *  表的建立
     *  利用子查询创建表格
     *  表的重命名
     *  截断表
     *  表的删除
     *  Oracle 10g以后的新特性闪回技术
     *  表的修改
 *  3.表的操作
 *   *  Insert语句
     *  Update语句
     *  Delete语句
 *  4.事务
 *  5.表约束的创建与管理

# 1.SQL语言简介 #

SQL 是 Structured Query Language（结构化查询语言）的首字母缩写词 SQL 是数 据库语言，Oracle 使用该语言存储和检索信息.

表是主要的数据库对象，用于存储数据

**SQL 语句可分为 5 类**：

1.  查询语句：检索数据库中的数据；SELECT
2.  数据操纵语句：修改和删除数据库中的数据；INSERT,UPDATE,DELETE ，DML(数据操作语言)
3.  数据定义语句：定义数据库结构；CREATE,ALTER,DROP,RENAME,TRUNCATE，DDL(数据定义语言)
4.  事务控制语句：用于撤销或提交某些操作；COMMIT,ROLLBACK,SAVEPOINT
5.  数据控制语句：用于控制数据的访问权限。GRANT,REVOKE ，DCL(数据控制语言)

可以通过一些辅助软件将 SQL 语句发送给数据库管理系统来执行；也可以在 java、 c++等应用程序中使用 SQL 语句。

**数据库中的主要对象**:

*  表(TABLE):数据库存储的基本单元,由行和列组成
 *  约束条件(CONSTRAINT):用来确保数据库中数据的完整性
 *  视图（VIEW）:一个或多个表的逻辑表示或虚拟表，主要用于简化查询操作
 *  索引（INDEX）:用来加速数据访问的数据库对象。
 *  序列（SEQUENCE）:用于生成唯一数字值的数据库对象。
 *  同义词（SYNONYM）：对象的别名。

另外数据库中还有存储过程、函数、触发器、包等对象。

# 2.创建和管理表 #

对于数据库而言实际上每一张表都表示一个数据库的对象，而数据库对象指的就是 DDL 定义的所有操作，例如：表、视图、索引、序列以及约束等等，都属于对象的操作，所以表的建立就是对象的建立，操作主要分为以下三类：

*  创建对象：CREATE 对象名称……;
 *  删除对象：DROP 对象名称……;
 *  修改对象：ALTER 对象名称……;

**Oracle常用数据类型**

*  Varchar2：Oracle特有的类型，是取值长度为1~4000的字符串
 *  Number：表示数字类型
 *  Date：日期类型，时间精确到天数
 *  Timestamp：Date数据类型的拓展，时间精确到毫秒
 *  BLOB：二进制数据大对象类型
 *  CLOB：字符数据大对象类型
 *  BFILE：数据库外部二进制文件

## 表的建立 ##

表的建立还是按照标准的语法进行，但是在表的建立时有时候会指定约束，那么此处先 给出一个建立表的简单语法。

CREATE TABLE 表名称( 
    字段名称1 字段类型 [default 默认值], 
    字段名称2 字段类型 [default 默认值], 
    .... 
    字段名称n 字段类型 [default 默认值] 
    )

## 利用子查询创建表格 ##

之前学习过的语法来创建表：

CREATE TABLE 表名称 as (子查询)

将子查询的结果作为一张表

示例如下：

CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=1;

因为1=1，所以条件恒成立，即会复制原表结构+数据

CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE 1=2;

因为1!=2，所以条件恒不成立，即会复制原表结构，不会复制数据

## 表的重命名 ##

在 Oracle 中提供 rename 命令，可以为表重新进行命名。语法格式如下：

RENEME 旧的表名称 TO 新的表名称;

示例：

RENAME EMP2 TO EMP4;

修改表emp2为emp4；

## 截断表 ##

使用 DELETE 操作可以删除表数据，但是这种删除操作本身有一个特点，即：可以进行事物 的回滚，也就是说删除之后并不会立刻释放数据的资源，如果现在希望彻底释放掉一张表所占用的全部资源（表空间、索引等等）就可以使用截断表的语法，如下

TRUNCATE TABLE 表名称

注意：

*  它删除表中所有的数据行，但保留表的结构；
 *  如果没有备份的话，所删除的数据行无法恢复；
 *  该语句释放表所占用的磁盘空间；
 *  它并不触发（运行）表的删除触发器。

## 表的删除 ##

表的删除操作指的是数据库对象的删除，使用的是 DROP 语句，语法如下

drop table 表名称;

这个和截断删除数据区别就是，drop会直接把表删了，数据+结构将全部不复存在。

## Oracle 10g以后的新特性闪回技术 ##

Oracle 10g 之后，为了预防用户的误删除表操作，专门提供了回收站的功能，用户所删除的 表默认情况下在一个回收站之中保存，而用户也可以通过回收站进行表的恢复，所以此技术称为闪回（FLASHBACK）。

查看回收站：

SHOW RECYCLEBIN

恢复表：

FLASHBACK TABLE 表名称 TO BEFORE DROP

删除回收站的表：

PURGE TABLE 表的名称

清除回收站：

PURGE RECYCLEBIN

如果现在希望删除一张表，而又不希望其进入到回收站之中，则可以在删除的时候增加PURGE:

DROP TABLE 表名称 PURGE;

## 表的修改 ##

在 SQL 语法操作中，提供了 ALTER 指令，通过 ALTER TABLE 语句来修改表结构。

1.  在一个表中加入一个新的列，格式如下：

ALTER TABLE 表名 ADD(列名数据类型 [DEFAULT 默认值]，
    列名数据类型 [DEFAULT 默认值]，…….

1.  修改在一个表中已经存在的列，格式如下：

ALTER TABLE 表名 MODIFY(列名数据类型 [DEFAULT 默认值]，
    列名数据类型 [DEFAULT 默认值]，…….

可以修改如下项：

*  修改列的可见性、
 *  允许或不允许NULL值、
 *  缩短或扩大列的大小
 *  更改列的默认值
 *  修改虚拟列的表达式

1.  在一个表中删除一列，格式如下：

ALTER TABLE 表名 DROP COLUMN 列名；

# 3.表的操作 #

最主要的操作就是CRUD：

*  Insert 语句 (增加数据)
 *  Update 语句 (更新数据)
 *  Delete 语句 (删除数据)
 *  Select 语句 (查找数据)

这里暂时只介绍Insert、Update、Delete，至于Select留到后面再介绍。

## Insert语句 ##

用来在数据表中增加记录，格式如下：

INSERT INTO tablename [(column, column, ….)]VALUES (value, value,...);

命令中\[(column, column, ….)\]是可选的。一般情况下，为了编程的方便，最好指定 字段列表。该命令每次只能增加一条记录。注意，CHARACTER 和 DATE 必须用单引号括起来。

注意：列名字段不加**单引号**和**双引号**，这点和MySql不同。

另外，要想增加Date类型的数据时，需要使用TO\_DATE函数进行转化。这样就牺牲了sql的通用性。

示例如下：

TO_DATE(‘24/06/2084 9:30’,’DD/MM/YYYY HH:MI’),

## Update语句 ##

在需要修改表中数据时，可使用 update 命令如下：

UPDATE table[alias] 
    SET column[,column….] = {expression, subquery} 
    [WHERE condition];

命令由三部分组成：

*  update 后跟一个或多个要修改的表，这部分是**必不可少**的。
 *  set 后跟一个或多个要修改的表列，这也是**必不可少**的。
 *  where 后跟查询条件，这是选项；如果 WHERE 子句忽略，UPDATE 命令将修改数据表中所有记录（必须是具体到能够修改的值的行）

## Delete语句 ##

Delete 命令用来从表中删除一行或多行记录。命令格式如下：

DELETE FROM table [WHERE condition];

该命令由两部分组成：

*  delete from 后跟准备要从中删除数据的表名，这是**必不可少**的。
 *  where 后跟删除条件，是可选项；如果不用 WHERE 子句，数据表中的

所有记录将被删除。

# 4.事务 #

事务是由一串修改数据库的操作组成的。Oracle 中有两种事务：DML 事务和 DDL 事务。

*  DML 事务是一些 DML 语句组成的，Oracle 把事务作为单个实体或逻辑工作单元来处理；
 *  DDL 事务只能由一条 DDL 语句组成。

事务的执行必须是完整的，也就是说事务处理中一部分提交给数据库而其他部分不 提交这是不允许的。对于事务来说，要么事务中所有处理都提交，要么所有的处理都放弃。

事务是以可执行的 DML 或 DDL 命令开始，以下面的情况结束：

*  COMMIT/ROLLBACK
 *  DDL 命令（DDL 语句是自动提交）
 *  一些错误（如死锁）
 *  注销（如退出 SQL\*Plus）
 *  硬件错误

**COMMIT和ROLLBACK的区别**

*  永久性修改——COMMIT

为了使修改变成永久性，这些修改必须提交给数据库。COMMIT 命令可以用来使数 据库永久性改变。而 ROLLBACK 可以撤消或放弃修改。在两次提交之间对数据库的修改 就是事务。

*  撤消修改——ROLLBACK

ROLLBACK 可以放弃不提交的修改。ROLLBACK 可以恢复上次提交之后修改过的数据。

# 5.表约束的创建与管理 #

在数据库表的开发中，是必不可少的支持。使用约束可以更好的保证数据库中的数据完 整性。数据库中的约束分类：

*  **主键约束** primary key：主键表示是一个唯一的标识，本身是不能为空的
    
     *  例如：身份证号是唯一的，不可重复，不可为空
 *  **唯一约束** unique：在一个表中只允许建立一个主键约束，而其他列如果不希望出现重复值的话，则可以使用唯一约束。
 *  **检查约束**：检查一个列的内容是否合法
    
     *  例如：年龄。只能在 0~150 之间
     *  例如：性别，只能是男、女
 *  **非空约束**：姓名这样的字段里面的内容就不能为空
 *  \*\*外键约束：\*\*在两张表中进行约束操作。