《SQL基础教程（第2版）Oracle》——（标准SQL的书写方式及其DDL语言）

Flerken101

已于 2024-10-05 11:18:02 修改

阅读量843

点赞数 8

分类专栏：《SQL基础教程（第2版）》文章标签： sql 数据库 oracle

于 2024-09-22 22:31:11 首次发布

本文链接：https://blog.csdn.net/qq_44883214/article/details/142444635

版权

《SQL基础教程（第2版）》专栏收录该内容

4 篇文章 0 订阅

订阅专栏

关系数据库是以行为单位存储或者读写数据的。
行式存储？列式存储？区别是啥？？？—— 岛主
一个单元格中只能输入一个数据。

在标准SQL的书写方式中：

SQL语句以分号（;）结尾。
SQL 不区分关键字、表名称、列名称的大小写。【但插入到表中的数据是区分大小写的。】

一般使用以下规则来书写SQL语句。
●关键字大写 ● 表名的首字母大写 ● 其余（列名等）小写

字符串和日期常数需要使用单引号（'）括起来。数字常数无需加注单引号（直接书写数字即可）。
如果SQL语句中有’'（单引号）括的字符串，则需要同表中严格一致，不然会显示“找不到行”这个错误。
SQL语句单词需要用半角空格或者换行来分隔。
SQL中子句的书写顺序是固定的，不能随意更改。例如：WHERE子句必须紧跟在FROM子句之后，书写顺序发生改变的话会造成执行错误。
单行注释，写在“- -”（中间没有空格）之后。

 MySQL中需要在“--” 之后加入半角空格。（如果不加的话就不会被认为是注释）。

多行注释书写在 “ / * ”和 “ * / ” （前后中间都没有空格）之间，可以跨多行。

1. DDL语言（Data definition language），数据定义语言：

用于描述数据库中要存储的现实世界实体的语言。
用来创建及删除数据库及数据库中的表。

1.1 CREATE：创建数据库和表等对象

1.1.1 创建数据库的CREATE DATABASE语句

# 创建数据库的CREATE DATABASE语句
CREATE DATABASE <数据库名称>;

1.1.2 创建表的CREATE TABLE语句

# 创建表的CREATE TABLE语句 
CREATE TABLE <表名>
(<列名1> <数据类型> <该列所需约束>,
<列名2> <数据类型> <该列所需约束>, 
<列名3> <数据类型> <该列所需约束>, 
<列名4> <数据类型> <该列所需约束>, 
. . .
<该表的约束1>， <该表的约束2>，……);


# 如：在Oracle中，创建表Product
CREATE TABLE Product
(product_id      CHAR(4)       NOT NULL,
 product_name    VARCHAR2(100)  NOT NULL,
 product_type    VARCHAR2(32)   NOT NULL,
 sale_price      INTEGER ,
 purchase_price  INTEGER ,
 regist_date     DATE ,
 PRIMARY KEY (product_id));

在标准SQL的书写方式中：
1. 数据库、表、列名称必须以半角英文字母开头。且由半角英文字母、半角数字、下划线（_）组成。
2.表名称，列名称均不能重复。

如：在Oracle中，创建表ProductIns

CREATE TABLE ProductIns
(product_id      CHAR(4)        NOT NULL,
 product_name    VARCHAR2(100)  NOT NULL,
 product_type    VARCHAR2(32)   NOT NULL,
 sale_price      INTEGER        DEFAULT 0,
 purchase_price  INTEGER ,
 regist_date     DATE ,
 PRIMARY KEY (product_id));

在这里插入图片描述

1.1.2.1 字段数据类型

（1）数字型：INTEGER，存储整数，不能存储小数。
Java基础之int和Integer有什么区别——一枚-狗子

（2）NUMBER
NUMBER 数据类型是一种通用的数值类型，它包括所有的数值类型，并且具有最大的精度和范围。
NUMBER 可以存储任意大小的数字，包括整数和小数，其精度为38位。
使用number类型存储数字可以更方便地进行计算和操作，有多少位就存储多少位。

