关系数据库标准语言SQL

最新推荐文章于 2022-11-18 20:33:57 发布

suoyue_zhan

最新推荐文章于 2022-11-18 20:33:57 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签： SQL

本文链接：https://blog.csdn.net/suoyue_py/article/details/101074415

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

（一）概念：

结构化查询语言SQL（Structured Query Language），是关系数据库的标准语言
SQL由以下6类内容组成：

数据定义语言（DDL）：创建、删除表结构的语言，包括Create、Drop
数据控制语言（DCL）：为定义数据访问及修改权限而实现的语句，包括Grant、Revoke
数据查询语言（DQL）：定义从数据表中查询已有数据的方法，例如Select
数据操作语言（DML）：定义对数据表中的数据进行增、删、改的方法，包括Insert、Delete、Update
事物处理语言（TPL）：为保证多条SQL语句的数据一致性而定义的语句，比如Commit、Rollback
指针控制语言（CCL）：定义对查询到的多条记录进行逐行控制的方法及与Cursor相关的语句

DDL和CCL是数据库管理员常用的语句；CCL是数据库存储过程中开发者需要掌握的技能

（二）SQL的特点

综合统一
高度非过程化
面向集合的操作方式
以同一种语法结构提供多种使用方式
语言简洁，易学易用
SQL功能极强，完成核心功能只用了9个动词

（三）SQL的基本概念

在这里插入图片描述

基本表
本身独立存在的表
SQL中一个关系就对应一个基本表
一个(或多个)基本表对应一个存储文件
一个表可以带若干索引
存储文件
逻辑结构组成了关系数据库的内模式
物理结构是任意的，对用户透明
视图
从一个或几个基本表导出的表
数据库中只存放视图的定义而不存放视图对应的数据
视图是一个虚表
用户可以在视图上再定义视图

基本表是本身独立存在的表，在SQL中一个关系就对应一个表。视图是从一个或几个基本表导出的表。视图本身不存在独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍然存放在导出视图的基本表中。视图在概念上基本与表等同，用户可以在基本表那样使用视图，可以在视图上再定义视图。

（四）SQL的数据定义

在这里插入图片描述
SQL标准不提供修改模式定义和修改视图定义的操作。若要修改，只能删后重建。
1.模式的定义和删除：
定义模式的语句为：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

注意：

调用该命令的用户必须拥有（数据库管理员-DBA）权限，或者获得了DBA授予的CREATE SCHEMA的权限
定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义其它一些数据库对象
AUTHORIZATION：授权，授权给“用户名”
若语句没有指定模式名，则模式名隐含为用户名

删除模式的语句为：

DROP SCHEMA <模式名><CASCADE|RESTRICT>

CASCADE(级联)： 删除模式的同时把该模式中所有的数据库对象全部删除
RESTRICT(限制)： 若该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。

2.基本表的定义、删除与修改：
2.1定义语句格式为：

CREATE TABLE <表名>
      （<列名> <数据类型>[ <列级完整性约束条件> ]
      [，<列名> <数据类型>[ <列级完整性约束条件>]] …
      [，<表级完整性约束条件> ] ）；

<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

常用完整性约束：
主码约束： PRIMARY KEY
唯一性约束：UNIQUE
非空值约束：NOT NULL
参照完整性约束

数据类型：
在SQL中域的概念用数据类型来实现。每个属性来自一个域，取值必须是域中的值。
在这里插入图片描述
模式与表：
每一个基本表都属于某一个模式
一个模式包含多个基本表
定义基本表所属模式：

在表名中明显地给出模式名
在创建模式语句中同时创建表
设置所属的模式

2.2修改基本表：

  ALTER TABLE <表名>
  [ ADD <新列名> <数据类型> [ 完整性约束 ] ]
  [ DROP <完整性约束名> ]
  [ ALTER COLUMN<列名> <数据类型> ]；

