SQL的特点
- 综合统一
SQL集定义语言、数据操作语言、数据控制语言的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。- 定义和修改、删除关系模式，定义和删除视图，插入数据，创建数据库。
- 对数据库中的数据进行查询和更新。
- 数据库重构和维护。
- 数据库安全性、完整性控制，以及事物控制。
- 嵌入式SQL和动态SQL。
- 高度非过程化
- 面向集合的操作方式
- 以同一种语言结构提供多种使用方式
- 语言简洁、易学易用
数据定义
| 属性 | 操作方式 | ||
|---|---|---|---|
| 创建 | 删除 | 修改 | |
| 模式 | CREATE SCHEMA | DROP SCHEMA | |
| 表 | CREATE TABLE | DROP TABLE | ALTER TABLE |
| 视图 | CREATE VIEW | DROP VIEW | |
| 索引 | CREATE INDEX | DROP INDEX | ALTER INDEX |
模式的定义与删除
- 定义模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];
如果没有指定<模式名>，那么<模式名>隐含为<用户名>。
定义模式实际上定义了一个命名空间，在这个空间中可以进一步吧定义该模式包含的数据库对象（基本表、视图、索引等）。 - 删除模式
DROP SCHEMA <模式名> <CASCADE | RESTRICT>;
CASCADE 和 RESTRICT两者必选其一。
选择了 CASCADE（级联），表示在删除模式的同时把该模式中的所有数据库对象全部删除；
选择了 RESTRICT （限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属对象时才能执行 DROP SCHEMA 语句。
基本表的定义、删除与修改
- 定义基本表
CREATE TABLE <表名> (<列名> <数据类型> [列级完整性约束条件] [,<列名> <数据类型> [列级完整性约束条件]] …… [,<表级完整性约束条件>]); - 数据类型
常见数据类型：数据类型 含义 CHAR(n),CHARACTER(n) 长度为 n 的定长字符串 VARCHAR(n), CHARACTERVARYING(n) 最长长度为 n 的变长字符串 CLOB 字符串大对象 BLOB 二进制大对象 INT, INTEGER 长整数（4字节） SMALLINT 短整数（2字节） BIGINT 大整数（8字节） NUMERIC(p, d) 定点数，由 p 位数字（不包括符号、小数点）组成，小数点后面有 d 位数 DECIMAL(p, d), DEC(p, d) 同NUMERIC REAL 取决于机器精度的单精度浮点数 DOUBLE PRECISION 取决于机器精度的双精度浮点数 FLOAT(n) 可选精度的浮点数，精度至少为 n 位数字 BOOLEAN 逻辑布尔量 DATE 日期，包含年、月、日，格式为 YYYY-MM-DD TIME 时间，包含一日的时、分、秒，格式为HH : MM : SS TIMESTAMP 时间戳类型 INTERVAL 时间间隔类型 - 模式与表
每一个基本表都属于某一个模式，一个模式可包含多个基本表。 - 修改基本表
其中<表名>是要修改的基本表。ALTER TABLE <表名> [ADD [COLUMN] <新列名> <数据类型> [完整性约束]] [ADD <表级完整性约束>] [DROP [COLUMN] <列名> [CASCADE | RESTRICT]] [DROP CONSTRAINT<完整性约束名> [RESTRICT | CASCADE]] [ALTER COLUMN <列名> <数据类型>];
ADD 子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。
DROP COLUMN 子句用于删除表中的列，如果指定了 CASCADE 短语，则自动删除引用了该列的其他对象，比如视图；如果指定了 RESTRICT 短语，则如果该列被其他对象引用，RDBMS 将拒绝删除该列。
DROP CONSTRAINT 子句用于删除指定的完整性约束条件。
ALTER COLUMN 子句用于修改原有的列定义，包括修改列名和数据类型。 - 删除基本表
DROP TABLE <表名> [RESTRICT | CASCADE];
若选择 RESTRICT，则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如CHECK，FOREIGN KEY等约束）不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。
若选择 CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。
默认情况是 RESTRICT。
注意：不同的数据库产品在遵循 SQL 标准的基础上具体实现细节和处理策略会与标准有差别。
索引的建立与删除
当表的数据量比较大是，查询操作会比较耗时。建立索引是加快查询速度的有效手段。
数据库索引有多种类型，常见索引包括顺序文件上的索引、B+树索引、hash索引、位图索引等。
索引虽然能够加速数据库查询，但需要占用一定的存储空间，当基本表更新事，索引要进行相应的维护，这些都会增加数据库的负担，因此要更具实际应用的需要有选择的创建索引。
- 创建索引
其中，<表名>是要建立的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引的排列次序，可选 ASC（升序）或 DESC（降序），默认值为ASC。CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON [表名](<列名>[<次序>][,<列名>[<次序>]]…);
UNIQUE 表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER 表示要建立的索引是聚簇索引。 - 修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>; - 删除索引
DROP INDEX <索引名>;
数据查询
数据查询是数据库的核心操作。SQL提供了 SELECT 语句进行数据查询。其一般格式为：
SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>]…
FROM <表名或视图名> [,<表名或视图名>…]|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
整个 SELECT 语句的含义是，根据 WHERE 子句的表达式从 FROM 子句指定的基本表、视图或派生表中找出满足条件的元组，再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表。
如果有 GROUP BY 子句，则将结果按<列名 1>的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果GROUP BY 子句带 HAVING 短语，则只有满足指定条件的组才予以输出。
如果有 ORDER BY 子句，则结果表还要按<列名 2>的值的升序或降序排序。
单表查询
- 选择表中的若干列
- 查询指定列
SELECT col1, col2 FROM table; - 查询全部列
SELECT * FROM table; - 查询经过计算的值
SELECT 100-col1, 2*col2 FROM table;
- 查询指定列
- 选择表中的若干元组
-
消除取值重复的行
SELECT DISTINCT col1 FROM table; -
查询满足条件的元组
查询满足指定条件的元组可以通过 WHERE 子句实现。查询条件 谓词 比较 =, >, <, >=, <=, !=, <>, !>, !<; NOT + 上述比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NIT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件（逻辑运算） AND, OR, NOT 重点注意字符匹配
[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<转码字符>’]- % 表示匹配任意长度的字符串
- _ 代表任意单个字符
-
- ORDER BY 子句
- 聚集函数
如果指定 DISTINCT 短语，则表示在计算时要取消指定列中的重复值。默认为 ALL。常用聚集函数 功能 COUNT(*) 统计元组个数 COUNT([DISTINCT | ALL] <列名>) 统计一列中值的个数 SUM([DISTINCT | ALL] <列名>) 计算一列值的总和（此列必须是数值型） AVG([DISTINCT | ALL] <列名>) 计算一列值的平均值（此列必须是数值型） MAX([DISTINCT | ALL] <列名>) 求一列值中的最大值 MIN([DISTINCT | ALL] <列名>) 求一列值中的最小值 当聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。
- GROUP BY 子句
GROUP BY 子句将查询结果按某一列或多列的值分组，值相等的一组。
对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。
连接查询
- 等值与非等值连接查询
连接查询的 WHERE 子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为
[<表名 1>.] <列名 1> <比较运算符> [<表名 2>.] <列名 2>
当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。
若在等值连接中把目标列中重复的属性列去掉则为自然连接。
*连接谓词中的列名称为连接字段。*连接条件中的各连接字段类型必须是可比的，但名字不必相同。 - 自身连接
连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接 - 外连接
左外连接：列出左边关系中所有的元组。
table1 LEFT OUTER JOIN table2 ON(连接条件)
右外连接：列出右边关系中所有的元组。
table1 RIGHT OUTER JOIN table2 ON(连接条件) - 多表连接
嵌套查询
在 SQL 语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询。
- 带有 IN 谓词的子查询
例如：
有些嵌套查询可以用连接运算替代，有些是不能替代的。SELECT Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno=‘2’); - 带有比较运算符的子查询
当用户能确切知道内层查询返回的是单个值时，可以用>, <, =, >=, <= !=, <>等比较运算符。 - 带有 ANY（SOME）或 ALL 谓词的子查询
谓词 语义 >ANY 大于子查询结果中的某个值 >ALL 大于子查询结果中的所有值 <ANY 小于子查询结果中的某个值 <ALL 小于子查询结果中的所有值 >=ANY 大于等于子查询结果中的某个值 >=ALL 大于等于子查询结果中的所有值 <=ANY 小于等于子查询结果中的某个值 <=ALL 小于等于子查询结果中的所有值 =ANY 等于子查询结果中的某个值 =ALL 等于子查询结果中的所有值（通常没有实际意义） !=(或<>) ANY 不等于子查询结果中的某个值 !=(或<>) ALL 不等于子查询结果中的任何一个值 - 带有 EXISTS 谓词的子查询
带有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“true” 或逻辑假值“false”。
若内层查询结果为空，则外层的 WHERE 子句返回真值，否则返回假值。
集合查询
SELECT 语句的查询结果是元组的集合，所以多个 SELECT 语句的结果可进行集合操作。主要包括并操作 UNION、交操作INTERSECT 和差操作EXCEPT。
参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同
基于派生表的查询
子查询不仅可以出现在WHERE 子句中，还可以出现在 FROM 子句中，这时子查询生成的临时派生表成为主查询的查询对象。
SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno)
AS Avg_sc(avg_sno, avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno AND SC.Grade >= Avg_sc.avg_grade;
通过 FROM 子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。
SELECT 语句的一般格式
数据查询是数据库的核心操作。SQL提供了 SELECT 语句进行数据查询。其一般格式为：
SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>]…
FROM <表名或视图名> [,<表名或视图名>…]|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
- 目标列表达式的可选格式
-
- <表名>.*
- COUNT([DISTINCT | ALL] *)
- [<表名>.] <属性列名表达式> [, [<表名>.] <属性列名表达式>]…
其中，<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算（+，-，*，/）组成的运算公式。
-
- 聚集函数的一般格式
- WHERE 子句的条件表达式的可选格式
数据更新
插入数据
- 插入元组
其功能是将新元组插入指定表中。其中新元组的属性列1 的值为常量1 ，属性列2 的值为常量2 ，…。INTO 子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL 的属性列不能取空值，否则会出错。INSERT INTO <表名> [(<属性列1> [,<属性列2>]...)] VALUES (<常量1>, [,<常量2>]...);
如果 INTO 子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值。 - 插入子查询结果
INSERT INTO <表名> [(<属性列1>[,<属性列2>...])] 子查询;
修改数据
UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>] ...
[WHERE <条件>];
删除数据
DELETE FROM <表名>
[WHERE <条件>];
DELETE 语句删除的是表中的数据，而不是关于表的定义。
视图
定义视图
- 创建视图
组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。CREATE VIEW <视图名> [(<列名> [,<列名>]...)] AS <子查询> [WITH CHECK OPTION];
以下三种情况下必须明确指定组成视图的所有列名：- 某个目标列不是单纯的属性名，而是聚集函数或列表达式。
- 多表连接时选出了几个同名列作为视图的字段。
- 需要在视图中为某个列启用新的更合适的名字。
DBMS 执行 CREATE VIEW 语句的结构只是把视图的定义存入数据字典，并不执行其中的 SELECT 语句。
- 删除视图
DROP VIEW <视图名> [CASCADE];
视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用 CASCADE 级联删除语句把该视图和由它导出的所有视图一起删除。
查询视图
视图定义后，用户就可以像对基本表一样对视图表进行查询。
关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为 视图消除。
目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能做转换了。
更新视图
更新视图是通过视图来插入(INSERT)、删除(DELETE)、和修改(UPDATE)数据
由于视图是不存在存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。
像查询视图那样，对视图的更新操作也是通过视图小姐，转换为对基本表的更新操作。
一般的，行列子集视图是可更新的。
视图的作用
- 简化用户的操作
- 使用户能以多种角度看待同一数据
- 视图对重构数据库提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适当利用视图可以更清晰地表达查询
扫一扫
582
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