NUMBER 类型可以使用参数来指定精度和范围，例如 NUMBER(p,s)，其中 p 表示总位数，s 表示小数位数。

oracle number与numeric——董晓敏

（3）NUMERIC
NUMERIC 数据类型也是一种数值类型，但是其精度和范围是有限的。

NUMERIC 的精度范围会随着数值范围的变化而变化。
例如，在SQL Server数据库中，numeric数据类型支持存储1到38位的数字，
而在MySQL数据库中，numeric数据类型支持存储1到65位的数字。

在开发数据库时，数据的精度和规模对于确保数据的正确性至关重要。
使用numeric数据类型可以确保存储的数据始终具有所需的精度和规模。
如果数据需要进行精确计算，例如在财务、百分比计算中，使用numeric数据类型是必要的，避免因为浮点数的误差导致的计算错误。

在 NUMERIC 类型中指定精度和范围的方式与 NUMBER 类型相同。

（4）字符型
定长字符串：CHAR（最大长度），在字符数未达到最大长度时，会用半角空格补足。
可变长字符串：VARCHAR（最大长度），即使字符数未达到最大长度，也不会用半角空格补足。
【在Oracle中推荐使用 VARCHAR2 类型。】
oracle中char、varchar和varchar2区别——斌阿哥

varchar2把所有字符都占两字节处理(一般情况下)，
varchar只对汉字和全角等字符占两字节，数字、英文字符等都是一个字节；
VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；
VARCHAR2字符要用几个字节存储，要看数据库使用的字符集。
大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

（5）日期型：DATE，存储年/月/日时:分:秒
Oracle：时间类型——不怕猫的耗子A

1.1.2.2 列的约束及表的约束

约束是除了数据类型之外，对列中存储的数据（每一个字段可以存储的内容）进行限制或者追加条件的功能。

对同一列可以同时设置多个约束。

（1）针对列的 NULL 或者 NOT NULL 约束，只能以列为单位进行设置，不能写在CREATE TABLE语句的末尾。
如果是针对列的NULL约束的话，可以省略NULL，代表可以输入空白；
如果是针对列的 NOT NULL 约束的话，不能省略，代表不能输入空白。

在创建好的，已存在数据的表中，只有没有NULL值的字段，才能为其添加NOT NULL约束。
SQL NOT NULL 约束——菜鸟教程
所以，如果要为字段添加NOT NULL约束，应该先确认并处理其中的NULL值。

（2）针对列的 DEFAULT 约束

# 语法
DEFAULT <默认值>

如在上面创建表ProductIns时的CREATE TABLE语句中，针对字段sale_price设置了 DEFAULT 约束，且默认值设置为了0

 sale_price      INTEGER        DEFAULT 0,

（3）针对表的主键约束：PRIMARY KEY（主键字段）——>>放在表尾
键分为很多类，除主键之外的普通的键可以是列或列的组合（即可以有多列/多个字段），确定的记录/数据可以是特定的一行，也可以是特定的多行。

SQL PRIMARY KEY 约束——菜鸟教程
 Oracle之主键(Primary Key)用法详解——paullinjie
但关于表的主键约束（PRIMARY KEY 约束）及其对应的主键（括号中的内容）：
①一张表中只能设置一个主键约束
【CREATE TABLE语句中，最后只能有一个PRIMARY KEY语句。】

②主键的任何取值都不能为空。而且必须是唯一的。

主键可以由一列/一个字段构成，设置主键约束。
但如果表中没有字段可以当唯一主键，即表中的每个字段都是有重复值的，因此无论> 单独使用哪个字段，都会有多个数据/记录与之相对应。此时：可以为整个表，使用> > 多列/多个字段充当复合/联合主键，设置主键约束。以保证根据复合主键中每个字段> 值，可以唯一地确定表中一条记录。
原则：一般情况下，主键中包含的字段数目要越少越好。