<表名>：要修改的基本表
ADD子句：增加新列和新的完整性约束条件
DROP子句：删除指定的完整性约束条件
ALTER子句：用于修改列名和数据类型

2.3删除基本表

DROP TABLE <表名>[RESTRICT| CASCADE]；

RESTRICT：删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象，则此表不能被删除
CASCADE：删除该表没有限制。
在删除基本表的同时，相关的依赖对象一起删除

（五）索引的建立与删除：

建立索引的目的：加快查询速度
谁可以建立索引：DBA 或表的属主（即建立表的人）
DBMS一般会自动建立以下列属性上的索引

 PRIMARY  KEY
 UNIQUE

谁维护索引：DBMS自动完成
使用索引：DBMS自动选择是否使用索引以及使用哪些索引
RDBMS中索引一般采用B+树、HASH索引来实现
B+树索引具有动态平衡的优点
HASH索引具有查找速度快的特点
索引是关系数据库的内部实现技术，属于内模式的范畴
CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引
1.建立索引：
语句格式为：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>     ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；

用<表名>指定要建索引的基本表名字
索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
CLUSTER表示要建立的索引是聚簇索引

唯一值索引
对于已含重复值的属性列不能建UNIQUE索引
对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束
聚簇索引
建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致
在一个基本表上最多只能建立一个聚簇索引

聚簇索引的用途：对于某些类型的查询，可以提高查询效率。
聚簇索引的适用范围：
很少对基表进行增删操作
很少对其中的变长列进行修改操作
一般创建索引的情况：

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

一般不创建索引的情况：

对于那些在查询中很少使用或者参考的列不应该创建索引；
对于那些只有很少数据值的列也不应该增加索引；
对于那些定义为text, image和bit数据类型的列不应该增加索引；
当修改性能远远大于检索性能时，不应该创建索引

2.删除索引：

DROP INDEX <索引名>；

删除索引时，系统会从数据字典中删去有关该索引的描述。

（六）数据查询

语句格式为：

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] |（<SELECT语句>[AS语句]<别名>）
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

SELECT子句：指定要显示的属性列
FROM子句：指定查询对象(基本表或视图)
WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
HAVING短语：筛选出只有满足指定条件的组
ORDER BY子句：对查询结果表按指定列值的升序或降序排序

1.单表查询： 查询仅涉及一个表

选择表中的若干列
选择表中的若干元组
消除取值重复的行：DISTINCT
查询满足条件的元组

通配符：
% (百分号) 代表任意长度（长度可以为0）的字符串
_ (下横线) 代表任意单个字符
ESCAPE 短语：
当用户要查询的字符串本身就含有 % 或 _ 时，要使用 ESCAPE ‘<换码字符>’ 短语对通配符进行转义。
ORDER BY子句：对查询结果排序
可以按一个或多个属性列排序
升序：ASC；降序：DESC；缺省值为升序
当排序列含空值时：（无穷大理解）
ASC：排序列为空值的元组最后显示
DESC：排序列为空值的元组最先显示
聚集函数
计数：
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
计算总和：
SUM（[DISTINCT|ALL] <列名>）
计算平均值：
AVG（[DISTINCT|ALL] <列名>）
求最大值：
MAX（[DISTINCT|ALL] <列名>）
求最小值：
MIN（[DISTINCT|ALL] <列名>）

DISTINCT短语：在计算时要取消指定列中的重复值
ALL短语：不取消重复值
ALL为缺省值
未对查询结果分组，集函数将作用于整个查询结果
对查询结果分组后，集函数将分别作用于每个组

GROUP BY子句：作用对象是查询的中间结果表
分组方法：按指定的一列或多列值分组，值相等的为一组
使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数

使用HAVING短语筛选最终输出结果：
只有满足HAVING短语指定条件的组才输出
HAVING短语与WHERE子句的区别：作用对象不同
WHERE子句作用于基表或视图，从中选择满足条件的元组。
HAVING短语作用于组，从中选择满足条件的组。