当主键由一个字段构成时，那么该字段的每个取值都不能为NULL值，且每个值都是唯一的。

当主键由多个字段构成时，该复合/联合主键的中每个字段取值也都不能为NULL值。
由这些字段值形成的组合值，也必须是唯一的。
拓展：MySQL 主键是否需要“NOT NULL”约束?——极客教程

③由主键约束中的主键确定/标识的记录/数据，只能是唯一的一行。

# 在Oracle中撤销设置好的主键
ALTER TABLE <表名>
  DROP PRIMARY KEY;

# 在Oracle中，为创建好的表设置主键
ALTER TABLE <表名>
  ADD PRIMARY KEY (<字段1>, <字段2>, .....);
# 注意：主键中的字段，只能包含被设置为NOT NULL约束的字段。
# 没有的话，要先为字段添加NOT NULL约束。

1.1.3 创建视图的CREATE VIEW…AS语句

# 创建视图的语法
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) 
AS 
<定义视图的SELECT语句>;  --其中SELECT子句不能使用ORDER BY子句

# 创建视图的一般语法：【SELECT语句中，除了ORDER BY子句都可扩展】
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) 
AS 
SELECT DISTINCT <列1>, <列2>, <列3>, ……  
  -- DISTINCT关键字不需要时可以省略；
  -- 列1、2、3可以是字段，也可以是表达式。必须和视图中的列数量相等，且名称一一对应。
  -- 也可以通过SELECT *来指定全部列
  FROM <创建视图所使用的原表>  --【可以ON多张表】
  WHERE <对整个表中的查询条件或其组合>    --不需要时，可以省略
  GROUP BY <聚合键1>, <聚合键2>, <聚合键3>, ……     --不需要时，可以省略
  HAVING <分组结果对应的筛选条件>;      --不需要时，可以省略


（1）<定义视图的SELECT语句>   中的列名 <列1>, <列2>, ......  不需要与 
最外层SELECT中的视图列名 <视图列名1>, <视图列名2>, ......   完全相同。
  但列的个数和意义必须相同，系统会自动一一对应。
  【区别子查询的定义。】
（2）<定义视图的SELECT语句> 可以扩展的子句有：
FROM、WHERE、GROUP BY、HAVING 【唯独不可使用ORDER BY】

视图在存储设备（硬盘）中保存的就是创建其本身时，使用到的SELECT语句。

当每次从视图中读取数据时：
（1）都会将该SELECT语句发送给服务器。
（2）服务器在接受后，就会执行，即从保存在存储设备（硬盘）的数据库中读取原表的数据，经过各种计算，得到用户请求的结果数据。
（3）服务器会创建出一张临时表保存这些结果数据，再将这张临时表返回给视图。
【所以我们可以把视图当作表一样，使用“FROM 视图”这种语句。
但又说：“视图的本质就只是创建其时使用的SELECT语言”，“视图中保存的仅仅是SELECT语句”。】
【临时表会随时消失，但视图只要不被删除，就不会消失。
视图在存储设备（硬盘）中保存的SELECT语句，也不会消失。】

区别子查询：
子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中。
子查询，是一次性视图（一次性SELECT语句）。因此子查询及其名称，在SELECT语句执行完毕之后就会消失。

视图的优点：
（1）无需保存数据，因此可以节省存储设备的容量。
【每一次从视图中读取数据，就会像上面那样这样执行一次。
这些操作与将结果数据保存在内存空间中相比，消耗的资源比较少。】
（2）可以将频繁使用的SELECT 语句保存成视图，这样就不用每次都重新书写了。
特别是在进行汇总以及复杂的查询条件导致SELECT语句非常庞大时，使用视图可以大大提高效率。
（3）视图中的结果数据会随着原表的变化自动更新，可以保证数据的最新状态。
这也是将数据保存在表中所不具备的优势，不需要显式地执行SQL更新语句。

可以以某一视图为基础创建多重视图。
但应该尽量避免。对多数DBMS来说，多重视图会降低SQL的性能。

视图的限制：
（1）定义视图时不能使用ORDER BY子句；
从视图中读取数据时，存储其保存的SELECT语句的结果数据的临时表也是：
没有被执行过任何操作的，保持原始状态的原表。因此数据行都是没有顺序的。
【在PostgreSQL数据库中，可以执行带有ORDER BY子句的CREATE VIEW语句，但这并不是通用的语法。不要使用。】
（2）只有定义视图的SELECT语句能够满足某些条件，这个视图才可以在被使用阶段时，通过INSERT、DELETE、UPDATE 这类更新语句，实现被更新。

一些比较具有代表性的条件：
① SELECT子句中未使用DISTINCT
② FROM子句中只有一张表
③ 未使用GROUP BY子句
④ 未使用HAVING子句
【③ ④的原因：视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。】

即：在定义视图时，只有使用下面这样的语句时：【既没有聚合又没有结合的SELECT语句】
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……) 
AS 
SELECT <列1>, <列2>, <列3>, ……     --列1、2、3只能是来自原表的字段，不能是表达式
 FROM <创建视图所使用的原表(仅有一张表)>      
WHERE <对整个表中的查询条件或其组合>    --不需要时，可以省略

才可以在FROM子句中使用视图的阶段，
使用DML语言中INSERT、DELETE、UPDATE 这类更新语句，更新视图中的数据：
INSERT INTO <视图名称>  列清单  VALUES  值清单;
INSERT INTO <视图名称>  列清单  SELECT<语句及其扩展>;
UPDATE <视图名称> SET <视图列名> = <表达式> ( WHERE <条件>);
DELETE FROM <视图名称> (WHERE <条件>);
【而且更新时，视图中的字段也受 与对表中字段及表 同样的约束（字符类型、NOT NULL约束、主键约束等）。】

PostgreSQL中的视图被初始设定为只读，因此更新前必须先通过指令，来允许更新操作。

# 允许PostgreSQL对视图进行更新
CREATE OR REPLACE RULE insert_rule 
AS ON INSERT 
TO <视图名称> DO INSTEAD 
INSERT INTO <创建视图所使用的原表(仅有一张表)> 
VALUES (new.原表列1,
		new.原表列2,
		......);

1.2 DROP：删除数据库和表等对象

1.2.1 删除数据库的DROP DATABASE语句

# 删除数据库的DROP DATABASE语句
DROP DATABASE;  # （10g及以后版本适用）

这条语句只删除了数据库文件（控制文件、数据文件、日志文件），
并不删除 $ ORACLE_BASE/admin/ $ ORACLE_SID目录下的文件，
也不会删除初始化参数文件及密码文件。

1.2.2 删除表的DROP TABLE语句

# 删除表时使用的DROP TABLE语句
DROP TABLE <表名>;

DROP TABLE语句会完全删除整张表，且删除了的表是无法恢复的。
在执行DROP TABLE语句之前请务必仔细确认。
删除之后再想插入数据，就必须使用CREATE TABLE语句重新创建一张表。

区别DML语言中的DELETE：
DELETE语句在删除数据（行）的同时会保留数据表，因此可以通过INSERT语句再次向表中插入数据。

Oracle之删除数据之后如何恢复的方法总结——远方的风景

# 在oracle中，使用下列语句可以恢复被删除的表（假设表名称为Product） ：
select * from user_recyclebin;
FLASHBACK TABLE Product TO BEFORE DROP;
# 但也还是需要导出结果再重新建表和插入数据。
# 无论如何，执行DROP TABLE语句之前都要仔细确认。

1.2.3 删除视图的DROP VIEW语句

# 语法
DROP VIEW 视图名称;