2.连接查询： 同时涉及多个表的查询
用来连接两个表的条件称为连接条件或连接谓词
一般格式：

[<表名1>.]<列名1>  <比较运算符>  [<表名2>.]<列名2>
   比较运算符：=、>、<、>=、<=、!=
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

连接字段：
连接谓词中的列名称为连接字段
连接条件中的各连接字段类型必须是可比的，但不必是相同的
连接操作的执行过程：

嵌套循环法(NESTED-LOOP)
排序合并法(SORT-MERGE)
索引连接(INDEX-JOIN)

SQL中连接查询的主要类型：

广义笛卡尔积：不带连接谓词的连接，很少使用
等值连接(含自然连接)：连接运算符为 = 的连接操作
[<表名1>.]<列名1> = [<表名2>.]<列名2>
任何子句中引用表1和表2中同名属性时，都必须加表名前缀。
引用唯一属性名时可以加也可以省略表名前缀。
自然连接： 等值连接的一种特殊情况，把目标列中重复的属性列去掉。
非等值连接查询：连接运算符不是 = 的连接操作
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
比较运算符：>、<、>=、<=、!=
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
自身连接查询：一个表与其自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀
外连接查询
普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
左外连接：列出左边关系中所有的元组
右外连接：列出右边关系中所有的元组
复合条件连接查询：WHERE子句中含多个连接条件

3.嵌套查询：
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
子查询的限制： 不能使用ORDER BY子句
层层嵌套方式反映了 SQL语言的结构化，有些嵌套查询可以用连接运算替代
不相关子查询： 子查询的查询条件不依赖于父查询
相关子查询： 子查询的查询条件依赖于父查询
带有比较运算符的子查询：
当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
与ANY或ALL谓词配合使用
带有ANY或ALL谓词的子查询：
配合使用比较运算符

> ANY	大于子查询结果中的某个值       
> ALL	大于子查询结果中的所有值
< ANY	小于子查询结果中的某个值    
< ALL	小于子查询结果中的所有值
>= ANY	大于等于子查询结果中的某个值    
>= ALL	大于等于子查询结果中的所有值
<= ANY	小于等于子查询结果中的某个值    
<= ALL	小于等于子查询结果中的所有值
= ANY	等于子查询结果中的某个值        
=ALL	等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY	不等于子查询结果中的某个值
!=（或<>）ALL	不等于子查询结果中的任何一个值

用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数
带有EXISTS谓词的子查询：

EXISTS谓词：存在量词
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则返回真值
若内层查询结果为空，则返回假值
由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
NOT EXISTS谓词
不同形式的查询间的替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
用EXISTS/NOT EXISTS实现全称量词
用EXISTS/NOT EXISTS实现逻辑蕴函

4.集合查询
集合操作的种类：

并操作UNION
形式：
<查询块>
UNION
<查询块>
交操作INTERSECT
差操作EXCEPT
参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同
对集合操作结果的排序：
ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序
任何情况下，ORDER BY子句只能出现在最后
对集合操作结果排序时，ORDER BY子句中用数字指定排序属性

补充------SELECT语句的一般格式：
SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ，<目标列表达式> [别名]] …
FROM <表名或视图名> [别名]
[ ，<表名或视图名> [别名]] …
[WHERE <条件表达式>]
[GROUP BY <列名1>[，<列名1’>] …
[HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]
[，<列名2’> [ASC|DESC] ] … ]；

5.数据更新
插入数据：
两种插入数据方式：