关于视图对象、列、行的删除总结：
（1）视图对象的删除：DROP VIEW 视图名称;

（2）视图中列的删除：
标准SQL中，没有这个语法，不同的数据库中使用不同的语法。
【如：MySQL中可能使用ALTER VIEW 语句】

Oracle数据库不允许直接删除视图中的列，但可以通过创建一个新的视图来完成这个操作。
新的视图将会从原视图中排除要删除的列。
Oracle 如何从视图中删除列——极客教程

语法如下：

CREATE VIEW <新视图名称> 
AS
SELECT <要保留的列>
FROM <原视图名称>;

（3）视图中行的删除：
同表的行删除一样，使用DELETE语句。
【但清空视图时不能使用TRUNCATE 语句】

创建视图时要满足一定的条件才可以使用DELETE，
详情见1.1.3 创建视图的CREATE VIEW语句 – 视图的限制第（2）点。

在视图中删除了某记录之后，创建视图使用的原表中的同一记录也会被删除。

1.3 ALTER：修改数据库中的表

表定义变更之后无法恢复。在执行 ALTER TABLE语句之前请务必仔细确认。
误添的列可以通过 ALTER TABLE … DROP 语句删除，
误删除的列，需要将表全部删除之后，重新再创建。

1.3.1 向表中添加列（ALTER TABLE…ADD）

# 添加列的 ALTER TABLE 语句 
ALTER TABLE <表名> ADD COLUMN <列的定义>;

# 在Oracle 和 SQL Server中不用写 COLUMN
ALTER TABLE <表名> ADD <列的定义>;

# 如：向表Product中，添加一列可以存储100位的可变长字符串的product_name_pinyin列
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);
# 或者
ALTER TABLE Product ADD (product_name_pinyin VARCHAR(100));

在Oracle中，使用ALTER TABLE … ADD增加列时，当只添加一列，给列名加或不加括号，都可添加成功。
使用ALTER TABLE … DROP删除列时，即使只删除一列，也一定要给列名加括号，否则无法删除成功。

1.3.2 从表中删除列（ALTER TABLE…DROP）

用来删除所有记录的某几列。

不能用此法删除表中的全部列，否则会报错。
表中至少留一列。

# 删除列的ALTER TABLE语句 
ALTER TABLE <表名> DROP COLUMN <列名>;

# Oracle中不用写 COLUMN
ALTER TABLE <表名> DROP (<列名>);  # 即使只删除一列，也一定要给列名加括号
# 在Oracle中同时删除多列的时候，可以像下面这样使用括号来实现
ALTER TABLE <表名> DROP (<列名>, <列名>, ……);

# 如：删除表Product中的product_name_pinyin一列，也需要给列名加括号
ALTER TABLE Product DROP (product_name_pinyin);

1.3.3 修改列名称

1.3.4 修改一个列的数据类型

(一般限于修改长度，修改为一个不同类型时有诸多限制)

1.4 RENAME：修改表名称

标准 SQL 并没有 RENAME，于是各个数据库便使用了各自惯用的语法。
在Oracle中，使用RENAME，修改表名称的语法为：

ALTER TABLE <变更前的名称> RENAME TO <变更后的名称> ;

创建出了名称错误的表时：
如果还没有向表中插入数据，那么只需要把表删除，再重新创建一个名称正确的表就可以了。
可如果在发现表名错误之前就已经向表中插入了大量数据，再这样做就麻烦了。此时，就可以使用RENAME来修改表名称。

1.5 TRUNCATE：清空表中全部数据行

# 语法：
TRUNCATE <表名>;  --在Oracle中，属于DDL语言
# TRUNCATE后面不能跟任何子句，包括WHERE子句。

# 等价于
DELETE FROM <表名>;  --属于DML语言
# 其处理速度比 DELETE 要快得多。
# 但 DELETE 后面可以跟 WHERE 子句