插入单个元组：
语句格式：
INSERT
INTO <表名> [(<属性列1>[，<属性列2 >…)]
VALUES (<常量1> [，<常量2>] … )
功能：将新元组插入指定表中
INTO子句：
指定要插入数据的表名及属性列
属性列的顺序可与表定义中的顺序不一致
没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
指定部分属性列：插入的元组在其余属性列上取空值
VALUES子句：
提供的值必须与INTO子句匹配
值的个数
值的类型
插入子查询结果：
语句格式：
INSERT
INTO <表名> [(<属性列1> [，<属性列2>… )]
子查询；
功能：将子查询结果插入指定表中
DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则：
实体完整性
参照完整性
用户定义的完整性
对于有NOT NULL约束的属性列是否提供了非空值
对于有UNIQUE约束的属性列是否提供了非重复值
对于有值域约束的属性列所提供的属性值是否在值域范围内

修改数据：
语句格式：
UPDATE <表名>
SET <列名>=<表达式>[，<列名>=<表达式>]…
[WHERE <条件>]；
功能：修改指定表中满足WHERE子句条件的元组
SET子句：
指定修改方式；要修改的列；修改后取值
WHERE子句：
指定要修改的元组；缺省表示要修改表中的所有元组
三种修改方式：
修改某一个元组的值；修改多个元组的值；带子查询的修改语句
DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则：
实体完整性
主码不允许修改
用户定义的完整性
NOT NULL约束
UNIQUE约束
值域约束

删除数据：
语句格式：
DELETE
FROM <表名>
[WHERE <条件>]；
功能：
删除指定表中满足WHERE子句条件的元组
WHERE子句：
指定要删除的元组；缺省表示要修改表中的所有元组
三种删除方式：
删除某一个元组的值；删除多个元组的值；带子查询的删除语句

DBMS在执行插入、删除、更新语句时必须保证数据库一致性：
必须有事务的概念和原子性
完整性检查和保证

6.视图
视图的特点：
虚表，是从一个或几个基本表（或视图）导出的表。
只存放视图的定义，不会出现数据冗余。
基表中的数据发生变化，从视图中查询出的数据也随之改变。
基于视图的操作：
查询；删除；受限更新；定义基于该视图的新视图
建立视图：
语句格式：

      CREATE  VIEW 
             <视图名>  [(<列名>  [，<列名>]…)]
       AS  <子查询>
       [WITH  CHECK  OPTION]；

组成视图的属性列名：全部省略或全部指定
子查询不允许含有ORDER BY子句和DISTINCT短语
DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。
在对视图查询时，按视图的定义从基本表中将数据查出。
WITH CHECK OPTION：通过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）
组成视图的属性列名：
全部省略或全部指定
省略：由子查询中SELECT目标列中的诸字段组成
明确指定视图的所有列名：
(1) 某个目标列是集函数或列表达式
(2) 目标列为 *
(3) 多表连接时选出了几个同名列作为视图的字段
(4) 需要在视图中为某个列启用新的更合适的名字

删除视图：
DROP VIEW <视图名>；
该语句从数据字典中删除指定的视图定义
如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

查询视图：
从用户角度：查询视图与查询基本表相同
DBMS实现视图查询的方法：

实体化视图（View Materialization）
有效性检查：检查所查询的视图是否存在
执行视图定义，将视图临时实体化，生成临时表
查询视图转换为查询临时表
查询完毕删除被实体化的视图(临时表)
视图消解法（View Resolution）
进行有效性检查
转换成等价的对基本表的查询
执行修正后的查询

更新视图：
用户角度：更新视图与更新基本表相同
DBMS实现视图更新的方法：

视图实体化法（View Materialization）
视图消解法（View Resolution）
指定WITH CHECK OPTION子句后DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新。
实际系统对视图更新的限制：
允许对行列子集视图进行更新
对其他类型视图的更新不同系统有不同限制
DB2对视图更新的限制：
(1) 若视图是由两个以上基本表导出的，则此视图不允许更新。
(2) 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
(3) 若视图的字段来自集函数，则此视图不允许更新。
(4) 若视图定义中含有GROUP BY子句，则此视图不允许更新。
(5) 若视图定义中含有DISTINCT短语，则此视图不允许更新。
(6) 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
(7) 一个不允许更新的视图上定义的视图也不允许更新。

视图的作用：